Problem with script

We've got a couple of scripts that we routinely run on several SQL
servers, and they usually run fine. But we're trying to run them on one
particular server, and we're running into problems. I'm no expert with
SQL, but two DBA's who I work with have spent hours on this and they're
stumped, so I don't feel so stupid. Maybe someone here can help.

Basically, we've got a .bat file that calls a .sql file. Within the
..bat file, isql is called a couple of times with no problems. Within
the .sql file, there are several lines like:

exec master..xp_cmdshell @@cmd

@@cmd has previously been defined as a text string that begins with
isql.

Whenever this runs, we get the error:

'isql' is not recognized as an internal or external command,
operable program or batch file.

In other words, it can't find the file isql.exe.

We already made sure that the Windows PATH variable for the user
running the .bat file includes the folder that has isql.exe in it.
Someone said that they once had a problem where that folder had to be
the first folder in the PATH variable in order to recognize isql, so we
even made that change (and logged off and back in, then checked the
path from a DOS prompt to make sure the change took effect).

Anyone know anything else to check that would explain why our sql
script would be unable to find the isql.exe command? Any help would be
greatly appreciated.

--Richard

re: Problem with script

Ok, I've got another detail that I was just looking at that seems like
it might be relevant. The initial .bat file uses isql to run the .sql
file. So maybe that has something to do with why the .sql file (run
from within isql) can't access isql? As I said, this works on most
other servers, so it's got to be some sort of security or something.

Would it the .sql file run from within isql use the same user security
as the .bat file that ran it? Or does it have seperate user access?

--Richard

re: Problem with script

blueghost73@yahoo.com (blueghost73@yahoo.com) writes:[color=blue]
> Whenever this runs, we get the error:
>
> 'isql' is not recognized as an internal or external command,
> operable program or batch file.
>
> In other words, it can't find the file isql.exe.
>
> We already made sure that the Windows PATH variable for the user
> running the .bat file includes the folder that has isql.exe in it.[/color]

That's does count here. What counts is how the PATH for the user
under which SQL Server itself looks like.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

re: Problem with script

Erland Sommarskog wrote:[color=blue]
> blueghost73@yahoo.com (blueghost73@yahoo.com) writes:[color=green]
> > Whenever this runs, we get the error:
> >
> > 'isql' is not recognized as an internal or external command,
> > operable program or batch file.
> >
> > In other words, it can't find the file isql.exe.
> >
> > We already made sure that the Windows PATH variable for the user
> > running the .bat file includes the folder that has isql.exe in it.[/color]
>
> That's does count here. What counts is how the PATH for the user
> under which SQL Server itself looks like.
>[/color]

That's the part I'm having trouble figuring out, I think. What user's
PATH would it be using when calling isql from within a .sql script that
was run by isql within a .bat file that was kicked off by a user?
Apparently, it's not using the PATH of the user who ran the .bat file.
I just don't know what user's PATH is being used.

As a temporary solution, I copied isql.exe to C:\Windows\System32,
since I know that'll be in the PATH of all users, and that worked.

--Richard

re: Problem with script

blueghost73@yahoo.com (blueghost73@yahoo.com) writes:[color=blue]
> That's the part I'm having trouble figuring out, I think. What user's
> PATH would it be using when calling isql from within a .sql script that
> was run by isql within a .bat file that was kicked off by a user?
> Apparently, it's not using the PATH of the user who ran the .bat file.
> I just don't know what user's PATH is being used.[/color]

Just like any other Windows process, SQL Server logs into to Windows
with a user and a password. Ot it logs in as LocalSystem.

To find out how SQL Server, do this on the server machine: In Control
Panel find Administrative Tools. Go there, and find the Services applet.
Open Services, and find the SQL Server service. Double-click it to see
Properties. Log On information is on the second tab.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

re: Problem with script

Erland Sommarskog wrote:[color=blue]
> blueghost73@yahoo.com (blueghost73@yahoo.com) writes:[color=green]
> > That's the part I'm having trouble figuring out, I think. What user's
> > PATH would it be using when calling isql from within a .sql script that
> > was run by isql within a .bat file that was kicked off by a user?
> > Apparently, it's not using the PATH of the user who ran the .bat file.
> > I just don't know what user's PATH is being used.[/color]
>
> Just like any other Windows process, SQL Server logs into to Windows
> with a user and a password. Ot it logs in as LocalSystem.
>
> To find out how SQL Server, do this on the server machine: In Control
> Panel find Administrative Tools. Go there, and find the Services applet.
> Open Services, and find the SQL Server service. Double-click it to see
> Properties. Log On information is on the second tab.
>
>[/color]

Because I know that the .bat file has the security access of the person
who started it, I thought the sql script run by the bat file would, as
well. But apparently, it always has the security access of the SQL
service, which I didn't realize, but it makes perfect sense. Thanks for
the clarification.

I checked, and the SQL service does log on as LocalSystem. So now for
the next obvious question: Since LocalSystem isn't a real user, how do
I change its PATH variable to include the folder that contains isql.exe
and other necessary SQL commands? I've hunted around a little, and I
haven't been able to find anything on changing the settings of
LocalSystem. I only know how to change the PATH of the username that
I'm logged in as.

--Richard

re: Problem with script

blueghost73@yahoo.com (blueghost73@yahoo.com) writes:[color=blue]
> I checked, and the SQL service does log on as LocalSystem. So now for
> the next obvious question: Since LocalSystem isn't a real user, how do
> I change its PATH variable to include the folder that contains isql.exe
> and other necessary SQL commands? I've hunted around a little, and I
> haven't been able to find anything on changing the settings of
> LocalSystem. I only know how to change the PATH of the username that
> I'm logged in as.[/color]

How to change the path for LocalSystem sounds like a question for
a Windows newsgroup. :-)

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp