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
id Mid ValueMid CatParent Enabled LastUpdate company
1 20 100001 NULL 25 NULL NULL
2 20 100007 NULL 25 NULL NULL
3 20 100030 NULL 25 NULL NULL
4 20 100042 NULL 25 NULL NULL
5 20 100043 NULL 25 NULL NULL
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
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
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply