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
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.
create table temp as
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;
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
from <tablename> a
inner join tempdb.##<SASTEMP-tablename> b on a.<variable> = b.<variable>
disconnect from odbc;
Further information can be found in this link: http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/
If get the error
“An error was encountered in the transport layer”
when you try to synchronize an existing SSAS-database onto another empty SSAS instance for the first time, a solution could be to take a backup of the source SSAS-database and restore it onto the new SSAS instance and then do the synchronization.
To get the port number used by your MSSQL-server, you can use the command below.
WHERE session_id = @@SPID
Go to the page https://www.microsoft.com/en-us/download/details.aspx?id=42295
Select language and the download.
Choose the correct bit-version for you OS.
NB! It has not been possible for me to get this driver to work with SSIS.
The code below will add a trigger to all the tables in a specific schema on the Microsoft
-- Uses the build-in stored procdure in Microsoft SQL-server to run through all tables.
-- Initial command for the DB to use.
@precommand = 'use <db>',
-- Check if trigger already exists on table. If it does drop/delete it.
IF OBJECT_ID(''[user].[?_trig]'', ''TR'') IS NOT NULL
DROP TRIGGER [user].[?_trig]
-- Create trigger on all tables in a specific schema.
@command2 = '
CREATE TRIGGER [?_trig]
SET NOCOUNT ON
INSERT [<schema>].[<table>] ([<column1>], [table_name], [dtNow])
SELECT <columnname>, ''?'', CURRENT_TIMESTAMP FROM ?
-- Only look at tables in a specific schema.
@whereand = 'and upper(schema_name(schema_id)) = ''<SCHEMA>'''
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
sys.database_permissions as dbp
sys.sysusers as u
where dbp.Class_desc='SCHEMA' and lower(u.name) = %str(%')Your domain\&uid.%str(%')
disconnect from odbc;
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
WHERE OBJECT_ID = OBJECT_ID(‘<Schema>.<Table>’)
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.
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.
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE PROCEDURE sp_BULK_DELETE_AREA_YEAR @ActionYear char(4)
— 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 @Count = 0
select @for_delete=count(*) from [YOUR_TABLE] where [YOUR_VARIABLE] in (@ActionYear)
While (@Count < @for_delete)
SELECT @Count = @Count + @chunk_size
DELETE top(@chunk_size) from [YOUR_TABLE] where [YOUR_VARIABLE] in (@ActionYear)
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.
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
EXEC master.dbo.sp_configure ‘xp_cmdshell’, 1
It’s also possible to use ‘sp_oacreate’.