Executing Remote SQL Scripts
-
I've created several "general purpose" SQL scripts and Stored Procedures that I'd like to execute across many/all of my 40+ database servers as scheduled jobs. In my research - I haven't found much in the way of centralizing SPs, etc so... Is this a reasonable expectation for SQL 2000?
My initial thought was to use:
EXEC xp_cmdshell 'osql -S remoteserver -U uid -P pwd -i\\remoteserver\C$\sqlscript.sql'
Unfortunately when I tried this approach I got errors: path/file not found, etc (oh well... it was a shot
)Can anyone suggest some way to centralize the maintentance of scripts and SPs? It's a real issue when making changes to scripts on 40+ db servers!
Thanks
Glenn
-
Under what account is sqlagent running? Local system account (no network) or another? Does it work if you give the command instead of the job?
Why are you resorting to administrative shares c$?
-
Jo -
- The sqlagent is running as a local admin (domain) account
- I've not tried the xp_cmdshell in a job yet. When using it as a command:
- Accessing remote servers does not work
- Accessing the localhost server works
- I'm using the Admin shares beacause that's part of our standard infrastructue configuration
Glenn
-
It's not SQLAgent that you need to be concerned with... it's the security setting on the server itself... WHO is the SQL Server SERVICE logging in as? If it's not a login in the domain that can see the path given in the "-i" parameter, nothing you do with the parameter in OSQL can be made to work.
In Enterprise Manager, right click on the server, select [Properties], select the [Security] tab, and change the [Startup Service Account] to [This Account] and fill in the correct login name and password. It should be one that doesn't change because, if it changes and you reboot the server, the service will not start and you need to do a little damage control to fix it.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply