The Archives

SEARCH

I’m Attending

CodeMash

Tag Archives: SQL

SQL Server Backup and Restore Across Server Username Fix

Posted in: Programming by Steve on August 22, 2012

I run into this a lot while deploying new fixes. If you have a production and a stage environment, before deploying to production, you should backup and restore your database to the staging database to do all your UAT. However, if your usernames are the same, then you may run into an issue where SQL has an "orphaned" user that cannot be associated to the database (http://msdn.microsoft.com/en-us/library/ms175475.aspx).

USE [database_name]
GO
exec sp_change_users_login "auto_fix", "username";

That should get you back up and running.

Building SQL Server T-SQL with Error Handling

Posted in: Programming by Steve on December 1, 2010

Today, my manager asked me how I build my T-SQL scripts when I need to update a bunch of data in SQL Server.  I hadn’t really thought much about it, but this is what I normally use.

DECLARE @ErrorCode INT

BEGIN TRANSACTION

	--UPDATE A TABLE OR SOMETHING

	SELECT @ErrorCode = @@ERROR
	IF (@ErrorCode <> 0) GOTO PROBLEM
	
	--DO SOMETHING ELSE
	
	SELECT @ErrorCode = @@ERROR
	IF (@ErrorCode <> 0) GOTO PROBLEM	
	
COMMIT TRANSACTION
	
PROBLEM:
IF (@ErrorCode <> 0)
BEGIN
	PRINT 'Error Executing Script: Error Code: ' + @ErrorCode
	ROLLBACK TRANSACTION
END

Not sure how anyone else does it, but that’s how I roll.  Any suggestions on how this could be improved? 

Converting Seconds to HH:MM:SS Format in SQL

Posted in: Programming by Steve on October 17, 2010

In our web application, we needed to display a duration that is stored in seconds as hours, minutes and seconds.  We had a way to do this in our .NET code, but we needed this for a report that is rendered using reporting services.  We needed to find an easy way to display this in the report.

For example, if we have a field returning 5578 seconds, we want it to display as 1 hour, 32 minutes and 58 seconds with this format: 01:32:58

My manager found this nifty little conversion available in SQL Server 2005:

DECLARE @seconds INT
SET @seconds = 5578

SELECT CONVERT(varchar, DATEADD(s, @seconds, 0), 108) --Result is 01:32:58

Note that this only works if the duration is less than 24 hours. If you use this on a duration longer than 24 hours, it will reset at 00:00:00.

.NET Migrations and Legacy Databases

Posted in: Programming by Steve on October 10, 2010

Trying to get our web application database migrations has been trying to say the least. I have been investigating which one of the .NET migration strategies will work best for our application.

Ben Scheirman has a blog post that has a list of tools available for .NET developers to do migrations.  It is a good starting point to look into how you can get your app under control.

FluentMigrator has just reached 1.0 RC1 and looks to be very promising.

http://code.google.com/p/migratordotnet/

http://github.com/schambers/fluentmigrator

Determine All Tables Sizes in a Database

Posted in: Programming by Steve on October 7, 2010

I always forget this and have to look it up. 

DECLARE @TableName VARCHAR(100)

DECLARE tableCursor CURSOR FORWARD_ONLY
FOR 
select [name]
from dbo.sysobjects 
where  OBJECTPROPERTY(id, N'IsUserTable') = 1
FOR READ ONLY

CREATE TABLE #TempTable
(
    tableName varchar(100),
    numberofRows varchar(100),
    reservedSize varchar(50),
    dataSize varchar(50),
    indexSize varchar(50),
    unusedSize varchar(50)
)

OPEN tableCursor

WHILE (1=1)
BEGIN
    FETCH NEXT FROM tableCursor INTO @TableName
    IF(@@FETCH_STATUS<>0)
      BREAK;

    INSERT  #TempTable
        EXEC sp_spaceused @TableName
END

CLOSE tableCursor
DEALLOCATE tableCursor

UPDATE #TempTable
SET reservedSize = REPLACE(reservedSize, ' KB', '')

SELECT tableName 'Table Name',
numberofRows 'Total Rows',
reservedSize 'Reserved KB',
dataSize 'Data Size',
indexSize 'Index Size',
unusedSize 'Unused Size'
FROM #TempTable
ORDER BY CONVERT(bigint,reservedSize) DESC

DROP TABLE #TempTable

I got this script from Steve Smith’s blog. (thanks!)

Subtracting a record count from current records

Posted in: Programming by Steve on September 25, 2010

In our application, we have a need to archive some specific records in a table.  After the records are archived, more records could potentially be added after those that are not archived.  Here is an example table:

ID    |   InstanceNumber     |   IsArchived

5000  |         1            |     True
8347  |         2            |     True
9343  |         3            |     False
11048 |         4            |     False
15311 |         6            |     False

So the record set I am trying to return is this:

ID    |   InstanceNumber     |   IsArchived

9343  |         1            |     False
11048 |         2            |     False
15311 |         4            |     False

This results in records that are not archived, but with Instance Numbers that have been adjusted by subtracting the count of archived records. The SQL that I came up with is this:

DECLARE @tbl TABLE
(ID INT NOT NULL, InstanceNumber INT NOT NULL, IsArchived BIT NOT NULL)

INSERT INTO @tbl VALUES (5000, 1, 1)
INSERT INTO @tbl VALUES (8347, 2, 1)
INSERT INTO @tbl VALUES (9343, 3, 0)
INSERT INTO @tbl VALUES (11048, 4, 0)
INSERT INTO @tbl VALUES (15311, 6, 0)

SELECT ID, InstanceNumber - (SELECT MAX(InstanceNumber) FROM @tbl WHERE IsArchived = 1), IsArchived
FROM @tbl
WHERE IsArchived = 0

I am looking to optimize this query so it does not have any adverse affect on the report that it is needed for.

Any ideas? I even added a StackOverflow question about it.

How to Verify Your Stored Procedures

Posted in: Programming by Steve on May 5, 2010

One of the things I’ve been trying to do is to eliminate the unnecessary stored procedures from our project.  Currently, we have about 150 left, but a lot of them are still used.  Some of them are still left over and no longer are called from our application.  As I was building a new SQL install script, I was trying to run a script of all stored procedures in one database and create them all in a new one.  I found that some of them no longer would work because of changes we’ve made to the database that have broken these stored procedures without even knowing. 

I asked on twitter how this could be done and Argenis Fernandez (@afernandez) suggested that I look into a Powershell script to verify stored procedures still work.

After some investigation on how Powershell and SQL work together, I scraped together this script:

$server = "localhost";		# The SQL Server instance name
$database = "MyDatabase";		# The database name
 
# Load the SMO assembly
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
 
# Create the SMO objects
$srv = New-Object "Microsoft.SqlServer.Management.SMO.Server" $server;
$db = New-Object ("Microsoft.SqlServer.Management.SMO.Database");
 
# Get the database
$db = $srv.Databases[$database];
 
# For each stored procedure in the database
foreach($proc in $db.StoredProcedures)
{   
    # NOTE: my stored procedures are all prefixed with "web_"
    if ($proc.Name.StartsWith("web_"))
	{    
        $proc.TextBody = $proc.TextBody;
        $proc.Alter();        
    }    
}

If any error occurs, it will display which procedure name did not get altered properly.

In my application, all my stored procedures that I use in the websites are prefixed with “web_”.  This made it easy to check if the procedures used still work. 

SQL Query Madness

Posted in: Programming by Steve on April 15, 2010

I would consider myself to be pretty good with database design and queries, but this one has me stumped.

Our application allows administrators to add “User Properties” in order for them to be able to tailor the system to match their own HR systems.  For example, if your company has departments, you can define “Departments” in the Properties table and then add values that correspond to “Departments” such as “Jewelry”, “Electronics” etc…  You are then able to assign a department to users.

Here is the schema:

image

In this schema, a user can have only one UserPropertyValue per Property, but doesn’t have to have a value for the property.

I am trying to build a query that can use the PropertyValues as the filter for users.  My query looks like this (SQL Server 2005):

SELECT UserLogin, FirstName, LastName
FROM Users U
LEFT OUTER JOIN UserPropertyValues UPV 
	ON U.ID = UPV.UserID
WHERE UPV.PropertyValueID IN (1, 5)

When I run this, if the user has ANY of the property values, they are returned.  What I would like to have is where this query will return users that have values BY PROPERTY.

So if PropertyValueID = 1 is of Department (Jewelry), and PropertyValueID = 5 is of EmploymentType (Full Time), I want to return all users that are in Department Jewelry that are EmployeeType of Full Time, can this be done?

I’m not sure how this can be accomplished without building dynamic SQL, so I thought I would throw this out to see what I could find.


UPDATE:

I posted this question on StackOverflow to see if there are any more ideas

http://stackoverflow.com/questions/2647570/where-clause-on-joined-table-used-for-user-defined-key-value-pairs

Combining Date and Time in SQL Server

Posted in: Programming by Steve on March 5, 2009

I have been working with Log Parser to go through IIS data from the last week. In working with it, I found that the queries I wanted to run were a little obscure to me, and not having time to mess around with learning a new query language, I just imported the data into SQL.

The W3C logs store the date and time in separate columns, and for one of my queries, I wanted to combine the date part with the time part to create a single datetime field. 

So here’s my function that I created:

CREATE FUNCTION [dbo].[CombineDateTime](@dtDate DATETIME, @dtTime DATETIME)
RETURNS DATETIME
AS
BEGIN
    RETURN @dtDate + CONVERT(CHAR(8), @dtTime, 108)
END

Hope this helps!

Revisiting the slow SqlDataAdapter.Fill method

Posted in: Programming by Steve on January 30, 2009

In my previous post, Do Not Taunt sp_updatestats, I described a problem where a query was taking a lot longer than expected.  I followed my own advice and ran sp_updatestats on the database and tried to run my query from the website again. 

What happened? Timeout. The same thing was happening where my query was running immediately in Sql Server Management Studio, but not when using ADO.NET.

I used Red Gate ANTS Profiler to see which method was slowing down.  It determined that the calling of the SqlDataAdapter.Fill(DataTable) method was causing the issue.

I then refactored to use a DataReader to rule out any weirdness and the same issue occurred.  I then looked for any issues with long queries in .NET, but should be shorter in SSMS.

I came across a couple of articles mentioning that if you want to replicate this behavior in SSMS, run this statement before you execute your proc:

SET ARITHABORT OFF

This will mimic the behavior of ADO.NET. 

So how do you fix this?  There are 3 things I found that you could do.  First, make sure your database indexes are reindexed in some kind of maintenance plan.  I didn’t have any kind of maintenance on my database.  If you need to reindex immediately, execute this SQL:

-- Execute this to rebuild all for a given database. Replace the <databasename> after EXEC.
EXEC <databasename>..sp_MSforeachtable @command1='DBCC DBREINDEX (''*'')', @replacechar='*'

The next solution would be to execute “SET ARITHABORT ON” before running your stored procedure.

Using cn As New SqlConnection(Me.ConnectionString)
    Dim cmd As New SqlCommand("web_LongIntenseQuery", cn)
    cmd.CommandType = CommandType.StoredProcedure
    cmd.Parameters.Add("@UserID", SqlDbType.BigInt).Value = UserID
    cn.Open()
    'this will allow the sproc to run
    Dim arithabortCmd As New SqlCommand("SET ARITHABORT ON", cn)
    arithabortCmd.ExecuteNonQuery()

    Dim ret As Integer = ExecuteNonQuery(cmd)
    Return (ret = 1)
End Using

Or the last option would be to add the SET ARITHABORT ON in your sproc.

More on stored procedure performance from Steven Smith (ASPAdvice.com, SteveSmithBlog.com)