I’m facing a issue in BODS 4.0.
I have sql server store procedure Which has two parameters. One is used as input parameter and other used as output parameter. Both Declared as Varchar(15). I have to use this Store procedure in a job in which i have to use values in a column from the source as input to the store procedure and have to fetch the value of the output parameter from the store procedure for loading to the target table. For this i import the store procedure in my datastore and used it as a function call inside the query transform. job is executing successfully even the store procedure is getting executed without error but i’m getting only Null values in the output parameter.
Please help me to resolve this issue. I even tried to call a that store procedure inside the script by providing a variable of size 15 to hold the output parameter value that variable is also getting assigned NULL.Please help me to solve this issue as soon as possible. Please Give all your thoughts to resolve this issue…
I am presuming you have tested the SP in query analyzer? Does it work there? When you call it in a script are you passing in a hard coded (non-variable) value?
Ganesh
Hi Checked value of Ret_code and error msg. Ret Cod is ACT_SP_OK, Error Msg Null. Is it a bug in BODS. I think its abug while handling Sql Server store proc with output parameter.
Ernie Phelps
Its wroking in SQL Analyser. Given hardcoded value as input
can you try the following stored procedure and see if this is working, it’s working fine for me when I am calling this in a script and query transform, I get the output, I used SQL Server 2008 R2
CREATE PROCEDURE GetText @ID VARCHAR(15), @strID VARCHAR(15) OUTPUT
AS
BEGIN
select @strID = ‘test’;
END
I tried with Patch 5, but I don’t think DS version is a problem, try the following and let’s see if this works or not, then we can compare your SP and this to see what is the difference
Thanks For your reply. I dont have the access privilage to create a new SP in ma server. I have requested for the same meanwhile will post you the SP code that causing the issue.Can you please analyse SP code to point out any issue with the code.
ALTER Procedure [dbo].[SPName]
( @Variable1 Varchar(15) = Null, @Variable2 Varchar(15) OutPut )
As
Begin
select @Variable1
If @Variable1 Is Null
Begin
print ’ is null’
Set @Variable2 = Null
Return
End
If Exists ( Select 1 From [ServerName].Dbname.dbo.Tablename RMI
Where RMI.FieldName = @Variable1 )
Begin
print ' is not null'
Set @Variable2 = @Variable1
Return
End
If Exists ( Select 1 From [ServerName].Dbname.dbo.Tablename RMI
Where RMI.FieldName Like @Variable1 + '_%' )
Begin
print ' is not null'
Set RowCount 1
Select @Variable2 = RMI.FieldName From [ServerName].Dbname.dbo.Tablename RMI
Where RMI.FieldName Like @Variable1 + '_%'
Order By RMI.FieldName
Set RowCount 0
Return
End
Return
If you need your database code to return a value when used within a query transform then you should be using a database function, not a database stored procedure.
Hi Jim,
Is it not possible with a SP with output parameter. Can you please provide some reference link for the same to point out this functionality will not work within Query transform.
No, it is not possible. To return a value from a parameter within the mapping of a query transform you would have to use a variable. Question: How does the variable work in that situation? Answer: It doesn’t.
If you wanted this to work you could write a custom function as a wrapper to the stored procedure. The custom function would have to return a value that would be used by the query transform’s mapping.
One of the major reasons that you DO NOT want to use a stored procedure is that in this context it CANNOT be pushed down to the database. If you were to use a database function then it COULD be pushed down to the database which would be the optimal solution. If you go the custom function route and you have 100,000 input rows then your Dataflow will perform 100,000 stored procedure executions. This is far from optimal!
your SP has 2 checks if first fails it does the second check if that fails there is nothing that is setting the output varaible so it will be NULL, you should test your SP with 3 different input from DS and also from Query Analyzer
1 - that will make the first condition to pass
2 - that will make the second condition to pass
3 - that will make both the condition to fail
you can also enable trace for the SP and see if the correct input is getting passed, the default for input is set to NULL, not sure if that is causing any issue
can you post the syntax of the SP that you are using ?
I’m passing a input value that gives output in Query Analyzer. I dont think its the issue with condtion check.
I analyzed the code more and find out that select @Variable1 just after the Begin statement in the below code is actually causing the problem i think if we remove that select value will gett assigned to the output parameter. I have not recieved the code alteration privilage yet will confirm once i get that. is there any issue it the SP is returning two values one in the ouput parameter other directly.
ALTER Procedure [dbo].[SPName]
( @Variable1 Varchar(15) = Null, @Variable2 Varchar(15) OutPut )
As
I see the same issue if I use your Stored procedure
I don’t think the issue is with the select @Variable1 , I tried after removing it and it still sets the output as NULL
–Update–
Yes, the Select @Variable1 is causing the issue, if you have that the stored procedure is returning a result set and DS is not handling that
(BOB member since 2008-07-17)