tag:blogger.com,1999:blog-36933622014046173432023-11-15T22:50:49.598-08:00SQL ELFEric Freemanhttp://www.blogger.com/profile/12705799218796920483noreply@blogger.comBlogger10125tag:blogger.com,1999:blog-3693362201404617343.post-70944578815079635442019-12-23T09:37:00.000-08:002019-12-23T09:38:37.930-08:00Scalar UDF Inlining (SQL 2019)- What is it and how do we know if we can use it?<span style="font-family: inherit;"><br /></span>
<br />
<div class="MsoNormal">
<span style="font-family: inherit;">I have been pretty excited about the new SQL 2019 feature “Scalar
UDF inlining”. I want to share what I learned about Scalar UDF inlining because it's awesome. It makes
functions easier to implement without becoming a plague to performance, thus functions will be used more often (finally).</span></div>
<div class="MsoNormal">
<span style="font-family: inherit;"><br /></span></div>
<div class="MsoNormal">
<span style="font-family: inherit;">Scalar UDF inlining in SQL 2019 simply makes most
(non-inlined) UDF’s execute faster. The SQL 2019 query engine interprets the
scalar UDF’s logic and makes it set-based (inlined), ready to take advantage of
the performance improvements that come with set based processing.<o:p></o:p></span><br />
<br /></div>
<div class="MsoNormal">
<span style="font-family: inherit;"><b>So how do we know if an existing function would be able to
use this new feature?</b></span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;">In my opinion, the easiest way to know if your function
is able to take advantage of this is to simply query the <b style="mso-bidi-font-weight: normal;">sys.sql_modules</b> view (SQL 2019). The <b>sys.sql_modules</b> catalog view
includes a property called “is_inlinable”, which indicates whether a UDF is
inlinable or not.</span><br />
<span style="background-color: white; color: #171717; text-indent: -24px;">·</span><span style="background-color: white; color: #171717; font-stretch: normal; line-height: normal; text-indent: -24px;"> </span><span style="font-family: inherit;">A value of 1 indicates that it is inlinable, and 0 indicates
otherwise</span><br />
<span style="background-color: white; color: #171717; text-indent: -24px;">·</span><span style="background-color: white; color: #171717; font-stretch: normal; line-height: normal; text-indent: -24px;"> </span><span style="font-family: inherit;">This property will also have a value of 1 for inline
table-valued functions, since they are inlinable by definition.</span><br />
<span style="font-family: inherit;"><br /></span></div>
<div class="MsoNormal">
<span style="font-family: inherit;">In my next blog post I will cover how to manually change scalar
UDFs into an inlinable (for those that won’t be going to 2019 any time soon).<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: inherit;"><br /></span></div>
<div class="MsoNormal">
<span style="font-family: inherit;"><b>Reference:</b></span><br />
<span style="font-family: inherit;">Microsoft has a large list of requirements that let you know if a
Scalar UDF can be inlined:<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: inherit;">(<a href="https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/scalar-udf-inlining?view=sql-server-ver15">Source: Microsoft</a>)<o:p></o:p></span></div>
<div class="MsoNormal" style="background: white; line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;">
<span style="color: #171717; font-family: inherit;">A
scalar T-SQL UDF can be inline if all of the following conditions are true:<o:p></o:p></span></div>
<div class="MsoNormal" style="background: white; line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.5pt; margin-right: 0in; margin-top: 0in; mso-list: l0 level1 lfo1; tab-stops: list .5in; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: inherit;"><span style="color: #171717;"><span style="mso-list: Ignore;">·<span style="font-stretch: normal; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
</span></span></span><!--[endif]--><span style="color: #171717;">The UDF is written
using the following constructs:<o:p></o:p></span></span></div>
<div class="MsoNormal" style="background: white; line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 43.5pt; margin-right: 0in; margin-top: 0in; mso-list: l0 level2 lfo1; tab-stops: list 1.0in; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: inherit;"><span style="color: #171717;"><span style="mso-list: Ignore;">o<span style="font-stretch: normal; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;"> </span></span></span><!--[endif]--><span style="color: #171717;">DECLARE</span><span style="color: #171717;">, </span><span style="color: #171717;">SET</span><span style="color: #171717;">: Variable declaration
and assignments.<o:p></o:p></span></span></div>
<div class="MsoNormal" style="background: white; line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 43.5pt; margin-right: 0in; margin-top: 0in; mso-list: l0 level2 lfo1; tab-stops: list 1.0in; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: inherit;"><span style="color: #171717;"><span style="mso-list: Ignore;">o<span style="font-stretch: normal; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;"> </span></span></span><!--[endif]--><span style="color: #171717;">SELECT</span><span style="color: #171717;">: SQL query with
single/multiple variable assignments</span><sup><span style="color: #171717;">1</span></sup><span style="color: #171717;">.<o:p></o:p></span></span></div>
<div class="MsoNormal" style="background: white; line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 43.5pt; margin-right: 0in; margin-top: 0in; mso-list: l0 level2 lfo1; tab-stops: list 1.0in; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: inherit;"><span style="color: #171717;"><span style="mso-list: Ignore;">o<span style="font-stretch: normal; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;"> </span></span></span><!--[endif]--><span style="color: #171717;">IF</span><span style="color: #171717;">/</span><span style="color: #171717;">ELSE</span><span style="color: #171717;">: Branching with
arbitrary levels of nesting.<o:p></o:p></span></span></div>
<div class="MsoNormal" style="background: white; line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 43.5pt; margin-right: 0in; margin-top: 0in; mso-list: l0 level2 lfo1; tab-stops: list 1.0in; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: inherit;"><span style="color: #171717;"><span style="mso-list: Ignore;">o<span style="font-stretch: normal; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;"> </span></span></span><!--[endif]--><span style="color: #171717;">RETURN</span><span style="color: #171717;">: Single or multiple
return statements.<o:p></o:p></span></span></div>
<div class="MsoNormal" style="background: white; line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 43.5pt; margin-right: 0in; margin-top: 0in; mso-list: l0 level2 lfo1; tab-stops: list 1.0in; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: inherit;"><span style="color: #171717;"><span style="mso-list: Ignore;">o<span style="font-stretch: normal; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;"> </span></span></span><!--[endif]--><span style="color: #171717;">UDF</span><span style="color: #171717;">: Nested/recursive
function calls</span><sup><span style="color: #171717;">2</span></sup><span style="color: #171717;">.<o:p></o:p></span></span></div>
<div class="MsoNormal" style="background: white; line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 43.5pt; margin-right: 0in; margin-top: 0in; mso-list: l0 level2 lfo1; tab-stops: list 1.0in; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: inherit;"><span style="color: #171717;"><span style="mso-list: Ignore;">o<span style="font-stretch: normal; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;"> </span></span></span><!--[endif]--><span style="color: #171717;">Others: Relational operations such as </span><span style="color: #171717;">EXISTS</span><span style="color: #171717;">, </span><span style="color: #171717;">ISNULL</span><span style="color: #171717;">.<o:p></o:p></span></span></div>
<div class="MsoNormal" style="background: white; line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.5pt; margin-right: 0in; margin-top: 0in; mso-list: l0 level1 lfo1; tab-stops: list .5in; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: inherit;"><span style="color: #171717;"><span style="mso-list: Ignore;">·<span style="font-stretch: normal; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
</span></span></span><!--[endif]--><span style="color: #171717;">The UDF does not
invoke any intrinsic function that is either time-dependent (such as </span><span style="color: #171717;">GETDATE()</span><span style="color: #171717;">) or has side effects</span><sup><span style="color: #171717;">3</span></sup><span style="color: #171717;"> (such as </span><span style="color: #171717;">NEWSEQUENTIALID()</span><span style="color: #171717;">).<o:p></o:p></span></span></div>
<div class="MsoNormal" style="background: white; line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.5pt; margin-right: 0in; margin-top: 0in; mso-list: l0 level1 lfo1; tab-stops: list .5in; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: inherit;"><span style="color: #171717;"><span style="mso-list: Ignore;">·<span style="font-stretch: normal; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
</span></span></span><!--[endif]--><span style="color: #171717;">The UDF uses the </span><span style="color: #171717;">EXECUTE AS CALLER</span><span style="color: #171717;"> clause (the default behavior if the </span><span style="color: #171717;">EXECUTE AS</span><span style="color: #171717;"> clause is not
specified).<o:p></o:p></span></span></div>
<div class="MsoNormal" style="background: white; line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.5pt; margin-right: 0in; margin-top: 0in; mso-list: l0 level1 lfo1; tab-stops: list .5in; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: inherit;"><span style="color: #171717;"><span style="mso-list: Ignore;">·<span style="font-stretch: normal; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
</span></span></span><!--[endif]--><span style="color: #171717;">The UDF does not
reference table variables or table-valued parameters.<o:p></o:p></span></span></div>
<div class="MsoNormal" style="background: white; line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.5pt; margin-right: 0in; margin-top: 0in; mso-list: l0 level1 lfo1; tab-stops: list .5in; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: inherit;"><span style="color: #171717;"><span style="mso-list: Ignore;">·<span style="font-stretch: normal; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
</span></span></span><!--[endif]--><span style="color: #171717;">The query invoking a
scalar UDF does not reference a scalar UDF call in its </span><span style="color: #171717;">GROUP BY</span><span style="color: #171717;"> clause.<o:p></o:p></span></span></div>
<div class="MsoNormal" style="background: white; line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.5pt; margin-right: 0in; margin-top: 0in; mso-list: l0 level1 lfo1; tab-stops: list .5in; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: inherit;"><span style="color: #171717;"><span style="mso-list: Ignore;">·<span style="font-stretch: normal; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
</span></span></span><!--[endif]--><span style="color: #171717;">The query invoking a
scalar UDF in its select list with </span><span style="color: #171717;">DISTINCT</span><span style="color: #171717;"> clause does not have </span><span style="color: #171717;">ORDER BY</span><span style="color: #171717;"> clause.<o:p></o:p></span></span></div>
<div class="MsoNormal" style="background: white; line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.5pt; margin-right: 0in; margin-top: 0in; mso-list: l0 level1 lfo1; tab-stops: list .5in; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: inherit;"><span style="color: #171717;"><span style="mso-list: Ignore;">·<span style="font-stretch: normal; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
</span></span></span><!--[endif]--><span style="color: #171717;">The UDF is not used
in </span><span style="color: #171717;">ORDER BY</span><span style="color: #171717;"> clause.<o:p></o:p></span></span></div>
<div class="MsoNormal" style="background: white; line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.5pt; margin-right: 0in; margin-top: 0in; mso-list: l0 level1 lfo1; tab-stops: list .5in; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: inherit;"><span style="color: #171717;"><span style="mso-list: Ignore;">·<span style="font-stretch: normal; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
</span></span></span><!--[endif]--><span style="color: #171717;">The UDF is not
natively compiled (interop is supported).<o:p></o:p></span></span></div>
<div class="MsoNormal" style="background: white; line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.5pt; margin-right: 0in; margin-top: 0in; mso-list: l0 level1 lfo1; tab-stops: list .5in; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: inherit;"><span style="color: #171717;"><span style="mso-list: Ignore;">·<span style="font-stretch: normal; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
</span></span></span><!--[endif]--><span style="color: #171717;">The UDF is not used in
a computed column or a check constraint definition.<o:p></o:p></span></span></div>
<div class="MsoNormal" style="background: white; line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.5pt; margin-right: 0in; margin-top: 0in; mso-list: l0 level1 lfo1; tab-stops: list .5in; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: inherit;"><span style="color: #171717;"><span style="mso-list: Ignore;">·<span style="font-stretch: normal; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
</span></span></span><!--[endif]--><span style="color: #171717;">The UDF does not
reference user-defined types.<o:p></o:p></span></span></div>
<div class="MsoNormal" style="background: white; line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.5pt; margin-right: 0in; margin-top: 0in; mso-list: l0 level1 lfo1; tab-stops: list .5in; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: inherit;"><span style="color: #171717;"><span style="mso-list: Ignore;">·<span style="font-stretch: normal; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
</span></span></span><!--[endif]--><span style="color: #171717;">There are no
signatures added to the UDF.<o:p></o:p></span></span></div>
<div class="MsoNormal" style="background: white; line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: 28.5pt; margin-right: 0in; margin-top: 0in; mso-list: l0 level1 lfo1; tab-stops: list .5in; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: inherit;"><span style="color: #171717;"><span style="mso-list: Ignore;">·<span style="font-stretch: normal; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;">
</span></span></span><!--[endif]--><span style="color: #171717;">The UDF is not a
partition function.<o:p></o:p></span></span></div>
<div class="MsoNormal" style="background: white; line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;">
<span style="font-family: inherit;"><sup><span style="color: #171717;">1</span></sup><span style="color: #171717;"> </span><span style="color: #171717;">SELECT</span><span style="color: #171717;"> with variable
accumulation/aggregation (for example, </span><span style="color: #171717;">SELECT
@val += col1 FROM table1</span><span style="color: #171717;">) is not supported for
inlining.<o:p></o:p></span></span></div>
<div class="MsoNormal" style="background: white; line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;">
<span style="font-family: inherit;"><sup><span style="color: #171717;">2</span></sup><span style="color: #171717;"> Recursive UDFs
will be inlined to a certain depth only.<o:p></o:p></span></span></div>
<div class="MsoNormal" style="background: white; line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;">
<span style="font-family: inherit;"><sup><span style="color: #171717;">3</span></sup><span style="color: #171717;"> Intrinsic
functions whose results depend upon the current system time are time-dependent.
An intrinsic function that may update some internal global state is an example
of a function with side effects. Such functions return different results each
time they are called, based on the internal state.<o:p></o:p></span></span></div>
<br />Eric Freemanhttp://www.blogger.com/profile/12705799218796920483noreply@blogger.com0tag:blogger.com,1999:blog-3693362201404617343.post-43997898080475648792017-11-28T07:00:00.000-08:002017-11-29T14:25:41.375-08:00SSIS Script Task, using C#, to Download Files w/ variable Security ProtocolsI have a SSIS package that I use to download files from various websites. I began by using a C# script published by Tom Archer - MSFT on <a href="https://www.codeguru.com/columns/dotnettips/article.php/c7005/Downloading-Files-with-the-WebRequest-and-WebResponse-Classes.htm">https://www.codeguru.com/columns/dotnettips/article.php/c7005/Downloading-Files-with-the-WebRequest-and-WebResponse-Classes.htm</a>. I had to modify this script because certain security protocols were required for some sites but not others. I've included my modifications below so that Security Protocols can be controlled based on variables I pass to the Script Task. My resulting code is as follows:<br />
<br />
<BR>
<!-- HTML generated using hilite.me --><div style="background: #ffffff; overflow:auto;width:auto;border:solid gray;border-width:.1em .1em .1em .8em;padding:.2em .6em;"><pre style="margin: 0; line-height: 125%"><span style="color: #008800; font-weight: bold">public</span> <span style="color: #008800; font-weight: bold">static</span> <span style="color: #333399; font-weight: bold">int</span> <span style="color: #0066BB; font-weight: bold">DownloadFile</span>(String remoteFilename,
String localFilename,
String securityProtocolType)
{
<span style="color: #888888">//Make sure to use the following namespaces:</span>
<span style="color: #888888">//using System.Net;</span>
<span style="color: #888888">//using System.IO;</span>
<span style="color: #888888">// Function will return the number of bytes processed</span>
<span style="color: #888888">// to the caller. Initialize to 0 here.</span>
<span style="color: #333399; font-weight: bold">int</span> bytesProcessed = <span style="color: #6600EE; font-weight: bold">0</span>;
<span style="color: #888888">// Assign values to these objects here so that they can</span>
<span style="color: #888888">// be referenced in the finally block</span>
Stream remoteStream = <span style="color: #008800; font-weight: bold">null</span>;
Stream localStream = <span style="color: #008800; font-weight: bold">null</span>;
WebResponse response = <span style="color: #008800; font-weight: bold">null</span>;
<span style="color: #008800; font-weight: bold">if</span> (securityProtocolType == <span style="background-color: #fff0f0">"Tls12"</span>) <span style="color: #888888">//Added if statement since some downloads will fail with Tls12 selected</span>
{ ServicePointManager.SecurityProtocol = (SecurityProtocolType)<span style="color: #6600EE; font-weight: bold">3072</span>; } <span style="color: #888888">// 3072 = TLS 1.2 Security Protocol</span>
<span style="color: #888888">// Use a try/catch/finally block as both the WebRequest and Stream</span>
<span style="color: #888888">// classes throw exceptions upon error</span>
<span style="color: #008800; font-weight: bold">try</span>
{
<span style="color: #888888">// Create a request for the specified remote file name</span>
WebRequest request = WebRequest.Create(remoteFilename);
<span style="color: #008800; font-weight: bold">if</span> (request != <span style="color: #008800; font-weight: bold">null</span>)
{
<span style="color: #888888">// Send the request to the server and retrieve the</span>
<span style="color: #888888">// WebResponse object </span>
response = request.GetResponse();
<span style="color: #008800; font-weight: bold">if</span> (response != <span style="color: #008800; font-weight: bold">null</span>)
{
<span style="color: #888888">// Once the WebResponse object has been retrieved,</span>
<span style="color: #888888">// get the stream object associated with the response's data</span>
remoteStream = response.GetResponseStream();
<span style="color: #888888">// Create the local file</span>
localStream = File.Create(localFilename);
<span style="color: #888888">// Allocate a 1k buffer</span>
<span style="color: #333399; font-weight: bold">byte</span>[] buffer = <span style="color: #008800; font-weight: bold">new</span> <span style="color: #333399; font-weight: bold">byte</span>[<span style="color: #6600EE; font-weight: bold">1024</span>];
<span style="color: #333399; font-weight: bold">int</span> bytesRead;
<span style="color: #888888">// Simple do/while loop to read from stream until</span>
<span style="color: #888888">// no bytes are returned</span>
<span style="color: #008800; font-weight: bold">do</span>
{
<span style="color: #888888">// Read data (up to 1k) from the stream</span>
bytesRead = remoteStream.Read(buffer, <span style="color: #6600EE; font-weight: bold">0</span>, buffer.Length);
<span style="color: #888888">// Write the data to the local file</span>
localStream.Write(buffer, <span style="color: #6600EE; font-weight: bold">0</span>, bytesRead);
<span style="color: #888888">// Increment total bytes processed</span>
bytesProcessed += bytesRead;
} <span style="color: #008800; font-weight: bold">while</span> (bytesRead > <span style="color: #6600EE; font-weight: bold">0</span>);
}
}
}
<span style="color: #008800; font-weight: bold">catch</span> (Exception e)
{
Console.WriteLine(e.Message);
}
<span style="color: #008800; font-weight: bold">finally</span>
{
<span style="color: #888888">// Close the response and streams objects here </span>
<span style="color: #888888">// to make sure they're closed even if an exception</span>
<span style="color: #888888">// is thrown at some point</span>
<span style="color: #008800; font-weight: bold">if</span> (response != <span style="color: #008800; font-weight: bold">null</span>) response.Close();
<span style="color: #008800; font-weight: bold">if</span> (remoteStream != <span style="color: #008800; font-weight: bold">null</span>) remoteStream.Close();
<span style="color: #008800; font-weight: bold">if</span> (localStream != <span style="color: #008800; font-weight: bold">null</span>) localStream.Close();
}
<span style="color: #888888">// Return total bytes processed to caller.</span>
<span style="color: #008800; font-weight: bold">return</span> bytesProcessed;
}
<span style="color: #888888">/// <summary></span>
<span style="color: #888888">/// This method is called when this script task executes in the control flow.</span>
<span style="color: #888888">/// Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.</span>
<span style="color: #888888">/// To open Help, press F1.</span>
<span style="color: #888888">/// </summary></span>
<span style="color: #008800; font-weight: bold">public</span> <span style="color: #008800; font-weight: bold">void</span> <span style="color: #0066BB; font-weight: bold">Main</span>()
{
<span style="color: #333399; font-weight: bold">int</span> read110 = DownloadFile(Dts.Variables[<span style="background-color: #fff0f0">"User::rSourceFullName"</span>].Value.ToString(), Dts.Variables[<span style="background-color: #fff0f0">"User::rDestinationFullName"</span>].Value.ToString(), Dts.Variables[<span style="background-color: #fff0f0">"User::rSecurityProtocolType"</span>].Value.ToString());
<span style="color: #888888">//if no bytes are processed return error</span>
<span style="color: #008800; font-weight: bold">if</span> (read110 > <span style="color: #6600EE; font-weight: bold">0</span>) { Dts.TaskResult = (<span style="color: #333399; font-weight: bold">int</span>)ScriptResults.Success; }
<span style="color: #008800; font-weight: bold">else</span> { Dts.TaskResult = (<span style="color: #333399; font-weight: bold">int</span>)ScriptResults.Failure; }
}
</pre></div>
Eric Freemanhttp://www.blogger.com/profile/12705799218796920483noreply@blogger.com0tag:blogger.com,1999:blog-3693362201404617343.post-83414610196864168952017-10-20T08:00:00.000-07:002017-11-29T14:27:33.298-08:00PASS Summit Tips for First TimersI volunteered for the pass buddy program and shared a list of tips with them for attending PASS Summit 2017.
<br><br><br>
Tip #1- if your staying near the event I would not suggest a rental car. You can catch the shuttle to/from the airport & Uber to other places you might want to go.
<br><br>
Tip #2- Sign up for after parties before you head down. This is a good time to meet other people.
<br><br>
Tip #3- Have a flexible schedule. Try to skip one time slot (maybe a slot where nothing seems overly interesting to you) to visit the vendor area (it’ll be less crowded). Also a session you start to attend may not be exactly what you expected, have a back up session you can go to.
<br><br>
Tip #4- Skip another time slot and talk to the Microsoft team that is on sight, they can give you great job specific advice on a project you may be working on or planning to implement.
<br><br>
Tip #5- Don’t bring a laptop, unless needed. Do you really want to lug it around all day? All sessions are recorded so you can reference them (as long as you purchased the recordings) *note it takes a bit of time (1-2 months) to receive the recordings*
<br><br>
Tip #6- Make sure your luggage has room for all the swag you end up picking up.
Eric Freemanhttp://www.blogger.com/profile/12705799218796920483noreply@blogger.com0tag:blogger.com,1999:blog-3693362201404617343.post-84422377536332266142016-02-29T08:00:00.000-08:002016-02-29T08:00:06.439-08:00
<span style="font-size: large;"><span style="color: #365f91;"><span style="font-family: Cambria;">How to turn off IntelliSense in SQL Server Management Studio<o:p></o:p></span></span></span><br />
<br />
<div class="MsoNormal" style="margin: 0in 0in 10pt;">
<span style="font-family: Calibri;">IntelliSense in SQL Server Management Studio is beneficial to some, but irritating to others. For me, when I type out a line of simple code, IntelliSense will spam me with suggestions and actually cause
my script to become gibberish if I ignore the suggestions. The suggestions will automatically be selected
if I type too fast and don’t hit the escape key every time a seemingly
meaningless IntelliSense option is selected. I will note that in SQL 2014 IntelliSense has become better, and I do use it now; however, while working in 2008R2 it was a major PIA and I always disabled it. Here is the way to turn it off,
for good, without having to hit the IntelliSense Button in the SQL Editor
Toolbar every time you load SQL.<o:p></o:p></span></div>
<br />
<h2 style="margin: 10pt 0in 0pt;">
<span style="font-size: medium;"><span style="color: #4f81bd;"><span style="font-family: Cambria;">IntelliSense Button in the SQL Editor Toolbar<span style="mso-no-proof: yes;"> <o:p></o:p></span></span></span></span></h2>
<br />
<div class="separator" style="clear: both; text-align: left;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhkC2Bb4D5U9EOFg-XqYe3VtkBzeggADwr4VzmRjH40M4cOh-FkFMkXIvuK2sQRZezhHHuiDpcRUM7dmUlm-x8eLqqZ7gOs0ga3YOsGY4HPGfxmSvmf4NvIyGDoDZmSrJKSipNICepSTh5f/s1600/IntelliSense+Toolbar.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhkC2Bb4D5U9EOFg-XqYe3VtkBzeggADwr4VzmRjH40M4cOh-FkFMkXIvuK2sQRZezhHHuiDpcRUM7dmUlm-x8eLqqZ7gOs0ga3YOsGY4HPGfxmSvmf4NvIyGDoDZmSrJKSipNICepSTh5f/s1600/IntelliSense+Toolbar.jpg" /></a></div>
<div class="MsoNormal" style="margin: 0in 0in 10pt;">
<span style="mso-no-proof: yes;"><!--[if gte vml 1]><v:shapetype
id="_x0000_t75" coordsize="21600,21600" o:spt="75" o:preferrelative="t"
path="m@4@5l@4@11@9@11@9@5xe" filled="f" stroked="f">
<v:stroke joinstyle="miter"/>
<v:formulas>
<v:f eqn="if lineDrawn pixelLineWidth 0"/>
<v:f eqn="sum @0 1 0"/>
<v:f eqn="sum 0 0 @1"/>
<v:f eqn="prod @2 1 2"/>
<v:f eqn="prod @3 21600 pixelWidth"/>
<v:f eqn="prod @3 21600 pixelHeight"/>
<v:f eqn="sum @0 0 1"/>
<v:f eqn="prod @6 1 2"/>
<v:f eqn="prod @7 21600 pixelWidth"/>
<v:f eqn="sum @8 21600 0"/>
<v:f eqn="prod @7 21600 pixelHeight"/>
<v:f eqn="sum @10 21600 0"/>
</v:formulas>
<v:path o:extrusionok="f" gradientshapeok="t" o:connecttype="rect"/>
<o:lock v:ext="edit" aspectratio="t"/>
</v:shapetype><v:shape id="Picture_x0020_4" o:spid="_x0000_i1026" type="#_x0000_t75"
style='width:125.25pt;height:20.25pt;visibility:visible;mso-wrap-style:square'>
<v:imagedata src="file:///C:\Users\efreeman\AppData\Local\Temp\msohtmlclip1\01\clip_image001.png"
o:title=""/>
</v:shape><![endif]--><!--[if !vml]--><!--[endif]--></span><br /></div>
<h2 style="margin: 10pt 0in 0pt;">
<span style="font-size: medium;"><span style="color: #4f81bd;"><span style="font-family: Cambria;">Turn off IntelliSense<o:p></o:p></span></span></span></h2>
<br />
<div class="MsoListParagraphCxSpFirst" style="margin: 0in 0in 0pt 0.5in; mso-list: l0 level1 lfo1; text-indent: -0.25in;">
<!--[if !supportLists]--><span style="font-family: Symbol; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol;"><span style="mso-list: Ignore;">·<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";">
</span></span></span><!--[endif]--><span style="font-family: Calibri;">From SSMS go to the Tools Menu, select Options<o:p></o:p></span></div>
<br />
<div class="MsoListParagraphCxSpMiddle" style="margin: 0in 0in 0pt 0.5in; mso-list: l0 level1 lfo1; text-indent: -0.25in;">
<!--[if !supportLists]--><span style="font-family: Symbol; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol;"><span style="mso-list: Ignore;">·<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";">
</span></span></span><!--[endif]--><span style="font-family: Calibri;">Under Text Editor, Transact-SQL, select
IntelliSense<o:p></o:p></span></div>
<br />
<div class="MsoListParagraphCxSpLast" style="margin: 0in 0in 10pt 0.5in; mso-list: l0 level1 lfo1; text-indent: -0.25in;">
<!--[if !supportLists]--><span style="font-family: Symbol; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol;"><span style="mso-list: Ignore;">·<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";">
</span></span></span><!--[endif]--><span style="font-family: Calibri;">If Enable IntelliSense is selected, clear the
checkbox and hit OK.<o:p></o:p></span></div>
<br />
<div class="separator" style="clear: both; text-align: left;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgjhyphenhyphenzLSpw53XzpyZ7QJw0k9khYQNg2lcAULYbpXrrWWk2XLbALC6WywC7Hpc3uLH1ZLbGuA0JWytCl6ea_gq1ZhdyRha6kVvF2ieK6YhMq4ki6puLyyJ3BRV5n2HB7FxRXR3zhNDcHHxVS/s1600/IntelliSense.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="185" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgjhyphenhyphenzLSpw53XzpyZ7QJw0k9khYQNg2lcAULYbpXrrWWk2XLbALC6WywC7Hpc3uLH1ZLbGuA0JWytCl6ea_gq1ZhdyRha6kVvF2ieK6YhMq4ki6puLyyJ3BRV5n2HB7FxRXR3zhNDcHHxVS/s320/IntelliSense.jpg" width="320" /></a></div>
<div class="MsoNormal" style="margin: 0in 0in 10pt;">
<span style="mso-no-proof: yes;"><!--[if gte vml 1]><v:shape
id="Picture_x0020_1" o:spid="_x0000_i1025" type="#_x0000_t75" style='width:468pt;
height:272.25pt;visibility:visible;mso-wrap-style:square'>
<v:imagedata src="file:///C:\Users\efreeman\AppData\Local\Temp\msohtmlclip1\01\clip_image003.png"
o:title=""/>
</v:shape><![endif]--><!--[if !vml]--><img height="363" src="file:///C:/Users/efreeman/AppData/Local/Temp/msohtmlclip1/01/clip_image004.jpg" v:shapes="Picture_x0020_1" width="624" /><!--[endif]--></span></div>
<o:p></o:p>
Eric Freemanhttp://www.blogger.com/profile/12705799218796920483noreply@blogger.com0tag:blogger.com,1999:blog-3693362201404617343.post-84949547239365778492016-02-23T08:00:00.000-08:002016-02-25T22:39:28.055-08:00<span style="mso-no-proof: yes;"><span style="font-size: large;"><span style="color: #365f91;"><span style="font-family: "cambria";">Using INTERSECT and EXCEPT to compare records</span></span></span></span><br />
<br />
<div class="MsoNormal" style="margin: 0in 0in 10pt;">
<span style="mso-no-proof: yes;"><span style="font-family: "calibri";">I was asked for a simple way to
compare two tables that should be identical and have the same schema. I
provided the following scripts to show records that are identical (Intersect) and different (Except):</span></span></div>
<code style="font-size: 12px;"><span style="color: black;"><br /></span><span style="color: blue;">IF </span><span style="color: magenta;">OBJECT_ID</span><span style="color: grey;">(</span><span style="color: red;">N'dbo.test1'</span><span style="color: grey;">) </span><span style="color: blue;">IS </span><span style="color: grey;">NOT NULL <br /> </span><span style="color: blue;">DROP TABLE </span><span style="color: black;">test1</span><span style="color: blue;"><br>CREATE TABLE </span><span style="color: black;">dbo.test1 </span><span style="color: grey;">(</span><span style="color: black;">name </span><span style="color: blue;">VARCHAR</span><span style="color: grey;">(</span><span style="color: black;">20</span><span style="color: grey;">), </span><span style="color: black;">dob </span><span style="color: blue;">DATETIME</span><span style="color: grey;">)</span><span style="color: blue;"><br><br>IF </span><span style="color: magenta;">OBJECT_ID</span><span style="color: grey;">(</span><span style="color: red;">N'dbo.test2'</span><span style="color: grey;">) </span><span style="color: blue;">IS </span><span style="color: grey;">NOT NULL <br /> </span><span style="color: blue;">DROP TABLE </span><span style="color: black;">test2</span><span style="color: blue;"><br>CREATE TABLE </span><span style="color: black;">dbo.test2 </span><span style="color: grey;">(</span><span style="color: black;">name </span><span style="color: blue;">VARCHAR</span><span style="color: grey;">(</span><span style="color: black;">20</span><span style="color: grey;">), </span><span style="color: black;">dob </span><span style="color: blue;">DATETIME</span><span style="color: grey;">)</span><span style="color: blue;"><br><br>INSERT INTO </span><span style="color: black;">test1 </span><span style="color: grey;">(</span><span style="color: black;">name</span><span style="color: grey;">, </span><span style="color: black;">dob</span><span style="color: grey;">) <br /> </span><span style="color: blue;">VALUES </span><span style="color: grey;">(</span><span style="color: red;">'Fred'</span><span style="color: grey;">,</span><span style="color: red;">'20010102'</span><span style="color: grey;">)</span><span style="color: blue;"><br>INSERT INTO </span><span style="color: black;">test1 </span><span style="color: grey;">(</span><span style="color: black;">name</span><span style="color: grey;">, </span><span style="color: black;">dob</span><span style="color: grey;">) <br /> </span><span style="color: blue;">VALUES </span><span style="color: grey;">(</span><span style="color: red;">'Fred1'</span><span style="color: grey;">,</span><span style="color: red;">'20010103'</span><span style="color: grey;">)</span><span style="color: blue;"><br>INSERT INTO </span><span style="color: black;">test1 </span><span style="color: grey;">(</span><span style="color: black;">name</span><span style="color: grey;">, </span><span style="color: black;">dob</span><span style="color: grey;">) <br /> </span><span style="color: blue;">VALUES </span><span style="color: grey;">(</span><span style="color: red;">'Fred2'</span><span style="color: grey;">,</span><span style="color: red;">'20010104'</span><span style="color: grey;">)</span><span style="color: blue;"><br>INSERT INTO </span><span style="color: black;">test1 </span><span style="color: grey;">(</span><span style="color: black;">name</span><span style="color: grey;">, </span><span style="color: black;">dob</span><span style="color: grey;">) <br /> </span><span style="color: blue;">VALUES </span><span style="color: grey;">(</span><span style="color: red;">'Fred3'</span><span style="color: grey;">,</span><span style="color: red;">'20010105'</span><span style="color: grey;">)</span><span style="color: blue;"><br>INSERT INTO </span><span style="color: black;">test1 </span><span style="color: grey;">(</span><span style="color: black;">name</span><span style="color: grey;">, </span><span style="color: black;">dob</span><span style="color: grey;">) <br /> </span><span style="color: blue;">VALUES </span><span style="color: grey;">(</span><span style="color: red;">'Fred4'</span><span style="color: grey;">,</span><span style="color: red;">'20010106'</span><span style="color: grey;">)</span><span style="color: blue;"><br>INSERT INTO </span><span style="color: black;">test1 </span><span style="color: grey;">(</span><span style="color: black;">name</span><span style="color: grey;">, </span><span style="color: black;">dob</span><span style="color: grey;">) <br /> </span><span style="color: blue;">VALUES </span><span style="color: grey;">(</span><span style="color: red;">'Fred5'</span><span style="color: grey;">,</span><span style="color: red;">'20010107'</span><span style="color: grey;">)</span><span style="color: blue;"><br>INSERT INTO </span><span style="color: black;">test2 </span><span style="color: grey;">(</span><span style="color: black;">name</span><span style="color: grey;">, </span><span style="color: black;">dob</span><span style="color: grey;">) <br /> </span><span style="color: blue;">VALUES </span><span style="color: grey;">(</span><span style="color: red;">'Fred'</span><span style="color: grey;">,</span><span style="color: red;">'20010102'</span><span style="color: grey;">)</span><span style="color: blue;"><br>INSERT INTO </span><span style="color: black;">test2 </span><span style="color: grey;">(</span><span style="color: black;">name</span><span style="color: grey;">, </span><span style="color: black;">dob</span><span style="color: grey;">) <br /> </span><span style="color: blue;">VALUES </span><span style="color: grey;">(</span><span style="color: red;">'Fred2'</span><span style="color: grey;">,</span><span style="color: red;">'20010104'</span><span style="color: grey;">)</span><span style="color: blue;"><br>INSERT INTO </span><span style="color: black;">test2 </span><span style="color: grey;">(</span><span style="color: black;">name</span><span style="color: grey;">, </span><span style="color: black;">dob</span><span style="color: grey;">) <br /> </span><span style="color: blue;">VALUES </span><span style="color: grey;">(</span><span style="color: red;">'Fred3'</span><span style="color: grey;">,</span><span style="color: red;">'20010105'</span><span style="color: grey;">)</span><span style="color: blue;"><br>INSERT INTO </span><span style="color: black;">test2 </span><span style="color: grey;">(</span><span style="color: black;">name</span><span style="color: grey;">, </span><span style="color: black;">dob</span><span style="color: grey;">) <br /> </span><span style="color: blue;">VALUES </span><span style="color: grey;">(</span><span style="color: red;">'Fred7'</span><span style="color: grey;">,</span><span style="color: red;">'20010106'</span><span style="color: grey;">)</span><span style="color: blue;"><br>INSERT INTO </span><span style="color: black;">test2 </span><span style="color: grey;">(</span><span style="color: black;">name</span><span style="color: grey;">, </span><span style="color: black;">dob</span><span style="color: grey;">) <br /> </span><span style="color: blue;">VALUES </span><span style="color: grey;">(</span><span style="color: red;">'Fred5'</span><span style="color: grey;">,</span><span style="color: red;">'20010107'</span><span style="color: grey;">)<br /> </span><span style="color: green;"><br>--exist and are the same in both tables test 1, not in test 2</span><span style="color: blue;"><br>SELECT </span><span style="color: black;">name</span><span style="color: grey;">, </span><span style="color: black;">dob <br /></span><span style="color: blue;">FROM </span><span style="color: black;">dbo.test1<br /></span><span style="color: blue;">INTERSECT<br>SELECT </span><span style="color: black;">name</span><span style="color: grey;">, </span><span style="color: black;">dob <br /></span><span style="color: blue;">FROM </span><span style="color: black;">dbo.test2<br /></span><span style="color: green;"><br>--exists in test 1, does not exist in test 2</span><span style="color: blue;"><br>SELECT </span><span style="color: black;">name</span><span style="color: grey;">, </span><span style="color: black;">dob <br /></span><span style="color: blue;">FROM </span><span style="color: black;">dbo.test1<br /></span><span style="color: blue;">EXCEPT<br>SELECT </span><span style="color: black;">name</span><span style="color: grey;">, </span><span style="color: black;">dob <br /></span><span style="color: blue;">FROM </span><span style="color: black;">dbo.test2<br /></span><span style="color: green;"><br>--exists in test 2, does not exist in test 1</span><span style="color: blue;"><br>SELECT </span><span style="color: black;">name</span><span style="color: grey;">, </span><span style="color: black;">dob <br /></span><span style="color: blue;">FROM </span><span style="color: black;">dbo.test2<br /></span><span style="color: blue;">EXCEPT<br>SELECT </span><span style="color: black;">name</span><span style="color: grey;">, </span><span style="color: black;">dob <br /></span><span style="color: blue;">FROM </span><span style="color: black;">dbo.test1<br /></span></code>
<BR><BR>
<code style="font-size: 12px;"><span style="font-size: small;"><span style="font-family: "calibri";">As you can see by running the script above, intersect shows
values from the first query where there are identical record matches in the
second query. Except shows values in the first query where there are No identical
record matches in the second query. Additional benefit is that it also includes
null values in the comparison, you do not get the same functionality with null
values on joins (when you just enter the column names) from 2 tables (without
adding specific null handling).<o:p></o:p></span></span></code>
<code style="font-size: 12px;">
<span style="font-family: "times new roman"; font-size: small;">
</span><br />
</code>
Eric Freemanhttp://www.blogger.com/profile/12705799218796920483noreply@blogger.com0tag:blogger.com,1999:blog-3693362201404617343.post-90471536814342674692013-09-10T13:43:00.001-07:002013-09-25T16:25:43.296-07:00CheckSum is pretty damn accurateI am at a SSIS Masters course with Jorge Segarra and Brian Knight (Pragmatic Works). An attendee brought up an issue about checksum not being 100% accurate, which we all acknowledge; however, another attendee said it is only 99% accurate when checking for differences. I know it's not even close to 1% inaccurate so I wrote a quick script to attempt find an inaccurate checksum value via brute force. I ran the script below for 15 minutes, checking over a quarter of a billion random values in which I found 0 invalid matches. If someone wants to run this longer and change the bigint to a numeric, let me know how many attempts until you find a record.<br />
<br />
Summary: checksum is pretty damn accurate (not 100%, but pretty close).<br />
<br />
<code style="font-size: 12px;"><span style="color:black"><br></span><span style="color:blue">DECLARE </span><span style="color:#434343">@value1a </span><span style="color:blue">VARCHAR</span><span style="color:gray">(</span><span style="color:black">36</span><span style="color:gray">)<br></span><span style="color:blue">DECLARE </span><span style="color:#434343">@value2a </span><span style="color:blue">VARCHAR</span><span style="color:gray">(</span><span style="color:black">36</span><span style="color:gray">)<br></span><span style="color:blue">DECLARE </span><span style="color:#434343">@value1b </span><span style="color:blue">VARCHAR</span><span style="color:gray">(</span><span style="color:black">36</span><span style="color:gray">)<br></span><span style="color:blue">DECLARE </span><span style="color:#434343">@value2b </span><span style="color:blue">VARCHAR</span><span style="color:gray">(</span><span style="color:black">36</span><span style="color:gray">)<br><br></span><span style="color:blue">DECLARE </span><span style="color:#434343">@attempt </span><span style="color:black">bigint<br></span><span style="color:blue">SET </span><span style="color:#434343">@attempt </span><span style="color:blue">= </span><span style="color:black">0<br><br><br> </span><span style="color:blue">SET </span><span style="color:#434343">@value1a </span><span style="color:blue">= </span><span style="color:magenta">NEWID</span><span style="color:gray">()<br> </span><span style="color:blue">SET </span><span style="color:#434343">@value2a </span><span style="color:blue">= </span><span style="color:magenta">NEWID</span><span style="color:gray">()<br> </span><span style="color:blue">SET </span><span style="color:#434343">@value1b </span><span style="color:blue">= </span><span style="color:magenta">NEWID</span><span style="color:gray">()<br> </span><span style="color:blue">SET </span><span style="color:#434343">@value2b </span><span style="color:blue">= </span><span style="color:magenta">NEWID</span><span style="color:gray">()<br><br></span><span style="color:blue">WHILE </span><span style="color:black">checksum</span><span style="color:gray">(</span><span style="color:#434343">@value1a</span><span style="color:gray">, </span><span style="color:#434343">@value2a</span><span style="color:gray">) <> </span><span style="color:black">checksum</span><span style="color:gray">(</span><span style="color:#434343">@value1b</span><span style="color:gray">, </span><span style="color:#434343">@value2b</span><span style="color:gray">)<br></span><span style="color:blue">BEGIN<br> SET </span><span style="color:#434343">@attempt </span><span style="color:blue">= </span><span style="color:#434343">@attempt </span><span style="color:gray">+ </span><span style="color:black">1<br> </span><span style="color:blue">SET </span><span style="color:#434343">@value1a </span><span style="color:blue">= </span><span style="color:magenta">NEWID</span><span style="color:gray">()<br> </span><span style="color:blue">SET </span><span style="color:#434343">@value2a </span><span style="color:blue">= </span><span style="color:magenta">NEWID</span><span style="color:gray">()<br> </span><span style="color:blue">SET </span><span style="color:#434343">@value1b </span><span style="color:blue">= </span><span style="color:magenta">NEWID</span><span style="color:gray">()<br> </span><span style="color:blue">SET </span><span style="color:#434343">@value2b </span><span style="color:blue">= </span><span style="color:magenta">NEWID</span><span style="color:gray">()<br> </span><span style="color:blue">IF </span><span style="color:#434343">@attempt</span><span style="color:gray">%</span><span style="color:black">100000 </span><span style="color:blue">= </span><span style="color:black">0 </span><span style="color:blue">PRINT </span><span style="color:#434343">@attempt<br></span><span style="color:blue">END<br><br>SELECT </span><span style="color:#434343">@attempt<br></span><span style="color:blue">SELECT </span><span style="color:#434343">@value1a </span><span style="color:black">value1a</span><span style="color:gray">, </span><span style="color:#434343">@value2a </span><span style="color:black">value2a</span><span style="color:gray">, </span><span style="color:black">checksum</span><span style="color:gray">(</span><span style="color:#434343">@value1a</span><span style="color:gray">, </span><span style="color:#434343">@value2a</span><span style="color:gray">) </span><span style="color:black">chk_a<br></span><span style="color:blue">SELECT </span><span style="color:#434343">@value1b </span><span style="color:black">value2a</span><span style="color:gray">, </span><span style="color:#434343">@value2b </span><span style="color:black">value2b</span><span style="color:gray">, </span><span style="color:black">checksum</span><span style="color:gray">(</span><span style="color:#434343">@value1b</span><span style="color:gray">, </span><span style="color:#434343">@value2b</span><span style="color:gray">) </span><span style="color:black">chk_b<br><br></span></code>
Eric Freemanhttp://www.blogger.com/profile/12705799218796920483noreply@blogger.com0tag:blogger.com,1999:blog-3693362201404617343.post-83914340362576894642013-01-17T16:55:00.001-08:002013-01-17T16:55:30.579-08:00Make a SQL Server Shortcut to Change Database ConnectionsIn SSMS, to change databases most people do one of the following:<br />
1. Click the Change Connection toolbar button<br />
2. Right click anywhere in the query pane -> Connection -> Change Connection<br />
3. Go to the query menu -> Connection -> Change Connection (Alt+Q, C, H)<br />
<br />
The change connection menu button actually does have a shortcut key predefined (Alt+H); however this action has a lower priority than the (Alt+H) shortcut for the Help Menu. Thanks Microsoft! I'll show you how to enable this the Change Connection action using (Alt+G).<br />
<br />
The work around is actually quite simple, but it is not very intuitive.<br />
<ul>
<li>Right click anywhere on the toolbar and select Customize</li>
<li>Ignore (but don't close this yet) the Customize window that pops up</li>
<li>Right Click on the Change Connection toolbar button and select "Image & Text"</li>
<ul>
<li>This enables the hotkey to be use inside the query window but you can not activate it yet because Help Menu shortcut (Alt + H) still has precedence.</li>
</ul>
<li>Right Click on the Change Connection toolbar button again</li>
<li>Change the Name field from "C&hange Connection..." to "Chan&ge Connection..."</li>
<li>Close the Customize window that popped up earlier.</li>
</ul>
Now you can use the Alt + G keyboard shortcut to change connections.Eric Freemanhttp://www.blogger.com/profile/12705799218796920483noreply@blogger.com0tag:blogger.com,1999:blog-3693362201404617343.post-22144796423550965172012-12-21T16:13:00.001-08:002012-12-21T16:15:00.498-08:00Converting from Varchar with Nonbreaking Spaces to Datetime<div class="MsoNormal" style="margin: 0in 0in 10pt;">
<span style="font-family: Calibri;">Converting varchar values to datetime with nonbreaking spaces</span></div>
<div class="MsoNormal" style="margin: 0in 0in 10pt;">
<span style="font-family: Calibri;">I was tasked with importing a csv from into SQL Server. The CSV included 40 date fields which included values that could not be converted to datetime (eg. 12-Dec, 2011, and 23 Jan 2012). I’ll explain why 23 Jan 2012 could not be converted later on. I wanted to do this quickly so I used the SSMS import wizard, included headers and imported the date values into a varchar column.</span></div>
<div class="MsoNormal" style="margin: 0in 0in 10pt;">
<span style="font-family: Calibri;">After converting the values I started looking into which values could not be converted using the following query:</span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<code style="font-size: 12px;"><span style="color: black;"><br /></span><span style="color: blue;"><span style="color: blue; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;">SELECT</span><span style="font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;"><span style="color: black;"> </span><span style="color: grey;">*</span><span style="color: black;"> <o:p></o:p></span></span></span></code><br />
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;">FROM</span><span style="font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;"><span style="color: black;"> myTable<o:p></o:p></span></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;">WHERE</span><span style="font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;"><span style="color: black;"> </span><span style="color: magenta;">ISDATE</span><span style="color: grey;">(</span><span style="color: black;">myDateField1</span><span style="color: grey;">)</span><span style="color: black;"> </span><span style="color: grey;">=</span><span style="color: black;"> 0<o:p></o:p></span></span></div>
</div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="font-family: Calibri;">I repeated this for all of the fields until I resolved all issues. The first issue I ran into was:</span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<br /></div>
<div class="MsoNormal" style="margin: 0in 0in 10pt;">
<span style="color: red; font-family: 'Courier New'; font-size: 8pt; line-height: 115%;">Msg 241, Level 16, State 1, Line 1<o:p></o:p></span></div>
<div class="MsoNormal" style="margin: 0in 0in 10pt;">
<span style="color: red; font-family: 'Courier New'; font-size: 8pt; line-height: 115%;">Conversion failed when converting date and/or time from character string.</span><span style="color: red; font-family: 'Courier New'; font-size: 10pt; line-height: 115%;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin: 0in 0in 10pt;">
<span style="font-family: Calibri;">I isolated this down to values that included 23 Jan 2012. I could not see at first why this was causing a conversion failure. After further research I found that the first space (between 23 and Jan) was actually a nonbreaking space which used ascii code 160 instead of an ASCII space which is ascii code 32. To resolve this I used the following replace command before converting:</span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<code style="font-size: 12px;"><span style="color: black;"><span style="color: blue; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;">SELECT</span><span style="color: magenta; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;"> CONVERT</span><span style="color: grey; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;">(</span><span style="color: blue; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;">datetime</span><span style="color: grey; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;">,</span><span style="color: magenta; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;">REPLACE</span><span style="color: grey; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;">(</span><span style="font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;">myTable.myDateField1<span style="color: grey;">,</span><span style="color: blue;">char</span><span style="color: grey;">(</span>160<span style="color: grey;">),</span><span style="color: red;">' '</span><span style="color: grey;">))</span><span style="color: blue;"> as</span> myDateField1<span style="color: blue;"><o:p></o:p></span></span></span></code><br />
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;">FROM </span><span style="font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;">myTable<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;">WHERE</span><span style="font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;"> <span style="color: magenta;">ISDATE</span><span style="color: grey;">(</span>myDateField1<span style="color: grey;">)</span> <span style="color: grey;">=</span> 0<o:p></o:p></span></div>
</div>
<br />
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="font-family: Calibri;">The other issues were resolved by applying a case statement to myTable.myDateFields, evaluating the length of the value then appending the appropriate values to make the value submitted a valid date. Eg. if the length was <5 it would apply <span style="color: magenta; mso-bidi-font-family: 'Courier New'; mso-no-proof: yes;">len</span><span style="color: grey; mso-bidi-font-family: 'Courier New'; mso-no-proof: yes;">(</span><span style="mso-bidi-font-family: 'Courier New'; mso-no-proof: yes;">myDateField1<span style="color: grey;">)</span> <span style="color: grey;"><</span> 5 <span style="color: blue;">then</span> <span style="color: red;">'1-1-'</span><span style="color: grey;">+</span>myDateField1.<o:p></o:p></span></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="mso-bidi-font-family: 'Courier New'; mso-no-proof: yes;"><span style="font-family: Calibri;">Next I needed to apply the changes to all of the date fields. Since all of the date fields had the same issues as the date field I evaluated I simply apply the same convert/replace/case satement to every date field.<o:p></o:p></span></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="mso-bidi-font-family: 'Courier New'; mso-no-proof: yes;"><span style="font-family: Calibri;">I created a quick recording of the key commands I needed to repeat and ran the following macro 40 times. After that I tested the script and was done!<o:p></o:p></span></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="mso-bidi-font-family: 'Courier New'; mso-no-proof: yes;"><span style="font-family: Calibri;">Here is the Macro I used:<o:p></o:p></span></span></div>
</div>
<div class="separator" style="border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none; clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiXFmc6O6oTvDatA8LmLoUj8m4tp4XJke5TDKIWvb3Nvh0HjznsVHGoKyQFj7EvFyVZplVMn2IAiUQ5JZCfvFMto8n5TUWVzz5-V3Ce4zEtQRCUalaQ1dnvYED28NrwGLJkeXPXZeOOy0Vi/s1600/untitled.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" eea="true" height="306" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiXFmc6O6oTvDatA8LmLoUj8m4tp4XJke5TDKIWvb3Nvh0HjznsVHGoKyQFj7EvFyVZplVMn2IAiUQ5JZCfvFMto8n5TUWVzz5-V3Ce4zEtQRCUalaQ1dnvYED28NrwGLJkeXPXZeOOy0Vi/s400/untitled.JPG" width="400" /></a></div>
<div align="left" class="separator" style="border-bottom: medium none; border-left: medium none; border-right: medium none; border-top: medium none; clear: both; text-align: center;">
</div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="mso-bidi-font-family: 'Courier New'; mso-no-proof: yes;"><span style="font-family: Calibri;">In the macro edit screen, the Prompt User “Times to Repeat” asks me how many times I want to repeat/playback a recording durring runtime and loads it into a variable named NumRepeats. I set the playback to run at 1500% the actual speed of the recording.</span></span></div>
Eric Freemanhttp://www.blogger.com/profile/12705799218796920483noreply@blogger.com0tag:blogger.com,1999:blog-3693362201404617343.post-79003832697722757122011-08-11T17:03:00.002-07:002012-02-28T13:54:03.652-08:00Search Columns, Stored Procedures, or Tables using Dynamic SQLI wanted to share the script I created to help Analysts and DBA's easily identify where a particular column, table, or keyword is used within a particular instance.<br /><br /><code style="font-size: 12px;"><span style="color:black"><br></span><span style="color:blue">DECLARE </span><span style="color:#434343">@Dynamic_SQL </span><span style="color:blue">VARCHAR</span><span style="color:gray">(</span><span style="color:magenta">MAX</span><span style="color:gray">) <br></span><span style="color:blue">DECLARE </span><span style="color:#434343">@SSQL </span><span style="color:blue">VARCHAR</span><span style="color:gray">(</span><span style="color:magenta">MAX</span><span style="color:gray">) <br></span><span style="color:blue">DECLARE </span><span style="color:#434343">@DB_NAME </span><span style="color:blue">VARCHAR</span><span style="color:gray">(</span><span style="color:black">256</span><span style="color:gray">) <br></span><span style="color:blue">DECLARE </span><span style="color:#434343">@SearchParam </span><span style="color:blue">VARCHAR</span><span style="color:gray">(</span><span style="color:black">256</span><span style="color:gray">) <br></span><span style="color:blue">SET </span><span style="color:#434343">@SearchParam </span><span style="color:blue">= </span><span style="color:red">'ENTER KEYWORD HERE' <br><br></span><span style="color:green">--REMOVE COMMENT FROM ONE OBJECT YOU WANT TO SEARCH <br>--Table Name Search <br>--set @Dynamic_SQL = 'select ''[dbname]'' database_name, name, null [col] from [dbname].dbo.sysobjects where xtype=''u'' and name like ''%'+@SearchParam+'%''' <br><br>--Stored Procedure Dependency Search <br>--set @Dynamic_SQL = 'use [dbname];select ''[dbname]'' database_name, Name, null [col] FROM sys.procedures WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE ''%'+@SearchParam+'%''' <br><br>--Column Name Search <br>--set @Dynamic_SQL = 'select ''[dbname]'' database_name, so.name, sc.name [col] from [dbname].dbo.sysobjects so inner join [dbname].dbo.syscolumns sc on so.id = sc.id where so.xtype=''u'' and so.name like ''%%'' and sc.name like ''%'+@SearchParam+'%''' <br><br></span><span style="color:blue">CREATE TABLE </span><span style="color:#434343">#Results </span><span style="color:gray">(</span><span style="color:black">dbname </span><span style="color:blue">VARCHAR</span><span style="color:gray">(</span><span style="color:black">256</span><span style="color:gray">), </span><span style="color:black">[name] </span><span style="color:blue">VARCHAR</span><span style="color:gray">(</span><span style="color:magenta">MAX</span><span style="color:gray">), </span><span style="color:black">col </span><span style="color:blue">VARCHAR</span><span style="color:gray">(</span><span style="color:magenta">MAX</span><span style="color:gray">)) <br><br></span><span style="color:green">--GET DB LIST <br></span><span style="color:blue">SELECT </span><span style="color:black">[name] <br></span><span style="color:blue">INTO </span><span style="color:#434343">#DBNAME <br></span><span style="color:blue">FROM MASTER</span><span style="color:black">.dbo.sysdatabases <br></span><span style="color:blue">WHERE </span><span style="color:black">[name] </span><span style="color:gray">NOT </span><span style="color:blue">IN</span><span style="color:gray">(</span><span style="color:red">'master'</span><span style="color:gray">,</span><span style="color:red">'tempdb'</span><span style="color:gray">,</span><span style="color:red">'model'</span><span style="color:gray">,</span><span style="color:red">'msdb'</span><span style="color:gray">)<br><br></span><span style="color:blue">DECLARE </span><span style="color:black">Cur_DB </span><span style="color:blue">CURSOR FOR SELECT </span><span style="color:black">[name] </span><span style="color:blue">FROM </span><span style="color:#434343">#DBNAME <br></span><span style="color:blue">OPEN </span><span style="color:black">Cur_DB <br></span><span style="color:blue">FETCH </span><span style="color:black">Next </span><span style="color:blue">FROM </span><span style="color:black">Cur_DB </span><span style="color:blue">INTO </span><span style="color:#434343">@DB_NAME <br><br></span><span style="color:blue">WHILE </span><span style="color:#434343">@@FETCH_STATUS </span><span style="color:blue">= </span><span style="color:black">0 </span><span style="color:blue">BEGIN <br> SET </span><span style="color:#434343">@SSQL </span><span style="color:blue">= </span><span style="color:magenta">REPLACE</span><span style="color:gray">(</span><span style="color:#434343">@Dynamic_SQL</span><span style="color:gray">,</span><span style="color:red">'dbname'</span><span style="color:gray">,</span><span style="color:#434343">@DB_Name</span><span style="color:gray">) <br> </span><span style="color:green">--print @SSQL <br> </span><span style="color:blue">BEGIN </span><span style="color:black">TRY <br> </span><span style="color:blue">INSERT INTO </span><span style="color:#434343">#Results </span><span style="color:gray">(</span><span style="color:black">dbname</span><span style="color:gray">, </span><span style="color:black">[name]</span><span style="color:gray">, </span><span style="color:black">[col]</span><span style="color:gray">) <br> </span><span style="color:blue">EXEC</span><span style="color:gray">(</span><span style="color:#434343">@SSQL</span><span style="color:gray">) <br> </span><span style="color:blue">END </span><span style="color:black">TRY <br> </span><span style="color:blue">BEGIN </span><span style="color:black">CATCH<br> </span><span style="color:blue">IF </span><span style="color:#434343">@@TRANCOUNT </span><span style="color:gray">> </span><span style="color:black">0<br> </span><span style="color:blue">ROLLBACK<br> END </span><span style="color:black">CATCH <br><br> </span><span style="color:blue">FETCH </span><span style="color:black">Next </span><span style="color:blue">FROM </span><span style="color:black">Cur_DB </span><span style="color:blue">INTO </span><span style="color:#434343">@DB_NAME <br><br></span><span style="color:blue">END <br>CLOSE </span><span style="color:black">Cur_DB <br></span><span style="color:blue">DEALLOCATE </span><span style="color:black">Cur_DB <br></span><span style="color:blue">DROP TABLE </span><span style="color:#434343">#DBNAME <br><br></span><span style="color:blue">SELECT </span><span style="color:black">dbname</span><span style="color:gray">, </span><span style="color:black">[name]</span><span style="color:gray">, </span><span style="color:black">col </span><span style="color:blue">FROM </span><span style="color:#434343">#Results <br></span><span style="color:blue">ORDER BY </span><span style="color:black">dbname</span><span style="color:gray">, </span><span style="color:black">[name]</span><span style="color:gray">, </span><span style="color:black">col <br><br></span><span style="color:blue">DROP TABLE </span><span style="color:#434343">#Results <br></span></code>Eric Freemanhttp://www.blogger.com/profile/12705799218796920483noreply@blogger.com0tag:blogger.com,1999:blog-3693362201404617343.post-10018495688523954092011-07-07T11:13:00.000-07:002013-09-25T16:27:27.393-07:00Creating a simple T-SQL queryFor people starting to learn how to write T-SQL queries the first thing you need to know is how to perform a simple query. A query is a way to get information back from a table in a database.<br /><br />A table should represent a single entity, for example you may have a person table or a product table. We will focus on the person table for this example. A person may have multiple attributes (first name, last name, birth date, sex). These attributes are defined as columns/fields in a table (similar to a row header in a spreadsheet).<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh3-1Io96ry4QJnrLtS7IJo1ek6RjOiNuzUENaWnjPfvBw8O-43uHPrB-tseVQ6HntWwbw9RoqaC5j6fsRD00mA4LPbefBwUD1bNRCmQXdRQTZRJuTG07mmqk4X8RSsI7ElSxh8rAzGCtbk/s1600/excel_person.bmp"><img style="WIDTH: 274px; HEIGHT: 106px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5626703310983763538" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh3-1Io96ry4QJnrLtS7IJo1ek6RjOiNuzUENaWnjPfvBw8O-43uHPrB-tseVQ6HntWwbw9RoqaC5j6fsRD00mA4LPbefBwUD1bNRCmQXdRQTZRJuTG07mmqk4X8RSsI7ElSxh8rAzGCtbk/s320/excel_person.bmp" /></a><br /><br />The first part of the query is the select statement. You will use the select statement to define what information you want to see pulled from the table. Each field needs to be separated by a comma. If we are only interested in seeing the first name and last name we would write the following.<br /><p style="mso-layout-grid-align: none" class="MsoNormal"><span style="mso-no-proof: yes;font-family:'Courier New';color:blue;" >select</span><span style="mso-no-proof: yes;font-family:'Courier New';" > first_name<span style="color:gray;">,</span> last_name<?xml:namespace prefix = o /></span></p><br />The statement above will fail to execute because the query is not complete. We have not defined where we wanted to get this information from. For this example table is named "person". In order to declare where we want to get the data from, we use the "from" statement after the list of columns as follows:<br /><p style="mso-layout-grid-align: none" class="MsoNormal"><span style="mso-no-proof: yes;font-family:'Courier New';color:blue;" >select</span><span style="mso-no-proof: yes;font-family:'Courier New';" > first_name<span style="color:gray;">,</span> last_name</span><br /><span style="mso-no-proof: yes;font-family:'Courier New';color:blue;" >from</span><span style="mso-no-proof: yes;font-family:'Courier New';" > person</span></p><br />That is pretty much the simplest a query will get.Eric Freemanhttp://www.blogger.com/profile/12705799218796920483noreply@blogger.com0