CREATE BLOG dbo.SqlServer

Recently we've had a few discussions about whether or not we would be willing to host ads on our sites. Yesterday, we spoke to a very large company who makes development tools and management software that is (a) directly related to SQL Server, Windows 2003, Exchange, Sharepoint et al, and (b) is one of the best software companies around. They were asking us if we would be willing to run text links and/or banner ads.

So...

We want to hear from you on this topic - what do you think? Here's a few questions that we're hoping to get feedback on:

1) If an ad was (a) relevant, and (b) did not detract in any way from the main goal of the site, would it still be intrusive to you? (We are most concerned about upsetting you, our user. Advertising simply isn't worth it if it makes your experience on our site less enjoyable)

2) What websites do you visit daily that do not have advertisements (except ours)?

To post feedback, send us an email

If you've been on our sites for very long, you're already accustomed to our in-house/cross-site advertising and this new advertising would either replace or be in addition.

Final thoughts (and we're not saying that we will allow advertising; we're just exploring the possibility):

1) We promise that we will NOT allow any "annoying" ads, "flashing" ads, or any of the "bad" ads that you see on places like MySpace.com. Ads would be placed prominently but not in areas that would take away from your normal browsing experience.

2) No Google or Yahoo or MSN or whatever ads like you see on blogs - these ads will be companies that we know, we respect, and that we would feel comfortable recommend to others (i.e., all ads will be vetted manually before being allowed on any of our sites).

The other day I got a question from a former student about a strange CASE statement error message she was getting: "None of the result expressions in a CASE specification can be NULL". She was kind enough to post her code and, sure enough, when I tested it, I got the same error. Now I've been around SQL Server for a long time but I've never seen SQL Server throw error 8133 so I started playing around... First - let's have a look at the code:

USE tempdb
GO
CREATE TABLE MyTable (EmpId INT NOT NULL PRIMARY KEY, EmpName VARCHAR(128))
GO
INSERT MyTable VALUES (1, 'Scott Whigham')
INSERT MyTable VALUES (2, 'Aimee Wilson')

SELECT EmpId, CASE WHEN EmpName = 'Scott Whigham' THEN NULL END AS TheName FROM MyTable

When (or "if") you try to run this code, you too will get error 8133, "None of the result expressions in a CASE specification can be NULL". If you know anything about SQL Server's case statements, you know that to be a lie - and we can easily prove it:

SELECT EmpId, CASE WHEN EmpName = 'Scott Whigham' THEN NULL ELSE EmpName END AS TheName FROM MyTable

Simply by adding the ELSE clause into the statement we get the query to run without an error - therefore, as far as I can tell, the error text for 8133 is misleading at best.

Here's a funny twist on it: if you reverse the logic of the original, offending statement, it works great:

SELECT EmpId, CASE WHEN EmpName <> 'Scott Whigham' THEN EmpName END AS TheName FROM MyTable

Although the syntax is different, this query is the same as the first query listed above yet it works fine. If you aren't aware of the logic of the CASE operator, because there is no ELSE clause in the above query, then it returns NULL for any EmpName = 'Scott Whigham' (which was what the first query asked for explicitly).

I can't find anything on the web about this error that gives me a reason for it showing up here - anyone have any ideas?

So here I am, running a sync script today to synchronize a remote production database with a local one and BAM! I get hit by a ridiculous (IMO) error. The table has an XML column therefore I cannot write a distributed query!

Msg 9514, Level 16, State 1, Line 2
Xml data type is not supported in distributed queries. Remote object 'dbo.MyTable' has xml column(s).

The silliness is that my query does not reference the XML column at all! Arrrgggh - I can't begin to imagine what sort of logic there is for not allowing me to write a distributed query against the "regular" (non-xml) columns. What a humongous pain...

Okay - let's recound what I've done this year:

1. Used the Control Panel/Services applet to change the SQL Server and SQL Agent service account passwords
2. Nothing else
   

Let's look at aalllllllll the fun (and different!) error messages and experiences today so far:

1. FallBack certificate initialization failed with error code: 4
2. The prelogin packet used to open the connection is structurally invalid; the connection has been closed. Please contact the vendor of the client library.
3. When trying to connect to my SSIS packages using SSMS: Client unable to establish connection
   Encryption not supported on SQL Server. (MsDtsSrvr) [note: I can browse/open file-based packages just fine)
1. Failed to retrieve data for this request was also seen
4. When trying to retrieve SSIS package info from a job: The LoadFromSqlServerMethod has encountered an OLE DB error code 0x80004005 (Client unable to establish connection...)
5. When trying to run SSIS package from job: Executed as user: .\Administrator. The package could not be loaded. The step failed.
   
6. All SSIS packages report an error upon opening:
   
1. The description for Event ID '1073819649' in Source 'SQLISPackage' cannot be found
2. SSIS An OLE DB error has occurred. Error code: 0x80040E4D (which was the result of it failing to remember my passwords when I typed them in after the previous error, I guess. Once I set the security to DoNoSaveSensitive and then saved it back to default of SaveWithUserKey it worked lol).
   
3. There were errors while the package was being loaded.
   The package might be corrupted.
   See the Error List for details.
4. The SQL server specified in SSIS service configuration is not present or is not available. This might occur when there is no default instance of SQL Server on the computer. For more information, see the topic "Configuring the Integration Services Service" in Server 2005 Books Online.
1. Of course I only have a single (default) instance of SQL 2005 on my machine

Unfortunately, due to so many errors and my being in a race to resolve them, I may have mixed one or two together in the list above.

Anyway, I have resolved 80% of the errors above but I still cannot connect up to my SSIS packages stored in msdb nor can I execute any jobs :(

And you know what is perhaps the most annoying part about this? When I go to Google, type in any one of the error messages/codes above and then click on almost any of the Microsoft links returned, it tries to force me to LOG OUT or CREATE ACCOUNT - there is no login page/link! It's either that or it returns results that require me to login on a different site to view the answer/post. Time for a new search engine?

I think this all stemmed from my not using the SQL Configuration Manager to change the passwords (instead, I used the Control Panel/Administrative Tools Services MMC snap-in). I know that I should use the Config Mgr but I didn't for whatever reason!

Labels: dts, integration services, sql, sql 2005, sql server, sql server 2005, ssis