topic ODBC connection to SSMS, in SAS Programming

ODBC connection to SSMS,

mcook — Thu, 19 Nov 2020 16:43:03 GMT

I have connected to a database on SQL Server Management Studio on my local machine, with SQL Express, with the following ODBC code.

  libname MattsLib odbc noprompt="driver=ODBC Driver 11 for SQL Server;
                             server=<my local machine ID>\SQLEXPRESS;
                             database=TestDB;
                             Trusted_Connection=yes"
                  schema=DBO;

This connects as expected, but in trying to figure out how to use "alter table" in SAS to alter SQL tables in SSMS, I ran this following code to create a table.

CREATE TABLE dbo.account
( account_id INT NOT NULL IDENTITY(1,1) CONSTRAINT PK_account PRIMARY KEY CLUSTERED,
  account_name VARCHAR(100) NOT NULL,
  account_start_date DATE NOT NULL,
  account_address VARCHAR(1000) NOT NULL,
  account_type VARCHAR(10) NOT NULL,
  account_create_timestamp DATETIME NOT NULL,
    account_notes VARCHAR(500) NULL,
  is_active BIT NOT NULL);

It ran with no errors, warnings or notes... But where did it create the table to? I am unable to locate it. It is not in any database in SSMS that i can see (Particularly not in TestDB where i was expecting it) And it is not in any library in my SAS session.

Any suggestions?

Note: i copied and pasted that create table code from here: https://www.sqlshack.com/methods-to-insert-data-into-sql-server/

Re: ODBC connection to SSMS,

JBailey — Thu, 19 Nov 2020 17:28:34 GMT

Hi @mcook

Can you show the actual SAS code you used to connect and run the CREATE TABLE statement?

The following options will show the exact SQL that SAS is sending to the database:

OPTIONS SASTRACE=',,,d' SASTRACELOC=saslog NOSTSUFFIX;

Rerun the job and include the resultant SAS log in this thread. If the table does exist, your SAS log will have an error stating that.

Good luck,

Jeff

Re: ODBC connection to SSMS,

mcook — Fri, 20 Nov 2020 14:38:06 GMT

I'm just a fool. Ignore this question. after working in sql for a few days, i forgot to start with proc sql in SAS. 😕

Re: ODBC connection to SSMS,

JBailey — Mon, 23 Nov 2020 16:20:25 GMT

Hi @mcook

Don't worry about it and welcome to the club. Many of us are members. I join this club at least once a day.

Best wishes,
Jeff