CREATE BLOG dbo.SqlServer

I received a question in our forum yesterday that stumped (and still stumps) me. Here's the forum discussion on the problem: http://forums.learnsqlserver.com/SqlServerTopic115.aspx?m=217#217

Basically, what we're seeing here is that, when you use brackets to delimit a parameterized expression in a stored procedure/function, your stored procedure will compile but it can never execute unless you override it's value. If, however, you try the same steps with a more declarative SQL, then it works fine.

It's quite odd and I just don't get it. I understand that brackets are a delimiter but why, as a stored procedure parameter, do they explicitly convert the argument to NVARCHAR? Has anyone see this before?

Labels: sql 2005, sql server

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

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

In case you don't know, for a few weeks, we have build 9.0.3042 of SQL Server 2005 SP2 but, on March 6, 2007, it was "refreshed" to 9.0.3043 so that it included a hotfix for some pretty intense maintenance plan scheduling failures.

Now, the same day they released 9.0.3043, MSFT also released build 9.0.3050 which is for those people who installed the original version of SP2 (9.0.3042). Yes, this is a bit strange: build 3050 effectively brings SQL Server 2005 SP2 build 9.0.3050 "down to" SQL Server 2005 SP2 build 3043's level. KB Article is dated March 23.... At least, given the docs, that's what I *think* is the case.

The very next day came build 9.0.3152 (March 7, 2007) which is billed as a "Cumulative Hotfix for SP2". This includes the file sqlserver2005-kb933097-x86-enu.exe and it resolves a bunch of bugs but apparently it wasn't ready to be released on March 6 so it was released on March 7.

Are you still reading?

Good. I need you here because actually, I'm kind of lost as to what to install! Does build 3152 include the fixes in 3050 or is it only for 3043? The KB article says that it is a cumulative hotfix but does not mention builds 4050, 3043 or 3042 at all. The KB article for 3152 mentions builds
2214 and 2219 only.....................

Do I need to uninstall 3050 just to install 3152?
Does 3152 require 3043 only?

Arrrgghhhhh!

Sooooooooo - I've just installed 3152... Let me go check it out and see what it says (and whether the SQL Server 2005 Performance Dashboard reports run successfully since they didn't on build 3050).

..........................

Well, the ProductLevel is still SP2 (SELECT SERVERPROPERTY('ProductLevel') ) but now the ProductVersion shows 9.0.3152 (SELECT SERVERPROPERTY('ProductVersion')) but the SQL Server Performance Dashboard reports still don't work... So much for that!

Labels: sql 2005, sql server 2005, sql server 2005 sp2

So I'm trying to get the SQL Server 2005 Performance Dashboard reports installed on my SQL Server 2005 Developer Edition and I'm not getting anywhere...

I successfully run SETUP.SQL so that it adds the necessary support objects into the MSDB but I cannot get a single report to run :(

I get a variety of errors:

• Report parameter 'session_id' was not found
• The 'version_string' parameter is missing a value
• Report parameter 'include_system_processes' is missing a value

It seems that, for whatever reason, SQL Server Management Studio (SSMS) is not passing the parameter values or the .rdl file is not able to get the values.

I've tried this on two separate machines, one S.E. and one D.E. edition. Both are running 9.0.3050 (so don't ask if I have installed kb933508, cuz I have!).

Help! Anyone know why I'm getting these failures? I've googled/yahoo'ed and the only site I came up with was something in Russian and it didn't offer any solution (that I could tell).

Labels: sql 2005, sql server, sql server 2005

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 preLoginpacket 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 Loginpage/link! It's either that or it returns results that require me to Loginon 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