I am usin Microsoft sql server 2008 , I tried all the 3 solutions , but every time I get the same error
"Error at Data Flow Task[OLEDB source[449]]:No colum information was returned by the sql command"
I am using the follwoing batch of sql statments to retreive the server level configuaration of all servers in my company. The table variable @tb1_SvrStng has 83 columns and it is populated using diffrenet resources.
So I summarize the sql script. I cannot use it as stored procedure because this script is going to run against 14 servers(once for each server)
So if i store the procedure on one server , other server canot able to execute that procedure in its context.
Please help me to solve my problem. I will highly appretiate your help.
I am not using any temporary tabel in my script.
declare @tb1_SvrStng table
(
srvProp_MachineName varchar(50),
srvProp_BldClrVer varchar(50),
srvProp_Collation varchar(50),
srvProp_CNPNB varchar(100),
...
xpmsver_ProdVer varchar(50),
..... .
syscnfg_UsrCon_cnfgVal int,
.....
);
insert into @tb1_SvrStng
(
srvProp_BldClrVer,
srvProp_Collation,
srvProp_CNPNB , ........
........ .
)
selectconvert(varchar, serverproperty('BuildClrVer')),
convert(varchar, serverproperty('Collation'))
........
.......
declare @temp_msver1 table
(
id int, name varchar(100),
...........
);
insert into @temp_msver1 exec xp_msver
Update@tb1_SvrStng
set xpmsver_ProdVer =
(
select value from @temp_msver1 where name = 'ProductVersion'
),
xpmsver_Platform =
(
select value from @temp_msver1 where name = 'Platform'
),
.....
......
select
srvProp_SerName as srvProp_SerName,
getdate() as reportDateTime,
srvProp_BldClrVer as srvProp_BldClrVer,
srvProp_Collation as srvProp_Collation,
.....
.....
from @tb1_SvrStng
Thanks
Jasdeep