creating indexes across servers

Question

creating indexes across servers

davidandrews13

SSCertifiable

Points: 6809
More actions
May 1, 2009 at 3:47 am

#200068

hi,
i've got a job which copies tables (that dont have a primarykeyindex) from a database on my sql 2008 server to a database on my sql 2000 server.
its running as a job on the 2008 server.
the basic jist of the code is
create index i1 on server.database.dbo.table1(col1)
go
create index i2 on server.database.dbo.table2(col1)
etc
etc
the error message states that the object name contains more than the maximum number of prefixes, and that the maximum is two. is it possible to create indexes across servers?
thanks

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

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

Carl Federl One Orange Chip Points: 25384 More actions · Answer 1

It is not possible to be connected to one SQL Server and create an index on a different SQL Server.

It is not possible to be connected to one SQL Server and have context of a database and create an index on a different database within the same SQL Server.

SQL = Scarcely Qualifies as a Language

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 2

Hi

You can use a OPENQUERY for this:

SELECT *

FROM OPENQUERY (Local_Link_Sa, 'ALTER INDEX IX_BigTable_AnyInt ON Sandbox.dbo.BigTable REBUILD SELECT 1')

Just tried and it seems to work. You just have to select anything after the rebuild (in my case "SELECT 1").

Greets

Flo

davidandrews13 SSCertifiable Points: 6809 More actions · Answer 3

thanks!

i've basically got this

SELECT * FROM OPENQUERY(SERVERNAME,'CREATE INDEX i1 ON DATABASENAME.dbo.TABLENAME(Col1, Col2) SELECT 1')

it doesn't error, although i've gone to enterprise manager to check the indexes and i can't see them. (i right clicked the table and selected 'design table', then right clicked a column and selected 'indexes/keys')