July 7, 2011 at 11:07 am
Hi All,
I am getting the following error when run the below script.
"Insert Error: Column name or number of supplied values does not match table definition."
DECLARE name_cur CURSOR FOR SELECT DISTINCT db_name, obj_name from table1
OPEN name_cur
CREATE TABLE [dbo].[Table_MetaData](
[name] [nvarchar](50)
[rows] [int]
[reserved] [varchar](50)
[data] [varchar](50)
[index_sze] [varchar](50)
[unused] [varchar](50)
)
FETCH name_cur INTO @db_name, @table_name
WHILE @@Fetch_Status = 0
BEGIN
SET @sql = @db_name+'..sp_spaceused '+ '"'+@table_name+'"'
insert into [dbo].[Table_MetaData]
EXEC (@sql)
FETCH name_cur INTO @db_name, @table_name
END
Thank you!
July 7, 2011 at 11:31 am
what you pasted was missing a lot...the table1 definition, three variables and more.
i think your insert needs to be inside the @sql variable.
SET @sql = ' insert into [master].[dbo].[Table_MetaData]([name],[rows],[reserved],[data],[index_sze],[unused]) '+ @db_name+'..sp_spaceused '+ '"'+@table_name+'"'
EXEC (@sql)
Lowell
July 7, 2011 at 12:33 pm
I have declared variables, just missed in the post. Table1 has two columns, Database name & Object name.
I tried putting insert statement in @sql variable. But that gives an error "Incorrect syntax near 'tempDB'"
tempDB is the database name.
July 7, 2011 at 12:38 pm
ahh i see it:
you need EXEC in there: can just have the proc name.,
...unused]) EXECUTE '+ @db_name+'..sp_spaceused ...
Lowell
July 7, 2011 at 12:42 pm
Here is an alternative to that as well.
http://jasonbrimhall.info/2010/05/25/space-used/
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 7, 2011 at 1:05 pm
anjaliv (7/7/2011)
@Lowell: Thank you!! It worked 🙂I am trying the same for sp_depends. Do you think following would work?
SET @sql = 'insert into [dbo].[Table_Dependency]([nam],[type]) EXECUTE USE '+@db_name+' EXEC sp_depends '+@table_name
EXEC (@sql)
Yes
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 7, 2011 at 1:30 pm
It says " Incorrect syntax near the keyword 'USE'."
July 7, 2011 at 1:39 pm
Change to this:
SET @sql = 'USE '+@db_name+'go; insert into [youradmindbname].[dbo].[Table_Dependency]([nam],[type]) EXEC sp_depends '+@table_name
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 7, 2011 at 1:40 pm
do a PRINT @sql before you EXEC(@sql); that gives you the statement so you can run it independantly;
look at the costruction in the middle where you inserted EXECUTE:
...EXECUTE USE ... that's not valid.
Lowell
July 7, 2011 at 1:54 pm
Still gives an error "Incorrect syntax near 'go'"
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply