SQL Server Error 15023: 'User already exists in current database'

I received this error when copying a database from one server to another and then tried to bring up a web application.  The system user that came over with the database was orphaned from the existing login.

The solution is relatively easy (for one login).  Use the sp_change_users_login with the 'update_one' option.  This will directly map the user to the SQL login.

---

exec sp_change_users_login 'update_one', '[your database user]', '[the SQL login]'

---

Thanks to Pinal Dave's article on this subject.

Some useful SQL Date functions

I have been working with dates lately and have come up with a few useful SQL Date functions lately. I find myself recreating these functions on all the projects, so I have saved below in the form of a script.

-- ============================================================================
-- Author: Rob Lieving
-- Create date: 2009-07-08
-- Description: Returns the number of days in a month for the passed date/time
-- ============================================================================
CREATE FUNCTION [dbo].[fncDaysInMonth]
(
@date AS DATETIME
)
RETURNS INT
AS
BEGIN
RETURN DAY(DATEADD(mm, DATEDIFF(mm, -1, @date), -1))
END
GO
-- ============================================================================
-- Author: Rob Lieving
-- Create date: 2009-07-08
-- Description: Returns a date from 3 integers
-- ============================================================================
CREATE FUNCTION dbo.fncDate
(
@Year INT,
@Month INT,
@Day INT
)
RETURNS DATETIME
AS
BEGIN
RETURN DATEADD(dd, @Day - 1, DATEADD(mm, @Month - 1, DATEADD(yyyy, @Year - 1900, 0)))
END
GO
-- ============================================================================
-- Author: Rob Lieving
-- Create date: 2009-06-29
-- Description: Calculates the last day of the month
-- ============================================================================
CREATE FUNCTION [dbo].[fncLastDayOfMonth]
(
@dt DATETIME
)
RETURNS DATETIME
AS
BEGIN
RETURN DATEADD(dd, -DAY(DATEADD(m,1,@dt)), DATEADD(m,1,@dt))
END
GO
-- ============================================================================
-- Author: Rob Lieving
-- Create date: 2009-06-29
-- Description: Returns the first day of the month for a given date
-- ============================================================================
CREATE FUNCTION [dbo].[fncFirstDayOfMonth]
(
@dt DATETIME
)
RETURNS DATETIME
AS
BEGIN
RETURN DATEADD(day, -DAY(@dt) + 1, @dt)
END
GO
-- ============================================================================
-- Author: Rob Lieving
-- Create date: 2009-02-17
-- Description: Returns a smalldatetime with the time removed
-- ============================================================================
CREATE FUNCTION [dbo].[fncRemoveTime]
(
@dt AS DATETIME
)
RETURNS SMALLDATETIME
AS
BEGIN
RETURN CAST(DATEADD(month,((YEAR(@dt)-1900)*12)+MONTH(@dt)-1,DAY(@dt)-1) AS SMALLDATETIME)
END
GO

Debugging Print Statement

I am doing some Stored Procedure testing and trying to push performance as much as possible. One thing that helps is the ability to time how long individual statements take to execute. The print statement has to display the time down to the second or millisecond.

A useful print statement looks like this:

PRINT '[string value]: ' + CONVERT(varchar,GETDATE(),109)

How to Deploy and Test An SSIS Package

When working with SSIS, it is not immediately obvious how to deploy a package.  Following are my short notes on deploying an SSIS package*.

Deploy the Package

1. While in the Package designer, choose Project > [Package Name] Properties.  The Configuration manager dialog will appear.
2. Choose Deployment Utility from the tree.
3. Change the CreateDeploymentUtility option from False to True.  Note the DeploymentOutputPath variable.  Push OK to close the dialog.
4. Open the Solution Explorer and right-click on the .dtsx file and choose Properties.  Copy the Full Path variable and use it to find the bin\Deployment folder.
5. Locate the [Package Name].SSISDeploymentManifest file.  Double-click on the file and follow the steps outlined by the wizard to deploy the package.

Test the deployed Package

1. Open MSFT SQL Server Management Studio and choose Connect > Integration Services from the UI.  Choose the Server and connect.
2. The packages will be saved under the MSDB folder.  Right-click on the package to run it.

---

*  To re-deploy a package, follow steps 1-5 again.

Find the row counts for all the tables in a SQL Server 2005 database

Have you ever needed to investigate an application database and quickly determine where the records are?
The following script returns a rowset that contains all the table names of a SQL Server 2005 database along with the row counts.

---

-- declare the variables

DECLARE @i INT, -- integer holder

  @table_name VARCHAR(50), -- table name

  @sql NVARCHAR(800) -- dynamic sql

-- create the temp table. Initialize count column to -1

CREATE TABLE #t (

[name] NVARCHAR(128),

rows CHAR(11),

reserved VARCHAR(18),

data VARCHAR(18),

index_size VARCHAR(18),

unused VARCHAR(18)

)

SELECT @table_name = [name]

  FROM sysobjects

  WHERE xtype = 'U'

-- Initialize i to run at least once

SET @i = 1

-- loop while rows are still being selected

WHILE (@i > 0)

BEGIN

 -- create the dynamic sql that updates the row counts

 -- for each table

SET @sql = 'INSERT #t ([name], rows, reserved, data, index_size, unused)

EXEC sp_spaceused ['+ @table_name + ']'

 -- execute the dynamic sql

 EXEC sp_executesql @sql

 -- find out the name of the next table 

 SELECT @table_name = [name]

  FROM sysobjects

  WHERE xtype = 'U'

  AND [name] NOT IN

     (

        SELECT [name]

FROM #t

     ) 

 -- stop looking if no rows are selected

 SET @i = @@ROWCOUNT

END

-- return the results

SELECT *

FROM #t

ORDER BY reserved DESC

DROP TABLE #t

Script to change object owners

I have recently started working with SQL Server 2005, and the first thing I noticed is that I cannot automatically set the owner of a script at creation time.  SQL Server 2005 automatically adds my user name as the object owner.

The following script, adapted from Scott Forsyth's blog, batches up the changes.  Change the name of the old owner (@old) and the new owner (@new) and it will change the ownership for all the database objects.

Script to update SQL Server Object Ownership

DECLARE @old nvarchar(30), @new nvarchar(30), @obj nvarchar(50), @x int; SET @old = 'domain\user-name'; SET @new = 'dbo'SELECT @x = count(*); FROM INFORMATION_SCHEMA.ROUTINES a WHERE   a.ROUTINE_TYPE IN('PROCEDURE', 'FUNCTION')   AND a.SPECIFIC_SCHEMA = @old; while(@x > 0)   BEGIN     SELECT @obj = [SPECIFIC_NAME]     FROM INFORMATION_SCHEMA.ROUTINES a     WHERE        a.ROUTINE_TYPE IN('PROCEDURE', 'FUNCTION')        AND a.SPECIFIC_SCHEMA = @old;      SET @x = @@ROWCOUNT - 1;     EXEC sp_changeobjectowner @obj, @new; END

Find a column in SQL Server 2005

SELECT tbl.[name]
FROM sys.columns col, sys.tables tbl
WHERE col.object_id = tbl.object_id
AND col.name = '[column name]'

Removing the Date from SQL DateTime

This solution is short and efficient:

CONVERT(VARCHAR(10), GETDATE(), 101)

Text search in SQL Server Procs/Views

In SQL Server 2000, sometimes one needs to find all the dependencies of one object on another. I don't trust the dependency functionality in SQL Server, so I wrote a query to scour the syscomments table in any database.

The following query allows you to search for text within queries and views in SQL Server. Just open Query Analyzer and paste the following code, replace the search string with the object name, and run.

---

SELECT [name]
FROM [dbo].[sysobjects] obj
INNER JOIN [dbo].[syscomments] cmt
ON obj.[id] = cmt.[id]
where cmt.[text] like '%search string%'