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

Coping with No Column Names in the SSIS OLEDB Data Source Editor Expand / Collapse
Author
Message
Posted Wednesday, February 27, 2008 12:03 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 21, 2008 4:05 AM
Points: 291, Visits: 32
Comments posted to this topic are about the item Coping with No Column Names in the SSIS OLEDB Data Source Editor


Paul Ibison
Paul.Ibison@replicationanswers.com
Post #460705
Posted Wednesday, February 27, 2008 4:16 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, March 28, 2014 5:06 AM
Points: 1,204, Visits: 915
;) What a very nice post. I tried this very same query in SQL Server 2005 because I used to have the same problem with the # tables. I did not even use the exec part of your query and it worked famously. I only had to some very minor changes.

ALTER PROCEDURE myproc

AS
begin
declare @mytable table (controlaccount tinyint, controldescription varchar(255))

INSERT INTO @mytable(controlaccount, controldescription)
SELECT controlaccount, controldescription FROM [asset types]

SELECT * FROM @mytable
end

But do not try this in MSAccess connected to SQL Server 2005. All you get back is "The stored procedure executed succesfully." even when you exec the myproc stored procedure.


Manie Verster
Developer
Johannesburg
South Africa

I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
Post #460769
Posted Wednesday, February 27, 2008 10:24 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, February 04, 2011 7:20 AM
Points: 977, Visits: 1,499
And if you need indexing, combine the 2 by explicity creating the temp table and indexes, and using INSERT INTO rather than SELECT INTO. This is my preferred method even without indexes. There are other reasons when a table variable is not an option. I just avoid using SELECT INTO for my own resons.

Tom Garth
Vertical Solutions

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers
Post #461089
Posted Thursday, February 28, 2008 12:38 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 7:19 AM
Points: 2,814, Visits: 3,851
Great Article! Just one comment on indexing table variables: It actually is possible, just a little limited: You can create unique or primary key constraints on single columns.
Example:
DECLARE @MyTable TABLE (
ColA int NOT NULL PRIMARY KEY CLUSTERED
,ColB int NOT NULL UNIQUE NONCLUSTERED
)

But you cannot create such constraints on multiple columns (eg. primary key on ColA and ColB)


Best Regards,
Chris Büttner
Post #461455
Posted Thursday, February 28, 2008 4:24 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, March 20, 2014 7:32 AM
Points: 5,194, Visits: 1,368
Great one. Very clearly explains the problem.
Thnx



Post #461537
Posted Tuesday, December 01, 2009 8:25 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 9:40 AM
Points: 358, Visits: 2,775
hi friends,

First of all I'm sorry as I'm recalling this old post.

I came accros the same problem 'Using Stored Procedure with temp table in SSIS' , and accidently I saw this old post.

From different articles I red that 3 methods to solve this issue.

1) At the start of the SP give two SET statements
SET FMTONLY OFF
SET NOCOUNT ON

Bad about this approach is when we execute the package once, the sp will get executed 5 times (by some method we can limit it to 2 only not to 1), so if insert statement is there in SP it will execute 5 times.

2) Use table variable

This case we can't create any nonclustured index on the table variable also SQL doesn't maintain any statistics for table variable.

3) Use a never true condition to trick the OLEDB , ie at the top of the SP give the condition as
if(1=2)
begin
here give the select statement with all columns in the final result with proper datatype,ie

SELECT CAST(NULL AS INT) AS C1,CAST(NULL AS VARCHAR(30)) AS C2..

end


Now I will tell you my problem, my sp has an insert to a table, so if I go for the first option it will insert more than once for each execution of the package, in my sp I need some nonclustered index on the table so I cant go with second option also.

So I preffered third option ,but in this case it will work only if we directly pass the input of the SP , ie

exec sp_name 1,'abcd'

It is not working if I pass the value through paramter,like

exec sp_name ?,? :-(


If any one has a solution for this please help me.

Once again sorry for recalling the old post.

Thanks & Regards,
MC



Thanks & Regards,
MC
Post #826730
Posted Tuesday, December 01, 2009 8:40 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 9:40 AM
Points: 358, Visits: 2,775
hi friends,

I got the solution also...

Inside the sp we need to give SET NOCOUNT ON ,ie

CREARE PROCEDURE SP_NAME
(@INPUT1 INT,
@INPUT2 VARCHAR(30)
)
AS
BEGIN
SET NOCOUNT ON
IF(1=2)
BEGIN
-- select all the columns in the final result
END

-- body of sp

END

Thanks&Regards,
MC



Thanks & Regards,
MC
Post #826749
Posted Tuesday, March 08, 2011 9:06 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 13, 2013 12:39 PM
Points: 155, Visits: 132
I have the same exact problem except that I am using Sybase as ole db data source. Columns are not appearing. I am calling a SP with the last statement is a select from temp table. I tried solution 1 and 3 with no success. I did not try solution 2 as the data source is sybase. Any help would be appreciated.



Give me a fish, you feed me for a day! Teach me to fish, you feed me for a life time.
Post #1074891
Posted Friday, April 22, 2011 10:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 14, 2013 5:32 PM
Points: 1, Visits: 133
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 , ........
........ .
)
select convert(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
Post #1097534
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse