Why does the syntax work for insert and not insert into?

Question

Post reply

Why does the syntax work for insert and not insert into?

Viewing 6 posts - 16 through 20 (of 20 total)

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

dwilliscp SSCrazy Eights Points: 9270 More actions · Answer 1

Different server than where the stored Proc is running.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

dwilliscp (7/17/2012)
Different server than where the stored Proc is running.

Taken from BOL http://msdn.microsoft.com/en-us/library/ms188029.aspx

"You cannot create new_table on a remote server..."

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Lowell SSC Guru Points: 323518 More actions · Answer 3

ok i know that you can create a table on a linked server using EXECUTE AT, but i think you have to create the table first, and then insert into it;

i don't think you can use the INSERT INTO structure with a linked server:

EXECUTE ( 'CREATE TABLE [this.that].dbo.SalesTbl

(SalesID int, SalesName varchar(10)) ; ' ) AT R2D2;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

dwilliscp SSCrazy Eights Points: 9270 More actions · Answer 4

I have data being loaded from two different companies... so the first time I use INSERT and the second I use INSERT INTO.... the first gives the error I posted earlier.. but if you create the table and then run the second command.. accross the network.. no error. For some reason the insert just does not like having the dot in the database name, even though you put it inside of [].... at least that is how I have taken the error message to mean.

dwilliscp SSCrazy Eights Points: 9270 More actions · Answer 5

Luis Cazares (7/17/2012)
dwilliscp (7/17/2012)
Different server than where the stored Proc is running.
Taken from BOL http://msdn.microsoft.com/en-us/library/ms188029.aspx
"You cannot create new_table on a remote server..."

Interesting.. just tried to create a table on R2D2's server_manager db and it gave the same message... I did not know you could not create a table from one server to the next.

Thanks

GSquared SSC Guru Points: 260824 More actions · Answer 6

As already mentioned, you can't do Select Into cross-server. In order to do cross-server DDL (including table-creation), you need to make sure the credentials used have the right permissions, and you have to execute the DDL at the other server. In other words, Exec At, like Lowell's example.

Check the permissions of the credential you're using if you can't create a table through that means.

The error is about having a server name on the DDL, not about a period in the server-name. Try it with a server that doesn't have punctuation in the name, you'll get the same error.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon