So, as a follow-up, I implemented the "worst-case" solution I had described above, and it turned the process from one that could take as much as 10 hours, down to...

Yeah that solution is also pretty much the same as the last-resort one. Only difference is that instead of writing the data on server B, you're proposing to write it...

What is essentially happening is, the stored procedure is creating a set of ID numbers from some data. Then, from those ID numbers, some data from server A is acquired,...

Daryl-273852 (3/5/2010)

---

Would it be possible to split the work?

Server A calls a proc on Server B that gets/creates a smaller subset of data in an actual table for this data...

Honestly, it's hard to notice - the individual subsets are flying by so fast that there's not really a noticable change. Maybe it might have gone from like 5 seconds...

Oh, neat. So I can do this instead:

SELECT

URL "loc",

REPLACE(CONVERT(VARCHAR, DateCreated, 111), '/', '-') + 'T' + CONVERT(VARCHAR, DateCreated, 8) + '-05:00' "lastmod",

'daily' "changefreq",

CAST(Priority AS VARCHAR) "priority"

FROM #Temp t2...

Never mind - figured it out! Actually starting to learn how this XML stuff works finally, lol.

Incase you're curious, here's the solution I employed:

SELECT TOP 1 @PrevURL = @PrevURL +...

Lol - yeah, see the correction I posted 😛 Had to replace the "&" characters, since otherwise it just showed the correct string 😛

Sorry for the double post, but there is one more problem left now.

The string, as it is written to the file, is converting many of the characters to their HTML...

Wow - that works amazingly well! Took a 45 minute operation down to 5 seconds!

Can you explain to me how this is working? I'm a bit fuzzy on the whole...

Actually, the size of the string is about that, yes. Some of the larger subsets have over 1 million characters in them.

So, yeah, they are storing a ton of data...

The stuff that I've used CLRs for are things that, as far as I know, can't be done using the native SQL environment.

In particular, at times I require code which...

I understand all that Lynn 😛 this was more of a theoretical question than one that needed to be applied on a specific case. But if you wish,

CREATE TABLE #User

(

ID...

Unfortunately that doesn't really change the solution much - instead of a subquery happening in the WHERE clause, you're running a subquery to populate a JOIN clause. I'm trying to...

*nods*, that's the way I've got it set up, yeah. Since I'm going to just write this stuff to a history table now, and remove the direct update against the...