Windows 2003 Server Web Edition doesn’t support SQL Server?!?

I am setting up a new computer running VMware GSX Server (because the new free VMware Server is still in beta) for which I plan to use one of the virtual machines to run a few websites including a DotNetNuke website that stores its content in SQL Server. So I decided to install Windows 2003 Server Web Edition and then ran into a problem when trying to install SQL Server 2000 (I’m using 2000 for compatibility with some older sites I am planning to move to this VM; I’ll installed SQL Server 2005 for new sites in a different VM.) 

When I ran the SQL2K install app (written in Demoshield) and I clicked on "Install Database", nothing happened.  Then when I tried to run the install app directly [setupsql.exe], I got the following error message:

Windows cannot open this program since it has been disabled.

Of course I immediately assumed it was a VMware problems (not unreasonable since I’ve had one problem after another trying to get VMware to work, mostly because of incompatible hardware, but still), but then their tech support provided me the answer on our forum.  Microsoft decided not to allow SQL Server 2000 to run on Windows 2003 Server Web Edition?!?!?!   Worse, their error message was so cryptic I spent hours trying to track down the problem!!!!!!! (Googling didn’t help.)

HELLO MICROSOFT, don’t you understand that most serious websites use a database?!?!?  I’d say this is especially true for smaller sites that can’t afford seperate servers.  And why must you persist in causing developers and IT people to have to track down the meaning of cryptic errors?!?!?!

I just love it when the marketing department of a company cripples a product for it’s intended use in order to ensure they "maximize profit," and when the implementation people don’t provide reasonable error messages for problem areas.  Sheesh!  The former makes me want to consider using LAMP.

Next Generation Demo Software with VMware Player

I just spotted the new free VMware Player.  This is totally cool. 

Free VMWare Player

I can envision companies using VMWare Player to demo complete environments, like an ASP.NET & SQL Server Express application; just configure the virtual machine to have everything needed! This basically eliminates most potential configuration problems that could cause a vendor to loose a sale when the software is what the prospect needs but getting it to work is too difficult or time-consuming for the prospect.  This is especially important for lower priced software.

ISVs could even start shiping preconfigured software using VMWare Player.

This is definitely something to watch.

I wonder when/if Microsoft will do something like this with their Virtual PC/Server technology?

Anyone know of a T-SQL Code Formatter?

I know I’m the tool man (with Xtras.Net) but I can’t seem to find a T-SQL code formatter and wondered if anyone else knows about one? 

I’m got someone inhouse who writes his SELECT, INSERT, and UPDATE statements in stored procs as if his monitor were infinitely wide, and only ever uses the tab or enter key if forced to do so.  His SQL code has no whitespace, and his statements never line up; whenever I have to touch anything he does I have to spend 15 to 30 minutes reformatting his code.  He also won’t comment his code telling me "I’m in development on it which is why I don’t format or comment; I’ll clean it up later when its finished" which of course never happens. I’m pretty much a standard’s zealot yet no matter what I do, I can’t get him to change.  I feel like the pious Baptist minister whose teenage daughter drinks, smokes, listens to rock music, and is having sex with the entire football team! 

We just had a big blow up for the nth time about it and his response to me was "Then adopt my style, I like it better, I can’t read your code when you format it." (If you want to see how I format T-SQL, you can read some of read my prior posts.)  Jeesh I’m frustrated.  Do any of you have to deal with this situation?  Is it me?  Any thoughts about how to get him to feel like there is value to working with standards everyone on a team can agree on?

Does anyone know of a T-SQL code formatting tool and standards management tool I can run on an entire SQL Server database?

It’s Always Something (#1): Wrestling with System.Xml.Xsl.XslTransform and the Document() function

It’s always something!

At Xtras, we have a rather sophisticated email broadcast system that we developed internally. Our system uses a set of tables in our SQL Server database that models the type of email, who it should go to, the mailing lists, etc. It loads each newsletter subscriber’s name and email address using a FOR XML EXPLICIT query from our SQL Server. It uses .XSL files to contain the HTML and/or Text content for each email, and then it uses a System.Xml.Xsl.XslTransform object to generate each HTML/Text email using a transform of the subscriber’s .XML fragment and the current newsletter’s .XSL file.  Once generated, it attempts to send using the SMTP component from /n software’s IP*Works!, and logs the success of failure into our database.

It is actually quite an elaborate and powerful system, and my hat’s off to Bill who developed it. However, it isn’t perfect. First, there is very little user interface; "the cobbler’s children go barefoot" as they say.  The second is our infrastructure doesn’t contemplate organization of .XSL files; there are literally hundreds all in the same directory.  But probably the biggest problem with it has been our approach to creating new email newsletters; we literally copy the XSL file from last newsletter issue and edit it, updating it for the current issue. 

Data driven?  Automated?  Modularized?  Yes it is, at least the parts that had to be are data-driven, automated, and modularized.  Like inserting the recipient’s email address and sending the emails.  But all other parts are manual!  Maybe you’ve gotten emails from us in the past where we’ve made errors like having two different dates on the same email in different places?  Now you know why…

Recently I grabbed a book on XSLT (Michael Kay’s XSLT: Programmer’s Reference; it’s actually quite good even though it’s four years old by now) and started learning how to write modular XSLT.  Of course it has taken longer than I planned so our next newsletter is late (our June newsletter for XDN; anyone notice its really late? Sorry…)  I managed to prepare a really elaborate stylesheet and pulled almost all of the content of the newsletter from data in our SQL Server database.  I then got the newsletter ready to go and passed it on to Bill to have him send it, and guess what?  It blew up his email system, giving code access security errors! 

Now you have to know Bill. He is very focused on completing his current projects, so Bill was not happy that my new .XSL blew up his "working" system!  But he understood that it would probaby take longer to rewrite than to solve the problem, and it is something we need to solve anyway.

The code access security error took us down the wrong path.  What I had added to the XSL file was a call to the XSLT document() function to load a second XML document (the newsletter content) from a URL, and we both thought XslTransform was telling us we needed to resolve a security problem in accessing a URL via HTTP.  Well, that wasn’t it; it was actually quite simple.  We needed an XmlResolver. More specifically, we needed an XmlUrlResolver.

So the moral of this long-winded story is, if you are using an XslTransform() and you add a reference to the document() function in  your .XSL file, you’ll need to add an XmlUrlResolver for it to work.  What follows is a self-contained simple example; copy into a class can call that class’ Exec method on a form click event to test (oh, and you’ll need to put an .XML and .XSL file at http://localhost/xslt/ for it to work):

Imports System.XmlImports System.Xml.XPathImports System.Xml.XslImports System.Text.EncodingPublic Class Test   Private Const outFile As String = "XsltOutput.html"   Public Sub Exec()      Dim xslt As New XslTransform      xslt.Load(FileUrl("XdnJun2004.xsl"))      Dim data As New XPathDocument(FileUrl("Subscriber.xml"))      Dim writer As New XmlTextWriter(outFile, UTF8)      Dim xmlr As New XmlUrlResolver      xslt.Transform(data, Nothing, writer, xmlr)   End Sub   Private Function FileUrl(ByVal filename As String) As String      Dim baseUrl As String = "http://localhost/xslt/"      Return CType(baseUrl & filename, String)   End FunctionEnd Class

I hope this post finds its way into Google’s index in hopes others with the same problem don’t have to spend as much time as we did trying resolving this problem.

Features I REALLY want in TSQL, Part #3

Refactoring.


Yes, I want refactoring in TSQL/SQL Server. What do I mean by Refactoring in SQL?


Specifically, I want to be able to evolve my database schema without breaking the application. For me at least, I frequently evolve the naming of my database schema as I’m adding features to deployed apps, but it is really hard to know for sure that I’ve updated everything that depends on those changes.


I have seen tools that generally hunt down all the references to tables, views, stored procedures, fields, etc. within SQL Server objects and update them for you. Some tools even look outside in VB.NET or C# code. But those techniques are, by their very nature, incomplete. I mean, what about dynamic SQL?


So what do I think is needed?


The first needed feature would be aliases for SQL Server objects. If I could define an alias for any SQL Server object such as a table, view, stored procedure, field, etc and have it behave when referenced exactly as the object it aliased, I would have part of what I need. For example, I envision you could define a alias called [Item] on the table [Inventory] like so:


CREATE TABLE ALIAS Item FROM Inventory

Then the following two SQL statements would be identical in function:


SELECT * FROM Inventory
SELECT * FROM Item

As would these SQL statements:


UPDATE Inventory SET Price= Price * 1.01
UPDATE Item SET Price= Price * 1.01

The second feature required would be the ability to tag an object as having all accesses to it logged:


ALTER TABLE ALIAS Item LOG ACCESS

Once you tagged a SQL Server object with “log access”, SQL Server would write a record to a special log table every time that object was touched in any way. Here is a potential Access Log Structure:


CREATE TABLE AccessLog
(
ObjectName nvarchar(255),
DateAccessed datetime,
AppName nvarchar(100),
ConnectionString nvarchar(255),
ConnectionID int,
ConnectionName nvarchar(100),
ExecuteStatement nvarchar(8000),
ExecuteID int,
ExecuteName nvarchar(100)
)

If you note some of the fields in my hypothetical access log you’ll see data that currently does not exist to be logged, such as ConnectionName and ExecuteID. A third “nice to have” feature would be the ability to give unique IDs and unique names in VB.NET and C# code where a connection is made and every place in code where a SQL statement is executed just like providing error numbers in error handlers.


With the first two features I could rename any SQL Server object, create an alias to that object using its old name, and then mark the alias to be logged. Then I could run my app, watch the log file, and see if I missed any references to the old names. If so I could easily use the logged data to track down the places I missed, especially if I had the third feature that allowed me to annotate ADO.NET calls with reference information. I could even deploy an app and let it run for a month or two while monitoring the access logs to make sure I didn’t miss anything.


I sure do wish SQL Server had those features. Don’t you?

Tags: ,

Very Cool Site: ConnectionStrings.com

I just found a very cool site called ConnectionStrings.com (I found it on the .Text Basic Configuration web page.) This site provides examples of connection strings for all of the following including subsets of each:



  • SQL Server
  • Access
  • Oracle
  • MySQL
  • Interbase
  • IBM DB2
  • Sybase
  • Informix
  • Mimer SQL
  • PostgreSQL
  • DSN
  • Excel
  • Text
  • DBF/FoxPro
  • Visual FoxPro
  • Pervasive
  • UDL

Almost never should you be at a loss for how to format a connection string again!  High recommended.

Features I REALLY want in TSQL, Part #2

This next feature isn’t exactly a TSQL feature, and it is not highly visionary, but after spending literally the past eight (8) hours (I kid you not) using SQL tools to manipulate data and evolve some table structures, I remembered it is something I really would like to see.



What I’d like to see added are Default Aliases for tables and fields.



To illustrate the value this feature would provide, let’s use a simple query. The following query is one take verbatim from SQL Enterprise Manager after clicking on tables and fields:


SELECT     dbo.tblVendor.Name, dbo.tblProduct.Name AS Expr1, dbo.tblItem.Name AS Expr2
FROM dbo.tblVendor INNER JOIN
dbo.tblProduct ON dbo.tblVendor.ID = dbo.tblProduct.VendorID INNER JOIN
dbo.tblItem ON dbo.tblProduct.ID = dbo.tblItem.ProductID

Before using the query I always have to edit it. I start by giving each table a simple alias to clean up the query:


SELECT     v.Name, p.Name AS Expr1, i.Name AS Expr2
FROM dbo.tblVendor v INNER JOIN
dbo.tblProduct p ON v.ID = p.VendorID INNER JOIN
dbo.tblItem i ON p.ID = i.ProductID

Next I give the fields meaningful aliases instead of those annoying “ExprN” aliases:


SELECT     v.Name AS VendorName, p.Name AS ProductName, i.Name AS ItemName
FROM dbo.tblVendor v INNER JOIN
dbo.tblProduct p ON v.ID = p.VendorID INNER JOIN
dbo.tblItem i ON p.ID = i.ProductID

Lastly I typically copy to SQL Query Analyzer and reformat so I can actually read and understand the query, and I also prefix the tables with database names for readability and maintainability assuming I plan to save the query in a stored proc, view, or function:


SELECT
v.Name AS VendorName,
p.Name AS ProductName,
i.Name AS ItemName
FROM
Products..tblVendor v
INNER JOIN Products..tblProduct p ON v.ID = p.VendorID
INNER JOIN Products..dbo.tblItem i ON p.ID = i.ProductID

Whew! That’s an awful lot of time spent just to get a clean workable query. Yes I know, some people say I am too anal, but my brain can’t process much at any one time so I always have to simplify and clean things up like this. Anyway, if SQL Server had default aliases for fields and tables and a few configuration options, a future SQL IDE could automagically add all my table and field aliases and database prefixes.



Now I know the first comment I’ll get will be someone saying: “But your suggestions makes no sense because you can’t be sure not to have potential conflicts among your tables and fields such as when you have a tblItem and tblInvoice and you set each to use “i” as the default alias.” To which my reply will be “So what? I’m proposing default aliases for fields and tables as “hints” for SQL IDEs, not as something people would depend on for robustness!



I would envision a future SQL IDE would have a set of configuration options something like this (my configuration options imply multiple default aliases for tables and fields that could be used in case of conflict with the primary default alias):




  • Always alias tables with their default alias:

    • [  ] Yes
    • [  ] No

  • How to handle table alias conflicts

    • [  ] Append a numeral: i.e. “i”, “i1″, “i2″, etc.
    • [  ] Use secondary default: i.e. try “i” then try “inv”, etc.
    • [  ] Ask Me

  • Default field aliases

    • [  ] Always alias fields
    • [  ] Only alias fields when field conflicts with another field

  • How to handle field alias conflicts

    • [  ] Append a numeral: i.e. “i”, “i1″, “i2″, etc.
    • [  ] Use secondary default: i.e. try “ItemName” then try “AlternateItemName”, etc.
    • [  ] Ask Me

  • Always prefix tables with database names

    • [  ] Yes
    • [  ] No

A really saavy Microsoft usability tester looking over my shoulder as I worked with SQL day in and day out would come up with the set of features I describe here because it would really reduce the amount of time I have to spend doing rote tasks in SQL.  Beyond that, I’d love to see configuration options that specify how to format SQL statements, but that’s beyond the scope of anything I’d like to post today.

Tags: ,

Features I REALLY want in TSQL, Part #1

I believe SQL was invented back in the 70’s back by E.F.Codd (don’t shoot me if I’m wrong; I’m not a historian.) Given the collective wisdom about programming back then, SQL is an amazing language.


I probably use TSQL more often than any other because of ad-hoc queries into my company’s SQL Server database. However today when I use it, I so often ask myself “What were they thinking when they designed SQL?!?“, which isn’t fair but that doesn’t keep me from being frustrated by its shortcomings.


Per chance you read my post about Developer Bias you’ll remember that my bias is strongly towards maintainability. And maintainability is where SQL needs great improvements. I’m going to start a multi-part series of posts about what I’d like to see in TSQL, with hopes it might inspire some of the Microsoft TSQL team for future versions.


As you probably know, many SQL commands use lists of fields and values: SELECT, INSERT, and UPDATE to name a few. For example:


SELECT
ID,
Name,
Company,
Street,
City,
State,
PostCode,
Country
FROM
tblCustomer

INSERT
tblCustomer
(
Name,
Company,
Street,
City,
State,
PostCode,
Country
)
VALUES
(
‘Mike Schinkel’,
‘Xtras, Inc.’,
‘1360 Seaboard Industrial Blvd’,
‘Atlanta’,
‘GA’,
‘30318′,
‘USA’
)

UPDATE
tblCustomer
SET
Name= ‘Mike Schinkel’,
Company= ‘Xtras, Inc.’,
Street= ‘1360 Seaboard Industrial Blvd’,
City= ‘Atlanta’,
State= ‘GA’,
PostCode= ‘30318′,
Country= ‘USA’
WHERE
ID=12345

The first feature I’d like to see is “Lists” for use as both “Field Lists” “Value Lists.” For example:
CREATE FIELDLIST flCustomer AS
ID NOINSERT NOUPDATE,
Name,
Company,
Street,
City,
State,
PostCode,
Country
Using the above field and value lists, we could make the SELECT, INSERT, and UPDATE statements much more maintainable:
SELECT FIELDLIST
flCustomer
FROM
tblCustomer

INSERT
tblCustomer
FIELDLIST
flCustomer
VALUELIST
vlMikeSchinkel

UPDATE
tblCustomer
SET FIELDLIST
flCustomer
VALUES
(
‘Mike Schinkel’,
‘Xtras, Inc.’,
‘1360 Seaboard Industrial Blvd’,
‘Atlanta’,
‘GA’,
‘30318′,
‘USA’
)
WHERE
ID=12345

Also, it would be useful to be able to create a valuelist from a function:
DECLARE @MikeSchinkel list
SELECT @MikeSchinkel= ValueList(
‘Mike Schinkel’,
‘Xtras, Inc.’,
‘1360 Seaboard Industrial Blvd’,
‘Atlanta’,
‘GA’,
‘30318′,
‘USA’)

INSERT
tblCustomer
FIELDLIST
flCustomer
VALUELIST
@MikeSchinkel


FieldLists/ValueLists would also be useful as PROCEDURE parameters:


CREATE PROCEDURE prcUpdateCustomer
(
@CustomerID int,
@Customer LIST flCustomer
)
AS
PRINT ‘Updating Customer ‘ + @Customer.Name
UPDATE
tblCustomers
SET FIELDLIST
flCustomer
TO VALUELIST
@Customer
WHERE
ID=@CustomerID
RETURN

Of course my exact syntax might need polishing, but with these extensions I think you’d see TSQL source code become much more maintainable. “Microsoft TSQL team: Are you listening?” :-)

Tags: ,

RSS for Periodic Report Distribution?

I just had an epiphany!  (So everyone, go ahead and send me 50 links that I personally have yet to run across where others have already suggested this. :)

I’ve always wanted to review key numbers related to my company’s permformance on a periodic basis; i.e. each day, each week, and each month, etc.  I know this is needed by almost every person in almost every company. One method is to create an “intranet dashboard.“  But I find I am personally more projected-focused and don’t look at my dashboard consistently enough.  Another is to send an email, but then I have to “manage“ those emails in additional to all my other emails; yuck!

My epiphany was to create a single RSS feed for periodic report distribution.  When the report server generates the report, it would get pulled down by my SharpReader and a Outlook-style notification pop-up would tell me about it if I’m at the PC otherwise next time I look at my reader I’ll see the new report and be able to review it.  There would be no need for me to manage those reports because they’d all be on the feed and stay there until my configured expire date, which could be different for daily, weekly, and monthly reports.  If I wanted a report that had dropped off my reader, I’d just go run the report like I currently do today.

Implementation wise, there would just be an “item” table in a SQL Server database where generated reports would be dumped by scheduled tasks.  The item table could have a “category” field that would also allow subset feeds for each category.  Super simple.

Is this idea cool or what?  :-)  Maybe I should track down the SQL Server Reporting Services guys and ask them to consider for v1.1?  Or maybe some SQL Server guru somewhere could just write a custom output format and custom delivery target and publish an article somewhere?

Annoying bug in Microsoft Access

I use MS Access from time to time, and for what it is, it is a pretty nice tool. However, there are a few things I’d really like to see added (subject of a future blog) and there are a few bugs that I run across from time to time that really frustrate. For example, I just ran across one of them.

Let’s say you have a simple table in a SQL Server database and you have an Access Data Project (an .ADP file) pointing to that database. Let’s say that table is called tblPeople and it has fields ID, Name, and Email. Further, let’s say we want to require the values in the Email field to be either 1.) NULL, or 2.) Unique. Of course you can’t set a constraint for it to be unique because then it has to be non-NULL. But, you can create a calculated field, let’s call it EmailKey that refers to the Email field if non-null, or refers to the int ID field converted to character when Email is null, i.e.:

ISNULL([Email],CONVERT(varchar,[ID]))

Then, create a UNIQUE index on EmailKey, and everything works great!

Great that is, except for when you try to add or edit a record from within Access and then you get this following error message "UPDATE failed because the following SET options have incorrect settings: ‘ARITHABORT’":

Microsoft Access Error Message: UPDATE failed because the following SET options have incorrect settings: 'ARITHABORT'

Arrggghhh!!! Perchance anyone know how to fix this, or it is just something I have to wait for MS to fix? (btw, this problem has existed for several versions of Access.)