CREATE BLOG dbo.SqlServer

NOTE: In case you haven't read my review of the HP Pavilion dv9000 laptop, you might want to do so before reading this post.

Let me explain what's going early on so that my rant makes you sick enough so that you will not buy from HP's website, hpshopping.com. My goal here is to prevent you from going through the same frustration I've experienced.

1. On February 23, 2007, I ordered a new dv9000t Pavilion notebook
2. It arrived sometime around March 1
3. On March 12 it was shipped back to the factory because apparently the dv9000's have a serious problem overheating (I just checked and the disk drive temperature at idle state is 49 degrees Celsius) and it would shut off after just minutes of activity.
   
4. On March 30, they informed me that the ship date for my laptop was still unknown. I explained that this was unacceptable and pressed for a new laptop. They obliged after I threatened legal action. They also agreed to provide me with an upgraded video card (from 256MB to 512MB) and to upgrade the screen to the highest resolution/quality screen.
5. On April 11, they said the laptop would arrive on April 13
6. On April 17, they said the laptop would arrive on April 20
7. I received the laptop April 27 (46 days after I shipped it back to them) but it does not have the upgraded video card, the upgraded screen, and worse, it doesn't even have the full 2GB of memory that the original laptop had! That's right - the computer they sent me is actually less than what I originally paid for :( They stole 1GB of RAM from me as far as I'm concerned.

Today is April 28th and, all together, I've had a laptop for 2 weeks from HP despite my giving them nearly $2100 on February 23. I've put in a call to my case manager, Lori Clemens, regarding this but she generally takes 3-5 business days to return a call.

I learned my lesson too on this: I originally paid for the laptop with a debit card, not a credit card, despite knowing better. I don't know why - it wasn't even a spur of the moment decision. In early April, I filed complaints with the Better Business Bureau in Santa Clara as well as the State Attorney General offices in both Texas and California. My next step is to talk (again) to my lawyer and find out how to just get my money back.

Do yourself a favor and avoid buying from hpshopping.com.

I just came across this today (thanks to reading Jasper Smith's blog) - Bob Ward, a member of Microsoft Product Support Services (PSS) team, has a great blog entry that explains the SQL Server 2005 Sp2 snafu and provides great advice on what to do ("Should I install SP2 and a hotfix?"). The best part about this is the list of file versions and how they can be different depending on what you've actually installed.

Check it out here.

Here's one I had not seen before:

SQL Server is not ready to accept new client connections. Wait a few minutes before trying again. If you have access to the error log, look for the informational message that indicates that SQL Server is ready before trying to connect again. [CLIENT: ]

I got this one today after installing a new certificate. I waited and, sure enough, it let me in. Funny message though!

This was in the Windows Application Event Log and, since it suggested that I browse to the SQL error log, I did. Sure enough, when it logged the message, "SQL Server is now ready for client connections. This is an informational message; no user action is required.", I was able to log in.

I don't know how helpful this will be for anyone but, today when my SQL Server 2005 default instance was not accepting any connections of any kind, it saved me. I'm on a web dev machine and was toying around with the certificates installed for SSL and I deleted the self-signed certificate that SQL Server 2005 generated for me automatically. From that point on, I could no longer connect to SQL Server. Also, because I had created a self-signed certificate in my local computer certificate store with the CN=, SQL Server 2005 was no longer auto-generating a new certificate on startup.

Bummer.

Well, here's how you can change your SQL Server from using a self-generated certificate to any other certificate:

1. At the DOS prompt, type MMC.exe to launch the MMC
2. Add new span=ins for "Certificates" (I use Local computer but you could use personal as well) and "SQL Server Configuration Manager" (File-Add/Remove Snap-Ins)
3. Ensure that the certificate you wish to use for SQL Server is in the "Personal/Certificates" folder for either the Local machine or your personal use (you kinda need to know where you installed your certificate to know which to use)
4. In the SQL Server Configuration Manager, drill down to the "SQL Server 2005 Network Configuration" and right-click on "Protocols for MSSQLSERVER" and select "Properties"
1. MSSQLSERVER is your default instance name. Change this if you are not assigning this certificate for your default instance
5. Flip to the "Certificate" tab and choose the certificate you wish to use


Hopefully this helps someone some day. I have to thank Il-Sung Lee for the "inspiration" for this post though. His post on SSL in SQL Server 2005 is what helped me resolve my issue.
I was toying around with the certificates on my machine and generated a few new certificates using OpenSSL and SelfSSL and, for whatever reason, SQL Server 2005 stopped accepting incoming connections after a restart. I suspect that I deleted the self-signed certificate that SQL creates when it starts up but it should've auto-generated one - or at least that's my understanding - and it didn't. I think it's a bug - SQL Server stopped accepting connections and would no longer auot-generate a certificate.

In order to help people find this solution in case they get into the same problem, I'm going to enter some of the errors I received when trying to connect to my SQL Server 2005 default instance that (1) has remote connections enabled, (2) the SQL Browser service was running, and (3) all protocols were enabled for both client and server:

• Named Pipes Provider: No process is on the other end of the pipe.
• Microsoft SQL Native Client : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
• My SQL Server Agent would not start due to a "Cannot connect to SQL Server 2005" error
• I tried odbcping.exe and using ODBC to connect - no luck
• I tried connecting through both osql and sqlcmd with no luck
• An error occurred during the pre-handshake login process

Here's two great resources for diagnosing SQL Server connection errors:

• http://blogs.msdn.com/sql_protocols/archive/2006/07/26/678596.aspx
• This post includes the solution to this problem but I didn't find it until after I had "fixed" the server! Dang...
  
• http://blogs.msdn.com/sql_protocols/archive/2005/10/22/483684.aspx

Anyway, maybe this helps you someday. Let me know if it does - I can't take the credit though since it was really Il-Sung Lee's post that helped me resolve my own problems!

Whew - this has been a challenging last 30 days as I've tried and tried to just get SP2 to work in various environments without generic and generally not-helping-thank-you-though error messages. I love the new features in SP2 particularly the ability to create and use Custom Reports, but today I've had about four hours of SQL Hell lol. One problem was that I installed Reporting Services onto an already-SP2 machine. I then applied SP2a then patched to 3152 - all good, right?

After that, my RSConfigTool.exe (a.k.a., the Report Services Configuration Manager/Tool/Utility) doesn't work lol. When I launch it, it lets me connect to SSRS but immediately throws one of the "Unhandled Exception has occurred in your application. If you click continue..." popups that we get when we are in development and haven't implemented an exception handler.... ahem....

Here's the error thrown:

System.Management.ManagementException was unhandled
Message="Not found "
Source="System.Windows.Forms"
StackTrace:
at System.Windows.Forms.Control.MarshaledInvoke(Control caller, Delegate method, Object[] args, Boolean synchronous)
at System.Windows.Forms.Control.Invoke(Delegate method, Object[] args)
at System.Windows.Forms.Control.Invoke(Delegate method)
at ReportServicesConfigUI.ConfigurationManager.Panel_ConfigurationChanged(Object sender, EventArgs e)
at ReportServicesConfigUI.Panels.ConfigurationPanel.OnConfigurationChanged(EventArgs e)
at ReportServicesConfigUI.Panels.DatabaseSetupPanel.SetDatabaseConnectionTask(String connectionString, String server, String database, ConfigurationCredentialsType credsType, String account, String password, Boolean upgrade, String dbVersion)
at ReportServicesConfigUI.Panels.DatabaseSetupPanel.SetDatabaseConnectionTask(Object state)
at System.Threading._ThreadPoolWaitCallback.WaitCallback_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading._ThreadPoolWaitCallback.PerformWaitCallback(Object state)

Likely it's too generic to offer anything but I put it up here just in case someone else has seen this :(

Another error that I've gotten in the RSConfigTool.exe is:

An unhandled exception ('System.Management.ManagementException') occurred in RSConfigTool.exe [7632]

When I click a panel, it then pops up a message that says:

There was an error while switching panels. The most likely cause is an error retrieving WMI properties. The exception details are: Not found

Speaking of WMI, I had a weird error installing SP2 on a Windows 2003 Standard Edition server today that had two instances, one with RTM and one with SP1 on it. The SP1 instance (the default) upgraded fine but the RTM would not update and the error message said something like, "An error occurred. Run over to Google and find KB921896 for details."

So I did.

And I'm not friggin happy!!!

KB921896 is the list of bugs fixed in Service Pack 2....

So, what would you do next? Well, I tried installing Service Pack 1 onto this instance which lasted for a total of 2 minutes before throwing three errors in succession. The final error? "An error has occurred. Please point yourself quickly to KB913090."

You guessed it: KB913090 is a list of all the bugs fixed in SQL Server 2005 SP1 lol.

Okay....

So which is it: do the folks at MSFT have a huge sense of humor or are they just rushing stuff out the door to meet some deadline that we don't know about? I don't know and I'm just kidding around anyway - we all know they don't have a sense of humor!

;)

Labels: Reporting Services, sql 2005, sql server 2005, sql server 2005 sp2

How to Find Out the Build Number for SQL Server Reporting Services

How do you find out if Reporting Services has Service Pack 2 installed? It's actually not easy!

You can look certainly at the SQL Server 2005 instance and determine whether it has SPx installed rather easily in the SQL Server Management Studio. One way is to connect to the SQL instance in the Object Explorer. The Object Explorer will show you the build number (such as 9.00.3042, .3043, .3050, .3152, etc. You can also easily see the build number of Integration Services in SSMS by connecting the Object Explorer to your Integration Services instance in the same way. But what about Reporting Services? It does *not* show you the build information...

There are lots of reason we need to know this information. You do know (or should know) that you can install SQL Server 2005 SP1/Sp2 without installing the service pack for Reporting Services, right? You could also have installed Reporting Services after installing SQL Server 2005 with a Service Pack and now you need to apply the SP to SSRS. So how can we figure out whether or not SSRS is up to SP2 (or SP whatever)?

The most logical and straight-forward way to determine whether your Report Server is using RTM, SP1, SP2, etc, is to look at the file version of ReportingServicesService.exe. On my server, this is installed in C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\ReportServer\bin\ and it may be for you as well. Simply right click on the file and choose "Properties". Click the "Version" tab and look at the "File Version". The various file versions and what they mean are listed below:

* 9.0.3042.0 or later - Service Pack 2
* 9.0.2047.0 - Service Pack 1
* 9.0.1399.0 - RTM

There are other visual indicators as well. If SP2 is installed, you will see a file named "rsreportserversp2update.config" in the root of your \ReportServer\ app. You'll also see a ReportService2006.asmx installed (as opposed to just having ReportService2005.asmx).

If you'll pop on over to your \ReportManager\ app and look in the "bin" directory, you can also view the File Version of the various .dlls. If they are dated February, 2007 or later then your Report Manager is at least on Service Pack 2.

Of course, you can always look in the logs, right? Mine are here: C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\LogFiles\. You will also see the file versions listed in the logs:

* Microsoft SQL Server Reporting Services Version 9.00.1399.00
* Microsoft SQL Server Reporting Services Version 9.00.2047.00
* Microsoft SQL Server Reporting Services Version 9.00.3042.00

Do you know of other ways to view the SSRS version/build number that I haven't listed? Please let me know :)

Labels: Reporting Services, SSRS

I was googling today for a few query tweaking tools and came across Adam Machanic's SqlQueryStress utility which is FREE and is a nice little tool that can show you information such as how "fast" a query will run 1x, 10x, 100x, specific metrics about page reads, CPU time, and a little more. It works with stored procedures, functions, views, ad-hoc queries - whatever.

It's too small to do a review on and besides, Adam made the software so easy to use that you really don't need much of a tutorial :) I did find a tiny bug when I was playing with it for the first time. If you type a query into the "Query" textbox, then click "Parameter Substitution", and then the "Get Columns" button, you get an error: "ExecuteReader: CommandText property has not been initialized." Obviously I shouldn't have clicked that button since my query had no parameters but it was my first time using it so I was just playing :)

I suggest you download it and play with it while it's free. It's very handy and who knows, Adam may get the bright idea to charge us for using his handy tool in the future! You can download it here. I particularly like the parameter substitution bit where I can take values from TableA and substitute them into queries against TableB. Adam posted a nice walk-through here.

Wishlist:

• I wish it would preserve my connection data between sessions
• I wish I could use GO to separate statements but an exception is raised
• I wish I had an option to dump the plan cache and buffer cache before running the "test"
• I tried adding DBCC FREEPROCCACHE, a GO, and then my query/proc but it failed. Once I removed the GO it was fine.
• I wish I could log the exceptions to a file
  
• I wish I could generate a report/something that would allow me to keep historical results of this information. The format is not important: .xml, .txt, .csv. I just want to be able to test a query, log the results, change something about the table/query/etc, run the query again, and compare results against previous execution.
  

We all owe Adam a bit of thanks for this nice tool so, "Thanks, Adam!"

Labels: sql 2005, sql server, sql server 2005

So I am the author of our new site, LearnReportingServices.com, and I wanted to update you on the launch date. This is cross-posted from LearnSqlServer.com as well...

Right now, I'm dealing with two mitigating factors that will drive when the new SSRS tutorial site is up and running. I have about 1/3 of the videos completed and two things will determine when I finish the remaining 2/3s:

1. When does my new laptop arrive? It is currently at HP Support and should arrive within 7-14 *business* days but hopefully sooner
   
2. My wife, Susan, and I are dealing with a health issue that: will be resolved simetime within the next three weeks but it is unknown *when* it will happen within that time frame
   

As for #1 above, my current laptop is sufficient for recording the videos so the impact there will be minimal. The difficult part is that my current laptop takes about 2-3x as long to produce the videos as the new one will which means he has to set aside off-time to produce the vids (for 50+ videos, it will take as long as 5-6 hours of dedicated production time wherein the only thing that can happen on the PC is that the videos get produced). As for #2 above, well - I'll just have to let you guess what the issue is because, out of respect for my family's privacy, I just can't elaborate. Let's just say that we are on what is known in the poker world as a freeroll. In other words, there is nothing wrong, nobody is sick and there's no reason to worry: only good can come out of it! I'll update you if I can...

Looking at the launch date for LearnReportingServices.com, when we planned the site's launch way back in January, we originally intended it to launch April 17th. There's about an 80% chance that it will still launch on April 17th (and that will be the earliest possible date). The 50% off pre-launch discount will be extended until 1 day before the launch as we always do so make sure that you purchase yours today (click here to add the subscription to your cart - you can always remove it later). There is also the possibility that the site will launch as late as the 30th but that will be the latest possible date. I apologize for not providing this information sooner but, as is often the case with health issues, my family and I are dealing with unknown time frames. As I said earlier: no one is sick and nothing is wrong so please don't worry.

So, to sum up - the site will launch sometime between April 17th and April 30th and we will give as much advance notice as we can. Since I can't exactly say when things will happen, I won't be able to give you more than a day or two's notice that the site will launch so, if you really, really want to get the 50% off pre-launch discount, you'd better get it now!

I will keep you updated through this site and in my blog.

Thank you for your understanding -

Scott Whigham

P.S. - Of course, it should go without saying that if anyone wants a refund of their 50% Off pre-launch subscription that they've already paid for for LearnReportingServices.com, we will be happy to refund your money. Just contact us through our online form here.