Category Archives: #SQLServer

All about Microsoft SQL Server

#0316 – SQL Server – sp_help and multi-part naming of objects – Msg 102 – Incorrect syntax near ‘.’

I was recently working on exploring a couple of tables in a database that I was troubleshooting for performance purposes. I was using the system stored procedure sp_help and all was fine until I started accessing tables with a schema other than the default – “dbo”.

As soon as I started to access tables with schemas other than “dbo”, I encountered the following error:

USE AdventureWorks2012;

GO

sp_help HumanResources.Employee;

GO

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘.’.

Initially I thought that it was because I had not enclosed them in square brackets to indicate object identifiers, so, I tried that with the same results:

USE AdventureWorks2012;

GO

sp_help [HumanResources].[Employee];

GO

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘.’.

Then, I realized that sp_help is possibly unable to handle the multi-part naming convention of SQL Server objects, which to me is quite odd. So, I enclosed the entire two-part name inside of square brackets and it worked!

USE AdventureWorks2012;

GO

sp_help [HumanResources.Employee];

GO

And so did this:

USE AdventureWorks2012;

GO

sp_help ‘HumanResources.Employee’;

GO

As I mentioned earlier, I find this behaviour of sp_help to be strange. I can understand why it accepts [schemaname.objectname] format, but what I can’t understand is why it can’t accept [schemaname].[objectname]. While I was able to get through the exploratory process with my newly discovered workaround, I am quite sure that many developers would be ending up with this error day in and day out.

Have you encountered this behaviour?

#0315-SQL Server-Different ways to check for existence of an object (table/SP, etc)

1 Reply

I was recently reviewing the deployment scripts for a couple of products and I noticed that each team/DBA had their own style – and neither of them was incorrect. So, I sat about documenting each of those different styles for my own academic interest. I was able to document a total of 6 different ways in which various teams/DBAs and tools check for existence of a SQL Server object (table, stored procedure, etc).

These 6 different ways are shown below:

USE AdventureWorks2012;
GO

--1. The ISO compliant way
SELECT *
FROM INFORMATION_SCHEMA.TABLES AS ist
WHERE ist.TABLE_SCHEMA = 'HumanResources'
  AND ist.TABLE_NAME = 'Employee';
GO

--2. Using SQL Server Catalog Views
SELECT *
FROM sys.tables AS st
WHERE st.schema_id = SCHEMA_ID('HumanResources')
  AND st.name = 'Employee'
  AND st.is_ms_shipped = 0; --We are only looking for user objects!
GO

--3. Using SQL Server Catalog Views
SELECT *
FROM sys.objects AS so
WHERE so.type = 'U'
  AND so.schema_id = SCHEMA_ID('HumanResources')
  AND so.name = 'Employee'
  AND so.is_ms_shipped = 0; --We are only looking for user objects!
GO

--4. Using the OBJECT_ID function (The easy way)
--If the OBJECT_ID does not return a NULL value, the object exists
--For the object type value, refer http://technet.microsoft.com/en-us/library/ms190324.aspx
SELECT OBJECT_ID('HumanResources.Employee','U') AS ObjectId;
GO

--5. A Hybrid appraoch
SELECT *
FROM sys.objects AS so
WHERE so.object_id = OBJECT_ID('HumanResources.Employee','U')
  AND so.is_ms_shipped = 0; --We are only looking for user objects!
GO

--6. The SSMS way 
--   (if you have set your scripting options to check for object existence)
SELECT *
FROM sys.objects AS so
WHERE so.object_id = OBJECT_ID('HumanResources.Employee')
  AND so.type IN ('U');
GO

You can see here that we have a wide variety of methods that check for existence of an object – from ISO compliant ways to purely SQL Server specific ways.

My favourite method is method #4, using the function OBJECT_ID(). What one is your favourite?

Do note that OBJECT_ID will not work for objects which are not schema scoped, e.g. DDL triggers.

#0314-SQL Server-Identify records with special characters using regular expressions

3 Replies

One of my team mates was recently faced with an interesting problem – a few records were inserted/updated into the database via some backend data append and bulk insert tasks. These records apparently had some special characters which was causing the application to crash. While a application code fix was being developed, tested and deployed it was essential to be able to identify the bad data and clean it up as a stop-gap arrangement. The problem was that the offending characters were not available on the keyboard! (i.e. they were “special” characters). I was approached to help with this task and this is an account of a solution that I came up with.

Demo

To begin with the demo, let us assume that we have a table with a text column that has both – “bad” and “good” data. To simulate the bad data, I am using special characters from the ASCII table which are not generally visible on the console (e.g. a vertical tab and others). Characters like Space and Horizontal tabs are quite normal in almost all documents and are therefore considered to be valid characters for the purposes of this demo.

Once the table has been created and test data created, we go about the task of identifying the offending records. To do so, we simply build a lookup table which would help us create a string of “bad” characters which can be used as part of a regular expression.

That’s it! Once the regular expression is ready, it’s a matter of writing a simple SELECT statement to identify the records matching the expression.

The complete script, with the output is shown below:

USE tempdb;

GO

–Create the test table

IF OBJECT_ID(‘tempdb..#hiddenCharacterValues’,’U’) IS NOT NULL

    DROP TABLE #hiddenCharacterValues;

GO
CREATE TABLE #hiddenCharacterValues

    (RecordId INT IDENTITY(1,1),

     RecordValue VARCHAR(25)

    );

GO
–Insert some test data into the table

INSERT INTO #hiddenCharacterValues

       (RecordValue)

VALUES (‘StandardSpace’  + CHAR(32)),   –Good

       (‘GroupSeparator’ + CHAR(29)),   –Bad

       (‘HorizontalTab’  + CHAR(9)),    –Good

       (‘VerticalTab’    + CHAR(11)),   –Bad

       (‘ExtendedASCII’  + CHAR(176));  –Bad

GO
–Build a comma separated list of known “bad characters”

DECLARE @regExString VARCHAR(MAX);

DECLARE @invalidCharLookUp TABLE (InvalidChars VARCHAR(2));

INSERT INTO @invalidCharLookUp (InvalidChars)

VALUES (CHAR(11)),

       (CHAR(29)),

       (CHAR(176));
SELECT @regExString = COALESCE(@regExString,”)

                      + iclu.InvalidChars

FROM @invalidCharLookUp AS iclu;
–Finally select records which contain these characters

SELECT hcv.RecordId,

       hcv.RecordValue

FROM #hiddenCharacterValues AS hcv

WHERE hcv.RecordValue LIKE ‘%[‘ + @regExString + ‘]%’;

GO

Now that the records are identified, they can either be manually updated or deleted (as required by the business).

Using Regular Expressions with CHECK constraints [Link]

Are there any other methods that you would use (or are using) to address such an issue? Please do let me know.

Until we meet next time,

Be courteous. Drive responsibly.

#0313 – SQL Server – sp_help returns double the field length for Unicode characters

An Example

The AddressLine1 and AddressLine2 fields in the Person.Address table of the AdventureWorks2012 sample database are defined to be 60 characters in length (see screenshot below), but when checked using sp_help, they show the length as being 120.

USE AdventureWorks2012 ;
GO
SELECT  DATABASEPROPERTYEX(DB_NAME(), 'Collation') 
        AS DBCollationName ;
GO

sp_help [Person.Address] ;
GO

The results of this query are shown below, which clearly show the doubled length value.

The reason for this discrepancy is that sp_help does not report the number of characters, but reports on the maximum number of bytes that the column can occupy.

In this case, both the AddressLine1 and AddressLine2 columns are 60 characters in length, but because the column is a NVARCHAR column, it occupies 120 bytes which is reported by the sp_help system stored procedure.

#0312 – SQL Server – List of SQL 2008 R2 and SQL 2012 SP1 based cumulative updates

SQLTwins by Nakul Vachhrajani

SQL Server tips and experiences dedicated to my twin daughters.