Inserting records into SQL Server

99+% of the time, I read records from SQL Server into SAS. I typically use C# and do it direct in code. However, I recently needed to write records into SQL Server from the SAS side.

Attempt #1 was to use PROC APPEND. This failed with the following:

"ERROR: During insert: Data was not set for one or more columns."

This was failing on the identity column.

Attempt #2 was to try a SQL Server insert:

proc sql ;
insert into SqlSrvr.Test
select * from newdata
;
quit;

ERROR: Attempt to insert fewer columns than specified after the INSERT table name.
ERROR: Value 1 on the SELECT clause does not match the data type of the corresponding column
listed after the INSERT table name.
ERROR: Value 2 on the SELECT clause does not match the data type of the corresponding column
listed after the INSERT table name.
ERROR: Value 17 on the SELECT clause does not match the data type of the corresponding column
listed after the INSERT table name.

Hmmmm, could it be a problem wit hthe identity column and me using the new XML filed type?

A little bit of sleep and attempt #3 worked:

libname SQLSrvr oledb provider=sqloledb init_string='Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MyCustomer;Data Source=SERVER01' schema=dbo ;

data NewData;
attrib platform length=$200
periodicity length=$200
level0-level10 length=$200
image length=$1024
data length=$1024
help length=$1024
;
DateTime = DateTime() ;
Platform = "MVS" ;
Periodicity = "Daily" ;
Level0 = "CPU Utilization" ;
Image = "c:\temp\myimage.jpg" ;
Data = "" ;
Help = "c:\temp\myhelp.doc" ;
run;

proc sql ;
insert into SqlSrvr.Test
select * from newdata
;
quit;

proc sql ;
insert into SqlSrvr.Test (datetime,platform, periodicity, level0, image, data, help)
select datetime, platform, periodicity, level0, image, data, help from newdata
;
quit;