- This topic has 5 replies, 1 voice, and was last updated 9 years, 1 month ago by
.
-
Posts
-
-
Hi Folks,
I’m very new to biztalk, but have a background in coding, writing SQL, XML, C#, Java, and many more.
I’ve been tasked with creating an orchestration which reads information from stored procedures on a sybase database. I’ve written and tested a stored procedure, which reads new records from a table, then marks a read flag, and works well. I’ve setup the ODBC Adapter & have tested it running normal SQL statements, and again, this works as expected. Now I’m trying to use the ODBC adapter with the stored procedure, and am having a few issues.
When using the test harness to run the procedure, I receive the error “Your tested failed: Receive Port query processing failed: ERROR [07001][DataDirect][ODBC Sybase Wire Protocol driver]Value has not been specified for parameter 1.”.
As my procedure takes no parameters, I assumed this must be something which is passed by biztalk when using the generated schema in a receive port. I therefore tested it in an orchestration, to find the following error in the application event log: “GetBatchOfMessagesAndSubmit Error: Receive Port query processing failed: ERROR [42000] [DataDirect][ODBC Sybase Wire Protocol driver][SQL Server]Incorrect syntax near ‘.’.”, coming from the ODBCAdapter.
The adapter I’m using comes from here: http://www.twoconnect.com/Default.aspx?tabid=269 (as the gotdotnet version no longer seems available). This is reported to work with Sybase 15, though there’s no mention of it being tested against v12.
Has anyone got any ideas on what I’ve done wrong, or if this is an issue with the adapter itself? If this is an adapter issue, do you have any suggestions on how I could talk to the sybase database stored procedures (e.g. is there a different ODBC adapter / could they be called using linked servers on SQL Server, then hooking the normal SQL adapter up to that)?
Thanks in advance,
JB
-
Stored Procedure
Create Procedure dbo.p_TEST_BIZTALK_SELECT_JB
As
declare @amt_key int, @Datetime_column datetime
Begin
set rowcount 1
select @amt_key = amt_key
, @Datetime_column = Datetime_column
from TEST_BIZTALK
where isnull(char_column,”) != ‘Done’
set rowcount 0
begin transaction
update TEST_BIZTALK
set char_column = ‘Done’
where amt_key = @amt_key
select @amt_key
, @Datetime_column
commit transaction
end
Input Schema
<?xml version=”1.0″ encoding=”utf-16″?>
<xs:schema xmlns:msbtsodbc=”http://schemas.microsoft.com/BizTalk/2003″ attributeFormDefault=”unqualified” elementFormDefault=”qualified” targetNamespace=”http://test/ODBCQUERY” xmlns:xs=”http://www.w3.org/2001/XMLSchema”>
<xs:annotation>
<xs:appinfo>
<msbtsodbc:SQLCMD>dbo.p_TEST_BIZTALK_SELECT_JB</msbtsodbc:SQLCMD>
<msbtsodbc:ODBCCMD>CALL dbo.p_TEST_BIZTALK_SELECT_JB</msbtsodbc:ODBCCMD>
<msbtsodbc:ResponseRootName>test</msbtsodbc:ResponseRootName>
<msbtsodbc:ResponseNS>http://test</msbtsodbc:ResponseNS>
</xs:appinfo>
</xs:annotation>
<xs:element name=”ODBCQUERY”>
<xs:complexType>
<xs:sequence>
<xs:element minOccurs=”1″ maxOccurs=”unbounded” name=”QueryParameters”>
<xs:complexType>
<xs:attribute name=”RETURN_VALUE” type=”xs:int”>
<xs:annotation>
<xs:appinfo>
<msbtsdbc:ParamType xmlns:msbtsdbc=”http://schemas.microsoft.com/BizTalk/2003″>Int</msbtsdbc:ParamType>
<msbtsodbc:ParamDir>ReturnValue</msbtsodbc:ParamDir>
<msbtsodbc:ParamSize>10</msbtsodbc:ParamSize>
</xs:appinfo>
</xs:annotation>
</xs:attribute>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
-
Output Schema
<?xml version=”1.0″ encoding=”utf-16″?>
<xs:schema xmlns:msdata=”urn:schemas-microsoft-com:xml-msdata” xmlns=”” targetNamespace=”http://test/test” id=”testSchema” xmlns:xs=”http://www.w3.org/2001/XMLSchema”>
<xs:annotation>
<xs:appinfo>
<msbtsodbc:SQLCMD xmlns:msbtsodbc=”http://schemas.microsoft.com/BizTalk/2003″>dbo.p_TEST_BIZTALK_SELECT_JB</msbtsodbc:SQLCMD>
<msbtsodbc:ODBCCMD xmlns:msbtsodbc=”http://schemas.microsoft.com/BizTalk/2003″>{? =CALL dbo.p_TEST_BIZTALK_SELECT_JB}</msbtsodbc:ODBCCMD>
<msbtsodbc:ResponseRootName xmlns:msbtsodbc=”http://schemas.microsoft.com/BizTalk/2003″>test</msbtsodbc:ResponseRootName>
<msbtsodbc:ResponseNS xmlns:msbtsodbc=”http://schemas.microsoft.com/BizTalk/2003″>http://test</msbtsodbc:ResponseNS>
</xs:appinfo>
</xs:annotation>
<xs:element msdata:IsDataSet=”true” msdata:UseCurrentLocale=”true” name=”test”>
<xs:complexType>
<xs:choice minOccurs=”0″ maxOccurs=”unbounded”>
<xs:element name=”Table”>
<xs:complexType>
<xs:sequence>
<xs:element minOccurs=”0″ name=”Column1″ type=”xs:int” />
<xs:element minOccurs=”0″ name=”Column2″ type=”xs:dateTime” />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name=”OutParameters”>
<xs:complexType>
<xs:attribute name=”RETURN_VALUE” type=”xs:int” />
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
-
Update
I’ve now modified the stored procedure to ensure it returns column names:
select @amt_key as amt_key
, @Datetime_column as Datetime_column
After doing this, I recreated the schemas & redeployed the solution. However, I’m still receiving the “Value has not been specified for parameter 1” error.
-
Resolution
I’ve found a workaround, which I thought I’d post for anyone following this thread.
I gave up with the ODBC Adapter, and turned instead to the standard SQL Adapter. I pointed this at some business functions in SQL Server, which called the Sybase business functions via SQL’s Linked Server functionality. To successfully link to Sybase, I created an ODBC connection to it on the SQL database server.
Steps
- Install Sybase driver on SQL Server
- Create & test an ODBC connection to your Sybase database
- Create the linked table in SQL Server
- exec sp_addlinkedserver @server=’linked_server_name‘, @srvproduct = ‘Sybase’, @provider = ‘MSDASQL’, @datasrc = ‘ODBC_Data_Source_Name‘
- exec sp_addlinkedsrvlogin @useself=’false‘, @rmtsrvname = ‘linked_server_name‘, @rmtuser = ‘username‘, @rmtpassword = ‘password‘
- Enable Remote Procedure Calls
- Right click on your linked server (found in SSMS under “Server Objects\Linked Servers”
- Select Properties
- Go to the “Server Options” page
- Change both “RPC” and “RPC Out” to True
- Test standard SQL over the linked server
- select * from linked_server_name.database_name.dbo.tablename;
- Test stored procedures over the linked server
- execute (‘database_name.dbo.stored_procedure_name’) at linked_server_name;
- You can now wrap any calls in your own SQL Server stored procedures, and access these from biztalk in the standard way.
* I believe this solution should work with any database with an ODBC driver. However, it’s always worth testing first, just to be sure :).
-
-
- The forum ‘BizTalk 2004 – BizTalk 2010’ is closed to new topics and replies.