Select returns different results when used in stored procedure

Question

We are starting the upgrade from SQL 2000 to SQL 2008 then SQL2012 we cannot go straight from 2000 to 2012. 

One of the  stored procedures in 2000 sets the value of @ErrorMsg to the name field from the sys.objects view where name =  a given value.  For these purposes here let’s say I am looking for the name “zz_BICWS_PersonInfo_Search_1000000000000000000“which does exist sys.objects view.

The procedure works in 2000 and if I run the following code in 2012 it will return the correct name but does not in the stored procedure in 2012.

declare @ErrorMsg varchar(1000)

set @ErrorMsg = 'xxxxxxx'

select @ErrorMsg = name

       from bic.dbo.sysobjects   

       Where name = 'zz_BICWS_PersonInfo_Search_10000000000000000000'

print 'test ' + @ErrorMsg

These same lines exist in a stored procedure, except the print line, @ErrorMsg is output to the app calling the procedure.  But it returns the initial value “xxxxxx” using a message box in the app to display the returned value.  But if I remvoe the where clause or change the where clause to something like

Where name = 'zz_BICWS_PersonInfo_Search_%

I will get the name of the first record found.

Answer 1

Are you sure the name is exactly what you're using? If you do a select statement with LIKE, can you copy the exact name from the resulting grid and use that name?

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

Yes the name is exactly the way it is in the objects table.  It is normaly referenced by another variable but since I was getting bad results after the upgrade to 2012 I copied a name from the objects view and hard coded it to see what is happening.

After further testing if I add a 1 to the like as in 'zz_BICWS_PersonInfo_Search_1%' it returns the default value of 'xxxxxx' which I put in to see if it was changing.

Below is 10 of 21 names that should be found with the where name like 'zz_BICWS_PersonInfo_Search_1% clause.  Which it does if I run in a script but not in the stored procedure.  This has me stumped should run the same. 

zz_BICWS_PersonInfo_Search_10000000000000000100
zz_BICWS_PersonInfo_Search_10000000000000000000
zz_BICWS_PersonInfo_Search_11000000000000000000
zz_BICWS_PersonInfo_Search_11100000000000000000
zz_BICWS_PersonInfo_Search_10101000000000000000
zz_BICWS_PersonInfo_Search_10111000000000000000
zz_BICWS_PersonInfo_Search_11101000000000000000
zz_BICWS_PersonInfo_Search_11001000000000000000

Answer 3

Strange. What will happen if you declare @ErrorMsg as sysname?

Also, are you using parameter for that name or the direct value in the stored procedure? If parameter, can you show its declaration?

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 4

There is an important difference between SQL 2000 and later versions. In SQL 2000 all metadata was visible to everyone, and there was no way to change it.

In SQL 2005 and later, you can only see metadata if you have permission to. Whence your SELECT statements hits no rows when it is executed by a plain user from your application. But when you run the SELECT in SSMS connected as sa, or at least with db_owner rights, everything works.

If you rely on metadata being visible you need to grant the permission VIEW DEFINITION to the appropriate users.

   GRANT VIEW DEFINITION TO appusers

will grant VIEW DEFINITION on all objects in the database to the role appusers. This statement grants the permission on all objects in the dbo schema:

  GRANT VIEW DEFINITION ON SCHEMA::dbo TO appusers

The permission VIEW DEFINITION is implied on an object if you have SELECT or EXECUTE permission on it.

---

Erland Sommarskog, SQL Server MVP, [e-mail]