Search results matching tags 'SSMS' and 'scripting'

Connect Digest : 2012-07-06

AaronBertrand — Fri, 06 Jul 2012 14:37:00 GMT

I've filed a few Connect items recently that I think are important.

In #752210, I complain that the documentation for DDL triggers suggests that they can prevent certain DDL from being run, which is not the case at all.

http://connect.microsoft.com/SQLServer/feedback/details/752210/doc-ddl-trigger-topic-suggests-that-rollbacks-run-before-action

In #745796, I complain that scripting datetime data in Management Studio yields output that contains a binary representation instead of a human-readable string. I suspect this is to avoid ambiguity but I guess someone forgot that there are unambiguous formats available (*cough* YYYYMMDD *cough*).

http://connect.microsoft.com/SQLServer/feedback/details/745796/ssms-generate-scripts-for-data-scripts-datetime-as-binary

In #752629, I complain about the broken procedure sp_msforeachdb, which I've complained about many times before and have even written a more functional, alternative version that actually works. I really think they need an ultimatum here: either fix the thing or turn it off by default. The only downside to turning it off by default: this will break maintenance plans that are probably breaking silently anyway.

http://connect.microsoft.com/SQLServer/feedback/details/752629/disable-sp-msforeachdb-by-default-or-fix-it

Connect Digest : 2011-08-08

AaronBertrand — Mon, 08 Aug 2011 12:30:00 GMT

This week I reported a few bugs - one with sp_helptext, one with Activity Monitor in Management Studio, and one in the new sys.dm_server_services DMV.

#683234 : sp_helptext treats non-system objects as system objects
For this one there is at least one weird case where running sp_helptext against on object prefixed with dbo ignores the dbo and tries to retrieve text for its sys counterpart (in this case, you can't run sp_helptext against a procedure you can create and execute called dbo.sp_hexadecimal; instead, you get error 15197, because the system thinks your object is a system object). I blogged about this situation over the weekend as well.

#683031 : SSMS : Activity Monitor, execution plan implies that .sqlplan file is saved
In testing the new Management Studio add-in we created for SQL Sentry Plan Explorer, it was discovered that the file handle SSMS associates with execution plans launched from within Activity Monitor is not valid - it says the file is in %USERPROFILE%\Documents, but the file is not actually there until you manually save it.

#683275 : sys.dm_server_services has two rows for SQL Server Agent in CTP3
This DMV is new in Denali and 2008 R2 SP1 (though the bug I've reported seems to only affect Denali CTP3). I had previously reported that last_startup_time is always NULL for SQL Server Agent; that bug does affect both 2008 R2 and Denali.

Then I have five others I'd like to highlight, only because they crossed my path for one reason or another. In no particular order:

#668871 : TSQL - Stop the STUFF function treating NULL as the empty string

While they've confirmed that they are not going to fix STUFF, the key here is to put in some comments suggesting why the ANSI-standard OVERLAY function would be a useful addition to T-SQL. (Or maybe create a new suggestion.)

#256665 : Extend OBJECT_DEFINITION to include more objects

I would love to be able to call a function that gives me the script for that object - be it CREATE TABLE, CREATE VIEW, CREATE SYNONYM, what have you. Currently this function only works for "script" objects, such as functions, procedures and views. If you've ever tried to manually/programmatically re-create the type of script Management Studio spits out for a table, you should have no problems voting this one up.

#640236 : The optimizer should considering materialising results of CTEs

Erland filed this one that properly suggests that a CTE *should* be able to be materialized by the optimizer only once, instead of up to (and sometimes more than) as many times as it is referenced in the rest of the query. For some insight into how much performance can be affected when the CTE is materialized only once, see this blog post from SQLCAT. For more viewpoints on the problem, see Connect #218968, Connect #377976 and Connect #483181.

#640863 : Please allow creation of temporary views

I don't think I would have very many uses for this one, but I can see the use case that is being presented. The workaround suggested by Microsoft (to create a view in tempdb, then drop it) is no more useful than creating a permanent view in the current database and then dropping it - the problem is that only one user could do this at a time.

#127219 : CREATE OR REPLACE

I've probably talked about this one before. Because of the complications with checking for existence of a stored procedure, for example, and the fact that a CREATE or ALTER PROCEDURE command must be the only statement in a batch, it is very difficult to determine the correct action without building the procedure body in dynamic SQL. On top of those, if you just blindly DROP and CREATE each time, you lose permissions, dependencies, etc. So the ability to say CREATE OR REPLACE or CREATE OR ALTER instead of performing these checks and executing one script or the other, would be welcome indeed.

Connect Digest : 2009-08-22

AaronBertrand — Sat, 22 Aug 2009 03:18:00 GMT

I rounded up a few interesting items this week.

Does "1/10/1900" mean January 10th, or October 1st?

This item demonstrates that Microsoft isn't all that concerned about publishing sample code that uses ambiguous or troublesome formats for date literals. 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. The alleged 'fix' was to change the string to "January 1, 1900." Which will simply fail if the user has, for example, SET LANGUAGE FRENCH. 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. Whereas if they had just changed "1/10/1900" to "19000101" in the first place, the problem would have been solved in one step. 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. 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. And I think this would be beneficial for everyone.

#482186 : DOC : Add/Subtract topics should warn about DATE / TIME types

Why can't we have an easier way to get first day of month, of year, etc.?

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. While the DATE data type in SQL Server 2008 will relieve some of this pain, it won't help in all cases. Meanwhile, other RDBMS have much more convenient ways to do this (e.g. Oracle's TRUNC()). I am hopeful that someday they will add a function like this to SQL Server; I call it "DATEROUND."

#483913 : Add a DATEROUND function similar to Oracle's TRUNC() for date handling

Why do we allow identifiers with trailing spaces?

While this may not be common practice (notice the trailing spaces in the table and column names)...

CREATE TABLE dbo.[foo ]
(
    [bar ] INT
);

...there are end users complaining about the fact that this works (see Connect #483389). 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. 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. 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.

#483553 : DOC : Delimited Identifiers topic lies about trailing spaces

#483527 : Tighten up identifier rules

Why does SSMS not like VARCHAR(MAX) parameters?

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 > Execute > 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. 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. My guess is that there is just a bug when handling VARCHAR() types where max_length = -1. Anyway the bug has been sitting around for almost three years and could possibly use a little traffic.

#241782 : create script for sp execute in parameter varchar(max) wrong

Who coded that infinite loop?

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. 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).

#125502 : Replace of char(0) does not work in DB with Windows collation

Can we have an option to turn off deferred name resolution?

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 SQL Dependency Tracker). 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. So in this case, we get a warning message:

CREATE PROCEDURE dbo.test1
AS
BEGIN
   EXEC dbo.proc_does_not_exist;
END
GO

Cannot add rows to sys.sql_dependencies for the stored procedure because it depends 
on the missing table 'dbo.proc_does_not_exist'. The stored procedure will still be 
created; however, it cannot be successfully executed until the table exists.

The warning message incorrectly says "table" when it should probably say "object." In SQL Server 2008, the error message is actually:

The module 'test1' depends on the missing object 'dbo.proc_does_not_exist'. The module 
will still be created; however, it cannot run successfully until the object exists.

Sadly, we don't get a warning or an error if we reference a table that doesn't yet exist:

CREATE PROCEDURE dbo.test2
AS
BEGIN
   SELECT * FROM dbo.table_does_not_exist;
END
GO

Command(s) completed successfully.

This was complained about in Connect #246014, 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). I guess the "fixed" part is just that the error message when the referenced object is NOT a table no longer incorrectly says "table." :-(

Deferred name resolution falls completely on its face if we reference a column that DOES NOT (yet) exist, in a table that DOES exist. Where is our benefit of the doubt now? This stored procedure won't compile, even if we intend to go create the column immediately:

CREATE TABLE dbo.foo
(
    bar INT
);
GO

CREATE PROCEDURE dbo.test3
AS
BEGIN
   SELECT column_does_not_exist FROM dbo.foo;
END
GO

Msg 207, Level 16, State 1, Procedure test3, Line 3
Invalid column name 'column_does_not_exist'.

As alluded to, dependency tracking gets better in SQL Server 2008, as *some* unresolved dependencies are stored in sys.dm_sql_referenced_entities. I wrote a lengthy blog post about this last year:

Keeping sysdepends up to date in SQL Server 2008

But I would still like to make it impossible to create anything (even a synonym) that references an object that doesn't yet exist. Erland goes into much more detail about this in his paper on SET STRICT_CHECKS ON. You can't vote on that, but you could vote on these:

#127152 : Ability to disable or workaround deferred name resolution

#287100 : Turn off deferred name resolution for CREATE SYNONYM
#260762 : Add optional checks for more robust development