I have a table with a text data type and when I run my sql manually,
everything works fine - the text column is completely filled with what
I need (it's html from a http post request via a stored procedure).
When I put the exact same SQL inside a job, and kick the job off (or
let the agent pick it up on the schedule), it truncates the text column
to a width of 498, rendering my later job steps useless b/c the
expected data isn't there.
Why would the job cutoff the text in this column?What is the syntax used in the proc? Are you issuing an update/insert
or a writetext/updatetext statement?
Showing posts with label table. Show all posts
Showing posts with label table. Show all posts
Friday, March 30, 2012
Job truncates text data type
I have a table with a text data type and when I run my sql manually,
everything works fine - the text column is completely filled with what
I need (it's html from a http post request via a stored procedure).
When I put the exact same SQL inside a job, and kick the job off (or
let the agent pick it up on the schedule), it truncates the text column
to a width of 498, rendering my later job steps useless b/c the
expected data isn't there.
Why would the job cutoff the text in this column?What is the syntax used in the proc? Are you issuing an update/insert
or a writetext/updatetext statement?
everything works fine - the text column is completely filled with what
I need (it's html from a http post request via a stored procedure).
When I put the exact same SQL inside a job, and kick the job off (or
let the agent pick it up on the schedule), it truncates the text column
to a width of 498, rendering my later job steps useless b/c the
expected data isn't there.
Why would the job cutoff the text in this column?What is the syntax used in the proc? Are you issuing an update/insert
or a writetext/updatetext statement?
Wednesday, March 28, 2012
Job System Table
Hello,
Three questions:
(1) Is there a table (a system table?) on sql server 2000 that will
give you a list of the jobs on the database and when they are
scheduled? Also,
(2) Is there a table on SQL server 2000 that will give you the list of
DTS packages?
(3) Does SQL server track dependencies between jobs and stored
procedures? That is does it store a reference to objects that a stored
procedure or DTS package uses? If so is this information available in
a table?
Thanks for the helpBonifide,
1 and 2 - yes. Download the following...it will help answer these questions
and maybe more.
SQL Server System Table Map
http://www.microsoft.com/sql/techinfo/productdoc/2000/systables.mspx
HTH
Jerry
"bonifide" <jeffsutthoff@.hotmail.com> wrote in message
news:1129748256.102327.303020@.o13g2000cwo.googlegroups.com...
> Hello,
> Three questions:
> (1) Is there a table (a system table?) on sql server 2000 that will
> give you a list of the jobs on the database and when they are
> scheduled? Also,
> (2) Is there a table on SQL server 2000 that will give you the list of
> DTS packages?
> (3) Does SQL server track dependencies between jobs and stored
> procedures? That is does it store a reference to objects that a stored
> procedure or DTS package uses? If so is this information available in
> a table?
> Thanks for the help
>|||Look in the MSDB DB for sysjobs, sysjobsschedules, sysjobsteps, sysdtspackages
http://sqlservercode.blogspot.com/
"bonifide" wrote:
> Hello,
> Three questions:
> (1) Is there a table (a system table?) on sql server 2000 that will
> give you a list of the jobs on the database and when they are
> scheduled? Also,
> (2) Is there a table on SQL server 2000 that will give you the list of
> DTS packages?
> (3) Does SQL server track dependencies between jobs and stored
> procedures? That is does it store a reference to objects that a stored
> procedure or DTS package uses? If so is this information available in
> a table?
> Thanks for the help
>|||Comments Inline
"bonifide" <jeffsutthoff@.hotmail.com> wrote in message
news:1129748256.102327.303020@.o13g2000cwo.googlegroups.com...
> Hello,
> Three questions:
> (1) Is there a table (a system table?) on sql server 2000 that will
> give you a list of the jobs on the database and when they are
> scheduled? Also,
There are multiple tables in msdb that together contain this information.
For jobs\databases, you will need to link sysjobsteps (for the database
information) and sysjobs. You will have to add sysjobservers if you are
using multi-server job administration. See the SQL 2000 System table map
for details.
> (2) Is there a table on SQL server 2000 that will give you the list of
> DTS packages?
msdb.dbo.sysdtspackages
> (3) Does SQL server track dependencies between jobs and stored
> procedures? That is does it store a reference to objects that a stored
> procedure or DTS package uses? If so is this information available in
> a table?
No. SQL Server 7.0 and higher use late bindings so you can create objects
that refer to other objects that may not exist yet. There are some semsible
exceptions such as you cannot create a trigger on a table that does not
exist, but job steps can refer to nonexistent objects indefinitely.
> Thanks for the help
>
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
Three questions:
(1) Is there a table (a system table?) on sql server 2000 that will
give you a list of the jobs on the database and when they are
scheduled? Also,
(2) Is there a table on SQL server 2000 that will give you the list of
DTS packages?
(3) Does SQL server track dependencies between jobs and stored
procedures? That is does it store a reference to objects that a stored
procedure or DTS package uses? If so is this information available in
a table?
Thanks for the helpBonifide,
1 and 2 - yes. Download the following...it will help answer these questions
and maybe more.
SQL Server System Table Map
http://www.microsoft.com/sql/techinfo/productdoc/2000/systables.mspx
HTH
Jerry
"bonifide" <jeffsutthoff@.hotmail.com> wrote in message
news:1129748256.102327.303020@.o13g2000cwo.googlegroups.com...
> Hello,
> Three questions:
> (1) Is there a table (a system table?) on sql server 2000 that will
> give you a list of the jobs on the database and when they are
> scheduled? Also,
> (2) Is there a table on SQL server 2000 that will give you the list of
> DTS packages?
> (3) Does SQL server track dependencies between jobs and stored
> procedures? That is does it store a reference to objects that a stored
> procedure or DTS package uses? If so is this information available in
> a table?
> Thanks for the help
>|||Look in the MSDB DB for sysjobs, sysjobsschedules, sysjobsteps, sysdtspackages
http://sqlservercode.blogspot.com/
"bonifide" wrote:
> Hello,
> Three questions:
> (1) Is there a table (a system table?) on sql server 2000 that will
> give you a list of the jobs on the database and when they are
> scheduled? Also,
> (2) Is there a table on SQL server 2000 that will give you the list of
> DTS packages?
> (3) Does SQL server track dependencies between jobs and stored
> procedures? That is does it store a reference to objects that a stored
> procedure or DTS package uses? If so is this information available in
> a table?
> Thanks for the help
>|||Comments Inline
"bonifide" <jeffsutthoff@.hotmail.com> wrote in message
news:1129748256.102327.303020@.o13g2000cwo.googlegroups.com...
> Hello,
> Three questions:
> (1) Is there a table (a system table?) on sql server 2000 that will
> give you a list of the jobs on the database and when they are
> scheduled? Also,
There are multiple tables in msdb that together contain this information.
For jobs\databases, you will need to link sysjobsteps (for the database
information) and sysjobs. You will have to add sysjobservers if you are
using multi-server job administration. See the SQL 2000 System table map
for details.
> (2) Is there a table on SQL server 2000 that will give you the list of
> DTS packages?
msdb.dbo.sysdtspackages
> (3) Does SQL server track dependencies between jobs and stored
> procedures? That is does it store a reference to objects that a stored
> procedure or DTS package uses? If so is this information available in
> a table?
No. SQL Server 7.0 and higher use late bindings so you can create objects
that refer to other objects that may not exist yet. There are some semsible
exceptions such as you cannot create a trigger on a table that does not
exist, but job steps can refer to nonexistent objects indefinitely.
> Thanks for the help
>
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
Job System Table
Hello,
Three questions:
(1) Is there a table (a system table?) on sql server 2000 that will
give you a list of the jobs on the database and when they are
scheduled? Also,
(2) Is there a table on SQL server 2000 that will give you the list of
DTS packages?
(3) Does SQL server track dependencies between jobs and stored
procedures? That is does it store a reference to objects that a stored
procedure or DTS package uses? If so is this information available in
a table?
Thanks for the helpBonifide,
1 and 2 - yes. Download the following...it will help answer these questions
and maybe more.
SQL Server System Table Map
http://www.microsoft.com/sql/techin.../systables.mspx
HTH
Jerry
"bonifide" <jeffsutthoff@.hotmail.com> wrote in message
news:1129748256.102327.303020@.o13g2000cwo.googlegroups.com...
> Hello,
> Three questions:
> (1) Is there a table (a system table?) on sql server 2000 that will
> give you a list of the jobs on the database and when they are
> scheduled? Also,
> (2) Is there a table on SQL server 2000 that will give you the list of
> DTS packages?
> (3) Does SQL server track dependencies between jobs and stored
> procedures? That is does it store a reference to objects that a stored
> procedure or DTS package uses? If so is this information available in
> a table?
> Thanks for the help
>|||Look in the MSDB DB for sysjobs, sysjobsschedules, sysjobsteps, sysdtspackag
es
http://sqlservercode.blogspot.com/
"bonifide" wrote:
> Hello,
> Three questions:
> (1) Is there a table (a system table?) on sql server 2000 that will
> give you a list of the jobs on the database and when they are
> scheduled? Also,
> (2) Is there a table on SQL server 2000 that will give you the list of
> DTS packages?
> (3) Does SQL server track dependencies between jobs and stored
> procedures? That is does it store a reference to objects that a stored
> procedure or DTS package uses? If so is this information available in
> a table?
> Thanks for the help
>|||Comments Inline
"bonifide" <jeffsutthoff@.hotmail.com> wrote in message
news:1129748256.102327.303020@.o13g2000cwo.googlegroups.com...
> Hello,
> Three questions:
> (1) Is there a table (a system table?) on sql server 2000 that will
> give you a list of the jobs on the database and when they are
> scheduled? Also,
There are multiple tables in msdb that together contain this information.
For jobs\databases, you will need to link sysjobsteps (for the database
information) and sysjobs. You will have to add sysjobservers if you are
using multi-server job administration. See the SQL 2000 System table map
for details.
> (2) Is there a table on SQL server 2000 that will give you the list of
> DTS packages?
msdb.dbo.sysdtspackages
> (3) Does SQL server track dependencies between jobs and stored
> procedures? That is does it store a reference to objects that a stored
> procedure or DTS package uses? If so is this information available in
> a table?
No. SQL Server 7.0 and higher use late bindings so you can create objects
that refer to other objects that may not exist yet. There are some semsible
exceptions such as you cannot create a trigger on a table that does not
exist, but job steps can refer to nonexistent objects indefinitely.
> Thanks for the help
>
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
Three questions:
(1) Is there a table (a system table?) on sql server 2000 that will
give you a list of the jobs on the database and when they are
scheduled? Also,
(2) Is there a table on SQL server 2000 that will give you the list of
DTS packages?
(3) Does SQL server track dependencies between jobs and stored
procedures? That is does it store a reference to objects that a stored
procedure or DTS package uses? If so is this information available in
a table?
Thanks for the helpBonifide,
1 and 2 - yes. Download the following...it will help answer these questions
and maybe more.
SQL Server System Table Map
http://www.microsoft.com/sql/techin.../systables.mspx
HTH
Jerry
"bonifide" <jeffsutthoff@.hotmail.com> wrote in message
news:1129748256.102327.303020@.o13g2000cwo.googlegroups.com...
> Hello,
> Three questions:
> (1) Is there a table (a system table?) on sql server 2000 that will
> give you a list of the jobs on the database and when they are
> scheduled? Also,
> (2) Is there a table on SQL server 2000 that will give you the list of
> DTS packages?
> (3) Does SQL server track dependencies between jobs and stored
> procedures? That is does it store a reference to objects that a stored
> procedure or DTS package uses? If so is this information available in
> a table?
> Thanks for the help
>|||Look in the MSDB DB for sysjobs, sysjobsschedules, sysjobsteps, sysdtspackag
es
http://sqlservercode.blogspot.com/
"bonifide" wrote:
> Hello,
> Three questions:
> (1) Is there a table (a system table?) on sql server 2000 that will
> give you a list of the jobs on the database and when they are
> scheduled? Also,
> (2) Is there a table on SQL server 2000 that will give you the list of
> DTS packages?
> (3) Does SQL server track dependencies between jobs and stored
> procedures? That is does it store a reference to objects that a stored
> procedure or DTS package uses? If so is this information available in
> a table?
> Thanks for the help
>|||Comments Inline
"bonifide" <jeffsutthoff@.hotmail.com> wrote in message
news:1129748256.102327.303020@.o13g2000cwo.googlegroups.com...
> Hello,
> Three questions:
> (1) Is there a table (a system table?) on sql server 2000 that will
> give you a list of the jobs on the database and when they are
> scheduled? Also,
There are multiple tables in msdb that together contain this information.
For jobs\databases, you will need to link sysjobsteps (for the database
information) and sysjobs. You will have to add sysjobservers if you are
using multi-server job administration. See the SQL 2000 System table map
for details.
> (2) Is there a table on SQL server 2000 that will give you the list of
> DTS packages?
msdb.dbo.sysdtspackages
> (3) Does SQL server track dependencies between jobs and stored
> procedures? That is does it store a reference to objects that a stored
> procedure or DTS package uses? If so is this information available in
> a table?
No. SQL Server 7.0 and higher use late bindings so you can create objects
that refer to other objects that may not exist yet. There are some semsible
exceptions such as you cannot create a trigger on a table that does not
exist, but job steps can refer to nonexistent objects indefinitely.
> Thanks for the help
>
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
Job System Table
Hello,
Three questions:
(1) Is there a table (a system table?) on sql server 2000 that will
give you a list of the jobs on the database and when they are
scheduled? Also,
(2) Is there a table on SQL server 2000 that will give you the list of
DTS packages?
(3) Does SQL server track dependencies between jobs and stored
procedures? That is does it store a reference to objects that a stored
procedure or DTS package uses? If so is this information available in
a table?
Thanks for the help
Bonifide,
1 and 2 - yes. Download the following...it will help answer these questions
and maybe more.
SQL Server System Table Map
http://www.microsoft.com/sql/techinf...systables.mspx
HTH
Jerry
"bonifide" <jeffsutthoff@.hotmail.com> wrote in message
news:1129748256.102327.303020@.o13g2000cwo.googlegr oups.com...
> Hello,
> Three questions:
> (1) Is there a table (a system table?) on sql server 2000 that will
> give you a list of the jobs on the database and when they are
> scheduled? Also,
> (2) Is there a table on SQL server 2000 that will give you the list of
> DTS packages?
> (3) Does SQL server track dependencies between jobs and stored
> procedures? That is does it store a reference to objects that a stored
> procedure or DTS package uses? If so is this information available in
> a table?
> Thanks for the help
>
|||Look in the MSDB DB for sysjobs, sysjobsschedules, sysjobsteps, sysdtspackages
http://sqlservercode.blogspot.com/
"bonifide" wrote:
> Hello,
> Three questions:
> (1) Is there a table (a system table?) on sql server 2000 that will
> give you a list of the jobs on the database and when they are
> scheduled? Also,
> (2) Is there a table on SQL server 2000 that will give you the list of
> DTS packages?
> (3) Does SQL server track dependencies between jobs and stored
> procedures? That is does it store a reference to objects that a stored
> procedure or DTS package uses? If so is this information available in
> a table?
> Thanks for the help
>
|||Comments Inline
"bonifide" <jeffsutthoff@.hotmail.com> wrote in message
news:1129748256.102327.303020@.o13g2000cwo.googlegr oups.com...
> Hello,
> Three questions:
> (1) Is there a table (a system table?) on sql server 2000 that will
> give you a list of the jobs on the database and when they are
> scheduled? Also,
There are multiple tables in msdb that together contain this information.
For jobs\databases, you will need to link sysjobsteps (for the database
information) and sysjobs. You will have to add sysjobservers if you are
using multi-server job administration. See the SQL 2000 System table map
for details.
> (2) Is there a table on SQL server 2000 that will give you the list of
> DTS packages?
msdb.dbo.sysdtspackages
> (3) Does SQL server track dependencies between jobs and stored
> procedures? That is does it store a reference to objects that a stored
> procedure or DTS package uses? If so is this information available in
> a table?
No. SQL Server 7.0 and higher use late bindings so you can create objects
that refer to other objects that may not exist yet. There are some semsible
exceptions such as you cannot create a trigger on a table that does not
exist, but job steps can refer to nonexistent objects indefinitely.
> Thanks for the help
>
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
Three questions:
(1) Is there a table (a system table?) on sql server 2000 that will
give you a list of the jobs on the database and when they are
scheduled? Also,
(2) Is there a table on SQL server 2000 that will give you the list of
DTS packages?
(3) Does SQL server track dependencies between jobs and stored
procedures? That is does it store a reference to objects that a stored
procedure or DTS package uses? If so is this information available in
a table?
Thanks for the help
Bonifide,
1 and 2 - yes. Download the following...it will help answer these questions
and maybe more.
SQL Server System Table Map
http://www.microsoft.com/sql/techinf...systables.mspx
HTH
Jerry
"bonifide" <jeffsutthoff@.hotmail.com> wrote in message
news:1129748256.102327.303020@.o13g2000cwo.googlegr oups.com...
> Hello,
> Three questions:
> (1) Is there a table (a system table?) on sql server 2000 that will
> give you a list of the jobs on the database and when they are
> scheduled? Also,
> (2) Is there a table on SQL server 2000 that will give you the list of
> DTS packages?
> (3) Does SQL server track dependencies between jobs and stored
> procedures? That is does it store a reference to objects that a stored
> procedure or DTS package uses? If so is this information available in
> a table?
> Thanks for the help
>
|||Look in the MSDB DB for sysjobs, sysjobsschedules, sysjobsteps, sysdtspackages
http://sqlservercode.blogspot.com/
"bonifide" wrote:
> Hello,
> Three questions:
> (1) Is there a table (a system table?) on sql server 2000 that will
> give you a list of the jobs on the database and when they are
> scheduled? Also,
> (2) Is there a table on SQL server 2000 that will give you the list of
> DTS packages?
> (3) Does SQL server track dependencies between jobs and stored
> procedures? That is does it store a reference to objects that a stored
> procedure or DTS package uses? If so is this information available in
> a table?
> Thanks for the help
>
|||Comments Inline
"bonifide" <jeffsutthoff@.hotmail.com> wrote in message
news:1129748256.102327.303020@.o13g2000cwo.googlegr oups.com...
> Hello,
> Three questions:
> (1) Is there a table (a system table?) on sql server 2000 that will
> give you a list of the jobs on the database and when they are
> scheduled? Also,
There are multiple tables in msdb that together contain this information.
For jobs\databases, you will need to link sysjobsteps (for the database
information) and sysjobs. You will have to add sysjobservers if you are
using multi-server job administration. See the SQL 2000 System table map
for details.
> (2) Is there a table on SQL server 2000 that will give you the list of
> DTS packages?
msdb.dbo.sysdtspackages
> (3) Does SQL server track dependencies between jobs and stored
> procedures? That is does it store a reference to objects that a stored
> procedure or DTS package uses? If so is this information available in
> a table?
No. SQL Server 7.0 and higher use late bindings so you can create objects
that refer to other objects that may not exist yet. There are some semsible
exceptions such as you cannot create a trigger on a table that does not
exist, but job steps can refer to nonexistent objects indefinitely.
> Thanks for the help
>
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
Monday, March 26, 2012
Job scheduler
Hi all
In which table in msdb Database the Executing status of
the job is stored. I need the table name - which holds the
information like
1. Job started.
2. Job executing
3. Job completed info.
The above status may be in the same field of the table
Thanks in advance
Anand.Hi,
Data will be stored in SYSJOBHISTORY table of MSDB database
Column name is : RUN_STATUS
The column hols the below status,
0 = Failed
1 = Succeeded
2 = Retry
3 = Canceled
4 = In progress
Since querying the system table is not advisible , you can execute the
system procedure "SP_HELP_JOBHISTORY" to get the information regarding the
status of each scheduled task.
Thanks
Hari
MCDBA
"Guru S. Anand" <anonymous@.discussions.microsoft.com> wrote in message
news:2d1b01c3fcd6$10b00d80$a001280a@.phx.gbl...
> Hi all
> In which table in msdb Database the Executing status of
> the job is stored. I need the table name - which holds the
> information like
> 1. Job started.
> 2. Job executing
> 3. Job completed info.
> The above status may be in the same field of the table
> Thanks in advance
> Anand.|||Thanks Hari
Anand.
>--Original Message--
>Hi,
>Data will be stored in SYSJOBHISTORY table of MSDB
database
>Column name is : RUN_STATUS
>The column hols the below status,
>0 = Failed
>1 = Succeeded
>2 = Retry
>3 = Canceled
>4 = In progress
>Since querying the system table is not advisible , you
can execute the
>system procedure "SP_HELP_JOBHISTORY" to get the
information regarding the
>status of each scheduled task.
>Thanks
>Hari
>MCDBA
>
>"Guru S. Anand" <anonymous@.discussions.microsoft.com>
wrote in message
>news:2d1b01c3fcd6$10b00d80$a001280a@.phx.gbl...
the
>
>.
>
In which table in msdb Database the Executing status of
the job is stored. I need the table name - which holds the
information like
1. Job started.
2. Job executing
3. Job completed info.
The above status may be in the same field of the table
Thanks in advance
Anand.Hi,
Data will be stored in SYSJOBHISTORY table of MSDB database
Column name is : RUN_STATUS
The column hols the below status,
0 = Failed
1 = Succeeded
2 = Retry
3 = Canceled
4 = In progress
Since querying the system table is not advisible , you can execute the
system procedure "SP_HELP_JOBHISTORY" to get the information regarding the
status of each scheduled task.
Thanks
Hari
MCDBA
"Guru S. Anand" <anonymous@.discussions.microsoft.com> wrote in message
news:2d1b01c3fcd6$10b00d80$a001280a@.phx.gbl...
> Hi all
> In which table in msdb Database the Executing status of
> the job is stored. I need the table name - which holds the
> information like
> 1. Job started.
> 2. Job executing
> 3. Job completed info.
> The above status may be in the same field of the table
> Thanks in advance
> Anand.|||Thanks Hari
Anand.
>--Original Message--
>Hi,
>Data will be stored in SYSJOBHISTORY table of MSDB
database
>Column name is : RUN_STATUS
>The column hols the below status,
>0 = Failed
>1 = Succeeded
>2 = Retry
>3 = Canceled
>4 = In progress
>Since querying the system table is not advisible , you
can execute the
>system procedure "SP_HELP_JOBHISTORY" to get the
information regarding the
>status of each scheduled task.
>Thanks
>Hari
>MCDBA
>
>"Guru S. Anand" <anonymous@.discussions.microsoft.com>
wrote in message
>news:2d1b01c3fcd6$10b00d80$a001280a@.phx.gbl...
the
>
>.
>
Job scheduler
Hi all
In which table in msdb Database the Executing status of
the job is stored. I need the table name - which holds the
information like
1. Job started.
2. Job executing
3. Job completed info.
The above status may be in the same field of the table
Thanks in advance
Anand.Hi,
Data will be stored in SYSJOBHISTORY table of MSDB database
Column name is : RUN_STATUS
The column hols the below status,
0 = Failed
1 = Succeeded
2 = Retry
3 = Canceled
4 = In progress
Since querying the system table is not advisible , you can execute the
system procedure "SP_HELP_JOBHISTORY" to get the information regarding the
status of each scheduled task.
Thanks
Hari
MCDBA
"Guru S. Anand" <anonymous@.discussions.microsoft.com> wrote in message
news:2d1b01c3fcd6$10b00d80$a001280a@.phx.gbl...
> Hi all
> In which table in msdb Database the Executing status of
> the job is stored. I need the table name - which holds the
> information like
> 1. Job started.
> 2. Job executing
> 3. Job completed info.
> The above status may be in the same field of the table
> Thanks in advance
> Anand.|||Thanks Hari
Anand.
>--Original Message--
>Hi,
>Data will be stored in SYSJOBHISTORY table of MSDB
database
>Column name is : RUN_STATUS
>The column hols the below status,
>0 = Failed
>1 = Succeeded
>2 = Retry
>3 = Canceled
>4 = In progress
>Since querying the system table is not advisible , you
can execute the
>system procedure "SP_HELP_JOBHISTORY" to get the
information regarding the
>status of each scheduled task.
>Thanks
>Hari
>MCDBA
>
>"Guru S. Anand" <anonymous@.discussions.microsoft.com>
wrote in message
>news:2d1b01c3fcd6$10b00d80$a001280a@.phx.gbl...
>> Hi all
>> In which table in msdb Database the Executing status of
>> the job is stored. I need the table name - which holds
the
>> information like
>> 1. Job started.
>> 2. Job executing
>> 3. Job completed info.
>> The above status may be in the same field of the table
>> Thanks in advance
>> Anand.
>
>.
>
In which table in msdb Database the Executing status of
the job is stored. I need the table name - which holds the
information like
1. Job started.
2. Job executing
3. Job completed info.
The above status may be in the same field of the table
Thanks in advance
Anand.Hi,
Data will be stored in SYSJOBHISTORY table of MSDB database
Column name is : RUN_STATUS
The column hols the below status,
0 = Failed
1 = Succeeded
2 = Retry
3 = Canceled
4 = In progress
Since querying the system table is not advisible , you can execute the
system procedure "SP_HELP_JOBHISTORY" to get the information regarding the
status of each scheduled task.
Thanks
Hari
MCDBA
"Guru S. Anand" <anonymous@.discussions.microsoft.com> wrote in message
news:2d1b01c3fcd6$10b00d80$a001280a@.phx.gbl...
> Hi all
> In which table in msdb Database the Executing status of
> the job is stored. I need the table name - which holds the
> information like
> 1. Job started.
> 2. Job executing
> 3. Job completed info.
> The above status may be in the same field of the table
> Thanks in advance
> Anand.|||Thanks Hari
Anand.
>--Original Message--
>Hi,
>Data will be stored in SYSJOBHISTORY table of MSDB
database
>Column name is : RUN_STATUS
>The column hols the below status,
>0 = Failed
>1 = Succeeded
>2 = Retry
>3 = Canceled
>4 = In progress
>Since querying the system table is not advisible , you
can execute the
>system procedure "SP_HELP_JOBHISTORY" to get the
information regarding the
>status of each scheduled task.
>Thanks
>Hari
>MCDBA
>
>"Guru S. Anand" <anonymous@.discussions.microsoft.com>
wrote in message
>news:2d1b01c3fcd6$10b00d80$a001280a@.phx.gbl...
>> Hi all
>> In which table in msdb Database the Executing status of
>> the job is stored. I need the table name - which holds
the
>> information like
>> 1. Job started.
>> 2. Job executing
>> 3. Job completed info.
>> The above status may be in the same field of the table
>> Thanks in advance
>> Anand.
>
>.
>
Job Schedule.
Hi to all,
does anyone knows how to schedule a job to run in seconds time interval ?
I have found the sysobschedule table in msdb but even if i change the
freq_subday_type to 2 (seconds) the job doesn't run in seconds.
The only solution i imagine is to run once the Stored Procedure i want
and add a WAITFOR DELAY '00:00.05' command,
so this will run it constantly (and for ever...)
Is there any other way to accomplish the same thing,
or are there any side effects to have an sp to run forever
with the WAITFOR statement '
Thanks a lot for any help!
Anthony BoudouvasGert Drapers posted an undocumented way around this
limitation awhile back- it sounds like you had the right
idea on how to go about it, maybe something just got missed
in how you implemented it. Check the following example:
http://tinyurl.com/q0vv
-Sue
On Tue, 7 Oct 2003 13:08:59 +0200, "Anthony Boudouvas"
<anthonyb@.mediatel.gr> wrote:
>Hi to all,
>does anyone knows how to schedule a job to run in seconds time interval ?
>I have found the sysobschedule table in msdb but even if i change the
>freq_subday_type to 2 (seconds) the job doesn't run in seconds.
>The only solution i imagine is to run once the Stored Procedure i want
>and add a WAITFOR DELAY '00:00.05' command,
>so this will run it constantly (and for ever...)
>Is there any other way to accomplish the same thing,
>or are there any side effects to have an sp to run forever
>with the WAITFOR statement '
>
>Thanks a lot for any help!
>Anthony Boudouvas
>|||Ok, it did work that way.
Thanks a lot!
anthonyb
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:2aj5ov8bk7klsmgf79hgn70285g8fk112i@.4ax.com...
> Gert Drapers posted an undocumented way around this
> limitation awhile back- it sounds like you had the right
> idea on how to go about it, maybe something just got missed
> in how you implemented it. Check the following example:
> http://tinyurl.com/q0vv
> -Sue
> On Tue, 7 Oct 2003 13:08:59 +0200, "Anthony Boudouvas"
> <anthonyb@.mediatel.gr> wrote:
> >Hi to all,
> >
> >does anyone knows how to schedule a job to run in seconds time interval ?
> >I have found the sysobschedule table in msdb but even if i change the
> >freq_subday_type to 2 (seconds) the job doesn't run in seconds.
> >
> >The only solution i imagine is to run once the Stored Procedure i want
> >and add a WAITFOR DELAY '00:00.05' command,
> >so this will run it constantly (and for ever...)
> >
> >Is there any other way to accomplish the same thing,
> >or are there any side effects to have an sp to run forever
> >with the WAITFOR statement '
> >
> >
> >Thanks a lot for any help!
> >
> >Anthony Boudouvas
> >
>
does anyone knows how to schedule a job to run in seconds time interval ?
I have found the sysobschedule table in msdb but even if i change the
freq_subday_type to 2 (seconds) the job doesn't run in seconds.
The only solution i imagine is to run once the Stored Procedure i want
and add a WAITFOR DELAY '00:00.05' command,
so this will run it constantly (and for ever...)
Is there any other way to accomplish the same thing,
or are there any side effects to have an sp to run forever
with the WAITFOR statement '
Thanks a lot for any help!
Anthony BoudouvasGert Drapers posted an undocumented way around this
limitation awhile back- it sounds like you had the right
idea on how to go about it, maybe something just got missed
in how you implemented it. Check the following example:
http://tinyurl.com/q0vv
-Sue
On Tue, 7 Oct 2003 13:08:59 +0200, "Anthony Boudouvas"
<anthonyb@.mediatel.gr> wrote:
>Hi to all,
>does anyone knows how to schedule a job to run in seconds time interval ?
>I have found the sysobschedule table in msdb but even if i change the
>freq_subday_type to 2 (seconds) the job doesn't run in seconds.
>The only solution i imagine is to run once the Stored Procedure i want
>and add a WAITFOR DELAY '00:00.05' command,
>so this will run it constantly (and for ever...)
>Is there any other way to accomplish the same thing,
>or are there any side effects to have an sp to run forever
>with the WAITFOR statement '
>
>Thanks a lot for any help!
>Anthony Boudouvas
>|||Ok, it did work that way.
Thanks a lot!
anthonyb
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:2aj5ov8bk7klsmgf79hgn70285g8fk112i@.4ax.com...
> Gert Drapers posted an undocumented way around this
> limitation awhile back- it sounds like you had the right
> idea on how to go about it, maybe something just got missed
> in how you implemented it. Check the following example:
> http://tinyurl.com/q0vv
> -Sue
> On Tue, 7 Oct 2003 13:08:59 +0200, "Anthony Boudouvas"
> <anthonyb@.mediatel.gr> wrote:
> >Hi to all,
> >
> >does anyone knows how to schedule a job to run in seconds time interval ?
> >I have found the sysobschedule table in msdb but even if i change the
> >freq_subday_type to 2 (seconds) the job doesn't run in seconds.
> >
> >The only solution i imagine is to run once the Stored Procedure i want
> >and add a WAITFOR DELAY '00:00.05' command,
> >so this will run it constantly (and for ever...)
> >
> >Is there any other way to accomplish the same thing,
> >or are there any side effects to have an sp to run forever
> >with the WAITFOR statement '
> >
> >
> >Thanks a lot for any help!
> >
> >Anthony Boudouvas
> >
>
Friday, March 23, 2012
JOb running SSIS package
I have an issue when a job is scheduled to run a SSIS package. The package (exporte a table to a text file) runs fine from microsoft visual studio but when i create a job and run it, i get the following error:
|||when i turn on the computer i get error messages about an open file? everything listed in the menu includes this axel but i canoot open it and I need to fix this error, it is preventing me from installing a camera. any help?
[298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user 'VOLCANO\Administrator', error code 0x534. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
Can you tell me what operating system you have? Windows 2000, Windows 2003?
Thanks
Ovidiu Burlacu
|||when i turn on the computer i get error messages about an open file? everything listed in the menu includes this axel but i canoot open it and I need to fix this error, it is preventing me from installing a camera. any help?
|||
This forum (and the error above) is for SQL Server Integration Services - is your question related to that?
JOb running SSIS package
I have an issue when a job is scheduled to run a SSIS package. The package (exporte a table to a text file) runs fine from microsoft visual studio but when i create a job and run it, i get the following error:
[298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user 'VOLCANO\Administrator', error code 0x534. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
Can you tell me what operating system you have? Windows 2000, Windows 2003?
Thanks
Ovidiu Burlacu
|||
This forum (and the error above) is for SQL Server Integration Services - is your question related to that?
Monday, March 12, 2012
Job failure
I have a job that runs every evening that calls 2 stored
procedures. The first sp inserts new records from a
source database.table into a destination database.table
the second job updates the records that were changed at
the source and updates them at the destination.
This job has run successfully without problems for 2+
years. Now for the past two days the second step is
blowing up the transaction log to more than 7GB (it's
limit). Any idea what could cause this new behaviour?
Any suggestions as to what I need to look at would be
most appreciated.
Thanks,
Marc
Is the recovery model of the destination database changed recently? Are the
backup jobs running successfully?
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Marc" <anonymous@.discussions.microsoft.com> wrote in message
news:244ad01c45f4f$3aef6880$a501280a@.phx.gbl...
I have a job that runs every evening that calls 2 stored
procedures. The first sp inserts new records from a
source database.table into a destination database.table
the second job updates the records that were changed at
the source and updates them at the destination.
This job has run successfully without problems for 2+
years. Now for the past two days the second step is
blowing up the transaction log to more than 7GB (it's
limit). Any idea what could cause this new behaviour?
Any suggestions as to what I need to look at would be
most appreciated.
Thanks,
Marc
|||Could be that the recovery model was changed from simple
to full, but I'm not 100% as this isn't my server.
The backups that are scheduled after this job runs fail
as there is no longer adequate disk space.
>--Original Message--
>Is the recovery model of the destination database
changed recently? Are the
>backup jobs running successfully?
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
>"Marc" <anonymous@.discussions.microsoft.com> wrote in
message
>news:244ad01c45f4f$3aef6880$a501280a@.phx.gbl...
>I have a job that runs every evening that calls 2 stored
>procedures. The first sp inserts new records from a
>source database.table into a destination database.table
>the second job updates the records that were changed at
>the source and updates them at the destination.
>This job has run successfully without problems for 2+
>years. Now for the past two days the second step is
>blowing up the transaction log to more than 7GB (it's
>limit). Any idea what could cause this new behaviour?
>Any suggestions as to what I need to look at would be
>most appreciated.
>Thanks,
>Marc
>
>.
>
|||Hi,
Execute the below command to get the recovery model.
select databasepropertyex('dbname','recovery')
If the recovery model is FULL or BULK_LOGGED, you need to perform a
trasnaction log backup inbetween your back job.
Currently to reduce the trasnaction log file size you have backup the
transaction log shrink the file
Steps to shrink the Log file.
1. Backup the transaction log (Use command:- BACKUP Log dbname to
disk='c:\backup\dbname.tr1' (or use enterprise manager) or (if you do need
the trasaction log backup execute below command)
backup log <dbname> with truncate_only
2. Indetify the log file for shrinking:-
use <dbname>
go
sp_helpfile
Based on the name column for the transaction log file execute the dbcc
shrinkfile
3. Shrink the transaction log file.
DBCC SHRINKFILE('logical_transaction_log file name','truncateonly')
Have a look into the below article,
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default...b;en-us;272318
Thanks
Hari
MCDBA
"Marc" <anonymous@.discussions.microsoft.com> wrote in message
news:2415501c45f52$18baf880$a301280a@.phx.gbl...[vbcol=seagreen]
> Could be that the recovery model was changed from simple
> to full, but I'm not 100% as this isn't my server.
> The backups that are scheduled after this job runs fail
> as there is no longer adequate disk space.
> changed recently? Are the
> message
|||Thanks for the info Hari, but I've done all that.
There are transaction log backups every two hours.
What I'm trying to resolve is the offending job that
unexplainably filling up the log to capacity every
evening and then subsequently failing out.
Thanks,
Marc
>--Original Message--
>Hi,
>Execute the below command to get the recovery model.
>select databasepropertyex('dbname','recovery')
>If the recovery model is FULL or BULK_LOGGED, you need
to perform a
>trasnaction log backup inbetween your back job.
>Currently to reduce the trasnaction log file size you
have backup the
>transaction log shrink the file
>Steps to shrink the Log file.
>1. Backup the transaction log (Use command:- BACKUP Log
dbname to
>disk='c:\backup\dbname.tr1' (or use enterprise
manager) or (if you do need
>the trasaction log backup execute below command)
> backup log <dbname> with truncate_only
>2. Indetify the log file for shrinking:-
> use <dbname>
> go
> sp_helpfile
>Based on the name column for the transaction log file
execute the dbcc
>shrinkfile
>3. Shrink the transaction log file.
> DBCC SHRINKFILE('logical_transaction_log file
name','truncateonly')
>
>Have a look into the below article,
>INF: Shrinking the Transaction Log in SQL Server 2000
with DBCC SHRINKFILE
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;272318
>
>--
>Thanks
>Hari
>MCDBA
>"Marc" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:2415501c45f52$18baf880$a301280a@.phx.gbl...
simple[vbcol=seagreen]
stored[vbcol=seagreen]
database.table[vbcol=seagreen]
at
>
>.
>
procedures. The first sp inserts new records from a
source database.table into a destination database.table
the second job updates the records that were changed at
the source and updates them at the destination.
This job has run successfully without problems for 2+
years. Now for the past two days the second step is
blowing up the transaction log to more than 7GB (it's
limit). Any idea what could cause this new behaviour?
Any suggestions as to what I need to look at would be
most appreciated.
Thanks,
Marc
Is the recovery model of the destination database changed recently? Are the
backup jobs running successfully?
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Marc" <anonymous@.discussions.microsoft.com> wrote in message
news:244ad01c45f4f$3aef6880$a501280a@.phx.gbl...
I have a job that runs every evening that calls 2 stored
procedures. The first sp inserts new records from a
source database.table into a destination database.table
the second job updates the records that were changed at
the source and updates them at the destination.
This job has run successfully without problems for 2+
years. Now for the past two days the second step is
blowing up the transaction log to more than 7GB (it's
limit). Any idea what could cause this new behaviour?
Any suggestions as to what I need to look at would be
most appreciated.
Thanks,
Marc
|||Could be that the recovery model was changed from simple
to full, but I'm not 100% as this isn't my server.
The backups that are scheduled after this job runs fail
as there is no longer adequate disk space.
>--Original Message--
>Is the recovery model of the destination database
changed recently? Are the
>backup jobs running successfully?
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
>"Marc" <anonymous@.discussions.microsoft.com> wrote in
message
>news:244ad01c45f4f$3aef6880$a501280a@.phx.gbl...
>I have a job that runs every evening that calls 2 stored
>procedures. The first sp inserts new records from a
>source database.table into a destination database.table
>the second job updates the records that were changed at
>the source and updates them at the destination.
>This job has run successfully without problems for 2+
>years. Now for the past two days the second step is
>blowing up the transaction log to more than 7GB (it's
>limit). Any idea what could cause this new behaviour?
>Any suggestions as to what I need to look at would be
>most appreciated.
>Thanks,
>Marc
>
>.
>
|||Hi,
Execute the below command to get the recovery model.
select databasepropertyex('dbname','recovery')
If the recovery model is FULL or BULK_LOGGED, you need to perform a
trasnaction log backup inbetween your back job.
Currently to reduce the trasnaction log file size you have backup the
transaction log shrink the file
Steps to shrink the Log file.
1. Backup the transaction log (Use command:- BACKUP Log dbname to
disk='c:\backup\dbname.tr1' (or use enterprise manager) or (if you do need
the trasaction log backup execute below command)
backup log <dbname> with truncate_only
2. Indetify the log file for shrinking:-
use <dbname>
go
sp_helpfile
Based on the name column for the transaction log file execute the dbcc
shrinkfile
3. Shrink the transaction log file.
DBCC SHRINKFILE('logical_transaction_log file name','truncateonly')
Have a look into the below article,
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default...b;en-us;272318
Thanks
Hari
MCDBA
"Marc" <anonymous@.discussions.microsoft.com> wrote in message
news:2415501c45f52$18baf880$a301280a@.phx.gbl...[vbcol=seagreen]
> Could be that the recovery model was changed from simple
> to full, but I'm not 100% as this isn't my server.
> The backups that are scheduled after this job runs fail
> as there is no longer adequate disk space.
> changed recently? Are the
> message
|||Thanks for the info Hari, but I've done all that.
There are transaction log backups every two hours.
What I'm trying to resolve is the offending job that
unexplainably filling up the log to capacity every
evening and then subsequently failing out.
Thanks,
Marc
>--Original Message--
>Hi,
>Execute the below command to get the recovery model.
>select databasepropertyex('dbname','recovery')
>If the recovery model is FULL or BULK_LOGGED, you need
to perform a
>trasnaction log backup inbetween your back job.
>Currently to reduce the trasnaction log file size you
have backup the
>transaction log shrink the file
>Steps to shrink the Log file.
>1. Backup the transaction log (Use command:- BACKUP Log
dbname to
>disk='c:\backup\dbname.tr1' (or use enterprise
manager) or (if you do need
>the trasaction log backup execute below command)
> backup log <dbname> with truncate_only
>2. Indetify the log file for shrinking:-
> use <dbname>
> go
> sp_helpfile
>Based on the name column for the transaction log file
execute the dbcc
>shrinkfile
>3. Shrink the transaction log file.
> DBCC SHRINKFILE('logical_transaction_log file
name','truncateonly')
>
>Have a look into the below article,
>INF: Shrinking the Transaction Log in SQL Server 2000
with DBCC SHRINKFILE
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;272318
>
>--
>Thanks
>Hari
>MCDBA
>"Marc" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:2415501c45f52$18baf880$a301280a@.phx.gbl...
simple[vbcol=seagreen]
stored[vbcol=seagreen]
database.table[vbcol=seagreen]
at
>
>.
>
Job failure
I have a job that runs every evening that calls 2 stored
procedures. The first sp inserts new records from a
source database.table into a destination database.table
the second job updates the records that were changed at
the source and updates them at the destination.
This job has run successfully without problems for 2+
years. Now for the past two days the second step is
blowing up the transaction log to more than 7GB (it's
limit). Any idea what could cause this new behaviour?
Any suggestions as to what I need to look at would be
most appreciated.
Thanks,
MarcIs the recovery model of the destination database changed recently? Are the
backup jobs running successfully?
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Marc" <anonymous@.discussions.microsoft.com> wrote in message
news:244ad01c45f4f$3aef6880$a501280a@.phx
.gbl...
I have a job that runs every evening that calls 2 stored
procedures. The first sp inserts new records from a
source database.table into a destination database.table
the second job updates the records that were changed at
the source and updates them at the destination.
This job has run successfully without problems for 2+
years. Now for the past two days the second step is
blowing up the transaction log to more than 7GB (it's
limit). Any idea what could cause this new behaviour?
Any suggestions as to what I need to look at would be
most appreciated.
Thanks,
Marc|||Could be that the recovery model was changed from simple
to full, but I'm not 100% as this isn't my server.
The backups that are scheduled after this job runs fail
as there is no longer adequate disk space.
>--Original Message--
>Is the recovery model of the destination database
changed recently? Are the
>backup jobs running successfully?
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
>"Marc" <anonymous@.discussions.microsoft.com> wrote in
message
> news:244ad01c45f4f$3aef6880$a501280a@.phx
.gbl...
>I have a job that runs every evening that calls 2 stored
>procedures. The first sp inserts new records from a
>source database.table into a destination database.table
>the second job updates the records that were changed at
>the source and updates them at the destination.
>This job has run successfully without problems for 2+
>years. Now for the past two days the second step is
>blowing up the transaction log to more than 7GB (it's
>limit). Any idea what could cause this new behaviour?
>Any suggestions as to what I need to look at would be
>most appreciated.
>Thanks,
>Marc
>
>.
>|||Hi,
Execute the below command to get the recovery model.
select databasepropertyex('dbname','recovery')
If the recovery model is FULL or BULK_LOGGED, you need to perform a
trasnaction log backup inbetween your back job.
Currently to reduce the trasnaction log file size you have backup the
transaction log shrink the file
Steps to shrink the Log file.
1. Backup the transaction log (Use command:- BACKUP Log dbname to
disk='c:\backup\dbname.tr1' (or use enterprise manager) or (if you do need
the trasaction log backup execute below command)
backup log <dbname> with truncate_only
2. Indetify the log file for shrinking:-
use <dbname>
go
sp_helpfile
Based on the name column for the transaction log file execute the dbcc
shrinkfile
3. Shrink the transaction log file.
DBCC SHRINKFILE('logical_transaction_log file name','truncateonly')
Have a look into the below article,
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/defaul...kb;en-us;272318
Thanks
Hari
MCDBA
"Marc" <anonymous@.discussions.microsoft.com> wrote in message
news:2415501c45f52$18baf880$a301280a@.phx
.gbl...[vbcol=seagreen]
> Could be that the recovery model was changed from simple
> to full, but I'm not 100% as this isn't my server.
> The backups that are scheduled after this job runs fail
> as there is no longer adequate disk space.
>
> changed recently? Are the
> message|||Thanks for the info Hari, but I've done all that.
There are transaction log backups every two hours.
What I'm trying to resolve is the offending job that
unexplainably filling up the log to capacity every
evening and then subsequently failing out.
Thanks,
Marc
>--Original Message--
>Hi,
>Execute the below command to get the recovery model.
>select databasepropertyex('dbname','recovery')
>If the recovery model is FULL or BULK_LOGGED, you need
to perform a
>trasnaction log backup inbetween your back job.
>Currently to reduce the trasnaction log file size you
have backup the
>transaction log shrink the file
>Steps to shrink the Log file.
>1. Backup the transaction log (Use command:- BACKUP Log
dbname to
>disk='c:\backup\dbname.tr1' (or use enterprise
manager) or (if you do need
>the trasaction log backup execute below command)
> backup log <dbname> with truncate_only
>2. Indetify the log file for shrinking:-
> use <dbname>
> go
> sp_helpfile
>Based on the name column for the transaction log file
execute the dbcc
>shrinkfile
>3. Shrink the transaction log file.
> DBCC SHRINKFILE('logical_transaction_log file
name','truncateonly')
>
>Have a look into the below article,
>INF: Shrinking the Transaction Log in SQL Server 2000
with DBCC SHRINKFILE
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;272318
>
>--
>Thanks
>Hari
>MCDBA
>"Marc" <anonymous@.discussions.microsoft.com> wrote in
message
> news:2415501c45f52$18baf880$a301280a@.phx
.gbl...
simple[vbcol=seagreen]
stored[vbcol=seagreen]
database.table[vbcol=seagreen]
at[vbcol=seagreen]
>
>.
>
procedures. The first sp inserts new records from a
source database.table into a destination database.table
the second job updates the records that were changed at
the source and updates them at the destination.
This job has run successfully without problems for 2+
years. Now for the past two days the second step is
blowing up the transaction log to more than 7GB (it's
limit). Any idea what could cause this new behaviour?
Any suggestions as to what I need to look at would be
most appreciated.
Thanks,
MarcIs the recovery model of the destination database changed recently? Are the
backup jobs running successfully?
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Marc" <anonymous@.discussions.microsoft.com> wrote in message
news:244ad01c45f4f$3aef6880$a501280a@.phx
.gbl...
I have a job that runs every evening that calls 2 stored
procedures. The first sp inserts new records from a
source database.table into a destination database.table
the second job updates the records that were changed at
the source and updates them at the destination.
This job has run successfully without problems for 2+
years. Now for the past two days the second step is
blowing up the transaction log to more than 7GB (it's
limit). Any idea what could cause this new behaviour?
Any suggestions as to what I need to look at would be
most appreciated.
Thanks,
Marc|||Could be that the recovery model was changed from simple
to full, but I'm not 100% as this isn't my server.
The backups that are scheduled after this job runs fail
as there is no longer adequate disk space.
>--Original Message--
>Is the recovery model of the destination database
changed recently? Are the
>backup jobs running successfully?
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
>"Marc" <anonymous@.discussions.microsoft.com> wrote in
message
> news:244ad01c45f4f$3aef6880$a501280a@.phx
.gbl...
>I have a job that runs every evening that calls 2 stored
>procedures. The first sp inserts new records from a
>source database.table into a destination database.table
>the second job updates the records that were changed at
>the source and updates them at the destination.
>This job has run successfully without problems for 2+
>years. Now for the past two days the second step is
>blowing up the transaction log to more than 7GB (it's
>limit). Any idea what could cause this new behaviour?
>Any suggestions as to what I need to look at would be
>most appreciated.
>Thanks,
>Marc
>
>.
>|||Hi,
Execute the below command to get the recovery model.
select databasepropertyex('dbname','recovery')
If the recovery model is FULL or BULK_LOGGED, you need to perform a
trasnaction log backup inbetween your back job.
Currently to reduce the trasnaction log file size you have backup the
transaction log shrink the file
Steps to shrink the Log file.
1. Backup the transaction log (Use command:- BACKUP Log dbname to
disk='c:\backup\dbname.tr1' (or use enterprise manager) or (if you do need
the trasaction log backup execute below command)
backup log <dbname> with truncate_only
2. Indetify the log file for shrinking:-
use <dbname>
go
sp_helpfile
Based on the name column for the transaction log file execute the dbcc
shrinkfile
3. Shrink the transaction log file.
DBCC SHRINKFILE('logical_transaction_log file name','truncateonly')
Have a look into the below article,
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/defaul...kb;en-us;272318
Thanks
Hari
MCDBA
"Marc" <anonymous@.discussions.microsoft.com> wrote in message
news:2415501c45f52$18baf880$a301280a@.phx
.gbl...[vbcol=seagreen]
> Could be that the recovery model was changed from simple
> to full, but I'm not 100% as this isn't my server.
> The backups that are scheduled after this job runs fail
> as there is no longer adequate disk space.
>
> changed recently? Are the
> message|||Thanks for the info Hari, but I've done all that.
There are transaction log backups every two hours.
What I'm trying to resolve is the offending job that
unexplainably filling up the log to capacity every
evening and then subsequently failing out.
Thanks,
Marc
>--Original Message--
>Hi,
>Execute the below command to get the recovery model.
>select databasepropertyex('dbname','recovery')
>If the recovery model is FULL or BULK_LOGGED, you need
to perform a
>trasnaction log backup inbetween your back job.
>Currently to reduce the trasnaction log file size you
have backup the
>transaction log shrink the file
>Steps to shrink the Log file.
>1. Backup the transaction log (Use command:- BACKUP Log
dbname to
>disk='c:\backup\dbname.tr1' (or use enterprise
manager) or (if you do need
>the trasaction log backup execute below command)
> backup log <dbname> with truncate_only
>2. Indetify the log file for shrinking:-
> use <dbname>
> go
> sp_helpfile
>Based on the name column for the transaction log file
execute the dbcc
>shrinkfile
>3. Shrink the transaction log file.
> DBCC SHRINKFILE('logical_transaction_log file
name','truncateonly')
>
>Have a look into the below article,
>INF: Shrinking the Transaction Log in SQL Server 2000
with DBCC SHRINKFILE
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;272318
>
>--
>Thanks
>Hari
>MCDBA
>"Marc" <anonymous@.discussions.microsoft.com> wrote in
message
> news:2415501c45f52$18baf880$a301280a@.phx
.gbl...
simple[vbcol=seagreen]
stored[vbcol=seagreen]
database.table[vbcol=seagreen]
at[vbcol=seagreen]
>
>.
>
Job failure
I have a job that runs every evening that calls 2 stored
procedures. The first sp inserts new records from a
source database.table into a destination database.table
the second job updates the records that were changed at
the source and updates them at the destination.
This job has run successfully without problems for 2+
years. Now for the past two days the second step is
blowing up the transaction log to more than 7GB (it's
limit). Any idea what could cause this new behaviour?
Any suggestions as to what I need to look at would be
most appreciated.
Thanks,
MarcIs the recovery model of the destination database changed recently? Are the
backup jobs running successfully?
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Marc" <anonymous@.discussions.microsoft.com> wrote in message
news:244ad01c45f4f$3aef6880$a501280a@.phx.gbl...
I have a job that runs every evening that calls 2 stored
procedures. The first sp inserts new records from a
source database.table into a destination database.table
the second job updates the records that were changed at
the source and updates them at the destination.
This job has run successfully without problems for 2+
years. Now for the past two days the second step is
blowing up the transaction log to more than 7GB (it's
limit). Any idea what could cause this new behaviour?
Any suggestions as to what I need to look at would be
most appreciated.
Thanks,
Marc|||Could be that the recovery model was changed from simple
to full, but I'm not 100% as this isn't my server.
The backups that are scheduled after this job runs fail
as there is no longer adequate disk space.
>--Original Message--
>Is the recovery model of the destination database
changed recently? Are the
>backup jobs running successfully?
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
>"Marc" <anonymous@.discussions.microsoft.com> wrote in
message
>news:244ad01c45f4f$3aef6880$a501280a@.phx.gbl...
>I have a job that runs every evening that calls 2 stored
>procedures. The first sp inserts new records from a
>source database.table into a destination database.table
>the second job updates the records that were changed at
>the source and updates them at the destination.
>This job has run successfully without problems for 2+
>years. Now for the past two days the second step is
>blowing up the transaction log to more than 7GB (it's
>limit). Any idea what could cause this new behaviour?
>Any suggestions as to what I need to look at would be
>most appreciated.
>Thanks,
>Marc
>
>.
>|||Hi,
Execute the below command to get the recovery model.
select databasepropertyex('dbname','recovery')
If the recovery model is FULL or BULK_LOGGED, you need to perform a
trasnaction log backup inbetween your back job.
Currently to reduce the trasnaction log file size you have backup the
transaction log shrink the file
Steps to shrink the Log file.
1. Backup the transaction log (Use command:- BACKUP Log dbname to
disk='c:\backup\dbname.tr1' (or use enterprise manager) or (if you do need
the trasaction log backup execute below command)
backup log <dbname> with truncate_only
2. Indetify the log file for shrinking:-
use <dbname>
go
sp_helpfile
Based on the name column for the transaction log file execute the dbcc
shrinkfile
3. Shrink the transaction log file.
DBCC SHRINKFILE('logical_transaction_log file name','truncateonly')
Have a look into the below article,
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
Thanks
Hari
MCDBA
"Marc" <anonymous@.discussions.microsoft.com> wrote in message
news:2415501c45f52$18baf880$a301280a@.phx.gbl...
> Could be that the recovery model was changed from simple
> to full, but I'm not 100% as this isn't my server.
> The backups that are scheduled after this job runs fail
> as there is no longer adequate disk space.
> >--Original Message--
> >Is the recovery model of the destination database
> changed recently? Are the
> >backup jobs running successfully?
> >--
> >HTH,
> >Vyas, MVP (SQL Server)
> >http://vyaskn.tripod.com/
> >Is .NET important for a database professional?
> >http://vyaskn.tripod.com/poll.htm
> >
> >
> >"Marc" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:244ad01c45f4f$3aef6880$a501280a@.phx.gbl...
> >I have a job that runs every evening that calls 2 stored
> >procedures. The first sp inserts new records from a
> >source database.table into a destination database.table
> >the second job updates the records that were changed at
> >the source and updates them at the destination.
> >
> >This job has run successfully without problems for 2+
> >years. Now for the past two days the second step is
> >blowing up the transaction log to more than 7GB (it's
> >limit). Any idea what could cause this new behaviour?
> >
> >Any suggestions as to what I need to look at would be
> >most appreciated.
> >
> >Thanks,
> >Marc
> >
> >
> >.
> >|||Thanks for the info Hari, but I've done all that.
There are transaction log backups every two hours.
What I'm trying to resolve is the offending job that
unexplainably filling up the log to capacity every
evening and then subsequently failing out.
Thanks,
Marc
>--Original Message--
>Hi,
>Execute the below command to get the recovery model.
>select databasepropertyex('dbname','recovery')
>If the recovery model is FULL or BULK_LOGGED, you need
to perform a
>trasnaction log backup inbetween your back job.
>Currently to reduce the trasnaction log file size you
have backup the
>transaction log shrink the file
>Steps to shrink the Log file.
>1. Backup the transaction log (Use command:- BACKUP Log
dbname to
>disk='c:\backup\dbname.tr1' (or use enterprise
manager) or (if you do need
>the trasaction log backup execute below command)
> backup log <dbname> with truncate_only
>2. Indetify the log file for shrinking:-
> use <dbname>
> go
> sp_helpfile
>Based on the name column for the transaction log file
execute the dbcc
>shrinkfile
>3. Shrink the transaction log file.
> DBCC SHRINKFILE('logical_transaction_log file
name','truncateonly')
>
>Have a look into the below article,
>INF: Shrinking the Transaction Log in SQL Server 2000
with DBCC SHRINKFILE
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;272318
>
>--
>Thanks
>Hari
>MCDBA
>"Marc" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2415501c45f52$18baf880$a301280a@.phx.gbl...
>> Could be that the recovery model was changed from
simple
>> to full, but I'm not 100% as this isn't my server.
>> The backups that are scheduled after this job runs fail
>> as there is no longer adequate disk space.
>> >--Original Message--
>> >Is the recovery model of the destination database
>> changed recently? Are the
>> >backup jobs running successfully?
>> >--
>> >HTH,
>> >Vyas, MVP (SQL Server)
>> >http://vyaskn.tripod.com/
>> >Is .NET important for a database professional?
>> >http://vyaskn.tripod.com/poll.htm
>> >
>> >
>> >"Marc" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:244ad01c45f4f$3aef6880$a501280a@.phx.gbl...
>> >I have a job that runs every evening that calls 2
stored
>> >procedures. The first sp inserts new records from a
>> >source database.table into a destination
database.table
>> >the second job updates the records that were changed
at
>> >the source and updates them at the destination.
>> >
>> >This job has run successfully without problems for 2+
>> >years. Now for the past two days the second step is
>> >blowing up the transaction log to more than 7GB (it's
>> >limit). Any idea what could cause this new behaviour?
>> >
>> >Any suggestions as to what I need to look at would be
>> >most appreciated.
>> >
>> >Thanks,
>> >Marc
>> >
>> >
>> >.
>> >
>
>.
>
procedures. The first sp inserts new records from a
source database.table into a destination database.table
the second job updates the records that were changed at
the source and updates them at the destination.
This job has run successfully without problems for 2+
years. Now for the past two days the second step is
blowing up the transaction log to more than 7GB (it's
limit). Any idea what could cause this new behaviour?
Any suggestions as to what I need to look at would be
most appreciated.
Thanks,
MarcIs the recovery model of the destination database changed recently? Are the
backup jobs running successfully?
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Marc" <anonymous@.discussions.microsoft.com> wrote in message
news:244ad01c45f4f$3aef6880$a501280a@.phx.gbl...
I have a job that runs every evening that calls 2 stored
procedures. The first sp inserts new records from a
source database.table into a destination database.table
the second job updates the records that were changed at
the source and updates them at the destination.
This job has run successfully without problems for 2+
years. Now for the past two days the second step is
blowing up the transaction log to more than 7GB (it's
limit). Any idea what could cause this new behaviour?
Any suggestions as to what I need to look at would be
most appreciated.
Thanks,
Marc|||Could be that the recovery model was changed from simple
to full, but I'm not 100% as this isn't my server.
The backups that are scheduled after this job runs fail
as there is no longer adequate disk space.
>--Original Message--
>Is the recovery model of the destination database
changed recently? Are the
>backup jobs running successfully?
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
>"Marc" <anonymous@.discussions.microsoft.com> wrote in
message
>news:244ad01c45f4f$3aef6880$a501280a@.phx.gbl...
>I have a job that runs every evening that calls 2 stored
>procedures. The first sp inserts new records from a
>source database.table into a destination database.table
>the second job updates the records that were changed at
>the source and updates them at the destination.
>This job has run successfully without problems for 2+
>years. Now for the past two days the second step is
>blowing up the transaction log to more than 7GB (it's
>limit). Any idea what could cause this new behaviour?
>Any suggestions as to what I need to look at would be
>most appreciated.
>Thanks,
>Marc
>
>.
>|||Hi,
Execute the below command to get the recovery model.
select databasepropertyex('dbname','recovery')
If the recovery model is FULL or BULK_LOGGED, you need to perform a
trasnaction log backup inbetween your back job.
Currently to reduce the trasnaction log file size you have backup the
transaction log shrink the file
Steps to shrink the Log file.
1. Backup the transaction log (Use command:- BACKUP Log dbname to
disk='c:\backup\dbname.tr1' (or use enterprise manager) or (if you do need
the trasaction log backup execute below command)
backup log <dbname> with truncate_only
2. Indetify the log file for shrinking:-
use <dbname>
go
sp_helpfile
Based on the name column for the transaction log file execute the dbcc
shrinkfile
3. Shrink the transaction log file.
DBCC SHRINKFILE('logical_transaction_log file name','truncateonly')
Have a look into the below article,
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
Thanks
Hari
MCDBA
"Marc" <anonymous@.discussions.microsoft.com> wrote in message
news:2415501c45f52$18baf880$a301280a@.phx.gbl...
> Could be that the recovery model was changed from simple
> to full, but I'm not 100% as this isn't my server.
> The backups that are scheduled after this job runs fail
> as there is no longer adequate disk space.
> >--Original Message--
> >Is the recovery model of the destination database
> changed recently? Are the
> >backup jobs running successfully?
> >--
> >HTH,
> >Vyas, MVP (SQL Server)
> >http://vyaskn.tripod.com/
> >Is .NET important for a database professional?
> >http://vyaskn.tripod.com/poll.htm
> >
> >
> >"Marc" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:244ad01c45f4f$3aef6880$a501280a@.phx.gbl...
> >I have a job that runs every evening that calls 2 stored
> >procedures. The first sp inserts new records from a
> >source database.table into a destination database.table
> >the second job updates the records that were changed at
> >the source and updates them at the destination.
> >
> >This job has run successfully without problems for 2+
> >years. Now for the past two days the second step is
> >blowing up the transaction log to more than 7GB (it's
> >limit). Any idea what could cause this new behaviour?
> >
> >Any suggestions as to what I need to look at would be
> >most appreciated.
> >
> >Thanks,
> >Marc
> >
> >
> >.
> >|||Thanks for the info Hari, but I've done all that.
There are transaction log backups every two hours.
What I'm trying to resolve is the offending job that
unexplainably filling up the log to capacity every
evening and then subsequently failing out.
Thanks,
Marc
>--Original Message--
>Hi,
>Execute the below command to get the recovery model.
>select databasepropertyex('dbname','recovery')
>If the recovery model is FULL or BULK_LOGGED, you need
to perform a
>trasnaction log backup inbetween your back job.
>Currently to reduce the trasnaction log file size you
have backup the
>transaction log shrink the file
>Steps to shrink the Log file.
>1. Backup the transaction log (Use command:- BACKUP Log
dbname to
>disk='c:\backup\dbname.tr1' (or use enterprise
manager) or (if you do need
>the trasaction log backup execute below command)
> backup log <dbname> with truncate_only
>2. Indetify the log file for shrinking:-
> use <dbname>
> go
> sp_helpfile
>Based on the name column for the transaction log file
execute the dbcc
>shrinkfile
>3. Shrink the transaction log file.
> DBCC SHRINKFILE('logical_transaction_log file
name','truncateonly')
>
>Have a look into the below article,
>INF: Shrinking the Transaction Log in SQL Server 2000
with DBCC SHRINKFILE
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;272318
>
>--
>Thanks
>Hari
>MCDBA
>"Marc" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2415501c45f52$18baf880$a301280a@.phx.gbl...
>> Could be that the recovery model was changed from
simple
>> to full, but I'm not 100% as this isn't my server.
>> The backups that are scheduled after this job runs fail
>> as there is no longer adequate disk space.
>> >--Original Message--
>> >Is the recovery model of the destination database
>> changed recently? Are the
>> >backup jobs running successfully?
>> >--
>> >HTH,
>> >Vyas, MVP (SQL Server)
>> >http://vyaskn.tripod.com/
>> >Is .NET important for a database professional?
>> >http://vyaskn.tripod.com/poll.htm
>> >
>> >
>> >"Marc" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:244ad01c45f4f$3aef6880$a501280a@.phx.gbl...
>> >I have a job that runs every evening that calls 2
stored
>> >procedures. The first sp inserts new records from a
>> >source database.table into a destination
database.table
>> >the second job updates the records that were changed
at
>> >the source and updates them at the destination.
>> >
>> >This job has run successfully without problems for 2+
>> >years. Now for the past two days the second step is
>> >blowing up the transaction log to more than 7GB (it's
>> >limit). Any idea what could cause this new behaviour?
>> >
>> >Any suggestions as to what I need to look at would be
>> >most appreciated.
>> >
>> >Thanks,
>> >Marc
>> >
>> >
>> >.
>> >
>
>.
>
Job fails..But DTS Works
Hi All,
I have a DTS Package which transforms data from a SQL
Server table to Oracle using ODBC (Oracle 9.1.2). If I
run the DTS, it works OK. If I Schedule the DTS as a job,
I'am getting error:
Error: -2147467259 (80004005); Provider Error: 0
(0) Error string: [Microsoft][ODBC Driver Manager]
Driver's SQLAllocHandle on SQL_HANDLE_ENV failed
Error source: Microsoft OLE DB Provider for ODBC Drivers
OS : Win200
Login : Administrator
SQL : 2000
Login :sa
Please help !!!!
Thanks in Advance,
SKHave a look at the following :-
http://support.microsoft.com/?kbid=269074
--
HTH
Ryan Waight, MCDBA, MCSE
"SK" <skumar@.nycboe.net> wrote in message
news:04fe01c38f38$cb1cf900$a001280a@.phx.gbl...
> Hi All,
> I have a DTS Package which transforms data from a SQL
> Server table to Oracle using ODBC (Oracle 9.1.2). If I
> run the DTS, it works OK. If I Schedule the DTS as a job,
> I'am getting error:
> Error: -2147467259 (80004005); Provider Error: 0
> (0) Error string: [Microsoft][ODBC Driver Manager]
> Driver's SQLAllocHandle on SQL_HANDLE_ENV failed
> Error source: Microsoft OLE DB Provider for ODBC Drivers
> OS : Win200
> Login : Administrator
> SQL : 2000
> Login :sa
> Please help !!!!
> Thanks in Advance,
> SK
>|||I have recreated the DTS and Job as per the security
mentioned in the article (same username with full
control), but still the same error !!!!!
>--Original Message--
>Have a look at the following :-
>http://support.microsoft.com/?kbid=269074
>--
>HTH
>Ryan Waight, MCDBA, MCSE
>"SK" <skumar@.nycboe.net> wrote in message
>news:04fe01c38f38$cb1cf900$a001280a@.phx.gbl...
>> Hi All,
>> I have a DTS Package which transforms data from a SQL
>> Server table to Oracle using ODBC (Oracle 9.1.2). If I
>> run the DTS, it works OK. If I Schedule the DTS as a
job,
>> I'am getting error:
>> Error: -2147467259 (80004005); Provider Error: 0
>> (0) Error string: [Microsoft][ODBC Driver
Manager]
>> Driver's SQLAllocHandle on SQL_HANDLE_ENV failed
>> Error source: Microsoft OLE DB Provider for ODBC
Drivers
>> OS : Win200
>> Login : Administrator
>> SQL : 2000
>> Login :sa
>> Please help !!!!
>> Thanks in Advance,
>> SK
>
>.
>
I have a DTS Package which transforms data from a SQL
Server table to Oracle using ODBC (Oracle 9.1.2). If I
run the DTS, it works OK. If I Schedule the DTS as a job,
I'am getting error:
Error: -2147467259 (80004005); Provider Error: 0
(0) Error string: [Microsoft][ODBC Driver Manager]
Driver's SQLAllocHandle on SQL_HANDLE_ENV failed
Error source: Microsoft OLE DB Provider for ODBC Drivers
OS : Win200
Login : Administrator
SQL : 2000
Login :sa
Please help !!!!
Thanks in Advance,
SKHave a look at the following :-
http://support.microsoft.com/?kbid=269074
--
HTH
Ryan Waight, MCDBA, MCSE
"SK" <skumar@.nycboe.net> wrote in message
news:04fe01c38f38$cb1cf900$a001280a@.phx.gbl...
> Hi All,
> I have a DTS Package which transforms data from a SQL
> Server table to Oracle using ODBC (Oracle 9.1.2). If I
> run the DTS, it works OK. If I Schedule the DTS as a job,
> I'am getting error:
> Error: -2147467259 (80004005); Provider Error: 0
> (0) Error string: [Microsoft][ODBC Driver Manager]
> Driver's SQLAllocHandle on SQL_HANDLE_ENV failed
> Error source: Microsoft OLE DB Provider for ODBC Drivers
> OS : Win200
> Login : Administrator
> SQL : 2000
> Login :sa
> Please help !!!!
> Thanks in Advance,
> SK
>|||I have recreated the DTS and Job as per the security
mentioned in the article (same username with full
control), but still the same error !!!!!
>--Original Message--
>Have a look at the following :-
>http://support.microsoft.com/?kbid=269074
>--
>HTH
>Ryan Waight, MCDBA, MCSE
>"SK" <skumar@.nycboe.net> wrote in message
>news:04fe01c38f38$cb1cf900$a001280a@.phx.gbl...
>> Hi All,
>> I have a DTS Package which transforms data from a SQL
>> Server table to Oracle using ODBC (Oracle 9.1.2). If I
>> run the DTS, it works OK. If I Schedule the DTS as a
job,
>> I'am getting error:
>> Error: -2147467259 (80004005); Provider Error: 0
>> (0) Error string: [Microsoft][ODBC Driver
Manager]
>> Driver's SQLAllocHandle on SQL_HANDLE_ENV failed
>> Error source: Microsoft OLE DB Provider for ODBC
Drivers
>> OS : Win200
>> Login : Administrator
>> SQL : 2000
>> Login :sa
>> Please help !!!!
>> Thanks in Advance,
>> SK
>
>.
>
Job Fails when scheduled
Another user who has sa privledges created a job to run an update on a table
.
I modifed it for him. Then I shceduled it. I or he can run it manaully and i
t
works fine, but when scheduled, it bombs everytime. HELPHope this article might be of some help
http://support.microsoft.com/defaul...;283811&sd=tech
"DBA" wrote:
> Another user who has sa privledges created a job to run an update on a tab
le.
> I modifed it for him. Then I shceduled it. I or he can run it manaully and
it
> works fine, but when scheduled, it bombs everytime. HELP
.
I modifed it for him. Then I shceduled it. I or he can run it manaully and i
t
works fine, but when scheduled, it bombs everytime. HELPHope this article might be of some help
http://support.microsoft.com/defaul...;283811&sd=tech
"DBA" wrote:
> Another user who has sa privledges created a job to run an update on a tab
le.
> I modifed it for him. Then I shceduled it. I or he can run it manaully and
it
> works fine, but when scheduled, it bombs everytime. HELP
Job fails when run as SQL Agent
Hi,
I have developed a package which selects records from SQL 2000 table from a server on network and writes it into an excel sheet.
The package runs fine from IDE. I am using "DoNotSaveSensitive" option. I have deployed this package on my local server which is SQL 2005 with SP2.
and scheduled through a job to run as SQLAgent. But I am getting the following error:
Message
Executed as user: STEL540\SYSTEM. ...on 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 3:22:20 p.m.
Error: 2007-08-20 15:22:22.00
Code: 0xC0202009
Source: LastMonthCallTagging Connection manager "HAMDB02-GenCallTagging"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred.
Error code: 0x80004005. An OLE DB record is available.
Source: "Microsoft SQL Native Client" Hresult: 0x80004005
Description: "Communication link failure". An OLE DB record is available.
Source: "Microsoft SQL Native Client" Hresult: 0x80004005
Description: "TCP Provider: An existing connection was forcibly closed by the remote host. ". An OLE DB record is available.
Source: "Microsoft SQL Native Client" Hresult: 0x80004005
Description: "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.".
End Error Error: 2007-08-20 15:22:22.02 Code: 0xC020... The package execution fa... The step failed.
Can someone please guide on this error?
Thanks,
How are you connecting to HAMDB02-GenCallTagging? Windows or sql server authentication?
|||When you are running packages it is all about context, when you run it manually it runs in the context of your permissions, as a Job it runs in the context of SQL Server Agent. Try the links below for details the Agent and the owner of the package needs admin level permissions.
http://support.microsoft.com/kb/918760/
http://support.microsoft.com/kb/938086/en-US
Job fails when run as SQL Agent
Hi,
I have developed a package which selects records from SQL 2000 table from a server on network and writes it into an excel sheet.
The package runs fine from IDE. I am using "DoNotSaveSensitive" option. I have deployed this package on my local server which is SQL 2005 with SP2.
and scheduled through a job to run as SQLAgent. But I am getting the following error:
Message
Executed as user: STEL540\SYSTEM. ...on 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 3:22:20 p.m.
Error: 2007-08-20 15:22:22.00
Code: 0xC0202009
Source: LastMonthCallTagging Connection manager "HAMDB02-GenCallTagging"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred.
Error code: 0x80004005. An OLE DB record is available.
Source: "Microsoft SQL Native Client" Hresult: 0x80004005
Description: "Communication link failure". An OLE DB record is available.
Source: "Microsoft SQL Native Client" Hresult: 0x80004005
Description: "TCP Provider: An existing connection was forcibly closed by the remote host. ". An OLE DB record is available.
Source: "Microsoft SQL Native Client" Hresult: 0x80004005
Description: "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.".
End Error Error: 2007-08-20 15:22:22.02 Code: 0xC020... The package execution fa... The step failed.
Can someone please guide on this error?
Thanks,
How are you connecting to HAMDB02-GenCallTagging? Windows or sql server authentication?
|||When you are running packages it is all about context, when you run it manually it runs in the context of your permissions, as a Job it runs in the context of SQL Server Agent. Try the links below for details the Agent and the owner of the package needs admin level permissions.
http://support.microsoft.com/kb/918760/
http://support.microsoft.com/kb/938086/en-US
Job fails but package will run from MSDB
I created a package for importing data from a flat file to an existing table in SQL Server 2005 using Management Studio and the administrator account working directly on the server through a remote login. I saved the package to MSDB.
I can see the package listed when I connect to Integration Services through Management Studio and expand the MSDB folder. In that location I have the option to run the package and it will run successfully. However, I cannot edit the package.
When I try to create a scheduled job using Sql Server Agent, where the first step is to run the package using the administrator login, the execution fails, and the job history will only tell me that the step failed. The Sql Server Agent log shows that the job started and completed. No error is shown. The Administrative Tools Event Viewer Application log shows only that the job started and failed. Security log shows no errors. System log shows no errors.
What am I doing wrong? How can I edit and schedule this package?
You cannot edit packages through SSMS, that is by design. BIDS is the design environment for SSIS, in fact for all the BI stuff.
When you schedule the package you are potentially changing the location and context compared to a right-click execute. It runs under the SQL Server agent service account, although may be using a SQL Agent Proxy, check the job. It also runs on the SQL Server machine itself.
The best thing to do its supply a step log file, or log to table. This needs to be set at a step level, and normally gives more than the job history. I would also change to CmdExec and DTEXEC. See link for an example of why-
http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html
|||I don't have a proxy set up - do I need to do that? Also, what are the steps to set up the log file you mentioned?|||If your job is owned by a sysadmin, and you don't have any special security requirements, then you do not need a proxy. It will run under the context of the SQL Server Agent service account.
To set the job step logging options, open the job, and then open the step. Select the Advanced tab, and set some options. I personally find output log file is the simplest to use and manage.
|||If part of the job is to create a destination table in the database, that part of the job will work correctly, but the data will not be imported from the source text file. I think the problem is that the SQL Server Agent doesn't have permission or security set somewhere so that the agent can read the source file directory. How can I fix this issue?|||the issue was solved by changing encription option and changing the file reference from a drive-specific source (X:\ImportData\FileName to \\HostServerName\Directory\FileName.
Job fails but package will run from MSDB
I created a package for importing data from a flat file to an existing table in SQL Server 2005 using Management Studio and the administrator account working directly on the server through a remote login. I saved the package to MSDB.
I can see the package listed when I connect to Integration Services through Management Studio and expand the MSDB folder. In that location I have the option to run the package and it will run successfully. However, I cannot edit the package.
When I try to create a scheduled job using Sql Server Agent, where the first step is to run the package using the administrator login, the execution fails, and the job history will only tell me that the step failed. The Sql Server Agent log shows that the job started and completed. No error is shown. The Administrative Tools Event Viewer Application log shows only that the job started and failed. Security log shows no errors. System log shows no errors.
What am I doing wrong? How can I edit and schedule this package?
You cannot edit packages through SSMS, that is by design. BIDS is the design environment for SSIS, in fact for all the BI stuff.
When you schedule the package you are potentially changing the location and context compared to a right-click execute. It runs under the SQL Server agent service account, although may be using a SQL Agent Proxy, check the job. It also runs on the SQL Server machine itself.
The best thing to do its supply a step log file, or log to table. This needs to be set at a step level, and normally gives more than the job history. I would also change to CmdExec and DTEXEC. See link for an example of why-
http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html
|||I don't have a proxy set up - do I need to do that? Also, what are the steps to set up the log file you mentioned?|||
If your job is owned by a sysadmin, and you don't have any special security requirements, then you do not need a proxy. It will run under the context of the SQL Server Agent service account.
To set the job step logging options, open the job, and then open the step. Select the Advanced tab, and set some options. I personally find output log file is the simplest to use and manage.
|||If part of the job is to create a destination table in the database, that part of the job will work correctly, but the data will not be imported from the source text file. I think the problem is that the SQL Server Agent doesn't have permission or security set somewhere so that the agent can read the source file directory. How can I fix this issue?|||the issue was solved by changing encription option and changing the file reference from a drive-specific source (X:\ImportData\FileName to \\HostServerName\Directory\FileName.
job fails
Hi all,
Sql server 7
I have created a Web assistant job which select
and displays data from a table (very small amt of data)
on to the webpage. the job is schedule to run
every 1 minute. the job is not running, in job view
history its gives the error msg
"could not open the output file" 16821 Error no.
pls suggest
Awaiting for ur reply
TIA
AdilYou need to make sure that the service account (it HAS to have an account, not System Account) for SQLAgent has Create/Write permissions on the destination.
Sql server 7
I have created a Web assistant job which select
and displays data from a table (very small amt of data)
on to the webpage. the job is schedule to run
every 1 minute. the job is not running, in job view
history its gives the error msg
"could not open the output file" 16821 Error no.
pls suggest
Awaiting for ur reply
TIA
AdilYou need to make sure that the service account (it HAS to have an account, not System Account) for SQLAgent has Create/Write permissions on the destination.
Subscribe to:
Posts (Atom)