Create Table on linked Server

Question

Post reply

Create Table on linked Server

mimorr

SSCarpal Tunnel

Points: 4850
More actions
January 22, 2003 at 1:43 pm

#158542

When I try to run a create table script on one server to create a table on another, I receive the following error:
The object name 'server.database.dbo.' contains more than the maximum number of prefixes. The maximum is 2.
Using openquery(select 0 as bogus create table...) works when going from our SQL 7 server to the SQL 2000 server, but doesn't work going from SQL 2000 to SQL 2000. How should we go about doing this? We are running Windows 2000 Advanced Server SP3 with SQL Server 2000 Enterprise Edition SP2 in a NT 4.0 domain.

Michelle

Viewing 4 posts - 1 through 3 (of 3 total)

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

Antares686 SSC Guru Points: 125444 More actions · Answer 1

Walk us thru your link server setup and the code you are actually using.

mimorr SSCarpal Tunnel Points: 4850 More actions · Answer 2

I have created linked Server,Server3,using the dialog box in EM. I chose SQL Server as the Server Type on the General Tab. On the Security Tab, I have nothing in the local server login mappings box since I don't believe I can use pass-through mappings with NT authentication because I am on an NT 4.0 domain and not a W2K Active Directory domain. Instead I have the radio button selected 'Be made using this security context' and I have sa as the remote login with the password. I know, VERY UNSECURE I haven't fixed that yet.

On the Server Options tab I have the following selected:

Collation Compatible

Data Access

RPC

RPC Out

And the Connection Timeout is set to 0(unlimited) and the Query Timeout is set to 0 (unlimited).

I have the data access option set to True.

These are the same options I have set on Server1(SQL 7) linking to Server3.

From the Server1 box I can run the following:

SELECT * FROM OPENQUERY(benroSQL03,'SELECT 0 AS Junk CREATE TABLE tempdb_userspace.dbo.Temp1 (temp1 INT)')

And the table is created. In the results pane of QA I have the column Junk with a record of 0. And (1 row(s) affected).

Running the exact query from the S2k Server4 box I get the same result set in the results pane, but when I check Server3, the table hasn't been created.

So then I tried running the create table script from Server1 to Server3 without using openquery. Just using the correct naming:

CREATE TABLE server3.tempdb_userspace.dbo.Temp1 (temp1 INT). This returns the error: 117 'The object name 'server3.tempdb_userspace.dbo.' contains more than the maximum number of prefixes. The maximum is 2.'

From Server4 I receive the same error 'The object name 'server3.tempdb_userspace.dbo.' contains more than the maximum number of prefixes. The maximum is 2.'

How can I create a table from one server to another? Why did it work SQL7 to SQL2k and not SQL2k to SQL2k?

If you have a better idea I am receptive to anything that works at this point(besides opening a new connection on server3 and creating the table and then inserting data.)

Thanks,

Michelle

mimorr SSCarpal Tunnel Points: 4850 More actions · Answer 3

I'm still interested in any reponses to this. I have searched support.microsoft.com, sqlservercentral.com, groups.google.com and haven't found any solution.

Thanks,

Michelle