How to Encrypt and Decrypt Text in SQL Server (2008+)

Here’s a simple example on how to encrypt (and decrypt) values in SQL Server:

How to Encrypt and Decrypt Text in SQL Server

 

--How to Encrypt and Decrypt Text in SQL Server (2008+)
--Example by Clint Huijbers

DECLARE
@Key1 NVARCHAR(50) = N'8g87g8ag4r8hinsg^%$#F&^F&^F^F&ÛIBOUG(%*^R&$%&#%^C5'
,@Key2 NVARCHAR(50) = N'&*^%R%^**F$F*OUYBYUB*F%74d654d7f685f65f56f6v6vc88d'
,@ValueToEncrypt VARCHAR(150) = 'This is the value that will be encrypted by the built-in function of SQL Server with two keys and 128-bits encryption :) blablablablablablablablablabl'
,@EncryptedValue VARBINARY(350) --Increase the VARBINARY size when you increase the lengh of @ValueToEncrypt!

SELECT @EncryptedValue = ENCRYPTBYPASSPHRASE((@Key1+N'||'+@Key2),@ValueToEncrypt)
SELECT @EncryptedValue

SELECT CONVERT(VARCHAR(150),DECRYPTBYPASSPHRASE((@Key1+N'||'+@Key2),@EncryptedValue))

 


 


 

Pause SQL Server service before restarting

Did you know that by pausing the SQL Server service before restarting the instance we allow end users to continue their work uninterrupted and we also stop any new connections to the instance? This is a nicer way of telling people to “get out” of the database in order for the server to be rebooted. I wouldn’t leave the server paused for 60 minutes of course, but I would rather use this method than forcibly disconnect users and rollback their transactions.

When a server is paused you will see messages similar to this in the SQL Server error log:

Error: 17142, Severity: 14, State: 0.
SQL Server service has been paused. No new connections will be allowed. To resume the
service, use SQL Computer Manager or the Services application in Control Panel.

Error: 18456, Severity: 14, State: 13.
Login failed for user ''. Reason: SQL Server service is paused.
No new connections can be accepted at this time. [CLIENT: ]

Next time you are worried about rebooting during the day think about the pause button instead. It might be a nice compromise for your end-users.

Thank Thomas Larock for his blog post: http://thomaslarock.com/2016/01/pause-sql-server-service-before-restarting/








T-SQL Metadata Queries

Stumbled upon this great post by Marco Schreuder about how to collect source system metadata with T-SQL queries:
The blog post is a few years old, but still the queries might come in handy.

TSQL Metadata Queries

The queries:

1. Get column information from all tables
2. Get column information from all views
3. Get details of foreign key constraints
4. Get details of the indices created on user tables








Having ‘INSERT EXEC nested issues’?

Without manual intervention, one level of INSERT…EXECUTE is the limit

An INSERT EXEC statement cannot be nested.

It is telling you is that you can’t have a proc that does an INSERT-EXEC operation call another proc that also does an INSERT-EXEC operation.

Two basic steps to solve this:

1) Create a #Temp table or @Table var
2) OPENROWSET your stored procedure data into it


INSERT INTO #YOUR_TEMP_TABLE
SELECT * FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off EXEC [ServerName].dbo.[StoredProcedureName] 1,2,3')



Or check out one of these links:
http://sqlserverplanet.com/sql/insert-stored-procedure-results-into-table/
http://blog.coryfoy.com/2005/07/inserting-the-results-of-a-stored-procedure-to-a-temp-table/
http://barry-king.com/2008/05/06/insert-into-temporary-table-from-stored-procedure/
http://www.informit.com/articles/article.aspx?p=25288&seqNum=6








OPENROWSET – The object has no columns or the current user does not have permissions on that object

Today I stumbled upon this little annoying issue.
If you are trying to run a stored procedure using OPENROWSET, for example:

SELECT *
FROM OPENROWSET('SQLNCLI','Server=(LOCAL);Trusted_Connection=Yes;Database=DB_Name','EXEC [DB_Name].dbo.SP_Name')

and you’ve received the following error:

Msg 50000, Level 16, State 1, Line 231
Cannot process the object [T-SQL statement]. The OLE DB provider "SQLNCLI10" for linked server [LinkedServerName] indicates that either the object has no columns or the current user does not have permissions on that object.

The thing is that I wasn’t able to solve it properly, just found a work-around thanks to Google:

By adding ‘SET FMTONLY OFF; SET NOCOUNT ON;‘ it will ignore the metadata as it seems, it will simply return the resultset without validating:

SELECT *
FROM OPENROWSET('SQLNCLI','Server=(LOCAL);Trusted_Connection=Yes;Database=DB_Name','SET FMTONLY OFF; SET NOCOUNT ON; EXEC [DB_Name].dbo.SP_Name')



More info about it:

The OLE DB provider “SQLNCLI10” for linked server indicates that either the object has no columns or the current user does not have permissions on that object.
When one of the columns has an invallid name this can occurs. This can be fixed by altering the sys.spt_columns_view by replacing the c.name column in quotename (c.name).

To avoid this error we need to check if our SP returns a result set that has two or more columns with the same name or does not have any column name. Next we need to set NOCOUNT ON at the beginning of the procedure (just after the begin statement). If these two things are correct then the statement will execute successfully.








From DATETIME(OFFSET) to VARCHAR(x), a world of differences :)

Always use the smallest data type, but in some cases this can really bite you.
In my example, I’ve both DATETIME and DATETIMEOFFSET types in a single table.
‘Simply’ converting/casting both to strings and expecting similar results, isn’t an option.

Instead of the ‘Jan 2 2012 9:15AM’, it would be nice to just have it like ‘2012-01-02 09:15:48’
But…you’ll have to do that yourself 🙂

datetime(offset)-to-(n)varchar-print

datetime(offset)-to-(n)varchar-select

Code

PRINT CAST('2012-01-02 09:15:48' AS datetime)
PRINT CAST('2012-01-02 09:15:48.0000000' AS datetimeoffset)
PRINT CAST('2012-01-02 09:15:48.7529001 +01:00' AS datetimeoffset)
PRINT ''
PRINT CONVERT(VARCHAR(100),CAST('2012-01-02 09:15:48' AS datetime),127)
PRINT CONVERT(VARCHAR(100),CAST('2012-01-02 09:15:48.0000000' AS datetimeoffset),127)
PRINT CONVERT(VARCHAR(100),CAST('2012-01-02 09:15:48.7529001 +01:00' AS datetimeoffset),127)

SELECT '1| -- '+CAST(CAST('2012-01-02 09:15:48' AS datetime) AS VARCHAR(100)) AS Results
UNION
SELECT '2| -- '+CAST(CAST('2012-01-02 09:15:48.0000000' AS datetimeoffset) AS VARCHAR(100))
UNION
SELECT '3| -- '+CAST(CAST('2012-01-02 09:15:48.7529001 +01:00' AS datetimeoffset) AS VARCHAR(100))
UNION
SELECT '4| -- '+CONVERT(VARCHAR(100),CAST('2012-01-02 09:15:48' AS datetime),127)
UNION
SELECT '5| -- '+CONVERT(VARCHAR(100),CAST('2012-01-02 09:15:48.0000000' AS datetimeoffset),127)
UNION
SELECT '6| -- '+CONVERT(VARCHAR(100),CAST('2012-01-02 09:15:48.7529001 +01:00' AS datetimeoffset),127)









T-SQL: How to generate a set of dates (time dimension) in a View itself?

Today I wanted to add all Saturdays and Sundays to our so called NonBusinessDays-table.
An easy join with a Time dimension/table would be fairly simple, but it also creates a dependency between the View and the Table itself. I didn’t want that, what I wanted to accomplish is to generate that mini Time dimension in the View itself (so without any other dependencies except for the actual so called NonBusinessDays-table).

So imagine something like:

SELECT * FROM dbo.tbl_NonBusinessDays
UNION
(
SELECT 'all Saturdays and Sundays between 2010 and 2020'
)

Normally a simple query like this would do the trick (inspired by this thread):

WITH AllWeekendDates(WeekendDate)
AS
(
SELECT CAST('2010-01-01 00:00:00.000' AS DATETIME) --From the year 2010
UNION ALL
SELECT DATEADD(DAY,1,WeekendDate) FROM AllWeekendDates
WHERE DATEPART(YEAR,DATEADD(DAY,1,WeekendDate)) < 2020 --Until the year 2020
)

SELECT
WeekendDate
,DATENAME(WEEKDAY,WeekendDate) AS DayName
FROM AllWeekendDates
WHERE DATENAME(WEEKDAY,WeekendDate) IN ('Saturday','Sunday')
OPTION (MAXRECURSION 0)

The results:

CTE-WeekendDays-Results

But! You’re not allowed to use the OPTION-statement in a View 🙂

OPTION (MAXRECURSION limit) is not allowed in view and UDF definitions

An easy thing to do is to move the ‘OPTION (MAXRECURSION 0)’ from inside the View to the select query that’s using the View:
http://benchmarkitconsulting.com/colin-stasiuk/2010/04/12/maxrecursion-with-a-cte-in-a-view/

Like this:

SELECT [WeekendDate]
FROM [dbo].[vw_WeekendDates]
OPTION (MAXRECURSION 0)

A trick that I found while Googling is to use numbers (like for example the surrogate key of a large table or by using ROW_NUMBER()):
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=139267 (bottom post)

SELECT DATEADD(d,number,'9/11/2009')AS Date
FROM master..spt_values
WHERE number BETWEEN 1 AND
DATEDIFF(d,'9/11/2009',GETDATE())
AND type = 'P'

Now, let’s combine the original query (that included the OPTION (MAXRECURSION 0) statement) with this fancy trick.
The ideal query to use in a View will be this one:


SELECT DATEADD(d, [Row],'2010-01-01') AS Date --From the year 2010
FROM (
SELECT TOP 10000 [Row] = ROW_NUMBER() OVER(ORDER BY v1.Number ASC)
FROM master.dbo.spt_values v1
CROSS JOIN master.dbo.spt_values v2
) d
WHERE DATENAME(WEEKDAY, DATEADD(d, [Row],'2010-01-01')) IN ('Saturday','Sunday')
AND DATEPART(YEAR, DATEADD(DAY, 1, DATEADD(d, [Row],'2010-01-01'))) < 2020 --Until the year 2020

The results:
WeekendDays-from-View-Results








How To: Generate a CSV file that only contains deltas without SSIS?

Hi there, I wrote this particular post for SQL Server Express users that would like to export data once in a while. If you’re running a Standard or Enterprise Edition of SQL Server, I’ll advise you to use SSIS to export data.

Want to export deltas (for BI/DWH purposes with SCD) to CSV files in SQL Server without using SSIS? You might want to check out the SQL code which uses xp_cmdshell .


Important note: Do not use this solution (with xp_cmdshell) on Production environments or other web/internet exposed SQL Server Instances. Xp_cmdshell poses a major security risk, unless it’s setup in a correct matter: How do you secure the SQL Server instance after enabling xp_cmdshell?


Here’s an example of such a file:
Deltas_ExampleDataset-20130727113829

The script (downloadable below) creates several database objects:
Export-deltas-to-CSV-file-SQL-Server-database-objects

Stored Procedures:
[dbo].[usp_GenerateDeltas_ExampleDataset]
This SP returns the example dataset.

[dbo].[usp_GenerateDeltas_ExampleDataset_Check]
This SP is generates an example dataset (TOP 1) to validate the code and verify the existince of all SQL Server Database Objects.

[dbo].[usp_GenerateDeltas_ExportToCSV]
This SP actually exports the dataset to a local CSV file (in C:\Temp in this example).


Scalar-valued Functions:
[dbo].[fn_DateTimeOffsetToDateId]
This SvF converts a DATETIMEOFFSET-value into a DATE-value, but in a different format like ‘99991231’ for 9999-12-31.

[dbo].[fn_DateTimeOffsetToTimeId]
This SvF converts a DATETIMEOFFSET-value into a TIME-value, but in a different format like ‘123031’ for 12:30:31.


Make sure that xp_cmdshell is enabled:
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE



Download the T-SQL code here:
Export-deltas-to-CSV-file-SQL (Word document – *.docx)

Have fun! 🙂


How To: Send an automated email on any report subscription failures (SSRS)

Ever wanted to create a process or job that will monitor all report subscriptions (SSRS) for you? It’s your lucky day mate 🙂

Here’s an example of an email that will be send:
(Made it a little bit less wide)

Failed-report-subscriptions-SSRS-email-preview

Download the full code here:
usp_SendMailOnReportSubscriptionFailures_ReportServerSQL1-SQL-code