Query - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Query

Author

Topic

starnold
Yak Posting Veteran

83 Posts

Posted - 2008-10-15 : 11:43:15

I have the below query which is not working? The problem is something to do with the Where clause but I can't work it out?

USE MIREPORTING
TRUNCATE TABLE VarAnalysisXML
GO
declare @table table (id int identity(1,1), dbname varchar(100))
declare @id int
declare @dbname varchar(100)
declare @sql nvarchar(2000)
insert into @table
select name
from sys.databases
-- Exclude Databases
where name not in ('tempdb','master','model','msdb','admin','aspnetforums','anupmove',
'distribution''dunsfiles','jobtracking','linkagewow','mireporting','PI','rep_test_db','testonly')
and name not like 'cascade%'
set @id = (select max(id) from @table)
while @id > 0
begin
set @dbname = (select dbname from @table where id = @id)
set @sql = 'if OBJECT_ID(''[' + @dbname + '].dbo.JobQueue'',''U'') is not null
Insert into MIReporting.dbo.VarAnalysisXML select JobID,UserId,Cast(JobCommand as xml), '''+ @dbname +''' from [' + @dbname + '].dbo.JobQueue
WHERE [' + @dbname + '].dbo.JobType IN ('Count','Venn','CUBE','BROWSE','CHART','DECISIONTREE','EXPORT','PROFILE','TABLE','TREE','VENN','SELECTION','DATAGRID','MAP')'
exec(@sql)
set @id = @id - 1
end

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-15 : 11:52:02

quote:
Originally posted by starnold

I have the below query which is not working? The problem is something to do with the Where clause but I can't work it out?

USE MIREPORTING
TRUNCATE TABLE VarAnalysisXML
GO
declare @table table (id int identity(1,1), dbname varchar(100))
declare @id int
declare @dbname varchar(100)
declare @sql nvarchar(2000)
insert into @table
select name
from sys.databases
-- Exclude Databases
where name not in ('tempdb','master','model','msdb','admin','aspnetforums','anupmove',
'distribution''dunsfiles','jobtracking','linkagewow','mireporting','PI','rep_test_db','testonly')
and name not like 'cascade%'
set @id = (select max(id) from @table)
while @id > 0
begin
set @dbname = (select dbname from @table where id = @id)
set @sql = 'if OBJECT_ID(''[' + @dbname + '].dbo.JobQueue'',''U'') is not null
Insert into MIReporting.dbo.VarAnalysisXML select JobID,UserId,Cast(JobCommand as xml), '''+ @dbname +''' from [' + @dbname + '].dbo.JobQueue
WHERE [' + @dbname + '].dbo.JobType IN (''Count'',''Venn'',''CUBE'',''BROWSE'',''CHART'',''DECISIONTREE'',''EXPORT'',''PROFILE'',''TABLE'',''TREE'',''VENN'',''SELECTION'',''DATAGRID'',''MAP'')'exec(@sql)
set @id = @id - 1
end

modify like above

starnold
Yak Posting Veteran

83 Posts

Posted - 2008-10-15 : 11:53:14

Got the below error?

Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "vtelnel.dbo.JobType" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "vtelnel.dbo.JobType" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "vtelnel.dbo.JobType" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "vtelnel.dbo.JobType" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "vtelnel.dbo.JobType" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "vtelnel.dbo.JobType" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "vtelnel.dbo.JobType" could not be bound.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-15 : 11:53:54

You forgot to put 2x "'"s

FIXED:


declare @table table (id int identity(1,1), dbname varchar(100))
declare @id int
declare @dbname varchar(100)
declare @sql nvarchar(2000)
insert into @table
select name
from sysdatabases
-- Exclude Databases
where name not in ('tempdb','master','model','msdb','admin','aspnetforums','anupmove',
'distribution''dunsfiles','jobtracking','linkagewow','mireporting','PI','rep_test_db','testonly')
and name not like 'cascade%'
set @id = (select max(id) from @table)
while @id > 0
begin
set @dbname = (select dbname from @table where id = @id)
set @sql = 'if OBJECT_ID(''[' + @dbname + '].dbo.JobQueue'',''U'') is not null 
Insert into MIReporting.dbo.VarAnalysisXML select JobID,UserId,Cast(JobCommand as xml), '''+ @dbname +''' from [' + @dbname + '].dbo.JobQueue
WHERE [' + @dbname + '].dbo.JobType IN 
(''Count'',''Venn'',''CUBE'',''BROWSE'',''CHART'',''DECISIONTREE'',''EXPORT'',''PROFILE'',''TABLE'',''TREE'',''VENN'',''SELECTION'',''DATAGRID'',''MAP'')'


exec(@sql)
set @id = @id - 1
end

starnold
Yak Posting Veteran

83 Posts

Posted - 2008-10-15 : 11:56:53

another error

Msg 208, Level 16, State 1, Line 5
Invalid object name 'sysdatabases'.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-15 : 12:02:17

ok change it to

master..sysdatabases

There is a problem with your generated INSERT statement. JobType is a column name. you can use it without

[' + @dbname + '].dbo.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-15 : 12:50:32

better use PRINT (@Sql) to print out SQL string and copy and paste it in another query window and check for any more syntax errors. Thats much easier when you use dynamic sql.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources