Right or Wrong, Dynamic SQL AND a Cursor

  • Kurt W. Zimmerman

    SSCrazy Eights

    Points: 8676

    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

  • Larry Aue

    Ten Centuries

    Points: 1027

    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

    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

    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

    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

    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

    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

    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

    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

    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

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

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