SQL 2000 Query Engine Error

[FredHelm]

This thread is in reference to thread 766-842225. "Query Engine Error: '22018:[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the nvarchar value '%' to a column of data type int.'"

Tom (misuser), how did you solve the problem?

A bit of background. I have a report based off of a SQL Server 2000 Stored Procedure. The report is written in CR 9 with SP2 applied. The stored procedure has only varchar parameters and there is no use of the nvarchar data type and there is no implicit conversion to data type int. Upon inspection of the SQL Profiler it appears to me that Crystal is calling other system procedures before calling the stored procedure that the report is based on. The stored procedure which I believe is causing problems is the following call "exec sp_sproc_columns N'ReportAttendanceTracking;1', N'%', NULL, NULL, @ODBCVer = 3" Notice the N'%'. The N tells SQL Server that this parameter is nvarchar data type.

The strange thing is that if I execute this report in a Windows 2003 environment the error does not show itself but in a Windows 2000 environment it does. I am aware that the level of MDAC is different and that might solve the problem. The issue I have with updating the MDAC level is that I have other reports based off of stored procedures and they execute with no problem leading me to ponder that there is an issue with my stored procedure. But why would the same stored procedure work when called from a Win 2003 machine?

Could Tom (misuser) or anyone else shead some light? I am open to suggestions as to how to isolate the problem even if you don't have the complete answer.

Thank you

Fred

 

[vidru]

Have a look at this:

http://support.businessobjects.com/library/kbase/articles/c2014614.asp

Even though your parameter isn't explicitly a NVARCHAR, it's being interpreted as such. I don't have CR 9 here to test, so I can't verify setting the key will actually work or not.

Just curious, what datatypes are your parameters? I'd like to try creating a procedure with the same parameter structure to see what happens with CR XI.

-dave

 

[FredHelm]

Thank you for finding the article. It seems that it might solve the problem. I wanted to keep this post simple that's why I did not want to indicate that I have this error in CR 9, 10, and XI for the identical rpt file. All the parameter data types are varchar. My stored procedure is also using table variables whereas the other reports use temporary table (ie. #TempTable) I have not determined if the table variables are an issue. Here is the SQL Profiler script:

Line1: exec sp_sproc_columns N'ReportAttendanceTracking;1', N'%', NULL, NULL, @ODBCVer = 3
Line2: exec sp_stored_procedures N'ReportAttendanceTracking', N'%', NULL
Line3: SET FMTONLY ON  EXEC "ReportAttendanceTracking";1 '*','QE','*','*','*','*','*','*','20050701','20060630','*','*','*','*','*','*','*','*','*','*','*','*','0'   SET FMTONLY OFF
Line4: exec ReportAttendanceTracking;1 '*', 'QE', '*', '*', '*', '*', '*', '*', '20050701', '20060630', '*', '*', '*', '*', '*', '*', '*', '*', '*', '*', '*', '*', '0'
Line5: exec sp_sproc_columns N'ReportAttendanceTracking;1', N'%', NULL, NULL, @ODBCVer = 3

I am not sure as to why crystal is calling sp_sproc_columns a second time. I hope you are able to reproduce the problem. The SQL Server is version 8.0.0.760 (which should be SP3)

What's even worse is that one of the reports that work correctly has only one line in SQL Profiler and that is the report stored procedure (ie exec ReportClassEnrollmentStatus). There are no calls to sp_sproc_columns, etc. It now seems that the report options is playing a role.

 

[FredHelm]

I solved the problem. It seems that because I used table variables I believe the system procedure sp_sproc_columns could not enumerate the table variable columns because such a table only exists in memory at the time of execution of the stored procedure whereas a temp table physically gets created in tempDB. I converted the table variables to # temporary table (not permanent temp table) and that has resolved my issue.

My explanation might be wrong but my solution works. Thanks Dave for replying and I hope this solution helps others.

Fred

 

[vidru]

Glad you (seemingly) resolved it. I've never used table variables within any of my report procedures (I mainly use them for heavy lifting in other stored procedures within the database).

-dave