Search results matching tags 'SQL Server 2008' and 'scripting'http://sqlblog.com/search/SearchResults.aspx?o=DateDescending&tag=SQL+Server+2008,scripting&orTags=0Search results matching tags 'SQL Server 2008' and 'scripting'en-USCommunityServer 2.1 SP2 (Build: 61129.1)Database Mail … and then the SMTP Server changedhttp://sqlblog.com/blogs/hugo_kornelis/archive/2014/07/18/database-mail-and-then-the-smtp-server-changed.aspxFri, 18 Jul 2014 20:30:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:54574Hugo Kornelis
<p>The database I inherited when I started my current job sends out lots of mails. It does so using Database Mail, and it has lots of mail accounts and mail profiles defined. I know that several of those profiles are used, I suspect that some are not, and I have no idea about the rest – one day I will find the time to clean up, but so far there have always been more pressing matters to attend to.</p>
<p>But today the mail administrator told me that due to a change in architecture, SQL Server had to start using a different SMTP server for sending mails. Quite an easy task if you have just a single profile – just a few clicks in the Database Mail Configuration Wizard, and done. But repeating those same mouse-clicks for every profile in the list was not my idea of a morning well spent, so I decided that I’d have to script this. (This should be easy – we have just a single SMTP server, so I could hit every single mail account and did not have to bother with exceptions).</p>
<p>Usually, scripts for such a task are very easy – just type a well-chosen search string in your favorite search engine, check the first two or three hits, and you’ll have a script. Usually even more than one. Carefully inspect the script (just because it’s on the internet does not mean it’s safe!), copy the script, paste into SSMS, make adjustments for your own situation, do one more inspection just to be sure – and then hit the F5 button and say “time to grab a coff … oh wait, it’s already done”.</p>
<p>In this case I had no luck. Maybe I used the wrong search phrase, or maybe there is a way to accomplish this that is so easy that nobody ever bother blogging about it and I am the only one who managed to overlook the option. Or maybe nobody has ever tried to automate the task of changing SMTP servers.</p>
<p>Bottom line, I did not find a pre-made script for this task, so I put in the effort to write one, and then decided to share it with you.</p>
<p>Note that the script below was tested on SQL Server 2008R2 only. Also note that it will update all mail accounts to use the new SMTP server. If you have a more complex setup with multiple servers and only some need to change, you will have to add the correct filtering criteria to the WHERE clause.</p>
<p><code>DECLARE @NewServer sysname = 'NotTelling.mail', -- New SMTP server<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; @OldServer sysname = 'MySecret.mail',&nbsp;&nbsp; -- Old SMTP server<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; @account_id int;<br><br>DECLARE Cursor_MailAccounts CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY<br>FOR SELECT account_id<br>&nbsp;&nbsp;&nbsp; FROM&nbsp;&nbsp; msdb.dbo.sysmail_server<br>&nbsp;&nbsp;&nbsp; WHERE&nbsp; servername = @OldServer;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- Add extra logic here<br><br>OPEN Cursor_MailAccounts;<br><br>FETCH NEXT<br>FROM&nbsp; Cursor_MailAccounts<br>INTO&nbsp; @account_id;<br><br>WHILE @@FETCH_STATUS = 0<br>BEGIN;<br>&nbsp;&nbsp;&nbsp; EXECUTE msdb.dbo.sysmail_update_account_sp<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; @account_id = @account_id,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; @mailserver_name = @NewServer;<br>&nbsp;&nbsp;&nbsp; <br>&nbsp;&nbsp;&nbsp; FETCH NEXT<br>&nbsp;&nbsp;&nbsp; FROM&nbsp; Cursor_MailAccounts<br>&nbsp;&nbsp;&nbsp; INTO&nbsp; @account_id;<br>END;<br><br>CLOSE Cursor_MailAccounts;<br>DEALLOCATE Cursor_MailAccounts;<br></code><font size="1">(And remember, just because you found it on the internet doesn’t mean it’s safe!)</font></p>
<p>With that done, my next task was to fix the mails being sent from SSIS packages. They use an SMTP connection that is defined in the package, not SQL Server’s own Database Mail, so I had to open and edit them by hand. Luckily, I have only four packages active, and only three of them have a Send Mail task, so I did not see any need to automate this.</p>Parent-Child Build Scripts with SQLCMDhttp://sqlblog.com/blogs/michael_coles/archive/2010/01/10/parent-child-build-scripts-with-sqlcmd.aspxSun, 10 Jan 2010 18:33:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:20821Mike C<P style="MARGIN:0in 0in 10pt;" class=MsoNormal><FONT size=3 face=Calibri>On the <A href="http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/7837d5428e6c83fd?hl=en#">SQL Server public programming newsgroup</A> someone recently posted a question about an SSMS error (<A href="https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=269566#details">"Cannot parse script. 'System.OutOfMemoryException' thrown."</A>) I hadn’t encountered this error myself, but the workaround is to break up very large scripts (50+ MB) into smaller scripts. Adam Machanic posted a T-SQL Tuesday challenge to post <A href="http://sqlblog.com/blogs/adam_machanic/archive/2010/01/04/invitation-for-t-sql-tuesday-002-a-puzzling-situation.aspx">a solution to a puzzling situation</A>, so this actually gives me a good opportunity to share how I structure my own build scripts -- which avoids this issue entirely.</FONT></P>
<P style="MARGIN:0in 0in 10pt;" class=MsoNormal><FONT size=3 face=Calibri>When I create database build scripts, I use the&nbsp;<A href="http://msdn.microsoft.com/en-us/library/ms162773.aspx">SQLCMD</A> utility to run them from the command line instead of using SSMS or another tool. SQLCMD has its own <A href="http://msdn.microsoft.com/en-us/library/ms162773.aspx#sectionToggle3">commands</A>, which it parses separately from SQL/T-SQL statements. These commands are not understood by SQL Server or other scripting tools like SSMS (<I style="mso-bidi-font-style:normal;">exception: you can run SSMS in <A href="http://msdn.microsoft.com/en-us/library/ms174187.aspx">SQLCMD mode</A>, but that’s another story</I>). These special SQLCMD commands all start with a ":" at the front of the line.</FONT></P>
<P style="MARGIN:0in 0in 10pt;" class=MsoNormal><FONT size=3 face=Calibri>The SQLCMD command that makes parent-child structured build-scripts possible is the ":r" or "run" command, which tells SQLCMD to run another script file from within the current script file. In the figure below I’ve set up a local directory structure with database object creation scripts in subdirectories:</FONT></P>
<P style="MARGIN:0in 0in 10pt;" class=MsoNormal><IMG style="WIDTH:367px;HEIGHT:544px;" src="http://e60ybw.bay.livefilestore.com/y1pbBwOsoJdF21J9eW0lf7zCk782rocpyFX5YFOkwiggop15Lzj9HOBHrhOEoj0jRq7wdUr8BaYFFcpRou_irLDIAXzS_bY7al3/sqlcmd-folders.png" width=367 height=544></P>
<P style="MARGIN:0in 0in 10pt;" class=MsoNormal><SPAN style="mso-no-proof:yes;"></SPAN></P>
<P style="MARGIN:0in 0in 10pt;" class=MsoNormal><FONT size=3 face=Calibri>The <EM>\Scripts</EM> directory contains a <EM>Create.All.Sql</EM> script. This script uses the SQLCMD <EM>run</EM> command to execute the <EM>Database\Create.Database.Sql</EM> script, the <EM>Create.All.Schemas.Sql</EM> script, and so on.<SPAN style="mso-spacerun:yes;">&nbsp; </SPAN>The <EM>Create.All.Schemas.Sql</EM> script calls the <EM>Person.Schema.Sql</EM> and <EM>Sales.Schema.Sql</EM> scripts in turn. The other <EM>Create.All.*</EM> scripts each call the object creation scripts in their subdirectories as well. Here’s what my Create.All.Sql script looks like:</FONT></P><PRE>/*<BR>&nbsp;&nbsp;&nbsp;&nbsp;Create All Items<BR>*/<BR><BR>
:r Database\Create.Database.sql<BR>:r Schemas\Create.All.Schemas.sql<BR>:r Types\Create.All.Types.sql<BR>:r Tables\Create.All.Tables.sql<FONT size=3 face=Calibri>&nbsp;</FONT></PRE>
<P style="MARGIN:0in 0in 10pt;" class=MsoNormal><FONT size=3 face=Calibri>Each <EM>:r</EM> command kicks off the next level of child packages in turn.</FONT></P>
<P style="MARGIN:0in 0in 10pt;" class=MsoNormal><FONT size=3 face=Calibri>SQLCMD has another great feature known as scripting variables that you can use to create dynamic scripts. Essentially you define a scripting variable on the command line with SQLCMD's <EM>-v</EM> command line option. Now the way scripting variables work, they are replaced wholesale in your scripts with their replacement value. So if you define a scripting variable named <EM>environment</EM> you can replace it with a value like "Dev", "QA" or "Prod" anywhere it occurs in your script. This is great for making dynamic scripts that need to be built across multiple environments.</FONT></P>
<P style="MARGIN:0in 0in 10pt;" class=MsoNormal><FONT size=3 face=Calibri>In the example I've used a scripting variable named <EM>database</EM>. You can set the value of the <EM>database</EM> variable from the command line with the <EM>-v</EM> option. In the example below I set the <EM>database</EM> variable to the value "Test".</FONT></P>
<P style="MARGIN:0in 0in 10pt;" class=MsoNormal><FONT size=3 face=Calibri><IMG style="WIDTH:843px;HEIGHT:187px;" title="SQLCMD Sample Command Line" alt="SQLCMD Sample Command Line" src="http://e60ybw.bay.livefilestore.com/y1py7Y3IXJHMFCeZDgZtZNWmVfQwxljC67X_AsNqD8JsJ1OM2OCxuApxSfz5V1Ze44963nb3_tZV4GUah4-4Dj0disbCrc5c7FD/Set-Scripting-Variable.png" width=843 height=187></FONT></P>
<P style="MARGIN:0in 0in 10pt;" class=MsoNormal><SPAN style="mso-no-proof:yes;"></SPAN></P>
<P style="MARGIN:0in 0in 10pt;" class=MsoNormal><FONT size=3 face=Calibri>The nice thing about the SQLCMD scripting variables is that once you declare them you can access them from the parent script you run (in this case <EM>Create.All.Sql</EM>) or from any child scripts that are run (like <EM>Create.Database.Sql</EM>, <EM>Create.All.Schemas.Sql</EM>, <EM>Person.Schema.Sql</EM> and <EM>Sales.Schema.Sql</EM>). Here’s the <EM>Create.Database.Sql</EM> script from the example:</FONT></P><PRE>/*<BR><BR>&nbsp;Create database<BR><BR>*/<BR><BR>USE master;<BR>GO<BR><BR>
CREATE DATABASE <STRONG><U>$(database)</U></STRONG>;<BR>GO<FONT size=3 face=Calibri>&nbsp;</FONT></PRE>
<P style="MARGIN:0in 0in 10pt;" class=MsoNormal><FONT size=3 face=Calibri>The scripting variable is accessed in the script with <EM>$(database)</EM>. The scripting variable is replaced with its value by SQLCMD, so in the example SQL Server sees this:</FONT></P><PRE>/*<BR><BR>&nbsp;Create database<BR><BR>*/<BR><BR>USE master;<BR>GO<BR><BR>
CREATE DATABASE <STRONG><U>Test</U></STRONG>;<BR>GO<FONT size=3 face=Calibri>&nbsp;</FONT></PRE>
<P style="MARGIN:0in 0in 10pt;" class=MsoNormal><FONT size=3 face=Calibri>One thing to keep in mind when you use this scripting pattern is that every script should end with the batch terminator (default is "GO").&nbsp; If not you could end up with one script running into another and get some strange, not-very-helpful&nbsp;error messages.</FONT></P>
<P style="MARGIN:0in 0in 10pt;" class=MsoNormal><FONT size=3 face=Calibri>Another thing you need to know is that scripting variables are replaced <EM>wholesale</EM> with their replacement text. This makes&nbsp;them very flexible, since you can replace text anywhere in the script with anything you want. It can also be dangerous if your script is run by someone with malicious intent. A malicious user can replace a scripting variable with T-SQL statements that could damage your data or database structure; so keep your scripting variable-enabled scripts out of the hands of potentially malicious users.</FONT></P>
<P style="MARGIN:0in 0in 10pt;" class=MsoNormal><FONT size=3 face=Calibri>I've attached a sample ZIP file with the directory structure shown in the example above. The scripts build out a few database objects from the AdventureWorks sample database.</FONT></P>Connect Digest : 2009-08-22http://sqlblog.com/blogs/aaron_bertrand/archive/2009/08/21/connect-digest-2009-08-22.aspxSat, 22 Aug 2009 03:18:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:16113AaronBertrand<p>I rounded up a few interesting items this week.
</p>
<hr style="height:1px;">
<p><b>Does "1/10/1900" mean January 10th, or October 1st?</b> <br></p>
<p>This item demonstrates that Microsoft isn't all that concerned about publishing sample code that uses ambiguous or troublesome formats for date literals.&nbsp; Initially the Books Online topics mentioned in the item used "1/10/1900" as a date literal, and of course that would have a different meaning in a default US English locale compared with a British locale.&nbsp; The alleged 'fix' was to change the string to "January 1, 1900."&nbsp; Which will simply fail if the user has, for example, SET LANGUAGE FRENCH.&nbsp; It seems funny that they could make a change for the better within a few days, but then making another change (also for the better) cannot be done due to costs.&nbsp; Whereas if they had just changed "1/10/1900" to "19000101" in the first place, the problem would have been solved in one step.&nbsp; What a weird organization they have over there; I personally think that the people writing their documentation samples should be assigned random locales and SET LANGUAGE settings so that they aren't writing code in a nice little US English vacuum.&nbsp; I can imagine a scenario where you have a bank of servers to deploy to, and you have to test on one of them, but you never know which one you're going to get... when your code blows up because the German server with SET LANGUAGE DUTCH doesn't know what "February" means, you have to go back to the drawing board.&nbsp; And I think this would be beneficial for everyone.<br></p>
<div style="margin-left:40px;" class="SubTitle"><a href="http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=482186" title="http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=482186" target="_blank">#482186 : DOC : Add/Subtract topics should warn about DATE / TIME types</a><br>&nbsp;</div>
<hr style="height:1px;">
<p><b>Why can't we have an easier way to get first day of month, of year, etc.?</b></p>
<p>We currently use clumsy methods to get the first day of the week, month or year (given a DATETIME/SMALLDATETIME value), or to strip the time.&nbsp; While the DATE data type in SQL Server 2008 will relieve some of this pain, it won't help in all cases.&nbsp; Meanwhile, other RDBMS have much more convenient ways to do this (e.g. Oracle's TRUNC()).&nbsp; I am hopeful that someday they will add a function like this to SQL Server; I call it "DATEROUND."</p>
<blockquote><a href="http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=483913" title="http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=483913" target="_blank" id="ctl00_MasterBody_PostedByUserView_ctl01_FeedbackSummaryDisplay_FeedbackLink">#483913 : Add a DATEROUND
function similar to Oracle's TRUNC() for date handling</a><br><br></blockquote>
<p><a href="http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=482186" title="http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=482186" target="_blank"></a>
</p>
<hr style="height:1px;">
<a href="http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=482186" title="http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=482186" target="_blank"></a>
<p><b>Why do we allow identifiers with trailing spaces?</b></p>
<p>While this may not be common practice (notice the trailing spaces in the table and column names)...</p>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;-moz-background-clip:border;-moz-background-origin:padding;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">CREATE&nbsp;TABLE&nbsp;</font><font color="black">dbo.[foo&nbsp;]<br></font><font color="gray">(<br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">[bar&nbsp;]&nbsp;</font><font color="blue">INT<br></font><font color="gray">);</font></pre></td>
</tr>
</table>
<p>...there are end users complaining about the fact that this works (see <a href="http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=483389" title="http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=483389" target="_blank">Connect #483389</a>).&nbsp; I had a slightly different take than user "aitcha1" in that I don't understand why SQL Server allows trailing spaces in identifier names in the first place.&nbsp; It does ignore trailing spaces when preventing duplicates (for example, you can't have a column named [bar] and a column named [bar ]), but in spite of what the documentation says, it certainly keeps the space intact when storing the metadata in the catalog views.&nbsp; So I filed two suggestions: one to fix the documentation to be more explicit about how "SQL Server stores the name without the trailing spaces," and one to actually tighten up identifier rules.</p>
<div style="margin-left:40px;" class="SubTitle"><a href="http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=483553" title="http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=483553" target="_blank">#483553 : DOC : Delimited Identifiers topic lies about trailing spaces</a></div><div style="margin-left:40px;" class="SubTitle">&nbsp;</div><div style="margin-left:40px;" class="SubTitle"><a href="http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=483527" title="http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=483527" target="_blank">#483527 : Tighten up identifier rules</a> <br><br></div>
<hr style="height:1px;">
<p><b>Why does SSMS not like VARCHAR(MAX) parameters?</b> <br></p>
<p>There was an avid discussion on the newsgroups this week about a long-acknowledged bug in SSMS. If you have a stored procedure that has a VARCHAR(MAX) parameter, and you open Object Explorer, right-click the procedure and choose Script As &gt; Execute &gt; New Query Window, the variable declaration for that parameter is simply VARCHAR, not VARCHAR(MAX). Obviously this isn't a huge show-stopper, and an easy workaround is to use NVARCHAR(MAX) parameters (if this is acceptable), but I also believe that this can't be a very difficult bug to fix.&nbsp; I tried to use Profiler to peek at what SSMS calls when you run this script, but they are only retrieving metadata and not revealing how they are actually building the output that you ultimately execute.&nbsp; My guess is that there is just a bug when handling VARCHAR() types where max_length = -1.&nbsp; Anyway the bug has been sitting around for almost three years and could possibly use a little traffic.<br></p>
<div style="margin-left:40px;" class="SubTitle"><a href="http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=241782" title="http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=241782" target="_blank">#241782 : create script for sp execute in parameter varchar(max) wrong</a><br>&nbsp;</div>
<hr style="height:1px;">
<p><b>Who coded that infinite loop?</b></p>
<p>In several Windows collations (e.g. Latin1_General_CI_AS), when performing a REPLACE() of CHAR(0), the SQL engine can enter an infinite loop and consume multiple CPUs.&nbsp; A problem with CHAR(0) was first reported by Erland back in 2006, but the infinite loop was a much more recent revelation (it was added in a comment this week, and may appear in an independent Connect item in the future).<br></p>
<p style="margin-left:40px;"><a href="http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=125502" title="http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=125502" target="_blank">#125502 : Replace of char(0) does not work in DB with Windows collation<br><br></a></p>
<hr style="height:1px;"><br><b>Can we have an option to turn off deferred name resolution?
</b>
<p>Deferred name resolution is confusing, and has even created a market for 3rd party products that track dependencies better than SQL Server can do on its own (e.g. Red-Gate's <a href="http://www.red-gate.com/products/SQL_Dependency_tracker/index.htm" title="http://www.red-gate.com/products/SQL_Dependency_tracker/index.htm" target="_blank">SQL Dependency Tracker</a>).&nbsp; We know that if we create a stored procedure that calls another stored procedure that doesn't yet exist, the parser still allows us to create the procedure, giving us the benefit of the doubt that we will create the other procedure later.&nbsp; So in this case, we get a warning message:</p>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;-moz-background-clip:border;-moz-background-origin:padding;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">CREATE&nbsp;PROCEDURE&nbsp;</font><font color="black">dbo.test1<br></font><font color="blue">AS<br>BEGIN<br>&nbsp;&nbsp;&nbsp;EXEC&nbsp;</font><font color="black">dbo.proc_does_not_exist</font><font color="gray">;<br></font><font color="blue">END<br></font><font color="blue">GO</font></pre>
</td>
</tr>
</table>
<br>
<table bgcolor="#efefef" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;-moz-background-clip:border;-moz-background-origin:padding;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;">Cannot add rows to sys.sql_dependencies for the stored procedure because it depends <br>on the missing table 'dbo.proc_does_not_exist'. The stored procedure will still be <br>created; however, it cannot be successfully executed until the table exists.</pre></td>
</tr>
</table>
<p>The warning message incorrectly says "table" when it should probably say "object."&nbsp; In SQL Server 2008, the error message is actually: <br></p>
<table bgcolor="#efefef" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;-moz-background-clip:border;-moz-background-origin:padding;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;">The module 'test1' depends on the missing object 'dbo.proc_does_not_exist'. The module <br>will still be created; however, it cannot run successfully until the object exists.</pre></td>
</tr>
</table>
<p>Sadly, we don't get a warning or an error if we reference a table that doesn't yet exist:</p>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;-moz-background-clip:border;-moz-background-origin:padding;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">CREATE&nbsp;PROCEDURE&nbsp;</font><font color="black">dbo.test2<br></font><font color="blue">AS<br>BEGIN<br>&nbsp;&nbsp; SELECT <font color="gray">*</font> FROM </font><font color="black">dbo.table_does_not_exist</font><font color="gray">;<br></font><font color="blue">END<br></font><font color="blue">GO</font></pre>
</td>
</tr>
</table>
<br>
<table bgcolor="#efefef" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;-moz-background-clip:border;-moz-background-origin:padding;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;">Command(s) completed successfully.</pre></td>
</tr>
</table>
<p>This was complained about in <a href="http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=246014" title="http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=246014" target="_blank">Connect #246014</a>, which was closed as fixed, however the parser is still silent about the problem in SQL Server 2008 (where we are told it has been addressed).&nbsp; I guess the "fixed" part is just that the error message when the referenced object is NOT a table no longer incorrectly says "table."&nbsp; :-(<br></p>
<p>Deferred name resolution falls completely on its face if we reference a column that DOES NOT (yet) exist, in a table that DOES exist.&nbsp; Where is our benefit of the doubt now?&nbsp; This stored procedure won't compile, even if we intend to go create the column immediately:</p>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;-moz-background-clip:border;-moz-background-origin:padding;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">CREATE&nbsp;TABLE&nbsp;</font><font color="black">dbo.foo<br></font><font color="gray">(<br>&nbsp;&nbsp;&nbsp; </font><font color="black">bar </font><font color="blue">INT<br></font><font color="gray">);<br></font><font><font color="blue">GO<br></font></font><br><font color="blue">CREATE&nbsp;PROCEDURE&nbsp;</font><font color="black">dbo.test3<br></font><font color="blue">AS<br>BEGIN<br>&nbsp;&nbsp; SELECT </font><font><font color="black">column_does_not_exist</font></font><font color="blue"> FROM </font><font color="black">dbo.foo</font><font color="gray">;<br></font><font color="blue">END<br></font><font color="blue">GO</font><br></pre></td>
</tr>
</table>
<br>
<table bgcolor="#efefef" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;-moz-background-clip:border;-moz-background-origin:padding;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="red">Msg 207, Level 16, State 1, Procedure test3, Line 3<br>Invalid column name 'column_does_not_exist'.</font></pre></td>
</tr>
</table>
<p>As alluded to, dependency tracking gets better in SQL Server 2008, as *some* unresolved dependencies are stored in sys.dm_sql_referenced_entities.&nbsp; I wrote a lengthy blog post about this last year:</p>
<blockquote>
<p><a href="http://sqlblog.com/blogs/aaron_bertrand/archive/2008/09/09/keeping-sysdepends-up-to-date-in-sql-server-2008.aspx" title="http://sqlblog.com/blogs/aaron_bertrand/archive/2008/09/09/keeping-sysdepends-up-to-date-in-sql-server-2008.aspx" target="_blank">Keeping sysdepends up to date in SQL Server 2008</a><br></p>
</blockquote>
<p>But I would still like to make it impossible to create anything (even a synonym) that references an object that doesn't yet exist.&nbsp; Erland goes into much more detail about this in <a href="http://www.sommarskog.se/strict_checks.html" title="http://www.sommarskog.se/strict_checks.html" target="_blank">his paper on SET STRICT_CHECKS ON</a>.&nbsp; You can't vote on that, but you could vote on these:</p>
<blockquote>
<p><a href="http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=127152" title="http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=" target="_blank">#127152 : Ability to disable or workaround deferred name resolution</a><br><br><a href="http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=287100" title="http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=287100" target="_blank">#287100 : Turn off deferred name resolution for CREATE SYNONYM</a></p>
<a href="http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=260762" title="http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=260762" target="_blank">#260762 : Add optional checks for more robust development</a><br></blockquote>
<p>&nbsp;</p>