Comma delimited string to table

CREATE FUNCTION [dbo].[ufn_CSVToTable] ( @StringInput VARCHAR(8000) )
RETURNS @OutputTable TABLE ( [String] VARCHAR(10) )
AS
BEGIN

DECLARE @String VARCHAR(10)

WHILE LEN(@StringInput) > 0
BEGIN
SET @String = LEFT(@StringInput,
ISNULL(NULLIF(CHARINDEX(',', @StringInput) - 1, -1),
LEN(@StringInput)))
SET @StringInput = SUBSTRING(@StringInput,
ISNULL(NULLIF(CHARINDEX(',', @StringInput), 0),
LEN(@StringInput)) + 1, LEN(@StringInput))

INSERT INTO @OutputTable ( [String] )
VALUES ( @String )
END

RETURN
END
GO

SQL Server performance counters are missing in Windows 64bit

When you run the PERFMON on a 64 bit windows server you are actually running the 64bit version of the application which can only load 64bit counters, but not counters for a local 32-bit applications. To workaround the problem, launch the application using /32 switch, like this mmc /32 perfmon.msc

Which action fired the trigger?

Using the following logic you can identify which action cased the trigger to fire

if exists (select 1 from deleted) and exists (select 1 from inserted)
print ‘Update’

if exists (select 1 from inserted) and not exists (select 1 from deleted)
print ‘Insert’

if exists (select 1 from deleted) and not exists (select 1 from inserted)
print ‘Delete’

Display trigger status

--version sql server 2000

SELECT
name,
status = CASE WHEN OBJECTPROPERTY (id, 'ExecIsTriggerDisabled') = 0
THEN 'Enabled' ELSE 'Disabled' END,
owner = OBJECT_NAME (parent_obj)
FROM
sysobjects
WHERE
type = 'TR'

Rotate a table in SQL Server

Rows to Column

Source Table

Year|Quarter|Amount
========================
1995|1 |125,000.90
1995|2 |136,000.75
1995|3 |212,000.34
1995|4 |328,000.82
1996|3 |728,000.35
1996|2 |422,000.13
1996|1 |328,000.82

Result Table

YEAR | Q1 | Q2 | Q3 | Q4
-------------------------------------------------------------------
1995 | 125,000.90 | 136,000.75 | 212,000.34| 328,000.82
1996 | 328,000.82 | 422,000.13 | 728,000.35| 0.00


SQL

SELECT year=q.year,
SUM(CASE quarter WHEN 1 THEN amount ELSE 0 END) as Q1,
SUM(CASE quarter WHEN 2 THEN amount ELSE 0 END) as Q2,
SUM(CASE quarter WHEN 3 THEN amount ELSE 0 END) as Q3,
SUM(CASE quarter WHEN 4 THEN amount ELSE 0 END) as Q4
FROM qtrsales q
GROUP BY year

Columns to Row

Source Table

ID | RefDate |Rcvd Date
----------------------------
1 | 01/01/04| 02/01/04
2 | 05/01/04| 07/01/04
3 | 07/01/04| 12/01/04

Result Table

ID | Date | Type
----------------------------
1 | 01/01/04| RefDate
1 | 02/01/04| RcvdDate
2 | 05/01/04| RefDate
2 | 07/01/04| RcvdDate
3 | 07/01/04| RefDate
3 | 12/01/04| RcvdDate

SQL

SELECT ID, RefDate, 'RefDate' AS Type FROM myTable
UNION
SELECT ID, RcvdDate, 'RcvdDate' AS Type FROM myTable

How to transfer database diagrams to a different database

Database diagrams are stored in the 'dtproperties' table within the database. So, database diagrams can be transferred to a different database, by transferring the contents of this table.

For example , run the following query to transfer the diagram named 'MyTableDesign' from 'pubs' database to 'northwind':

INSERT northwind..dtproperties
SELECT objectid, property, value, lvalue, version
FROM pubs..dtproperties
WHERE objectid =
(
SELECT objectid
FROM pubs..dtproperties
WHERE value='MyTableDesign'
)

Make sure, the tables referenced by these diagrams already exist in the target database, or else these diagrams won't show up in the target database.

Moving SQL Server 2000 databases to another machine

1.Detach all user databasese from source server
2.Stop SQL Server service on the source server
3.Copy the master, msdb and model database to the destination server

Attaching MSDB database on to destination server

To attach the MSDB database, you must startt SQL Server together with the -c option, the -m option, and trace flag 3608. Trace flag 3608 prevents SQL Server from recovering any database except the master database. To add the -c option, the -m option, and trace flag 3608, follow the steps in the "Moving the model database" section. After you add the -c option, the -m option and trace flag 3608, follow these steps:
1. Stop, and then restart SQL Server.
2. Make sure that the SQL Server Agent service is not currently running.

3. Detach the msdb database as follows:
use master
go
sp_detach_db 'msdb'
go
4. Move the Msdbdata.mdf and Msdblog.ldf files from the current location (D:\Mssql8\Data) to the new location (E:\Mssql8\Data).
5. Remove -c -m -T3608 from the startup parameters box in Enterprise Manager.
6. Stop and then restart SQL Server.
7. Reattach the msdb database as follows:
use master
go
sp_attach_db 'msdb','E:\Mssql8\Data\msdbdata.mdf','E:\Mssql8\Data\msdblog.ldf'
go

attaching the master database on to destination server

1. Change the path for the master data files and the master log files in SQL Server Enterprise Manager.

Note You may also change the location of the error log here.
2. Right-click the SQL Server in Enterprise Manager and then click Properties.
3. Click Startup Parameters to see the following entries:
-dD:\MSSQL7\data\master.mdf
-eD:\MSSQL7\log\ErrorLog
-lD:\MSSQL7\data\mastlog.ldf
-d is the fully qualified path for the master database data file.

-e is the fully qualified path for the error log file.

-l is the fully qualified path for the master database log file.
4. Change these values as follows:
a. Remove the current entries for the Master.mdf and Mastlog.ldf files.
b. Add new entries specifying the new location:
-dE:\SQLDATA\master.mdf
-lE:\SQLDATA\mastlog.ldf


5. Stop SQL Server.
6. Copy the Master.mdf and Mastlog.ldf files to the new location (E:\Sqldata).
7. Restart SQL Server

Moving Temp database
You can move tempdb files by using the ALTER DATABASE statement.

1. Use the ALTER DATABASE statement, specifying the logical file name as follows:use master
go
Alter database tempdb modify file (name = tempdev, filename = 'E:\Sqldata\tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = 'E:\Sqldata\templog.ldf')
go
2. Stop and then restart SQL Server.

Attaching User database
1. use exec sp_attach_db
use master
go
EXEC sp_attach_db @dbname = N'pubs',
@filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf',
@filename2 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log.ldf'
go