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;