Category: Microsoft SQL-server

Moving from DB2 to MSSQL

Moving data from DB2 to Microsoft SQL-server (MSSQL) can involve some difficulties, because there has to be a transformation of datatypes from one RDBMS to other.

DB2 and MSSQL doesn’t contain the exact same data types and therefore a translation/conversion between the two RDBMS has to occur.

This article from Microsoft explain how the feature DB2ToSQL handles that conversion. And can also be used for inspiration for your own handling of transferring data between the to RDBMS’s.

The article can be found here: Project Settings (Type Mapping) (DB2ToSQL)

Installing and configuring an SAS OLEDB-driver for MSSQL

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.

Be aware, that it is possible to code a program in e.g. .NET that reads a SAS-dataset. It can be done with the SasReader (currently in version 1.0.6).

Below code in C# reads a SAS-dataset and outputs it into a .CSV-file.
Credits to my colleague that figured this out.

using System;
using System.IO;
using SasReader;
using System.Text;

namespace SasToCsvConverter
{
    class Program
    {
        static void Main(string[] args)
        {
           // Define paths
           // string sasFilePath = @"C:\temp\<YOUR SAS-DATASET>.sas7bdat";
           // string csvFilePath = @"C:\temp\output.csv";
            try
            {
                // Initialize SAS file reader
                using (FileStream sasToParseFileInputStream = File.OpenRead(sasFilePath))
                {
                    SasFileReader sasFileReader = new SasFileReaderImpl(sasToParseFileInputStream);

                    // Open the CSV file for writing
                    using (var writer = new StreamWriter(csvFilePath, false, Encoding.UTF8))
                    {
                        // Read and write META DATA
                        var sasMetaColumns = sasFileReader.getColumns();

                        // Write header
                        var headerNames = new StringBuilder();
                        foreach (var column in sasMetaColumns)
                        {
                            headerNames.Append(column.getName()).Append(",");
                        }

                        // Remove the trailing comma
                        writer.WriteLine(headerNames.ToString().TrimEnd(','));

                        // Write DATA
                        long rowCount = sasFileReader.getSasFileProperties().getRowCount();
                        for (int i = 0; i < rowCount; i++)
                        {
                            var row = sasFileReader.readNext(); // object[]
                            var rowValues = new StringBuilder();
                            foreach (var value in row)
                            {
                                var stringValue = value?.ToString() ?? string.Empty;
                                rowValues.Append(EscapeCsvValue(stringValue)).Append(",");
                            }

                            // Remove the trailing comma
                            writer.WriteLine(rowValues.ToString().TrimEnd(','));

                            // Optional: Log progress
                            Console.WriteLine($"Processed row {i + 1}/{rowCount}");
                        }
                    }
                }

                Console.WriteLine("Conversion to CSV completed successfully.");
            }

            catch (Exception ex)
            {
                Console.WriteLine($"An error occurred: {ex.Message}");
            }
        }

        private static string EscapeCsvValue(string value)
        {
            if (value.Contains(",") || value.Contains("\"") || value.Contains("\n"))
            {
                return $"\"{value.Replace("\"", "\"\"")}\"";
            }
            return value;
        }
    }
}

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>’)