Ambition IT - Weblog

Ambition IT sponsort SQL Zaterdag

HJ — Thu, 31 Mar 2011 12:50:27 GMT

Ambition IT sponsort SQL Zaterdag op 2 april 2011! Zie www.sqlzaterdag.nl .

Key management services (KMS) host could not be located ...

HJ — Tue, 05 Oct 2010 08:57:46 GMT

We installed a new Windows Server 2008 server and were not able to activate online. The activation gave this error message:

Key management services (KMS) host could not be located in domain name system (DNS),
please have your system administrator verify that a kms is published correctly in dns.
0X8007232B.

This post by Code-Journey showed the easiest way to fix this problem: just retype your key.

"It is stated on Microsoft.com to be an error related to trying to activate a Multiple Activation Key(MAK) while not having access to a Key Management Service (KMS).

If you have your volume license key (MAK) all you have to do is go to “Control Panel” -> “System” -> “Change Product key”,
and insert the key. Even if you did it during installation!"

Thank you, Code-Journey!

Wij zoeken een .Net developer

HJ — Fri, 24 Sep 2010 13:08:09 GMT

Ambition IT is op zoek naar een .Net developer! Zie http://ambitionit.heeft-vacatures.nl/.

5 oktober 2010: Donald Farmer bij PASS Nederland

HJ — Fri, 24 Sep 2010 10:46:19 GMT

Op 5 oktober 2010 is Donald Farmer gastspreker bij onze maandelijkse PASS - bijeenkomst! Zie www.sqlpass.nl voor meer details.

SQL Zaterdag 23 oktober 2010: Inschrijving geopend

HJ — Tue, 21 Sep 2010 06:29:52 GMT

De inschrijving voor SQL Zaterdag op 23 oktober 2010 is geopend! Zie http://sqlzaterdag.eventbrite.com.

Error when changing the collation of a database

HJ — Thu, 16 Sep 2010 13:56:03 GMT

Recently I had to change the collation of a database. That resulted in an error message:

The database could not be exclusively locked to perform the operation.

I found the solution at http://www.stolenbit.com/2008/10/database-could-not-be-exclusively.html. To change the collation in this case you have to put the database in single user mode first:

ALTER DATABASE <database_name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE 
ALTER DATABASE <database_name> COLLATE <target_collation_name like SQL_Latin1_General_CP1_CI_AS>
ALTER DATABASE <database_name> SET MULTI_USER

Kimberly Tripp has more on this subject at http://www.sqlskills.com/BLOGS/KIMBERLY/post/Changing-Database-Collation-and-dealing-with-TempDB-Objects.aspx.

SQL Zaterdag 2010 / SQL Saturday in the Netherlands

HJ — Thu, 16 Sep 2010 13:22:06 GMT

Aimed at a Dutch audience: The second SQL Saturday in The Netherlands is on it's way! Be there on October 23.

Read more information at www.sqlzaterdag.nl.

Delete Maintance Plan without SSIS installed

HJ — Wed, 15 Sep 2010 12:08:25 GMT

Today I ran into an interesting problem. I wanted to delete a Maintenance Plan on a server that had SQL Server reinstalled without SSIS (Integration Services). (I was not responsible for this reinstallation...).

I did not know that yet when I wanted to delete the Maintenance Plan. So I followed the menu (right-click the Maintenance Plan and select Modify) and I got this error:

The error message in plain text for the search engines:

Exception has been thrown by the target of an invocation. (mscorlib)

Additional information:
An Integration Services class cannot be found. Make sure that Integration Services is correctly installed on the computer that is running the application. Also, make sure that the 64-bit version of Integration Services is installed if you are running a 64-bit application.

Class not registered.

T-SQL

I then searched for and found the T-SQL code for deleting the Maintenance Plan manually:

/* Delete a Maintenance Plan manually.

Works in SQL Server 2005 and SQL Server 2008. */

--To get the Maintenance Plan IDs:
USE msdb
GO
SELECT * FROM sysmaintplan_plans
GO

USE msdb
GO

DECLARE @varID varchar(100)
--Paste the ID of the Maintenance Plan you want to delete below
SET @varID ='E13A5028-A35C-43BE-96C0-46B436CCDBB5'
DELETE FROM sysmaintplan_log WHERE plan_id = @varID
DELETE FROM sysmaintplan_subplans WHERE plan_id = @varID
DELETE FROM sysmaintplan_plans WHERE id = @varID
GO

References

I found the code in this thread: http://www.eggheadcafe.com/software/aspnet/33774157/cannot-delete-maintenance-plan-or-scheduled-job.aspx.

Show / hide text, notes, images in Reporting Services with a report parameter

HJ — Mon, 30 Aug 2010 13:10:50 GMT

Reports usually contain some kind of explanation, mostly at the end of the report. The bigger the report, or the more complex the report, the longer the explanation. And you may not want to print this extra text on every occasion, e.g. for saving the paper, the environment and your wallet.

A way to do this is by using a report parameter. This parameter typically defaults to 'do not show' and the user can simply change this to 'show'.

Define the parameter

Define the parameter as shown in the image.

Define the Hidden property of the text, notes, image

In the Hidden property set =Iif(Parameters!varExplanation.Value = 1, False, True).

Result

The result is that:

By default the text, notes, image are not shown.
The user can choose to show the text, notes, image.

Execute a command against all tables in a database

HJ — Tue, 06 Jul 2010 16:15:34 GMT

Have you ever wanted to execute a command againts all tables in your database? And did you struggle with cursors or WHILE BEGIN ... END loops?

So did I. Until I discovered the undocumented stored procedure sp_MSforeachtable and I love it.

I now use it for rebuilding all indexes on all tables. The syntax is

sp_MSforeachtable <command>

In the command you use a question mark (?) as a placeholder for the table name. So for rebuilding all indexes on all tables in a database you would use the following command:

sp_MSforeachtable 'ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR = 80)'

Is it really undocumented?

Well, you can find details with EXEC sp_helptext sp_MSforeachtable:

create proc sys.sp_MSforeachtable  
 @command1 nvarchar(2000), @replacechar nchar(1) = N'?', @command2 nvarchar(2000) = null,  
   @command3 nvarchar(2000) = null, @whereand nvarchar(2000) = null,  
 @precommand nvarchar(2000) = null, @postcommand nvarchar(2000) = null  
as  
 /* This proc returns one or more rows for each table (optionally, matching @where), with each table defaulting to its own result set */  
 /* @precommand and @postcommand may be used to force a single result set via a temp table. */  
  
 /* Preprocessor won't replace within quotes so have to use str(). */  
 declare @mscat nvarchar(12)  
 select @mscat = ltrim(str(convert(int, 0x0002)))  
  
 if (@precommand is not null)  
  exec(@precommand)  
  
 /* Create the select */  
   exec(N'declare hCForEachTable cursor global for select ''['' + REPLACE(schema_name(syso.schema_id), N'']'', N'']]'') + '']'' + ''.'' + ''['' + REPLACE(object_name(o.id), N'']'', N'']]'') + '']'' from dbo.sysobjects o join sys.all_objects syso on o.id =
 syso.object_id '  
         + N' where OBJECTPROPERTY(o.id, N''IsUserTable'') = 1 ' + N' and o.category & ' + @mscat + N' = 0 '  
         + @whereand)  
 declare @retval int  
 select @retval = @@error  
 if (@retval = 0)  
  exec @retval = sys.sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 0  
  
 if (@retval = 0 and @postcommand is not null)  
  exec(@postcommand)  
  
 return @retval

sp_MSforeachdb

A related undocumented stored procedure is sp_MSforeachdb. EXEC sp_helptext sp_MSforeachdb says:


/*  
 * The following table definition will be created by SQLDMO at start of each connection.  
 * We don't create it here temporarily because we need it in Exec() or upgrade won't work.  
 */  
  
create proc sys.sp_MSforeachdb  
 @command1 nvarchar(2000), @replacechar nchar(1) = N'?', @command2 nvarchar(2000) = null, @command3 nvarchar(2000) = null,  
 @precommand nvarchar(2000) = null, @postcommand nvarchar(2000) = null  
as  
    set deadlock_priority low  
      
 /* This proc returns one or more rows for each accessible db, with each db defaulting to its own result set */  
 /* @precommand and @postcommand may be used to force a single result set via a temp table. */  
  
 /* Preprocessor won't replace within quotes so have to use str(). */  
 declare @inaccessible nvarchar(12), @invalidlogin nvarchar(12), @dbinaccessible nvarchar(12)  
 select @inaccessible = ltrim(str(convert(int, 0x03e0), 11))  
 select @invalidlogin = ltrim(str(convert(int, 0x40000000), 11))  
 select @dbinaccessible = N'0x80000000'  /* SQLDMODbUserProf_InaccessibleDb; the negative number doesn't work in convert() */  
  
 if (@precommand is not null)  
  exec(@precommand)  
  
 declare @origdb nvarchar(128)  
 select @origdb = db_name()  
  
 /* If it's a single user db and there's an entry for it in sysprocesses who isn't us, we can't use it. */  
   /* Create the select */  
 exec(N'declare hCForEachDatabase cursor global for select name from master.dbo.sysdatabases d ' +  
   N' where (d.status & ' + @inaccessible + N' = 0)' +  
   N' and (DATABASEPROPERTY(d.name, ''issingleuser'') = 0 and (has_dbaccess(d.name) = 1))' )  
  
 declare @retval int  
 select @retval = @@error  
 if (@retval = 0)  
  exec @retval = sys.sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 1  
  
 if (@retval = 0 and @postcommand is not null)  
  exec(@postcommand)  
  
   declare @tempdb nvarchar(258)  
   SELECT @tempdb = REPLACE(@origdb, N']', N']]')  
   exec (N'use ' + N'[' + @tempdb + N']')  
  
 return @retval

See an elaborate explanation of the sp_MSforeachdb stored procedure at http://www.databasejournal.com/features/mssql/article.php/3441031/SQL-Server-Undocumented-Stored-Procedures-spMSforeachtable-and-spMSforeachdb.htm