Enable Remote Errors in SSRS

http://msdn.microsoft.com/en-us/library/aa337165(v=sql.105).aspx

SQL Server - Database Restore History

A handy script to see which databases have been restored, when, from where etc.

DECLARE @dbname sysname, @days int
SET @dbname = NULL 
SET @days = -30
SELECT
rsh.destination_database_name AS [Database],
rsh.user_name AS [Restored By],
CASE WHEN rsh.restore_type = 'D' THEN 'Database'
  WHEN rsh.restore_type = 'F' THEN 'File'
  WHEN rsh.restore_type = 'G' THEN 'Filegroup'
  WHEN rsh.restore_type = 'I' THEN 'Differential'
  WHEN rsh.restore_type = 'L' THEN 'Log'
  WHEN rsh.restore_type = 'V' THEN 'Verifyonly'
  WHEN rsh.restore_type = 'R' THEN 'Revert'
  ELSE rsh.restore_type 
 END AS [Restore Type],
rsh.restore_date AS [Restore Started],
bmf.physical_device_name AS [Restored From], 
 rf.destination_phys_name AS [Restored To]
FROM msdb.dbo.restorehistory rsh
INNER JOIN msdb.dbo.backupset bs ON rsh.backup_set_id = bs.backup_set_id
INNER JOIN msdb.dbo.restorefile rf ON rsh.restore_history_id = rf.restore_history_id
INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id
WHERE rsh.restore_date >= DATEADD(dd, ISNULL(@days, -30), GETDATE()) --want to search for previous days
AND destination_database_name = ISNULL(@dbname, destination_database_name) --if no dbname, then return all
ORDER BY rsh.restore_history_id DESC
GO

SQL Server - Cannot resolve collation conflict for UNION operation

Ever tried to UNION two SELECT statement from tables/databases with different collations and hit the error: "Cannot resolve collation conflict for UNION operation"?

Me neither until today - managed to workaround it with help of the examples posted here:

DROP TABLE CollationTest1
CREATE TABLE [dbo].[CollationTest1](
     [CT1ID] [int] IDENTITY(1,1) NOT NULL,
     [CT1] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS
) ON [PRIMARY]

INSERT INTO CollationTest1 (CT1) VALUES ('varchar string 1 in CT1')
INSERT INTO CollationTest1 (CT1) VALUES ('varchar string 2 in CT1')
INSERT INTO CollationTest1 (CT1) VALUES ('varchar string 3 in CT1')
------------------------------------------------------------------------------
DROP TABLE CollationTest2
CREATE TABLE [dbo].[CollationTest2](
     [CT2ID] [int] IDENTITY(1,1) NOT NULL,
     [CT2] [varchar](30) COLLATE SQL_Latin1_General_Cp437_BIN
) ON [PRIMARY]

INSERT INTO CollationTest2 (CT2) VALUES ('varchar string 1 in CT2')
INSERT INTO CollationTest2 (CT2) VALUES ('varchar string 2 in CT2')
INSERT INTO CollationTest2 (CT2) VALUES ('varchar string 3 in CT2')
------------------------------------------------------------------------------
------------------------------------------------------------------------------
-- unmatched collation
SELECT CT1ID, CT1
FROM CollationTest1
UNION
SELECT CT2ID, CT2
FROM CollationTest2
/*
Msg 446, Level 16, State 9, Line 1
Cannot resolve collation conflict for UNION operation.
*/

-- unmatched collation
SELECT CT1ID, CT1, CT2
FROM CollationTest1
INNER JOIN CollationTest2 ON CT1ID = CT2ID
WHERE LEFT(CT1, 1) = LEFT(CT2, 1)
/*
Msg 446, Level 16, State 9, Line 1
Cannot resolve collation conflict for equal to operation.
*/

------------------------------------------------------------------------------
-- Force column CollationTest1.CT1 to match collation of CollationTest2.CT2
SELECT CT1ID, CT1 COLLATE SQL_Latin1_General_Cp437_BIN
FROM CollationTest1
UNION
SELECT CT2ID, CT2
FROM CollationTest2
/*
Results:
CT1ID   CT1
1               varchar string 1 in CT1
2               varchar string 2 in CT1
3               varchar string 3 in CT1
1               varchar string 1 in CT2
2               varchar string 2 in CT2
3               varchar string 3 in CT2
*/


-- Force column CollationTest2.CT2 to match collation of CollationTest1.CT1
SELECT CT1ID, CT1
FROM CollationTest1
UNION ALL
SELECT CT2ID, CT2 COLLATE SQL_Latin1_General_CP1_CI_AS
FROM CollationTest2
/*
Results:
CT1ID   CT1
1               varchar string 1 in CT1
2               varchar string 2 in CT1
3               varchar string 3 in CT1
1               varchar string 1 in CT2
2               varchar string 2 in CT2
3               varchar string 3 in CT2
*/


-- matched collation
SELECT CT1ID, CT1, CT2
FROM CollationTest1
INNER JOIN CollationTest2 ON CT1ID = CT2ID
WHERE LEFT(CT1 COLLATE SQL_Latin1_General_Cp437_BIN, 1) = LEFT(CT2, 1)
/*
Results:
CT1ID   CT1                                             CT2
1               varchar string 1 in CT1 varchar string 1 in CT2
2               varchar string 2 in CT1 varchar string 2 in CT2
3               varchar string 3 in CT1 varchar string 3 in CT2
*/


-- matched collation
SELECT CT1ID, CT1, CT2
FROM CollationTest1
INNER JOIN CollationTest2 ON CT1ID = CT2ID
WHERE LEFT(CT1, 1) = LEFT(CT2 COLLATE SQL_Latin1_General_CP1_CI_AS, 1)
/*
Results:
CT1ID   CT1                                             CT2
1               varchar string 1 in CT1 varchar string 1 in CT2
2               varchar string 2 in CT1 varchar string 2 in CT2
3               varchar string 3 in CT1 varchar string 3 in CT2
*/

I also discovered sp_help which is handy to query the collation of objects.

SQL Server - Take Offline - Database is in transition...

Tried to take a database offline by simply right-clicking on the database in SQL Management Studio and selecting 'Tasks > Take Offline'

The dialog box which was presented just hung for ages and afterwards when trying to access the database is was giving the error:

Database 'myDatabase' is in transition. Try the statement later.

I would not have guessed it, but this post pointed me in the direction of restarting SQL Management Studio and everything was ok again!

SQL Server Full-Text Search

Episode 61 of the DeepFriedBytes podcast recently introduced me to 2 new SQL Server concepts. The first one was SQL Server Full-Text Search which I'd heard of as we use it for some of our business applications, but I've personally never really been involved. The second one is SQL Server Service Broker, which I must admit to having never heard of, but from the use cases described in the podcast it looks like something worth exploring further.

SQL Server Full-Text Search allows you to:

"perform linguistic searches against text data in full-text indexes by operating on words and phrases based on rules of a particular language such as English or Japanese. Full-text queries can include simple words and phrases or multiple forms of a word or phrase"

The basic steps to configure table columns in a database for full-text search are as follows:

1. Create a full-text catalog e.g.

use mydatabase
go
EXEC sp_fulltext_database 'enable'
go
CREATE FULLTEXT CATALOG mycatalog
go

2. On each table that you want to search, create a full-text index e.g.

CREATE FULLTEXT INDEX ON mydatabase.dbo.mytable
(
column_to_index
Language 0X0
)
KEY INDEX myindex ON mycatalog
WITH CHANGE_TRACKING AUTO

a. Identify each text columns that you want to include in the full-text index.

b. If a given column contains documents stored as binary data (varbinary, varbinary(max), or image data), you must specify a table column (the type column) that identifies the type of each document in the column being indexed.
c. Specify the language that you want full-text search to use on the documents in the column.
d. Choose the change-tracking mechanism that you want to use on the full-text index to track changes in the base table and its columns.

3. After the columns have been added to a full-text index, applications and users can run full-text queries on the text in the columns. These queries can search for any of the following:

• One or more specific words or phrases (simple term)
• A word or a phrase where the words begin with specified text (prefix term)
• Inflectional forms of a specific word (generation term)
• A word or phrase close to another word or phrase (proximity term)
• Synonymous forms of a specific word (thesaurus)
• Words or phrases using weighted values (weighted term)

SELECT    product_id
FROM      products
WHERE     CONTAINS(product_description, ”Snap Happy 100EZ”
              OR FORMSOF(THESAURUS,’Snap Happy’)
              OR ‘100EZ’)
AND       product_cost<200

SELECT    candidate_name,SSN
FROM      candidates
WHERE     CONTAINS(candidate_resume,”SQL Server”)
AND       candidate_division =DBA

And that's about it to get started...!

DeepFriedBytes Podcast: Episode 61: What should Developers know about SQL Server?

See: Full-Text Search on MSDN

SQL Server - Try, Catch and Throw Exception

Wasn't sure until now how to re-throw a caught error in SQL Server - turns out it's relatively straightforward using RAISERROR:

BEGIN TRY
    -- RAISERROR with severity 11-19 will cause execution to
    -- jump to the CATCH block.
    RAISERROR ('Error raised in TRY block.', -- Message text.
               16, -- Severity.
               1 -- State.
               );
END TRY
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

    -- Use RAISERROR inside the CATCH block to return error
    -- information about the original error that caused
    -- execution to jump to the CATCH block.
    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
               );
END CATCH;

Source: http://msdn.microsoft.com/en-us/library/ms178592.aspx

SQL Server – MAXDOP Settings to Limit Query to Run on Specific CPU

MAXDOP – Maximum Degree Of Parallelism can be set to restrict query to run on a certain CPU. Please note that this query cannot restrict or dictate which CPU to be used, but for sure, it restricts the usage of number of CPUs in a single batch:

USE AdventureWorks
GO
SELECT *
FROM Sales.SalesOrderDetail
ORDER BY ProductID
OPTION (MAXDOP 1)
GO

http://blog.sqlauthority.com/2010/03/15/sql-server-maxdop-settings-to-limit-query-to-run-on-specific-cpu/

SQL Server - Restoring 2008 Backup to 2005!

Having developed and populated a signicantly large database on my local SQLEXPRESS 2008 installation, I thought it would be a sinch to back it up and get our DBA to restore it to our SQL Server 2005 cluster - how wrong was I...

Determined to get around the problem without having to manually re-do any of the work to create or populate the database, I tried the following and it worked a treat:

• SQL Management Studio 2008 - right-click on Database > Tasks > Generate Scripts... - select Script Data and any other relevant options
• Create a batch file to execute each of the SQL scripts using SQLCMD

SQL Server - Drop/Truncate all Tables from a Database

Found this nice SP call to execute a statement for each table:

EXEC sp_MSforeachtable @command1 = "DELETE FROM ?"

EXEC sp_MSforeachtable @command1 = "TRUNCATE TABLE ?"

SQL Server - Row Count for All Tables

I found this nice simple query here to get a row count for all tables in a SQL Server database:

SELECT
    [TableName] = so.name,
    [RowCount] = MAX(si.rows)
FROM
    sysobjects so,
    sysindexes si
WHERE
    so.xtype = 'U'
    AND
    si.id = OBJECT_ID(so.name)
GROUP BY
    so.name
ORDER BY
    2 DESC

Installing SSMSE 2005 on Windows Server 2008 R2

Thanks to Chris McK's post here for this:

You have to use the 64 bit CMD prompt as administrator.
1. Create a shortcut to C:\Windows\SysWOW64\cmd.exe on the desktop
2. Right click the shortcut and Runas ADMINISTRATOR
3. Enter full path and file name: e.g. C:\Users\[YOUR NAME]\Downloads\SQLManagementStudio_x86_ENU.exe

Connect to SQL Server Using Windows Authentication in ASP.NET

Step 1. Configure a Connection String

<connectionStrings>
  <add name="MyDbConn1" 
       connectionString="Server=MyServer;Database=MyDb;Trusted_Connection=Yes;"/>
  <add name="MyDbConn2" 
      connectionString="Initial Catalog=MyDb;Data Source=MyServer;Integrated Security=SSPI;"/>
</connectionStrings> 

Step 2. Create a custom service account

• Create a Windows domain account
• Run the following Aspnet_regiis.exe command to assign the relevant ASP.NET permissions to the account: aspnet_regiis.exe -ga machineName\userName
• Use the Local Security Policy tool to grant the Windows account the Deny logon locally user right
• Use IIS Manager to create an application pool running under the new account's identity and assign the ASP.NET application to the pool.

For more details see: How To: Connect to SQL Server Using Windows Authentication in ASP.NET 2.0

SQL Server 2008 - Saving Changes is Not Permitted?

Extracted from CodeSlammer:

"Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created."

This is now the default behavior for any of the following changes to a table:

• Adding a new column to the middle of the table
• Dropping a column
• Changing column nullability
• Changing the order of the columns
• Changing the data type of a column

In order to prevent this default behavior, uncheck the following:

SQL Forward Engineering with Visio

After spending much time modelling an ERD in Visio 2007, you can imagine my frustration at discovering that the Professional edition is not able to generate the SQL DDL! However, Google and Doug Boude came to the rescue with this post which makes use of the Orthogonal Toolbox provided by Orthogonal Software to export the ERD to XML and an XSLT nicely formatted for SQL Server 2005.

Search All Columns of All Tables in a Database

DECLARE @SearchStr nvarchar(100)
SET @SearchStr = '03'

DROP TABLE #Results
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL
BEGIN
    SET @ColumnName = ''
    SET @TableName = 
    (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM     INFORMATION_SCHEMA.TABLES
        WHERE         TABLE_TYPE = 'BASE TABLE'
            AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
            AND    OBJECTPROPERTY(
                    OBJECT_ID(
                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped'
                           ) = 0
    )

    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
    BEGIN
        SET @ColumnName =
        (
            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM     INFORMATION_SCHEMA.COLUMNS
            WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                AND    TABLE_NAME    = PARSENAME(@TableName, 1)
                AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
                AND    QUOTENAME(COLUMN_NAME) > @ColumnName
        )

        IF @ColumnName IS NOT NULL
        BEGIN
            INSERT INTO #Results
            EXEC
            (
                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                FROM ' + @TableName + ' (NOLOCK) ' +
                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
            )
        END
    END    
END

SELECT ColumnName, ColumnValue FROM #Results

Source: How to search all columns of all tables in a database for a keyword?

SQL Server Developers Factsheet

A handy SQL Server factsheet/poster for the forgetful developer!

SQL Server 2005 - View Executed SQL Queries

How to view queries run on SQL Server 2005:

SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
    FROM sys.dm_exec_query_stats AS deqs
            CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
    ORDER BY deqs.last_execution_time DESC

Source: SQL SERVER - 2005 - Last Ran Query - Recently Ran Query

SQL Server 2005 Track & Notify DDL Events

A quick way to keep track of DDL events on any database:

1. Create a table to log the events:

CREATE TABLE [dbo].[DDLChangeLog]
  (
    [DDLChangeLog_ID] [int] IDENTITY(1, 1) NOT NULL,
    [InsertionDate] [datetime] NOT NULL CONSTRAINT [DF_ddl_log_InsertionDate] DEFAULT ( GETDATE() ),
    [CurrentUser] [nvarchar](50) NOT NULL CONSTRAINT [DF_ddl_log_CurrentUser] DEFAULT ( CONVERT([nvarchar](50), USER_NAME(), ( 0 )) ),
    [LoginName] [nvarchar](50) NOT NULL CONSTRAINT [DF_DDLChangeLog_LoginName] DEFAULT ( CONVERT([nvarchar](50), SUSER_SNAME(), ( 0 )) ),
    [Username] [nvarchar](50) NOT NULL CONSTRAINT [DF_DDLChangeLog_Username] DEFAULT ( CONVERT([nvarchar](50), original_login(),(0)) ),
    [EventType] [nvarchar](100) NULL,
    [objectName] [nvarchar](100) NULL,
    [objectType] [nvarchar](100) NULL,
    [tsql] [nvarchar](MAX) NULL
  )
ON  [PRIMARY]

2. Create a database level trigger to capture the events:

IF EXISTS (SELECT * FROM sys.triggers WHERE name = N'trgLogDDLEvent' AND parent_class=0)
    DROP TRIGGER [trgLogDDLEvent] ON DATABASE

GO
CREATE TRIGGER trgLogDDLEvent ON DATABASE 
    FOR DDL_DATABASE_LEVEL_EVENTS 
AS
BEGIN

    --Enable ARITHABORT to avoid exceptions
    SET ARITHABORT ON

    --Declarations
    DECLARE @data XML;
    DECLARE @username NVARCHAR(MAX);
    DECLARE @db NVARCHAR(MAX);
    DECLARE @server NVARCHAR(MAX);
    DECLARE @html NVARCHAR(MAX);
    DECLARE @emailsubject NVARCHAR(2048);

    --Capture the database event data
    SET @data = EVENTDATA();
    SET @username = @data.value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(max)');
    SET @db = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(max)');
    SET @server = @data.value('(/EVENT_INSTANCE/ServerName)[1]','nvarchar(max)');

    --Log the event
    IF @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(max)') <> 'CREATE_STATISTICS' 
    BEGIN 

        INSERT  INTO DDLChangeLog (
               EventType, 
               ObjectName, 
               ObjectType, 
               tsql 
        ) 
        VALUES ( 
               @data.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(max)'), 
               @data.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)'), 
               @data.value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(max)'), 
               @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]','nvarchar(max)') 
        )

        --Format the SQL as pretty HTML
        SELECT TOP 1 @html = COALESCE(@html, ' <style type="text/css"> 
                              <!-- 
                              #changes { 
                                             border: 1px solid silver; 
                                             font-family: Arial, Helvetica, sans-serif; 
                                             font-size: 11px; 
                                             padding: 10px 10px 10px 10px; 
                              } 
                              #changes td.date { 
                                             font-style: italic; 
                              } 
                              #changes td.tsql { 
                                             border-bottom: 1px solid silver; color: #00008B;            
                              } 
                              -->     
                              </style>
                              <table id="changes"> 
                              ') + '<tr class="recordtop"> 
                                             <td class="date">' + CONVERT(CHAR(18), InsertionDate, 113) + '</td> 
                                             <td class="currentuser">' + currentUser + '</td> 
                                             <td class="loginname">' + LoginName + CASE WHEN loginName <> UserName THEN '(' + UserName + ')' ELSE '' END + '</td> 
                                             <td class="eventtype">' + EventType + '</td> 
                                             <td class="objectname">' + ObjectName + ' (' + objectType + ')' + '</td>
                              </tr> 
                              <tr class="recordbase"><td colspan="6" class="tsql"><pre>' + tsql + '</pre></td></tr> 
                              ' 
        FROM           DDLChangeLog 
        ORDER BY       insertionDate DESC; 
        SELECT         @html + ' </table>';
        
        --Notify via email
        SELECT @emailsubject = 'DDL Statement on ' + @server + ': ' +  + @db;

        EXEC msdb.dbo.sp_send_dbmail 
               @recipients ='myemail@gmail.com', 
               @body = @html,  
               @subject = @emailsubject, 
               @profile_name = 'SQLMail',
               @importance = 'high',
               @body_format = 'HTML';
    END
END

Oracle <-> SQL Server Equivalents

A growing list of comparitive SQL functions for transition from Oracle to SQL Server:

Replacing Null Values
Oracle: NVL(field1, 'THIS IS NULL')
SQL Server: ISNULL(field1, 'THIS IS NULL')

Rownum
Oracle: rownum
SQL Server: row_number() over(ORDER BY field1)

Replace Characters in String
Oracle: REPLACE(field1, ';', ',')
SQL Server: REPLACE(field1, ';', ',')

Replace New Line Characters in String (CHR vs. CHAR)
Oracle: REPLACE(REPLACE(REPLACE(field1, CHR(10), ' '), CHR(13), ' '), CHR(9), ' ')
SQL Server: REPLACE(REPLACE(REPLACE(field1, CHAR(10), ' '), CHAR(13), ' '), CHAR(9), ' ')

ToChar for DD/MM/YYYY HH24:MM:SS
Oracle: TO_CHAR(datefield1,'dd/mm/yyyy hh24:mm:ss')
SQL Server: convert(varchar,datefield1,103) + ' ' + convert(varchar,datefield1,108)

Outer Join
Oracle: table1.id =+ table2.id
SQL Server: table1.id =* table2.id

'IN' List
Oracle: IN list limited to 1000 expressions
SQL Server: 18,000 didn't hit any limits!

Decode
Oracle: Decode(field1,'Yes','Y','N')
SQL Server: CASE field1 WHEN 'Yes' THEN 'Y' ELSE 'N' END AS "Field 1"