April 26, 2011 at 8:55 am
Hi,
I've run into a problem while using an OLE DB Source.
I'll explain a bit what I'm trying to do.
We have our sql server 2008 prod server which is replicated on to another server. On the replication server we also store our BI database.
One of our tasks is to create hot fixes in production but log the change in on the replication server. I've come up with the idea that I can use an OLE DB Source in prod and generate a select statement that will be passed to an OLE DB Destination on the replication for logging purposes.
I'll provide an example of the problem:
SET NOCOUNT ON
DECLARE @source TABLE (
pkINT IDENTITY(1,1),
oldBIGINT,
newBIGINT
)
DECLARE @logging TABLE (
col1VARCHAR(50),
col2VARCHAR(50),
oldBIGINT,
newBIGINT
)
-- Populating the source data with data
INSERT INTO @source
select 5736657,6133567
UNION
select 5759948,5862614
UNION
select 5849187,5848480
UNION
select 5848605,5848604
UNION
select 5848604,5848605
UNION
select 5848820,5848819
UNION
select 5848819,5848820
UNION
select 5848480,5849187
UNION
select 5849214,5849213
UNION
select 5849213,5849214
DECLARE @pk INT = 1,
@max INT = (SELECT MAX(pk) FROM @source)
-- Loop through records in the source
WHILE @pk <= @max
BEGIN
EXEC zNested
SET @pk = @pk + 1
END
INSERT INTO @logging
SELECT 'table' AS col1, 'field' AS col2, old, new FROM @source
SELECT * FROM @logging
SET NOCOUNT OFF
As you can see there is a nested stored proc. In my situation, that nested stored proc applies the fix to the data on a per-record basis. That's why it's encased in a while loop to it can batch run.
If I were to encompass this whole thing in a stored proc, it'll work... but I don't want to do that because it means that every new hotfix that we need to create, we would need to make a stored proc which would need to be on the production server and thus it would clutter up the production database.
If I leave it as is, I get the cursed No column information error.
I'm hoping that someone has a solution to this problem.
Thanks,
Sam
April 27, 2011 at 11:53 am
Admit up front this is a stretch.. But you might wrap it all in an EXEC ( ' ' ). I'm wondering if the compiler is getting confused with all the intermediate tables and such.
I tried a really simple version along the lines of:
EXEC( 'DECLARE @Wk TABLE ( Field1 int )
INSERT @Wk ( Field1 ) VALUES ( 0 )
SELECT * FROM @Wk' )
And it said it found Field1.
Whether this will work for your particular query I can't be sure.
CEWII
April 27, 2011 at 1:36 pm
A couple of ideas spring to mind:
1) Do not use Select *
2) Put a dummy Select near the top of your routine to try and trick SSIS into thinking it knows what it's doing.
IF 1 = 0
BEGIN
SELECT CAST(NULL as smallint) as Field1, Cast(NULL as Varchar(20)) as Field2
END
April 28, 2011 at 12:35 pm
@Elliott:
Excellent! Encompassing the entire thing in an EXEC statement worked! 🙂
One thing that I noticed was that I needed to add the SET FMTONLY OFF flag.
@Phil:
Your solution was one that I had previously tried and it appears that regardless of whether they're a * or explicitly declared fields in the select statement doesn't matter as long as the final select statement is declared in a variable.
Thanks guys for posting. I greatly appreciate it!
Sam
April 28, 2011 at 12:55 pm
Really.. Hm... I didn't, but it could be the code you were using, good to know..
CEWII
April 28, 2011 at 1:44 pm
With the example I provided, I didn't need to set that flag however with my code I did lest it give me the same "No column information..." error.
There could be something else in there that requires to have that FMTONLY flag set to off.
Sam
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply