Must declare the scalar variable

Last Post 20 Feb 2008 10:01 AM by SQLUSA. 7 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
leohalim
New Member
New Member

--
15 Feb 2008 09:12 AM
I'm scratching my head with this problem.

I have two identical databases installed on two environments with similar set ups (Enterprise edition, 8 processors, etc). The only differences are: One is in clustered and the other is not. The clustered one has a SQL 2005 SP2 with CU2 (ver 9.00.3175). The non clustered one has a SP2 only (ver 9.00.3043.00)

Here's the problem:
When I run this simple statement: select top 1 * from vwReview on the non-clustered SQL 2005, the statement returns result.
However, when I run it on the clustered environment, I get "Must declare the scalar variable" error message.

Would the cluster environment cause that error message?

Leo
leohalim
New Member
New Member

--
15 Feb 2008 01:18 PM
There is a UDF on that view.

However, when I run a stored procedure which has no UDF, I get the same error message.

Thanks for replying.

Leo
leohalim
New Member
New Member

--
19 Feb 2008 02:17 PM
Yes, I can give you the code.

However, my question is why in a different server the code either the view or the stored procedure would run without any problem.

Leo
SQLUSA
New Member
New Member

--
20 Feb 2008 10:01 AM
What is Microsoft saying?

Appears to be a bug.

Can you do one final test: drop the views and recreate them. Test.


Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/order2005grandslam/ The Best SQL Server 2005 Training in the World!
najmh
New Member
New Member

--
21 Feb 2008 09:36 AM
Leo,
Do a comparison of your UDF, SPS of both environments. It looks like there could be some typo.
It is very plausible that data in your clustered environment forcing a different path where a variable is not properly declared i.e. @ is missing or something like that. It had happened to me. A proc failed in production while it worked in QA.

You can easily download a comparison trail program from different vendors.

All the best.
leohalim
New Member
New Member

--
27 Feb 2008 09:23 AM
Thanks guys for the replies.

I upgraded the Hot Fixes on the non-clustered server to the same level as the clustered one so both clustered and non-clustered environment have the same level of SP.

The code still runs fine in the non-clustered environment.

I'm working with our programmer to have a simple code written that can basically pinpoint the cause of the problem.

Leo
SQLUSA
New Member
New Member

--
28 Feb 2008 12:02 AM
Can you post the code?

Anything esoteric in it?


Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/sql-server-2008-training/ The Best SQL Server 2008 Training in the World!
leohalim
New Member
New Member

--
12 Mar 2008 10:22 AM
OK. The problem is solved.

The problem was the cluster/problem server had a server collation of Latin1_General_Bin while the non-problem server has a server collation of SQL_Latin1_General_CP1_Cl_AS.

Once the server collation was changed to SQL_Latin1_General_CP1_Cl_AS, the code ran with no problem.

BTW, here is the simple code that the programmer wrote to nail down the cause of the problem:
declare @MyID int
set @myID=123
(notice how MyID on the first line is spelled differently than the myID on the second line)

The above code would result in the "Must Declare the scalar variable" error message on the Latin1_General_Bin server collation setting, but not on the SQL_Latin1_General_CP1_Cl_AS server collation setting.

Anyway, thanks for all your interest and time in responding to my post.

Leo
You are not authorized to post a reply.

Acceptable Use Policy