On this blog, you’ll find articles about TSQL, SSIS, Databases, Administration and SQL Server. Mostly.
Do you like awesome learning opportunities or resources? Me too! That’s why I share stuff on this blog, Twitter or Facebook whenever I come across any that is worth your time. Please subscribe via email or connect on social media so that we can keep in touch.
What topics would you like to read about? Let me know via comments, email or a tweet.
I use an SSIS naming convention that is based on assigning a prefix to each type of component. My first version of SSIS naming convention list was based on SSIS 2008 R2. Since then, Microsoft has introduced new connections, tasks, sources and destinations that are mostly related to Azure, Hadoop, ODATA and CDC.
With the launch of SQL Server 2016, I have updated my list to include all those new components and assigned prefixes to them. There are minor changes to some existing prefixes too e.g. RBLDIDXT is now REBLDIDXT and few others like this.
Why Should You Use a SSIS Naming Convention
A naming convention may not appear to be a big deal at the time of development because you have a GUI development environment that makes it very easy to identify the components. But during the support and maintenance phase, when trying to read the execution logs or error messages, bad naming choices make it difficult to quickly identify the source of errors. Having a consistent naming convention across the enterprise makes a lot of difference in supporting the packages.
Feel free to reuse the conventions in the list or use it as a template to define your own. I’d love to hear your thoughts and suggestions in the comments, Twitter or Facebook.
You can download the complete list at the TechNet Gallery link below –
Microsoft announced the general availability of SQL Server 2016 today. This version has many path-breaking updates for performance, security and business intelligence. On top of that, the Developer Edition of SQL Server 2016 and 2014 are now completely free! There is no restriction on features, capabilities or how long you can use them! Read on to find out how to download free SQL Server 2016 Developer Edition and other goodies.
In these Developer Editions, the databases do not have to be smaller than X GB, the feature Y will not be disabled, the time is not restricted to Z days (i.e. 180 days) and you don’t have to pay any $ Dollars. These Developer Editions have all the features of the Enterprise Edition, but should not be used for Production.
Although the title says that it is an introductory book, it is certainly not a beginner’s level text. In its nine chapters, it introduces the new features of SQL Server 2016. So it will be more useful if you have already worked with SQL Server for a while and are aware of features (or possibilities for enhancements) in the previous versions.
Another technically correct thing to do is to use the QUOTENAME function to wrap the object name in [ and ] brackets. This handles those cases where there are special characters in the name or the name is a reserved keyword.
set @dbname = quotename(db_name(db_id()))
set @tablename = quotename(object_name(object_id))
set @indexname = quotename(object_name(object_id))
The brackets will add two more characters to the value.
So for really long object names that are 128 characters in length, the value would be 128 + 2 = 130 characters.
The two extra characters will break the variable assignment or row insert statements with truncation error –
String or binary data would be truncated. [SQLSTATE 22001] (Error 8152).
In my shop, we do have some long index names and I have had to debug some scripts for this issue. And this is not the first time!
I suggest that you use 130 as the width for the variables or columns that will store an object name. SQL Server object names can be maximum 128 characters long so using 130 characters in scripts will handle the extra two characters.
One of our database restore attempt failed with an error message that mentioned cluster resources. At least the error message indicated that the issue was not related to backward compatibility but rather a physical resource or cluster settings.
The Error Message Window –
The Error Message –
TITLE: Microsoft SQL Server Management Studio
Restore failed for Server 'MYDEVSQLSERVER'. (Microsoft.SqlServer.SmoExtended)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.2500.0+((KJ_PCU_Main).110617-0038+)&
System.Data.SqlClient.SqlError: Cannot use file 'J:MSSQL10_50MSSQLDATAMyDatabaseName.mdf' for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used. Either the disk resource containing the file is not present in the cluster group or the cluster resource of the Sql Server does not have a dependency on it. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.2500.0+((KJ_PCU_Main).110617-0038+)&LinkId=20476
Both the links in the error message above pointed to a missing information message on Microsoft website –
There is no additional information about this issue in the Error and Event Log Messages or Knowledge Base databases at this time. You can use the links in the Support area to determine whether any additional information might be available elsewhere.
Thank you for searching on this message; your search helps us identify those areas for which we need to provide more information.
Cause and Resolution
We determined the cause rather quickly. The source system of the backup file had a drive letter layout that was different from the destination server. The restore process was trying to create the data files on a drive that didn’t exist on the destination! So the location of the files was changed in the restore dialog to a correct drive letter of the destination server. After that the restore progressed normally.
Posts on a WordPress blog can be shared on social media sites like Facebook, Twitter etc. You can do the reverse too and share Facebook page posts in a WordPress blog post which will include the Facebook comments, likes and shares. The Facebook page timeline has to be public.
So you first post something on your blog. Then you share it on Facebook. Then you again share the Facebook post back to your blog! Why would someone do this kind of cross posting?! The answer is, to promote your Facebook page with your blog readers and vice-a-versa. Even if you are not promoting a page (because it is not YOUR page), Facebook posts from others’ pages can still be useful to your blog readers or relevant to what you already write about.
Now, why Facebook? A Facebook user is most likely just browsing, not actively looking for solutions to any technical issue. But they sure love to share and read interesting stuff. So by posting to a Facebook page, there is a higher chance of someone liking or sharing it with their friends and as a result catch the attention of another casual browser. Oh yes, there are social-sharing buttons on a WordPress post too. But those buttons will be used by a reader who is already on your blog. Also, in some situations the sharing buttons may not even work due to restrictions like work network, device issues etc. The point here is to engage a casual reader on Facebook who is not actively looking for content on a search engine or a forum.
On the other hand, most of blog readers are looking actively for a solution to an issue. There is a high chance that they have been directed to the blog via a search engine result or a technical forum thread about a specific issue. (Check your stats, duh!) Very few are here for general reading. As a blogger, I would like to capture the interest of this “accidental” reader and hope that they return. One way for me to stay connected with them is to have them subscribed to or follow my blog through (at least one or preferably) various channels like email, RSS, WordPress, Twitter, Facebook etc. Some people may be reluctant to share their email address but may be willing to Like the Facebook page. Sending a new reader to your Facebook page and getting them to Like your Facebook page is equivalent to getting a new subscriber. Of course the restrictions mentioned above will come into play here too but the opportunity can still be used to make the reader aware of a Facebook page. With its huge active user count, Facebook is a good medium for outreach and engagement. Major websites report that bulk of their views are driven by social media sites. Consider the Facebook cross-post as a banner ad for yourself!
To promote your blog, you can either use your personal Facebook profile page or create a new page for the blog. I would suggest the latter so that you don’t spam your non-tech friends with technical rants.
I plan to experiment with various blog promotion strategies, especially in the technical niche, and write in more detail about them. I have started a Facebook page for this blog and will share my adventures periodically. If you are interested in following my learning path, you can subscribe to this blog or like my Facebook page. (Notice what I did right there?!)
One of my SQL Server databases was returning an error 102 while creating a full-text stoplist. We were trying to create a stoplist based on the system stoplist and later also tried to create a blank stoplist. The error happened both via SSMS, and equivalent TSQL commands.
The Error, 102
The following TSQL gave the error –
CREATE FULLTEXT STOPLIST [DemoStopList]
CREATE FULLTEXT STOPLIST [DemoStopList]
FROM SYSTEM STOPLIST
The error dialog box –
The text in the error message –
TITLE: Microsoft SQL Server Management Studio
Cannot execute changes.
Create failed for FullTextStopList 'Demo'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.5058.0+((SQL11_PCU_Main).140514-1820+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+FullTextStopList&LinkId=20476
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Incorrect syntax near 'STOPLIST'. (Microsoft SQL Server, Error: 102)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=102&LinkId=20476
I looked at the MSDN page related to the TSQL command to check if I was using the right syntax.
My syntax was correct but there was something else on the page that looked relevant. Right at the top of the documentation page is the following message –
CREATE FULLTEXT STOPLIST, ALTER FULLTEXT STOPLIST, and DROP FULLTEXT STOPLIST are supported only under compatibility level 100. Under compatibility levels 80 and 90, these statements are not supported. However, under all compatibility levels the system stoplist is automatically associated with new full-text indexes.
To verify if the compatibility level of my database could indeed be an issue, I checked the properties of the database by –
There you have it! My database was originally on a SQL Server 2005 installation so its compatibility level was 90, and that was the reason the CREATE/ALTER/DROP STOPLIST commands were unavailable. The current server that I was working on was SQL Server 2008 R2, which could be checked by –
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1
So the resolution to the error lies in changing the compatibility level. As per the documentation, the highest compatibility level I could go on a SQL Server 2008 R2 installation was 100.
View or Change the Compatibility Level of a Database
ALTER DATABASE Compatibility Level (Transact-SQL)
Changing the Compatibility Level
I checked that no other users were connected to the database and then issued this command to change the compatibility level.
ALTER DATABASE [DEMO_database]
SET COMPATIBILITY_LEVEL = 100;
It ran successfully and I could verify in the sys.databases catalog view that the compatibility level has changed to 100.
Now I was able to create a Stop List, Full-text Catalog and a Full-text Index on my table, and was able to run queries using the CONTAINS and CONTAINSTABLE keywords.
Fixed? Not so fast!
Interestingly, even though I could use the Full-text features now, the is_fulltext_enabled property still showed up as 0 (i.e. Disabled).
That was fixed by running the following –
@action = 'enable'
Some things are meant to be learnt the hard way. And that is how I learnt about today’s gotcha.
Overview of [sysdiagrams]
I have been working on an ERD (Entity Relationship Diagram) recently and used the Database Diagram feature in SSMS for this purpose. When you try to create a diagram for the first time in a database, a message box asks you if you would like to create diagramming objects.
On clicking Yes, a system table by the name of [sysdiagrams] is created in the same database that you are creating the diagram in. The diagrams are stored in this table. SSMS shows the diagram in the Database Diagrams node of the Object Explorer tree view.
I was creating my diagram in a test environment. At some point I had to refresh all data from the production environment. The easiest way for me to do a full refresh is to use the Import and Export Wizard. The wizard can be launched either via SSMS context menu or in the Business Intelligence Developer Studio as an SSIS project. As usual in the case of quick data refreshes, I selected all tables using the top-left Source checkbox in the wizard, and chose the options to delete and reinsert all rows with identity values.
When the wizard ran successfully, my database diagram at the destination test system was missing!
Upon some research and trials I found that that if the diagramming capabilities in the source and destination servers are enabled, the wizard includes the [sysdiagrams] table automatically in the list of tables to refresh. As you can see, there are no other system tables in the wizard except the [sysdiagrams] table so it is easy to miss it in a long list.
So in my case, all data in the destination [sysdiagrams] table was deleted. There were no diagrams at the source so nothing was imported for this table. This outcome would have been the same with the drop and recreate option too because the destination table would have been recreated.
One needs to be careful while using the Import and Export Wizard. Uncheck this table in the selection list to preserve the diagrams at destination.
I recently wrote about How to Use Temp Table in SSIS. One of the requirements to successfully reuse a temporary table across multiple tasks in SSIS is to set the RetainSameConnection property of the OLEDB Connection to TRUE. In this post, I will discuss the property and also use a Profiler Trace to find out its behavior.
The RetainSameConnection Property
RetainSameConnection is a property of an OLEDB Connection Manager. The default value of this property is FALSE. This default value makes SSIS execution engine open a new OLEDB connection for each task that uses the connection and closes that connection when the task is complete. I believe the idea behind this is to not block a connection to a server unnecessarily and release it until it is needed again. And it makes sense too because some packages can run for an extended duration and may not need to be connected to an OLEDB server all the time. For example, an OLEDB in not required to be open while parsing text files, sending emails, ETL operations not involving the OLEDB server in question etc. Releasing connections unless really required can be certainly helpful on busy servers because SQL Server needs some memory for each open connection.
On the other hand, some scenarios require a persistent connection e.g. temporary table reuse across multiple tasks. We can set the property value to TRUE and then it will open just one OLEDB connection with a server and keep it alive until the end of the package execution. The property can be set via the Properties window for the OLEDB Connection Manager.
The Temporary Table Scenario
Local temporary tables (with a # in front of their name) in SQL Server are scoped to a session. SQL Server drops them when the session is closed. This means, local temporary tables created in one session are not available in another session. In SSIS, with the RetainSameConnection set to FALSE (the default), a new session is opened for each task. Therefore, temporary tables created by a task are not available to another task.
I have a demo package with two Execute SQL Tasks and one OLEDB Connection Manager. The Execute SQL Tasks have a simple SELECT statement and they both use the same connection manager.
I have a Profiler Trace to monitor the number of connections created by the SSIS package.
The first execution of the package is with the RetainSameConnection set to the default value of FALSE. The trace captures two pairs of login/logout events, one for each task. The second execution is with the property value set to TRUE. This time, the trace captures only one pair of login/logout events.
In most cases, the default value of RetainSameConnection=FALSE will be suitable. A developer should make a decision to enable it when the package tasks really need a persistent connection. In addition to the temporary table reuse, a TRUE value for this property can also be useful in managing transactions and reducing the number of recurring connection requests to a server.
Event logging in SSIS gives a lot of valuable information about the run-time behavior and execution status of the SSIS package. Having a common minimum number of events is good for consistency in reports and general analysis. Lets say your team wants to ensure that all packages must log at least OnError, OnWarning, OnPreExecute and OnPostExecute events. If the package has a DataFlowTask then the BufferSizeTuning should also be logged. The developer can include more events to log as required but these mentioned previously are the minimum that must be included.
You can create pre-deployment checklists or documentation to ensure this minimum logging. Probably you already have that. As the number of developers and/or packages increase, it becomes difficult to ensure consistency in anything, not just for event logging. Therefore documentation, checklists and training are helpful to an extent only. Your requirement could be more complex than my five-event example above and thus more prone to to oversight.
The easiest way to ensure a common logging implementation would be a logging template that has all the minimum event pre-selected. The developer should just need to apply that to the package.
Event Logging in SSIS with a Template
I assume that you are already familiar with the concepts of event logging in SSIS so this post is not going to be a beginners level introduction to event logging. I will rather discuss options to have a minimum standard event logging across SSIS packages and teams with minimal effort. I’ll also mention some traps to avoid.
I am using a demo SSIS package with two Data Flow Tasks and an Execute SQL Task. I have enabled event logging in SSIS for the first few events at the package level for the sake of demonstration. The logging configuration options for the package node (which is the top node) are shown in the first image.
The logging options at the child container node Data Flow Task 1 are shown in the second image. The configuration for other Data Flow and the Execute SQL Task look the same.
The check marks for the tasks are grayed out which means they are inheriting the logging options from their parent, i.e. the package. To disable logging for a task, remove its check mark in the left tree view window.
TIP: Logging can also be disabled by going to the Control Flow canvas and changing the LoggingMode property of the task to Disabled.
Now look at the bottom of the images again. Notice the Load… and Save… buttons? They do exactly what they say. You can set your logging options and save them as an XML template. Later, this XML template can be loaded into other packages to enable the same logging options.
The XML template file has nodes for each event. For example, the logging options for OnError event are saved like this –
Notice that the XML just mentions the event name, not the name of any task. This means that when the template file is loaded, this logging option will be set for any task where the event is applicable. More on this later.
The OnError event is a generic event applicable to all tasks. Lets talk about events that are specific to tasks. For example, the BufferSizeTuning event is applicable just to the Data Flow Tasks, not Execute SQL Tasks.
When I proceed to set logging for BufferSizeTuning event, I have to set it individually in the Data Flow Task tree node. Notice the message at the bottom of the second image that says –
To enable unique logging options for this container, enable logging for it in the tree view.
This message is important in the context of saving and loading a template file too. When I save a template file, the logging options of just that tree view node are saved. For example, the BufferSizeTuning event will be saved in the template only if I am at the Data Flow task in the tree view. It will not be saved if I am at the Package or the Execute SQL task in the tree view.
The reverse is also true. When I load a template, its logging options are applied to just that node which I select in the tree view. For example, if I load a template at the Data Flow Task 1, the options will not be applied to the Data Flow Task 2 or the Execute SQL Task. If the template has an event that is not applicable to the task then that event’s settings will be ignored. For example, the BufferSizeTuning event logging option is meant for Data Flow Tasks so it will be ignored for the Execute SQL Task. The fact that non-relevant options are ignored can be helpful for us to consolidate all logging options in a single template file.
A package level Save and Load of a logging template is straight forward. But if you need to have logging for events that are specific to a task type, then consider creating a logging template for each type of task. Also, if your logging configuration requires anything else than the package level settings, remember to load the template for each task in the tree view.
Number of Template Files
Pros and Cons
Individual File per Task
Create one template file for each type of task. The file will have events applicable to that task.
Easier to know what type of tasks have a template and which ones do not.Cons –
More files to manage.
Single File for All Tasks
Create a template file for each task. Then copy all event options in a single XML file.
One file is easier to manage.Cons –
Not obvious which tasks are include. Need to put in comments in the XML file.