The other day I was trying to migrate data from SQL Server to SAS.  The first thing I had to do was generate an insert/update SQL script from my production database so I could place the data in a local SQL Server instance with ODBC access to SAS.

The first issue I had was running the SQL query (backup from production) to create and populate the database on my local server.   For some reason I had to change my command to the following:

sqlcmd -S [server instance name] -i [location of the sql file on my network drive] -d [database to 'use'the sql script on]

So that code looked like:

sqlcmd -S MySQLServerInstanceName -i C:\mydirectory\sqlbackup.sql -d dbFromProd

Also I found the best way to generate the SQL query from production was to set the ‘Script USE DATABASE’ option to false so I could specify the USE [database name] when executing the SQL on my local machine like so (note that I used !! in the sqlcmd statement due to the fact that I executed this statement from within SQL Management Studio with the Query/SQLCMD Mode enabled):

USE [database name]
!!sqlcmd -S MySQLServerInstanceName -i C:\mydirectory\sqlbackup.sql -d dbFromProd

With my production database now on my local database and an SQL Server ODBC connection created to the local copy of my database, I wanted to see that SAS could actually connect to the ODBC connection and see the tables in my database using this code:

*SAS code to check tables SAS is able to see;
LIBNAME mydblib ODBC DSN=prod_db_copy_dsn ACCESS=READONLY SCHEMA=dbo PRESERVE_TAB_NAMES=YES;
proc contents data=mydblib._ALL_ nods; run;

The first thing I noticed was not all of the tables in the database were showing up for my dbo schema, although SQL Server Managerment Studio was telling me otherwise just looking at the tables.  However the following SQL code told me that the schema indeed was not assigned to all of the tables:

USE prod_db_copy
SELECT * FROM INFORMATION_SCHEMA.TABLES

I then found a script to assign all of the database tables to my schema (the question mark must mean ‘for the selected table in the foreach loop’:

USE [database name]
exec sp_MSforeachtable "ALTER SCHEMA [dbo or other schema you want to use] TRANSFER ?"

I then returned to my SAS code and to find out if it was able to see all of the tables…and it still was unable to see them.  I went back to look at my ODBC connection and the problem lay in not checking the ‘Change default database to’ and selecting the database I wanted.  After doing this I was able to see all of the database tables in SAS with no problem.