How to create/drop indexes on linked servers ?

[ptpenry]

I am writing a stored proc on our financial data warehouse, during which I need to repopulate a table in a system server. Both servers are SQL Server 2000. My plan for this part was to:

1. populate a local temp table
2. TRUNCATE the live system table, "CDRSOS"
3. DROP INDEX on the live system table (to speed up the next stage)
4. Populate the live system table using a SELECT INTO command
5. Reapply indexes
6. drop the temp table

This fails on step 3. I am trying to use:

DROP Index Claims.Benjamin.dbo.CDRSOS.[IDX_CDRSOS_TRANSTYPE]

but SQL Server complains that "The index name 'Claims.Benjamin.dbo.CDRSOS.' contains more than the maximum number of prefixes. The maximum is 3."

It also fails for step 5 as I need the same 3 prefixes. Is there any way I can drop/create indexes on a linked server ?

Thanks

 

[ptpenry]

Sorry, I've just noticed in Books Online:

"Data Definition Language statements (such as CREATE, ALTER, or DROP statements) are not allowed against linked servers.
"

Back to the drawing board...

 

[mrdenny]

What you can do instead of trying to use a linked server is change the job step to a Command Shell step, then run osql and connect to the other SQL Server and run the code you need to run.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)