Create Table help

[puterkrazy]

I need to create a table on a database on a linked server...
I get the following error when specifying the server:
create table server.db.dbo.test_table
(
col_test char(10)
)

Server: Msg 117, Level 15, State 1, Line 2
The object name 'server.db.dbo.' contains more than the maximum number of prefixes. The maximum is 2.

how can I create a table while specifying the server? thanks in advance!

 

[NoCoolHandle]

You can't create a table on a linked server.

Log in to that server and then create it there, at that point you will be able to use it as a linked server table.

Linked servers are supposed to be used for select,insert,update and delete and create is not supported.

HTH

Rob

 

[puterkrazy]

Well I am able to create a table on a database on a linked server by using that without the server specified. The problem occurs when there is a database on each server with the same name and it uses the database on the current server instead of the linked. So it is possible to create a table on a database on a linked server... but I need to know how...?

 

[NoCoolHandle]

You log on to that server.

 

[puterkrazy]

I need to do this programmatically.............

 

[SQLBill]

Try identifying it this way

\\server.database.owner.table

or in your case:

create table \\server.db.dbo.test_table

-SQLBill

 

[puterkrazy]

nope, i got an incorrect sytax near '\'

any other ideas?

 

[SQLBill]

References: BOL - linked servers
Microsoft SQL Server 2000 DBA Survival Guide - chapter on Transact-SQL, SQL Essentials-Distributed Queries.

Based on reading the above in regards to Linked Servers, I have come to the conclusion that you can only do Queries (retrieve data). You can not create tables, etc.

The naming convention for a linked server is:

linked_server_name.catalog.schema.object

for a SQL Server linked server the catalog is the database name, the schema is the owner, and the object is the table or view.

So, your naming convention was correct...you just can't do what you are trying to do.

-SQLBill