Monday, October 30, 2006

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;

Tuesday, October 03, 2006

SAS and AJAX

SAS has no built in support for AJAX at this time. However, you can hack up some of it by using enabling technologies such as ASP.NET or just code it yourself in JavaScript.

However, I have coded AJAX bits in JavaScript using SAS/IntrNet and found the experience less than desirable. A better way to make this happen, IMO, is to use the new Atlas framework from Microsoft:

http://atlas.asp.net/Default.aspx?tabid=47

It's quick and easy and makes coding AJAX much easier. JavaScript is god-awful due to lack of true debugging support but it works. Make it easier though by focusing on doing the AJAX piece in Atlas and let them handle the JavaScript bits. My $0.01.

Alan

SAS throwing RPC error

If you are doing code in C#  and get this error when creating a LanguageService: The RPC server is unavailable. (Exception from HRESULT:...