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 to run a remote process

I have a question regard the scheduling of a database job. I want to run a job that executes the following steps:

1) backup an entire database from DB server A

2) restore the database on DB Sever B

3) run a non database process on Server C (with xp_cmdshell)

Step 1 and 2 are easy enough but how do I start a process on an remote machine (Step 3)?

In my case the process in step 3 is will execute a Cognos DecisionStream job (rundsjob.exe)

My environment is

SQL Server 2000.

Windows Server 2003

Paul

If you are trying to do something like run a batch file remotely, you may want to look at WMIC.

Prior to Windows 2003, you'd use RCMD. This link might help you get started:

http://www.microsoft.com/technet/prodtechnol/windows2000serv/maintain/featusability/wmic.mspx

-Sue

Job to Execute SSIS package fails

This question has been asked earlier in this forum. But, I still didn't get the correct resolution of my problem.

I am trying to run the DTS package from my filesystem.

Command Line:
/FILE "C:\SSIS\IS\bin\Package1.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF

Executed as user: FILESERVER\SQLServiceQA. The command line parameters are invalid. The step failed.

Can anyone please let me know whats the problem with it.

I am not using any Script Task in the package.

Thanks.Does it run if you execute the same command line as the same user using DTExec? If so then you should probably post to the agent forum. If not then what errors do you get when running it from DTExec?

Thanks,
Matt

Job to be run ever first for the previous calendar month

Hi!

I have a query that has to return bunch of data based on the calendar
month. I have to make sure that it will return data to me for 28 days
if it is February and for 31 if it is August(for example). I need to
be able to execute it every first of every month for the past 30, 31 or
28 days based on the calendar month. Is there a function or a stored
procedure that I can use to do that?

Thank you,
T.tolcis,

There might be a more elegant way, but this should work:

declare @.Now datetime
declare @.StartDate datetime
declare @.EndDate datetime

set @.Now = getdate()
if datepart(d, @.Now) = 1 -- only execute if it is the first day of the month
begin
-- to get start date, subtract one month from the date and remove the
time from the date
set @.StartDate = convert(varchar(15), dateadd (m, -1, @.now), 112)
-- to get end date, remove time from date
set @.EndDate = dateadd(m, 1, @.StartDate)
print cast(@.StartDate as char(25)) + cast(@.EndDate as char(25))
-- execute your code here using >= @.StartDate and < @.EndDate
end

-- Bill

"tolcis" <nytollydba@.gmail.comwrote in message
news:1169073028.098703.235980@.11g2000cwr.googlegro ups.com...

Quote:

Originally Posted by

Hi!
>
I have a query that has to return bunch of data based on the calendar
month. I have to make sure that it will return data to me for 28 days
if it is February and for 31 if it is August(for example). I need to
be able to execute it every first of every month for the past 30, 31 or
28 days based on the calendar month. Is there a function or a stored
procedure that I can use to do that?
>
Thank you,
T.
>

|||WHERE create_date < DATEADD(month,DATEDIFF(month, 0,getdate()),0)
AND create_date >= DATEADD(month,DATEDIFF(month, 0,getdate())-1,0)

Roy Harvey
Beacon Falls, CT

On 17 Jan 2007 14:30:29 -0800, "tolcis" <nytollydba@.gmail.comwrote:

Quote:

Originally Posted by

>Hi!
>
>I have a query that has to return bunch of data based on the calendar
>month. I have to make sure that it will return data to me for 28 days
>if it is February and for 31 if it is August(for example). I need to
>be able to execute it every first of every month for the past 30, 31 or
>28 days based on the calendar month. Is there a function or a stored
>procedure that I can use to do that?
>
>Thank you,
>T.

|||tocis,

Ignore the first post, this is better. Schedule your job to be run on the
first of every month. This will give you the prior month date range for any
date, not just the 1st. This way if the job fails you can also run it on the
2nd, 3rd, etc.

declare @.Now datetime
declare @.StartDate datetime
declare @.EndDate datetime

set @.Now = getdate()

-- to get end date, subtract days to get to get 1st of the month for any
date
set @.EndDate = dateadd(d, 1 - datepart(d,@.Now), @.Now)

-- remove the time portion
set @.EndDate = convert(char(12), @.EndDate, 112)

-- subtract a month for start date
set @.StartDate = dateadd(m, -1, @.EndDate)
print cast(@.StartDate as char(25)) + cast(@.EndDate as char(25))
-- execute your code here using >= @.StartDate and < @.EndDate

-- Bill

"AlterEgo" <alterego55@.dslextreme.comwrote in message
news:12qte0qjqivt0ae@.corp.supernews.com...

Quote:

Originally Posted by

tolcis,
>
There might be a more elegant way, but this should work:
>
declare @.Now datetime
declare @.StartDate datetime
declare @.EndDate datetime
>
set @.Now = getdate()
if datepart(d, @.Now) = 1 -- only execute if it is the first day of the
month
begin
-- to get start date, subtract one month from the date and remove the
time from the date
set @.StartDate = convert(varchar(15), dateadd (m, -1, @.now), 112)
-- to get end date, remove time from date
set @.EndDate = dateadd(m, 1, @.StartDate)
print cast(@.StartDate as char(25)) + cast(@.EndDate as char(25))
-- execute your code here using >= @.StartDate and < @.EndDate
end
>
-- Bill
>
"tolcis" <nytollydba@.gmail.comwrote in message
news:1169073028.098703.235980@.11g2000cwr.googlegro ups.com...

Quote:

Originally Posted by

>Hi!
>>
>I have a query that has to return bunch of data based on the calendar
>month. I have to make sure that it will return data to me for 28 days
>if it is February and for 31 if it is August(for example). I need to
>be able to execute it every first of every month for the past 30, 31 or
>28 days based on the calendar month. Is there a function or a stored
>procedure that I can use to do that?
>>
>Thank you,
>T.
>>

>
>

|||Roy,

Wya cool, did not know this!

-- Bill

"Roy Harvey" <roy_harvey@.snet.netwrote in message
news:vietq2l4344pq52plgrnael566amuu5pfb@.4ax.com...

Quote:

Originally Posted by

WHERE create_date < DATEADD(month,DATEDIFF(month, 0,getdate()),0)
AND create_date >= DATEADD(month,DATEDIFF(month, 0,getdate())-1,0)
>
Roy Harvey
Beacon Falls, CT
>
On 17 Jan 2007 14:30:29 -0800, "tolcis" <nytollydba@.gmail.comwrote:
>

Quote:

Originally Posted by

>>Hi!
>>
>>I have a query that has to return bunch of data based on the calendar
>>month. I have to make sure that it will return data to me for 28 days
>>if it is February and for 31 if it is August(for example). I need to
>>be able to execute it every first of every month for the past 30, 31 or
>>28 days based on the calendar month. Is there a function or a stored
>>procedure that I can use to do that?
>>
>>Thank you,
>>T.

|||Thanks. Works great.

Roy Harvey wrote:

Quote:

Originally Posted by

WHERE create_date < DATEADD(month,DATEDIFF(month, 0,getdate()),0)
AND create_date >= DATEADD(month,DATEDIFF(month, 0,getdate())-1,0)
>
Roy Harvey
Beacon Falls, CT
>
On 17 Jan 2007 14:30:29 -0800, "tolcis" <nytollydba@.gmail.comwrote:
>

Quote:

Originally Posted by

Hi!

I have a query that has to return bunch of data based on the calendar
month. I have to make sure that it will return data to me for 28 days
if it is February and for 31 if it is August(for example). I need to
be able to execute it every first of every month for the past 30, 31 or
28 days based on the calendar month. Is there a function or a stored
procedure that I can use to do that?

Thank you,
T.

sql

JOB TIME (twice a day)..?

Hi,
In our database a job is scheduled to run at 6:30 PM.
I need to run that job twice a day ie., at 12:30 PM & 6:30 PM
Is it possible?
Is there any alternative for this without creating a new job?
Thanks,
Soura
Yes, right click on the agent, select agent properties and have it run every
6 hours, starting at 12:30 pm. ending at 7:00 pm
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:65EADF5F-4D28-4835-A54F-F209FD3422F7@.microsoft.com...
> Hi,
> In our database a job is scheduled to run at 6:30 PM.
> I need to run that job twice a day ie., at 12:30 PM & 6:30 PM
> Is it possible?
> Is there any alternative for this without creating a new job?
> Thanks,
> Soura
|||Thank you Cotter
It's working fine.
Soura.
"Hilary Cotter" wrote:

> Yes, right click on the agent, select agent properties and have it run every
> 6 hours, starting at 12:30 pm. ending at 7:00 pm
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "SouRa" <SouRa@.discussions.microsoft.com> wrote in message
> news:65EADF5F-4D28-4835-A54F-F209FD3422F7@.microsoft.com...
>
>

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 synchronous?

Quick question - if I have several steps in a job are they run one after the other or can there potentially be overlap. In one step I create a file and the subsequent step copies it, so I'm concerned that the first completes before the second begins
TIA
Frank SThey are sequental.
Bojidar Alexandrov|||Frank,
My experience is that it is no overlap.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Frank Spencer" <anonymous@.discussions.microsoft.com> wrote in message
news:C2E0F3EF-331D-49CF-A77E-ECCEF0710F32@.microsoft.com...
> Quick question - if I have several steps in a job are they run one after the other or can there potentially
be overlap. In one step I create a file and the subsequent step copies it, so I'm concerned that the first
completes before the second begins.
> TIA,
> Frank S

job steps synchronous?

Quick question - if I have several steps in a job are they run one after the
other or can there potentially be overlap. In one step I create a file and
the subsequent step copies it, so I'm concerned that the first completes bef
ore the second begins.
TIA,
Frank SThey are sequental.
Bojidar Alexandrov|||Frank,
My experience is that it is no overlap.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Frank Spencer" <anonymous@.discussions.microsoft.com> wrote in message
news:C2E0F3EF-331D-49CF-A77E-ECCEF0710F32@.microsoft.com...
> Quick question - if I have several steps in a job are they run one after the other
or can there potentially
be overlap. In one step I create a file and the subsequent step copies it, s
o I'm concerned that the first
completes before the second begins.
> TIA,
> Frank Ssql

job steps synchronous?

Quick question - if I have several steps in a job are they run one after the other or can there potentially be overlap. In one step I create a file and the subsequent step copies it, so I'm concerned that the first completes before the second begins.
TIA,
Frank S
They are sequental.
Bojidar Alexandrov
|||Frank,
My experience is that it is no overlap.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Frank Spencer" <anonymous@.discussions.microsoft.com> wrote in message
news:C2E0F3EF-331D-49CF-A77E-ECCEF0710F32@.microsoft.com...
> Quick question - if I have several steps in a job are they run one after the other or can there potentially
be overlap. In one step I create a file and the subsequent step copies it, so I'm concerned that the first
completes before the second begins.
> TIA,
> Frank S

Job step hanging

Hi,
I am trying to run a VB Script step on A SQL Server 2000
Developer SP3a - OS Win 2000 Professional SP2 and the job
is hanging.
If the same script I am executing from a DTS ActiveX task
it runs without any issue.
Here is the script
Dim fso, Folder, Files, File, datecreated
Set fso = CreateObject("Scripting.FileSystemObject")
'Get handles to folder current location.
Set Folder = fso.GetFolder("E:\Backup_History")
Set Files = Folder.Files
If Files.Count > 0 Then
For Each File In Files
'Get files creation date
MsgBox ( file.DateLastModified)
If file.DateLastModified < date -
7 Then
' Delete the files.
File.Delete
End If
Next
End If
Set fso = Nothing
Set Folder = Nothing
Set Files = Nothing
Set File = Nothing
It meant to delete files older then a week.
Any help is very appreciated.
IonelGet rid of the MsgBox ( file.DateLastModified) :-)
There is no visible desktop to display this message box on so it can never
be acknowledged thus the job hangs
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Ionel" <anonymous@.discussions.microsoft.com> wrote in message
news:016401c3c3fd$a96226b0$a301280a@.phx.gbl...
> Hi,
> I am trying to run a VB Script step on A SQL Server 2000
> Developer SP3a - OS Win 2000 Professional SP2 and the job
> is hanging.
> If the same script I am executing from a DTS ActiveX task
> it runs without any issue.
> Here is the script
> Dim fso, Folder, Files, File, datecreated
> Set fso = CreateObject("Scripting.FileSystemObject")
> 'Get handles to folder current location.
> Set Folder = fso.GetFolder("E:\Backup_History")
> Set Files = Folder.Files
> If Files.Count > 0 Then
> For Each File In Files
> 'Get files creation date
> MsgBox ( file.DateLastModified)
> If file.DateLastModified < date -
> 7 Then
> ' Delete the files.
> File.Delete
> End If
> Next
> End If
>
> Set fso = Nothing
> Set Folder = Nothing
> Set Files = Nothing
> Set File = Nothing
> It meant to delete files older then a week.
> Any help is very appreciated.
> Ionel
>|||Thanks,
I had several MsgBox that I used for debugging the script.
This one just escape my attention.
Thanks again.
Ionel
>--Original Message--
>Get rid of the MsgBox ( file.DateLastModified) :-)
>There is no visible desktop to display this message box
on so it can never
>be acknowledged thus the job hangs
>--
>HTH
>Jasper Smith (SQL Server MVP)
>I support PASS - the definitive, global
>community for SQL Server professionals -
>http://www.sqlpass.org
>
>"Ionel" <anonymous@.discussions.microsoft.com> wrote in
message
>news:016401c3c3fd$a96226b0$a301280a@.phx.gbl...
>> Hi,
>> I am trying to run a VB Script step on A SQL Server 2000
>> Developer SP3a - OS Win 2000 Professional SP2 and the
job
>> is hanging.
>> If the same script I am executing from a DTS ActiveX
task
>> it runs without any issue.
>> Here is the script
>> Dim fso, Folder, Files, File, datecreated
>> Set fso = CreateObject("Scripting.FileSystemObject")
>> 'Get handles to folder current location.
>> Set Folder = fso.GetFolder("E:\Backup_History")
>> Set Files = Folder.Files
>> If Files.Count > 0 Then
>> For Each File In Files
>> 'Get files creation date
>> MsgBox ( file.DateLastModified)
>> If file.DateLastModified < date -
>> 7 Then
>> ' Delete the files.
>> File.Delete
>> End If
>> Next
>> End If
>>
>> Set fso = Nothing
>> Set Folder = Nothing
>> Set Files = Nothing
>> Set File = Nothing
>> It meant to delete files older then a week.
>> Any help is very appreciated.
>> Ionel
>>
>
>.
>|||I've done the same thing myself :-)
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Ionel" <anonymous@.discussions.microsoft.com> wrote in message
news:07e701c3c402$e33f6ff0$a001280a@.phx.gbl...
> Thanks,
> I had several MsgBox that I used for debugging the script.
> This one just escape my attention.
> Thanks again.
> Ionel
> >--Original Message--
> >Get rid of the MsgBox ( file.DateLastModified) :-)
> >There is no visible desktop to display this message box
> on so it can never
> >be acknowledged thus the job hangs
> >
> >--
> >HTH
> >
> >Jasper Smith (SQL Server MVP)
> >
> >I support PASS - the definitive, global
> >community for SQL Server professionals -
> >http://www.sqlpass.org
> >
> >
> >"Ionel" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:016401c3c3fd$a96226b0$a301280a@.phx.gbl...
> >> Hi,
> >>
> >> I am trying to run a VB Script step on A SQL Server 2000
> >> Developer SP3a - OS Win 2000 Professional SP2 and the
> job
> >> is hanging.
> >> If the same script I am executing from a DTS ActiveX
> task
> >> it runs without any issue.
> >>
> >> Here is the script
> >>
> >> Dim fso, Folder, Files, File, datecreated
> >>
> >> Set fso = CreateObject("Scripting.FileSystemObject")
> >>
> >> 'Get handles to folder current location.
> >> Set Folder = fso.GetFolder("E:\Backup_History")
> >>
> >> Set Files = Folder.Files
> >> If Files.Count > 0 Then
> >> For Each File In Files
> >> 'Get files creation date
> >> MsgBox ( file.DateLastModified)
> >> If file.DateLastModified < date -
> >> 7 Then
> >> ' Delete the files.
> >> File.Delete
> >> End If
> >> Next
> >> End If
> >>
> >>
> >> Set fso = Nothing
> >> Set Folder = Nothing
> >> Set Files = Nothing
> >> Set File = Nothing
> >>
> >> It meant to delete files older then a week.
> >>
> >> Any help is very appreciated.
> >>
> >> Ionel
> >>
> >>
> >
> >
> >.
> >

Job step fails with Error 7399 and 7312

We have a job that intermittently fails on the same step with the same error.
Identical jobs that run before this one always succeed. The step uses the
following T-SQL to update a table on a linked server:
DBCC TRACEON (7300, 3604)
INSERT INTO <linkedservername>.ods.dbo.[!ODSReplicationTimes ]
([Date], Imports, Div)
VALUES (dbo.DateOnly(DATEADD(hh, 7, GETDATE())), GETDATE(), 'wbd')
if the job fails, it is exactly the default timeout of 10 minutes after the
start time, and results in this error:
Executed as user: <username>. OLE DB provider 'SQLOLEDB' reported an error.
[SQLSTATE 42000] (Error 7399) [SQLSTATE 01000] (Error 7312) OLE DB error
trace [OLE/DB Provider 'SQLOLEDB' IRowsetChange::InsertRow returned
0x80004005: ]. [SQLSTATE 01000] (Error 7300). The step failed.
on failure, this step proceeds to a similar step which calls a sproc:
DBCC TRACEON (7300, 3604)
CheckFutureStatus 'JobName Finish','!Check Status WBD',240,240,3,'wbd'
yielding a similar result:
Executed as user: <username>. OLE DB provider 'SQLOLEDB' reported an error.
[SQLSTATE 42000] (Error 7399) [SQLSTATE 01000] (Error 7312) OLE DB error
trace [OLE/DB Provider 'SQLOLEDB' IRowsetChange::SetData returned 0x80004005:
]. [SQLSTATE 01000] (Error 7300). The step failed.
I've set the step to retry 3 times/5 minutes to no avail. the traceon
doesn't give any additional information.
Any ideas?
Hello
Did you try to perform a SQL Profiler trace on the server that you have
configured as the linked server to see what happens on that server. I'm
assuming that the query should finish within the 10 minute timeout period.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||What Events/Data would you want to see. anything in particular for this
situation?
"Pankaj Agarwal [MSFT]" wrote:

> Hello
> Did you try to perform a SQL Profiler trace on the server that you have
> configured as the linked server to see what happens on that server. I'm
> assuming that the query should finish within the 10 minute timeout period.
> Thank you for using Microsoft newsgroups.
> Sincerely
> Pankaj Agarwal
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
|||I think it would be beneficial to capture the Execution Statistics, Stored
Procedure and TSQL related events. Also capture SP Recompile, Database File
Autogrow. This would give you at least a starting point to see what event
is the query stuck on. I would also download the blocker script from the
following KB article and run that to see if there is any blocking when the
query does not complete during the 10 min timeout interval.
271509 INF: How to Monitor SQL Server 2000 Blocking
http://support.microsoft.com/?id=271509
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Job step fails with Error 7399 and 7312

We have a job that intermittently fails on the same step with the same error
.
Identical jobs that run before this one always succeed. The step uses the
following T-SQL to update a table on a linked server:
DBCC TRACEON (7300, 3604)
INSERT INTO <linkedservername>.ods.dbo.[!ODSReplicationTimes]
([Date], Imports, Div)
VALUES (dbo.DateOnly(DATEADD(hh, 7, GETDATE())), GETDATE(), 'wbd')
if the job fails, it is exactly the default timeout of 10 minutes after the
start time, and results in this error:
Executed as user: <username>. OLE DB provider 'SQLOLEDB' reported an error.
[SQLSTATE 42000] (Error 7399) [SQLSTATE 01000] (Error 7312) OLE D
B error
trace [OLE/DB Provider 'SQLOLEDB' IRowsetChange::InsertRow returned
0x80004005: ]. [SQLSTATE 01000] (Error 7300). The step failed.
on failure, this step proceeds to a similar step which calls a sproc:
DBCC TRACEON (7300, 3604)
CheckFutureStatus 'JobName Finish','!Check Status WBD',240,240,3,'wbd'
yielding a similar result:
Executed as user: <username>. OLE DB provider 'SQLOLEDB' reported an error.
[SQLSTATE 42000] (Error 7399) [SQLSTATE 01000] (Error 7312) OLE D
B error
trace [OLE/DB Provider 'SQLOLEDB' IRowsetChange::SetData returned 0x8000
4005:
]. [SQLSTATE 01000] (Error 7300). The step failed.
I've set the step to retry 3 times/5 minutes to no avail. the traceon
doesn't give any additional information.
Any ideas?Hello
Did you try to perform a SQL Profiler trace on the server that you have
configured as the linked server to see what happens on that server. I'm
assuming that the query should finish within the 10 minute timeout period.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||What Events/Data would you want to see. anything in particular for this
situation?
"Pankaj Agarwal [MSFT]" wrote:

> Hello
> Did you try to perform a SQL Profiler trace on the server that you have
> configured as the linked server to see what happens on that server. I'm
> assuming that the query should finish within the 10 minute timeout period.
> Thank you for using Microsoft newsgroups.
> Sincerely
> Pankaj Agarwal
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>|||I think it would be beneficial to capture the Execution Statistics, Stored
Procedure and TSQL related events. Also capture SP Recompile, Database File
Autogrow. This would give you at least a starting point to see what event
is the query stuck on. I would also download the blocker script from the
following KB article and run that to see if there is any blocking when the
query does not complete during the 10 min timeout interval.
271509 INF: How to Monitor SQL Server 2000 Blocking
http://support.microsoft.com/?id=271509
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.sql

Job Step

I have a job step that needs to run with a different parameter on Sunday.
The job step is of type Operating System Command. This step kicks of an
executable.
Can you help me with a solution?
Thanks,
ChrisHow about creating a second step that has different parameters. Then create
a second job that runs prior to this job. In this second job, you can check
to see if it is Sunday and then enable appropriate step on first job and
disable the other one (and vice versa).
"csl" <kcl1998@.hotmail.com> wrote in message
news:uCOv4089HHA.3548@.TK2MSFTNGP06.phx.gbl...
>I have a job step that needs to run with a different parameter on Sunday.
> The job step is of type Operating System Command. This step kicks of an
> executable.
> Can you help me with a solution?
> Thanks,
> Chris
>|||Hi,
I do this with three job steps. Job step 2 has the 6 days a week command,
and job step 3 has the sunday command. In job step one I put a statement
that calls RAISERROR('whatever', 16, 1) on Sunday. Then I set up step one to
go to step 2 on success and step 3 on failure. Both steps 2 and 3 quit
reporting success on success and quit reporting failure on failure.
Or, just create two SQL Agent jobs. One runs weekly on Mon-Sat, and the
other runs weekly on Sun.
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Dragon" <noSpam_Badill@.hotmail.com> wrote in message
news:uk2aK689HHA.4584@.TK2MSFTNGP03.phx.gbl...
>
> How about creating a second step that has different parameters. Then
> create a second job that runs prior to this job. In this second job, you
> can check to see if it is Sunday and then enable appropriate step on first
> job and disable the other one (and vice versa).
> "csl" <kcl1998@.hotmail.com> wrote in message
> news:uCOv4089HHA.3548@.TK2MSFTNGP06.phx.gbl...
>>I have a job step that needs to run with a different parameter on Sunday.
>> The job step is of type Operating System Command. This step kicks of an
>> executable.
>> Can you help me with a solution?
>> Thanks,
>> Chris
>>
>

Job skips certain queries?

Has this happened to anyone else?

I have a large job and all the queries run fine when used
individually, but in the job, SQL says completed with success but in
two cases the queries did not run. One of them is even ridiculously
simple: DROP TABLE tblMyTable, etc.Steve (go2toa@.hotmail.com) writes:
> Has this happened to anyone else?
> I have a large job and all the queries run fine when used
> individually, but in the job, SQL says completed with success but in
> two cases the queries did not run. One of them is even ridiculously
> simple: DROP TABLE tblMyTable, etc.

Are these individual job steps, or are the part of a larger batch?
It is quite difficult to give any assistance with that minimum of
information.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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

Job Scheduling question

I think I know the answer to my question, but I thought I would ask to be
sure that I'm correct. I have a need to run a job every 30 seconds, but the
Job Scheduler only gives the option of a frequency of 1 minute. Is there
anyway to set this up that way?Try this link:
http://www.sqldev.net/sqlagent/SQLAgentRecuringJobsInSecs.htm
"Big Ern" wrote:
> I think I know the answer to my question, but I thought I would ask to be
> sure that I'm correct. I have a need to run a job every 30 seconds, but the
> Job Scheduler only gives the option of a frequency of 1 minute. Is there
> anyway to set this up that way?
>

job scheduling

Hello,
Is it possible to schedule the sql agent to run a job every so many
seconds? I may have a need to schedule a job at a certain interval of
seconds and the sql agent gui scheduler seems to only allow scheduling
by the minute or more.
Thanks,
Greg
http://sqldev.net/sqlagent/SQLAgentR...JobsInSecs.htm
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Greg" <gjackson@.thq.com> wrote in message news:d9ii90l53hekbeg32g0cfcmfhavuak1dm5@.4ax.com...
> Hello,
> Is it possible to schedule the sql agent to run a job every so many
> seconds? I may have a need to schedule a job at a certain interval of
> seconds and the sql agent gui scheduler seems to only allow scheduling
> by the minute or more.
> Thanks,
> Greg
|||If you are going to have something run every so many seconds you might
consider having a never ending stored procedure that uses the WAITFOR DELAY
command to control your running of some commands every few seconds.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Greg" <gjackson@.thq.com> wrote in message
news:d9ii90l53hekbeg32g0cfcmfhavuak1dm5@.4ax.com...
> Hello,
> Is it possible to schedule the sql agent to run a job every so many
> seconds? I may have a need to schedule a job at a certain interval of
> seconds and the sql agent gui scheduler seems to only allow scheduling
> by the minute or more.
> Thanks,
> Greg

Job Scheduling

How do I schedule the jobs to run in sequence? I have created a package to
call the sql server agent jobs in sequence but looks like all the jobs are
running at the same time, not waiting for the other jobs to complete. I did
put the constriants not to run the second job until the first job is
complete. But still it is not behaving as instructed.
Any thoughts,
Thanks,
MariJobs run asynchronously and independently of each other.
You need to make ONE job and call each "job" as an individual step. That
way, "job"/step 2 will not start until "job"/step 1 is complete (and you can
terminate the whole sequence on an individual step failure, or you can have
individual step failures ignored and just move to the next step).
"Maria" <Maria@.discussions.microsoft.com> wrote in message
news:B7B1BBAC-F54E-48C6-A871-A9FBFE1BB0C2@.microsoft.com...
> How do I schedule the jobs to run in sequence? I have created a package to
> call the sql server agent jobs in sequence but looks like all the jobs are
> running at the same time, not waiting for the other jobs to complete. I
> did
> put the constriants not to run the second job until the first job is
> complete. But still it is not behaving as instructed.
> Any thoughts,
> Thanks,
> Mari|||Thank you Aaron. But there is no option to define the "type" for sql server
Agent jobs to call the another job. I guess you know what I am talking.
When I am creating the 'job', for the step 1, there is no Agent Jobs
options. We have only IS package or AS or activeX etc., but not the agent
jobs : (
Tks
"Aaron Bertrand [SQL Server MVP]" wrote:

> Jobs run asynchronously and independently of each other.
> You need to make ONE job and call each "job" as an individual step. That
> way, "job"/step 2 will not start until "job"/step 1 is complete (and you c
an
> terminate the whole sequence on an individual step failure, or you can hav
e
> individual step failures ignored and just move to the next step).
>
> "Maria" <Maria@.discussions.microsoft.com> wrote in message
> news:B7B1BBAC-F54E-48C6-A871-A9FBFE1BB0C2@.microsoft.com...
>
>|||If you are using SQL Server 2005, Service Broker can be used to guarantee
in-order execution:
http://www.microsoft.com/technet/te...lt.a
spx
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Maria" <Maria@.discussions.microsoft.com> wrote in message
news:B7B1BBAC-F54E-48C6-A871-A9FBFE1BB0C2@.microsoft.com...
> How do I schedule the jobs to run in sequence? I have created a package to
> call the sql server agent jobs in sequence but looks like all the jobs are
> running at the same time, not waiting for the other jobs to complete. I
> did
> put the constriants not to run the second job until the first job is
> complete. But still it is not behaving as instructed.
> Any thoughts,
> Thanks,
> Mari|||Maria wrote:
> How do I schedule the jobs to run in sequence? I have created a package to
> call the sql server agent jobs in sequence but looks like all the jobs are
> running at the same time, not waiting for the other jobs to complete. I di
d
> put the constriants not to run the second job until the first job is
> complete. But still it is not behaving as instructed.
> Any thoughts,
> Thanks,
> Mari
Are you referring to SQL Agent jobs, or tasks within a DTS package?|||It is "Execute SQL Server Agent Job Task" under Maintenance Plan Tasks for a
DTS package.
"Tracy McKibben" wrote:

> Maria wrote:
> Are you referring to SQL Agent jobs, or tasks within a DTS package?
>|||Maria wrote:
> It is "Execute SQL Server Agent Job Task" under Maintenance Plan Tasks for
a
> DTS package.
>
I'm not familiar with that one. So, you're using a DTS package to
execute Maintenance Plan tasks. Is the DTS package then going to be
scheduled to run via SQL Agent? Seems sorta bass-ackwards... Am I
completely not understanding what you're trying to accomplish?|||I think your understanding is right. But looks like that doesn't work. When
I
created the package to run all these jobs in a sequence, all thejobs are
running at the same time though I put constraints on it.
Hope this is clear.
thanks
"Tracy McKibben" wrote:

> Maria wrote:
> I'm not familiar with that one. So, you're using a DTS package to
> execute Maintenance Plan tasks. Is the DTS package then going to be
> scheduled to run via SQL Agent? Seems sorta bass-ackwards... Am I
> completely not understanding what you're trying to accomplish?
>|||Hi Roger,
I never used the Broker Service. Could you give some more details how to use
this service to schedule batch jobs.
Thanks
"Roger Wolter[MSFT]" wrote:

> If you are using SQL Server 2005, Service Broker can be used to guarantee
> in-order execution:
> http://www.microsoft.com/technet/te...lt
.aspx
>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Maria" <Maria@.discussions.microsoft.com> wrote in message
> news:B7B1BBAC-F54E-48C6-A871-A9FBFE1BB0C2@.microsoft.com...
>
>|||Maria wrote:
> I think your understanding is right. But looks like that doesn't work. Whe
n I
> created the package to run all these jobs in a sequence, all thejobs are
> running at the same time though I put constraints on it.
>
Ok... Well, that leads me to ask, why not just let the maintenance plan
schedule the jobs in SQL Agent like it's supposed to, instead of
wrapping them in a DTS package like this?
I'll go one step further, and suggest that you dump the maintenance plan
altogether, and write your own processes to do what the maintenance plan
offers you. Write your own BACKUP job, write your own index maintenance
routine, don't shrink your databases. You'll learn alot more about your
environment by doing so...sql

job scheduling

Hello,
Is it possible to schedule the sql agent to run a job every so many
seconds? I may have a need to schedule a job at a certain interval of
seconds and the sql agent gui scheduler seems to only allow scheduling
by the minute or more.
Thanks,
Greghttp://sqldev.net/sqlagent/SQLAgent...gJobsInSecs.htm
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Greg" <gjackson@.thq.com> wrote in message news:d9ii90l53hekbeg32g0cfcmfhavuak1dm5@.
4ax.com..
.
> Hello,
> Is it possible to schedule the sql agent to run a job every so many
> seconds? I may have a need to schedule a job at a certain interval of
> seconds and the sql agent gui scheduler seems to only allow scheduling
> by the minute or more.
> Thanks,
> Greg|||If you are going to have something run every so many seconds you might
consider having a never ending stored procedure that uses the WAITFOR DELAY
command to control your running of some commands every few seconds.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Greg" <gjackson@.thq.com> wrote in message
news:d9ii90l53hekbeg32g0cfcmfhavuak1dm5@.
4ax.com...
> Hello,
> Is it possible to schedule the sql agent to run a job every so many
> seconds? I may have a need to schedule a job at a certain interval of
> seconds and the sql agent gui scheduler seems to only allow scheduling
> by the minute or more.
> Thanks,
> Greg

job scheduling

Hello,
Is it possible to schedule the sql agent to run a job every so many
seconds? I may have a need to schedule a job at a certain interval of
seconds and the sql agent gui scheduler seems to only allow scheduling
by the minute or more.
Thanks,
Greghttp://sqldev.net/sqlagent/SQLAgentRecuringJobsInSecs.htm
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Greg" <gjackson@.thq.com> wrote in message news:d9ii90l53hekbeg32g0cfcmfhavuak1dm5@.4ax.com...
> Hello,
> Is it possible to schedule the sql agent to run a job every so many
> seconds? I may have a need to schedule a job at a certain interval of
> seconds and the sql agent gui scheduler seems to only allow scheduling
> by the minute or more.
> Thanks,
> Greg|||If you are going to have something run every so many seconds you might
consider having a never ending stored procedure that uses the WAITFOR DELAY
command to control your running of some commands every few seconds.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Greg" <gjackson@.thq.com> wrote in message
news:d9ii90l53hekbeg32g0cfcmfhavuak1dm5@.4ax.com...
> Hello,
> Is it possible to schedule the sql agent to run a job every so many
> seconds? I may have a need to schedule a job at a certain interval of
> seconds and the sql agent gui scheduler seems to only allow scheduling
> by the minute or more.
> Thanks,
> Greg