Twitter Updates

    follow me on Twitter

    Wednesday, December 20, 2006

    SQL Server 2005 SP2 December CTP

    Microsoft released a new December CTP of SP2 for SQL Server 2005. Along with this refresh came some other nice downloads:

    Microsoft SQL Server 2005 Data Mining Add-ins for Office 2007 - Community Technology Preview (CTP) December 2006
    Microsoft SQL Server 2005 Data Mining Add-ins for Office 2007 (Data Mining Add-ins) allow you take advantage of SQL Server 2005 predictive analytics in Office Excel 2007 and Office Visio 2007. The download includes the following components:
    • Table Analysis Tools for Excel: This add-in provides you with easy-to-use tasks that leverage SQL Server 2005 Data Mining under the covers to perform powerful analytics on your spreadsheet data.
    • Data Mining Client for Excel: This add-in allows you to go through the full data mining model development lifecycle within Excel 2007 using either your spreadsheet data or external data accessible through your SQL Server 2005 Analysis Services instance.
    • Data Mining Templates for Visio: This add-in allows you to render and share your mining models as annotatable Visio 2007 drawings."
    SQL Server Reporting Services Add-in for SharePoint Technologies CTP
    Microsoft SQL Server 2005 Reporting Services Add-in for SharePoint Technologies (Reporting Services Add-in) allows you to take advantage of SQL Server 2005 Service Pack 2 (SP2) report processing and management capabilities within Windows SharePoint Services 3.0 or Microsoft Office SharePoint Server 2007. The download provides the following functionality

    SQL Server 2005 Books Online SP2 CTP (December 2006)
    Updated BOL.

    SQL Server Database Publishing Wizard 1.0 RC
    SQL Server Database Publishing Wizard enables the deployment of SQL Server databases into a hosted environment on either a SQL Server 2000 or 2005 server. It generates a single SQL script file which can be used to recreate a database (both schema and data) in a shared hosting environment where the only connectivity to a server is through a web-based control panel with a script execution window. If supported by the hosting service provider, the Database Publishing Wizard can also directly upload databases to servers located at the shared hosting provider."

    And there is even more .... A full list of recent SQL related downloads can be found here.

    Monday, December 11, 2006

    The Cowboy Way of upgrading to SQL Server 2005

    One of the scenarios I showed during my presentation on upgrading to SQL Sever 2005 was "The Cowboy Way of upgrading". Here's the situation. A SQL 2000 database containing a user called sys, cannot be upgraded to SQL Server 2005. If you run the Upgrade Advisor, you will get a warning on this show stopper. So, the best practice here is to rename the user on SQL Server 2000 and afterwards upgrade to SQL 2005.

    In "The Cowboy Way of upgrading" I restored the SQL Server 2000 database with the sys user on my SQL 2005. The restore generated an error because of the sys users and the database was marked suspect.

    Remember that is definitely not the best way of upgrading. But since quite a few people asked about the script I decided to post it. Have fun but be careful.
    Connecting to localhost\sql2005...
    15 percent processed.
    23 percent processed.
    30 percent processed.
    46 percent processed.
    53 percent processed.
    61 percent processed.
    76 percent processed.
    84 percent processed.
    92 percent processed.
    100 percent processed.
    Processed 104 pages for database 'sys_database', file 'sys_database_Data' on file 1.
    Processed 1 pages for database 'sys_database', file 'sys_database_Log' on file 1.
    Converting database 'sys_database' from version 539 to the current version 611.
    Database 'sys_database' running the upgrade step from version 539 to version 551.
    Warning: User "sys" (principal_id = 5) in database "sys_database" has been renamed
    to "_ID_0x00000005_RENAMED_FROM_sys". "sys" is a reserved user or schema name
    in this version of SQL Server.
    Warning: Database "sys_database" was marked suspect because of actions taken during
    upgrade. See errorlog or eventlog for more information.
    Use ALTER DATABASE to bring the database online. The database will come online in
    restricted_user state.
    Database 'sys_database' running the upgrade step from version 551 to version 552.
    Database 'sys_database' running the upgrade step from version 552 to version 553.
    Database 'sys_database' running the upgrade step from version 553 to version 554.
    Database 'sys_database' running the upgrade step from version 554 to version 589.
    Database 'sys_database' running the upgrade step from version 589 to version 590.
    Database 'sys_database' running the upgrade step from version 590 to version 593.
    Database 'sys_database' running the upgrade step from version 593 to version 597.
    Database 'sys_database' running the upgrade step from version 597 to version 604.
    Database 'sys_database' running the upgrade step from version 604 to version 605.
    Database 'sys_database' running the upgrade step from version 605 to version 606.
    Database 'sys_database' running the upgrade step from version 606 to version 607.
    Database 'sys_database' running the upgrade step from version 607 to version 608.
    Database 'sys_database' running the upgrade step from version 608 to version 609.
    Database 'sys_database' running the upgrade step from version 609 to version 610.
    Database 'sys_database' running the upgrade step from version 610 to version 611.
    Msg 3167, Level 16, State 3, Line 1
    RESTORE could not start database 'sys_database'.
    Msg 3167, Level 16, State 1, Line 1
    RESTORE could not start database 'sys_database'.
    Msg 3013, Level 16, State 1, Line 1
    RESTORE DATABASE is terminating abnormally.

    Here's how I fixed this one. First of all I forced the database online (ALTER DATABASE sys_database SET ONLINE). SQL Server will put the database in single user. In the next step I checked the system catalogs to find out that SQL Server created a new schema called _ID_0x00000005_RENAMED_FROM_sys. My table was moved from the sys schema to this new schema.

    In the next step I created a new schema and used ALTER SCHEMA to move the table from _ID_0x00000005_RENAMED_FROM_sys to my new schema.
    CREATE SCHEMA system

    ALTER SCHEMA system
    TRANSFER _ID_0x00000005_RENAMED_FROM_sys.myTable

    In the last step I put the database in multi user mode.
    ALTER DATABASE sys_database
    SET MULTI_USER

    Here's the full script.
    USE master

    --Force online
    ALTER DATABASE sys_database
    SET ONLINE

    --Now in single user, show object explorer first
    USE sys_database


    SELECT name, SCHEMA_NAME(schema_id) as schema_name, *
    FROM sys.objects
    WHERE NAME = 'myTable'

    SELECT * FROM sys.schemas

    SELECT * FROM sys.database_principals

    SELECT * FROM sys.server_principals

    CREATE SCHEMA system

    ALTER SCHEMA system
    TRANSFER _ID_0x00000005_RENAMED_FROM_sys.myTable

    SELECT * FROM system.myTable

    INSERT INTO system.mYTable (col1) VALUES ('Frederik')

    ALTER DATABASE sys_database
    SET MULTI_USER