Insert Into Failing. - 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 2000 Forums

SQL Server Development (2000)

Insert Into Failing.

Author

Topic

Anh
Starting Member

3 Posts

Posted - 2008-01-07 : 09:20:12

Hi everybody!!

I am having issues with an insert statement... This is the script:

CREATE TABLE tblResults
(
d_name nvarchar(128) NULL,
d_size varchar(18) NULL,
d_unspace varchar(18) NULL,
d_reserved varchar(18) NULL,
d_data varchar(18) NULL,
d_indexsize varchar(18) NULL,
d_unused varchar(18) NULL
)
INSERT INTO tblResults
(d_name, d_size, d_unspace, d_reserved, d_data, d_indexsize, d_unused)
EXEC sp_spaceused

The issues goes from : Invalid Column name to Column name or number of supplied values does not match table definition..

Can you please help me find the problem?

Many Thanks,
Anh.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-07 : 09:26:11

Can you run this seperately and check that it retrieves the right number of values and that datatypes of all values are as per table definition?

EXEC sp_spaceused

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-01-07 : 09:41:14

This is most probably happining due to the multiple resultsets returned by sp_spaceused. You can get hold of first resultset values using:

Select * into dbo.tblresults
from OPENROWSET('SQLOLEDB','Data Source=MyServer;Trusted_Connection=yes;Integrated Security=SSPI', 'set fmtonly off;EXEC Database..SP_SPACEUSED')

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Anh
Starting Member

3 Posts

Posted - 2008-01-07 : 10:29:55

On This page: http://technet.microsoft.com/en-us/library/ms188776.aspx
database_name is nvarchar(128), but on Books Online is varchar (18), anyway I have test the two options without good results.

A simple insert like this, works perfect:
INSERT INTO tblResults
(d_name, d_size, d_unspace, d_reserved, d_data, d_indexsize, d_unused)
values
('','','','','','','')

exex sp_spaceused returns two results set, it is working for me when I run it for different databases, but all the script with the insert, continues not working.

I don't know what else to test :(

Anh
Starting Member

3 Posts

Posted - 2008-01-07 : 10:39:06

Great !!!!!!! Thanks!!!!!!!
The first result set has been populated into the table!!!

Is there any way to open the second result set ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources