THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Uri Dimant

  • Successful upgrade to SQL Server 2012

    Hi everyone. A few days ago I successfully upgraded our production database to the new version -SQL Server 2012. Actually, all installation process went ok, and after running Upgrade Advisor I restored the database onto a new server without any problems. The "challenge" was to upgrade existing SSRS reports and SSIS packages. I migrated the entire solution of our reports and it looked ok but trying open the data source or even click on Properties ,I was kicked off and prompt to close or restart SQL Data Tools solution.What I would recommend is to open a new project in SQL Data Tools (yes BIDS is gone) ,creating a new data source and adding report by report to the project. SQL Server  will automatically upgrade them for first time you run it. Also ,SSIS package migration process went smoothly where I just needed to replace connection string for OLEDB source only.Another good thing is that now you can much easily configure SSRS and even if you specified not to "install and configure" during the installation.More over, I have not noticed any performance degradation since we moved from SQL Server 2005 after running Update Statistics on all user databases. So lets enjoy new features that were introduced and happy working with SQL Server 2012 to everyone.

  • SQL Server 2005 Maintenance Plan won't delete backup files created in SQL Server 2008

    I have been asked recently to help to one of my friends with "strange behaviour" as he described...He has SQL Server 2008 where sql job copies .BAK files (database backup) into his local disk drive with requirement to keep those file only for one month. So as he has SQL Server 2005 (SP3) installed on his machine the first idea was to create Maintenance Clean Up task to delete the files. But as you imagine that did not work. Surely, when I came to help, I did not know that those files are created in SQL Server 2008 and has been copied to his local machine and after cheching all possibilities I launch up SQL Server Profiler to see what is going on... I saw the below command to be executed for each file to be deleted .

    exec sp_executesql N'RESTORE LABELONLY FROM DISK=@P1',N'@P1 nchar(27)',N'X:\DB\Log\log010411PM.bak'

    Well, I put that statement in Query window and obviously got the error which leads me to the root of  the problem.

    Msg 3241, Level 16, State 7, Line 1

    The media family on device 'X:\DB\Log\log010411PM.bak' is incorrectly formed. SQL Server cannot process this media family.

    Msg 3013, Level 16, State 1, Line 1

    RESTORE LABELONLY is terminating abnormally.

     What do you think guys? Does SQL Server need to check out the version of the files need to be deleted or just delete files with .BAK extension regardless on version they were created ?

     

  • Alias issue in T-SQL or defensive programming

    Recently I have talked to our developer who wanted to delete TOP x rows from the table. I pointed him to the below artcile http://blogs.msdn.com/b/sqlcat/archive/2009/05/21/fast-ordered-delete.aspx where a tip – a view with ORDER BY.

    As alternative he wanted using a derived table but cannot understand why all rows are deleted from the table instead of TOP(x). See the below demo.

    create table #t (c int)
    insert into #t values (1)
    insert into #t values (1)
    insert into #t values (2)
    insert into #t values (3)
    insert into #t values (3)

    delete #t from (select top (2) c
            from #t order by c) t

    How does  DELETE extension in T-SQL work?. The FROM clause after the DELETE specifies the target table to delete. The second optional FROM clause specifies the qualifying rows. But if I change 't' alias to '#t' as original name  of the temporary table that would work...

    delete #t from (select top (2) c
            from #t order by c) #t

    Now, SQL Server 'sees' that derived table has the same name as a target and thus deletes only TOP(x) rows

    There is no goal of this post to get into a discussion about how to write correlated subquery to perform such operations, I just wanted you to pay attention on if you choose using derived tables to perform deletion please make sure  that alias you specify for derived table is the same as a target table..

    PS. If you are testing and not sure about the result please use BEGIN TRAN... before executing the script.If you see that rows affected by the script is too many issue ROLLBACK TRAN to back  to original data.


     

  • Be careful to grant dbCreator server role to the user

    It is common that vendors ask for permission to create databases (or they applications need to create database) on your servers and most of DBAs I have seen immediately grant them dbCreator server role. But they are not aware that members of that role are able to DROP/ALTER any databases on the entire server regardless of whether or not you even have a user account in the database.Did you really want that?

    The right approach is to grant CREATE ANY DATABASE permission and then the user is able to DROP/ALTER he/she owns.
  • Find dependency task again

    If you have ever dealt with projects where you need to script out the objects and the recreate them on the destination database  you first always have been thinking of sys.sql_dependencies system table (if you are on SQL Server 2005 and onwards) . At our company we have a project to create script  on many client machines that have variuos versions of SQL Server Express Edition from SQL Server 2000  to SQL  Server 2008 R2. The team has automated script that creates all objects on the client  by using old syscomments table. They could not rely on sys.dependencies table because it has some critical bugs as  you are aware of. They did script out all views into a text file and run it for more than once because it is possoble that for the first run it is failed (if you have nested views.).It is very complicated script and in narrow down we can reproduce the problem as the following.

    USE AdventureWorks
    GO
    CREATE TABLE t1 (c INT)
    GO
    CREATE VIEW v1
    AS
    SELECT c FROM v2
    GO
    /*

    Msg 208, Level 16, State 1, Procedure v1, Line 3
    Invalid object name 'v2'
    */
    CREATE VIEW v2
    AS
    SELECT * FROM t1
    --Clean up
    DROP VIEW v1,v2
    DROP TABLE t1

    It worked well for a while but very slowely and they look for improvment. I would like to share with you very reliable and fast option developed by colleague of mine by using VB.NET. Please test it before  running on production server.

    This script reads the view definition, find the level of dependecy, and then set priority to create.

    Var
    _dsV – Data table of views and column of “PL” – Place or priority
    _dsD  - data table of dependences (view on view )
    _dsC –context of views


    Function
    CreateDependence(_dsV.Tables(0), _dsC.Tables(0), _dsD)  - find on context  of view if exist other views
    CheckChildViews(_dsV.Tables(0), _dsD, _dsD.Select(), 0) -  recursive  function which set priority of views.
    On the start all view with priority 1000 (it’s don’t have dependence) . When view has some dependence then it gets priority = 0  or parent priority + 1. All child views (nested view) get higher among (parent priority +1) or its priority.
    GetViews(_dsV.Tables(0), _dsC.Tables(0)) -  get all view by sort of priority descending


    Private Sub CreateViews()
            Dim cldb As clDB ‘  class -  connection  to database
            Dim _dsV As DataSet
            Dim _dsD As New DataTable("DEP")
            Dim _dsC As DataSet
            lblResultSqlScript.Text = ""
            Try
                cldb = New clDB
                cldb.ConnectionString = conectionString
                Dim tmp As String = ""
                'all views
                tmp = " SELECT  ID,NAME,1000 as PL "
                tmp &= " FROM dbo.sysobjects   "
                tmp &= " WHERE xtype = 'V'   and category=0  "
                tmp &= " order by name  " & vbNewLine
                _dsV = cldb.GetDataSet(tmp)

                'context of views
                tmp = " SELECT text ,COLID,ID "
                tmp &= " FROM syscomments "
                tmp &= " ORDER BY COLID " & vbNewLine
                _dsC = cldb.GetDataSet(tmp)
     
                'depandences
                CreateDependence(_dsV.Tables(0), _dsC.Tables(0), _dsD)
     
                ClearIndex(_dsV.Tables(0), _dsD)
                CheckChildViews(_dsV.Tables(0), _dsD, _dsD.Select(), 0)
                -----GetViews(_dsV.Tables(0), _dsC.Tables(0))
               RunViews (_dsV.Tables(0), _dsC.Tables(0))
            Catch ex As System.Exception
            Finally
                cldb = Nothing
            End Try
       End Sub
     

        Private Sub CreateDependence(ByVal dsV As DataTable, ByVal dsC As DataTable, ByRef dt As DataTable)
            'create datatable
            Dim cl1 As New DataColumn("PNAME", System.Type.GetType("System.String"))
            Dim cl2 As New DataColumn("PID", System.Type.GetType("System.Int32"))
            Dim cl3 As New DataColumn("CNAME", System.Type.GetType("System.String"))
            Dim cl4 As New DataColumn("CID", System.Type.GetType("System.Int32"))
            dt.Columns.Add(cl1)
            dt.Columns.Add(cl2)
            dt.Columns.Add(cl3)
            dt.Columns.Add(cl4)
            Dim cur As Integer = -1
            Dim tmp As String = ""
     
            'load view
            Dim bs As New Text.StringBuilder
            For Each ddr As DataRow In dsV.Select("", "PL DESC")
                Try
                    bs.Length = 0
                    For Each dr As DataRow In dsC.Select("ID=" & ddr("ID"), "COLID")
                        Try
                            bs.Append(dr("TEXT").ToString)
                        Catch ex As System.Exception
                        End Try
                    Next
                    'find dependence
                    'loop on all view without current
                    For Each ddr1 As DataRow In dsV.Select("ID<>" & ddr("ID"), "PL DESC")
                        cur = bs.ToString.IndexOf(ddr1("NAME").ToString)
                        If cur > -1 Then
                            'check if it's real name and not peace of name
                            tmp = bs.ToString.Substring(cur + ddr1("NAME").ToString.Length, 1)
                            If tmp = "" Or tmp = "." Or tmp = " " Or Asc(tmp) = 13 Then
                                'add to dependence
                                Dim newdr As DataRow = dt.NewRow
                                newdr("PNAME") = ddr("NAME")
                                newdr("PID") = ddr("ID")
                                newdr("CNAME") = ddr1("NAME")
                                newdr("CID") = ddr1("ID")
                                dt.Rows.Add(newdr)
                            End If
                        End If
                    Next
                Catch ex As System.Exception
                End Try
            Next
        End Sub
        Private Sub GetViews(ByVal dsV As DataTable, ByVal dsC As DataTable)

            txtSQLScript.Text = ""
            Dim bs As New Text.StringBuilder
            bs.Append(" SET QUOTED_IDENTIFIER OFF    " & vbNewLine)
            bs.Append(" GO " & vbNewLine)
            bs.Append(" SET ANSI_NULLS OFF    " & vbNewLine)
            bs.Append(" GO " & vbNewLine)
            For Each ddr As DataRow In dsV.Select("", "PL DESC")
                bs.Append(" IF EXISTS (select * from dbo.sysobjects where id = object_id(N'" & ddr("NAME") & "') and OBJECTPROPERTY(id, N'IsView') = 1)" & vbNewLine)
                bs.Append(" DROP VIEW " & ddr("NAME") & vbNewLine)
                bs.Append(" GO " & vbNewLine)
     
                For Each dr As DataRow In dsC.Select("ID=" & ddr("ID"))
                    Try
                        bs.Append(dr("TEXT").ToString)
     
                    Catch ex As System.Exception
                    End Try
                Next
                bs.Append(vbNewLine & " GO " & vbNewLine)
                bs.Append(vbNewLine & "--------------------------------" & vbNewLine)
            Next
            txtSQLScript.Text = bs.ToString
        End Sub
        Private Sub ClearIndex(ByRef dsV As DataTable, ByVal dsd As DataTable)
            For Each ddr As DataRow In dsV.select
                If dsd.Select("PID=" & ddr("ID").ToString).Length > 0 Then
                    ddr("PL") = 0
                End If
            Next
        End Sub
     
        Private Sub CheckChildViews(ByRef dsV As DataTable, _
                            ByVal dsd As DataTable, _
                            ByVal dr() As DataRow, ByVal index As Integer)
            For Each ddr As DataRow In dr
                Try
                    Dim pd As DataRow = dsV.Select("ID=" & ddr("PID").ToString)(0)
                    If pd("PL") < index Then pd("PL") = index
                    CheckChildViews(dsV, dsd, dsd.Select("PID=" & ddr("CID").ToString), index + 1)
                Catch ex As System.Exception
                End Try
            Next
        End Sub

    Private Sub RunViews(ByVal dsV As DataTable, ByVal dsC As DataTable)
            txtSQLScript.Text = ""
            Dim bs As New Text.StringBuilder
            Dim cldb As New dbAdministration.clDB
            For Each ddr As DataRow In dsV.Select("", "PL DESC")
                bs.Length = 0
                bs.Append(" IF EXISTS (select * from dbo.sysobjects where id = object_id(N'" & ddr("NAME") & "') and OBJECTPROPERTY(id, N'IsView') = 1)" & vbNewLine)
                bs.Append(" DROP VIEW " & ddr("NAME") & vbNewLine)
                cldb.Execute(bs.ToString()) 'run delete if exist view
     
                bs.Length = 0
             
               For Each dr As DataRow In dsC.Select("ID=" & ddr("ID"))
                    Try
                        bs.Append(dr("TEXT").ToString)
                    Catch ex As System.Exception
                    End Try
                Next
                cldb.Execute(bs.ToString()) 'run create view
            Next
        End Sub
     

  • Don't speak to the DBA while he/she is doing the job?!

    Our DBA was very busy today, he helped out our developers to write efficient code,explained to the programmer how to launch the old DTS package on SQL Server 2005 and etc. You know how it is, someone comes in and asking the question and you answer the question at the same time you are busy to do something. So  today answering one of such questions he deleted by mistake very ctitical database. Fortunately, we had zero data loss,thanks to our backup/recovery strategy.

    I think that is acceptable to say that you are busy right now ,please ask the question later on, what do you think? How do you respond if someone asks you a queston while you are doing something on the server/database and etc.?

     

  • Another bad habit to kick: checking variables in DML operations not just before

    Perhaps I am 'breaking in' Aaron's bad habits series of blogs or Alex's defensive programming but I would like to share with you the style I have seen recently on the client's side and how it affects perfromance.

    There is long lines stored procedure that has many DML operations but I have seen that repeated many times within the stored procedure.

    Test table has a clustered unique index created on c column and has 2 million rows.

    DECLARE @par1 char(1)='A'

    DECLARE @par2 INT=10

     

    DELETE FROM Test WHERE

    c >20000 AND c<60000

    AND @par1<>'B' AND @par2>9

    If you turn on an actual execution plan you will see that SQL Server  uses CI index SEEK to get to  the range and intresting , Filter operator to check variables and  also Sort operator to select the first few rows based on a sort order.

    On the other hand running the below you will see only CI Delete operator only. And running then secod one takes  on my laptop slightly faster.

    DELETE FROM Test WHERE

    c >20000 AND c<60000

    I would like to hear from you friends how you deal with such programming styles and how it affects performance at your company. Would it be much better to write IF..ELSE block to check for deletion which I proposed to the client in that case?

     

     

  • Think before unchecking sysadmin rights of BUILTIN\Administrators.

    Hello every body. This is my first blog on that great site so I am really exciting.

    I have recently met our client who uchecked the sysadmin rights of BUILTIN\Administrators group before given any permissions to another account.

    That was NOT such problem if the BUILTIN\Administrators group was removed from sysadmin role accidentally/by mistake, then you must login with another sysadmin login. If there is no other sysadmin login, you must login with SQL authentication as sa with the password that was set during setup to sa. Once logged in as a member of sysadmin, you are able to add BUILTIN\Admisnitrators back to sysadmin role.
    However everything above does not work for the client. Uhhh,the client also disabled SA accoount as well as DAC connection.
    Moreover, there is no domain controller where you can create a sysadmin domain acoount and grant the access to the machine running SQL Server,that was a stand alone computer with single instance installed on.

    The solution we found was to start SQL Server with single user mode. As Raul said that using the single-user mode, SQL Server 2005 prevents a Windows Administrator to abuse this privilege to act on behalf of the sysadmin without being noticed. This allows Windows Administrator accounts to perform certain maintenance tasks, such as installing patches. To someone who is not familiar how to start the instance in single user mode and adding login to the server role being system administrator please read the below link describing step by step the procedure.

    http://blogs.msdn.com/raulga/archive/2007/07/12/disaster-recovery-what-to-do-when-the-sa-account-password-is-lost-in-sql-server-2005.aspx

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement