perl DBI error

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

using active perl 5.8 and windows sql server 2000 executing sql statement which has complex sub queries using perl DBI DBD::ODBC get below error

DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver][SQL 
+Server]Internal Query Processor Error: 
The query processor ran out of stack space during query optimization. 
+(SQL-42000)(DBD: st_execute/SQLExecute err=-1)
[download]

the same sql statement gets executed successfully in sql server query analyzer or through asp page using adodb

$sth=$dbh->prepare($sqlarr[2]);    
$sth->execute($reportstartdt,$reportenddt);
[download]

after sth execute get error

i am unable to understand whether problem is in perl or in sql server

any help or pointers for resolution will be helpful thanks in advance
krishna

Code tags and formatting to match original input added by davido.

Comment on perl DBI error Select or Download Code

Replies are listed 'Best First'.
Re: perl DBI error by gellyfish (Monsignor) on Apr 05, 2005 at 16:28 UTC
We really would need to see the query you are passing to `prepare` as the error code `SQL-42000` indicates simply `Syntax error or access violation` /J\	[reply] [d/l] [select]
Re: perl DBI error by RazorbladeBidet (Friar) on Apr 05, 2005 at 16:13 UTC
Can you run the statement directly in SQL Server? It looks to be more of a DB issue (SQL Server) than DBI, but that's not 100% certain. ~~P.S. please use some formatting in your post - thanks!~~ Update: Post formatted :) I see that you already checked w/ analyzer (I missed that) - ~~but a quick search turned up this - which mentions the use of SQL_INTEGER vs. SQL_VARCHAR hints... might apply to your situation?~~ In other words - try without using placeholders - if that ends up working, the DBD might be trying to bind to the wrong SQL type (integer or varchar) Update II: Wow, this is getting messy... as gellyfish mentions, SQL-42000 is a general-purpose error message. However, I still would try it without the bind params in the SQL. -------------- "But what of all those sweet words you spoke in private?" "Oh that's just what we call pillow talk, baby, that's all."	[reply]
Re: perl DBI error by cog (Parson) on Apr 06, 2005 at 10:29 UTC
The first I'd do would be to check whether the sql statement actually is the same: `warn $sqlarr[2];` And assuming you have two placeholders, include the contents of $reportstartdt and $reportenddt. You probably already tried this, but I thought I should mention it anyway. Get the result and put it into sql server to see if it really runs. If that still doesn't solve the problem... well, try pasting the sql statement here, or even the part of your code that generates that statement. It's a bit hard trying to find a bug in code we can't see :-)	[reply] [d/l]