CREATE BLOG dbo.SqlServer

Over at LearnItFirst.com, we have over two hours worth of free SQL Server training videos. If you need free SQL Server 2008 training videos, we have 'em! Check it out:

http://www.learnitfirst.com/Free/SQL-Server-Training.aspx

Labels: sql, sql server, sql server 2005, sql server 2008

Are you new to the world that is SQL Server and in dire need of affordable, online SQL Server training? Or maybe you just need to brush up on your SQL Server programming skills. Whatever the case may be, I have found a great site that offers computer based training for SQL Server - LearnItFirst.com.


These aren't your run-of-the-mill training videos either. The videos from LearnItFirst.com are recorded by expert instructors and can be downloaded and watched anytime, anywhere. LearnItFirst.com also offers online training videos for Windows Server, Exchange Server and SharePoint Services, so check them out today!

Labels: sql dba, sql server

For two weeks in a row, Windows Update has told me that I need to patch my SQL Server 2005 SP2 installation - with a lower build number than I currently have! I have build 3159 (click here for an explanation of the SP2 silliness) and yet Windows Update tells me that I should let it install build 3150. When it popped up two weeks ago, I told it to not install and to not notify me again of this patch but, hey! Whaddayaknow? It popped up again today asking me to install it. I think that, since it was Tuesday, it felt the need to do *something*...

Oh hell - just as I started trying to find the link to 3159, i noticed that a new hotfix is out dated two days ago: build 3161. It is really annoying at how they have been forced to throw out so many hotfixes post-SP2. Oh well - do you think they've learned their lesson so that we won't have this problem again? No? Me either... We went through this with Service Pack 2 for SQL Server 2000 as well you may remember if you've been around SQL Server for more than a few years.

Anyway, back on topic...

Here's a few helpful links to help you wade through the myriad of hotfixes and keep up-to-date:

• Aaron Bertrand has a comprehensive list going of all the hotfixes here
• SQL Server 2005 Service Pack 2 (SP2) Re-release and post fixes explained

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

Want to download the latest CTP of SQL Server 2008 (nee SQL Server "Katmai")? I just did :)

If you want to download this first beta of SQL 2008, check it out here: https://connect.microsoft.com/SQLServer/content/content.aspx?ContentID=5395

It takes a good deal of logic problem solving to figure out this website but, once you do, you're in!

Labels: sql, sql server, sql server 2008

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

Microsoft released build 9.0.3161 (a.k.a. build 3161) for SQL Server 2005 on May 22. This is one of those interim hotfixes that you must receive permission from Microsoft to receive and install. In the past, acquiring this hotfix would've required a potentially-paid phone call to Microsoft Product Support. Today though all you need to do is to submit an online request via Microsoft Online Customer Service (good luck!). I would suggest that you read the fix list (available here) before you decide that you need this version. Check out KB 935356 here.


PDC 2007 Canceled!
Yesterday, Microsoft canceled a developer conference - a big developer conference, the PDC. This is really, really odd in my opinion. If ever there was a year to have a developer conference, I would imagine this would've been it. Think about all the products released this year, last year and next year:

• Windows Vista
• Office 2007
• Exchange 2007
• SharePoint 2007/3.0
• Longhorn/Windows 2008
• Silverlight
• Ajax for ASP.NET
  
• Visual Studio Team Suite for Database Professionals (or whatever the hell that silly name is)
• SQL Server 2008 ("Katmai")
  
• Visual Studio 2008 ("Orcas")

I'm just having trouble understanding the logic of their press release announcing the "rescheduling" (read it here). Interesting tidbit in the article though:

We are currently in the process of rescheduling [emphasis mine] this fall’s Professional Developer Conference. As the PDC is the definitive developer event focused on the future of the Microsoft platform, we try to align it to be in front of major platform milestones. By this fall, however, upcoming platform technologies including Windows Server 2008, SQL Server codenamed "Katmai," Visual Studio codenamed "Orcas" and Silverlight will already be in developers' hands and approaching launch, which is where we’ll focus our developer engagement in the near term.

I don't know what to think but I sure as hell don't believe the press release. I've read a few opinions from folks and one pundit said that he felt Microsoft just wasn't going to be ready for Windows 2008 by PDC and decided to bail. I don't know... I went to TechEd 2003 - paid the $1400 - with the express purpose of getting the prelaunch info on SQL Server 2003 lol. I was mad as #$#% when they announced a few days before the conference that SQL Server v.Next wasn't going to be released until 2004 (lol). As far as I was concerned, the whole conference was a wasted $1400. Therefore, if that is the reason, then I appreciate Microsoft canceling the conference.

I don't know - I'm rambling at this point... Anyway - just wanted to pass on the news about the new hotfix really.

Labels: sql server, sql server 2005

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'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