I was on a SQL Server 2008 R2 database I wanted to backup and put on my machine.

First I tried backing it up and got this error:


Backup failed for Server. 'MyDatabaseServerName' (Microsoft.SqlServer.SmoExtended)

System.Data.SqlClient.SqlError: Cannot open backup device 'C:\MyBackupFileName.bak'. Operating system error 5(Access is denied.). (Microsoft.SqlServer.Smo)


For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1539+)&LinkId=20476

Then I tried generating scripts and got this error:

Microsoft.SqlServer.Management.Smo.SmoException: Could not read metadata, possibly due to insufficient access rights. at Microsoft.SqlServer.Management.SqlScriptPublish.GeneratePublishPage.worker_DoWork(Object sender, DoWorkEventArgs e) at System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e) at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)

I figured I must not have rights to some metadata or something. In the wizard I try selecting Tables, Views, and Schemas. My script generates fine. I notice there is no create database in the resulting script. I go back to the Choose Objects screen, unselect "Script entire database and all database objects" and "Select All" objects. My script generates fine.

Storing your SQL Server Database as Text

Have you ever had trouble moving a database from one server to another?  Ever wanted to store the database in source control?  Storing the database as text (a SQL Server Script) enables plain text tools to operate on your database.

Jeff McWherter from Gravity Works showed me how to easily store a SQL Server Database as Text:

1. In SQL Server Management Studio right click on a database
2. However over Tasks
3. Select Generate Scripts
4. Click 'Set Scripting Options' to skip the first two steps.
5. Click the 'Advanced' button
6. For the last option in general change 'Types of data to script' from schema only to 'Schema and data'
7. Click OK to close the Advanced dialog
8. Select a location to save the script to
9. Click Next to go to summary.
10. Click Next for SQL Server Management Studio to generate the script.
11. Click Finish and you've got your text!
    
12. When executing this script
1. Ensure the Filename in the CREATE DATABASE Statement is correct when executing this script. (Or you can comment out everything after CREATE DATABASE $YOURDBNAME$ until the first GO to have SQL Server use defaults)
2. Since this works at the SQL Server level, you will get errors when the script attempts to create users that don't exist on the target server and then errors when it authorizes that user.

P.S. See my post SQL Server Management Studio Generating Change Scripts if you want to use SQL Server Management Studio to graphically modify the database but then script the change.

How hard is it to change a column datatype?

One of our clients initially specified one of their columns as an int, and after being deployed for a month realized it needed to support other characters.  I found a couple of ways to do this in SQL Server 2008.

We'll use this sample table:

CREATE TABLE dbo.Orders

      (  OrderId int NOT NULL IDENTITY (1,1),

         OrderNumber int NOT NULL

      ) 

INSERT INTO Orders

      SELECT 234

     

SELECT * FROM Orders

The way I normally do things is by generating a change script in SQL Server Management Studio.  Here is the SQL output by that process.

CREATE TABLE dbo.Tmp_Orders

      (

      OrderId int NOT NULL IDENTITY (1, 1),

      OrderNumber varchar(50) NOT NULL

      )  ON [PRIMARY]

GO

ALTER TABLE dbo.Tmp_Orders SET (LOCK_ESCALATION = TABLE)

GO

SET IDENTITY_INSERT dbo.Tmp_Orders ON

GO

IF EXISTS(SELECT * FROM dbo.Orders)

       EXEC('INSERT INTO dbo.Tmp_Orders (OrderId, OrderNumber)

            SELECT OrderId, CONVERT(varchar(50), OrderNumber) FROM dbo.Orders WITH (HOLDLOCK TABLOCKX)')

GO

SET IDENTITY_INSERT dbo.Tmp_Orders OFF

GO

DROP TABLE dbo.Orders

GO

EXECUTE sp_rename N'dbo.Tmp_Orders', N'Orders', 'OBJECT'

GO

I wasn't thrilled about copying the entire table for our whole dataset.  I thought the change script should create a new column, copy the data to it, remove the old column, and rename the new column to the old column's name.  Googling about for a way to do this I found it was even easier in SQL Server:

ALTER table dbo.Orders ALTER Column OrderNumber varchar(50)

It's a good old one liner :)

Troubleshooting a failed login with correct user name and password

After setting up a new Database in SQL Server 2008 R2 and configuring DotNetNuke (DNN), an ASP.NET application, to connect to the new database I got received this message:
Connection Error(s):
Index #: 0
Source: .Net SqlClient Data Provider
Class: 14
Number: 18456
Message: Sql login failed

I open up SQL Server Management Studio and reset the user password.  Try again from DNN, same error.

I try connecting via SQL Server Management Studio.  Login failed.

I look in event Viewer and see
Login failed for user 'dotnetnuke'. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: <local machine>]

I open up up SQL Server Management Studio and connect to '.'

I right click the SQL Server 10.50.1600 node and

1. Select properties
2. Select Security Page
3. Under Server authentication change it to 'SQL Server and Windows Authentication mode'

Right click the SQL Server 10.50.1600 node again and select 'Restart' from the sub menu.

Back to the application and login is successful.

Access Denied on Adding a Database

Today I ran into a problem where I couldn't run a create script to create a local database in SQL Server Management Studio 2008 running on 64 bit Windows 7 Professional.  This is the error I received:

Msg 5123, Level 16, State 1, Line 1
CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\xxx.mdf'.
Msg 1802, Level 16, State 4, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

This is how it was fixed on my machine:

1. Fire up SysInternals Process Monitor
2. Open the 'Filter' menu
3. Click 'Filter...'
4. From the first dropdown select 'Path' in the second dropdown select 'contains'.
5. For the value enter '\data'
6. Click the 'OK' button to add the filter.
7. Retry the operation
8. Open up one of the NAME NOT FOUND entries with a Detail of Access Denied.
9. Click the Process tab
10. Note the process is sqlsvr.exe and the user is NT Authority\Network Service
11. Navigate to the Data directory in Windows Explorer
12. Click the Security tab
13. Click the 'Edit' Button
14. On the Permissions for 'Data' click the 'Add' button.
15. For the object name enter 'Network Service'
16. Click 'OK'
17. Disregard any errors regarding not being able to set the permissions on files.
18. Retry the operation in Sql Server Management Studio and it should work!

Unable to rename database in SQL Server Management Studio 2008

Today I wanted to rename a SQL Server 2008 database so I would have a rollback if my create from script went bad.

Upon rename SQL Server Management Studio 2008 gave me the error 'The database could not be exclusively locked to perform the operation. (Microsoft SQL Server, Error: 5030)'

I executed this in a query window connected to the master database:
sp_who2

Looking at the DBName column in the results for each match of the db I was trying to rename I executed 'kill xxx' where xxx was equal to the SPID of the row.

After doing this for all the rows rename succeeded immediately.

SQL Server Management Studio Generating Change Scripts

For those of you who need to lookup SQL syntax for things like alter table, this will let SQL Server Management Studio (SSMS) do the work for you giving you a baseline script to modify or check right into source control.

Generating an Alter Table Statement:

1. In SSMS use the object explorer to navigate to a table.
2. Right click and select design.
3. Make your changes.
4. From the menu select Table Designer --> "Generate Change Script..."
5. Copy and paste the script text or click the yes button to save it to a text file.

Adding a Foreign Key:

1. In SSMS use the object explorer to navigate to a table.
2. Expand the table node.
3. Right click on Keys  and select "New Foreign Key..."  Notice this brings up the table designer if it isn't already open.
4. Fill out the new FK and click Close
5. You should now be in the table designer.  Follow the steps from above.

Search all columns of all tables for a pattern

Today at work someone wanted to know how to search all columns of all tables for a pattern.  The code below uses a recursive select to generate dynamic SQL which will search all varchar columns for the specified pattern.  Not all columns are able to be cast to varchar.
declare @searchString varchar(max)
set @searchString = '%dealer sites%' --the pattern to search for


declare @queryToRun varchar(max) --internal variable.

--this variable needs to be non null otherwise it will make the recursive select return null.

set @queryToRun = ''

--create a statement to search all columns of all tables for the text.

SELECT @queryToRun = @queryToRun + 'select '''+t.table_name +'.'+c.column_name+''' from ' + QUOTENAME(t.TABLE_SCHEMA) + '.' + QUOTENAME(t.TABLE_NAME) + ' where ' + QUOTENAME(c.column_name) + ' like '''+ @searchString + ''' union '

                  FROM INFORMATION_SCHEMA.TABLES t JOIN

                  INFORMATION_SCHEMA.COLUMNS c on t.table_name = c.table_name

                  WHERE             TABLE_TYPE = 'BASE TABLE' and data_type='varchar'

--trim off the last union statement.

select @queryToRun = left(@queryToRun, datalength(@queryToRun) - datalength('union '))

--run the query.

exec (@queryToRun)

Recursive Selects

Last week at work I created some SQL Server functions to take a list of rows and turn them into a comma delimited string.  It feels like data formatting which should be done on the client side, but that was the method we went with.

It does show recursive selects though, which can also be used for dynamic cross tab queries.  At first this method didn't make any sense to me, but thinking about a select statement as a loop makes it make sense.

declare @siblings table (id int, firstName varchar(max))

insert into @siblings

      select 0, 'Jordan' UNION

      select 1, 'Mia'

declare @siblingString varchar(max)

set @siblingString = ''

select @siblingString = @siblingString + firstName + ', '

      from @siblings

if (datalength(@siblingString) > 2)

      select @siblingString = left(@siblingString, datalength(@siblingString) -2)

select @siblingString

Outputs 'Jordan, Mia'

Select * from table where column in %

This method allows you to filter a column based on a list supplied by the user or when the user doesn't supply anything to return all the results without using dynamic SQL.  You can use ANDs in the where clause to include additional filters.  In our application we parse a comma delimited string in SQL to get our list of user input.  SQL 2005 introduced table variables and you could probably use those instead.

--pretend this table contains all the states.

      declare @states table (identifier varchar(2))

      insert into @states

            select 'MI' UNION

            select 'CO' UNION

            select 'NY'

--this simulate states selected in the application

      declare @selectedStates table (identifier varchar(2))

--insert a state to simulate the user selecting a state.

      insert into @selectedStates --comment this line to see @allStates flip

            select 'MI'           --comment this line to see @allStates flip

declare @allStates bit

--if the user hasn't selected a state get all the results.

if ((select count(*) from @selectedStates) = 0)

      select @allStates = 1

--give back the results:

select identifier from @states

      where (identifier in (select identifier from @selectedStates) OR @allStates = 1) AND 1=1 --additional filters could go here instead of 1=1

00 Querying Named instance Linked SQL Server

This is how you query against a linked named instance of sql server.


sp_addlinkedserver 'servername\instanceName' --create the link.

select * from [servername\instanceName].databaseName.dbo.TableName --execute the query.

Clustered Index slows down BCP signiificantly

Our 19 hour job was reduced to about 30 minutes using this technique.

We had to import 110 million rows into a SQL Server table containing 5 ints during 4 import sessions. There was a clustered index over 4 of those ints.

We used the System.Data.SqlClient.SqlBulkCopy class to do the import (class mirroring the sql server bcp utility). The initial import took ~19 hours. On a new table we removed the clustered index and inserted all the rows again. This took about 12 minutes. Applying the clustered index took about 20 minutes.

Programmatically Executing SQL Scripts

I didn't realize GO is not a SQL keyword. When I was trying to execute a script that worked in SQL Server Management Studio I was getting SQLException Incorrect syntax near 'Go'. The solution is to split the input script on GOs and execute those scripts.

const char splitChar = '☻'; //this character should never appear in command files
if (createDatabaseScript.Contains(splitChar.ToString()) == true)
throw new Exception("Aborting. Splitting this file may break the script because the script contains the split character");
using (dbConnnection)
{
dbConnnection.Open();
SqlCommand createDbCommand = new SqlCommand(string.Empty, dbConnnection);

//split the input script into multiple scripts based on GOs
foreach (string command in createDatabaseScript.Replace("GO", splitChar.ToString()).Split(new Char[] { splitChar }))
{
try
{
createDbCommand.CommandText = command;
createDbCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
//log the error for later review.
results += ex.Message + Environment.NewLine;
}
}
}
Console.Write(results);

SQL Server random number generator

This SQL Script generates a random number between 1 and 5.

It demonstrates a few interesting ideas:
1) Temporary tables - These are in memory temporary tables (I use these all the time)
2) SQL Server looping with While (you may use these instead of cursors. I've heard cursors are slower)
3) Random number generation with newid() (select top 5 * from [table] order by newid() returns 5 random rows.
4)

declare @tempTable table (column1 int) --declaring a temporary table
declare @i int
set @i =1
While @i <=1000000 begin insert into @temptable (column1) select abs(cast(cast(newid() as varbinary) as int))%5+1 set @i = @i +1 end

--select * from @temptable

select column1 as [value],count(*) as [Occurrences],
cast(count(*)*100 / cast((select count(*) from @temptable) as decimal) as int) as [Percentage] from @temptable
group by column1