Using Variable with Four Part Name

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

SQL Server Development (2000)

Using Variable with Four Part Name

Author

Topic

scotthanes
Starting Member

6 Posts

Posted - 2005-11-22 : 09:55:50

Is it possible to use a variable in a four part name within a Join statement.

declare @sqlserver varchar
set @sqlserver='ABCDEFG'

SELECT b.[originating_server],b.[name],a.step_name,a.output_file_name,a.last_run_outcome,a.last_run_date, a.last_run_time
FROM @sqlserver.msdb.dbo.sysjobsteps AS a INNER JOIN @sqlserver.msdb.dbo.sysjobs AS b
ON a.job_id = b.job_id

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-11-22 : 09:58:16

No. unless you go for dynamic sql. sp_executesql

-----------------
[KH]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-22 : 09:59:08

Without Dynamic SQL it is not possible
Why do you want to do this?

Madhivanan

Failing to plan is Planning to fail

scotthanes
Starting Member

6 Posts

Posted - 2005-11-22 : 10:15:01

I want to consolidate the results of all of our SQl jobs into one log file. Used OSQL /L to query all of the SQL Servers and now have them in a temp table. I was going to use this Join statement inside of a cursor.

Thanks for your input...

scotthanes
Starting Member

6 Posts

Posted - 2005-11-22 : 11:22:49

I think I have this setup correctly but I'm still getting the same error.

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '.'.

Any Ideas??

DECLARE @IntVariable NVARCHAR(30)
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)

/* Build the SQL string once.*/
SET @SQLString =
N'SELECT b.[originating_server],b.[name],a.step_name,a.output_file_name,a.last_run_outcome,a.last_run_date, a.last_run_time
FROM @sqlserver.msdb.dbo.sysjobsteps AS a INNER JOIN @sqlserver.msdb.dbo.sysjobs AS b
ON a.job_id = b.job_id'
SET @ParmDefinition = N'@sqlserver NVARCHAR(30)'
/* Execute the string with the first parameter value. */
SET @IntVariable = 'ABCDEFG'
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@sqlserver = @IntVariable

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-11-22 : 23:51:45

you can't pass in the name as a parameter.

you have to format it in your @sqlstring

SET @SQLString =
N'SELECT b.[originating_server],b.[name],a.step_name,a.output_file_name,a.last_run_outcome,a.last_run_date, a.last_run_time FROM ' + @sqlserver + '.msdb.dbo.sysjobsteps AS a INNER JOIN ' + @sqlserver + '.msdb.dbo.sysjobs AS b ON a.job_id = b.job_id'

-----------------
[KH]

scotthanes
Starting Member

6 Posts

Posted - 2005-11-29 : 10:46:12

Khtan....your the man. That works fine. thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources