March 23, 2007 at 2:55 am
Hi All
I did manage to sort out the SET IDENTITY INSERT ON problem, thanks for all your input. I now, however have another prob.
I have the following query:
DECLARE
@count1 int
DECLARE
@count2 int
DECLARE
@ServerName varchar(300)
DECLARE
@Query1 nvarchar(2000)
DECLARE
@Query2 nvarchar(2000)
DECLARE
@Query3 nvarchar(2000)
set
@count1 =0
set
@count2 =(Select max(ServerID) from BEPSQL01.DBA_Reports.dbo.Server_Lookup where Status = 'Active')
While
@count1 <= @count2
begin
set
@count1=@count1 +1
set
@ServerName =(Select distinct ServerName,AppName from BEPSQL01.DBA_Reports.dbo.Server_Lookup where Status = 'Active' AND serverID = +@count1)
--SET IDENTITY_INSERT SQL_Database_Logins ON
set
@Query2 = 'insert into dbo.SQL_Database_Logins
(ServerName,Version,DataBaseName,LoginName,CreateDate,UpdateDate,Access,Role,RunDate)'
set
@Query3 = @Query2 + ' SELECT * FROM OPENQUERY ('+@ServerName +','+''' SET FMTONLY OFF exec master.dbo.sp__DatabaseLogins'')'
@Query3
exec
(@Query3)
end
It gives me the following error:
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Any help please!!
Anchelin
March 23, 2007 at 4:31 am
Anchelin
I imagine the error is in this line:
set @ServerName =(Select distinct ServerName,AppName from BEPSQL01.DBA_Reports.dbo.Server_Lookup where Status = 'Active' AND serverID = +@count1)
Have a look what it's trying to do - you are attempting to set the value of one variable, and yet you have two columns in the select list. Also, does this select statement return only one row?
John
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply