SQL_Server_SP..ODBC_5.DSLink6: DSD.BCIOpenR call to SQLExecDirect failed.
Statement was:{call DW1.dbo.spLoyaltySales(@NULL, @NULL, 08152005, 08142005, 0)}
SQLSTATE=22005, DBMS.CODE=0
[DataStage][SQL Client][ODBC][Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification
Does anybody know where I'm going wrong? Am I passing the wrong parameter values to the stored prodecure when I call it?
Your help is very much appreciated.
Thanks,
Naveen.
DataStage doesn't support Microsoft SQL Server Procedures - dynamic SQL I'm afraid.
Import only works for SQL Stored Procedures when they have access rights granted to Public - this is not recommended for Production systems. Remove Public access and you won't be able to import the definition.
While I am on the subject, BCP is also not supported for Microsoft SQL Server on versions after 6.0.
DataStage supports Sybase Stored procedures as of version 7.5.1 (new stage) although they can be called for 7.5 but again not supported (not sure about 7.0).
It also supports BCP for Sybase.
There are only 10 kinds of people in the world, those that understand binary and those that don't.
Is it not sufficient if the definition is imported while in development. Why do we have to import the definition again in production?
Can't we use the same definition that was imported in development? Please correct me if I'm thinking wrong. Thanks a lot for your help.
Yes! the stored procedure that I'm calling is in the public domain.
Are you saying that the stored procedure cannot be called once it is removed from the public domain. Please help.
You do not have to re-import definitions for Production.
What I am saying is :
(1) MSSQL Stored Procedures are not supported/do not work within DataStage.
(2) The reason you are able to bring back the definitions of the procedures is because the code is read form the same tables as Sybase (sysobjects, syscomments) - this gives the impression that MSSQL Stored Procedures are supported but they are not and won't work.
(3) If you amended the "grant to public" on the MSSQL Stored Procedures to "grant to production_user" then you would not see any MSSQL Stored Procedures to import.
(4) Your only option is to code with dynamic SQL (through ODBC Stage) rather than MSSQL Stored Procedures.
I only wish I was wrong.
I would hope that DataStage going forward support MSSQL Stored Procedures and BCP because they are missing out on a big market share.
Version 7.5.1 only officially started to support Sybase Stored Procedures although they worked to a fashion in 7.5 but not transactionally.
Hope this helps clear up things for you but I guess won't help you achieve what you are trying to do.
There are only 10 kinds of people in the world, those that understand binary and those that don't.
Can I just execute a SQL Server Stored Procedure from Datastage. I don't want any result set. I just want to execute the stored procedure by passing some parameters.
The stored procedure actually truncates and then loads a table when called. Is this possible in DS?
I've just tried calling an MSSQL stored procedure via odbc and it actually worked - this goes against what I said above but don't confuse this with thinking that MSSQL Procedures will work as you expect.
The definition of the procedure will only be picked up if defined as public but this is not a problem if it is only public on the dev server but not production.
I was able to pass a parameter to the procedure (user defined SQL) and I even got back a result set.
Within the procedure I set up a transaction, created a temporary table and deleted from a user database table. I was even able to set "Read Committed" on the DataStage side.
However, when I tried to use MSSQL Procedures before (bit more complex procedure) then I was finding that certain things just didn't work - I can't remember specifics but between myself and confirmation from an Ascential consultant DataStage shouldn't be used for MSSQL Procedures.
Try running your code this way and do two things -
(1) hit the DataStage button to stop processing whilst the procedure is running - see if control is lost to DataStage as what happens if your procedure was to hang ?
(2) force an error to happen within the stored procedure to see if DataStage can handle it. I did a "return -1" and DataStage ignored it - the process then continued to the next Stage thinking that it is working ok.
At the end of the day it is up to you what level of risk you want to take. I have tried to get a definitive statement from Ascential (IBM now I guess) about the state of play regarding MSSQL Procedures
This is the answer that I received from the consultant (in June) re the support of MSSQL Procedures in version 7.5.1.
"1) Currently ODBC does not support (MSSQL) stored procedures.
2) ODBC is a generic object not a specific DB connectivity to MS SQL
Server. I may need to research this further to confirm whether this
function will be added in a future release."
If you are only doing a delete and then an insert into a table then could you not just write some user-defined SQL ?
There are only 10 kinds of people in the world, those that understand binary and those that don't.
