Win32::ODBC getting syntax error while executing sql

srivasva has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks, In my perl script when I am executing below sql by connection to SQL Server through Win32::ODBC I am getting error : SQL failed. Error: 102 1 [0] "Microsoft ODBC SQL Server Driver SQL ServerIncorrect s yntax near ','." whereas when I execute it in SQL Server Management Studio it runs fine. I think it is coz of declare statement . I have a limitation that I cannot put this code in stored proc. Is there any way I can get away with this error ?

use Win32::ODBC;
use Win32::OLE;
$DSN = "dsn=CRRLIVE;uid=$UserID;pwd=$Password";

if (!($db = new Win32::ODBC($DSN))){
    print "Error connecting to $DSN\n";
    print "Error: " . Win32::ODBC::Error() . "\n";
    exit;
}
$SqlStatement = <<ENDSQL;
declare @RepDate datetime    
select @RepDate = max(Date) from CRRWarehouse..AERReportDate where Dat
+e >= '1 jan 2008' and [User] like '%SYS%'    

declare    
    @CntCurrentDealSize           int,
    @CntCurrentStatic             int,
    @CntCurrentTrade              int,
    @CntErrorTrade                int,
    @CntFirstLegalEntity          int,
    @CntGroupReport               int,
    @CntImportBedrockData         int,
--- More code 
ENDSQL
[download]

Comment on Win32::ODBC getting syntax error while executing sql Download Code

Replies are listed 'Best First'.
Re: Win32::ODBC getting syntax error while executing sql by Bloodnok (Vicar) on Nov 26, 2008 at 12:26 UTC
I don't know/can't remember much about SQL Server, but in `$SqlStatement = <<ENDSQL;`, perl treats ENDSQL as being double quoted, hence it will attempt to expand any variables it finds in the here document. Since, from a perl POV, you reference a hash & number of arrays, I suspect this is generating an illegal SQL statement in `$SqlStatement` - try `print`ing the variable. If this _is_ the problem, it's easily cured by replacing `$SqlStatement = <<ENDSQL;` with `$SqlStatement = <<'ENDSQL';` - note the single quotes. HTH , A user level that continues to overstate my experience :-))	[reply] [d/l] [select]
Re: Win32::ODBC getting syntax error while executing sql by jonadab (Parson) on Nov 26, 2008 at 12:18 UTC
`declare @RepDate datetime` Don't you need a semicolon after this declaration? `select @RepDate = max(Date) from CRRWarehouse..AERReportDate where Date >= '1 jan 2008' and [User] like '%SYS%'` Don't you need a semicolon after this SELECT statement? Also, in general, if you're getting an error in your SQL, I would suggest executing just one SQL statement at a time so that you can easily identify which one has the error in it. -- We're working on a six-year set of freely redistributable Vacation Bible School materials.	[reply] [d/l] [select]