Job truncates text data type

I have a table with a text data type and when I run my sql manually,
everything works fine - the text column is completely filled with what
I need (it's html from a http post request via a stored procedure).
When I put the exact same SQL inside a job, and kick the job off (or
let the agent pick it up on the schedule), it truncates the text column
to a width of 498, rendering my later job steps useless b/c the
expected data isn't there.
Why would the job cutoff the text in this column?What is the syntax used in the proc? Are you issuing an update/insert
or a writetext/updatetext statement?

Job truncates text data type

I have a table with a text data type and when I run my sql manually,
everything works fine - the text column is completely filled with what
I need (it's html from a http post request via a stored procedure).
When I put the exact same SQL inside a job, and kick the job off (or
let the agent pick it up on the schedule), it truncates the text column
to a width of 498, rendering my later job steps useless b/c the
expected data isn't there.
Why would the job cutoff the text in this column?What is the syntax used in the proc? Are you issuing an update/insert
or a writetext/updatetext statement?

Job succeeds manually but fails if scheduled

My client has a number of jobs that are run overnight. We've set them
up to email me when they're completed. Every morning I get in to a
bunch of emails like this:

<quote
JOB RUN:'Tech Pubs Email Notification' was run on 18/03/2006 at
00:00:00
DURATION:0 hours, 0 minutes, 0 seconds
STATUS: Succeeded
MESSAGES:The job succeeded. The Job was invoked by Schedule 10 (Send
Mail). The last step to run was step 1 (Send Mail).
</quote
However, the most important job - the database backup - fails every
time.

<quote>
JOB RUN:'DB Backup Job for DB Maintenance Plan 'DB Maintenance Plan1''
was run on 20/03/2006 at 18:00:00
DURATION:0 hours, 0 minutes, 2 seconds
STATUS: Failed
MESSAGES:The job failed. The Job was invoked by Schedule 7 (Schedule
1). The last step to run was step 1 (Step 1).
</quote
What's strange is that the job runs successfully if you kick it off
manually (in EM: right-click and "Start Job")!!! Does anyone have any
idea of why that might be? Where to look for diagnostic information?

TIA

Edwardteddysnips@.hotmail.com wrote:
> My client has a number of jobs that are run overnight. We've set them
> up to email me when they're completed. Every morning I get in to a
> bunch of emails like this:
> <quote>
> JOB RUN:'Tech Pubs Email Notification' was run on 18/03/2006 at
> 00:00:00
> DURATION:0 hours, 0 minutes, 0 seconds
> STATUS: Succeeded
> MESSAGES:The job succeeded. The Job was invoked by Schedule 10 (Send
> Mail). The last step to run was step 1 (Send Mail).
> </quote>
> However, the most important job - the database backup - fails every
> time.
> <quote>
> JOB RUN:'DB Backup Job for DB Maintenance Plan 'DB Maintenance Plan1''
> was run on 20/03/2006 at 18:00:00
> DURATION:0 hours, 0 minutes, 2 seconds
> STATUS: Failed
> MESSAGES:The job failed. The Job was invoked by Schedule 7 (Schedule
> 1). The last step to run was step 1 (Step 1).
> </quote>
> What's strange is that the job runs successfully if you kick it off
> manually (in EM: right-click and "Start Job")!!! Does anyone have any
> idea of why that might be? Where to look for diagnostic information?
> TIA
> Edward

Edward,

Have you got the right permissions?

This might sound a bit obvious but its usually the case. You might not
be the owner of the package and the system will only schedule the job
to run if the permissions are correct.

Bryan|||Bryan wrote:
> teddysnips@.hotmail.com wrote:
[...]
>
> Edward,
> Have you got the right permissions?
> This might sound a bit obvious but its usually the case. You might not
> be the owner of the package and the system will only schedule the job
> to run if the permissions are correct.
> Bryan

I would have assumed so, but I'll check. Thanks for the suggestion.

Edward|||Bryan wrote:
[...]
> Edward,
> Have you got the right permissions?
> This might sound a bit obvious but its usually the case. You might not
> be the owner of the package and the system will only schedule the job
> to run if the permissions are correct.

I checked on this today. All the jobs that run correctly have exactly
the same owners/permissions as the job that fails. I can't find any
attribute of the job (apart from what it actually does, obviously) that
distinguishes it from any of the other, successful jobs.

One other thing of which I was not aware. Apparently this job had been
scheduled successfully up until about a week ago, when it began
failing.

Any further thoughts/ideas?

TIA

Edward|||Hi Edward,

To be honest I don't ( I hate admitting defeat though....)

I have encountered this problem in the past especially when picking up
from any previous owners of packages.
One solution I have done is to copy the code from one package and dump
it into a package that is being allowed to run but with a different
owner.
This usually confirmed to myself that the problem must a a Owner /
Permission issue.

Apart from that I'm at a loss to recommend any other course of action.|||(teddysnips@.hotmail.com) writes:
> I checked on this today. All the jobs that run correctly have exactly
> the same owners/permissions as the job that fails. I can't find any
> attribute of the job (apart from what it actually does, obviously) that
> distinguishes it from any of the other, successful jobs.
> One other thing of which I was not aware. Apparently this job had been
> scheduled successfully up until about a week ago, when it began
> failing.
> Any further thoughts/ideas?

Check View History for the job. Don't miss to check View Step History.

Unfortunately, jobs that set up from a maintenance plan does not seem to
write very much useful information, so I'm not really expecting this to
give you anything. (But check nevertheless.) I the maintenance plan has
its own log somewhere, but I don't remember where - or if there was anything
that useful in it.

May you should scrap the plan, and set up the jobs without it.

--
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|||<teddysnips@.hotmail.com> wrote in message
news:1142932717.825816.151430@.z34g2000cwc.googlegr oups.com...
> My client has a number of jobs that are run overnight. We've set them
> up to email me when they're completed. Every morning I get in to a
> bunch of emails like this:
> <quote>
> JOB RUN: 'Tech Pubs Email Notification' was run on 18/03/2006 at
> 00:00:00
> DURATION: 0 hours, 0 minutes, 0 seconds
> STATUS: Succeeded
> MESSAGES: The job succeeded. The Job was invoked by Schedule 10 (Send
> Mail). The last step to run was step 1 (Send Mail).
> </quote>
> However, the most important job - the database backup - fails every
> time.
> <quote>
> JOB RUN: 'DB Backup Job for DB Maintenance Plan 'DB Maintenance Plan1''
> was run on 20/03/2006 at 18:00:00
> DURATION: 0 hours, 0 minutes, 2 seconds
> STATUS: Failed
> MESSAGES: The job failed. The Job was invoked by Schedule 7 (Schedule
> 1). The last step to run was step 1 (Step 1).
> </quote>
> What's strange is that the job runs successfully if you kick it off
> manually (in EM: right-click and "Start Job")!!! Does anyone have any
> idea of why that might be? Where to look for diagnostic information?
> TIA
> Edward

If it isn't the owner of the job step (sa would be good if sa owns the
database), then check for proper disk
space in the backup destination.

Job Steps Displayed Out of Order

When selecting to start a job manually, the job steps
appear out of order (step 3 is above steps 1 and 2 and
the like).
Any ideas what is causing this, and how to correct it?And, as I said, I don't see this and can't reproduce it. So something must
be different about *your* job, which is why I asked to see the SQL script.
We're not going to be able to guess at what's wrong unless we actually have
a script that we can use to reproduce your scenario!
"Arbiter Becker" <absql@.hotmail.com> wrote in message
news:0b7e01c356a5$12262c30$a601280a@.phx.gbl...
> Note;
> The job runs fine, if running off a schedule, and is
> displayed correctly if looking at the individual job
> steps. It is only when doing the manual start option for
> the job does it display incorrectly.
>
> >--Original Message--
> >When selecting to start a job manually, the job steps
> >appear out of order (step 3 is above steps 1 and 2 and
> >the like).
> >
> >Any ideas what is causing this, and how to correct it?
> >.
> >|||Hi Arbiter,
When you say step 3, 2, 1..., do you refer to the ID shown on Steps tab, or
the number prefix you may have added in the name of each job step?
If you click on "Move step" (up and down arrows), the ID always stays in
ascending order but the actual job steps move.
Richard
"Arbiter Becker" <absql@.hotmail.com> wrote in message
news:0da801c3569f$f4d71130$a101280a@.phx.gbl...
> When selecting to start a job manually, the job steps
> appear out of order (step 3 is above steps 1 and 2 and
> the like).
> Any ideas what is causing this, and how to correct it?

Job History is not showing

Using Sql 2005, I have a job that will not display job history when it runs
at the schedule time. It will show job history if you manually run the job.
Any thoughts?
Are you sure that the history settings are not such that it gets purged by
the time you view it?
Andrew J. Kelly SQL MVP
"Cindy B" <CindyB@.discussions.microsoft.com> wrote in message
news:EE87736D-8DC0-4646-B67F-FF2477DF8ABA@.microsoft.com...
> Using Sql 2005, I have a job that will not display job history when it
> runs
> at the schedule time. It will show job history if you manually run the
> job.
> Any thoughts?
|||The Limit Size of Job History Log is checked with Max Log size of 1000 and
Max Job history is 105. When I looked at the job history for all it showed
only 212 entries.
This particular server is the distributor. The only jobs on the server that
do show history deal with the replication.
"Andrew J. Kelly" wrote:

> Are you sure that the history settings are not such that it gets purged by
> the time you view it?
> --
> Andrew J. Kelly SQL MVP
> "Cindy B" <CindyB@.discussions.microsoft.com> wrote in message
> news:EE87736D-8DC0-4646-B67F-FF2477DF8ABA@.microsoft.com...
>
>
|||OK my guess is that the history limit is beeing exceeded by one of the jobs
that runs say every minute or so. You can try bumping up the history limits
but it depends on how often the other jobs run.
Andrew J. Kelly SQL MVP
"Cindy B" <CindyB@.discussions.microsoft.com> wrote in message
news:A1B7DE7A-B3CD-4C05-B971-46A00161624B@.microsoft.com...[vbcol=seagreen]
> The Limit Size of Job History Log is checked with Max Log size of 1000 and
> Max Job history is 105. When I looked at the job history for all it
> showed
> only 212 entries.
> This particular server is the distributor. The only jobs on the server
> that
> do show history deal with the replication.
> "Andrew J. Kelly" wrote:

Job History is not showing

Using Sql 2005, I have a job that will not display job history when it runs
at the schedule time. It will show job history if you manually run the job.
Any thoughts?Are you sure that the history settings are not such that it gets purged by
the time you view it?
Andrew J. Kelly SQL MVP
"Cindy B" <CindyB@.discussions.microsoft.com> wrote in message
news:EE87736D-8DC0-4646-B67F-FF2477DF8ABA@.microsoft.com...
> Using Sql 2005, I have a job that will not display job history when it
> runs
> at the schedule time. It will show job history if you manually run the
> job.
> Any thoughts?|||The Limit Size of Job History Log is checked with Max Log size of 1000 and
Max Job history is 105. When I looked at the job history for all it showed
only 212 entries.
This particular server is the distributor. The only jobs on the server that
do show history deal with the replication.
"Andrew J. Kelly" wrote:

> Are you sure that the history settings are not such that it gets purged by
> the time you view it?
> --
> Andrew J. Kelly SQL MVP
> "Cindy B" <CindyB@.discussions.microsoft.com> wrote in message
> news:EE87736D-8DC0-4646-B67F-FF2477DF8ABA@.microsoft.com...
>
>|||OK my guess is that the history limit is beeing exceeded by one of the jobs
that runs say every minute or so. You can try bumping up the history limits
but it depends on how often the other jobs run.
Andrew J. Kelly SQL MVP
"Cindy B" <CindyB@.discussions.microsoft.com> wrote in message
news:A1B7DE7A-B3CD-4C05-B971-46A00161624B@.microsoft.com...[vbcol=seagreen]
> The Limit Size of Job History Log is checked with Max Log size of 1000 and
> Max Job history is 105. When I looked at the job history for all it
> showed
> only 212 entries.
> This particular server is the distributor. The only jobs on the server
> that
> do show history deal with the replication.
> "Andrew J. Kelly" wrote:
>

Job History is not showing

Using Sql 2005, I have a job that will not display job history when it runs
at the schedule time. It will show job history if you manually run the job.
Any thoughts?Are you sure that the history settings are not such that it gets purged by
the time you view it?
--
Andrew J. Kelly SQL MVP
"Cindy B" <CindyB@.discussions.microsoft.com> wrote in message
news:EE87736D-8DC0-4646-B67F-FF2477DF8ABA@.microsoft.com...
> Using Sql 2005, I have a job that will not display job history when it
> runs
> at the schedule time. It will show job history if you manually run the
> job.
> Any thoughts?|||The Limit Size of Job History Log is checked with Max Log size of 1000 and
Max Job history is 105. When I looked at the job history for all it showed
only 212 entries.
This particular server is the distributor. The only jobs on the server that
do show history deal with the replication.
"Andrew J. Kelly" wrote:
> Are you sure that the history settings are not such that it gets purged by
> the time you view it?
> --
> Andrew J. Kelly SQL MVP
> "Cindy B" <CindyB@.discussions.microsoft.com> wrote in message
> news:EE87736D-8DC0-4646-B67F-FF2477DF8ABA@.microsoft.com...
> > Using Sql 2005, I have a job that will not display job history when it
> > runs
> > at the schedule time. It will show job history if you manually run the
> > job.
> >
> > Any thoughts?
>
>|||OK my guess is that the history limit is beeing exceeded by one of the jobs
that runs say every minute or so. You can try bumping up the history limits
but it depends on how often the other jobs run.
--
Andrew J. Kelly SQL MVP
"Cindy B" <CindyB@.discussions.microsoft.com> wrote in message
news:A1B7DE7A-B3CD-4C05-B971-46A00161624B@.microsoft.com...
> The Limit Size of Job History Log is checked with Max Log size of 1000 and
> Max Job history is 105. When I looked at the job history for all it
> showed
> only 212 entries.
> This particular server is the distributor. The only jobs on the server
> that
> do show history deal with the replication.
> "Andrew J. Kelly" wrote:
>> Are you sure that the history settings are not such that it gets purged
>> by
>> the time you view it?
>> --
>> Andrew J. Kelly SQL MVP
>> "Cindy B" <CindyB@.discussions.microsoft.com> wrote in message
>> news:EE87736D-8DC0-4646-B67F-FF2477DF8ABA@.microsoft.com...
>> > Using Sql 2005, I have a job that will not display job history when it
>> > runs
>> > at the schedule time. It will show job history if you manually run the
>> > job.
>> >
>> > Any thoughts?
>>sql

job fails through scheduler

I run a DTS job manually, it works fine woth no proble.
However, if I schedule it to run, it always fails
with the following message: ( in the Event Viewer )
Event Type: Warning
Description:
SQL Server Scheduled Job 'BackorderByItemReport'
(0x6C0FDB5A6F7B6149AB8565F9F349FDE4) - Status: Failed -
Invoked on: 2003-09-09 14:44:00 - Message: The job
failed. The Job was invoked by Schedule 2
(BackorderByItemReport). The last step to run was step 1
(BackorderByItemReport).
Since the message doesn't provide enough info, I have no
idea about what's wrong. BTW, I have set up the
SQLServerAgent service running as a network user who is
also a member of the Local System Admin Group.
Has anybody experienced this?
Thanx.
Y.J.Yanjie,
INF: How to Run a DTS Package as a Scheduled Job
http://support.microsoft.com/default.aspx?scid=kb;en-us;269074
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Yanjie Ren" <yren@.cc3.com> wrote in message
news:640d01c377aa$773abe30$a501280a@.phx.gbl...
> I run a DTS job manually, it works fine woth no proble.
> However, if I schedule it to run, it always fails
> with the following message: ( in the Event Viewer )
> Event Type: Warning
> Description:
> SQL Server Scheduled Job 'BackorderByItemReport'
> (0x6C0FDB5A6F7B6149AB8565F9F349FDE4) - Status: Failed -
> Invoked on: 2003-09-09 14:44:00 - Message: The job
> failed. The Job was invoked by Schedule 2
> (BackorderByItemReport). The last step to run was step 1
> (BackorderByItemReport).
> Since the message doesn't provide enough info, I have no
> idea about what's wrong. BTW, I have set up the
> SQLServerAgent service running as a network user who is
> also a member of the Local System Admin Group.
> Has anybody experienced this?
> Thanx.
> Y.J.
>

Job Fails

I have a package that runs fine when manually executed, but if a job is created that executes the package the job fails. The job is run as the same account that runs sql server agent. The error message that appears is to see the job history but the job history just says the package failed. When I try to add logging no log is created.When you manually execute the package are you executing it under the same account that Agent is running. If not, then try and do that and see if the package runs. If it doesn't then it would most likely be a permissions problem for that account. If it does then you should try and post this in the Agent forum.

Thanks,
Matt|||

I get this exact same problem... but only with one specific package.
Other packages run fine - both manually and as jobs.

This particular package runs fine manually but NOT as job.
This package only has file tasks which copies file from a remote location to local disk. I have double-checked permissions. The account which has permission on the remote location is the same account under which SQL Server, and SQL Agent are running.

Any clue?

thanks,
Nitesh

|||So you are running both SQLServer and Agent as a domain account and the same domain acount has permissions on the remote server? Then I don't have any further thoughts but you should first verify that if you use runas to run a cmd shell under the agent account that the package still works. You should verify that the job isn't running under a proxy with a different account. If both these are true then you should post in the Agent forum since this is not a SSIS problem if the package runs when not run under Agent.

Sorry I can't be of more assistance,
Matt|||

Yes, I am running both SQLServer and Agent as a domain account and the same domain acount has permissions on the remote server.

I have 16 packages scheduled like this. 15 of them work fine (none of them copy files from remote location). Only this package breaks.

I will try to post this on Agent forum and update this thread with replies.

thanks,
Nitesh

|||Matt,

I found the problem.

I had mapped the network share (\\servername\sharename) to X:\ on the box running SSIS. So, my file source connection strings looked like X:\filename.txt

When I changed the file source connection string to \\servername\sharename\filename.txt , then it started working fine - both manually AND as a job.

Although my problem is solved, can you throw some light on why it happens like this?

thanks,
Nitesh|||

How are you mapping to X:? Are you executing the package on a different machine when you execute it under a job? Sounds like a problem with the context of the machine; doesn't have the mapping. So, if it doesn't have the mapping, it will be looking for the literal X: drive.
K

|||

Nitesh Ambastha wrote:

I had mapped the network share (\\servername\sharename) to X:\ on the box running SSIS. So, my file source connection strings looked like X:\filename.txt

When I changed the file source connection string to \\servername\sharename\filename.txt , then it started working fine - both manually AND as a job.

Although my problem is solved, can you throw some light on why it happens like this?

This is the way share security works in Windows 2003 - the network share mapped by user is private to this user session, and is invisible to other users running on same machine.

E.g. I mapped a disk using 'net share x: \\machine\share' and now have a drive x:. But when I (simultaneously) create another remote desktop connection to the same machine, I don't have x: drive.

The Agent creates a new user session, and thus this session does not see the share created by interactive user session.|||Thanks Michael.
That explains it.

regards,
Nitesh|||This is really not important but after reading your post, I created 1 desktop connection, mapped the network drive (X: drive to \\remoteserver\sharename) and then created another desktop connection to the same machine - and I found the mapped network drive (X: drive). Am I missing something or maybe network admins here carry over my profile and map it for me :(

regards,
Nitesh|||Did you check 'reconnect at logon' option (or /persistent:yes in net.exe command line)? In this case every logon creates a connection, but technically they are different :)

Is this Windows 2003? I'm not sure it worked the same way on Windows 2000.

This is simple to check with SysInternal's WinObj utility - \DosDevices object used to live in root, but now it is \Sessions\<N>\DosDevices.