Error executing Packaga from job

Hello, I finally could upload the package, and from the management studio interface I ran the package and it worked perfectly.

When I created a job, with one step only to execute that package, the job fails.

When I go to history it doesnt give me any details of what failed on the package or in the job

Date 24/01/2007 12:30:28
Log Job History (Carga datos ACH)

Step ID 1
Server ATLANTE\SQL2005
Job Name Carga datos ACH
Step Name Carga de datos de ach
Duration 00:00:02
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: ATLANTE\SYSTEM. The package execution failed. The step failed.

Maybe is the user that it tried to execute the package as?

How can I change it?

please delete this post. sorry for repeating|||I can't, but can someone merge some of Luis' threads that all revolve around the same issue?

Thanks,
Phil|||

this article describe solution to most comon issues when executing the package:

http://support.microsoft.com/kb/918760

|||

Hello, when I try to change the package protection level to server storage I got this

Failed to apply package protection with error 0xC0014061 "The protection level, ServerStorage, cannot be used when saving to this destination. The system could not verify that the destination supports secure storage capability.". This error occurs when saving to Xml.

|||Are you storing the packages as XML files or inside the DB? if you are using xmls files you can use DonSaveSensitive and the use package configurations to set the conection strings at run time as described in method 4 in that article.

Error During Job

We have getting an intermitant error when running
some of our Jobs.
ConnectionCheckForData (checkfordata)
[sqlstate 01000][message 10054]
General Network Error. Check Network Documentation
[sqlstat 08s01][error 11]
We can put retries on this step, and sometimes it
works after a couple of retries.. Other times
the job dies. If we restart the job, it will
usually complete.
We don't know why this is happening. The only thing
that step is doing is some inserts and deletes to a
couple of tables.
The connection problem seems to get worse as time goes
by... If we refresh the indexes, and do some other
database cleanup... it gets better.
Can anyone help us in preventing this problem
answered above - sometimes these newsgroups take a few minutes for posts to
appear...
Regards,
Greg Linwood
SQL Server MVP
"scot" <sstyer@.alltel.net> wrote in message
news:481001c42bcd$eff4bba0$a001280a@.phx.gbl...
> We have getting an intermitant error when running
> some of our Jobs.
> ConnectionCheckForData (checkfordata)
> [sqlstate 01000][message 10054]
> General Network Error. Check Network Documentation
> [sqlstat 08s01][error 11]
> We can put retries on this step, and sometimes it
> works after a couple of retries.. Other times
> the job dies. If we restart the job, it will
> usually complete.
> We don't know why this is happening. The only thing
> that step is doing is some inserts and deletes to a
> couple of tables.
> The connection problem seems to get worse as time goes
> by... If we refresh the indexes, and do some other
> database cleanup... it gets better.
> Can anyone help us in preventing this problem
>

Error During Job

We have getting an intermitant error when running
some of our Jobs.
ConnectionCheckForData (checkfordata)
[sqlstate 01000][message 10054]
General Network Error. Check Network Documentation
[sqlstat 08s01][error 11]
We can put retries on this step, and sometimes it
works after a couple of retries.. Other times
the job dies. If we restart the job, it will
usually complete.
We don't know why this is happening. The only thing
that step is doing is some inserts and deletes to a
couple of tables.
The connection problem seems to get worse as time goes
by... If we refresh the indexes, and do some other
database cleanup... it gets better.
Can anyone help us in preventing this problemanswered above - sometimes these newsgroups take a few minutes for posts to
appear...
Regards,
Greg Linwood
SQL Server MVP
"scot" <sstyer@.alltel.net> wrote in message
news:481001c42bcd$eff4bba0$a001280a@.phx.gbl...
> We have getting an intermitant error when running
> some of our Jobs.
> ConnectionCheckForData (checkfordata)
> [sqlstate 01000][message 10054]
> General Network Error. Check Network Documentation
> [sqlstat 08s01][error 11]
> We can put retries on this step, and sometimes it
> works after a couple of retries.. Other times
> the job dies. If we restart the job, it will
> usually complete.
> We don't know why this is happening. The only thing
> that step is doing is some inserts and deletes to a
> couple of tables.
> The connection problem seems to get worse as time goes
> by... If we refresh the indexes, and do some other
> database cleanup... it gets better.
> Can anyone help us in preventing this problem
>

Error During Job

We have getting an intermitant error when running
some of our Jobs.
ConnectionCheckForData (checkfordata)
[sqlstate 01000][message 10054]
General Network Error. Check Network Documentation
[sqlstat 08s01][error 11]
We can put retries on this step, and sometimes it
works after a couple of retries.. Other times
the job dies. If we restart the job, it will
usually complete.
We don't know why this is happening. The only thing
that step is doing is some inserts and deletes to a
couple of tables.
The connection problem seems to get worse as time goes
by... If we refresh the indexes, and do some other
database cleanup... it gets better.
Can anyone help us in preventing this problemanswered above - sometimes these newsgroups take a few minutes for posts to
appear...
Regards,
Greg Linwood
SQL Server MVP
"scot" <sstyer@.alltel.net> wrote in message
news:481001c42bcd$eff4bba0$a001280a@.phx.gbl...
> We have getting an intermitant error when running
> some of our Jobs.
> ConnectionCheckForData (checkfordata)
> [sqlstate 01000][message 10054]
> General Network Error. Check Network Documentation
> [sqlstat 08s01][error 11]
> We can put retries on this step, and sometimes it
> works after a couple of retries.. Other times
> the job dies. If we restart the job, it will
> usually complete.
> We don't know why this is happening. The only thing
> that step is doing is some inserts and deletes to a
> couple of tables.
> The connection problem seems to get worse as time goes
> by... If we refresh the indexes, and do some other
> database cleanup... it gets better.
> Can anyone help us in preventing this problem
>

Error copying data to a remote sql server 2005 database.

When copying data to a remote SQL2K5 destination from a SQL2K5 source database, both using mixed sql server security mode, my job generates the following error:

[Transfer SQL Server Objects Task] Error: Execution failed with the following error: "Cannot apply value null to property Login: Value cannot be null..".

This occurs after the destination database tables have been truncated and replacement data from the source would begin to copy.

The same process can be successfully completed from the Management studio with a simple data export process. However, when I run the saved package again from the BI interface, I get this error.

My search engine searches have yielded numerous hits of others having the same problem with one microsoft rep indicating it was a bug and would be resolved in sp1. I am working with sp1. Oddly, there is only mention of this in the forums. No KB article from MS addresses the problem and I do not see it addressed elsewhere at sqlservercentral.

It appears that others have switched to Integrated Security and resolved the problem. However, I do not have that option with a remotely hosted database.

Does anyone have any information concerning this problem?

bug reports may be submitted here: SQL Server Feedback.

i suggest that you first check for the status of any existing bug report to avoid duplication.

Error converting datatypes

Hi,

This script gets run by a job every 3 mins, and it's falling over with an "Error converting varchar value... to column of datatype int", and I think it's on this line:

select @.sbj1='New ICNA Forum Post (ThreadID='+@.existingID+')'

...where I'm trying to build up a string by dropping an ID number (datatype int) into it.

So I tried:

select @.sbj1='New ICNA Forum Post (ThreadID='+CAST(@.existingID AS varchar(100))+')'

and:

select @.sbj1='New ICNA Forum Post (ThreadID='+CONVERT(varchar(100), @.existingID)+')'

Both of these result in the job running successfully, but no emails get sent and the job history shows the error "Incorrect syntax near 'Forum'." On the good side, it's supposed to be looping through the email-sending bit 4 times (there are currently 4 users) and sure enough, it repeats that error message 4 times.

The full script follows below. I'd be hugely grateful if anyone could point out what I'm doing wrong, and how to do it right.

Cheers.

Declare @.hMessage varchar(255),@.msg_id varchar(255)
Declare @.MessageText varchar(8000),@.message varchar(8000)
Declare @.MessageSubject varchar(8000),@.subject varchar(8000)
Declare @.Origin varchar (8000), @.originator_address varchar(8000)

EXEC master.dbo.xp_findnextmsg @.unread_only='true',@.msg_id=@.hMessage OUT

WHILE @.hMessage IS NOT NULL
BEGIN

exec master.dbo.xp_readmail
@.msg_id=@.hMessage,
@.message=@.MessageText OUT,
@.subject=@.MessageSubject OUT,
@.originator_address=@.Origin OUT

IF ((SELECT COUNT(*) FROM forum_users WHERE email = @.Origin) = 1) -- IF email from forum-recognised address
BEGIN
IF (CHARINDEX('(ThreadID=', @.MessageSubject)>0) -- IF email has a thread ID
BEGIN
DECLARE @.existingID int, @.em1 varchar(100), @.bdy1 varchar(8000), @.sbj1 varchar(500)
SELECT @.existingID=CAST(SUBSTRING(@.MessageSubject, (CHARINDEX('=', @.MessageSubject)+1), (CHARINDEX(')', @.MessageSubject)-(CHARINDEX('=', @.MessageSubject)+1))) AS int)
INSERT INTO forum_posts (body, thread_id) VALUES (@.MessageText, @.existingID)

-- Do mailing

declare em_cursor1 cursor for
SELECT email FROM forum_users WHERE email_option='yes'
open em_cursor1
fetch next from em_cursor1
into @.em1

while @.@.FETCH_STATUS=0
begin
select @.bdy1='New ICNA Forum Post:'+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)+@.Messag eText
select @.sbj1='New ICNA Forum Post (ThreadID='+@.existingID+')'
exec master.dbo.xp_sendmail @.em1,@.bdy1,@.sbj1
fetch next from em_cursor1
into @.em1
end
close em_cursor1
deallocate em_cursor1

END
ELSE -- IF email has no thread ID
BEGIN
DECLARE @.newID int, @.em2 varchar(100), @.bdy2 varchar(8000), @.sbj2 varchar(500) -- Create a new thread record and use the resulting ID to add a thread_post record
INSERT INTO forum_threads (subject) VALUES (@.MessageSubject)
SELECT @.newID=@.@.IDENTITY
INSERT INTO forum_posts (body, thread_id) VALUES (@.MessageText, @.newID)

-- Do mailing

declare em_cursor2 cursor for
SELECT email FROM forum_users WHERE email_option='yes'
open em_cursor2
fetch next from em_cursor2
into @.em2

while @.@.FETCH_STATUS=0
begin
select @.bdy2='New ICNA Forum Post:'+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)+@.Messag eText
select @.sbj2='New ICNA Forum Post (ThreadID='+@.newID+')'
exec master.dbo.xp_sendmail @.em2,@.bdy2,@.sbj2
fetch next from em_cursor2
into @.em2
end
close em_cursor2
deallocate em_cursor2

END
END

SET @.hMessage = NULL

EXEC master.dbo.xp_findnextmsg @.unread_only='true',@.msg_id=@.hMessage OUT
ENDHave you tried substituing the xp_sendmail with SELECT just to see if you have formatted very thing correctly.|||Ah. Did I mention that my grasp of SQL and its debugging techniques was a little sparse?

Thanks very much for your help, but could you possibly explain how do do that?

Cheers.|||OK;

In your script you have a line like:
exec master.dbo.xp_sendmail @.em1,@.bdy1,@.sbj1
RewriteSELECT 'master.dbo.xp_sendmail', @.em1,@.bdy1,@.sbj1
Do this for all xp_sendmail. This may shine some light.|||Aha! Well, at least I've learnt how to get some debugging output. Unfortunately I'm none the wiser as to why sendmail isn't working.

It loops 4 times, once for each email address in my forum_users table. I'm sending the "trigger" email from a hotmail address (needs to be external to our network) and thus for each email it tries to send the subject and body look like:

@.sbj1:
New ICNA Forum Post (ThreadID=24)

@.bdy1:
New ICNA Forum Post: Friday morning test 1 __________________________________________________ _______________ Join the worlds largest e-mail service with MSN Hotmail. http://www.hotmail.com

Which are pretty well exactly what I was expecting - so why on earth is it falling over? :(|||At last, got it working! It didn't like this line:

exec master.dbo.xp_sendmail @.em1,@.bdy1,@.sbj1

when I replaced it with:

exec master.dbo.xp_sendmail
@.recipients=@.em1,
@.message=@.bdy1,
@.subject=@.sbj1

it worked fine. I don't know why, and I don't care :) It works...

Error connecting to DB

Hi everyone,

I am learning how to use .NET and MSDE. I am working out of the book "ASP.NET Unleashed" on the Job Site (Chap 30)

I have used osql to run the .sql script, but now I am having problems connecting to the DB; I get "SQL Server does not exist or access denied"

The web.config file is:

<configuration>
<appSettings>
<add key="constring"
value="Server=localhost;UID=AspNETJobsUser;PWD=secret;Database=AspNetJobs" />
</appSettings>
<system.web>
<authentication mode="Forms" >
<forms name=".ASPNETJOBS"
loginUrl="/aspnetjobs/site/password/login.aspx" />
</authentication>
</system.web>
</configuration
My computer is running in Windows Authentication Mode.

I have tried replacing the UID and PWD to my sa account settings, but I still can not connect.
The books says I need to manually add a login that has access to the AspNETJobsDatabase, but I assume that would be the sa login or do I need to create another all together? If so, can anyone suggest an article to lead me in that direction.

Any suggestions would be helpful and greatly appreciated.

ThanksHere's an article that'll tell you how to add a login to an MSDE instance.

Regards,

Xander|||Data Source=IpAddyorServerName;Initial Catalog=DbName;User ID=UserName;Password=BlankpasswordSucks|||[SqlException: Impossibile eseguire l'accesso per l'utente 'sa'. Motivo: l'utente non è associato a una connessione SQL Server trusted.]

Why?
Bye Bye!|||I changed my code in the Web.config file to:

<configuration>
<appSettings>
<add key="constring"
value="Data Source=SHAYLA1;Initial Catalog=AspNetJobs;User ID=AspNETJobsUser;Password=secret" />
</appSettings>
<system.web>
<authentication mode="Forms" >
<forms name=".ASPNETJOBS"
loginUrl="/aspnetjobs/site/password/login.aspx" />
</authentication>
</system.web>
</configuration
and now I get the error:
Login failed for user 'AspNETJobsUser'. Reason: Not associated with a trusted SQL Server connection.

I followed the article on how to create a login, and I got the message of "user AspNETJobsUser already exists"

When I change the User to sa and password to "my password," I still get the same error

Still missing something?|||just an update for anybody out there that might be facing the same problem. I changed the setting to Mixed Mode from Windows authentication following the direction of the article at

http://support.microsoft.com/?kbid=322336#2

Once I restarted my computer, the app came up fine.

My final web.config files looks like so:

<configuration>
<appSettings>
<add key="constring"
value="Server=(local);Database=AspNetJobs;UID=AspNETJobsUser;PWD=secret" />
</appSettings>
<system.web>
<authentication mode="Forms" >
<forms name=".ASPNETJOBS"
loginUrl="/aspnetjobs/site/password/login.aspx" />
</authentication>
</system.web>
</configuration
Thank you to those who responded to my original post.

Error Condition for Updates

Hi,

I have a series of Updates that I perform on 7 tables. I would like to execute these updates on a nighly basis via a job. I have the update statements lined up in a stored proc.

So I have -

Create Proc Update
AS
SET NOCOUNT ON

DECLARE @.RETURNVALUE int
DECLARE @.ERRORMESSAGETXT varchar(510)
DECLARE @.ERRORNUM int

set @.RETURNVALUE = 0

BEGIN --Start here

Update table1
Update table2
.
.
.
Update table7

SELECT @.ERRORNUM = @.@.ERROR
IF @.ERRORNUM = 0
SELECT @.RETURNVALUE = 0
ELSE
BEGIN
SELECT @.ERRORMESSAGETXT = description FROM [master].[dbo].[sysmessages]
WHERE error = @.@.ERROR
RAISERROR (@.ERRORMESSAGETXT, 16, 1)
SELECT @.RETURNVALUE = 1
END
RETURN @.RETURNVALUE
END -- END HERE

--

Is the error checking at the appropriate place? Should I have error conditions after every update? If yes then what condition do I check for?

Thanks@.@.ERROR only shows the result of the most recent statement. It is cleared for the next statement. Therefor your code is only going to indicate whether an error occured on Update Table7.

You will need to check for an error after each critical step, and then store the results of the error check. You can store it in a single value like:
@.ERROR_MONITOR = @.ERROR_MONITOR + @.@.ERROR
...which will result in a value greater than zero if any errors occured. Or you can store the results of each check separately (@.UPDATETABLE1ERROR, @.UPDATETABLE2ERROR..etc).|||So is this correct. Check the where clause in the ELSE part and the statement after Update Table 7.
----
Update table1
SELECT @.ERRORNUM = @.ERRORNUM + @.@.ERROR
Update table2
SELECT @.ERRORNUM = @.ERRORNUM + @.@.ERROR
.
.
.
Update table7
SELECT @.ERRORNUM = @.ERRORNUM + @.@.ERROR

IF @.ERRORNUM = 0
SELECT @.RETURNVALUE = 0
ELSE
BEGIN
SELECT @.ERRORMESSAGETXT = description FROM [master].[dbo].[sysmessages]
WHERE error = @.@.ERROR (OR is this @.ERRORNUM)
RAISERROR (@.ERRORMESSAGETXT, 16, 1)
SELECT @.RETURNVALUE = 1
END
RETURN @.RETURNVALUE

Let me know.

Thanks|||@.ERRORNUM holds the sum of all the error message IDs, so it can be used to determine whether any error occured but if more than one error occured then it is useless for looking up the error description.

"SELECT @.ERRORMESSAGETXT = description FROM [master].[dbo].[sysmessages] WHERE error = @.@.ERROR "
...will, again, only give you the error message for the last error to occur.

If you want to be able to handle multiple errors, create a varchar variable and append any error text to it after each critical step.

Depending on your process, you may just want to exit the procedure after the first error...|||Don't call your procedure "Update". And I don't think you could actually compile what you posted.

And if you are really interested in capturing all the errors while continuing to do your updates, then you'll need a @.temptable to acquire a value of @.@.error on each update. At the end you would parse a string of all errors by joining this table with sysmessages and scrolling through it while concatenating [description] into a varchar variable.

Error concatenating string

I am trying to create a job that, as one of its steps, will kick off a
DTS package. As part of the command parameter, I need to concat a
system variable (@.@.SERVERNAME) to a constant string. I am receiving an
error about incorrect syntax near the +.

Here is the code for the job step.

-- Add the job steps
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep@.job_id = @.JobID,
@.step_id = 1,
@.step_name = N'Import OCC Series Data',
@.command = N'DTSRun /F
D:\Databases\Scripts\DTS\ImportOCCSeriesData.dts /A DbName:8=' +
@.@.SERVERNAME,
@.database_name = N'',
@.server = N'',
@.database_user_name = N'',
@.subsystem = N'CmdExec',
@.cmdexec_success_code = 0,
@.flags = 2,
@.retry_attempts = 0,
@.retry_interval = 1,
@.output_file_name = N'',
@.on_success_step_id = 0,
@.on_success_action = 3,
@.on_fail_step_id = 0,
@.on_fail_action = 3
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback

If I just try SELECT N'DTSRun /F
D:\Databases\Scripts\DTS\ImportOCCSeriesData.dts /A DbName:8=' +
@.@.SERVERNAME, everything works fine. I even tried declaring a local
variable named @.command and setting it in the select statement, but no
dice."Jason" <JayCallas@.hotmail.com> wrote in message
news:f01a7c89.0402051028.4aa7015e@.posting.google.c om...
> I am trying to create a job that, as one of its steps, will kick off a
> DTS package. As part of the command parameter, I need to concat a
> system variable (@.@.SERVERNAME) to a constant string. I am receiving an
> error about incorrect syntax near the +.
> Here is the code for the job step.
> -- Add the job steps
> EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID,
> @.step_id = 1,
> @.step_name = N'Import OCC Series Data',
> @.command = N'DTSRun /F
> D:\Databases\Scripts\DTS\ImportOCCSeriesData.dts /A DbName:8=' +
> @.@.SERVERNAME,
> @.database_name = N'',
> @.server = N'',
> @.database_user_name = N'',
> @.subsystem = N'CmdExec',
> @.cmdexec_success_code = 0,
> @.flags = 2,
> @.retry_attempts = 0,
> @.retry_interval = 1,
> @.output_file_name = N'',
> @.on_success_step_id = 0,
> @.on_success_action = 3,
> @.on_fail_step_id = 0,
> @.on_fail_action = 3
> IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
> If I just try SELECT N'DTSRun /F
> D:\Databases\Scripts\DTS\ImportOCCSeriesData.dts /A DbName:8=' +
> @.@.SERVERNAME, everything works fine. I even tried declaring a local
> variable named @.command and setting it in the select statement, but no
> dice.

You can't build a parameter value 'dynamically' like that, but assigning the
entire string to a variable first should work:

declare @.mycommand nvarchar(1000)
set @.mycommand = N'DTSRun /F
D:\Databases\Scripts\DTS\ImportOCCSeriesData.dts /A DbName:8=' +
@.@.SERVERNAME

EXECUTE msdb.dbo.sp_add_jobstep
...
@.command = @.mycommand
...

What error did you get when you tried this?

Simon|||I ended up figuring that out and doing exactly what you suggested.
Something I did not know about passing parameters to stored
procedures...

The error I had gotten was
Incorrect syntax at '+'

Thanks for the help.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Error cause job to quit running

I have a sp that calls another sp. The second sp will generate an error
which the parent sp expects and then calls the child sp again with a
different set of parameters until success is achieved. If I run this in
Query Analyser all is good:). But when I put the parent sp in a job step
and run it as a job, it quits on the first generated error. I don't
understand what is going on here'?
BarryHi Barry,
Could you help show me detailed sample stored procedures, with which I
could repo it on my machine and then make further research on it.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Sure, here's the code I am running. It is just a db restore. You will need
a db backup file named the way that the maintenance plans name backup files.
create proc spSYSRestoreGDInventoryDB
as
begin
declare @.MinCntr int
declare @.MinStr varchar(8)
set @.MinCntr = 0
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name =N'GDInventory')
DROP DATABASE [GDInventory]
CREATE DATABASE [GDInventory] ON (NAME = N'GDInventory_data', FILENAME =N'c:\database\mssql\data\GDInventory_Data.MDF' ,
SIZE = 1, FILEGROWTH = 10%) LOG ON (NAME = N'GDInventory_log', FILENAME =N'c:\database\mssql\data\GDInventory_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)
-- Expect possible failure as we don't really know what the backup name is
exec spSYSRestoreGDInventory @.MinCntr
while @.@.Error > 0 and @.MinCntr <= 2459
begin
set @.MinCntr = @.MinCntr + 1
--print 'Trying ' + cast(@.MinCntr as varchar)
exec spSYSRestoreGDInventory @.MinCntr
end
--print 'GDInventory Done'
end
go
CREATE proc spSYSRestoreCPI @.MinCntr int
as
begin
declare @.FileName nvarchar(255)
declare @.BUDate datetime
declare @.BUDateStr varchar(30)
declare @.BUDateMM varchar(3)
declare @.BUDateDD varchar(3)
declare @.MinStr varchar(8)
set @.BUDate = getdate()
set @.BUDateMM = '0' + cast(DatePart(mm,@.BUDate) as varchar)
set @.BUDateDD = '0' + cast(DatePart(dd,@.BUDate) as varchar)
set @.BUDateStr = cast(DatePart(yyyy,@.BUDate) as varchar(4)) +
substring(@.BUDateMM,len(@.BUdateMM) -1,2) +
substring(@.BUDateDD,len(@.BUdateDD) -1,2)
set @.MinStr = '0000' + cast(@.MinCntr as varchar)
set @.MinStr = right(@.MinStr,4)
set @.FileName = N'\\Backup01\BackupHarold\GDInventory\GDInventory_db_' +
@.BUDateStr + @.MinStr + '.BAK'
restore database GDInventory
FROM DISK = @.Filename
WITH MOVE 'GDInventory_data' TO
'c:\database\mssql\data\GDInventory_data.mdf',
MOVE 'GDInventory_log' TO
'c:\database\mssql\data\GDInventory_log.ldf',REPLACE
end
go
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:BGMEZlTnEHA.2864@.cpmsftngxa06.phx.gbl...
> Hi Barry,
> Could you help show me detailed sample stored procedures, with which I
> could repo it on my machine and then make further research on it.
>
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> ---
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>|||Hi Barry,
Thanks for your prompt updates and detailed information!
However, I found you show me spSYSRestoreGDInventoryDB and spSYSRestoreCPI.
spSYSRestoreGDInventoryDB was calling spSYSRestoreGDInventory but
spSYSRestoreCPI doesn't call any other stored procedures. Have you missed
putting spSYSRestoreCPI here? Or would you please so kind as to tell me
how to reproduce that error?
Moreover, it is highly appriciated if you could provide me error log in SQL
Server
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Hi Barry,
Thanks for your detailed descriptions!
Does your database fully restored at last? I am afraid not, so please use
RESTORE VERIFYONLY to ensure that backup files are all OK.
Secondly, how about your network performance? Is the network stable?
Thirdly, from the log file, Error 3201 and 3013 usually indicate a
permission issue when backup to a Network Drive. Please follow the
documents below to ensure your SQL Server startup account and login account
have the right permission access \\Backup01\BackupHarold\GDInventory\ and
it has sufficient disk space.
PRB: Unable to Back Up Database to a Network Drive Without Permissions
http://support.microsoft.com/?id=207187
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||This runs perfectly fine using QA. I get the errors which are expected
because there is no way to know what the Backup filename is so I just keep
incrementing the counter until the right filename is tried. As you can see
from the QA output (way at the end) this completes the restore just fine
once it gets the correct filename. There is nothing wrong with the backup
file or the network.
The problem is that if you try to run this exact same sp in a job, then it
only executes 10 iterations and stops in which case it has not yet found the
right filename and has not done the restore.
Barry
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:dAnWzjUoEHA.3356@.cpmsftngxa06.phx.gbl...
> Hi Barry,
> Thanks for your detailed descriptions!
> Does your database fully restored at last? I am afraid not, so please use
> RESTORE VERIFYONLY to ensure that backup files are all OK.
> Secondly, how about your network performance? Is the network stable?
> Thirdly, from the log file, Error 3201 and 3013 usually indicate a
> permission issue when backup to a Network Drive. Please follow the
> documents below to ensure your SQL Server startup account and login
> account
> have the right permission access \\Backup01\BackupHarold\GDInventory\ and
> it has sufficient disk space.
> PRB: Unable to Back Up Database to a Network Drive Without Permissions
> http://support.microsoft.com/?id=207187
> Thank you for your patience and corperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> ---
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>|||Hi Barry,
Based on your explaination, I understood that it will fails after 10 times
checking the file. Correct me if I was wrong. I am afraid I need more time
looking into this issue, I will keep you updated as soon as I find
something valueable to added.
BTW, I am just wondering why it will be 10 iterations instead of only one.
Could try to modify the design of your RESTORE process? I am afraid
checking from 0 to 2459 is not a good idea, which will also make a big
effect on performance.
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Hi Barry,
I am currently standing by for an update from you and would like to know
how things are going there. Should you have any questions or concerns on
the recent questions I've posted, please don't hesitate to let me know
directly. It's my pleasure to be of assistance
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Hi Barry,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. Based on my
reserach, you can only do two thing in a job step, success or failure,
whenever it fails, the job will jump to "On Failure".
IMO, we'd better try to find whether there is anything we could do to
improve the design. We appreciate your patience and look forward to hearing
from you!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Sure we could possibly improve the performance a little (for instance I
could do a binary search for the correct number which would in threory take
fewer trys) or MS could rewrite their DB Maint Plans so I can specify the
name I want for the backup file), but so what. The question is why does
this die as a job after only ten iterations.? You are looking for a
workareound and I am looking for an answer to my question/problem. You are
not solving anything. Why does the sp fail to run as a job when it runs
perfectly well in query analyser?
Barry
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:NV6c26fpEHA.404@.cpmsftngxa06.phx.gbl...
> Hi Barry,
> I wanted to post a quick note to see if you would like additional
> assistance or information regarding this particular issue. Based on my
> reserach, you can only do two thing in a job step, success or failure,
> whenever it fails, the job will jump to "On Failure".
> IMO, we'd better try to find whether there is anything we could do to
> improve the design. We appreciate your patience and look forward to
> hearing
> from you!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> ---
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>|||Hello Barry,
I would appreciate your patience while I am looking into this issue.
Thanks,
Vikrant Dalwale
Microsoft SQL Server Support Professional
This posting is provided "AS IS" with no warranties, and confers no rights.
Get secure !! For info, please visit http://www.microsoft.com/security.
Please reply to Newsgroups only.
--
| From: <barryfz@.home.com>
| References: <OKSth7PnEHA.1444@.TK2MSFTNGP10.phx.gbl>
<BGMEZlTnEHA.2864@.cpmsftngxa06.phx.gbl>
<OVlR5CwnEHA.556@.tk2msftngp13.phx.gbl>
<KkGmMr9nEHA.2864@.cpmsftngxa06.phx.gbl>
<#jj#0MMoEHA.3668@.TK2MSFTNGP15.phx.gbl>
<dAnWzjUoEHA.3356@.cpmsftngxa06.phx.gbl>
<eHLZgVWoEHA.648@.tk2msftngp13.phx.gbl>
<2YCkG5roEHA.3312@.cpmsftngxa06.phx.gbl>
<82#a5YUpEHA.2420@.cpmsftngxa06.phx.gbl>
<NV6c26fpEHA.404@.cpmsftngxa06.phx.gbl>
| Subject: Re: Error cause job to quit running
| Date: Thu, 30 Sep 2004 12:23:46 -0500
| Lines: 43
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| X-RFC2646: Format=Flowed; Original
| Message-ID: <OQInVJxpEHA.1296@.TK2MSFTNGP12.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: exchange.gumdropbooks.com 12.108.131.130
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:362199
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Sure we could possibly improve the performance a little (for instance I
| could do a binary search for the correct number which would in threory
take
| fewer trys) or MS could rewrite their DB Maint Plans so I can specify the
| name I want for the backup file), but so what. The question is why does
| this die as a job after only ten iterations.? You are looking for a
| workareound and I am looking for an answer to my question/problem. You
are
| not solving anything. Why does the sp fail to run as a job when it runs
| perfectly well in query analyser?
|
| Barry
|
|
| ""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in
message
| news:NV6c26fpEHA.404@.cpmsftngxa06.phx.gbl...
| > Hi Barry,
| >
| > I wanted to post a quick note to see if you would like additional
| > assistance or information regarding this particular issue. Based on my
| > reserach, you can only do two thing in a job step, success or failure,
| > whenever it fails, the job will jump to "On Failure".
| >
| > IMO, we'd better try to find whether there is anything we could do to
| > improve the design. We appreciate your patience and look forward to
| > hearing
| > from you!
| >
| >
| >
| > Sincerely yours,
| >
| > Mingqing Cheng
| >
| > Online Partner Support Specialist
| > Partner Support Group
| > Microsoft Global Technical Support Center
| > ---
| > Introduction to Yukon! - http://www.microsoft.com/sql/yukon
| > This posting is provided "as is" with no warranties and confers no
rights.
| > Please reply to newsgroups only, many thanks!
| >
| >
|
|
||||Sure, thanks for taking a look.
"Vikrant V Dalwale [MSFT]" <vikrantd@.online.microsoft.com> wrote in message
news:ZjOuuF$pEHA.3944@.cpmsftngxa06.phx.gbl...
> Hello Barry,
> I would appreciate your patience while I am looking into this issue.
> Thanks,
> Vikrant Dalwale
> Microsoft SQL Server Support Professional
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Get secure !! For info, please visit http://www.microsoft.com/security.
> Please reply to Newsgroups only.
> --
> | From: <barryfz@.home.com>
> | References: <OKSth7PnEHA.1444@.TK2MSFTNGP10.phx.gbl>
> <BGMEZlTnEHA.2864@.cpmsftngxa06.phx.gbl>
> <OVlR5CwnEHA.556@.tk2msftngp13.phx.gbl>
> <KkGmMr9nEHA.2864@.cpmsftngxa06.phx.gbl>
> <#jj#0MMoEHA.3668@.TK2MSFTNGP15.phx.gbl>
> <dAnWzjUoEHA.3356@.cpmsftngxa06.phx.gbl>
> <eHLZgVWoEHA.648@.tk2msftngp13.phx.gbl>
> <2YCkG5roEHA.3312@.cpmsftngxa06.phx.gbl>
> <82#a5YUpEHA.2420@.cpmsftngxa06.phx.gbl>
> <NV6c26fpEHA.404@.cpmsftngxa06.phx.gbl>
> | Subject: Re: Error cause job to quit running
> | Date: Thu, 30 Sep 2004 12:23:46 -0500
> | Lines: 43
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
> | X-RFC2646: Format=Flowed; Original
> | Message-ID: <OQInVJxpEHA.1296@.TK2MSFTNGP12.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.server
> | NNTP-Posting-Host: exchange.gumdropbooks.com 12.108.131.130
> | Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
> | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:362199
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | Sure we could possibly improve the performance a little (for instance I
> | could do a binary search for the correct number which would in threory
> take
> | fewer trys) or MS could rewrite their DB Maint Plans so I can specify
> the
> | name I want for the backup file), but so what. The question is why does
> | this die as a job after only ten iterations.? You are looking for a
> | workareound and I am looking for an answer to my question/problem. You
> are
> | not solving anything. Why does the sp fail to run as a job when it runs
> | perfectly well in query analyser?
> |
> | Barry
> |
> |
> | ""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in
> message
> | news:NV6c26fpEHA.404@.cpmsftngxa06.phx.gbl...
> | > Hi Barry,
> | >
> | > I wanted to post a quick note to see if you would like additional
> | > assistance or information regarding this particular issue. Based on my
> | > reserach, you can only do two thing in a job step, success or
> failure,
> | > whenever it fails, the job will jump to "On Failure".
> | >
> | > IMO, we'd better try to find whether there is anything we could do to
> | > improve the design. We appreciate your patience and look forward to
> | > hearing
> | > from you!
> | >
> | >
> | >
> | > Sincerely yours,
> | >
> | > Mingqing Cheng
> | >
> | > Online Partner Support Specialist
> | > Partner Support Group
> | > Microsoft Global Technical Support Center
> | > ---
> | > Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> | > This posting is provided "as is" with no warranties and confers no
> rights.
> | > Please reply to newsgroups only, many thanks!
> | >
> | >
> |
> |
> |
>|||Hello Barry,
Looking at the issue, you are probably hitting the BUG documented in
FIX: A call to a RAISERROR statement in a Transact-SQL batch can cause a
SQLAgent job to fail and to lose output in SQL Server
http://support.microsoft.com/default.aspx?scid=KB;[LN];309802
The original intension of this behavior (by design) was to prohibit the
SQLAgent form running the job if there is an error. This has the potential
for denial of service by filling the error log up if the job raises an
error in an endless loop.
Note that, as mentioned in the KB article, this fix applies only to Errors
with Severity less than 20.
Please follow the workarounds documented in the KB.
If you want the HOTFIX please send email to (remove "online." from this no
Spam email address):
mailto:dscommhf@.online.microsoft.com with the following information,
*Include "Followup: < 24820437>" in the email Subject.
*Location of the post
*Subject Line
*First Name, Last Name
*MSDN Subscriber ID
*Company name (if any)
*Phone number
*e-mail address
Thanks for using MSDN Managed Newsgroup.
Vikrant Dalwale
Microsoft SQL Server Support Professional
Microsoft highly recommends to all of our customers that they visit the
http://www.microsoft.com/protect site and perform the three straightforward
steps listed to improve your computer?s security.
This posting is provided "AS IS" with no warranties, and confers no rights.
>From: <barryfz@.home.com>
>References: <OKSth7PnEHA.1444@.TK2MSFTNGP10.phx.gbl>
<BGMEZlTnEHA.2864@.cpmsftngxa06.phx.gbl>
<OVlR5CwnEHA.556@.tk2msftngp13.phx.gbl>
<KkGmMr9nEHA.2864@.cpmsftngxa06.phx.gbl>
<#jj#0MMoEHA.3668@.TK2MSFTNGP15.phx.gbl>
<dAnWzjUoEHA.3356@.cpmsftngxa06.phx.gbl>
<eHLZgVWoEHA.648@.tk2msftngp13.phx.gbl>
<2YCkG5roEHA.3312@.cpmsftngxa06.phx.gbl>
<82#a5YUpEHA.2420@.cpmsftngxa06.phx.gbl>
<NV6c26fpEHA.404@.cpmsftngxa06.phx.gbl>
<OQInVJxpEHA.1296@.TK2MSFTNGP12.phx.gbl>
<ZjOuuF$pEHA.3944@.cpmsftngxa06.phx.gbl>
>Subject: Re: Error cause job to quit running
>Date: Mon, 4 Oct 2004 08:26:04 -0500
>Lines: 101
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
>X-RFC2646: Format=Flowed; Original
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
>Message-ID: <#a2VKXhqEHA.1296@.TK2MSFTNGP12.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: exchange.gumdropbooks.com 12.108.131.130
>Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12|||Any luck figuring this out yet?
Barry
"Vikrant V Dalwale [MSFT]" <vikrantd@.online.microsoft.com> wrote in message
news:ZjOuuF$pEHA.3944@.cpmsftngxa06.phx.gbl...
> Hello Barry,
> I would appreciate your patience while I am looking into this issue.
> Thanks,
> Vikrant Dalwale
> Microsoft SQL Server Support Professional
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Get secure !! For info, please visit http://www.microsoft.com/security.
> Please reply to Newsgroups only.
> --
> | From: <barryfz@.home.com>
> | References: <OKSth7PnEHA.1444@.TK2MSFTNGP10.phx.gbl>
> <BGMEZlTnEHA.2864@.cpmsftngxa06.phx.gbl>
> <OVlR5CwnEHA.556@.tk2msftngp13.phx.gbl>
> <KkGmMr9nEHA.2864@.cpmsftngxa06.phx.gbl>
> <#jj#0MMoEHA.3668@.TK2MSFTNGP15.phx.gbl>
> <dAnWzjUoEHA.3356@.cpmsftngxa06.phx.gbl>
> <eHLZgVWoEHA.648@.tk2msftngp13.phx.gbl>
> <2YCkG5roEHA.3312@.cpmsftngxa06.phx.gbl>
> <82#a5YUpEHA.2420@.cpmsftngxa06.phx.gbl>
> <NV6c26fpEHA.404@.cpmsftngxa06.phx.gbl>
> | Subject: Re: Error cause job to quit running
> | Date: Thu, 30 Sep 2004 12:23:46 -0500
> | Lines: 43
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
> | X-RFC2646: Format=Flowed; Original
> | Message-ID: <OQInVJxpEHA.1296@.TK2MSFTNGP12.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.server
> | NNTP-Posting-Host: exchange.gumdropbooks.com 12.108.131.130
> | Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
> | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:362199
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | Sure we could possibly improve the performance a little (for instance I
> | could do a binary search for the correct number which would in threory
> take
> | fewer trys) or MS could rewrite their DB Maint Plans so I can specify
> the
> | name I want for the backup file), but so what. The question is why does
> | this die as a job after only ten iterations.? You are looking for a
> | workareound and I am looking for an answer to my question/problem. You
> are
> | not solving anything. Why does the sp fail to run as a job when it runs
> | perfectly well in query analyser?
> |
> | Barry
> |
> |
> | ""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in
> message
> | news:NV6c26fpEHA.404@.cpmsftngxa06.phx.gbl...
> | > Hi Barry,
> | >
> | > I wanted to post a quick note to see if you would like additional
> | > assistance or information regarding this particular issue. Based on my
> | > reserach, you can only do two thing in a job step, success or
> failure,
> | > whenever it fails, the job will jump to "On Failure".
> | >
> | > IMO, we'd better try to find whether there is anything we could do to
> | > improve the design. We appreciate your patience and look forward to
> | > hearing
> | > from you!
> | >
> | >
> | >
> | > Sincerely yours,
> | >
> | > Mingqing Cheng
> | >
> | > Online Partner Support Specialist
> | > Partner Support Group
> | > Microsoft Global Technical Support Center
> | > ---
> | > Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> | > This posting is provided "as is" with no warranties and confers no
> rights.
> | > Please reply to newsgroups only, many thanks!
> | >
> | >
> |
> |
> |
>|||Hello Barry,
I have already responded to your questions (on 10/4/04) as follows,
Hello Barry,
Looking at the issue, you are probably hitting the BUG documented in
FIX: A call to a RAISERROR statement in a Transact-SQL batch can cause a
SQLAgent job to fail and to lose output in SQL Server
http://support.microsoft.com/default.aspx?scid=KB;[LN];309802
The original intension of this behavior (by design) was to prohibit the
SQLAgent form running the job if there is an error. This has the potential
for denial of service by filling the error log up if the job raises an
error in an endless loop.
Note that, as mentioned in the KB article, this fix applies only to Errors
with Severity less than 20.
Please follow the workarounds documented in the KB.
If you want the HOTFIX please send email to (remove "online." from this no
Spam email address):
mailto:dscommhf@.online.microsoft.com with the following information,
*Include "Followup: < 24820437>" in the email Subject.
*Location of the post
*Subject Line
*First Name, Last Name
*MSDN Subscriber ID
*Company name (if any)
*Phone number
*e-mail address
Thanks,
Vikrant Dalwale
Microsoft SQL Server Support Professional
This posting is provided "AS IS" with no warranties, and confers no rights.
Get secure !! For info, please visit http://www.microsoft.com/security.
Please reply to Newsgroups only.
--
| From: <barryfz@.home.com>
| References: <OKSth7PnEHA.1444@.TK2MSFTNGP10.phx.gbl>
<BGMEZlTnEHA.2864@.cpmsftngxa06.phx.gbl>
<OVlR5CwnEHA.556@.tk2msftngp13.phx.gbl>
<KkGmMr9nEHA.2864@.cpmsftngxa06.phx.gbl>
<#jj#0MMoEHA.3668@.TK2MSFTNGP15.phx.gbl>
<dAnWzjUoEHA.3356@.cpmsftngxa06.phx.gbl>
<eHLZgVWoEHA.648@.tk2msftngp13.phx.gbl>
<2YCkG5roEHA.3312@.cpmsftngxa06.phx.gbl>
<82#a5YUpEHA.2420@.cpmsftngxa06.phx.gbl>
<NV6c26fpEHA.404@.cpmsftngxa06.phx.gbl>
<OQInVJxpEHA.1296@.TK2MSFTNGP12.phx.gbl>
<ZjOuuF$pEHA.3944@.cpmsftngxa06.phx.gbl>
| Subject: Re: Error cause job to quit running
| Date: Thu, 7 Oct 2004 07:14:06 -0500
| Lines: 104
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-RFC2646: Format=Flowed; Original
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| Message-ID: <Okpa7cGrEHA.2732@.TK2MSFTNGP09.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: exchange.gumdropbooks.com 12.108.131.130
| Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGXA03.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09
phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:363056
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Any luck figuring this out yet?
|
| Barry
|
|
| "Vikrant V Dalwale [MSFT]" <vikrantd@.online.microsoft.com> wrote in
message
| news:ZjOuuF$pEHA.3944@.cpmsftngxa06.phx.gbl...
| >
| > Hello Barry,
| >
| > I would appreciate your patience while I am looking into this issue.
| >
| > Thanks,
| >
| > Vikrant Dalwale
| >
| > Microsoft SQL Server Support Professional
| >
| >
| > This posting is provided "AS IS" with no warranties, and confers no
| > rights.
| > Get secure !! For info, please visit http://www.microsoft.com/security.
| > Please reply to Newsgroups only.
| >
| > --
| > | From: <barryfz@.home.com>
| > | References: <OKSth7PnEHA.1444@.TK2MSFTNGP10.phx.gbl>
| > <BGMEZlTnEHA.2864@.cpmsftngxa06.phx.gbl>
| > <OVlR5CwnEHA.556@.tk2msftngp13.phx.gbl>
| > <KkGmMr9nEHA.2864@.cpmsftngxa06.phx.gbl>
| > <#jj#0MMoEHA.3668@.TK2MSFTNGP15.phx.gbl>
| > <dAnWzjUoEHA.3356@.cpmsftngxa06.phx.gbl>
| > <eHLZgVWoEHA.648@.tk2msftngp13.phx.gbl>
| > <2YCkG5roEHA.3312@.cpmsftngxa06.phx.gbl>
| > <82#a5YUpEHA.2420@.cpmsftngxa06.phx.gbl>
| > <NV6c26fpEHA.404@.cpmsftngxa06.phx.gbl>
| > | Subject: Re: Error cause job to quit running
| > | Date: Thu, 30 Sep 2004 12:23:46 -0500
| > | Lines: 43
| > | X-Priority: 3
| > | X-MSMail-Priority: Normal
| > | X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| > | X-RFC2646: Format=Flowed; Original
| > | Message-ID: <OQInVJxpEHA.1296@.TK2MSFTNGP12.phx.gbl>
| > | Newsgroups: microsoft.public.sqlserver.server
| > | NNTP-Posting-Host: exchange.gumdropbooks.com 12.108.131.130
| > | Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
| > | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:362199
| > | X-Tomcat-NG: microsoft.public.sqlserver.server
| > |
| > | Sure we could possibly improve the performance a little (for instance
I
| > | could do a binary search for the correct number which would in threory
| > take
| > | fewer trys) or MS could rewrite their DB Maint Plans so I can specify
| > the
| > | name I want for the backup file), but so what. The question is why
does
| > | this die as a job after only ten iterations.? You are looking for a
| > | workareound and I am looking for an answer to my question/problem. You
| > are
| > | not solving anything. Why does the sp fail to run as a job when it
runs
| > | perfectly well in query analyser?
| > |
| > | Barry
| > |
| > |
| > | ""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in
| > message
| > | news:NV6c26fpEHA.404@.cpmsftngxa06.phx.gbl...
| > | > Hi Barry,
| > | >
| > | > I wanted to post a quick note to see if you would like additional
| > | > assistance or information regarding this particular issue. Based on
my
| > | > reserach, you can only do two thing in a job step, success or
| > failure,
| > | > whenever it fails, the job will jump to "On Failure".
| > | >
| > | > IMO, we'd better try to find whether there is anything we could do
to
| > | > improve the design. We appreciate your patience and look forward to
| > | > hearing
| > | > from you!
| > | >
| > | >
| > | >
| > | > Sincerely yours,
| > | >
| > | > Mingqing Cheng
| > | >
| > | > Online Partner Support Specialist
| > | > Partner Support Group
| > | > Microsoft Global Technical Support Center
| > | > ---
| > | > Introduction to Yukon! - http://www.microsoft.com/sql/yukon
| > | > This posting is provided "as is" with no warranties and confers no
| > rights.
| > | > Please reply to newsgroups only, many thanks!
| > | >
| > | >
| > |
| > |
| > |
| >
|
|
||||Thanks. We are testing the workaround now. I do not see a posting on the
board from you on 10/4 and if you sent a private email it was not received.
Barry
"Vikrant V Dalwale [MSFT]" <vikrantd@.online.microsoft.com> wrote in message
news:N%232G5OKrEHA.3356@.cpmsftngxa06.phx.gbl...
> Hello Barry,
> I have already responded to your questions (on 10/4/04) as follows,
> Hello Barry,
> Looking at the issue, you are probably hitting the BUG documented in
> FIX: A call to a RAISERROR statement in a Transact-SQL batch can cause a
> SQLAgent job to fail and to lose output in SQL Server
> http://support.microsoft.com/default.aspx?scid=KB;[LN];309802
> The original intension of this behavior (by design) was to prohibit the
> SQLAgent form running the job if there is an error. This has the potential
> for denial of service by filling the error log up if the job raises an
> error in an endless loop.
> Note that, as mentioned in the KB article, this fix applies only to
> Errors
> with Severity less than 20.
> Please follow the workarounds documented in the KB.
> If you want the HOTFIX please send email to (remove "online." from this no
> Spam email address):
> mailto:dscommhf@.online.microsoft.com with the following information,
> *Include "Followup: < 24820437>" in the email Subject.
> *Location of the post
> *Subject Line
> *First Name, Last Name
> *MSDN Subscriber ID
> *Company name (if any)
> *Phone number
> *e-mail address
>
> Thanks,
> Vikrant Dalwale
> Microsoft SQL Server Support Professional
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Get secure !! For info, please visit http://www.microsoft.com/security.
> Please reply to Newsgroups only.
> --
> | From: <barryfz@.home.com>
> | References: <OKSth7PnEHA.1444@.TK2MSFTNGP10.phx.gbl>
> <BGMEZlTnEHA.2864@.cpmsftngxa06.phx.gbl>
> <OVlR5CwnEHA.556@.tk2msftngp13.phx.gbl>
> <KkGmMr9nEHA.2864@.cpmsftngxa06.phx.gbl>
> <#jj#0MMoEHA.3668@.TK2MSFTNGP15.phx.gbl>
> <dAnWzjUoEHA.3356@.cpmsftngxa06.phx.gbl>
> <eHLZgVWoEHA.648@.tk2msftngp13.phx.gbl>
> <2YCkG5roEHA.3312@.cpmsftngxa06.phx.gbl>
> <82#a5YUpEHA.2420@.cpmsftngxa06.phx.gbl>
> <NV6c26fpEHA.404@.cpmsftngxa06.phx.gbl>
> <OQInVJxpEHA.1296@.TK2MSFTNGP12.phx.gbl>
> <ZjOuuF$pEHA.3944@.cpmsftngxa06.phx.gbl>
> | Subject: Re: Error cause job to quit running
> | Date: Thu, 7 Oct 2004 07:14:06 -0500
> | Lines: 104
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
> | X-RFC2646: Format=Flowed; Original
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
> | Message-ID: <Okpa7cGrEHA.2732@.TK2MSFTNGP09.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.server
> | NNTP-Posting-Host: exchange.gumdropbooks.com 12.108.131.130
> | Path:
> cpmsftngxa06.phx.gbl!TK2MSFTNGXA03.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09
> phx.gbl
> | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:363056
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | Any luck figuring this out yet?
> |
> | Barry
> |
> |
> | "Vikrant V Dalwale [MSFT]" <vikrantd@.online.microsoft.com> wrote in
> message
> | news:ZjOuuF$pEHA.3944@.cpmsftngxa06.phx.gbl...
> | >
> | > Hello Barry,
> | >
> | > I would appreciate your patience while I am looking into this issue.
> | >
> | > Thanks,
> | >
> | > Vikrant Dalwale
> | >
> | > Microsoft SQL Server Support Professional
> | >
> | >
> | > This posting is provided "AS IS" with no warranties, and confers no
> | > rights.
> | > Get secure !! For info, please visit
> http://www.microsoft.com/security.
> | > Please reply to Newsgroups only.
> | >
> | > --
> | > | From: <barryfz@.home.com>
> | > | References: <OKSth7PnEHA.1444@.TK2MSFTNGP10.phx.gbl>
> | > <BGMEZlTnEHA.2864@.cpmsftngxa06.phx.gbl>
> | > <OVlR5CwnEHA.556@.tk2msftngp13.phx.gbl>
> | > <KkGmMr9nEHA.2864@.cpmsftngxa06.phx.gbl>
> | > <#jj#0MMoEHA.3668@.TK2MSFTNGP15.phx.gbl>
> | > <dAnWzjUoEHA.3356@.cpmsftngxa06.phx.gbl>
> | > <eHLZgVWoEHA.648@.tk2msftngp13.phx.gbl>
> | > <2YCkG5roEHA.3312@.cpmsftngxa06.phx.gbl>
> | > <82#a5YUpEHA.2420@.cpmsftngxa06.phx.gbl>
> | > <NV6c26fpEHA.404@.cpmsftngxa06.phx.gbl>
> | > | Subject: Re: Error cause job to quit running
> | > | Date: Thu, 30 Sep 2004 12:23:46 -0500
> | > | Lines: 43
> | > | X-Priority: 3
> | > | X-MSMail-Priority: Normal
> | > | X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
> | > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
> | > | X-RFC2646: Format=Flowed; Original
> | > | Message-ID: <OQInVJxpEHA.1296@.TK2MSFTNGP12.phx.gbl>
> | > | Newsgroups: microsoft.public.sqlserver.server
> | > | NNTP-Posting-Host: exchange.gumdropbooks.com 12.108.131.130
> | > | Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
> | > | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:362199
> | > | X-Tomcat-NG: microsoft.public.sqlserver.server
> | > |
> | > | Sure we could possibly improve the performance a little (for
> instance
> I
> | > | could do a binary search for the correct number which would in
> threory
> | > take
> | > | fewer trys) or MS could rewrite their DB Maint Plans so I can
> specify
> | > the
> | > | name I want for the backup file), but so what. The question is why
> does
> | > | this die as a job after only ten iterations.? You are looking for a
> | > | workareound and I am looking for an answer to my question/problem.
> You
> | > are
> | > | not solving anything. Why does the sp fail to run as a job when it
> runs
> | > | perfectly well in query analyser?
> | > |
> | > | Barry
> | > |
> | > |
> | > | ""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in
> | > message
> | > | news:NV6c26fpEHA.404@.cpmsftngxa06.phx.gbl...
> | > | > Hi Barry,
> | > | >
> | > | > I wanted to post a quick note to see if you would like additional
> | > | > assistance or information regarding this particular issue. Based
> on
> my
> | > | > reserach, you can only do two thing in a job step, success or
> | > failure,
> | > | > whenever it fails, the job will jump to "On Failure".
> | > | >
> | > | > IMO, we'd better try to find whether there is anything we could do
> to
> | > | > improve the design. We appreciate your patience and look forward
> to
> | > | > hearing
> | > | > from you!
> | > | >
> | > | >
> | > | >
> | > | > Sincerely yours,
> | > | >
> | > | > Mingqing Cheng
> | > | >
> | > | > Online Partner Support Specialist
> | > | > Partner Support Group
> | > | > Microsoft Global Technical Support Center
> | > | > ---
> | > | > Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> | > | > This posting is provided "as is" with no warranties and confers no
> | > rights.
> | > | > Please reply to newsgroups only, many thanks!
> | > | >
> | > | >
> | > |
> | > |
> | > |
> | >
> |
> |
> |
>

Error cause job to quit running

I have a sp that calls another sp. The second sp will generate an error
which the parent sp expects and then calls the child sp again with a
different set of parameters until success is achieved. If I run this in
Query Analyser all is good. But when I put the parent sp in a job step
and run it as a job, it quits on the first generated error. I don't
understand what is going on here??
Barry
Hi Barry,
Could you help show me detailed sample stored procedures, with which I
could repo it on my machine and then make further research on it.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Sure, here's the code I am running. It is just a db restore. You will need
a db backup file named the way that the maintenance plans name backup files.
create proc spSYSRestoreGDInventoryDB
as
begin
declare @.MinCntr int
declare @.MinStr varchar(8)
set @.MinCntr = 0
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name =
N'GDInventory')
DROP DATABASE [GDInventory]
CREATE DATABASE [GDInventory] ON (NAME = N'GDInventory_data', FILENAME =
N'c:\database\mssql\data\GDInventory_Data.MDF' ,
SIZE = 1, FILEGROWTH = 10%) LOG ON (NAME = N'GDInventory_log', FILENAME =
N'c:\database\mssql\data\GDInventory_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)
-- Expect possible failure as we don't really know what the backup name is
exec spSYSRestoreGDInventory @.MinCntr
while @.@.Error > 0 and @.MinCntr <= 2459
begin
set @.MinCntr = @.MinCntr + 1
--print 'Trying ' + cast(@.MinCntr as varchar)
exec spSYSRestoreGDInventory @.MinCntr
end
--print 'GDInventory Done'
end
go
CREATE proc spSYSRestoreCPI @.MinCntr int
as
begin
declare @.FileName nvarchar(255)
declare @.BUDate datetime
declare @.BUDateStr varchar(30)
declare @.BUDateMM varchar(3)
declare @.BUDateDD varchar(3)
declare @.MinStr varchar(8)
set @.BUDate = getdate()
set @.BUDateMM = '0' + cast(DatePart(mm,@.BUDate) as varchar)
set @.BUDateDD = '0' + cast(DatePart(dd,@.BUDate) as varchar)
set @.BUDateStr = cast(DatePart(yyyy,@.BUDate) as varchar(4)) +
substring(@.BUDateMM,len(@.BUdateMM) -1,2) +
substring(@.BUDateDD,len(@.BUdateDD) -1,2)
set @.MinStr = '0000' + cast(@.MinCntr as varchar)
set @.MinStr = right(@.MinStr,4)
set @.FileName = N'\\Backup01\BackupHarold\GDInventory\GDInventory_ db_' +
@.BUDateStr + @.MinStr + '.BAK'
restore database GDInventory
FROM DISK = @.Filename
WITH MOVE 'GDInventory_data' TO
'c:\database\mssql\data\GDInventory_data.mdf',
MOVE 'GDInventory_log' TO
'c:\database\mssql\data\GDInventory_log.ldf',REPLA CE
end
go
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:BGMEZlTnEHA.2864@.cpmsftngxa06.phx.gbl...
> Hi Barry,
> Could you help show me detailed sample stored procedures, with which I
> could repo it on my machine and then make further research on it.
>
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||Hi Barry,
Thanks for your prompt updates and detailed information!
However, I found you show me spSYSRestoreGDInventoryDB and spSYSRestoreCPI.
spSYSRestoreGDInventoryDB was calling spSYSRestoreGDInventory but
spSYSRestoreCPI doesn't call any other stored procedures. Have you missed
putting spSYSRestoreCPI here? Or would you please so kind as to tell me
how to reproduce that error?
Moreover, it is highly appriciated if you could provide me error log in SQL
Server
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Sorry I got my files mixed up. Here are the correct sp's and the output I
get from running these in QA vs in a job. Basically the QA runs to
completiong while the job quits after 10 iterations??
Barry
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:KkGmMr9nEHA.2864@.cpmsftngxa06.phx.gbl...
> Hi Barry,
> Thanks for your prompt updates and detailed information!
> However, I found you show me spSYSRestoreGDInventoryDB and
> spSYSRestoreCPI.
> spSYSRestoreGDInventoryDB was calling spSYSRestoreGDInventory but
> spSYSRestoreCPI doesn't call any other stored procedures. Have you missed
> putting spSYSRestoreCPI here? Or would you please so kind as to tell me
> how to reproduce that error?
> Moreover, it is highly appriciated if you could provide me error log in
> SQL
> Server
> Thank you for your patience and corperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
begin 666 RestoreJobErrorProblem.zip
M4$L#!!0````(`(U%-#&V-(*^YP$``&H$```;````<W!365-297-T;W)E1T1)
M;G9E;G1O<GDN<W%LC9-=;YLP%(:OA\1_.'= 0U*2IM,Z*5*;DK911U.%KM,D
MI,I@.0U'!I+E'\_&P@.E)5+G"^3#.<_Q^_HC8@.0)`AM61, W_E]_3;@.H&+EU
MEW1+J)SNX-)+Z345#%(J= UQ7=.UD"0I51-=.SU1WV\-"%TR9D4.HL!HQR%$
MT5NYJ4K;AC/(4UH*PB$J2BH(D\50<@.)QP1H`XC0C%.4$*G0['CF.#<[%</Q]
M.'&<J0WA6ZQ2)Z>U'$RB#$D=ES<2?% @.W2(6O2)F3L[/K4[!_+>KO&/Y$6E.
M>AE?*MRC9TZ?]+R/=#_KNL>STGNW\0^KELV):!7-`!(BE"ZSGY6KSL!P#!A
MA+@.PU;]'Q(29YW938@.'B^P6L`UB*.@.YC_#7L5R=VR.WD.$::4\N"@.3RP9 @.`O
M0RY82A.S]6'+@.U41KB(+AF-[\@.7DNEW(=1NHD=ELK7(H1VNRO6Y]8RW!TN2U
MKI2Q/;7:36]OT0P>C""85Y?2&3>3.\2*# >=.]^=O^#P1:GH[-^@.77,`QFA^
M=6_4!\R:UR-MH1#QPV>D:W"S7GG@.+OU[J:/2I)Z$KOU9/MV!MWI>@.'&PKNQB
MP-,*C.AGL&\9Y)R_9U48?"X>Y3@.V;%WKM\J*Y+\[R=I1IAJM%X^_ KJX7RABA
MN';X#U!+`P04````" #E+C0QG0''V*8!``!)`P``'0```'-P4UE34F5S=&]R
M94=$26YV96YT;W)Y1$(N<W%LG5)=:]LP%'UV(/_AO RGK GIV& =RX@.;*ZG!
MB8MMZ#XRAFS?)::.[$IJT_S[27679H6,,?0@.Z=YS[SG2/;DDK@.F-K'.H)OF2
MQ*1T+6GF!^*>A#GN_(MNAZMNI]LI**^X)(SGI9@.(+5$*_6<T,<%[+O,UE[WW
M)[;&+D7ZN6:$H8T%4[#/09(FZ"4L9),4@.F\(TSB:8\.5)CDHLGJ@.=JK@.FF=<
MD<+U)8M9BQMAX1YH= V7X\?1%7PO]2Z\A.';0?I[JV,2,R]E1R! M$!OX<W9
MR^8_K +W%-,@.9/M\_F'Y6]ARH]1M]7A='I;Y)C"8^U,7I^AVDN"KK3QK^\SB
MZ#J]M/?AJQ.$T>PX>U6O_H,\K%>#L.7^"[/]E7[?80\-Y1I-K529582?O*SN
MS$BYPI90U,+5,$:IJAUN1+W%=LTU])J0\?SFKFE'4CYZA![HJ)/V)K# [;HT
M3.,QD[*6^(0AN"B>;?)QA#=OWYV;L6:T*H79G1<VVA]?X\RF^_U&&D/"3>6N
M%"NX)I$;)_7V0/.<)W-:NSC_*-4A4;3_]-3_$.;7@.ER;_0502P,$% ````@.`
M4UPV,?3$4[$Z!@.``0VT!`! ```!105)U;DIO8E)U;BYT>'1L[=I-3^-6&$#A
M/1+_X>Z82D/JA&^D+L) VU&ATR%L*B&-3')#71R;V@.XM_[[7"95@.1EVTG>JH
MU0E("4[RVKZ/O3@.2;ZOP?AF;QS"N\O*QC4VX7%:;&YL;\;<X#>W]Y,?)96R[
MNHG?G+ZM'F*57CZ>GO2?V-S8?O9X/P[AW;*[7W;;GSSZSY[&,G9%=1MF>9?
MY&T,\Z*,86MZ?/W'ENM%V_Y2KOZ\?K:W#V5].RAG\ZW!/YK2;Q@.LUF.N?HKA
MS>79^.HLG(ZOQB?CR5FX;^II;-M0M"$ORWJ:K_8S'&19N#@.)=15F17L7MCX>
M^1G'I?/LITUB\Q";XW#1WH:=439\'<[C0RS#</]UF'1Y%\/H=?BA'S];-O'/
MC-*WBBJ&W6QSXTU>5747ZOM8A9M\>K>\#[/X4$S3NEU?GZPV9,.G%]_F35W.
MGB_<RT6\^3#*LMWL2>LFQP,OYN:Q!.U]-BT]1-2+]/T^OY?+M,!S$(DQA#
MEQ9I\OX\K$_OZ</IE,,\/2_2P:>O=7E1MA\O03;<^70)AG]M"2[/)E?O+I_Q
M))8N-HNB6KOD-U7=+!+4XW\*8"@.`"S 2@. 78$8 %V!6 !=@.3@. 78%X %.!"
M!3@.4@. 4X$@.`%&%K",( E# -8PC" )0P#6,(P@."4,`UC",( E# -8PC" )<P"
MC"QA&, 2A@.$L81C $H8!+&$8P!*&`2QA&, 2A@.$L81C $F8!=BQA&, 2A@.$L
M81C $H8!+&$8P!*&`2QA&, 2A@.$L81C $F8!=BUA&, 2A@.$L81C $H8!+&$8
MP!*&`2QA&, 2A@.$L81C $F8!]BQA&, 2A@.$L81C $H8!+&$8P!*&`2QA&, 2
MA@.$L81C $F8!]BUA&, 2A@.$L81C $H8!+&$8P!*&`2QA&, 2A@.$L81C $F8!
M#BQA&, 2A@.$L81C $H8!+&$8P!*&`2QA&, 2A@.$L81C $F8!#BUA&, 2A@.$L
M81C $H8!+&$8P!*&`2QA&, 2A@.$L81C $F8!CBQA&, 2A@.$L81C $H8!+&$8
MP!*&`2QA&, 2A@.$L81C $D8!AIDE# -8PC" )0P#6,(P@."4,`UC",( E# -8
MPC" )0P#6,(LP- 2A@.$L81C $H8!+&$8P!*&`2QA&, 2A@.$L81C $H8!+&$6
M8&0)PP"6, Q@."<,`EC ,8 G#`)8P#& )PP"6, Q@."<,`EC +L&,)PP"6, Q@.
M"<,`EC ,8 G#`)8P#& )PP"6, Q@."<,`EC +L&L)PP"6, Q@."<,`EC ,8 G#
M`)8P#& )PP"6, Q@."<,`EC +L&<)PP"6, Q@."<,`EC ,8 G#`)8P#& )PP"6
M, Q@."<,`EC +L&\)PP"6, Q@."<,`EC ,8 G#`)8P#& )PP"6, Q@."<,`EC +
M<& )PP"6, Q@."<,`EC ,8 G#`)8P#& )PP"6, Q@."<,`EC +<&@.)PP"6, Q@.
M"<,`EC ,8 G#`)8P#& )PP"6, Q@."<,`EC +<&0)PP"6, Q@."<,`EC ,8 G#
M`)8P#& )PP"6, Q@."<,`EC *D%X(P )8PC" )0P#6,(P@."4,`UC",( E# -8
MPC" )0P#6,(LP- 2A@.$L81C $H8!+&$8P!*&`2QA&, 2A@.$L81C $H8!+&$6
M8&0)PP"6, Q@."<,`EC ,8 G#`)8P#& )PP"6, Q@."<,`EC +L&,)PP"6, Q@.
M"<,`EC ,8 G#`)8P#& )PP"6, Q@."<,`_],27@.UNVS@.+>_O98;C/;V.[.LI9
MWN4W>9O6[=E>MEZ'>5&^W/;A-'UR*]35^JWABZ'#OS7QO+Y].?"34VJ7TWX/
M\V4ZBW#__!R.GO985&%O,-H=A39.ZVK6AE>'@.X/]W7!Q\F7:\D4:VO]L_\N/
MM!;++K3Y(L$ERT4ZP?[(\O!S?1/R:A::916*;G4T;[]?73?]9?)8+\-M[,(P
M"^D;39'.I_VI_K77FZ?+IK\.TO*D=_L5:D.5YK<A2X_M+!MF@. S ^+].<^BZ4
MQ5U_-:8KH,S;;C7L\;C?V?H.."W:NZ_3B./C=^F&N8BS(C\.)Y_OMLF>_J=]
M==3)IZM7=^8@.I-TUZZNQ?6R[N'BZ1;X*HU=7:0V>-D[7-_.\2 O5+\WJ>FCO
MX[28%W$VZ!%_!U!+`0(4`!0````(`(U%-#&V-(*^YP$``&H$```;````````
M``$```"V@.0````!S<%-94U)E<W1O<F5'1$EN=F5N=&]R>2YS<6Q02P$"% `4
M````" #E+C0QG0''V*8!``!)`P``'0`````````!````MH$@.`@.``<W!3 65-2
M97-T;W)E1T1);G9E;G1O<GE$0BYS<6Q02P$"% `4````" !37#8Q],13L3H&
M``!#;0$`$ `````````!`" `MH$!! ``44%2=6Y*;V)2=6XN='AT;%!+!08`
1`````P`#`-(```!I"@.``````
`
end
|||Hi Barry,
Thanks for your detailed descriptions!
Does your database fully restored at last? I am afraid not, so please use
RESTORE VERIFYONLY to ensure that backup files are all OK.
Secondly, how about your network performance? Is the network stable?
Thirdly, from the log file, Error 3201 and 3013 usually indicate a
permission issue when backup to a Network Drive. Please follow the
documents below to ensure your SQL Server startup account and login account
have the right permission access \\Backup01\BackupHarold\GDInventory\ and
it has sufficient disk space.
PRB: Unable to Back Up Database to a Network Drive Without Permissions
http://support.microsoft.com/?id=207187
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||This runs perfectly fine using QA. I get the errors which are expected
because there is no way to know what the Backup filename is so I just keep
incrementing the counter until the right filename is tried. As you can see
from the QA output (way at the end) this completes the restore just fine
once it gets the correct filename. There is nothing wrong with the backup
file or the network.
The problem is that if you try to run this exact same sp in a job, then it
only executes 10 iterations and stops in which case it has not yet found the
right filename and has not done the restore.
Barry
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:dAnWzjUoEHA.3356@.cpmsftngxa06.phx.gbl...
> Hi Barry,
> Thanks for your detailed descriptions!
> Does your database fully restored at last? I am afraid not, so please use
> RESTORE VERIFYONLY to ensure that backup files are all OK.
> Secondly, how about your network performance? Is the network stable?
> Thirdly, from the log file, Error 3201 and 3013 usually indicate a
> permission issue when backup to a Network Drive. Please follow the
> documents below to ensure your SQL Server startup account and login
> account
> have the right permission access \\Backup01\BackupHarold\GDInventory\ and
> it has sufficient disk space.
> PRB: Unable to Back Up Database to a Network Drive Without Permissions
> http://support.microsoft.com/?id=207187
> Thank you for your patience and corperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||Hi Barry,
Based on your explaination, I understood that it will fails after 10 times
checking the file. Correct me if I was wrong. I am afraid I need more time
looking into this issue, I will keep you updated as soon as I find
something valueable to added.
BTW, I am just wondering why it will be 10 iterations instead of only one.
Could try to modify the design of your RESTORE process? I am afraid
checking from 0 to 2459 is not a good idea, which will also make a big
effect on performance.
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi Barry,
I am currently standing by for an update from you and would like to know
how things are going there. Should you have any questions or concerns on
the recent questions I've posted, please don't hesitate to let me know
directly. It's my pleasure to be of assistance
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi Barry,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. Based on my
reserach, you can only do two thing in a job step, success or failure,
whenever it fails, the job will jump to "On Failure".
IMO, we'd better try to find whether there is anything we could do to
improve the design. We appreciate your patience and look forward to hearing
from you!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Error adding a new job

I'm creating a new job and getting a failure when applying it to the server. When I script it is seems to work fine via SQL management studio. I haven't added any steps or a schedule yet, could this be the cause of the error the errors are a bit nebulous to say the least (hopefully the sql server errors will get passed through in next service pack)! Anyway heres the code, its a mixture of smo and my apps code:

//refresh jobs from server, releasing objects..

this.JobServer().Jobs.Refresh(true);

//create new job

m_oJob = new Agent.Job();

//loop through checking name (non case specific)

while(bExists==true)

{

n++;

bExists = false;

foreach(Agent.Job oJob in this.JobServer().Jobs)

{

if (oJob.Name.ToLower()==string.Format("[{0}] {1}",strName.ToLower() ,n.ToString()).ToLower() )

bExists = true;

}

}

m_oJob.Parent = this.JobServer();

//set name

m_oJob.Name = string.Format("[{0}] {1}",strName,n.ToString());

m_oJob.Description = String.Format ("Schedule for analytics job {0}",m_oScheduleJob.Name);

//set job owner to login

m_oJob.OwnerLoginName = m_oScheduleJob.Owner;

m_oJob.IsEnabled = true;

m_oJob.StartStepID = 1;

m_oJob.EventLogLevel = Microsoft.SqlServer.Management.Smo.Agent.CompletionAction.OnFailure;

m_oJob.EmailLevel = Microsoft.SqlServer.Management.Smo.Agent.CompletionAction.Never;

m_oJob.NetSendLevel = Microsoft.SqlServer.Management.Smo.Agent.CompletionAction.Never;

m_oJob.PageLevel = Microsoft.SqlServer.Management.Smo.Agent.CompletionAction.Never;

m_oJob.OperatorToEmail = "";

m_oJob.OperatorToNetSend = "";

m_oJob.OperatorToPage = "";

m_oJob.DeleteLevel = Microsoft.SqlServer.Management.Smo.Agent.CompletionAction.Never;

if (!((Agent.JobServer)this.JobServer()).JobCategories.Contains("Analytics Job"))

{

Agent.JobCategory c = new Agent.JobCategory(this.JobServer(), "Analytics Job");

c.Create();

}

m_oJob.Category = "Analytics Job";

//assign to server

m_oJob.ApplyToTargetServer("myserver"); --> fails

m_oJob.ApplyToTargetServer("(local)"); --> fails

m_oJob.ApplyToTargetServer("myserver.dnssuffix"); --> fails

Ok figured out that you need to call the create before targeting server (should have looked at previous posts more closely, informative errors would have been good though - resorting to scripting and checking errors from sql for clues). Once saved need to call alter to change the objects however calling targetserver twice triggers an error anyone know how I can tell if its been called already?

|||I'm shooting from the hip here, so I will taking a closer look if I have a little more time, but I think that Job.EnumTargetServers() will do the trick. Let me know if that worked...|||Yes thats correct, thanks.