Tag Archives: Gotcha

Filtered index gotcha

We added a filtered index to a table today and a SQL Agent job that updates that table started to fail with this error message:

Msg 1934, Sev 16, State 1, Line 290 : UPDATE failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations. [SQLSTATE 42000]

It turns out that you must have certain options enabled in your session in order to use or update a filtered index.

All these must be ON:


And this one must be OFF:


See Books Online for details: https://msdn.microsoft.com/en-us/library/ms188783.aspx (it’s about half way down the page in the Remarks section).

So what happened here? Well it looks like SQL Agent does not, by default, set QUOTED_IDENTIFIER on. I can’t find any clear official information on this but that’s what I’m seeing in SQL Profiler. I also couldn’t find any way to override this in SQL Agent’s configuration settings.

Anyway, I added SET QUOTED_IDENTIFIER ON at the start of the job step and it worked fine.


Reporting and the Edge browser

I recently updated my dev machine to Windows 10. I like it a lot more than Windows 8.1. I was keen to see whether Reporting works in the new Edge browser and it turns out it works just fine. Apart from a strange inability to edit a data source in Report Manager.

When you update anything on a data source you have to re-enter the login’s password but in Edge the page keeps insisting the password is wrong and refuses to save the change.

So I recommend you edit data sources in Internet Explorer for now.

sp_help_revlogin revisited

I believe I’ve come up with an improvement to sp_help_revlogin.

Transferring a login from one server to another is one of those learning experiences we all go through. If you just do CREATE LOGIN on another server the login in the master database usually gets a different ID. When you restore the database on the other server, the ID of the user in the database doesn’t match the ID of the login in master and so the login can’t access the database. (And, as they say, ‘hilarity ensues’.)

A better way to move the login is to use a Microsoft stored procedure sp_help_revlogin (read more here: http://support.microsoft.com/kb/918992/) to generate a script that will re-create the login with the same ID. But I noticed it doesn’t include the login’s default language, which is something that can have a major impact on things like whether the account can re-use existing cached query plans. So I’ve added this element.

Instead of this:

	, SID = 0x8E<snip>80
	, DEFAULT_DATABASE = [databaseY]

you now get this:

create login [loginX]
with	password = 0x02<snip>9E hashed
	, sid = 0x8E<snip>80
	, default_database = [databaseY]
	, default_language = [us_english]
	, check_policy = on
	, check_expiration = off

(While I was at it, I changed the output to lower case because that’s how I like to code.)

Here’s the new version of the procedure with the altered lines highlighted:

Purpose:	To script out logins for copying to another server.
Source:		http://support.microsoft.com/kb/918992/
Modifications:	I added the default language.

use master;

IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
  DROP PROCEDURE sp_hexadecimal

CREATE PROCEDURE sp_hexadecimal
    @binvalue varbinary(256),
    @hexvalue varchar (514) OUTPUT
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i &amp;amp;lt;= @length)
  DECLARE @tempint int
  DECLARE @firstint int
  DECLARE @secondint int
  SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
  SELECT @firstint = FLOOR(@tempint/16)
  SELECT @secondint = @tempint - (@firstint*16)
  SELECT @charvalue = @charvalue +
    SUBSTRING(@hexstring, @firstint+1, 1) +
    SUBSTRING(@hexstring, @secondint+1, 1)
  SELECT @i = @i + 1

SELECT @hexvalue = @charvalue

IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
  DROP PROCEDURE sp_help_revlogin
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary  varbinary (256)
DECLARE @PWD_string  varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr  varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)

DECLARE @defaultdb sysname
DECLARE @defaultlanguage sysname

IF (@login_name IS NULL)

      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, p.default_language_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name &amp;amp;lt;&amp;amp;gt; 'sa'

      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, p.default_language_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @defaultlanguage, @hasaccess, @denylogin
IF (@@fetch_status = -1)
  PRINT 'No login(s) found.'
  CLOSE login_curs
  DEALLOCATE login_curs
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
WHILE (@@fetch_status &amp;amp;lt;&amp;amp;gt; -1)
  IF (@@fetch_status &amp;amp;lt;&amp;amp;gt; -2)
    PRINT ''
    SET @tmpstr = '-- Login: ' + @name
    PRINT @tmpstr
    IF (@type IN ( 'G', 'U'))
    BEGIN -- NT authenticated account/group

      SET @tmpstr = 'create login ' + QUOTENAME( @name ) + ' from windows with default_database = [' + @defaultdb + '], default_language = [' + @defaultlanguage + ']'
    ELSE BEGIN -- SQL Server authentication
        -- obtain password and sid
            SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
        EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
        EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

        -- obtain password policy state
        SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
        SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

            SET @tmpstr = 'create login ' + QUOTENAME( @name ) + ' with password = ' + @PWD_string + ' hashed, sid = ' + @SID_string + ', default_database = [' + @defaultdb + '], default_language = [' + @defaultlanguage + ']'

        IF ( @is_policy_checked IS NOT NULL )
          SET @tmpstr = @tmpstr + ', check_policy = ' + @is_policy_checked
        IF ( @is_expiration_checked IS NOT NULL )
          SET @tmpstr = @tmpstr + ', check_expiration = ' + @is_expiration_checked
    IF (@denylogin = 1)
    BEGIN -- login is denied access
      SET @tmpstr = @tmpstr + '; deny connect sql to ' + QUOTENAME( @name )
    ELSE IF (@hasaccess = 0)
    BEGIN -- login exists but does not have access
      SET @tmpstr = @tmpstr + '; revoke connect sql to ' + QUOTENAME( @name )
    IF (@is_disabled = 1)
    BEGIN -- login is disabled
      SET @tmpstr = @tmpstr + '; alter login ' + QUOTENAME( @name ) + ' disable'
    PRINT @tmpstr

  FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @defaultlanguage, @hasaccess, @denylogin
CLOSE login_curs
DEALLOCATE login_curs

You can download the code here.

PowerShell script path limitations

Having discovered that SQL Agent PowerShell steps use PowerShell 2.0, I found you can use the latest version of PowerShell on your server by invoking PowerShell and passing it the path to a script file:

powershell.exe -file “\\servername\path\filename.ps1”

I thought it would make the job easier to port to a new server one day if, instead of \\servername, I used \\ but PowerShell said that the current execution policy prevented running remote unsigned scripts. (Our execution policy was RemoteSigned.)

I don’t understand why it treats the names this way. According to my reading, you can spoof \\machinename simply by adding it to the hosts file and redirecting it elsewhere. But, although you can do that with, it doesn’t work so I would have though PowerShell would treat such a script as local.

I’m not sure what’s going on here but it’s one to remember.


SQL Agent CmdExec gotcha

I was writing a new SQL Agent job for somebody else so I thought I should comment it a bit more thoroughly than I usually do. There’s an Operating System (CmdExec) step that calls sqlcmd to run a script file:

sqlcmd -S localhost -E -d master -i "\\\joblogs\somescript.sql" -b

This pattern works fine elsewhere. Here though, I added a comment just to point out something:

rem A comment
sqlcmd -S localhost -E -d master -i "\\\joblogs\somescript.sql" -b

The job ran successfully but it was suspiciously fast. It turns out that’s because it didn’t do anything. Apparently SQL Agent interprets this differently. I know the DOS shell language is a bit arcane sometimes but rem means ‘ignore everything until the next command’. This works in a .bat file. I just tested to make sure I wasn’t going mad.

Time to dig deeper. Books Online (https://msdn.microsoft.com/en-GB/library/ms190264.aspx) says:

This topic describes how to create and define a Microsoft SQL Server Agent job step in SQL Server 2016 that uses an executable program or operating system command

That ‘an‘ is a subtle clue. I had always assumed this worked like a batch file but it seems only the first command gets executed. In my case the first line is commented out, which effectively means ‘do nothing’. I tested with multiple statements and again only the first one executed.

So don’t confuse it with batch files. Put one command per step and – a bit of lateral thinking here – put your comments in the line(s) underneath:

sqlcmd -S localhost -E -d master -i "\\\joblogs\somescript.sql" -b
rem A comment

Denali Side-by-Side Gotcha

Don’t try this at home. I just got my new (new to me anyway) laptop set up nicely and then installed an instance of Denali CTP3 to start exploring. RTFM? Not me! The first thing I thought I’d play with was the tabular model and Project Crescent as this has huge potential for Reporting & BI people. So I click New Project and it says you can only do this on a default instance. Erm, but my default instance is 2008R2. Oh well, back to the (un)installer.

Strange gotcha with column names

Here’s a funny thing I discovered while working with a table I created by importing data from Excel. (I know, I only have myself to blame.) A number of the column names ended up with a trailing space on the end. They were Y/N type columns where the value was either Y or null. I decided to update all the nulls to Ns so the nulls wouldn’t trip me up later. So I typed a query like this: update

set = isnull(, ‘N’). I noticed Intellisense had underlined the column name but I knew what I was doing and assumed it was still refreshing its cache. The update ran fine but when I tried to select that column I got a ‘no such column’ error. WTF?

I can only assume this is a side-effect of the way you can update things like rowset functions and not just tables. I’ve done a bit of playing around and found that (surprise surprise) some variants will work and some will fail. Try it for yourself:

create table dbo.TestTable
 [TestColumn ] varchar(20) null

--This update will give the result: (0 row(s) affected)
update dbo.TestTable 
set TestColumn = 'hello';

--But this update will fail:
--Msg 207, Level 16, State 1, Line 2
--Invalid column name 'TestColumn2'.
update dbo.TestTable 
set TestColumn2 = 'hello';

drop table dbo.TestTable;

I suppose the moral of the story is: concentrate and check that updates affect the number of rows you expect. (Damn, that’s not going to work!)