Code runs in Query Analyzer... but not as a job

Question

Post reply

Code runs in Query Analyzer... but not as a job

rummings

SSCarpal Tunnel

Points: 4575
More actions
July 25, 2012 at 12:21 pm

#264685

A developer showed me a very small sql server t-sql script he wrote (5 SQL lines). It loads information into a SQL Server 2005 table. If he runs the script in Query Analyzer.... it runs and populates the table. He then deletes the table records... and runs the same script as a job in SQL Server.... it says it ran successfully... but no records appear in the table. Has anyone else seen this?
Thank you in advance!
Charlie

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Okay, what would you like us to do? We, unfortunately, can't see what you see.

rummings SSCarpal Tunnel Points: 4575 More actions · Answer 2

Hi Lynn,

I was just curious if anyone had seen this before. I am one of those accidental DBA's and I do not troubleshoot security errors often. It seemed like a persmission situation being as when ran as a job ... it did not write to the table, but job says it finished successfully. I guess it successfully failed.

Charlie

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Without seeing the code, we can't really give an answer.

Steve Jones - SSC Editor SSC Guru Points: 736139 More actions · Answer 4

Could be permissions. If the job didn't fail, perhaps it's not dependent on local resources.

You'd have to show more details. It's certainly possible this is the case. Also possible the testing was on a different data set?

rummings SSCarpal Tunnel Points: 4575 More actions · Answer 5

Hi Lynn,

If this helps... great... if not... I do appreciate your time looking into this.

Charlie

DECLARE @rc int

DECLARE @File_Exists int

DECLARE @Line_Nbr int

-- TODO: Set parameter values here.

EXECUTE @rc = [TUMBA].[TUMBA].[loadTumbaBioDemoSource]

@File_Exists

,@Line_Nbr

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

rummings (7/25/2012)
Hi Lynn,
If this helps... great... if not... I do appreciate your time looking into this.
Charlie
DECLARE @rc int
DECLARE @File_Exists int
DECLARE @Line_Nbr int
-- TODO: Set parameter values here.
EXECUTE @rc = [TUMBA].[TUMBA].[loadTumbaBioDemoSource]
@File_Exists
,@Line_Nbr

Actually, it doesn't as this is executing a stored procedure. What may be happening at this point could be permissions issue.

cphite SSCarpal Tunnel Points: 4278 More actions · Answer 7

rummings (7/25/2012)
A developer showed me a very small sql server t-sql script he wrote (5 SQL lines). It loads information into a SQL Server 2005 table. If he runs the script in Query Analyzer.... it runs and populates the table. He then deletes the table records... and runs the same script as a job in SQL Server.... it says it ran successfully... but no records appear in the table. Has anyone else seen this?
Thank you in advance!
Charlie

As has already been pointed out, you've basically given us nothing to work with here... we can't see what your code is actually doing. If you gave us the code behind the stored procedure, that would be a start.

That said - and this is entirely based on a guess - in the step of your job that actually runs the stored procedure, check to see what database it's running against. It usually defaults to master. See if your master database on that server contains a table with the name of the one you're trying to fill; and if it contains the data you're expecting to see.