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;

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

Weird VS2005 Error

I'm posting this in case others hit the same issue.

When trying to doa ClickOnce deployment, we hit the following error:

"Cannot publish because a project failed to build."
"SignTool reported an error. "The parameter is incorrect."

We switched from VB to C# and it worked fine. I'll leave this blog posting out on the net so it can help someone else out doing ClickOnce deployments.

EG Tasks not displaying

Installed EG4.1 and no task were displayed. Here's how I fixed it (based upon an old 2.0 TS post):

Go to:

Tools > SAS Enterprise Guide Explorer >

In Enterprise Guide Explorer

Tools > Options > Uncheck Enable Task Administration

SAS EG and .NET 2.0

Ok, so the official word is no .NET 2.0 apps in EG. I understand this position 100% and I agree with the position. Regardless, .NET 2.0 costs me 25-50% less effort than 1.1 so my goal was to see if I could hack out something that would allow me to post a 2.0 app in EG 4.1.

It is a hack, it's not official, it's limited, etc. but I successfully got my 2.0 app to run under EG and had it post my code to an EG task. Here's how I did it but it is simplistic and not pretty. I share it in case you need something similar.

First, create a 2.0 app. Make it a WinForm and have fun on layout, generics, etc.

Then change parts of your program.cs to something like the following:

MainForm frm = new MainForm();
Application.Run(frm);
Console.WriteLine(frm.SasCode);

All Winform apps can write to a console but this output goes to a standard out.

Then change your EG add-in to support it:

public SAS.Shared.AddIns.ShowResult Show(System.Windows.Forms.IWin32Window Owner)
{
Process proc ;
proc = new Process() ;
proc.StartInfo.UseShellExecute = false ;
proc.StartInfo.RedirectStandardOutput = true ;
proc.StartInfo.RedirectStandardError = true ;
proc.StartInfo.CreateNoWindow = true ;
proc.StartInfo.FileName = "AnalystToolkit.exe";
proc.Start() ;
proc.WaitForExit() ;
sasCode = proc.StandardOutput.ReadToEnd() ;
return SAS.Shared.AddIns.ShowResult.RunLater;
}

I could have done a lot more with standard out (and I probably will) but this shows you a quick and easy way to hack up a solution that works. From this standard out, you should be able to make out a way to do anything you need.


From out in left field and having fun,

Alan

Excel 2007 and SAS Programmers

SAS-L is always having questions on Excel (and sometimes Word and PowerPoint). Office 2007 has now gone to beta 2 and should go production by the end of the year.

There are some areas that might be of relevance to SAS programmers who have to interface with the new Excel 2007.

- Color coding of cells by value is now very, very easy for users to do. So easy in fact that it is 1 click for the entire sheet.
- Cell formatting, coloring, etc. is now wide-open and very, very easy to do.
- Color choices now expand to 16M
- Rows go to 1M, columns to 65K
- Pivot tables no longer expand into other columns but remain fixed in the 1 column
- The file format for Excel files will be changing. Currently, it is binary. The new file format will be XML based, may contain multiple files, and all of them will be zipped up into a single package
- Etc., etc.

The reason I mention this is because a) it’s on my mind, b) I'm at TechEd and just saw mor eof this stuff, and c) I think it may have significant impact on SAS programmers. BTW, the beta 2 of Office may be one of the largest download activities ever seen by Microsoft (500K+ in 2 days).

I can see users using Excel a lot more for data storage. Also, expect a lot more data to be displayed by color-coding rather than value-based.

Why should SAS users care?

It will break a lot of existing paradigms for reading Excel and from what we expect from users. Data will now be stored a lot more in unstructured forms and there will be a lot more data. Also, users will expect Excel sheets to be done nicely and not just be a data dump.

The move of SAS programmers away from using DDE is long overdue. As difficult as it may be for many, the use of Excel object models will becoome more and more of a job necessity IMO.

For what it's worth...

Alan

MHTML, AOL, C#, and the new WebBrowser

Recently, I had a customer who used AOL as their primary email account. This was a CEO so it was important to accomodate their desired email vendor. Well, AOL doesn't support MIME HTML (MHTML). Since the data being reporting was coming out of SQL Server Reporting Services, the choices for output were limited. The CEO liked the layout of the MHTML, but there was a hex dump at the end of the emails. He wanted to keep the look of the emails but without the hex dump.

The solution to this problem, and I hope this helps someone else, was to write a custom C# program to rip out the meta tags in the emails. There were several tricks that needed to be applied so I will list them here for someone else.

1. Switch the program.cs code to instantiate a form but don't run it directly. Make the application run a null value. This keeps the form from popping:

static void Main(string[] args)
{
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
Main main = new Main(args);
Application.Run();
}

2. Use the new webbrowser control in .NET 2.0. You can then grab the DocumentText from the browser control AFTER it finishes rendering:

class Main
{
string report;
string reportName;
string distributionList;
static string server = "mail.savian.net";
WebBrowser browser = new WebBrowser();

public Main(string[] args)
{
if (args.Length < 2)
MessageBox.Show("Too few arguments to process");
else
{
distributionList = args[1];
ConvertReportToHtml(args[0]);
reportName = args[0].Substring(args[0].LastIndexOf('\\') + 1).Replace(".mhtml", "");
}
}

private void ConvertReportToHtml(string p)
{
browser.Navigate(p);
browser.DocumentCompleted += new WebBrowserDocumentCompletedEventHandler(browser_DocumentCompleted);
}

void browser_DocumentCompleted(object sender, WebBrowserDocumentCompletedEventArgs e)
{
report = browser.DocumentText;
StripMhtmlSection();
SendReport();
}

private void StripMhtmlSection()
{
Regex regex = new Regex(@"");
regex.Replace(report, "");
}

private void SendReport()
{
try
{
SmtpClient client = new SmtpClient(server);
MailAddress from = new MailAddress("CCLeadManagementSystem@savian.net");
MailAddress to = new MailAddress(distributionList);
MailMessage message = new MailMessage(from, to);
message.IsBodyHtml = true;
message.Subject = reportName;
message.Body = report;
client.Send(message);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
Application.Exit();
}
}


I hope this helps someone. It took a lot of time to figure this all out. I tried FileWebRequest as well to no avail.


Alan