I'm having the same original problem...can someone else besides Erland
comment on the issue. Erland, with all due respect, I don't think you
understand the original question. It is an SSIS question and you have
stated you "don't know SSIS per se".

Basically in SSIS, using the OLE connection, it will not parse:

INSERT TABLENAME EXEC STORED_PROC

This syntax is fine in query analyzer.

it has nothing to do with the "?" parmamters.

What we want to know is what SSIS settings (if there is any) that will
allow SSIS to parse this as good SQL?

I've tried changing BypassPrepare and IsStoredProcedure but no luck. I
could write a stored procedure that has this code and make it work like
the original poster did as a test...but I would rather have the
INSERT...EXEC...syntax.

I am going to try the ADO connector to see if that parses it right.







*** Sent via Developersdex http://www.developersdex.com ***

Note: the ADO.NET adapter parses the SQL fine....I suggest switching
from the OLE unless your platforms don't support the ADO adapter.

One thing, you don't use "?" for parameters but actual variable names
with @...like @parm1. You will also need to re-add them as parameters in
SSIS after you change the connector to ADO.NET.

Hope that makes sense.

Would still like an answer to the SQL SERVER OLE connector not parsing

INSERT TABLE EXEC STOREDPROC



*** Sent via Developersdex http://www.developersdex.com ***

John Heimiller (jheimiller@starkinvestments.com) writes:
> I'm having the same original problem...can someone else besides Erland
> comment on the issue. Erland, with all due respect, I don't think you
> understand the original question. It is an SSIS question and you have
> stated you "don't know SSIS per se".

Hey, I may not know SSIS, but I do have experience of OLE DB, and
I doubt that SSIS performs any parsing of its own. So the problem
should appear about any code that uses OLE DB.

But you are right that I did not understand the question in full, but
I'm used to that: too many questions are posted with incomplete
information.

> I am going to try the ADO connector to see if that parses it right.

Ah, my bad. That much I know of SSIS that it can use either an OLE DB
provider or SqlClient, so I should have given that advice.


<Aside>
Actually some time back, I had a bit of fun in our private MVP forum. A
fellow MVP had just started using SSIS on a gig, and was not able to
get parameters to work, and made noise as if it was a misdesign in SSIS.
That is about the only time I've composed an SSIS package, just to show
how to get parameters working, which I was able to. I think my MVP
colleague's problem was that he had had very little experience of client-
side programming overall.
</Aside>
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx