May 8, 2015 at 7:34 am
I have a question about using dynamic sql and cursors. First all I have read a lot of responses saying that both are bad or evil in most cases. I know how I will probably do this using these concepts, but I am asking is their a better way of doing this if so, please give me a detailed example of how. Because all of research I have done does not tell me another way of doing it.
1) I have a central admin database. In this database I need to keep statistics on when was the last time a piece of equipment was reported as running. The data for each equipment is not in this database but in separate databases for each customer, which may also be on different servers. This code will be run once an hour
2) In central database I have tables that have all of the servers and databases I want to collect information from.
3) I will need to use dynamic sql to select the required data from each database. In each case of dynamic sql the only thing that will change is the server and database name, the tables and columns being selected are un changed.
4) I will be using 2 cursors, so that with each record I get from my select I can do separate processing on that data. As I see it their is no way to get out of using a while loop
5) Basic way I am doing my code
create cursor to get server and database information to be used by dynamic sql
for each record in cursor
build a dynamic select statement that includes creating a second cursor. The only things that will change in this dynamic sql is the server and database name in the query.
for each record in dynamic sql cursor
determine if the equipment has already been previously reported on. If not then it is a new piece of equipment that needs to be added to central database.
Add the statistics information for that piece of equipment
end dynamic cursor loop
end cursor loop
May 8, 2015 at 7:39 am
Have you looked into setting up a CMS (Central Management Server)?
Cursor's and Dynamic SQL aren't bad per se but rather can cause issues when not executed properly. Try to avoid them when you can but sometimes you just have to.
May 8, 2015 at 8:31 am
That's not really ad hoc or dynamic SQL. The query is going to be the same every time you run it. You're not building it on the fly. You just have to loop through the servers. That's actually not a bad use of cursors. It's the kind of thing a lot of people do with them. For example, database backups. How do you do those? One at a time through a cursor (ms_foreachdb is a cursor). It's accessing and modifying rows of data through cursors, manipulating them one at a time, as Jeff Moden calls it, Row-By-Agonizing-Row (RBAR, pronounced reebar). That's not what you're doing.
Now, if you want to get fancy, you could use Powershell and your central management server. Have the Powershell script get the list of servers you want to run the query against and then thread those queries in order to run them all at once, gathering back the data. But that's probably not really needed. The cursor approach in what you describe should be OK.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply