Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2002

    Wink Unanswered: Accessing script file on local computer

    I am trying to create a sproc creates several tables, views, and sprocs across several similar DBs ( 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).


  2. #2
    Join Date
    Aug 2002
    If the SQL service account has required privilege on your local drive then set the path of .SQL file with your computer name.
    --Satya SKJ
    Microsoft SQL Server MVP

  3. #3
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    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.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts