SSRS: Remove Duplicates in Parameter Drop-down

While SharePoint was being queries from a SSRS report, GroupBy did not work in the CAML query in the DataSet definition of the SSRS report.

 One workaround was to use a global VB function inside the report, which removes duplicate parameter values in a parameter dropdown.

https://social.technet.microsoft.com/wiki/contents/articles/36632.ssrs-remove-duplicate-filter-values-from-parameter-drop-down-using-vb-code.aspx

Insert Stored Procedure Result into Table

The following does not work unfortunately unless table1 already exists with columns with matching field names, data types and field order.

INSERT INTO table1
exec sp_GetProducts @CategoryID=12

Stored procedures that returns tabular results in SQL Server do not allow saving the results in a new table like we would expect (The receiving table must exist with all columns' data types and fields names ahead of time for the query to work).
The workaround is to use either OpenRowset or by creating a new function.

sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

SELECT *
INTO table1
FROM OPENROWSET (
  'sqlncli',
  'server=(local)\SQL12;trusted_connection=yes',
  'exec Northwind.dbo.sp_GetProducts @CategoryID=12'
);

When using a user-defined function instead, we are basically re-writing the stored procedure in user-defined function.

CREATE FUNCTION GetProductsByCategory
(
    @CategoryID int
)
RETURNS TABLE
AS
    RETURN
        select * from dbo.Products where CategoryID=@CategoryID
GO

SELECT *
INTO table1
FROM GetProductsByCategory(12);
GO

SQL Server Database and Log File Details

Quick way to retrieve a database and its log file size, etc...

/*-- Get all databases' mdf and ldf info, including logical names --*/
SELECT * FROM SYS.MASTER_FILES

/*-- Get mdf info (more detail) -- */
SELECT * FROM SYS.DATABASES

/*-- Get mdf info (less detail)  --*/
SELECT * FROM SYS.SYSDATABASES

/*-- Get database info -- */
EXEC sp_helpdb

/*-- Get database info (mdf and ldf), including logical names --*/
EXEC sp_helpdb @dbname = N'Northwind'

Get "exec sp_who2" into a table

Running sp_who2 will quickly display all connections to a SQL Server instance. Below is an enhancement to sp_who2 that allows database-specific connections.

DECLARE @DB_NAME varchar(1000)
SELECT @DB_NAME = 'Northwind_DB'

DECLARE @AllConnections TABLE(
    SPID INT,
    Status VARCHAR(MAX),
    LOGIN VARCHAR(MAX),
    HostName VARCHAR(MAX),
    BlkBy VARCHAR(MAX),
    DBName VARCHAR(MAX),
    Command VARCHAR(MAX),
    CPUTime INT,
    DiskIO INT,
    LastBatch VARCHAR(MAX),
    ProgramName VARCHAR(MAX), 
    SPID_1 INT,
    REQUESTID INT
)

INSERT INTO @AllConnections EXEC sp_who2
SELECT * FROM @AllConnections WHERE DBName = @DB_Name
ORDER BY HostName

Database Drop takes a long time when deleting backup history option is selected in SQL Server

Here's a workaround to speed up database drop. When there are a long history of backup in MSDB database, dropping a database with "Delete backup history" option could take a while.

Update statistics on MSDB tables and create an index for backupset table for [database_name] column. 

/* Update statistics in [msdb] database */
   
USE msdb; 
GO
UPDATE STATISTICS backupfile; 
GO
UPDATE STATISTICS backupmediafamily; 
GO
UPDATE STATISTICS backupmediaset; 
GO
UPDATE STATISTICS backupset; 
GO
UPDATE STATISTICS restorefile; 
GO
UPDATE STATISTICS restorefilegroup; 
GO
UPDATE STATISTICS restorehistory; 
GO


/* Create an index on [backupset] table in [msdb] database for [database_name] column */

Create index IX_backupset_database_name on backupset(database_name); 
GO

Sample Databases (AdventureWorks)

Various SQL Server sample databases including the AdventureWorks2012 sample databases are available at this location.

https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks

Remove time info from getdate()

In SQL Server, a quick efficient way to remove time info from getdate() is

Select [Today] = DateAdd(dd, DateDiff(dd, 0, getdate()), 0)

The result would be something like '2017-05-04 00:00:00.000', which is handy when comparing against date-only column values.

SQL Server Transaction - Basic Syntax Example

A quick refresher on proper SQL Server transaction syntax example:

BEGIN TRANSACTION;
BEGIN TRY

    UPDATE dbo.Users set Acitve = 1 Where UserID = 23398

END TRY
BEGIN CATCH

    SELECT 
        ERROR_NUMBER() AS ErrorNumber
       ,ERROR_SEVERITY() AS ErrorSeverity
       ,ERROR_STATE() AS ErrorState
       ,ERROR_PROCEDURE() AS ErrorProcedure
       ,ERROR_LINE() AS ErrorLine
       ,ERROR_MESSAGE() AS ErrorMessage;

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION

END CATCH

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION

ActiveX Script Task in SSIS 2005 and 2008

ActiveX Script Task in SSIS requires the following library to be registered manually using regsvr32.exe on the Windows OS that runs SQL Server.

Otherwise, the SSIS package may fail when it is run as a SQL Server job.

[SQL Server 2005]
regsvr32.exe "C:\Program Files\Microsoft SQL Server\90\COM\AXSCPHST90.DLL"

[SQL Server 2008]
regsvr32.exe "C:\Program Files\Microsoft SQL Server\100\COM\AXSCPHST.DLL"

Another element of surprise to keep in mind is to try configuring the SSIS package to run in 32-bit mode. I realize that SSIS Task such as Excel Destination would only work when run in 32-bit mode. 


When you create a SQL 2008 SSIS package on 64-bit Windows 7, for example, the SSIS package defaults to 64-bit environment (as well as .NET Framework 2.0). You can change these default settings in the property section of either at the Task-scope or project-scope.


Of course, if you choose to use 32-bit environment when you complete your SSIS package, you'd have to run the SQL Server job that runs the SSIS package in 32-bit mode as well. You can set this option in the job properties window.

Cannot find server certificate with thumbprint error ... TDE error

Situation 

1. Primary SQL Server's database has TDE (Transparent Data Encryption) enabled and encrypted with Master key (private key). 
2. The database backup has been made and backup file was copied to a secondary SQL Server. 
3. When restoring the database on a secondary SQL Server, "Cannot find server certificate with thumbprint error" happens.

Solution:

1. Backup the certificate with master key on the Primary Server

• BACKUP CERTIFICATE  [EncryptionCertificate]
  TO FILE = 'Certificate File path'
  WITH PRIVATE KEY (FILE = 'Master Key File path.dat', ENCRYPTION BY PASSWORD ='password')

2. Restore the certificate with master key password on the Secondary Server

• CREATE CERTIFICATE [EncryptionCertificate]
  FROM FILE='Certificate File path'
  WITH PRIVATE KEY ( FILE =  'Master Key File path.dat' , DECRYPTION BY PASSWORD ='password')

Concatenate Multiple Rows into Single Row using XML PATH

Use Northwind;
GO

select 
  c.CustomerID, c.CompanyName, c.City, c.Country
  , [Orders] = 
  STUFF((
      select ', ' + CAST(o.OrderID as VARCHAR) 
      + ' (' + convert(varchar, o.OrderDate, 101) + ')'
      from Orders o  
      where o.CustomerID = c.CustomerID
      for XML PATH ('')), 1, 2, '')
from Customers c
left join Orders o on o.CustomerID = c.CustomerID
group by 
c.CustomerID, c.CompanyName, c.City, c.Country
order by c.CustomerID

Results: 

CustomerID	CompanyName	Orders
ALFKI	Alfreds Futterkiste	10643 (08/25/1997), 10692 (10/03/1997), 10702 (10/13/1997), 10835 (01/15/1998), 10952 (03/16/1998), 11011 (04/09/1998)
ANATR	Ana Trujillo	10308 (09/18/1996), 10625 (08/08/1997), 10759 (11/28/1997), 10926 (03/04/1998)
ANTON	Antonio Moreno	10365 (11/27/1996), 10507 (04/15/1997), 10535 (05/13/1997), 10573 (06/19/1997), 10677 (09/22/1997), 10682 (09/25/1997), 10856 (01/28/1998)

Keep SSRS staying active

It does take a long time for the first SSRS report to run after worker process shutdown (default = 7200 min or every 12 hours). This behavior is a nuisance but it is due to the many chores that take place to initialize SSRS service.

The following article seems to be a good way to automate to keep SSRS stay active at all time.

 http://gallery.technet.microsoft.com/scriptcenter/SSRS-Wake-Up-Call-b2026b2c#content

Good Pivot Example for T-SQL

Pivot in T-SQL is very useful for ad-hoc reports. It's syntax is easy to forget. I found the following example from here. Very good example indeed.


SELECT pId,_year,amount
FROM Products

..produces the following.

pId _year amount

124 2001 125

125 2001 454

126 2001 75

127 2002 256

128 2004 457

129 2004 585

130 2002 142

131 2002 785

132 2005 452

133 2005 864

134 2005 762

135 2004 425

136 2003 452

137 2003 1024

138 2003 575

..using Pivot as follows..

SELECT * FROM

(

SELECT pId,_year,amount

FROM Products

)t

PIVOT (SUM(amount) FOR _year

IN ([2001],[2003])) AS pvt

.. You have this result... Nice!

pId 2001 2003

124 125 NULL

125 454 NULL

126 75 NULL

127 NULL NULL

128 NULL NULL

129 NULL NULL

130 NULL NULL

131 NULL NULL

132 NULL NULL

133 NULL NULL

134 NULL NULL

135 NULL NULL

136 NULL 452

137 NULL 1024

138 NULL 575

.. Now to make it better by implementing the year as dynamic query..

You can dynamically generate _year list as follows.

DECLARE @years VARCHAR(2000)

SELECT  @years = STUFF(( SELECT DISTINCT

'],[' + ltrim(str(_year))

                        FROM    Products

                        ORDER BY '],[' + ltrim(str(YEAR(_year)))

                        FOR XML PATH('')

), 1, 2, '') + ']'

.. which returns [2001],[2002],[2003],[2004],[2005]

.. To complete the PIVOT query with dynamically generated year list...

DECLARE @query VARCHAR(4000)

DECLARE @years VARCHAR(2000)

SELECT  @years = STUFF(( SELECT DISTINCT

'],[' + ltrim(str(_year))

                        FROM    Products

                        ORDER BY '],[' + ltrim(str(YEAR(_year)))

                        FOR XML PATH('')

), 1, 2, '') + ']'

SET @query =

  'SELECT * FROM

  (

SELECT pId,_year,amount

FROM Products

  )t

  PIVOT (SUM(amount) FOR _year

  IN ('+@years+')) AS pvt'

EXECUTE (@query)

.. And the final result...Nice!!!

pId 2001 2002 2003 2004 2005

124 125 NULL NULL NULL NULL

125 454 NULL NULL NULL NULL

126 75 NULL NULL NULL NULL

127 NULL 256 NULL NULL NULL

128 NULL NULL NULL 457 NULL

129 NULL NULL NULL 585 NULL

130 NULL 142 NULL NULL NULL

131 NULL 785 NULL NULL NULL

132 NULL NULL NULL NULL 452

133 NULL NULL NULL NULL 864

134 NULL NULL NULL NULL 762

135 NULL NULL NULL 425 NULL

136 NULL NULL 452 NULL NULL

137 NULL NULL 1024 NULL NULL

138 NULL NULL 575 NULL NULL

SQL Server Login Migration Scripts

If you haven't visited http://www.overachiever.net/, you'll be surprised to find out the usefulness of tutorial materials available for free.

My work has several SQL Server 2000 and 2005 and will soon begin to implement 2008. From time to time, I wanted to have reliable scripts that can assist me in migrating logins quickly when database migration needed to be done between servers.

You will find video tutorial plus all the scripts that cover for login migration between multiple versions of SQL Servers here, i.e., SQL Server 2000 to 2005, SQL Server 2005 to 2005, etc.

Really excellent materials!

Is varchar a good candidate for primary key?

Is varchar a good candidate for primary key?



The answer is: No.

Variable-length data is a poor choice to be a primary key. It is better to use fixed length data such as Int, BigInt or CHAR(10) for a primary key candidate.