BizTalk Server Applications Integration - SQL Adapter Generated Schema Question

Author

SQL Adapter Generated Schema Question

2004-04-19, 12:36 pm

Hi,
I am trying to use the BTS 2004 SQL Adapter to insert a row into a table, and return to the orchestration the new row's autoincremented Primary Key value. My stored procedure to do this looks like

this:

procMysproc(
@myCol1 varchar(10),
@myCol2 real
)

AS SET NOCOUNT ON

INSERT INTO [my table](
myCol1,
myCol2
)
VALUES(
@myCol1,
@myCol2
)

SELECT SCOPE_IDENTITY() AS TranID
FROM [my table]
FOR XML RAW, XMLDATA

When I use this to generate an XSD, I get the proper request Root, but the response Root contains only one element, called Success, which is of "xs:anyType". Furthermore, I cannot change this in the

properties section. Apparently, this is a return value?
The xsd I want to get would look the one in the SDK \Samples\Adapters\SQLAdapter\New Customer Processing, which gives a responseRoot with a child element of q1:rowType, which in turn has an

attribute of the desired datatype for the sproc output. I have studied the sproc used to generate this xsd, called SP_Get_Customer_Rating, which essentially use this sql:
select @Rating as Rating for xml raw, xmldata
When I generate an sql Adapter with this sproc, I get the same xsd as the SDK. I have tried rewriting my sproc in several different ways, but still cannot get the right xsd shape.

Also, and I believe definately related: in the schema node of my generated sql service schema, the imports dialog box is showing only the xs prefix - the default Namespace and 'b' prefix are missing.

Comparing the actual XSD, xsd schemas generated by my sql adapter lack the name space tag (xmlns) for the default namespace. For example,

My generated schema:
<?xml version="1.0" encoding="utf-16"?><xs:schema xmlns:b="http://schemas.microsoft.com/BizTalk/2003" attributeFormDefault="unqualified" elementFormDefault="qualified" targetNamespace="http://RemitPost" version="1.0"

xmlns:xs="http://www.w3.org/2001/XMLSchema">

SDK schema:
<?xml version="1.0" encoding="utf-16"?><xs:schema xmlns="http://Microsoft.BTS2004.Sample.SQL_Adapter" xmlns:b="http://schemas.microsoft.com/BizTalk/2003" attributeFormDefault="unqualified" elementFormDefault="qualified"

targetNamespace="http://Microsoft.BTS2004.Sample.SQL_Adapter" version="1.0" xmlns:xs="http://www.w3.org/2001/XMLSchema">

Where is my xmlns="http://RemitPost" ??????

I would really appreciate any help I can get with. I don't know if this is a bug, if I am somehow phrasing my sproc wrong, if I have missed something when setting up SQL Adapter, or what. Some

possibly important additional info:
I have applied BTS 2004 roll-up 1 (KB837168) and hotfix KB840760

Thanks in advance for any insight.
JT

larry franks

2004-04-19, 5:37 pm

Hi JT, I haven't tried this with BizTalk 2004 yet but I will see what I can
dig up on getting a return from an insert.

Larry Franks

This posting is provided "AS IS" with no warranties,and confers no rights.
Subscribe at
http://support.microsoft.com/defaul...msdn/nospam.asp
&SD=msdn
--------------------
| Thread-Topic: SQL Adapter Generated Schema Question
| thread-index: AcQmKOxtNnjpFdkySeuZcsm4CcoZ0Q==
| X-WN-Post: microsoft.public.biztalk.appintegration
| From: "=?Utf-8?B?SlQ=?=" <JTnospam@verizon.net>
| Subject: SQL Adapter Generated Schema Question
| Date: Mon, 19 Apr 2004 09:11:08 -0700
| Lines: 59
| Message-ID: <BD978C04-4D75-4FFD-AF74-BA11544A3BCB@microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.biztalk.appintegration
| Path: cpmsftngxa10.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.biztalk.appintegration:6538
| NNTP-Posting-Host: tk2msftcmty1.phx.gbl 10.40.1.180
| X-Tomcat-NG: microsoft.public.biztalk.appintegration
|
| Hi,
I am trying to use the BTS 2004 SQL Adapter to insert a row into a table,
and return to the orchestration the new row's autoincremented Primary Key
value. My stored procedure to do this looks like

this:

procMysproc(
@myCol1 varchar(10),
@myCol2 real
)

AS SET NOCOUNT ON

INSERT INTO [my table](
myCol1,
myCol2
)
VALUES(
@myCol1,
@myCol2
)

SELECT SCOPE_IDENTITY() AS TranID
FROM [my table]
FOR XML RAW, XMLDATA

When I use this to generate an XSD, I get the proper request Root, but the
response Root contains only one element, called Success, which is of
"xs:anyType". Furthermore, I cannot change this in the

properties section. Apparently, this is a return value?
The xsd I want to get would look the one in the SDK
\Samples\Adapters\SQLAdapter\New Customer Processing, which gives a
responseRoot with a child element of q1:rowType, which in turn has an

attribute of the desired datatype for the sproc output. I have studied the
sproc used to generate this xsd, called SP_Get_Customer_Rating, which
essentially use this sql:
select @Rating as Rating for xml raw, xmldata
When I generate an sql Adapter with this sproc, I get the same xsd as the
SDK. I have tried rewriting my sproc in several different ways, but still
cannot get the right xsd shape.

Also, and I believe definately related: in the schema node of my generated
sql service schema, the imports dialog box is showing only the xs prefix -
the default Namespace and 'b' prefix are missing.

Comparing the actual XSD, xsd schemas generated by my sql adapter lack the
name space tag (xmlns) for the default namespace. For example,

My generated schema:
<?xml version="1.0" encoding="utf-16"?><xs:schema
xmlns:b="http://schemas.microsoft.com/BizTalk/2003"
attributeFormDefault="unqualified" elementFormDefault="qualified"
targetNamespace="http://RemitPost" version="1.0"

xmlns:xs="http://www.w3.org/2001/XMLSchema">

SDK schema:
<?xml version="1.0" encoding="utf-16"?><xs:schema
xmlns="http://Microsoft.BTS2004.Sample.SQL_Adapter"
xmlns:b="http://schemas.microsoft.com/BizTalk/2003"
attributeFormDefault="unqualified" elementFormDefault="qualified"

targetNamespace="http://Microsoft.BTS2004.Sample.SQL_Adapter" version="1.0"
xmlns:xs="http://www.w3.org/2001/XMLSchema">

Where is my xmlns="http://RemitPost" ??????

I would really appreciate any help I can get with. I don't know if this is
a bug, if I am somehow phrasing my sproc wrong, if I have missed something
when setting up SQL Adapter, or what. Some

possibly important additional info:
I have applied BTS 2004 roll-up 1 (KB837168) and hotfix KB840760

Thanks in advance for any insight.
JT

|

2004-04-23, 11:36 am

Hi Larry,
Any luck on this issue?
Thanks.
JT

larry franks

2004-04-23, 11:36 am

Not yet, I've ran into the same issues you are seeing. I've sent a request
to the product group on this to see what further information I can find.

Larry Franks

This posting is provided "AS IS" with no warranties,and confers no rights.
Subscribe at
http://support.microsoft.com/defaul...msdn/nospam.asp
&SD=msdn
--------------------
| Thread-Topic: SQL Adapter Generated Schema Question
| thread-index: AcQpQ9C9ZhZSge2eQkG/ReORrTXvOQ==
| X-WN-Post: microsoft.public.biztalk.appintegration
| From: "=?Utf-8?B?SlQ=?=" <anonymous@discussions.microsoft.com>
| References: <BD978C04-4D75-4FFD-AF74-BA11544A3BCB@microsoft.com>
<XcDIQDlJEHA.4016@cpmsftngxa10.phx.gbl>
| Subject: RE: SQL Adapter Generated Schema Question
| Date: Fri, 23 Apr 2004 08:01:11 -0700
| Lines: 4
| Message-ID: <FEAF4439-BD4C-4AAB-9CB8-223C04C77A55@microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.biztalk.appintegration
| Path: cpmsftngxa10.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.biztalk.appintegration:6564
| NNTP-Posting-Host: tk2msftcmty1.phx.gbl 10.40.1.180
| X-Tomcat-NG: microsoft.public.biztalk.appintegration
|
| Hi Larry,
Any luck on this issue?
Thanks.
JT
|

2004-04-23, 4:35 pm

Thanks Larry. Between this and the custom functoids resources, I am going to have to start paying you directly.
JT

larry franks

2004-04-28, 12:36 pm

JT, can you forward me information on creating the database tables to
replicate the database you are using? Also I'm assuming the stored
proceedure posted in your initial post is the one you are using? I'd like
to try setting up your specific scenario here and see what I get.

I've been able to get this working using one of the labs as a base, in this
case the select is select * from Loans where LoanID = @newLoanID. In this
case the outbound schema is automatically generated for me.
Also I tried using select scope_identity() as TransID from [table] for xml
raw, xmldata and it came back with <root><loans TransID>

So it looks like it should work, we just need to figure out why it's not
working in your case. In my case I started with the SQL adapter SDK sample
as a base and modified the stored proc that does the insert to return rows.
When that worked I modified it to return the scope_identity trying to
match your stored proc, but that appears to have worked also.

Larry Franks

This posting is provided "AS IS" with no warranties,and confers no rights.
Subscribe at
http://support.microsoft.com/defaul...msdn/nospam.asp
&SD=msdn
--------------------
| Thread-Topic: SQL Adapter Generated Schema Question
| thread-index: AcQpbxvfvRQfcNxlSee3shrlgVUqRg==
| X-WN-Post: microsoft.public.biztalk.appintegration
| From: "=?Utf-8?B?SlQ=?=" <anonymous@discussions.microsoft.com>
| References: <BD978C04-4D75-4FFD-AF74-BA11544A3BCB@microsoft.com>
<XcDIQDlJEHA.4016@cpmsftngxa10.phx.gbl>
<FEAF4439-BD4C-4AAB-9CB8-223C04C77A55@microsoft.com>
<gIY6oYUKEHA.1460@cpmsftngxa10.phx.gbl>
| Subject: RE: SQL Adapter Generated Schema Question
| Date: Fri, 23 Apr 2004 13:11:05 -0700
| Lines: 2
| Message-ID: <6E15D833-792A-4160-8B3C-F2DB9DB58B4F@microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.biztalk.appintegration
| Path: cpmsftngxa10.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.biztalk.appintegration:6566
| NNTP-Posting-Host: tk2msftcmty1.phx.gbl 10.40.1.180
| X-Tomcat-NG: microsoft.public.biztalk.appintegration
|
| Thanks Larry. Between this and the custom functoids resources, I am
going to have to start paying you directly.
JT
|

2004-05-03, 12:37 pm

Hi Larry,
Here is the sql to generate the table I am trying to insert into:

CREATE TABLE [835 Transaction Level] (
[TransID] [int] IDENTITY (1, 1) NOT NULL ,
[TSCtlNo] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TransHandlCode] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TransactionPaidAmt] [money] NULL CONSTRAINT [DF__835 Trans__Trans__6E01572D] DEFAULT (0),
[CredDebitFlag] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PayMethodCode] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PayFormatCode] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SenderDFIQual] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SenderDFIID] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SenderAcctNo] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PayerId] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PayerSupplCode] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DepBankNo] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DepAcctNo] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CheckDate] [smalldatetime] NULL ,
[CheckEFTNmbr] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CheckSource] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PayerSupplCode2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RcvrID] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VersionIDCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CycleEndDate] [smalldatetime] NULL ,
[PayerName] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PayerIDCode] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PayerAddress] [varchar] (55) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PayerAddress2] [varchar] (55) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PayerCity] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PayerState] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PayerZip] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AddPayerIDQualifier] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AddPayerID] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PayerContactName] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PayerComQualCode] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PayerComNumber] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PayeeName] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_835 Transaction Level] PRIMARY KEY CLUSTERED
(
[CheckEFTNmbr],
[CheckSource]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO

Here is the sql to create the parent database:

CREATE DATABASE [PracMan] ON (NAME = N'PracMan_Data', FILENAME = N'F:\SQLData\MSSQL\Data\PracMan_Data.MDF' , SIZE = 8, FILEGROWTH = 10%) LOG ON (NAME = N'PracMan_Log', FILENAME = N'F:\SQLData\MSSQL\Data\PracMan_Log.LDF' , SIZE = 5, FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO

exec sp_dboption N'PracMan', N'autoclose', N'false'
GO

exec sp_dboption N'PracMan', N'bulkcopy', N'false'
GO

exec sp_dboption N'PracMan', N'trunc. log', N'false'
GO

exec sp_dboption N'PracMan', N'torn page detection', N'true'
GO

exec sp_dboption N'PracMan', N'read only', N'false'
GO

exec sp_dboption N'PracMan', N'dbo use', N'false'
GO

exec sp_dboption N'PracMan', N'single', N'false'
GO

exec sp_dboption N'PracMan', N'autoshrink', N'false'
GO

exec sp_dboption N'PracMan', N'ANSI null default', N'false'
GO

exec sp_dboption N'PracMan', N'recursive triggers', N'false'
GO

exec sp_dboption N'PracMan', N'ANSI nulls', N'false'
GO

exec sp_dboption N'PracMan', N'concat null yields null', N'false'
GO

exec sp_dboption N'PracMan', N'cursor close on commit', N'false'
GO

exec sp_dboption N'PracMan', N'default to local cursor', N'false'
GO

exec sp_dboption N'PracMan', N'quoted identifier', N'false'
GO

exec sp_dboption N'PracMan', N'ANSI warnings', N'false'
GO

exec sp_dboption N'PracMan', N'auto create statistics', N'true'
GO

exec sp_dboption N'PracMan', N'auto update statistics', N'true'
GO

if( (@@microsoftversion / power(2, 24) = 8) and (@@microsoftversion & 0xffff >= 724) )
exec sp_dboption N'PracMan', N'db chaining', N'false'
GO

Sorry this took so long - I've been on vacation. Please let me know if you need anything else.
John

2004-05-04, 1:37 pm

Hi Larry,
One more critical piece of SQL - the stored procedure I am using. I was reviewing this thread and saw that I had made up a fictious one in my first post.

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE [proc835TransactionLevelInsert](
@TSCtlNo [varchar](10),
@TransHandlCode [varchar](2),
@TransactionPaidAmt real,
@CredDebitFlag [varchar](1),
@PayMethodCode [char](3),
@PayFormatCode [char](3) = NULL,
@SenderDFIQual [char](2) = NULL,
@SenderDFIID [varchar](12) = NULL,
@SenderAcctNo [varchar](35) = NULL,
@PayerId [varchar](10) = NULL,
@PayerSupplCode [varchar](9) = NULL,
@DepBankNo [varchar](12) = NULL,
@DepAcctNo [varchar](35) = NULL,
@CheckDate varchar(20) = NULL,
@CheckEFTNmbr [varchar](30),
@CheckSource char(10),
@PayerSupplCode2 [varchar](30) = NULL,
@RcvrID [varchar](30) = NULL,
@VersionIDCode [varchar](10) = NULL,
@CycleEndDate varchar(20) = NULL,
@PayerName [varchar](60) = NULL,
@PayerIDCode [varchar](80) = NULL,
@PayerAddress [varchar](55) = NULL,
@PayerAddress2 [varchar](55) = NULL,
@PayerCity [varchar](30) = NULL,
@PayerState [varchar](2) = NULL,
@PayerZip [varchar](15) = NULL,
@AddPayerIDQualifier char(2) = NULL,
@AddPayerID [varchar](30) = NULL,
@PayerContactName [varchar](60) = NULL,
@PayerComQualCode char(2) = NULL,
@PayerComNumber [varchar](80) = NULL,
@PayeeName [varchar](60) = NULL
)
AS

SET NOCOUNT ON;

DECLARE @TPAmt money
SELECT @TPAmt = Convert(money, @TransactionPaidAmt)
DECLARE @CkDt smalldatetime
SELECT @CkDt = Convert(smalldatetime, @CheckDate)
DECLARE @CeDt smalldatetime
SELECT @CeDt = Convert(smalldatetime, @CycleEndDate)

INSERT INTO [835 Transaction Level](
[TSCtlNo],
[TransHandlCode],
[TransactionPaidAmt],
[CredDebitFlag],
[PayMethodCode],
[PayFormatCode],
[SenderDFIQual],
[SenderDFIID],
[SenderAcctNo],
[PayerId],
[PayerSupplCode],
[DepBankNo],
[DepAcctNo],
[CheckDate],
[CheckEFTNmbr],
[CheckSource],
[PayerSupplCode2],
[RcvrID],
[VersionIDCode],
[CycleEndDate],
[PayerName],
[PayerIDCode],
[PayerAddress],
[PayerAddress2],
[PayerCity],
[PayerState],
[PayerZip],
[AddPayerIDQualifier],
[AddPayerID],
[PayerContactName],
[PayerComQualCode],
[PayerComNumber],
[PayeeName]
)

VALUES(
@TSCtlNo,
@TransHandlCode,
@TPAmt,
@CredDebitFlag,
@PayMethodCode,
@PayFormatCode,
@SenderDFIQual,
@SenderDFIID,
@SenderAcctNo,
@PayerId,
@PayerSupplCode,
@DepBankNo,
@DepAcctNo,
@CkDt,
@CheckEFTNmbr,
@CheckSource,
@PayerSupplCode2,
@RcvrID,
@VersionIDCode,
@CeDt,
@PayerName,
@PayerIDCode,
@PayerAddress,
@PayerAddress2,
@PayerCity,
@PayerState,
@PayerZip,
@AddPayerIDQualifier,
@AddPayerID,
@PayerContactName,
@PayerComQualCode,
@PayerComNumber,
@PayeeName
)

SELECT SCOPE_IDENTITY() AS TransactionID FROM [835 Transaction Level] tbl835Tran
FOR XML RAW, XMLDATA

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

2004-05-05, 1:36 pm

Hi Larry,
Well, I finally got it to work!! The problem was in my insert sproc. Turns out that the statement

SELECT SCOPE_IDENTITY() AS TransactionID FROM [835 Transaction Level] tbl835Tran

returns the SCOPE_IDENTITY(), but repeats it for each row in the table. So, if last @@Identity is 25, and the are 20 rows in the table,
you get a column of twenty 25's.

I changed my sproc to retrieve just one row, and the generated SQL Service schema has the response Root structure I was looking for.

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

ALTER PROCEDURE [proc835TransactionLevelInsert](
@TSCtlNo [varchar](10),
@TransHandlCode [varchar](2),
@TransactionPaidAmt real,
@CredDebitFlag [varchar](1),
@PayMethodCode [char](3),
@PayFormatCode [char](3) = NULL,
@SenderDFIQual [char](2) = NULL,
@SenderDFIID [varchar](12) = NULL,
@SenderAcctNo [varchar](35) = NULL,
@PayerId [varchar](10) = NULL,
@PayerSupplCode [varchar](9) = NULL,
@DepBankNo [varchar](12) = NULL,
@DepAcctNo [varchar](35) = NULL,
@CheckDate varchar(20) = NULL,
@CheckEFTNmbr [varchar](30),
@CheckSource char(10),
@PayerSupplCode2 [varchar](30) = NULL,
@RcvrID [varchar](30) = NULL,
@VersionIDCode [varchar](10) = NULL,
@CycleEndDate varchar(20) = NULL,
@PayerName [varchar](60) = NULL,
@PayerIDCode [varchar](80) = NULL,
@PayerAddress [varchar](55) = NULL,
@PayerAddress2 [varchar](55) = NULL,
@PayerCity [varchar](30) = NULL,
@PayerState [varchar](2) = NULL,
@PayerZip [varchar](15) = NULL,
@AddPayerIDQualifier char(2) = NULL,
@AddPayerID [varchar](30) = NULL,
@PayerContactName [varchar](60) = NULL,
@PayerComQualCode char(2) = NULL,
@PayerComNumber [varchar](80) = NULL,
@PayeeName [varchar](60) = NULL
)
AS

SET NOCOUNT ON;

DECLARE @TranID INT

INSERT [835 Transaction Level](
[TSCtlNo],
[TransHandlCode],
[TransactionPaidAmt],
[CredDebitFlag],
[PayMethodCode],
[PayFormatCode],
[SenderDFIQual],
[SenderDFIID],
[SenderAcctNo],
[PayerId],
[PayerSupplCode],
[DepBankNo],
[DepAcctNo],
[CheckDate],
[CheckEFTNmbr],
[CheckSource],
[PayerSupplCode2],
[RcvrID],
[VersionIDCode],
[CycleEndDate],
[PayerName],
[PayerIDCode],
[PayerAddress],
[PayerAddress2],
[PayerCity],
[PayerState],
[PayerZip],
[AddPayerIDQualifier],
[AddPayerID],
[PayerContactName],
[PayerComQualCode],
[PayerComNumber],
[PayeeName]
)

VALUES(
@TSCtlNo,
@TransHandlCode,
Convert(money, @TransactionPaidAmt),
@CredDebitFlag,
@PayMethodCode,
@PayFormatCode,
@SenderDFIQual,
@SenderDFIID,
@SenderAcctNo,
@PayerId,
@PayerSupplCode,
@DepBankNo,
@DepAcctNo,
Convert(smalldatetime, @CheckDate),
@CheckEFTNmbr,
@CheckSource,
@PayerSupplCode2,
@RcvrID,
@VersionIDCode,
Convert(smalldatetime, @CycleEndDate),
@PayerName,
@PayerIDCode,
@PayerAddress,
@PayerAddress2,
@PayerCity,
@PayerState,
@PayerZip,
@AddPayerIDQualifier,
@AddPayerID,
@PayerContactName,
@PayerComQualCode,
@PayerComNumber,
@PayeeName
)
IF @@ROWCOUNT = 1 AND @@ERROR = 0 -- SUCCESS
BEGIN
SELECT @TranID = SCOPE_IDENTITY()
SELECT TranID AS TransactionID FROM [835 Transaction Level]
WHERE TranID = @TranID
FOR XML RAW, XMLDATA
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

larry franks

2004-05-05, 1:36 pm

Thanks JT, I've been able to set this up and reproduce it. I'll see what I
can find further on this.

Larry Franks

This posting is provided "AS IS" with no warranties,and confers no rights.
Subscribe at
http://support.microsoft.com/defaul...msdn/nospam.asp
&SD=msdn
--------------------
| Thread-Topic: SQL Adapter Generated Schema Question
| thread-index: AcQx/C6lXDjAgyzySSeRM0q4iVMLAA==
| X-WN-Post: microsoft.public.biztalk.appintegration
| From: "=?Utf-8?B?SlQ=?=" <JTnospam@verizon.net>
| References: <BD978C04-4D75-4FFD-AF74-BA11544A3BCB@microsoft.com>
<XcDIQDlJEHA.4016@cpmsftngxa10.phx.gbl>
<FEAF4439-BD4C-4AAB-9CB8-223C04C77A55@microsoft.com>
<gIY6oYUKEHA.1460@cpmsftngxa10.phx.gbl>
<6E15D833-792A-4160-8B3C-F2DB9DB58B4F@microsoft.com>
<doF8LhTLEHA.3064@cpmsftngxa10.phx.gbl>
<5B69F01E-A0E8-4928-A4B8-77008A88BBA6@microsoft.com>
| Subject: RE: SQL Adapter Generated Schema Question
| Date: Tue, 4 May 2004 10:21:05 -0700
| Lines: 141
| Message-ID: <7725DA96-6E1D-4120-B909-147A2CA06E94@microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.biztalk.appintegration
| Path: cpmsftngxa10.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.biztalk.appintegration:6629
| NNTP-Posting-Host: tk2msftcmty1.phx.gbl 10.40.1.180
| X-Tomcat-NG: microsoft.public.biztalk.appintegration
|
| Hi Larry,
One more critical piece of SQL - the stored procedure I am using. I was
reviewing this thread and saw that I had made up a fictious one in my first
post.

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE [proc835TransactionLevelInsert](
@TSCtlNo [varchar](10),
@TransHandlCode [varchar](2),
@TransactionPaidAmt real,
@CredDebitFlag [varchar](1),
@PayMethodCode [char](3),
@PayFormatCode [char](3) = NULL,
@SenderDFIQual [char](2) = NULL,
@SenderDFIID [varchar](12) = NULL,
@SenderAcctNo [varchar](35) = NULL,
@PayerId [varchar](10) = NULL,
@PayerSupplCode [varchar](9) = NULL,
@DepBankNo [varchar](12) = NULL,
@DepAcctNo [varchar](35) = NULL,
@CheckDate varchar(20) = NULL,
@CheckEFTNmbr [varchar](30),
@CheckSource char(10),
@PayerSupplCode2 [varchar](30) = NULL,
@RcvrID [varchar](30) = NULL,
@VersionIDCode [varchar](10) = NULL,
@CycleEndDate varchar(20) = NULL,
@PayerName [varchar](60) = NULL,
@PayerIDCode [varchar](80) = NULL,
@PayerAddress [varchar](55) = NULL,
@PayerAddress2 [varchar](55) = NULL,
@PayerCity [varchar](30) = NULL,
@PayerState [varchar](2) = NULL,
@PayerZip [varchar](15) = NULL,
@AddPayerIDQualifier char(2) = NULL,
@AddPayerID [varchar](30) = NULL,
@PayerContactName [varchar](60) = NULL,
@PayerComQualCode char(2) = NULL,
@PayerComNumber [varchar](80) = NULL,
@PayeeName [varchar](60) = NULL
)
AS

SET NOCOUNT ON;

DECLARE @TPAmt money
SELECT @TPAmt = Convert(money, @TransactionPaidAmt)
DECLARE @CkDt smalldatetime
SELECT @CkDt = Convert(smalldatetime, @CheckDate)
DECLARE @CeDt smalldatetime
SELECT @CeDt = Convert(smalldatetime, @CycleEndDate)

INSERT INTO [835 Transaction Level](
[TSCtlNo],
[TransHandlCode],
[TransactionPaidAmt],
[CredDebitFlag],
[PayMethodCode],
[PayFormatCode],
[SenderDFIQual],
[SenderDFIID],
[SenderAcctNo],
[PayerId],
[PayerSupplCode],
[DepBankNo],
[DepAcctNo],
[CheckDate],
[CheckEFTNmbr],
[CheckSource],
[PayerSupplCode2],
[RcvrID],
[VersionIDCode],
[CycleEndDate],
[PayerName],
[PayerIDCode],
[PayerAddress],
[PayerAddress2],
[PayerCity],
[PayerState],
[PayerZip],
[AddPayerIDQualifier],
[AddPayerID],
[PayerContactName],
[PayerComQualCode],
[PayerComNumber],
[PayeeName]
)

VALUES(
@TSCtlNo,
@TransHandlCode,
@TPAmt,
@CredDebitFlag,
@PayMethodCode,
@PayFormatCode,
@SenderDFIQual,
@SenderDFIID,
@SenderAcctNo,
@PayerId,
@PayerSupplCode,
@DepBankNo,
@DepAcctNo,
@CkDt,
@CheckEFTNmbr,
@CheckSource,
@PayerSupplCode2,
@RcvrID,
@VersionIDCode,
@CeDt,
@PayerName,
@PayerIDCode,
@PayerAddress,
@PayerAddress2,
@PayerCity,
@PayerState,
@PayerZip,
@AddPayerIDQualifier,
@AddPayerID,
@PayerContactName,
@PayerComQualCode,
@PayerComNumber,
@PayeeName
)

SELECT SCOPE_IDENTITY() AS TransactionID FROM [835 Transaction Level]
tbl835Tran
FOR XML RAW, XMLDATA

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

|

larry franks

2004-05-05, 1:36 pm

Hey, think I may have figured it out. Not certain yet but the only
difference I see between what your stored proc returns, and what mine
returns, is the table name. In your case the table name is "835
transaction level" and in my case it's just "Loans". The first element in
the return schema appears to be named after this table. I'm thinking that
since your table has spaces we are falling through some sort of test since
we can't create an element that has spaces in it, and we default to this
Success element.

I'll have to test this out and see if I can find a way around it if this is
what's happening.

Larry Franks

This posting is provided "AS IS" with no warranties,and confers no rights.
Subscribe at
http://support.microsoft.com/defaul...msdn/nospam.asp
&SD=msdn
--------------------
| Thread-Topic: SQL Adapter Generated Schema Question
| thread-index: AcQx/C6lXDjAgyzySSeRM0q4iVMLAA==
| X-WN-Post: microsoft.public.biztalk.appintegration
| From: "=?Utf-8?B?SlQ=?=" <JTnospam@verizon.net>
| References: <BD978C04-4D75-4FFD-AF74-BA11544A3BCB@microsoft.com>
<XcDIQDlJEHA.4016@cpmsftngxa10.phx.gbl>
<FEAF4439-BD4C-4AAB-9CB8-223C04C77A55@microsoft.com>
<gIY6oYUKEHA.1460@cpmsftngxa10.phx.gbl>
<6E15D833-792A-4160-8B3C-F2DB9DB58B4F@microsoft.com>
<doF8LhTLEHA.3064@cpmsftngxa10.phx.gbl>
<5B69F01E-A0E8-4928-A4B8-77008A88BBA6@microsoft.com>
| Subject: RE: SQL Adapter Generated Schema Question
| Date: Tue, 4 May 2004 10:21:05 -0700
| Lines: 141
| Message-ID: <7725DA96-6E1D-4120-B909-147A2CA06E94@microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.biztalk.appintegration
| Path: cpmsftngxa10.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.biztalk.appintegration:6629
| NNTP-Posting-Host: tk2msftcmty1.phx.gbl 10.40.1.180
| X-Tomcat-NG: microsoft.public.biztalk.appintegration
|
| Hi Larry,
One more critical piece of SQL - the stored procedure I am using. I was
reviewing this thread and saw that I had made up a fictious one in my first
post.

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE [proc835TransactionLevelInsert](
@TSCtlNo [varchar](10),
@TransHandlCode [varchar](2),
@TransactionPaidAmt real,
@CredDebitFlag [varchar](1),
@PayMethodCode [char](3),
@PayFormatCode [char](3) = NULL,
@SenderDFIQual [char](2) = NULL,
@SenderDFIID [varchar](12) = NULL,
@SenderAcctNo [varchar](35) = NULL,
@PayerId [varchar](10) = NULL,
@PayerSupplCode [varchar](9) = NULL,
@DepBankNo [varchar](12) = NULL,
@DepAcctNo [varchar](35) = NULL,
@CheckDate varchar(20) = NULL,
@CheckEFTNmbr [varchar](30),
@CheckSource char(10),
@PayerSupplCode2 [varchar](30) = NULL,
@RcvrID [varchar](30) = NULL,
@VersionIDCode [varchar](10) = NULL,
@CycleEndDate varchar(20) = NULL,
@PayerName [varchar](60) = NULL,
@PayerIDCode [varchar](80) = NULL,
@PayerAddress [varchar](55) = NULL,
@PayerAddress2 [varchar](55) = NULL,
@PayerCity [varchar](30) = NULL,
@PayerState [varchar](2) = NULL,
@PayerZip [varchar](15) = NULL,
@AddPayerIDQualifier char(2) = NULL,
@AddPayerID [varchar](30) = NULL,
@PayerContactName [varchar](60) = NULL,
@PayerComQualCode char(2) = NULL,
@PayerComNumber [varchar](80) = NULL,
@PayeeName [varchar](60) = NULL
)
AS

SET NOCOUNT ON;

DECLARE @TPAmt money
SELECT @TPAmt = Convert(money, @TransactionPaidAmt)
DECLARE @CkDt smalldatetime
SELECT @CkDt = Convert(smalldatetime, @CheckDate)
DECLARE @CeDt smalldatetime
SELECT @CeDt = Convert(smalldatetime, @CycleEndDate)

INSERT INTO [835 Transaction Level](
[TSCtlNo],
[TransHandlCode],
[TransactionPaidAmt],
[CredDebitFlag],
[PayMethodCode],
[PayFormatCode],
[SenderDFIQual],
[SenderDFIID],
[SenderAcctNo],
[PayerId],
[PayerSupplCode],
[DepBankNo],
[DepAcctNo],
[CheckDate],
[CheckEFTNmbr],
[CheckSource],
[PayerSupplCode2],
[RcvrID],
[VersionIDCode],
[CycleEndDate],
[PayerName],
[PayerIDCode],
[PayerAddress],
[PayerAddress2],
[PayerCity],
[PayerState],
[PayerZip],
[AddPayerIDQualifier],
[AddPayerID],
[PayerContactName],
[PayerComQualCode],
[PayerComNumber],
[PayeeName]
)

VALUES(
@TSCtlNo,
@TransHandlCode,
@TPAmt,
@CredDebitFlag,
@PayMethodCode,
@PayFormatCode,
@SenderDFIQual,
@SenderDFIID,
@SenderAcctNo,
@PayerId,
@PayerSupplCode,
@DepBankNo,
@DepAcctNo,
@CkDt,
@CheckEFTNmbr,
@CheckSource,
@PayerSupplCode2,
@RcvrID,
@VersionIDCode,
@CeDt,
@PayerName,
@PayerIDCode,
@PayerAddress,
@PayerAddress2,
@PayerCity,
@PayerState,
@PayerZip,
@AddPayerIDQualifier,
@AddPayerID,
@PayerContactName,
@PayerComQualCode,
@PayerComNumber,
@PayeeName
)

SELECT SCOPE_IDENTITY() AS TransactionID FROM [835 Transaction Level]
tbl835Tran
FOR XML RAW, XMLDATA

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

|

2004-05-05, 3:36 pm

Hi Larry,
I thought that too, at first. I read the documentation about that issue in the on-line help. However, my table name is bracketed, which should cover me. Anyway, if you look at my other post from today, you'll see that the problem was with the sproc res
ult set. I think I will post to SQL newsgroup to try to understand this further.
John

2004-05-05, 5:36 pm

Hi again Larry,
I am feeling sort of stupid, but I am learning! Here is some feedback I got from a post to SQL Server newsgroup:

JT,

SCOPE_IDENTITY does not have any correlation to a table. That is why you can do below:
SELECT @TranID = SCOPE_IDENTITY()

Below statement:
SELECT SCOPE_IDENTITY() AS TransactionID FROM myTable

Is similar to:
SELECT 'Hello' AS TransactionID FROM myTable

As you have no WHERE clause, one row is returned for each row in the table. But the column returned in the
select list does not pick the value form any column in the table, seen from the SELECT statement's
perspective, it is a constant.
--
Tibor Karaszi, SQL Server MVP

larry franks

2004-05-05, 5:36 pm

Thanks for the update John!

Larry Franks

This posting is provided "AS IS" with no warranties,and confers no rights.
Subscribe at
http://support.microsoft.com/defaul...msdn/nospam.asp
&SD=msdn
--------------------
| Thread-Topic: SQL Adapter Generated Schema Question
| thread-index: AcQy1Lq+VfsOvHiCRraZMW7Tfs6t5w==
| X-WN-Post: microsoft.public.biztalk.appintegration
| From: "=?Utf-8?B?SlQ=?=" <JTnospam@verizon.net>
| References: <BD978C04-4D75-4FFD-AF74-BA11544A3BCB@microsoft.com>
<XcDIQDlJEHA.4016@cpmsftngxa10.phx.gbl>
<FEAF4439-BD4C-4AAB-9CB8-223C04C77A55@microsoft.com>
<gIY6oYUKEHA.1460@cpmsftngxa10.phx.gbl>
<6E15D833-792A-4160-8B3C-F2DB9DB58B4F@microsoft.com>
<doF8LhTLEHA.3064@cpmsftngxa10.phx.gbl>
<5B69F01E-A0E8-4928-A4B8-77008A88BBA6@microsoft.com>
<7725DA96-6E1D-4120-B909-147A2CA06E94@microsoft.com>
<quuUuZsMEHA.3064@cpmsftngxa10.phx.gbl>
| Subject: RE: SQL Adapter Generated Schema Question
| Date: Wed, 5 May 2004 12:11:12 -0700
| Lines: 3
| Message-ID: <00E0DC08-1A2B-42CA-A1A9-58C17DDAD140@microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.biztalk.appintegration
| Path: cpmsftngxa10.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.biztalk.appintegration:6640
| NNTP-Posting-Host: tk2msftcmty1.phx.gbl 10.40.1.180
| X-Tomcat-NG: microsoft.public.biztalk.appintegration
|
| Hi Larry,
I thought that too, at first. I read the documentation about that issue in
the on-line help. However, my table name is bracketed, which should cover
me. Anyway, if you look at my other post from today, you'll see that the
problem was with the sproc result set. I think I will post to SQL
newsgroup to try to understand this further.
John
|

2004-05-06, 10:51 am

You bet. Sorry for the long wild goose chase.
John

EastOx

2004-07-19, 5:52 pm

Hi, JT:
You SP using For XML RAW, my SQL Adapter does not work with it. Any fixing you do with your Biztalk Server 2004? Thanks.

plugin from Microsoft. We are now moving to 2004 version. >>Anybody know how to fix it for the new version.[vbcol=seagreen]

"JT" wrote:
[vbcol=seagreen]
> Hi Larry,
> Well, I finally got it to work!! The problem was in my insert sproc. Turns out that the statement
>
> SELECT SCOPE_IDENTITY() AS TransactionID FROM [835 Transaction Level] tbl835Tran
>
> returns the SCOPE_IDENTITY(), but repeats it for each row in the table. So, if last @@Identity is 25, and the are 20 rows in the table,
> you get a column of twenty 25's.
>
> I changed my sproc to retrieve just one row, and the generated SQL Service schema has the response Root structure I was looking for.
>
>
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS OFF
> GO
>
>
>
> ALTER PROCEDURE [proc835TransactionLevelInsert](
> @TSCtlNo [varchar](10),
> @TransHandlCode [varchar](2),
> @TransactionPaidAmt real,
> @CredDebitFlag [varchar](1),
> @PayMethodCode [char](3),
> @PayFormatCode [char](3) = NULL,
> @SenderDFIQual [char](2) = NULL,
> @SenderDFIID [varchar](12) = NULL,
> @SenderAcctNo [varchar](35) = NULL,
> @PayerId [varchar](10) = NULL,
> @PayerSupplCode [varchar](9) = NULL,
> @DepBankNo [varchar](12) = NULL,
> @DepAcctNo [varchar](35) = NULL,
> @CheckDate varchar(20) = NULL,
> @CheckEFTNmbr [varchar](30),
> @CheckSource char(10),
> @PayerSupplCode2 [varchar](30) = NULL,
> @RcvrID [varchar](30) = NULL,
> @VersionIDCode [varchar](10) = NULL,
> @CycleEndDate varchar(20) = NULL,
> @PayerName [varchar](60) = NULL,
> @PayerIDCode [varchar](80) = NULL,
> @PayerAddress [varchar](55) = NULL,
> @PayerAddress2 [varchar](55) = NULL,
> @PayerCity [varchar](30) = NULL,
> @PayerState [varchar](2) = NULL,
> @PayerZip [varchar](15) = NULL,
> @AddPayerIDQualifier char(2) = NULL,
> @AddPayerID [varchar](30) = NULL,
> @PayerContactName [varchar](60) = NULL,
> @PayerComQualCode char(2) = NULL,
> @PayerComNumber [varchar](80) = NULL,
> @PayeeName [varchar](60) = NULL
> )
> AS
>
> SET NOCOUNT ON;
>
> DECLARE @TranID INT
>
> INSERT [835 Transaction Level](
> [TSCtlNo],
> [TransHandlCode],
> [TransactionPaidAmt],
> [CredDebitFlag],
> [PayMethodCode],
> [PayFormatCode],
> [SenderDFIQual],
> [SenderDFIID],
> [SenderAcctNo],
> [PayerId],
> [PayerSupplCode],
> [DepBankNo],
> [DepAcctNo],
> [CheckDate],
> [CheckEFTNmbr],
> [CheckSource],
> [PayerSupplCode2],
> [RcvrID],
> [VersionIDCode],
> [CycleEndDate],
> [PayerName],
> [PayerIDCode],
> [PayerAddress],
> [PayerAddress2],
> [PayerCity],
> [PayerState],
> [PayerZip],
> [AddPayerIDQualifier],
> [AddPayerID],
> [PayerContactName],
> [PayerComQualCode],
> [PayerComNumber],
> [PayeeName]
> )
>
> VALUES(
> @TSCtlNo,
> @TransHandlCode,
> Convert(money, @TransactionPaidAmt),
> @CredDebitFlag,
> @PayMethodCode,
> @PayFormatCode,
> @SenderDFIQual,
> @SenderDFIID,
> @SenderAcctNo,
> @PayerId,
> @PayerSupplCode,
> @DepBankNo,
> @DepAcctNo,
> Convert(smalldatetime, @CheckDate),
> @CheckEFTNmbr,
> @CheckSource,
> @PayerSupplCode2,
> @RcvrID,
> @VersionIDCode,
> Convert(smalldatetime, @CycleEndDate),
> @PayerName,
> @PayerIDCode,
> @PayerAddress,
> @PayerAddress2,
> @PayerCity,
> @PayerState,
> @PayerZip,
> @AddPayerIDQualifier,
> @AddPayerID,
> @PayerContactName,
> @PayerComQualCode,
> @PayerComNumber,
> @PayeeName
> )
> IF @@ROWCOUNT = 1 AND @@ERROR = 0 -- SUCCESS
> BEGIN
> SELECT @TranID = SCOPE_IDENTITY()
> SELECT TranID AS TransactionID FROM [835 Transaction Level]
> WHERE TranID = @TranID
> FOR XML RAW, XMLDATA
> END
>
>
>
>
>
>
>
>
>
>
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
>