Notice: On April 23, 2014, Statalist moved from an email list to a forum, based at statalist.org.
From | "Dimitriy V. Masterov" <dvmaster@gmail.com> |
To | Statalist <statalist@hsphsun2.harvard.edu> |
Subject | st: What is pure SQL for odbc? |
Date | Tue, 23 Nov 2010 16:42:11 -0500 |
I've been trying to run some SQL code written by a colleague using the odbc exec() and odbc sqlfile(). I know the code runs successfully in Microsoft SQL Studio. The code does some calculations on data from a table and then left joins them. The exec() part mostly works in that I see what appears to be my data flashing by on the screen, but nothing is actually loaded into Stata. The sqlfile() approach fails, and it complains about about the very first thing I try to do, which is to declare a variable: DECLARE @BeginWeekEndingDate DateTime; DECLARE @EndWeekEndingDate DateTime; SET @BeginWeekEndingDate = '2010-11-7'; SET @EndWeekEndingDate = '2010-11-14'; WITH NetPivot AS ( SELECT * ..... The output looks like this: . odbc sqlfile("Pivot.sql"), dsn("Analysis"); SET @BeginWeekEndingDate = '2010-10-3'; The ODBC driver reported the following diagnostics [Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the scalar variable "@BeginWeekEndingDate". SQLSTATE=42000 r(682); The SQL code is identical in the exec() and sqlfile() approaches. The help file for odbc mentions that the SQL statements must be pure, but that is pretty vague. Things like "SELECT * FROM ..." work splendidly, but presumably something more complicated in TSQL may not. Are the rules defined somewhere? Why does it matter if the SQL statements are just sent along to the server anyway? Any insight about how to make sense of this or make it work would be appreciated. Dimitriy * * For searches and help try: * http://www.stata.com/help.cgi?search * http://www.stata.com/support/statalist/faq * http://www.ats.ucla.edu/stat/stata/