Can you guess it's a slow time for me at work? This is my third post within one week! During downtime I like to read articles to further my knowledge of SQL Server. That's when I catch up on newsletters that collected in my inbox or prepare for my upcoming certification test.
Today, I ran across Kalen Delaney's article about a curious undocumented feature of SQL Server. If you put an integer after the batch separator "go" (e.g. go 10) the sql statement(s) before the "go" will be executed that many times (in my example 10 times.)
She uses this interesting little feature to populate test tables with test data but it can be used as a quick and dirty stress/concurrency testing technique as well.
Thank you Kalen for documenting the undocumented!
Thursday, August 11, 2011
Wednesday, August 10, 2011
SQL Server Data Collector Woes
This post is to list the idiosyncrasies I came across while setting up Data Collector.
Cache directory
Issue: As the name implies this is the location where the cache collects the data. When clicking on the ellipsis it opens up the drive and folder structure of the server where the management data warehouse resides. Guess what? If you specify a folder on that server you will get the following error: The system cannot find the file specified.
Solution: The folder needs to exist on the server against which the data collection is executed.
Tip: This folder seems to be used even if you set the data collection to be non-cached.
Permissions
Issue: Login failed for user error.
Solution: Make sure the user set up to run the SQL Agent Service has access to the database server where the management data warehouse resides.
QueryActivityUpload
Issue: The error "Arithmetic overflow error converting expression to data type int" during Query Statistics processing.
Solution: As per this Connect page explains Cumulative Update #5 for SQL Server 2008 SP1 will fix this problem. However, if you cannot wait to get and apply that update there is a manual workaround explained on that same page. The workaround involves modifying the QueryActivityUpload SSIS package. I went by that route and the modifying the package does work. I just want to add that There are 2 places where the fix needs to be applied: OLE DB Source in the DFT - Create Interesting Queries Last Snapshot and the ODS - Get current snapshot of dm_exec_query_stats in the DFT - Create Interesting Queries Upload Bath dataflow tasks.
Cache directory
Issue: As the name implies this is the location where the cache collects the data. When clicking on the ellipsis it opens up the drive and folder structure of the server where the management data warehouse resides. Guess what? If you specify a folder on that server you will get the following error: The system cannot find the file specified.
Solution: The folder needs to exist on the server against which the data collection is executed.
Tip: This folder seems to be used even if you set the data collection to be non-cached.
Permissions
Issue: Login failed for user error.
Solution: Make sure the user set up to run the SQL Agent Service has access to the database server where the management data warehouse resides.
QueryActivityUpload
Issue: The error "Arithmetic overflow error converting expression to data type int" during Query Statistics processing.
Solution: As per this Connect page explains Cumulative Update #5 for SQL Server 2008 SP1 will fix this problem. However, if you cannot wait to get and apply that update there is a manual workaround explained on that same page. The workaround involves modifying the QueryActivityUpload SSIS package. I went by that route and the modifying the package does work. I just want to add that There are 2 places where the fix needs to be applied: OLE DB Source in the DFT - Create Interesting Queries Last Snapshot and the ODS - Get current snapshot of dm_exec_query_stats in the DFT - Create Interesting Queries Upload Bath dataflow tasks.
SQL Server Deprecated Features
I ran across this very useful performance counter today: SQLServer:Deprecated Features. This shows you how often SQL Server encountered listed deprecated features since the latest start.
In my SQL 2008 environment I see such item as "String literals as column aliases" or "sp_change_users_login." Some of these features are self-explanatory. For example, I can deduct from "Table hint without WITH" what I need to use the keyword "WITH" for table hints to correct this for a future version of SQL Server when skipping WITH will not be allowed.
For some others it's not so obvious or I need to find out another option. In those cases I can look at Microsoft's page that describes all the deprecated features in detail giving alternatives: http://technet.microsoft.com/en-us/library/bb510662.aspx. On this article, for instance, I find out that instead of DBCC DBREINDEX I need to use the REBUILD option of ALTER INDEX.
If you have a lot of legacy code in your application (and who doesn't?) be sure to take a look at this counter by running the following SQL statement: SELECT * FROM sys.dm_os_performance_counters where object_name = 'SQLServer:Deprecated Features'
In my SQL 2008 environment I see such item as "String literals as column aliases" or "sp_change_users_login." Some of these features are self-explanatory. For example, I can deduct from "Table hint without WITH" what I need to use the keyword "WITH" for table hints to correct this for a future version of SQL Server when skipping WITH will not be allowed.
For some others it's not so obvious or I need to find out another option. In those cases I can look at Microsoft's page that describes all the deprecated features in detail giving alternatives: http://technet.microsoft.com/en-us/library/bb510662.aspx. On this article, for instance, I find out that instead of DBCC DBREINDEX I need to use the REBUILD option of ALTER INDEX.
If you have a lot of legacy code in your application (and who doesn't?) be sure to take a look at this counter by running the following SQL statement: SELECT * FROM sys.dm_os_performance_counters where object_name = 'SQLServer:Deprecated Features'
Monday, July 25, 2011
Unexpected error from external database driver
I'm working on an SSIS package today. Nothing really special. Just looping through some Excel files and processing the content. I was unable to set up the Excel Source component because whenever I wanted to select the sheet name (with or without using a variable) I received the following error: Unexpected error from external database driver (22)
Nice! What is causing this? It turned out that the name of the sheet was 31 characters long. I removed the last character, saved the spread sheet and voila! the name of the sheet is in the drop down box. So it seems that while the sheet name limit in Excel is 31 characters when dealing with it in SSIS the name of the sheet cannot be longer than 30 characters because the 31 characters need to be able to include the $ sign which indicates sheet name and not a range name.
Update: this may not have been the problem. I have "fixed" the error by saving the Excel file without changing the name of the sheet. So I am back to drawing board with this error.
Now if I could just figure out the "Value does not fall within the expected range" error that I get when I am trying to view the columns on this same component.
Update: I resolved this last error by deleting my Excel Source component and recreating it.
Nice! What is causing this? It turned out that the name of the sheet was 31 characters long. I removed the last character, saved the spread sheet and voila! the name of the sheet is in the drop down box. So it seems that while the sheet name limit in Excel is 31 characters when dealing with it in SSIS the name of the sheet cannot be longer than 30 characters because the 31 characters need to be able to include the $ sign which indicates sheet name and not a range name.
Update: this may not have been the problem. I have "fixed" the error by saving the Excel file without changing the name of the sheet. So I am back to drawing board with this error.
Now if I could just figure out the "Value does not fall within the expected range" error that I get when I am trying to view the columns on this same component.
Update: I resolved this last error by deleting my Excel Source component and recreating it.
Wednesday, June 8, 2011
Setting up SQL Server to email you when a SQL job fails
In this post I will list all the steps needed to be completed in order to have SQL Server send email notifications when a job fails.
Step 1.
Enable Database Mail unless it has already been done. Make sure you have a profile set up. I will not go into details on this step as it's pretty straight-forward. Make sure your test email will go through though. In our case our STMP server only accepts email messages from specific servers so if I set up database mail on a new SQL Server then I will have to notify our server admin to add that server to the white list.
Step 2.
Create an Operator. This item is found under SQL Server Agent in SSMS.
This step is pretty straight-forward as well. You need to do is name the operator and specify an email address for it. At my current job we do not have a large group of database admins so I only have one operator with a distribution group email address that goes to exactly 2 people.
Step 3.
Enable mail profile for SQL Server Agent. This can be done in the Properties of SQL Server Agent, Alert System section. Check the Enable mail profile item, select Database Mail as the Mail system and select the Mail profile that was created in Step 1. In this section you may enable the a fail-safe operator as well by selecting the Operator you created in Step 2.
Step 4.
Restart SQL Server Agent! This bites me all the time. You do everything but this step and you wonder why it does not work. Because you forgot to restart the Agent!
Step 5.
Edit the properties of the SQL Job for which you want to set up email notifications. In the Notifications section check E-mail and specify the Operator you set up in Step 2. You obviously want the notification to be sent out when the job fails. However, in some unique scenarios you also want notifications when the succeeds or simply just completes. I usually also check the Write to the Windows Application event log as well. Emails can get deleted. You need to make of an effort to get rid of an event log. Save the SQL Job and you are done!
Step 1.
Enable Database Mail unless it has already been done. Make sure you have a profile set up. I will not go into details on this step as it's pretty straight-forward. Make sure your test email will go through though. In our case our STMP server only accepts email messages from specific servers so if I set up database mail on a new SQL Server then I will have to notify our server admin to add that server to the white list.
Step 2.
Create an Operator. This item is found under SQL Server Agent in SSMS.
This step is pretty straight-forward as well. You need to do is name the operator and specify an email address for it. At my current job we do not have a large group of database admins so I only have one operator with a distribution group email address that goes to exactly 2 people.
Step 3.
Enable mail profile for SQL Server Agent. This can be done in the Properties of SQL Server Agent, Alert System section. Check the Enable mail profile item, select Database Mail as the Mail system and select the Mail profile that was created in Step 1. In this section you may enable the a fail-safe operator as well by selecting the Operator you created in Step 2.
Step 4.
Restart SQL Server Agent! This bites me all the time. You do everything but this step and you wonder why it does not work. Because you forgot to restart the Agent!
Step 5.
Edit the properties of the SQL Job for which you want to set up email notifications. In the Notifications section check E-mail and specify the Operator you set up in Step 2. You obviously want the notification to be sent out when the job fails. However, in some unique scenarios you also want notifications when the succeeds or simply just completes. I usually also check the Write to the Windows Application event log as well. Emails can get deleted. You need to make of an effort to get rid of an event log. Save the SQL Job and you are done!
Friday, February 4, 2011
Dundas and SSRS 2008 R2
I am in the process of upgrading our SQL Server Reporting Services 2008 to R2. One of the issues that I ran into is that in about half of our reports we use Dundas controls and the Dundas controls does not show up on the reports in SSRS 2008 R2. In the event viewer the following error is logged: Report Server (MSSQLSERVER) cannot load the DundasChartControl extension.
I am doing a side by side upgrade. That means that I have a brand new instance of SQL Server 2008 R2. Unfortunately I found poor documentation on how the upgrade would affect the Dundas controls. Microsoft does have one page dedicated to the upgrade process and how it affects custom controls but it's not very useful: http://technet.microsoft.com/en-us/library/ms143674.aspx.
With some trial and error I finally found the solution.
Copy over the Dundas Dlls (DundasRSChart.dll,etc) to the ReportServer's bin folder. E.g.C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer\bin
Make sure you have this item in the rsreportserver.config file within the Extension tag:
<ReportItems>
<ReportItem Name="DundasChartControl" Type="Dundas.ReportingServices.DundasChart,DundasRSChart" />
<ReportItem Name="DundasMapControl" Type="Dundas.ReportingServices.Maps.DundasMap,DundasRSMap" />
<ReportItem Name="DundasGaugeControl" Type="Dundas.ReportingServices.DundasGauge,DundasRSGauge" />
<ReportItem Name="DundasCalendarControl" Type="Dundas.ReportingServices.Calendar.DundasCalendar,DundasRSCalendar" /> </ReportItems>
In the ReportServer's rssrvpolicy.config, make sure this exists inside this tag:
<CodeGroup class="FirstMatchCodeGroup" version="1" PermissionSetName="Execution"
Description="This code group grants MyComputer code Execution permission. ">
<CodeGroup class="UnionCodeGroup" version="1" PermissionSetName="FullTrust" Name="DundasChartControl" Description="This code group grants FullTrust to DundasChartControl assemblies.">
<IMembershipCondition class="StrongNameMembershipCondition" version="1" PublicKeyBlob="0024000004800000940000000602000000240000525341310004000001000100d16006505277d0860ce7d429331480cb3b9711481cd51213259cf3b106e30156f5ef48262e0154e862fa05374e6c7cef5c4daa95b2272a73e503f0e5077c6b71a24176e67eadbff8e8765742f022e317194571de05d0ac9a4fca62fd355854ebe5a38969a831030018edad7d7405dd23d0710c9faf95acf16a4a14003bada5be" />
</CodeGroup>
After this restart the reporting services service and the Dundas controls should show up on the reports.
I am doing a side by side upgrade. That means that I have a brand new instance of SQL Server 2008 R2. Unfortunately I found poor documentation on how the upgrade would affect the Dundas controls. Microsoft does have one page dedicated to the upgrade process and how it affects custom controls but it's not very useful: http://technet.microsoft.com/en-us/library/ms143674.aspx.
With some trial and error I finally found the solution.
Copy over the Dundas Dlls (DundasRSChart.dll,etc) to the ReportServer's bin folder. E.g.C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer\bin
Make sure you have this item in the rsreportserver.config file within the Extension tag:
<ReportItems>
<ReportItem Name="DundasChartControl" Type="Dundas.ReportingServices.DundasChart,DundasRSChart" />
<ReportItem Name="DundasMapControl" Type="Dundas.ReportingServices.Maps.DundasMap,DundasRSMap" />
<ReportItem Name="DundasGaugeControl" Type="Dundas.ReportingServices.DundasGauge,DundasRSGauge" />
<ReportItem Name="DundasCalendarControl" Type="Dundas.ReportingServices.Calendar.DundasCalendar,DundasRSCalendar" /> </ReportItems>
In the ReportServer's rssrvpolicy.config, make sure this exists inside this tag:
<CodeGroup class="FirstMatchCodeGroup" version="1" PermissionSetName="Execution"
Description="This code group grants MyComputer code Execution permission. ">
<CodeGroup class="UnionCodeGroup" version="1" PermissionSetName="FullTrust" Name="DundasChartControl" Description="This code group grants FullTrust to DundasChartControl assemblies.">
<IMembershipCondition class="StrongNameMembershipCondition" version="1" PublicKeyBlob="0024000004800000940000000602000000240000525341310004000001000100d16006505277d0860ce7d429331480cb3b9711481cd51213259cf3b106e30156f5ef48262e0154e862fa05374e6c7cef5c4daa95b2272a73e503f0e5077c6b71a24176e67eadbff8e8765742f022e317194571de05d0ac9a4fca62fd355854ebe5a38969a831030018edad7d7405dd23d0710c9faf95acf16a4a14003bada5be" />
</CodeGroup>
After this restart the reporting services service and the Dundas controls should show up on the reports.
Friday, January 7, 2011
SSRS 2008 R2 Bug
One of the major reasons I want to upgrade our Reporting Services to 2008 R2 is because R2 comes with PowerPivot and SSRS report can be easily imported into PowerPivot. One way to bring report data into PowerPivot is using the Export to Data Feed button on a report.
Unfortunately the upgrade process from plain vanilla SQL 2008 to R2 have a bug that somehow excluded that rendering format and that option is missing from the reports.
After a little searching I found the solution. The following line needs to be added in the rsreportserver.config file in the Render section:
<extension name="ATOM" type="Microsoft.ReportingServices.Rendering.DataRenderer.AtomDataReport,Microsoft.ReportingServices.DataRendering" visible="false"></extension>
Unfortunately the upgrade process from plain vanilla SQL 2008 to R2 have a bug that somehow excluded that rendering format and that option is missing from the reports.
After a little searching I found the solution. The following line needs to be added in the rsreportserver.config file in the Render section:
<extension name="ATOM" type="Microsoft.ReportingServices.Rendering.DataRenderer.AtomDataReport,Microsoft.ReportingServices.DataRendering" visible="false"></extension>
Subscribe to:
Posts (Atom)