What is the current user for an ODBC Trusted Connection to SQL Server?

ScottBass — Thu, 11 Aug 2016 08:30:51 GMT

My SQL Server table has a few default contraints:

[Name] [varchar](8) NOT NULL,
[Sex] [char](1) NOT NULL CONSTRAINT [DF_TARGET_SEX]  DEFAULT ('U'),
[Age] [int] NOT NULL CONSTRAINT [DF_TARGET_AGE]  DEFAULT ((-1)),
[Height] [numeric](7, 1) NOT NULL CONSTRAINT [DF_TARGET_HEIGHT]  DEFAULT ((-1)),
[Weight] [numeric](7, 1) NOT NULL CONSTRAINT [DF_TARGET_WEIGHT]  DEFAULT ((-1)),
[valid_from] [datetime2](0) NOT NULL CONSTRAINT [DF_TARGET_VALID_FROM]  DEFAULT (getdate()),
[valid_to] [datetime2](0) NOT NULL CONSTRAINT [DF_TARGET_VALID_TO]  DEFAULT ('9999-12-31 23:59:50'),
[current_record] [char](1) NULL CONSTRAINT [DF_TARGET_CURRENT_RECORD]  DEFAULT ('Y'),
[DimensionCheckSum] [int] NOT NULL CONSTRAINT [DF_TARGET_DimensionCheckSum]  DEFAULT ((-1)),
[LastUpdated] [datetime] NOT NULL CONSTRAINT [DF_TARGET_LastUpdated]  DEFAULT (getdate()),
[UpdatedBy] [varchar](50) NOT NULL CONSTRAINT [DF_TARGET_UpdatedBy]  DEFAULT (SUSER_SNAME()),

If I submit this in SSMS, all the above fields are auto-populated, and get their default values:

INSERT INTO tmp.target (Name) VALUES ('A')

When I have an ODBC connection to this table in SAS, and I execute the below code:

data foo;
   if 0 then set tmp.target (keep=name);
   Name='B';
   output;
   stop;
run;

proc append base=tmp.target data=foo force nowarn;
run;

All the default values are auto-populated, except for UpdatedBy and Sex.

If I use explicit pass-through it works as expected:

proc sql;
   connect using tmp;
   execute(
INSERT INTO tmp.target (Name) VALUES ('C') 
   ) by tmp;
quit;

My ODBC libref is allocated as follows:

%macro allocate_sqlsvr_database(libref=,database=,schema=dbo);
   %* set connection string ;
   %let connect = NOPROMPT="Driver={SQL Server Native Client 10.0};Server=XXXXX,#####;Database=&database;Trusted_Connection=yes;";
   %* set any libname options ;
   %let options = bulkload=yes schema=&schema reread_exposure=yes DBINDEX=YES UPDATE_LOCK_TYPE= NOLOCK;
   %* issue the libname statement ;
   libname &libref odbc &connect &options;
%mend;

%allocate_sqlsvr_database(libref=TMP, database=MyDatabase,schema=tmp)

Any ideas why these two default values aren't populated when using PROC APPEND? Esp. UpdatedBy? Could this be a bug?

SAS 9.3 on Windows...

Thanks...

Re: What is the current user for an ODBC Trusted Connection to SQL Server?

LinusH — Thu, 11 Aug 2016 08:55:54 GMT

There is the th BL_KEEPNULLS options that seems to be tailored for this problem. Unfortunately it seems only to be supported for OLEDB connections.

For ODBC you could try setting the IGNORE_READ_ONLY_COLUMNS to YES, if I recall right I think we solved this problem by doing that.

topic Re: What is the current user for an ODBC Trusted Connection to SQL Server? in SAS Programming

What is the current user for an ODBC Trusted Connection to SQL Server?

Re: What is the current user for an ODBC Trusted Connection to SQL Server?