Hi,
I am trying to use the BTS 2004 SQL Adapter to insert a row into a table, an
d return to the orchestration the new row's autoincremented Primary Key valu
e.  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 r
esponse Root contains only one element, called Success, which is of "xs:anyT
ype".  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\SQLAda
pter\New Customer Processing, which gives a responseRoot with a child elemen
t 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 essent
ially 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 SD
K.  I have tried rewriting my sproc in several different ways, but still can
not 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 - t
he default Namespace and 'b' prefix are missing.

Comparing the actual XSD, xsd schemas generated by my sql adapter lack the n
ame 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.mi
crosoft.com/BizTalk/2003" attributeFormDefault="unqualified" elementFormDefa
ult="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.BT
S2004.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 w
hen 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

[ Post a follow-up to this message ]

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: "examnotes" <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


|

[ Post a follow-up to this message ]

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

[ Post a follow-up to this message ]

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: "examnotes" <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
|

[ Post a follow-up to this message ]

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

[ Post a follow-up to this message ]

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 x
ml
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: "examnotes" <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
|

[ Post a follow-up to this message ]

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 NU
LL ,
[TransHandlCode] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS 
NOT NULL ,
[TransactionPaidAmt] [money] NULL CONSTRAINT [DF__835 Trans__Tra
ns__6E01572D] DEFAULT (0),
[CredDebitFlag] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS N
OT 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 NU
LL ,
[SenderAcctNo] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
[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 N
OT NULL ,
[CheckSource] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NO
T NULL ,
[PayerSupplCode2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_A
S 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 NU
LL ,
[PayerAddress] [varchar] (55) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
[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_A
S NULL ,
[AddPayerID] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
[PayerContactName] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_
AS NULL ,
[PayerComQualCode] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
[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:\S
QLData\MSSQL\Data\PracMan_Data.MDF' , SIZE = 8, FILEGROWTH = 10%) LOG ON (NA
ME = N'PracMan_Log', FILENAME = N'F:\SQLData\MSSQL\Data\PracMan_Log.LDF' , S
IZE = 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

[ Post a follow-up to this message ]

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

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] tb
l835Tran
FOR XML RAW, XMLDATA





GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

[ Post a follow-up to this message ]

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] tb
l835Tran

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 s
chema 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

[ Post a follow-up to this message ]

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: "examnotes" <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


|

[ Post a follow-up to this message ]