Using MSSQL TEMPDB in SAS

You can use the TEMPDB in Microsoft SQL-server through SAS by creating a ODBC-libname – like the libname below.

libname TMPLIB ODBC NOPROMPT="DRIVER=SQL Server; SERVER=<SERVERNAME>; DATABASE=TEMPDB; TRUSTED_CONNECTION=yes" schema=DBO CONNECTION=SHARED;

It’s important to provide the option CONNECTION=SHARED or else it will not work.

Through the libname it’s now possible to write and read from TEMPDB. The dataset has to have this syntax ‘#<DATASETNAME>’n e.g. like below ‘#temp’n

data tmplib.'#temp'n;
set sashelp.class;
run;

Be aware, that you are not able to view this new table through the Display Manager in SAS. When Microsoft SQL-server names the table, it makes the table name longer than SAS is able to display in the Display Manager.
You’re able to verify that the table do exist through SSMS (SQL Server Management Studio), or you can verify it’s existence by reading it back to SAS by using the code below.

proc sql;
create table temp as
select *
from tmplib.'#temp'n
;
quit;

If you want to use MSSQL-server temp-tables in Pass-Through SQL in SAS, then you need to use the libname option dbmstemp=yes.
Using this option will make it possible to execute the code below and force SAS to use the MSSQL-server to process the SQL-code in the pass-through SQL. If the option dbmstemp=yes is not used, then SAS will pull the data from the MSSQL-server back to be executed locally on the SAS-installation. It works with the below driver.

libname tmplib odbc noprompt="driver=odbc driver 11 for sql server; server=<servername>; database=tempdb; trusted_connection=yes" schema=dbo connection=shared dbmstemp=yes;

data tmplib.<SASTEMP-tablename>;
set <tablename>;
run;

proc sql noprint;
connect to odbc (noprompt="driver=odbc driver 11 for sql server; server=<servername>; trusted_connection=yes");
create table <tablename> as
select * from connection to odbc
(
select a.*
from <tablename> a
inner join tempdb.##<SASTEMP-tablename> b on a.<variable> = b.<variable>
);
disconnect from odbc;
quit;

Further information can be found in this link: http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/
default/viewer.htm#a002677192.htm

 

Add trigger to all tables in schema

The code below will add a trigger to all the tables in a specific schema on the Microsoft
SQL-server.

-- Uses the build-in stored procdure in Microsoft SQL-server to run through all tables.
EXEC sp_MSForEachTable 
-- Initial command for the DB to use.
@precommand = 'use <db>',
-- Check if trigger already exists on table. If it does drop/delete it.
@command1 = 
'
IF OBJECT_ID(''[user].[?_trig]'', ''TR'') IS NOT NULL
BEGIN
	DROP TRIGGER [user].[?_trig]
END
',
-- Create trigger on all tables in a specific schema.
@command2 = '
CREATE TRIGGER [?_trig]
ON ?
AFTER INSERT
AS
	SET NOCOUNT ON
	INSERT [<schema>].[<table>] ([<column1>], [table_name], [dtNow])
	SELECT <columnname>, ''?'', CURRENT_TIMESTAMP FROM ?
GO
',
-- Only look at tables in a specific schema.
@whereand = 'and upper(schema_name(schema_id)) = ''<SCHEMA>'''
go

 

Getting accessible schemas for a user

The code below gets the schemas from a Microsoft SQL-server that is accessible for a given user. It is made with SAS-code but the SQL-statement can be used regardless of using SAS.

proc sql noprint;
connect to odbc(datasrc=&_datasrc user=Your user; pwd="Your password");
select schema_name into :_schema_names separated by " "
from connection to odbc
(
select s.name as schema_name
from sys.schemas as s
inner join
sys.database_permissions as dbp
on s.schema_id=dbp.major_id
inner join
sys.sysusers as u
on
u.uid=dbp.Grantee_principal_id
where dbp.Class_desc='SCHEMA' and lower(u.name) = %str(%')Your domain\&uid.%str(%')
);

%put &_schema_names;
disconnect from odbc;
quit;

Find partitioned tables in Microsoft SQL-server

This will find partitioned tables

select distinct object_name(object_id) from sys.partitions
group by object_id, index_id
having COUNT(*) > 1

This will find the partitions for a specific table

SELECT *
FROM sys.partitions
WHERE OBJECT_ID = OBJECT_ID(‘<Schema>.<Table>’)

Securing the Tabular BI Semantic Model

Summary: This paper introduces the security model for tabular BI semantic models in SQL Server 2012. You will learn how to create roles, implement dynamic security, configure impersonation settings, manage roles, and choose a method for connecting to models that works in your network security context.

Download here

Bulk delete in MS-SQL server

If you want to simulate bulk-deleting in Microsoft SQL-server this can do the trick. I use it to avoid getting a transaction log that fills up the harddisk.
The example uses a stored procedure, but you don’t have to do this. The stored procedure is given a year-variable that is used in the SQL-sentences that selects what has to be deleted.

USE [YOUR_DATABASE]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE sp_BULK_DELETE_AREA_YEAR @ActionYear char(4)
AS
BEGIN

— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.

SET NOCOUNT ON;

— Insert statements for procedure here
DECLARE @Count INT
Declare @for_delete INT
Declare @chunk_size INT

SELECT @chunk_size=100000
SELECT @Count = 0
select @for_delete=count(*) from [YOUR_TABLE] where [YOUR_VARIABLE] in (@ActionYear)

While (@Count < @for_delete)
BEGIN
SELECT @Count = @Count + @chunk_size
BEGIN TRAN
DELETE top(@chunk_size) from [YOUR_TABLE] where [YOUR_VARIABLE] in (@ActionYear)
COMMIT TRAN
END
END
GO

It’s excecuted in the following manner: exec sp_BULK_DELETE_AREA_YEAR ‘2011’

I haven’t figured out how to use a variable that contains the tablename. So I don’t think it’s possible. You have to write the table name.
The solution was found here.

Enable xp_cmdshell in Microsoft SQL-server

Disclaimer: Beware of the possible security threat when you enable xp_cmdshell usage. You can also take a look at this article, it’s at bit more complicated, but a lot safer.
The article pretty much walks you through, what you need to do to use SQLCLR. I tried it out and got stuck when i got a security threat. I got the threat because you have to give the assemblies that you use (‘alter assemblies…’) a given state – safe, unsafe and external in the SQL-server.

EXEC master.dbo.sp_configure ‘show advanced options’, 1
RECONFIGURE
EXEC master.dbo.sp_configure ‘xp_cmdshell’, 1
RECONFIGURE

It’s also possible to use ‘sp_oacreate’.