How to handle "\" in the server name: Incorrect syntax near '\'

Question

How to handle "\" in the server name: Incorrect syntax near '\'

gregarobinson

Right there with Babe

Points: 736
More actions
December 4, 2008 at 10:23 am

#194717

I need to do the following inside a stored procedure:
INSERT INTO DEV001\SQL2005DEV.myDataBase.myTable
SQL does not like the "\": Incorrect syntax near '\'.
I tried:
INSERT INTO [DEV001\SQL2005DEV].myDataBase.myTable.
I do not get a compile error but the insert does not insert any rows.
How do I handle the "\" in the server name?

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Is this a linked server? If not you do not need the server name. You are also missing the schema in the 4 part naming. You need server.database.schema.table and you are missing schema.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Hi Greg

Some of our older servers have similar names and I've worked around it successfully by using square brackets, as you suggest.

Have you ran the SELECT part of your INSERT on its own, to ensure that rows are returned for the INSERT?

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

gregarobinson Right there with Babe Points: 736 More actions · Answer 3

gregarobinson

Right there with Babe

Points: 736

December 4, 2008 at 10:37 am

#907786

Yes, it's linked

gregarobinson Right there with Babe Points: 736 More actions · Answer 4

Yes, I have run the insert on it's own without the server name, just the database name, and it works correctly.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 5

Was the omission of schema a typo or is it missing?

Can you do a Select * from [DEV001\SQL2005DEV].myDataBase.SCHEMA.myTable across the linked server?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

gregarobinson (12/4/2008)
Yes, I have run the insert on it's own without the server name, just the database name, and it works correctly.

Not the INSERT, the SELECT. What does it return?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

Can you post the whole INSERT statement?

INSERT INTO DEV001\SQL2005DEV...........

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

gregarobinson Right there with Babe Points: 736 More actions · Answer 8

SELECT* from DEV001\SQL2005DEV.myDatabase.dbo.myTable returns:

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near '\'.

gregarobinson Right there with Babe Points: 736 More actions · Answer 9

Well, I cannot tell you what's different but I just tried this:

INSERT INTO [DEV001\SQL2005DEV].myDatabase.dbo.myTable

and it worked. "dbo" has always been present so that is not a change.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 10

gregarobinson (12/4/2008)
SELECT* from DEV001\SQL2005DEV.myDatabase.dbo.myTable returns:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '\'.

Yes, I would have expected that which is why I used the "[]" around the linked server name.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

gregarobinson (12/4/2008)
Well, I cannot tell you what's different but I just tried this:
INSERT INTO [DEV001\SQL2005DEV].myDatabase.dbo.myTable
and it worked. "dbo" has always been present so that is not a change.

This syntax looks fine...but you weren't getting any rows inserted (see original post). What's the rest of the statement?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 12

Since you are on SQL Server 2005 - you might want to look into using synonyms instead of using the four-part naming convention in your code. I would recommend also creating a schema for the database on the linked server that you are accessing:

CREATE SCHEMA MyDatabase AUTHORIZATION dbo;

CREATE SYNONYM MyDatabase.MyObject

FOR [LinkedServer\Instance].MyDatabase.dbo.MyObject;

And now, in your code you would use:

INSERT INTO LocalTable

SELECT ...

FROM MyDatabase.MyObject;

Just a thought 😉

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs