Re: Communicate XML between SP's

On Mar 4, 2:59 pm, prabh...@xxxxxxxxx wrote:
On Feb 25, 9:54 am, "Lauren Quantrell" <laurenquantr...@xxxxxxxxxxx>
wrote:





On Feb 23, 8:42 am, Merijn <Mer...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

Hi,
I have two databases and need to transfer data between them. The problem is
that I'm facing the 8000 characters limitation of a varchar and I cannot use
a text declaration within the trigger/SP itself. Therefore I had the
following idea:

SP 1:
CREATE PROCEDURE [DBO].[testXMLSelect] AS
declare @idoc as int
--Load and parse the XML document in Memory
EXEC sp_xml_preparedocument @idoc OUTPUT select * from testtext where id=3
for xml auto
exec testXMLInsert @idoc
--now clear the XML document from memory
EXEC sp_xml_removedocument @idoc
GO

SP 2
CREATE PROCEDURE [dbo].[testXMLInsert]
@idoc as int
AS
insert into testtext (test)
select test from openxml(@idoc,'/testtext',0) WITH (id int, test text)
GO

My Idea was to parse the handle to the XML document in Memory from one to
the other SP, and later on to the other database (same server). Does anyone
have an idea how to solve this?
Regards,
Merijn

I have a requirment to share data between two databases and I'm hoping
you could outline to me the steps you have taken to get me started. I
won't run into the varchar limit in my application.
Thanks,
lq

Now that you mentioned the Server is same, but have 2 seperate DBs,
how about using DB Name of the 1st DB in your QUERY of 2nd DB;
something like this

In DB2, you have your query as

Create table #t (MyXMLColumn XML )
Insert INTO #t (MyXMLColumn)
Select <WhatEverXMLColumnNameYouWant> From
<DBName>.<ObjectOwner>.testtext where id=3- Hide quoted text -

- Show quoted text -


In my case it's two different servers I cave to communicate using SOAP/
XML to send data from the SQL Server 2000 database to the remote
server.

.

Relevant Pages

  • Re: DISCOVER_XML_METADATA
    ... expansion of ASSL XML returned by the server. ... You could do ExpandObject for the server in step 1. ... you could request ExpandObject for that database -- this is step ... all cubes and nothing else. ...
    (microsoft.public.sqlserver.olap)
  • Is FOR XML EXPLICIT still an accepted technique?
    ... I have inherited an ASP IIS site attached to a SQL ... Server 2000 database. ... I am new at using XML and have done considerable reading. ...
    (microsoft.public.sqlserver.xml)
  • Re: XML->Dataset->Database
    ... latest configuration information and the server contains completed survey ... special database tables to control the flow of information, and using XML ... based on what needs to be sent to the client, ...
    (microsoft.public.dotnet.general)
  • VB.Net Web Services?
    ... database to intigrate with another company's database via web services ... using WSDL, SOAP and XML. ... application sitting on a server? ... external company and wrap it in a SOAP envelope. ...
    (microsoft.public.dotnet.general)
  • Create SharePoint Portal failed.
    ... One mentioned ensuring that SQL Server uses a case ... 13:55:40 Service database server is 'USDC-JOHRIV'. ... Update dbo.propertylist set DisplayName = N'Last name' ...
    (microsoft.public.sharepoint.portalserver)
Loading