The below SQL will in a database on a Microsoft SQL-server find all tables with information about the tables schema, name, creation date and modifications date.
schema_name(t.schema_id) as schema_name
, t.name as table_name
from sys.tables t
order by table_name
To get the correct SAS OLEDB driver or newest SAS OLEDB driver. It is best to do a search e.g. on Google.
NB! You need an account at SAS to be able to download.
And even thou this guide for Installing and configuring an SAS OLEDB-driver for MSSQL is old – it is still very useful.
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.