Msmerge_genhistory problems

Hi,
I have 12 merge replications (1 Pub, 2 Subs) with remote distributor.
Recently all replications were failed. The error message is :
Cannot insert duplicate key row in object 'MSmerge_genhistory' with unique
index 'unc1MSmerge_genhistory'.
I investigated and discovered that the procedure sp_MsMakegeneration is
responsable for produce the error on line:
update dbo.MSmerge_genhistory set guidsrc = @.genguid, guidlocal = @.genguid,
coldate = @.dt where generation = @.gen
The problem is intermittent.
Error: 2601, Severity: 14, State: 3
Has anyone saw the same problem ?
Thanks
Rodrigo Varella
is this applicable?
http://support.microsoft.com/kb/276427/en-us
and how about
http://groups.google.com/group/microsoft.public.sqlserver.replication/msg/6f9a6ad3c148beef?dmode=source
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Rodrigo Varella" <RodrigoVarella@.discussions.microsoft.com> wrote in
message news:B45D4DE6-B2F9-4B0E-BD89-9FF6A6B3DEA7@.microsoft.com...
> Hi,
> I have 12 merge replications (1 Pub, 2 Subs) with remote distributor.
> Recently all replications were failed. The error message is :
> Cannot insert duplicate key row in object 'MSmerge_genhistory' with unique
> index 'unc1MSmerge_genhistory'.
> I investigated and discovered that the procedure sp_MsMakegeneration is
> responsable for produce the error on line:
> update dbo.MSmerge_genhistory set guidsrc = @.genguid, guidlocal =
> @.genguid,
> coldate = @.dt where generation = @.gen
> The problem is intermittent.
> Error: 2601, Severity: 14, State: 3
> Has anyone saw the same problem ?
> Thanks
> Rodrigo Varella
>
>
|||Hi Hilary,
Thanks about the articles, but I had seem them before. My environment is
SQL 2000 full patched.
Rodrigo Varella
"Hilary Cotter" wrote:

> is this applicable?
> http://support.microsoft.com/kb/276427/en-us
> and how about
> http://groups.google.com/group/microsoft.public.sqlserver.replication/msg/6f9a6ad3c148beef?dmode=source
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Rodrigo Varella" <RodrigoVarella@.discussions.microsoft.com> wrote in
> message news:B45D4DE6-B2F9-4B0E-BD89-9FF6A6B3DEA7@.microsoft.com...
>
>
|||can you reinitialize and redistribute the snapshot to the subscribers?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Rodrigo Varella" <RodrigoVarella@.discussions.microsoft.com> wrote in
message news:FCB2167B-556B-4642-BE3A-F7513044ACB4@.microsoft.com...[vbcol=seagreen]
> Hi Hilary,
> Thanks about the articles, but I had seem them before. My environment
> is
> SQL 2000 full patched.
> Rodrigo Varella
> "Hilary Cotter" wrote:
|||I also found this - it looks like its from a PSS incident.
http://www.gobrien.net/kb/sox/sox/01/11/28/X700048.txt
It suggests dropping the index.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Rodrigo Varella" <RodrigoVarella@.discussions.microsoft.com> wrote in
message news:FCB2167B-556B-4642-BE3A-F7513044ACB4@.microsoft.com...[vbcol=seagreen]
> Hi Hilary,
> Thanks about the articles, but I had seem them before. My environment
> is
> SQL 2000 full patched.
> Rodrigo Varella
> "Hilary Cotter" wrote:
|||Hilary,
The procedure solves the problem!
Thank you!
Rodrigo Varella
"Hilary Cotter" wrote:

> I also found this - it looks like its from a PSS incident.
> http://www.gobrien.net/kb/sox/sox/01/11/28/X700048.txt
> It suggests dropping the index.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Rodrigo Varella" <RodrigoVarella@.discussions.microsoft.com> wrote in
> message news:FCB2167B-556B-4642-BE3A-F7513044ACB4@.microsoft.com...
>
>
|||I sometimes get this. I think it happens when you have an unreliable
environment - is this true in your case?. The fix is to run DBCC
DBREINDEX on the affected index.
Rodrigo Varella wrote:
> Hi,
> I have 12 merge replications (1 Pub, 2 Subs) with remote distributor.
> Recently all replications were failed. The error message is :
> Cannot insert duplicate key row in object 'MSmerge_genhistory' with unique
> index 'unc1MSmerge_genhistory'.
> I investigated and discovered that the procedure sp_MsMakegeneration is
> responsable for produce the error on line:
> update dbo.MSmerge_genhistory set guidsrc = @.genguid, guidlocal = @.genguid,
> coldate = @.dt where generation = @.gen
> The problem is intermittent.
> Error: 2601, Severity: 14, State: 3
> Has anyone saw the same problem ?
> Thanks
> Rodrigo Varella
>
>
|||I have more than 3.000 subscriptions and more than 100 publications. We have
used replications since 2000. We reindex all tables every day. I have never
seem this problem before. My enviroment is checked constantly. We discovered
the duplicate row in msmerge_genhistory using the snapshot procedure. I have
no idea the origin of the problem. It happened suddenly.
Rodrigo Varella
"JE" wrote:

> I sometimes get this. I think it happens when you have an unreliable
> environment - is this true in your case?. The fix is to run DBCC
> DBREINDEX on the affected index.
> Rodrigo Varella wrote:
>
|||I'm running inot the same problem, what was snapshot procedure you used to
resolve this? The link referenced is no longer viewable.
Thank you,
Pauly C
"Rodrigo Varella" wrote:
[vbcol=seagreen]
> I have more than 3.000 subscriptions and more than 100 publications. We have
> used replications since 2000. We reindex all tables every day. I have never
> seem this problem before. My enviroment is checked constantly. We discovered
> the duplicate row in msmerge_genhistory using the snapshot procedure. I have
> no idea the origin of the problem. It happened suddenly.
> Rodrigo Varella
> "JE" wrote:

MSmerge_genhistory error-515

Can't insert value NULL into column nicknames.

This error generated when I try start sync.

SQL Profiler is not helping w/ locating specific error.

I tried to allow NULLS in nickname column, for this table, but this table gets dropped and recreated each time.

Pls. advise on where to make correction.

thx..bt

Can you tell us what version of sql server you're using, including Service Pack # (if any)?|||

Setup:

Server A: SQL Server 2000, Enterprise Ed, SP4 (setup as subscriber)

Server B: SQL Server 2000, Personal Ed, SP4 (setup as publisher/distributor, using PUSH subscription type service).

Server B is a Tablet PC running Win XP 2005, SP2.

When I force 'start sync' to run for Merge Agent, I get failure in Agent logs, but when I query Server A I see the updated data sets. Why would I get failure "error 515 for insert of NULL to nicknames column in Process table and still pass updates?

|||

Additional information that may or may not be helpful:

My data entries are stored on Server B (no connection to LAN or wireless), when I return the tablet PC to cradle I expect snapshot creation and merge to occur.

Server A-subscription -status shows 'Never Started'.

How can this be? When I query tables on Server A I see the updates have occured in my tables.

Apparently I can force 'start sync' from the Merge Agent, but I can't get the agent to successfully run w/o intervention.

Here's the latest error:

process could not bulk copy into table 'x' - error 20037.

thx. for taking the time to review and provide input..bt

|||

You've described three different problems, but it's unclear what the root problem is.

First, replication does not ship anything out of the box that would allow your tablet pc to automatically sync when first connecting to the cradle. You have to either sync manually, schedule a sync, or write a program to sync on connect.

Regarding the last error, 20037, can you drill down to get the exact error message? I don't know what error 20037 is, but if you cannot apply your snapshot, then you cannot sync properly afterwards. I'm guessing that you were able to apply part of your snapshot successfully, which is why you see some rows in your table.

|||

Error 20037 indicates article already exists in another publication w/ different article resolver. (I think this occurs if merge agent is being re-ran w/ same snapshot files.)

I have not figured out how to delete the snapshot directory files directly after Merge agent runs. Maybe it will not delete until it can run successfully w/o manual push.

If delete MSmerge_contents, MSmerge_tombstone,MSmerge_genhistory tables, then re-run Merge agent, result is:

'can't insert value NULL into column nicknames.table Process.dbo.MSmerge_genhistory, column does not allow nulls.'

Review of same tables, I see MSmerge_genhistory has records, but other two tables have none.

If re-run Merge agent, result is:

error 20037 (see above), but data copies to server tables.

So the cycle continues.

|||

Manually deleting merge metadata tables is an unsupported option. This explains why you're getting the "cannot insert NULL" error message. The only workaround now is to remove replication completely and start over. What can happen when you manually delete rows from metadata tables is that cleanup/setup/etc. will try to reference rows that don't exist, or tables will have orphaned rows.

Once you start over on a clean slate, and you're still getting errors, please post the first error and error message that you get.

|||

I deleted the publication and articles via use of stored procedures in SQL Books Online and verified no rowsets in tables.

I then re-created a new Process publication and a new PUSH subscription, it ran. The snapshot created successfully, but the merge agent gave this error.

cannot insert value NULL into column 'nicknames'.table 'Process.dbo.MSmerge_genhistory'. column does not allow nulls. INSERT fails. error 515.

|||

After the publication deletion process and getting same error 515, I did the following: because you indicated the metatables were trashed after I did some manual deletions.

I have looked at MSmerge_history table on both servers. The number of row sets does not match (don't know if it should though).

Anyway, the two rowsets on subscriber do match 2 of the 11 that exist on publisher server.

MSmerge_contents table does match across both servers-w/ 3 rowsets.

MSmerge_tombstone matches on both servers-w/ no rowsets.

Not sure what else to check or whether I should detach my db and remove SQL Server from both systems and start over.

|||

Why does nickname column from MSmerge_genhistory have to accept non-NULL values?

This is the crux of my problem. I can't change the table design becuase it gets re-created each time merge agent runs.

Does anyone know what stored procedure is used to drop/create this table each time?

|||Are there any other errors that you can see in the Merge history table? Can you do "select * from distribution.dbo.MSmerge_history" and see if there are any other errors returned from that particular sync? This may be just one of several errors that showed up, and unfortunately it's not enough info at the moment to determine where the problem is.|||Have you renamed one of your machines recently, or in the past, while SQL Server was installed?

Msg 9002, Level 17, State 4, Line 2

Hi fiaola
SQL Server has to log every data modification operation, every insert,
update and delete. If the log is full, nothing can be written to it and no
data modifications can be done. This has nothing to do with the tool you are
using.
Just do what the messages says. Look at the sys.databases view and tell us
what the log_reuse_wait_desc says. Also look at the recovery model
SELECT log_reuse_wait_desc, recovery_model_desc
FROM sys.databases
WHERE name = 'EventSentry'
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"fiaola" <fiaola@.mail.com> wrote in message
news:uLj2q0WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
> I'm trying to clear some data from a SQL DB table, but i'm getting this
> error:
> I'm just running a Delete Query using Date Diff function. I read in some
> web site that this maybe a problem with SQL Server Management Tools. I
> appreciate any help to get around this and any suggestions would be great.
> Msg 9002, Level 17, State 4, Line 2
> The transaction log for database 'EventSentry' is full. To find out why
> space in the log cannot be reused, see the log_reuse_wait_desc column in
> sys.databases
> Thanks for answering.
>
It might just be that your log is not big enough for the operation you were
doing.
How big is your log file, and is it set to autogrow?
You can get the information in the sys.files view in the database that is
having the problems.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"fiaola" <fiaola@.mail.com> wrote in message
news:uH1ss$YUIHA.5816@.TK2MSFTNGP06.phx.gbl...
> Thanks for the quick reply, here are the results.
> Log_Reuse_Wait_Desc = NOTHING
> Recovery_Model_Desc = SIMPLE
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:eMohs$WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>
sql

msg 8152

When I try to run an insert query, I got the following error message:
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.
Can anyone please help? Thanks.Hi
It is happening because you try to insert a string value which is greater
then you have declared.
CREATE TABLE #Test
(
col VARCHAR(5)
)
INSERT INTO #Test VALUES ('AAAAAA')--6 characters
" -00Eric Clapton" <a@.b.com> wrote in message
news:Oz1KFHcuFHA.2948@.TK2MSFTNGP15.phx.gbl...
> When I try to run an insert query, I got the following error message:
> Server: Msg 8152, Level 16, State 9, Line 1
> String or binary data would be truncated.
> The statement has been terminated.
>
> Can anyone please help? Thanks.
>|||Hi Eric,
Thanks for the post.
For error message 8152, it usually means when the length of the value
entered by you into a char, varchar, nchar, nvarchar column is longer than
the maximum length of the column. For example, inserting 'FAQ' into a
char(2) column would result in this error.
You cou use Profiler to troubleshooting this error message. Tracing what
statement causes this.
If you have any questions or concerns, don't hesitate to let me know. We
are always here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
Others: https://partner.microsoft.com/US/technicalsupport/supportoverview/
If you are outside the United States, please visit our International
Support page: http://support.microsoft.com/common/international.aspx
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

msg 8152

When I try to run an insert query, I got the following error message:
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.
Can anyone please help? Thanks.
Hi
It is happening because you try to insert a string value which is greater
then you have declared.
CREATE TABLE #Test
(
col VARCHAR(5)
)
INSERT INTO #Test VALUES ('AAAAAA')--6 characters
" -00Eric Clapton" <a@.b.com> wrote in message
news:Oz1KFHcuFHA.2948@.TK2MSFTNGP15.phx.gbl...
> When I try to run an insert query, I got the following error message:
> Server: Msg 8152, Level 16, State 9, Line 1
> String or binary data would be truncated.
> The statement has been terminated.
>
> Can anyone please help? Thanks.
>
|||Hi Eric,
Thanks for the post.
For error message 8152, it usually means when the length of the value
entered by you into a char, varchar, nchar, nvarchar column is longer than
the maximum length of the column. For example, inserting 'FAQ' into a
char(2) column would result in this error.
You cou use Profiler to troubleshooting this error message. Tracing what
statement causes this.
If you have any questions or concerns, don't hesitate to let me know. We
are always here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/tec...rview/40010469
Others: https://partner.microsoft.com/US/tec...pportoverview/
If you are outside the United States, please visit our International
Support page: http://support.microsoft.com/common/international.aspx
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

msg 8152

When I try to run an insert query, I got the following error message:
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.
Can anyone please help? Thanks.Hi
It is happening because you try to insert a string value which is greater
then you have declared.
CREATE TABLE #Test
(
col VARCHAR(5)
)
INSERT INTO #Test VALUES ('AAAAAA')--6 characters
" -00Eric Clapton" <a@.b.com> wrote in message
news:Oz1KFHcuFHA.2948@.TK2MSFTNGP15.phx.gbl...
> When I try to run an insert query, I got the following error message:
> Server: Msg 8152, Level 16, State 9, Line 1
> String or binary data would be truncated.
> The statement has been terminated.
>
> Can anyone please help? Thanks.
>|||Hi Eric,
Thanks for the post.
For error message 8152, it usually means when the length of the value
entered by you into a char, varchar, nchar, nvarchar column is longer than
the maximum length of the column. For example, inserting 'FAQ' into a
char(2) column would result in this error.
You cou use Profiler to troubleshooting this error message. Tracing what
statement causes this.
If you have any questions or concerns, don't hesitate to let me know. We
are always here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...erview/40010469
Others: https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page: http://support.microsoft.com/common/international.aspx
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

MSG 605 - Server having problems with fetching the logical pages

Hi,
I'm running SQL 2000 and server quite often (3 times within the last week)
fails on insert operation with the messages like:
"Attempt to fetch logical page (1:202685) in database 'tempdb' belongs to
object '-948063835' not to object #tmp
Connection Broken"
Similar messages were for the tables in the completely different databases.
Does not look like this is directly related to the table's size.
ThanksHi
Looks like corruption. Either your RAM or disk could ber causing this.
Run DBCC CHECKDB againt all your DB's on the server and see what the output
is.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"alex" wrote:

> Hi,
> I'm running SQL 2000 and server quite often (3 times within the last week)
> fails on insert operation with the messages like:
> "Attempt to fetch logical page (1:202685) in database 'tempdb' belongs to
> object '-948063835' not to object #tmp
> Connection Broken"
> Similar messages were for the tables in the completely different databases
.
> Does not look like this is directly related to the table's size.
> Thanks
>

MSG 605 - Server having problems with fetching the logical pages

Hi,
I'm running SQL 2000 and server quite often (3 times within the last week)
fails on insert operation with the messages like:
"Attempt to fetch logical page (1:202685) in database 'tempdb' belongs to
object '-948063835' not to object #tmp
Connection Broken"
Similar messages were for the tables in the completely different databases.
Does not look like this is directly related to the table's size.
Thanks
Hi
Looks like corruption. Either your RAM or disk could ber causing this.
Run DBCC CHECKDB againt all your DB's on the server and see what the output
is.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"alex" wrote:

> Hi,
> I'm running SQL 2000 and server quite often (3 times within the last week)
> fails on insert operation with the messages like:
> "Attempt to fetch logical page (1:202685) in database 'tempdb' belongs to
> object '-948063835' not to object #tmp
> Connection Broken"
> Similar messages were for the tables in the completely different databases.
> Does not look like this is directly related to the table's size.
> Thanks
>

MSG 605 - Server having problems with fetching the logical pages

Hi,
I'm running SQL 2000 and server quite often (3 times within the last week)
fails on insert operation with the messages like:
"Attempt to fetch logical page (1:202685) in database 'tempdb' belongs to
object '-948063835' not to object #tmp
Connection Broken"
Similar messages were for the tables in the completely different databases.
Does not look like this is directly related to the table's size.
ThanksHi
Looks like corruption. Either your RAM or disk could ber causing this.
Run DBCC CHECKDB againt all your DB's on the server and see what the output
is.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"alex" wrote:
> Hi,
> I'm running SQL 2000 and server quite often (3 times within the last week)
> fails on insert operation with the messages like:
> "Attempt to fetch logical page (1:202685) in database 'tempdb' belongs to
> object '-948063835' not to object #tmp
> Connection Broken"
> Similar messages were for the tables in the completely different databases.
> Does not look like this is directly related to the table's size.
> Thanks
>|||Also check your backups (restore to a different sql server, run dbcc checkdb
on those). Find a backup that does not contain the corruption.
You may lose data.
If DBCC reports the minimal repair level is "repair_allow_data_loss" it's
not kidding.
If you run that, be prepared to lose some data. It might be a row, a page,
whole tables. There's really no telling.
Going back to a known good state is often a better alternative. At least
you know what data you lost.
:D
Donna Lambert
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Looks like corruption. Either your RAM or disk could ber causing this.
> Run DBCC CHECKDB againt all your DB's on the server and see what the output
> is.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "alex" wrote:
> > Hi,
> > I'm running SQL 2000 and server quite often (3 times within the last week)
> > fails on insert operation with the messages like:
> >
> > "Attempt to fetch logical page (1:202685) in database 'tempdb' belongs to
> > object '-948063835' not to object #tmp
> >
> > Connection Broken"
> >
> > Similar messages were for the tables in the completely different databases.
> >
> > Does not look like this is directly related to the table's size.
> >
> > Thanks
> >
> >|||Thanks. Did that and got:
....
CHECKDB found 0 allocation errors and 0 consistency errors in database
'tempdb'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Looks like database itself is OK. Can the problem be a result of some
application's
activities like the following scenario:
main stored procedure calls, withing a cycle, underlying stored procedure
and this
procedure contains the following:
create table #tmp(....)
....
drop table #tmp
It looks like after few repetitions of this scenario (number of the
iterations varies)
breakdown occurs. After replacement of the temp table with a regular one,
situation seems to be remedied.
Can it be that tempdb may have problems with a repeated re-creation of a
dropped temp. table within a single session?
Thanks
"Donna Lambert" wrote:
> Also check your backups (restore to a different sql server, run dbcc checkdb
> on those). Find a backup that does not contain the corruption.
> You may lose data.
> If DBCC reports the minimal repair level is "repair_allow_data_loss" it's
> not kidding.
> If you run that, be prepared to lose some data. It might be a row, a page,
> whole tables. There's really no telling.
> Going back to a known good state is often a better alternative. At least
> you know what data you lost.
> :D
> Donna Lambert
>
> "Mike Epprecht (SQL MVP)" wrote:
> > Hi
> >
> > Looks like corruption. Either your RAM or disk could ber causing this.
> >
> > Run DBCC CHECKDB againt all your DB's on the server and see what the output
> > is.
> >
> > Regards
> > --
> > Mike Epprecht, Microsoft SQL Server MVP
> > Zurich, Switzerland
> >
> > MVP Program: http://www.microsoft.com/mvp
> >
> > Blog: http://www.msmvps.com/epprecht/
> >
> >
> >
> > "alex" wrote:
> >
> > > Hi,
> > > I'm running SQL 2000 and server quite often (3 times within the last week)
> > > fails on insert operation with the messages like:
> > >
> > > "Attempt to fetch logical page (1:202685) in database 'tempdb' belongs to
> > > object '-948063835' not to object #tmp
> > >
> > > Connection Broken"
> > >
> > > Similar messages were for the tables in the completely different databases.
> > >
> > > Does not look like this is directly related to the table's size.
> > >
> > > Thanks
> > >
> > >|||> Can the problem be a result of some
> application's
> activities like the following scenario:
Basically, no. No TSQL code should be able to corrupt a database. If you can find such code that
reproduces, you have found a bug in SQL Server. You should investigate for hardware errors. See
general recommendations at http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp. Also,
remember that tempdb is re-created at each startup...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"alex" <alex@.discussions.microsoft.com> wrote in message
news:8D8B81BF-1C2A-4088-ACAE-1609A0FC5223@.microsoft.com...
> Thanks. Did that and got:
> ....
> CHECKDB found 0 allocation errors and 0 consistency errors in database
> 'tempdb'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Looks like database itself is OK. Can the problem be a result of some
> application's
> activities like the following scenario:
> main stored procedure calls, withing a cycle, underlying stored procedure
> and this
> procedure contains the following:
> create table #tmp(....)
> ....
> drop table #tmp
> It looks like after few repetitions of this scenario (number of the
> iterations varies)
> breakdown occurs. After replacement of the temp table with a regular one,
> situation seems to be remedied.
> Can it be that tempdb may have problems with a repeated re-creation of a
> dropped temp. table within a single session?
> Thanks
> "Donna Lambert" wrote:
>> Also check your backups (restore to a different sql server, run dbcc checkdb
>> on those). Find a backup that does not contain the corruption.
>> You may lose data.
>> If DBCC reports the minimal repair level is "repair_allow_data_loss" it's
>> not kidding.
>> If you run that, be prepared to lose some data. It might be a row, a page,
>> whole tables. There's really no telling.
>> Going back to a known good state is often a better alternative. At least
>> you know what data you lost.
>> :D
>> Donna Lambert
>>
>> "Mike Epprecht (SQL MVP)" wrote:
>> > Hi
>> >
>> > Looks like corruption. Either your RAM or disk could ber causing this.
>> >
>> > Run DBCC CHECKDB againt all your DB's on the server and see what the output
>> > is.
>> >
>> > Regards
>> > --
>> > Mike Epprecht, Microsoft SQL Server MVP
>> > Zurich, Switzerland
>> >
>> > MVP Program: http://www.microsoft.com/mvp
>> >
>> > Blog: http://www.msmvps.com/epprecht/
>> >
>> >
>> >
>> > "alex" wrote:
>> >
>> > > Hi,
>> > > I'm running SQL 2000 and server quite often (3 times within the last week)
>> > > fails on insert operation with the messages like:
>> > >
>> > > "Attempt to fetch logical page (1:202685) in database 'tempdb' belongs to
>> > > object '-948063835' not to object #tmp
>> > >
>> > > Connection Broken"
>> > >
>> > > Similar messages were for the tables in the completely different databases.
>> > >
>> > > Does not look like this is directly related to the table's size.
>> > >
>> > > Thanks
>> > >
>> > >|||Thanks but I did not say that DB was corrupted: DBCC CHECKDATABASE indicated
that there are no problems.
Is corruption the only reason for getting Msg 605?
"Tibor Karaszi" wrote:
> > Can the problem be a result of some
> > application's
> > activities like the following scenario:
> Basically, no. No TSQL code should be able to corrupt a database. If you can find such code that
> reproduces, you have found a bug in SQL Server. You should investigate for hardware errors. See
> general recommendations at http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp. Also,
> remember that tempdb is re-created at each startup...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "alex" <alex@.discussions.microsoft.com> wrote in message
> news:8D8B81BF-1C2A-4088-ACAE-1609A0FC5223@.microsoft.com...
> > Thanks. Did that and got:
> > ....
> > CHECKDB found 0 allocation errors and 0 consistency errors in database
> > 'tempdb'.
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> >
> > Looks like database itself is OK. Can the problem be a result of some
> > application's
> > activities like the following scenario:
> >
> > main stored procedure calls, withing a cycle, underlying stored procedure
> > and this
> > procedure contains the following:
> >
> > create table #tmp(....)
> > ....
> > drop table #tmp
> >
> > It looks like after few repetitions of this scenario (number of the
> > iterations varies)
> > breakdown occurs. After replacement of the temp table with a regular one,
> > situation seems to be remedied.
> >
> > Can it be that tempdb may have problems with a repeated re-creation of a
> > dropped temp. table within a single session?
> >
> > Thanks
> >
> > "Donna Lambert" wrote:
> >
> >> Also check your backups (restore to a different sql server, run dbcc checkdb
> >> on those). Find a backup that does not contain the corruption.
> >> You may lose data.
> >> If DBCC reports the minimal repair level is "repair_allow_data_loss" it's
> >> not kidding.
> >> If you run that, be prepared to lose some data. It might be a row, a page,
> >> whole tables. There's really no telling.
> >> Going back to a known good state is often a better alternative. At least
> >> you know what data you lost.
> >> :D
> >> Donna Lambert
> >>
> >>
> >> "Mike Epprecht (SQL MVP)" wrote:
> >>
> >> > Hi
> >> >
> >> > Looks like corruption. Either your RAM or disk could ber causing this.
> >> >
> >> > Run DBCC CHECKDB againt all your DB's on the server and see what the output
> >> > is.
> >> >
> >> > Regards
> >> > --
> >> > Mike Epprecht, Microsoft SQL Server MVP
> >> > Zurich, Switzerland
> >> >
> >> > MVP Program: http://www.microsoft.com/mvp
> >> >
> >> > Blog: http://www.msmvps.com/epprecht/
> >> >
> >> >
> >> >
> >> > "alex" wrote:
> >> >
> >> > > Hi,
> >> > > I'm running SQL 2000 and server quite often (3 times within the last week)
> >> > > fails on insert operation with the messages like:
> >> > >
> >> > > "Attempt to fetch logical page (1:202685) in database 'tempdb' belongs to
> >> > > object '-948063835' not to object #tmp
> >> > >
> >> > > Connection Broken"
> >> > >
> >> > > Similar messages were for the tables in the completely different databases.
> >> > >
> >> > > Does not look like this is directly related to the table's size.
> >> > >
> >> > > Thanks
> >> > >
> >> > >
>|||Probably the corruption disappeared since objects are created and dropped all the time in tempdb.
Also, you possibly re-started SQL server in between so tempdb was rebuilt. However, you have had a
corruption, and that it something you should investigate further.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"alex" <alex@.discussions.microsoft.com> wrote in message
news:E28034E7-9F14-46EB-B491-CB690F52E0D0@.microsoft.com...
> Thanks but I did not say that DB was corrupted: DBCC CHECKDATABASE indicated
> that there are no problems.
> Is corruption the only reason for getting Msg 605?
> "Tibor Karaszi" wrote:
>> > Can the problem be a result of some
>> > application's
>> > activities like the following scenario:
>> Basically, no. No TSQL code should be able to corrupt a database. If you can find such code that
>> reproduces, you have found a bug in SQL Server. You should investigate for hardware errors. See
>> general recommendations at http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp. Also,
>> remember that tempdb is re-created at each startup...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "alex" <alex@.discussions.microsoft.com> wrote in message
>> news:8D8B81BF-1C2A-4088-ACAE-1609A0FC5223@.microsoft.com...
>> > Thanks. Did that and got:
>> > ....
>> > CHECKDB found 0 allocation errors and 0 consistency errors in database
>> > 'tempdb'.
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator.
>> >
>> > Looks like database itself is OK. Can the problem be a result of some
>> > application's
>> > activities like the following scenario:
>> >
>> > main stored procedure calls, withing a cycle, underlying stored procedure
>> > and this
>> > procedure contains the following:
>> >
>> > create table #tmp(....)
>> > ....
>> > drop table #tmp
>> >
>> > It looks like after few repetitions of this scenario (number of the
>> > iterations varies)
>> > breakdown occurs. After replacement of the temp table with a regular one,
>> > situation seems to be remedied.
>> >
>> > Can it be that tempdb may have problems with a repeated re-creation of a
>> > dropped temp. table within a single session?
>> >
>> > Thanks
>> >
>> > "Donna Lambert" wrote:
>> >
>> >> Also check your backups (restore to a different sql server, run dbcc checkdb
>> >> on those). Find a backup that does not contain the corruption.
>> >> You may lose data.
>> >> If DBCC reports the minimal repair level is "repair_allow_data_loss" it's
>> >> not kidding.
>> >> If you run that, be prepared to lose some data. It might be a row, a page,
>> >> whole tables. There's really no telling.
>> >> Going back to a known good state is often a better alternative. At least
>> >> you know what data you lost.
>> >> :D
>> >> Donna Lambert
>> >>
>> >>
>> >> "Mike Epprecht (SQL MVP)" wrote:
>> >>
>> >> > Hi
>> >> >
>> >> > Looks like corruption. Either your RAM or disk could ber causing this.
>> >> >
>> >> > Run DBCC CHECKDB againt all your DB's on the server and see what the output
>> >> > is.
>> >> >
>> >> > Regards
>> >> > --
>> >> > Mike Epprecht, Microsoft SQL Server MVP
>> >> > Zurich, Switzerland
>> >> >
>> >> > MVP Program: http://www.microsoft.com/mvp
>> >> >
>> >> > Blog: http://www.msmvps.com/epprecht/
>> >> >
>> >> >
>> >> >
>> >> > "alex" wrote:
>> >> >
>> >> > > Hi,
>> >> > > I'm running SQL 2000 and server quite often (3 times within the last week)
>> >> > > fails on insert operation with the messages like:
>> >> > >
>> >> > > "Attempt to fetch logical page (1:202685) in database 'tempdb' belongs to
>> >> > > object '-948063835' not to object #tmp
>> >> > >
>> >> > > Connection Broken"
>> >> > >
>> >> > > Similar messages were for the tables in the completely different databases.
>> >> > >
>> >> > > Does not look like this is directly related to the table's size.
>> >> > >
>> >> > > Thanks
>> >> > >
>> >> > >
>>|||Also, CHECKDB doesn't check everything in TEMPDB so may not pick up on the
problem you have. You also can't run repair on TEMPDB as its impossible to
put TEMPDB into single_user mode (prerequisite for running repair).
If you can repro the problem, you should call product support
(http://support.microsoft.com)
Thanks
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eBmRC1EbFHA.2420@.TK2MSFTNGP12.phx.gbl...
> Probably the corruption disappeared since objects are created and dropped
> all the time in tempdb. Also, you possibly re-started SQL server in
> between so tempdb was rebuilt. However, you have had a corruption, and
> that it something you should investigate further.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "alex" <alex@.discussions.microsoft.com> wrote in message
> news:E28034E7-9F14-46EB-B491-CB690F52E0D0@.microsoft.com...
>> Thanks but I did not say that DB was corrupted: DBCC CHECKDATABASE
>> indicated
>> that there are no problems.
>> Is corruption the only reason for getting Msg 605?
>> "Tibor Karaszi" wrote:
>> > Can the problem be a result of some
>> > application's
>> > activities like the following scenario:
>> Basically, no. No TSQL code should be able to corrupt a database. If you
>> can find such code that
>> reproduces, you have found a bug in SQL Server. You should investigate
>> for hardware errors. See
>> general recommendations at
>> http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp. Also,
>> remember that tempdb is re-created at each startup...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "alex" <alex@.discussions.microsoft.com> wrote in message
>> news:8D8B81BF-1C2A-4088-ACAE-1609A0FC5223@.microsoft.com...
>> > Thanks. Did that and got:
>> > ....
>> > CHECKDB found 0 allocation errors and 0 consistency errors in database
>> > 'tempdb'.
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator.
>> >
>> > Looks like database itself is OK. Can the problem be a result of some
>> > application's
>> > activities like the following scenario:
>> >
>> > main stored procedure calls, withing a cycle, underlying stored
>> > procedure
>> > and this
>> > procedure contains the following:
>> >
>> > create table #tmp(....)
>> > ....
>> > drop table #tmp
>> >
>> > It looks like after few repetitions of this scenario (number of the
>> > iterations varies)
>> > breakdown occurs. After replacement of the temp table with a regular
>> > one,
>> > situation seems to be remedied.
>> >
>> > Can it be that tempdb may have problems with a repeated re-creation of
>> > a
>> > dropped temp. table within a single session?
>> >
>> > Thanks
>> >
>> > "Donna Lambert" wrote:
>> >
>> >> Also check your backups (restore to a different sql server, run dbcc
>> >> checkdb
>> >> on those). Find a backup that does not contain the corruption.
>> >> You may lose data.
>> >> If DBCC reports the minimal repair level is "repair_allow_data_loss"
>> >> it's
>> >> not kidding.
>> >> If you run that, be prepared to lose some data. It might be a row, a
>> >> page,
>> >> whole tables. There's really no telling.
>> >> Going back to a known good state is often a better alternative. At
>> >> least
>> >> you know what data you lost.
>> >> :D
>> >> Donna Lambert
>> >>
>> >>
>> >> "Mike Epprecht (SQL MVP)" wrote:
>> >>
>> >> > Hi
>> >> >
>> >> > Looks like corruption. Either your RAM or disk could ber causing
>> >> > this.
>> >> >
>> >> > Run DBCC CHECKDB againt all your DB's on the server and see what
>> >> > the output
>> >> > is.
>> >> >
>> >> > Regards
>> >> > --
>> >> > Mike Epprecht, Microsoft SQL Server MVP
>> >> > Zurich, Switzerland
>> >> >
>> >> > MVP Program: http://www.microsoft.com/mvp
>> >> >
>> >> > Blog: http://www.msmvps.com/epprecht/
>> >> >
>> >> >
>> >> >
>> >> > "alex" wrote:
>> >> >
>> >> > > Hi,
>> >> > > I'm running SQL 2000 and server quite often (3 times within the
>> >> > > last week)
>> >> > > fails on insert operation with the messages like:
>> >> > >
>> >> > > "Attempt to fetch logical page (1:202685) in database 'tempdb'
>> >> > > belongs to
>> >> > > object '-948063835' not to object #tmp
>> >> > >
>> >> > > Connection Broken"
>> >> > >
>> >> > > Similar messages were for the tables in the completely different
>> >> > > databases.
>> >> > >
>> >> > > Does not look like this is directly related to the table's size.
>> >> > >
>> >> > > Thanks
>> >> > >
>> >> > >
>>
>

msg 4820 Bulk insert error -- "Unknown version of format file"

Hi all,

ISSUE:
====================
In SQL 2005 (sp2) I get the following error when preforming a bulk
insert with an associated xml format file:
"Could not bulk insert. Unknown version of format file"

Question:
====================
I am unsure what they mean by "unknown version". Specifically the
format file in question was created using bcp. Also the entire table
scenario was created from a msdn example.

Any ideas? have you seen this before?

NOTE: i can reproduce this issue outside the example but will refer to
msdn considering it is simple and easily reproducible.

Scenario
====================
I can reproduce this error with the BULK INSERT example discussed on
msdn (example A)
http://msdn2.microsoft.com/en-us/library/ms191234.aspx

TO REPRODUCE:

* In short the table structure is:
Person (Age int, FirstName varchar(20), LastName varchar(30))

* Data File Template:
Age<tab>Firstname<tab>Lastname<return>

* xml file format from bcp (and described on msdn)
<?xml version="1.0"?>
<BCPFORMAT
xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\t"
MAX_LENGTH="12"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t"
MAX_LENGTH="20" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\r\n"
MAX_LENGTH="30"
COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="age" xsi:type="SQLINT"/>
<COLUMN SOURCE="2" NAME="firstname" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="3" NAME="lastname" xsi:type="SQLVARYCHAR"/>
</ROW>
</BCPFORMAT>

* Here is some the actual sql statement that pulls this all together
BULK INSERT mytestnames
FROM 'C:\datatest\exampledata-c.Dat'
WITH (FORMATFILE = 'C:\datatest\examplefmt.Fmt');

Thanks in advanced for any feedback.

Cheers!

Hi,

plz can you show your XML format file "examplefmt.Fmt".

there must be something like this:

examplefmt.Fmt

...

2 SQLCHAR 0 100 "," 2 firstname SQL_Latin1_General_CP1_CI_AS
...

msg 4820 Bulk insert error -- "Unknown version of format file"

Hi all,

ISSUE:
====================
In SQL 2005 (sp2) I get the following error when preforming a bulk
insert with an associated xml format file:
"Could not bulk insert. Unknown version of format file"

Question:
====================
I am unsure what they mean by "unknown version". Specifically the
format file in question was created using bcp. Also the entire table
scenario was created from a msdn example.

Any ideas? have you seen this before?

NOTE: i can reproduce this issue outside the example but will refer to
msdn considering it is simple and easily reproducible.

Scenario
====================
I can reproduce this error with the BULK INSERT example discussed on
msdn (example A)
http://msdn2.microsoft.com/en-us/library/ms191234.aspx

TO REPRODUCE:

* In short the table structure is:
Person (Age int, FirstName varchar(20), LastName varchar(30))

* Data File Template:
Age<tab>Firstname<tab>Lastname<return>

* xml file format from bcp (and described on msdn)
<?xml version="1.0"?>
<BCPFORMAT
xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\t"
MAX_LENGTH="12"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t"
MAX_LENGTH="20" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\r\n"
MAX_LENGTH="30"
COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="age" xsi:type="SQLINT"/>
<COLUMN SOURCE="2" NAME="firstname" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="3" NAME="lastname" xsi:type="SQLVARYCHAR"/>
</ROW>
</BCPFORMAT>

* Here is some the actual sql statement that pulls this all together
BULK INSERT mytestnames
FROM 'C:\datatest\exampledata-c.Dat'
WITH (FORMATFILE = 'C:\datatest\examplefmt.Fmt');

Thanks in advanced for any feedback.

Cheers!

Hi,

plz can you show your XML format file "examplefmt.Fmt".

there must be something like this:

examplefmt.Fmt

...

2 SQLCHAR 0 100 "," 2 firstname SQL_Latin1_General_CP1_CI_AS
...

Msg 208 / Temp Tables

I'm Using SQL 2000.
I issue the following commands in the Query Analyzer:
Select * from Prod into #Temp1
Insert into #Temp1 Select * from OldProd
I Get the following error:
Server: Msg 208, Level 16, State 1, Line 121
Invalid object name '#Temp1'.
Any ideas?
Thanks,
KFTo create the table using SELECT...INTO the syntax should be:
Select * into #Temp1 from Prod
Then run
Insert into #Temp1 Select * from OldProd
- Vishal|||Below executes OK on my machine...
Select * into #Temp1 from authors
Insert into #Temp1 Select * from authors
If you post a repro which we can execute, we might be able to help...
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Kflash" <kflash@.ameriflashtech.com> wrote in message
news:1f36401c389c5$9b9bfdc0$a601280a@.phx.gbl...
> I know. I got the syntax wrong on the original post but
> its right in the Query analyzer.
> When I run these commands:
> Select * into #Temp1 from Prod
> Insert into #Temp1 Select * from OldProd
> I get this Result:
> Server: Msg 208, Level 16, State 1, Line 121
> Invalid object name '#Temp1'.
> If I highlight the above commands and run them by
> themselves, it returns the correct result.
> It seems that Microsoft has issues with Temp tables. I'm
> hoping that there is a work around.
> Keith
>
> >--Original Message--
> >To create the table using SELECT...INTO the syntax
> should be:
> >
> >Select * into #Temp1 from Prod
> >
> >Then run
> >
> >Insert into #Temp1 Select * from OldProd
> >
> >
> >--
> >- Vishal
> >
> >
> >.
> >

MSDTC on server xx is unavailable

Hi:
We have 2 sql server(both sql2000) and using update/insert
triggers to replicate between the 2 servers. the
replication has been working and now We got this error
when doing an update:
ole/db provider returned message, new transaction cannot
enlist in the specified transaction coordinator. the
operation could not be performed because the oledb
provider 'sqloledb' was unable to begin a distributed
transaction.
So I restarted msdtc service on both sql servers, now when
running an update, I got msdtc on server
destinationservernm is unavailable, but both dtc services
are started. Any thoughts? thanks.Which OS version?
--
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2003 All rights reserved.
"dean" <dean@.hotmail.com> wrote in message
news:016b01c377c6$693f1e40$a401280a@.phx.gbl...
> Hi:
> We have 2 sql server(both sql2000) and using update/insert
> triggers to replicate between the 2 servers. the
> replication has been working and now We got this error
> when doing an update:
> ole/db provider returned message, new transaction cannot
> enlist in the specified transaction coordinator. the
> operation could not be performed because the oledb
> provider 'sqloledb' was unable to begin a distributed
> transaction.
> So I restarted msdtc service on both sql servers, now when
> running an update, I got msdtc on server
> destinationservernm is unavailable, but both dtc services
> are started. Any thoughts? thanks.

MSDTC is unavailable.

The following code:

DECLARE @.tsql varchar(1000)
CREATE TABLE #Test (...)
SET @.tsql = 'SELECT * FROM OPENQUERY(MyDataSource, ...)'
INSERT INTO #Test EXEC(@.tsql)

generates the following error message at the insert statement:

Server: Msg 8501, Level 16, State 3, Line 4
MSDTC on server 'MyComputer' is unavailable.

Why?

Thanks,
Tony Perovic
Compumation, Inc.Starting the DTC service caused the problem to go away but why does it need
it?

"tperovic" <tonyperovic@.yahoo.com> wrote in message
news:_bfLc.5403$iK.4645@.newsread2.news.atl.earthli nk.net...
> The following code:
> DECLARE @.tsql varchar(1000)
> CREATE TABLE #Test (...)
> SET @.tsql = 'SELECT * FROM OPENQUERY(MyDataSource, ...)'
> INSERT INTO #Test EXEC(@.tsql)
> generates the following error message at the insert statement:
> Server: Msg 8501, Level 16, State 3, Line 4
> MSDTC on server 'MyComputer' is unavailable.
> Why?
> Thanks,
> Tony Perovic
> Compumation, Inc.|||tperovic (tonyperovic@.yahoo.com) writes:
> Starting the DTC service caused the problem to go away but why does it
> need it?
>> DECLARE @.tsql varchar(1000)
>> CREATE TABLE #Test (...)
>> SET @.tsql = 'SELECT * FROM OPENQUERY(MyDataSource, ...)'
>> INSERT INTO #Test EXEC(@.tsql)

Because you call the other server in the context of a transaction,
defined by the INSERT statement.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

MSDTC forcing distributed transaction

We're performing the following query from a stored procedure on our
SQL Server database:
Begin Transaction
insert into local SQL Server table
select *
from OPENQUERY(DB2 Linked Server, 'select * from DB2 Table')
Commit Transaction
When this runs, SQL Server escalates it to a distributed transaction
and MSDTC manages the transaction.
The problem is that this transaction is not always successfully
committed on the DB2 side. As part of trying to figure out why, we're
trying to figure out why this is even a distributed transaction, since
the only update is occuring locally. Why does this query force a
distributed transaction? Is there some way to reconfigure it so that
a distributed transaction is not forced?
Kees VanTilburg
VanTilburg EnterprisesHello kees,
If I learned correctly that you want to know why the following sql
statements use the distributed transaction.
Begin Transaction
insert into local SQL Server table
select * from OPENQUERY(DB2 Linked Server, 'select * from DB2 Table')
Commit Transaction
As far as I know, When you execute a distributed query while in a local
transaction. If the OLE DB data source supports the ITransactionJoin
interface, the transaction is promoted to a distributed transaction, even
if the query is a read-only query. Therefore, I am afraid there is not an
easy way to reconfigure so that a distributed transaction is not used in
this situation.
I found the following article for your reference.
274348 INF: Distributed Queries Executed Within a Trigger Enlist MSDTC
http://support.microsoft.com/?id=274348
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.

MSDTC

Hi,

I am running SQl 7.0 (SP4) on Windows XP Professional (SP2). Whenever I try an insert/update type of activity, the System returns the following message :

Server: Msg 8501, Level 16, State 3, Line 2
MSDTC on server 'SERVER' is unavailable.

However, all the required services including MSDTC are running on the System.

Please help ASAP.

Thanks & Regards - Rajesh Pathak

It sounds like DTC is turned off on the other machine. Can you confirm that it is on and try again?

Thanks,
Sam Lester (MSFT)

|||Hello Sam,

Thanks for the prompt reply. As a matter of fact, trying any operation on the Server System which itself runs SQL 7.0 returns the MSDTC not running message. If it works on the Server System then I can try debugging problems on other Client Systems.

Kindly let me have probable resolution to this.

Thanks & Regards - Rajesh Pathak