tag:blogger.com,1999:blog-9952681643823046362024-02-19T00:11:46.313-08:00Real Life Experiences of a DBA in North TexasA place to post real life experiences when working with developers that have little to no SQL Experience and methods we are using to be more effecient.
Issues/Resolutions for SQL2000 to SQL20008 migration.ntx.dbahttp://www.blogger.com/profile/12800616241931590645noreply@blogger.comBlogger14125tag:blogger.com,1999:blog-995268164382304636.post-63875125965052174092013-08-01T10:59:00.000-07:002013-08-01T10:59:29.485-07:00Restore Master to Different Instance<div class="MsoNormal">
<span style="background-color: white; color: #222222; font-family: Arial, sans-serif; font-size: 10pt; line-height: 115%;">While setting up a warm
standby instance for one our job servers, I encountered an issue with
decryption of credential passwords. I
have included the error text from the popup message box below.</span></div>
<div class="MsoNormal">
<span style="background: white; color: #222222; font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 115%;"><br /></span></div>
<div class="MsoNormal" style="text-indent: .5in;">
<b><span style="background: white; color: #222222; font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 115%;">An error occurred during decryption. (Microsoft
SQL Server, Error: 15466)<o:p></o:p></span></b></div>
<div class="MsoNormal" style="text-indent: .5in;">
<b><span style="background: white; color: #222222; font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 115%;"><br /></span></b></div>
<div class="MsoNormal">
<span style="background: white; color: #222222; font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 115%;">This error occurred either
during creation of a new credential or modification of an existing credential.<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="background: white; color: #222222; font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 115%;">In our environment, we
have elected to run all user jobs from a separate server, the job server
referenced above. A second instance has
been brought online and we are configuring them for warm standby for each other. Each instance will have the same
configuration as the other; i.e. jobs, proxies, packages etc. However, the jobs will only be enabled on the
primary node for that job.<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="background: white; color: #222222; font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 115%;"><br /></span></div>
<div class="MsoNormal">
<span style="background: white; color: #222222; font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 115%;">In an attempt to get the
two instances in sync, the master and msdb databases from the original node were
restored to the new node. Restoring of
the databases went well and it wasn’t until testing of the jobs to verify the
functionality that the issue was uncovered.<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="background: white; color: #222222; font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 115%;">The job failed and logged
the error: <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="background: white; color: #222222; font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 115%;"><br /></span></div>
<div class="MsoNormal" style="margin-left: .5in;">
<b><span style="background: white; color: #222222; font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 115%;">Error authenticating proxy domain\userid,
system error: Logon failure: unknown <br />
user name or bad password.) </span></b><span style="background: white; color: #222222; font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 115%;"> <o:p></o:p></span></div>
<div class="MsoNormal" style="margin-left: .5in;">
<span style="background: white; color: #222222; font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 115%;"><br /></span></div>
<div class="MsoNormal">
<span style="background: white; color: #222222; font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 115%;">Having seen this error in
the past when researching other job failures, I assumed that for some reason
the password had been corrupted during the restore of the master database.<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="background: white; color: #222222; font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 115%;"><br /></span></div>
<div class="MsoNormal">
<span style="background: white; color: #222222; font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 115%;">After opening the
credential in question and rekeying the password, the error above was
displayed. I tried again with the same
results. I then decided just to delete the credential and re-add with the
correct password, failure again. Attempting
to add a new credential that had not existed on the server before ended with
the same error.<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="background: white; color: #222222; font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 115%;">After the repeated failures,
I opened my browser to see what others had done to resolve this issue. To my surprise, I couldn’t find another case
of this happening. I did find a couple
of mentions of this perhaps pointing to a corrupt Server Master Key for the new
instance.<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="background: white; color: #222222; font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 115%;"><br /></span></div>
<div class="MsoNormal">
<span style="background: white; color: #222222; font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 115%;">Since this instance had
been restored from another instance, I tried to import the Service Master Key
from the original instance. This
attempt also failed. When reviewing the
articles above, the mention of the SQL Service Account got me to thinking. What would happen if I changed the service
account on my new instance to match the original instance?<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="background: white; color: #222222; font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 115%;"><br /></span></div>
<div class="MsoNormal">
<span style="background: white; color: #222222; font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 115%;">Using SQL Configuration
Manager, I made that change and restarted the instance. Going back to SSMS I was able to create the
proxy with the correct password, and I was able to run the job to success.<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="background: white; color: #222222; font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 115%;">However, leaving the
server in this configuration violated our security policy. I hesitantly changed the service account back
to the correct domain id, restarted the instance and then executed the job. Success was once again reported by the job.<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="background: white; color: #222222; font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 115%;"><br /></span></div>
<div class="MsoNormal">
<span style="background: white; color: #222222; font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 115%;">Below are the steps that
were taken to resolve the issue:<o:p></o:p></span></div>
<div class="MsoNormal">
<div class="MsoNoSpacing">
</div>
<ul>
<li><span style="background-color: white;">Restore master database</span></li>
<li><span style="background-color: white;">Change SQLService Account
to original account from source server</span></li>
<li><span style="background-color: white;">Restart SQL Services</span></li>
<li><span style="background-color: white;">Change SQLService Account
to correct id for this instance</span></li>
<li><span style="background-color: white;">Restart SQLServices</span></li>
</ul>
<br />
</div>
<div class="MsoListParagraphCxSpFirst" style="mso-list: l0 level1 lfo1; text-indent: -.25in;">
<span style="background-color: white; color: #222222; font-family: Arial, sans-serif; font-size: 10pt; line-height: 115%;">I hope that by documenting
the issue, troubleshooting steps taken, and the successful resolution, it may save someone else some time and headaches.</span></div>
ntx.dbahttp://www.blogger.com/profile/12800616241931590645noreply@blogger.com0tag:blogger.com,1999:blog-995268164382304636.post-50214807796754894092013-07-31T07:10:00.000-07:002013-07-31T07:10:00.989-07:00SSIS ForEach continuation after Error<br />
<h1>
ForEach in SQLServer Integration Services<o:p></o:p></h1>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Integration Services has built in constructs that provide
the ability to loop through a result set.
This functionality can be used to loop through files in a directory,
items returned from a query, or a list generated with another method.<o:p></o:p></div>
<div class="MsoNormal">
One issue with the ForEach container, when using the default
configuration, is that if one loop iteration fails the loop fails and
processing stops. Below is a technique
that can be used to log the error and proceed to the next value in the list.<o:p></o:p></div>
<div class="MsoNormal">
The package shown uses a ‘ForEach File Enumerator’ to loop
through a directory, in this example c:\temp.
Three files exist in the directory that match the criteria. LoopDemo.txt, LoopReadOnly.txt, and LoopSequential.txt. LoopReadOnly.txt has the read only attribute
set to true.<o:p></o:p></div>
<div class="MsoNormal">
With the default options set for the ForEach loop construct
the first file is deleted, LoopDemo.txt. When deletion on the second file is
attempted, LoopReadOnly.txt, an error is produced and the loop fails after
displaying the error.<o:p></o:p></div>
<div class="MsoNormal">
<!--[if gte vml 1]><v:shapetype id="_x0000_t75" coordsize="21600,21600"
o:spt="75" o:preferrelative="t" path="m@4@5l@4@11@9@11@9@5xe" filled="f"
stroked="f">
<v:stroke joinstyle="miter"/>
<v:formulas>
<v:f eqn="if lineDrawn pixelLineWidth 0"/>
<v:f eqn="sum @0 1 0"/>
<v:f eqn="sum 0 0 @1"/>
<v:f eqn="prod @2 1 2"/>
<v:f eqn="prod @3 21600 pixelWidth"/>
<v:f eqn="prod @3 21600 pixelHeight"/>
<v:f eqn="sum @0 0 1"/>
<v:f eqn="prod @6 1 2"/>
<v:f eqn="prod @7 21600 pixelWidth"/>
<v:f eqn="sum @8 21600 0"/>
<v:f eqn="prod @7 21600 pixelHeight"/>
<v:f eqn="sum @10 21600 0"/>
</v:formulas>
<v:path o:extrusionok="f" gradientshapeok="t" o:connecttype="rect"/>
<o:lock v:ext="edit" aspectratio="t"/>
</v:shapetype><v:shape id="Picture_x0020_1" o:spid="_x0000_s1027" type="#_x0000_t75"
style='position:absolute;margin-left:0;margin-top:.05pt;width:256.45pt;
height:169.5pt;z-index:-251658240;visibility:visible;mso-wrap-style:square;
mso-width-percent:0;mso-height-percent:0;mso-wrap-distance-left:9pt;
mso-wrap-distance-top:0;mso-wrap-distance-right:9pt;
mso-wrap-distance-bottom:0;mso-position-horizontal:absolute;
mso-position-horizontal-relative:text;mso-position-vertical:absolute;
mso-position-vertical-relative:text;mso-width-percent:0;mso-height-percent:0;
mso-width-relative:page;mso-height-relative:page'>
<v:imagedata src="file:///C:\Users\tpbolin\AppData\Local\Temp\msohtmlclip1\01\clip_image001.png"
o:title=""/>
<w:wrap type="through"/>
</v:shape><![endif]--><!--[if !vml]--><!--[endif]--><!--[if gte vml 1]><v:shape id="Picture_x0020_2"
o:spid="_x0000_i1026" type="#_x0000_t75" style='width:241.5pt;height:147pt;
visibility:visible;mso-wrap-style:square'>
<v:imagedata src="file:///C:\Users\tpbolin\AppData\Local\Temp\msohtmlclip1\01\clip_image003.png"
o:title=""/>
</v:shape><![endif]--><!--[if !vml]--><!--[endif]--><o:p></o:p></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi0lGr_LJqngN6_hcM4fOfa0OHrxRQ6bVuWzIzb5_3gerWyZHrfcNYDA1lp4DFwwKWQ21462kHYdjBjIHH23XL-UgONNlBH4N8R7uRFsxmShI42qnOH9eqiblif0j89IWhM_3aULN2O3UqG/s1600/blog1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="211" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi0lGr_LJqngN6_hcM4fOfa0OHrxRQ6bVuWzIzb5_3gerWyZHrfcNYDA1lp4DFwwKWQ21462kHYdjBjIHH23XL-UgONNlBH4N8R7uRFsxmShI42qnOH9eqiblif0j89IWhM_3aULN2O3UqG/s320/blog1.png" width="320" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEio2URJx1tjI8LMy20WDWkO3UyvIUBvs3H2E5D1LmqzYe85PbRxV385kur-H3fwFzWATtxQoif0hjNyHqiI-VCGN6T8ixirEZOEksX0lq6clb35v-801QzrJ40RRQiK6Kac3qMZ2BPirpyA/s1600/blog2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="194" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEio2URJx1tjI8LMy20WDWkO3UyvIUBvs3H2E5D1LmqzYe85PbRxV385kur-H3fwFzWATtxQoif0hjNyHqiI-VCGN6T8ixirEZOEksX0lq6clb35v-801QzrJ40RRQiK6Kac3qMZ2BPirpyA/s320/blog2.png" width="320" /></a></div>
<div class="MsoNormal">
<br /></div>
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt; line-height: 115%; mso-ansi-language: EN-US; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: "Times New Roman"; mso-bidi-language: AR-SA; mso-bidi-theme-font: minor-bidi; mso-fareast-font-family: Calibri; mso-fareast-language: EN-US; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin;"><br clear="all" style="mso-special-character: line-break; page-break-before: always;" />
</span>
<br />
<div class="MsoNormal">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhS2HmdsKrne76H2LgRN9FQVU8uMYktggn9x7aVy1maqDrgmPEoTz_d933qUlMqGyzWa9bR8JTZQln3i8hP_ssFCeedEhUvpgcW3UlJZYVy59VSMKmx4sCmUK6mnuOTSUIKaJO2WCSUYjxV/s1600/blog3.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhS2HmdsKrne76H2LgRN9FQVU8uMYktggn9x7aVy1maqDrgmPEoTz_d933qUlMqGyzWa9bR8JTZQln3i8hP_ssFCeedEhUvpgcW3UlJZYVy59VSMKmx4sCmUK6mnuOTSUIKaJO2WCSUYjxV/s1600/blog3.png" /></a></div>
<div class="MsoNormal">
By setting the ‘MaximumErrorCount’ to 0 on the ForEach container
the failure will occur and the message displayed, however the loop will
continue through the remaining file.<o:p></o:p></div>
<br /><div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<br /></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhMkNjjWdEQs3MbT4EkPVTZlVqSldNzGqyCO3N57No4HCMnIYKS1cHhEBANZeSQGRJ0XC-T5BnFS31AaCGPjsJBm4eONMPPFhOxWhjWYpnVgSkVHRIUh0KU3_54Oj3Cp7lb8Yiu8ZTKPrbS/s1600/blog4.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="201" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhMkNjjWdEQs3MbT4EkPVTZlVqSldNzGqyCO3N57No4HCMnIYKS1cHhEBANZeSQGRJ0XC-T5BnFS31AaCGPjsJBm4eONMPPFhOxWhjWYpnVgSkVHRIUh0KU3_54Oj3Cp7lb8Yiu8ZTKPrbS/s320/blog4.png" width="320" /></a></div>
<div class="MsoNormal">
Errors produced by the task can be logged in any manner that
is available, email notification, logging to a table etc.<o:p></o:p></div>
ntx.dbahttp://www.blogger.com/profile/12800616241931590645noreply@blogger.com0tag:blogger.com,1999:blog-995268164382304636.post-35524081708527180302013-01-08T08:32:00.001-08:002013-01-08T08:32:22.349-08:00Server in Script Upgrade ModeWhen one of our SQL Server 2008R2 clusters failed over the amount of time needed between when the server came online and when clients could connect to the instance seemed to be overly long. After some research into the logs I found this error occuring multiple times for different ids, most of the ids were automated processes that connect as quickly as possible.<br />
<br />
<em><span style="font-family: "Helvetica Neue", Arial, Helvetica, sans-serif;">Login Failed for user 'xxxxx'. Reason: Server is in script upgrade mode. Only administrator can connect at this time.</span></em><br />
<em><span style="font-family: Arial;"></span></em><br />
<span style="font-family: inherit;">Going back over the upgrade logs the SQL Instance in question hadn't been patched in quite some time. So what was the reason for the upgrade script running?</span><br />
<br />
I googled for answer and came up empty as to why this would be happening after the patch was successfully installed.<br />
<br />
After some additional research the issue was found, SP1 had been installed on the primary node but not the secondary node of the cluster. Each time the instance was failed over the SQL Engine had to perform and upgrade/downgrade of the system databases.<br />
<br />
Lesson Learned: Patch one node and schedule the other node upgrade.<br />
<br />
ntx.dbahttp://www.blogger.com/profile/12800616241931590645noreply@blogger.com0tag:blogger.com,1999:blog-995268164382304636.post-2379504191143982062011-04-25T10:47:00.000-07:002011-04-25T10:47:24.143-07:00SSMS 2005 Edit Job IssueSeveral people at my company have been experiencing issues when double-clicking existing jobs on a SQL2005 server running SP3. The individual owns the job they are attempting to modify but when they double-click the job Management Studio opens up a new job window rather than the existing job.<br />
We searched the internet and were unable to find a resolution to this issue. After some testing it was discovered that when the client tools are running the RTM version, 1399, and the instances are running SP2 or beyond this behavior occurs.<br />
<br />
See screenshots below for how to find the versions you are running.<br />
<br />
The simple fix to this was to install the latest service pack of SQL2005 for the client tools on the workstation. There is no error given and none logged that we could find.<br />
<br />
Lesson Learned: Always keep your clients tools on the the same or later version of SQL that your instances are running on.<br />
<br />
<em>Register the server and to the right of the name you will see the version - 9.0.3042 in this example</em><br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhm9v2QcvpSxlulpV_LzEcTQgcabFUWsfQFBjAwTRKShYQzvvk2tVY8IT8qf7KrzJpDkhFmA9RqBNvyiV3s7VKgtHUYIIgwcHXtlo0XsHQoyuuuk6m4bq4i19yG-e5rTmhY2QMz951ejcFe/s1600/serverversion.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="320" i8="true" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhm9v2QcvpSxlulpV_LzEcTQgcabFUWsfQFBjAwTRKShYQzvvk2tVY8IT8qf7KrzJpDkhFmA9RqBNvyiV3s7VKgtHUYIIgwcHXtlo0XsHQoyuuuk6m4bq4i19yG-e5rTmhY2QMz951ejcFe/s320/serverversion.JPG" width="287" /></a><br />
<div align="left" class="separator" style="clear: both; text-align: center;"></div><em>In Management Studio, Click Help/About, the version is on the first line as shown circled in red.</em><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjpN2wxIU9knq9sX-uyHqyuHXbkgd-yS37xwCRODG0Reqt30oOt4OdtHOpnn2gYeUe2BGFd9Dqm0JtQsSIOGyU1svZipkpJ2Wtz1wIoq3L-yXfrHlIBploh9xh8oOpEcWA5AiQcGksiZIZk/s1600/clientversion.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="304" i8="true" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjpN2wxIU9knq9sX-uyHqyuHXbkgd-yS37xwCRODG0Reqt30oOt4OdtHOpnn2gYeUe2BGFd9Dqm0JtQsSIOGyU1svZipkpJ2Wtz1wIoq3L-yXfrHlIBploh9xh8oOpEcWA5AiQcGksiZIZk/s320/clientversion.JPG" width="320" /></a></div><br />
9.00.1399 is the RTM release any other version may cause the issue noted above.ntx.dbahttp://www.blogger.com/profile/12800616241931590645noreply@blogger.com0tag:blogger.com,1999:blog-995268164382304636.post-68396077466536237022011-03-30T11:26:00.000-07:002011-03-30T11:26:20.441-07:00Conversion Strikes AgainWhile doing some performance tuning this week I found a query that was executing very frequently from a web application. The query was doing a simple select from the base table with two columns in the where clause both of which were in the same index.<br />
The query in question went something like this:<br />
select contactid, documentid, documenttype from documents where contactid = ? and documentid = ?<br />
<br />
Profiler showed the query is this format:<br />
exec sp_executesql N' SELECT contactid, documentid, documenttype from Documents WHERE ContactID = @ContactID AND DocumentID = @DocumentID', N'@ContactID nvarchar(36),@DocumentID int', @ContactID = N'464906a7-5acc-44a0-9b50-76da83ba05d8', @DocumentID = 280<br />
<br />
The column ContactID is defined as a varchar(36) in the documents table. When the value to compare to comes in as nvarchar a conversion is required and the index is lost, a table scan ensues and reads skyrocket.<br />
<br />
The performance for the query is not that bad, but when that much data must be moved into memory to check for a match other items get pushed out.<br />
<br />
The fix the developer decided to go with was to change the column definition to nvarchar from varchar. This allowed the index to be used and the reads to drop significantly.<br />
<br />
In my environment we are finding more and more places where conversion required for the where clauses are causing indexes to be ignored in favor of a less efficient index or a table scan.ntx.dbahttp://www.blogger.com/profile/12800616241931590645noreply@blogger.com0tag:blogger.com,1999:blog-995268164382304636.post-10170881420108824912011-03-15T14:56:00.000-07:002011-03-15T14:56:07.169-07:00Skipping Task when Empty FileAt times just checking for a file existence may not enough, if the file exists but is empty the load task may need to be skipped. I have compiled the example below to show one method of skipping the DTS task on file empty. The example utilizes the Workflow Properties page of each DTS Task. Although most corporations are moving away from DTS in favor of new ETL work being done in SSIS, SQL Server Integration Services, there are still plenty of opportunites to improve DTS packages that are still running. This example only works if the load task is the only task in the DTS package. I’ll be posting an update later with an example of how to move to the next task without executing the current task.<br />
<br />
The workflow properties tab is available on the following components in the DTS Task pane. Bulk Insert Task, ActiveX Script Task, Execute SQL Task and Data Driven Qeury Task. The option is located by right-clicking the component, selecting Workflow and then Properties. Once on the Workflow Property tab select Options, check ‘Use Active X Script’ and then click properties. The mentioned sequence will open an Active X Script task window. This script is executed prior to the main body of the task.<br />
<br />
The code below opens a file that is defined in the global variable, CheckFIle. This is the full file path including name to be checked. If the file is empty or has a blank first line the task is skipped. The result in a job is that the step reports success.<br />
<br />
'**********************************************************************<br />
' Skip task if empty file or first line is blank, uses global variable CheckFile<br />
'************************************************************************<br />
Function Main()<br />
SET fso = CreateObject("Scripting.FileSystemObject")<br />
DataSetNameTxt = DTSGlobalVariables("CheckFile").value<br />
Set TextStream = fso.OpenTextFile(DataSetNameTxt,1)<br />
If TextStream.AtEndOFStream or TextStream.AtEndOfLine then<br />
Main=DTSStepScriptResult_DontExecuteTask<br />
else<br />
Main=DTSStepScriptResult_ExecuteTask<br />
end if<br />
TextStream.Close<br />
Set TextStream = nothing<br />
End Function<br />
<br />
By returning the value of either DontExecuteTask, skip the main task, or ExecuteTask proceed normally, execution the main body of the task is controlled.<br />
<br />
Below are the screenshots to show the path to Workflow Properties Task input window:<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjVlK8gI_UDulDbCfOOARkBW_7DGqHh7avaj6ms8uUsAwiMXoQ40W1Ovs4wtgIiwcTs7GaywUAeHJpQheBGyAIvM-MQaCvthQ5gGQGVtF6nxV5V9yzmQYASGDsajQzQxGXloChNXlRU0Tbx/s1600/WorkFlowProperties.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="251" q6="true" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjVlK8gI_UDulDbCfOOARkBW_7DGqHh7avaj6ms8uUsAwiMXoQ40W1Ovs4wtgIiwcTs7GaywUAeHJpQheBGyAIvM-MQaCvthQ5gGQGVtF6nxV5V9yzmQYASGDsajQzQxGXloChNXlRU0Tbx/s320/WorkFlowProperties.JPG" width="320" /></a></div><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh3WYgGHZO0qoys45GD1WAw5-rIjozhsINCAFiNGVa48_PLpcSGpY-Fgva8Ksm6_H0qXY8nKLEY6Ndusn2qGhPrsGpEw9eVXdNSVpVzmf-f729YXgrMIVIArJ1HMl7HycMyvFDShxmtMyWi/s1600/WorkFlowOptions.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="320" q6="true" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh3WYgGHZO0qoys45GD1WAw5-rIjozhsINCAFiNGVa48_PLpcSGpY-Fgva8Ksm6_H0qXY8nKLEY6Ndusn2qGhPrsGpEw9eVXdNSVpVzmf-f729YXgrMIVIArJ1HMl7HycMyvFDShxmtMyWi/s320/WorkFlowOptions.JPG" width="298" /></a></div>ntx.dbahttp://www.blogger.com/profile/12800616241931590645noreply@blogger.com0tag:blogger.com,1999:blog-995268164382304636.post-73886389911677066472011-01-11T07:38:00.000-08:002011-01-11T07:38:33.802-08:00Unexpected View PerformanceArticle published at http://www.sqlservercentral.com/articles/Views/71787/, covers an issue with differing datatypes hampering performance of a view. This was created after a real life experience I had.<br />
<br />
SQLServerCentral is an excellent resource for anyone involved with SQL.<br />
<br />
Take a look at the article and let me know what you think.ntx.dbahttp://www.blogger.com/profile/12800616241931590645noreply@blogger.com0tag:blogger.com,1999:blog-995268164382304636.post-69234818641613832172010-12-14T08:58:00.000-08:002010-12-14T08:58:25.703-08:00Start of WeekWorking with a developer last week to find for any given date the Monday of the week the date falls within. Several scripts online but none seemed to work reliably so the script below was written. Two parameters are used, @currdate, the date within the week, and @startdate, the first day of the year for @currdate.<br />
<br />
DECLARE @currdate DATETIME<br />
DECLARE @startdate DATETIME<br />
SET @currdate = GETDATE()<br />
SET @startdate = '01/01/' + CONVERT(CHAR(4),@currdate,112)<br />
select DATEADD(wk, DATEDIFF(wk, 7, dateadd(dd,(datepart(dy,@currdate))-1,@startdate)), 7)<br />
select DATENAME(dw,DATEADD(wk, DATEDIFF(wk, 7, dateadd(dd,(datepart(dy,@currdate))-1,@startdate)), 7))ntx.dbahttp://www.blogger.com/profile/12800616241931590645noreply@blogger.com0tag:blogger.com,1999:blog-995268164382304636.post-2914356527171699262010-11-22T11:39:00.000-08:002010-11-22T11:39:34.109-08:00Copy Objects Not CopyingAs sited in past posts we are shop that is still in the process of migrating off SQL Server 2000. We have several jobs that use copy objects to replace production data with a minimum of downtime. The process flow is similar to the following:<br />
<ul><li>Load Data into Simple Recovery Database</li>
<li>Massage Data doing updates/inserts to prepare for production</li>
<li>Use DTS Copy Object Task to move tables to production database</li>
<li>Rename current production tables to backup</li>
<li>Rename copied tables to production name</li>
</ul><br />
<div> </div>This process works very well except when two copy object tasks execute in parallel mode. When this happens one of the two copies will fail referencing objects that are not in the database the task is working with.<br />
<br />
<div> </div>In Microsoft's wisdom they default the script directory and filename and these are the same for each instance of the copy object task. If multiple tasks execute at the same time the files are overwritten with subsequent runs. When the process goes back to read the file for actions it picks up the new task, using objects not in the current connection.<br />
<br />
<div> </div>The developer who was working on a solution to this issue found this URL that explains the situation and a fix:<br />
http://www.sqlservercentral.com/articles/DTS/dtscopyobjectstask/1531/<br />
<br />
<div> </div>Hope this will help someone else.ntx.dbahttp://www.blogger.com/profile/12800616241931590645noreply@blogger.com0tag:blogger.com,1999:blog-995268164382304636.post-15095320636767704802010-08-30T07:46:00.000-07:002010-08-30T07:46:25.027-07:00Clustering GotchasA couple of weeks ago my company clustered a SQL2005 install and I posted about the 6 core CPU issues that were experienced. This weekend we clustered a SQL2000 on SQL2003 environment. SQL2000 is end of life but we still use it until the financial guys approve our upgrades. So with one of our most critical servers running standalone we attempted the cluster.<br />
<br />
This server had been clustered in the past under a design the previous DBA had created and sold using mountpoints. Mountpoints are not supported on SQL2000/Windows 2003 configurations. Timeout issues on the mountpoints caused the cluster to fail regularly.<br />
<br />
The following issues and resolutions from this weekend are below, it was successful and within the outage window.<br />
Temp and Tmp environment variables have spaces in the paths:<br />
Microsoft sets the default location for the Temp and Tmp variables are in the <Document and Settings> folder of the current user. When installing SQL on a cluster this will cause the install to fail with a folder not found error. The folder exists but if the path has spaces the install fails. Change the environment variables to a path without spaces, I usually use c:\temp for both.<br />
<br />
Timeout during install caused by multiple drives in the SQL cluster group;<br />
Not sure what causes the timeout but to get around this issue move all physical drives, excluding those needed for the install to the Cluster group. Once the install is complete move them back to the SQL group. We have 20 drive resources in the SQL group, this was done to assist with EMC snapshots but that technology is not currently being used.<br />
<br />
Clustering in SQL2000 is a far easier task than was SQL7.0. SQL2005 continues the improvement and hopefully so will SQL2008. As tasks become more and more straight forward DBAs will have to distinguish themselves by the other talents needed to excel. Performance tuning, database design etc. The administrative tasks will always be needed, but at least at my company, it takes more than just normal admin skills to succeed.ntx.dbahttp://www.blogger.com/profile/12800616241931590645noreply@blogger.com0tag:blogger.com,1999:blog-995268164382304636.post-86118232071636265692010-08-27T07:25:00.000-07:002010-08-27T07:25:22.222-07:00SQL2005 on 6 Core CPUA couple of weeks ago we upgraded one of our enterprise systems to a clustered environment using Dell Hardware with 2 physical CPUS, 6 Core Hyperthreaded, showing 24 available CPUs in task manager, add in 32gb of memory and still stay under 10k. These were great boxes for the application a document storage retrieval system.<br />
<br />
After windows was installed and we started on the SQL2005 initial install the setup would just fail and not report any usable error. What had started as a fairly simple upgrade turned into a nightmare.<br />
<br />
After several attempts and several hours of scouring the web our systems guy found an article about SQL2005 not installing on a server where the cores were not divisible by 4. Who would have thought that MS would have been so short sighted, or that the error message would not have given some indication.<br />
<br />
Once the system team modified the server to only show a single CPU the install went smooth and after applying SP2 all 24 processors put back into the pool.<br />
<br />
Since this upgrade the server is running at under 10 percent CPU usage and very seldom has any type of wait condition.<br />
<br />
Hope this helps someone else avoid a headache.ntx.dbahttp://www.blogger.com/profile/12800616241931590645noreply@blogger.com0tag:blogger.com,1999:blog-995268164382304636.post-38546310157100338302010-06-16T08:57:00.000-07:002010-06-16T08:57:26.573-07:00SSRS 2005 MigrationMy company is working on a DR Plan that includes VM for recovery while new hardware can be ordered and systems built. Last week I had the task of moving our SSRS 2005 installation to new VM that in the end would have the same name as the original physical server.<br />
<br />
Lets name the original server ServerA and the VM ServerB.<br />
<br />
I found a couple of articles through Google which covered the process with good detail, the links can be found at the below.<br />
<br />
After installing SQL2005 and SSRS on ServerB to match the versions on ServerA, I took a backup of the Source Databases, reportserver and reportservertempdb and restored those back to ServerB.<br />
Ensured the configuration for both servers matched and then imported the Encryption Keys ServerB came up and worked, it would serve up the Reports, ServerB\Reports.<br />
<br />
After shutting down ServerA we renamed ServerB to ServerA, performed the required updates on new ServerA for SSRS to be able to connect, link below and everything was good to go. So we thought.<br />
<br />
Two issues arose after the migration, neither of which I could find a fix for through Google or other searches.<br />
<br />
The first issue is that when IIS is installed two accounts are created IUSR_(servername) and IWAM_(servername), both of these retain their original name after the server rename. Our issue was that IUSR_ServerA owned subscriptions, these failed because the user did not exist. The system team renamed the account, IUSR_ServerB and IWAM_ServerB to IUSR_ServerA and IWAM_ServerA, this resolved the problem of the account not existing but it was missing some privileges so it was still unable to successfully execute the subscriptions, I ended up changing the owner of the subscription to the SQL Service Account and the subscriptions would generate. Reinstalling IIS may resolve this issue, but not sure what would happen to the SSRS configuration information.<br />
<br />
The second issue arose when the subscriptions emailed links to the reports, the links included ServerB in the path, \\ServerB\Daily Reports...., resulting in a 404 error when following. Inside the config file referenced below there is a key named URLRoot, this required updating to the correct server name, C:\Program Files\Microsoft SQL Server\MSSQL.x\Reporting Services\Report Server\rsreportserver.cfg.<br />
I didn't need to change the user/password as detailed in the first article.<br />
<br />
Overall this was a fairly smooth process, the two issues were easy to resolve once found but caused unplanned report failures and production issues for users relying on the reports.<br />
<br />
Migration Assistance:<br />
http://blogs.egroup-us.com/?p=1600<br />
http://support.microsoft.com/Default.aspx?id=842425<br />
<br />
Renaming a SSRS Computer:<br />
http://msdn.microsoft.com/en-us/library/ms345235.aspxntx.dbahttp://www.blogger.com/profile/12800616241931590645noreply@blogger.com0tag:blogger.com,1999:blog-995268164382304636.post-61702953209069635042010-06-11T13:04:00.000-07:002010-06-11T13:08:32.346-07:00SQL2000I work for an insurance company in North Texas that is running still running a majority of its SQL serves on SQL2000. We have requested budget for licensing of SQL2008 and have just received our first SQL2008 test server from the network group. It is in a segmented VLAN so only one user at a time will be able to test but it is a start.<br /><br />I created this blog as a place where issues from SQL2000 to SQL2008 can be recorded and solutions posted. I'm not sure but I would think there are still quite a few SQL2000 installations out there.<br /><br />As I find items to help out our developers be more effecient, they are a good bunch but very few have any database knowledge. With only 2 dbas at the company and 42 production servers, we serve in a consulting role.ntx.dbahttp://www.blogger.com/profile/12800616241931590645noreply@blogger.com0tag:blogger.com,1999:blog-995268164382304636.post-51989910025149991962010-06-11T12:57:00.000-07:002010-06-11T13:03:32.462-07:00xp_sendmail issuesAfter several weeks of wrestling with xp_sendmail failing with the error 'No such user or group (sqlusername)' it has been resolved. The error only showed up when the @query parameter was also in use. The solution was easier than I thought, each of the xp_sendmail commands that used the @query also needed the @dbuse parameter added.<br />This all started happening after I moved the system databases off of a mountpoint to a drive letter.<br />The company I work for runs on SQL2000 mainly, in the process of upgrading to SQL2008. Not sure what the actual cause of the change in behavior was but it has been resolved.ntx.dbahttp://www.blogger.com/profile/12800616241931590645noreply@blogger.com0