db files: job

Showing posts with label job. Show all posts

Monday, March 26, 2012

Running TSQL job for SQL server agent is failing, giving me ANSI errors?

Does anybody know why this statement wont run on a schedule for the
server agent job. I ran it as a TSQL statement and tried it as a
stored procedure and both failed. this is what I'm trying to run
below...
-- Following is a simple procedure for creating a temporary table
-- that has the table_name of each of the user tables in the linked
server.
Declare
@.Table_Name varchar(255),
@.Sql Nvarchar(500)
-- Create a temporary table for storing result of sp_tables_ex
Create Table #tLinkedServerTables
(
Table_Cat varchar(255),
Table_schem varchar(255),
Table_Name varchar(255),
Table_Type varchar(255),
Remarks varchar(255)
)
-- Populate Temporary table with the results of the sp_tables_ex
command
-- NOTE: the paramater passed to sp_tables_ex MUST be the name of a
-- valid linked server (already defined)
Insert Into #tLinkedServerTables exec sp_tables_ex 'TLRA'
-- Create cursor for selecting the Table names from the linked server
Declare crsLinkedServerTables Cursor For
Select table_name
>From #tLinkedServerTables
Where Table_type = 'Table'
and table_name like 'CF%'
and table_name not like 'CF*%'
and table_name not like 'CFDAILY%' --test 'public.CF%'
-- Open the cursor defined above
Open crsLinkedServerTables
Fetch Next From crsLinkedServerTables Into @.Table_Name
While @.@.Fetch_Status = 0 Begin -- 0 = more records to process
-- Your Update process goes here... Need to use dynamic SQL to
create the
-- Query.
Set @.Sql = 'Update WorkList SET [Tickler Last Action DT] =
Data.[Last_Action_Date], [Next Work Date] = Data.[Next_Work_Date] FROM
OPENQUERY(TLRA,''SELECT [Account_ID], [Last_Action_Date],
[Next_Work_Date] FROM public.' + @.table_name + ''') as Data Where
Data.Account_ID = WorkList.[ACCOUNT#] AND WorkList.Logon = ''TLRA'''
print @.Sql
exec (@.Sql) /* Better to use sp_executesql, but this is here as
an example and should work */
-- Move to the next record
Fetch Next From crsLinkedServerTables Into @.Table_Name
End
-- Clean up (drop temp tables, remove cursors)
select * from #tLinkedServerTables
drop table #tLinkedServerTables
Close crsLinkedServerTables
Deallocate crsLinkedServerTables
This is what it shows in the job history when I run it as a TSQL
statement and it fails.
... [Tickler Last Action DT] = Data.[Last_Action_Date], [Next Work
Date] = Data.[Next_Work_Date] FROM OPENQUERY(SCH,'SELECT [Account_ID],
[Last_Action_Date], [Next_Work_Date] FROM public.CFAB1') as Data Where
Data.Account_ID = WorkList.[ACCOUNT#] AND WorkList.Logon = 'SCH'
[SQLSTATE 01000] (Message 0) Update WorkList SET [Tickler Last Action
DT] = Data.[Last_Action_Date], [Next Work Date] = Data.[Next_Work_Date]
FROM OPENQUERY(SCH,'SELECT [Account_ID], [Last_Action_Date],
[Next_Work_Date] FROM public.CFAC1') as Data Where Data.Account_ID =
WorkList.[ACCOUNT#] AND WorkList.Logon = 'SCH' [SQLSTATE 01000]
(Message 0) Update WorkList SET [Tickler Last Action DT] =
Data.[Last_Action_Date], [Next Work Date] = Data.[Next_Work_Date] FROM
OPENQUERY(SCH,'SELECT [Account_ID], [Last_Action_Date],
[Next_Work_Date] FROM public.CFAD1') as Data Where Data.Account_ID =
WorkList.[ACCOUNT#] AND WorkList.Logon = 'SCH' [SQLSTATE 01000]
(Message 0) Update WorkList SET [Tickler Last Action DT] =
Data.[Last_... The step failed.
I also tried to make a stored procedure and I get an error when I
execute it in a job as well. My job run statement exec [SCH CF
UPDATE]. This is the error I get below.
Update WorkList SET [Tickler Last Action DT] = Data.[Last_Action_Date],
[Next Work Date] = Data.[Next_Work_Date] FROM OPENQUERY(SCH,'SELECT
[Account_ID], [Last_Action_Date], [Next_Work_Date] FROM public.CFAB1')
as Data Where Data.Account_ID = WorkList.[ACCOUNT#] AND WorkList.Logon
= 'SCH' [SQLSTATE 01000] (Message 0) Heterogeneous queries require the
ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This
ensures consistent query semantics. Enable these options and then
reissue your query. [SQLSTATE 42000] (Error 7405). The step failed.
Hi Mike
"mike11d11" wrote:

> Does anybody know why this statement wont run on a schedule for the
> server agent job. I ran it as a TSQL statement and tried it as a
> stored procedure and both failed. this is what I'm trying to run
> below...
> ----
> --
> -- Following is a simple procedure for creating a temporary table
> -- that has the table_name of each of the user tables in the linked
> server.
> --
> ----
> Declare
> @.Table_Name varchar(255),
> @.Sql Nvarchar(500)
> ----
> -- Create a temporary table for storing result of sp_tables_ex
> ----
> Create Table #tLinkedServerTables
> (
> Table_Cat varchar(255),
> Table_schem varchar(255),
> Table_Name varchar(255),
> Table_Type varchar(255),
> Remarks varchar(255)
> )
> ----
> -- Populate Temporary table with the results of the sp_tables_ex
> command
> -- NOTE: the paramater passed to sp_tables_ex MUST be the name of a
> -- valid linked server (already defined)
> ----
> Insert Into #tLinkedServerTables exec sp_tables_ex 'TLRA'
> ----
> -- Create cursor for selecting the Table names from the linked server
> ----
> Declare crsLinkedServerTables Cursor For
> Select table_name
> Where Table_type = 'Table'
> and table_name like 'CF%'
> and table_name not like 'CF*%'
> and table_name not like 'CFDAILY%' --test 'public.CF%'
> -- Open the cursor defined above
> Open crsLinkedServerTables
> Fetch Next From crsLinkedServerTables Into @.Table_Name
> While @.@.Fetch_Status = 0 Begin -- 0 = more records to process
> -- Your Update process goes here... Need to use dynamic SQL to
> create the
> -- Query.
> Set @.Sql = 'Update WorkList SET [Tickler Last Action DT] =
> Data.[Last_Action_Date], [Next Work Date] = Data.[Next_Work_Date] FROM
> OPENQUERY(TLRA,''SELECT [Account_ID], [Last_Action_Date],
> [Next_Work_Date] FROM public.' + @.table_name + ''') as Data Where
> Data.Account_ID = WorkList.[ACCOUNT#] AND WorkList.Logon = ''TLRA'''
> print @.Sql
> exec (@.Sql) /* Better to use sp_executesql, but this is here as
> an example and should work */
> -- Move to the next record
> Fetch Next From crsLinkedServerTables Into @.Table_Name
> End
> ----
> -- Clean up (drop temp tables, remove cursors)
> ----
> select * from #tLinkedServerTables
> drop table #tLinkedServerTables
> Close crsLinkedServerTables
> Deallocate crsLinkedServerTables
>
>
>
>
>
> This is what it shows in the job history when I run it as a TSQL
> statement and it fails.
>
> ... [Tickler Last Action DT] = Data.[Last_Action_Date], [Next Work
> Date] = Data.[Next_Work_Date] FROM OPENQUERY(SCH,'SELECT [Account_ID],
> [Last_Action_Date], [Next_Work_Date] FROM public.CFAB1') as Data Where
> Data.Account_ID = WorkList.[ACCOUNT#] AND WorkList.Logon = 'SCH'
> [SQLSTATE 01000] (Message 0) Update WorkList SET [Tickler Last Action
> DT] = Data.[Last_Action_Date], [Next Work Date] = Data.[Next_Work_Date]
> FROM OPENQUERY(SCH,'SELECT [Account_ID], [Last_Action_Date],
> [Next_Work_Date] FROM public.CFAC1') as Data Where Data.Account_ID =
> WorkList.[ACCOUNT#] AND WorkList.Logon = 'SCH' [SQLSTATE 01000]
> (Message 0) Update WorkList SET [Tickler Last Action DT] =
> Data.[Last_Action_Date], [Next Work Date] = Data.[Next_Work_Date] FROM
> OPENQUERY(SCH,'SELECT [Account_ID], [Last_Action_Date],
> [Next_Work_Date] FROM public.CFAD1') as Data Where Data.Account_ID =
> WorkList.[ACCOUNT#] AND WorkList.Logon = 'SCH' [SQLSTATE 01000]
> (Message 0) Update WorkList SET [Tickler Last Action DT] =
> Data.[Last_... The step failed.
>
> I also tried to make a stored procedure and I get an error when I
> execute it in a job as well. My job run statement exec [SCH CF
> UPDATE]. This is the error I get below.
> Update WorkList SET [Tickler Last Action DT] = Data.[Last_Action_Date],
> [Next Work Date] = Data.[Next_Work_Date] FROM OPENQUERY(SCH,'SELECT
> [Account_ID], [Last_Action_Date], [Next_Work_Date] FROM public.CFAB1')
> as Data Where Data.Account_ID = WorkList.[ACCOUNT#] AND WorkList.Logon
> = 'SCH' [SQLSTATE 01000] (Message 0) Heterogeneous queries require the
> ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This
> ensures consistent query semantics. Enable these options and then
> reissue your query. [SQLSTATE 42000] (Error 7405). The step failed.
>
Have you tried SELECT @.@.OPTIONS in the script to see if ANSI_NULLS and
ANSI_WARNINGS are set? If not try setting them.
John
|||It's in the error message you got:
Heterogeneous queries require the ANSI_NULLS and
ANSI_WARNINGS options to be set for the connection. This
ensures consistent query semantics. Enable these options and
then reissue your query.
Set the ansi settings in the job script, something like:
SET ANSI_WARNINGS ON
GO
SET ANSI_NULLS ON
GO
<your job stuff here>
Or try recreating your stored procedure using:
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE YourSP...etc.
-Sue
On 26 Jan 2007 11:48:53 -0800, "mike11d11"
<mike11d11@.yahoo.com> wrote:

>Does anybody know why this statement wont run on a schedule for the
>server agent job. I ran it as a TSQL statement and tried it as a
>stored procedure and both failed. this is what I'm trying to run
>below...
>----
>--
>-- Following is a simple procedure for creating a temporary table
>-- that has the table_name of each of the user tables in the linked
>server.
sql

Running TSQL job for SQL server agent is failing, giving me ANSI errors?

Does anybody know why this statement wont run on a schedule for the
server agent job. I ran it as a TSQL statement and tried it as a
stored procedure and both failed. this is what I'm trying to run
below...
----
--
-- Following is a simple procedure for creating a temporary table
-- that has the table_name of each of the user tables in the linked
server.
--
----
Declare
@.Table_Name varchar(255),
@.Sql Nvarchar(500)
----
-- Create a temporary table for storing result of sp_tables_ex
----
Create Table #tLinkedServerTables
(
Table_Cat varchar(255),
Table_schem varchar(255),
Table_Name varchar(255),
Table_Type varchar(255),
Remarks varchar(255)
)
----
-- Populate Temporary table with the results of the sp_tables_ex
command
-- NOTE: the paramater passed to sp_tables_ex MUST be the name of a
-- valid linked server (already defined)
----
Insert Into #tLinkedServerTables exec sp_tables_ex 'TLRA'
----
-- Create cursor for selecting the Table names from the linked server
----
Declare crsLinkedServerTables Cursor For
Select table_name
>From #tLinkedServerTables
Where Table_type = 'Table'
and table_name like 'CF%'
and table_name not like 'CF*%'
and table_name not like 'CFDAILY%' --test 'public.CF%'
-- Open the cursor defined above
Open crsLinkedServerTables
Fetch Next From crsLinkedServerTables Into @.Table_Name
While @.@.Fetch_Status = 0 Begin -- 0 = more records to process
-- Your Update process goes here... Need to use dynamic SQL to
create the
-- Query.
Set @.Sql = 'Update WorkList SET [Tickler Last Action DT] = Data.[Last_Action_Date], [Next Work Date] = Data.[Next_Work_Date] FROM
OPENQUERY(TLRA,''SELECT [Account_ID], [Last_Action_Date],
[Next_Work_Date] FROM public.' + @.table_name + ''') as Data Where
Data.Account_ID = WorkList.[ACCOUNT#] AND WorkList.Logon = ''TLRA'''
print @.Sql
exec (@.Sql) /* Better to use sp_executesql, but this is here as
an example and should work */
-- Move to the next record
Fetch Next From crsLinkedServerTables Into @.Table_Name
End
----
-- Clean up (drop temp tables, remove cursors)
----
select * from #tLinkedServerTables
drop table #tLinkedServerTables
Close crsLinkedServerTables
Deallocate crsLinkedServerTables
This is what it shows in the job history when I run it as a TSQL
statement and it fails.
... [Tickler Last Action DT] = Data.[Last_Action_Date], [Next Work
Date] = Data.[Next_Work_Date] FROM OPENQUERY(SCH,'SELECT [Account_ID],
[Last_Action_Date], [Next_Work_Date] FROM public.CFAB1') as Data Where
Data.Account_ID = WorkList.[ACCOUNT#] AND WorkList.Logon = 'SCH'
[SQLSTATE 01000] (Message 0) Update WorkList SET [Tickler Last Action
DT] = Data.[Last_Action_Date], [Next Work Date] = Data.[Next_Work_Date]
FROM OPENQUERY(SCH,'SELECT [Account_ID], [Last_Action_Date],
[Next_Work_Date] FROM public.CFAC1') as Data Where Data.Account_ID = WorkList.[ACCOUNT#] AND WorkList.Logon = 'SCH' [SQLSTATE 01000]
(Message 0) Update WorkList SET [Tickler Last Action DT] = Data.[Last_Action_Date], [Next Work Date] = Data.[Next_Work_Date] FROM
OPENQUERY(SCH,'SELECT [Account_ID], [Last_Action_Date],
[Next_Work_Date] FROM public.CFAD1') as Data Where Data.Account_ID = WorkList.[ACCOUNT#] AND WorkList.Logon = 'SCH' [SQLSTATE 01000]
(Message 0) Update WorkList SET [Tickler Last Action DT] = Data.[Last_... The step failed.
I also tried to make a stored procedure and I get an error when I
execute it in a job as well. My job run statement exec [SCH CF
UPDATE]. This is the error I get below.
Update WorkList SET [Tickler Last Action DT] = Data.[Last_Action_Date],
[Next Work Date] = Data.[Next_Work_Date] FROM OPENQUERY(SCH,'SELECT
[Account_ID], [Last_Action_Date], [Next_Work_Date] FROM public.CFAB1')
as Data Where Data.Account_ID = WorkList.[ACCOUNT#] AND WorkList.Logon
= 'SCH' [SQLSTATE 01000] (Message 0) Heterogeneous queries require the
ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This
ensures consistent query semantics. Enable these options and then
reissue your query. [SQLSTATE 42000] (Error 7405). The step failed.Hi Mike
"mike11d11" wrote:
> Does anybody know why this statement wont run on a schedule for the
> server agent job. I ran it as a TSQL statement and tried it as a
> stored procedure and both failed. this is what I'm trying to run
> below...
> ----
> --
> -- Following is a simple procedure for creating a temporary table
> -- that has the table_name of each of the user tables in the linked
> server.
> --
> ----
> Declare
> @.Table_Name varchar(255),
> @.Sql Nvarchar(500)
> ----
> -- Create a temporary table for storing result of sp_tables_ex
> ----
> Create Table #tLinkedServerTables
> (
> Table_Cat varchar(255),
> Table_schem varchar(255),
> Table_Name varchar(255),
> Table_Type varchar(255),
> Remarks varchar(255)
> )
> ----
> -- Populate Temporary table with the results of the sp_tables_ex
> command
> -- NOTE: the paramater passed to sp_tables_ex MUST be the name of a
> -- valid linked server (already defined)
> ----
> Insert Into #tLinkedServerTables exec sp_tables_ex 'TLRA'
> ----
> -- Create cursor for selecting the Table names from the linked server
> ----
> Declare crsLinkedServerTables Cursor For
> Select table_name
> >From #tLinkedServerTables
> Where Table_type = 'Table'
> and table_name like 'CF%'
> and table_name not like 'CF*%'
> and table_name not like 'CFDAILY%' --test 'public.CF%'
> -- Open the cursor defined above
> Open crsLinkedServerTables
> Fetch Next From crsLinkedServerTables Into @.Table_Name
> While @.@.Fetch_Status = 0 Begin -- 0 = more records to process
> -- Your Update process goes here... Need to use dynamic SQL to
> create the
> -- Query.
> Set @.Sql = 'Update WorkList SET [Tickler Last Action DT] => Data.[Last_Action_Date], [Next Work Date] = Data.[Next_Work_Date] FROM
> OPENQUERY(TLRA,''SELECT [Account_ID], [Last_Action_Date],
> [Next_Work_Date] FROM public.' + @.table_name + ''') as Data Where
> Data.Account_ID = WorkList.[ACCOUNT#] AND WorkList.Logon = ''TLRA'''
> print @.Sql
> exec (@.Sql) /* Better to use sp_executesql, but this is here as
> an example and should work */
> -- Move to the next record
> Fetch Next From crsLinkedServerTables Into @.Table_Name
> End
> ----
> -- Clean up (drop temp tables, remove cursors)
> ----
> select * from #tLinkedServerTables
> drop table #tLinkedServerTables
> Close crsLinkedServerTables
> Deallocate crsLinkedServerTables
>
>
>
>
>
> This is what it shows in the job history when I run it as a TSQL
> statement and it fails.
>
> ... [Tickler Last Action DT] = Data.[Last_Action_Date], [Next Work
> Date] = Data.[Next_Work_Date] FROM OPENQUERY(SCH,'SELECT [Account_ID],
> [Last_Action_Date], [Next_Work_Date] FROM public.CFAB1') as Data Where
> Data.Account_ID = WorkList.[ACCOUNT#] AND WorkList.Logon = 'SCH'
> [SQLSTATE 01000] (Message 0) Update WorkList SET [Tickler Last Action
> DT] = Data.[Last_Action_Date], [Next Work Date] = Data.[Next_Work_Date]
> FROM OPENQUERY(SCH,'SELECT [Account_ID], [Last_Action_Date],
> [Next_Work_Date] FROM public.CFAC1') as Data Where Data.Account_ID => WorkList.[ACCOUNT#] AND WorkList.Logon = 'SCH' [SQLSTATE 01000]
> (Message 0) Update WorkList SET [Tickler Last Action DT] => Data.[Last_Action_Date], [Next Work Date] = Data.[Next_Work_Date] FROM
> OPENQUERY(SCH,'SELECT [Account_ID], [Last_Action_Date],
> [Next_Work_Date] FROM public.CFAD1') as Data Where Data.Account_ID => WorkList.[ACCOUNT#] AND WorkList.Logon = 'SCH' [SQLSTATE 01000]
> (Message 0) Update WorkList SET [Tickler Last Action DT] => Data.[Last_... The step failed.
>
> I also tried to make a stored procedure and I get an error when I
> execute it in a job as well. My job run statement exec [SCH CF
> UPDATE]. This is the error I get below.
> Update WorkList SET [Tickler Last Action DT] = Data.[Last_Action_Date],
> [Next Work Date] = Data.[Next_Work_Date] FROM OPENQUERY(SCH,'SELECT
> [Account_ID], [Last_Action_Date], [Next_Work_Date] FROM public.CFAB1')
> as Data Where Data.Account_ID = WorkList.[ACCOUNT#] AND WorkList.Logon
> = 'SCH' [SQLSTATE 01000] (Message 0) Heterogeneous queries require the
> ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This
> ensures consistent query semantics. Enable these options and then
> reissue your query. [SQLSTATE 42000] (Error 7405). The step failed.
>
Have you tried SELECT @.@.OPTIONS in the script to see if ANSI_NULLS and
ANSI_WARNINGS are set? If not try setting them.
John|||It's in the error message you got:
Heterogeneous queries require the ANSI_NULLS and
ANSI_WARNINGS options to be set for the connection. This
ensures consistent query semantics. Enable these options and
then reissue your query.
Set the ansi settings in the job script, something like:
SET ANSI_WARNINGS ON
GO
SET ANSI_NULLS ON
GO
<your job stuff here>
Or try recreating your stored procedure using:
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE YourSP...etc.
-Sue
On 26 Jan 2007 11:48:53 -0800, "mike11d11"
<mike11d11@.yahoo.com> wrote:
>Does anybody know why this statement wont run on a schedule for the
>server agent job. I ran it as a TSQL statement and tried it as a
>stored procedure and both failed. this is what I'm trying to run
>below...
>----
>--
>-- Following is a simple procedure for creating a temporary table
>-- that has the table_name of each of the user tables in the linked
>server.

Running TSQL job for SQL server agent is failing, giving me ANSI errors?

Running Transact-SQL script from SQL server agent with a different account

Hi,
I whised to run a T-SQL script as a SQL server Job that uses a linked
server to connect to a remote DB.
When I run the stored procedure in Query Analyser (with a specified
user) everything works fone but on SQL Agent I have the following
error:
Access to the remote server is denied because the current security
context is not trusted
How can I specify with wich user should the step run? The "run as" list
on Job Step window is disabled. How to use the new credential?
(The user exists on local and remote server).
ThanksHi
You don't say if your SQL Agent Service is running under a domain account or
not? At a guess you are using LOCALSYSTEM? See
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
John
"pedro.f.silva@.gmail.com" wrote:
> Hi,
> I whised to run a T-SQL script as a SQL server Job that uses a linked
> server to connect to a remote DB.
> When I run the stored procedure in Query Analyser (with a specified
> user) everything works fone but on SQL Agent I have the following
> error:
> Access to the remote server is denied because the current security
> context is not trusted
> How can I specify with wich user should the step run? The "run as" list
> on Job Step window is disabled. How to use the new credential?
> (The user exists on local and remote server).
> Thanks
>|||It is using LOCALSYSTEM, I think.
But I would like to use a domain account just for this job. Is it
possible?
Why cannot i use "run as" on job step?
I thought that could be done creating a credential and a proxy but SQL
server agent doesn't allow proxys on T-SQL steps.
What can I do?
John Bell escreveu:
> Hi
> You don't say if your SQL Agent Service is running under a domain account or
> not? At a guess you are using LOCALSYSTEM? See
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
> John
> "pedro.f.silva@.gmail.com" wrote:
> > Hi,
> >
> > I whised to run a T-SQL script as a SQL server Job that uses a linked
> > server to connect to a remote DB.
> > When I run the stored procedure in Query Analyser (with a specified
> > user) everything works fone but on SQL Agent I have the following
> > error:
> >
> > Access to the remote server is denied because the current security
> > context is not trusted
> >
> > How can I specify with wich user should the step run? The "run as" list
> > on Job Step window is disabled. How to use the new credential?
> >
> > (The user exists on local and remote server).
> >
> > Thanks
> >
> >|||Hi
With a T-SQL step there is a run as user on the avanced options if you are
running xp_cmdshell or a cmdexec then look at xp_sqlagent_proxy_account.
John
"pedro.f.silva@.gmail.com" wrote:
> It is using LOCALSYSTEM, I think.
> But I would like to use a domain account just for this job. Is it
> possible?
> Why cannot i use "run as" on job step?
> I thought that could be done creating a credential and a proxy but SQL
> server agent doesn't allow proxys on T-SQL steps.
> What can I do?
> John Bell escreveu:
> > Hi
> >
> > You don't say if your SQL Agent Service is running under a domain account or
> > not? At a guess you are using LOCALSYSTEM? See
> >
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
> >
> > John
> >
> > "pedro.f.silva@.gmail.com" wrote:
> >
> > > Hi,
> > >
> > > I whised to run a T-SQL script as a SQL server Job that uses a linked
> > > server to connect to a remote DB.
> > > When I run the stored procedure in Query Analyser (with a specified
> > > user) everything works fone but on SQL Agent I have the following
> > > error:
> > >
> > > Access to the remote server is denied because the current security
> > > context is not trusted
> > >
> > > How can I specify with wich user should the step run? The "run as" list
> > > on Job Step window is disabled. How to use the new credential?
> > >
> > > (The user exists on local and remote server).
> > >
> > > Thanks
> > >
> > >
>|||I had noticed that there was a "run as" user on advanced options but it
didn't work.
I was expecting it (no working) since there's no place to specify the
user password. How could it run with that identify if I hadn't
specified the password? But there's no place to do it!
John Bell escreveu:
> Hi
> With a T-SQL step there is a run as user on the avanced options if you are
> running xp_cmdshell or a cmdexec then look at xp_sqlagent_proxy_account.
> John
> "pedro.f.silva@.gmail.com" wrote:
> > It is using LOCALSYSTEM, I think.
> >
> > But I would like to use a domain account just for this job. Is it
> > possible?
> >
> > Why cannot i use "run as" on job step?
> >
> > I thought that could be done creating a credential and a proxy but SQL
> > server agent doesn't allow proxys on T-SQL steps.
> >
> > What can I do?
> >
> > John Bell escreveu:
> > > Hi
> > >
> > > You don't say if your SQL Agent Service is running under a domain account or
> > > not? At a guess you are using LOCALSYSTEM? See
> > >
> > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
> > >
> > > John
> > >
> > > "pedro.f.silva@.gmail.com" wrote:
> > >
> > > > Hi,
> > > >
> > > > I whised to run a T-SQL script as a SQL server Job that uses a linked
> > > > server to connect to a remote DB.
> > > > When I run the stored procedure in Query Analyser (with a specified
> > > > user) everything works fone but on SQL Agent I have the following
> > > > error:
> > > >
> > > > Access to the remote server is denied because the current security
> > > > context is not trusted
> > > >
> > > > How can I specify with wich user should the step run? The "run as" list
> > > > on Job Step window is disabled. How to use the new credential?
> > > >
> > > > (The user exists on local and remote server).
> > > >
> > > > Thanks
> > > >
> > > >
> >
> >|||Hi
I would suggest that you create an account to use for a service account and
change the service account to be that.
John
"pedro.f.silva@.gmail.com" wrote:
> I had noticed that there was a "run as" user on advanced options but it
> didn't work.
> I was expecting it (no working) since there's no place to specify the
> user password. How could it run with that identify if I hadn't
> specified the password? But there's no place to do it!
>
> John Bell escreveu:
> > Hi
> >
> > With a T-SQL step there is a run as user on the avanced options if you are
> > running xp_cmdshell or a cmdexec then look at xp_sqlagent_proxy_account.
> >
> > John
> >
> > "pedro.f.silva@.gmail.com" wrote:
> >
> > > It is using LOCALSYSTEM, I think.
> > >
> > > But I would like to use a domain account just for this job. Is it
> > > possible?
> > >
> > > Why cannot i use "run as" on job step?
> > >
> > > I thought that could be done creating a credential and a proxy but SQL
> > > server agent doesn't allow proxys on T-SQL steps.
> > >
> > > What can I do?
> > >
> > > John Bell escreveu:
> > > > Hi
> > > >
> > > > You don't say if your SQL Agent Service is running under a domain account or
> > > > not? At a guess you are using LOCALSYSTEM? See
> > > >
> > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
> > > >
> > > > John
> > > >
> > > > "pedro.f.silva@.gmail.com" wrote:
> > > >
> > > > > Hi,
> > > > >
> > > > > I whised to run a T-SQL script as a SQL server Job that uses a linked
> > > > > server to connect to a remote DB.
> > > > > When I run the stored procedure in Query Analyser (with a specified
> > > > > user) everything works fone but on SQL Agent I have the following
> > > > > error:
> > > > >
> > > > > Access to the remote server is denied because the current security
> > > > > context is not trusted
> > > > >
> > > > > How can I specify with wich user should the step run? The "run as" list
> > > > > on Job Step window is disabled. How to use the new credential?
> > > > >
> > > > > (The user exists on local and remote server).
> > > > >
> > > > > Thanks
> > > > >
> > > > >
> > >
> > >
>|||Thanks John.
That works if I want all my Jobs to run with the same user.
Since I only have one it's ok :) - SQL server agent runs with the
account I need to invoke the Stored Procedure.
It would be nice however to be able to run different T-SQL Jobs with
different users.
John Bell escreveu:
> Hi
> I would suggest that you create an account to use for a service account and
> change the service account to be that.
> John
> "pedro.f.silva@.gmail.com" wrote:
> > I had noticed that there was a "run as" user on advanced options but it
> > didn't work.
> >
> > I was expecting it (no working) since there's no place to specify the
> > user password. How could it run with that identify if I hadn't
> > specified the password? But there's no place to do it!
> >
> >
> > John Bell escreveu:
> > > Hi
> > >
> > > With a T-SQL step there is a run as user on the avanced options if you are
> > > running xp_cmdshell or a cmdexec then look at xp_sqlagent_proxy_account.
> > >
> > > John
> > >
> > > "pedro.f.silva@.gmail.com" wrote:
> > >
> > > > It is using LOCALSYSTEM, I think.
> > > >
> > > > But I would like to use a domain account just for this job. Is it
> > > > possible?
> > > >
> > > > Why cannot i use "run as" on job step?
> > > >
> > > > I thought that could be done creating a credential and a proxy but SQL
> > > > server agent doesn't allow proxys on T-SQL steps.
> > > >
> > > > What can I do?
> > > >
> > > > John Bell escreveu:
> > > > > Hi
> > > > >
> > > > > You don't say if your SQL Agent Service is running under a domain account or
> > > > > not? At a guess you are using LOCALSYSTEM? See
> > > > >
> > > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
> > > > >
> > > > > John
> > > > >
> > > > > "pedro.f.silva@.gmail.com" wrote:
> > > > >
> > > > > > Hi,
> > > > > >
> > > > > > I whised to run a T-SQL script as a SQL server Job that uses a linked
> > > > > > server to connect to a remote DB.
> > > > > > When I run the stored procedure in Query Analyser (with a specified
> > > > > > user) everything works fone but on SQL Agent I have the following
> > > > > > error:
> > > > > >
> > > > > > Access to the remote server is denied because the current security
> > > > > > context is not trusted
> > > > > >
> > > > > > How can I specify with wich user should the step run? The "run as" list
> > > > > > on Job Step window is disabled. How to use the new credential?
> > > > > >
> > > > > > (The user exists on local and remote server).
> > > > > >
> > > > > > Thanks
> > > > > >
> > > > > >
> > > >
> > > >
> >
> >|||Hi
I don't know what your job is doing or why it needs to communicate with the
remote server, but it seems to be your only option with your current
implementation. If you used SQL 2005 there are more alternatives.
John
"pedro.f.silva@.gmail.com" wrote:
> Thanks John.
> That works if I want all my Jobs to run with the same user.
> Since I only have one it's ok :) - SQL server agent runs with the
> account I need to invoke the Stored Procedure.
> It would be nice however to be able to run different T-SQL Jobs with
> different users.
>
> John Bell escreveu:
> > Hi
> >
> > I would suggest that you create an account to use for a service account and
> > change the service account to be that.
> >
> > John
> >
> > "pedro.f.silva@.gmail.com" wrote:
> >
> > > I had noticed that there was a "run as" user on advanced options but it
> > > didn't work.
> > >
> > > I was expecting it (no working) since there's no place to specify the
> > > user password. How could it run with that identify if I hadn't
> > > specified the password? But there's no place to do it!
> > >
> > >
> > > John Bell escreveu:
> > > > Hi
> > > >
> > > > With a T-SQL step there is a run as user on the avanced options if you are
> > > > running xp_cmdshell or a cmdexec then look at xp_sqlagent_proxy_account.
> > > >
> > > > John
> > > >
> > > > "pedro.f.silva@.gmail.com" wrote:
> > > >
> > > > > It is using LOCALSYSTEM, I think.
> > > > >
> > > > > But I would like to use a domain account just for this job. Is it
> > > > > possible?
> > > > >
> > > > > Why cannot i use "run as" on job step?
> > > > >
> > > > > I thought that could be done creating a credential and a proxy but SQL
> > > > > server agent doesn't allow proxys on T-SQL steps.
> > > > >
> > > > > What can I do?
> > > > >
> > > > > John Bell escreveu:
> > > > > > Hi
> > > > > >
> > > > > > You don't say if your SQL Agent Service is running under a domain account or
> > > > > > not? At a guess you are using LOCALSYSTEM? See
> > > > > >
> > > > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
> > > > > >
> > > > > > John
> > > > > >
> > > > > > "pedro.f.silva@.gmail.com" wrote:
> > > > > >
> > > > > > > Hi,
> > > > > > >
> > > > > > > I whised to run a T-SQL script as a SQL server Job that uses a linked
> > > > > > > server to connect to a remote DB.
> > > > > > > When I run the stored procedure in Query Analyser (with a specified
> > > > > > > user) everything works fone but on SQL Agent I have the following
> > > > > > > error:
> > > > > > >
> > > > > > > Access to the remote server is denied because the current security
> > > > > > > context is not trusted
> > > > > > >
> > > > > > > How can I specify with wich user should the step run? The "run as" list
> > > > > > > on Job Step window is disabled. How to use the new credential?
> > > > > > >
> > > > > > > (The user exists on local and remote server).
> > > > > > >
> > > > > > > Thanks
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > >
> > >
>|||I am using SQL Server 2005 :)
Which alternatives do I have?
John Bell escreveu:
> Hi
> I don't know what your job is doing or why it needs to communicate with the
> remote server, but it seems to be your only option with your current
> implementation. If you used SQL 2005 there are more alternatives.
> John
> "pedro.f.silva@.gmail.com" wrote:
> > Thanks John.
> >
> > That works if I want all my Jobs to run with the same user.
> >
> > Since I only have one it's ok :) - SQL server agent runs with the
> > account I need to invoke the Stored Procedure.
> >
> > It would be nice however to be able to run different T-SQL Jobs with
> > different users.
> >
> >
> > John Bell escreveu:
> > > Hi
> > >
> > > I would suggest that you create an account to use for a service account and
> > > change the service account to be that.
> > >
> > > John
> > >
> > > "pedro.f.silva@.gmail.com" wrote:
> > >
> > > > I had noticed that there was a "run as" user on advanced options but it
> > > > didn't work.
> > > >
> > > > I was expecting it (no working) since there's no place to specify the
> > > > user password. How could it run with that identify if I hadn't
> > > > specified the password? But there's no place to do it!
> > > >
> > > >
> > > > John Bell escreveu:
> > > > > Hi
> > > > >
> > > > > With a T-SQL step there is a run as user on the avanced options if you are
> > > > > running xp_cmdshell or a cmdexec then look at xp_sqlagent_proxy_account.
> > > > >
> > > > > John
> > > > >
> > > > > "pedro.f.silva@.gmail.com" wrote:
> > > > >
> > > > > > It is using LOCALSYSTEM, I think.
> > > > > >
> > > > > > But I would like to use a domain account just for this job. Is it
> > > > > > possible?
> > > > > >
> > > > > > Why cannot i use "run as" on job step?
> > > > > >
> > > > > > I thought that could be done creating a credential and a proxy but SQL
> > > > > > server agent doesn't allow proxys on T-SQL steps.
> > > > > >
> > > > > > What can I do?
> > > > > >
> > > > > > John Bell escreveu:
> > > > > > > Hi
> > > > > > >
> > > > > > > You don't say if your SQL Agent Service is running under a domain account or
> > > > > > > not? At a guess you are using LOCALSYSTEM? See
> > > > > > >
> > > > > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
> > > > > > >
> > > > > > > John
> > > > > > >
> > > > > > > "pedro.f.silva@.gmail.com" wrote:
> > > > > > >
> > > > > > > > Hi,
> > > > > > > >
> > > > > > > > I whised to run a T-SQL script as a SQL server Job that uses a linked
> > > > > > > > server to connect to a remote DB.
> > > > > > > > When I run the stored procedure in Query Analyser (with a specified
> > > > > > > > user) everything works fone but on SQL Agent I have the following
> > > > > > > > error:
> > > > > > > >
> > > > > > > > Access to the remote server is denied because the current security
> > > > > > > > context is not trusted
> > > > > > > >
> > > > > > > > How can I specify with wich user should the step run? The "run as" list
> > > > > > > > on Job Step window is disabled. How to use the new credential?
> > > > > > > >
> > > > > > > > (The user exists on local and remote server).
> > > > > > > >
> > > > > > > > Thanks
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> >
> >|||Hi
Look at the using extensions to the EXECUTE command. But first read
http://www.sommarskog.se/grantperm.html
John
"pedro.f.silva@.gmail.com" wrote:
> I am using SQL Server 2005 :)
> Which alternatives do I have?
> John Bell escreveu:
> > Hi
> >
> > I don't know what your job is doing or why it needs to communicate with the
> > remote server, but it seems to be your only option with your current
> > implementation. If you used SQL 2005 there are more alternatives.
> >
> > John
> >
> > "pedro.f.silva@.gmail.com" wrote:
> >
> > > Thanks John.
> > >
> > > That works if I want all my Jobs to run with the same user.
> > >
> > > Since I only have one it's ok :) - SQL server agent runs with the
> > > account I need to invoke the Stored Procedure.
> > >
> > > It would be nice however to be able to run different T-SQL Jobs with
> > > different users.
> > >
> > >
> > > John Bell escreveu:
> > > > Hi
> > > >
> > > > I would suggest that you create an account to use for a service account and
> > > > change the service account to be that.
> > > >
> > > > John
> > > >
> > > > "pedro.f.silva@.gmail.com" wrote:
> > > >
> > > > > I had noticed that there was a "run as" user on advanced options but it
> > > > > didn't work.
> > > > >
> > > > > I was expecting it (no working) since there's no place to specify the
> > > > > user password. How could it run with that identify if I hadn't
> > > > > specified the password? But there's no place to do it!
> > > > >
> > > > >
> > > > > John Bell escreveu:
> > > > > > Hi
> > > > > >
> > > > > > With a T-SQL step there is a run as user on the avanced options if you are
> > > > > > running xp_cmdshell or a cmdexec then look at xp_sqlagent_proxy_account.
> > > > > >
> > > > > > John
> > > > > >
> > > > > > "pedro.f.silva@.gmail.com" wrote:
> > > > > >
> > > > > > > It is using LOCALSYSTEM, I think.
> > > > > > >
> > > > > > > But I would like to use a domain account just for this job. Is it
> > > > > > > possible?
> > > > > > >
> > > > > > > Why cannot i use "run as" on job step?
> > > > > > >
> > > > > > > I thought that could be done creating a credential and a proxy but SQL
> > > > > > > server agent doesn't allow proxys on T-SQL steps.
> > > > > > >
> > > > > > > What can I do?
> > > > > > >
> > > > > > > John Bell escreveu:
> > > > > > > > Hi
> > > > > > > >
> > > > > > > > You don't say if your SQL Agent Service is running under a domain account or
> > > > > > > > not? At a guess you are using LOCALSYSTEM? See
> > > > > > > >
> > > > > > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
> > > > > > > >
> > > > > > > > John
> > > > > > > >
> > > > > > > > "pedro.f.silva@.gmail.com" wrote:
> > > > > > > >
> > > > > > > > > Hi,
> > > > > > > > >
> > > > > > > > > I whised to run a T-SQL script as a SQL server Job that uses a linked
> > > > > > > > > server to connect to a remote DB.
> > > > > > > > > When I run the stored procedure in Query Analyser (with a specified
> > > > > > > > > user) everything works fone but on SQL Agent I have the following
> > > > > > > > > error:
> > > > > > > > >
> > > > > > > > > Access to the remote server is denied because the current security
> > > > > > > > > context is not trusted
> > > > > > > > >
> > > > > > > > > How can I specify with wich user should the step run? The "run as" list
> > > > > > > > > on Job Step window is disabled. How to use the new credential?
> > > > > > > > >
> > > > > > > > > (The user exists on local and remote server).
> > > > > > > > >
> > > > > > > > > Thanks
> > > > > > > > >
> > > > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > >
> > >
>sql

Friday, March 23, 2012

Running Time of Package to be logged in the Email Notification

Hi,

I want to include the running time (Start Time and End Time) of the Package in my script task that sends out an email after job completion.

How do I get the start time and end time?

thanks a lot

cherriesh

There is a system variable called StartTime that will give you the start time. For the Endtime; you could use an execute sql task to get the time from the DB (e.g. Select getdate()) and put that value in a SSIS variable.|||You can also use the ContainerStartTime system variable, which should reflect the start time of the current task. The difference between StartTime (which is at the package level) and ContainerStartTime of your SendMail Task will give you the runtime up to that task.|||

is this how i retrieve the system variable?

Dts.Variables("System: Tongue Tied tartTime").Value.ToString

Because I'm having an error:

The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there.

cheriesh

|||

Make sure you have either added it to the ReadOnlyVariables property in the Script property pages, or lock it in the script using the VariableDispenser (the better option).

Here's a post that explains why using the VariableDispenser is better:

http://blogs.conchango.com/jamiethomson/archive/2007/08/28/Beware-of-variable-usage-in-script-tasks.aspx

Code Snippet

Dim var As Variables

Dts.VariableDispenser.LockOneForRead("StartTime", var)

MsgBox(var(0).Value)

var.Unlock()

Running Time of Package to be logged in the Email Notification

Hi,

I want to include the running time (Start Time and End Time) of the Package in my script task that sends out an email after job completion.

How do I get the start time and end time?

thanks a lot

cherriesh

is this how i retrieve the system variable?

Dts.Variables("System: Tongue Tied tartTime").Value.ToString

Because I'm having an error:

The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there.

cheriesh

|||

Make sure you have either added it to the ReadOnlyVariables property in the Script property pages, or lock it in the script using the VariableDispenser (the better option).

Here's a post that explains why using the VariableDispenser is better:

http://blogs.conchango.com/jamiethomson/archive/2007/08/28/Beware-of-variable-usage-in-script-tasks.aspx

Code Snippet

Dim var As Variables

Dts.VariableDispenser.LockOneForRead("StartTime", var)

MsgBox(var(0).Value)

var.Unlock()

running the upgrade step from version 610 to version 611 fails

I am having an intermittent problem when restoring a SQL Server 2000 database to SQL Server 2005.

I have a job that runs daily that restores the database. This job has ran 20 times with no problem and has failed twice. When it fails it fails in the exact same place with the same error message:

>>Restoring Backup File: D:\Backups\Production\IHI.bak [SQLSTATE 01000]
Processed 388880 pages for database 'IHI', file 'IHIDat.mdf' on file 1. [SQLSTATE 01000]
Processed 1 pages for database 'IHI', file 'IHILog.ldf' on file 1. [SQLSTATE 01000]
RESTORE DATABASE successfully processed 388881 pages in 130.732 seconds (24.368 MB/sec). [SQLSTATE 01000]
>>Bringing database online... [SQLSTATE 01000]
Converting database 'IHI' from version 539 to the current version 611. [SQLSTATE 01000]
Database 'IHI' running the upgrade step from version 539 to version 551. [SQLSTATE 01000]
Database 'IHI' running the upgrade step from version 551 to version 552. [SQLSTATE 01000]
Database 'IHI' running the upgrade step from version 552 to version 553. [SQLSTATE 01000]
Database 'IHI' running the upgrade step from version 553 to version 554. [SQLSTATE 01000]
Database 'IHI' running the upgrade step from version 554 to version 589. [SQLSTATE 01000]
Database 'IHI' running the upgrade step from version 589 to version 590. [SQLSTATE 01000]
Database 'IHI' running the upgrade step from version 590 to version 593. [SQLSTATE 01000]
Database 'IHI' running the upgrade step from version 593 to version 597. [SQLSTATE 01000]
Database 'IHI' running the upgrade step from version 597 to version 604. [SQLSTATE 01000]
Database 'IHI' running the upgrade step from version 604 to version 605. [SQLSTATE 01000]
Database 'IHI' running the upgrade step from version 605 to version 606. [SQLSTATE 01000]
Database 'IHI' running the upgrade step from version 606 to version 607. [SQLSTATE 01000]
Database 'IHI' running the upgrade step from version 607 to version 608. [SQLSTATE 01000]
Database 'IHI' running the upgrade step from version 608 to version 609. [SQLSTATE 01000]
Database 'IHI' running the upgrade step from version 609 to version 610. [SQLSTATE 01000]
Msg 3023, Sev 16, State 3, Line 1 : Backup and file manipulation operations (such as ALTER DATABASE ADD FILE) on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed. [SQLSTATE 42000]
Msg 5069, Sev 16, State 1, Line 1 : ALTER DATABASE statement failed. [SQLSTATE 42000]
Msg 951, Sev 16, State 1, Line 287 : Database 'IHI' running the upgrade step from version 610 to version 611. [SQLSTATE 01000]
Msg 3014, Sev 16, State 1, Line 287 : RESTORE DATABASE successfully processed 0 pages in 30.370 seconds (0.000 MB/sec). [SQLSTATE 01000]

For some reason the last step of upgrading to 611 seems to be having a problem. If I rerun the job on the exact same IHI.bak 10 minutes later it will/has succeeded. So I don't particulary think there is a problem with the .bak it self.

Anyone have any ideas?

-Mark

I think there is some reordering of error messages here by the client that is confusing things some, but the 5069 error you are getting should only come from an ALTER DATABASE command, not the RESTORE, and that seems to be failing. Is your job doing some kind of option or state change on the DB after the RESTORE? Can you post a fragment of the errorlog around the time of such a failure?

|||

At 6AM the Job started which refreshes numerous databases. Three of these DBs are from 2000 server: DYNAMICS, IHI, and SBM01. All the others are from a 2005 server.

The Job was re-ran at 7:14 on the same day and completed successfully. The 6am job failed.

SQL Error Log:

01/28/2007 07:40:52,spid57,Unknown,Setting database option RECOVERY to SIMPLE for database SBM01.
01/28/2007 07:40:25,spid57,Unknown,Recovery is writing a checkpoint in database 'SBM01' (7). This is an informational message only. No user action is required.
01/28/2007 07:40:25,spid57,Unknown,Starting up database 'SBM01'.
01/28/2007 07:40:25,Backup,Unknown,Database was restored: Database: SBM01<c/> creation date(time): 2005/01/04(11:36:34)<c/> first LSN: 3943:4917:1<c/> last LSN: 3943:4922:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'D:\Backups\Production\SBM01.bak'}). Informational message. No user action required.
01/28/2007 07:40:25,spid57,Unknown,The database 'SBM01' is marked RESTORING and is in a state that does not allow recovery to be run.
01/28/2007 07:40:25,spid57,Unknown,Starting up database 'SBM01'.
01/28/2007 07:40:03,spid57,Unknown,The database 'SBM01' is marked OFFLINE and is in a state that does not allow recovery to be run.
01/28/2007 07:40:02,spid57,Unknown,Starting up database 'SBM01'.
01/28/2007 07:40:01,spid57,Unknown,Setting database option OFFLINE to ON for database SBM01.
01/28/2007 07:36:03,spid57,Unknown,Setting database option RECOVERY to SIMPLE for database IHI.
01/28/2007 07:35:33,spid57,Unknown,Recovery is writing a checkpoint in database 'IHI' (5). This is an informational message only. No user action is required.
01/28/2007 07:35:31,spid57,Unknown,Starting up database 'IHI'.
01/28/2007 07:35:31,Backup,Unknown,Database was restored: Database: IHI<c/> creation date(time): 2003/07/22(11:29:55)<c/> first LSN: 17290:32:1<c/> last LSN: 17290:45:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'D:\Backups\Production\IHI.bak'}). Informational message. No user action required.
01/28/2007 07:35:31,spid57,Unknown,The database 'IHI' is marked RESTORING and is in a state that does not allow recovery to be run.
01/28/2007 07:35:31,spid57,Unknown,Starting up database 'IHI'.
01/28/2007 07:33:21,spid57,Unknown,The database 'IHI' is marked OFFLINE and is in a state that does not allow recovery to be run.
01/28/2007 07:33:20,spid57,Unknown,Starting up database 'IHI'.
01/28/2007 07:33:20,spid57,Unknown,Setting database option OFFLINE to ON for database IHI.
01/28/2007 07:33:17,spid57,Unknown,Setting database option RECOVERY to SIMPLE for database DYNAMICS.
01/28/2007 07:33:14,spid57,Unknown,Recovery is writing a checkpoint in database 'DYNAMICS' (6). This is an informational message only. No user action is required.
01/28/2007 07:33:14,spid57,Unknown,Starting up database 'DYNAMICS'.
01/28/2007 07:33:14,Backup,Unknown,Database was restored: Database: DYNAMICS<c/> creation date(time): 2003/07/22(11:20:05)<c/> first LSN: 579:34:1<c/> last LSN: 579:36:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'D:\Backups\Production\DYNAMICS.bak'}). Informational message. No user action required.
01/28/2007 07:33:14,spid57,Unknown,The database 'DYNAMICS' is marked RESTORING and is in a state that does not allow recovery to be run.
01/28/2007 07:33:14,spid57,Unknown,Starting up database 'DYNAMICS'.
01/28/2007 07:33:09,spid57,Unknown,The database 'DYNAMICS' is marked OFFLINE and is in a state that does not allow recovery to be run.
01/28/2007 07:33:09,spid57,Unknown,Starting up database 'DYNAMICS'.
01/28/2007 07:33:09,spid57,Unknown,Setting database option OFFLINE to ON for database DYNAMICS.
01/28/2007 07:33:08,spid57,Unknown,Setting database option RECOVERY to SIMPLE for database DW_EventStats.
01/28/2007 07:33:08,spid57,Unknown,Starting up database 'DW_EventStats'.
01/28/2007 07:33:08,Backup,Unknown,Database was restored: Database: DW_EventStats<c/> creation date(time): 2006/02/25(10:37:46)<c/> first LSN: 569:307:37<c/> last LSN: 569:323:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'D:\Backups\Production\PROD_EventStats.bak'}). Informational message. No user action required.
01/28/2007 07:33:08,spid57,Unknown,The database 'DW_EventStats' is marked RESTORING and is in a state that does not allow recovery to be run.
01/28/2007 07:33:08,spid57,Unknown,Starting up database 'DW_EventStats'.
01/28/2007 07:33:08,spid57,Unknown,The database 'DW_EventStats' is marked OFFLINE and is in a state that does not allow recovery to be run.
01/28/2007 07:33:08,spid57,Unknown,Starting up database 'DW_EventStats'.
01/28/2007 07:33:07,spid57,Unknown,Setting database option OFFLINE to ON for database DW_EventStats.
01/28/2007 07:30:01,Backup,Unknown,Log was backed up. Database: IHI<c/> creation date(time): 2007/01/17(09:44:21)<c/> first LSN: 17445:99857:1<c/> last LSN: 17445:99857:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'S:\backup\IHI\IHI_20070128073001257_S.trn'}). This is an informational message only. No user action is required.
01/28/2007 07:25:16,spid57,Unknown,Setting database option RECOVERY to SIMPLE for database DW_IHIDB.
01/28/2007 07:25:15,spid57,Unknown,Starting up database 'DW_IHIDB'.
01/28/2007 07:25:15,Backup,Unknown,Database was restored: Database: DW_IHIDB<c/> creation date(time): 2006/02/25(10:41:52)<c/> first LSN: 78692:11007:261<c/> last LSN: 78692:11114:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'D:\Backups\Production\PROD_IHIDB.bak'}). Informational message. No user action required.
01/28/2007 07:25:13,spid57,Unknown,The database 'DW_IHIDB' is marked RESTORING and is in a state that does not allow recovery to be run.
01/28/2007 07:25:13,spid57,Unknown,Starting up database 'DW_IHIDB'.
01/28/2007 07:22:05,spid57,Unknown,The database 'DW_IHIDB' is marked OFFLINE and is in a state that does not allow recovery to be run.
01/28/2007 07:22:05,spid57,Unknown,Starting up database 'DW_IHIDB'.
01/28/2007 07:22:05,spid57,Unknown,Setting database option OFFLINE to ON for database DW_IHIDB.
01/28/2007 07:22:05,spid57,Unknown,Setting database option RECOVERY to SIMPLE for database DW_NEventStats.
01/28/2007 07:22:05,spid57,Unknown,Starting up database 'DW_NEventStats'.
01/28/2007 07:22:05,Backup,Unknown,Database was restored: Database: DW_NEventStats<c/> creation date(time): 2006/02/25(10:53:08)<c/> first LSN: 252:570:81<c/> last LSN: 252:603:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'D:\Backups\Production\PROD_NEventStats.bak'}). Informational message. No user action required.
01/28/2007 07:22:05,spid57,Unknown,The database 'DW_NEventStats' is marked RESTORING and is in a state that does not allow recovery to be run.
01/28/2007 07:22:05,spid57,Unknown,Starting up database 'DW_NEventStats'.
01/28/2007 07:22:04,spid57,Unknown,The database 'DW_NEventStats' is marked OFFLINE and is in a state that does not allow recovery to be run.
01/28/2007 07:22:04,spid57,Unknown,Starting up database 'DW_NEventStats'.
01/28/2007 07:22:04,spid57,Unknown,Setting database option OFFLINE to ON for database DW_NEventStats.
01/28/2007 07:21:10,spid57,Unknown,Setting database option RECOVERY to SIMPLE for database DW_NICHQDB.
01/28/2007 07:21:07,spid57,Unknown,Starting up database 'DW_NICHQDB'.
01/28/2007 07:21:07,Backup,Unknown,Database was restored: Database: DW_NICHQDB<c/> creation date(time): 2006/02/25(10:54:06)<c/> first LSN: 5137:438:99<c/> last LSN: 5137:479:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'D:\Backups\Production\PROD_NICHQDB.bak'}). Informational message. No user action required.
01/28/2007 07:21:06,spid57,Unknown,The database 'DW_NICHQDB' is marked RESTORING and is in a state that does not allow recovery to be run.
01/28/2007 07:21:06,spid57,Unknown,Starting up database 'DW_NICHQDB'.
01/28/2007 07:20:37,spid57,Unknown,The database 'DW_NICHQDB' is marked OFFLINE and is in a state that does not allow recovery to be run.
01/28/2007 07:20:37,spid57,Unknown,Starting up database 'DW_NICHQDB'.
01/28/2007 07:20:37,spid57,Unknown,Setting database option OFFLINE to ON for database DW_NICHQDB.
01/28/2007 07:15:01,Backup,Unknown,Log was backed up. Database: IHI<c/> creation date(time): 2007/01/17(09:44:21)<c/> first LSN: 17445:99857:1<c/> last LSN: 17445:99857:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'S:\backup\IHI\IHI_20070128071501527_S.trn'}). This is an informational message only. No user action is required.
01/28/2007 07:14:44,spid56,Unknown,Recovery is writing a checkpoint in database 'SBM01' (7). This is an informational message only. No user action is required.
01/28/2007 07:14:44,spid56,Unknown,0 transactions rolled back in database 'SBM01' (7). This is an informational message only. No user action is required.
01/28/2007 07:14:44,spid56,Unknown,1 transactions rolled forward in database 'SBM01' (7). This is an informational message only. No user action is required.
01/28/2007 07:14:44,spid56,Unknown,Starting up database 'SBM01'.
01/28/2007 07:14:44,spid56,Unknown,Setting database option ONLINE to ON for database SBM01.
01/28/2007 07:00:00,Backup,Unknown,Log was backed up. Database: IHI<c/> creation date(time): 2007/01/17(09:44:21)<c/> first LSN: 17445:99857:1<c/> last LSN: 17445:99857:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'S:\backup\IHI\IHI_20070128070000313_S.trn'}). This is an informational message only. No user action is required.
01/28/2007 06:45:00,Backup,Unknown,Log was backed up. Database: IHI<c/> creation date(time): 2007/01/17(09:44:21)<c/> first LSN: 17445:99824:1<c/> last LSN: 17445:99857:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'S:\backup\IHI\IHI_20070128064500223_S.trn'}). This is an informational message only. No user action is required.
01/28/2007 06:31:04,Backup,Unknown,Log was backed up. Database: IHI<c/> creation date(time): 2007/01/17(09:44:21)<c/> first LSN: 17328:5750:1<c/> last LSN: 17445:99824:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'S:\backup\IHI\IHI_20070128063000540_S.trn'}). This is an informational message only. No user action is required.
01/28/2007 06:19:37,spid55,Unknown,Setting database option OFFLINE to ON for database SBM01.
01/28/2007 06:15:25,Backup,Unknown,Log was backed up. Database: IHI<c/> creation date(time): 2007/01/17(09:44:21)<c/> first LSN: 17290:45:1<c/> last LSN: 17328:5750:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'S:\backup\IHI\IHI_20070128061502183_S.trn'}). This is an informational message only. No user action is required.
01/28/2007 06:15:22,spid55,Unknown,Setting database option RECOVERY to SIMPLE for database IHI.
01/28/2007 06:14:51,spid55,Unknown,Recovery is writing a checkpoint in database 'IHI' (5). This is an informational message only. No user action is required.
01/28/2007 06:14:51,spid55,Unknown,Starting up database 'IHI'.
01/28/2007 06:14:51,Backup,Unknown,Database was restored: Database: IHI<c/> creation date(time): 2003/07/22(11:29:55)<c/> first LSN: 17290:32:1<c/> last LSN: 17290:45:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'D:\Backups\Production\IHI.bak'}). Informational message. No user action required.
01/28/2007 06:14:50,spid55,Unknown,The database 'IHI' is marked RESTORING and is in a state that does not allow recovery to be run.
01/28/2007 06:14:50,spid55,Unknown,Starting up database 'IHI'.
01/28/2007 06:12:39,spid55,Unknown,The database 'IHI' is marked OFFLINE and is in a state that does not allow recovery to be run.
01/28/2007 06:12:39,spid55,Unknown,Starting up database 'IHI'.
01/28/2007 06:12:39,spid55,Unknown,Setting database option OFFLINE to ON for database IHI.
01/28/2007 06:12:37,spid55,Unknown,Setting database option RECOVERY to SIMPLE for database DYNAMICS.
01/28/2007 06:12:34,spid55,Unknown,Recovery is writing a checkpoint in database 'DYNAMICS' (6). This is an informational message only. No user action is required.
01/28/2007 06:12:34,spid55,Unknown,Starting up database 'DYNAMICS'.
01/28/2007 06:12:34,Backup,Unknown,Database was restored: Database: DYNAMICS<c/> creation date(time): 2003/07/22(11:20:05)<c/> first LSN: 579:34:1<c/> last LSN: 579:36:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'D:\Backups\Production\DYNAMICS.bak'}). Informational message. No user action required.
01/28/2007 06:12:33,spid55,Unknown,The database 'DYNAMICS' is marked RESTORING and is in a state that does not allow recovery to be run.
01/28/2007 06:12:33,spid55,Unknown,Starting up database 'DYNAMICS'.
01/28/2007 06:12:28,spid55,Unknown,The database 'DYNAMICS' is marked OFFLINE and is in a state that does not allow recovery to be run.
01/28/2007 06:12:28,spid55,Unknown,Starting up database 'DYNAMICS'.
01/28/2007 06:12:27,spid55,Unknown,Setting database option OFFLINE to ON for database DYNAMICS.
01/28/2007 06:12:27,spid55,Unknown,Setting database option RECOVERY to SIMPLE for database DW_EventStats.
01/28/2007 06:12:27,spid55,Unknown,Starting up database 'DW_EventStats'.
01/28/2007 06:12:27,Backup,Unknown,Database was restored: Database: DW_EventStats<c/> creation date(time): 2006/02/25(10:37:46)<c/> first LSN: 569:307:37<c/> last LSN: 569:323:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'D:\Backups\Production\PROD_EventStats.bak'}). Informational message. No user action required.
01/28/2007 06:12:27,spid55,Unknown,The database 'DW_EventStats' is marked RESTORING and is in a state that does not allow recovery to be run.
01/28/2007 06:12:27,spid55,Unknown,Starting up database 'DW_EventStats'.
01/28/2007 06:12:26,spid55,Unknown,The database 'DW_EventStats' is marked OFFLINE and is in a state that does not allow recovery to be run.
01/28/2007 06:12:26,spid55,Unknown,Starting up database 'DW_EventStats'.
01/28/2007 06:12:26,spid55,Unknown,Setting database option OFFLINE to ON for database DW_EventStats.
01/28/2007 06:04:43,spid55,Unknown,Setting database option RECOVERY to SIMPLE for database DW_IHIDB.
01/28/2007 06:04:42,spid55,Unknown,Starting up database 'DW_IHIDB'.
01/28/2007 06:04:42,Backup,Unknown,Database was restored: Database: DW_IHIDB<c/> creation date(time): 2006/02/25(10:41:52)<c/> first LSN: 78692:11007:261<c/> last LSN: 78692:11114:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'D:\Backups\Production\PROD_IHIDB.bak'}). Informational message. No user action required.
01/28/2007 06:04:40,spid55,Unknown,The database 'DW_IHIDB' is marked RESTORING and is in a state that does not allow recovery to be run.
01/28/2007 06:04:40,spid55,Unknown,Starting up database 'DW_IHIDB'.
01/28/2007 06:01:31,spid55,Unknown,The database 'DW_IHIDB' is marked OFFLINE and is in a state that does not allow recovery to be run.
01/28/2007 06:01:30,spid55,Unknown,Starting up database 'DW_IHIDB'.
01/28/2007 06:01:30,spid55,Unknown,Setting database option OFFLINE to ON for database DW_IHIDB.
01/28/2007 06:01:30,spid55,Unknown,Setting database option RECOVERY to SIMPLE for database DW_NEventStats.
01/28/2007 06:01:30,spid55,Unknown,Starting up database 'DW_NEventStats'.
01/28/2007 06:01:30,Backup,Unknown,Database was restored: Database: DW_NEventStats<c/> creation date(time): 2006/02/25(10:53:08)<c/> first LSN: 252:570:81<c/> last LSN: 252:603:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'D:\Backups\Production\PROD_NEventStats.bak'}). Informational message. No user action required.
01/28/2007 06:01:30,spid55,Unknown,The database 'DW_NEventStats' is marked RESTORING and is in a state that does not allow recovery to be run.
01/28/2007 06:01:30,spid55,Unknown,Starting up database 'DW_NEventStats'.
01/28/2007 06:01:29,spid55,Unknown,The database 'DW_NEventStats' is marked OFFLINE and is in a state that does not allow recovery to be run.
01/28/2007 06:01:29,spid55,Unknown,Starting up database 'DW_NEventStats'.
01/28/2007 06:01:29,spid55,Unknown,Setting database option OFFLINE to ON for database DW_NEventStats.
01/28/2007 06:00:33,spid55,Unknown,Setting database option RECOVERY to SIMPLE for database DW_NICHQDB.
01/28/2007 06:00:31,spid55,Unknown,Starting up database 'DW_NICHQDB'.
01/28/2007 06:00:31,Backup,Unknown,Database was restored: Database: DW_NICHQDB<c/> creation date(time): 2006/02/25(10:54:06)<c/> first LSN: 5137:438:99<c/> last LSN: 5137:479:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'D:\Backups\Production\PROD_NICHQDB.bak'}). Informational message. No user action required.
01/28/2007 06:00:30,spid55,Unknown,The database 'DW_NICHQDB' is marked RESTORING and is in a state that does not allow recovery to be run.
01/28/2007 06:00:30,spid55,Unknown,Starting up database 'DW_NICHQDB'.
01/28/2007 06:00:01,spid55,Unknown,The database 'DW_NICHQDB' is marked OFFLINE and is in a state that does not allow recovery to be run.
01/28/2007 06:00:01,spid55,Unknown,Starting up database 'DW_NICHQDB'.
01/28/2007 06:00:01,spid55,Unknown,Setting database option OFFLINE to ON for database DW_NICHQDB.

What I had posted earlier was the output from the Job.

My restore process is:

'ALTER DATABASE ' + @.database + ' SET OFFLINE WITH ROLLBACK IMMEDIATE'
RESTORE DATABASE @.database FROM DISK = @.fileBAK WITH FILE = 1, MOVE @.backupMDFName TO @.dbMDFFile, MOVE @.backupLDFName O @.dbLDFFile, NORECOVERY, REPLACE
RESTORE DATABASE @.database FROM DISK = @.fileDFF WITH FILE = 1, MOVE @.backupMDFName TO @.dbMDFFile, MOVE @.backupLDFName O @.dbLDFFile, NORECOVERY, REPLACE
RESTORE DATABASE @.database WITH RECOVERY

I then change the owner, fix users, change recovery model to SIMPLE, and finally shrink the DB.

What I find strange is that this works almost all of the time. In the case of the error log above the job completed successfully the second time it ran 40 minutes later. What would cause it to fail. I haven't had any issues with the 2005 databases and they all to through the exact same steps.

Here is an excerpt from the job log where it completed successfully for the IHI DB today:

>>Restoring: 6=IHI [SQLSTATE 01000]
DATABASE: IHI [SQLSTATE 01000]
PATH: D:\Backups\Production [SQLSTATE 01000]
FILE: IHI [SQLSTATE 01000]
>>Taking the database offline... [SQLSTATE 01000]
>>Restoring Backup File: D:\Backups\Production\IHI.bak [SQLSTATE 01000]
Processed 390848 pages for database 'IHI', file 'IHIDat.mdf' on file 1. [SQLSTATE 01000]
Processed 3 pages for database 'IHI', file 'IHILog.ldf' on file 1. [SQLSTATE 01000]
RESTORE DATABASE successfully processed 390851 pages in 126.707 seconds (25.269 MB/sec). [SQLSTATE 01000]
>>Bringing database online... [SQLSTATE 01000]
Converting database 'IHI' from version 539 to the current version 611. [SQLSTATE 01000]
Database 'IHI' running the upgrade step from version 539 to version 551. [SQLSTATE 01000]
Database 'IHI' running the upgrade step from version 551 to version 552. [SQLSTATE 01000]
Database 'IHI' running the upgrade step from version 552 to version 553. [SQLSTATE 01000]
Database 'IHI' running the upgrade step from version 553 to version 554. [SQLSTATE 01000]
Database 'IHI' running the upgrade step from version 554 to version 589. [SQLSTATE 01000]
Database 'IHI' running the upgrade step from version 589 to version 590. [SQLSTATE 01000]
Database 'IHI' running the upgrade step from version 590 to version 593. [SQLSTATE 01000]
Database 'IHI' running the upgrade step from version 593 to version 597. [SQLSTATE 01000]
Database 'IHI' running the upgrade step from version 597 to version 604. [SQLSTATE 01000]
Database 'IHI' running the upgrade step from version 604 to version 605. [SQLSTATE 01000]
Database 'IHI' running the upgrade step from version 605 to version 606. [SQLSTATE 01000]
Database 'IHI' running the upgrade step from version 606 to version 607. [SQLSTATE 01000]
Database 'IHI' running the upgrade step from version 607 to version 608. [SQLSTATE 01000]
Database 'IHI' running the upgrade step from version 608 to version 609. [SQLSTATE 01000]
Database 'IHI' running the upgrade step from version 609 to version 610. [SQLSTATE 01000]
Database 'IHI' running the upgrade step from version 610 to version 611. [SQLSTATE 01000]
RESTORE DATABASE successfully processed 0 pages in 29.169 seconds (0.000 MB/sec). [SQLSTATE 01000]
>>Changing owner to sa... [SQLSTATE 01000]
>>Fixing user account: mark [SQLSTATE 01000]
The row for user 'mark' will be fixed by updating its login link to a login already in existence. [SQLSTATE 01000]
The number of orphaned users fixed by updating users was 1. [SQLSTATE 01000]
The number of orphaned users fixed by adding new logins and then updating users was 0. [SQLSTATE 01000]
USE IHI; EXEC sp_change_users_login 'Auto_Fix', 'mark' [SQLSTATE 01000]
>>Changing Recovery Model to SIMPLE... [SQLSTATE 01000]
>>Shrinking the database... [SQLSTATE 01000]

Thanks, -Mark

|||

These two lines are interesting. I'm going to reverse them so they are in chronological order

...

01/28/2007 06:15:22,spid55,Unknown,Setting database option RECOVERY to SIMPLE for database IHI.
01/28/2007 06:15:25,Backup,Unknown,Log was backed up. Database: IHI<c/> creation date(time): 2007/01/17(09:44:21)<c/> first LSN: 17290:45:1<c/> last LSN: 17328:5750:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'S:\backup\IHI\IHI_20070128061502183_S.trn'}). This is an informational message only. No user action is required.
...

The first line actually gets output before we attempt the ALTER DATABASE operation. Do you have some kind of background process that goes through and does log backups on databases that are in FULL recovery mode? It looks like what is happening is that your ALTER DATABASE SET RECOVERY SIMPLE is failing because it is colliding with some other thread that is doing a BACKUP LOG at the same time. Is that possible?

|||

That is very possible. Since I convert these DBs to SIMPLE as part of the restore operation I didn't even really notice that the error log said a log was being taken.

This server has several custom jobs that our Datacenter host provides and supports that does daily backups along with 15 minute transaction logs. I convert these DBs to SIMPLE since we are only using them for reporting purposes on this server and I don't need to have the extra overhead with the backups.

When I am restoring the DB it is in FULL recovery mode since that is how it is on the production server we are copying from. During the restore operation the 15 minute backup is occasionally colliding with my restore causing the problem. All makes perfect sense now.

I never considered that job potentially being issue since for one I didn't create it and manage it and the other being that I know I put everything into SIMPLE which is skipped by that job.

I will work with my host to resolve the timing of my restores.

Thanks for your help. I definitelly needed another set of eyes.

-Mark

running stored procedures from SQL Agent

I'm trying to understand some behavior I'm seeing running a stored procedure
as a TSQL jobstep in a SQL Agent job. If I run the sp from query analyzer or
osql, it completes as expected, but in the process may generate a few 3604
errors (duplicate keys ignored on insert). If any errors other than 3604 are
generated, the procedure aborts.
When running this as a job step, this 3604 error causes the step to fail,
which I can understand. To try to work around this problem, in this stored
procedure I write out the status codes to a table each time. If the job
reports failure, I set the next job step to search this table of status
codes for any that are non-zero and not 3604. If none are found, this
jobstep reports success, and the job completes with success, otherwise the
job fails. The problem is, the first job stop does not actually run to
completion; it process a few times through its main loop and then seems to
just abort. Looking at the generated log file I see no indication of errors.
Profiler tells me that the first job step completed, but I can tell from the
logfile that it did not. It then goes onto run the second step, which
completes with success.
Any advice as to why this is happening would be greatly appreciated. I know
that error handling is somewhat limited in T-SQL, and that I cannot suppress
the error completely. I have a fair amount of experience working with SQL
Agent and jobs and have never run into a problem like this where a procedure
behaves differently when run as a job step.
Thanks for any advice.
-Gary
> I'm trying to understand some behavior I'm seeing running a stored
> procedure as a TSQL jobstep in a SQL Agent job. If I run the sp from query
> analyzer or osql, it completes as expected, but in the process may
> generate a few 3604 errors (duplicate keys ignored on insert). If any
> errors other than 3604 are generated, the procedure aborts.
Well, why not re-write the INSERT statement so that duplicate keys are not
inserted at all? Or, better yet, why bother having a primary key at all if
you are just going to insert redundant data and ignore the duplicate?
I imagine the main problem is that data is coming from BCP or BULK INSERT.
My suggestion is, if this is why you have IGNORE_DUP_KEY on, that you insert
into a work table and then perform an insert/update combination on the
primary table, after you've cleaned up the data.
Back to the original problem, while msg 3604 is technically a warning and
not an error, some applications are still going to view it as an error, and
there is not much you can do about that...
A
|||BTW, what version are you at? There was a hotfix for this issue a while
back:
http://support.microsoft.com/?id=295032
"Gary" <spam@.mail.com> wrote in message
news:O7cJra49FHA.2472@.TK2MSFTNGP12.phx.gbl...
> I'm trying to understand some behavior I'm seeing running a stored
> procedure as a TSQL jobstep in a SQL Agent job. If I run the sp from query
> analyzer or osql, it completes as expected, but in the process may
> generate a few 3604 errors (duplicate keys ignored on insert). If any
> errors other than 3604 are generated, the procedure aborts.
> When running this as a job step, this 3604 error causes the step to fail,
> which I can understand. To try to work around this problem, in this stored
> procedure I write out the status codes to a table each time. If the job
> reports failure, I set the next job step to search this table of status
> codes for any that are non-zero and not 3604. If none are found, this
> jobstep reports success, and the job completes with success, otherwise the
> job fails. The problem is, the first job stop does not actually run to
> completion; it process a few times through its main loop and then seems to
> just abort. Looking at the generated log file I see no indication of
> errors. Profiler tells me that the first job step completed, but I can
> tell from the logfile that it did not. It then goes onto run the second
> step, which completes with success.
> Any advice as to why this is happening would be greatly appreciated. I
> know that error handling is somewhat limited in T-SQL, and that I cannot
> suppress the error completely. I have a fair amount of experience working
> with SQL Agent and jobs and have never run into a problem like this where
> a procedure behaves differently when run as a job step.
> Thanks for any advice.
> -Gary
>
>
|||> inserted at all? Or, better yet, why bother having a primary key at all
> if you are just going to insert redundant data and ignore the duplicate?
Of course, it's late on Friday, and my brain is fried. Some of this stuff
I'm saying today doesn't make sense to my dogs, never mind me.
|||BTW, what version are you at? There was a hotfix for this issue a while
back:
http://support.microsoft.com/?id=295032
"Gary" <spam@.mail.com> wrote in message
news:O7cJra49FHA.2472@.TK2MSFTNGP12.phx.gbl...
> I'm trying to understand some behavior I'm seeing running a stored
> procedure as a TSQL jobstep in a SQL Agent job. If I run the sp from query
> analyzer or osql, it completes as expected, but in the process may
> generate a few 3604 errors (duplicate keys ignored on insert). If any
> errors other than 3604 are generated, the procedure aborts.
> When running this as a job step, this 3604 error causes the step to fail,
> which I can understand. To try to work around this problem, in this stored
> procedure I write out the status codes to a table each time. If the job
> reports failure, I set the next job step to search this table of status
> codes for any that are non-zero and not 3604. If none are found, this
> jobstep reports success, and the job completes with success, otherwise the
> job fails. The problem is, the first job stop does not actually run to
> completion; it process a few times through its main loop and then seems to
> just abort. Looking at the generated log file I see no indication of
> errors. Profiler tells me that the first job step completed, but I can
> tell from the logfile that it did not. It then goes onto run the second
> step, which completes with success.
> Any advice as to why this is happening would be greatly appreciated. I
> know that error handling is somewhat limited in T-SQL, and that I cannot
> suppress the error completely. I have a fair amount of experience working
> with SQL Agent and jobs and have never run into a problem like this where
> a procedure behaves differently when run as a job step.
> Thanks for any advice.
> -Gary
>
>
|||Thanks for the response.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OzX0Rj49FHA.2936@.tk2msftngp13.phx.gbl...
> Well, why not re-write the INSERT statement so that duplicate keys are not
> inserted at all? Or, better yet, why bother having a primary key at all
> if you are just going to insert redundant data and ignore the duplicate?
I think that may be my only choice. I was hoping to avoid this as there are
existing tables that are very large and I fear that all the speed I've
gained by doing BULK INSERT (over the previous way, which was done in vb,
but at least the warning could be suppressed) will be lost having to search
the existing data for anything in the work table. But I suppose that's what
indexes are for. I'm already creating a temp table to store the data before
it gets to it's final destination table, maybe it won't be too bad.

> I imagine the main problem is that data is coming from BCP or BULK INSERT.
> My suggestion is, if this is why you have IGNORE_DUP_KEY on, that you
> insert into a work table and then perform an insert/update combination on
> the primary table, after you've cleaned up the data.
Yes. This is really a band-aid for some older code that created indexes with
this option. Unfortunately these are on what are probably the biggest tables
in the database.
Thanks again for the advice,
-Gary
|||I've got SP3. I did see that there was a bug fixed in SP1 related to this.
What really surprises me is that it behaves differently when run as a job
step. All I do is "exec <sp>".
Thanks,
-Gary
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eNhK3n49FHA.1484@.tk2msftngp13.phx.gbl...
> BTW, what version are you at? There was a hotfix for this issue a while
> back:
> http://support.microsoft.com/?id=295032
>
|||> I fear that all the speed I've gained by doing BULK INSERT (over the
> previous way, which was done in vb, but at least the warning could be
> suppressed) will be lost having to search the existing data for anything
> in the work table.
Well, don't you think SQL Server is doing all that work anyway? How else
would it know to raise all those msg 3604's?
Anyway, I came across a potential workaround, and that is to fire your BULK
INSERT from CMDExec and OSQL instead of directly from within the job step.
Sounds like this will interpret the warning correctly and will prevent SQL
Agent from barfing, though it will make your job step slightly more complex.
Credit to Tibor, though I didn't research further to find the origin of this
solution:
http://tinyurl.com/73yes
|||> I fear that all the speed I've gained by doing BULK INSERT (over the
> previous way, which was done in vb, but at least the warning could be
> suppressed) will be lost having to search the existing data for anything
> in the work table.
Well, don't you think SQL Server is doing all that work anyway? How else
would it know to raise all those msg 3604's?
Anyway, I came across a potential workaround, and that is to fire your BULK
INSERT from CMDExec and OSQL instead of directly from within the job step.
Sounds like this will interpret the warning correctly and will prevent SQL
Agent from barfing, though it will make your job step slightly more complex.
Credit to Tibor, though I didn't research further to find the origin of this
solution:
http://tinyurl.com/73yes
|||> I've got SP3. I did see that there was a bug fixed in SP1 related to this.
> What really surprises me is that it behaves differently when run as a job
> step. All I do is "exec <sp>".
Right, but there is more to SQL Agent than just calling your code... this is
NOT apples to apples. For starters, it uses a different library to connect
to the server than, say, QA or VB would. It also executes as a different
user, in most cases, unless your job is owned by SA and you routinely
connect via QA or VB using SA (shame, shame).
I am sure there are long-winded discussions on google and probably some good
information in Books Online about all the nitty-gritty differences between
code you execute and code SQL Agent executes. The short answer is that they
are not the same.
A

Monday, March 26, 2012

Running TSQL job for SQL server agent is failing, giving me ANSI errors?

Running TSQL job for SQL server agent is failing, giving me ANSI errors?

Running TSQL job for SQL server agent is failing, giving me ANSI errors?

Running Transact-SQL script from SQL server agent with a different account

Friday, March 23, 2012

Running Time of Package to be logged in the Email Notification

Running Time of Package to be logged in the Email Notification

running the upgrade step from version 610 to version 611 fails

running stored procedures from SQL Agent

db files

Blog Archive

About Me