Topic
  • 9 replies
  • Latest Post - ‏2013-03-13T09:59:07Z by Vinod.M.Lanjekar
vamseedhar
vamseedhar
16 Posts

Pinned topic Calling Sybase sp from WTX map

‏2008-09-15T21:23:51Z |
Hi,

I am calling a sybase sp in WTX map field passing the required input and output params as below.

=DBLOOKUP("call csrsp_translate_code_alias 'abcde', null, 'xyz', 'abcxy', 'order', null,?,?,?,?,?,?,?)", "-DBTYPE SYBASE -SOURCE myServer\\mydatabase -USER myuser -PASSWORD mypassword -TRACE")

But having problem in getting back the output params( all it returns empty values"|||||||||||||" even after having valid output, how can I say that I executed the sp call with same inputs on isql it returns the values).
Th error info from dbl file is as below. The UserID used to call this sybase sp has only execute permissions (ID can be given only those permissions as per clients security complaince)

Error: <3372-3360>: Server(testrch0) error or informational msg--->number(2762) severity(16) state(3) line(207) status(6)error or info: The 'CREATE TABLE' command is not allowed within a multi-statement transaction in the 'tempdb_02' database.

Can some one help me out in resolving this issue. When I execute the default sybase sp i.e. sp_who can get all the results with pipe seperated.

Thanks
Vamseedhar
Updated on 2013-03-13T09:59:07Z at 2013-03-13T09:59:07Z by Vinod.M.Lanjekar
  • SystemAdmin
    SystemAdmin
    6184 Posts

    Re: Calling Sybase sp from WTX map

    ‏2008-09-16T14:47:51Z  
    According to the Sybase adapter documentation, the user must have "Create Procedure" access in the tempdb. To separate our processes from the client, our Sybase DBA created a tempdb for the WTX user only and granted the proper permissions. You'll need to add an environment variable (DTX_TEMPDB or MERC_TEMPDB depending on version you're using) so that your work uses that tempdb.

    For more info, go to the Sybase Adapter documentation and search for tempdb.
  • vamseedhar
    vamseedhar
    16 Posts

    Re: Calling Sybase sp from WTX map

    ‏2008-09-17T14:11:48Z  
    According to the Sybase adapter documentation, the user must have "Create Procedure" access in the tempdb. To separate our processes from the client, our Sybase DBA created a tempdb for the WTX user only and granted the proper permissions. You'll need to add an environment variable (DTX_TEMPDB or MERC_TEMPDB depending on version you're using) so that your work uses that tempdb.

    For more info, go to the Sybase Adapter documentation and search for tempdb.
    Thanks omhopper, For now our sybase DBA has chaged the sp not use temp tables. So after changing that can get the results. But am still working on it like what if the sp needs temp table and we have the same permissions to wtx userID what we have today, how to get the results.

    Also even now I have pass the whole sybase sp syntax to get the results i.e.

    =DBLOOKUP("Declare variables execute sp_name in/out params select outparams", "-DBTYPE SYBASE ......")

    If I use normal WTX database independent syntax i.e.
    =DBLOOKUP("call sp_name in/out params","-DBTYPE sybase .....") it won't resturn me the results.

    Any suggestions on it

    Thanks
    Vamsee
  • SystemAdmin
    SystemAdmin
    6184 Posts

    Re: Calling Sybase sp from WTX map

    ‏2008-09-17T18:17:29Z  
    Thanks omhopper, For now our sybase DBA has chaged the sp not use temp tables. So after changing that can get the results. But am still working on it like what if the sp needs temp table and we have the same permissions to wtx userID what we have today, how to get the results.

    Also even now I have pass the whole sybase sp syntax to get the results i.e.

    =DBLOOKUP("Declare variables execute sp_name in/out params select outparams", "-DBTYPE SYBASE ......")

    If I use normal WTX database independent syntax i.e.
    =DBLOOKUP("call sp_name in/out params","-DBTYPE sybase .....") it won't resturn me the results.

    Any suggestions on it

    Thanks
    Vamsee
    Do you have trace enabled when you're executing the WTX syntax call? What does the database report as being wrong?
  • vamseedhar
    vamseedhar
    16 Posts

    Re: Calling Sybase sp from WTX map

    ‏2008-09-17T19:22:13Z  
    Do you have trace enabled when you're executing the WTX syntax call? What does the database report as being wrong?
    When I run with WTX syntax as below. I get empty result set returned "|||||||||||||"

    =DBLOOKUP("call csrsp_translate_code_alias 'amclab', null, '12CAR', 'LW-t20', 'Orderable', null,?,?,?,?,?,?,?)", "-DBTYPE SYBASE -SOURCE CSR_Dev\\csrdb_rch01_devl -USER userID -PASSWORD password -TRACE")

    Also have attached the trace file.
  • SystemAdmin
    SystemAdmin
    6184 Posts

    Re: Calling Sybase sp from WTX map

    ‏2008-09-17T20:17:00Z  
    When I run with WTX syntax as below. I get empty result set returned "|||||||||||||"

    =DBLOOKUP("call csrsp_translate_code_alias 'amclab', null, '12CAR', 'LW-t20', 'Orderable', null,?,?,?,?,?,?,?)", "-DBTYPE SYBASE -SOURCE CSR_Dev\\csrdb_rch01_devl -USER userID -PASSWORD password -TRACE")

    Also have attached the trace file.
    The trace indicates that results were found. Are you

    <808-216>: Number of buffers in fetch array = 1
    <808-216>: Writing results to a buffer.
    <808-216>: Retrieved 1 records (51 bytes).
    <808-216>: The following data was returned from DBLOOKUP:

    <808-216>: amclab|12CAR|LW-t20|82429-ROCLISO|CK-MB Iso, CCL|N|

    Are you using the stored procedure as Input or Output? If it's Output, there's a line in the documentation (Database Interface Designer -> Using a Stored Procedure as an Output):

    "However, there is no mechanism to return values from output parameters. Any values passed to an OUT parameter will be ignored."
  • vamseedhar
    vamseedhar
    16 Posts

    Re: Calling Sybase sp from WTX map

    ‏2008-09-19T15:34:24Z  
    The trace indicates that results were found. Are you

    <808-216>: Number of buffers in fetch array = 1
    <808-216>: Writing results to a buffer.
    <808-216>: Retrieved 1 records (51 bytes).
    <808-216>: The following data was returned from DBLOOKUP:

    <808-216>: amclab|12CAR|LW-t20|82429-ROCLISO|CK-MB Iso, CCL|N|

    Are you using the stored procedure as Input or Output? If it's Output, there's a line in the documentation (Database Interface Designer -> Using a Stored Procedure as an Output):

    "However, there is no mechanism to return values from output parameters. Any values passed to an OUT parameter will be ignored."
    Hi,

    My bad have attached a wrong log file, when I run the rule on a output card with

    =DBLOOKUP("call csrsp_translate_code_alias 'amclab', null, '12CAR', 'LW-t20', 'Orderable', null,?,?,?,?,?,?,?)", "-DBTYPE SYBASE -SOURCE CSR_Dev\\csrdb_rch01_devl -USER myuserid -PASSWORD mypassword -TRACE")

    It get null values returned something like "|||||||||||||"

    Also have set the -PROC sp_name under Output card PUT ->Target ->Command

    Here is the log file..
  • SystemAdmin
    SystemAdmin
    6184 Posts

    Re: Calling Sybase sp from WTX map

    ‏2008-09-22T18:31:26Z  
    Hi,

    My bad have attached a wrong log file, when I run the rule on a output card with

    =DBLOOKUP("call csrsp_translate_code_alias 'amclab', null, '12CAR', 'LW-t20', 'Orderable', null,?,?,?,?,?,?,?)", "-DBTYPE SYBASE -SOURCE CSR_Dev\\csrdb_rch01_devl -USER myuserid -PASSWORD mypassword -TRACE")

    It get null values returned something like "|||||||||||||"

    Also have set the -PROC sp_name under Output card PUT ->Target ->Command

    Here is the log file..
    I'm surprised the procedure is running. From the log:

    Userid : tu00206
    Password : *****
    Query : call csrsp_translate_code_alias 'amclab', null, '12CAR', 'LW-t20', 'Orderable', null,?,?,?,?,?,?,?)

    It looks like the opening parenthesis is missing. The Query should be:

    call csrsp_translate_code_alias *(*'amclab', null, '12CAR', 'LW-t20', 'Orderable', null,?,?,?,?,?,?,?)

    Shouldn't it?
  • vamseedhar
    vamseedhar
    16 Posts

    Re: Calling Sybase sp from WTX map

    ‏2008-09-23T17:39:41Z  
    I'm surprised the procedure is running. From the log:

    Userid : tu00206
    Password : *****
    Query : call csrsp_translate_code_alias 'amclab', null, '12CAR', 'LW-t20', 'Orderable', null,?,?,?,?,?,?,?)

    It looks like the opening parenthesis is missing. The Query should be:

    call csrsp_translate_code_alias *(*'amclab', null, '12CAR', 'LW-t20', 'Orderable', null,?,?,?,?,?,?,?)

    Shouldn't it?
    Thank you omhopper, yes your are right I missed the parantheses and also now getting results as our Sybase DBA changed the SP API not to use temp tables.
  • Vinod.M.Lanjekar
    Vinod.M.Lanjekar
    9 Posts

    Re: Calling Sybase sp from WTX map

    ‏2013-03-13T09:59:07Z  
    Hi,

    In my project, I have requirement in such way that when I call a SP from WTX map I should get the multiple rows.If we need to get few columns and row ,as we know we will use '?'.
    But i am not getting how get set of rows. Kindly provide me your input,if you have come across such scenario.

    The output from SP looks like this :

    Slno Name EmpID Desgnation
    Slno Name EmpID Desgnation
    Slno Name EmpID Desgnation
    Slno Name EmpID Desgnation
    Slno Name EmpID Desgnation
    Thanks and Regards,

    Vinod