SQL CLR Error: Publishing SQL Server Project from C# Visual Studio

Created new SQL Server project in Visual studio, to generate dll file having SQL function, when we tried to use published function we ended with below error message:

Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option.

To fix the above issue enable CLR in sql server:


sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

After applying the code we tried to execute the created function, but ended in below error:

A .NET Framework error occurred during execution of user-defined routine or aggregate "<functionname>":
System.Security.HostProtectionException: Attempted to perform an operation that was forbidden by the CLR host.

The protected resources (only available with full trust) were: All
The demanded resources were: MayLeakOnAbort

System.Security.HostProtectionException:
   at UserDefinedFunctions.<functionname>(SqlDateTime pstTime, String Timezone)

To overcome the above issue, run below commands:

ALTER ASSEMBLY <functionname> WITH PERMISSION_SET = Safe

ALTER DATABASE <DBNAME>
SET TRUSTWORTHY ON


ALTER ASSEMBLY Timezone WITH PERMISSION_SET = UnSafe

Convert Unix Timestamp to Date Format

SELECT  DATE_FORMAT(FROM_UNIXTIME(TimeStamp), '%d-%m-%Y %h:%i:%s') AS LogDate
FROM dbo.reportlog
ORDER BY Timestamp DESC
LIMIT 10

T-SQL to Diable all Jobs in SQL Agent

USE MSDB;
GO
UPDATE MSDB.dbo.sysjobs
SET Enabled = 0
WHERE Enabled = 1;
GO

ODBC Connectivity Issue - Missing System DSN connection in SSIS

We often come across this issue when our working server and source server have different bits 32 or 64.

You might have created System DSN in your system, but when try to create connection manager, the DSN will be missing in ODBC list. To overcome this you need to create DSN connection in appropriate ODBC (32/64).

Perform the followings:

Open command window:


1. Navigate to C:\Windows\Sysos64\Odbacd32.exe

2. ODBC Connection wizard will appear.
3. Create a new system DSN there.
4. Now try creating Connection Manager in SSIS package

Attach MDF file to a Database

Below is the query to attach MDF file to a database:

CREATE DATABASE AdventureWorks2008DWR2 ON

( FILENAME = N'C:\Users\mvaradhan\Downloads\AdventureWorksDW2008R2.mdf')

FOR ATTACH

GO

Attaching MDF file to a Database

Below is the query to attach mdf file to a database:

CREATE DATABASE AdventureWorks2008DWR2 ON

( FILENAME = N'C:\Users\mvaradhan\Downloads\AdventureWorksDW2008R2.mdf')

FOR ATTACH

GO

Attaching .MDF file to a Database

Below is the script to attach AdventureWorks.mdf file to a newly created database:

CREATE DATABASE AdventureWorks2008DWR2 ON

( FILENAME = N'C:\Users\mvaradhan\Downloads\AdventureWorksDW2008R2.mdf')

FOR ATTACH

GO