MSSql Server and Stored Procedures

palmeal · Post by **palmeal** » Mon Apr 25, 2005 1:16 am

I think I know the answer to this one already but I can always hope. The project that I am working on takes data from approx 10 Sybase servers and dumps the data onto an MSSQL Server.
When all of the data are loaded I have to carry out a series of transforms/sums/aggregates etc that would easily be achieved through the use of a stored procedure. I know that MSSQL stored procedures are not supported within DataStage but would it be safe to use them as I wouldn't be returning any data except the return code ?

If I can't use stored procedures then a large number of server jobs will be required to achieve what I want to do. If that is my only option then I'll just have to get on with it. I'm just looking for clarification that this is my only option.

It is not possible to carry out the processing elsewhere (sybase/oracle) due to the volumes of data involved.

ray.wurlod · Post by **ray.wurlod** » Mon Apr 25, 2005 4:14 am

Who says MS SQL Server stored procedures aren't supported in DataStage?

Can you import the stored procedures? That's ultimately the test of whether they're supported - you can read about what the rules for supported/unsupported are in the Server Job Developer's Guide - it's about whether there are only input arguments. (I don't have DS available at the moment.)

palmeal · Post by **palmeal** » Mon Apr 25, 2005 4:47 am

When I try to import the stored procedures the only ones that are listed are ones named dt_**** which are all for version control. These are installed at run time. No other user created procedure names are returned. I tried creating a stored proc named dt_delme but this is not picked up in DataStage. The other columns in sysobjects show the same values as the system procedures.
If I log onto the SQL Server with the same user then I can see these procedures in the sysobjects table. They are all owned by dbo so I can't see why some names should be returned and not others. I would have to see the DataStage code that queries the sysobjects code - is there any way to get my hands on this ?

Assuming that I can get around this and find my stored procedures I would only have input arguments which looking at your reply would probably be ok to do ?

palmeal · Post by **palmeal** » Mon Apr 25, 2005 5:02 am

I can now see all of the stored procedures when I do import. I had to add on grant permissions to my MSSQL user despite the fact that my user is already dbo. So despite the fact that I can exec these procedures whilst logged onto an MSSQL server when I try and retrieve the definitions through DataStage from the same user the definitions aren't returned. Maybe this points to MSSQL procedures not being supported.

I'm going to push on with their use and see how things go - I don't think I am ready to write a recursion function yet in DataStage !!!

palmeal · Post by **palmeal** » Mon Apr 25, 2005 6:31 am

Delving a little deeper into this has thrown up a problem which I don't think I can get around: the grant of exec must be given to public in order for DataStage to pick up these stored procedures. I have tried granting access to individual users but DataStage fails to pick up these procedures. In a live production system this is obviously a no no.

Is there anyone else in the DataStage world running along happily with MSSQL ?

ray.wurlod · Post by **ray.wurlod** » Mon Apr 25, 2005 1:42 pm

Have done so in the past, though DS was running on a Windows-based server. We used stored procedures to generate GUID values (as surrogate keys) and return these to DataStage (as VarChar(36) or VarChar(38) - the latter are quoted). I think I posted example code - it's about two years back.