Newbie question on stored procedures and linked servers

I have a "main" SQL Server 2005 server and several "farm" SQL Servers using
2005.
I'd like to be able to create stored procedures on the main server and run
them from the farm server against the farm server. For example, one stored
procedure would create a database and tables. I'd like it to live on the mai
n
server and create the database and tables on the farm server.
Is this possible? If so, are there any examples available?
JohnIn general, stored procedures executing on remote servers are restricted
from changed the schema on the remote server. It's a good security
consideration.
Now there are many 'work-a-rounds'. One that I employ is having the 'main'
server create a SQL script file, dropping that in a location available to
the remote server, and then executing a job that looks for file(s) in that
location (perhaps filtered by naming conventions, etc.), and then executes
that script file.
Perhaps if you provided a bit more details about what you are attempting to
accomplish, someone here will have a better solution.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"JColaizzi" <JColaizzi@.discussions.microsoft.com> wrote in message
news:009DE414-CED1-4882-B8F6-FA314C8643BE@.microsoft.com...
> I have a "main" SQL Server 2005 server and several "farm" SQL Servers
> using
> 2005.
> I'd like to be able to create stored procedures on the main server and run
> them from the farm server against the farm server. For example, one stored
> procedure would create a database and tables. I'd like it to live on the
> main
> server and create the database and tables on the farm server.
> Is this possible? If so, are there any examples available?
> John|||"Arnie Rowland" wrote:
> Perhaps if you provided a bit more details about what you are attempting t
o
> accomplish, someone here will have a better solution.
>
The main server is a data warehouse storing metadata and fact data for
approximately 1000 cubes that have to be produced monthly. The farm servers
are the servers where the cubes will be produced. (We do this now with
Oracle and are moving to SS05.)
I want to avoid having multiple farm servers where if a stored procedure is
changed it has to be propagated to mulitple servers. So in the example abov
e
the stored procedure would accept some variables and create the database and
tables on the farm server that are used to create one of the thousand cubes.
So in essence I want a local server to call a remote server stored procedure
that will create a database and tables on the local server.
John|||You may wish to look into aspects of schema replication.
With SQL Server 2005, you can replicate Stored Procedures. So you would only
have to manage the main server, and yes, propagation is involved, but it
becomes a background process and part of the database 'system' itself. Not
something that would have to be managed manually.
Calling those replicated 'remote' procedures is, perforce, a normal
operation.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"JColaizzi" <JColaizzi@.discussions.microsoft.com> wrote in message
news:3ABDDDF1-C638-4FAA-BBE1-A10581257EED@.microsoft.com...
> "Arnie Rowland" wrote:
> The main server is a data warehouse storing metadata and fact data for
> approximately 1000 cubes that have to be produced monthly. The farm
> servers
> are the servers where the cubes will be produced. (We do this now with
> Oracle and are moving to SS05.)
> I want to avoid having multiple farm servers where if a stored procedure
> is
> changed it has to be propagated to mulitple servers. So in the example
> above
> the stored procedure would accept some variables and create the database
> and
> tables on the farm server that are used to create one of the thousand
> cubes.
> So in essence I want a local server to call a remote server stored
> procedure
> that will create a database and tables on the local server.
> John