Problems creating subscription...

I am attempting to subscribe to a publication and I receive the
following message...
SQL 2005 & Win 2005 on both ends.
===================================
SQL Server could not create a subscription for Subscriber 'r99s-sqla'.
(New Subscription Wizard)
===================================
An exception occurred while executing a Transact-SQL statement or
batch. (Microsoft.SqlServer.ConnectionInfo)
Program Location:
at
Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(String
sqlCommand, ExecutionTypes executionType)
at
Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(String
sqlCommand)
at
Microsoft.SqlServer.Replication.ReplicationObject. ExecCommand(String
commandIn)
at Microsoft.SqlServer.Replication.ReplicationObject. CommonCreate()
at Microsoft.SqlServer.Replication.Subscription.Creat e()
at
Microsoft.SqlServer.Management.UI.CreateSubscripti onWizard.CreatePushSubscription(SubWizardSubscribe r
subscriber, SubWizardPublication pubInfo)
at
Microsoft.SqlServer.Management.UI.CreateSubscripti onWizard.CreateSubscriptions(Boolean
executeNow)
===================================
String or binary data would be truncated.
Changed database context to 'STOREMAIN'.
Job 'R00S-SQLA-STOREMAIN-Storemain from 00-R99S-SQLA-427' started
successfully.
Warning: The distribution agent job has been implicitly created and
will run under the SQL Server Agent Service Account.
'ROUSES.COM\LarryDSI' is a member of sysadmin server role and cannot be
granted to or revoked from the proxy. Members of sysadmin server role
are allowed to use any proxy. (.Net SqlClient Data Provider)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00 .2047&EvtSrc=MSSQLServer&EvtID=8152&LinkId=20476[/url]
Server Name: r00s-sqla
Error Number: 8152
Severity: 16
State: 10
Procedure: sp_MSget_synctran_commands
Line Number: 198
Program Location:
at System.Data.SqlClient.SqlConnection.OnError(SqlExc eption
exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(TdsParserStateObject
stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
SqlCommand cmdHandler, SqlDataReader dataStream,
BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQuer yTds(String
methodName, Boolean async)
at
System.Data.SqlClient.SqlCommand.InternalExecuteNo nQuery(DbAsyncResult
result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at
Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(String
sqlCommand, ExecutionTypes executionType)
Hi Larry
Can you script out the subscription and run it from Query Analyzer/SSMS?
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
"LPR-3rd" <lreames@.gmail.com> wrote in message
news:1168884119.848778.223260@.m58g2000cwm.googlegr oups.com...
> I am attempting to subscribe to a publication and I receive the
> following message...
> SQL 2005 & Win 2005 on both ends.
> ===================================
> SQL Server could not create a subscription for Subscriber 'r99s-sqla'.
> (New Subscription Wizard)
> ===================================
> An exception occurred while executing a Transact-SQL statement or
> batch. (Microsoft.SqlServer.ConnectionInfo)
> --
> Program Location:
> at
> Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(String
> sqlCommand, ExecutionTypes executionType)
> at
> Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(String
> sqlCommand)
> at
> Microsoft.SqlServer.Replication.ReplicationObject. ExecCommand(String
> commandIn)
> at Microsoft.SqlServer.Replication.ReplicationObject. CommonCreate()
> at Microsoft.SqlServer.Replication.Subscription.Creat e()
> at
> Microsoft.SqlServer.Management.UI.CreateSubscripti onWizard.CreatePushSubscription(SubWizardSubscribe r
> subscriber, SubWizardPublication pubInfo)
> at
> Microsoft.SqlServer.Management.UI.CreateSubscripti onWizard.CreateSubscriptions(Boolean
> executeNow)
> ===================================
> String or binary data would be truncated.
> Changed database context to 'STOREMAIN'.
> Job 'R00S-SQLA-STOREMAIN-Storemain from 00-R99S-SQLA-427' started
> successfully.
> Warning: The distribution agent job has been implicitly created and
> will run under the SQL Server Agent Service Account.
> 'ROUSES.COM\LarryDSI' is a member of sysadmin server role and cannot be
> granted to or revoked from the proxy. Members of sysadmin server role
> are allowed to use any proxy. (.Net SqlClient Data Provider)
> --
> For help, click:
> [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00 .2047&EvtSrc=MSSQLServer&EvtID=8152&LinkId=20476[/url]
> --
> Server Name: r00s-sqla
> Error Number: 8152
> Severity: 16
> State: 10
> Procedure: sp_MSget_synctran_commands
> Line Number: 198
>
> --
> Program Location:
> at System.Data.SqlClient.SqlConnection.OnError(SqlExc eption
> exception, Boolean breakConnection)
> at System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException
> exception, Boolean breakConnection)
> at
> System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(TdsParserStateObject
> stateObj)
> at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
> SqlCommand cmdHandler, SqlDataReader dataStream,
> BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
> at System.Data.SqlClient.SqlCommand.RunExecuteNonQuer yTds(String
> methodName, Boolean async)
> at
> System.Data.SqlClient.SqlCommand.InternalExecuteNo nQuery(DbAsyncResult
> result, String methodName, Boolean sendToPipe)
> at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
> at
> Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(String
> sqlCommand, ExecutionTypes executionType)
>
|||That is what I did to fix the problem.
Thanks,
Larry...
|||Sooy...different problem...
I scriped the creation of the publication, it took 23 min to create it.
I scripted the creation of the subscription & received the following
message......
Job 'R00S-SQLA-STOREMAIN-Storemain to 99-R99S-SQLA-449' started
successfully.
Warning: The distribution agent job has been implicitly created and
will run under the SQL Server Agent Service Account.
Msg 8152, Level 16, State 10, Procedure sp_MSget_synctran_commands,
Line 198
String or binary data would be truncated.
'ROUSES.COM\LarryDSI' is a member of sysadmin server role and cannot be
granted to or revoked from the proxy. Members of sysadmin server role
are allowed to use any proxy.
AHIA,
Larry...
|||I am looking at the code for sp_MSget_synctran_commands and this is what it
has around line 198
if @.command_only = 0
select * from #art_commands order by id
else
select commands from #art_commands order by id
Do you have any really long table names?
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
"LPR-3rd" <lreames@.gmail.com> wrote in message
news:1168927021.980769.34300@.q2g2000cwa.googlegrou ps.com...
> Sooy...different problem...
> I scriped the creation of the publication, it took 23 min to create it.
> I scripted the creation of the subscription & received the following
> message......
> Job 'R00S-SQLA-STOREMAIN-Storemain to 99-R99S-SQLA-449' started
> successfully.
> Warning: The distribution agent job has been implicitly created and
> will run under the SQL Server Agent Service Account.
> Msg 8152, Level 16, State 10, Procedure sp_MSget_synctran_commands,
> Line 198
> String or binary data would be truncated.
> 'ROUSES.COM\LarryDSI' is a member of sysadmin server role and cannot be
> granted to or revoked from the proxy. Members of sysadmin server role
> are allowed to use any proxy.
>
> AHIA,
> Larry...
>
|||The longest would be 'tblReceivingHeader'
That is not long in my book!!!
Larry...
|||Nope, its not. Enable logging to find out where it is bombing.
If you narrow this down, please post the schema of the problem table, and
publication and subscription scripts here.
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
"LPR-3rd" <lreames@.gmail.com> wrote in message
news:1169050867.439445.84200@.11g2000cwr.googlegrou ps.com...
> The longest would be 'tblReceivingHeader'
> That is not long in my book!!!
> Larry...
>