December 21, 2006 at 12:36 am
Hello everyone
I have the following error:
The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns
Should I select the column names? I have the script below:
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 dbo.Server_Lookup where Status = 'Active')
While
@count1 <= @count2
begin
set
@count1=@count1 +1
set
@ServerName =(Select distinct ServerName from 'Servername' where Status = 'Active' AND serverID = +@count1)
set
@Query2 = 'insert into 'tablename'
([col],[col],[col],[col],[col],[col],[col],[col])'
set
@Query3 = @Query2 + ' SELECT * FROM OPENQUERY ('+@ServerName +','+''' SET FMTONLY OFF exec master.Stored_proc'')'
exec
(@Query3)
end
GO
Any help would be welcome!
Thanks
Anchelin
I THEN select the column names
set
@Query3 = @Query2 + ' SELECT [ServerName],[DatabaseName],[BackupStartDate],[BackupEndDate],[BackupSize],[BackupSizeMB],[BackupSizeGB],[BackupStatus]
FROM OPENQUERY ('
+@ServerName +','+''' SET FMTONLY OFF exec master.dbo.sp__DBBackupInfo'')'
exec
(@Query3)
and get the following error:
Invalid column name 'col''.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'col''.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'col''.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'col''.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'col'.
Am I missing something?
Anchelin
December 21, 2006 at 1:48 am
Anchelin
You need to substitute the actual names of the columns in tablename (why do you have that enclosed in quotes in your script?) otherwise it will look for columns named col.
John
December 21, 2006 at 1:52 am
I did... ok here is the script:
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 dbo.Server_Lookup where Status = 'Active')
While @count1 <= @count2
begin
set @count1=@count1 +1
set @ServerName =(Select distinct ServerName from dbo.Server_Lookup where Status = 'Active' AND serverID = +@count1)
set @Query2 = 'insert into dbo.DBBackUpInfo
([ServerName],[DatabaseName],[BackupStartDate],[BackupEndDate],[BackupSize],[BackupSizeMB],[BackupSizeGB],[BackupStatus])'
set @Query3 = @Query2 + ' SELECT * FROM OPENQUERY ('+@ServerName +','+''' SET FMTONLY OFF exec master.dbo.sp__DBBackupInfo'')'
exec (@Query3)
end
GO
I then go ahead and did the following:
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 dbo.Server_Lookup where Status = 'Active')
While
@count1 <= @count2
begin
set
@count1=@count1 +1
set
@ServerName =(Select distinct ServerName from dbo.Server_Lookup where Status = 'Active' AND serverID = +@count1)
set
@Query2 = 'insert into dbo.DBBackUpInfo
([ServerName],[DatabaseName],[BackupStartDate],[BackupEndDate],[BackupSize],[BackupSizeMB],[BackupSizeGB],[BackupStatus])
values (''BEPTDR01'',''billing_services_datavoir_back'',''Dec 20 2006 9:02PM'',''Dec 20 2006 9:02PM'',''6374912'',''6.08'',''0.01'',''Backup Current Within A Day'') '
set
@Query3 = @Query2 + ' SELECT [ServerName],[DatabaseName],[BackupStartDate],[BackupEndDate],[BackupSize],[BackupSizeMB],[BackupSizeGB],[BackupStatus]
FROM OPENQUERY ('
+@ServerName +','+''' SET FMTONLY OFF exec master.dbo.sp__DBBackupInfo'')'
exec
(@Query3)
end
GO
it returns the following error:
Msg 207, Level 16, State 1, Line 3
Invalid column name 'BackupStartDate'.
Msg 207, Level 16, State 1, Line 3
Invalid column name 'BackupEndDate'.
Msg 207, Level 16, State 1, Line 3
Invalid column name 'BackupSize'.
Msg 207, Level 16, State 1, Line 3
Invalid column name 'BackupSizeMB'.
Msg 207, Level 16, State 1, Line 3
Invalid column name 'BackupSizeGB'
This I DONT understand because the the columns I specify is the same as in my table
December 21, 2006 at 2:27 am
Anchelin
Perhaps you ought to include a "USE DBName" statement at the beginning of @Query2. If that doesn't work, please post the result of this query:
USE DBName
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'DBBackUpInfo'
John
December 21, 2006 at 3:10 am
Hi John, THANKS for sticking with me!!
result:
(24 row(s) affected)
(10 row(s) affected)
(10 row(s) affected)
(5 row(s) affected)
Msg 120, Level 15, State 1, Line 3
The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.
(11 row(s) affected)
(14 row(s) affected)
(11 row(s) affected)
(11 row(s) affected)
(10 row(s) affected)
(15 row(s) affected)
(13 row(s) affected)
(6 row(s) affected)
(8 row(s) affected)
(8 row(s) affected)
(10 row(s) affected)
(7 row(s) affected)
(5 row(s) affected)
Msg 120, Level 15, State 1, Line 3
The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.
(7 row(s) affected)
(4 row(s) affected)
(5 row(s) affected)
(5 row(s) affected)
(17 row(s) affected)
(14 row(s) affected)
(3 row(s) affected)
(17 row(s) affected)
(8 row(s) affected)
(5 row(s) affected)
(11 row(s) affected)
MY SCRIPT
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 dbo.Server_Lookup where Status = 'Active')
While
@count1 <= @count2
begin
set
@count1=@count1 +1
set
@SERVERNAME =(Select distinct ServerName from dbo.Server_Lookup where Status = 'Active' AND serverID = +@count1)
set
@Query2 = 'use DBA_Reports
insert into dbo.DBBackUpInfo
([ServerName],[DatabaseName],[BackupStartDate],[BackupEndDate],[BackupSize],[BackupSizeMB],[BackupSizeGB],[BackupStatus])'
set
@Query3 = @Query2 + ' SELECT * FROM OPENQUERY ('+@SERVERNAME +','+''' SET FMTONLY OFF exec master.dbo.sp__DBBackupInfo'')'
exec
(@Query3)
end
GO
THANKS!!
A
December 21, 2006 at 3:32 am
Anchelin
How many columns are in the result set when you run sp__DBBackupInfo on the remote server?
John
December 21, 2006 at 3:54 am
Hi
It returns 8 columns, the same amount as in my table DBBackupInfo.
I have created the stored proc on all the servers.
Anchelin
December 21, 2006 at 4:04 am
Anchelin
OK. Can you please put a PRINT @Query3 statement just before the EXEC command and post the result of that.
Thanks
John
December 21, 2006 at 5:44 am
Hi
See below
use DBA_Reports
insert into dbo.DBBackUpInfo
([ServerName],[DatabaseName],[BackupStartDate],[BackupEndDate],[BackupSize],[BackupSizeMB],[BackupSizeGB],[BackupStatus]) SELECT * FROM OPENQUERY (BEPTDR01,' SET FMTONLY OFF exec master.dbo.sp__DBBackupInfo')
(24 row(s) affected)
use DBA_Reports
insert into dbo.DBBackUpInfo
([ServerName],[DatabaseName],[BackupStartDate],[BackupEndDate],[BackupSize],[BackupSizeMB],[BackupSizeGB],[BackupStatus]) SELECT * FROM OPENQUERY (BEPCBT01,' SET FMTONLY OFF exec master.dbo.sp__DBBackupInfo')
(10 row(s) affected)
use DBA_Reports
insert into dbo.DBBackUpInfo
([ServerName],[DatabaseName],[BackupStartDate],[BackupEndDate],[BackupSize],[BackupSizeMB],[BackupSizeGB],[BackupStatus]) SELECT * FROM OPENQUERY (BEPCBT02,' SET FMTONLY OFF exec master.dbo.sp__DBBackupInfo')
(10 row(s) affected)
use DBA_Reports
insert into dbo.DBBackUpInfo
([ServerName],[DatabaseName],[BackupStartDate],[BackupEndDate],[BackupSize],[BackupSizeMB],[BackupSizeGB],[BackupStatus]) SELECT * FROM OPENQUERY (CTTIRS02,' SET FMTONLY OFF exec master.dbo.sp__DBBackupInfo')
(5 row(s) affected)
use DBA_Reports
insert into dbo.DBBackUpInfo
([ServerName],[DatabaseName],[BackupStartDate],[BackupEndDate],[BackupSize],[BackupSizeMB],[BackupSizeGB],[BackupStatus]) SELECT * FROM OPENQUERY (BEPIRSSQL,' SET FMTONLY OFF exec master.dbo.sp__DBBackupInfo')
Msg 120, Level 15, State 1, Line 3
The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.
use DBA_Reports
insert into dbo.DBBackUpInfo
([ServerName],[DatabaseName],[BackupStartDate],[BackupEndDate],[BackupSize],[BackupSizeMB],[BackupSizeGB],[BackupStatus]) SELECT * FROM OPENQUERY (BEDTNG01,' SET FMTONLY OFF exec master.dbo.sp__DBBackupInfo')
(11 row(s) affected)
use DBA_Reports
insert into dbo.DBBackUpInfo
([ServerName],[DatabaseName],[BackupStartDate],[BackupEndDate],[BackupSize],[BackupSizeMB],[BackupSizeGB],[BackupStatus]) SELECT * FROM OPENQUERY (BEPTNG01,' SET FMTONLY OFF exec master.dbo.sp__DBBackupInfo')
(14 row(s) affected)
use DBA_Reports
insert into dbo.DBBackUpInfo
([ServerName],[DatabaseName],[BackupStartDate],[BackupEndDate],[BackupSize],[BackupSizeMB],[BackupSizeGB],[BackupStatus]) SELECT * FROM OPENQUERY (CTPTNG01,' SET FMTONLY OFF exec master.dbo.sp__DBBackupInfo')
(11 row(s) affected)
use DBA_Reports
insert into dbo.DBBackUpInfo
([ServerName],[DatabaseName],[BackupStartDate],[BackupEndDate],[BackupSize],[BackupSizeMB],[BackupSizeGB],[BackupStatus]) SELECT * FROM OPENQUERY (CTTMFT01,' SET FMTONLY OFF exec master.dbo.sp__DBBackupInfo')
(11 row(s) affected)
use DBA_Reports
insert into dbo.DBBackUpInfo
([ServerName],[DatabaseName],[BackupStartDate],[BackupEndDate],[BackupSize],[BackupSizeMB],[BackupSizeGB],[BackupStatus]) SELECT * FROM OPENQUERY ([BETSQL01\SQL2005],' SET FMTONLY OFF exec master.dbo.sp__DBBackupInfo')
(10 row(s) affected)
use DBA_Reports
insert into dbo.DBBackUpInfo
([ServerName],[DatabaseName],[BackupStartDate],[BackupEndDate],[BackupSize],[BackupSizeMB],[BackupSizeGB],[BackupStatus]) SELECT * FROM OPENQUERY (UMSSERVER02,' SET FMTONLY OFF exec master.dbo.sp__DBBackupInfo')
(15 row(s) affected)
use DBA_Reports
insert into dbo.DBBackUpInfo
([ServerName],[DatabaseName],[BackupStartDate],[BackupEndDate],[BackupSize],[BackupSizeMB],[BackupSizeGB],[BackupStatus]) SELECT * FROM OPENQUERY (CTPUMS01,' SET FMTONLY OFF exec master.dbo.sp__DBBackupInfo')
(13 row(s) affected)
use DBA_Reports
insert into dbo.DBBackUpInfo
([ServerName],[DatabaseName],[BackupStartDate],[BackupEndDate],[BackupSize],[BackupSizeMB],[BackupSizeGB],[BackupStatus]) SELECT * FROM OPENQUERY (BEPUMS01,' SET FMTONLY OFF exec master.dbo.sp__DBBackupInfo')
(6 row(s) affected)
use DBA_Reports
insert into dbo.DBBackUpInfo
([ServerName],[DatabaseName],[BackupStartDate],[BackupEndDate],[BackupSize],[BackupSizeMB],[BackupSizeGB],[BackupStatus]) SELECT * FROM OPENQUERY (BEPKBS01,' SET FMTONLY OFF exec master.dbo.sp__DBBackupInfo')
(8 row(s) affected)
use DBA_Reports
insert into dbo.DBBackUpInfo
([ServerName],[DatabaseName],[BackupStartDate],[BackupEndDate],[BackupSize],[BackupSizeMB],[BackupSizeGB],[BackupStatus]) SELECT * FROM OPENQUERY (BEPKBSSQL,' SET FMTONLY OFF exec master.dbo.sp__DBBackupInfo')
(8 row(s) affected)
use DBA_Reports
insert into dbo.DBBackUpInfo
([ServerName],[DatabaseName],[BackupStartDate],[BackupEndDate],[BackupSize],[BackupSizeMB],[BackupSizeGB],[BackupStatus]) SELECT * FROM OPENQUERY (BEDRPT01,' SET FMTONLY OFF exec master.dbo.sp__DBBackupInfo')
(10 row(s) affected)
use DBA_Reports
insert into dbo.DBBackUpInfo
([ServerName],[DatabaseName],[BackupStartDate],[BackupEndDate],[BackupSize],[BackupSizeMB],[BackupSizeGB],[BackupStatus]) SELECT * FROM OPENQUERY (BETRPT01,' SET FMTONLY OFF exec master.dbo.sp__DBBackupInfo')
(7 row(s) affected)
use DBA_Reports
insert into dbo.DBBackUpInfo
([ServerName],[DatabaseName],[BackupStartDate],[BackupEndDate],[BackupSize],[BackupSizeMB],[BackupSizeGB],[BackupStatus]) SELECT * FROM OPENQUERY (CTPCIM01,' SET FMTONLY OFF exec master.dbo.sp__DBBackupInfo')
(5 row(s) affected)
use DBA_Reports
insert into dbo.DBBackUpInfo
([ServerName],[DatabaseName],[BackupStartDate],[BackupEndDate],[BackupSize],[BackupSizeMB],[BackupSizeGB],[BackupStatus]) SELECT * FROM OPENQUERY (BEDCIM01,' SET FMTONLY OFF exec master.dbo.sp__DBBackupInfo')
Msg 120, Level 15, State 1, Line 3
The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.
use DBA_Reports
insert into dbo.DBBackUpInfo
([ServerName],[DatabaseName],[BackupStartDate],[BackupEndDate],[BackupSize],[BackupSizeMB],[BackupSizeGB],[BackupStatus]) SELECT * FROM OPENQUERY (BEPCIM01,' SET FMTONLY OFF exec master.dbo.sp__DBBackupInfo')
(7 row(s) affected)
use DBA_Reports
insert into dbo.DBBackUpInfo
([ServerName],[DatabaseName],[BackupStartDate],[BackupEndDate],[BackupSize],[BackupSizeMB],[BackupSizeGB],[BackupStatus]) SELECT * FROM OPENQUERY (BEPARE01,' SET FMTONLY OFF exec master.dbo.sp__DBBackupInfo')
(4 row(s) affected)
use DBA_Reports
insert into dbo.DBBackUpInfo
([ServerName],[DatabaseName],[BackupStartDate],[BackupEndDate],[BackupSize],[BackupSizeMB],[BackupSizeGB],[BackupStatus]) SELECT * FROM OPENQUERY (BEDARE01,' SET FMTONLY OFF exec master.dbo.sp__DBBackupInfo')
(5 row(s) affected)
use DBA_Reports
insert into dbo.DBBackUpInfo
([ServerName],[DatabaseName],[BackupStartDate],[BackupEndDate],[BackupSize],[BackupSizeMB],[BackupSizeGB],[BackupStatus]) SELECT * FROM OPENQUERY (BETARE01,' SET FMTONLY OFF exec master.dbo.sp__DBBackupInfo')
(5 row(s) affected)
use DBA_Reports
insert into dbo.DBBackUpInfo
([ServerName],[DatabaseName],[BackupStartDate],[BackupEndDate],[BackupSize],[BackupSizeMB],[BackupSizeGB],[BackupStatus]) SELECT * FROM OPENQUERY (BEDWFM01,' SET FMTONLY OFF exec master.dbo.sp__DBBackupInfo')
(17 row(s) affected)
use DBA_Reports
insert into dbo.DBBackUpInfo
([ServerName],[DatabaseName],[BackupStartDate],[BackupEndDate],[BackupSize],[BackupSizeMB],[BackupSizeGB],[BackupStatus]) SELECT * FROM OPENQUERY (BETWFM01,' SET FMTONLY OFF exec master.dbo.sp__DBBackupInfo')
(14 row(s) affected)
use DBA_Reports
insert into dbo.DBBackUpInfo
([ServerName],[DatabaseName],[BackupStartDate],[BackupEndDate],[BackupSize],[BackupSizeMB],[BackupSizeGB],[BackupStatus]) SELECT * FROM OPENQUERY (BEPWFM01,' SET FMTONLY OFF exec master.dbo.sp__DBBackupInfo')
(3 row(s) affected)
use DBA_Reports
insert into dbo.DBBackUpInfo
([ServerName],[DatabaseName],[BackupStartDate],[BackupEndDate],[BackupSize],[BackupSizeMB],[BackupSizeGB],[BackupStatus]) SELECT * FROM OPENQUERY (BEPWFM02,' SET FMTONLY OFF exec master.dbo.sp__DBBackupInfo')
(17 row(s) affected)
use DBA_Reports
insert into dbo.DBBackUpInfo
([ServerName],[DatabaseName],[BackupStartDate],[BackupEndDate],[BackupSize],[BackupSizeMB],[BackupSizeGB],[BackupStatus]) SELECT * FROM OPENQUERY ([10.122.122.39],' SET FMTONLY OFF exec master.dbo.sp__DBBackupInfo')
(8 row(s) affected)
use DBA_Reports
insert into dbo.DBBackUpInfo
([ServerName],[DatabaseName],[BackupStartDate],[BackupEndDate],[BackupSize],[BackupSizeMB],[BackupSizeGB],[BackupStatus]) SELECT * FROM OPENQUERY (BEPBTS01,' SET FMTONLY OFF exec master.dbo.sp__DBBackupInfo')
(5 row(s) affected)
use DBA_Reports
insert into dbo.DBBackUpInfo
([ServerName],[DatabaseName],[BackupStartDate],[BackupEndDate],[BackupSize],[BackupSizeMB],[BackupSizeGB],[BackupStatus]) SELECT * FROM OPENQUERY (RPTSQL,' SET FMTONLY OFF exec master.dbo.sp__DBBackupInfo')
(11 row(s) affected)
Again... THANK YOU
Anche
December 21, 2006 at 6:06 am
Hi John
It's working now, THANK YOU so much!! The PRINT statement showed me where the error was. THANKS!!!
The columns of the sp on those two servers, didnt match columns of table!!!
THANK YOU!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Anchelin
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply