Calling SQL server stored procedure from datastage.

saraswati · Post by **saraswati** » Mon Sep 26, 2011 3:10 pm

I am getting an error:

ODBC_Enterprise_0: [IBM(DataDirect OEM)][ODBC SQL Server Driver][SQL Server]The EXECUTE permission was denied on the object 'xp_sendmail', database 'mssqlsystemresource', schema 'sys'.

when I am putting EXECUTE PROC_NAME(param1,param2) in the user-defined SQL portion in ODBC stage.

chulett · Post by **chulett** » Mon Sep 26, 2011 6:20 pm

saraswati wrote:Tell me one thing....The ODBC stage must be used as a source and not as a target.

That's not correct. In fact, I was assuming you were using it as a target here, which is probably where much of the confusion comes from.

Row Generator --> ODBC

Why as a source? You said you didn't want to 'return any values' so naturally that makes it a target rather than a source.

chulett · Post by **chulett** » Mon Sep 26, 2011 6:21 pm

saraswati wrote:I am getting an error

Take that error to your DBA.

saraswati · Post by **saraswati** » Mon Sep 26, 2011 8:05 pm

If you say that I need to use the ODBC stage as the target then please let know where I need to put the EXECUTE STOR_PROC(param1,param2) in the ODBC stage.

If I use ODBC stage as the target then I do not find User-defined SQL portion to put the EXECUTE STOR_PROC.

Let me know if you have any more questions.

chulett · Post by **chulett** » Mon Sep 26, 2011 9:20 pm

I just checked the documentation for 8.5 and there only seems to be user-defined sql functionality for reading, which to me seems... odd. Realized I was seeing the Server ODBC stage in my head when talking about all this, something much more flexible, it seems. I suppose one would leverage the "Open SQL" or "Close SQL" properties with a known "do nothing" target table operation.

I just don't see how your proc can be treated as a source. Sure, you can pass parameters into and execute it but it will attempt to bind output from the proc to whatever columns you have in the stage and you've got no output parameters from what you've said. What have other people here done in a similar situation?

I'm not sure that you've ever explained what it is this procedure actually does and under what circumstances you need to run it. Perhaps if we understood that, a more appropriate mechanism could be discussed rather than going round and round over the use of an ODBC stage.

ps. If this could be done as a Server job, we would have been done and moved on days ago.

saraswati · Post by **saraswati** » Mon Sep 26, 2011 9:32 pm

I don't think you need to know what my stored proc is doing.

My objective is to execute the stored proc passing input parameters using datastage job.

Please let me know your views on it.

chulett · Post by **chulett** » Mon Sep 26, 2011 9:40 pm

saraswati wrote:I don't think you need to know what my stored proc is doing.

Errr... ok. Then I think that after two pages of my "views" on the subject, I'm done and ready to let someone else take over. Hopefully in the meantime, you've actually involved your official support provider and asked them what they think your options are here.

saraswati · Post by **saraswati** » Fri Sep 30, 2011 9:30 am

Can anyone else help me on this?

Help on this will be highly appreciated.

ray.wurlod · Post by **ray.wurlod** » Fri Sep 30, 2011 3:58 pm

They will if they can. They're less likely to if you keep pushing.

If it's urgent involve your official support provider. After all, that's what you're paying them for.