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...
>

Problems connectiong to a remote DB

Hello

I developed a vb.net win app which interacts with a sqlserver 2005 express DB.

When the DB is located in my local computer everything works just fine.

I'm trying to connect to a remote DB (mdf file that located on a remote server) but without any success.

I assume that a connectionstring change is needed, however I can't find what change to perform (I tried to insert the server IP address to the connectionstring but it didn't work either).

the app connectionstring looks like that:

connectionString

="Data Source=.\SQLExpress;AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DBName.mdf;Integrated Security=True;User Instance=False" providerName="System.Data.SqlClient" />

Hope someone can help me find the solution.

Since your question concerns a Windows Forms application, and not ASP.NET, you should ask it on theMSDN Forums.|||Try looking atwww.connectionstrings.com...|||Hi yarit, post the connection failure error message would help to solve the issue. You can also take a look at this post:
http://forums.asp.net/thread/1289341.aspx|||

Hello

Firstly thanks for all of your replies.

Now, I've succeeded to connect my app to a remote DBbut only in my company's network, the DB is located on a different machine in the company's network and I can connect to it using this connection string:

connectionString

="Data Source=Inner network IP address \ SQLEXPRESS;Database=DBName;Uid=xxx;Pwd=yyy;"providerName="System.Data.SqlClient">

However I have no success connecting it to an outerIP address, although the machine is connected to the internet via a router and has an outerIP address.

I'll justindicate that my GUI is supposed to be accessed via the internet (a web app) so I must be able to connecteventually via an outer IP address.

Does anybody know if I need to change some config or if it is a pure network problem.

|||

Hello

Firstly thanks for all of your replies.

Now, I've succeeded to connect my app to a remote DBbut only in my company's network, the DB is located on a different machine in the company's network and I can connect to it using this connection string:

connectionString

="Data Source=Inner network IP address \ SQLEXPRESS;Database=DBName;Uid=xxx;Pwd=yyy;"providerName="System.Data.SqlClient">

However I have no success connecting it to an outerIP address, although the machine is connected to the internet via a router and has an outerIP address.

I'll justindicate that my GUI is supposed to be accessed via the internet (a web app) so I must be able to connecteventually via an outer IP address.

Does anybody know if I need to change some config or if it is a pure network problem.

|||Hi yairt, first of all you have to make sure on teh SQL box no firewall is blocking TCP/IP communication to the SQL Express service from Internet. You can use telnet to check this, please refer to the post I mentioned in my previous post.|||

hi Iori jay

thanks for your reply.

The problem was solved (or better said was by-passed), apparently it has nothing to do with SQLSERVER or connection strings.

We connected the server to the internet via ADSL line (`and not through a router that probably concealed its address) and everything worked just fine.

Problems connecting to SQL2K after install on same server as SQL 2005

We have a current install of SQL2005 Developer on a Win 2003 Server Enterprise and it works as advertised. It is accessed as Test05

Yesterday a SQL2000 Developer Edition was also installed on the same Server as named instance Test05\Test052000.

The 2000 seems to start and run OK and can be connect to by Management studio etc from that same box. However it can not be connect to via the network. Any ideas?

You will have to make sure that the system and SQL Server are set up to allow remote connections to the server. Look at this KB Article.

Problems connecting to SQL server on W2K3 from Win 2000 workstatio

We are running SQL Server 2000 on Windows Server 2003 (SP1 not installed).
We are trying to access our databases using Enterprise manager from our
machines in the office using windows authentication. We have no problems
from Windows XP machines (using both TCP/IP and Named Pipes). I can connect
to the server using the SA account (TCP/IP) and Named Pipes from a windows
2000 workstation, but cannot access the SQL server from Windows 2000 machine
s
using TCP/IP.
When we try to log in using Windows 2000 we get "login failed for user
null". From xp machines it goes straight in.
Testing that I have done:
? Set up an alias to the Windows 2003 server name (SQL Server Client Netwo
rk
Utility). No luck
? I can map a folder to the machine using net use command
? I am in the same AD domain.
? The clients are connecting to the server using integrated security (not
allowing SQL authentication)
I cannot understand what’s going on; do you have any pointers on where to
start looking?
ThanksSame here, except we can not to connect from win98 machines. From XP
everything looks fine. Before everything worked for about 1,5 years and
only today morning crashed. Btw, connection to (local) on server machine
works fine. Nobody did any changes in TCP/IP nor in DSN settings.|||This is because you are using Windows authentication without a domain setup.
If you aren't using a domain you shouldn't use windows authentication
because it tends not to work, use SQL Authentication and you'll always
connect. The problem is mainly that the users you are signed in to the W2k
box doesn't match a user on the W2k3 box, or the user isn't passed properly
to SQL, or if you really want to setup Windows authentication use to put the
workstations that servers into a domain..
Thanks,
Mike Bulava, MCDBA
"Mike Allen" <MikeAllen@.discussions.microsoft.com> wrote in message
news:071EE849-F61A-4729-8598-67974D2D5269@.microsoft.com...
> We are running SQL Server 2000 on Windows Server 2003 (SP1 not installed).
> We are trying to access our databases using Enterprise manager from our
> machines in the office using windows authentication. We have no problems
> from Windows XP machines (using both TCP/IP and Named Pipes). I can
> connect
> to the server using the SA account (TCP/IP) and Named Pipes from a windows
> 2000 workstation, but cannot access the SQL server from windows 2000
> machines
> using TCP/IP.
> When we try to log in using Windows 2000 we get "login failed for user
> null". From xp machines it goes straight in.
> Testing that I have done:
> . Set up an alias to the Windows 2003 server name (SQL Server Client
> Network
> Utility). No luck
> . I can map a folder to the machine using net use command
> . I am in the same AD domain.
> . The clients are connecting to the server using integrated security (not
> allowing SQL authentication)
> I cannot understand what's going on; do you have any pointers on where to
> start looking?
> Thanks
>
>

Problems connecting to SQL server on W2K3 from Win 2000 workstatio

We are running SQL Server 2000 on Windows Server 2003 (SP1 not installed).
We are trying to access our databases using Enterprise manager from our
machines in the office using windows authentication. We have no problems
from windows XP machines (using both TCP/IP and Named Pipes). I can connect
to the server using the SA account (TCP/IP) and Named Pipes from a windows
2000 workstation, but cannot access the SQL server from windows 2000 machines
using TCP/IP.
When we try to log in using windows 2000 we get "login failed for user
null". From xp machines it goes straight in.
Testing that I have done:
?Set up an alias to the Windows 2003 server name (SQL Server Client Network
Utility). No luck
?I can map a folder to the machine using net use command
?I am in the same AD domain.
?The clients are connecting to the server using integrated security (not
allowing SQL authentication)
I cannot understand what’s going on; do you have any pointers on where to
start looking?
Thanks
Same here, except we can not to connect from win98 machines. From XP
everything looks fine. Before everything worked for about 1,5 years and
only today morning crashed. Btw, connection to (local) on server machine
works fine. Nobody did any changes in TCP/IP nor in DSN settings.
|||This is because you are using Windows authentication without a domain setup.
If you aren't using a domain you shouldn't use windows authentication
because it tends not to work, use SQL Authentication and you'll always
connect. The problem is mainly that the users you are signed in to the W2k
box doesn't match a user on the W2k3 box, or the user isn't passed properly
to SQL, or if you really want to setup Windows authentication use to put the
workstations that servers into a domain..
Thanks,
Mike Bulava, MCDBA
"Mike Allen" <MikeAllen@.discussions.microsoft.com> wrote in message
news:071EE849-F61A-4729-8598-67974D2D5269@.microsoft.com...
> We are running SQL Server 2000 on Windows Server 2003 (SP1 not installed).
> We are trying to access our databases using Enterprise manager from our
> machines in the office using windows authentication. We have no problems
> from windows XP machines (using both TCP/IP and Named Pipes). I can
> connect
> to the server using the SA account (TCP/IP) and Named Pipes from a windows
> 2000 workstation, but cannot access the SQL server from windows 2000
> machines
> using TCP/IP.
> When we try to log in using windows 2000 we get "login failed for user
> null". From xp machines it goes straight in.
> Testing that I have done:
> . Set up an alias to the Windows 2003 server name (SQL Server Client
> Network
> Utility). No luck
> . I can map a folder to the machine using net use command
> . I am in the same AD domain.
> . The clients are connecting to the server using integrated security (not
> allowing SQL authentication)
> I cannot understand what's going on; do you have any pointers on where to
> start looking?
> Thanks
>
>

problem with WIN2000 client

One Winn 2000 workstatiom has problem with ACCESS 2003 ODBC conection to SQL
server on WIN 2003 server. Other clients are all WINXP. This cient is on the
different domain too. Some reports works, and some have error, ask for
password again but not accept it even if it is supplied again. Application
on other clients works well.Hi Zeljko,
I noticed that the issue was posted in this newsgroup as a duplicated one.
You have another post titled "problem with WIN2000 client" and I will add a
reply to that post. If you have follow up questions, please post there and
I will work with you. Thanks you very much.
Sincerely yours,
Michael Cheng
Microsoft Online Support
****************************************
*******************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.

problem with WIN2000 client

One Winn 2000 workstatiom has problem with ACCESS 2003 ODBC conection to SQL
server on WIN 2003 server. Other clients are all WINXP. This cient is on the
different domain too. Some reports works, and some have error, ask for
password again but not accept it even if it is supplied again. Application
on other clients works well.
Hi Zeljko,
I noticed that the issue was posted in this newsgroup as a duplicated one.
You have another post titled "problem with WIN2000 client" and I will add a
reply to that post. If you have follow up questions, please post there and
I will work with you. Thanks you very much.
Sincerely yours,
Michael Cheng
Microsoft Online Support
************************************************** *********
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.