tag:blogger.com,1999:blog-16712491583088578622024-03-05T18:11:48.905+01:00SQL Server AreaEgon Vandenrijdthttp://www.blogger.com/profile/17366254188434203611noreply@blogger.comBlogger21125tag:blogger.com,1999:blog-1671249158308857862.post-13853197729486464132015-05-08T13:08:00.002+02:002015-05-08T13:08:33.282+02:00<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<h1 style="margin: 12pt 0cm 3pt;">
<span lang="EN-GB"><span style="font-size: large;"><span style="font-family: Cambria;">SSIS 2012 / 2014 SSIS Catalog Package Protection level<o:p></o:p></span></span></span></h1>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span lang="EN-GB"><span style="font-family: Calibri;">The last 2 days I’ve spend too much time
struggling with the protection level of my SSIS Catalog deployed packages. So I
investigated why I was having all those problems, and I will give you an
overview of how to solve it.<o:p></o:p></span></span></div>
<br />
<h2 style="margin: 10pt 0cm 0pt;">
<span lang="EN-GB"><span style="font-size: medium;"><span style="color: #4f81bd;"><span style="font-family: Cambria;">Scenario<o:p></o:p></span></span></span></span></h2>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span lang="EN-GB"><span style="font-family: Calibri;">We have our SQLServer 2014 SSIS servers
installed for more than half a year, and after some initial problems (Double
Hop, executing Packages from within a Stored Procedure,…) and going through a
learning curve, we were really comfortable and pleased with this SSIS Catalog
environment, so we wanted to convert some “older” (2008R2) packages and jobs,
and deploy them to our new environment. <o:p></o:p></span></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span lang="EN-GB"><span style="font-family: Calibri;">The Deployment of our packages is done by a
PowerShell script to reduce the manual work. <o:p></o:p></span></span></div>
<br />
<h2 style="margin: 10pt 0cm 0pt;">
<span lang="EN-GB"><span style="font-size: medium;"><span style="color: #4f81bd;"><span style="font-family: Cambria;">Case<o:p></o:p></span></span></span></span></h2>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span lang="EN-GB"><span style="font-family: Calibri;">Some of our packages use SQLServer
authentication :-S, because they need to connect to an untrusted domain. There
for the user and password were specified in the connection manager.<o:p></o:p></span></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span lang="EN-GB"><span style="font-family: Calibri;">The Protection Level of the project and the
packages was set “</span></span><strong><span lang="EN-GB" style="color: #222222; font-family: "Helvetica","sans-serif";">EncryptSensitiveWithPassword</span></strong><span lang="EN-GB"><span style="font-family: Calibri;">”,
which allows us to run the packages during the development (Data Tools), and
allowed us also to Decrypt the package at runtime from within a command or job:<o:p></o:p></span></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 12pt; mso-background-themecolor: background1; mso-background-themeshade: 217; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;">
<span lang="EN-GB" style="color: #222222; font-family: Consolas; font-size: 10pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL-BE;">/FILE
"C:\Program Files\Microsoft SQL Server\100\DTS\Packages<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 12pt; mso-background-themecolor: background1; mso-background-themeshade: 217; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;">
<span lang="EN-GB" style="color: #222222; font-family: Consolas; font-size: 10pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL-BE;"><span style="mso-spacerun: yes;"> </span>\MainSSISPackage.dtsx" <span style="background: yellow; mso-highlight: yellow;">/DECRYPT P@ssWord</span> /CONFIGFILE
"C:\Program Files<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 12pt; mso-background-themecolor: background1; mso-background-themeshade: 217; tab-stops: 45.8pt 91.6pt 137.4pt 183.2pt 229.0pt 274.8pt 320.6pt 366.4pt 412.2pt 458.0pt 503.8pt 549.6pt 595.4pt 641.2pt 687.0pt 732.8pt;">
<span lang="EN-GB" style="color: #222222; font-family: Consolas; font-size: 10pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: NL-BE;"><span style="mso-spacerun: yes;"> </span>\Microsoft SQL
Server\100\DTS\Packages\DataConfig.dtsConfig"<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span lang="EN-GB"><o:p><span style="font-family: Calibri;"> </span></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span lang="EN-GB"><span style="font-family: Calibri;">This is where all troubles began.
Apparently SQLServer 2012 / 2014 SSIS catalogs have their own encryption
“system” (ServerStorage), and the configuration of the Decryption password
seems to be “missing”.<o:p></o:p></span></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span lang="EN-GB"><span style="font-family: Calibri;">(More detailed information about SSIS
Encryption can be found at: </span><a href="https://msdn.microsoft.com/en-us/library/ms141747.aspx"><span style="color: blue; font-family: Calibri;">https://msdn.microsoft.com/en-us/library/ms141747.aspx</span></a><span style="font-family: Calibri;">)<o:p></o:p></span></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span lang="EN-GB"><o:p><span style="font-family: Calibri;"> </span></o:p></span></div>
<br />
<h2 style="margin: 10pt 0cm 0pt;">
<span lang="EN-GB"><span style="font-size: medium;"><span style="color: #4f81bd;"><span style="font-family: Cambria;">Error<o:p></o:p></span></span></span></span></h2>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span lang="EN-GB"><span style="font-family: Calibri;">After the conversion of the package (as-is:
“EncryptSensitiveWithPassword”) and deploy it to the SSIS Catalog, we create
our Job, based on our “experience” with the previous version like this:<o:p></o:p></span></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="EN-US" style="color: blue; font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">EXEC</span><span lang="EN-US" style="font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">
@ReturnCode <span style="color: grey;">=</span> msdb<span style="color: grey;">.</span>dbo<span style="color: grey;">.</span><span style="color: maroon;">sp_add_jobstep</span><span style="color: blue;"> </span>@job_id<span style="color: grey;">=</span>@jobId<span style="color: grey;">,</span> @step_name<span style="color: grey;">=</span><span style="color: red;">N'Start Demoload Package'</span><span style="color: grey;">,</span>
<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"><span style="mso-tab-count: 2;"> </span>@step_id<span style="color: grey;">=</span>1<span style="color: grey;">,</span> <o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"><span style="mso-tab-count: 2;"> </span>@cmdexec_success_code<span style="color: grey;">=</span>0<span style="color: grey;">,</span> <o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"><span style="mso-tab-count: 2;"> </span>@on_success_action<span style="color: grey;">=</span>1<span style="color: grey;">,</span> <o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"><span style="mso-tab-count: 2;"> </span>@on_success_step_id<span style="color: grey;">=</span>0<span style="color: grey;">,</span> <o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"><span style="mso-tab-count: 2;"> </span>@on_fail_action<span style="color: grey;">=</span>2<span style="color: grey;">,</span> <o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"><span style="mso-tab-count: 2;"> </span>@on_fail_step_id<span style="color: grey;">=</span>0<span style="color: grey;">,</span> <o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"><span style="mso-tab-count: 2;"> </span>@retry_attempts<span style="color: grey;">=</span>0<span style="color: grey;">,</span> <o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"><span style="mso-tab-count: 2;"> </span>@retry_interval<span style="color: grey;">=</span>0<span style="color: grey;">,</span> <o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"><span style="mso-tab-count: 2;"> </span>@os_run_priority<span style="color: grey;">=</span>0<span style="color: grey;">,</span> @subsystem<span style="color: grey;">=</span><span style="color: red;">N'SSIS'</span><span style="color: grey;">,</span> <o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"><span style="mso-tab-count: 2;"> </span>@command<span style="color: grey;">=</span><span style="color: red;">N'/ISSERVER "\"\SSISDB\Acc\MyProject\DemoUpload.dtsx\""
<span style="background: yellow; mso-highlight: yellow;">/DECRYPT
CalculatorDbUpload</span> /SERVER "\"env-ssis-asql\"" /Par
"\"$Project::Compatibilitygroup\"";9616EE37E32A8D9 /Par
"\"$Project::Country\"";ES /Par "\"$Project::DestinationDb\"";MyDB
/Par
"\"$Project::DestinationServer\"";"\"databasecalculator.sqldo.acc.intacc.conseur.org\""
/Par "\"$ServerOption::LOGGING_LEVEL(Int16)\"";1 /Par
"\"$ServerOption::SYNCHRONIZED(Boolean)\"";True /CALLERINFO
SQLAGENT /REPORTING E'</span><span style="color: grey;">,</span> <o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"><span style="mso-tab-count: 2;"> </span>@database_name<span style="color: grey;">=</span><span style="color: red;">N'master'</span><span style="color: grey;">,</span> <o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"><span style="mso-tab-count: 2;"> </span>@flags<span style="color: grey;">=</span>0<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 9.5pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"><o:p> </o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span lang="EN-GB"><span style="font-family: Calibri;">When executing the job, or executing the
package directly, we receive an error:<o:p></o:p></span></span></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhZpKjR5HXNi2emIayy5DExMl0MiDp3pieLnV1wXkS-ydF8_3_KEruS-58no43j1c1GF9NXVwIzy-gTDkrRUWR4UDSj1qn2C4IXmfEq-Ig-R37gYkvm6GZ_nShS6zvBGSkLspMN9BUbwzE/s1600/SSIS_1_Error.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhZpKjR5HXNi2emIayy5DExMl0MiDp3pieLnV1wXkS-ydF8_3_KEruS-58no43j1c1GF9NXVwIzy-gTDkrRUWR4UDSj1qn2C4IXmfEq-Ig-R37gYkvm6GZ_nShS6zvBGSkLspMN9BUbwzE/s640/SSIS_1_Error.png" width="640" /></a></div>
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="mso-ansi-language: NL-BE; mso-fareast-language: NL-BE; mso-no-proof: yes;"></span><span lang="EN-GB"><o:p></o:p></span><br /></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span lang="EN-GB"><o:p><span style="font-family: Calibri;"> </span></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span lang="EN-GB" style="color: red;"><span style="font-family: Calibri;">“<b style="mso-bidi-font-weight: normal;">Failed to decrypt an encrypted XML node because the password was not
specified or not correct</b>”<o:p></o:p></span></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span lang="EN-GB"><span style="font-family: Calibri;">So, the decrypt option in our job statement
didn’t do the trick, and, as a result of this, the password for our user
(sensitive data) couldn’t be decrypted, so we can’t connect to our Destination
connection manager.<o:p></o:p></span></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span lang="EN-GB"><span style="font-family: Calibri;">Although some of the posts I found on this
on the internet tell me that “EncryptSensitiveWithPassword” is the correct
option to deploy packages to the SSIS Catalog, it doesn’t work for me. A
possible reason for can be the fact that I deploy my packages by a PowerShell
script, and not by the “default” Deploy option in the Data Tools.<o:p></o:p></span></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span lang="EN-GB"><span style="font-family: Calibri;">So, when looking a little bit further, I
blogs of people who had a similar problem, and putting the protection level to
“DontSaveSensitive” did the trick for them. When they deployed their package,
the SSIS Catalog Automagically ( ;-) ) knew the password. Unfortunately, also
this solution didn’t work for me. Again, maybe my way of Deploying (PowerShell
scripts) makes the difference.<o:p></o:p></span></span></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjh2SDboCwzt-9w23yYQKjCpYWEklicHMaH88WBmJ-zJ6COKkd44rvfvVc1to9Ms6XYns2SVgLrc4CEYNGB8dUEyJnWawlZz_P-gmF3wEaYOwZ7vTAyBZ2eFo5-Lnpi_HGPy4OWB0IfGBs/s1600/SSIS_2_Error.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="220" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjh2SDboCwzt-9w23yYQKjCpYWEklicHMaH88WBmJ-zJ6COKkd44rvfvVc1to9Ms6XYns2SVgLrc4CEYNGB8dUEyJnWawlZz_P-gmF3wEaYOwZ7vTAyBZ2eFo5-Lnpi_HGPy4OWB0IfGBs/s640/SSIS_2_Error.png" width="640" /></a></div>
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="mso-ansi-language: NL-BE; mso-fareast-language: NL-BE; mso-no-proof: yes;"></span><span lang="EN-GB"><o:p></o:p></span><br /></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span lang="EN-GB"><span style="font-family: Calibri;">The “Decryption” of the package is ok
(there is no encryption anymore), but the user password is missing.<o:p></o:p></span></span></div>
<br />
<h2 style="margin: 10pt 0cm 0pt;">
<span lang="EN-GB"><span style="font-size: medium;"><span style="color: #4f81bd;"><span style="font-family: Cambria;">Solution<o:p></o:p></span></span></span></span></h2>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span lang="EN-GB"><span style="font-family: Calibri;">After being looking for a way to include
the Package Decryption Password in my PowerShell script, which I didn’t found,
I found that there is a configuration option at the package level to use
Environment Variables to set my user Password. In that way they are not
included in the package, and thus, not encrypted by the Package Password
anymore.<o:p></o:p></span></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span lang="EN-GB"><o:p><span style="font-family: Calibri;"> </span></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span lang="EN-GB"><span style="font-family: Calibri;">So, I expanded my PowerShell script a
little bit:<o:p></o:p></span></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="EN-US" style="color: maroon; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">...<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="EN-US" style="color: purple; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">$SourceDB</span><span lang="EN-US" style="color: black; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">
</span><span lang="EN-US" style="color: red; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">=</span><span lang="EN-US" style="color: black; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"> </span><span lang="EN-US" style="color: maroon; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">"OCU_Small"</span><span lang="EN-US" style="color: black; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="EN-US" style="color: purple; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">$DestinationDB</span><span lang="EN-US" style="color: black; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">
</span><span lang="EN-US" style="color: red; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">=</span><span lang="EN-US" style="color: black; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"> </span><span lang="EN-US" style="color: maroon; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">"CalculatorDB"</span><span lang="EN-US" style="color: black; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span style="background: yellow; color: purple; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: NL-BE; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-highlight: yellow;">$SourcePW</span><span style="background: yellow; color: black; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: NL-BE; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-highlight: yellow;"> </span><span style="background: yellow; color: red; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: NL-BE; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-highlight: yellow;">=</span><span style="background: yellow; color: black; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: NL-BE; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-highlight: yellow;"> </span><span style="background: yellow; color: maroon; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: NL-BE; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-highlight: yellow;">"MySourcePw"</span><span style="color: black; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: NL-BE; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span style="background: yellow; color: purple; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: NL-BE; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-highlight: yellow;">$DestinationPW</span><span style="background: yellow; color: black; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: NL-BE; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-highlight: yellow;"> </span><span style="background: yellow; color: red; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: NL-BE; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-highlight: yellow;">=</span><span style="background: yellow; color: black; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: NL-BE; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-highlight: yellow;"> </span><span style="background: yellow; color: maroon; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: NL-BE; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-highlight: yellow;">"MyDestinationPw"</span><span style="color: maroon; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: NL-BE; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span style="color: maroon; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: NL-BE; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">...<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="EN-US" style="color: purple; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">$environment</span><span lang="EN-US" style="color: black; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">.</span><span lang="EN-US" style="color: saddlebrown; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">Variables</span><span lang="EN-US" style="color: black; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">.</span><span lang="EN-US" style="color: saddlebrown; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">Add</span><span lang="EN-US" style="color: black; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">(</span><span lang="EN-US" style="color: maroon; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">"SourceDB"</span><span lang="EN-US" style="color: black; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">,[</span><span lang="EN-US" style="color: teal; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">System.TypeCode</span><span lang="EN-US" style="color: black; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">]::</span><span lang="EN-US" style="color: saddlebrown; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">String</span><span lang="EN-US" style="color: black; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">, </span><span lang="EN-US" style="color: purple; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">$SourceDB</span><span lang="EN-US" style="color: black; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">,
</span><span lang="EN-US" style="color: purple; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">$false</span><span lang="EN-US" style="color: black; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">,</span><span lang="EN-US" style="color: maroon; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">"SourceDB"</span><span lang="EN-US" style="color: black; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">)<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="EN-US" style="color: purple; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">$environment</span><span lang="EN-US" style="color: black; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">.</span><span lang="EN-US" style="color: saddlebrown; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">Variables</span><span lang="EN-US" style="color: black; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">.</span><span lang="EN-US" style="color: saddlebrown; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">Add</span><span lang="EN-US" style="color: black; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">(</span><span lang="EN-US" style="color: maroon; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">"DestinationDB"</span><span lang="EN-US" style="color: black; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">,[</span><span lang="EN-US" style="color: teal; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">System.TypeCode</span><span lang="EN-US" style="color: black; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">]::</span><span lang="EN-US" style="color: saddlebrown; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">String</span><span lang="EN-US" style="color: black; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">, </span><span lang="EN-US" style="color: purple; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">$DestinationDB</span><span lang="EN-US" style="color: black; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">,
</span><span lang="EN-US" style="color: purple; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">$false</span><span lang="EN-US" style="color: black; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">,</span><span lang="EN-US" style="color: maroon; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">"DestinationDB"</span><span lang="EN-US" style="color: black; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">)<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="EN-US" style="background: yellow; color: purple; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-highlight: yellow;">$environment</span><span lang="EN-US" style="background: yellow; color: black; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-highlight: yellow;">.</span><span lang="EN-US" style="background: yellow; color: saddlebrown; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-highlight: yellow;">Variables</span><span lang="EN-US" style="background: yellow; color: black; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-highlight: yellow;">.</span><span lang="EN-US" style="background: yellow; color: saddlebrown; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-highlight: yellow;">Add</span><span lang="EN-US" style="background: yellow; color: black; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-highlight: yellow;">(</span><span lang="EN-US" style="background: yellow; color: maroon; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-highlight: yellow;">"DestinationPW"</span><span lang="EN-US" style="background: yellow; color: black; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-highlight: yellow;">,[</span><span lang="EN-US" style="background: yellow; color: teal; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-highlight: yellow;">System.TypeCode</span><span lang="EN-US" style="background: yellow; color: black; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-highlight: yellow;">]::</span><span lang="EN-US" style="background: yellow; color: saddlebrown; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-highlight: yellow;">String</span><span lang="EN-US" style="background: yellow; color: black; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-highlight: yellow;">, </span><span lang="EN-US" style="background: yellow; color: purple; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-highlight: yellow;">$DestinationPW</span><span lang="EN-US" style="background: yellow; color: black; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-highlight: yellow;">, </span><span lang="EN-US" style="background: yellow; color: purple; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-highlight: yellow;">$false</span><span lang="EN-US" style="background: yellow; color: black; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-highlight: yellow;">,</span><span lang="EN-US" style="background: yellow; color: maroon; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-highlight: yellow;">"DestinationPW"</span><span lang="EN-US" style="background: yellow; color: black; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-highlight: yellow;">)</span><span lang="EN-US" style="color: black; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="EN-US" style="background: yellow; color: purple; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-highlight: yellow;">$environment</span><span lang="EN-US" style="background: yellow; color: black; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-highlight: yellow;">.</span><span lang="EN-US" style="background: yellow; color: saddlebrown; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-highlight: yellow;">Variables</span><span lang="EN-US" style="background: yellow; color: black; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-highlight: yellow;">.</span><span lang="EN-US" style="background: yellow; color: saddlebrown; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-highlight: yellow;">Add</span><span lang="EN-US" style="background: yellow; color: black; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-highlight: yellow;">(</span><span lang="EN-US" style="background: yellow; color: maroon; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-highlight: yellow;">"SourcePW"</span><span lang="EN-US" style="background: yellow; color: black; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-highlight: yellow;">,[</span><span lang="EN-US" style="background: yellow; color: teal; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-highlight: yellow;">System.TypeCode</span><span lang="EN-US" style="background: yellow; color: black; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-highlight: yellow;">]::</span><span lang="EN-US" style="background: yellow; color: saddlebrown; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-highlight: yellow;">String</span><span lang="EN-US" style="background: yellow; color: black; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-highlight: yellow;">, </span><span lang="EN-US" style="background: yellow; color: purple; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-highlight: yellow;">$SourcePW</span><span lang="EN-US" style="background: yellow; color: black; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-highlight: yellow;">, </span><span lang="EN-US" style="background: yellow; color: purple; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-highlight: yellow;">$false</span><span lang="EN-US" style="background: yellow; color: black; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-highlight: yellow;">,</span><span lang="EN-US" style="background: yellow; color: maroon; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-highlight: yellow;">"SourcePW"</span><span lang="EN-US" style="background: yellow; color: black; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-highlight: yellow;">)</span><span lang="EN-US" style="color: black; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="EN-US" style="color: purple; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">$environment</span><span lang="EN-US" style="color: black; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">.</span><span lang="EN-US" style="color: saddlebrown; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">Alter</span><span lang="EN-US" style="color: black; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">()<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span style="color: maroon; font-family: "Courier New"; font-size: 10pt; mso-ansi-language: NL-BE; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">...<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span lang="EN-US" style="mso-ansi-language: EN-US;"><o:p><span style="font-family: Calibri;"> </span></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span lang="EN-US" style="mso-ansi-language: EN-US;"><span style="font-family: Calibri;">And I bound
the environment Variables to my package in the configuration:<o:p></o:p></span></span></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiaCg0b1Oq4NCOc7RSYVWLRsh_wwa260Ld9A5KUWDe41_k7iPO3mKLG1wKg9SXb_5Z7tHVkS5FOQ1H2Q4SGLiR_85gflze1oB9o_YrToG3fdafL7hU_eDX1mnv27LwU8up5yCnGv3TLRS8/s1600/SSIS_3_Error.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiaCg0b1Oq4NCOc7RSYVWLRsh_wwa260Ld9A5KUWDe41_k7iPO3mKLG1wKg9SXb_5Z7tHVkS5FOQ1H2Q4SGLiR_85gflze1oB9o_YrToG3fdafL7hU_eDX1mnv27LwU8up5yCnGv3TLRS8/s640/SSIS_3_Error.png" width="640" /></a></div>
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="mso-ansi-language: NL-BE; mso-fareast-language: NL-BE; mso-no-proof: yes;"></span><span lang="EN-US" style="mso-ansi-language: EN-US;"><o:p></o:p></span><br /></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="mso-ansi-language: NL-BE; mso-fareast-language: NL-BE; mso-no-proof: yes;"></span><span lang="EN-US" style="mso-ansi-language: EN-US;"><o:p></o:p></span><br /></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span lang="EN-US" style="mso-ansi-language: EN-US;"><span style="font-family: Calibri;">And then,
the package did run successfully directly started from the catalog!!!<o:p></o:p></span></span></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi4yjh5Ny52qCRr7yDbjkfI1pPbFK-APF9OJJ115W0SVKrzODWoyCxlJPqhh2Nu8eduhLik5TwFCMUhPWnteGV4M4WnflcznX_yoBsfbixoaeIc4AgdGkBNNQMU2h1BnIreX3CbuLmSn7Q/s1600/SSIS_4_Error.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="168" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi4yjh5Ny52qCRr7yDbjkfI1pPbFK-APF9OJJ115W0SVKrzODWoyCxlJPqhh2Nu8eduhLik5TwFCMUhPWnteGV4M4WnflcznX_yoBsfbixoaeIc4AgdGkBNNQMU2h1BnIreX3CbuLmSn7Q/s640/SSIS_4_Error.png" width="640" /></a></div>
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="mso-ansi-language: NL-BE; mso-fareast-language: NL-BE; mso-no-proof: yes;"></span><span lang="EN-US" style="mso-ansi-language: EN-US;"><o:p></o:p></span><br /></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span lang="EN-US" style="mso-ansi-language: EN-US;"><o:p><span style="font-family: Calibri;"> </span></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<v:shape id="Picture_x0020_6" o:spid="_x0000_s1026" style="height: 142.55pt; margin-left: -0.3pt; margin-top: 59.8pt; mso-height-percent: 0; mso-height-relative: page; mso-position-horizontal-relative: text; mso-position-horizontal: absolute; mso-position-vertical-relative: text; mso-position-vertical: absolute; mso-width-percent: 0; mso-width-relative: page; mso-wrap-distance-bottom: 0; mso-wrap-distance-left: 9pt; mso-wrap-distance-right: 9pt; mso-wrap-distance-top: 0; mso-wrap-style: square; position: absolute; visibility: visible; width: 224.55pt; z-index: 251658240;" type="#_x0000_t75"><span style="font-family: Calibri;">
<v:imagedata o:title="" src="file:///C:\Users\ext_evn\AppData\Local\Temp\msohtmlclip1\01\clip_image021.png">
<w:wrap type="square">
</w:wrap></v:imagedata></span></v:shape><span lang="EN-US" style="mso-ansi-language: EN-US;"><span style="font-family: Calibri;">Unfortunately, it still fails when called from
the job (it doesn’t take the environment variable). So let’s create an
environment reference to solve this, and specify it in the Package Call from
the job:<o:p></o:p></span></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<u><span lang="EN-US" style="mso-ansi-language: EN-US;"><span style="font-family: Calibri;">Code:<o:p></o:p></span></span></u></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="EN-US" style="color: blue; font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">USE</span><span lang="EN-US" style="font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"> SSISDB<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="EN-US" style="color: blue; font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">GO</span><span lang="EN-US" style="font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"><o:p> </o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="EN-US" style="color: blue; font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">DECLARE</span><span lang="EN-US" style="font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">
@reference_id <span style="color: blue;">bigint</span><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"><o:p> </o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="EN-US" style="color: blue; font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">EXEC</span><span lang="EN-US" style="font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"> [SSISDB]<span style="color: grey;">.</span>[catalog]<span style="color: grey;">.</span>[create_environment_reference]<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="EN-US" style="color: blue; font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"><span style="mso-spacerun: yes;"> </span></span><span lang="EN-US" style="font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">@environment_name <span style="color: grey;">=</span>
<span style="color: red;">N'MyEnvironmentName'</span><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="EN-US" style="color: grey; font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">,</span><span lang="EN-US" style="font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"><span style="mso-spacerun: yes;"> </span>@reference_id <span style="color: grey;">=</span>
@reference_id <span style="color: blue;">OUTPUT</span><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span style="color: grey; font-family: Consolas; font-size: 8pt; mso-ansi-language: NL-BE; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">,</span><span style="font-family: Consolas; font-size: 8pt; mso-ansi-language: NL-BE; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"><span style="mso-spacerun: yes;">
</span>@project_name <span style="color: grey;">=</span> <span style="color: red;">N'MyProjectName'</span><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span style="color: grey; font-family: Consolas; font-size: 8pt; mso-ansi-language: NL-BE; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">,</span><span style="font-family: Consolas; font-size: 8pt; mso-ansi-language: NL-BE; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"><span style="mso-spacerun: yes;">
</span>@folder_name <span style="color: grey;">=</span> <span style="color: red;">N'MyFolderName'</span><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="PT-BR" style="color: grey; font-family: Consolas; font-size: 8pt; mso-ansi-language: PT-BR; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">,</span><span lang="PT-BR" style="font-family: Consolas; font-size: 8pt; mso-ansi-language: PT-BR; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"><span style="mso-spacerun: yes;"> </span>@reference_type <span style="color: grey;">=</span>
R<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span style="color: blue; font-family: Consolas; font-size: 8pt; mso-ansi-language: NL-BE; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">SELECT</span><span style="font-family: Consolas; font-size: 8pt; mso-ansi-language: NL-BE; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span style="font-family: Consolas; font-size: 8pt; mso-ansi-language: NL-BE; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"><span style="mso-spacerun: yes;">
</span>@reference_id<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span lang="EN-US" style="color: blue; font-family: Consolas; font-size: 9.5pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"><o:p> --> Result: 79 (= reference_id)</o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span lang="EN-US" style="color: blue; font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"><o:p> </o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="EN-US" style="color: blue; font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">EXEC</span><span lang="EN-US" style="font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">
@ReturnCode <span style="color: grey;">=</span> msdb<span style="color: grey;">.</span>dbo<span style="color: grey;">.</span><span style="color: maroon;">sp_add_jobstep</span><span style="color: blue;"> </span>@job_id<span style="color: grey;">=</span>@jobId<span style="color: grey;">,</span> @step_name<span style="color: grey;">=</span><span style="color: red;"> N'Start Demoload Package'</span><span style="color: grey;">,</span>
<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"><span style="mso-tab-count: 2;"> </span>@step_id<span style="color: grey;">=</span>1<span style="color: grey;">,</span> <o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"><span style="mso-tab-count: 2;"> </span>@cmdexec_success_code<span style="color: grey;">=</span>0<span style="color: grey;">,</span> <o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"><span style="mso-tab-count: 2;"> </span>@on_success_action<span style="color: grey;">=</span>1<span style="color: grey;">,</span> <o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"><span style="mso-tab-count: 2;"> </span>@on_success_step_id<span style="color: grey;">=</span>0<span style="color: grey;">,</span> <o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"><span style="mso-tab-count: 2;"> </span>@on_fail_action<span style="color: grey;">=</span>2<span style="color: grey;">,</span> <o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"><span style="mso-tab-count: 2;"> </span>@on_fail_step_id<span style="color: grey;">=</span>0<span style="color: grey;">,</span> <o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"><span style="mso-tab-count: 2;"> </span>@retry_attempts<span style="color: grey;">=</span>0<span style="color: grey;">,</span> <o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"><span style="mso-tab-count: 2;"> </span>@retry_interval<span style="color: grey;">=</span>0<span style="color: grey;">,</span> <o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"><span style="mso-tab-count: 2;"> </span>@os_run_priority<span style="color: grey;">=</span>0<span style="color: grey;">,</span> @subsystem<span style="color: grey;">=</span><span style="color: red;">N'SSIS'</span><span style="color: grey;">,</span> <o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"><span style="mso-tab-count: 2;"> </span>@command<span style="color: grey;">=</span><span style="color: red;">N'/ISSERVER "\"\SSISDB\Acc\MyProject\DemoUpload.dtsx\""
<span style="background: yellow; mso-highlight: yellow;">/ENVREFERENCE 79</span>
/SERVER "\"env-ssis-asql\"" /Par
"\"$Project::Compatibilitygroup\"";9616EE37E32A8D9 /Par
"\"$Project::Country\"";ES /Par
"\"$Project::DestinationDb\"";MyDB /Par "\"$Project::DestinationServer\"";"\"databasecalculator.sqldo.acc.intacc.conseur.org\""
/Par "\"$ServerOption::LOGGING_LEVEL(Int16)\"";1 /Par
"\"$ServerOption::SYNCHRONIZED(Boolean)\"";True /CALLERINFO
SQLAGENT /REPORTING E'</span><span style="color: grey;">,</span> <o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"><span style="mso-tab-count: 2;"> </span>@database_name<span style="color: grey;">=</span><span style="color: red;">N'master'</span><span style="color: grey;">,</span> <o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"><span style="mso-tab-count: 2;"> </span>@flags<span style="color: grey;">=</span>0<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span lang="EN-US" style="mso-ansi-language: EN-US;"><o:p><span style="font-family: Calibri;"> </span></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span lang="EN-US" style="color: red; mso-ansi-language: EN-US;"><span style="font-family: Calibri;">Still
no success :-s .<o:p></o:p></span></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span lang="EN-US" style="color: red; mso-ansi-language: EN-US;"><o:p><span style="font-family: Calibri;"> </span></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span lang="EN-US" style="mso-ansi-language: EN-US;"><span style="font-family: Calibri;">Finally,
calling the package with the “<b style="mso-bidi-font-weight: normal;">catalog.start_execution</b>”
Stored Procedure, and the reference_id:<o:p></o:p></span></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="EN-US" style="color: blue; font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">Declare</span><span lang="EN-US" style="font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">
@execution_id <span style="color: blue;">bigint</span><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="EN-US" style="color: blue; font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">EXEC</span><span lang="EN-US" style="font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"> [SSISDB]<span style="color: grey;">.</span>[catalog]<span style="color: grey;">.</span>[create_execution]<span style="color: blue;"> </span><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="EN-US" style="color: blue; font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"><span style="mso-tab-count: 1;"> </span></span><span lang="EN-US" style="font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">@package_name<span style="color: grey;">=</span><span style="color: red;">N'DemoUpload.dtsx'</span><span style="color: grey;">,</span> <o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"><span style="mso-tab-count: 1;"> </span>@execution_id<span style="color: grey;">=</span>@execution_id
<span style="color: blue;">OUTPUT</span><span style="color: grey;">,</span> <o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"><span style="mso-tab-count: 1;"> </span>@folder_name<span style="color: grey;">=</span><span style="color: red;">N'ACC'</span><span style="color: grey;">,</span><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"><span style="mso-tab-count: 1;"> </span>@project_name<span style="color: grey;">=</span><span style="color: red;">N'DemoUpload'</span><span style="color: grey;">,</span><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"><span style="mso-tab-count: 1;"> </span>@use32bitruntime<span style="color: grey;">=</span>False<span style="color: grey;">,</span> <o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"><span style="mso-tab-count: 1;"> </span>@reference_id<span style="color: grey;">=</span><span style="background: yellow; mso-highlight: yellow;">79</span><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"><o:p> </o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="EN-US" style="color: blue; font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">Select</span><span lang="EN-US" style="font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">
@execution_id<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="EN-US" style="color: blue; font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">DECLARE</span><span lang="EN-US" style="font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"> @var0 <span style="color: blue;">sql_variant</span> <span style="color: grey;">=</span> <span style="color: red;">N'9616EE37E32A8D9'</span><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="EN-US" style="color: blue; font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">EXEC</span><span lang="EN-US" style="font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"> [SSISDB]<span style="color: grey;">.</span>[catalog]<span style="color: grey;">.</span>[set_execution_parameter_value]<span style="color: blue;"> </span>@execution_id<span style="color: grey;">,</span><span style="mso-spacerun: yes;"> </span>@object_type<span style="color: grey;">=</span>20<span style="color: grey;">,</span> @parameter_name<span style="color: grey;">=</span><span style="color: red;">N'Compatibilitygroup'</span><span style="color: grey;">,</span>
@parameter_value<span style="color: grey;">=</span>@var0<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="EN-US" style="color: blue; font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">DECLARE</span><span lang="EN-US" style="font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"> @var1 <span style="color: blue;">sql_variant</span> <span style="color: grey;">=</span> <span style="color: red;">N'IT'</span><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="EN-US" style="color: blue; font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">EXEC</span><span lang="EN-US" style="font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"> [SSISDB]<span style="color: grey;">.</span>[catalog]<span style="color: grey;">.</span>[set_execution_parameter_value]<span style="color: blue;"> </span>@execution_id<span style="color: grey;">,</span><span style="mso-spacerun: yes;"> </span>@object_type<span style="color: grey;">=</span>20<span style="color: grey;">,</span> @parameter_name<span style="color: grey;">=</span><span style="color: red;">N'Country'</span><span style="color: grey;">,</span>
@parameter_value<span style="color: grey;">=</span>@var1<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="EN-US" style="color: blue; font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">DECLARE</span><span lang="EN-US" style="font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"> @var2 <span style="color: blue;">sql_variant</span> <span style="color: grey;">=</span> <span style="color: red;">N'MyDestinationDB'</span><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="EN-US" style="color: blue; font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">EXEC</span><span lang="EN-US" style="font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"> [SSISDB]<span style="color: grey;">.</span>[catalog]<span style="color: grey;">.</span>[set_execution_parameter_value]<span style="color: blue;"> </span>@execution_id<span style="color: grey;">,</span><span style="mso-spacerun: yes;"> </span>@object_type<span style="color: grey;">=</span>20<span style="color: grey;">,</span> @parameter_name<span style="color: grey;">=</span><span style="color: red;">N'DestinationDb'</span><span style="color: grey;">,</span>
@parameter_value<span style="color: grey;">=</span>@var2<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="EN-US" style="color: blue; font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">DECLARE</span><span lang="EN-US" style="font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"> @var3 <span style="color: blue;">sql_variant</span> <span style="color: grey;">=</span> <span style="color: red;">N'MySQLServer.MyDomain.org'</span><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="EN-US" style="color: blue; font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">EXEC</span><span lang="EN-US" style="font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"> [SSISDB]<span style="color: grey;">.</span>[catalog]<span style="color: grey;">.</span>[set_execution_parameter_value]<span style="color: blue;"> </span>@execution_id<span style="color: grey;">,</span><span style="mso-spacerun: yes;"> </span>@object_type<span style="color: grey;">=</span>20<span style="color: grey;">,</span> @parameter_name<span style="color: grey;">=</span><span style="color: red;">N'DestinationServer'</span><span style="color: grey;">,</span>
@parameter_value<span style="color: grey;">=</span>@var3<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"><o:p> </o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="EN-US" style="color: blue; font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">EXEC</span><span lang="EN-US" style="font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"> [SSISDB]<span style="color: grey;">.</span>[catalog]<span style="color: grey;">.</span>[set_execution_parameter_value]<span style="color: blue;"> </span>@execution_id<span style="color: grey;">,</span><span style="mso-spacerun: yes;"> </span>@object_type<span style="color: grey;">=</span>50<span style="color: grey;">,</span> @parameter_name<span style="color: grey;">=</span><span style="color: red;">N'LOGGING_LEVEL'</span><span style="color: grey;">,</span> @parameter_value<span style="color: grey;">=</span>@var5<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span lang="EN-US" style="color: blue; font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">EXEC</span><span lang="EN-US" style="font-family: Consolas; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"> [SSISDB]<span style="color: grey;">.</span>[catalog]<span style="color: grey;">.</span>[start_execution]<span style="color: blue;"> </span>@execution_id<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="background: rgb(217, 217, 217); line-height: normal; margin: 0cm 0cm 0pt; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-layout-grid-align: none;">
<span style="color: blue; font-family: Consolas; font-size: 8pt; mso-ansi-language: NL-BE; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">GO<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span lang="EN-US" style="mso-ansi-language: EN-US;"><o:p><span style="font-family: Calibri;"> </span></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<b style="mso-bidi-font-weight: normal;"><span lang="EN-US" style="color: #00b050; mso-ansi-language: EN-US;"><span style="font-family: Calibri;">Succeeded!!!!<o:p></o:p></span></span></b></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgPkSZe6OLU4CV15ExV8f7MLKgdQaIF2bWvLeCH2x66wXxb02Qz-2lxGkT-ow54nritkWzlHxTvmMSARqZJlMniQ6DIONIRGGnMukUKnvjBaYZNrYx_jI438V98NAUeRgny9iNVZiUM7SM/s1600/SSIS_5_NoError.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="186" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgPkSZe6OLU4CV15ExV8f7MLKgdQaIF2bWvLeCH2x66wXxb02Qz-2lxGkT-ow54nritkWzlHxTvmMSARqZJlMniQ6DIONIRGGnMukUKnvjBaYZNrYx_jI438V98NAUeRgny9iNVZiUM7SM/s640/SSIS_5_NoError.png" width="640" /></a></div>
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="mso-ansi-language: NL-BE; mso-fareast-language: NL-BE; mso-no-proof: yes;"></span><span lang="EN-US" style="mso-ansi-language: EN-US;"><o:p></o:p></span><br /></div>
</div>
Egon Vandenrijdthttp://www.blogger.com/profile/17366254188434203611noreply@blogger.com0tag:blogger.com,1999:blog-1671249158308857862.post-28644456522904090212015-05-08T12:54:00.001+02:002015-05-08T12:54:19.001+02:00Getting all (calendar)days within a period<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<h1 style="margin: 24pt 0cm 0pt;">
<span lang="EN-US"><span style="font-size: large;"><span style="color: #365f91;"><span style="font-family: Cambria;">Getting all (calendar)days within a period<o:p></o:p></span></span></span></span></h1>
<br />
<h2 style="margin: 10pt 0cm 0pt;">
<span lang="EN-US"><span style="font-size: medium;"><span style="color: #4f81bd;"><span style="font-family: Cambria;">Goal<o:p></o:p></span></span></span></span></h2>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span lang="EN-US"><span style="font-family: Calibri;">Sometimes you need a list with all days
within a specific period, for example to outer join with other tables to have a
complete calendar overview. Here is a simple way to get those days.<o:p></o:p></span></span></div>
<br />
<h2 style="margin: 10pt 0cm 0pt;">
<span lang="EN-US"><span style="font-size: medium;"><span style="color: #4f81bd;"><span style="font-family: Cambria;">Solution<o:p></o:p></span></span></span></span></h2>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<u><span lang="EN-US"><span style="font-family: Calibri;">Create
the Table-Valued Function:<o:p></o:p></span></span></u></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span lang="EN-US" style="color: blue; font-family: Consolas; font-size: 9.5pt;"><o:p> </o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt 36pt; mso-layout-grid-align: none;">
<span lang="EN-US" style="color: blue; font-family: Consolas; font-size: 9.5pt;">CREATE</span><span lang="EN-US" style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">FUNCTION</span> <span style="color: teal;">fnGetCalendarDaysInPeriod</span><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt 36pt; mso-layout-grid-align: none;">
<span lang="EN-US" style="color: grey; font-family: Consolas; font-size: 9.5pt;">(</span><span lang="EN-US" style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt 36pt; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 9.5pt;"><span style="mso-tab-count: 1;"> </span><span style="color: teal;">@StartDate</span>
<span style="color: blue;">DATE</span><span style="color: grey;">,</span><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt 36pt; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 9.5pt;"><span style="mso-tab-count: 1;"> </span><span style="color: teal;">@EndDate</span>
<span style="color: blue;">DATE</span><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt 36pt; mso-layout-grid-align: none;">
<span lang="EN-US" style="color: grey; font-family: Consolas; font-size: 9.5pt;">)</span><span lang="EN-US" style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt 36pt; mso-layout-grid-align: none;">
<span lang="EN-US" style="color: blue; font-family: Consolas; font-size: 9.5pt;">RETURNS</span><span lang="EN-US" style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">TABLE</span><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt 36pt; mso-layout-grid-align: none;">
<span lang="EN-US" style="color: blue; font-family: Consolas; font-size: 9.5pt;">AS</span><span lang="EN-US" style="font-family: Consolas; font-size: 9.5pt;"> <o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt 36pt; mso-layout-grid-align: none;">
<span lang="EN-US" style="color: blue; font-family: Consolas; font-size: 9.5pt;">RETURN</span><span lang="EN-US" style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt 36pt; mso-layout-grid-align: none;">
<span lang="EN-US" style="color: grey; font-family: Consolas; font-size: 9.5pt;">(</span><span lang="EN-US" style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt 36pt; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 9.5pt;"><span style="mso-tab-count: 1;"> </span><span style="color: blue;">WITH</span>
<span style="color: teal;">mycte</span> <span style="color: blue;">AS</span><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt 36pt; mso-layout-grid-align: none;">
<span lang="EN-US" style="color: blue; font-family: Consolas; font-size: 9.5pt;"><span style="mso-tab-count: 1;"> </span></span><span lang="EN-US" style="color: grey; font-family: Consolas; font-size: 9.5pt;">(</span><span lang="EN-US" style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt 36pt; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 9.5pt;"><span style="mso-tab-count: 1;"> </span><span style="mso-spacerun: yes;"> </span><span style="color: blue;">SELECT</span> <span style="color: magenta;">CAST</span><span style="color: grey;">(</span><span style="color: teal;">@StartDate</span> <span style="color: blue;">AS</span> <span style="color: blue;">DATE</span><span style="color: grey;">)</span> <span style="color: teal;">DateValue</span><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt 36pt; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 9.5pt;"><span style="mso-tab-count: 1;"> </span><span style="mso-spacerun: yes;"> </span><span style="color: blue;">UNION</span> <span style="color: grey;">ALL</span><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt 36pt; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 9.5pt;"><span style="mso-tab-count: 1;"> </span><span style="mso-spacerun: yes;"> </span><span style="color: blue;">SELECT</span><span style="mso-spacerun: yes;"> </span><span style="color: magenta;">DATEADD</span><span style="color: grey;">(</span><span style="color: teal;">dd</span><span style="color: grey;">,</span> 1<span style="color: grey;">,</span> <span style="color: teal;">DateValue</span><span style="color: grey;">)</span><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt 36pt; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 9.5pt;"><span style="mso-tab-count: 1;"> </span><span style="mso-spacerun: yes;"> </span><span style="color: blue;">FROM</span><span style="mso-spacerun: yes;"> </span><span style="color: teal;">mycte</span><span style="mso-spacerun: yes;"> </span><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt 36pt; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 9.5pt;"><span style="mso-tab-count: 1;"> </span><span style="mso-spacerun: yes;"> </span><span style="color: blue;">WHERE</span><span style="mso-spacerun: yes;"> </span><span style="color: magenta;">DATEADD</span><span style="color: grey;">(</span><span style="color: teal;">dd</span><span style="color: grey;">,</span> 1<span style="color: grey;">,</span> <span style="color: teal;">DateValue</span><span style="color: grey;">)</span> <span style="color: grey;"><</span> <span style="color: teal;">@EndDate</span><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt 36pt; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 9.5pt;"><span style="mso-tab-count: 1;"> </span><span style="color: grey;">)</span><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt 36pt; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 9.5pt;"><o:p> </o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt 36pt; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 9.5pt;"><span style="mso-tab-count: 1;"> </span><span style="color: blue;">SELECT</span><span style="mso-spacerun: yes;"> </span><span style="color: teal;">DateValue</span><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt 36pt; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 9.5pt;"><span style="mso-tab-count: 1;"> </span><span style="color: blue;">FROM</span><span style="mso-spacerun: yes;"> </span><span style="color: teal;">mycte</span><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt 36pt; mso-layout-grid-align: none;">
<span lang="EN-US" style="color: grey; font-family: Consolas; font-size: 9.5pt;">)<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span lang="EN-US"><o:p><span style="font-family: Calibri;"> </span></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<u><span lang="EN-US"><span style="font-family: Calibri;">The Function CALL:<o:p></o:p></span></span></u></div>
<br />
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt 36pt; mso-layout-grid-align: none;">
<span lang="EN-US" style="color: blue; font-family: Consolas; font-size: 9.5pt;">DECLARE</span><span lang="EN-US" style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: teal;">@StartDate</span> <span style="color: blue;">DATE</span><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt 36pt; mso-layout-grid-align: none;">
<span lang="EN-US" style="color: blue; font-family: Consolas; font-size: 9.5pt;">DECLARE</span><span lang="EN-US" style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: teal;">@EndDate</span> <span style="color: blue;">DATE</span><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt 36pt; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 9.5pt;"><o:p> </o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt 36pt; mso-layout-grid-align: none;">
<span lang="EN-US" style="color: blue; font-family: Consolas; font-size: 9.5pt;">SET</span><span lang="EN-US" style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: teal;">@StartDate</span> <span style="color: grey;">=</span> <span style="color: magenta;">DATEADD</span><span style="color: grey;">(</span><span style="color: teal;">yy</span><span style="color: grey;">,</span> <span style="color: grey;">-</span>2<span style="color: grey;">,</span> <span style="color: magenta;">GETDATE</span><span style="color: grey;">())</span><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt 36pt; mso-layout-grid-align: none;">
<span lang="EN-US" style="color: blue; font-family: Consolas; font-size: 9.5pt;">SET</span><span lang="EN-US" style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: teal;">@EndDate</span> <span style="color: grey;">=</span> <span style="color: magenta;">GETDATE</span><span style="color: grey;">()</span><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt 36pt; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 9.5pt;"><o:p> </o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt 36pt; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 9.5pt;"><o:p> </o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt 36pt; mso-layout-grid-align: none;">
<span lang="EN-US" style="color: blue; font-family: Consolas; font-size: 9.5pt;">SELECT</span><span lang="EN-US" style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: grey;">*</span><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt 36pt; mso-layout-grid-align: none;">
<span lang="EN-US" style="color: blue; font-family: Consolas; font-size: 9.5pt;">FROM</span><span lang="EN-US" style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: teal;">fnGetCalendarDaysInPeriod</span><span style="color: blue;"> </span><span style="color: grey;">(</span><span style="color: teal;">@StartDate</span><span style="color: grey;">,</span><span style="color: teal;">@EndDate</span><span style="color: grey;">)</span><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt 36pt; mso-layout-grid-align: none;">
<span lang="EN-US" style="color: blue; font-family: Consolas; font-size: 9.5pt;">OPTION </span><span lang="EN-US" style="color: grey; font-family: Consolas; font-size: 9.5pt;">(</span><span lang="EN-US" style="color: teal; font-family: Consolas; font-size: 9.5pt;">MAXRECURSION</span><span lang="EN-US" style="font-family: Consolas; font-size: 9.5pt;"> 0<span style="color: grey;">)</span><o:p></o:p></span></div>
<br />
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<b><span lang="EN-US" style="color: #4f81bd; font-family: "Cambria","serif"; font-size: 13pt; line-height: 115%; mso-ascii-theme-font: major-latin; mso-bidi-font-family: "Times New Roman"; mso-bidi-theme-font: major-bidi; mso-fareast-font-family: "Times New Roman"; mso-fareast-theme-font: major-fareast; mso-hansi-theme-font: major-latin; mso-themecolor: accent1;">Note<o:p></o:p></span></b></div>
<br />
<div class="MsoListParagraph" style="margin: 0cm 0cm 10pt 36pt;">
<span lang="EN-US"><span style="font-family: Calibri;">Use the Option “<b style="mso-bidi-font-weight: normal;">MAXRECURSION 0</b>”, otherwise you will exceed the limit of maximum 100
recursions (Default Value).<span style="mso-spacerun: yes;"> </span>The “<b style="mso-bidi-font-weight: normal;">OPTION</b>” clause can be used only at the
statement level, so you can’t put this in a “RETURN”.<o:p></o:p></span></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span lang="EN-US" style="font-size: 10pt; line-height: 115%;"><o:p><span style="font-family: Calibri;"> </span></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<u><span lang="EN-US"><span style="font-family: Calibri;">Workaround MAXRECURSION problem:<o:p></o:p></span></span></u></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt 36pt;">
<span lang="EN-US"><span style="font-family: Calibri;">Return a Table
Variable so you can put the “<b style="mso-bidi-font-weight: normal;">OPTION(MAXRECURSION)</b>”
in the “<b style="mso-bidi-font-weight: normal;">INSERT INTO</b>” statement<o:p></o:p></span></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt 36pt; mso-layout-grid-align: none;">
<span lang="EN-US" style="color: blue; font-family: Consolas; font-size: 9.5pt;">CREATE</span><span lang="EN-US" style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">FUNCTION</span> <span style="color: teal;">[dbo]</span><span style="color: grey;">.</span><span style="color: teal;">[fnGetCalendarDaysInPeriod]</span><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt 36pt; mso-layout-grid-align: none;">
<span lang="EN-US" style="color: grey; font-family: Consolas; font-size: 9.5pt;">(</span><span lang="EN-US" style="font-family: Consolas; font-size: 9.5pt;"><span style="mso-spacerun: yes;"> </span><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt 36pt; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 9.5pt;"><span style="mso-spacerun: yes;"> </span><span style="color: teal;">@StartDate</span><span style="mso-spacerun: yes;"> </span><span style="color: blue;">DATETIME</span><span style="color: grey;">,</span><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt 36pt; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 9.5pt;"><span style="mso-spacerun: yes;"> </span><span style="color: teal;">@EndDate</span><span style="mso-spacerun: yes;"> </span><span style="color: blue;">DATETIME</span><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt 36pt; mso-layout-grid-align: none;">
<span lang="EN-US" style="color: grey; font-family: Consolas; font-size: 9.5pt;">)</span><span lang="EN-US" style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt 36pt; mso-layout-grid-align: none;">
<span lang="EN-US" style="color: blue; font-family: Consolas; font-size: 9.5pt;">RETURNS</span><span lang="EN-US" style="font-family: Consolas; font-size: 9.5pt;"><span style="mso-spacerun: yes;"> </span><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt 36pt; mso-layout-grid-align: none;">
<span lang="EN-US" style="color: teal; font-family: Consolas; font-size: 9.5pt;">@SelectedRange</span><span lang="EN-US" style="font-family: Consolas; font-size: 9.5pt;"><span style="mso-spacerun: yes;"> </span><span style="color: blue;">TABLE</span> <o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt 36pt; mso-layout-grid-align: none;">
<span lang="EN-US" style="color: grey; font-family: Consolas; font-size: 9.5pt;">(</span><span lang="EN-US" style="color: teal; font-family: Consolas; font-size: 9.5pt;">IndividualDate</span><span lang="EN-US" style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">DATETIME</span><span style="color: grey;">)</span><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt 36pt; mso-layout-grid-align: none;">
<span lang="EN-US" style="color: blue; font-family: Consolas; font-size: 9.5pt;">AS</span><span lang="EN-US" style="font-family: Consolas; font-size: 9.5pt;"> <o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt 36pt; mso-layout-grid-align: none;">
<span lang="EN-US" style="color: blue; font-family: Consolas; font-size: 9.5pt;">BEGIN</span><span lang="EN-US" style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt 36pt; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 9.5pt;"><span style="mso-tab-count: 1;"> </span><span style="color: blue;">WITH</span>
<span style="color: teal;">mycte</span> <span style="color: blue;">AS</span><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt 36pt; mso-layout-grid-align: none;">
<span lang="EN-US" style="color: blue; font-family: Consolas; font-size: 9.5pt;"><span style="mso-tab-count: 1;"> </span></span><span lang="EN-US" style="color: grey; font-family: Consolas; font-size: 9.5pt;">(</span><span lang="EN-US" style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt 36pt; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 9.5pt;"><span style="mso-tab-count: 1;"> </span><span style="mso-spacerun: yes;"> </span><span style="color: blue;">SELECT</span> <span style="color: magenta;">CAST</span><span style="color: grey;">(</span><span style="color: teal;">@StartDate</span> <span style="color: blue;">AS</span> <span style="color: blue;">DATE</span><span style="color: grey;">)</span> <span style="color: teal;">DateValue</span><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt 36pt; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 9.5pt;"><span style="mso-tab-count: 1;"> </span><span style="mso-spacerun: yes;"> </span><span style="color: blue;">UNION</span> <span style="color: grey;">ALL</span><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt 36pt; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 9.5pt;"><span style="mso-tab-count: 1;"> </span><span style="mso-spacerun: yes;"> </span><span style="color: blue;">SELECT</span><span style="mso-spacerun: yes;"> </span><span style="color: magenta;">DATEADD</span><span style="color: grey;">(</span><span style="color: teal;">dd</span><span style="color: grey;">,</span> 1<span style="color: grey;">,</span> <span style="color: teal;">DateValue</span><span style="color: grey;">)</span><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt 36pt; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 9.5pt;"><span style="mso-tab-count: 1;"> </span><span style="mso-spacerun: yes;"> </span><span style="color: blue;">FROM</span><span style="mso-spacerun: yes;"> </span><span style="color: teal;">mycte</span><span style="mso-spacerun: yes;"> </span><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt 36pt; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 9.5pt;"><span style="mso-tab-count: 1;"> </span><span style="mso-spacerun: yes;"> </span><span style="color: blue;">WHERE</span><span style="mso-spacerun: yes;"> </span><span style="color: magenta;">DATEADD</span><span style="color: grey;">(</span><span style="color: teal;">dd</span><span style="color: grey;">,</span> 1<span style="color: grey;">,</span> <span style="color: teal;">DateValue</span><span style="color: grey;">)</span> <span style="color: grey;"><</span> <span style="color: teal;">@EndDate</span><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt 36pt; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 9.5pt;"><span style="mso-tab-count: 1;"> </span><span style="color: grey;">)</span><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt 36pt; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 9.5pt;"><o:p> </o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt 36pt; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 9.5pt;"><span style="mso-tab-count: 1;"> </span><span style="color: blue;">INSERT</span>
<span style="color: blue;">INTO</span> <span style="color: teal;">@SelectedRange</span><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt 36pt; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 9.5pt;"><span style="mso-tab-count: 1;"> </span><span style="color: blue;">SELECT</span><span style="mso-spacerun: yes;"> </span><span style="color: teal;">DateValue</span><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt 36pt; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 9.5pt;"><span style="mso-tab-count: 1;"> </span><span style="color: blue;">FROM</span><span style="mso-spacerun: yes;"> </span><span style="color: teal;">mycte</span><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt 36pt; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 9.5pt;"><span style="mso-tab-count: 1;"> </span><span style="color: blue;">OPTION
</span><span style="color: grey;">(</span><span style="color: teal;">MAXRECURSION</span>
0<span style="color: grey;">);</span><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt 36pt; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 9.5pt;"><o:p> </o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt 36pt; mso-layout-grid-align: none;">
<span lang="EN-US" style="font-family: Consolas; font-size: 9.5pt;"><span style="mso-spacerun: yes;"> </span><span style="color: blue;">RETURN</span><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt 36pt; mso-layout-grid-align: none;">
<span lang="EN-US" style="color: blue; font-family: Consolas; font-size: 9.5pt;">END</span><span lang="EN-US" style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt 36pt; mso-layout-grid-align: none;">
<span lang="EN-US" style="color: blue; font-family: Consolas; font-size: 9.5pt;">GO<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt 36pt; mso-layout-grid-align: none;">
<span lang="EN-US" style="color: blue; font-family: Consolas; font-size: 9.5pt;"><o:p> </o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt 36pt;">
<span lang="EN-US"><span style="font-family: Calibri;">This will make
life much easier for the Developers; they don’t have to care about the “<b style="mso-bidi-font-weight: normal;">MAXRECURSION</b>” anymore.<o:p></o:p></span></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<b><span lang="EN-US" style="color: #4f81bd; font-family: "Cambria","serif"; font-size: 13pt; line-height: 115%; mso-ascii-theme-font: major-latin; mso-bidi-font-family: "Times New Roman"; mso-bidi-theme-font: major-bidi; mso-fareast-font-family: "Times New Roman"; mso-fareast-theme-font: major-fareast; mso-hansi-theme-font: major-latin; mso-themecolor: accent1;">Note 2<o:p></o:p></span></b></div>
<span lang="EN-US" style="font-family: "Calibri","sans-serif"; font-size: 11pt; 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;">Using a <b>table valued function</b> can be bad
for performance due to the fact that it's treated like a table except there are
no statistics available for SQL Server to base a good execution plan on.
SQLServer will estimate the function as returning a very small number of rows.
If it returns a larger number of rows, then therefore the plan generated could
be a lot less than optimal.</span></div>
Egon Vandenrijdthttp://www.blogger.com/profile/17366254188434203611noreply@blogger.com0tag:blogger.com,1999:blog-1671249158308857862.post-37925045069633485772015-05-08T12:51:00.004+02:002015-05-08T12:51:39.359+02:00TLog impact when Rebuilding SQLServer Indexes<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<div align="center" class="MsoNormal" style="margin: 0cm 0cm 10pt; text-align: center;">
<b style="mso-bidi-font-weight: normal;"><u><span lang="EN-US" style="mso-ansi-language: EN-US;"><span style="font-family: Calibri;">Let’s talk about
Rebuilding SQLServer Indexes and TLog<o:p></o:p></span></span></u></b></div>
<br />
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<u><span lang="EN-US" style="color: #444444; mso-ansi-language: EN-US;"><span style="font-family: Calibri;">First some footnotes:<o:p></o:p></span></span></u></div>
<br />
<div class="MsoListParagraphCxSpFirst" style="margin: 0cm 0cm 0pt 36pt; mso-list: l0 level1 lfo1; text-indent: -18pt;">
<span lang="EN-US" style="color: #444444; font-family: "Arial","sans-serif"; font-size: 9.5pt; line-height: 115%; mso-ansi-language: EN-US; mso-bidi-font-size: 8.0pt; mso-fareast-font-family: Arial;"><span style="mso-list: Ignore;">-<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";">
</span></span></span><b style="mso-bidi-font-weight: normal;"><span lang="EN-US" style="color: #444444; font-family: "Arial","sans-serif"; font-size: 8pt; line-height: 115%; mso-ansi-language: EN-US;">Rebuilding</span></b><span lang="EN-US" style="color: #444444; font-family: "Arial","sans-serif"; font-size: 8pt; line-height: 115%; mso-ansi-language: EN-US;"> indexes <b style="mso-bidi-font-weight: normal;">online</b> is only available in <b style="mso-bidi-font-weight: normal;">Enterprise</b>
(or Developer) edition, so reorganizing is the only online option on Standard
Edition. <o:p></o:p></span></div>
<br />
<div class="MsoListParagraphCxSpMiddle" style="margin: 0cm 0cm 0pt 36pt; mso-list: l0 level1 lfo1; text-indent: -18pt;">
<span lang="EN-US" style="color: #444444; font-family: "Arial","sans-serif"; font-size: 9.5pt; line-height: 115%; mso-ansi-language: EN-US; mso-bidi-font-size: 8.0pt; mso-fareast-font-family: Arial;"><span style="mso-list: Ignore;">-<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";">
</span></span></span><b style="mso-bidi-font-weight: normal;"><span lang="EN-US" style="color: #444444; font-family: "Arial","sans-serif"; font-size: 8pt; line-height: 115%; mso-ansi-language: EN-US;">Reorganizing</span></b><span lang="EN-US" style="color: #444444; font-family: "Arial","sans-serif"; font-size: 8pt; line-height: 115%; mso-ansi-language: EN-US;"> indexes is always online and it doesn’t
require a schema mod lock, so it can provide better concurrency.<o:p></o:p></span></div>
<br />
<div class="MsoListParagraphCxSpMiddle" style="margin: 0cm 0cm 0pt 36pt; mso-list: l0 level1 lfo1; text-indent: -18pt;">
<span lang="EN-US" style="color: #444444; font-family: "Arial","sans-serif"; font-size: 9.5pt; line-height: 115%; mso-ansi-language: EN-US; mso-bidi-font-size: 8.0pt; mso-fareast-font-family: Arial;"><span style="mso-list: Ignore;">-<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";">
</span></span></span><b style="mso-bidi-font-weight: normal;"><span lang="EN-US" style="color: #444444; font-family: "Arial","sans-serif"; font-size: 8pt; line-height: 115%; mso-ansi-language: EN-US;">Reorganizing</span></b><span lang="EN-US" style="color: #444444; font-family: "Arial","sans-serif"; font-size: 8pt; line-height: 115%; mso-ansi-language: EN-US;"> only defragments the leaf level of the
index. On large tables it will take longer than a rebuild would take. But it’s
nice that you can reorganize for a while and then stop without facing a massive
rollback.<o:p></o:p></span></div>
<br />
<div class="MsoListParagraphCxSpMiddle" style="margin: 0cm 0cm 0pt 36pt; mso-list: l0 level1 lfo1; text-indent: -18pt;">
<span lang="EN-US" style="color: #444444; font-family: "Arial","sans-serif"; font-size: 9.5pt; line-height: 115%; mso-ansi-language: EN-US; mso-bidi-font-size: 8.0pt; mso-fareast-font-family: Arial;"><span style="mso-list: Ignore;">-<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";">
</span></span></span><span lang="EN-US" style="color: #444444; font-family: "Arial","sans-serif"; font-size: 8pt; line-height: 115%; mso-ansi-language: EN-US;">For <b style="mso-bidi-font-weight: normal;">partitioned indexes</b> built
on a partition scheme, you can use either of these methods (Rebuild/reorganize)
on a complete index or a single partition of an index. Unfortunately, <b style="mso-bidi-font-weight: normal;">partition</b> level rebuilds are <b style="mso-bidi-font-weight: normal;">offline</b> until </span><a href="http://blogs.technet.com/b/dataplatforminsider/archive/2013/08/16/improved-application-availability-during-online-operations-in-sql-server-2014.aspx" target="_blank"><span style="color: #444444; font-family: "Arial","sans-serif"; font-size: 8pt; line-height: 115%;">SQL Server 2014</span></a><span lang="EN-US" style="color: #444444; font-family: "Arial","sans-serif"; font-size: 8pt; line-height: 115%; mso-ansi-language: EN-US;">.<o:p></o:p></span></div>
<br />
<div class="MsoListParagraphCxSpMiddle" style="margin: 0cm 0cm 0pt 36pt; mso-list: l0 level1 lfo1; text-indent: -18pt;">
<span lang="EN-US" style="color: #444444; font-family: "Arial","sans-serif"; font-size: 9.5pt; line-height: 115%; mso-ansi-language: EN-US; mso-bidi-font-size: 8.0pt; mso-fareast-font-family: Arial;"><span style="mso-list: Ignore;">-<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";">
</span></span></span><span lang="EN-US" style="color: #444444; font-family: "Arial","sans-serif"; font-size: 8pt; line-height: 115%; mso-ansi-language: EN-US;">A rebuild of an index needs to be done when there is no (/less) activity
to avoid locking issues. <o:p></o:p></span></div>
<br />
<div class="MsoListParagraphCxSpLast" style="margin: 0cm 0cm 10pt 36pt;">
<span lang="EN-US" style="color: #444444; font-family: "Arial","sans-serif"; font-size: 8pt; line-height: 115%; mso-ansi-language: EN-US;"><o:p><span style="font-family: Times New Roman; font-size: small;"> </span></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<u><span lang="EN-US" style="color: #444444; mso-ansi-language: EN-US;"><span style="font-family: Calibri;">The facts<o:p></o:p></span></span></u></div>
<br />
<span lang="EN-US" style="color: #444444; font-family: "Arial","sans-serif"; font-size: 8pt; mso-ansi-language: EN-US; mso-fareast-font-family: Calibri; mso-fareast-language: EN-US; mso-fareast-theme-font: minor-latin;">When I was at one of our
customers a few weeks ago, they had some questions about the rebuild index
process. On some servers it caused big TLog growths, and on other servers, it
didn’t. So I did a quick investigation on the differences in versions and
options. These are my findings:<o:p></o:p></span><br />
<br />
<div class="MsoListParagraphCxSpFirst" style="margin: 0cm 0cm 0pt 36pt; mso-list: l0 level1 lfo1; text-indent: -18pt;">
<span lang="EN-US" style="color: #444444; font-family: "Arial","sans-serif"; font-size: 9.5pt; line-height: 115%; mso-ansi-language: EN-US; mso-bidi-font-size: 8.0pt; mso-fareast-font-family: Arial;"><span style="mso-list: Ignore;">-<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";">
</span></span></span><span lang="EN-US" style="color: #444444; font-family: "Arial","sans-serif"; font-size: 8pt; line-height: 115%; mso-ansi-language: EN-US;">Rebuilding an index needs enough space to create the new index. A
simplified rule of thumb seems to be that you need about 120% of the space used
by the original index. This may be in the database or in tempdb, depending on
whether <b style="mso-bidi-font-weight: normal;">SORT_IN_TEMPDB</b> is ON or OFF.
If possible, have SORT_IN_TEMPDB = ON this will reduce some of the logging that
is done.<o:p></o:p></span></div>
<br />
<div class="MsoListParagraphCxSpMiddle" style="margin: 0cm 0cm 0pt 36pt; mso-list: l0 level1 lfo1; text-indent: -18pt;">
<span lang="EN-US" style="color: #444444; font-family: "Arial","sans-serif"; font-size: 9.5pt; line-height: 115%; mso-ansi-language: EN-US; mso-bidi-font-size: 8.0pt; mso-fareast-font-family: Arial;"><span style="mso-list: Ignore;">-<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";">
</span></span></span><span lang="EN-US" style="color: #444444; font-family: "Arial","sans-serif"; font-size: 8pt; line-height: 115%; mso-ansi-language: EN-US;">If you rebuild all of the indexes between LOG backups, then all of the
logs for reindexing all of the indexes will be in the log file. Therefore,
major reorganization needs to have the appropriate resources of disk space, log
space, and so on. A solution can be to reorganizing one table at a time and
doing a log backup after each.<o:p></o:p></span></div>
<br />
<div class="MsoListParagraphCxSpMiddle" style="margin: 0cm 0cm 0pt 36pt; mso-list: l0 level1 lfo1; text-indent: -18pt;">
<span lang="EN-US" style="color: #444444; font-family: "Arial","sans-serif"; font-size: 9.5pt; line-height: 115%; mso-ansi-language: EN-US; mso-bidi-font-size: 8.0pt; mso-fareast-font-family: Arial;"><span style="mso-list: Ignore;">-<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";">
</span></span></span><span lang="EN-US" style="color: #444444; font-family: "Arial","sans-serif"; font-size: 8pt; line-height: 115%; mso-ansi-language: EN-US;">You could change to a minimally logged recovery model such as SIMPLE or
BULK_LOGGED during the Index Rebuild, but be aware of the impact of that
process on your production databases. Make sure that your important database is
back in FULL recovery after the rebuild process, no matter what happened.<o:p></o:p></span></div>
<br />
<div class="MsoListParagraphCxSpMiddle" style="margin: 0cm 0cm 0pt 36pt; mso-list: l0 level1 lfo1; text-indent: -18pt;">
<span lang="EN-US" style="color: #444444; font-family: "Arial","sans-serif"; font-size: 9.5pt; line-height: 115%; mso-ansi-language: EN-US; mso-bidi-font-size: 8.0pt; mso-fareast-font-family: Arial;"><span style="mso-list: Ignore;">-<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";">
</span></span></span><span lang="EN-US" style="color: #444444; font-family: "Arial","sans-serif"; font-size: 8pt; line-height: 115%; mso-ansi-language: EN-US;">A log file can be shrunk, but only after the high order pages are freed
by a log backup. This is usually a cycle of backup log, DBCC SHRINKFILE and then
check the space and try again.<o:p></o:p></span></div>
<br />
<div class="MsoListParagraphCxSpMiddle" style="margin: 0cm 0cm 0pt 36pt; mso-list: l0 level1 lfo1; text-indent: -18pt;">
<span lang="EN-US" style="color: #444444; font-family: "Arial","sans-serif"; font-size: 9.5pt; line-height: 115%; mso-ansi-language: EN-US; mso-bidi-font-size: 8.0pt; mso-fareast-font-family: Arial;"><span style="mso-list: Ignore;">-<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";">
</span></span></span><span lang="EN-US" style="color: #444444; font-family: "Arial","sans-serif"; font-size: 8pt; line-height: 115%; mso-ansi-language: EN-US;">In <b style="mso-bidi-font-weight: normal;">SQLServer 2008 (or later
versions</b>), the transaction log for the database <b style="mso-bidi-font-weight: normal;">expands at a much higher rate</b> than in the older version (SQLServer
2005 or before) for the same operation.<br />
As as a result of the increased transaction log size, utilities such as log
shipping, database mirroring, transaction log backups, and transactional
replication may run slower than they used to in SQL Server 2005.<o:p></o:p></span></div>
<br />
<div class="MsoListParagraphCxSpLast" style="margin: 0cm 0cm 10pt 36pt;">
<span lang="EN-US" style="color: #444444; font-family: "Arial","sans-serif"; font-size: 8pt; line-height: 115%; mso-ansi-language: EN-US;"><o:p><span style="font-family: Times New Roman; font-size: small;"> </span></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<u><span lang="EN-US" style="color: #444444; mso-ansi-language: EN-US;"><span style="font-family: Calibri;">Possible resolutions:<o:p></o:p></span></span></u></div>
<br />
<div class="MsoListParagraphCxSpFirst" style="margin: 0cm 0cm 0pt 36pt; mso-list: l0 level1 lfo1; text-indent: -18pt;">
<span lang="EN-US" style="color: #444444; font-family: "Arial","sans-serif"; font-size: 9.5pt; line-height: 115%; mso-ansi-language: EN-US; mso-bidi-font-size: 8.0pt; mso-fareast-font-family: Arial;"><span style="mso-list: Ignore;">-<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";">
</span></span></span><span lang="EN-US" style="color: #444444; font-family: "Arial","sans-serif"; font-size: 8pt; line-height: 115%; mso-ansi-language: EN-US;">Consider reducing the frequency of online index rebuild operations. To
ensure optimal index usage and performance, update statistics more frequently
rather than reorganizing the indexes. Also, if query plan stability is an
issue, consider using plan guides or other techniques for tuning performance.
Please refer to the following links for further information.<o:p></o:p></span></div>
<br />
<div class="MsoListParagraphCxSpLast" style="margin: 0cm 0cm 10pt 36pt; mso-list: l0 level1 lfo1; text-indent: -18pt;">
<span lang="EN-US" style="color: #444444; font-family: "Arial","sans-serif"; font-size: 9.5pt; line-height: 115%; mso-ansi-language: EN-US; mso-bidi-font-size: 8.0pt; mso-fareast-font-family: Arial;"><span style="mso-list: Ignore;">-<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";">
</span></span></span><span lang="EN-US" style="color: #444444; font-family: "Arial","sans-serif"; font-size: 8pt; line-height: 115%; mso-ansi-language: EN-US;">You can also consider rebuilding the indexes offline.To rebuild indexes
offline, the ALTER INDEX statements have the ONLINE option set to OFF.<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span lang="EN-US" style="mso-ansi-language: EN-US;"><o:p> </o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span lang="EN-US" style="color: #444444; font-family: "Arial","sans-serif"; font-size: 8pt; line-height: 115%; mso-ansi-language: EN-US;">More
information about the TLog space for Index operations can be found at:<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt; text-indent: 35.4pt;">
<a href="http://msdn.microsoft.com/en-us/library/ms184246.aspx"><span lang="EN-US" style="mso-ansi-language: EN-US;"><span style="color: #0096ce; font-family: Calibri;">http://msdn.microsoft.com/en-us/library/ms184246.aspx</span></span></a><span lang="EN-US" style="mso-ansi-language: EN-US;"><o:p></o:p></span></div>
</div>
Egon Vandenrijdthttp://www.blogger.com/profile/17366254188434203611noreply@blogger.com0tag:blogger.com,1999:blog-1671249158308857862.post-88809598697999853902013-09-09T14:15:00.000+02:002013-09-09T14:16:39.925+02:00SQLServer Page restore (SQLServer 2005 and later)<div dir="ltr" style="text-align: left;" trbidi="on">
<h4 style="text-align: left;">
Page Restore (SQLServer 2005 and later):</h4>
<br />
A page restore is intended for repairing isolated damaged pages. Restoring and recovering a few individual pages might be faster than a file restore, reducing the amount of data that is offline during a restore operation. However, if you have to restore more than a few pages in a file, it is generally more efficient to restore the whole file. <br />
<br />
<h4 class="subHeading" style="text-align: left;">
Limitations and Restrictions</h4>
<div class="subsection">
<br /></div>
<div class="subsection">
<ul>
<li>Page restore applies to SQL Server databases that are using the <strong>full or bulk-logged</strong> recovery models. Page restore is supported only for<strong> read/write filegroups</strong>.</li>
<li><strong>Only database pages</strong> can be restored. <strong>Page restore cannot</strong> be used to restore the following:</li>
<ul>
<li>Transaction log</li>
<li>Allocation pages: Global Allocation Map (GAM) pages, Shared Global Allocation Map (SGAM) pages, and Page Free Space (PFS) pages. </li>
<li>Page 0 of all data files (the file boot page)</li>
<li>Page 1:9 (the database boot page)</li>
<li>Full-text catalog</li>
</ul>
<li>Online: Enterprise Edition</li>
<li>Offline: all other editions</li>
</ul>
</div>
<br />
<ul>
</ul>
<h4 style="text-align: left;">
Performing a page Restore (scenario)</h4>
1) Try to find the damaged paged, if you have a lot of damaged pages, perform a full restore<br />
<div style="text-align: left;">
</div>
<ul style="text-align: left;">
<li><span lang="EN-US" style="font-family: "Arial","sans-serif"; font-size: 7.5pt; line-height: 107%; mso-ansi-language: EN-US;">DBCC CHECKDB</span></li>
</ul>
<div style="text-align: left;">
</div>
2) Restore page(s) (from Full, File or Filegroup Backup)<br />
<ul style="text-align: left;">
<li> <span style="font-family: Arial;"><span style="font-size: xx-small;">USE master <br /> GO <br /><span class="style18"> RESTORE DATABASE</span> MyDb </span><span class="style29"><span style="font-size: xx-small;">PAGE = </span><span class="style25"><span style="font-size: xx-small;">'1:345' </span></span></span></span><span style="font-size: xx-small;"><br /><span style="font-family: Arial;"> <span class="style18">FROM DISK</span> = <span class="style25">N'E:\Backup\MyDb.bak' </span><br /> GO </span></span></li>
</ul>
<em>Note: you cannot restore additional tlogs afterwards</em><br />
<br />
<br />
4) Perform a taillog Backup<br />
<ul>
<li><div align="left" class="style19">
<span style="font-family: Arial; font-size: xx-small;"><span style="color: black;"><span style="color: blue;"><span lang="EN-US" style="font-family: "Arial","sans-serif"; font-size: 7.5pt; mso-ansi-language: EN-US;"><span style="color: black;">BACKUP LOG MyDb<br />
TO DISK = N'E:\Backup\MyDb_log.TRN'
<br />
WITH INIT, NO_TRUNCATE, <br />
STATS = 10 <br />
GO <o:p></o:p></span></span><span style="color: black; font-family: Times New Roman; font-size: small;">
</span></span></span> </span></div>
</li>
</ul>
4) Restore the Taillog Backup<br />
<ul>
<li>
<div class="MsoNormal" style="margin: 0cm 0cm 8pt;">
<span lang="EN-US" style="font-family: "Arial","sans-serif"; font-size: 7.5pt; line-height: 107%; mso-ansi-language: EN-US;">RESTORE LOG MyDb
<br />
FROM DISK = N'E:\Backup\MyDb_log.TRN'<br />
WITH RECOVERY, <br />
</span><span lang="EN-US" style="font-size: 7.5pt; line-height: 107%; mso-ansi-language: EN-US;"><span style="font-family: Calibri;">STATS</span></span><span lang="EN-US" style="font-family: "Arial","sans-serif"; font-size: 7.5pt; line-height: 107%; mso-ansi-language: EN-US;"> = 10
<br />
GO <o:p></o:p></span></div>
</li>
</ul>
<span style="font-family: Arial; font-size: xx-small;"></span><br />
<div align="left" class="style19">
<span style="font-family: Arial; font-size: xx-small;"><span style="color: black;"> </span></span></div>
</div>
Egon Vandenrijdthttp://www.blogger.com/profile/17366254188434203611noreply@blogger.com0tag:blogger.com,1999:blog-1671249158308857862.post-59621341308452368182009-08-11T16:58:00.000+02:002009-08-11T17:12:02.104+02:00SQL Traces mixing SQLServer2005 - 2008Today was expiriencing some problem opening a Trace File:<br /><br /><strong>Unable to locate trace definition file Microsoft SQL Server TraceDefinition 10.0.0.xml for open trace file... </strong>It took me more than 10 min to figure out that the server I was profiling (Using SQLServer 2005 profiler) was a SQLServer 2008. :-p<br /><br />So you can trace a SQLServer 2008 using a SQLServer 2005 Profiler, but you cannot re-open the same traces using a SQLServer 2005 profiler, you have to use a SQLServer 2008 Profiler instead!<br /><br />Or if you don't have the 2008 tools installed yet on your locale machine, you can use:<br /><br />select * <br />from fn_trace_gettable('\\share\...\TestTrc.trc',default)<br /><br />It works fine on both SQLServer 2005 and SQLServer2008 SSMS to connect to a SQLServer2005 or SQLServer2008 trace.Egon Vandenrijdthttp://www.blogger.com/profile/17366254188434203611noreply@blogger.com0tag:blogger.com,1999:blog-1671249158308857862.post-82535253159941256972009-08-07T13:22:00.000+02:002009-08-07T14:02:54.227+02:00Fatal error 8646 occurred when performing an "ALTER PARTITION FUNCTION"Today I was just doing some tests to create a partitioned table with a sliding window on an existing table.<br /><br />So I created my partition function and partition scheme, and did a drop index with move to my partition scheme on the existing table. Everithing (selects, inserts,...) worked well, until I wanted to Alter my Partition function to do an extra RANGE SPLIT. Than I did get a <strong>Fatal Error 8646</strong>... error message. Fortunately, I remembered some problems with indexes and replication from the past who gave the same "8646" error message. And when looking at the table, I saw there was also an existing non-clustered index. After I dropped the non-clustered index, the problem was solved.<br /><br />I tried to reproduce the same error with a smaller table, but didn't get any "succes".Egon Vandenrijdthttp://www.blogger.com/profile/17366254188434203611noreply@blogger.com0tag:blogger.com,1999:blog-1671249158308857862.post-29160148423098229002009-08-05T14:45:00.000+02:002009-08-05T14:50:59.715+02:00SQLServer Parameter sniffingWe had an issue in two of our stored procedures recently with query execution plan caching. After digging deep in the code base we found what was causing the problem. <br /><br />One of the great features in SQL Server is its optimizing query processor. There are, however, times when some of the advanced features of the optimizer can cause problems. Sometimes it simply helps too much. One of those is when it automatically uses the parameter values passed into a stored procedure to tailor the execution plan it will construct for a query. This is called "parameter sniffing" and is normally a good thing. It allows the optimizer to take into account the actual values of stored procedure parameters as it builds plans to service queries within the procedure that make use of them. IOW, if you pass a value of "test" for a parameter named @param, then use @param in the WHERE clause of a query within the procedure, the optimizer is smart enough to use "test" when it estimates how many rows will match the WHERE clause as it compiles a plan for the query.<br />While parameter sniffing is certainly a powerful feature, it can cause problems when a procedure’s plan happens to have been kicked out of the procedure cache (or was never in it) just prior to the procedure being called with atypical parameter values. This can result in a plan that is skewed toward atypical use, one that is suboptimal (or even downright slow) when called with typical values. Since, once cached, a query plan can be reused for parameter values that vary widely, the ideal situation is to have a plan in the cache that covers the typical usage of the procedure as much as possible. If a plan makes it into the cache that is oriented toward atypical parameter values, it can have a devastating effect on performance. <br />SQL Server 2005 has some features to help combat this, but there are no built-in features for dealing with it in prior releases. There are three simple tricks that you can use to solve this problem:<br /><br />- the use of "decoy variables”. This means that we filter the query on a local variable value; witch replaces the parameter that we actually pass into the procedure.<br /><br />CREATE PROCEDURE usp_avoidSniff1(@param int) <br />AS <br /><br /> BEGIN <br /> Decalre @lclVarParam int<br /> <br /> SET @lclVarParam = @param<br /> <br /> SELECT x, y<br /> FROM MyTable <br /> WHERE z = @lclVarParam <br /> END <br /><br />GO<br /><br />- Filter the query on a dummy parameter, i.e. @template_param, that we never actually pass into the procedure. We always pass in the filter value we want to use via @param and let @template_param retain its default. The optimizer notices that we’re filtering the query against the sales table using @template_param and sniffs its value (which will always be the default value at compilation time) when creating the query plan for it.<br /><br />CREATE PROCEDURE usp_avoidSniff2(@param int, @param_def int= ‘My optim val’) <br />AS <br /><br /> BEGIN <br /><br /> SET @param_def = @param<br /> <br /> <br /> SELECT x, y<br /> FROM MyTable <br /> WHERE z = @param_def <br /> END <br /><br />GO<br /><br />- Use the OPTIMIZE FOR statement (SQLServer 2005). Adding this to a query makes sure the plan is optimized for the value you specify. And when all else fails you can even force a particular query plan with the new USE PLAN statement<br /><br />CREATE PROCEDURE usp_avoidSniff1(@param int) <br />AS <br /><br /> BEGIN <br /> <br /> SELECT x, y<br /> FROM MyTable <br />WHERE z = @param OPTION(OPTIMIZE FOR (@IDate = Null))<br /><br /> END <br /><br />GOEgon Vandenrijdthttp://www.blogger.com/profile/17366254188434203611noreply@blogger.com0tag:blogger.com,1999:blog-1671249158308857862.post-27231105939126959482009-07-28T10:44:00.000+02:002009-07-28T10:49:01.439+02:00SQLServer Locking and deadlocksIn SQLServer environments with a lot of concurrency, locks, blocks and deadlocks are a very common issue.<br /><br />To avoid negative performance impact, here are some best practices and monitoring tools to use:<br /><br /><strong>1) Use of WITH (NOLOCK)<br /></strong><br /><br />Where applicable, use <strong>WITH (NOLOCK)</strong> in the FROM-clause and in JOIN. It avoids locks on the database while performing read-operations.<br />Even when retrieving a value for values of local parameters inside a stored procedure, use the <strong>WITH (NOLOCK)</strong> statement.<br /><br />Example:<br /><br />SELECT x.a<br /> , y.b<br /> , …<br /> FROM dbo.MyTableX AS x <strong>WITH (NOLOCK)<br /></strong> INNER JOIN dbo.MyTableY As y <strong>WITH (NOLOCK)<br /></strong> ON …..<br /><br />The NOLOCK keyword instructs SQL Server not to wait until any exclusive locks are gone. This improves concurrency on the database but, a resultset might contain data that have not been committed.<br /><br />In rare cases we can encounter this scenario:<br />Query session A starts to change data for a in table X. Milliseconds later session B starts to read these data with the WITH (NOLOCK) option. Data, as changed by session A are shown to user B. But then user A cancels his action and the transaction is rolled back. At this point user B can see data on his screen that have not been committed.<br />To avoid problems with uncommitted data (dirty reads) we should not use the WITH (NOLOCK) option when retrieving data that will be used in calculations and batches or on reports.<br /><br /><strong>2) Always check that the columns in your WHERE-clause are covered by an index.<br /></strong><br /><br />If your statement takes more than 1 second, use the sp_lock command in SQL Server to check on exclusive locks (X)<br /><br /><strong>3) Use of Cursors<br /></strong><br />Do not use cursors in Stored Procedures. They are too expensive for the DB performance. But sometimes are cursors required.<br /><br /><strong>4) Keep transactions short<br /></strong><br />Put only the strictly necessary commands in the same transaction, and split up all commands in as much transactions as possible!<br /><br /><strong>5) During transactions, don't allow any user input. Collect it before the transaction begins.<br /></strong><br /><strong>6) Execute inserts/ updates on the same tables in the same order to avoid deadlocks.<br /></strong><br /><strong>7) Choose your clustered index wisely:<br /></strong><br />a. Choose a very narrow index which is very unique<br /><br />b. If the clustered index contains more then 1 column, place them in the right “fine graining” order.<br /><br />c. Avoid lock escalations during the update<br /><br /><strong>8) Avoid page splits:<br /></strong><br />a. Use a correct fillfactor<br />b. Again choose your clustered index wisely!!!<br /><br /><strong>9) Use the right monitoring tools when necessary:<br /></strong><br />a. Profiler traces (DeadLocks + DeadLock chains + Deadlock Graph, lock timeouts).<br /><br />b. Trace flags: To help identify which tables or stored procedures are causing deadlock problems, turn on trace flag 1204 (outputs basic trace data) or trace flag 1205 (outputs more detailed trace data).<br /><br />DBCC TRACEON (3605,1204,-1)<br /><br />Be sure to turn off this trace flag when you are done, as this trace can eat up SQL Server's resources unnecessarily, hurting performance.<br /><br />10) <strong>Change the default deadlock behavior when appropriate</strong><br /><br />You can this by using the following command:<br /><br />SET DEADLOCK_PRIORITY { LOW NORMAL @deadlock_var }<br /><br />WHERE:<br /><br /><strong>Low</strong> tells SQL Server that the current session should be the preferred deadlock victim, not the session that incurs the least amount of rollback resources. The standard deadlock error message 1205 is returned.<br /><br /><strong>Normal</strong> tells SQL Server to use the default deadlock method.<br /><br /><strong>@deadlock_var</strong> is a character variable specifying which deadlock method you want to use. Specify "3" for low, or "6" for normal.<br /><br />This command is set a runtime for a specified user connectionEgon Vandenrijdthttp://www.blogger.com/profile/17366254188434203611noreply@blogger.com0tag:blogger.com,1999:blog-1671249158308857862.post-90235286616137363702008-05-16T13:30:00.000+02:002008-05-16T13:55:11.434+02:00SSIS debugging on 64<strong>Problem</strong>: When I debug my SSIS package (which imports an Excel-sheet into SQLServer) on a 64-bit server, I get this errors:<br />[Excel Source [9]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.<br /><br />[Connection manager "Excel Connection Manager"] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data.".<br /><br /><strong>Cause</strong>: There is no 64 bit driver for excel. The workaround is to run the package using the 32 bit version of the execution utility.<br /><br /><strong>Solution</strong>: Project Properties->Degugging Section, I set Run64bit RunTime to False.Egon Vandenrijdthttp://www.blogger.com/profile/17366254188434203611noreply@blogger.com0tag:blogger.com,1999:blog-1671249158308857862.post-26088183598180809702008-05-06T15:42:00.000+02:002008-05-06T15:52:44.231+02:00Rebuild Master --> back to RTMIf you rebuild the master database in SQL Server 2005, it reverts to the RTM version, losing service packs and additonal patches.<br /><br />The "strange" thing is that the server net libraries remain the original service pack versions, wich can be confusing.Egon Vandenrijdthttp://www.blogger.com/profile/17366254188434203611noreply@blogger.com0tag:blogger.com,1999:blog-1671249158308857862.post-11972238816938631582008-05-06T14:31:00.000+02:002008-05-06T15:18:37.214+02:00sp_MSforeachtable and sp_MSforeachdbHow simple can things be?<br /><br />I just discovered these "undocumented" sp's. No more cursor creations for these kind of queries ;-). In combination with all those dbcc and dm_... statements and views (i.e. DBCC CLEANTABLE), a good thing to know. They both work in 2000 and 2005 (in 2005 there is no extra filtering on schema level :-s )<br /><br /><br />Examples:<br /><br />USE AdventureWorks<br />go<br /><br />EXEC sp_MSforeachtable 'select ''?'',count(*) from ?'<br /><br /><br /><br />DECLARE @command varchar(1000)<br />SELECT @command = 'USE ? SELECT name FROM sysobjects WHERE xtype = ''U'' ORDER BY name'<br /><br />EXEC sp_MSforeachdb @commandEgon Vandenrijdthttp://www.blogger.com/profile/17366254188434203611noreply@blogger.com0tag:blogger.com,1999:blog-1671249158308857862.post-26959919021689225902008-04-25T10:55:00.000+02:002008-04-25T11:10:32.755+02:00Linked Server failure for IBM OLE DB Providers on SQLServer2005 X64 ClusterI've Created Linked servers to IBM AS400 machines using the IBMDA400 and IBMSQL400 OleDb providers. If I failover, or take off-line and bring back on-line the SQL Resource Group, I get this Error:<br /><br />Msg 7340, Level 16, State 4, Line 1<br />Cannot create a column accessor for OLE DB provider "IBMDA400" for linked server "ATCOAIRC".<br /><br />Solution:<br /><br />- Browse in the Management Studio to the linked Server, request the properties, and close this window again everytime SQLServer Service starts ;-)<br /><br />Or, if you don't want to do this manually - Duhhh...<br /><br />- USE master;<br />GO<br /><br />EXEC sp_configure 'show advanced option', '1';<br />RECONFIGURE<br /><br />EXEC sp_configure 'scan for startup procs', '1';<br />RECONFIGURE<br />GO<br /><br />CREATE PROC usp_enum_oledb_providers<br />AS<br /><br />exec sp_enum_oledb_providers<br /><br />GO<br /><br />exec sp_procoption N'usp_enum_oledb_providers', 'startup', 'on'<br /><br /><br />Note: I didn't have this problem with SQLServer or Oracle Linked Servers.Egon Vandenrijdthttp://www.blogger.com/profile/17366254188434203611noreply@blogger.com0tag:blogger.com,1999:blog-1671249158308857862.post-61846400877537260812008-04-11T10:50:00.000+02:002008-04-11T11:22:36.435+02:00IBM Ole DB error in SQLServer 2005 on x64<strong>Error:</strong><br /><strong></strong><br />Msg 7399, Level 16, State 1, Line 2<br />The OLE DB provider "IBMDA400" for linked server "(null)" reported an error. Access denied.<br />Msg 7350, Level 16, State 2, Line 2<br />Cannot get the column information from OLE DB provider "IBMDA400" for linked server "(null)".<br /><br />Or<br /><br />Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)<br />--> An exception occured while executing a Transact statement or batch.<br /> --> Cannot obtain the required interface ("IID_IDBSchemaRowset") from OLE DB Provider "IBMDA400" for linked server.<br /><br /><br /><br /><strong>Solution:</strong><br /><strong></strong><br />1) Make sure on your SQL Server 2005 x64 (64 bit) machine that "<strong>Allow inprocess</strong>" is checked for the linked server provider (IBMDASQL in my case).<br />--> In SQL Mgmt Studio, connect to the SQL Server Database Engine and go to Server Objects->Linked Server->Providers in the Object Explorer.<br />--> Right-click on IBMDASQL and select Properties. Check the "Allow inprocess" box in the Provider Options dialog that comes up.<br /><br />2) Enable Ole Automation Procedures;<br /><br />sp_configure 'show advanced options', 1;<br />GO<br />RECONFIGURE;<br />GO<br /><br />sp_configure 'Ole Automation Procedures', 1;<br />GO<br />RECONFIGURE;<br />GOEgon Vandenrijdthttp://www.blogger.com/profile/17366254188434203611noreply@blogger.com1tag:blogger.com,1999:blog-1671249158308857862.post-59400811031482003502008-04-04T10:10:00.000+02:002008-04-04T10:15:24.661+02:00fn_my_permissionsReturns a list of the permissions effectively granted to the principal on a securable.<br /><br />Examples:<br /><br />A. Listing<strong> </strong>effective permissions on the <strong>server<br /></strong>The following example returns a list of the effective permissions of the caller on the server.<br /><br />SELECT * FROM fn_my_permissions(NULL, 'SERVER');<br />GO<br /><br />B. Listing effective permissions on the <strong>database</strong><br />The following example returns a list of the effective permissions of the caller on the AdventureWorks database.<br /><br />USE AdventureWorks;<br />SELECT * FROM fn_my_permissions (NULL, 'DATABASE');<br />GO<br /><br />C. Listing effective permissions on a <strong>view</strong><br />The following example returns a list of the effective permissions of the caller on the vIndividualCustomer view in the Sales schema of the AdventureWorks database.<br /><br /><a class="copyCode" tabindex="0" href="javascript:CopyCode("></a>USE AdventureWorks;<br />SELECT * FROM fn_my_permissions('Sales.vIndividualCustomer', 'OBJECT')<br />ORDER BY subentity_name, permission_name ;<br />GO<br /><br />D. Listing effective permissions of <strong>another user</strong><br />The following example returns a list of the effective permissions of database user Wanida on the Employee table in the HumanResources schema of the AdventureWorks database. The caller requires IMPERSONATE permission on user Wanida.<br /><br /><a class="copyCode" tabindex="0" href="javascript:CopyCode("></a>EXECUTE AS USER = 'Wanida';<br />SELECT * FROM fn_my_permissions('HumanResources.Employee', 'OBJECT')<br />ORDER BY subentity_name, permission_name ;<br />REVERT;<br />GO<br /><br />E. Listing effective permissions on a <strong>certificate</strong><br />The following example returns a list of the effective permissions of the caller on a certificate named Shipping47 in the current database.<br /><br /><a class="copyCode" tabindex="0" href="javascript:CopyCode("></a>SELECT * FROM fn_my_permissions('Shipping47', 'CERTIFICATE');<br />GO<br /><br />F. Listing effective permissions on an <strong>XML Schema Collection</strong><br />The following example returns a list of the effective permissions of the caller on an XML Schema Collection named ProductDescriptionSchemaCollection in the AdventureWorks database.<br /><br /><a class="copyCode" tabindex="0" href="javascript:CopyCode("></a>USE AdventureWorks;<br />SELECT * FROM fn_my_permissions('ProductDescriptionSchemaCollection',<br />'XML SCHEMA COLLECTION');<br />GO<br /><br />G. Listing effective permissions on a <strong>database user</strong><br />The following example returns a list of the effective permissions of the caller on a user named MalikAr in the current database.<br /><br /><a class="copyCode" tabindex="0" href="javascript:CopyCode("></a>SELECT * FROM fn_my_permissions('MalikAr', 'USER');<br />GO<br /><br />H. Listing effective permissions of <strong>another login</strong><br />The following example returns a list of the effective permissions of SQL Server login WanidaBenshoof on the Employee table in the HumanResources schema of the AdventureWorks database. The caller requires IMPERSONATE permission on SQL Server login WanidaBenshoof.<br /><br /><a class="copyCode" tabindex="0" href="javascript:CopyCode("></a>EXECUTE AS LOGIN = 'WanidaBenshoof';<br />SELECT * FROM fn_my_permissions('AdventureWorks.HumanResources.Employee', 'OBJECT')<br />ORDER BY subentity_name, permission_name ;<br />REVERT;<br />GOEgon Vandenrijdthttp://www.blogger.com/profile/17366254188434203611noreply@blogger.com0tag:blogger.com,1999:blog-1671249158308857862.post-36051374847620805802008-04-04T09:47:00.000+02:002008-04-04T09:48:30.401+02:00Windows Vista and Office 2007 CompatibilitySQL Server <strong>Service Pack 2</strong> or greater is needed to be fully compatible with Windows Vista and Microsoft Office 2007.Egon Vandenrijdthttp://www.blogger.com/profile/17366254188434203611noreply@blogger.com0tag:blogger.com,1999:blog-1671249158308857862.post-14207054959135594782008-03-28T01:34:00.000+01:002008-03-27T17:34:13.327+01:00Login "error States" in SQL LogStates Description<br /><br />2 and 5 Invalid userid <br />6 Attempt to use a Windows login name with SQL Authentication <br />7 Login disabled and password mismatch <br />8 Password mismatch <br />9 Invalid password<br />11 and 12 Valid login but server access failure <br />13 SQL Server service paused <br />18 Change password requiredEgon Vandenrijdthttp://www.blogger.com/profile/17366254188434203611noreply@blogger.com0tag:blogger.com,1999:blog-1671249158308857862.post-25674526248435689902008-03-27T12:24:00.000+01:002008-03-27T13:18:03.789+01:00SQL Server Management Studio takes a long time to load, how to improve the load performance of this tool?In general the SQL Server Management Studio takes at least 10 to 15 seconds to load on a user machine. Sometimes the SSMS tool can take a long time to load when there are a lot of SQL Server instances, this problem may also occur on the new 64-bit SQL Server 2005 platform.<br />One resolution is to add a switch to the Management Studio shortcut as defined below. Access the below screen by going to Start > Programs > SQL Server 2005 program group > 'SQL Server Management Studio' program, right-clicking will get you the following screen by opening the General tab, then select shortcut tab to add '<strong>nosplash</strong>' switch:<br /><br />Also the user needs to tweak the Internet Explorer security settings as follows:<br />Uncheck two options in Internet Explorer's Advanced Security settings. Namely:<br />"<strong>Check for publisher's certificate revocation</strong>"<br />"<strong>Check for server certificate revocation</strong>".<br />Both these steps combined should improve the load time of SQL Server Management Studio.Egon Vandenrijdthttp://www.blogger.com/profile/17366254188434203611noreply@blogger.com0tag:blogger.com,1999:blog-1671249158308857862.post-52776836152718114702008-03-26T13:13:00.000+01:002008-03-26T13:30:49.070+01:00How to debug T-SQL stored procedures in SQLServer 2005We will debug our procedure, <strong>not</strong> from Management Studio but from the <strong>Visual Studio 2005</strong> (only Team Systems or Professional editions) development environment. I mention this because under SQL Server 2000 we are able to debug stored procedures using Query Analyzer. Perhaps debugging capabilities will be added to Management Studio in the future. <br />Before any debugging can take place, you first must create the store procedure. This can be done either by using Management Studio/New Query or by building the procedure graphically in Visual Studio 2005. Stored procedures can be created in Visual Studio by opening a new database project and using the <a href="http://msdn2.microsoft.com/en-us/library/6db0hwky.aspx">Visual Studio Installed Templates</a>.<br />Either way, once the procedure exists, you are ready to start debugging from Visual Studio 2005. When you start up Visual Studio for debugging purposes, you don't have to create a project. Instead, you can create a connection to your database from Server Explorer (You will have to provide your server name along with your login credentials and choose the AdventureWorks database.) Then you can drill down and open up the stored procedures folder. Next, right click on the procedure you wish to debug and choose Step Into Stored Procedure from the context menu. Then you are ready to start debugging!<br /><br />Note: Before you can start "Remote debugging", you have to install "msvsmon.exe" from the visual studio CD or the sqlserver directory "C:\Program Files\Microsoft SQL Server\90\Shared\1033\rdbgsetup.exe".<br /><br />Micorosft development team is looking to implement a better way within SQL Server 2008 and as of now 2005 is only way out with Visual Studio.Egon Vandenrijdthttp://www.blogger.com/profile/17366254188434203611noreply@blogger.com0tag:blogger.com,1999:blog-1671249158308857862.post-51571426445242013302008-03-26T13:08:00.000+01:002008-03-26T13:09:55.962+01:00Moving msdb<p>Moving MSDB is more complicated than other system databases and requires you to add trace flags and parameters to the SQL Server startup to detach msdb, then remove them and restart SQL Server to attach it:</p><p>Start SQL Server with -c, -m and -T3608 and then perform a detach. You would then remove these parameters before attaching MSDB.<br /></p>Egon Vandenrijdthttp://www.blogger.com/profile/17366254188434203611noreply@blogger.com0tag:blogger.com,1999:blog-1671249158308857862.post-17089229509956916152008-03-21T09:20:00.000+01:002008-03-21T09:26:30.495+01:00Moving System DatabasesIf you are moving system databases (master, msdb, model, tempdb), you would change the location of <strong>master</strong> by changing the startup parameters, bringing down the instance, and moving the master.mdf and master.ldf files.<br />Secondly you need to address the <strong>model</strong> database, because tempdb is reallocated every time the instance starts. This reallocation requires the existence of model to be successful.<br />The last one you have to re-address is the msdb, because msdb is used by the SQL Server agent and has no bearing on the stability of the database engine.Egon Vandenrijdthttp://www.blogger.com/profile/17366254188434203611noreply@blogger.com0tag:blogger.com,1999:blog-1671249158308857862.post-23222571233765925972008-03-20T17:20:00.000+01:002008-03-20T17:22:40.890+01:00Hierarchyid Travesal (SQLServer2008)In which type of tree traversal would all nodes at a particular level below the root be traversed before those at lower levels?<br /><br />Given a tree such as this:<br /> 1<br /> / \<br /> 2 3 4<br /> / \ / \ \<br />5 6 7 8 9<br /><br />a traversal of nodes in 1-2-3-4-5 order would be a <strong>breadth-first</strong> traversal.Egon Vandenrijdthttp://www.blogger.com/profile/17366254188434203611noreply@blogger.com0