trouble using a variable in a script

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

trouble using a variable in a script

Author

Topic

rtutus
Aged Yak Warrior

522 Posts

Posted - 2007-01-10 : 14:57:18

Hi I have this script:

use mosaikDB761
exec sp_MSforeachDB '
use

declare @myval char(150)
set @myval=(select collation from syscolumns where id=object_id(''syscomments'') and name=''text'');
select text collate @myval as mytext from syscomments
print @myval;
'But i get an error near @myval like this:

Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '@myval'.

If I run the script without

select text collate @myval from syscomments

I get no error. I get the good results:

SQL_Latin1_General_CP1_CI_AS SQL_Latin1_General_CP1_CI_AS Latin1_General_BIN SQL_Latin1_General_CP1_CI_AS SQL_Latin1_General_CP1_CI_AS
...etc

But If I run the script by replacing @myval with a real value like this:
SQL_Latin1_General_CP1_CI_AS

my script works perfectly. All this means i GUESS I have a problem in the synatx of @myval within my script

Thanks a lot

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-10 : 16:43:03

You've got stuff underlined, stuff in colors, a question mark, and all sorts of other formatting. I can't tell what your actual code is and what is failing. Please correct your post.

Tara Kizer

Kristen
Test

22859 Posts

Posted - 2007-01-10 : 16:58:48

I think you will find it easier to use a CURSOR (or some other type of loop) to walk round the databases - all the doubled-up-quotes and so on, using sp_MSforeachDB, is very hard to debug.

Kristen

rtutus
Aged Yak Warrior

522 Posts

Posted - 2007-01-10 : 17:03:28

Thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources