Developer hearted / Relational minded

Bulk Backup and Restore at the tip of your finger

2011-09-08T17:52:25Z

(Code can be downloaded here ) The Backup part “Can you please give me a recent backup of all user databases on that computer please ?” That is the sentence that will delay your planned work for the afternoon and will keep you busy for numerous hours depending on the number of databases you have in your environment. Wouldn´t it be nice to have something like an automated process that you can trigger to create those backups for you ? Well, that is what I thought some years ago and so I created a backup...(read more)

IsGuid or not isGuid, that is the question…

2011-09-07T19:04:00Z

As I was asked about a functionality how to check a string for being of the type GUID and only finding either solution with massive string operations, or using TRY..CATCH trying to CAST a value and then returning the CAST or CONVERT result, I created a new and simple one, based on a simple pattern matching using the LIKE operator: CREATE FUNCTION dbo.FnIsGuid ( @StringToCheck NVARCHAR(36) ) RETURNS BIT AS BEGIN DECLARE @Result BIT = 0 SELECT @Result = 1 WHERE @StringToCheck LIKE...(read more)

Get the NULL percentage of values in a specific table dynamically

2010-08-28T22:42:00Z

Nothing really fancy, but a small script I was now asked twice for. So I thought I publish it for the public and for the reference for others. It simply calculates the percentage of NULL values for a specific table in all columns. The last request was originated from the post here . Be aware that this will scan the table entirely and might need some time for coming back. You will get each column with an integer value as the percentage. This script will work on any version of SQL Server (except...(read more)

ALM Visual Studio Database Projects Guidance is out in the wild !

2010-08-21T16:56:47Z

After many months of hard work, collaboration, short weekends and nights, the new ALM Rangers guidance document is out covering Visual Studio Database Projects. Details about the documents and the documents themselves can be found here: Visual Studio Database Guide on Codeplex You will find a General guidance Specific scenarios HOLs for each topic Solution and Project Management Section Source Code Control and Configuration Management Integrating External Changes with the Project...(read more)

Expand :r referenced files for SQLCMD into one file (SQLCmdScriptExpander)

2010-08-16T17:47:42Z

Again a common question which I wanted to be able to answer once and for all: How can we expand files from a .sql file referenced with the :r operator ? Ok, lets make a step back in the past and see how we used to reference files in .sql files and calling them via OSQL or ISQL. The answer is, we couldn't. We needed to use either one file with the whole statement OR call OSQL/ISQL for each files separately. Now we have SQLCMD.EXE, the new command line utility for issuing SQL Server statements against...(read more)

Script out jobs in SQL Server v2

2010-08-13T11:37:59Z

For those asking a couple of times if the JobScripter for SQL Server Agent jobs is also available in 2008… here it is ! The only difference is that the version v2 is now targeting to SMO Assemblies of SQL Server 2008 which is version 10.0. This sure means that you are able to pick up the information from older servers as well. Only SQL Server 7.0 is not supported by this version of SMO. If you want to script out jobs from 7.0 you will need to use the old JobScripter version from the blog post...(read more)

Database internal file versions– How to determine if the database was upgraded or created from scratch

2010-07-28T07:13:59Z

In some cases it it interesting to know whether a database was directly created on the current version of the running SQL Server instance or if the database was upgraded during an upgrade of the instance or by attaching a database from an older version to a newer version of SQL Server. The information is stored in the datafile headers. To reveal it you will have to use the DBCC command and redirect the information to the trace output (by default this will be send to the errorlog) by using...(read more)

Did your SQL Agent doze off and you don´t know which jobs to run afterwards ?

2010-07-23T13:11:03Z

In some cases you might face the problem that SQL Server Agent Service didn't come up or was stopped for reason. Jobs scheduled in the downtime will not run automatically when the Agent service comes up again, and that is a good thing. I often hear people saying, why isn’t that supposed to be the other way around? Shouldn’t the service revisit the schedules and see which ones he missed and run that ? The precise answer to that is – it depends. Imagine you have jobs doing Index maintenance...(read more)

Database internal file versions–Where is the Undo button for a database upgrade ?

2010-07-19T15:47:44Z

A common question is how to move back to the older version of the database once upgraded. The answer is, you can´t . By attaching the database to the new SQL Server version an upgrade is done internally which can be seen if you run the attach commands in e.g. the script window of SSMS. In the following sample, I am attaching a SQL Server 2005 database to a SQL Server 2008 instance. The result is that it is upgraded during the attachment process (which also happens while restoring a backup...(read more)

SSIS as a data source for SSRS–where is it gone for SQL Server 2008 R2

2010-07-11T10:14:00Z

Some time ago I did a blog entry for SQL Server Integration Services packages as a data source for SQL Server Reporting Services reports. You can find this article here. This feature relies on a data processing extension that is explained in detail here ( http://msdn.microsoft.com/en-us/library/ms345250.aspx ). Due to security reasons, this feature has been deprecated and removed in SQL Server 2008 R2 . If you want to create and consume a more complicated data extraction you should think of either...(read more)

Usage of @@Trancount in DML statements

2010-07-10T14:01:00Z

Some time ago I got an interesting questions why the following applies: -- drop table a create table a(m varchar ( max ), i int ) go declare @cnt int --Explicit transaction begin tran set @cnt=@@trancount insert into a select 'inside' , @@trancount insert into a select 'inside cnt' , @cnt commit tran set @cnt=@@trancount --Implicit transcation insert into a select 'outside' , @@trancount insert into a select 'outside cnt' , @cnt select * from a ...(read more)

Light weight SQL Server procedure auditing without using SQL Server auditing version 2

2010-07-10T12:29:00Z

(The project is attached to this blog post, if you have any questions, feel free to send me a mail. You find the bits for download here .) For all of you who haven´t been able to read my blog entry on logging yet, I suggest you to read the first version of the blog entry located here . It basically described the options for doing logging in your database and why I came up with this solution for you to share. To summarize the purpose of this logging solution, here are the basic steps: ...(read more)

The New! report in town

2010-01-25T06:19:38Z

Ever wondered what New! means if you see a report in SQL Server Reporting Services Manager ? Obviously it is a report which was created recently. But what does “recently” mean and do I have a way to influence what SQL Server identifies with “New!”. Well, sorry to say that, but it depends :-) The clear answer is: yes and no. There are two scenarios for this, the native mode and the SharePoint integrated one. For the Native mode you will see from the reflected sources that this setting is non-configurable...(read more)

Want to know what happened on your database by reading the transaction log ?

2009-11-18T09:23:39Z

(Download the dbo.fndblog2 function here ) Update and word of caution: Be aware that the mentioned function is undocumented and subject to change in any release of SQL Server. You should only use that for investigational purposes and in case you are investigating any sever data issues on your server. The function will parse the logs and might cause (depending on the size of the log) a severe impact on performance while running. In various situations you will not be able to either use third...(read more)

An “secret” SSIS XML Destination Provider you might not found yet

2009-11-06T13:16:14Z

(Sample code included at the end of the post) The initiator for this post was Dan Atkins who wanted to create a feed from relational data to consume it directly from a created gadget. Where can I find that in the toolbox ? First of all, you won’t a XML destination adapter as of the shipped components in SQL Server 2005 and 2008. There are for sure third party components which can directly convert data from the data pipeline to defined XML but sometimes it is much easier than that and you just...(read more)

SSIS as a data source needed ? Watch out for configuration traps

2009-10-26T20:27:57Z

Someone pinged me according the article I wrote about SSIS as a data source in Reporting Services. In general this is easy to implement and configure. One problem though occurs if you already had SQL Server 2005 on the machine first and updated to SQL Server 2008. You will receive the following error: The data processing extension used for this report is not available. It has either been uninstalled, or it is not configured correctly. According to the configuration file RSReportServcer.config...(read more)

SQLIOSIMParser 0.3.3.0 is available

2009-10-23T10:56:47Z

After skipping some internal version the 0.3.3.0 is available now , these are the new features: Version 0.3.1.0 <Internal Bugfixing> Version 0.3.3.0 -Moved settings to a separate settings page and save them for later use (Suggested by me) -Added additionally feedback options (Feedback by me) Feel free to provide feedback to either the new feedback functionality or the discussion list on the codeplex site: http://sqliosimparser.codeplex.com/Thread/List.aspx -Jens...(read more)

SQLIOSimParser 0.3.1.0 is available

2009-10-17T16:45:25Z

These are the new functionalities comparing the 0.2.3.1 version: -Added n-m mapping for different test iterations (Suggestion to Jimmy) -Included output to CSV (Suggestion to Jimmy) -Being able to specify a delimiter for the CSV output (Suggestion to Jens) -Added an additional text column for using as TestName (Suggestion by Jimmy) -Changed column headings to include the units in most attributes (Suggestion by Jimmy and checked by Franz) Feedback is warmly welcome, either through the codeplex...(read more)

SQL Server Reporting Services Logging & Caching project released on Codeplex

2009-10-12T11:36:52Z

The first version (0.3.0.0) of a small project I did was published to Codeplex ( http://rsparamlogcache.codeplex.com/ ). It enables you to log and cache user parameter values for further usage and statistics. Here is short extract from the project description: Project Description The solution tracks the selected parameters from a report execution and uses them later for the next execution to take them as default parameters. This enables users to log parameters and inspect the execution statistics...(read more)

Jump on the Express Trace if you can – SQL Server Express server side tracing

2009-10-03T19:25:00Z

Running SQL Server Traces on SQL Server Express SQL Server Express does not come with a SQL Server Profiler GUI. (Period) Though can still use SQL Server Profiler tools from other editions to connect to the SQL Server Express edition. (But make sure that you do have a valid license for that.) (I appreciate all the work done by many community members developing a SQL Server Profiler Trace tool for SQL Server Express which can be found here. But in some cases you cannot use any external tools due to...(read more)

New Cumulative Update for CU4 for SQL Server 2008 SP1

2009-10-03T19:08:18Z

Head up, the new CU is ready ! This one is CU (Cumulative Update) 4 for SQL Server 2008 SP1 (means that you will need to have SP1 installed in order to install the update) See the information of the release services team here: http://blogs.msdn.com/sqlreleaseservices/archive/2009/09/22/cumulative-update-4-for-sql-server-2008-sp1.aspx -Jens...(read more)

Project corruption after upgrade VSTSDB & Unit tests

2009-10-03T18:33:33Z

As it was hard to find the cause of this error and the answer, I wanted to share some searchable information on the internet for that. The following problem occurs while doing an upgrade of “older” VSDB project to the new GDR releases. As you have seen, the version of some assemblies within the GDR changed from 9.0.0.0 to 9.1.0.0. During a project upgrade you find yourself in one of the following errors: (For search sake the error reads “Cannot add a ConfigurationSection with the same...(read more)

Basta Herbst 2009 in Mainz

2009-09-30T23:49:07Z

Thank for all participants attending my sessions in Mainz. As promised, here are the slide decks and demos I used during my presentations. If you have any question to this are other SQL Server related topics, feel free to ping me. I am looking forward to hear from you ! Volltext voraus – Volltextindizierung in SQL Server Database lifecycle mit Visual Studio Team Database Edition -Jens...(read more)

SQLDays in Rosenheim Herbst 2009

2009-09-30T21:59:47Z

Thank for all participants attending my sessions in Mainz. As promised, here are the slide decks and demos I used during my presentations. If you have any question to this are other SQL Server related topics, feel free to ping me. I am looking forward to hear from you ! Volltext voraus – Volltextindizierung in SQL Server Implement now – performance later BTW, I didn’t forget the question I put in my backlog. If a table is partitioned and I have fulltext-indexed the table, will be switch off...(read more)

Need more spare time ? Use SQLIOSimParser to interpret your IO results !

2009-09-27T19:14:45Z

My colleague Marvelous Jimmy ( JimmyMay ) and myself did a small project to parse and interpret the results of the testing tool SQLIO. It produces ready-to-interpret reports and reusable results in Excel and will save you (as of Jimmy) much time doing your performance tracing and giving your more spare time to to fun stuff like… more Performance tracing sessions :-) Check out his blog post about the functionality and the walkthrough with a small sample. -Jens...(read more)