I changed the name of a database in Microsoft Sql Server 2005.  The database happened to be the default database for a user.  The next time the user tried to connect to the SQL server instance, they received the following error message: Cannot Open User Default Database.  Here is the fix.

SQL Server 2005

You can use the sqlcmd utility to change the default database in SQL Server 2005. To do this, follow these steps:

  1. Click Start, click Run, type cmd, and then press ENTER.
  2. Use one of the following methods, depending on the kind of authentication that the SQL Server login uses:

    If the SQL Server login uses Microsoft Windows authentication to connect to the instance, type the following at the command prompt, and then press ENTER:

    sqlcmd –E -S InstanceName –d master

    If the SQL Server login uses SQL Server authentication to connect to the instance, type the following at the command prompt, and then press ENTER:

    sqlcmd -S InstanceName -d master -U SQLLogin -P Password

    Note InstanceName is a placeholder for the name of the SQL Server 2005 instance to which you are connecting. SQLLogin is a placeholder for the SQL Server login whose default database has been dropped. Password is a placeholder for the SQL Server login password.
  3. At the sqlcmd prompt, type the following, and then press ENTER:
    ALTER LOGIN SQLLogin WITH DEFAULT_DATABASE = AvailDBName
    Note AvailDBName is a placeholder for the name of the existing database that can be accessed by the SQL Server login in the instance.
  4. At the sqlcmd prompt, type GO, and then press ENTER.

 

  • Custom Sort from Andy Warren.  How to get United States to appear first in a drop down list of countries. 

    Select CountryName
    From Countries
    order by
      Case when CountryName = 'United States' then 0 else 1 end,
      CountryName

     
  • I was having trouble parsing an Update query that was using an inner join.  It turned out that I was missing the From clause in my update statement.
Update Tablename
Set Field1 = 100
Inner Join --Wrong
Update Tablename
Set Field1 = 100
Fom Tablename
Inner Join --Correct 
  • In Sequel Server Reporting Services (SSRS), how to hide a textbox (follow link, go to using Complex Expressions, about halfway down within Complex Expressions, Hide a text box on the design surface...) based on a boolean field value in the recordset:
Open Textbox properties Dialaog box, click on Visibility tab, and enter =Not fields!VoidFlag.Value in the Expression box.