Temp Table Column name or number of supplied values does not match table definition
-
Hi all,
Starting to play around with PBM and found an example of using DBCC LOGINFO.
When i copy the code, i get the error message
Msg 213, Level 16, State 7, Line 1Column name or number of supplied values does not match table definition.
meaning im using the wrong column type, I found another example of putting dbcc loginfo into a temp table and it had different values for the cols. I tried that and again it failed with the same error.
Im guessings its because im in the UK so using a different collation to the online examples?
Is there a way to find out what col types are for dbcc results? or is it a case of keep trying different values till you get it to work?
-
Can you display the code you are trying to use ?
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITPPlease don't trust me, test the solutions I give you before using them.
-
Hi,
ive tried the following and still getting the same error..
http://sqlfool.com/2010/06/check-vlf-counts/[/url]
Create Table #stage(
FileID int
, FileSize bigint
, StartOffset bigint
, FSeqNo bigint
, [Status] bigint
, Parity bigint
, CreateLSN numeric(38)
);
Create Table #results(
Database_Name sysname
, VLF_count int
);
Exec sp_msforeachdb N'Use ?;
Insert Into #stage
Exec sp_executeSQL N''DBCC LogInfo(?)'';
Insert Into #results
Select DB_Name(), Count(*)
From #stage;
Truncate Table #stage;'
Select *
From #results
Order By VLF_count Desc;
Drop Table #stage;
Drop Table #results;
http://www.mssqltips.com/sqlservertip/1938/monitor-your-sql-server-virtual-log-files-with-policy-based-management/[/url]
create table #tmp (
FileID varchar(3),
FileSize numeric(20,0),
StartOffset bigint,
FSeqNo bigint,
Status char(1),
Parity varchar(4),
CreateLSN numeric(25,0)
)
insert into #tmp
EXEC (''dbcc loginfo'')
select COUNT(*) from #tmp
drop table #tmp
The original code was from a PASS video, but its always the same error that i posted previously.
-
This works fine in SQL 2008:
create table #tmp (FileID varchar(3),
FileSize numeric(20,0),
StartOffset bigint,
FSeqNo bigint,
Status char(1),
Parity varchar(4),
CreateLSN numeric(25,0)
)
insert into #tmp (FileID, FileSize, StartOffset, FSeqNo, Status, Parity, CreateLSN)
EXEC ('dbcc loginfo')
select COUNT(*) from #tmp
drop table #tmp
As does this:
Create Table #stage(FileID int
, FileSize bigint
, StartOffset bigint
, FSeqNo bigint
, [Status] bigint
, Parity bigint
, CreateLSN numeric(38)
);
Create Table #results(
Database_Name sysname
, VLF_count int
);
Exec sp_msforeachdb N'Use ?;
Insert Into #stage (FileID, FileSize, StartOffset, FSeqNo, Status, Parity, CreateLSN)
Exec sp_executeSQL N''DBCC LogInfo(?)'';
Insert Into #results
Select DB_Name(), Count(*)
From #stage;
Truncate Table #stage;'
Select *
From #results
Order By VLF_count Desc;
Drop Table #stage;
Drop Table #results;
The error has nothing to do with the data types you're using. The error says that the number of columns doesn't match, that there are more or fewer columns in the insert list as in the resultset being inserted.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Oh! thanks Gila,
Im on 2012, must have changed then!?
never thought that it might be that...
Thanks!
-
I seem to recall, maybe that DBCC Loginfo gained an additional column in 2012. Check that, run DBCC LogInfo by itself and see how many columns it returns. If there's more than what the temp tables have, you'll need to add more columns to those temp tables.
btw, 2012 questions in the 2012 forums in future please?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
This is correct - additional columns were added for 2012 version.
If you are creating Temp Tables - then they will need to be revisited for the results set to be compared and make sure they agree in column numbers and types.
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITPPlease don't trust me, test the solutions I give you before using them.
-
Create Table #stage(RecoverUnitId int
, FileID int
, FileSize bigint
, StartOffset bigint
, FSeqNo bigint
, [Status] bigint
, Parity bigint
, CreateLSN numeric(38)
);
Create Table #results(
Database_Name sysname
, VLF_count int
);
Exec sp_msforeachdb N'Use [?];
Insert Into #stage
Exec sp_executesql N''DBCC LogInfo([?])'';
Insert Into #results
Select DB_Name(), Count(*)
From #stage;
Truncate Table #stage;'
Select *
From #results
Order By VLF_count Desc;
Drop Table #stage;
Drop Table #results;
I added the required column for 2012.
I also added [] wrappers around the database name.
And I changed sp_executeSQL to sp_executesql which will fail on case sensitive servers.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply