After SQL Not Executing: MS SQL 2005

[dec1177]

Hello all. I'm having an issue with a very simple job that moves data from one SQL Server 2005 DB to another SQL Server 2005 DB and it looks like this:


ODBC ------>XFM------>DRS


I'm utilizing the DRS stage in the target because of the need to execute a stored procedure on the target server once data has been loaded. I've done this in the past on a SQL Server 2000 DB with no problems. This job completes successfully, with no warnings or errors in the log, but the stored procedure is not executing on the SQL box. Permissions are granted appropriately and I can log into Management Studio and execute the stored proc successfully using the same credentials as I'm using in my job. Anyone ever seen this before? Any help or guidance is much appreciated.

Thanks.

[dec1177]

Uh...is there anybody that can suggest an idea on this...? It's been an open topic for almost 2 months but no reply. Still having to manually execute the stored proc after the job is complete.

Any help is much appreciated.

[chulett]

Well... keep in mind the fact that once a topic rolls off the first page, pretty much the only thing that's going to bring it back to people's attention is a reply, much like you just did.

So are you saying that the statements you have in the After SQL tab are not even being executed or they are being executed but the sproc doesn't actually seem to run? The job's log should make the distinction clear as each action would be there - for example, when the After SQL runs, the job will log a message along the lines of:

Code: Select all

JobName..StageName: Executing AfterSQL

Do you not see this in your log? Can you post the actual contents of the tab, please? Do you have the 'Treat errors as non-fatal' option enabled?

[dec1177]

Chulett,

Thanks for the response, I realize my response refreshed the topic...I'd just seen that it had been viewed 3 dozen times without a response to date and figured there just HAD to at least be one other individual that has seen this before. :D

Anyway, what I'm saying is that the after sql APPEARS to be executing per the job log, yet it's not actually executing on the SQL server. It's one simple statement that executes one stored proc. I've pasted the log line below to show you that the job claims to be executing the after sql:

"irsac_Hyperion_To_AssetsInformation..AVDB: Executing SQL statement 'Exec USP_ProcessHyperion'"

The "Treat Errors as non-fatal" box is unchecked, however I've run the job both ways and no difference (in target server or job log).

I have built a couple of other jobs in the past that do this exact same thing, but those jobs were targeting a SQL 2000 DB (the job in question targets a SQL 2005 DB).

Thanks for your response, it really is appreciated. Any ideas?

[chulett]

Unfortunately, no, not really.

I mostly just wanted to verify that it was 'being executed'. I've no real experience with SQL Server so the gory details of differences between the 2000 and 2005 editions are unknown to me and thus I've got no clue why it would work in the older version and not work (or even throw an error) in the newer one.

I'm assuming you've taken this to your DBAs and they haven't been able to help. So it seems to me, unless someone else chimes in, that you'd need to open a case with your official support provider and see if this is a known issue or bug with SQL Server 2005.

One thing does come to mind - is your proc 'robust'? Meaning, if it does have any problems or failures does it actually report them back or does it just exit? For an Oracle proc, that would mean "raising" an error so that DataStage knows there was a problem, what is the equivalent for SQL Server?

[dec1177]

DBAs? What are DBAs? :D

I'm about as close as you get to one here in my organization, and I'm far from being a DBA...

I'm not sure how 'Robust' the sproc is, it was created by an outsourced development boutique and I've not had the agonizing 'luxury' of obtaing the source code yet.

I'm going to work this problem from the SQL Server side though and see if I can trace what, if anything, is going on when the ETL job executes the 'After' SQL, and maybe even attempt to create my own sproc that will execute some mundane task on a SQL 2005 DB to see if I can get it to work and target the sproc in question as the problem. I'll reply with my findings.

In the meantime, if anyone can suggest a tip on the DataStage side, it would be much appreciated.

[chulett]

Fun! :D

About the only thing I can think of on the DataStage side would be to enable tracing. Switch to the "Tracing" tab in the Run dialog and turn on at least the Property Values and Subroutine Calls trace levels. Maybe something will come of that, but I suspect that any "tracing" will need to happen on the database side.

[dec1177]

Okay, I believe I figured out why the stored proc would not execute, and have discovered a very simple workaround to this issue.

After gaining access to the code for the stored procedure in question (that I was executing in the After SQL tab in the DRS stage) I discovered that there was something that set it apart from any other stored proc I had executed from a DRS stage...it uses CURSORS. For some reason, I think a stored proc utilizing any CURSORS will not execute as desired from the DRS stage in 7.5.2. I don't know if this is a known issue or not.

My workaround to the problem involved me creating a second stored procedure thats sole purpose is to execute the initial stored procedure.

So now I call my new stored proc from the After SQL tab in the DRS Stage:

exec newStoredProc

And all this stored procedure does is call the initial sp:

exec initialStoredProc

It works perfectly, everyone is happy, and now I no longer have to manually execute the stored proc after each run of my DataStage job!

Thanks chulett for your $0.02!

[chulett]

Interesting solution, glad you found one.

Thanks chulett for your $0.02!

No problem, that will be $27.95 please.