Re: st: Re: What is pure SQL for odbc?

["Dimitriy V. Masterov" <dvmaster@gmail.com>]

Joseph's advice about taking the semicolons out makes the first four
lines work, but now there is a problem with the WITH clause. I
e-mailed Stata to get them to comment on this, so I hope to have an
answer soon, which I will report back.

To answer Nick's question, my data is weekly observations, with each
week identified by Sunday's date. I think that may clarify things.

DVM

On Tue, Nov 23, 2010 at 9:10 PM, Joseph Coveney <jcoveney@bigplanet.com> wrote:
> 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/
>

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