Unanswered: Accessing script file on local computer
I am trying to create a sproc creates several tables, views, and sprocs across several similar DBs (please...no lectures on structure...I didn't create this multi-DB setup) .
My sproc loops through the DBs and runs a script on each DB. I am running the Sproc from a client computer and cannot easily access the c drive on the SQL server.
The only way I have figured out how do do this is to run the following
xp_cmdshell 'isql d- %@mydb% i- c:\script.sql'
inside a loop.
My problem is that xp_cmdshell refers to the server c drive instead of my local c drive, where my script file is.
Is there a way to refer to my local c drive? Also any thoughts on a better way to approach this (other than running the entire script inline in the sproc since I don't want to have to run quotes around each batch of the script).
What Satya is suggesting is actually a simple concept, it is just hard to explain in plain English.
Depending on how your SQL Server is configured, any command run using xp_cmdshell will be run as though it were done by one of two NT logins. In most cases, the command runs as though it were started by the NT user that SQL Server uses.
If the NT user that appears to run the command has permission, you can use a UNC for the script. This will "reach across the network" to get the script file, even though the script actually executes on the server itself.