sql resuest/response adapter in Biztalk

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

sql resuest/response adapter in Biztalk

Author

Topic

prettypixie
Starting Member

8 Posts

Posted - 2007-05-15 : 12:26:02

Hi,

I am trying to test a stored procedure in my sql adapter with Biztalk 2004 that inserts records and then returns a xml page showing what records were inserted. I first tried with just inserting the records and that worked fine, but when I add the select statement and changed the sql adapter to a request/response I got the error:

Streaming not supported over multiple column result

Though when I test it in Query Analyser it works fine?

All I really want is some response to say that the procedure was successful, if it isn't possible the way I have tried can someone then suggest how I can do this? I would appreciate any help!!!

Thanks, Shazz

here is my stored procedure:

CREATE PROCEDURE sp_UpdateTestPricelist

(
--UpdateArticle table
@SupplierArticleGUID uniqueidentifier='{00000000-0000-0000-0000-000000000000}',
@SupplierGUID uniqueidentifier='{00000000-0000-0000-0000-000000000000}',
@SupplierArticleDescription nvarchar(150)='sugar',
@SupplierArticleNumber nvarchar(50)='4711',
@EAN nvarchar(50)='',
@VatKeyValue int=16,
--UpdateArticlePrice table
@SupplierArticlePriceGUID uniqueidentifier='{00000000-0000-0000-0000-000000000000}',
--@SupplierArticleGUID uniqueidentifier,
@ValidFrom datetime='',
@ValidTo datetime='',
@UnitOfMeasurement nvarchar(50)='kg',
@LotType uniqueidentifier='{00000000-0000-0000-0000-000000000000}',
@LotSize decimal(6,2)=1,
@GrossWeight decimal(6,2)=0.0,
@NetWeight decimal(6,2)=0.0,
@UnitPrimeCost decimal(6,2)=0.0,
@LotPrimeCost decimal(6,2)=0.0,
@Currency nvarchar(50)='EUR'
)
AS

DECLARE @ImportDate int

SET @ImportDate = year(getdate()) * 10000 + month(getdate()) * 100 + day(getdate())

DELETE FROM UpdateArticlePrice WHERE SupplierArticleGUID IN (SELECT SupplierArticleGUID FROM UpdateArticle WHERE ImportDate <> @ImportDate AND SupplierGUID = @SupplierGUID)
DELETE FROM UpdateArticle WHERE ImportDate <> @ImportDate AND SupplierGUID = @SupplierGUID

BEGIN
SET @SupplierArticleGUID = newid()
SET @SupplierArticlePriceGUID = newid()
INSERT INTO UpdateArticle
(
SupplierArticleGUID,
SupplierGUID,
SupplierArticleDescription,
SupplierArticleNumber,
EAN,
VatKeyValue,
ImportDate
)
VALUES
(
@SupplierArticleGUID,
@SupplierGUID,
@SupplierArticleDescription,
@SupplierArticleNumber,
@EAN,
@VatKeyValue,
@ImportDate
)

insert into UpdateArticlePrice
(
SupplierArticlePriceGUID,
SupplierArticleGUID,
ValidFrom,
ValidTo,
UnitOfMeasurement,
LotType,
LotSize,
GrossWeight,
NetWeight,
UnitPrimeCost,
LotPrimeCost,
Currency
)
VALUES
(
@SupplierArticlePriceGUID,
@SupplierArticleGUID,
@ValidFrom,
@ValidTo,
@UnitOfMeasurement,
@LotType,
@LotSize,
@GrossWeight,
@NetWeight,
@UnitPrimeCost,
@LotPrimeCost,
@Currency
)
END

SELECT * FROM UpdateArticle UA, UpdateArticlePrice UAP
WHERE UA.SupplierArticleGUID=UAP.SupplierArticleGUID
AND UA.SupplierGUID=@SupplierGUID
--for xml auto, xmldata
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources