#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

image


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?


Further Reading:



  • An Introduction to Multi-part naming standards for Object name referencing [Link]
  • Scripts to generate and parse multi-part database object names – PARSENAME() function [Link]
  • sp_help [Books On Line Link]

Until we meet next time,


Be courteous. Drive responsibly.

Advertisements

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

  1. Bodhi2

    The PARSENAME function can help in procedures that must accept multipart object names and then pass them to something that likes a one-part name.

    SELECT PARSENAME(‘AdventureWorks.Person.Contact’, 1) AS ‘Object Name’; — Contact
    SELECT PARSENAME(‘AdventureWorks.Person.Contact’, 2) AS ‘Schema Name’; — Person
    SELECT PARSENAME(‘AdventureWorks.Person.Contact’, 3) AS ‘Database Name’; — AdventureWorks
    SELECT PARSENAME(‘Bodhi2.AdventureWorks.Person.Contact’, 4) AS ‘Server Name’;

    use AdventureWorks;
    exec sp_help ‘AdventureWorks.Person.Contact’; –This works
    exec sp_help ‘Bodhi2CBI.AdventureWorks.Person.Contact’; — This fails. sp_help will never accept a 4-part name.
    use master;
    exec sp_help ‘AdventureWorks.Person.Contact’; –This fails on “The database name component of the object qualifier must be the name of the current database.”

    Like

    Reply

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s