Microsoft SQL Server 2005 RTM merge replication issues

We are trying to implement merge replication between 2 servers. Database size is around 11GB.

When I tried to create publication, snaphot agent is failing with following errror.

Error: 14151, Severity: 18, State: 1.
Replication-Replication Snapshot Subsystem: agent NBTENTSQL1X-PayDirectWeb-PayDirectPub-2 failed. The
replication agent had encountered an exception.
Source: Replication
Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentSqlException
Exception Message: String or binary data would be truncated.
Cannot find the object 'MSmerge_sel_4D388

The table where the snapshot is breaking contains around 169 columns. Can someone please help me get through this error ?

Are you using vertical partitioning to replicate subset of the columns of the table? You may be hitting an known issue in Microsoft SQL Server 2005 where the list of column names for the table is longer than 4K and hence gets truncated in an internal proc.

|||

Thanks Sudarshan, your reply is very helpful. No we are not using vertical partitioning.

If this is a known issue, did Microsoft have any immediate fix for it or we have wait for SQL2K5 SP2 ?

|||Please wait for SQL2K5 SP2. You can install the latest CTPs availalbe and test out that your scenarios works. If not, please reply back and we can take a look further.|||This is a known issue in SQL2K5 SP1 which is fixed in SP2.|||Does the same limitation apply to the complexity of joined tables in the filter? I'm getting an error in the snapshot agent, saying:

String or

binary data would be truncated.

Cannot find

the object 'MSmerge_ctsv_2C675AF5938E4EA1B7D0DCFF9ECAD9EB', because it does not

exist or you do not have permission.

sp_MS_marksystemobject:

Invalid object name '[dbo].[MSmerge_upd_2C675AF5938E4EA1B7

But this only happens when I add too many joined tables to the filter section of the publication.

Dan

Microsoft SQL Server 2005 RTM merge replication issues

We are trying to implement merge replication between 2 servers. Database size is around 11GB.

When I tried to create publication, snaphot agent is failing with following errror.

Error: 14151, Severity: 18, State: 1.
Replication-Replication Snapshot Subsystem: agent NBTENTSQL1X-PayDirectWeb-PayDirectPub-2 failed. The
replication agent had encountered an exception.
Source: Replication
Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentSqlException
Exception Message: String or binary data would be truncated.
Cannot find the object 'MSmerge_sel_4D388

The table where the snapshot is breaking contains around 169 columns. Can someone please help me get through this error ?

Are you using vertical partitioning to replicate subset of the columns of the table? You may be hitting an known issue in Microsoft SQL Server 2005 where the list of column names for the table is longer than 4K and hence gets truncated in an internal proc.

|||

Thanks Sudarshan, your reply is very helpful. No we are not using vertical partitioning.

If this is a known issue, did Microsoft have any immediate fix for it or we have wait for SQL2K5 SP2 ?

|||Please wait for SQL2K5 SP2. You can install the latest CTPs availalbe and test out that your scenarios works. If not, please reply back and we can take a look further.|||This is a known issue in SQL2K5 SP1 which is fixed in SP2.|||Does the same limitation apply to the complexity of joined tables in the filter? I'm getting an error in the snapshot agent, saying:

String or

binary data would be truncated.

Cannot find

the object 'MSmerge_ctsv_2C675AF5938E4EA1B7D0DCFF9ECAD9EB', because it does not

exist or you do not have permission.

sp_MS_marksystemobject:

Invalid object name '[dbo].[MSmerge_upd_2C675AF5938E4EA1B7

But this only happens when I add too many joined tables to the filter section of the publication.

Dan

Microsoft Security Bulletin MS06-071 ROLLBACK

Hi,
I am producing a Rollback plan in case I find any issue with MS06-071 hotfix
installation. We have several SQL 2005 Servers so I need to have this
rollback plan ready for the day we install this hotfix.
This is a hotfix for MSXML 6.0 which is needed for SQL 2005 Server. If
something goes wrong with the SQL server after I install this hotfix and I
uninstall this hotfix it automatically uninstall MSXML 6.0 as well.
My question is : do I have to install the whole SQL server 2005 again if I
uninstall this hotfix and therefore I uninstall MSXML 6.0? would it be enough
if I install MSXML 6.0 afterwards?
Thank you for your help
Best regards,
Juanjo
Hi Juanjo,
If you do need to uninstall MSXML6 after applying MS06-071 you can reinstall
MSXML6 MS06-061 or MSXML6 RTM directly without reinstalling SQL 2005.
However, most likely your SQL Server 2005 will not function until MSXML6 is
re-installed.
You can download MSXML6-061 from here -
http://www.microsoft.com/downloads/details.aspx?FamilyId=fd513435-fa6d-407c-bedc-5fd03e5b7d6c
or MSXML6 RTM from here -
http://www.microsoft.com/downloads/details.aspx?FamilyID=993C0BCF-3BCF-4009-BE21-27E85E1857B1&displaylang=en
However I hope MS06-071 rollout goes smoothly for you - if you have any
trouble please come to our blog site http://blogs.msdn.com/xmlteam and click
on the email link to let us know what's going on.
Thanks,
Adam Wiener [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Juanjo" <Juanjo@.discussions.microsoft.com> wrote in message
news:4F2BA3B9-0569-46A3-BB66-50FA01AA15F6@.microsoft.com...
> Hi,
> I am producing a Rollback plan in case I find any issue with MS06-071
> hotfix
> installation. We have several SQL 2005 Servers so I need to have this
> rollback plan ready for the day we install this hotfix.
> This is a hotfix for MSXML 6.0 which is needed for SQL 2005 Server. If
> something goes wrong with the SQL server after I install this hotfix and I
> uninstall this hotfix it automatically uninstall MSXML 6.0 as well.
> My question is : do I have to install the whole SQL server 2005 again if I
> uninstall this hotfix and therefore I uninstall MSXML 6.0? would it be
> enough
> if I install MSXML 6.0 afterwards?
> Thank you for your help
> Best regards,
> Juanjo

Microsoft Security Bulletin MS06-071 ROLLBACK

Hi,
I am producing a Rollback plan in case I find any issue with MS06-071 hotfix
installation. We have several SQL 2005 Servers so I need to have this
rollback plan ready for the day we install this hotfix.
This is a hotfix for MSXML 6.0 which is needed for SQL 2005 Server. If
something goes wrong with the SQL server after I install this hotfix and I
uninstall this hotfix it automatically uninstall MSXML 6.0 as well.
My question is : do I have to install the whole SQL server 2005 again if I
uninstall this hotfix and therefore I uninstall MSXML 6.0? would it be enoug
h
if I install MSXML 6.0 afterwards?
Thank you for your help
Best regards,
JuanjoHi Juanjo,
If you do need to uninstall MSXML6 after applying MS06-071 you can reinstall
MSXML6 MS06-061 or MSXML6 RTM directly without reinstalling SQL 2005.
However, most likely your SQL Server 2005 will not function until MSXML6 is
re-installed.
You can download MSXML6-061 from here -
http://www.microsoft.com/downloads/.../>
fd03e5b7d6c
or MSXML6 RTM from here -
http://www.microsoft.com/downloads/...&displaylang=en
However I hope MS06-071 rollout goes smoothly for you - if you have any
trouble please come to our blog site http://blogs.msdn.com/xmlteam and click
on the email link to let us know what's going on.
Thanks,
Adam Wiener [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Juanjo" <Juanjo@.discussions.microsoft.com> wrote in message
news:4F2BA3B9-0569-46A3-BB66-50FA01AA15F6@.microsoft.com...
> Hi,
> I am producing a Rollback plan in case I find any issue with MS06-071
> hotfix
> installation. We have several SQL 2005 Servers so I need to have this
> rollback plan ready for the day we install this hotfix.
> This is a hotfix for MSXML 6.0 which is needed for SQL 2005 Server. If
> something goes wrong with the SQL server after I install this hotfix and I
> uninstall this hotfix it automatically uninstall MSXML 6.0 as well.
> My question is : do I have to install the whole SQL server 2005 again if I
> uninstall this hotfix and therefore I uninstall MSXML 6.0? would it be
> enough
> if I install MSXML 6.0 afterwards?
> Thank you for your help
> Best regards,
> Juanjo

Microsoft OLE DB Provider for SQL Server error '80004005' on Load Balancing

I have posted this also in one forum. Somebody might have idea here.

We have four Windows 2003 advance servers with SP 3 configured on NLB.
Each of them has one NIC.
Recently we started to get frequent time-out error messages from web applications on those servers when they try to establish the connection with the SQL 2005. If you run web pages application to connect to sql server 10 times, 9 times you will get good connection results and 1 time you will get time-out.

Microsoft OLE DB Provider for SQL Server error '80004005'
Timeout expired
/common/mypage.asp, line 20

Connection to sql server is established with the connection string. Any idea how to solve this problem? Is there a connectionproblem? from NLB to Web to SQL?

We have tried doing some suggestions (e.g. LMHOST, IP, domain account for WEb to SQL connection, the Query Wait in SQL advance property.), but seems this timeout expired still exist

Thanks in advance...

We thought it was only in IIS and Firewall pooling connections. There were open connection threads on IIS, where in fact the firewall already disconnect it. When a new request from IIS is set, sometimes it uses the aged out connections. This was one of the cause. What we did is modify the timeout connection from firewall 1 minute more than the IIS timeout expiration. Though we still encounter timeout expired once in a while. Any idea?

|||

Another thing that might help to analyze this issue:

I tried to check the Profiler for the timeout expiration. On the Profiler, SQL grants login (AuditLogin) but kicked it out right away (AuditLogut) for that specific timeout connection. No duration cost.

Microsoft OLE DB Provider for SQL Server error ''80004005'' on Load Balancing

I have posted this also in one forum. Somebody might have idea here.

We have four Windows 2003 advance servers with SP 3 configured on NLB.
Each of them has one NIC.
Recently we started to get frequent time-out error messages from web applications on those servers when they try to establish the connection with the SQL 2005. If you run web pages application to connect to sql server 10 times, 9 times you will get good connection results and 1 time you will get time-out.

Microsoft OLE DB Provider for SQL Server error '80004005'
Timeout expired
/common/mypage.asp, line 20

Connection to sql server is established with the connection string. Any idea how to solve this problem? Is there a connectionproblem? from NLB to Web to SQL?

We have tried doing some suggestions (e.g. LMHOST, IP, domain account for WEb to SQL connection, the Query Wait in SQL advance property.), but seems this timeout expired still exist

Thanks in advance...

We thought it was only in IIS and Firewall pooling connections. There were open connection threads on IIS, where in fact the firewall already disconnect it. When a new request from IIS is set, sometimes it uses the aged out connections. This was one of the cause. What we did is modify the timeout connection from firewall 1 minute more than the IIS timeout expiration. Though we still encounter timeout expired once in a while. Any idea?

|||

Another thing that might help to analyze this issue:

I tried to check the Profiler for the timeout expiration. On the Profiler, SQL grants login (AuditLogin) but kicked it out right away (AuditLogut) for that specific timeout connection. No duration cost.

sql

MICROSOFT JET OLEDB 4.0?

hi i have another question.. is this connection provider supported on servers running under 64 bits? if not? which provider should i use instead? is there any service pack or upgrades for this ?

i think this is the problem running my job.. there's no other option left.. it is not permissions.. the path of the connection manager exists, the file destination does exist as well.. the curious thing is that my dts runs fine from SSIS but fails using the job..

do i need to have installed excel in my server to do this or any other component?

There is only 32-bit Jet provider, so you need to run the package using 32-bit DtExec.exe to use it. When scheduling job in Agent, select Operating System (CmdExec) step type, and the command to run is
"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DtExec.exe" /f "package-path"|||

thaaaaaaaanks thanks thaaaank youuuuuuuu this was the solution for all my problems... i own you one!!!!!

regards

|||

DTEXEC does not work well with Excel on my system

I am using DTEXEC utility to run the packages on 64-bit system. One of the packages has connection to Excel. The package runs fine from SSMS but return error “Class not registered” OLE DB error has occurred. Error code: 0x80040154.

The package is stored in MSDB. I use following syntax to run the package:

dtexec /sq DTSpkg

Any help?

|||Have you read the discussion above?

There is no 64-bit OLEDB provider, so you need to use 32-bit DTEXEC:
"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DtExec.exe" <rest of the arguments>|||

Hi Michael, Thanks for help. Connection to Excel works. Can you take a look on another problem bellow?

I use Flat File Connection manager in different package. The package creates a file on other server on completion. The package runs successfully from SSMS but returns error when run with dtexec /sq DTSpkg

Description: The file name "\\SERVER2\FTProot\PackageOK.RCV" specified in the connection was not valid.

MICROSOFT JET OLEDB 4.0?

hi i have another question.. is this connection provider supported on servers running under 64 bits? if not? which provider should i use instead? is there any service pack or upgrades for this ?

i think this is the problem running my job.. there's no other option left.. it is not permissions.. the path of the connection manager exists, the file destination does exist as well.. the curious thing is that my dts runs fine from SSIS but fails using the job..

do i need to have installed excel in my server to do this or any other component?

There is only 32-bit Jet provider, so you need to run the package using 32-bit DtExec.exe to use it. When scheduling job in Agent, select Operating System (CmdExec) step type, and the command to run is
"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DtExec.exe" /f "package-path"|||

thaaaaaaaanks thanks thaaaank youuuuuuuu this was the solution for all my problems... i own you one!!!!!

regards

|||

DTEXEC does not work well with Excel on my system

I am using DTEXEC utility to run the packages on 64-bit system. One of the packages has connection to Excel. The package runs fine from SSMS but return error “Class not registered” OLE DB error has occurred. Error code: 0x80040154.

The package is stored in MSDB. I use following syntax to run the package:

dtexec /sq DTSpkg

Any help?

|||Have you read the discussion above?

There is no 64-bit OLEDB provider, so you need to use 32-bit DTEXEC:
"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DtExec.exe" <rest of the arguments>|||

Hi Michael, Thanks for help. Connection to Excel works. Can you take a look on another problem bellow?

I use Flat File Connection manager in different package. The package creates a file on other server on completion. The package runs successfully from SSMS but returns error when run with dtexec /sq DTSpkg

Description: The file name "\\SERVER2\FTProot\PackageOK.RCV" specified in the connection was not valid.

Method EnumDatabaseMapping on Login SMO object takes 3 minutes

Hi,

The EnumDatabaseMapping call below takes upto 3 minutes on some SQL Servers whereas it takes hardly any time at all on others. What may cause such a long delay?

Regards,
Joginder Nahil
www.starprint2000.com

Private Sub OutputUserMapping(ByVal oLogin As Microsoft.SqlServer.Management.Smo.Login)

Dim colDatabaseMapping() As DatabaseMapping
colDatabaseMapping = oLogin.EnumDatabaseMappings

' Rest of the code has been deleted

End sub

Hi Joginder,

EnumDatabaseMappings is an expensive operation. SMO issues queries that iterate over all databases on the server and stores intermediate results in a temporary table. This might take time for a server with many databases and users, especially if under heavy load.

You can find out more by running SQL Server Profiler and inspecting the queries that are being sent to the server.

Arur Laksberg
SQL Server Team
Microsoft

|||

Hi Artur,

I am inclined to say that 3 minutes is a very long time in computer processing time (BTW my SQL Server is on the same computer as running the application and has 1GB memory + 3.6 Pentium 4 Processor+ there is nothing much else running) to enumerate just three databases AdventureWorks, Pubs and Northwind.

Regards,

Joginder Nahil
www.starprint2000.com

meta data cleanup

Hi
I am running a couple of sql 2000 SP3a servers with merge and snapshot
replication. One server acting as publisher and distributor and the rest
subscribers. On one of the server I have got the error below and have tried
most of the suggestions by msdn. This server has not crashed ever before or
any hardware problems. It has been running for a couple of months and no
problems. This has not happened no any of the other servers. Any
suggestions would be greatly appreciated as the only resolution I have left
is to bring up a new instance, setup replication and see if this would
resolve the issue. Thanx
Server: EASTSRV3
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[4/14/2005 12:57:26 PM]EASTSRV3.ICASData: {call sp_MSgetversion }
Percent Complete: 2
Connecting to Subscriber 'EASTSRV3'
Percent Complete: 3
Retrieving publication information
Percent Complete: 4
Retrieving subscription information
Percent Complete: 4
The merge process is cleaning up meta data in database 'HO_Master'.
Percent Complete: 4
The merge process cleaned up 0 row(s) in MSmerge_genhistory, 0 row(s) in
MSmerge_contents, and 0 row(s) in MSmerge_tombstone.
Percent Complete: 4
The merge process is cleaning up meta data in database 'ICASData'.
The merge process could not perform retention-based meta data cleanup in
database 'ICASData'.
Percent Complete: 0
The merge process could not perform retention-based meta data cleanup in
database 'ICASData'.
Percent Complete: 0
Category:NULL
Source: Merge Replication Provider
Number: -2147199467
Message: The merge process could not perform retention-based meta data
cleanup in database 'ICASData'.
Percent Complete: 0
Category:COMMAND
Source: Failed Command
Number: 0
Message: {call sp_mergemetadataretentioncleanup(?, ?, ?)}
Percent Complete: 0
Category:SQLSERVER
Source: EASTSRV3
Number: 11
Message: General network error. Check your network documentation.
Message posted via http://www.sqlmonster.com
Jayanthi from MS posted this before:
"The most common reason I can think of is that the query timed out. Sometime
retention based cleaup may need to cleanup a lot of rows and that can take
time. Rerunning the merge with a larger value for the querytimeout parameter
should fix this problem."
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Paul
Thanx for the reply.
I have played with the agent profiles aswell. From default to 1800. This
has been going on for like two days now no synch at all. I have tried to
reindex also as this was also one of the msdn suggestions.
Message posted via http://www.sqlmonster.com

Message: Missing end comment mark '*/'.

Hi,

I created a web syncronization merge replication ,both servers and clients are sql server 2005 and everything is fine with the settings , but while synchronization and after i received some sch files i got the following message at the subscribers on the initial snapshot operation:

2006-06-27 07:50:14.471 The schema script 'myfile_311.sch' could not be propagated to the subscriber.
2006-06-27 07:50:14.471 Category:NULL
Source: Merge Replication Provider
Number: -2147201001
Message: The schema script 'myfile_311.sch' could not be propagated to the subscriber.
2006-06-27 07:50:14.471 Category:NULL
Source: Microsoft SQL Native Client
Number: 113
Message: Missing end comment mark '*/'.

Any idea ?

Tarek Ghazali

SQL Server MVP

My guess is that myfile_311.sch contains a stored procedure|view|function with something like

/*

...

go

*/

There is a longstanding limitation in our batch parsing logic that will erroroneously recognize 'go's embedded in comments to be batch delimiters. The only workaround for this is to edit the script generated by the snapshot agent to remove the offending comment. We really hope that we can address this issue in the near future.

-Raymond

message could not be delivered errors in service broker

Hi,

I am using service broker in between two database servers. following is the way i am sending and receiving messages

Send

BEGIN TRAN
BEGIN DIALOG CONVERSATION @.handle
FROM SERVICE @.SendService
TO SERVICE @.ReceiveService
ON CONTRACT @.Contract
WITH LIFETIME = @.lifetime;

SEND ON CONVERSATION @.handle
MESSAGE TYPE @.xmlMessageType(@.xmlMessage);
COMMIT

Receive

BEGIN TRAN;
RECEIVE TOP(1) @.xmlMessage = message_body,
@.handle = conversation_handle,
@.message_type = message_type_name
FROM TransactionQueue;

-
-- Check to make sure a message was returned to process. In theory this should never happen.
-
IF @.@.rowcount = 1
BEGIN

IF @.message_type = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
BEGIN
END CONVERSATION @.handle;
COMMIT
RETURN 0
END

IF @.message_type = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
BEGIN
RAISERROR(N'Received error %s from service [Target]', 10, 1)
END CONVERSATION @.handle;
COMMIT
RETURN 0
END

SET @.sql = 'EXEC '+@.message_processor_name+' @.xml'

BEGIN TRAN
EXEC sp_executesql @.sql, N'@.xml XML', @.xml=@.xmlMessage
COMMIT TRAN
END CONVERSATION @.handle;
END
COMMIT

I see Messages are delivered to the target every thing working fine other than following errors which i am seeing in profiler.

1) "This message could not be delivered because the conversation endpoint has already been closed." I see this error on initiator end. Is it like ending conversation on initiator end when i get "EndDialog" send an acknowledgement, which cannot be recieved by target as it has already ended conversation.

2) "An error occurred while receiving data: '64(The specified network name is no longer available.)'." I don't have much idea about the reason for this error. But in profiler i see value for GUID is different for this error and the real message.

Let me know if you need any other information

---

Please let me know if I have missed anything in above post|||

Just wondering if you resolved this issue as I am having the same issues with very similar code.

If you have resolved this, could you share what you did please so that I can (hopefully) resolve my problems too?

Thanks in advance.

message could not be delivered errors in service broker

Hi,

I am using service broker in between two database servers. following is the way i am sending and receiving messages

Send

BEGIN TRAN
BEGIN DIALOG CONVERSATION @.handle
FROM SERVICE @.SendService
TO SERVICE @.ReceiveService
ON CONTRACT @.Contract
WITH LIFETIME = @.lifetime;

SEND ON CONVERSATION @.handle
MESSAGE TYPE @.xmlMessageType(@.xmlMessage);
COMMIT

Receive

BEGIN TRAN;
RECEIVE TOP(1) @.xmlMessage = message_body,
@.handle = conversation_handle,
@.message_type = message_type_name
FROM TransactionQueue;

-
-- Check to make sure a message was returned to process. In theory this should never happen.
-
IF @.@.rowcount = 1
BEGIN

IF @.message_type = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
BEGIN
END CONVERSATION @.handle;
COMMIT
RETURN 0
END

IF @.message_type = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
BEGIN
RAISERROR(N'Received error %s from service [Target]', 10, 1)
END CONVERSATION @.handle;
COMMIT
RETURN 0
END

SET @.sql = 'EXEC '+@.message_processor_name+' @.xml'

BEGIN TRAN
EXEC sp_executesql @.sql, N'@.xml XML', @.xml=@.xmlMessage
COMMIT TRAN
END CONVERSATION @.handle;
END
COMMIT

I see Messages are delivered to the target every thing working fine other than following errors which i am seeing in profiler.

1) "This message could not be delivered because the conversation endpoint has already been closed." I see this error on initiator end. Is it like ending conversation on initiator end when i get "EndDialog" send an acknowledgement, which cannot be recieved by target as it has already ended conversation.

2) "An error occurred while receiving data: '64(The specified network name is no longer available.)'." I don't have much idea about the reason for this error. But in profiler i see value for GUID is different for this error and the real message.

Let me know if you need any other information

---

Please let me know if I have missed anything in above post|||

Just wondering if you resolved this issue as I am having the same issues with very similar code.

If you have resolved this, could you share what you did please so that I can (hopefully) resolve my problems too?

Thanks in advance.