Right or Wrong, Dynamic SQL AND a Cursor

Question

Right or Wrong, Dynamic SQL AND a Cursor

Kurt W. Zimmerman

SSCrazy Eights

Points: 8676
More actions
November 7, 2013 at 10:40 pm

#300824

Comments posted to this topic are about the item Right or Wrong, Dynamic SQL AND a Cursor
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Larry Aue Ten Centuries Points: 1027 More actions · Answer 1

Use the INSENSITIVE keyword in your declare statement and the cursor is essentially just like using a temp table, since insensitive cursors don't keep track of changes that are occurring to the result set. I use cursors for mundane 1-time tasks, too.

Kurt W. Zimmerman SSCrazy Eights Points: 8676 More actions · Answer 2

Larry Aue (11/8/2013)
Use the INSENSITIVE keyword in your declare statement and the cursor is essentially just like using a temp table, since insensitive cursors don't keep track of changes that are occurring to the result set. I use cursors for mundane 1-time tasks, too.

Thanks Larry for your comments.

Kurt

Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman

bradphilp Valued Member Points: 57 More actions · Answer 3

The reasons we don't like cursors are due to the unpredictability of locks, not to mention not set programming. Anything you can do with a cursor, you can do with a temp table, a while loop and a counter. Please STOP advocating bad habits.

Brian J. Parker SSC Eights! Points: 879 More actions · Answer 4

Sure, but you can often do them faster and easier with a cursor than with a temp table and while loop. And fast and easy is great for a rarely-run utility script.

If we're going to analyze the cursor declaration, I'd advocate using LOCAL FAST_FORWARD rather than STATIC. In my experience, the vast majority of cursors can and should be be LOCAL FAST_FORWARD, so using that is a good habit to get into.

In any case, thanks for sharing, Kurt!

Kurt W. Zimmerman SSCrazy Eights Points: 8676 More actions · Answer 5

bradphilp (11/8/2013)
The reasons we don't like cursors are due to the unpredictability of locks, not to mention not set programming. Anything you can do with a cursor, you can do with a temp table, a while loop and a counter. Please STOP advocating bad habits.

I agree with you not to use cursors for the purpose of managing transactions in a table. However for the purpose of creating utilities such as the script I provided, I see no issues/problems utilizing the cursor. Sure I can use a WHILE LOOP to perform the same function. For me, cursors have their place and I see no harm in using them used in that place. Thanks for your comments.

Kurt

Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman

bradphilp Valued Member Points: 57 More actions · Answer 6

Even though Microsoft has provided guidance since 2005 to not use cursors unless there is no other way to do it (at least we agree there IS another way to do it.

Postings are dangerous things as they always get taken changed and half understood, so people that don't know the history or the whys use it in different ways and don't know it is wrong!

I want to provide the best guidance, not just stuff that will work....

Nadrek SSC-Insane Points: 20039 More actions · Answer 7

It is not generalizable to restore every single t-log backup in the directory you store backups - this script as presented only works if a human has already selected the backup files to restore, and moved/copied them to a directory by themselves.

As I recall, you need to restore transaction logs where the LastLSN value (from RESTORE HEADERONLY) of the Transaction log is strictly greater than the LastLSN value of the Full or Differential backup in this restore chain, and BackupFinishDate is less than or equal to your StopAt date.

You also should not try to restore transaction logs where the FirstLSN and LastLSN are the same - these are common on systems that take very short interval T-logs on low use databases.

Why are you using xp_cmdshell at all? There's no need to open up such a major security hole for this:

Try something more in line with:

EXEC master.dbo.xp_dirtree @FileDirectory, 1, 1

Kurt W. Zimmerman SSCrazy Eights Points: 8676 More actions · Answer 8

Nadrek (11/8/2013)
It is not generalizable to restore every single t-log backup in the directory you store backups - this script as presented only works if a human has already selected the backup files to restore, and moved/copied them to a directory by themselves.
As I recall, you need to restore transaction logs where the LastLSN value (from RESTORE HEADERONLY) of the Transaction log is strictly greater than the LastLSN value of the Full or Differential backup in this restore chain, and BackupFinishDate is less than or equal to your StopAt date.
You also should not try to restore transaction logs where the FirstLSN and LastLSN are the same - these are common on systems that take very short interval T-logs on low use databases.
Why are you using xp_cmdshell at all? There's no need to open up such a major security hole for this:
Try something more in line with:
EXEC master.dbo.xp_dirtree @FileDirectory, 1, 1

Thanks for your response. None of my sql boxes have public facing interfaces. So there isn't much to worry about having a security hole. Second, typically what I'd do is stage the necessary backup & t-logs into a directory, so they are "hand picked" in the first place. Therefore I will not be going through unnecessary t-logs.

Thanks for the xp_dirtree suggestion. Let me see what I can do with it.

Have a great weekend.

Kurt

Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman

Pascal J v Vuuren Old Hand Points: 316 More actions · Answer 9

Hi Kurt,

A colleague showed me a slightly different pattern that I tend to use for utility scripts. I've had to mock up the file names as I don't have xp_cmdshell enabled. You can do similar with XML PATH type constructs but I find this syntax easier to remember and control for the occasions I need this type of thing.

declare @SQL nvarchar(max);

declare @Database varchar(200) = 'dwts001'

declare @FileDirectory varchar(200) = 'E:\Transfer\TransactionLogs\'

declare @CRLF char(2) = char(13) + char(10);

with cteFileNames as

(

select top(100) 'test'+convert(varchar(10),base.fileNumber)+'.log' as transactionLog from

(

select top(100) ROW_NUMBER( ) over (ORDER BY table_name) as fileNumber from information_schema.columns

) as base

)

select

@SQL = isnull(@SQL+@CRLF,'') +

'RESTORE LOG ' + @Database + @CRLF +

'FROM DISK = N''' + @FileDirectory + transactionLog + '''' + @CRLF +

'WITH FILE = 1' + @CRLF +

', NORECOVERY' + @CRLF +

', NOUNLOAD' + @CRLF +

', STATS = 10;' + @CRLF +

'PRINT ''Restoring Transaction File: ' + transactionLog + ''';' + @CRLF +

'PRINT ''-- -- --'';' + @CRLF

from cteFileNames;

-- NB Limit of printable characters characters

print @SQL;

Regards,

Pascal