st: Re: What is pure SQL for odbc?

["Joseph Coveney" <jcoveney@bigplanet.com>]

Dimitriy V. Masterov wrote:

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.

--------------------------------------------------------------------------------

Try removing the semicolons at the end of the lines when using -odbc sqlfile()-.
That is, your file will contain:
   
   DECLARE @BeginWeekEndingDate DateTime
   DECLARE @EndWeekEndingDate DateTime
   SET @BeginWeekEndingDate = '2010-11-07'
   SET @EndWeekEndingDate = '2010-11-14'
   . . .

Joseph Coveney


*
*   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/