Problems Running a good stored procedure as a job

[Tom Bartos]

I wrote a Stored Procedure I wish to run as a job. It inserts data to a linked server. The stored procedure runs fine from the sql query analyzer but fails as a job. There are no permissions assigned to this stored procedure as I beleive it runs under the context of sa which has default access granted.

Can someone give me some insite why this stored procedure won't run as a scheduled job?

ALTER PROCEDURE tsul_insertintolinkedserver
AS
DECLARE @srvname varChar(20)
SELECT @srvname = @@servername
insert into THOMAS.tsnet.dbo.usagelog
select id, tsgroup, account, error, failedpin, type, servername, ipbrowser, cid, logintime, expand , msgspresent, msgslistened, @srvname
from usagelog
where id >
( select max(id) from THOMAS.tsnet.dbo.usagelog
where hostserver = @srvname
)

Thanks in advance-

[Ray Miao]

Do you have detailed error message?


------------
Tom Bartos at 12/13/99 1:27:49 AM

I wrote a Stored Procedure I wish to run as a job. It inserts data to a linked server. The stored procedure runs fine from the sql query analyzer but fails as a job. There are no permissions assigned to this stored procedure as I beleive it runs under the context of sa which has default access granted.

Can someone give me some insite why this stored procedure won't run as a scheduled job?

ALTER PROCEDURE tsul_insertintolinkedserver
AS
DECLARE @srvname varChar(20)
SELECT @srvname = @@servername
insert into THOMAS.tsnet.dbo.usagelog
select id, tsgroup, account, error, failedpin, type, servername, ipbrowser, cid, logintime, expand , msgspresent, msgslistened, @srvname
from usagelog
where id >
( select max(id) from THOMAS.tsnet.dbo.usagelog
where hostserver = @srvname
)

Thanks in advance-

[Tom Bartos]

The error message doesn't help alot but here it is.

SQL Server Scheduled Job tsul_insertintolinkedserver' (0xF1DFC3FC6CA6D311A78B00105A5EAA5F) - Status: Failed - Invoked on: 12/12/99 9:40:52 PM - Message: The job failed. The Job was invoked by User sa. The last step to run was step 1 (tsul_insertintolinkedserver).
------------
Ray Miao at 12/13/99 8:36:23 AM

Do you have detailed error message?


------------
Tom Bartos at 12/13/99 1:27:49 AM

I wrote a Stored Procedure I wish to run as a job. It inserts data to a linked server. The stored procedure runs fine from the sql query analyzer but fails as a job. There are no permissions assigned to this stored procedure as I beleive it runs under the context of sa which has default access granted.

Can someone give me some insite why this stored procedure won't run as a scheduled job?

ALTER PROCEDURE tsul_insertintolinkedserver
AS
DECLARE @srvname varChar(20)
SELECT @srvname = @@servername
insert into THOMAS.tsnet.dbo.usagelog
select id, tsgroup, account, error, failedpin, type, servername, ipbrowser, cid, logintime, expand , msgspresent, msgslistened, @srvname
from usagelog
where id >
( select max(id) from THOMAS.tsnet.dbo.usagelog
where hostserver = @srvname
)

Thanks in advance-

[k]

my 2 cents.........
not sure but did you check the simple stuff.... on the STEPS dialog box, the Database name is correct, and the type is "Transact-SQL"

-k


------------
Tom Bartos at 12/13/99 9:08:53 AM

The error message doesn't help alot but here it is.

SQL Server Scheduled Job tsul_insertintolinkedserver' (0xF1DFC3FC6CA6D311A78B00105A5EAA5F) - Status: Failed - Invoked on: 12/12/99 9:40:52 PM - Message: The job failed. The Job was invoked by User sa. The last step to run was step 1 (tsul_insertintolinkedserver).
------------
Ray Miao at 12/13/99 8:36:23 AM

Do you have detailed error message?


------------
Tom Bartos at 12/13/99 1:27:49 AM

I wrote a Stored Procedure I wish to run as a job. It inserts data to a linked server. The stored procedure runs fine from the sql query analyzer but fails as a job. There are no permissions assigned to this stored procedure as I beleive it runs under the context of sa which has default access granted.

Can someone give me some insite why this stored procedure won't run as a scheduled job?

ALTER PROCEDURE tsul_insertintolinkedserver
AS
DECLARE @srvname varChar(20)
SELECT @srvname = @@servername
insert into THOMAS.tsnet.dbo.usagelog
select id, tsgroup, account, error, failedpin, type, servername, ipbrowser, cid, logintime, expand , msgspresent, msgslistened, @srvname
from usagelog
where id >
( select max(id) from THOMAS.tsnet.dbo.usagelog
where hostserver = @srvname
)

Thanks in advance-

[Ray Miao]

Find detailed error message in sql server agent errorlog and/or steps detail page.


------------
k at 12/13/99 9:39:11 AM

my 2 cents.........
not sure but did you check the simple stuff.... on the STEPS dialog box, the Database name is correct, and the type is "Transact-SQL"

-k


------------
Tom Bartos at 12/13/99 9:08:53 AM

The error message doesn't help alot but here it is.

SQL Server Scheduled Job tsul_insertintolinkedserver' (0xF1DFC3FC6CA6D311A78B00105A5EAA5F) - Status: Failed - Invoked on: 12/12/99 9:40:52 PM - Message: The job failed. The Job was invoked by User sa. The last step to run was step 1 (tsul_insertintolinkedserver).
------------
Ray Miao at 12/13/99 8:36:23 AM

Do you have detailed error message?


------------
Tom Bartos at 12/13/99 1:27:49 AM

I wrote a Stored Procedure I wish to run as a job. It inserts data to a linked server. The stored procedure runs fine from the sql query analyzer but fails as a job. There are no permissions assigned to this stored procedure as I beleive it runs under the context of sa which has default access granted.

Can someone give me some insite why this stored procedure won't run as a scheduled job?

ALTER PROCEDURE tsul_insertintolinkedserver
AS
DECLARE @srvname varChar(20)
SELECT @srvname = @@servername
insert into THOMAS.tsnet.dbo.usagelog
select id, tsgroup, account, error, failedpin, type, servername, ipbrowser, cid, logintime, expand , msgspresent, msgslistened, @srvname
from usagelog
where id >
( select max(id) from THOMAS.tsnet.dbo.usagelog
where hostserver = @srvname
)

Thanks in advance-

This seems to be a permissions error

Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. [SQLSTATE 42000] (Error 18452). The step failed.
------------
Ray Miao at 12/13/99 10:24:21 AM

Find detailed error message in sql server agent errorlog and/or steps detail page.


------------
k at 12/13/99 9:39:11 AM

my 2 cents.........
not sure but did you check the simple stuff.... on the STEPS dialog box, the Database name is correct, and the type is "Transact-SQL"

-k


------------
Tom Bartos at 12/13/99 9:08:53 AM

The error message doesn't help alot but here it is.

SQL Server Scheduled Job tsul_insertintolinkedserver' (0xF1DFC3FC6CA6D311A78B00105A5EAA5F) - Status: Failed - Invoked on: 12/12/99 9:40:52 PM - Message: The job failed. The Job was invoked by User sa. The last step to run was step 1 (tsul_insertintolinkedserver).
------------
Ray Miao at 12/13/99 8:36:23 AM

Do you have detailed error message?


------------
Tom Bartos at 12/13/99 1:27:49 AM

I wrote a Stored Procedure I wish to run as a job. It inserts data to a linked server. The stored procedure runs fine from the sql query analyzer but fails as a job. There are no permissions assigned to this stored procedure as I beleive it runs under the context of sa which has default access granted.

Can someone give me some insite why this stored procedure won't run as a scheduled job?

ALTER PROCEDURE tsul_insertintolinkedserver
AS
DECLARE @srvname varChar(20)
SELECT @srvname = @@servername
insert into THOMAS.tsnet.dbo.usagelog
select id, tsgroup, account, error, failedpin, type, servername, ipbrowser, cid, logintime, expand , msgspresent, msgslistened, @srvname
from usagelog
where id >
( select max(id) from THOMAS.tsnet.dbo.usagelog
where hostserver = @srvname
)

Thanks in advance-

[Tom Bartos]

This seems to be a permissions error. Which user is responsible for running jobs? I am assuming it is sa.

Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. [SQLSTATE 42000] (Error 18452). The step failed.
------------
Ray Miao at 12/13/99 10:24:21 AM

Find detailed error message in sql server agent errorlog and/or steps detail page.


------------
k at 12/13/99 9:39:11 AM

my 2 cents.........
not sure but did you check the simple stuff.... on the STEPS dialog box, the Database name is correct, and the type is "Transact-SQL"

-k


------------
Tom Bartos at 12/13/99 9:08:53 AM

The error message doesn't help alot but here it is.

SQL Server Scheduled Job tsul_insertintolinkedserver' (0xF1DFC3FC6CA6D311A78B00105A5EAA5F) - Status: Failed - Invoked on: 12/12/99 9:40:52 PM - Message: The job failed. The Job was invoked by User sa. The last step to run was step 1 (tsul_insertintolinkedserver).
------------
Ray Miao at 12/13/99 8:36:23 AM

Do you have detailed error message?


------------
Tom Bartos at 12/13/99 1:27:49 AM

I wrote a Stored Procedure I wish to run as a job. It inserts data to a linked server. The stored procedure runs fine from the sql query analyzer but fails as a job. There are no permissions assigned to this stored procedure as I beleive it runs under the context of sa which has default access granted.

Can someone give me some insite why this stored procedure won't run as a scheduled job?

ALTER PROCEDURE tsul_insertintolinkedserver
AS
DECLARE @srvname varChar(20)
SELECT @srvname = @@servername
insert into THOMAS.tsnet.dbo.usagelog
select id, tsgroup, account, error, failedpin, type, servername, ipbrowser, cid, logintime, expand , msgspresent, msgslistened, @srvname
from usagelog
where id >
( select max(id) from THOMAS.tsnet.dbo.usagelog
where hostserver = @srvname
)

Thanks in advance-

[Tom Bartos]

Thanks, for leading me down the road to knowledge

------------
Tom Bartos at 12/13/99 10:33:28 AM

This seems to be a permissions error. Which user is responsible for running jobs? I am assuming it is sa.

Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. [SQLSTATE 42000] (Error 18452). The step failed.
------------
Ray Miao at 12/13/99 10:24:21 AM

Find detailed error message in sql server agent errorlog and/or steps detail page.


------------
k at 12/13/99 9:39:11 AM

my 2 cents.........
not sure but did you check the simple stuff.... on the STEPS dialog box, the Database name is correct, and the type is "Transact-SQL"

-k


------------
Tom Bartos at 12/13/99 9:08:53 AM

The error message doesn't help alot but here it is.

SQL Server Scheduled Job tsul_insertintolinkedserver' (0xF1DFC3FC6CA6D311A78B00105A5EAA5F) - Status: Failed - Invoked on: 12/12/99 9:40:52 PM - Message: The job failed. The Job was invoked by User sa. The last step to run was step 1 (tsul_insertintolinkedserver).
------------
Ray Miao at 12/13/99 8:36:23 AM

Do you have detailed error message?


------------
Tom Bartos at 12/13/99 1:27:49 AM

I wrote a Stored Procedure I wish to run as a job. It inserts data to a linked server. The stored procedure runs fine from the sql query analyzer but fails as a job. There are no permissions assigned to this stored procedure as I beleive it runs under the context of sa which has default access granted.

Can someone give me some insite why this stored procedure won't run as a scheduled job?

ALTER PROCEDURE tsul_insertintolinkedserver
AS
DECLARE @srvname varChar(20)
SELECT @srvname = @@servername
insert into THOMAS.tsnet.dbo.usagelog
select id, tsgroup, account, error, failedpin, type, servername, ipbrowser, cid, logintime, expand , msgspresent, msgslistened, @srvname
from usagelog
where id >
( select max(id) from THOMAS.tsnet.dbo.usagelog
where hostserver = @srvname
)

Thanks in advance-