MQSeries.net :: View topic - MB V6

scravr · Posted: Thu Nov 29, 2007 7:00 am Post subject:

When configuring new data-connection to MSSQL 2000, I see the tables and views only. How can I bring stored-procedures?
I can select,update... on tables but cannot exec SP.
Getting errors saying SP not exist:

2007-11-28 14:25:56.612037 3580 UserTrace BIP2231E: Error detected whilst processing a message in node 'DataShare_PCS_MF_PROJ_112007.PcsDbCallsCompute'.
The message broker detected an error whilst processing a message in node 'DataShare_PCS_MF_PROJ_112007.PcsDbCallsCompute'. The message has been augmented with an exception list and has been propagated to the node's failure terminal for further processing.
See the following messages for details of the error.
2007-11-28 14:25:56.612049 3580 RecoverableException BIP2488E: ('.DataShare_PCS_MF_PROJ_112007_PcsDbCallsCompute.Main', '33.3') Error detected whilst executing the SQL statement ''SET Dup = CheckDuplicate2(CAST(PCS.SiteXCoordinate AS CHARACTER), CAST(PCS.SiteYCoordinate AS CHARACTER), CAST(PCS.DateStarted AS CHARACTER) || ' 12:00:00 AM', CAST(Environment.Variables.BoroCode[1].BoroCode AS CHARACTER), CAST(PCS.SiteAddrNum AS CHARACTER), CAST(PCS.SiteStreetName AS CHARACTER), CAST(Environment.Variables.AddressInputTypeNo[1].AddressInputTypeNo AS CHARACTER), CAST(PCS.SiteOnStreet AS CHARACTER), CAST(PCS.CrossStreet1 AS CHARACTER), CAST(PCS.CrossStreet2 AS CHARACTER), CAST(PCS.SiteLandmark AS CHARACTER), CAST(Environment.Variables.LocationTypeA[1].EstablishTypeCode AS CHARACTER), CAST(PCS.SiteAptNum AS CHARACTER));''.
The message broker detected an error whilst executing the given statement. An exception has been thrown to cut short the SQL program.
See the following messages for details of the error.
2007-11-28 14:25:56.612056 3580 RecoverableException BIP2934E: Error detected whilst executing the function or procedure ''CheckDuplicate2''.
The message broker detected an error whilst executing the function or procedure ''CheckDuplicate2''. An exception has been thrown to cut short the processing of the message.
See the following messages for details of the error.
2007-11-28 14:25:56.612064 3580 RecoverableException BIP2920E: Whilst attempting to obtain a procedure definition, the database reported that the procedure ''PCS.Database.dbo.Duplicate2'' does not exist, or cannot be accesed.
If the procedure ''PCS.Database.dbo.Duplicate2'' does not exist it will need to be created before it can be called. Otherwise ensure that the EXTERNAL NAME matches the name by which it is known to the database, including any necessary schema/package qualifiers.
Make sure that this procedure exists and you have authority to access it and have qualified the EXTERNAL NAME if necessary. then redeploy the message flow.

Any ideas why MSSQL SP are not comming up?
I am using: Websphere Connect JDBC DRIVE
JDBC drive class: com.ibm.websphere.jdbc.sqlserver.SQLServerDriver
Connection URL: jdbc:ibm:sqlserver://xx.xxx.xxx.xxx:xxxx;SelectMethod=cursor;DatabaseName=MyDB
with sqlserver_all2.jar

When creating connection to UDB, I do see all SP and all works fine when calling UDB SP.

Thanks,
Moshe