Me, Myself and Mayvelous

Archive for the ‘SQL’ Category

While working on this one release, I had to do lots of backup, restore, and scripts for different database server versions. Run this script a lot today. Noted that this script is very handy to check out the server edition and version.

select serverproperty('edition')
select @@version

And another annoying thing I found is that when you script the whole database with schema + data, it generates huge script file which sometimes cannot open in SQL Management Studio and even if the file gets opened, cannot execute it due to not enough memory problem. So found out that I cannot solve the problem of restoring backups on different servers by scripting whole db.

  • Comments Off
  • Filed under: SQL, Tips and Tricks, Work
  • I learnt something new today. I needed to make a release which includes lots of changes to DB over 3 months period and needed a quick way to figure out which staging db objects to move across to the production.

    I wished many times to list storedprocedures by last modified date but stupid enough never Google it and just did the painful way of noting down and scripting them. Then I face the problem of all those script files keep piling up over the period of time and lost track of which scripts have been executed or not.

    Today I’m in a bit of sensible mode and did a quick googling and found this useful script, so I’m posting here for later reference.

    Listing last modified storedprocedures
    select name, create_date, modify_date
    from sys.procedures
    order by modify_date desc

    Listing last modified tables
    select name, create_date, modify_date
    from sys.tables
    order by modify_date desc

    Listing db objects by type and date
    SELECT *
    FROM sys.objects
    WHERE type in ('P', 'U')
    AND DATEDIFF(D,modify_date, GETDATE()) < 90

    Type “P” is for storedprocedures and “U” for tables. More on available Types:

    Now that I know which objects have been last updated, I can do the DB script very easily by just selecting those objects. Sweet isn’t it? ;)

  • Comments Off
  • Filed under: Development, Microsoft, SQL, Tips and Tricks, Work
  • This was a humongous pain for me and took quite a lot of my time so just noting down a few points for self reference. Previously, I have Visual Studio 2005 and SQL Server 2005 installed on my Vista machine and they worked just fine. Now we are upgrading to VS2008, I uninstalled everything related to VS2005 then reinstall SQL Server 2005 and things got screwed up. Check out the failure list that I got – pretty much everything failed.

    MS SQL Server 2005 Setup Errors

    The worst thing about this installation is that, it takes ages to install till the end, then shows up the error message box.

    Program Versions:

    # SQL Server 2005 – Developer Edition
    # Windows Vista Ultimate
    # Visual Studio Team Suite 2008

    Make sure you cross check Hardware and Software Requirements for Installing SQL Server 2005 before installation.

    The first error/warning I tried to fixed was about IIS requirement. I’m sure I have IIS7 enabled on my Vista and also enabled IIS6 compatiblity mode but that is not enough and have to enable some more features.

    System Configuration Check

    System Configuration Check: IIS Feature Requirement
    Microsoft Internet Information Services (IIS) is either not installed or is disabled. IIS is required by some SQL Server features. Without IIS, some SQL Server features will not be available for installation. To install all SQL Server features, install IIS from Add or Remove programs in Control Panel or enable the IIS server through the Control Panel if it is already installed, and then run SQL Server Setup again. For a list of features that depend on IIS, see Features Supported by Editions of SQL Server in Books Online.

    I think it is to do with Installing SQL Server 2005 Reporting Service on Vista, untill or unless you enable all those features the Reporting services cannot be installed.

    The main features to turn on are as mentioned here but I turned on almost everything just to make sure.

    IIS 7 Features

    More info links:
    # How to install SQL Server 2005 Reporting Services on a Windows Vista-based computer
    # Installation of SQL Server 2005 in Windows Vista – warning message on IIS feature requirement during System Configuration Check
    # Tip for Installing SQL Server 2005 Reporting Service on Vista

    Ok, this one is the weirdest error and solution I got. Don’t even ask why in the world do I need to install Office 2003 Web Component add-in while I have Office 2007 installed on my machine. I have no clue, but that’s the only solution I found which works for me. It solves the “OWC11 Setup failed error and the following error message.

    MS SQL Server 2005 - Error 1706

    ERROR 1706 Setup cannot find the required files. Check your connection to the network, or CD ROM! For other potential solutions to this problem, see c:\Program Files\Microsoft Office\Office11\1033\Setup.chm

    You have to download and install “Office 2003 web components” first and install SQL Server 2005 again; which then in my case cleared the error and installed successfully.
    # Download: Office 2003 Add-in: Office Web Components – owc11.exe [ 17.6MB ]

    After everything is done, remember to install Microsoft SQL Server 2005 Service Pack 2. Pheewww! That’s it!

    Just for my own note since I keep forgetting the code and also testing out the new SyntaxHighlighter plugin that I just installed. Look great isn’t it?

    SQL 2005 System Databases

    Information schema views provide an internal, system table-independent view of the SQL Server metadata. Information schema views enable applications to work correctly although significant changes have been made to the underlying system tables. (#)

    SQL 2005 System Views

    Select a List of all Tables in a Database

    SELECT *
    FROM [DB-Name].information_schema.tables
    WHERE table_type = 'Base Table'


    • The columns returns are ‘Table_Catalog’, ‘Table_Schema’, ‘Table_Name’ and ‘Table_Type’
    • The result includes dtproperties table in the list as well. To remove it add another condition in the WHERE statement.
      eg. WHERE table_type = ‘Base Table’ AND table_name != ‘dtproperties’
    • Use square brackets [] if the DB name includes a dash ‘-’.

    SQL 2005 Information Schema Columns

    Select a list of all Tables and Columns in a Database

    SELECT *
    FROM FIBS.information_schema.columns

    23 columns resulted by the above query. To refine more, try the query below.

    SELECT table_name, column_name, data_type, character_maximum_length, is_nullable
    FROM Information_Schema.columns
    WHERE table_name IN (Select name
    		                  FROM sysobjects 
    		                  WHERE xtype = 'U')
    ORDER BY table_name


    • xtype has two values – ‘U’ or ‘S’
    • U = User generated tables
    • S = System generated tables

    More info: SQL Server 2005 System Tables and Views
    This page includes a good list of all the How-To that you might want to check out: Querying the SQL Server System Catalog FAQ

  • Comments Off
  • Filed under: Development, Notes, SQL
  • Flickr Photos


    Twitter Status

    Goodie Links

    Mayvelous Friends

    I'm an Author for Global Voices