Steve's SQL Server Blog

SSMS 2008 cannot connect to a SSIS 2005 instance

Read the title carefully and take it literally. I had discussions with people trying to convince me this is indeed possible. What they were trying to explain to me is the following:

Let's take a SQL Server Management Studio 2008 and connect to a SQL Server Integration Services 2005 instance. Proof can be found here.

It won't work. One would think it's backward compatible but it's not.
However, there is somewhat a workaround: connect to SSIS 2008 instance through SSMS 2008 which of course should work AND within the stored packages node, you are indeed able to connect to 2005 packages.

Literally, SSMS 2008 cannot connect to a SSIS 2005 instance

 

SQL Server Day 2009

On Thursday, the 3rd of December 2009 SQLUG.BE (The Belgian SQL Server User Group) and Microsoft are teaming up to organize the second Belgian SQL Server Day. Focus of the day is off course SQL Server.

Last year, SQL Server Day 2008 was the biggest community driven SQL Server event in the Benelux, with more than 200 attendees.

Date and hours:
December, 3rd, 2009
Registration opens at 9.00 AM. The opening keynote starts at 10.00 AM.
The event is free, but registration is required.

Sessions:
The event will kick-off with an international speaker positioning the SQL Server data platform, today and tomorrow.

We have 6 break-out sessions with technical content on SQL Server. The speakers are recognized international SQL Server experts.

During the lunch break our gold partners will present their solutions and offerings in the lightning sessions .

We conclude the day with a closing keynote on Gemini, the new breakthrough Self-Service Business Intelligence (BI) capabilities being delivered in SQL Server 2008 R2.

The full agenda can be found here.

Location:
Utopolis Mechelen
Spuibeekstraat 5
2800 Mechelen

SQL Server MVP Deep Dives

I'm about to preorder a copy of SQL Server MVP Deep Dives at Amazon.

53 SQL Server MVP's have been working on the book for the past year or so. Among the authors are Paul Nielsen, Hugo Kornelis, Itzik Ben-Gan, Bob Beauchemin, Erland Sommarskog, Brad McGehee, Tibor Karaszi, Aaron Bertrand, Joe Webb, Kevin Kline

Now, where did I leave my Master Card?

SQL Server Management Studio Hack

Database administrators are lazy by nature. And although Books Online is a very good resource I'd like to get my own mini overview of most used statements in a new query template such as this one:

 

So whenever I hit "New Query" I get my template. It's also usefull when you want to send your script to a colleague so you won't have to tell him/her what server they need to connect to and which database to select.

You might want to give it a try.

1. Open file 'C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\SQL\SQLFile.sql' in Management Studio and copy the code below and save.

-- ! Please execute in SQLCMD mode ! click SQLCMD Mode on the SQL Server Management Studio Query menu,

SET NOCOUNT ON
:CONNECT <servername>

USE <databasename>
GO

 
/*

 DBCC SQLPERF(LOGSPACE)WITH NO_INFOMSGS
 DBCC OPENTRAN WITH TABLERESULTS
 DBCC INPUTBUFFER ( session_id )
 DBCC SHOW_STATISTICS ("Person.Address", AK_Address_rowguid) WITH HISTOGRAM;
 DBCC SHOWCONTIG ("HumanResources.Employee");
 DBCC FREEPROCCACHE WITH NO_INFOMSGS;
 DBCC DROPCLEANBUFFERS [ WITH NO_INFOMSGS ]
 DBCC CHECKCONSTRAINTS ("Production.CK_ProductCostHistory_EndDate");
 DBCC DBREINDEX ("HumanResources.Employee", " ", 70);
 DBCC INDEXDEFRAG (AdventureWorks, "Production.Product", PK_Product_ProductID)
 DBCC CHECKDB;
 DBCC CHECKTABLE ("HumanResources.Employee") WITH PHYSICAL_ONLY;

 EXEC sp_helpfile
 EXEC sp_spaceused
 EXEC sp_change_users_login 'Report';
 EXEC master.dbo.xp_fixeddrives

*/
 

Community Day 2009

 

Community Day is back again, are you ready for our Third Edition of Microsoft Community Day?
 
Eleven Microsoft User Groups combine their efforts to organize this unique networking and knowledge sharing
event. A unique opportunity to learn about Microsoft’s latest developments and technologies like Exchange
2010, Silverlight 3, Visual Studio 2010, SQL Server 2008, Windows 2008R2, Powershell V2, Forefront
Identity Manager 2010, XNA 3.1. and OCS R2.
 
Microsoft Community Day will take place on Thursday 25th June 2009 in Utopolis, Mechelen, where we will
bring together 300 IT Pro’s and developers.
 
The Microsoft Community Day 2009 is supported by BESUG, BIWUG, IT-Talks, MVUG, Pro-Exchange, SCUG, SQLUG, VBIB, Visug, WinSec, XNA-BUG.

(Original source: www.mscommunity.be)

 

SQL Server 2008 Developer Training Kit

I stumbled upon the SQL Server 2008 Developer Training Kit in the latest weekly Microsoft Download Notifications: May 15, 2009 newsletter.

Topics discussed in the training kit:

Let the wife know you won't be available this week.

 

Real World DBA podcasts

A very nice collection (2.4 hours) of the Real World DBA podcast series by Buck Woody: http://edge.technet.com/Tags/Real+World+DBA/

 

Cannot find template file for the new query

Be carefull when using cleanup tools which remove empty files from your system.
The application I used deleted files with 0KB sizes.

Apparently it also deleted files like C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\SQL\SQLFile.sql which is 108 Bytes.

You can spot it from a 20 miles distance this is going to be major poo poo.
What was the impact removing that "SQLFile.sql" file?

I fired up SQL Server Management Studio and hit the New Query button and this popped up:

---------------------------
Microsoft SQL Server Management Studio
---------------------------
Cannot find template file for the new query ('C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\SQL\SQLFile.sql').
---------------------------
OK  
---------------------------

I created an empty sql script file and named it SQLFile.sql. That's it!

Sigh...

The DELETE statement conflicted with the REFERENCE constraint "FK_subplan_job_id"

I was 'trying' to delete an obselete SQL Server Agent job until this nice error popped up:

 

 

TITLE: Microsoft SQL Server Management Studio

------------------------------

 

Drop failed for Job 'Optimiser.Subplan_1'.  (Microsoft.SqlServer.Smo)

 

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3239.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+Job&LinkId=20476

 

------------------------------

ADDITIONAL INFORMATION:

 

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

 

------------------------------

 

The DELETE statement conflicted with the REFERENCE constraint "FK_subplan_job_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_subplans", column 'job_id'.

The statement has been terminated. (Microsoft SQL Server, Error: 547)

 

The solution:

 

use msdb

go

 

select * from sysmaintplan_subplans

 

delete from sysmaintplan_subplans

where subplan_name = 'Subplan_1' -- 'Subplan_1' in my case is the name of the subplan which is the culprit here

SQL Server Myths

I knew it!!

I noticed the following blog post the other day on SQL Server myths: http://sqlblog.com/blogs/paul_nielsen/archive/2007/12/28/sql-myths.aspx
We tend to take things for granted when reading blog posts. Aparently there is a lot of crap written about SQL Server on the Internet.
Man I sometimes can't believe what people write about indexes or transaction logs!

Who's right and who's not? Blogs are a great learning resource but I'd rather read from the great ones or buy a good descent book.

Community Day 2008

The Belgian SQL Server User Group will take part in the next Community Day which will take place in Keerbergen at June 26th.
One or two free sessions will be given by each one of the 9 participating user groups
Community Day is a free event including food and beverages.

http://www.communityday.be/

SQLCMD + SQLDIAG

I stepped in some cat food on my way to my computer. It was duck I believe. Darn #@! cat...

But nevertheless, today I played around with SQLCMD mode in Management Studio. 
When running the following script, the SQLDIAG STOP_ABORT immediately gets executed before the stored procedure has even started.

:!! SQLDIAG /R /Q /O"C:\SQLDIAGResults"
:!! SQLDIAG START

EXEC my_proc
GO

:!! SQLDIAG STOP_ABORT

A small workaround I found as I thought it might help if a wrap the execution of the proc: put the exec proc statement in a try and cath block

:!! SQLDIAG /R /Q /O"C:\SQLDIAGResults"
:!! SQLDIAG START

BEGIN TRY 
    EXEC my_proc
END TRY

BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO

:!! SQLDIAG STOP_ABORT

A try and catch statement never hurts does it...

 

Simple SQL Server Database Schema Printer (ASP.NET)

Check this out: A webpage that gets all the tables, their column names, column data types and other information from an SQL Server database.

SQL Server 2008 Exams

The new SQL Server 2008 MCTS exams are expected in august 2008. Need to find a spot in my brain to fit that in.

Read more

Next Belgian SQL Server User Group evening on Thu, March 27