November 30, 2013 at 12:19 am
Hello
I have this table
declare @UserMidListTable Table (Id Int,Mid int,ValueMid int,CatParent int, [Enabled] int,LastUpdate Datetime,Company nvarchar(max))
I want to fill it with the execution of this stored procedure
insert into @UserMidListTable (Id ,Mid ,ValueMid ,CatParent , [Enabled] ,LastUpdate ,Company)
exec UserMidList @userRkey,'20','0','0','10','1','2','',''
the execution of stored procedure is bellow
idMidValueMidCatParentEnabledLastUpdatecompany
120100001NULL25NULLNULL
220100007NULL25NULLNULL
320100030NULL25NULLNULL
420100042NULL25NULLNULL
520100043NULL25NULLNULL
BUT I see this error
Column name or number of supplied values does not match table definition.
I don't know what is wrong.
November 30, 2013 at 12:43 am
SQL Server Version?
November 30, 2013 at 12:50 am
sql server 2008 R2
November 30, 2013 at 12:56 am
Is your server case sensitive by any chance? Also, what is the compatibility level for the database set to?
--Jeff Moden
Change is inevitable... Change for the better is not.
November 30, 2013 at 1:04 am
Are the datatypes of the output columns from stored procedure same as the Table Variable?
November 30, 2013 at 1:17 am
Yes they are same
November 30, 2013 at 7:31 am
can you please provide definition of your sql procedure
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
December 2, 2013 at 1:16 pm
I ran following on sql server 2008 r2 with no errors . you prbly need to check your sproc definition.
if exists(select * from sys.objects (nolock) where name = 'UserMidList' and type = 'P')
begin
exec('drop proc UserMidList');
end
go
create proc UserMidList as
select 1,20,100001,NULL,25,NULL,NULL
union
select 2,20,100007,NULL,25,NULL,NULL
union
select 3,20,100030,NULL,25,NULL,NULL
union
select 4,20, 100042,NULL,25,NULL,NULL
union
select 5,20,100043,NULL,25,NULL,NULL
go
declare @UserMidListTable Table (Id Int,Mid int,ValueMid int,CatParent int, [Enabled] int,LastUpdate Datetime,Company nvarchar(max))
insert into @UserMidListTable (Id ,Mid ,ValueMid ,CatParent , [Enabled] ,LastUpdate ,Company)
exec UserMidList
select * from @UserMidListTable
go
select @@version
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
--Jun 17 2011 00:54:03
--Copyright (c) Microsoft Corporation
--Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
December 3, 2013 at 12:51 pm
You could use OPENROWSET to create a temporary table (example and link to example web page below) and check that the stored procedure definition matches your table variable definition.
SELECT * into #Temp2
FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off exec master.dbo.sp_who2')
December 4, 2013 at 4:12 pm
The problem with using OPENROWSET is that it requires "SA" privs. That's ok for jobs but should not be ok for application logins.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 4, 2013 at 4:13 pm
bkshn (11/30/2013)
Yes they are same
As I asked before, have you checked the compatability level of the database?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply