May 9, 2016 at 12:13 am
Hi,
I've recently started a new job and have found cursors to exist pretty much everywhere. I'm trying to replace these to use set based T-SQl but I'm struggling to find a solution. We have mutliple servers which hold more than one database and each database holds the same schema, so something along the lines of:
Server1.DatabaseA.dbo.TestTable
Server1.DatabaseB.dbo.TestTable
Server2.DatabaseA.dbo.TestTable
Server2.DatabaseB.dbo.TestTable
Server3.DatabaseA.dbo.TestTable
Server3.DatabaseB.dbo.TestTable
For data retrieval, the curser will pull all the unique link and db names and then loop round them in dynamic sql as table names can be passed into dynmaic sql. I'm stuggling to find an alterative to this despite a lot of time spent so far on this, I'm currently looking at using a foreach loop container in SSIS, as far as I'm aware, the server and database name can be held in an object variable which is then native to SSIS so the contention issues in SQL should be reduced.
Any advice on this is greatly appreciated.
Thanks
Matt
May 9, 2016 at 3:47 am
.
May 9, 2016 at 5:38 am
MaPar (5/9/2016)
Hi,I've recently started a new job and have found cursors to exist pretty much everywhere. I'm trying to replace these to use set based T-SQl but I'm struggling to find a solution. We have mutliple servers which hold more than one database and each database holds the same schema, so something along the lines of:
Server1.DatabaseA.dbo.TestTable
Server1.DatabaseB.dbo.TestTable
Server2.DatabaseA.dbo.TestTable
Server2.DatabaseB.dbo.TestTable
Server3.DatabaseA.dbo.TestTable
Server3.DatabaseB.dbo.TestTable
For data retrieval, the curser will pull all the unique link and db names and then loop round them in dynamic sql as table names can be passed into dynmaic sql. I'm stuggling to find an alterative to this despite a lot of time spent so far on this, I'm currently looking at using a foreach loop container in SSIS, as far as I'm aware, the server and database name can be held in an object variable which is then native to SSIS so the contention issues in SQL should be reduced.
Any advice on this is greatly appreciated.
Thanks
Matt
Without more detail, it's difficult to say for sure, but if Cursors are being used purely to iterate round servers (and, specifically, not individual table rows), it may be that Cursors are a good solution for what you are doing.
Perhaps you could go into more detail with a specific example, to provide more background?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 9, 2016 at 6:38 am
Given the design you describe cursors are probably the way to go and nothing wrong with that (assuming what is being done per object is actually set-based/efficient). I question the entire design of this system though. 😉
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply