Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Problem with inserting the data into temp table Expand / Collapse
Author
Message
Posted Wednesday, January 9, 2013 2:24 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, October 13, 2014 4:08 AM
Points: 268, Visits: 670
Hi all,

I am trying to stored the output of sp_spaceused into a temp table as follow.

if exists (select 1 from sys.objects where object_id=OBJECT_ID(N'#TEMP'))

create table #temp (name nvarchar(50),rows nvarchar(50),reserved nvarchar(50),data nvarchar(50),index_size nvarchar(50),unused nvarchar(50))
insert into #temp
exec sp_spaceused 'dbo.Emp'

i am getting the error as below
Msg 8114, Level 16, State 1, Procedure sp_spaceused, Line 178
Error converting data type varchar to int.

Now if we observed closely in my temp table there isn't any column with datatype as int.So i am not able to figure it out, why is it trying to convert the column to int when it can stored it nvarchar.
Post #1404610
Posted Wednesday, January 9, 2013 2:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:24 AM
Points: 5,430, Visits: 10,099
The error is occurring in the stored procedure itself. Do you get the same error if you run just the EXEC line?

John
Post #1404616
Posted Wednesday, January 9, 2013 2:44 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, October 13, 2014 4:08 AM
Points: 268, Visits: 670
John Mitchell-245523 (1/9/2013)
The error is occurring in the stored procedure itself. Do you get the same error if you run just the EXEC line?

John


No, it is not giving me the error it is actually giving me the result of sp_spaceused
Post #1404622
Posted Wednesday, January 9, 2013 3:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:24 AM
Points: 5,430, Visits: 10,099
The your first statement should start IF NOT EXISTS, should it not? In any case, please post the results of the following query:
SELECT * FROM tempdb.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME LIKE '#temp%'

John
Post #1404645
Posted Wednesday, January 9, 2013 3:45 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, October 13, 2014 4:08 AM
Points: 268, Visits: 670
John Mitchell-245523 (1/9/2013)
The your first statement should start IF NOT EXISTS, should it not? In any case, please post the results of the following query:
SELECT * FROM tempdb.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME LIKE '#temp%'

John



Ooppss.....My Apologies.
Post #1404654
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse