tag:blogger.com,1999:blog-74822897747655085462024-03-13T01:39:38.268-04:00Random Technical "Stuff"John Henneseyhttp://www.blogger.com/profile/14350314173210731480noreply@blogger.comBlogger18125tag:blogger.com,1999:blog-7482289774765508546.post-70309050079175009292011-12-20T13:27:00.001-05:002011-12-20T13:27:40.895-05:00SQL 2008 LimitationsHandy to have around...<br />
<a href="http://msdn.microsoft.com/en-us/library/ms143432.aspx">http://msdn.microsoft.com/en-us/library/ms143432.aspx</a>John Henneseyhttp://www.blogger.com/profile/14350314173210731480noreply@blogger.com0tag:blogger.com,1999:blog-7482289774765508546.post-84531786110046331642011-08-31T08:39:00.000-04:002011-08-31T08:39:07.264-04:00Adding assemblies at run time to rehosted workflow designerWhen creating a rehosted solution for Workflow Foundation 4 you need to make whichever assemblies available at run time in the "Imports" section - but how to do this? Either you can hard code each in your code behind, which works if you have a relatively static environment and/or access to the code, but in my case where we distribute it out to many clients I would like for them to be able to modify the list at will. Enter custom config sections.<br />
<br />
First, the entry in our .config file. There are many good articles out there on the meaning behind configSections and how to do it, this is meant as a copy/paste helper. :)<br />
<br />
<pre style="font-family: consolas;"><span style="color: blue;"> <</span><span style="color: #a31515;">configSections</span><span style="color: blue;">></span>
<span style="color: blue;"> <</span><span style="color: #a31515;">section</span><span style="color: blue;"> </span><span style="color: red;">name</span><span style="color: blue;">=</span>"<span style="color: blue;">DesignerImportedAssemblies</span>"<span style="color: blue;"> </span><span style="color: red;">type</span><span style="color: blue;">=</span>"<span style="color: blue;">RehostedWorkflowDesigner.Configuration.DesignerImportedAssembliesConfigSection, RehostedWorkflowDesigner</span>"<span style="color: blue;">/></span>
<span style="color: blue;"> </</span><span style="color: #a31515;">configSections</span><span style="color: blue;">></span>
<span style="color: blue;"> <</span><span style="color: #a31515;">DesignerImportedAssemblies</span><span style="color: blue;">></span>
<span style="color: blue;"> <</span><span style="color: #a31515;">ImportedAssemblies</span><span style="color: blue;">></span>
<span style="color: blue;"> <</span><span style="color: #a31515;">add</span><span style="color: blue;"> </span><span style="color: red;">assemblyName</span><span style="color: blue;">=</span>"<span style="color: blue;">mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</span>"<span style="color: blue;"> /></span>
<span style="color: blue;"> <</span><span style="color: #a31515;">add</span><span style="color: blue;"> </span><span style="color: red;">assemblyName</span><span style="color: blue;">=</span>"<span style="color: blue;">PresentationCore, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35</span>"<span style="color: blue;"> /></span>
<span style="color: blue;"> <</span><span style="color: #a31515;">add</span><span style="color: blue;"> </span><span style="color: red;">assemblyName</span><span style="color: blue;">=</span>"<span style="color: blue;">PresentationFramework, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35</span>"<span style="color: blue;"> /></span>
<span style="color: blue;"> <</span><span style="color: #a31515;">add</span><span style="color: blue;"> </span><span style="color: red;">assemblyName</span><span style="color: blue;">=</span>"<span style="color: blue;">System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</span>"<span style="color: blue;"> /></span>
<span style="color: blue;"> <</span><span style="color: #a31515;">add</span><span style="color: blue;"> </span><span style="color: red;">assemblyName</span><span style="color: blue;">=</span>"<span style="color: blue;">System.Activities, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35</span>"<span style="color: blue;"> /></span>
<span style="color: blue;"> <</span><span style="color: #a31515;">add</span><span style="color: blue;"> </span><span style="color: red;">assemblyName</span><span style="color: blue;">=</span>"<span style="color: blue;">System.Core, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</span>"<span style="color: blue;"> /></span>
<span style="color: blue;"> <</span><span style="color: #a31515;">add</span><span style="color: blue;"> </span><span style="color: red;">assemblyName</span><span style="color: blue;">=</span>"<span style="color: blue;">System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</span>"<span style="color: blue;"> /></span>
<span style="color: blue;"> <</span><span style="color: #a31515;">add</span><span style="color: blue;"> </span><span style="color: red;">assemblyName</span><span style="color: blue;">=</span>"<span style="color: blue;">System.Data.DataSetExtensions, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</span>"<span style="color: blue;"> /></span>
<span style="color: blue;"> <</span><span style="color: #a31515;">add</span><span style="color: blue;"> </span><span style="color: red;">assemblyName</span><span style="color: blue;">=</span>"<span style="color: blue;">System.Web, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a</span>"<span style="color: blue;"> /></span>
<span style="color: blue;"> <</span><span style="color: #a31515;">add</span><span style="color: blue;"> </span><span style="color: red;">assemblyName</span><span style="color: blue;">=</span>"<span style="color: blue;">System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</span>"<span style="color: blue;"> /></span>
<span style="color: blue;"> <</span><span style="color: #a31515;">add</span><span style="color: blue;"> </span><span style="color: red;">assemblyName</span><span style="color: blue;">=</span>"<span style="color: blue;">WindowsFormsIntegration, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35</span>"<span style="color: blue;"> /></span>
<span style="color: blue;"> </</span><span style="color: #a31515;">ImportedAssemblies</span><span style="color: blue;">></span>
<span style="color: blue;"> </</span><span style="color: #a31515;">DesignerImportedAssemblies</span><span style="color: blue;">></span></pre><br />
<br />
Then the configSection handler:<br />
<span class="Apple-style-span" style="font-family: consolas; white-space: pre;"><span style="color: blue;">using</span> System.Configuration;</span><br />
<pre style="font-family: consolas;"><span style="color: blue;">namespace</span> RehostedWorklowDesigner.Configuration
{
<span style="color: green;">// Config section</span>
<span style="color: blue;">public</span> <span style="color: blue;">class</span> <span style="color: #2b91af;">DesignerImportedAssembliesConfigSection</span> : <span style="color: #2b91af;">ConfigurationSection</span>
{
[<span style="color: #2b91af;">ConfigurationProperty</span>(<span style="color: #a31515;">"ImportedAssemblies"</span>)]
<span style="color: blue;">public</span> <span style="color: #2b91af;">DesignerIncludedAssembliesCollection</span> DesignerImportedAssemblies
{
<span style="color: blue;">get</span> { <span style="color: blue;">return</span> (<span style="color: #2b91af;">DesignerIncludedAssembliesCollection</span>)<span style="color: blue;">base</span>[<span style="color: #a31515;">"ImportedAssemblies"</span>]; }
}
}
<span style="color: green;">// Collection to hold all imported assemblies</span>
<span style="color: blue;">public</span> <span style="color: blue;">class</span> <span style="color: #2b91af;">DesignerIncludedAssembliesCollection</span> : <span style="color: #2b91af;">ConfigurationElementCollection</span>
{
<span style="color: blue;">protected</span> <span style="color: blue;">override</span> <span style="color: #2b91af;">ConfigurationElement</span> CreateNewElement()
{
<span style="color: blue;">return</span> <span style="color: blue;">new</span> <span style="color: #2b91af;">ImportedAssemblyElement</span>();
}
<span style="color: blue;">protected</span> <span style="color: blue;">override</span> <span style="color: blue;">object</span> GetElementKey(<span style="color: #2b91af;">ConfigurationElement</span> element)
{
<span style="color: blue;">return</span> ((<span style="color: #2b91af;">ImportedAssemblyElement</span>)(element)).AssemblyName;
}
<span style="color: blue;">public</span> <span style="color: #2b91af;">ImportedAssemblyElement</span> <span style="color: blue;">this</span>[<span style="color: blue;">int</span> idx]
{ <span style="color: blue;">get</span> { <span style="color: blue;">return</span> (<span style="color: #2b91af;">ImportedAssemblyElement</span>)BaseGet(idx); } }</pre><pre style="font-family: consolas;">}
<span style="color: green;">// and the assembly information itself.</span>
<span style="color: blue;">public</span> <span style="color: blue;">class</span> <span style="color: #2b91af;">ImportedAssemblyElement</span> : <span style="color: #2b91af;">ConfigurationElement</span>
{
[<span style="color: #2b91af;">ConfigurationProperty</span>(<span style="color: #a31515;">"assemblyName"</span>, IsKey = <span style="color: blue;">true</span>, IsRequired = <span style="color: blue;">true</span>)]
<span style="color: blue;">public</span> <span style="color: blue;">string</span> AssemblyName
{
<span style="color: blue;">get</span> { <span style="color: blue;">return</span> (<span style="color: blue;">string</span>)<span style="color: blue;">base</span>[<span style="color: #a31515;">"assemblyName"</span>]; }</pre><pre style="font-family: consolas;"><span style="color: blue;">set</span> { <span style="color: blue;">base</span>[<span style="color: #a31515;">"assemblyName"</span>] = <span style="color: blue;">value</span>; }
}
}
}</pre><pre style="font-family: consolas;"><span class="Apple-style-span" style="font-size: x-small;">
</span></pre><pre style="font-family: consolas;"><span class="Apple-style-span" style="font-size: x-small;"><span class="Apple-style-span" style="font-family: 'Times New Roman'; font-size: small; white-space: normal;">And lastly putting the two together and joining them to the workflow designer control via the AssemblyContextControlItem object :</span></span></pre><pre style="font-family: consolas;"><span class="Apple-style-span" style="font-size: x-small;"><span class="Apple-style-span" style="font-family: 'Times New Roman'; font-size: small; white-space: normal;">
</span></span></pre><pre style="font-family: consolas;"><span class="Apple-style-span" style="font-family: 'Times New Roman'; white-space: normal;"><pre style="font-family: consolas;"><span style="color: #2b91af;">AssemblyContextControlItem</span> acci = <span style="color: blue;">new</span> <span style="color: #2b91af;">AssemblyContextControlItem</span>();
acci.ReferencedAssemblyNames = <span style="color: blue;">new</span> <span style="color: #2b91af;">List</span><<span style="color: #2b91af;">AssemblyName</span>>();
acci.LocalAssemblyName = <span style="color: #2b91af;">Assembly</span>.GetExecutingAssembly().GetName();
<span style="color: green;">// Pull all the referenced assemblies from the .config file.</span>
Configuration.<span style="color: #2b91af;">DesignerImportedAssembliesConfigSection</span> section = (Configuration.<span style="color: #2b91af;">DesignerImportedAssembliesConfigSection</span>)<span style="color: #2b91af;">ConfigurationManager</span>.GetSection(<span style="color: #a31515;">"DesignerImportedAssemblies"</span>);
<span style="color: blue;">foreach</span> (Configuration.<span style="color: #2b91af;">ImportedAssemblyElement</span> element <span style="color: blue;">in</span> section.DesignerImportedAssemblies)
{
acci.ReferencedAssemblyNames.Add(<span style="color: blue;">new</span> <span style="color: #2b91af;">AssemblyName</span>(element.AssemblyName));
}</pre></span></pre>John Henneseyhttp://www.blogger.com/profile/14350314173210731480noreply@blogger.com0tag:blogger.com,1999:blog-7482289774765508546.post-69370431113124703452010-11-12T21:35:00.000-05:002010-11-12T21:35:16.142-05:00How to see object definition in sql server<span class="Apple-style-span" style="font-family: Arial, Helvetica, sans-serif;">Found this neat function today - it shows you the internals of what tables a view is hitting, or the definition of a stored proc (ie sp_who2). For example running the following will return you the definition of the service broker view:</span><br />
<br />
<br />
<div class="MsoNormal"><span class="Apple-style-span" style="color: blue; font-family: 'Courier New'; font-size: small;"><span class="Apple-style-span" style="font-size: 13px; line-height: 14px;"><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-ansi-language: EN-US; mso-bidi-language: AR-SA; mso-fareast-font-family: Calibri; mso-fareast-language: EN-US; mso-fareast-theme-font: minor-latin; mso-no-proof: yes;">SELECT</span><span style="font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-ansi-language: EN-US; mso-bidi-language: AR-SA; mso-fareast-font-family: Calibri; mso-fareast-language: EN-US; mso-fareast-theme-font: minor-latin; mso-no-proof: yes;"> <span style="color: magenta;">object_definition</span><span style="color: grey;">(</span><span style="color: magenta;">OBJECT_ID</span><span style="color: grey;">(</span><span style="color: red;">'sys.service_queues'</span><span style="color: grey;">))</span></span></span></span></div><div class="MsoNormal"><span class="apple-style-span"><span style="color: grey; font-family: "Courier New"; font-size: 10.0pt; line-height: 115%;"><br />
</span></span></div><div class="MsoNormal"><span class="Apple-style-span" style="font-family: Arial, Helvetica, sans-serif;">Yields:</span></div><div class="MsoNormal"></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">CREATE</span><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"> <span style="color: blue;">VIEW</span> <span style="color: green;">sys</span><span style="color: grey;">.</span><span style="color: green;">service_queues</span> <span style="color: blue;">AS</span><span style="mso-spacerun: yes;"> </span><o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">SELECT</span><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"> o<span style="color: grey;">.</span>name<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"><span style="mso-tab-count: 1;"> </span><span style="mso-spacerun: yes;"> </span><span style="color: grey;">,</span>o<span style="color: grey;">.</span><span style="color: magenta;">object_id<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"><span style="mso-tab-count: 1;"> </span><span style="mso-spacerun: yes;"> </span><span style="color: grey;">,</span>o<span style="color: grey;">.</span>principal_id<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"><span style="mso-tab-count: 1;"> </span><span style="mso-spacerun: yes;"> </span><span style="color: grey;">,</span>o<span style="color: grey;">.</span><span style="color: magenta;">schema_id<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"><span style="mso-tab-count: 1;"> </span><span style="mso-spacerun: yes;"> </span><span style="color: grey;">,</span>o<span style="color: grey;">.</span>parent_object_id<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"><span style="mso-tab-count: 1;"> </span><span style="mso-spacerun: yes;"> </span><span style="color: grey;">,</span>o<span style="color: grey;">.</span><span style="color: blue;">type<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"><span style="mso-tab-count: 1;"> </span><span style="mso-spacerun: yes;"> </span><span style="color: grey;">,</span>o<span style="color: grey;">.</span>type_desc<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"><span style="mso-tab-count: 1;"> </span><span style="mso-spacerun: yes;"> </span><span style="color: grey;">,</span>o<span style="color: grey;">.</span>create_date<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"><span style="mso-tab-count: 1;"> </span><span style="mso-spacerun: yes;"> </span><span style="color: grey;">,</span>o<span style="color: grey;">.</span>modify_date<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"><span style="mso-tab-count: 1;"> </span><span style="mso-spacerun: yes;"> </span><span style="color: grey;">,</span>o<span style="color: grey;">.</span>is_ms_shipped<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"><span style="mso-tab-count: 1;"> </span><span style="mso-spacerun: yes;"> </span><span style="color: grey;">,</span>o<span style="color: grey;">.</span>is_published<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"><span style="mso-tab-count: 1;"> </span><span style="mso-spacerun: yes;"> </span><span style="color: grey;">,</span>o<span style="color: grey;">.</span>is_schema_published<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"><span style="mso-tab-count: 1;"> </span><span style="mso-spacerun: yes;"> </span><span style="color: grey;">,</span><span style="color: magenta;">convert</span><span style="color: grey;">(</span><span style="color: blue;">smallint</span><span style="color: grey;">,</span> o<span style="color: grey;">.</span>property<span style="color: grey;">)</span> <span style="color: blue;">AS</span> max_readers<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"><span style="mso-tab-count: 1;"> </span><span style="mso-spacerun: yes;"> </span><span style="color: grey;">,</span><span style="color: magenta;">convert</span><span style="color: grey;">(</span><span style="color: blue;">nvarchar</span><span style="color: grey;">(</span>776<span style="color: grey;">),</span> v<span style="color: grey;">.</span>value<span style="color: grey;">)</span> <span style="color: blue;">COLLATE</span> database_default <span style="color: blue;">AS</span> activation_procedure<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"><span style="mso-tab-count: 1;"> </span><span style="mso-spacerun: yes;"> </span><span style="color: grey;">,</span>e<span style="color: grey;">.</span>indepid <span style="color: blue;">AS</span> execute_as_principal_id<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"><span style="mso-tab-count: 1;"> </span><span style="mso-spacerun: yes;"> </span><span style="color: grey;">,</span>o<span style="color: grey;">.</span>is_activation_enabled<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"><span style="mso-tab-count: 1;"> </span><span style="mso-spacerun: yes;"> </span><span style="color: grey;">,</span>o<span style="color: grey;">.</span>is_receive_enabled<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"><span style="mso-tab-count: 1;"> </span><span style="mso-spacerun: yes;"> </span><span style="color: grey;">,</span>o<span style="color: grey;">.</span>is_enqueue_enabled<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"><span style="mso-tab-count: 1;"> </span><span style="mso-spacerun: yes;"> </span><span style="color: grey;">,</span>o<span style="color: grey;">.</span>is_retention_enabled<span style="mso-spacerun: yes;"> </span><o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">FROM</span><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"> <span style="color: green;">sys</span><span style="color: grey;">.</span>objects$ o<span style="mso-spacerun: yes;"> </span><o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;"> </span><span style="color: grey;">LEFT</span> <span style="color: grey;">JOIN</span> <span style="color: green;">sys</span><span style="color: grey;">.</span>sysobjvalues v <o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;"> </span><span style="color: blue;">ON</span> v<span style="color: grey;">.</span>valclass <span style="color: grey;">=</span> 12 <span style="color: grey;">AND</span> v<span style="color: grey;">.</span><span style="color: blue;">objid</span> <span style="color: grey;">=</span> o<span style="color: grey;">.</span><span style="color: magenta;">object_id</span> <span style="color: grey;">AND</span> v<span style="color: grey;">.</span>subobjid <span style="color: grey;">=</span> 0 <span style="color: grey;">AND</span> v<span style="color: grey;">.</span>valnum <span style="color: grey;">=</span> 0 <span style="color: green;">-- SVC_QUEUEACTIVPROC<span style="mso-spacerun: yes;"> </span><o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;"> </span><span style="color: grey;">LEFT</span> <span style="color: grey;">JOIN</span> <span style="color: green;">sys</span><span style="color: grey;">.</span>syssingleobjrefs e <o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;"> </span><span style="color: blue;">ON</span> e<span style="color: grey;">.</span>depid <span style="color: grey;">=</span> o<span style="color: grey;">.</span><span style="color: magenta;">object_id</span> <span style="color: grey;">AND</span> e<span style="color: grey;">.</span>class <span style="color: grey;">=</span> 22 <span style="color: grey;">AND</span> e<span style="color: grey;">.</span>depsubid <span style="color: grey;">=</span> 0 <span style="color: green;">-- SRC_OBJEXECASOWNER<span style="mso-spacerun: yes;"> </span><o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">WHERE</span><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"> o<span style="color: grey;">.</span><span style="color: blue;">type</span> <span style="color: grey;">=</span> <span style="color: red;">'SQ'<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="font-family: 'Courier New'; font-size: 10pt;"> </span><span style="color: grey;"><o:p></o:p></span></div><div class="MsoNormal"><o:p><span class="Apple-style-span" style="font-family: Arial, Helvetica, sans-serif;"> Very cool stuff.</span></o:p></div><br />
<div class="MsoNormal"><span class="apple-style-span"><span style="color: grey; font-family: "Courier New"; font-size: 10.0pt; line-height: 115%;"><span class="Apple-style-span" style="color: black; font-family: 'Times New Roman'; font-size: medium; line-height: normal;"><br />
</span></span></span></div>John Henneseyhttp://www.blogger.com/profile/14350314173210731480noreply@blogger.com0tag:blogger.com,1999:blog-7482289774765508546.post-14243844126129601352010-11-04T08:33:00.000-04:002010-11-04T08:33:27.578-04:00Find out last backup time for all databases<span class="Apple-style-span" style="font-family: Arial, Helvetica, sans-serif;"><span class="Apple-style-span" style="font-size: small;">This is merely a <a href="http://blog.sqlauthority.com/2010/11/04/sql-server-finding-last-backup-time-for-all-database/">re-post of a script</a> done on <a href="http://blog.sqlauthority.com/">SQL Authority</a> that I placed on my site so I can easily find it in the future.</span></span><br />
<span class="Apple-style-span" style="font-family: Arial, Helvetica, sans-serif;"><span class="Apple-style-span" style="font-size: small;"><br />
</span></span><br />
<span class="Apple-style-span" style="font-family: Arial, Helvetica, sans-serif;"><span class="Apple-style-span" style="font-size: small;">If you have not read any of Pinal Dave's blogs (SQL Authority), you should - he posts some very good information and knows a vast amount about SQL and it's inner workings.</span></span><br />
<span class="Apple-style-span" style="font-family: Arial, Helvetica, sans-serif;"><span class="Apple-style-span" style="font-size: small;"><br />
</span></span><br />
<span class="Apple-style-span" style="font-family: Arial, Helvetica, sans-serif;"><span class="Apple-style-span" style="font-size: small;"> This script will display by database the last time it was backed up.</span></span><br />
<br />
<br />
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">SELECT</span><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"> sdb<span style="color: grey;">.</span>Name <span style="color: blue;">AS</span> DatabaseName<span style="color: grey;">,<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="color: magenta; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">COALESCE</span><span style="color: grey; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">(</span><span style="color: magenta; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">CONVERT</span><span style="color: grey; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">(</span><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">VARCHAR</span><span style="color: grey; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">(</span><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">12<span style="color: grey;">),</span> <span style="color: magenta;">MAX</span><span style="color: grey;">(</span>bus<span style="color: grey;">.</span>backup_finish_date<span style="color: grey;">),</span> 101<span style="color: grey;">),</span><span style="color: red;">'-'</span><span style="color: grey;">)</span> <span style="color: blue;">AS</span> LastBackUpTime<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">FROM</span><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"> <span style="color: green;">sys</span><span style="color: grey;">.</span><span style="color: green;">sysdatabases</span> sdb<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="color: grey; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">LEFT</span><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"> <span style="color: grey;">OUTER</span> <span style="color: grey;">JOIN</span> msdb<span style="color: grey;">.</span>dbo<span style="color: grey;">.</span>backupset bus <span style="color: blue;">ON</span> bus<span style="color: grey;">.</span>database_name <span style="color: grey;">=</span> sdb<span style="color: grey;">.</span>name<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">GROUP</span><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"> <span style="color: blue;">BY</span> sdb<span style="color: grey;">.</span>Name<o:p></o:p></span></div><div class="MsoNormal"><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-no-proof: yes;">Order</span><span style="font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-no-proof: yes;"> <span style="color: blue;">by</span> 2</span><span style="font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 115%;"><o:p></o:p></span></div>John Henneseyhttp://www.blogger.com/profile/14350314173210731480noreply@blogger.com0tag:blogger.com,1999:blog-7482289774765508546.post-39656862584662386042010-11-02T20:37:00.000-04:002010-11-02T20:37:13.406-04:00Multi Column Report in Reporting Services<div class="MsoNormal"><span style="font-family: Arial, sans-serif; line-height: 115%;"><span class="Apple-style-span" style="font-size: small;">Reporting Services in all of its awesomeness is lacking in one area- multi-column (or newspaper style) reports. It can do it, but only certain rendering formats support it. One way around it in RS 2008+ is to use the oh so handy tablix control and the TSQL row_number() function. Here’s how it works:</span></span><br />
<span style="font-family: Arial, sans-serif; line-height: 115%;"><span class="Apple-style-span" style="font-size: small;"></span></span><span class="Apple-style-span" style="font-family: Arial, sans-serif; font-size: small; line-height: 18px;"><span style="font: normal normal normal 7pt/normal 'Times New Roman';"> </span></span></div><div class="MsoListParagraph" style="mso-list: l0 level1 lfo1; text-indent: -.25in;"><span class="Apple-style-span" style="font-family: Arial, sans-serif; line-height: 14px;"><span class="Apple-style-span" style="font-size: small;"> 1. Create a query that returns the information you want. Include 2 additional columns: Row Number and Column Number. The purpose of these is to show the x/y coordinates in the tablix group. The example query I use will pull a list of tables and columns from the information_schema view:</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="color: blue; font-family: 'Courier New';"><span class="Apple-style-span" style="font-size: small;">declare</span></span><span style="font-family: 'Courier New';"><span class="Apple-style-span" style="font-size: small;"> @numberOfColumns <span style="color: blue;">int</span> <span style="color: grey;">=</span> 3<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;"><span class="Apple-style-span" style="font-size: small;"><br />
</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="color: blue; font-family: 'Courier New';"><span class="Apple-style-span" style="font-size: small;">select</span></span><span style="font-family: 'Courier New';"><span class="Apple-style-span" style="font-size: small;"> table_name<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="font-family: 'Courier New';"><span class="Apple-style-span" style="font-size: small;"> </span><span style="color: grey;"><span class="Apple-style-span" style="font-size: small;">,</span></span><span class="Apple-style-span" style="font-size: small;">column_name<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="font-family: 'Courier New';"><span class="Apple-style-span" style="font-size: small;"> </span><span style="color: grey;"><span class="Apple-style-span" style="font-size: small;">,</span></span><span class="Apple-style-span" style="font-size: small;">[row_number] <span style="color: grey;">=</span><span style="color: blue;"> </span><span style="color: grey;">(</span> <span style="color: magenta;">ROW_NUMBER</span><span style="color: grey;">()</span> <span style="color: blue;">over </span><span style="color: grey;">(</span><span style="color: blue;">partition</span> <span style="color: blue;">by</span> table_name <span style="color: blue;">order</span> <span style="color: blue;">by</span> table_name<span style="color: grey;">)</span> <span style="color: grey;">-</span> 1 <span style="color: grey;">)</span> <span style="color: grey;">/</span> @numberOfColumns<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="font-family: 'Courier New';"><span class="Apple-style-span" style="font-size: small;"> </span><span style="color: grey;"><span class="Apple-style-span" style="font-size: small;">,</span></span><span class="Apple-style-span" style="font-size: small;">[column_number] <span style="color: grey;">=</span><span style="color: blue;"> </span><span style="color: grey;">(</span> <span style="color: magenta;">ROW_NUMBER</span><span style="color: grey;">()</span> <span style="color: blue;">over </span><span style="color: grey;">(</span><span style="color: blue;">partition</span> <span style="color: blue;">by</span> table_name <span style="color: blue;">order</span> <span style="color: blue;">by</span> table_name<span style="color: grey;">)</span> <span style="color: grey;">-</span> 1<span style="color: grey;">)</span> <span style="color: grey;">%</span> @numberOfColumns<o:p></o:p></span></span></div><div class="MsoNormal" style="margin-left: .5in;"><span style="color: blue; font-family: 'Courier New'; line-height: 115%;"><span class="Apple-style-span" style="font-size: small;">from</span></span><span style="font-family: 'Courier New'; line-height: 115%;"><span class="Apple-style-span" style="font-size: small;"> <span style="color: green;">INFORMATION_SCHEMA</span><span style="color: grey;">.</span><span style="color: green;">COLUMNS</span></span></span><br />
<span style="font-family: 'Courier New'; line-height: 115%;"><span class="Apple-style-span" style="font-size: small;"><span style="color: green;"></span></span></span><span class="Apple-style-span" style="font-size: small;"> </span></div><div class="MsoListParagraph" style="mso-list: l0 level1 lfo1; text-indent: -.25in;"><span style="font: normal normal normal 7pt/normal 'Times New Roman';"><span class="Apple-style-span" style="font-size: small;"></span></span><span class="Apple-style-span" style="font-size: small;"> </span><span class="Apple-style-span" style="font-family: Arial, sans-serif; font-size: small; line-height: 14px;">2. Create a new report, place a matrix (tablix) on the report body, and create the groups as follows:</span></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhM9NgeTfjPf2nWbc_qduce3U29lJjDxbUbg1ZYDtY6mX3kBAUeu5yb4Z_GO3_QC08Ev_SmMVoaYQ0j_SQfvdqkWOyj5tSKjJoy7gsf4qLuX_qAXytNnWjYrejPNjytNMjfLOe_Zh_iBSz9/s1600/figure1.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span class="Apple-style-span" style="font-size: small;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhM9NgeTfjPf2nWbc_qduce3U29lJjDxbUbg1ZYDtY6mX3kBAUeu5yb4Z_GO3_QC08Ev_SmMVoaYQ0j_SQfvdqkWOyj5tSKjJoy7gsf4qLuX_qAXytNnWjYrejPNjytNMjfLOe_Zh_iBSz9/s1600/figure1.jpg" /></span></a></div><div class="separator" style="clear: both; text-align: left;"><span style="line-height: 115%;"><span style="font: normal normal normal 7pt/normal 'Times New Roman';"><span class="Apple-style-span" style="font-family: Arial, Helvetica, sans-serif;"><span class="Apple-style-span" style="font-size: small;"><br />
</span></span></span></span></div><div class="separator" style="clear: both; text-align: left;"><span style="line-height: 115%;"><span style="font: normal normal normal 7pt/normal 'Times New Roman';"><span class="Apple-style-span" style="font-family: Arial, Helvetica, sans-serif;"><span class="Apple-style-span" style="font-size: small;">3. </span></span></span></span><span style="line-height: 115%;"><span class="Apple-style-span" style="font-family: Arial, Helvetica, sans-serif;"><span class="Apple-style-span" style="font-size: small;">For </span></span><span class="Apple-style-span" style="font-family: Arial, sans-serif;"><span class="Apple-style-span" style="font-size: small;">the </span></span></span><span style="font-family: Arial, sans-serif; line-height: 115%;"><span class="Apple-style-span" style="font-size: small;">column title I set the value of the expression to show the column number returned by the query: </span></span><span style="font-family: 'Courier New'; line-height: 115%;"><span class="Apple-style-span" style="font-size: small;">=<span style="color: #a31515;">"Column "</span> & Fields!column_number.Value</span></span></div><span style="font: normal normal normal 7pt/normal 'Times New Roman';"><span class="Apple-style-span" style="font-family: Arial, sans-serif;"><span class="Apple-style-span" style="line-height: 14px;"><span class="Apple-style-span" style="font-size: small;"><br />
</span></span></span></span><br />
<span style="font: normal normal normal 7pt/normal 'Times New Roman';"><span class="Apple-style-span" style="font-family: Arial, sans-serif;"><span class="Apple-style-span" style="line-height: 14px;"><span class="Apple-style-span" style="font-size: small;">4. </span></span></span></span><span style="font-family: Arial, sans-serif; line-height: 115%;"><span class="Apple-style-span" style="font-size: small;">Add some alternating background coloring to make the report more readable : </span></span><span style="font-family: 'Courier New'; line-height: 115%;"><span class="Apple-style-span" style="font-size: small;">=iif(RunningValue(Fields!table_name.Value,<span style="color: blue;">CountDistinct</span>,<span style="color: blue;">Nothing</span>) <span style="color: blue;">Mod</span> 2, <span style="color: #a31515;">"AliceBlue"</span>, <span style="color: #a31515;">"White"</span>)</span></span><br />
<span style="font-family: 'Courier New'; line-height: 115%;"><span class="Apple-style-span" style="font-size: small;"><br />
</span></span><br />
<span style="font-family: 'Courier New'; line-height: 115%;"><span class="Apple-style-span" style="font-size: small;"></span></span><br />
<div class="MsoNormal"><span style="font-family: 'Courier New'; line-height: 115%;"><span style="font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 115%;">And presto!<span style="mso-spacerun: yes;"> </span>When you view your report it will show you a nicely formatted multiple column report.<span style="mso-spacerun: yes;"> </span>For fun, you can make the @numberOfColumns a parameter for the report and allow dynamic column counts.<o:p></o:p></span></span></div><div class="separator" style="clear: both; text-align: center;"><span style="font-family: 'Courier New'; line-height: 115%;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjj5I9w5OpHEpk3BC0qd-lnNcJ2IwcglmYIxI6P-W4XP1SdBmzC1DodcC6G40xgpteXg_GASHOaEdRdUFUblNkl2_N6BV4b1ARjQ4CN6CbSV2apjATdp6m0GM5J95xYa6klhozxTqEC8APs/s1600/figure2.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="365" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjj5I9w5OpHEpk3BC0qd-lnNcJ2IwcglmYIxI6P-W4XP1SdBmzC1DodcC6G40xgpteXg_GASHOaEdRdUFUblNkl2_N6BV4b1ARjQ4CN6CbSV2apjATdp6m0GM5J95xYa6klhozxTqEC8APs/s640/figure2.jpg" width="640" /></a></span></div><div class="MsoNormal"><span style="font-family: 'Courier New'; line-height: 115%;"><span style="font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 115%;"><br />
</span></span></div><br />
<span style="font-family: 'Courier New'; line-height: 115%;"><span class="Apple-style-span" style="font-size: small;"><br />
</span></span><br />
<div class="MsoListParagraph" style="mso-list: l0 level1 lfo1; text-indent: -.25in;"><span style="font-family: Arial, sans-serif; font-size: 10pt; line-height: 115%;"><o:p></o:p></span></div>John Henneseyhttp://www.blogger.com/profile/14350314173210731480noreply@blogger.com0tag:blogger.com,1999:blog-7482289774765508546.post-5672278557733899082010-10-25T14:04:00.000-04:002010-10-25T14:04:22.125-04:00Forms Authentication against Active DirectoryWhile trying to authenticate against AD I ran into some problems and was wondering if it was my ldap connection string. Found a great way to verify it with the Group Policy Result tool (<a href="http://www.microsoft.com/windowsxp/using/setup/expert/gpresults.mspx">http://www.microsoft.com/windowsxp/using/setup/expert/gpresults.mspx</a>)<br />
<blockquote>gpresult.exe /r</blockquote>It lists all sorts of items about the currently logged in user - look for the "User Settings" section, and the first line shows you the exact path to your entry. In my case:<br />
<br />
CN=John Hennesey,CN=Users,DC=domain,DC=com<br />
<br />
and there is an entry "group policy was applied from" - there's your server.domain.com. <br />
<br />
and the web.config entries (no magic here - this is on thousands of web pages - this is for easy reference in the future)<br />
<br />
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span class="Apple-style-span" style="color: blue; font-family: 'Courier New'; font-size: small;"><span class="Apple-style-span" style="font-size: 13px;"><br />
</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span class="Apple-style-span" style="color: blue; font-family: 'Courier New'; font-size: small;"><span class="Apple-style-span" style="font-size: 13px;"></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;"><span class="Apple-style-span" style="color: blue; font-family: 'Courier New'; font-size: small;"><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"><</span><span style="color: #a31515; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">connectionStrings</span><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">><o:p></o:p></span></span></div><span class="Apple-style-span" style="color: blue; font-family: 'Courier New'; font-size: small;"> <div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"><span style="mso-tab-count: 1;"> </span><</span><span style="color: #a31515; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">add</span><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"> </span><span style="color: red; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">name</span><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">=</span><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">"<span style="color: blue;">ADConnectionString</span>"<span style="color: blue;"> </span><span style="color: red;">connectionString</span><span style="color: blue;">=</span>"<span style="color: blue;">LDAP://server.domain.com/CN=Users,DC=domain,DC=com</span>"<span style="color: blue;">/><o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"></</span><span style="color: #a31515; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">connectionStrings</span><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">><o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"><</span><span style="color: #a31515; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">authentication</span><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"> </span><span style="color: red; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">mode</span><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">=</span><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">"<span style="color: blue;">Forms</span>"<span style="color: blue;">><o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"><span style="mso-tab-count: 1;"> </span><</span><span style="color: #a31515; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">forms</span><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"> </span><span style="color: red; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">name</span><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">=</span><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">"<span style="color: blue;">.ADAuthCookie</span>"<span style="color: blue;"> </span><span style="color: red;">timeout</span><span style="color: blue;">=</span>"<span style="color: blue;">10</span>"<span style="color: blue;">/><o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"><span style="mso-tab-count: 1;"> </span></</span><span style="color: #a31515; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">authentication</span><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">><o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"><</span><span style="color: #a31515; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">authorization</span><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">><o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"><span style="mso-tab-count: 1;"> </span><</span><span style="color: #a31515; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">deny</span><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"> </span><span style="color: red; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">users</span><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">=</span><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">"<span style="color: blue;">?</span>"<span style="color: blue;">/><o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none; text-indent: .5in;"><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"><</span><span style="color: #a31515; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">allow</span><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"> </span><span style="color: red; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">users</span><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">=</span><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">"<span style="color: blue;">*</span>"<span style="color: blue;">/><o:p></o:p></span></span></div><div class="MsoNormal" style="margin-left: .5in;"><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-no-proof: yes;"></</span><span style="color: #a31515; font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-no-proof: yes;">authorization</span><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-no-proof: yes;">><o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"><</span><span style="color: #a31515; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">membership</span><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"> </span><span style="color: red; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">defaultProvider</span><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">=</span><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">"<span style="color: blue;">MyADMembershipProvider</span>"<span style="color: blue;">><o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"><span style="mso-tab-count: 1;"> </span><</span><span style="color: #a31515; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">providers</span><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">><o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"><span style="mso-tab-count: 1;"> </span><</span><span style="color: #a31515; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">add</span><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"> </span><span style="color: red; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">name</span><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">=</span><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">"<span style="color: blue;">MyADMembershipProvider</span>"<span style="color: blue;"> </span><span style="color: red;">type</span><span style="color: blue;">=</span>"<span style="color: blue;">System.Web.Security.ActiveDirectoryMembershipProvider, System.Web, Version=2.0.0.0,<span style="mso-spacerun: yes;"> </span>Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a</span>"<span style="color: blue;"> </span><span style="color: red;">connectionStringName</span><span style="color: blue;">=</span>"<span style="color: blue;">ADConnectionString</span>"<span style="color: blue;"> </span><span style="color: red;">attributeMapUsername</span><span style="color: blue;">=</span>"<span style="color: blue;">sAMAccountName</span>"<span style="color: blue;">/><o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"><span style="mso-tab-count: 1;"> </span></</span><span style="color: #a31515; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">providers</span><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">><o:p></o:p></span></div><div class="MsoNormal" style="margin-left: .5in;"><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-no-proof: yes;"></</span><span style="color: #a31515; font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-no-proof: yes;">membership</span><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-no-proof: yes;">></span><span style="font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 115%;"><o:p></o:p></span></div></span><br />
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span class="Apple-style-span" style="color: blue; font-family: 'Courier New'; font-size: small;"><span class="Apple-style-span" style="font-size: 13px;"><br />
</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span class="Apple-style-span" style="color: blue; font-family: 'Courier New'; font-size: small;"><span class="Apple-style-span" style="font-size: 13px;"><span class="Apple-style-span" style="color: black; font-family: 'Times New Roman'; font-size: medium;">Couple this with the asp Login control, and all set. Easy!</span></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span class="Apple-style-span" style="color: blue; font-family: 'Courier New'; font-size: small;"><span class="Apple-style-span" style="font-size: 13px;"><br />
</span></span></div>John Henneseyhttp://www.blogger.com/profile/14350314173210731480noreply@blogger.com0tag:blogger.com,1999:blog-7482289774765508546.post-16494335621391370372010-10-22T23:12:00.000-04:002010-10-22T23:12:11.822-04:00Nolocks on a view - does it nolock all the tables in the view?I received this question from a colleague this week:<br />
<span class="Apple-style-span" style="font-family: sans-serif; font-size: 15px;"><br />
"If there is a view that is not using nolock [in the from clause] but you have a stored procedure that uses nolock when it calls the view then do all the FROMs in the view run with nolock?"</span><br />
<span class="Apple-style-span" style="font-family: sans-serif; font-size: 15px;"><br />
</span><br />
<span class="Apple-style-span" style="font-family: sans-serif; font-size: 15px;">The simple answer : </span><span class="Apple-style-span" style="font-family: sans-serif; font-size: 15px;">Yes, the nolock hint on the view should propagate down to the tables queried in the view*.</span><br />
<span class="Apple-style-span" style="font-family: sans-serif; font-size: 15px;"><br />
</span><br />
<span class="Apple-style-span" style="font-family: sans-serif; font-size: 15px;">A brief visit to our favorite resource, <a href="http://msdn.microsoft.com/en-us/library/ms187373.aspx">Books Online</a>, reveals the following:</span><br />
<blockquote>In SQL Server 2005/2008, all lock hints are propagated to all the tables and views that are referenced in a view. Also, SQL Server performs the corresponding lock consistency checks.</blockquote><br />
<div class="MsoNormal" style="font-family: sans-serif; margin-bottom: 0.0001pt; margin-left: 0in; margin-right: 0in; margin-top: 0in;"><span class="Apple-style-span" style="font-size: medium;"><span class="Apple-style-span" style="font-size: 11pt;"></span></span></div><div class="MsoNormal" style="font-family: sans-serif; font-size: 11pt; margin-bottom: 0.0001pt; margin-left: 0in; margin-right: 0in; margin-top: 0in;"> And the asterisk:</div><blockquote><span class="Apple-style-span" style="font-family: 'times new roman', 'new york', times, serif;">*If a table contains computed columns and the computed columns are computed by expressions or functions accessing columns in other tables, the table hints are not used on those tables. This means the table hints are not propagated. For example, a NOLOCK table hint is specified on a table in the query. This table has computed columns that are computed by a combination of expressions and functions that access columns in another table. The tables referenced by the expressions and functions do not use the NOLOCK table hint when accessed.</span></blockquote><br />
<span class="Apple-style-span" style="color: #1f497d; font-family: Arial, sans-serif; font-size: 15px;">And my (rather simple) test proved it out:</span><br />
<span class="Apple-style-span" style="font-size: 15px;"> </span><br />
<div class="MsoNormal" style="color: navy; font-family: sans-serif; line-height: normal; margin-bottom: 0.0001pt; margin-left: 0.5in; margin-right: 0in; margin-top: 0in;"><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">use</span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";"> </span><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">master</span><span style="color: black; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div><div class="MsoNormal" style="color: navy; font-family: sans-serif; line-height: normal; margin-bottom: 0.0001pt; margin-left: 0.5in; margin-right: 0in; margin-top: 0in;"><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">go</span><span style="color: black; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div><div class="MsoNormal" style="color: navy; font-family: sans-serif; line-height: normal; margin-bottom: 0.0001pt; margin-left: 0.5in; margin-right: 0in; margin-top: 0in;"><br />
</div><div class="MsoNormal" style="color: navy; font-family: sans-serif; line-height: normal; margin-bottom: 0.0001pt; margin-left: 0.5in; margin-right: 0in; margin-top: 0in;"><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">create</span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";"> </span><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">database</span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";"> </span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">lockTest</span><span style="color: black; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div><div class="MsoNormal" style="color: navy; font-family: sans-serif; line-height: normal; margin-bottom: 0.0001pt; margin-left: 0.5in; margin-right: 0in; margin-top: 0in;"><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">go</span><span style="color: black; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div><div class="MsoNormal" style="color: navy; font-family: sans-serif; line-height: normal; margin-bottom: 0.0001pt; margin-left: 0.5in; margin-right: 0in; margin-top: 0in;"><br />
</div><div class="MsoNormal" style="color: navy; font-family: sans-serif; line-height: normal; margin-bottom: 0.0001pt; margin-left: 0.5in; margin-right: 0in; margin-top: 0in;"><br />
</div><div class="MsoNormal" style="color: navy; font-family: sans-serif; line-height: normal; margin-bottom: 0.0001pt; margin-left: 0.5in; margin-right: 0in; margin-top: 0in;"><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">use</span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";"> </span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">lockTest</span><span style="color: black; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div><div class="MsoNormal" style="color: navy; font-family: sans-serif; line-height: normal; margin-bottom: 0.0001pt; margin-left: 0.5in; margin-right: 0in; margin-top: 0in;"><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">go</span><span style="color: black; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div><div class="MsoNormal" style="color: navy; font-family: sans-serif; line-height: normal; margin-bottom: 0.0001pt; margin-left: 0.5in; margin-right: 0in; margin-top: 0in;"><br />
</div><div class="MsoNormal" style="color: navy; font-family: sans-serif; line-height: normal; margin-bottom: 0.0001pt; margin-left: 0.5in; margin-right: 0in; margin-top: 0in;"><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">create</span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";"> </span><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">table</span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";"> </span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">t1</span><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";"> </span><span style="color: grey; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">(</span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">f1</span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";"> </span><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">int</span><span style="color: grey; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">,</span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";"> </span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">f2</span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";"> </span><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">int</span><span style="color: grey; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">)</span><span style="color: black; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div><div class="MsoNormal" style="color: navy; font-family: sans-serif; line-height: normal; margin-bottom: 0.0001pt; margin-left: 0.5in; margin-right: 0in; margin-top: 0in;"><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">go</span><span style="color: black; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div><div class="MsoNormal" style="color: navy; font-family: sans-serif; line-height: normal; margin-bottom: 0.0001pt; margin-left: 0.5in; margin-right: 0in; margin-top: 0in;"><br />
</div><div class="MsoNormal" style="color: navy; font-family: sans-serif; line-height: normal; margin-bottom: 0.0001pt; margin-left: 0.5in; margin-right: 0in; margin-top: 0in;"><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">insert</span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";"> </span><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">into</span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";"> </span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">t1</span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";"> </span><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">values</span><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";"> </span><span style="color: grey; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">(</span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">1</span><span style="color: grey; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">,</span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";"> </span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">1</span><span style="color: grey; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">)</span><span style="color: black; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div><div class="MsoNormal" style="color: navy; font-family: sans-serif; line-height: normal; margin-bottom: 0.0001pt; margin-left: 0.5in; margin-right: 0in; margin-top: 0in;"><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">insert</span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";"> </span><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">into</span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";"> </span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">t1</span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";"> </span><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">values</span><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";"> </span><span style="color: grey; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">(</span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">2</span><span style="color: grey; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">,</span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";"> </span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">2</span><span style="color: grey; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">)</span><span style="color: black; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div><div class="MsoNormal" style="color: navy; font-family: sans-serif; line-height: normal; margin-bottom: 0.0001pt; margin-left: 0.5in; margin-right: 0in; margin-top: 0in;"><br />
</div><div class="MsoNormal" style="color: navy; font-family: sans-serif; line-height: normal; margin-bottom: 0.0001pt; margin-left: 0.5in; margin-right: 0in; margin-top: 0in;"><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">go</span><span style="color: black; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div><div class="MsoNormal" style="color: navy; font-family: sans-serif; line-height: normal; margin-bottom: 0.0001pt; margin-left: 0.5in; margin-right: 0in; margin-top: 0in;"><br />
</div><div class="MsoNormal" style="color: navy; font-family: sans-serif; line-height: normal; margin-bottom: 0.0001pt; margin-left: 0.5in; margin-right: 0in; margin-top: 0in;"><br />
</div><div class="MsoNormal" style="color: navy; font-family: sans-serif; line-height: normal; margin-bottom: 0.0001pt; margin-left: 0.5in; margin-right: 0in; margin-top: 0in;"><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">create</span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";"> </span><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">view</span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";"> </span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">v1</span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";"> </span><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">as</span><span style="color: black; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div><div class="MsoNormal" style="color: navy; font-family: sans-serif; line-height: normal; margin-bottom: 0.0001pt; margin-left: 0.5in; margin-right: 0in; margin-top: 0in;"><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">select</span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";"> </span><span style="color: grey; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">*</span><span style="color: black; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div><div class="MsoNormal" style="color: navy; font-family: sans-serif; line-height: normal; margin-bottom: 0.0001pt; margin-left: 0.5in; margin-right: 0in; margin-top: 0in;"><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">from</span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";"> </span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">t1</span><span style="color: black; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div><div class="MsoNormal" style="color: navy; font-family: sans-serif; line-height: normal; margin-bottom: 0.0001pt; margin-left: 0.5in; margin-right: 0in; margin-top: 0in;"><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">go</span><span style="color: black; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div><div class="MsoNormal" style="color: navy; font-family: sans-serif; line-height: normal; margin-bottom: 0.0001pt; margin-left: 0.5in; margin-right: 0in; margin-top: 0in;"><br />
</div><div class="MsoNormal" style="color: navy; font-family: sans-serif; line-height: normal; margin-bottom: 0.0001pt; margin-left: 0.5in; margin-right: 0in; margin-top: 0in;"><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">set</span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";"> </span><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">transaction</span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";"> </span><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">isolation</span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";"> </span><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">level</span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";"> </span><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">serializable</span><span style="color: black; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div><div class="MsoNormal" style="color: navy; font-family: sans-serif; line-height: normal; margin-bottom: 0.0001pt; margin-left: 0.5in; margin-right: 0in; margin-top: 0in;"><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">go</span><span style="color: black; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div><div class="MsoNormal" style="color: navy; font-family: sans-serif; line-height: normal; margin-bottom: 0.0001pt; margin-left: 0.5in; margin-right: 0in; margin-top: 0in;"><br />
</div><div class="MsoNormal" style="color: navy; font-family: sans-serif; line-height: normal; margin-bottom: 0.0001pt; margin-left: 0.5in; margin-right: 0in; margin-top: 0in;"><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">begin</span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";"> </span><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">tran</span><span style="color: black; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div><div class="MsoNormal" style="color: navy; font-family: sans-serif; line-height: normal; margin-bottom: 0.0001pt; margin-left: 0.5in; margin-right: 0in; margin-top: 0in;"><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">select</span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";"> </span><span style="color: grey; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">*</span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";"> </span><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">from</span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";"> </span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">v1</span><span style="color: black; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div><div class="MsoNormal" style="color: navy; font-family: sans-serif; line-height: normal; margin-bottom: 0.0001pt; margin-left: 0.5in; margin-right: 0in; margin-top: 0in;"><br />
</div><div class="MsoNormal" style="color: navy; font-family: sans-serif; line-height: normal; margin-bottom: 0.0001pt; margin-left: 0.5in; margin-right: 0in; margin-top: 0in;"><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">insert</span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";"> </span><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">into</span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";"> </span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">v1</span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";"> </span><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">values</span><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";"> </span><span style="color: grey; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">(</span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">3</span><span style="color: grey; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">,</span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">3</span><span style="color: grey; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">)</span><span style="color: black; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div><div class="MsoNormal" style="color: navy; font-family: sans-serif; line-height: normal; margin-bottom: 0.0001pt; margin-left: 0.5in; margin-right: 0in; margin-top: 0in;"><br />
</div><div class="MsoNormal" style="color: navy; font-family: sans-serif; line-height: normal; margin-bottom: 0.0001pt; margin-left: 0.5in; margin-right: 0in; margin-top: 0in;"><span style="color: green; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">-- go into a new window and run the following:</span><span style="color: black; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div><div class="MsoNormal" style="color: navy; font-family: sans-serif; line-height: normal; margin-bottom: 0.0001pt; margin-left: 0.5in; margin-right: 0in; margin-top: 0in;"><span style="color: green; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">/*</span><span style="color: black; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div><div class="MsoNormal" style="color: navy; font-family: sans-serif; line-height: normal; margin-bottom: 0.0001pt; margin-left: 0.5in; margin-right: 0in; margin-top: 0in;"><span style="color: green; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">-- Works</span><span style="color: black; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div><div class="MsoNormal" style="color: navy; font-family: sans-serif; line-height: normal; margin-bottom: 0.0001pt; margin-left: 0.5in; margin-right: 0in; margin-top: 0in;"><span style="color: green; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">select *</span><span style="color: black; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div><div class="MsoNormal" style="color: navy; font-family: sans-serif; line-height: normal; margin-bottom: 0.0001pt; margin-left: 0.5in; margin-right: 0in; margin-top: 0in;"><span style="color: green; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">from v1 with (nolock)</span><span style="color: black; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div><div class="MsoNormal" style="color: navy; font-family: sans-serif; line-height: normal; margin-bottom: 0.0001pt; margin-left: 0.5in; margin-right: 0in; margin-top: 0in;"><br />
</div><div class="MsoNormal" style="color: navy; font-family: sans-serif; line-height: normal; margin-bottom: 0.0001pt; margin-left: 0.5in; margin-right: 0in; margin-top: 0in;"><span style="color: green; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">-- Hangs</span><span style="color: black; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div><div class="MsoNormal" style="color: navy; font-family: sans-serif; line-height: normal; margin-bottom: 0.0001pt; margin-left: 0.5in; margin-right: 0in; margin-top: 0in;"><span style="color: green; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">select *</span><span style="color: black; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div><div class="MsoNormal" style="color: navy; font-family: sans-serif; line-height: normal; margin-bottom: 0.0001pt; margin-left: 0.5in; margin-right: 0in; margin-top: 0in;"><span style="color: green; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">from v1</span><span style="color: black; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div><div class="MsoNormal" style="color: navy; font-family: sans-serif; line-height: normal; margin-bottom: 0.0001pt; margin-left: 0.5in; margin-right: 0in; margin-top: 0in;"><span style="color: green; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">*/</span><span style="color: black; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div><div class="MsoNormal" style="color: navy; font-family: sans-serif; line-height: normal; margin-bottom: 0.0001pt; margin-left: 0.5in; margin-right: 0in; margin-top: 0in;"><br />
</div><div class="MsoNormal" style="color: navy; font-family: sans-serif; line-height: normal; margin-bottom: 0.0001pt; margin-left: 0.5in; margin-right: 0in; margin-top: 0in;"><br />
</div><div class="MsoNormal" style="color: navy; font-family: sans-serif; line-height: normal; margin-bottom: 0.0001pt; margin-left: 0.5in; margin-right: 0in; margin-top: 0in;"><br />
</div><div class="MsoNormal" style="color: navy; font-family: sans-serif; line-height: normal; margin-bottom: 0.0001pt; margin-left: 0.5in; margin-right: 0in; margin-top: 0in;"><span style="color: green; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">/*</span><span style="color: black; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div><div class="MsoNormal" style="color: navy; font-family: sans-serif; line-height: normal; margin-bottom: 0.0001pt; margin-left: 0.5in; margin-right: 0in; margin-top: 0in;"><span style="color: green; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">-- cleanup</span><span style="color: black; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div><div class="MsoNormal" style="color: navy; font-family: sans-serif; line-height: normal; margin-bottom: 0.0001pt; margin-left: 0.5in; margin-right: 0in; margin-top: 0in;"><span style="color: green; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">use master</span><span style="color: black; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div><div class="MsoNormal" style="color: navy; font-family: sans-serif; line-height: normal; margin-bottom: 0.0001pt; margin-left: 0.5in; margin-right: 0in; margin-top: 0in;"><span style="color: green; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">go</span><span style="color: black; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div><div class="MsoNormal" style="color: navy; font-family: sans-serif; line-height: normal; margin-bottom: 0.0001pt; margin-left: 0.5in; margin-right: 0in; margin-top: 0in;"><br />
</div><div class="MsoNormal" style="color: navy; font-family: sans-serif; line-height: normal; margin-bottom: 0.0001pt; margin-left: 0.5in; margin-right: 0in; margin-top: 0in;"><span style="color: green; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">drop database lockTest</span><span style="color: black; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div><div class="MsoNormal" style="color: navy; font-family: sans-serif; line-height: normal; margin-bottom: 0.0001pt; margin-left: 0.5in; margin-right: 0in; margin-top: 0in;"><span style="color: green; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">go</span><span style="color: black; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div><div class="MsoNormal" style="color: navy; font-family: sans-serif; line-height: normal; margin-bottom: 0.0001pt; margin-left: 0.5in; margin-right: 0in; margin-top: 0in;"><span style="color: green; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">*/</span><span style="color: black; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div><div class="MsoNormal" style="color: navy; font-family: sans-serif;"><br />
</div><br />
<span class="Apple-style-span" style="color: navy; font-family: sans-serif; font-size: 15px;"><br />
</span>John Henneseyhttp://www.blogger.com/profile/14350314173210731480noreply@blogger.com0tag:blogger.com,1999:blog-7482289774765508546.post-76264202106537120762010-10-22T22:52:00.000-04:002010-10-22T22:52:03.811-04:00Sql - search for column commentsTechnical reference - an easy way to query the system tables to search for column comments.<br />
<br />
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">select</span><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: grey;">*</span></span><br />
<div class="MsoNormal" style="text-autospace: none;"><span style="font-family: 'Courier New'; font-size: 10pt;"><span class="Apple-style-span" style="color: blue;">from s</span><span style="color: green;">ys</span><span style="color: grey;">.</span><span style="color: green;">extended_properties</span> <o:p></o:p></span></div><div class="MsoNormal" style="text-autospace: none;"><span style="font-family: 'Courier New'; font-size: 10pt;"><span class="Apple-style-span" style="color: blue;">where </span>class <span style="color: grey;">=</span> 1 </span></div><div class="MsoNormal" style="text-autospace: none;"><span style="font-family: 'Courier New'; font-size: 10pt;"><span style="color: grey;"> and </span></span><span class="Apple-style-span" style="font-family: 'Courier New'; font-size: 13px;">major_id <span style="color: grey;">=</span> <span style="color: magenta;">object_id</span><span style="color: grey;">(</span><span style="color: red;">N'<<schema>>.<<table>>'</span><span style="color: grey;">)</span> </span></div><div class="MsoNormal" style="text-autospace: none;"><span class="Apple-style-span" style="font-family: 'Courier New'; font-size: 13px;"><span style="color: grey;"> and </span></span><span class="Apple-style-span" style="font-family: 'Courier New'; font-size: 13px;">name <span style="color: grey;">=</span> <span style="color: red;">'MS_Description'</span></span></div>John Henneseyhttp://www.blogger.com/profile/14350314173210731480noreply@blogger.com0tag:blogger.com,1999:blog-7482289774765508546.post-70122453614641617252010-10-22T22:47:00.000-04:002010-10-22T22:47:42.754-04:00Reporting Services 2008 over SSL - Certificates<div class="MsoNormal"><span style="font-family: "Arial","sans-serif"; font-size: 10.0pt;">(If you are trying to run RS over SSL and want to cut to the chase, skip this paragraph of frustration)<o:p></o:p></span></div><div class="MsoNormal"><br />
</div><div class="MsoNormal"><span style="font-family: "Arial","sans-serif"; font-size: 10.0pt;">Disclaimer – I am not that savvy with administration – better at the nuts & bolts of programming, so most of this was learning.<o:p></o:p></span></div><div class="MsoNormal"><br />
</div><span style="font-family: "Arial","sans-serif"; font-size: 10.0pt; mso-ansi-language: EN-US; mso-bidi-language: AR-SA; mso-fareast-font-family: Calibri; mso-fareast-language: EN-US; mso-fareast-theme-font: minor-latin;">Today I needed to experiment with the LogonUser method of the web service – the only way to call it is over SSL.<span style="mso-spacerun: yes;"> </span>OK, easy enough, just tell Reporting Services to use SSL – just a check box, right?<span style="mso-spacerun: yes;"> </span>Nope.<span style="mso-spacerun: yes;"> </span>Do a little reading – all signs point to HttpCfg.exe.<span style="mso-spacerun: yes;"> </span>Unless using Vista.<span style="mso-spacerun: yes;"> </span>Then it’s netsh.exe.<span style="mso-spacerun: yes;"> </span>Need a certificate – must generate one.<span style="mso-spacerun: yes;"> </span>Use MakeCert.<span style="mso-spacerun: yes;"> </span>OK, got all my parameters in check – generated.<span style="mso-spacerun: yes;"> </span>What’s the hash?<span style="mso-spacerun: yes;"> </span>Use certutil to get the hash (thumbprint), guid (app id).<span style="mso-spacerun: yes;"> </span>Register it with netsh http add …..<span style="mso-spacerun: yes;"> </span>ok, everything all lined up.</span><div><span class="Apple-style-span" style="font-family: Arial, sans-serif; font-size: small;"><span class="Apple-style-span" style="font-size: 13px;"><br />
</span></span><div><span style="font-family: "Arial","sans-serif"; font-size: 10.0pt; mso-ansi-language: EN-US; mso-bidi-language: AR-SA; mso-fareast-font-family: Calibri; mso-fareast-language: EN-US; mso-fareast-theme-font: minor-latin;"><span style="font-family: "Arial","sans-serif"; font-size: 10.0pt; mso-ansi-language: EN-US; mso-bidi-language: AR-SA; mso-fareast-font-family: Calibri; mso-fareast-language: EN-US; mso-fareast-theme-font: minor-latin;">Now let’s go back into Reporting Services Configuration Manager and instruct it to use the certificate.<span style="mso-spacerun: yes;"> </span>Piece of cake, select it from the drop down and fire it up.<span style="mso-spacerun: yes;"> </span>Error?<span style="mso-spacerun: yes;"> </span>Certificate is not trusted?<span style="mso-spacerun: yes;"> </span>Oh good gracious.<span style="mso-spacerun: yes;"> </span>Keep on researching, find out just how easy it is to do in XP.<span style="mso-spacerun: yes;"> </span>Nothing about how easy it is to do in Vista, just lots of people complaining about how incredibly hard it is.</span></span></div></div><div><span style="font-family: "Arial","sans-serif"; font-size: 10.0pt; mso-ansi-language: EN-US; mso-bidi-language: AR-SA; mso-fareast-font-family: Calibri; mso-fareast-language: EN-US; mso-fareast-theme-font: minor-latin;"><span style="font-family: "Arial","sans-serif"; font-size: 10.0pt; mso-ansi-language: EN-US; mso-bidi-language: AR-SA; mso-fareast-font-family: Calibri; mso-fareast-language: EN-US; mso-fareast-theme-font: minor-latin;"><br />
</span></span></div><div><span class="Apple-style-span" style="font-family: Arial, sans-serif; font-size: small;"><span class="Apple-style-span" style="font-size: 13px;"><br />
</span></span></div><div><div class="MsoNormal" style="font-family: Arial, sans-serif; font-size: 10pt;"><span style="font-family: "Arial","sans-serif"; font-size: 10.0pt;">Oh, what’s this?<span style="mso-spacerun: yes;"> </span>IIS 7 has an easy way to do it?<span style="mso-spacerun: yes;"> </span>Oh YEAH!<span style="mso-spacerun: yes;"> </span>Here are the steps to quickly create a test certificate you can use seamlessly:<o:p></o:p></span></div><div class="MsoNormal" style="font-family: Arial, sans-serif; font-size: 10pt;"><span style="font-family: "Arial","sans-serif"; font-size: 10.0pt;"><br />
</span></div><div class="MsoNormal" style="font-family: Arial, sans-serif; font-size: 10pt;"><span style="font-family: "Arial","sans-serif"; font-size: 10.0pt;">1. </span>Open IIS 7, click on the computer you wish to create the certificate for.<span class="Apple-style-span" style="font-family: Arial, sans-serif; font-size: 13px;"><span style="mso-spacerun: yes;"> </span></span><span class="Apple-style-span" style="font-family: Arial, sans-serif; font-size: 13px;">In the Content View double click on Server Certificates.</span></div><div class="separator" style="clear: both; font-family: Arial, sans-serif; font-size: 10pt; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgbXNsEF3EmGc-9RnTbxkC-1fmjcUQJw0VxGMRiJBWVOoWy6XYmOu6wm05MJF5MfVX5Nn9whB8xgUQxpN0kzleA5zKx9yq0_mNmqkTrh3drTOhbuNRVJBB8OEZmGgpAKhKW-ePdsqLb-DOE/s1600/step+1.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgbXNsEF3EmGc-9RnTbxkC-1fmjcUQJw0VxGMRiJBWVOoWy6XYmOu6wm05MJF5MfVX5Nn9whB8xgUQxpN0kzleA5zKx9yq0_mNmqkTrh3drTOhbuNRVJBB8OEZmGgpAKhKW-ePdsqLb-DOE/s1600/step+1.jpg" /></a></div><div style="font-family: Arial, sans-serif; font-size: 10pt;"><br />
</div><div style="font-family: Arial, sans-serif; font-size: 10pt;"><br />
</div><div><div class="MsoListParagraph" style="text-indent: -0.25in;"><span style="font: normal normal normal 7pt/normal 'Times New Roman';"><span class="Apple-style-span" style="font-family: Arial, sans-serif; font-size: small;"><span class="Apple-style-span" style="font-size: 13px;">2. </span></span><span class="Apple-style-span" style="font-family: Arial, sans-serif; font-size: medium;"><span class="Apple-style-span" style="font-size: 10pt;"> 2. </span></span></span><span style="font-family: Arial, sans-serif; font-size: 10pt;">The top right has an “Actions” menu.<span style="mso-spacerun: yes;"> </span>Click on “Create Certificate Request” – could it really be this easy?</span><!--[if !supportLists]--></div></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh0hcXm6itt3P4LNnWtbIahAujLbzZp-xUbhZy9qS2djizrSjh-EVYxb58BsTRVNQGmyhwdQinBnXW_N3aQRndVVFtc-cbaQ2Jm5-Bt4cSMRnWv1nzD4pHhTE2jYCkR0OK2waEXNTtnJ_cS/s1600/step+1.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh0hcXm6itt3P4LNnWtbIahAujLbzZp-xUbhZy9qS2djizrSjh-EVYxb58BsTRVNQGmyhwdQinBnXW_N3aQRndVVFtc-cbaQ2Jm5-Bt4cSMRnWv1nzD4pHhTE2jYCkR0OK2waEXNTtnJ_cS/s1600/step+1.jpg" /></a></div><div style="font-family: Arial, sans-serif; font-size: 10pt;"><br />
</div><div style="font-family: Arial, sans-serif; font-size: 10pt;"><br />
</div><div style="font-family: Arial, sans-serif; font-size: 10pt;"><br />
</div><div style="font-family: Arial, sans-serif; font-size: 10pt;">3. <span class="Apple-style-span" style="font-family: 'Times New Roman'; font-size: medium;"><span style="font-family: "Arial","sans-serif"; font-size: 10.0pt;">Type in a name for your certificate (yes, it really is this easy)</span></span></div><div class="MsoListParagraph" style="mso-list: l0 level1 lfo1; text-indent: -.25in;"><span class="Apple-style-span" style="font-family: Arial, sans-serif; font-size: medium;"><span class="Apple-style-span" style="font-size: 10pt;"><o:p></o:p></span></span></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg1qazVa6QNHZKuyr0PiZg3E2a4NGMExgMQhDG7FYx7t7hCyfuVjY0vJnub4v8aOpEhN3dtDOoJXYi99M6OdJ5OvtA3Bg6_6_k7EGxk-AkLN_iailjXR9iTZaP6aWpn4_OkBg6XWZkRD_Ex/s1600/step+1.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="301" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg1qazVa6QNHZKuyr0PiZg3E2a4NGMExgMQhDG7FYx7t7hCyfuVjY0vJnub4v8aOpEhN3dtDOoJXYi99M6OdJ5OvtA3Bg6_6_k7EGxk-AkLN_iailjXR9iTZaP6aWpn4_OkBg6XWZkRD_Ex/s400/step+1.jpg" width="400" /></a></div><div style="font-family: Arial, sans-serif; font-size: 10pt;"><br />
</div><div style="font-family: Arial, sans-serif; font-size: 10pt;"><br />
</div><div style="font-family: Arial, sans-serif; font-size: 10pt;">4. <span class="Apple-style-span" style="font-family: 'Times New Roman'; font-size: medium;"><span style="font-family: "Arial","sans-serif"; font-size: 10.0pt;">Launch Reporting Services Configuration Manager, go to “Web Service URL”, select your newly created SSL certificate from the list and click apply.<span style="mso-spacerun: yes;"> </span>It <i>is </i>just that easy!</span></span></div><div class="MsoListParagraph" style="mso-list: l0 level1 lfo1; text-indent: -.25in;"><span class="Apple-style-span" style="font-family: Arial, sans-serif; font-size: medium;"><span class="Apple-style-span" style="font-size: 10pt;"><o:p></o:p></span></span></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiuuxoxM3vA7wi08m1mnQ0HiOOc0e4JWBWenOZ3jBSCv5QsMLA9XPfLzV1bfMPnY5BJsuqLjHBVRV7MS4_QtXKXS0Y7DI3AuWPgxU6b_AHpox9Z0NX6eoF4eq3p5ejmNuoisAZ2D0EhZSTq/s1600/step+1.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="456" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiuuxoxM3vA7wi08m1mnQ0HiOOc0e4JWBWenOZ3jBSCv5QsMLA9XPfLzV1bfMPnY5BJsuqLjHBVRV7MS4_QtXKXS0Y7DI3AuWPgxU6b_AHpox9Z0NX6eoF4eq3p5ejmNuoisAZ2D0EhZSTq/s640/step+1.jpg" width="640" /></a></div><div style="font-family: Arial, sans-serif; font-size: 10pt;"><br />
</div><div style="font-family: Arial, sans-serif; font-size: 10pt;"><br />
</div><div style="font-family: Arial, sans-serif; font-size: 10pt;">And now you can do all of your development testing over SSL locally.</div><div style="font-family: Arial, sans-serif; font-size: 10pt;"><br />
</div><div style="font-family: Arial, sans-serif; font-size: 10pt;"><br />
</div><div style="font-family: Arial, sans-serif; font-size: 10pt;"><br />
</div><div style="font-family: Arial, sans-serif; font-size: 10pt;"><br />
</div><div style="font-family: Arial, sans-serif; font-size: 10pt;"><br />
</div><div style="font-family: Arial, sans-serif; font-size: 10pt;"><br />
</div><!--[if !supportLists]--><div style="font-family: Arial, sans-serif; font-size: 10pt;"></div><div style="font-family: Arial, sans-serif; font-size: 10pt;"></div></div>John Henneseyhttp://www.blogger.com/profile/14350314173210731480noreply@blogger.com0tag:blogger.com,1999:blog-7482289774765508546.post-66426578339106682382010-09-15T10:25:00.001-04:002010-10-22T22:29:17.556-04:00One big cube or multiple cubes?<span class="Apple-style-span" style="font-family: Arial, Helvetica, sans-serif;"><span class="Apple-style-span" style="font-size: small;">Found a good article by a Chris Webb (SQL Server MVP) who knows quite a bit about analysis services. This answers the question of "One cube or Multiple?". Good reading for bigger more complex AS setups.</span></span><br />
<span class="Apple-style-span" style="font-family: Arial, Helvetica, sans-serif;"><span class="Apple-style-span" style="font-size: small;"><br />
</span></span><br />
<a href="http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!7728.entry"><span class="Apple-style-span" style="font-family: Arial, Helvetica, sans-serif;"><span class="Apple-style-span" style="font-size: small;">http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!7728.entry</span></span></a>John Henneseyhttp://www.blogger.com/profile/14350314173210731480noreply@blogger.com0tag:blogger.com,1999:blog-7482289774765508546.post-69667531362324640092010-09-14T21:51:00.000-04:002010-09-14T21:51:06.975-04:00Speeding up scalar functions in SQL Server<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span class="Apple-style-span" style="font-family: Arial, sans-serif;">Our warehouse uses a UDF to check for dates outside of the acceptable range and assign them the designated “unknown dates”.</span><span class="Apple-style-span" style="font-family: Arial, sans-serif;"><span style="mso-spacerun: yes;"> </span></span><span class="Apple-style-span" style="font-family: Arial, sans-serif;">Why do it in SQL you ask (vs SSIS)?</span><span class="Apple-style-span" style="font-family: Arial, sans-serif;"><span style="mso-spacerun: yes;"> </span></span><span class="Apple-style-span" style="font-family: Arial, sans-serif;">Because it is an easy way to have control in one central place.</span><span class="Apple-style-span" style="font-family: Arial, sans-serif;"><span style="mso-spacerun: yes;"> </span></span><span class="Apple-style-span" style="font-family: Arial, sans-serif;">I made a change that pushed the date ranges from hardcoded in a function to table driven so it can be shared across multiple clients and allow them to retain their custom settings during deployments.</span><span class="Apple-style-span" style="font-family: Arial, sans-serif;"><span style="mso-spacerun: yes;"> </span></span><span class="Apple-style-span" style="font-family: Arial, sans-serif;">It was like someone hit the breaks – sure the queries were very light (7 rows in the table), but calling it several times in each ETL sproc blew it out to 20-30 million times in a single ETL job. Sure the cache hit rate was 100%, but it still added almost 50% to the total ETL time.</span><span class="Apple-style-span" style="font-family: Arial, sans-serif;"><span style="mso-spacerun: yes;"> </span></span><span class="Apple-style-span" style="font-family: Arial, sans-serif;">What to do… what to do…</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: black; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";">I ran across a great article by Itzik Ben-Gan (<a href="http://www.sqlmag.com/article/sql-server/inline-scalar-functions.aspx" target="_blank"><span style="color: blue;">http://www.sqlmag.com/article/sql-server/inline-scalar-functions.aspx</span></a>) on how to make your functions perform extremely fast – by simply changing them from scalar to table.<span style="mso-spacerun: yes;"> </span>He goes into the details of why it works – I will simply state the benchmarks:<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: black; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";">This is calling the function as a scalar with the syntax of:<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in;"><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">[DateField]</span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";"> </span><span style="color: grey; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">=</span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";"> </span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">dbo</span><span style="color: grey; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">.</span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">fn_CleanDate</span><span style="color: grey; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">(</span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">[column]</span><span style="color: grey; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">)</span><span style="color: black; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in;"><br />
</div><table border="0" cellpadding="0" cellspacing="0" class="MsoNormalTable" style="border-collapse: collapse; margin-left: 34.85pt; width: 293px;"><tbody>
<tr style="height: 15.0pt; mso-yfti-firstrow: yes; mso-yfti-irow: 0;"> <td nowrap="" style="background: black; border: solid windowtext 1.0pt; height: 15.0pt; padding: 0in 5.4pt 0in 5.4pt; width: .75in;" valign="bottom" width="72"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><span style="color: white; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";">CPU</span><span style="font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div></td> <td nowrap="" style="background: black; border-left: none; border: solid windowtext 1.0pt; height: 15.0pt; padding: 0in 5.4pt 0in 5.4pt; width: .75in;" valign="bottom" width="72"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><span style="color: white; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";">Reads</span><span style="font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div></td> <td nowrap="" style="background: black; border-left: none; border: solid windowtext 1.0pt; height: 15.0pt; padding: 0in 5.4pt 0in 5.4pt; width: .75in;" valign="bottom" width="72"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><span style="color: white; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";">Writes</span><span style="font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div></td> <td nowrap="" style="background: black; border-left: none; border: solid windowtext 1.0pt; height: 15.0pt; padding: 0in 5.4pt 0in 5.4pt; width: 58.05pt;" valign="bottom" width="77"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><span style="color: white; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";">Duration</span><span style="font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div></td> </tr>
<tr style="height: 15.0pt; mso-yfti-irow: 1; mso-yfti-lastrow: yes;"> <td nowrap="" style="border-top: none; border: solid windowtext 1.0pt; height: 15.0pt; padding: 0in 5.4pt 0in 5.4pt; width: .75in;" valign="bottom" width="72"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><span style="color: black; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";">491,918</span><span style="font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div></td> <td nowrap="" style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 15.0pt; padding: 0in 5.4pt 0in 5.4pt; width: .75in;" valign="bottom" width="72"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><span style="color: black; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";">62,832,254</span><span style="font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div></td> <td nowrap="" style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 15.0pt; padding: 0in 5.4pt 0in 5.4pt; width: .75in;" valign="bottom" width="72"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><span style="color: black; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";">1</span><span style="font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div></td> <td nowrap="" style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 15.0pt; padding: 0in 5.4pt 0in 5.4pt; width: 58.05pt;" valign="bottom" width="77"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><span style="color: black; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";">493,181,940</span><span style="font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div></td> </tr>
</tbody></table><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: black; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";">Those are some big numbers… so if we rewrite the function so that it returns a table with a single field, the changes resulting:<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in;"><span style="color: black; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";">[DateField] = (select value from dbo.fn_SelectCleandate([column]))<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in;"><br />
</div><table border="0" cellpadding="0" cellspacing="0" class="MsoNormalTable" style="border-collapse: collapse; margin-left: 34.85pt; width: 313px;"><tbody>
<tr style="height: 15.0pt; mso-yfti-firstrow: yes; mso-yfti-irow: 0;"> <td nowrap="" style="background: black; border: solid windowtext 1.0pt; height: 15.0pt; padding: 0in 5.4pt 0in 5.4pt; width: .75in;" valign="bottom" width="72"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><span style="color: white; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";">CPU</span><span style="font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div></td> <td nowrap="" style="background: black; border-left: none; border: solid windowtext 1.0pt; height: 15.0pt; padding: 0in 5.4pt 0in 5.4pt; width: 58.8pt;" valign="bottom" width="78"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><span style="color: white; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";">Reads</span><span style="font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div></td> <td nowrap="" style="background: black; border-left: none; border: solid windowtext 1.0pt; height: 15.0pt; padding: 0in 5.4pt 0in 5.4pt; width: .75in;" valign="bottom" width="72"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><span style="color: white; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";">Writes</span><span style="font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div></td> <td nowrap="" style="background: black; border-left: none; border: solid windowtext 1.0pt; height: 15.0pt; padding: 0in 5.4pt 0in 5.4pt; width: 68.0pt;" valign="bottom" width="91"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><span style="color: white; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";">Duration</span><span style="font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div></td> </tr>
<tr style="height: 15.0pt; mso-yfti-irow: 1; mso-yfti-lastrow: yes;"> <td nowrap="" style="border-top: none; border: solid windowtext 1.0pt; height: 15.0pt; padding: 0in 5.4pt 0in 5.4pt; width: .75in;" valign="bottom" width="72"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><span style="color: black; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";">330,176</span><span style="font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div></td> <td nowrap="" style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 15.0pt; padding: 0in 5.4pt 0in 5.4pt; width: 58.8pt;" valign="bottom" width="78"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><span style="color: black; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";">41,901,480</span><span style="font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div></td> <td nowrap="" style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 15.0pt; padding: 0in 5.4pt 0in 5.4pt; width: .75in;" valign="bottom" width="72"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><span style="color: black; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";">0</span><span style="font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div></td> <td nowrap="" style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 15.0pt; padding: 0in 5.4pt 0in 5.4pt; width: 68.0pt;" valign="bottom" width="91"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><span style="color: black; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";">330,524,577</span><span style="font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div></td> </tr>
</tbody></table><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: black; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";">Now for the kicker. If you select your date values higher up in the sproc and <i>pass</i> them into the function so there is no data access required in the sub-select it performs. Blazing fast. Yeah, it might clutter up the syntax a bit, but it is definitely worth it!<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in;"><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">[DateField]</span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";"> </span><span style="color: grey; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">=</span><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";"> </span><span style="color: grey; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">(</span><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">select</span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";"> </span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">value</span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";"> </span><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">from</span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";"> </span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">dbo</span><span style="color: grey; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">.</span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">fn_SelectCleanDate</span><span style="color: grey; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">(</span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">[column]</span><span style="color: grey; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">,</span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";"> </span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">@minDate</span><span style="color: grey; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">,</span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";"> </span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">@maxDate</span><span style="color: grey; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">,</span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">@minUnknownDate</span><span style="color: grey; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">,</span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";"> </span><span style="color: black; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">@maxUnknownDate</span><span style="color: grey; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">)),</span><span style="color: black; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in;"><br />
</div><table border="0" cellpadding="0" cellspacing="0" class="MsoNormalTable" style="border-collapse: collapse; margin-left: 34.85pt; width: 307px;"><tbody>
<tr style="height: 15.0pt; mso-yfti-firstrow: yes; mso-yfti-irow: 0;"> <td nowrap="" style="background: black; border: solid windowtext 1.0pt; height: 15.0pt; padding: 0in 5.4pt 0in 5.4pt; width: .75in;" valign="bottom" width="72"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><span style="color: white; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";">CPU</span><span style="font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div></td> <td nowrap="" style="background: black; border-left: none; border: solid windowtext 1.0pt; height: 15.0pt; padding: 0in 5.4pt 0in 5.4pt; width: .75in;" valign="bottom" width="72"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><span style="color: white; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";">Reads</span><span style="font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div></td> <td nowrap="" style="background: black; border-left: none; border: solid windowtext 1.0pt; height: 15.0pt; padding: 0in 5.4pt 0in 5.4pt; width: .75in;" valign="bottom" width="72"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><span style="color: white; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";">Writes</span><span style="font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div></td> <td nowrap="" style="background: black; border-left: none; border: solid windowtext 1.0pt; height: 15.0pt; padding: 0in 5.4pt 0in 5.4pt; width: 68.0pt;" valign="bottom" width="91"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><span style="color: white; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";">Duration</span><span style="font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div></td> </tr>
<tr style="height: 15.0pt; mso-yfti-irow: 1; mso-yfti-lastrow: yes;"> <td nowrap="" style="border-top: none; border: solid windowtext 1.0pt; height: 15.0pt; padding: 0in 5.4pt 0in 5.4pt; width: .75in;" valign="bottom" width="72"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><span style="color: black; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";">1,670</span><span style="font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div></td> <td nowrap="" style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 15.0pt; padding: 0in 5.4pt 0in 5.4pt; width: .75in;" valign="bottom" width="72"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><span style="color: black; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";">4,578</span><span style="font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div></td> <td nowrap="" style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 15.0pt; padding: 0in 5.4pt 0in 5.4pt; width: .75in;" valign="bottom" width="72"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><span style="color: black; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";">0</span><span style="font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div></td> <td nowrap="" style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 15.0pt; padding: 0in 5.4pt 0in 5.4pt; width: 68.0pt;" valign="bottom" width="91"> <div align="center" class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-align: center;"><span style="color: black; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";">308</span><span style="font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></div></td> </tr>
</tbody></table><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: black; font-family: "Arial","sans-serif"; mso-fareast-font-family: "Times New Roman";">That deserves restating. 308 milliseconds vs. 493 thousand.<o:p></o:p></span></div><div class="MsoNormal"><br />
</div>John Henneseyhttp://www.blogger.com/profile/14350314173210731480noreply@blogger.com0tag:blogger.com,1999:blog-7482289774765508546.post-58855397531975434032010-08-26T14:55:00.002-04:002010-10-15T16:10:28.513-04:00Execute SQL on linked server (pass-through)<div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><span class="Apple-style-span" style="font-family: Arial; font-size: small;"><span class="Apple-style-span" style="font-size: 13px;">So living with one foot in Oracle land is helping me learn some of the lesser-known features of SQL. For my 2nd post in one day here is another thing I found too good to not spread; executing SQL on a linked server withough SQL interpreting anything (a.k.a. Pass-Through). While tinkering with using openquery to insert and update, delete was just too darn slow and I knew there had to be a better way. Turns out the execute function allows you to specify a linked server as a target. Too cool...</span></span></div><div><div style="font-family: Arial;"><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; border-collapse: separate; color: black; font-family: 'Times New Roman'; font-size: medium; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px;"><span class="Apple-style-span" style="color: #222222; font-family: arial; font-size: 13px; line-height: 18px;"><br />
</span></span></div></div></div><table><tbody>
<tr style="padding-left: 30pt; padding-right: 30pt;"><td style="background-color: #ffffcc; border-bottom-color: gray; border-bottom-style: solid; border-bottom-width: 1pt; border-left-color: gray; border-left-style: solid; border-left-width: 1pt; border-right-color: gray; border-right-style: solid; border-right-width: 1pt; border-top-color: gray; border-top-style: solid; border-top-width: 1pt;"><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><span class="Apple-style-span" style="color: blue; font-family: 'Courier New';"><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; line-height: 18px;"></span></span></div></div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><span class="Apple-style-span" style="color: blue; font-family: 'Courier New';"></span></div><span class="Apple-style-span" style="color: blue; font-family: 'Courier New';"></span><br />
<span class="Apple-style-span" style="color: blue; font-family: 'Courier New';"></span><br />
<span class="Apple-style-span" style="color: blue; font-family: 'Courier New';"></span><br />
<span class="Apple-style-span" style="color: blue; font-family: 'Courier New';"></span><br />
<span class="Apple-style-span" style="color: blue; font-family: 'Courier New';"><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"></div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0in;"><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">execute </span><span style="color: grey; font-family: 'Courier New'; font-size: 10pt;">(</span><span style="color: red; font-family: 'Courier New'; font-size: 10pt;">'delete from table_name1'</span><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: grey;">)</span> at <span style="color: grey;"><<</span>linkedServer<span style="color: grey;">>></span></span></div></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0in;"><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">execute </span><span style="color: grey; font-family: 'Courier New'; font-size: 10pt;">(</span><span style="color: red; font-family: 'Courier New'; font-size: 10pt;">'delete from table_name2'</span><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: grey;">)</span> at <span style="color: grey;"><<</span>linkedServer<span style="color: grey;">>><o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">execute </span><span style="color: grey; font-family: 'Courier New'; font-size: 10pt;">(</span><span style="color: red; font-family: 'Courier New'; font-size: 10pt;">'delete from table_name3'</span><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: grey;">)</span> at <span style="color: grey;"><<</span>linkedServer<span style="color: grey;">>><o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">execute </span><span style="color: grey; font-family: 'Courier New'; font-size: 10pt;">(</span><span style="color: red; font-family: 'Courier New'; font-size: 10pt;">'delete from table_name4'</span><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: grey;">)</span> at <span style="color: grey;"><<</span>linkedServer<span style="color: grey;">>><o:p></o:p></span></span></div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;"><span class="Apple-style-span" style="font-size: medium;"><span style="color: blue; font-family: 'Courier New'; font-size: 10pt; line-height: 115%;">execute </span><span style="color: grey; font-family: 'Courier New'; font-size: 10pt; line-height: 115%;">(</span><span style="color: red; font-family: 'Courier New'; font-size: 10pt; line-height: 115%;">'delete from table_name5'</span><span style="font-family: 'Courier New'; font-size: 10pt; line-height: 115%;"> <span style="color: grey;">)</span> at <span style="color: grey;"><<</span>linkedServer<span style="color: grey;">>></span></span></span></span></div></div></span></td></tr>
</tbody></table><span class="Apple-style-span" style="font-family: Arial; font-size: 13px;"><br />
</span></div><div><span class="Apple-style-span" style="font-family: Arial; font-size: small;"><span class="Apple-style-span" style="font-size: 13px;">In the case above there were foreign key references between the tables - in my example I knew the relationships (thus the order) to clean out the table to avoid FK errors - but with a little probing of the tables in oracle via openquery I'm sure you could produce the necessary deletes in the correct order.</span></span><br />
<span class="Apple-style-span" style="font-family: Arial; font-size: small;"><span class="Apple-style-span" style="font-size: 13px;"><br />
</span></span><br />
<span class="Apple-style-span" style="font-family: Arial; font-size: small;"><span class="Apple-style-span" style="font-size: 13px;">Another way to do it (non pass through) is the following - which I recommend only for selects, as updates/inserts/deletes will be evaluated SQL server side and take too long.</span></span><br />
<span class="Apple-style-span" style="font-family: Arial; font-size: small;"><span class="Apple-style-span" style="font-size: 13px;"><br />
</span></span><br />
<span class="Apple-style-span" style="font-family: Arial; font-size: small;"><span class="Apple-style-span" style="font-size: 13px;"><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; font-family: 'Times New Roman'; font-size: medium;"></span></span></span><br />
<span class="Apple-style-span" style="color: blue; font-family: 'Courier New';"><div class="MsoNormal" style="line-height: normal; margin-bottom: 0in;"><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"></div><div class="MsoNormal" style="mso-layout-grid-align: none; text-autospace: none;"><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">select</span><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"> <span style="color: grey;">*</span> <o:p></o:p></span></div><div class="MsoNormal" style="mso-layout-grid-align: none; text-autospace: none;"><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">from</span><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"> <span style="color: blue;">openquery</span><span style="color: grey;">(</span><<linkedserver>><span style="color: grey;">,</span> <span style="color: red;">'select * from table_name1'</span><span style="color: grey;">)<o:p></o:p></span></span></div></div></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0in;"><div class="MsoNormal" style="line-height: normal; margin-bottom: 0in;"><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><span class="Apple-style-span" style="color: grey;"><span class="Apple-style-span" style="line-height: 14px;"><span class="Apple-style-span" style="color: blue;"><span class="Apple-style-span" style="line-height: normal;"><br />
</span></span></span></span></div></div></div></span><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"></div></div>John Henneseyhttp://www.blogger.com/profile/14350314173210731480noreply@blogger.com0tag:blogger.com,1999:blog-7482289774765508546.post-32011035312518554952010-08-26T14:38:00.000-04:002010-08-26T14:38:36.555-04:00Composite Foreign Keys<span class="Apple-style-span" style="font-family: Arial; font-size: small;"><span class="Apple-style-span" style="font-size: 13px;">You learn something new every day... Today I found a table that nobody could figure out - turns out it's sole purpose in life is to maintain foreign key relationships (via data) so the objects knew how to verify referential integrity before inserting. After a little googling I found out that it is possible to have a composite foreign key. Crazy I thought... but it works. If anyone has any (negative) experience in this area I would love to hear about it.</span></span><div><div style="font-family: Arial;"><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; border-collapse: separate; color: black; font-family: 'Times New Roman'; font-size: medium; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px;"><span class="Apple-style-span" style="color: #222222; font-family: arial; font-size: 13px; line-height: 18px;"><br />
</span></span></div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; border-collapse: separate; color: black; font-family: 'Times New Roman'; font-size: medium; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px;"><span class="Apple-style-span" style="color: #222222; font-family: arial; font-size: 13px; line-height: 18px;">(Note I am now living with one foot in SQL Server land and the other in Oracle Land - hence the semicolons)</span></span></div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; border-collapse: separate; color: black; font-family: 'Times New Roman'; font-size: medium; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px;"><span class="Apple-style-span" style="color: #222222; font-family: arial; font-size: 13px; line-height: 18px;"><br />
</span></span></div></div><table style="width: 676px;"><tbody>
<tr style="padding-left: 30pt; padding-right: 30pt;"><td style="background-color: #ffffcc; border-bottom-color: gray; border-bottom-style: solid; border-bottom-width: 1pt; border-left-color: gray; border-left-style: solid; border-left-width: 1pt; border-right-color: gray; border-right-style: solid; border-right-width: 1pt; border-top-color: gray; border-top-style: solid; border-top-width: 1pt;"><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><span class="Apple-style-span" style="color: blue; font-family: 'Courier New';"><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; line-height: 18px;"></span></span></div><span class="Apple-style-span" style="color: blue; font-family: 'Courier New';"><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"></div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">create</span><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"> <span style="color: blue;">table</span> tmp1<span style="color: blue;"> </span><span style="color: grey;">(<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;"> </span>f1<span style="mso-spacerun: yes;"> </span><span style="color: blue;">char</span><span style="color: grey;">(</span>1<span style="color: grey;">),<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;"> </span>f2<span style="mso-spacerun: yes;"> </span><span style="color: blue;">char</span><span style="color: grey;">(</span>1<span style="color: grey;">),<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;"> </span><span style="color: blue;">primary</span> <span style="color: blue;">key </span><span style="color: grey;">(<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;"> </span>f1<span style="color: grey;">,</span> f2<span style="color: grey;">)</span> <o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="color: grey; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">);<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;"> </span><o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">create</span><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"> <span style="color: blue;">table</span> tmp2<span style="color: blue;"> </span><span style="color: grey;">(<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;"> </span>f1<span style="mso-spacerun: yes;"> </span><span style="color: blue;">char</span><span style="color: grey;">(</span>1<span style="color: grey;">),<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;"> </span>f2<span style="mso-spacerun: yes;"> </span><span style="color: blue;">char</span><span style="color: grey;">(</span>1<span style="color: grey;">)<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;"> </span><span style="color: grey;">,</span> <span style="color: blue;">constraint</span> FK_composite <span style="color: blue;">foreign</span> <span style="color: blue;">key </span><span style="color: grey;">(</span>f1<span style="color: grey;">,</span> f2<span style="color: grey;">)</span> <span style="color: blue;">references</span> tmp1<span style="color: blue;"> </span><span style="color: grey;">(</span>f1<span style="color: grey;">,</span> f2<span style="color: grey;">)<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="color: grey; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">);<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">insert</span><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"> <span style="color: blue;">into</span> tmp1<span style="color: blue;"> </span><span style="color: grey;">(</span>f1<span style="color: grey;">,</span> f2<span style="color: grey;">)</span> <span style="color: blue;">values </span><span style="color: grey;">(</span><span style="color: red;">'1'</span><span style="color: grey;">,</span> <span style="color: red;">'1'</span><span style="color: grey;">);<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">insert</span><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"> <span style="color: blue;">into</span> tmp1<span style="color: blue;"> </span><span style="color: grey;">(</span>f1<span style="color: grey;">,</span> f2<span style="color: grey;">)</span> <span style="color: blue;">values </span><span style="color: grey;">(</span><span style="color: red;">'2'</span><span style="color: grey;">,</span> <span style="color: red;">'2'</span><span style="color: grey;">);<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="color: green; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">-- succeeds<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">insert</span><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"> <span style="color: blue;">into</span> tmp2<span style="color: blue;"> </span><span style="color: grey;">(</span>f1<span style="color: grey;">,</span> f2<span style="color: grey;">)</span> <span style="color: blue;">values </span><span style="color: grey;">(</span><span style="color: red;">'1'</span><span style="color: grey;">,</span> <span style="color: red;">'1'</span><span style="color: grey;">);<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">insert</span><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"> <span style="color: blue;">into</span> tmp2<span style="color: blue;"> </span><span style="color: grey;">(</span>f1<span style="color: grey;">,</span> f2<span style="color: grey;">)</span> <span style="color: blue;">values </span><span style="color: grey;">(</span><span style="color: red;">'2'</span><span style="color: grey;">,</span> <span style="color: red;">'2'</span><span style="color: grey;">);<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="color: green; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">-- fails<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">insert</span><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"> <span style="color: blue;">into</span> tmp2<span style="color: blue;"> </span><span style="color: grey;">(</span>f1<span style="color: grey;">,</span> f2<span style="color: grey;">)</span> <span style="color: blue;">values </span><span style="color: grey;">(</span><span style="color: red;">'1'</span><span style="color: grey;">,</span> <span style="color: red;">'2'</span><span style="color: grey;">);<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">insert</span><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"> <span style="color: blue;">into</span> tmp2<span style="color: blue;"> </span><span style="color: grey;">(</span>f1<span style="color: grey;">,</span> f2<span style="color: grey;">)</span> <span style="color: blue;">values </span><span style="color: grey;">(</span><span style="color: red;">'2'</span><span style="color: grey;">,</span> <span style="color: red;">'1'</span><span style="color: grey;">);<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">select</span><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"> <span style="color: grey;">*</span> <span style="color: blue;">from</span> tmp1<span style="color: grey;">;<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">select</span><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"> <span style="color: grey;">*</span> <span style="color: blue;">from</span> tmp2<span style="color: grey;">;<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-layout-grid-align: none; text-autospace: none;"><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">drop</span><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"> <span style="color: blue;">table</span> tmp2<span style="color: grey;">;<o:p></o:p></span></span></div><div class="MsoNormal"><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-no-proof: yes;">drop</span><span style="font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-no-proof: yes;"> <span style="color: blue;">table</span> tmp1<span style="color: grey;">;</span></span></div><br />
</span></td></tr>
</tbody></table></div>John Henneseyhttp://www.blogger.com/profile/14350314173210731480noreply@blogger.com0tag:blogger.com,1999:blog-7482289774765508546.post-73773523389065482542010-07-29T22:50:00.002-04:002010-11-02T20:24:36.016-04:00Analysis Services Personalization Extensions<span class="Apple-style-span" style="font-family: Arial; font-size: small;"><span class="Apple-style-span" style="font-size: 13px;"></span></span><br />
<span class="Apple-style-span" style="font-family: Arial; font-size: small;"><span class="Apple-style-span" style="font-size: 13px;"></span></span><br />
<div style="font-family: Arial;"><span class="Apple-style-span" style="font-size: 13px;">** Update 2010-11-02 - In order for personalization extensions to work, the assembly must be registered at the instance level (NOT at the database level).</span><br />
<span class="Apple-style-span" style="font-size: 13px;"><br />
</span><br />
<span class="Apple-style-span" style="font-size: 13px;"><br />
</span><br />
<span class="Apple-style-span" style="font-size: 13px;"> So my favorite feature of Analysis Services (this week) is peronalization extensions. This is a very powerful feature that can have many different uses. In a nutshell it allows you to write .net code that hooks into AS events - SessionOpen, SessionClose, CubeOpen and CubeClose. Although there are an unlimited number of uses, I find it very handy in this scenario :</span><br />
<span class="Apple-style-span" style="font-family: Arial; font-size: small;"><span class="Apple-style-span" style="font-size: 13px;">Imagine you have a cube that you deploy to xx number of clients. The datamart it is built upon has user defined fields, say "UserDebit1", "UserDebit2" ... "UserDebitn". Each client calls the fields whatever they want in the source app, and wants their own copy of the cube to have the name they choose show up in their browsing tool. Choices are 1) have a different cube for each client (no way!), or use PE to dynamically expose out the named calculation. Another very useful application of this is each client calculates something a little different - say "Revenue". You can build in a member into your cube that is custom tailored to each client, with no one-off's. The way to do it is:</span></span><br />
<ul><li><span class="Apple-style-span" style="font-family: Arial; font-size: small;"><span class="Apple-style-span" style="font-size: 13px;">Include the UserDebitx measures in the cube. Make them not visible</span></span></li>
<span class="Apple-style-span" style="font-family: Arial; font-size: small;"><span class="Apple-style-span" style="font-size: 13px;">
<li>Use PE to build a calculated member on either SessionOpen or CubeOpen event. I chose CubeOpen as I don't want all sort of stuff going into cubes they don't open.</li>
<li>On CubeClose event drop the calculated members.</li>
</span></span></ul><span class="Apple-style-span" style="font-family: Arial; font-size: small;"><span class="Apple-style-span" style="font-size: 13px;">Since the events are dynamic in nature, I chose to put the member definitions in a database - that way there is no need to redeploy the assembly when things change. The code is as follows (I used the <a href="http://msftasprodsamples.codeplex.com/wikipage?title=SS2008!Readme%20for%20Analysis%20Services%20Personalization%20Extensions%20Sample&ProjectName=msftasprodsamples">code found on CodePlex</a> as the base)</span></span></div><table style="font-size: small; width: 100%;"><tbody>
<tr style="padding-left: 30pt; padding-right: 30pt;"> <td style="background-color: #ffffcc; border: solid 1pt gray;"><div class="MsoNormal"><span class="style1" style="color: blue; font-family: 'Courier New';">using</span><span class="style1" style="font-family: 'Courier New';"> System;</span><span style="color: blue; font-family: 'Courier New';"><br class="style1" /> </span> <span class="style1" style="color: blue; font-family: 'Courier New';">using</span><span class="style1" style="font-family: 'Courier New';"> System.Collections.Generic;</span><span style="color: blue; font-family: 'Courier New';"><br class="style1" /> </span> <span class="style1" style="color: blue; font-family: 'Courier New';">using</span><span class="style1" style="font-family: 'Courier New';"> System.Text;<br />
</span> <span class="style1" style="color: blue; font-family: 'Courier New';">using</span><span class="style1" style="font-family: 'Courier New';"> Microsoft.AnalysisServices.AdomdServer;<o:p> </o:p></span></div><div class="MsoNormal"><span class="style1" style="font-family: 'Courier New';">[</span><span style="font-family: 'Courier New';"><span class="style1" style="color: blue;">assembly</span></span><span style="font-family: 'Courier New'; font-size: 10pt;"><span class="style1" style="font-family: 'Courier New';">: <span style="color: #2b91af;">CLSCompliant</span>(<span style="color: blue;">true</span>)]<o:p><br />
</o:p> </span> </span> <span class="style1" style="color: blue; font-family: 'Courier New';">namespace</span><span class="style1" style="font-family: 'Courier New';"> PE.ASClientExtensions<br />
{<br />
[<span style="color: #2b91af;">PlugInAttribute</span>]<br />
<span style="color: blue;">public</span> <span style="color: blue;">class</span> <span style="color: #2b91af;">ASClientExtension<br />
</span> {<br />
</span> <span style="font-family: 'Courier New'; font-size: 10pt;"> <span class="style1" style="font-family: 'Courier New';">[System.Diagnostics.CodeAnalysis.<span style="color: #2b91af;">SuppressMessage</span>(<span style="color: #a31515;">"Microsoft.Performance"</span>, <span style="color: #a31515;">"CA1804:RemoveUnusedLocals"</span>, MessageId = <span style="color: #a31515;">"environment"</span>)]<br />
<span style="color: blue;">public</span> ASClientExtension()<br />
{<br />
<span style="color: #2b91af;">Context</span>.Server.SessionOpened += <span style="color: blue;">new</span> <span style="color: #2b91af;">EventHandler</span>(<span style="color: blue;">this</span>.SessionOpened);<br />
<span style="color: #2b91af;">Context</span>.Server.SessionClosing += <span style="color: blue;">new</span> <span style="color: #2b91af;">EventHandler</span>(<span style="color: blue;">this</span>.SessionClosing);<br />
}<o:p> </o:p></span></span></div><div class="MsoNormal"><span style="font-family: 'Courier New';"> <span class="style1"> </span> </span> <span class="style1" style="font-family: 'Courier New';"> [System.Diagnostics.CodeAnalysis.<span style="color: #2b91af;">SuppressMessage</span>(<span style="color: #a31515;">"Microsoft.Security"</span>, <span style="color: #a31515;">"CA2109:ReviewVisibleEventHandlers"</span>), System.Diagnostics.CodeAnalysis.<span style="color: #2b91af;">SuppressMessage</span>(<span style="color: #a31515;">"Microsoft.Performance"</span>, <span style="color: #a31515;">"CA1804:RemoveUnusedLocals"</span>, MessageId = <span style="color: #a31515;">"session"</span>)]<br />
<span style="color: blue;">public</span> <span style="color: blue;">void</span> SessionOpened(<span style="color: blue;">object</span> sender, <span style="color: #2b91af;">EventArgs</span> e)<br />
{<br />
<span style="color: #2b91af;">SessionMgr</span> mgr = <span style="color: blue;">new</span> <span style="color: #2b91af;">SessionMgr</span>();<br />
}<o:p> </o:p></span></div><div class="MsoNormal"><span style="font-family: 'Courier New';"> <span class="style1"> </span> </span> <span class="style1" style="font-family: 'Courier New';"> [System.Diagnostics.CodeAnalysis.<span style="color: #2b91af;">SuppressMessage</span>(<span style="color: #a31515;">"Microsoft.Security"</span>, <span style="color: #a31515;">"CA2109:ReviewVisibleEventHandlers"</span>)]<br />
<span style="color: blue;">public</span> <span style="color: blue;">void</span> SessionClosing(<span style="color: blue;">object</span> sender, <span style="color: #2b91af;">EventArgs</span> e)<br />
{<br />
<span style="color: green;">// Any Session close code goes here.<br />
</span> }<br />
}<br />
</span> <span style="font-family: 'Courier New'; font-size: 10pt; line-height: 115%;">}</span></div></td> </tr>
</tbody></table><div class="MsoNormal"></div><div class="MsoNormal"><span class="Apple-style-span" style="font-family: Arial; font-size: small;"><span class="Apple-style-span" style="font-size: 13px;">And this is where the magic happens...</span></span></div><table style="width: 100%;"><tbody>
<tr style="padding-left: 30pt; padding-right: 30pt;"> <td style="background-color: #ffffcc; border: solid 1pt gray;"><div class="MsoNormal"><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">using</span><span style="font-family: 'Courier New'; font-size: 10pt;"> System;<br />
</span> <span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">using</span><span style="font-family: 'Courier New'; font-size: 10pt;"> System.Collections.Generic;<br />
</span> <span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">using</span><span style="font-family: 'Courier New'; font-size: 10pt;"> System.Linq;<br />
</span> <span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">using</span><span style="font-family: 'Courier New'; font-size: 10pt;"> System.Text;<br />
</span> <span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">using</span><span style="font-family: 'Courier New'; font-size: 10pt;"> Microsoft.AnalysisServices.AdomdServer;<br />
</span> <span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">using</span><span style="font-family: 'Courier New'; font-size: 10pt;"> System.Data;<br />
</span> <span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">using</span><span style="font-family: 'Courier New'; font-size: 10pt;"> System.Data.OleDb;<br />
</span> <span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">using</span><span style="font-family: 'Courier New'; font-size: 10pt;"> System.Xml;<br />
</span> <span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">using</span><span style="font-family: 'Courier New'; font-size: 10pt;"> System.IO;<o:p></o:p></span></div><div class="MsoNormal"><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">namespace</span><span style="font-family: 'Courier New'; font-size: 10pt;"> PE.ASClientExtensions<br />
</span> <span style="font-family: 'Courier New'; font-size: 10pt;">{<br />
<span style="color: blue;">public</span> <span style="color: blue;">class</span> <span style="color: #2b91af;">SessionMgr<br />
</span> {<br />
<span style="color: blue;">private</span> <span style="color: #2b91af;">List</span><<span style="color: #2b91af;">CalculatedMember</span>> CalculatedMembers;<o:p></o:p></span></div><div class="MsoNormal"><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: blue;">class</span> <span style="color: #2b91af;">CalculatedMember<br />
</span> {<br />
<span style="color: blue;">private</span> <span style="color: blue;">int</span> _id;<br />
<span style="color: blue;">private</span> <span style="color: blue;">string</span> _database = <span style="color: blue;">string</span>.Empty;<br />
<span style="color: blue;">private</span> <span style="color: blue;">string</span> _cube = <span style="color: blue;">string</span>.Empty;<br />
<span style="color: blue;">private</span> <span style="color: blue;">string</span> _dimension = <span style="color: blue;">string</span>.Empty;<br />
<span style="color: blue;">private</span> <span style="color: blue;">string</span> _attribute = <span style="color: blue;">string</span>.Empty;<br />
<span style="color: blue;">private</span> <span style="color: blue;">string</span> _definition = <span style="color: blue;">string</span>.Empty;<o:p></o:p></span></div><div class="MsoNormal"><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: blue;">public</span> <span style="color: blue;">int</span> Id { <span style="color: blue;">get</span> { <span style="color: blue;">return</span> _id; } }<br />
<span style="color: blue;">public</span> <span style="color: blue;">string</span> Database { <span style="color: blue;">get</span> { <span style="color: blue;">return</span> _database; } }<br />
<span style="color: blue;">public</span> <span style="color: blue;">string</span> Cube { <span style="color: blue;">get</span> { <span style="color: blue;">return</span> _cube; } }<br />
<span style="color: blue;">public</span> <span style="color: blue;">string</span> Dimension { <span style="color: blue;">get</span> { <span style="color: blue;"> return</span> _dimension; } }<br />
<span style="color: blue;">public</span> <span style="color: blue;">string</span> Attribute { <span style="color: blue;">get</span> { <span style="color: blue;"> return</span> _attribute; } }<br />
<span style="color: blue;">public</span> <span style="color: blue;">string</span> Definition { <span style="color: blue;">get</span> { <span style="color: blue;"> return</span> _definition; } }<o:p></o:p></span></div><div class="MsoNormal"><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: blue;">public</span> CalculatedMember(<span style="color: blue;">int</span> Id, <span style="color: blue;">string</span> Database, <span style="color: blue;">string</span> Cube, <span style="color: blue;"> string</span> Dimension, <span style="color: blue;">string</span> Attribute, <span style="color: blue;">string</span> Definition)<br />
{<br />
_id = Id;<br />
_database = Database;<br />
_cube = Cube;<br />
_dimension = Dimension;<br />
_attribute = Attribute;<br />
_definition = Definition;<br />
}<br />
}<o:p> </o:p></span></div><div class="MsoNormal"><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: blue;">private</span> <span style="color: blue;">void</span> LoadAll()<br />
{<br />
<span style="color: green;">// Load connection string from global package configurations<br />
</span> <span style="color: green;">// This allows us to reuse the same pacakge configs as ssis<br />
</span> <br />
<span style="color: #2b91af;">XmlDocument</span> xml = <span style="color: blue;"> new</span> <span style="color: #2b91af;">XmlDocument</span>();<br />
xml.Load(<span style="color: #a31515;">@"Package Configuration Path\Db.dtsConfig"</span>);<br />
<span style="color: blue;">string</span> connectionString = xml.SelectSingleNode(<span style="color: #a31515;">"/DTSConfiguration[1]/Configuration[1]/ConfiguredValue[1]"</span>).InnerText;<br />
System.Data.OleDb.<span style="color: #2b91af;">OleDbConnectionStringBuilder</span> ocon = <span style="color: blue;">new</span> <span style="color: #2b91af;"> OleDbConnectionStringBuilder</span>(connectionString);<br />
<br />
<span style="color: blue;">using</span> (<span style="color: #2b91af;">OleDbConnection</span> con = <span style="color: blue;">new</span> <span style="color: #2b91af;"> OleDbConnection</span>(ocon.ConnectionString))<br />
{<o:p><br />
</o:p> <span style="color: blue;">using</span> (<span style="color: #2b91af;">OleDbCommand</span> cmd = <span style="color: blue;">new</span> <span style="color: #2b91af;"> OleDbCommand</span>(<span style="color: #a31515;">"select * from dbo.ASCalculatedMember"</span>, con))<br />
{<br />
con.Open();<br />
<span style="color: #2b91af;">OleDbDataReader</span> reader = cmd.ExecuteReader();<br />
<o:p><br />
</o:p> <span style="color: blue;">while</span> (reader.Read())<br />
{<br />
CalculatedMembers.Add(<br />
<span style="color: blue;">new</span> <span style="color: #2b91af;"> CalculatedMember</span>(<br />
<span style="color: #2b91af;">Convert</span>.ToInt32(reader[<span style="color: #a31515;">"id"</span>]),<br />
reader[<span style="color: #a31515;">"Database"</span>].ToString(),<br />
reader[<span style="color: #a31515;">"Cube"</span>].ToString(),<br />
reader[<span style="color: #a31515;">"Dimension"</span>].ToString(),<br />
reader[<span style="color: #a31515;">"Attribute"</span>].ToString(),<br />
reader[<span style="color: #a31515;">"Definition"</span>].ToString()));<br />
}<br />
}<br />
con.Close();<br />
}<br />
}<br />
<o:p><br />
</o:p> <span style="color: blue;">public</span> SessionMgr()<br />
{<br />
<span style="color: #2b91af;">Context</span>.CurrentConnection.CubeOpened += <span style="color: blue;">new</span> <span style="color: #2b91af;">EventHandler</span>(CubeOpened);<br />
<span style="color: #2b91af;">Context</span>.CurrentConnection.CubeClosing += <span style="color: blue;">new</span> <span style="color: #2b91af;">EventHandler</span>(CubeClosing);<br />
CalculatedMembers = <span style="color: blue;">new</span> <span style="color: #2b91af;">List</span><<span style="color: #2b91af;">CalculatedMember</span>>();<o:p><br />
<br />
</o:p> LoadAll();<o:p><br />
</o:p> }<br />
<o:p><br />
</o:p> [System.Diagnostics.CodeAnalysis.<span style="color: #2b91af;">SuppressMessage</span>(<span style="color: #a31515;">"Microsoft.Security"</span>, <span style="color: #a31515;">"CA2109:ReviewVisibleEventHandlers"</span>)]<br />
<span style="color: blue;">public</span> <span style="color: blue;">void</span> CubeOpened(<span style="color: blue;">object</span> sender, <span style="color: #2b91af;">EventArgs</span> e)<br />
{<br />
<span style="color: blue;">foreach</span> (<span style="color: #2b91af;">CalculatedMember</span> _cm <span style="color: blue;">in</span> CalculatedMembers)<br />
{<br />
<span style="color: blue;">if</span> (<span style="color: #2b91af;">Context</span>.CurrentDatabaseName.ToLower() == _cm.Database.ToLower() &&<br />
<span style="color: #2b91af;">Context</span>.CurrentCube.Name.ToLower() == _cm.Cube.ToLower())<br />
{<br />
<span style="color: green;">// This will subscribe to the events.<br />
</span> <span style="color: #2b91af;">SessionMgr</span> session = <span style="color: blue;">new</span> <span style="color: #2b91af;">SessionMgr</span>();<br />
<span style="color: blue;">string</span> command = <span style="color: blue;"> string</span>.Format(<span style="color: #a31515;">"alter cube [{0}] create member [{1}].[{2}] as {3}"</span>,<br />
_cm.Cube, _cm.Dimension, _cm.Attribute, _cm.Definition);<br />
<o:p><br />
</o:p> <span style="color: #2b91af;">AdomdCommand</span> cmd = <span style="color: blue;">new</span> <span style="color: #2b91af;">AdomdCommand</span>(command);<br />
<o:p><br />
</o:p> <span style="color: green;">// Ideally check to make sure it exists.<br />
</span> <span style="color: green;">// Also if a definition is incorrect, put it in a try/catch block<br />
</span> <span style="color: green;">// so only the bad one fails.<br />
</span> <span style="color: blue;">try</span><br />
{<br />
cmd.ExecuteNonQuery();<br />
}<br />
<span style="color: blue;">catch<br />
</span> {<br />
}<br />
<span style="color: blue;">finally<br />
</span> {<br />
cmd.Dispose();<br />
}<br />
}<br />
}<br />
}<o:p></o:p></span></div><div class="MsoNormal"><span style="font-family: 'Courier New'; font-size: 10pt;"> [System.Diagnostics.CodeAnalysis.<span style="color: #2b91af;">SuppressMessage</span>(<span style="color: #a31515;">"Microsoft.Security"</span>, <span style="color: #a31515;">"CA2109:ReviewVisibleEventHandlers"</span>)]<br />
<span style="color: blue;">public</span> <span style="color: blue;">void</span> CubeClosing(<span style="color: blue;">object</span> sender, <span style="color: #2b91af;">EventArgs</span> e)<br />
{<br />
<span style="color: blue;">try<br />
</span> {<br />
<span style="color: blue;">foreach</span> (<span style="color: #2b91af;">CalculatedMember</span> _cm <span style="color: blue;">in</span> CalculatedMembers)<br />
{<br />
<span style="color: blue;">if</span> (<span style="color: #2b91af;">Context</span>.CurrentCube.Name.ToLower() == _cm.Cube.ToLower())<br />
{<br />
<span style="color: green;">// This will subscribe to the events.<br />
</span> <span style="color: #2b91af;">SessionMgr</span> session = <span style="color: blue;">new</span> <span style="color: #2b91af;">SessionMgr</span>();<br />
<span style="color: blue;">string</span> command = <span style="color: blue;">string</span>.Format(<span style="color: #a31515;">"alter cube [{0}] drop [{1}].[{2}]"</span>,<br />
_cm.Cube, _cm.Dimension, _cm.Attribute);<br />
<o:p><br />
</o:p> <span style="color: #2b91af;">AdomdCommand</span> cmd = <span style="color: blue;">new</span> <span style="color: #2b91af;">AdomdCommand</span>(command);<br />
<span style="color: green;">// Putting this inside of a try catch - when one connects to it via<br />
</span> <span style="color: green;">// management studio it will open several connections for the session (metadata manager, etc.)<br />
</span> <span style="color: blue;">try<br />
</span> {<br />
cmd.ExecuteNonQuery();<br />
}<br />
<span style="color: blue;">catch<br />
</span> {<br />
}<br />
<span style="color: blue;">finally<br />
</span> {<br />
cmd.Dispose();<br />
}<br />
}<br />
}<br />
}<br />
<span style="color: blue;">catch</span> (<span style="color: #2b91af;">AdomdException</span> ex)<br />
{<br />
}<br />
}<br />
<o:p> </o:p> }<br />
}<o:p></o:p></span></div></td> </tr>
</tbody></table><div class="MsoNormal"><span class="Apple-style-span" style="font-family: Arial; font-size: small;"><span class="Apple-style-span" style="font-size: 13px;">And a 3rd class to supress messages (following the example on codeplex)</span></span></div><table style="width: 100%;"><tbody>
<tr style="padding-left: 30pt; padding-right: 30pt;"> <td style="background-color: #ffffcc; border: solid 1pt gray;"><div class="MsoNormal"><span style="color: green; font-family: 'Courier New'; font-size: 10pt;">// This file is used by Code Analysis to maintain SuppressMessage <br />
// attributes that are applied to this project. <br />
// Project-level suppressions either have no target or are given <br />
// a specific target and scoped to a namespace, type, member, etc. <br />
//<br />
// To add a suppression to this file, right-click the message in the <br />
// Error List, point to "Suppress Message(s)", and click <br />
// "In Project Suppression File". <br />
// You do not need to add suppressions to this file manually. <o:p></o:p></span> </div><div class="MsoNormal"><span style="font-family: 'Courier New'; font-size: 10pt;">[<span style="color: blue;">assembly</span>: System.Diagnostics.CodeAnalysis.<span style="color: #2b91af;">SuppressMessage</span>(<span style="color: #a31515;">"Microsoft.Naming"</span>, <span style="color: #a31515;"> "CA1704:IdentifiersShouldBeSpelledCorrectly"</span>, MessageId = <span style="color: #a31515;">"Aspe"</span>)]<o:p></o:p></span></div></td> </tr>
</tbody></table><div class="MsoNormal"><span class="Apple-style-span" style="font-family: Arial; font-size: small;"><span class="Apple-style-span" style="font-size: 13px;">And the place to store it...</span></span></div><table style="width: 100%;"><tbody>
<tr style="padding-left: 30pt; padding-right: 30pt;"> <td style="background-color: #ffffcc; border: solid 1pt gray;"><div class="MsoNormal"><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">CREATE</span><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: blue;">TABLE</span> [dbo]<span style="color: grey;">.</span>[ASCalculatedMember]<span style="color: grey;">(<br />
</span></span> <span style="font-family: 'Courier New'; font-size: 10pt;"> [Id] [int] <span style="color: blue;">IDENTITY</span><span style="color: grey;">(</span>1<span style="color: grey;">,</span>1<span style="color: grey;">)</span> <span style="color: grey;">NOT</span> <span style="color: grey;">NULL,<br />
</span> [Database] [varchar]<span style="color: grey;">(</span>100<span style="color: grey;">)</span> <span style="color: grey;">NOT</span> <span style="color: grey;">NULL,<br />
</span> [Cube] [varchar]<span style="color: grey;">(</span>100<span style="color: grey;">)</span> <span style="color: grey;">NOT</span> <span style="color: grey;">NULL,<br />
</span> [Dimension] [varchar]<span style="color: grey;">(</span>100<span style="color: grey;">)</span> <span style="color: grey;">NOT</span> <span style="color: grey;">NULL,<br />
</span> [Attribute] [varchar]<span style="color: grey;">(</span>100<span style="color: grey;">)</span> <span style="color: grey;">NOT</span> <span style="color: grey;">NULL,<br />
</span> [Definition] [varchar]<span style="color: grey;">(</span><span style="color: magenta;">max</span><span style="color: grey;">)</span> <span style="color: grey;">NOT</span> <span style="color: grey;">NULL,<br />
</span> <span style="color: blue;">CONSTRAINT</span> [PK_ASCalculatedMember] <span style="color: blue;">PRIMARY</span> <span style="color: blue;">KEY</span> <span style="color: blue;">CLUSTERED</span> <br />
</span> <span style="color: grey; font-family: 'Courier New'; font-size: 10pt;">(<br />
</span> <span style="font-family: 'Courier New'; font-size: 10pt;"> [Id] <span style="color: blue;">ASC<br />
</span></span> <span style="color: grey; font-family: 'Courier New'; font-size: 10pt;">)</span><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">WITH </span> <span style="color: grey; font-family: 'Courier New'; font-size: 10pt;">(</span><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">PAD_INDEX</span><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: grey;">=</span> <span style="color: blue;">OFF</span><span style="color: grey;">,</span> <span style="color: blue;">STATISTICS_NORECOMPUTE</span> <span style="color: grey;">=</span> <span style="color: blue;">OFF</span><span style="color: grey;">,</span> <span style="color: blue;">IGNORE_DUP_KEY</span> <span style="color: grey;">=</span> <span style="color: blue;">OFF</span><span style="color: grey;">,</span> <span style="color: blue;">ALLOW_ROW_LOCKS</span> <span style="color: grey;">=</span> <span style="color: blue;">ON</span><span style="color: grey;">,</span> <span style="color: blue;">ALLOW_PAGE_LOCKS</span> <span style="color: grey;">=</span> <span style="color: blue;">ON</span><span style="color: grey;">)</span> <span style="color: blue;">ON</span> [PRIMARY]<br />
</span> <span style="color: grey; font-family: 'Courier New'; font-size: 10pt;">)</span><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: blue;">ON</span> [PRIMARY]</span> </div></td></tr>
</tbody></table>John Henneseyhttp://www.blogger.com/profile/14350314173210731480noreply@blogger.com0tag:blogger.com,1999:blog-7482289774765508546.post-30030816917250547472010-07-19T21:36:00.001-04:002010-07-19T21:56:28.028-04:00How to query for SQL text in rdl stored in reportserver catalog table<div style="font-family: Arial;">Ever make a change to your database and wonder what reports are dependant upon <br />
it? If you use stored procedures / views in your reports you can query the <br />
system catalogs. But if you are using inline SQL you have to root through <br />
the RDL's. The report content is an image field which means you have to <br />
convert it to varbinary (max) then to Xml. From there you can easily look <br />
for your SQL. For different versions of Reporting services use the <br />
corresponding namespace (found at the top of the RDL file).</div><div style="font-family: Arial;"><br />
</div><table style="width: 100%;"><tbody>
<tr style="padding-left: 30pt; padding-right: 30pt;"> <td style="background-color: #ffffcc; border: solid 1pt gray;"><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; font-family: sans-serif;"><span style="color: blue; font-family: 'Courier New';"><span class="Apple-style-span" style="font-size: x-small;">with</span></span><span style="font-family: 'Courier New';"><span class="Apple-style-span" style="font-size: x-small;"> xmlnamespaces</span><span style="color: blue;"><span class="Apple-style-span" style="font-size: x-small;"> </span></span></span></span><br />
<span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; font-family: sans-serif;"><span style="font-family: 'Courier New';"><span style="color: blue;"><span class="Apple-style-span" style="font-size: x-small;"></span></span></span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; font-family: sans-serif; font-size: 16px;"><span style="font-family: 'Courier New';"><span style="color: grey;"><span class="Apple-style-span" style="font-size: x-small;"> (</span></span><span class="Apple-style-span" style="font-size: x-small;"> </span><span style="color: red;"><span class="Apple-style-span" style="font-size: x-small;">'</span><a href="http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition" style="color: blue; text-decoration: underline;" target="_blank"><span class="yshortcuts" id="lw_1279584685_0" style="color: #366388;"><span class="Apple-style-span" style="font-size: x-small;">http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition</span></span></a><span class="Apple-style-span" style="font-size: x-small;">'</span></span><span class="Apple-style-span" style="font-size: x-small;"> </span><span style="color: blue;"><span class="Apple-style-span" style="font-size: x-small;">AS</span></span><span class="Apple-style-span" style="font-size: x-small;"> </span><span class="Apple-style-span" style="font-size: x-small;">REP</span><span class="Apple-style-span" style="font-size: x-small;"> </span><span style="color: grey;"><span class="Apple-style-span" style="font-size: x-small;">)</span> </span></span> <span style="color: blue; font-family: 'Courier New';"><span class="Apple-style-span" style="font-size: x-small;">select</span></span><span style="font-family: 'Courier New';"><span class="Apple-style-span" style="font-size: x-small;"> </span><span style="color: grey;"><span class="Apple-style-span" style="font-size: x-small;">*</span></span><span class="Apple-style-span" style="font-size: x-small;"> </span><span style="color: blue;"><span class="Apple-style-span" style="font-size: x-small;">from</span><span class="Apple-style-span" style="font-size: x-small;"> </span></span><span style="color: grey;"><span class="Apple-style-span" style="font-size: x-small;">(</span> </span><span class="Apple-style-span" style="font-size: x-small;"> </span><span class="Apple-style-span" style="font-size: x-small;"> </span><span style="color: blue;"><span class="Apple-style-span" style="font-size: x-small;">select </span></span><span class="Apple-style-span" style="font-size: x-small;">c</span><span style="color: grey;"><span class="Apple-style-span" style="font-size: x-small;">.</span></span><span style="color: blue;"><span class="Apple-style-span" style="font-size: x-small;">Path</span></span><span style="color: grey;"><span class="Apple-style-span" style="font-size: x-small;">,</span></span></span></span><br />
<span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; border-collapse: separate; color: black; font-family: 'Times New Roman'; font-size: medium; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px;"><span class="Apple-style-span" style="font-family: 'times new roman', 'new york', times, serif; font-size: 16px;"></span></span><br />
<span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; border-collapse: separate; color: black; font-family: 'Times New Roman'; font-size: medium; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px;"><span class="Apple-style-span" style="font-family: 'times new roman', 'new york', times, serif; font-size: 16px;"><div class="MsoNormal" style="font-family: sans-serif; margin-bottom: 0.0001pt; margin-left: 0in; margin-right: 0in; margin-top: 0in;"><span style="font-family: 'Courier New';"><span class="Apple-style-span" style="font-size: x-small;"> c</span><span style="color: grey;"><span class="Apple-style-span" style="font-size: x-small;">.</span></span><span class="Apple-style-span" style="font-size: x-small;">Name</span><span style="color: grey;"><span class="Apple-style-span" style="font-size: x-small;">,</span></span></span></div><div class="MsoNormal" style="font-family: sans-serif; margin-bottom: 0.0001pt; margin-left: 0in; margin-right: 0in; margin-top: 0in;"><span style="font-family: 'Courier New';"><span class="Apple-style-span" style="font-size: x-small;"> DataSetXML</span><span style="color: grey;"><span class="Apple-style-span" style="font-size: x-small;">.</span></span><span class="Apple-style-span" style="font-size: x-small;">value</span><span style="color: grey;"><span class="Apple-style-span" style="font-size: x-small;">(</span></span><span style="color: red;"><span class="Apple-style-span" style="font-size: x-small;">'@Name'</span></span><span style="color: grey;"><span class="Apple-style-span" style="font-size: x-small;">,</span></span><span style="color: red;"><span class="Apple-style-span" style="font-size: x-small;">'varchar(MAX)'</span></span><span style="color: grey;"><span class="Apple-style-span" style="font-size: x-small;">)</span></span><span class="Apple-converted-space"><span class="Apple-style-span" style="font-size: x-small;"> </span></span><span class="Apple-style-span" style="font-size: x-small;">DataSourceName</span><span style="color: grey;"><span class="Apple-style-span" style="font-size: x-small;">,</span></span></span></div><div class="MsoNormal" style="font-family: sans-serif; margin-bottom: 0.0001pt; margin-left: 0in; margin-right: 0in; margin-top: 0in;"><span style="font-family: 'Courier New';"><span class="Apple-style-span" style="font-size: x-small;"> DataSetXML</span><span style="color: grey;"><span class="Apple-style-span" style="font-size: x-small;">.</span></span><span class="Apple-style-span" style="font-size: x-small;">value</span><span style="color: grey;"><span class="Apple-style-span" style="font-size: x-small;">(</span></span><span style="color: red;"><span class="Apple-style-span" style="font-size: x-small;">'REP:Query[1]/REP:CommandText[1]'</span></span><span style="color: grey;"><span class="Apple-style-span" style="font-size: x-small;">,</span></span><span style="color: red;"><span class="Apple-style-span" style="font-size: x-small;">'varchar(MAX)'</span></span><span style="color: grey;"><span class="Apple-style-span" style="font-size: x-small;">)</span></span><span class="Apple-converted-space"><span class="Apple-style-span" style="font-size: x-small;"> </span></span><span class="Apple-style-span" style="font-size: x-small;">CommandText</span></span></div><div class="MsoNormal" style="font-family: sans-serif; margin-bottom: 0.0001pt; margin-left: 0in; margin-right: 0in; margin-top: 0in;"><span style="font-family: 'Courier New';"><span class="Apple-style-span" style="font-size: x-small;"> </span><span class="Apple-converted-space"><span class="Apple-style-span" style="font-size: x-small;"> </span></span><span style="color: blue;"><span class="Apple-style-span" style="font-size: x-small;">from</span></span></span></div><div class="MsoNormal" style="font-family: sans-serif; margin-bottom: 0.0001pt; margin-left: 0in; margin-right: 0in; margin-top: 0in;"><span style="color: blue; font-family: 'Courier New';"><span class="Apple-style-span" style="font-size: x-small;"> </span><span class="Apple-converted-space"><span class="Apple-style-span" style="font-size: x-small;"> </span></span></span><span style="color: grey; font-family: 'Courier New';"><span class="Apple-style-span" style="font-size: x-small;">(</span></span><span style="color: blue; font-family: 'Courier New';"><span class="Apple-style-span" style="font-size: x-small;">select</span></span><span style="font-family: 'Courier New';"><span class="Apple-converted-space"><span class="Apple-style-span" style="font-size: x-small;"> </span></span><span class="Apple-style-span" style="font-size: x-small;">ItemID</span><span style="color: grey;"><span class="Apple-style-span" style="font-size: x-small;">,</span></span></span></div><div class="MsoNormal" style="font-family: sans-serif; margin-bottom: 0.0001pt; margin-left: 0in; margin-right: 0in; margin-top: 0in;"><span style="font-family: 'Courier New';"><span class="Apple-style-span" style="font-size: x-small;"> </span><span class="Apple-converted-space"><span class="Apple-style-span" style="font-size: x-small;"> </span></span><span style="color: magenta;"><span class="Apple-style-span" style="font-size: x-small;">CAST</span></span><span style="color: grey;"><span class="Apple-style-span" style="font-size: x-small;">(</span></span><span style="color: magenta;"><span class="Apple-style-span" style="font-size: x-small;">CAST</span></span><span style="color: grey;"><span class="Apple-style-span" style="font-size: x-small;">(</span></span><span class="Apple-style-span" style="font-size: x-small;">Content</span><span class="Apple-converted-space"><span class="Apple-style-span" style="font-size: x-small;"> </span></span><span style="color: blue;"><span class="Apple-style-span" style="font-size: x-small;">AS</span></span><span class="Apple-converted-space"><span class="Apple-style-span" style="font-size: x-small;"> </span></span><span style="color: blue;"><span class="Apple-style-span" style="font-size: x-small;">varbinary</span></span><span style="color: grey;"><span class="Apple-style-span" style="font-size: x-small;">(</span></span><span style="color: magenta;"><span class="Apple-style-span" style="font-size: x-small;">max</span></span><span style="color: grey;"><span class="Apple-style-span" style="font-size: x-small;">))</span></span><span class="Apple-converted-space"><span class="Apple-style-span" style="font-size: x-small;"> </span></span><span style="color: blue;"><span class="Apple-style-span" style="font-size: x-small;">AS</span></span><span class="Apple-converted-space"><span class="Apple-style-span" style="font-size: x-small;"> </span></span><span style="color: blue;"><span class="Apple-style-span" style="font-size: x-small;">xml</span></span><span style="color: grey;"><span class="Apple-style-span" style="font-size: x-small;">)</span></span><span class="Apple-converted-space"><span class="Apple-style-span" style="font-size: x-small;"> </span></span><span class="Apple-style-span" style="font-size: x-small;">ReportXML</span></span></div><div class="MsoNormal" style="font-family: sans-serif; margin-bottom: 0.0001pt; margin-left: 0in; margin-right: 0in; margin-top: 0in;"><span style="font-family: 'Courier New';"><span class="Apple-style-span" style="font-size: x-small;"> </span><span class="Apple-converted-space"><span class="Apple-style-span" style="font-size: x-small;"> </span></span><span style="color: blue;"><span class="Apple-style-span" style="font-size: x-small;">from</span></span><span class="Apple-converted-space"><span class="Apple-style-span" style="font-size: x-small;"> </span></span><span class="Apple-style-span" style="font-size: x-small;">dbo</span><span style="color: grey;"><span class="Apple-style-span" style="font-size: x-small;">.</span></span><span style="color: blue;"><span class="Apple-style-span" style="font-size: x-small;">Catalog</span></span></span></div><div class="MsoNormal" style="font-family: sans-serif; margin-bottom: 0.0001pt; margin-left: 0in; margin-right: 0in; margin-top: 0in;"><span style="font-family: 'Courier New';"><span class="Apple-style-span" style="font-size: x-small;"> </span><span class="Apple-converted-space"><span class="Apple-style-span" style="font-size: x-small;"> </span></span><span style="color: grey;"><span class="Apple-style-span" style="font-size: x-small;">)</span></span><span class="Apple-converted-space"><span class="Apple-style-span" style="font-size: x-small;"> </span></span><span class="Apple-style-span" style="font-size: x-small;">ReportXML </span></span></div><div class="MsoNormal" style="font-family: sans-serif; margin-bottom: 0.0001pt; margin-left: 0in; margin-right: 0in; margin-top: 0in;"><span class="Apple-style-span" style="font-size: x-small;"><span style="font-family: 'Courier New';"></span></span><span class="Apple-style-span" style="font-family: 'Courier New';"><span style="color: grey;"><span class="Apple-style-span" style="font-size: x-small;"> CROSS</span></span><span class="Apple-style-span" style="font-size: x-small;"> </span><span style="color: grey;"><span class="Apple-style-span" style="font-size: x-small;">APPLY</span></span><span class="Apple-style-span" style="font-size: x-small;"> ReportXML</span><span style="color: grey;"><span class="Apple-style-span" style="font-size: x-small;">.</span></span><span class="Apple-style-span" style="font-size: x-small;">nodes</span><span style="color: grey;"><span class="Apple-style-span" style="font-size: x-small;">(</span></span><span style="color: red;"><span class="Apple-style-span" style="font-size: x-small;">'//REP:DataSet'</span></span><span style="color: grey;"><span class="Apple-style-span" style="font-size: x-small;">)</span></span><span class="Apple-style-span" style="font-size: x-small;"> DataSetXML</span><span style="color: blue;"><span class="Apple-style-span" style="font-size: x-small;"> </span></span><span style="color: grey;"><span class="Apple-style-span" style="font-size: x-small;">(</span></span><span class="Apple-style-span" style="font-size: x-small;">DataSetXML</span><span style="color: grey;"><span class="Apple-style-span" style="font-size: x-small;">)</span></span></span></div><div class="MsoNormal" style="font-family: sans-serif; margin-bottom: 0.0001pt; margin-left: 0in; margin-right: 0in; margin-top: 0in;"><span style="font-family: 'Courier New';"><span class="Apple-style-span" style="font-size: x-small;"> </span><span class="Apple-converted-space"><span class="Apple-style-span" style="font-size: x-small;"> </span></span><span style="color: grey;"><span class="Apple-style-span" style="font-size: x-small;">INNER</span></span><span class="Apple-converted-space"><span class="Apple-style-span" style="font-size: x-small;"> </span></span><span style="color: grey;"><span class="Apple-style-span" style="font-size: x-small;">JOIN</span></span><span class="Apple-converted-space"><span class="Apple-style-span" style="font-size: x-small;"> </span></span><span class="Apple-style-span" style="font-size: x-small;">dbo</span><span style="color: grey;"><span class="Apple-style-span" style="font-size: x-small;">.</span></span><span style="color: blue;"><span class="Apple-style-span" style="font-size: x-small;">Catalog</span></span><span class="Apple-converted-space"><span class="Apple-style-span" style="font-size: x-small;"> </span></span><span class="Apple-style-span" style="font-size: x-small;">c</span><span class="Apple-converted-space"><span class="Apple-style-span" style="font-size: x-small;"> </span></span><span style="color: blue;"><span class="Apple-style-span" style="font-size: x-small;">ON</span></span><span class="Apple-converted-space"><span class="Apple-style-span" style="font-size: x-small;"> </span></span><span class="Apple-style-span" style="font-size: x-small;">ReportXML</span><span style="color: grey;"><span class="Apple-style-span" style="font-size: x-small;">.</span></span><span class="Apple-style-span" style="font-size: x-small;">ItemID</span><span class="Apple-converted-space"><span class="Apple-style-span" style="font-size: x-small;"> </span></span><span style="color: grey;"><span class="Apple-style-span" style="font-size: x-small;">=</span></span><span class="Apple-converted-space"><span class="Apple-style-span" style="font-size: x-small;"> </span></span><span class="Apple-style-span" style="font-size: x-small;">c</span><span style="color: grey;"><span class="Apple-style-span" style="font-size: x-small;">.</span></span><span class="Apple-style-span" style="font-size: x-small;">ItemID</span></span></div><div class="MsoNormal" style="font-family: sans-serif; margin-bottom: 0.0001pt; margin-left: 0in; margin-right: 0in; margin-top: 0in;"><span style="color: grey; font-family: 'Courier New';"><span class="Apple-style-span" style="font-size: x-small;">)</span></span><span style="font-family: 'Courier New';"><span class="Apple-converted-space"><span class="Apple-style-span" style="font-size: x-small;"> </span></span><span class="Apple-style-span" style="font-size: x-small;">tbl</span></span></div></span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; border-collapse: separate; color: black; font-family: 'Times New Roman'; font-size: medium; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px;"><span class="Apple-style-span" style="font-family: 'times new roman', 'new york', times, serif; font-size: 16px;"><span class="Apple-style-span" style="font-family: sans-serif;"><span style="color: blue; font-family: 'Courier New';"><span class="Apple-style-span" style="font-size: x-small;">where</span></span><span style="font-family: 'Courier New';"><span class="Apple-style-span" style="font-size: x-small;"> CommandText </span><span style="color: grey;"><span class="Apple-style-span" style="font-size: x-small;">like</span></span><span class="Apple-style-span" style="font-size: x-small;"> </span><span style="color: red;"><span class="Apple-style-span" style="font-size: x-small;">'%search </span></span></span></span><span class="Apple-style-span" style="color: red; font-family: 'Courier New';"><span class="Apple-style-span" style="font-size: x-small;">criteria goes here...%'</span></span></span></span></td> </tr>
</tbody></table>John Henneseyhttp://www.blogger.com/profile/14350314173210731480noreply@blogger.com0tag:blogger.com,1999:blog-7482289774765508546.post-80451468764987746822010-07-16T23:00:00.000-04:002010-07-19T21:55:56.026-04:00Query system catalogs for foreign key constraints<div style="font-family: Arial;">While <br />
putting together my latest warehouse (on a very tight deadline) I noticed that <br />
some ETL jobs were taking way longer than expected. I thought I had disabled all <br />
referential constraints, but while digging through (there were quite a few of <br />
them - and that little FK window is darn near useless) I found one that was <br />
still enforcing RI. Rather than dig through all the tables, why not just hit the <br />
system catalogs? Ran across this nifty little script... this will show you all <br />
constraints sorted by enabled / disabled.</div><div style="font-family: Arial;"><br />
</div><table style="width: 100%;"><tbody>
<tr style="padding-left: 30pt; padding-right: 30pt;"> <td style="background-color: #ffffcc; border: solid 1pt gray;"><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">select </span><span class="Apple-style-span" style="font-family: 'Courier New'; font-size: 13px;">[status] =</span><br />
<div class="MsoNormal"><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: blue;">case<br />
</span> <span style="color: blue;">when</span> <span style="color: magenta;">objectproperty</span><span style="color: grey;">(</span>constid<span style="color: grey;">,</span> <span style="color: red;">'CnstIsDisabled'</span><span style="color: grey;">)</span> <span style="color: grey;">=</span> 0 <span style="color: blue;">then</span> <span style="color: red;">'Enabled'</span><br />
<span style="color: blue;">else</span> <span style="color: red;">'Disabled'<br />
</span> <span style="color: blue;">end</span><span style="color: grey;">,<br />
</span> [constraint_name] <span style="color: grey;">=</span> <span style="color: magenta;">object_name</span><span style="color: grey;">(</span>constid<span style="color: grey;">),<br />
</span> [table_name] <span style="color: grey;">=</span> <span style="color: magenta;">object_name</span><span style="color: grey;">(</span>fKeyId<span style="color: grey;">),<br />
</span> [column_name] <span style="color: grey;">=</span> <span style="color: magenta;">col_name</span><span style="color: grey;">(</span>fKeyId<span style="color: grey;">,</span> fKey<span style="color: grey;">),<br />
</span> [referenced_table_name] <span style="color: grey;">=</span> <span style="color: magenta;">object_name</span><span style="color: grey;">(</span>rKeyId<span style="color: grey;">),<br />
</span> [referenced_column_name] <span style="color: grey;">=</span> <span style="color: magenta;">col_name</span><span style="color: grey;">(</span>rKeyId<span style="color: grey;">,</span> rKey<span style="color: grey;">)<br />
</span></span> <span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">from</span><span style="font-family: 'Courier New'; font-size: 10pt;"> </span><span style="font-family: 'Courier New'; font-size: 10pt;"><span style="color: green;">sysforeignkeys</span></span><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;"><br />
where</span><span style="font-family: 'Courier New'; font-size: 10pt;"> </span><span style="font-family: 'Courier New'; font-size: 10pt;"><span style="color: magenta;">objectproperty</span><span style="color: grey;">(</span>constid<span style="color: grey;">,</span> <span style="color: red;">'CnstIsDisabled'</span><span style="color: grey;">)</span> <span style="color: grey;">=</span> 0<br />
</span> <span style="color: blue; font-family: 'Courier New'; font-size: 10pt; line-height: 115%;">order</span><span style="font-family: 'Courier New'; font-size: 10pt; line-height: 115%;"> </span><span style="font-family: 'Courier New'; font-size: 10pt; line-height: 115%;"><span style="color: blue;">by</span> 1</span></div></td> </tr>
</tbody></table>John Henneseyhttp://www.blogger.com/profile/14350314173210731480noreply@blogger.com0tag:blogger.com,1999:blog-7482289774765508546.post-48935698392790084002010-07-16T22:38:00.001-04:002010-07-22T14:14:21.232-04:00Execute stored proc into a temp table<div style="font-family: Arial;"><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; border-collapse: separate; color: black; font-family: 'Times New Roman'; font-size: medium; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px;"><span class="Apple-style-span" style="color: #222222; font-family: arial; font-size: 13px; line-height: 18px;">Ever want to select the results from a sproc into a temp table? Why you might <br />
ask? Sometimes I do not have access to the source (encrypted), or it is on a <br />
production system and I am not able to crack it open to run it bit by bit, and <br />
have only the option of running the sproc plain vanilla. Here is the code to <br />
capture it into a temp table (no need to worry about declaring the table). Only <br />
catch is openrowset settings need to be enabled.</span></span></div><div style="font-family: Arial;"><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; border-collapse: separate; color: black; font-family: 'Times New Roman'; font-size: medium; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px;"><span class="Apple-style-span" style="color: #222222; font-family: arial; font-size: 13px; line-height: 18px;"><br />
</span></span></div><table style="width: 100%;"><tbody>
<tr style="padding-left: 30pt; padding-right: 30pt;"> <td style="background-color: #ffffcc; border: solid 1pt gray;"><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; color: #222222; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; line-height: 18px;"><span class="Apple-style-span" style="font-family: sans-serif;"><span style="color: blue; font-family: 'Courier New';"><span class="Apple-style-span" style="font-size: small;">SELECT</span></span><span style="font-family: 'Courier New';"><span class="Apple-style-span" style="font-size: small;"> </span><span style="color: grey;"><span class="Apple-style-span" style="font-size: small;">*</span></span><span class="Apple-style-span" style="font-size: small;"> </span><span style="color: blue;"><span class="Apple-style-span" style="font-size: small;">INTO</span></span><span class="Apple-style-span" style="font-size: small;"> #tmp</span></span></span></span><br />
<span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; color: #222222; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; line-height: 18px;"><span class="Apple-style-span" style="font-family: sans-serif;"><span style="font-family: 'Courier New';"><span style="color: blue;"><span class="Apple-style-span" style="font-size: small;">FROM </span></span><span style="color: blue;"><span class="Apple-style-span" style="font-size: small;">OPENROWSET</span></span><span style="color: grey;"><span class="Apple-style-span" style="font-size: small;">(</span></span><span style="color: red;"><span class="Apple-style-span" style="font-size: small;">'SQLNCLI'</span></span><span style="color: grey;"><span class="Apple-style-span" style="font-size: small;">,</span></span><span style="color: red;"><span class="Apple-style-span" style="font-size: small;">'Server=(local);Trusted_Connection=yes;'</span></span><span style="color: grey;"><span class="Apple-style-span" style="font-size: small;">,</span></span></span></span></span><br />
<span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; color: #222222; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; line-height: 18px;"><span class="Apple-style-span" style="font-family: sans-serif;"><span style="font-family: 'Courier New';"><span style="color: grey;"><span class="Apple-style-span" style="font-size: small;"></span></span></span></span><span class="Apple-style-span" style="font-family: 'Courier New';"><span style="color: red;"><span class="Apple-style-span" style="font-size: small;"> 'EXEC [database].[schema].[stored </span></span></span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; color: #222222; font-family: 'Courier New'; line-height: 18px;"><span style="color: red;"><span class="Apple-style-span" style="font-size: small;">proc]'</span></span><span style="color: grey;"><span class="Apple-style-span" style="font-size: small;">)</span></span></span></td> </tr>
</tbody></table>John Henneseyhttp://www.blogger.com/profile/14350314173210731480noreply@blogger.com0tag:blogger.com,1999:blog-7482289774765508546.post-29539492099396141402010-07-16T22:33:00.000-04:002010-07-16T22:36:11.649-04:00Welcome<span class="Apple-style-span" style="font-family:arial;">Welcome to my blog. For years I have used my yahoo account to keep track of the little technical tid-bits I have picked up over the years, and after fumbling around through emails I have decided enough! So this is starting out as my personal repository of tricks and code memos. If you ever have a question about databases, specifically SQL Server, feel free to post a comment and ask - I will most certainly help you!</span>John Henneseyhttp://www.blogger.com/profile/14350314173210731480noreply@blogger.com0