Hello,
I have a batch file in the operating system, which runs an osql command that
create a stored procedure, a view and a function on a database. It is with
-E trust connect. It works when I just run it on windows.
However, if I run in as a SQL Job by using the CmdExec, it could not create
any stored procedure, view or function. And there is no error return
neither. The account to run the job is the same NT account with local admin
rights on the server.
Does anyone have some idea about this matter?
Many Thanks,
LianneAre you running the Job from a different server?
If so you need to use -U and -P providing a username and password. I
have experienced this problem before, but apparently linked servers do
not pass the user via windows authentication.|||Lianne Kwock (LianneKwock@.discussions.microsoft.com) writes:
> I have a batch file in the operating system, which runs an osql command
> that create a stored procedure, a view and a function on a database. It
> is with -E trust connect. It works when I just run it on windows.
> However, if I run in as a SQL Job by using the CmdExec, it could not
> create any stored procedure, view or function. And there is no error
> return neither. The account to run the job is the same NT account with
> local admin rights on the server.
Did the job fail or succeed?
In case it failed, there is a checkbox in the upper right of the View
History dialog which says "Show job steps" or somesuch. The output from
the job step should have the real error.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||The job was running on the physical server. and the job return as
successful. but nothing was created on SQL Server side.
Thanks again,
Lianne
"Johnny D" wrote:
> Are you running the Job from a different server?
> If so you need to use -U and -P providing a username and password. I
> have experienced this problem before, but apparently linked servers do
> not pass the user via windows authentication.
>|||Hi John,
I change the -E to -U sa login, but I got this error message:
Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server
connection.
Any idea?
Many Thanks,
Lianne
"Johnny D" wrote:
> Are you running the Job from a different server?
> If so you need to use -U and -P providing a username and password. I
> have experienced this problem before, but apparently linked servers do
> not pass the user via windows authentication.
>|||Xref: TK2MSFTNGP01.phx.gbl microsoft.public.sqlserver.programming:605603
Lianne Kwock (LianneKwock@.discussions.microsoft.com) writes:
> I change the -E to -U sa login, but I got this error message:
> Login failed for user 'sa'. Reason: Not associated with a trusted SQL
> Server connection.
> Any idea?
That would indicate that SQL authentication is not enabled on the server.
Well, changing the autentication should not matter, so consider that a
dead end.
As for why your job does not work as expected, I'm afraid that with the
information you have provided, all we can offer is wild guesses. What
is the exact command line you are using?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Eriand,
the command line is below:
for %%f IN (*.sql) DO echo %%f >> Createcp.txt & osql -S LKWOCK-D1 -U sa -P
zhongshan -n -d test -i "%%f" >> Createcp.txt
this is the command I put in .bat file. on the same level, I have three sql
files, which creates a view, a user defined function and a stored procedure.
Also, I had set SQL Server running on both (SQL and NT authentication)
If you meed more informations, let me know.
Many Thanks,
Lianne
"Erland Sommarskog" wrote:
> Lianne Kwock (LianneKwock@.discussions.microsoft.com) writes:
> That would indicate that SQL authentication is not enabled on the server.
> Well, changing the autentication should not matter, so consider that a
> dead end.
> As for why your job does not work as expected, I'm afraid that with the
> information you have provided, all we can offer is wild guesses. What
> is the exact command line you are using?
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||Lianne Kwock (LianneKwock@.discussions.microsoft.com) writes:
> the command line is below:
> for %%f IN (*.sql) DO echo %%f >> Createcp.txt & osql -S LKWOCK-D1 -U
> sa -P zhongshan -n -d test -i "%%f" >> Createcp.txt
> this is the command I put in .bat file. on the same level, I have three
> sql files, which creates a view, a user defined function and a stored
> procedure.
Real stupid question: the BAT file does include a CD to the directory
where you have the SQL, doesn't it?
I composed this file on my machine:
F:
cd \moje dokumenty\WWW\dynsearch
for %%f IN (*.sp) DO echo %%f >> Createcp.txt & osql -E -n -d Northgale -i "
%%f" >> Createcp.txt
And the I created a job of which the command text was
C:\temp\test.bat
I deleted all stored procedure in Northgale, and then started the job.
When I had completed the job, all my procedures were back.
In job history I saw this for the job step:
Executed as user: KESMETS\sql. ...search
F:\Moje dokumenty\WWW\dynsearch>for %f IN (*.sp) DO echo %f 1>>Createcp.tx
t & osql -E -n -d Northgale -i "%f" 1>>Createcp.txt
F:\Moje dokumenty\WWW\dynsearch>echo search_orders_1.sp 1>>Createcp.txt &
osql -E -n -d Northgale -i "search_orders_1.sp" 1>>Createcp.txt
F:\Moje dokumenty\WWW\dynsearch>echo search_orders_10.sp 1>>Createcp.txt
& osql -E -n -d Northgale -i "search_orders_10.sp" 1>>Createcp.txt
F:\Moje dokumenty\WWW\dynsearch>echo search_orders_2.sp 1>>Createcp.txt &
osql -E -n -d Northgale -i "search_orders_2.sp" 1>>Createcp.txt
F:\Moje dokumenty\WWW\dynsearch>echo search_orders_3.sp 1>>Createcp.txt &
osql -E -n -d Northgale -i "search_orders_3.sp" 1>>Createcp.txt
F:\Moje dokumenty\WWW\dynsearch>echo search_orders_3a.sp 1>>Createcp.txt
& osql -E -n -d Northgale -i "search_orders_3a.sp" 1>>Createcp.txt
F:\Moje dokumenty\WWW\dynsearch>echo search_orders_4.sp 1>>Createcp.txt &
.. Process Exit Code 0. The step succeeded.
A way to get more output from the job step is to go the Advanced tab
for the job step, and there select a output file.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland,
Thank you very much for your suggection. I did not enclude the Cd line in
the .bat file since it work just fine when I run it through windows. My bad
.
I guess SQL is very piggy on directory specification then.
Thank you again. I appreciate your help very much!
Lianne
"Erland Sommarskog" wrote:
> Lianne Kwock (LianneKwock@.discussions.microsoft.com) writes:
> Real stupid question: the BAT file does include a CD to the directory
> where you have the SQL, doesn't it?
> I composed this file on my machine:
> F:
> cd \moje dokumenty\WWW\dynsearch
> for %%f IN (*.sp) DO echo %%f >> Createcp.txt & osql -E -n -d Northgale
-i "%%f" >> Createcp.txt
> And the I created a job of which the command text was
> C:\temp\test.bat
> I deleted all stored procedure in Northgale, and then started the job.
> When I had completed the job, all my procedures were back.
> In job history I saw this for the job step:
>
> Executed as user: KES?METS?\sql. ...search
> F:\Moje dokumenty\WWW\dynsearch>for %f IN (*.sp) DO echo %f 1>>Create
cp.txt & osql -E -n -d Northgale -i "%f" 1>>Createcp.txt
> F:\Moje dokumenty\WWW\dynsearch>echo search_orders_1.sp 1>>Createcp.t
xt & osql -E -n -d Northgale -i "search_orders_1.sp" 1>>Createcp.txt
> F:\Moje dokumenty\WWW\dynsearch>echo search_orders_10.sp 1>>Createcp.
txt & osql -E -n -d Northgale -i "search_orders_10.sp" 1>>Createcp.txt
> F:\Moje dokumenty\WWW\dynsearch>echo search_orders_2.sp 1>>Createcp.t
xt & osql -E -n -d Northgale -i "search_orders_2.sp" 1>>Createcp.txt
> F:\Moje dokumenty\WWW\dynsearch>echo search_orders_3.sp 1>>Createcp.t
xt & osql -E -n -d Northgale -i "search_orders_3.sp" 1>>Createcp.txt
> F:\Moje dokumenty\WWW\dynsearch>echo search_orders_3a.sp 1>>Createcp.
txt & osql -E -n -d Northgale -i "search_orders_3a.sp" 1>>Createcp.txt
> F:\Moje dokumenty\WWW\dynsearch>echo search_orders_4.sp 1>>Createcp.t
xt &... Process Exit Code 0. The step succeeded.
> A way to get more output from the job step is to go the Advanced tab
> for the job step, and there select a output file.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>
