Dan, The server and all the SP's I am running this on is MSSQL and not Sybase. I have another DSN to a Sybase server and tested this behavior on that one, and reported the results here. I was hoping to find someone who knew the ODBC driver guts well enough to know what the problem was or knew a solution. I personally don't use PRINT in my SQL and before I go ask these other programmers to stop doing what works everywhere else but in DBI::ODBC I was hoping to find the solution. Perhaps this is just broken here and it cannot be fixed. So far everyone acknowledges the problem, but no one knows why it doesn't work or how to fix it. If this is the wrong place to discuss this, then I apologize and will try to find the correct forum. Thanks.
-----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 11:47 PM To: Moosmann, James Cc: '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]' Subject: RE: Invalid cursor state when using PRINT in MSSQL James if you think it's a DBD::ODBC issue, why not use DBD::Sybase instead? Dan "Moosmann, James" <[EMAIL PROTECTED]> 19/10/2004 16:17 To: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]> cc: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]>, (bcc: Dan Horne/IT/AKLWHG/WHNZ) Subject: RE: Invalid cursor state when using PRINT in MSSQL Jeff, It is VERY VALID syntax for both MSSQL ... AND.. Sybase. I have been coding in Perl and DBI for several years now and I am NOT trying to use the perl print function. Here is the skinny on PRINT direct from our DBA for MSSQL Server PRINT : The PRINT statement takes either one character or a Unicode string expression as a parameter. It returns the string as a message to the application. The message is returned as an informational error in ADO, OLE DB, and ODBC applications. SQLSTATE is set to 01000, the native error is set to 0, and the error message string is set to the character string specified in the PRINT statement. The string is returned to the message handler call-back function in DB-Library applications. The example I gave is just minimal (...sigh) to display the error, and I am not sure if you understand the question. I asked this question in Perl Monks and got this informative reply: PRINT '...begin' select * from foo.bar.mytable SQLExecDirect: In: hstmt = 0x00991FB8, szSqlStr = "", cbSqlStr = -3 Return: SQL_SUCCESS_WITH_INFO=1 stmt: szSqlState = "01000", *pfNativeError = 0, *pcbErrorMsg = 55, *ColumnNumber = -1, *RowNumber + = 1 MessageText = "[Microsoft][ODBC SQL Server Driver][SQL Server]...begin" Get Data All: -1 rows affected by INSERT/UPDATE/DELETE or other statement. "col1", "col2", "col3" 1, "row1", "row1" 2, "row2", "row2" 2 rows fetched from 3 columns. MS SQL Server help also mentions that you have to call SQLError right after statement is executed. The timing of calling SQLError is critical when output from PRINT or RAISERROR statements are included in a result set. The call to SQLError to retrieve the PRINT or RAISERROR output must be made immediately after the statement that receives SQL_ERROR or SQL_SUCCESS_WITH_INFO. This is straightforward when only a single SQL statement is executed, as in the examples above. In these cases, the call to SQLExecDirect or SQLExecute returns SQL_ERROR or SQL_SUCCESS_WITH_INFO and SQLError can then be called. It is less straightforward when coding loops to handle the output of a batch of SQL statements or when executing SQL Server stored procedures. *** I have researched this and this looks like a ODBC driver issue. You get the first print and the select if you use Sybase, but you don't get the ending PRINT. We have some VERY long running an complex queries and the PRINTS are embedded in STORED PROCEDURES. I am trying to use the output of the PRINTS to give feedback to the user when the queries will finish. -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, October 18, 2004 9:08 PM To: Moosmann, James Cc: '[EMAIL PROTECTED]' Subject: Re: Invalid cursor state when using PRINT in MSSQL my $sql = qq# PRINT 'starting select' select count(*) from anytable PRINT 'finished' #; is not valid sql. try this: my $sql = qq# select count(*) from anytable #; my $sth = $dbh->prepare( $sql ); print 'starting select'; my $rv = $sth->execute(); while ( my $hr = $sth->fetchrow_hashref ){ print $$hr{$_}, "\n" for ( keys %$hr ); print "Err: ", $dbh->errstr, "\n" if $dbh->errstr; } print 'finished'; _________________________ Jeff Seger Fairchild Semiconductor [EMAIL PROTECTED] ____________________________ "Moosmann, James" <[EMAIL PROTECTED]> 10/18/2004 06:46 PM To: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]> cc: Subject: Invalid cursor state when using PRINT in MSSQL I am using DBI and DBD-ODBC to connect to an MSSQL Server and this query gets an invalid cursor state: PRINT 'starting select' select count(*) from anytable PRINT 'finished' If I only run the PRINT command... it works. If I run the full query against a sybase server... only the top select prints. I am using: Win32 AS-5.8.0 Multithread build 806 DBI(1.43)DBD::ODBC(1.07) MSSQL Server I get a invalid cursor state when I run any SQL with a PRINT statement at the beginning of any SQL statement. The error: DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver]Invalid cursor state (SQL-24000)(DB D: dbd_describe/SQLNumResultCols err=-1) at H:\progs\dbi_test.pl line 10. Example code: #c:/perl/bin/perl use DBI; my $dbh = DBI->connect( 'dbi:ODBC:TEST_DSN', '','', {RaiseError=> 1} ); my $sql = qq# PRINT 'starting select' select count(*) from anytable PRINT 'finished' #; my $sth = $dbh->prepare( $sql ); my $rv = $sth->execute(); while ( my $hr = $sth->fetchrow_hashref ){ print $$hr{$_}, "\n" for ( keys %$hr ); print "Err: ", $dbh->errstr, "\n" if $dbh->errstr; } Does this have anything to do with how SQL_SUCCESS_WITH_INFO is being handled by the driver? How do I get all the results.. Thanks! James **************************************************************************** **** NOTICE This email and any attachments are confidential. They may contain privileged information or copyright material. If you are not an intended recipient, you should not read, copy, use or disclose the contents without authorisation as we request you contact us as once by return email. Please then delete the email and any attachments from your system. We do not accept liability in connection with computer viruses, data corruption, delay, interruption, unauthorised access or unauthorised amendment. Any views expressed in this email and any attachments do not necessarily reflect the views of the company. **************************************************************************** ****
