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

OLEDB Command fails pre-execute phase Expand / Collapse
Author
Message
Posted Thursday, March 4, 2010 1:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 21, 2013 5:05 AM
Points: 46, Visits: 159
Im using an OLEDB Command to call a certain procedure, which is failing at runtime with the following error: (Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Syntax error, permission violation, or other nonspecific error".)

Been doing some research and there's nothing wrong with the passing of the parameters nor is there any update statement in the procedure which was working just fine.. In fact the problem seems to be with the transformation itself: ([DTS.Pipeline] Error: component "OLE DB Command" (79785) failed the pre-execute phase and returned error code 0xC0202009.)

Recently however I added an sql task transformation to the flow which creates and fills a temporary table. I don't find any reason though why it should affect the flow, nevertheless its the only thing that has changed in the package ever since it was executing normally!

any ideas??


Post #876621
Posted Thursday, March 4, 2010 2:06 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:29 PM
Points: 12,966, Visits: 10,742
You mentioned a temporary table.
When SSIS starts evaluating your package, the temporary table doesn't exist yet.
Hence, he will find a reference to an object that he cannot find.

Solution: you must set the property DelayValidation of all components/tasks that use this temporary table to True.

See if that helps.

Regards




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #876634
Posted Thursday, March 4, 2010 5:43 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 21, 2013 5:05 AM
Points: 46, Visits: 159
The delay validation property is already set to true and the temporary table is not related to the procedure in any way! I tried to disable the DFT that is failing, another DFT also containing an OLEDB Command calling the same procedure fails on pre-execution, the weird thing though is that the validate external metadata property on that transformation is set to False... im starting to doubt either it has something to do with parameter passing or with database permissions, the schema's owner however is set to dbo.

Post #876737
Posted Friday, March 5, 2010 12:34 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 21, 2013 5:05 AM
Points: 46, Visits: 159
Solved: The components that relate to the temporary tables should have the Persist Security Info property in the OLEDB Connection manager set to False, while the rest of the transformations requiring an OLEDB Connection should read from one that has the Persist Security Info property set to True (that what was causing the OLEDB Command to fail calling the stored procedure at runtime)

regards,
Samer.
Post #877435
Posted Friday, March 5, 2010 1:36 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:29 PM
Points: 12,966, Visits: 10,742
Learned something new today. Thanks for letting us know.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #877460
Posted Thursday, July 18, 2013 1:05 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 5, 2013 9:53 AM
Points: 3, Visits: 27
This worked fine. Thanks a lot.
But my problem is source and destination are both pointing to the same database and hence I had to create a new connection with same details again and the only difference between the 2 connections is "Persist Security Info". Is there any other way to resolve this issue?
Post #1475181
Posted Thursday, July 18, 2013 11:45 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:45 AM
Points: 5,083, Visits: 11,865
Use Windows authentication in your connection.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1475275
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse