Error when running DTS package as job

I have a DTS package which deletes some rows in a table and then inserts new ones from a Dbase file.

When I execute the package it works perfectly, but when I shedule it as a job it gives this error message:

Executed as user: LOMMELDOM\Administrator. ...DTSStep_DTSExecuteSQLTask_2 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_2 DTSRun OnStart: Copy Data from GRES to [ZNA].[dbo].[GRES] Step DTSRun OnError: Copy Data from GRES to [ZNA].[dbo].[GRES] Step, Error = -2147221164 (80040154) Error string: Class not registered Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 700 Error Detail Records: Error: -2147221164 (80040154); Provider Error: 0 (0) Error string: Class not registered Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 700 Error: -2147221164 (80040154); Provider Error: 0 (0) Error string: Class not registered Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 4700 Error: -2147221164 (... Process Exit Code 5. The step failed.

The deleting of the rows works but the import from the Dbase doens't, I thought it was a security issue. So I made sure the JobAgent has all the rights it needs to access the directory of the Dbase file.

Any clues?

You are using a custom class / transform / task that is not registered on the server. You will need to register it through the GAC....|||But how come it works perfectly on the same server when I just execute the package? And on the same server it fails if job agent does it?|||

You are executing it on YOUR machine, even if it is hosted on the server.

Edit: Unless if you are running it from the job by right clicking and hitting execute job starting at step...

|||

Hi Eric,

Thanks for the reply.

Euh, my machine = server so there is no other machine around.

It fails when sheduled but it also fails when I right click the job and say execute. It only works if I right click the package and execute that! Further there are no other steps in the job and the job was created by right clicking on the package and sheduling it.

|||Looking a bit more closely at your error messages, are you running SSIS (SQL 2005)? It kind of looks like you are still using SQL 2000 and DTS...|||Yes I'm using SQL2000 with DTS, that's why I also mentionned this in the title of my question. I didn't find a DTS specific newsgroup.|||Try this one: http://groups.google.com/group/microsoft.public.sqlserver.dts?lnk=srg

Error when running DTS package as job

I have a DTS package which deletes some rows in a table and then inserts new ones from a Dbase file.

When I execute the package it works perfectly, but when I shedule it as a job it gives this error message:

Executed as user: LOMMELDOM\Administrator. ...DTSStep_DTSExecuteSQLTask_2 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_2 DTSRun OnStart: Copy Data from GRES to [ZNA].[dbo].[GRES] Step DTSRun OnError: Copy Data from GRES to [ZNA].[dbo].[GRES] Step, Error = -2147221164 (80040154) Error string: Class not registered Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 700 Error Detail Records: Error: -2147221164 (80040154); Provider Error: 0 (0) Error string: Class not registered Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 700 Error: -2147221164 (80040154); Provider Error: 0 (0) Error string: Class not registered Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 4700 Error: -2147221164 (... Process Exit Code 5. The step failed.

The deleting of the rows works but the import from the Dbase doens't, I thought it was a security issue. So I made sure the JobAgent has all the rights it needs to access the directory of the Dbase file.

Any clues?

You are using a custom class / transform / task that is not registered on the server. You will need to register it through the GAC....|||But how come it works perfectly on the same server when I just execute the package? And on the same server it fails if job agent does it?|||

You are executing it on YOUR machine, even if it is hosted on the server.

Edit: Unless if you are running it from the job by right clicking and hitting execute job starting at step...

|||

Hi Eric,

Thanks for the reply.

Euh, my machine = server so there is no other machine around.

It fails when sheduled but it also fails when I right click the job and say execute. It only works if I right click the package and execute that! Further there are no other steps in the job and the job was created by right clicking on the package and sheduling it.

|||Looking a bit more closely at your error messages, are you running SSIS (SQL 2005)? It kind of looks like you are still using SQL 2000 and DTS...|||Yes I'm using SQL2000 with DTS, that's why I also mentionned this in the title of my question. I didn't find a DTS specific newsgroup.|||Try this one: http://groups.google.com/group/microsoft.public.sqlserver.dts?lnk=srg

Error when attempting to delete rows

I have a log table with no indexes, triggers, or keys. During the course of development, I will clean out the entries by selecting all the rows (in Database Explorer) and hitting the delete key.

Occassionally, I will end up with couple rows (out of many) that won't delete. I get the following error when I attempt to delete the "affected" rows:

A problem occurred attempting to delete row 1.
Error Source: Microosft.VisualStudio.DataTools.
Error Message: The row value(s) updated or deleted either do not make the
unique or they alter multiple rows (2 rows).

I'm also not able to modify any of the data of the affected rows. There don't seem to be anything really special or unique about these affected rows (compared to the ones I'm able to delete). I've tried rebooting the machine and that did resolve the issue.

This is the second time I've encountered this issue. By any chance, is this a known bug with the beta (it's SQL Server 2005 June CTP)? Or is it some else I'm not aware of?

I've also tried deleting the rows using SQL Server Management, same results.

RichardCan you run SQL Profiler to check the different events executing while you delete these records? There must be something happening in the background that is causing all these.|||

Did you ever get the problem resolved? I am having the same problem.

Thanks, Bob

|||

Does the table have a primary key or unique constraint?

|||I just saw this. I'm sure it was resolved since the post was awhile ago, but that error message can occur if there is a constraint which the deletion violates. A common one might be that a field is set to "NOT NULL".

Error when attempting to delete rows

I have a log table with no indexes, triggers, or keys. During the course of development, I will clean out the entries by selecting all the rows (in Database Explorer) and hitting the delete key.

Occassionally, I will end up with couple rows (out of many) that won't delete. I get the following error when I attempt to delete the "affected" rows:

A problem occurred attempting to delete row 1.
Error Source: Microosft.VisualStudio.DataTools.
Error Message: The row value(s) updated or deleted either do not make the
unique or they alter multiple rows (2 rows).

I'm also not able to modify any of the data of the affected rows. There don't seem to be anything really special or unique about these affected rows (compared to the ones I'm able to delete). I've tried rebooting the machine and that did resolve the issue.

This is the second time I've encountered this issue. By any chance, is this a known bug with the beta (it's SQL Server 2005 June CTP)? Or is it some else I'm not aware of?

I've also tried deleting the rows using SQL Server Management, same results.

Richard
Can you run SQL Profiler to check the different events executing while you delete these records? There must be something happening in the background that is causing all these.|||

Did you ever get the problem resolved? I am having the same problem.

Thanks, Bob

|||

Does the table have a primary key or unique constraint?

|||I just saw this. I'm sure it was resolved since the post was awhile ago, but that error message can occur if there is a constraint which the deletion violates. A common one might be that a field is set to "NOT NULL".

Error when attempting to delete rows

I have a log table with no indexes, triggers, or keys. During the course of development, I will clean out the entries by selecting all the rows (in Database Explorer) and hitting the delete key.

Occassionally, I will end up with couple rows (out of many) that won't delete. I get the following error when I attempt to delete the "affected" rows:

A problem occurred attempting to delete row 1.
Error Source: Microosft.VisualStudio.DataTools.
Error Message: The row value(s) updated or deleted either do not make the
unique or they alter multiple rows (2 rows).

I'm also not able to modify any of the data of the affected rows. There don't seem to be anything really special or unique about these affected rows (compared to the ones I'm able to delete). I've tried rebooting the machine and that did resolve the issue.

This is the second time I've encountered this issue. By any chance, is this a known bug with the beta (it's SQL Server 2005 June CTP)? Or is it some else I'm not aware of?

I've also tried deleting the rows using SQL Server Management, same results.

Richard
Can you run SQL Profiler to check the different events executing while you delete these records? There must be something happening in the background that is causing all these.|||

Did you ever get the problem resolved? I am having the same problem.

Thanks, Bob

|||

Does the table have a primary key or unique constraint?

|||I just saw this. I'm sure it was resolved since the post was awhile ago, but that error message can occur if there is a constraint which the deletion violates. A common one might be that a field is set to "NOT NULL".

Error when alternating background color in table rows

Hi All,

I have a table containing 21 columns. I want to alternate the color on the rows, to make it more radable and nocer to the eye. As such I set the BackgroundColor property on the entire TableRow (so it is applied to every textbox) to the following:

Code Snippet

=iif(RowNumber(nothing)Mod 2,"LightGray","White")

In theory it should work. I checked a few of the texboxes to make sure everything was okay and indeed the BackgroundColor property on each one of them was properly set to the above expression.

When I tried to build the report, I was prompted by 21 errors (one per textbox) stating the following:

[rsCompilerErrorInExpression] The BackgroundColor expression for the textbox 'FIELDNAME' contains an error: [BC30455] Argument not specified for parameter 'TruePart' of 'Public Function IIf(Expression As Boolean, TruePart As Object, FalsePart As Object) As Object'.

Obviously the TruePart parameter has been specified ("LightGray" in my expression), so what could be wrong?

Thanks in advance for the help

Idelso

Hello,

Your function is good. Check the spelling of "LightGray". Try "LightGrey".

Regards...|||

Hi asiaindian,

Unfortunately that didnt do the trick. This time around a went with much simpler, one word, color names and still nothing.

The error messages keep popping up.

Code Snippet

=iif(RowNumber(nothing)Mod 2,"Silver","White")

Thanks,

Idelso

|||

Well, I found the solution to the problem, for those of you who might have to face it in the future....it is a very simple fix.

Amazing what a great "compiler" SSRS has (I am being sarcastic for the record).

It so happens that you can enter the expression as previously shown without the system indicating any syntax problem with it. Well, we are wrong, the only way this is going to be built is if you keep a space between your RowNumber function call and the "Mod" call. So the correct format is:

Code Snippet

=iif(RowNumber(nothing) Mod 2, "Silver","White")

(Pay attention to the space between (nothing) and Mod)

Yup, this was the problem.....interesting

Idelso

Error Violation of Primary Key While Attaching Database

Hello all,
I have been trying to attach a database but it fails because of
duplicate rows (Error 2627).
I searched in the Internet for removing duplicate records but it can
only work if I manage to attach the database first (correct me if I'm
wrong). So without having the database attached to the SQL server, I
will not manage to remove the duplicate rows. But when I tried to
attach, it checks for duplicate records automatically and fail to
attach.
Is there any workaround for my case?
Thanks in advance.
Regards,
Garreth Goh
BTW, it is SQL 2000 with SP3.
Regards,
Garreth Goh
On Dec 18, 10:02 am, gohtiamt...@.gmail.com wrote:
> Hello all,
> I have been trying to attach a database but it fails because of
> duplicate rows (Error 2627).
> I searched in the Internet for removing duplicate records but it can
> only work if I manage to attach the database first (correct me if I'm
> wrong). So without having the database attached to the SQL server, I
> will not manage to remove the duplicate rows. But when I tried to
> attach, it checks for duplicate records automatically and fail to
> attach.
> Is there any workaround for my case?
> Thanks in advance.
> Regards,
> Garreth Goh
|||Hi,
More updates after several attempts (with separate SQL server 2000
SP4).
01. I move the MDF and LDF files to another folders. I have two MDF
and two LDF files for one database.
02. I attach the same but blank backup database to my SQL server with
a new database name.
03. Back it up.
04. Restore the backup with the original database name in the initial
folder where the corrupted files were.
05. Detach the new restored (but empty) database.
06. Move the new created MDF and LDF files to another folder.
07. Copy the original corrupted MDF and LDF files back to the original
folder.
08. Attach the database. Got "Suspect" message.
09. Update "sysdatabases" to set the database into Emergency Mode.
10. Attach the database back. I got Emergency Mode.
11. Run "DBCC CHECKDB WITH NO_INFOMSGS" tells me to do
"REPAIR_ALLOW_DATA_LOSS".
12. Stop SQL services and run in Command Prompt "SQLSERVR -c -m" cause
it tells me to have Single-User mode when trying to run "DBCC
CHECKDB ...REPAIR_ALLOW_DATA_LOSS".
13. When I try to run "DBCC CHECKDB ...REPAIR_ALLOW_DATA_LOSS" in
this Single-Mode, it tells me "Could not run BEGIN TRANSACTION in
database 'MyDB' because the database is in bypass recovery mode."
I am stuck here. Could anyone here help, please?
I may have incorrectly listed down the sequence from point 8 to 11 but
my current status is at point 13.
Regards,
Garreth Goh
On Dec 18, 10:58 am, gohtiamt...@.gmail.com wrote:
> BTW, it is SQL 2000 with SP3.
> Regards,
> Garreth Goh
|||Or get Microsoft Technical Support on the phone.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:261F6FD2-B073-42F2-A218-B3B6AFE3750D@.microsoft.com...
> The error pretty much speak for itself. My guess is that you have to try
> to export the data to a clean database, using some combination of script
> objects (unless you have DDL files to re-create them) and for the data
> SELECT INTO., INSERT SELECT, BCP etc.
> I would bring in an expert if above doesn't feel comfortable to you...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> <gohtiamtjai@.gmail.com> wrote in message
> news:078e33bc-1d03-4d24-a65b-96367d382dde@.a35g2000prf.googlegroups.com...
>

Error Violation of Primary Key While Attaching Database

Hello all,
I have been trying to attach a database but it fails because of
duplicate rows (Error 2627).
I searched in the Internet for removing duplicate records but it can
only work if I manage to attach the database first (correct me if I'm
wrong). So without having the database attached to the SQL server, I
will not manage to remove the duplicate rows. But when I tried to
attach, it checks for duplicate records automatically and fail to
attach.
Is there any workaround for my case?
Thanks in advance.
Regards,
Garreth GohBTW, it is SQL 2000 with SP3.
Regards,
Garreth Goh
On Dec 18, 10:02 am, gohtiamt...@.gmail.com wrote:
> Hello all,
> I have been trying to attach a database but it fails because of
> duplicate rows (Error 2627).
> I searched in the Internet for removing duplicate records but it can
> only work if I manage to attach the database first (correct me if I'm
> wrong). So without having the database attached to the SQL server, I
> will not manage to remove the duplicate rows. But when I tried to
> attach, it checks for duplicate records automatically and fail to
> attach.
> Is there any workaround for my case?
> Thanks in advance.
> Regards,
> Garreth Goh|||Hi,
More updates after several attempts (with separate SQL server 2000
SP4).
01. I move the MDF and LDF files to another folders. I have two MDF
and two LDF files for one database.
02. I attach the same but blank backup database to my SQL server with
a new database name.
03. Back it up.
04. Restore the backup with the original database name in the initial
folder where the corrupted files were.
05. Detach the new restored (but empty) database.
06. Move the new created MDF and LDF files to another folder.
07. Copy the original corrupted MDF and LDF files back to the original
folder.
08. Attach the database. Got "Suspect" message.
09. Update "sysdatabases" to set the database into Emergency Mode.
10. Attach the database back. I got Emergency Mode.
11. Run "DBCC CHECKDB WITH NO_INFOMSGS" tells me to do
"REPAIR_ALLOW_DATA_LOSS".
12. Stop SQL services and run in Command Prompt "SQLSERVR -c -m" cause
it tells me to have Single-User mode when trying to run "DBCC
CHECKDB ...REPAIR_ALLOW_DATA_LOSS".
13. When I try to run "DBCC CHECKDB ...REPAIR_ALLOW_DATA_LOSS" in
this Single-Mode, it tells me "Could not run BEGIN TRANSACTION in
database 'MyDB' because the database is in bypass recovery mode."
I am stuck here. Could anyone here help, please?
I may have incorrectly listed down the sequence from point 8 to 11 but
my current status is at point 13.
Regards,
Garreth Goh
On Dec 18, 10:58 am, gohtiamt...@.gmail.com wrote:
> BTW, it is SQL 2000 with SP3.
> Regards,
> Garreth Goh|||The error pretty much speak for itself. My guess is that you have to try to
export the data to a
clean database, using some combination of script objects (unless you have DD
L files to re-create
them) and for the data SELECT INTO., INSERT SELECT, BCP etc.
I would bring in an expert if above doesn't feel comfortable to you...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<gohtiamtjai@.gmail.com> wrote in message
news:078e33bc-1d03-4d24-a65b-96367d382dde@.a35g2000prf.googlegroups.com...[vbcol=seagreen]
> Hi,
> More updates after several attempts (with separate SQL server 2000
> SP4).
> 01. I move the MDF and LDF files to another folders. I have two MDF
> and two LDF files for one database.
> 02. I attach the same but blank backup database to my SQL server with
> a new database name.
> 03. Back it up.
> 04. Restore the backup with the original database name in the initial
> folder where the corrupted files were.
> 05. Detach the new restored (but empty) database.
> 06. Move the new created MDF and LDF files to another folder.
> 07. Copy the original corrupted MDF and LDF files back to the original
> folder.
> 08. Attach the database. Got "Suspect" message.
> 09. Update "sysdatabases" to set the database into Emergency Mode.
> 10. Attach the database back. I got Emergency Mode.
> 11. Run "DBCC CHECKDB WITH NO_INFOMSGS" tells me to do
> "REPAIR_ALLOW_DATA_LOSS".
> 12. Stop SQL services and run in Command Prompt "SQLSERVR -c -m" cause
> it tells me to have Single-User mode when trying to run "DBCC
> CHECKDB ...REPAIR_ALLOW_DATA_LOSS".
> 13. When I try to run "DBCC CHECKDB ...REPAIR_ALLOW_DATA_LOSS" in
> this Single-Mode, it tells me "Could not run BEGIN TRANSACTION in
> database 'MyDB' because the database is in bypass recovery mode."
> I am stuck here. Could anyone here help, please?
> I may have incorrectly listed down the sequence from point 8 to 11 but
> my current status is at point 13.
>
> Regards,
> Garreth Goh
>
> On Dec 18, 10:58 am, gohtiamt...@.gmail.com wrote:|||Or get Microsoft Technical Support on the phone.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:261F6FD2-B073-42F2-A218-B3B6AFE3750D@.microsoft.com...
> The error pretty much speak for itself. My guess is that you have to try
> to export the data to a clean database, using some combination of script
> objects (unless you have DDL files to re-create them) and for the data
> SELECT INTO., INSERT SELECT, BCP etc.
> I would bring in an expert if above doesn't feel comfortable to you...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> <gohtiamtjai@.gmail.com> wrote in message
> news:078e33bc-1d03-4d24-a65b-96367d382dde@.a35g2000prf.googlegroups.com...
>

Error Violation of Primary Key While Attaching Database

Hello all,
I have been trying to attach a database but it fails because of
duplicate rows (Error 2627).
I searched in the Internet for removing duplicate records but it can
only work if I manage to attach the database first (correct me if I'm
wrong). So without having the database attached to the SQL server, I
will not manage to remove the duplicate rows. But when I tried to
attach, it checks for duplicate records automatically and fail to
attach.
Is there any workaround for my case?
Thanks in advance.
Regards,
Garreth GohBTW, it is SQL 2000 with SP3.
Regards,
Garreth Goh
On Dec 18, 10:02 am, gohtiamt...@.gmail.com wrote:
> Hello all,
> I have been trying to attach a database but it fails because of
> duplicate rows (Error 2627).
> I searched in the Internet for removing duplicate records but it can
> only work if I manage to attach the database first (correct me if I'm
> wrong). So without having the database attached to the SQL server, I
> will not manage to remove the duplicate rows. But when I tried to
> attach, it checks for duplicate records automatically and fail to
> attach.
> Is there any workaround for my case?
> Thanks in advance.
> Regards,
> Garreth Goh|||Hi,
More updates after several attempts (with separate SQL server 2000
SP4).
01. I move the MDF and LDF files to another folders. I have two MDF
and two LDF files for one database.
02. I attach the same but blank backup database to my SQL server with
a new database name.
03. Back it up.
04. Restore the backup with the original database name in the initial
folder where the corrupted files were.
05. Detach the new restored (but empty) database.
06. Move the new created MDF and LDF files to another folder.
07. Copy the original corrupted MDF and LDF files back to the original
folder.
08. Attach the database. Got "Suspect" message.
09. Update "sysdatabases" to set the database into Emergency Mode.
10. Attach the database back. I got Emergency Mode.
11. Run "DBCC CHECKDB WITH NO_INFOMSGS" tells me to do
"REPAIR_ALLOW_DATA_LOSS".
12. Stop SQL services and run in Command Prompt "SQLSERVR -c -m" cause
it tells me to have Single-User mode when trying to run "DBCC
CHECKDB ...REPAIR_ALLOW_DATA_LOSS".
13. When I try to run "DBCC CHECKDB ...REPAIR_ALLOW_DATA_LOSS" in
this Single-Mode, it tells me "Could not run BEGIN TRANSACTION in
database 'MyDB' because the database is in bypass recovery mode."
I am stuck here. Could anyone here help, please?
I may have incorrectly listed down the sequence from point 8 to 11 but
my current status is at point 13.
Regards,
Garreth Goh
On Dec 18, 10:58 am, gohtiamt...@.gmail.com wrote:
> BTW, it is SQL 2000 with SP3.
> Regards,
> Garreth Goh|||The error pretty much speak for itself. My guess is that you have to try to export the data to a
clean database, using some combination of script objects (unless you have DDL files to re-create
them) and for the data SELECT INTO., INSERT SELECT, BCP etc.
I would bring in an expert if above doesn't feel comfortable to you...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<gohtiamtjai@.gmail.com> wrote in message
news:078e33bc-1d03-4d24-a65b-96367d382dde@.a35g2000prf.googlegroups.com...
> Hi,
> More updates after several attempts (with separate SQL server 2000
> SP4).
> 01. I move the MDF and LDF files to another folders. I have two MDF
> and two LDF files for one database.
> 02. I attach the same but blank backup database to my SQL server with
> a new database name.
> 03. Back it up.
> 04. Restore the backup with the original database name in the initial
> folder where the corrupted files were.
> 05. Detach the new restored (but empty) database.
> 06. Move the new created MDF and LDF files to another folder.
> 07. Copy the original corrupted MDF and LDF files back to the original
> folder.
> 08. Attach the database. Got "Suspect" message.
> 09. Update "sysdatabases" to set the database into Emergency Mode.
> 10. Attach the database back. I got Emergency Mode.
> 11. Run "DBCC CHECKDB WITH NO_INFOMSGS" tells me to do
> "REPAIR_ALLOW_DATA_LOSS".
> 12. Stop SQL services and run in Command Prompt "SQLSERVR -c -m" cause
> it tells me to have Single-User mode when trying to run "DBCC
> CHECKDB ...REPAIR_ALLOW_DATA_LOSS".
> 13. When I try to run "DBCC CHECKDB ...REPAIR_ALLOW_DATA_LOSS" in
> this Single-Mode, it tells me "Could not run BEGIN TRANSACTION in
> database 'MyDB' because the database is in bypass recovery mode."
> I am stuck here. Could anyone here help, please?
> I may have incorrectly listed down the sequence from point 8 to 11 but
> my current status is at point 13.
>
> Regards,
> Garreth Goh
>
> On Dec 18, 10:58 am, gohtiamt...@.gmail.com wrote:
>> BTW, it is SQL 2000 with SP3.
>> Regards,
>> Garreth Goh|||Or get Microsoft Technical Support on the phone.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:261F6FD2-B073-42F2-A218-B3B6AFE3750D@.microsoft.com...
> The error pretty much speak for itself. My guess is that you have to try
> to export the data to a clean database, using some combination of script
> objects (unless you have DDL files to re-create them) and for the data
> SELECT INTO., INSERT SELECT, BCP etc.
> I would bring in an expert if above doesn't feel comfortable to you...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> <gohtiamtjai@.gmail.com> wrote in message
> news:078e33bc-1d03-4d24-a65b-96367d382dde@.a35g2000prf.googlegroups.com...
>> Hi,
>> More updates after several attempts (with separate SQL server 2000
>> SP4).
>> 01. I move the MDF and LDF files to another folders. I have two MDF
>> and two LDF files for one database.
>> 02. I attach the same but blank backup database to my SQL server with
>> a new database name.
>> 03. Back it up.
>> 04. Restore the backup with the original database name in the initial
>> folder where the corrupted files were.
>> 05. Detach the new restored (but empty) database.
>> 06. Move the new created MDF and LDF files to another folder.
>> 07. Copy the original corrupted MDF and LDF files back to the original
>> folder.
>> 08. Attach the database. Got "Suspect" message.
>> 09. Update "sysdatabases" to set the database into Emergency Mode.
>> 10. Attach the database back. I got Emergency Mode.
>> 11. Run "DBCC CHECKDB WITH NO_INFOMSGS" tells me to do
>> "REPAIR_ALLOW_DATA_LOSS".
>> 12. Stop SQL services and run in Command Prompt "SQLSERVR -c -m" cause
>> it tells me to have Single-User mode when trying to run "DBCC
>> CHECKDB ...REPAIR_ALLOW_DATA_LOSS".
>> 13. When I try to run "DBCC CHECKDB ...REPAIR_ALLOW_DATA_LOSS" in
>> this Single-Mode, it tells me "Could not run BEGIN TRANSACTION in
>> database 'MyDB' because the database is in bypass recovery mode."
>> I am stuck here. Could anyone here help, please?
>> I may have incorrectly listed down the sequence from point 8 to 11 but
>> my current status is at point 13.
>>
>> Regards,
>> Garreth Goh
>>
>> On Dec 18, 10:58 am, gohtiamt...@.gmail.com wrote:
>> BTW, it is SQL 2000 with SP3.
>> Regards,
>> Garreth Goh
>

Error using EM to view data

Hi,
I have a rather annoying problem with enterprise manager. When I select
Open Table, then Select All Rows . or Select Top . I get the following error
from EM:
Provider cannot be found. It may not be properly installed.
This started a few days ago for no apparent reason. The problem seems to be
limited to my machine, and happens if I access the local data store (MSDE)
or that on other servers (full SQL Sever, ent. ed.). I'm running Win2k
(SP4) and the client tools I'm running are from SQL Server 2000 enterprise
edition (SP3).
All other functions seem to work in EM (such as creating / modifying tables,
etc), and I can access data through other applications on this machine (such
as Query Analyzer, VS.Net, etc).
Things I have tried include reinstalling the latest MDAC (2.8), uninstalling
and reinstalling SQL Server client tools + sp3, and using regsvr32 to
reregister some DLLs suggested in other postings.
One last thing - using Profiler and a different workstation, I noticed that
the Open Table, Select all rows. function starts a new application with a
separate login (the app name is listed as "MS SQLEM - Data Tools"). Does
anyone know anything about this?
TIA for any help anyone can provide.
-DH
I found the solution for this and I am posting it for the common good...
Using filemon and regmon (from sysinternals, nice products), I saw that MMC
was trying to use an old version
of the file msdasql.dll in winnt\system32 rather than the version MDAC
installed in
Program Files\Common Files\System\Ole DB
Using regsvr32 to unregister then re-register the newer file fixed the
problem. Since this file is part of MDAC, I would
have thought that reinsalling MDAC should have fixed the problem, but it
didn't. Ditto for SP4 for Win2k.
I hope this helps someone somewhere someday. -DH
"David Howard" <reply@.to.group> wrote in message
news:UvC8c.290$yN6.61@.newsread2.news.atl.earthlink .net...
> Hi,
> I have a rather annoying problem with enterprise manager. When I select
> Open Table, then Select All Rows . or Select Top . I get the following
error
> from EM:
> Provider cannot be found. It may not be properly installed.
>
> This started a few days ago for no apparent reason. The problem seems to
be
> limited to my machine, and happens if I access the local data store (MSDE)
> or that on other servers (full SQL Sever, ent. ed.). I'm running Win2k
> (SP4) and the client tools I'm running are from SQL Server 2000 enterprise
> edition (SP3).
>
> All other functions seem to work in EM (such as creating / modifying
tables,
> etc), and I can access data through other applications on this machine
(such
> as Query Analyzer, VS.Net, etc).
>
> Things I have tried include reinstalling the latest MDAC (2.8),
uninstalling
> and reinstalling SQL Server client tools + sp3, and using regsvr32 to
> reregister some DLLs suggested in other postings.
>
> One last thing - using Profiler and a different workstation, I noticed
that
> the Open Table, Select all rows. function starts a new application with a
> separate login (the app name is listed as "MS SQLEM - Data Tools"). Does
> anyone know anything about this?
>
> TIA for any help anyone can provide.
> -DH
>
|||It helped me, today.
Thanks a lot for your effort to solve this.
Andrew Arace

Error using EM to view data

Hi,
I have a rather annoying problem with enterprise manager. When I select
Open Table, then Select All Rows . or Select Top . I get the following error
from EM:
Provider cannot be found. It may not be properly installed.
This started a few days ago for no apparent reason. The problem seems to be
limited to my machine, and happens if I access the local data store (MSDE)
or that on other servers (full SQL Sever, ent. ed.). I'm running Win2k
(SP4) and the client tools I'm running are from SQL Server 2000 enterprise
edition (SP3).
All other functions seem to work in EM (such as creating / modifying tables,
etc), and I can access data through other applications on this machine (such
as Query Analyzer, VS.Net, etc).
Things I have tried include reinstalling the latest MDAC (2.8), uninstalling
and reinstalling SQL Server client tools + sp3, and using regsvr32 to
reregister some DLLs suggested in other postings.
One last thing - using Profiler and a different workstation, I noticed that
the Open Table, Select all rows. function starts a new application with a
separate login (the app name is listed as "MS SQLEM - Data Tools"). Does
anyone know anything about this?
TIA for any help anyone can provide.
-DHI found the solution for this and I am posting it for the common good...
Using filemon and regmon (from sysinternals, nice products), I saw that MMC
was trying to use an old version
of the file msdasql.dll in winnt\system32 rather than the version MDAC
installed in
Program Files\Common Files\System\Ole DB
Using regsvr32 to unregister then re-register the newer file fixed the
problem. Since this file is part of MDAC, I would
have thought that reinsalling MDAC should have fixed the problem, but it
didn't. Ditto for SP4 for Win2k.
I hope this helps someone somewhere someday. -DH
"David Howard" <reply@.to.group> wrote in message
news:UvC8c.290$yN6.61@.newsread2.news.atl.earthlink.net...
> Hi,
> I have a rather annoying problem with enterprise manager. When I select
> Open Table, then Select All Rows . or Select Top . I get the following
error
> from EM:
> Provider cannot be found. It may not be properly installed.
>
> This started a few days ago for no apparent reason. The problem seems to
be
> limited to my machine, and happens if I access the local data store (MSDE)
> or that on other servers (full SQL Sever, ent. ed.). I'm running Win2k
> (SP4) and the client tools I'm running are from SQL Server 2000 enterprise
> edition (SP3).
>
> All other functions seem to work in EM (such as creating / modifying
tables,
> etc), and I can access data through other applications on this machine
(such
> as Query Analyzer, VS.Net, etc).
>
> Things I have tried include reinstalling the latest MDAC (2.8),
uninstalling
> and reinstalling SQL Server client tools + sp3, and using regsvr32 to
> reregister some DLLs suggested in other postings.
>
> One last thing - using Profiler and a different workstation, I noticed
that
> the Open Table, Select all rows. function starts a new application with a
> separate login (the app name is listed as "MS SQLEM - Data Tools"). Does
> anyone know anything about this?
>
> TIA for any help anyone can provide.
> -DH
>|||It helped me, today.
Thanks a lot for your effort to solve this.
Andrew Arace

error selecting null columns

I want to run a query that selects rows from the table where a datetime column has null values;

select*from OrderswhereIsNull(dClosedDate,'Null')='Null'

However i get this error:

Conversion failed when converting datetime from character string.

Any help appreciated

SELECT * FROM Orders WHERE ISNULL(dClosedDate, '1900-01-01') = '1900-01-01'

|||

thx...i tried dClosedDate is NULL and it worked too.