DBLOAD Procedure Specifics for Microsoft SQL Server

Overview

For general information about this feature, see the DBLOAD Procedure. Microsoft SQL Server examples are available.

The Microsoft SQL Server under UNIX hosts interface supports all DBLOAD procedure statements (except ACCDESC=) in batch mode. Here are SAS/ACCESS Interface to Microsoft SQL Server specifics for the DBLOAD procedure.

The DBLOAD step DBMS= value is SQLSVR.
Here are the database description statements that PROC DBLOAD uses:

DSN= <'>database-name<'>;

specifies the name of the database in which you want to store the new Microsoft SQL Server table. The database-name is limited to eight characters.

The database that you specify must already exist. If the database name contains the _, $, @, or # special character, you must enclose it in quotation marks. The Microsoft SQL Server standard recommends against using special characters in database names, however

USER= <'>user name<'>;

enables you to connect to a Microsoft SQL Server database with a user ID that is different from the default ID.

USER= is optional in the Microsoft SQL Server interface. If you specify USER=, you must also specify PASSWORD=. If USER= is omitted, your default user ID is used.

PASSWORD=<'>password<'>;

specifies the Microsoft SQL Server password that is associated with your user ID.

PASSWORD= is optional in the Microsoft SQL Server interface because users have default user IDs. If you specify USER=, you must specify PASSWORD=. If you do not wish to enter your SQL Server password in clear text on this statement, see PROC PWENCODE in the Base SAS Procedures Guide for a method to encode it.

Examples

This example creates a new Microsoft SQL Server table, TESTUSER.EXCHANGE, from the DLIB.RATEOFEX data file. You must be granted the appropriate privileges in order to create new Microsoft SQL Server tables or views.

proc dbload dbms=SQLSVR data=dlib.rateofex;
   dsn=sample;
   user='testuser';
   password='testpass';
   table=exchange;
   rename fgnindol=fgnindollars
          4=dollarsinfgn;
   nulls updated=n fgnindollars=n
         dollarsinfgn=n country=n;
   load;
run;

This example only sends a Microsoft SQL Server SQL GRANT statement to the SAMPLE database and does not create a new table. Therefore, the TABLE= and LOAD statements are omitted.

proc dbload dbms=SQLSVR;
   user='testuser';
   password='testpass';
   dsn=sample;
   sql grant select on testuser.exchange
      to dbitest;
run;