I guess the title explains everything, but I was wondering if anyone had a method of my being able to create a table over a linked server? Either by executing a SP on the remote side with the name and schema passed as parameters or directly?
I need to seperate off some very CPU intensive pivoting that is killing the main production server, so my thoughs are to create the table, populate it with the data as needed, run the pivot SP on the remote server via openrowset or some other method and then delete the table again.
I've got pretty much everything working except for the create table, I have a SP on the remote side that runs and creates the table when run locally but not when ran across a linked server (very annoying)
Oh yeah, it's SQL 2000 enterprise edition (Itanium version if you're interested) running on Windows Enterprise edition.
If no-one can help then I might just go with creating some static tables and controlling them via a key for each set of data that uses it, but that would be messy and involve more maintenance than the one that I want to do.