SQL tool for MSDE: fails

Showing posts with label fails. Show all posts

Sunday, March 25, 2012

alerts

How can I set an alert to email me when a Login fails? I have been getting
several "Login failed for user '(null)'. Reason : Not associated witha
trusted SQL Server Connection." I need to find out what web site is doing
this.You can create alerts for specific error numbers - this one
I think is error number 18452
I don't think that will help you find out what web site is
failing causing the error. You'd probably have to monitor at
the network level.
-Sue
On Thu, 25 Aug 2005 11:01:06 -0700, "Rich"
<Rich@.discussions.microsoft.com> wrote:
>How can I set an alert to email me when a Login fails? I have been getting
>several "Login failed for user '(null)'. Reason : Not associated witha
>trusted SQL Server Connection." I need to find out what web site is doing
>this.

alerts

How can I set an alert to email me when a Login fails? I have been getting
several "Login failed for user '(null)'. Reason : Not associated witha
trusted SQL Server Connection." I need to find out what web site is doing
this.
You can create alerts for specific error numbers - this one
I think is error number 18452
I don't think that will help you find out what web site is
failing causing the error. You'd probably have to monitor at
the network level.
-Sue
On Thu, 25 Aug 2005 11:01:06 -0700, "Rich"
<Rich@.discussions.microsoft.com> wrote:

>How can I set an alert to email me when a Login fails? I have been getting
>several "Login failed for user '(null)'. Reason : Not associated witha
>trusted SQL Server Connection." I need to find out what web site is doing
>this.
sql

alerts

How can I set an alert to email me when a Login fails? I have been getting
several "Login failed for user '(null)'. Reason : Not associated witha
trusted SQL Server Connection." I need to find out what web site is doing
this.You can create alerts for specific error numbers - this one
I think is error number 18452
I don't think that will help you find out what web site is
failing causing the error. You'd probably have to monitor at
the network level.
-Sue
On Thu, 25 Aug 2005 11:01:06 -0700, "Rich"
<Rich@.discussions.microsoft.com> wrote:

>How can I set an alert to email me when a Login fails? I have been getting
>several "Login failed for user '(null)'. Reason : Not associated witha
>trusted SQL Server Connection." I need to find out what web site is doing
>this.

Thursday, March 22, 2012

Alert when backup fails

I am using SQL Server 2000, and I would like to configure an email alert to
occur when any backup job on the server fails. I have SQL Mail working,but
I don't see any obvious option in Alerts that would trigger a notification
when a backup job fails. I did a google search on the subject in this
newsgroup, and found several people asking this question, but none were
answered. Is there a way to configure an alert like this? Surely there
must be a way to accomplish this."J.Marsch" <jeremy@.ctcdeveloper.com> wrote in message
news:uH%23BFLQlDHA.1656@.tk2msftngp13.phx.gbl...
> I am using SQL Server 2000, and I would like to configure an email alert
to
> occur when any backup job on the server fails. I have SQL Mail
working,but
> I don't see any obvious option in Alerts that would trigger a notification
> when a backup job fails. I did a google search on the subject in this
> newsgroup, and found several people asking this question, but none were
> answered. Is there a way to configure an alert like this? Surely there
> must be a way to accomplish this.
>
The easiest way is probably to have scheduled jobs for the backups, then
send a notification if the job fails.
Simon|||Hello,
Thanks for Simon's help. I agree with him. Also, I'm including some
supplemental information below:
SQL Server Agent Mail (the SQLAgentMail service) can use its own domain
account and mail profile, rather than the one set up for SQL Mail. With SQL
Server, you can configure SQL Agent Mail to send e-mail messages when:
1. An alert is triggered.
Alerts can be configured to send e-mail notification of specific events
that occur without implementing SQL Mail. For example, alerts can be
configured to notify an operator of a particular database event that may
need immediate action.
2. A scheduled task (such as a database backup or replication event)
succeeds or fails.
E-mail messages can be sent to a list of recipients informing them of the
status of scheduled jobs for possible user action. You can expand the
capabilities of jobs to include sending a result set by e-mail to a list of
recipients. For example, a monthly inventory report could send SQL Agent
Mail notification to the designated operators and the result set to the
purchasing manager and supplier.
In this case, we can set the "E-mail operator" option in Notifications Tab
of Job Properties to send a notification when a database backup job failed.
E-mail operator specifies that an operator is notified by e-mail when a
Microsoft? SQL Server? event completes. Select the name of the operator to
notify by e-mail, or click the browse (...) button to add a new operator or
edit the properties of an existing operator. Also, select the completion
status about which the operator will be notified.
For additional information regarding this issue, please refer to the
following articles on the SQL Server Books Online.
Topic: "SQL Mail"
Topic: "Job Properties (Notifications Tab)"
Please feel free to let me know if this solves your problem or if you would
like further assistance.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

alert in clustering

i have created sql alerts in sql server cluster

the alert fails trigger eventhough eventlog and sql server agents are working it reflects as if no events occur

Please verify that the Alerts are [enabled].|||

Arnie Rowland wrote:
Please verify that the Alerts are [enabled].

it is enabled

Tuesday, March 20, 2012

Alert for any Job that Fails

This may be a silly question, but I am having trouble setting up an alert to
email an operator about any job that may fail for any reason. Is there an
easy way to set this up?
ThanksHi,
Take a look at this:
http://support.microsoft.com/defaul...kb;EN-US;315886
hth
DeeJay Puar
"Cooper" wrote:

> This may be a silly question, but I am having trouble setting up an alert
to
> email an operator about any job that may fail for any reason. Is there an
> easy way to set this up?
> Thanks|||Oh Ok.
I don't think there is a easy way to do that. I am using Reporting Services
as well and there is nothing in reporting services either.
However, at the risk of your jobs failing, you could query the 'sysjobs'
table in the msdb database and update the notifications (notify_*_* columns)
.
This should do the trick.
I have not tested this yet...lack of time, but give it a shot and post your
findings.
hth
DeeJay Puar
"Cooper" wrote:
[vbcol=seagreen]
> I'm sorry I should have stated a bit more than I did. I can make alerts ju
st
> fine. I just can't figure out how to specifically set one up to catch any
job
> that fails for any reason. I have many other alerts that are working and
> emailing me just fine. This particualr one I want to email me if any job
> fails for any reason. I know this can be done inside the job itself. But w
ith
> reporting services subscriptions are made that make jobs and I do not want
to
> check through all of them to set up the alert. Instead I want to make an
> alert that will email when any job fails for any reason without me having
to
> denote in the job itself to email me upon failure.
> "DeeJay Puar" wrote:
>sql

Alert for any Job that Fails

This may be a silly question, but I am having trouble setting up an alert to
email an operator about any job that may fail for any reason. Is there an
easy way to set this up?
Thanks
Hi,
Take a look at this:
http://support.microsoft.com/default...b;EN-US;315886
hth
DeeJay Puar
"Cooper" wrote:

> This may be a silly question, but I am having trouble setting up an alert to
> email an operator about any job that may fail for any reason. Is there an
> easy way to set this up?
> Thanks
|||Oh Ok.
I don't think there is a easy way to do that. I am using Reporting Services
as well and there is nothing in reporting services either.
However, at the risk of your jobs failing, you could query the 'sysjobs'
table in the msdb database and update the notifications (notify_*_* columns).
This should do the trick.
I have not tested this yet...lack of time, but give it a shot and post your
findings.
hth
DeeJay Puar
"Cooper" wrote:
[vbcol=seagreen]
> I'm sorry I should have stated a bit more than I did. I can make alerts just
> fine. I just can't figure out how to specifically set one up to catch any job
> that fails for any reason. I have many other alerts that are working and
> emailing me just fine. This particualr one I want to email me if any job
> fails for any reason. I know this can be done inside the job itself. But with
> reporting services subscriptions are made that make jobs and I do not want to
> check through all of them to set up the alert. Instead I want to make an
> alert that will email when any job fails for any reason without me having to
> denote in the job itself to email me upon failure.
> "DeeJay Puar" wrote:

Alert for a job step

hi.
I have a job with 5 steps.
I want a custom alert after the first step is executed reagrdless of whether it succeeds or fails on execution. I want to do a net send of the alert only after step1 is completed. How do i do this?
Thank you very much for the help.You can create step 2 as the send alert and go to that step on both success and failure.

You can also call your step step 2
Create step 1 as a failure alert step
Create step 3 as a success alert step
Start the job at step 2 and goto step 1 on failure, step 3 on success.
Step 1 and step 3 can call an SP to send the alert.|||thank you for the reply. Appreciate your responses and help in this forum.
Yes, I have made a custom error message/code and an custom alert for that error code.
And all i do is raiserror with the custom error code passed as parameter in the job step. this is captured by the alert and the net send is generated. By the way how do i send an alert from an SP?:confused:

Alert fails to notify Operator (SQL Server 2005)

I am trying to set up a new alert which will notify me when a particular
error is raised. I have configured the SQL Server Agent Alert System to use
Database Mail, and confirmed that this works with Job notifications (I have
successfully notified myself by email on completion of a job).
I have added a new user-defined error with sp_addmessage and have created an
alert to notify me when this error is raised. I have then generated the error
with the raiserror statement. The error message is returned in the query
result window, but I do not get the email.Hi Jon
"Jon Ley" wrote:
> I am trying to set up a new alert which will notify me when a particular
> error is raised. I have configured the SQL Server Agent Alert System to use
> Database Mail, and confirmed that this works with Job notifications (I have
> successfully notified myself by email on completion of a job).
> I have added a new user-defined error with sp_addmessage and have created an
> alert to notify me when this error is raised. I have then generated the error
> with the raiserror statement. The error message is returned in the query
> result window, but I do not get the email.
Is there anything in the SQL Server error log?
If you have email blocking as part of the antivirus suite you may have
exceptions logged there.
John|||How did you define the alert notification? By error number?
--
TheSQLGuru
President
Indicium Resources, Inc.
"Jon Ley" <JonLey@.discussions.microsoft.com> wrote in message
news:29B92338-9259-489C-BFBA-C3D75B72B901@.microsoft.com...
>I am trying to set up a new alert which will notify me when a particular
> error is raised. I have configured the SQL Server Agent Alert System to
> use
> Database Mail, and confirmed that this works with Job notifications (I
> have
> successfully notified myself by email on completion of a job).
> I have added a new user-defined error with sp_addmessage and have created
> an
> alert to notify me when this error is raised. I have then generated the
> error
> with the raiserror statement. The error message is returned in the query
> result window, but I do not get the email.|||On May 19, 1:18 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> How did you define the alert notification? By error number?
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Jon Ley" <Jon...@.discussions.microsoft.com> wrote in message
> news:29B92338-9259-489C-BFBA-C3D75B72B901@.microsoft.com...
>
> >I am trying to set up a new alert which will notify me when a particular
> > error is raised. I have configured the SQL Server Agent Alert System to
> > use
> > Database Mail, and confirmed that this works with Job notifications (I
> > have
> > successfully notified myself by email on completion of a job).
> > I have added a new user-defined error with sp_addmessage and have created
> > an
> > alert to notify me when this error is raised. I have then generated the
> > error
> > with the raiserror statement. The error message is returned in the query
> > result window, but I do not get the email.- Hide quoted text -
> - Show quoted text -
Are you on service pak 2? There are problems with database mail that
are corrected with service SP2.
Worth a shot anyway.
Kristina|||John,
Thanks for your response. There does not appear to be anything in the error
log.
Jon.
"John Bell" wrote:
> Hi Jon
> "Jon Ley" wrote:
> > I am trying to set up a new alert which will notify me when a particular
> > error is raised. I have configured the SQL Server Agent Alert System to use
> > Database Mail, and confirmed that this works with Job notifications (I have
> > successfully notified myself by email on completion of a job).
> >
> > I have added a new user-defined error with sp_addmessage and have created an
> > alert to notify me when this error is raised. I have then generated the error
> > with the raiserror statement. The error message is returned in the query
> > result window, but I do not get the email.
> Is there anything in the SQL Server error log?
> If you have email blocking as part of the antivirus suite you may have
> exceptions logged there.
> John|||TheSQLGuru: Yes, I defined the alert notification by error number (50001)
Kristina: I have just applied sp2 and retried. Still not working.
Regards,
Jon.
"Kristina" wrote:
> On May 19, 1:18 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> > How did you define the alert notification? By error number?
> >
> > --
> > TheSQLGuru
> > President
> > Indicium Resources, Inc.
> >
> > "Jon Ley" <Jon...@.discussions.microsoft.com> wrote in message
> >
> > news:29B92338-9259-489C-BFBA-C3D75B72B901@.microsoft.com...
> >
> >
> >
> > >I am trying to set up a new alert which will notify me when a particular
> > > error is raised. I have configured the SQL Server Agent Alert System to
> > > use
> > > Database Mail, and confirmed that this works with Job notifications (I
> > > have
> > > successfully notified myself by email on completion of a job).
> >
> > > I have added a new user-defined error with sp_addmessage and have created
> > > an
> > > alert to notify me when this error is raised. I have then generated the
> > > error
> > > with the raiserror statement. The error message is returned in the query
> > > result window, but I do not get the email.- Hide quoted text -
> >
> > - Show quoted text -
> Are you on service pak 2? There are problems with database mail that
> are corrected with service SP2.
> Worth a shot anyway.
> Kristina
>|||Hi Jon
"Jon Ley" wrote:
> John,
> Thanks for your response. There does not appear to be anything in the error
> log.
> Jon.
>
Under the Alert Properties, there is a history dialog which should show you
if the alert has been triggered. If this is not updated when you cause the
error then you are not satisfying the criteria described in the remarks
section of http://msdn2.microsoft.com/en-us/library/ms189531.aspx. Check that
the errors appears in the windows event log.
John|||Thanks, John, I have got it working now using the WITH LOG option on the
RAISERROR statement. This appears to be a change from SQL Server 2000 - I had
exactly the same alert configured with a severity level of 10 and without it
being logged, and it would quite happily notify me by email when this error
was raised.
Regards,
Jon Ley.
"John Bell" wrote:
> Hi Jon
> "Jon Ley" wrote:
> > John,
> >
> > Thanks for your response. There does not appear to be anything in the error
> > log.
> >
> > Jon.
> >
> Under the Alert Properties, there is a history dialog which should show you
> if the alert has been triggered. If this is not updated when you cause the
> error then you are not satisfying the criteria described in the remarks
> section of http://msdn2.microsoft.com/en-us/library/ms189531.aspx. Check that
> the errors appears in the windows event log.
> John|||Did you configure the alert to always log to windows eventlog? You can check this in sys.messages.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jon Ley" <JonLey@.discussions.microsoft.com> wrote in message
news:54D9247D-1915-41DB-AA98-09D1E1E7B4DF@.microsoft.com...
> Thanks, John, I have got it working now using the WITH LOG option on the
> RAISERROR statement. This appears to be a change from SQL Server 2000 - I had
> exactly the same alert configured with a severity level of 10 and without it
> being logged, and it would quite happily notify me by email when this error
> was raised.
> Regards,
> Jon Ley.
> "John Bell" wrote:
>> Hi Jon
>> "Jon Ley" wrote:
>> > John,
>> >
>> > Thanks for your response. There does not appear to be anything in the error
>> > log.
>> >
>> > Jon.
>> >
>> Under the Alert Properties, there is a history dialog which should show you
>> if the alert has been triggered. If this is not updated when you cause the
>> error then you are not satisfying the criteria described in the remarks
>> section of http://msdn2.microsoft.com/en-us/library/ms189531.aspx. Check that
>> the errors appears in the windows event log.
>> Johnsql

Saturday, February 25, 2012

Agent Service Fails to Start

For some time now, I've been unable to start SQL Server
Agent service. I can run agent in cmd window via:
sqlagent.exe -c -v. The error on failed startup is
something like, sqlagent failed to start in a timely
manner.Have you tried starting the agent from Service Manager? If so, what error
message do you get? What user account does the Agent run with? Does the
account belong to a Win2K domain or the local machine?
The answers to these questions will help to diagnose the problem.
"Clark" <anonymous@.discussions.microsoft.com> wrote in message
news:4a2e01c3ab7d$eebde570$a601280a@.phx.gbl...
> For some time now, I've been unable to start SQL Server
> Agent service. I can run agent in cmd window via:
> sqlagent.exe -c -v. The error on failed startup is
> something like, sqlagent failed to start in a timely
> manner.|||Oh yes, I've tried that.
1. With Service Manager, it fails to start with something
like, sqlagent failed to start in a timely manner.
2. Agent uses the sama account as SQL Server service.
I plan to dink with this on Monday morning.
Question: Can the time to load a service be extended,
for example, to load a service on a very busy server?
>--Original Message--
>Have you tried starting the agent from Service Manager?
If so, what error
>message do you get? What user account does the Agent run
with? Does the
>account belong to a Win2K domain or the local machine?
>The answers to these questions will help to diagnose the
problem.
>
>"Clark" <anonymous@.discussions.microsoft.com> wrote in
message
>news:4a2e01c3ab7d$eebde570$a601280a@.phx.gbl...
>> For some time now, I've been unable to start SQL Server
>> Agent service. I can run agent in cmd window via:
>> sqlagent.exe -c -v. The error on failed startup is
>> something like, sqlagent failed to start in a timely
>> manner.
>
>.
>|||I am having a similar problem too. I am trying to start the service from
Enterprise Manager and get the following error:
A error 5 - (Access is denied) occured while performing the service opeation
on the SQL ServerAgent service
I tried changeing the account info to the SA login and password but to no
avail. Is it possibel that the service is needs the machine domain Admin
account info?
John.
"Clark" <anonymous@.discussions.microsoft.com> wrote in message
news:505301c3ac4e$be0c0070$a601280a@.phx.gbl...
> Oh yes, I've tried that.
> 1. With Service Manager, it fails to start with something
> like, sqlagent failed to start in a timely manner.
> 2. Agent uses the sama account as SQL Server service.
> I plan to dink with this on Monday morning.
> Question: Can the time to load a service be extended,
> for example, to load a service on a very busy server?
>
> >--Original Message--
> >Have you tried starting the agent from Service Manager?
> If so, what error
> >message do you get? What user account does the Agent run
> with? Does the
> >account belong to a Win2K domain or the local machine?
> >
> >The answers to these questions will help to diagnose the
> problem.
> >
> >
> >"Clark" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:4a2e01c3ab7d$eebde570$a601280a@.phx.gbl...
> >> For some time now, I've been unable to start SQL Server
> >> Agent service. I can run agent in cmd window via:
> >> sqlagent.exe -c -v. The error on failed startup is
> >> something like, sqlagent failed to start in a timely
> >> manner.
> >
> >
> >.
> >|||Hi John,
Sa login is for SQL server authentication, which is different from the account used for SQL server agent service. To run sql server agent, one must typically have a domain user account (as it is used for sql mail, replication, backup etc). Also this domain name should have administrative privs on the machine where sql server instance is running.
Sometimes I guess rebuilding registry can solve the problem. Use "regrebld" in the cmd line prompt. I haven't used this utility myself, so inquire with others before using it
Thank
GYK|||Thanks for you help.
"GYK" <anonymous@.discussions.microsoft.com> wrote in message
news:CFD08193-68CB-48CA-A931-04BBA0EC21A2@.microsoft.com...
> Hi John,
> Sa login is for SQL server authentication, which is different from the
account used for SQL server agent service. To run sql server agent, one must
typically have a domain user account (as it is used for sql mail,
replication, backup etc). Also this domain name should have administrative
privs on the machine where sql server instance is running.
> Sometimes I guess rebuilding registry can solve the problem. Use
"regrebld" in the cmd line prompt. I haven't used this utility myself, so
inquire with others before using it.
> Thanks
> GYK

Agent Jobs Failure

I have setup the sending of an email when a job fails, but the Messages
portion of the email only includes the step that errored and a generic
message, how can I get the email to also contain the actual error message?
--
HaroldsHi Harolds,
Thank you for your post.
Based on my scope, you can not include the detail error message directly.
Please build up a custom stored procedure to send the email.
Here is an article for your reference:
http://www.orcsweb.com/articles/sendmailsql.aspx
http://support.microsoft.com/default.aspx?scid=kb;en-us;312839&sd=tech
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks that did the trick.
--
Harolds
"Wei Lu" wrote:
> Hi Harolds,
> Thank you for your post.
> Based on my scope, you can not include the detail error message directly.
> Please build up a custom stored procedure to send the email.
> Here is an article for your reference:
> http://www.orcsweb.com/articles/sendmailsql.aspx
> http://support.microsoft.com/default.aspx?scid=kb;en-us;312839&sd=tech
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hi Harolds,
Thank you for the update. Glad to hear the information is helpful.
If you have any questions or concerns, please feel free to let me know.
Have a nice day!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Agent Jobs Failure

I have setup the sending of an email when a job fails, but the Messages
portion of the email only includes the step that errored and a generic
message, how can I get the email to also contain the actual error message?
--
HaroldsHi Harolds,
Thank you for your post.
Based on my scope, you can not include the detail error message directly.
Please build up a custom stored procedure to send the email.
Here is an article for your reference:
http://www.orcsweb.com/articles/sendmailsql.aspx
http://support.microsoft.com/defaul...;312839&sd=tech
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks that did the trick.
--
Harolds
"Wei Lu" wrote:

> Hi Harolds,
> Thank you for your post.
> Based on my scope, you can not include the detail error message directly.
> Please build up a custom stored procedure to send the email.
> Here is an article for your reference:
> http://www.orcsweb.com/articles/sendmailsql.aspx
> http://support.microsoft.com/defaul...;312839&sd=tech
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ========================================
==========
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
==========
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>|||Hi Harolds,
Thank you for the update. Glad to hear the information is helpful.
If you have any questions or concerns, please feel free to let me know.
Have a nice day!
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.

Friday, February 24, 2012

Agent job fails, why?

SQL Server 2005 RTM:
I have created a job to copy a database from another server, but the job
fails when I try to run it.
This is the only information I can see in the Job History:
Date,Source,Severity,Step ID,Server,Job Name,Step
Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator
Emailed,Operator Net sent,Operator Paged,Retries Attempted
11/02/2005
13:35:51,CDW_LION_SQLSERVER2005_DEV01_0,Error,0,DE V01,CDW_LION_SQLSERVER2005_DEV01_0,(Job
outcome),,The job failed. The Job was invoked by User SAFARI\olavt. The
last step to run was step 1
(CDW_LION_SQLSERVER2005_DEV01_0_Step).,00:00:31,0, 0,,,,0
11/02/2005
13:35:51,CDW_LION_SQLSERVER2005_DEV01_0,Error,1,DE V01,CDW_LION_SQLSERVER2005_DEV01_0,CDW_LION_SQLSER VER2005_DEV01_0_Step,,Executed
as user: SAFARI\Administrator. The package execution failed. The step
failed.,00:00:31,0,0,,,,0
This doesn't help me to understand why the job fails.
How can I find the "Real" error log and get a decent clue on why the job
failed?
Olav
Specify an output file for the job step and see if you get any root error messages there.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Olav Tollefsen" <x@.y.com> wrote in message news:OAZp3hM4FHA.3636@.TK2MSFTNGP09.phx.gbl...
> SQL Server 2005 RTM:
> I have created a job to copy a database from another server, but the job fails when I try to run
> it.
> This is the only information I can see in the Job History:
> Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql
> Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
> 11/02/2005
> 13:35:51,CDW_LION_SQLSERVER2005_DEV01_0,Error,0,DE V01,CDW_LION_SQLSERVER2005_DEV01_0,(Job
> outcome),,The job failed. The Job was invoked by User SAFARI\olavt. The last step to run was
> step 1 (CDW_LION_SQLSERVER2005_DEV01_0_Step).,00:00:31,0, 0,,,,0
> 11/02/2005
> 13:35:51,CDW_LION_SQLSERVER2005_DEV01_0,Error,1,DE V01,CDW_LION_SQLSERVER2005_DEV01_0,CDW_LION_SQLSER VER2005_DEV01_0_Step,,Executed
> as user: SAFARI\Administrator. The package execution failed. The step failed.,00:00:31,0,0,,,,0
> This doesn't help me to understand why the job fails.
> How can I find the "Real" error log and get a decent clue on why the job failed?
> Olav
>
|||I have created a new SSIS package using the Import / Export Wizard and
theese are the errors I get when I run it:
...
Information: 0x40043006 at {6C86B366-5C26-40A1-B214-86D284BE277B},
DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at {6C86B366-5C26-40A1-B214-86D284BE277B},
DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x4004300C at {6C86B366-5C26-40A1-B214-86D284BE277B},
DTS.Pipeline: Execute phase is beginning.
Error: 0xC0202009 at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB
Destination [2]: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client"
Hresult: 0x80004005 Description: "Ad hoc updates to system catalogs are not
allowed.".
Error: 0xC0209029 at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB
Destination [2]: The "input "OLE DB Destination Input" (29)" failed because
error code 0xC020907B occurred, and the error row disposition on "input "OLE
DB Destination Input" (29)" specifies failure on error. An error occurred on
the specified object of the specified component.
Error: 0xC0047022 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline:
The ProcessInput method on component "OLE DB Destination" (2) failed with
error code 0xC0209029. The identified component returned an error from the
ProcessInput method. The error is specific to the component, but the error
is fatal and will cause the Data Flow task to stop running.
Error: 0xC0047021 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline:
Thread "WorkThread0" has exited with error code 0xC0209029.
Information: 0x40043008 at {6C86B366-5C26-40A1-B214-86D284BE277B},
DTS.Pipeline: Post Execute phase is beginning.
Information: 0x402090DF at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB
Destination [2]: The final commit for the data insertion has started.
Information: 0x402090E0 at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB
Destination [2]: The final commit for the data insertion has ended.
Information: 0x40043009 at {6C86B366-5C26-40A1-B214-86D284BE277B},
DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at {6C86B366-5C26-40A1-B214-86D284BE277B},
DTS.Pipeline: "component "OLE DB Destination" (2)" wrote 0 rows.
Task failed: {6C86B366-5C26-40A1-B214-86D284BE277B}
Task failed: Execute Inner Package
Task failed: CompensatingSql
SSIS package "Package1.dtsx" finished: Failure.
...
Seems like the root error message is "Ad hoc updates to system catalogs are
not allowed.".
This doesn't make me very much wiser!
Does anyone have a clueon what this means?
Olav
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OiKc3CR4FHA.744@.TK2MSFTNGP10.phx.gbl...
> Specify an output file for the job step and see if you get any root error
> messages there.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Olav Tollefsen" <x@.y.com> wrote in message
> news:OAZp3hM4FHA.3636@.TK2MSFTNGP09.phx.gbl...
>
|||I'm no DTS person, but it looks line you try to execute some code that tries to modify the system
tables on 2005 (which isn't possible). If this isn't the case, I suggest you post to the DTS group.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Olav Tollefsen" <x@.y.com> wrote in message news:uaSLdqw4FHA.696@.TK2MSFTNGP09.phx.gbl...
>I have created a new SSIS package using the Import / Export Wizard and theese are the errors I get
>when I run it:
> ...
> Information: 0x40043006 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline: Prepare for
> Execute phase is beginning.
> Information: 0x40043007 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline: Pre-Execute phase
> is beginning.
> Information: 0x4004300C at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline: Execute phase is
> beginning.
> Error: 0xC0202009 at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB Destination [2]: An OLE DB
> error has occurred. Error code: 0x80004005.
> An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005
> Description: "Ad hoc updates to system catalogs are not allowed.".
> Error: 0xC0209029 at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB Destination [2]: The "input
> "OLE DB Destination Input" (29)" failed because error code 0xC020907B occurred, and the error row
> disposition on "input "OLE DB Destination Input" (29)" specifies failure on error. An error
> occurred on the specified object of the specified component.
> Error: 0xC0047022 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline: The ProcessInput method
> on component "OLE DB Destination" (2) failed with error code 0xC0209029. The identified component
> returned an error from the ProcessInput method. The error is specific to the component, but the
> error is fatal and will cause the Data Flow task to stop running.
> Error: 0xC0047021 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline: Thread "WorkThread0"
> has exited with error code 0xC0209029.
> Information: 0x40043008 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline: Post Execute
> phase is beginning.
> Information: 0x402090DF at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB Destination [2]: The
> final commit for the data insertion has started.
> Information: 0x402090E0 at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB Destination [2]: The
> final commit for the data insertion has ended.
> Information: 0x40043009 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline: Cleanup phase is
> beginning.
> Information: 0x4004300B at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline: "component "OLE
> DB Destination" (2)" wrote 0 rows.
> Task failed: {6C86B366-5C26-40A1-B214-86D284BE277B}
> Task failed: Execute Inner Package
> Task failed: CompensatingSql
> SSIS package "Package1.dtsx" finished: Failure.
> ...
> Seems like the root error message is "Ad hoc updates to system catalogs are not allowed.".
> This doesn't make me very much wiser!
> Does anyone have a clueon what this means?
> Olav
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:OiKc3CR4FHA.744@.TK2MSFTNGP10.phx.gbl...
>

Agent job fails, why?

SQL Server 2005 RTM:
I have created a job to copy a database from another server, but the job
fails when I try to run it.
This is the only information I can see in the Job History:
Date,Source,Severity,Step ID,Server,Job Name,Step
Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator
Emailed,Operator Net sent,Operator Paged,Retries Attempted
11/02/2005
13:35:51,CDW_LION_SQLSERVER2005_DEV01_0,
Error,0,DEV01,CDW_LION_SQLSERVER2005
_DEV01_0,(Job
outcome),,The job failed. The Job was invoked by User SAFARI\olavt. The
last step to run was step 1
(CDW_LION_SQLSERVER2005_DEV01_0_Step).,00:00:31,0,0,,,,0
11/02/2005
13:35:51,CDW_LION_SQLSERVER2005_DEV01_0,
Error,1,DEV01,CDW_LION_SQLSERVER2005
_DEV01_0,CDW_LION_SQLSERVER2005_DEV01_0_
Step,,Executed
as user: SAFARI\Administrator. The package execution failed. The step
failed.,00:00:31,0,0,,,,0
This doesn't help me to understand why the job fails.
How can I find the "Real" error log and get a decent clue on why the job
failed?
OlavSpecify an output file for the job step and see if you get any root error me
ssages there.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Olav Tollefsen" <x@.y.com> wrote in message news:OAZp3hM4FHA.3636@.TK2MSFTNGP09.phx.gbl...[vb
col=seagreen]
> SQL Server 2005 RTM:
> I have created a job to copy a database from another server, but the job f
ails when I try to run
> it.
> This is the only information I can see in the Job History:
> Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Messa
ge,Duration,Sql
> Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,
Retries Attempted
> 11/02/2005
> 13:35:51,CDW_LION_SQLSERVER2005_DEV01_0,
Error,0,DEV01,CDW_LION_SQLSERVER20
05_DEV01_0,(Job
> outcome),,The job failed. The Job was invoked by User SAFARI\olavt. The
last step to run was
> step 1 (CDW_LION_SQLSERVER2005_DEV01_0_Step).,00:00:31,0,0,,,,0
> 11/02/2005
> 13:35:51,CDW_LION_SQLSERVER2005_DEV01_0,
Error,1,DEV01,CDW_LION_SQLSERVER20
05_DEV01_0,CDW_LION_SQLSERVER2005_DEV01_
0_Step,,Executed
> as user: SAFARI\Administrator. The package execution failed. The step fai
led.,00:00:31,0,0,,,,0
> This doesn't help me to understand why the job fails.
> How can I find the "Real" error log and get a decent clue on why the job f
ailed?
> Olav
>[/vbcol]|||I have created a new SSIS package using the Import / Export Wizard and
theese are the errors I get when I run it:
...
Information: 0x40043006 at {6C86B366-5C26-40A1-B214-86D284BE277B},
DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at {6C86B366-5C26-40A1-B214-86D284BE277B},
DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x4004300C at {6C86B366-5C26-40A1-B214-86D284BE277B},
DTS.Pipeline: Execute phase is beginning.
Error: 0xC0202009 at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB
Destination [2]: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client"
Hresult: 0x80004005 Description: "Ad hoc updates to system catalogs are not
allowed.".
Error: 0xC0209029 at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB
Destination [2]: The "input "OLE DB Destination Input" (29)" failed beca
use
error code 0xC020907B occurred, and the error row disposition on "input "OLE
DB Destination Input" (29)" specifies failure on error. An error occurred on
the specified object of the specified component.
Error: 0xC0047022 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeli
ne:
The ProcessInput method on component "OLE DB Destination" (2) failed with
error code 0xC0209029. The identified component returned an error from the
ProcessInput method. The error is specific to the component, but the error
is fatal and will cause the Data Flow task to stop running.
Error: 0xC0047021 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeli
ne:
Thread "WorkThread0" has exited with error code 0xC0209029.
Information: 0x40043008 at {6C86B366-5C26-40A1-B214-86D284BE277B},
DTS.Pipeline: Post Execute phase is beginning.
Information: 0x402090DF at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE
DB
Destination [2]: The final commit for the data insertion has started.
Information: 0x402090E0 at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE
DB
Destination [2]: The final commit for the data insertion has ended.
Information: 0x40043009 at {6C86B366-5C26-40A1-B214-86D284BE277B},
DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at {6C86B366-5C26-40A1-B214-86D284BE277B},
DTS.Pipeline: "component "OLE DB Destination" (2)" wrote 0 rows.
Task failed: {6C86B366-5C26-40A1-B214-86D284BE277B}
Task failed: Execute Inner Package
Task failed: CompensatingSql
SSIS package "Package1.dtsx" finished: Failure.
...
Seems like the root error message is "Ad hoc updates to system catalogs are
not allowed.".
This doesn't make me very much wiser!
Does anyone have a clueon what this means?
Olav
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OiKc3CR4FHA.744@.TK2MSFTNGP10.phx.gbl...
> Specify an output file for the job step and see if you get any root error
> messages there.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Olav Tollefsen" <x@.y.com> wrote in message
> news:OAZp3hM4FHA.3636@.TK2MSFTNGP09.phx.gbl...
>|||I'm no DTS person, but it looks line you try to execute some code that tries
to modify the system
tables on 2005 (which isn't possible). If this isn't the case, I suggest you
post to the DTS group.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Olav Tollefsen" <x@.y.com> wrote in message news:uaSLdqw4FHA.696@.TK2MSFTNGP09.phx.gbl...[vbc
ol=seagreen]
>I have created a new SSIS package using the Import / Export Wizard and thee
se are the errors I get
>when I run it:
> ...
> Information: 0x40043006 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DT
S.Pipeline: Prepare for
> Execute phase is beginning.
> Information: 0x40043007 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DT
S.Pipeline: Pre-Execute phase
> is beginning.
> Information: 0x4004300C at {6C86B366-5C26-40A1-B214-86D284BE277B}, DT
S.Pipeline: Execute phase is
> beginning.
> Error: 0xC0202009 at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB D
estination [2]: An OLE DB
> error has occurred. Error code: 0x80004005.
> An OLE DB record is available. Source: "Microsoft SQL Native Client" Hres
ult: 0x80004005
> Description: "Ad hoc updates to system catalogs are not allowed.".
> Error: 0xC0209029 at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB D
estination [2]: The "input
> "OLE DB Destination Input" (29)" failed because error code 0xC020907B occu
rred, and the error row
> disposition on "input "OLE DB Destination Input" (29)" specifies failure o
n error. An error
> occurred on the specified object of the specified component.
> Error: 0xC0047022 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipe
line: The ProcessInput method
> on component "OLE DB Destination" (2) failed with error code 0xC0209029. T
he identified component
> returned an error from the ProcessInput method. The error is specific to t
he component, but the
> error is fatal and will cause the Data Flow task to stop running.
> Error: 0xC0047021 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipe
line: Thread "WorkThread0"
> has exited with error code 0xC0209029.
> Information: 0x40043008 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DT
S.Pipeline: Post Execute
> phase is beginning.
> Information: 0x402090DF at {6C86B366-5C26-40A1-B214-86D284BE277B}, OL
E DB Destination [2]: The
> final commit for the data insertion has started.
> Information: 0x402090E0 at {6C86B366-5C26-40A1-B214-86D284BE277B}, OL
E DB Destination [2]: The
> final commit for the data insertion has ended.
> Information: 0x40043009 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DT
S.Pipeline: Cleanup phase is
> beginning.
> Information: 0x4004300B at {6C86B366-5C26-40A1-B214-86D284BE277B}, DT
S.Pipeline: "component "OLE
> DB Destination" (2)" wrote 0 rows.
> Task failed: {6C86B366-5C26-40A1-B214-86D284BE277B}
> Task failed: Execute Inner Package
> Task failed: CompensatingSql
> SSIS package "Package1.dtsx" finished: Failure.
> ...
> Seems like the root error message is "Ad hoc updates to system catalogs ar
e not allowed.".
> This doesn't make me very much wiser!
> Does anyone have a clueon what this means?
> Olav
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:OiKc3CR4FHA.744@.TK2MSFTNGP10.phx.gbl...
>[/vbcol]

Agent job fails, why?

SQL Server 2005 RTM:
I have created a job to copy a database from another server, but the job
fails when I try to run it.
This is the only information I can see in the Job History:
Date,Source,Severity,Step ID,Server,Job Name,Step
Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator
Emailed,Operator Net sent,Operator Paged,Retries Attempted
11/02/2005
13:35:51,CDW_LION_SQLSERVER2005_DEV01_0,Error,0,DEV01,CDW_LION_SQLSERVER2005_DEV01_0,(Job
outcome),,The job failed. The Job was invoked by User SAFARI\olavt. The
last step to run was step 1
(CDW_LION_SQLSERVER2005_DEV01_0_Step).,00:00:31,0,0,,,,0
11/02/2005
13:35:51,CDW_LION_SQLSERVER2005_DEV01_0,Error,1,DEV01,CDW_LION_SQLSERVER2005_DEV01_0,CDW_LION_SQLSERVER2005_DEV01_0_Step,,Executed
as user: SAFARI\Administrator. The package execution failed. The step
failed.,00:00:31,0,0,,,,0
This doesn't help me to understand why the job fails.
How can I find the "Real" error log and get a decent clue on why the job
failed?
OlavSpecify an output file for the job step and see if you get any root error messages there.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Olav Tollefsen" <x@.y.com> wrote in message news:OAZp3hM4FHA.3636@.TK2MSFTNGP09.phx.gbl...
> SQL Server 2005 RTM:
> I have created a job to copy a database from another server, but the job fails when I try to run
> it.
> This is the only information I can see in the Job History:
> Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql
> Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
> 11/02/2005
> 13:35:51,CDW_LION_SQLSERVER2005_DEV01_0,Error,0,DEV01,CDW_LION_SQLSERVER2005_DEV01_0,(Job
> outcome),,The job failed. The Job was invoked by User SAFARI\olavt. The last step to run was
> step 1 (CDW_LION_SQLSERVER2005_DEV01_0_Step).,00:00:31,0,0,,,,0
> 11/02/2005
> 13:35:51,CDW_LION_SQLSERVER2005_DEV01_0,Error,1,DEV01,CDW_LION_SQLSERVER2005_DEV01_0,CDW_LION_SQLSERVER2005_DEV01_0_Step,,Executed
> as user: SAFARI\Administrator. The package execution failed. The step failed.,00:00:31,0,0,,,,0
> This doesn't help me to understand why the job fails.
> How can I find the "Real" error log and get a decent clue on why the job failed?
> Olav
>|||I have created a new SSIS package using the Import / Export Wizard and
theese are the errors I get when I run it:
...
Information: 0x40043006 at {6C86B366-5C26-40A1-B214-86D284BE277B},
DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at {6C86B366-5C26-40A1-B214-86D284BE277B},
DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x4004300C at {6C86B366-5C26-40A1-B214-86D284BE277B},
DTS.Pipeline: Execute phase is beginning.
Error: 0xC0202009 at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB
Destination [2]: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client"
Hresult: 0x80004005 Description: "Ad hoc updates to system catalogs are not
allowed.".
Error: 0xC0209029 at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB
Destination [2]: The "input "OLE DB Destination Input" (29)" failed because
error code 0xC020907B occurred, and the error row disposition on "input "OLE
DB Destination Input" (29)" specifies failure on error. An error occurred on
the specified object of the specified component.
Error: 0xC0047022 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline:
The ProcessInput method on component "OLE DB Destination" (2) failed with
error code 0xC0209029. The identified component returned an error from the
ProcessInput method. The error is specific to the component, but the error
is fatal and will cause the Data Flow task to stop running.
Error: 0xC0047021 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline:
Thread "WorkThread0" has exited with error code 0xC0209029.
Information: 0x40043008 at {6C86B366-5C26-40A1-B214-86D284BE277B},
DTS.Pipeline: Post Execute phase is beginning.
Information: 0x402090DF at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB
Destination [2]: The final commit for the data insertion has started.
Information: 0x402090E0 at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB
Destination [2]: The final commit for the data insertion has ended.
Information: 0x40043009 at {6C86B366-5C26-40A1-B214-86D284BE277B},
DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at {6C86B366-5C26-40A1-B214-86D284BE277B},
DTS.Pipeline: "component "OLE DB Destination" (2)" wrote 0 rows.
Task failed: {6C86B366-5C26-40A1-B214-86D284BE277B}
Task failed: Execute Inner Package
Task failed: CompensatingSql
SSIS package "Package1.dtsx" finished: Failure.
...
Seems like the root error message is "Ad hoc updates to system catalogs are
not allowed.".
This doesn't make me very much wiser!
Does anyone have a clueon what this means?
Olav
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OiKc3CR4FHA.744@.TK2MSFTNGP10.phx.gbl...
> Specify an output file for the job step and see if you get any root error
> messages there.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Olav Tollefsen" <x@.y.com> wrote in message
> news:OAZp3hM4FHA.3636@.TK2MSFTNGP09.phx.gbl...
>> SQL Server 2005 RTM:
>> I have created a job to copy a database from another server, but the job
>> fails when I try to run it.
>> This is the only information I can see in the Job History:
>> Date,Source,Severity,Step ID,Server,Job Name,Step
>> Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator
>> Emailed,Operator Net sent,Operator Paged,Retries Attempted
>> 11/02/2005
>> 13:35:51,CDW_LION_SQLSERVER2005_DEV01_0,Error,0,DEV01,CDW_LION_SQLSERVER2005_DEV01_0,(Job
>> outcome),,The job failed. The Job was invoked by User SAFARI\olavt. The
>> last step to run was step 1
>> (CDW_LION_SQLSERVER2005_DEV01_0_Step).,00:00:31,0,0,,,,0
>> 11/02/2005
>> 13:35:51,CDW_LION_SQLSERVER2005_DEV01_0,Error,1,DEV01,CDW_LION_SQLSERVER2005_DEV01_0,CDW_LION_SQLSERVER2005_DEV01_0_Step,,Executed
>> as user: SAFARI\Administrator. The package execution failed. The step
>> failed.,00:00:31,0,0,,,,0
>> This doesn't help me to understand why the job fails.
>> How can I find the "Real" error log and get a decent clue on why the job
>> failed?
>> Olav
>|||I'm no DTS person, but it looks line you try to execute some code that tries to modify the system
tables on 2005 (which isn't possible). If this isn't the case, I suggest you post to the DTS group.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Olav Tollefsen" <x@.y.com> wrote in message news:uaSLdqw4FHA.696@.TK2MSFTNGP09.phx.gbl...
>I have created a new SSIS package using the Import / Export Wizard and theese are the errors I get
>when I run it:
> ...
> Information: 0x40043006 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline: Prepare for
> Execute phase is beginning.
> Information: 0x40043007 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline: Pre-Execute phase
> is beginning.
> Information: 0x4004300C at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline: Execute phase is
> beginning.
> Error: 0xC0202009 at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB Destination [2]: An OLE DB
> error has occurred. Error code: 0x80004005.
> An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005
> Description: "Ad hoc updates to system catalogs are not allowed.".
> Error: 0xC0209029 at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB Destination [2]: The "input
> "OLE DB Destination Input" (29)" failed because error code 0xC020907B occurred, and the error row
> disposition on "input "OLE DB Destination Input" (29)" specifies failure on error. An error
> occurred on the specified object of the specified component.
> Error: 0xC0047022 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline: The ProcessInput method
> on component "OLE DB Destination" (2) failed with error code 0xC0209029. The identified component
> returned an error from the ProcessInput method. The error is specific to the component, but the
> error is fatal and will cause the Data Flow task to stop running.
> Error: 0xC0047021 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline: Thread "WorkThread0"
> has exited with error code 0xC0209029.
> Information: 0x40043008 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline: Post Execute
> phase is beginning.
> Information: 0x402090DF at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB Destination [2]: The
> final commit for the data insertion has started.
> Information: 0x402090E0 at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB Destination [2]: The
> final commit for the data insertion has ended.
> Information: 0x40043009 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline: Cleanup phase is
> beginning.
> Information: 0x4004300B at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline: "component "OLE
> DB Destination" (2)" wrote 0 rows.
> Task failed: {6C86B366-5C26-40A1-B214-86D284BE277B}
> Task failed: Execute Inner Package
> Task failed: CompensatingSql
> SSIS package "Package1.dtsx" finished: Failure.
> ...
> Seems like the root error message is "Ad hoc updates to system catalogs are not allowed.".
> This doesn't make me very much wiser!
> Does anyone have a clueon what this means?
> Olav
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:OiKc3CR4FHA.744@.TK2MSFTNGP10.phx.gbl...
>> Specify an output file for the job step and see if you get any root error messages there.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Olav Tollefsen" <x@.y.com> wrote in message news:OAZp3hM4FHA.3636@.TK2MSFTNGP09.phx.gbl...
>> SQL Server 2005 RTM:
>> I have created a job to copy a database from another server, but the job fails when I try to run
>> it.
>> This is the only information I can see in the Job History:
>> Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql
>> Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
>> 11/02/2005
>> 13:35:51,CDW_LION_SQLSERVER2005_DEV01_0,Error,0,DEV01,CDW_LION_SQLSERVER2005_DEV01_0,(Job
>> outcome),,The job failed. The Job was invoked by User SAFARI\olavt. The last step to run was
>> step 1 (CDW_LION_SQLSERVER2005_DEV01_0_Step).,00:00:31,0,0,,,,0
>> 11/02/2005
>> 13:35:51,CDW_LION_SQLSERVER2005_DEV01_0,Error,1,DEV01,CDW_LION_SQLSERVER2005_DEV01_0,CDW_LION_SQLSERVER2005_DEV01_0_Step,,Executed
>> as user: SAFARI\Administrator. The package execution failed. The step failed.,00:00:31,0,0,,,,0
>> This doesn't help me to understand why the job fails.
>> How can I find the "Real" error log and get a decent clue on why the job failed?
>> Olav
>>
>

Sunday, February 19, 2012

After SQL 2005 SP2, Query to Apartment Linked Servers fails

We have a linked server that is an Apartment model OLE DB Provider. It works fine on SQL 2005 SP1 and previous versions.
After applying SP2, we get the following error message when running a query against the linked server.
I cannot find anything in the SP2 documentation that indicates a change of behavior for linked servers.
Any ideas ?

Msg 7308, Level 16, State 1, Line 1

OLE DB provider 'XXX.XXXXX' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

TobyHi, I have the same problem with MSDAORA when I try to execute a query using a Linked Server with Oracle Server.
"Msg 7308, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
"

Someone can help us?

Thanks

Denis
|||

I'm not in a position to answer about the change in the linked server code, but perhaps it is possible to reconfigure the appartment model.

What do you have under HKEY_CLASSES_ROOT\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\InprocServer32\ThreadingModel?

Can you try setting it to "Both" and see if you can connect?|||

Ditto,

Did you find a solution?

Thanks.

|||Did you try changing apartment model? Does it not work for you?|||

No, Basically I found out that the 32 bit OLEDB Provider will not work correctly on 64Bit Server 2003 with SQL 2005 SP2 64Bits. I ended up installed the Oracle 64Bit Provider and use OraOLEDB.Oracle provider.

Here's the complete thread:

I'm trying to get SQLServer 2005 to link to an Oracle 10g Database.

The OS is Server 2003 SP2 64Bit.

The Microsoft OLE DB For Oracle driver is installed (MSDAORA) but doesn't appear in SQL Server Providers.

It's a 32 Bit provider.

Since it didn't appear, I used T-SQL sp_addlinkedserver (http://www.sqlmag.com/Article/ArticleID/49687/sql_server_49687.html) to add my linked server.

When I tested the link, it told me MSDAORA didn't exist. So I ran regsvr32 on "C:\Program Files (x86)\Common Files\System\Ole DB\msdaora.dll" to re-register the Provider.

After that, when executing some T-SQL against Oracle, I got a new error:

http://www.sqlmag.com/Article/ArticleID/49687/sql_server_49687.html) to add my linked server.

When I tested the link, it told me MSDAORA didn't exist. So I ran regsvr32 on "C:\Program Files (x86)\Common Files\System\Ole DB\msdaora.dll" to re-register the Provider.

After that, when executing some T-SQL against Oracle, I got a new error:

Msg 7308, Level 16, State 1, Line 1

OLE DB provider 'MSDAORA' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

But the registry shows:

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}]
@.="MSDAORA"
"OLEDB_SERVICES"=dword:ffffffff

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\ExtendedErrors]
@.="Extended Error Service"

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\ExtendedErrors\{e8cc4cbf-fdff-11d0-b865-00a0c9081c1d}]
@.="MSDAORA ErrorLookup"

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\InprocServer32]
@.="C:\\Program Files (x86)\\Common Files\\System\\Ole DB\\msdaora.dll"
"ThreadingModel"="Both"

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\OLE DB Provider]
@.="Microsoft OLE DB Provider for Oracle"

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\ProgID]
@.="MSDAORA.1"

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\VersionIndependentProgID]
@.="MSDAORA"

>>>

Looks like SQL 2005 SP2 may have caused the problem.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1433118&SiteID=1

>>>

Use Oracle 64Bit drivers : http://www.oracle.com/technology/software/tech/windows/odpnet/64-bit/index.html

>>>

But after creating the Linked Server I got:

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "OraOLEDB.Oracle" for linked server "IBM_EDBD" reported an error. The provider did not give any information about the error.

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "IBM_EDBD".

>>>

http://www.oracle.com/technology/software/tech/windows/odpnet/64-bit/index.html

>>>

But after creating the Linked Server I got:

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "OraOLEDB.Oracle" for linked server "IBM_EDBD" reported an error. The provider did not give any information about the error.

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "IBM_EDBD".

>>>

For 64-bit Distributors, Oracle publishing uses the Oracle OLEDB Provider for Oracle (OraOLEDB.Oracle). SQL Server creates a registry entry to allow the Oracle provider to run in process with SQL Server. If there is a problem reading or writing this registry entry, the following error message is shown:

"Unable to update the registry of distributor '%s' to allow Oracle OLEDB provider OraOLEDB.Oracle to run in process with SQL Server. Make certain that current login is authorized to modify SQL Server owned registry keys."

Oracle publishing requires the registry entry to exist and to be set to 1 for 64 bit Distributors. If the entry does not exist, SQL Server will attempt to create it. If the entry exists, but is set to 0, the setting will not be changed; the configuration of the Oracle Publisher will fail.

To view and modify the registry setting:

1. Click Start, and then click Run.

2. In the Run dialog box, type regedit, and then click OK.

3. Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<InstanceName>\Providers.
Included under Providers should be a folder named OraOLEDB.Oracle. Within this folder should be the DWORD value name AllowInProcess, with a value of 1.

4. If you determine that AllowInProcess is set to 0, update the registry entry to 1:

a. Right-click the entry, and then click Modify.

b. In the Edit String dialog box, type 1 in the Value data field.

So in conclusion:

To create a linked server on SQL Server 2005 SP2 64Bits,

- Install 64Bit Oracle Client

- Install 64Bit Oracle OLEDB Provider

- Set the AllowInProcess value in the registry

After SQL 2005 SP2, Query to Apartment Linked Servers fails

Msg 7308, Level 16, State 1, Line 1

OLE DB provider 'XXX.XXXXX' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

Someone can help us?

Thanks

Denis
|||

I'm not in a position to answer about the change in the linked server code, but perhaps it is possible to reconfigure the appartment model.

What do you have under HKEY_CLASSES_ROOT\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\InprocServer32\ThreadingModel?

Can you try setting it to "Both" and see if you can connect?|||

Ditto,

Did you find a solution?

Thanks.

|||Did you try changing apartment model? Does it not work for you?|||

Here's the complete thread:

I'm trying to get SQLServer 2005 to link to an Oracle 10g Database.

The OS is Server 2003 SP2 64Bit.

The Microsoft OLE DB For Oracle driver is installed (MSDAORA) but doesn't appear in SQL Server Providers.

It's a 32 Bit provider.

Since it didn't appear, I used T-SQL sp_addlinkedserver (http://www.sqlmag.com/Article/ArticleID/49687/sql_server_49687.html) to add my linked server.

When I tested the link, it told me MSDAORA didn't exist. So I ran regsvr32 on "C:\Program Files (x86)\Common Files\System\Ole DB\msdaora.dll" to re-register the Provider.

After that, when executing some T-SQL against Oracle, I got a new error:

http://www.sqlmag.com/Article/ArticleID/49687/sql_server_49687.html) to add my linked server.

When I tested the link, it told me MSDAORA didn't exist. So I ran regsvr32 on "C:\Program Files (x86)\Common Files\System\Ole DB\msdaora.dll" to re-register the Provider.

After that, when executing some T-SQL against Oracle, I got a new error:

Msg 7308, Level 16, State 1, Line 1

OLE DB provider 'MSDAORA' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

But the registry shows:

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}]
@.="MSDAORA"
"OLEDB_SERVICES"=dword:ffffffff

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\ExtendedErrors]
@.="Extended Error Service"

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\ExtendedErrors\{e8cc4cbf-fdff-11d0-b865-00a0c9081c1d}]
@.="MSDAORA ErrorLookup"

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\InprocServer32]
@.="C:\\Program Files (x86)\\Common Files\\System\\Ole DB\\msdaora.dll"
"ThreadingModel"="Both"

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\OLE DB Provider]
@.="Microsoft OLE DB Provider for Oracle"

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\ProgID]
@.="MSDAORA.1"

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\VersionIndependentProgID]
@.="MSDAORA"

>>>

Looks like SQL 2005 SP2 may have caused the problem.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1433118&SiteID=1

>>>

Use Oracle 64Bit drivers : http://www.oracle.com/technology/software/tech/windows/odpnet/64-bit/index.html

>>>

But after creating the Linked Server I got:

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "OraOLEDB.Oracle" for linked server "IBM_EDBD" reported an error. The provider did not give any information about the error.

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "IBM_EDBD".

>>>

http://www.oracle.com/technology/software/tech/windows/odpnet/64-bit/index.html

>>>

But after creating the Linked Server I got:

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "OraOLEDB.Oracle" for linked server "IBM_EDBD" reported an error. The provider did not give any information about the error.

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "IBM_EDBD".

>>>

For 64-bit Distributors, Oracle publishing uses the Oracle OLEDB Provider for Oracle (OraOLEDB.Oracle). SQL Server creates a registry entry to allow the Oracle provider to run in process with SQL Server. If there is a problem reading or writing this registry entry, the following error message is shown:

"Unable to update the registry of distributor '%s' to allow Oracle OLEDB provider OraOLEDB.Oracle to run in process with SQL Server. Make certain that current login is authorized to modify SQL Server owned registry keys."

Oracle publishing requires the registry entry to exist and to be set to 1 for 64 bit Distributors. If the entry does not exist, SQL Server will attempt to create it. If the entry exists, but is set to 0, the setting will not be changed; the configuration of the Oracle Publisher will fail.

To view and modify the registry setting:

1. Click Start, and then click Run.

2. In the Run dialog box, type regedit, and then click OK.

3. Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<InstanceName>\Providers.
Included under Providers should be a folder named OraOLEDB.Oracle. Within this folder should be the DWORD value name AllowInProcess, with a value of 1.

4. If you determine that AllowInProcess is set to 0, update the registry entry to 1:

a. Right-click the entry, and then click Modify.

b. In the Edit String dialog box, type 1 in the Value data field.

So in conclusion:

To create a linked server on SQL Server 2005 SP2 64Bits,

- Install 64Bit Oracle Client

- Install 64Bit Oracle OLEDB Provider

- Set the AllowInProcess value in the registry

After SQL 2005 SP2, Query to Apartment Linked Servers fails

Msg 7308, Level 16, State 1, Line 1

OLE DB provider 'XXX.XXXXX' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

Someone can help us?

Thanks

Denis
|||

I'm not in a position to answer about the change in the linked server code, but perhaps it is possible to reconfigure the appartment model.

What do you have under HKEY_CLASSES_ROOT\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\InprocServer32\ThreadingModel?

Can you try setting it to "Both" and see if you can connect?|||

Ditto,

Did you find a solution?

Thanks.

|||Did you try changing apartment model? Does it not work for you?|||

Here's the complete thread:

I'm trying to get SQLServer 2005 to link to an Oracle 10g Database.

The OS is Server 2003 SP2 64Bit.

The Microsoft OLE DB For Oracle driver is installed (MSDAORA) but doesn't appear in SQL Server Providers.

It's a 32 Bit provider.

Since it didn't appear, I used T-SQL sp_addlinkedserver (http://www.sqlmag.com/Article/ArticleID/49687/sql_server_49687.html) to add my linked server.

When I tested the link, it told me MSDAORA didn't exist. So I ran regsvr32 on "C:\Program Files (x86)\Common Files\System\Ole DB\msdaora.dll" to re-register the Provider.

After that, when executing some T-SQL against Oracle, I got a new error:

http://www.sqlmag.com/Article/ArticleID/49687/sql_server_49687.html) to add my linked server.

When I tested the link, it told me MSDAORA didn't exist. So I ran regsvr32 on "C:\Program Files (x86)\Common Files\System\Ole DB\msdaora.dll" to re-register the Provider.

After that, when executing some T-SQL against Oracle, I got a new error:

Msg 7308, Level 16, State 1, Line 1

OLE DB provider 'MSDAORA' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

But the registry shows:

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}]
@.="MSDAORA"
"OLEDB_SERVICES"=dword:ffffffff

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\ExtendedErrors]
@.="Extended Error Service"

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\ExtendedErrors\{e8cc4cbf-fdff-11d0-b865-00a0c9081c1d}]
@.="MSDAORA ErrorLookup"

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\InprocServer32]
@.="C:\\Program Files (x86)\\Common Files\\System\\Ole DB\\msdaora.dll"
"ThreadingModel"="Both"

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\OLE DB Provider]
@.="Microsoft OLE DB Provider for Oracle"

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\ProgID]
@.="MSDAORA.1"

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\VersionIndependentProgID]
@.="MSDAORA"

>>>

Looks like SQL 2005 SP2 may have caused the problem.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1433118&SiteID=1

>>>

Use Oracle 64Bit drivers : http://www.oracle.com/technology/software/tech/windows/odpnet/64-bit/index.html

>>>

But after creating the Linked Server I got:

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "OraOLEDB.Oracle" for linked server "IBM_EDBD" reported an error. The provider did not give any information about the error.

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "IBM_EDBD".

>>>

http://www.oracle.com/technology/software/tech/windows/odpnet/64-bit/index.html

>>>

But after creating the Linked Server I got:

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "OraOLEDB.Oracle" for linked server "IBM_EDBD" reported an error. The provider did not give any information about the error.

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "IBM_EDBD".

>>>

For 64-bit Distributors, Oracle publishing uses the Oracle OLEDB Provider for Oracle (OraOLEDB.Oracle). SQL Server creates a registry entry to allow the Oracle provider to run in process with SQL Server. If there is a problem reading or writing this registry entry, the following error message is shown:

"Unable to update the registry of distributor '%s' to allow Oracle OLEDB provider OraOLEDB.Oracle to run in process with SQL Server. Make certain that current login is authorized to modify SQL Server owned registry keys."

Oracle publishing requires the registry entry to exist and to be set to 1 for 64 bit Distributors. If the entry does not exist, SQL Server will attempt to create it. If the entry exists, but is set to 0, the setting will not be changed; the configuration of the Oracle Publisher will fail.

To view and modify the registry setting:

1. Click Start, and then click Run.

2. In the Run dialog box, type regedit, and then click OK.

3. Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<InstanceName>\Providers.
Included under Providers should be a folder named OraOLEDB.Oracle. Within this folder should be the DWORD value name AllowInProcess, with a value of 1.

4. If you determine that AllowInProcess is set to 0, update the registry entry to 1:

a. Right-click the entry, and then click Modify.

b. In the Edit String dialog box, type 1 in the Value data field.

So in conclusion:

To create a linked server on SQL Server 2005 SP2 64Bits,

- Install 64Bit Oracle Client

- Install 64Bit Oracle OLEDB Provider

- Set the AllowInProcess value in the registry

Sunday, March 25, 2012

alerts

alerts

alerts

Thursday, March 22, 2012

Alert when backup fails

alert in clustering

Tuesday, March 20, 2012

Alert for any Job that Fails

Alert for any Job that Fails

Alert for a job step

Alert fails to notify Operator (SQL Server 2005)

Alert fails to notify Operator (SQL Server 2005)

Alert fails to notify Operator (SQL Server 2005)

Saturday, February 25, 2012

Agent Service Fails to Start

Agent Jobs Failure

Agent Jobs Failure

Friday, February 24, 2012

Agent job fails, why?

Agent job fails, why?

Agent job fails, why?

Sunday, February 19, 2012

After SQL 2005 SP2, Query to Apartment Linked Servers fails

After SQL 2005 SP2, Query to Apartment Linked Servers fails

After SQL 2005 SP2, Query to Apartment Linked Servers fails

SQL tool for MSDE

Blog Archive

About Me