error on variable - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

error on variable

Author

Topic

Mikehjun
Starting Member

24 Posts

Posted - 2012-01-13 : 13:52:04

I totally have no clue what to do.

declare @server varchar(100)
declare @dbo varchar(100)
declare @sysjobs varchar(100)
set @server = 'cobgissde.'
set @dbo = 'msdb.dbo.'
set @sysjobs = 'sysjobs'

select *
from @server + @dbo + @sysjobs

I got error message like 'Msg 102, Level 15, State 1, Line 9
Incorrect syntax near '+'.'

it runs fine
select *
from cobgissde.msdb.dbo.sysjobs

or
I tried

declare @db varchar(10)
set @db = 'dbo'

select *
from @db.sysjobs

I got error message like 'Must declare the scalar variable "@db".'

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-13 : 14:03:56

What you are trying is sort of a hybrid dynamic SQL. T-SQL does not like that. You can run it like this:

declare @server varchar(100)
declare @dbo varchar(100)
declare @sysjobs varchar(100)
set @server = 'cobgissde'
set @dbo = 'msdb.dbo.'
set @sysjobs = 'sysjobs'

--select *
--from @server + @dbo + @sysjobs

DECLARE @sql NVARCHAR(4000);
SET @sql = 'select * from ' + @server + @dbo + @sysjobs;

EXEC (@sql);

BUT, don't do that. That is susceptible to SQL injection. Instead, look up sp_execute_sql here: http://msdn.microsoft.com/en-us/library/ms188001.aspx

Mikehjun
Starting Member

24 Posts

Posted - 2012-01-13 : 14:17:27

thanks for the quick response.

What I try to do is to get the list of jobs in many other server.
So I need to set up server name as a variable and loop through all servers and spit out the list of job.

When I set up server name as a variable and place into from statement, I got error like 'Must declare the scalar variable'.

Is there any other way to do this task?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2012-01-13 : 14:44:06

pick a server, create a job with the TSQL that you want executed, set the target server property via sp_add_jobserver. If you need to run this for a list of servers you can kick off the job in a loop..

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources