Blog posts about sql

  • Can't save changes in SQL Server 2008 Management Studio table design?

    March 27, 2013 | Tags: Troubleshooting, SQL

    Made changes in the table designer but SQL Server 2008 Management Studio won't let you save?

    Problem

    This is a kind of annoying new feature in SQL Server 2008 Management Studio. In previous versions you could make changes in the design view of a table and if it wasn't possible to save these changes without dropping the table first you would be noticed about it and have the choice to do so. In the 2008 version you will just get a dialog that says it's not possible to save as it would require a drop:


    "Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created."

    Your only option is to cancel. Surprisingly this is actually by design.

    Solution

    To turn off this feature, go into the menu Tools / Options and under Designers uncheck the box for "Prevent saving changes that require table re-creation" and save. You will now be able to save your changes and the table will be dropped (loosing current data) to be re-created with your changes.


    Remember to turn back on!It's pretty scary because with this off your table is dropped without any notice. In order to prevent future accidentally drops you should turn this feature back on before you shut down the studio.


  • List all tables in a SQL database

    February 14, 2012 | Tags: SQL, Snippet
    Need to list which tables exists in your database? Then this will do the trick:
    select table_name as Name 
    from INFORMATION_SCHEMA.Tables
    where TABLE_TYPE = 'BASE TABLE'

  • Sync user and login for SQL database

    February 01, 2011 | Tags: Frax, Database, Troubleshooting, SQL, Snippet
    Got a login on your SQL server and the same user in a database but they are not connected? Even though the names are the sames the unique IDs are not. Here's a snippet from my college Fredrik that's very useful connecting the user name between server login and database user. Replace 'user' with 'your user name':
    EXEC sp_change_users_login 'Auto_Fix', 'user'

    And a small celebration! This is the 100th post :)

  • Get version info from SQL backup

    January 17, 2011 | Tags: SQL, Snippet
    This neat little snippet shows you alot of information from a SQL database backup (.bak file)

    RESTORE HEADERONLY

    FROM DISK = N'c:\temp\dbMyDatabase.bak'

    WITH NOUNLOAD;

    If this doesn't work you're probebly trying to run it on an older SQL Server version than the backup was taken from.

    Unfortunately I haven't found a proper lookup table to translate the version number to SQL Server version, but here's two:
    611 = SQL Server 2005
    655 = SQL Server 2008

    If you find a good resource for version lookup, please post it in the comments, thanks!

  • Copy data from another table using INSERT

    September 20, 2010 | Tags: SQL, Snippet
    INSERT INTO MyDestinationTable (MyField1, MyField2, MyField3)
    SELECT MyField1, MyField2, MyField3 FROM MySourceTable
    If your two tables have the exact same structure you may omit the list of columns in both the INSERT and SELECT sub statements, but it doesn't hurt to be explicit.