June 11, 2010 at 5:09 pm
I have two sql 2000 servers. On server1 I need to be able to call a remote stored procedure on server2 to fetch some data and insert it into a table. But the procedure on server2 needs to do some update/delete statements as part of the procedure. If I use "insert into server1.tbl1 exec server2.procedure", the DTC gets involved and the transaction becomes stuck requiring a server1 restart. (Without the server2 procedure update statements the procedure completes in milliseconds). Perhaps there is a DTC configuration problem. On the other hand, there is no real reason I need a distributed transaction, but as far as I know is no way to avoid this. I thought perhaps I could solve the DTC issue by passing the data in a varchar(8000) string of xml data via output parameters. (got idea from this link: http://www.sommarskog.se/share_data.html#XML ). But sql2000 does not have an XML data type. And I cannot find a way to get the results of "select for xml" as varchar. I actually considered manually constructing an XML string for each row, and then concatanating them via a tally table ( thx Jeff Moden ) but that seemed a stretch. Or if someone has another solution to passing data without invoking the DTC, that would be welcome.
Thanks!
June 11, 2010 at 9:08 pm
A simple DTS package would do it. Seed a SQL source with the stored proc, point it at the remote DB's table, and hten - schedule it.
Matter of fact - you should be able to use the import/export wizard to set att of that up for you using the wizard (and the sceudule as well).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 11, 2010 at 10:23 pm
randy rice (6/11/2010)
I have two sql 2000 servers. On server1 I need to be able to call a remote stored procedure on server2 to fetch some data and insert it into a table. But the procedure on server2 needs to do some update/delete statements as part of the procedure. If I use "insert into server1.tbl1 exec server2.procedure", the DTC gets involved and the transaction becomes stuck requiring a server1 restart. (Without the server2 procedure update statements the procedure completes in milliseconds). Perhaps there is a DTC configuration problem. On the other hand, there is no real reason I need a distributed transaction, but as far as I know is no way to avoid this. I thought perhaps I could solve the DTC issue by passing the data in a varchar(8000) string of xml data via output parameters. (got idea from this link: http://www.sommarskog.se/share_data.html#XML ). But sql2000 does not have an XML data type. And I cannot find a way to get the results of "select for xml" as varchar. I actually considered manually constructing an XML string for each row, and then concatanating them via a tally table ( thx Jeff Moden ) but that seemed a stretch. Or if someone has another solution to passing data without invoking the DTC, that would be welcome.Thanks!
I'm not 100% sure of all that you're trying to do but... You could concatenate what you want to pass into a delimited string and pass that. On the receiving side, just use a Tally table splitter in an iTVF (Inline Table Valued Function) to parse the delimited parameter.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 12, 2010 at 8:03 am
Matt - yes, that would work, and now that I think about it, for what I'm doing may be a more appropriate way than RPCs. Thanks.
Jeff - concatenating and then splitting was the approach I was working on, but it seemed an awfully round-about way of doing something that should be easier. And it just bugs me that there doesn't seem to be a way to pass the "for xml" output between procedures (in sql2000).
Randy
June 12, 2010 at 8:33 am
randy rice (6/12/2010)
Matt - yes, that would work, and now that I think about it, for what I'm doing may be a more appropriate way than RPCs. Thanks.Jeff - concatenating and then splitting was the approach I was working on, but it seemed an awfully round-about way of doing something that should be easier. And it just bugs me that there doesn't seem to be a way to pass the "for xml" output between procedures (in sql2000).
Randy
Actually, it's just about as easy as having an app concatenate XML and then shred it on the receiving end. It's kinder to the pipe, too.
If you're using a WHILE loop anywhere in T-SQL in this process, post back because there are some high speed and short-to-write methods to pull all of this off.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 12, 2010 at 10:16 am
In fact, Randy, here's the SQL Server 2000 compatible general purpose string splitter function that I used (took me a bit to find it) before I started using SQL Server 2005. As usual, the details for my code are in the code itself... (revision dates are estimated from old notes... for some reason, I never included them in the original code).
CREATE FUNCTION dbo.Split8K
/**************************************************************************************************
Purpose:
This function accepts a delimited string up to 7999 bytes in length and a delimiter string of
1 or more characters and returns the delimited list of elements as a numbered table of values.
Return:
Table containing the numeric position of each element (ItemNumber) as an INT and the string
value of each element (ItemValue).
Example Usage:
SELECT * FROM dbo.Split8K('1,10,100,1000,10000,100000,1000000', ',')
SELECT * FROM dbo.Split8K('1||10||100||1000||10000||100000||1000000', '||')
SELECT * FROM dbo.Split8K('1abcde10abcde100abcde1000abcde10000abcde100000abcde1000000' ,'abcde')
Programmer's Notes:
1. This code requires a Tally Table starting at either 0 or 1 with at least 8000 rows.
2. This code works in SQL Server 2000 and up.
3. If a leading delimiter is included in the string passed, the first element will be empty.
SELECT * FROM dbo.Split8K(',1,10,100,1000,10000,100000,1000000' ,',')
4. If a trailing delimiter is included in the string passed, the last element will be empty.
SELECT * FROM dbo.Split8K('1,10,100,1000,10000,100000,1000000,', ',')
5. If adjacent delimiters are included in the string passed, those elements will be empty.
SELECT * FROM dbo.Split8K('1,10,,1000,,100000,1000000', ',')
6. No white space trimming of elements is accomplished.
SELECT * FROM dbo.Split8K(',1,10,100, 1000,10000,100000,1000000' ,',')
7. Any combination of notes 3, 4, 5, and 6 may be used.
8. The string passed must not exceed 7999 bytes so a single byte common delimiter may be
concatenated to the string. NO ERROR CHECKING FOR STRING LENGTH IS DONE!
Revision History:
Rev 00 - 30 Dec 2006 - Jeff Moden - Initial creation and test.
Rev 01 - 10 May 2008 - Jeff Moden - Various optimizations added to reduce the number of
string concatenations and the math on t.N.
- Converted to "inline" Table Valued Function.
**************************************************************************************************/
(
@pString VARCHAR(7999),
@pDelimiter VARCHAR(7999)
)
RETURNS TABLE AS
RETURN
(
SELECT t.N - LEN(REPLACE(LEFT(CHAR(7) + s.String, t.N), CHAR(7), '')) AS ItemNumber,
SUBSTRING(s.String, t.N, CHARINDEX(CHAR(7), s.String + CHAR(7), t.N) -t.N) AS ItemValue
FROM dbo.Tally t
CROSS JOIN ( --=== Replace unknown delimiter with known delimiter (BELL character)
SELECT REPLACE(@pString, @pDelimiter, CHAR(7)) AS String
) AS s
WHERE t.N BETWEEN 1 AND LEN(s.String) + 1
AND SUBSTRING(CHAR(7) + s.String, t.N, 1) = CHAR(7)
)
GO
If you know that you'll always be passing the string with the same delimiter (a comma, for example) and you don't care about the position of elements, the code may be further optimized for performance...
CREATE FUNCTION dbo.Split8KCSV
/**************************************************************************************************
Purpose:
This function accepts a comma separated string up to 7999 bytes in length returns the delimited
list of elements as a table of values.
Return:
Table containing the string
value of each element (ItemValue).
Example Usage:
SELECT * FROM dbo.Split8KCSV('1,10,100,1000,10000,100000,1000000')
Programmer's Notes:
1. This code requires a Tally Table starting at either 0 or 1 with at least 8000 rows.
2. This code works in SQL Server 2000 and up.
3. If a leading delimiter is included in the string passed, the first element will be empty.
SELECT * FROM dbo.Split8KCSV(',1,10,100,1000,10000,100000,1000000')
4. If a trailing delimiter is included in the string passed, the last element will be empty.
SELECT * FROM dbo.Split8KCSV('1,10,100,1000,10000,100000,1000000,')
5. If adjacent delimiters are included in the string passed, those elements will be empty.
SELECT * FROM dbo.Split8KCSV('1,10,,1000,,100000,1000000')
6. No white space trimming of elements is accomplished.
SELECT * FROM dbo.Split8KCSV(',1,10,100, 1000,10000,100000,1000000')
7. Any combination of notes 3, 4, 5, and 6 may be used.
8. The string passed must not exceed 7999 bytes so a single byte common delimiter may be
concatenated to the string. NO ERROR CHECKING FOR STRING LENGTH IS DONE!
Revision History:
Rev 00 - 12 Jan 2006 - Jeff Moden - Initial creation and test.
Rev 01 - 10 May 2008 - Jeff Moden - Various optimizations added to reduce the number of
string concatenations and the math on t.N.
- Converted to "inline" Table Valued Function.
**************************************************************************************************/
(
@pString VARCHAR(7999)
)
RETURNS TABLE AS
RETURN
(
SELECT SUBSTRING(@pString, t.N, CHARINDEX(',', @pString + ',', t.N) -t.N) AS ItemValue
FROM dbo.Tally t
WHERE t.N BETWEEN 1 AND LEN(@pString) + 1
AND SUBSTRING(',' + @pString, t.N, 1) = ','
)
GO
--Jeff Moden
Change is inevitable... Change for the better is not.
June 12, 2010 at 4:57 pm
Jeff Moden (6/12/2010)
randy rice (6/12/2010)
Matt - yes, that would work, and now that I think about it, for what I'm doing may be a more appropriate way than RPCs. Thanks.Jeff - concatenating and then splitting was the approach I was working on, but it seemed an awfully round-about way of doing something that should be easier. And it just bugs me that there doesn't seem to be a way to pass the "for xml" output between procedures (in sql2000).
Randy
Actually, it's just about as easy as having an app concatenate XML and then shred it on the receiving end. It's kinder to the pipe, too.
If you're using a WHILE loop anywhere in T-SQL in this process, post back because there are some high speed and short-to-write methods to pull all of this off.
Completely agree about XML in this case. If the source and destination formats sre known, XMl is a waste of space ( or perhaps I should say "inefficient in its space usage while sending the message"). The DTS was to do a direct transfer (no conversion).
Randy - either method will work. I use Jeff's method a lot if isn't something that you plan on doing over and over again (or really big): I find it take a little longer to set pu, but can be really fast. Smaller items, or short-term deals I do using DTS.
Just post back with any question you might have.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 14, 2010 at 8:34 am
Thanks to both of you. I've used DTS extensively for on-demand data loading, just never thought about scheduling it for a "once a minute" type of transfer of sql data between servers. As for the delimited text approach, I'm going to try that out too. The examples seem to describe a situation where the set represents all like data (single column). Have you used this to pass table data with multiple columns. I've seen some articles about passing arrays of data, I'm sure I can dig that up and modify the example code.
June 14, 2010 at 4:25 pm
Jeff's got the whole swiss army knife on this approach:
http://www.sqlservercentral.com/articles/T-SQL/63003/[/url]
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply