August 12, 2008 at 8:35 am
Hi all,
I have a OLEDB source objust in my dataflow task which keeps timing out and I'm not sure why this is happening. The statement in the command window runs a stored procedure from a variable called strSQL:
SET FMTONLY OFF EXEC PROVIDER_DIRECTORIES 510,23,'8/7/2008 2:22:50 PM',' ',NULL
The statement runs just fine in Management Studio. But when I hit preview it times out. I've even tried changing the command timeout on the data flow task to a very large number and still nothing, as well as to 0 (which according to the description is infinite timeout) and still it times out. Can anyone see if I'm doing something wrong?
Thanks,
Strick
August 12, 2008 at 8:42 am
And you sure the connection manager is ok? Test Connection button is ok?
Are there any expressions on the connection manager that could be changing the server name at runtime?
If you remove SET FMTONLY, does it work?
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
August 12, 2008 at 9:14 am
Hi, Yes the test connection works fine. I can't remove the SET FMTONLY OFF because what this does is return the column schema to the meta data. The stored procedure itself is complex and this statement in essense runs the last SELECT statement in the stored procedure without returning any data to get the column info.
Thanks,
Strick
August 12, 2008 at 9:17 am
When it's trying to run, no blocks on the DB for your SPID?
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
August 12, 2008 at 10:04 am
No, it just times out in SSIS. The exact same statement will run in Mgmt Studio
August 12, 2008 at 12:50 pm
Very weird, does not happen to me.
If you try with a different proc? Or cut down your existing proc for the purpose of testing and try see what is happening.
What does profiler say? Can you see it running in SQL?
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply