Replication to IP addressed server

we have an outside company that replicates to us and we are known to them via an IP address. We get an error now...Sql Server 2005 now does not support a server Alias, IP address, or any other alternate name are not supports. Specify the actual Server Name

I tried putting in the hosts file on their server

168.168.110.2 SQL2005

able to connect to our server through Studio Manager...no problem....

replication....no dice

what is the work around to make this happen, I am not a network guru...know enough to kill someone, but I need to get this to work before I can move to the new production server

Thanks

John

Exactly what part is failing? Have you tried creating an alias and mapping it to the IP address?|||

When the outside company tries to set the Subscription up it fails since they try to enter the IP address. From what the network people tell me, we have a T1 line between us and open addresses so they can ping the IP address. But, we are on two completely different networks....2 different companies.

They tried to put it into the hosts file on their server (which is SQL 2000) and on the PC they were using studio manager on, but that did not help

When you say create an alias, what exactly are you referring to, since my network skills are enough to kill someone right now.

On their side, any network operations will take some paper work so we are hoping to keep it on the server....paper work just takes a bit too long sometimes

|||

the error we get

SQL Server Replication requires the actual server name to make a connection to the server.
Connects through a server alias, IP addres, or any other alternate name are not supported.
Specify the actual server name 'SQL2005'

|||

Hi,

When you setup subscription, do you specify the subscriber using IP address or server name? I saw you have do the mapping with host file. You may want to check the following:

1) Make sure the outside company can connect to your server using name "SQL2005" using osql or management studio.

2) after connect to SQL2005, try "select @.@.SERVERNAME". Make sure the server name is "SQL2005" if it is default instance, or "SQL2005\instancename" if it is named instance

3) when create subscription, enter "SQL2005" instead of IP address.

Let me know if it still doesn't work for you.

Thanks,

-Peng

|||

It seems we have gotten past one problem and on to a new problem.

It looks like the Alias took care of the problem.

now, when the wizard gets to "New subscription Wizard"

Choose one or more Subscribers and specify each subscrition database

We can choose "Subscriber" as "SQL2005" - which is the server

Great!

but, there are no "Subscription Database" to choose from

The sql user has access to the DB, they have "Connect Replication" and "Connect" permission. The sql user also has public permission to the DB.

What else could be missing?

I feel we are close, just trying to finish this

One other thing, I have read that SQL 2000 distributor will work with a sql 2005 Subscription if it is transactional, is that correct?

from another post "If you are using a transactional read-only subscriber then the subscriber can be up to two versions higher than the publisher. SQL 2000 publisher and SQL 2005 subscriber should work, "

|||

Isn't "SQL2005" the publisher machine? The "Subscriber" should be the machine on the other end, the one receiving the data. You may need to click on the "Add Subscriber" button below and add the machine as a valid subscriber.

|||

SQL2000 at the outside company is the publisher and distributor

they also set the subscription for us (Push).

I can not access there SQL Server at all since it has windows auth

We are SQL Server 2005 (not yet, maybe this weekend)

So they are pushing the data from their DB to us

SQL2000 -> SQL2005

which, according to the docs does work. There just seems to be some bloody security issue going on....seems Microsoft went overboard on some of this and now has made it a real pain in the neck to even get working.

What do they permissions have to be to get this working

|||

Is "SQL2005" the actual name of your machine?

|||

SQL2005 is the name of my server which is also a SQL2005 server

Windows 2003 Server - 64 bit, SQL Server 2005 Enterprise Edition 64 bit

The server will be renamed to what the production server is currently this weekend...right now we are just trying to test this replication issue.

We had the replication working a few minutes before we got a

the user is not associated with a trusted SQL Server Connection

Source:SQL2005

The user they are using on my server (SQL2005) now has every role and has every permission on the DB they are trying to replicate to.

|||

So you're saying you got past the problem with adding a subscription?

And now you're having connection problems? Can you describe what exactly was working, and what failed suddenly?

And I assume this is transactional replication?

|||

This is transactional replication.

First, we had the issues with trying to replication from 2000-> 2005 because of the IP address issue, that was fixed with using an Alias.

which the error we got clearly stated that a server alias could not be used....so I guess it can ....which the error screen sure does say that does not work. Of course, no help button on that screen to help a person out. That was one of the bigger huddles to get over.

the SQL User, since we allow both connections, has access to the DB and we gave them the ability to do "Connection Replication"

That does not seem to be enough.

The person on the SQL2000 server (who is using Studio manger by the way) was using the Subscription Wizard and could not even get any DBs in the drop down to select from

Now, I have given all permissions for the user for the DB and all roles for the entire Server.

He can now see the DB to replicate too on the SQL2000 Subscription Wizard and is able to select the DB.

So, we crossed through some security issue to allow that, but we have no clue which one.

This use to be a basic thing to really do, now it is complicated with too much security.

We are right now waiting to see if the subscription actually takes place......and waiting and waiting....atleast no errors yet.

So, in a nutshell

do a transactional replication from SQL2000 -> SQL2005

where the sql2005 can not do any of the setup because of security

the SQL2000 server has to be accessed with Studio manager - check

an Alias must be set up...even though the subscription wizard says that this is wrong

and then there is some permission besides "Connection replication" that has to be set that we can't figure out

|||

Couple things:

- To create a subscription and see the list of databases at the subscriber, you need to be a minimum of db_owner of the subscriber database, or be a member of the sysadmin role at the subscriber machine. This is documented in the "Security Role Requirements for Replication Setup" topic in SQL Server 2005 Books Online.

Since they connect to your subscriber machine using a standard login/password, that login needs to be at least a dbo of the subscriber database.

- Where do you see the "Connection replication" permission thing? If I remember correctly, you need to set permissions for the agents to connect to the publisher, distributor and subscriber machine. Did you start the snapshot agent, logreader agent and distribution agent yet? Do you see any failures?

|||

They are failing on the Distribution Agent...snapshot and logreader does work

Login failed for user "The User is not associated with a Trusted SQL Server Connection. The Process Could not connect to Subscrivber "SQL2005" The Step Failed

The outside company can use SQL Server Man Studio to connect to our server using the same account, so we know the account works.....

something else is messed up still.

"Connection replication" is a permission of the user on the DB that we are trying to replicate to .....seemed like something we needed.

But right now, the user has every permission there is

|||

By default, the subscription will be created and use windows authentication for all connections to the publisher, distributor and subscriber.

To change the security settings for the subscriber to use standard security, you need to modify the security settings for the distribution agent.

TO learn how to do this, see SQL 2005 Books Online topic "How to: View and Modify Replication Security Settings".

You can also access search for, or access this topic at msdn.com.

http://msdn2.microsoft.com/ms151761.aspx

Replication Timeout Issue

Greetings!

I am running into this error when running Replication:

The response message was received from 'https://replia.websitename.net/replia/replisapi.dll' and is being processed.

(f) The process could not connect to Distributor 'OURDATABASE-DB'.

(f) The process could not connect to Distributor 'OURDATABASE-DB'.

(f) Unable to complete login process due to delay in opening server connection

*******************************************************

01/11/2007 18:26:09 - Error during FINAL DB Replication. The process could not connect to Distributor 'OURDATABASE-DB'.

I am running Replication on a Windows XP Pro laptop, with 1 GB of RAM, 1.83 GHz CPU. Any ideas, suggestions and/or help would be appreciated!

Thanks!

Cristian E.

Please provide more information. What version of SQL server are you using? Are you using standard edition, enterprise, or express? Transactional or merge replication?

Gary

|||

Hello,

1. Is the Laptop computer your Subscriber or Publisher/Distributor?

2. If you do not do web sync, can you run sync through merge agent (replmerg.exe) from Console window command line using fully connected mode?

This posting is provided AS IS with no warranties, and confers no rights.

|||

I am using SQL Server 2005 Express version, Merge replication. I changed the History Verbose Level to a 2 and I got a different error:

(f) The response message was received from 'https://replia.websitename.net/replia/replisapi.dll' and is being processed.
(f) The process could not connect to Distributor 'ABCD-DB'.
(f) The process could not connect to Distributor 'ABCD-DB'.
(f) Named Pipes Provider: Could not open a connection to SQL Server [2].
(f) An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
(f) Login timeout expired
(f) The merge process failed to execute a query because the query timed out. If this failure continues, increase the query timeout for the process. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write.
*******************************************************
01/13/2007 09:31:39 - Error during FINAL DB Replication. The process could not connect to Distributor 'ABCD-DB'.

*******************************************************

I have already increased the timeout to 15-20 mins. Any other suggestions?

Thanks!

Cristian E.

|||

I am using SQL Server 2005 Express version, Merge replication. I changed the History Verbose Level to a 2 and I got a different error:

(f) The response message was received from 'https://replia.websitename.net/replia/replisapi.dll' and is being processed.
(f) The process could not connect to Distributor 'ABCD-DB'.
(f) The process could not connect to Distributor 'ABCD-DB'.
(f) Named Pipes Provider: Could not open a connection to SQL Server [2].
(f) An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
(f) Login timeout expired
(f) The merge process failed to execute a query because the query timed out. If this failure continues, increase the query timeout for the process. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write.
*******************************************************
01/13/2007 09:31:39 - Error during FINAL DB Replication. The process could not connect to Distributor 'ABCD-DB'.

*******************************************************

I have already increased the timeout to 15-20 mins.

The Laptop computer is the Publisher and it is running web synchronization (web services).

|||

Has it ever worked before?

Can you try this on your machine?

osql -S"ABCD-DB" -U"login"

See if you get the same error message for "Named Pipes Provider: Could not open a connection to SQL Server [2]. "

If you get the same message, then the problem is as simple as enable the protocols in your sql server setting.

I also noticed that the name "ABCD-DB" sounds more like a database name than a server name...

Regards,
Gary

sql

Replication that causes blocking of process

Hi ,
My Replication also got this timeout error while running the last command :
{CALL sp_MSdel_SL030100 (N'BJX0047', N'26.01.06AF')} {CALL sp_MSins_SL030100
(N'BJX0047', N'26.01.06AF', N'535488', 2006-01-19 etc ..)
i found out that SPid83 --> which is doing the sp_MSins_SL030100;1 is
blocking
SPid73 --> which is doing the CALL sp_MSdel_SL030100
How can i resolve it as i am afraid that if i kill any process the sync is
out and i would be forced to re-create the snapshot and also shldn't the
SP_MSdel be done first ?
apreciate ur advise
Message posted via http://www.droptable.com
This looks like an update which is being performed as a delete insert pair.
Study what the procs are doing and see if it can't benefit from new indexes
of updating the indexes.
You might also want to look at this trace flag.
http://support.microsoft.com/kb/238254/en-us
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
"maxzsim via droptable.com" <u14644@.uwe> wrote in message
news:5a944df8034f0@.uwe...
> Hi ,
> My Replication also got this timeout error while running the last command
> :
> {CALL sp_MSdel_SL030100 (N'BJX0047', N'26.01.06AF')} {CALL
> sp_MSins_SL030100
> (N'BJX0047', N'26.01.06AF', N'535488', 2006-01-19 etc ..)
> i found out that SPid83 --> which is doing the sp_MSins_SL030100;1 is
> blocking
> SPid73 --> which is doing the CALL sp_MSdel_SL030100
> How can i resolve it as i am afraid that if i kill any process the sync is
> out and i would be forced to re-create the snapshot and also shldn't the
> SP_MSdel be done first ?
>
> apreciate ur advise
> --
> Message posted via http://www.droptable.com
|||Hi,
First of all tks for the links provided.
as menitoned in the link it's doing a so-called "deferred" update when the
unique constraint (i.e the PK) has been changed
However , from the stored procedure below
{CALL sp_MSdel_SL030100 (N'BJX0047', N'26.01.06AF')} {CALL sp_MSins_SL030100
(N'BJX0047', N'26.01.06AF', N'535488', 2006-01-19
BJX0047 & 26.01.06AF are the 2 PKs' values and both the DELETE/INSERT are
showing the same values, so i am confused
appreciate ur further advice
btw : after leaving this replication for the whole nite , it somehow resolved
by itself
tks & rdgs
Hilary Cotter wrote:[vbcol=seagreen]
>This looks like an update which is being performed as a delete insert pair.
>Study what the procs are doing and see if it can't benefit from new indexes
>of updating the indexes.
>You might also want to look at this trace flag.
>http://support.microsoft.com/kb/238254/en-us
>[quoted text clipped - 13 lines]
Message posted via http://www.droptable.com

Replication Synchronization Error

I have a default full install of the JULY CTP on X86 Windows Server 2003 (fully up to date on the patches). I used the Merge Replication Sample to create the Publisher/Publication. SInce the code sample to create the subscriber doesn't work (apparently some major changes in the assemblies) I had to create the Subscription using the wizard. Both databases are on the same machine (adventureworks and adventureworksreplicated). When I try and sychronize the database the first time I get the following error.

Error loading custom assembly "C:\Program Files\Microsoft SQL Server\100\COM\BusinessLogic.dll", Error : "Could not load file or assembly 'C:\\Program Files\\Microsoft SQL Server\\100\\COM\\BusinessLogic.dll' or one of its dependencies. The given assembly name or codebase was invalid. (Exception from HRESULT: 0x80131047)".

I did confirm that the assembly is there, but do not know what else to look for. The account the synch is running under is the local Administrator (not a good idea I know but just want it to work).

Any ideas?

Thanks

Steve Muise

Neudesic LLC

Two threads down is a workaround for what looks like might be your problem, can you give it a shot? If it doesn't work, then let us know.

Replication Subscription Error

Hi,
I tried setting a server up as the publisher, distributor and subscriber.
When I tried to create either a push or pull subscription I got the following
message:
Error 14053: Cannot load the DLL replincrementlsn extended procedure, or one
of the DLLs it references. Reason: 126(error not found).
The subscription could not be updated at this time.
The subscription could not be created.
SQL Server Enterprise Manager could not create a pull subscription to
publication XXXX
Error 14053: Cannot load the DLL replincrementlsn extended procedure, or one
of the DLLs it references. Reason: 126(error not found).
The subscription could not be updated at this time.
The subscription could not be created.
Has anyone seen this before?
Server running SQL 2000 on Windows 2000 Server
Thanks
Something is not installed correctly. Reapply your latest service pack.
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
"DavidW" <DavidW@.discussions.microsoft.com> wrote in message
news:895B9996-9D97-4CAA-84F2-FF314F842E6F@.microsoft.com...
> Hi,
> I tried setting a server up as the publisher, distributor and subscriber.
> When I tried to create either a push or pull subscription I got the
> following
> message:
> Error 14053: Cannot load the DLL replincrementlsn extended procedure, or
> one
> of the DLLs it references. Reason: 126(error not found).
> The subscription could not be updated at this time.
> The subscription could not be created.
> SQL Server Enterprise Manager could not create a pull subscription to
> publication XXXX
> Error 14053: Cannot load the DLL replincrementlsn extended procedure, or
> one
> of the DLLs it references. Reason: 126(error not found).
> The subscription could not be updated at this time.
> The subscription could not be created.
> Has anyone seen this before?
> Server running SQL 2000 on Windows 2000 Server
> Thanks
>
|||Thank you Hilary, that has resolved the problem!
"Hilary Cotter" wrote:

> Something is not installed correctly. Reapply your latest service pack.
> --
> 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
> "DavidW" <DavidW@.discussions.microsoft.com> wrote in message
> news:895B9996-9D97-4CAA-84F2-FF314F842E6F@.microsoft.com...
>
>

Replication snapshot error

The process could not bulk copy out of table '[dbo].[syncobj_0x4333454238413435]'.

Under Error Information, Full Message:
ODBCBCP/Driver version mismatch

This was working fine, until we had to drop the publication to make a change to the table. Now we get this, after creating the publication and subscriptions
We did apply Service Pack 3 recently (running SQL Server 2000).I suspect the .dll used in MDAC has the problem, refer to MS Data (http://www.microsoft.com/data/) link and download the compnent checker tool and check for any inconsistencies.

Replication setup via VB.NET

I have a VB.NET program that is executing the SQL commands to setup
replication. I am getting an error (A severe error has occured) on the
sp_addmergepubliction command.
I did setup a profiler and notice one area:
sp_adddistributor was found in the text of this event, the text has been
replaced with this comment for security reasons.
My entire text, before the sp_addmergepulication does:
use master
exec sp_replicationdboption @.dbname='InWare',etc
exec dbo.sp_helpditributor @.rpcsrvname=@.distributor OUTPUT
if @.distributor is null
begin
declare @.xpath varchar (255)
select @.xpath=substring(filename,1,charindex('InWare_data ',filename)-1)
FROM master..sysdatabases where name='inware'
exec sp_adddistributor @.distributor=@.@.servername
exec sp_adddistributiondb @.database='inxsqlDIST',etc
exec sp_adddistpublisher @.publisher=@.@.servername, etc
end
exec sp_replicationdboption @.dbname='InWare',@.optname='merge
publish',@.value='true'
I guess, now that I see Profiler has the line sp_adddistributor
commented out, this could cause the problem. What is commenting it out -
SQL or VB.NET?
I want to use VB (my program) to have the user setup replication so they
don't have to use the enterprise manager, and it allows me to control
the setup.
Thanks.
Darin
Darin
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Darin wrote:
> I want to use VB (my program) to have the user setup replication so they
> don't have to use the enterprise manager, and it allows me to control
> the setup.
How about using SQL-DMO through COM Interop?
|||Profiler is removing it because the command contains the keyword "password".
Profiler won't display anything that includes that keyword to eliminate a
security hole. The command is still likely being run, just not displayed in
Profiler.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
"Darin" <darin_nospam@.nospamever> wrote in message
news:exGVx28KFHA.2596@.TK2MSFTNGP10.phx.gbl...
>I have a VB.NET program that is executing the SQL commands to setup
> replication. I am getting an error (A severe error has occured) on the
> sp_addmergepubliction command.
> I did setup a profiler and notice one area:
> sp_adddistributor was found in the text of this event, the text has been
> replaced with this comment for security reasons.
> My entire text, before the sp_addmergepulication does:
> use master
> exec sp_replicationdboption @.dbname='InWare',etc
> exec dbo.sp_helpditributor @.rpcsrvname=@.distributor OUTPUT
> if @.distributor is null
> begin
> declare @.xpath varchar (255)
> select @.xpath=substring(filename,1,charindex('InWare_data ',filename)-1)
> FROM master..sysdatabases where name='inware'
> exec sp_adddistributor @.distributor=@.@.servername
> exec sp_adddistributiondb @.database='inxsqlDIST',etc
> exec sp_adddistpublisher @.publisher=@.@.servername, etc
> end
> exec sp_replicationdboption @.dbname='InWare',@.optname='merge
> publish',@.value='true'
> I guess, now that I see Profiler has the line sp_adddistributor
> commented out, this could cause the problem. What is commenting it out -
> SQL or VB.NET?
> I want to use VB (my program) to have the user setup replication so they
> don't have to use the enterprise manager, and it allows me to control
> the setup.
> Thanks.
> Darin
> Darin
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Any idea what could be causing the error:
A severe error occured .... Any results should be discarded
on the exec sp_addreplication @.publication='InWareMerge'
when it works find on QA?
Darin
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||If you script a publication in EM, it assumes you have the publisher,
distributor, and subscribers already set up for all servers involved in
replication. If that is not the case, then creating a publication will
fail.
GNH
"Darin" <darin_nospam@.nospamever> wrote in message
news:ukmK5P9KFHA.3992@.TK2MSFTNGP15.phx.gbl...
> Any idea what could be causing the error:
> A severe error occured .... Any results should be discarded
> on the exec sp_addreplication @.publication='InWareMerge'
> when it works find on QA?
> Darin
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||My problem is all of my script run from the VB.NET program work fine
until that command.
Whereas if I take all of the script and paste it into a QA window it all
works perfectly.
Darin
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Replication Setup Issues

While trying to configure replication from and to SQL Server 2000 boxes, I
encountered the following error, which I was not able to get past. The
error is encountered after completing the "Push Subscription Wizard" which
is set to create the database and initialize the schema and data. The
database gets created, but the schema does not.
Does anyone have advice on how to get past this error?
If not, I have questions further on regarding my test environment...
The process could not connect to Subscriber '<SERVERNAME>'.
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
(Source: <SERVERNAME> (Data source); Error number: 18452)
I am connected using windows authentication, and I am an administrator on
both boxes. I can connect to either database and perform any action I want,
but I still keep getting this error. I have not found any way to specify a
login for replication, I assume the job runs either under my login or as SA.
I changed the scheduled task to be owned by SA, and attempted to run it as
both "Self" and "DBO", with no luck. I kept getting the same error. Some
of the IDs that exist in our production database do not exist in the
database we are replicating to, although SA exists, as does my account.
Normally I do not use Enterprise manager for anything and I script
everything out, but the only instructions I had were for EM. Would I be
better off using a script in QA for setting up replication?
Regarding my test environment...
Now, after spending a couple of hours on this error, I decided to go back to
my test environment (where everything worked the first time) and make it
look more like my production environment so I could do another round of
testing. I restored the master database and my 3 database which will be
replicated, but the backups were form different dates and I ran into all
kinds of conflicts. Essentially, SQL Server said the replication
definitions were invalid, and would not let me remove them. Next week, when
time allows, I will be getting a cold backup of the entire database to
attach to, hopefully that will get my test database in a state where I can
do another round of testing.
Am I on the right track with my test environment? What is the proper way to
copy a replication database, and remove the replication?
Any advice will be greatly appreciated.
Are you using sql server logins for the distribution agent, and the
subscriber is set to only allow windows (trusted) authentication? This could
be one cause of the error.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Both of the databases are set up to allow both SQL Server and trusted
authentication. I confirmed by logging into each server as SA, just to be
sure.
I am, however, performing all of the setup using trusted authentication,
under my own account, which has administrative priviledges.
I never specified a logon for the distribution agent, however. Maybe that
is the problem? I was never actually prompted to do so, and was unable to
find where to specify it. I am the owner of the distribution agent
process, and the first process in the scheduled job is set to run as (Self),
so I assume it is running under my account. I tried changing this to DBA,
and I tried changing the owner to SA, neither made a difference.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:exM%23TihUHHA.3652@.TK2MSFTNGP04.phx.gbl...
> Are you using sql server logins for the distribution agent, and the
> subscriber is set to only allow windows (trusted) authentication? This
could
> be one cause of the error.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
|||Jim,
just testing all posibilities here so pls can you check what the following
script returns at the subscriber.
Use Master
go
Select @.@.Servername
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Excellent catch. My test database comes back with the production database
name. The question is, how do I correct this, or how do I get the test
database to match my production database configuration?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ua103$CVHHA.4784@.TK2MSFTNGP03.phx.gbl...
> Jim,
> just testing all posibilities here so pls can you check what the following
> script returns at the subscriber.
> Use Master
> go
> Select @.@.Servername
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>
>
|||ok, I found this in BOL explaining how to change the server name. The
question is, will this be enough to get the restored database in a testable
state, or am I going about this the wrong way? Tonight I intend to take a
full cold backup from production and reattach to all the databases on test.
How to change to the current server name in the SQL Server 6.5 master
database (ISQL/w)
To change to the current server name in the SQL Server 6.5 master database
1.. Start Microsoft SQL ServerT in minimal configuration mode. In a
command prompt window, from the \Mssql\Binn directory, run:
sqlservr -f
2.. On the Start menu, point to Programs /Microsoft SQL Server 6.5, and then
click ISQL/w.
3.. Enter the sa password, and then click Connect.
4.. Execute SELECT @.@.SERVERNAME to retrieve the former server name.
5.. Execute sp_dropserver to drop the former server.
6.. Execute sp_addserver to add the current server.
7.. Stop SQL Server. In the command prompt window, press Ctrl+C.
8.. Restart SQL Server.
9.. Execute SELECT @.@.SERVERNAME to verify the current server name.
Examples
--Start SQL Server in minimal configuration mode.
--Retrieve the former server name.
SELECT @.@.SERVERNAME
--Drop the server returned from the previous select.
sp_dropserver 'SERVER6X'
--Add the current server.
sp_addserver 'SERVER70', local
--Stop SQL Server.
--Restart SQL Server in minimal configuration mode.
--Verify the current server name.
SELECT @.@.SERVERNAME
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:uDG$BKDVHHA.2212@.TK2MSFTNGP02.phx.gbl...[vbcol=seagreen]
> Excellent catch. My test database comes back with the production database
> name. The question is, how do I correct this, or how do I get the test
> database to match my production database configuration?
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:ua103$CVHHA.4784@.TK2MSFTNGP03.phx.gbl...
following
>
|||Jim,
this should be enough to get the subscriber set up. For step 6 you just have
to remember the 'local' switch.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Paul, thank you for the assistance. I'll post back in a couple of days to
let everyone know how I made out.
I have added your site to my online SQL Server library. I have a feeling it
will come in useful.
Thanks again,
Jim
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23Lh$XgDVHHA.4756@.TK2MSFTNGP06.phx.gbl...
> Jim,
> this should be enough to get the subscriber set up. For step 6 you just
have
> to remember the 'local' switch.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
|||I was finally able to get my test environment up and running, after renaming
my server, deleting the entries in the distribution database, and updating
the server name in the msdb.dbo.jobs table.
The test environment replicated without any issue, so I do not know why I am
having trouble in production.
Maybe I just need to remove the replication entirely and start from scratch?
Before I go that route, does anyone know what would be likely to cause this
error when trying to run the distribution agent?
The process could not connect to Subscriber '<SERVERNAME>'.
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
(Source: <SERVERNAME> (Data source); Error number: 18452)
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:e5aCzMhUHHA.5012@.TK2MSFTNGP04.phx.gbl...
> While trying to configure replication from and to SQL Server 2000 boxes, I
> encountered the following error, which I was not able to get past. The
> error is encountered after completing the "Push Subscription Wizard" which
> is set to create the database and initialize the schema and data. The
> database gets created, but the schema does not.
> Does anyone have advice on how to get past this error?
> If not, I have questions further on regarding my test environment...
> ----
--
> --
> The process could not connect to Subscriber '<SERVERNAME>'.
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection.
> (Source: <SERVERNAME> (Data source); Error number: 18452)
> ----
--
> --
> I am connected using windows authentication, and I am an administrator on
> both boxes. I can connect to either database and perform any action I
want,
> but I still keep getting this error. I have not found any way to specify
a
> login for replication, I assume the job runs either under my login or as
SA.
> I changed the scheduled task to be owned by SA, and attempted to run it as
> both "Self" and "DBO", with no luck. I kept getting the same error.
Some
> of the IDs that exist in our production database do not exist in the
> database we are replicating to, although SA exists, as does my account.
> Normally I do not use Enterprise manager for anything and I script
> everything out, but the only instructions I had were for EM. Would I be
> better off using a script in QA for setting up replication?
> Regarding my test environment...
> Now, after spending a couple of hours on this error, I decided to go back
to
> my test environment (where everything worked the first time) and make it
> look more like my production environment so I could do another round of
> testing. I restored the master database and my 3 database which will be
> replicated, but the backups were form different dates and I ran into all
> kinds of conflicts. Essentially, SQL Server said the replication
> definitions were invalid, and would not let me remove them. Next week,
when
> time allows, I will be getting a cold backup of the entire database to
> attach to, hopefully that will get my test database in a state where I can
> do another round of testing.
> Am I on the right track with my test environment? What is the proper way
to
> copy a replication database, and remove the replication?
> Any advice will be greatly appreciated.
>
|||OK, I removed the replication entirely and started over, with the same
problems as before. It turned out to be an NT account/permissions issue.
The account that runs the SQL Server Agent on the distributor needs to have
access to the subscriber server as well.
Just to repeat the error:
> ----
> The process could not connect to Subscriber '<SERVERNAME>'.
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection.
> (Source: <SERVERNAME> (Data source); Error number: 18452)
I looked at the security event logs on the subscriber server and saw a login
failure when I was trying to push the subscription. The ID that was
erroring out was a local ID on the distributor. The SQL Server Agent was
being run under this local account. Creating the same local account on the
subscriber server and giving it the required permissions, corrected the
problem.

Replication setup Error..

When I tried to configure replication, the following error comes out.
What is that about and any idea?When I tried to configure replication, the following error comes out.
What is that about and any idea?

Try this Microsoft article:
http://support.microsoft.com/?id=321822

Replication service error

hello everybody
when i create the subscribers . i got error . i try both account system
account and this account ...
but same error
Subscriptions were created successfully at the following Subscribers:
BOYD
SQL Server Enterprise Manager could not start the service 'SQLSERVERAGENT'
on server 'SERVER1'.
5 - (Access is denied)
Sam,
on Server1 check the domain login used by the sqlserveragent service. Make
sure it is a local administrator and that the password is correct, then
start the service manually using control panel.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I got the same error this morning. Could you please give me more detail on
how to check the login account.
Thanks.
Perayu
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uBf5LtLyFHA.1252@.TK2MSFTNGP09.phx.gbl...
> Sam,
> on Server1 check the domain login used by the sqlserveragent service. Make
> sure it is a local administrator and that the password is correct, then
> start the service manually using control panel.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||OK - on server1 open up control panel, administrative tools, services. Find
the sqlserveragent service (assuming the default instance), open the
properties and make sure it uses a domain user that is a local
administrator. If you are unclear about which account has these privileges,
it's probably best to get your network admin to create an account for you.
Once selected, restart the service.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thank you so much!
It is working now.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OCMwK8PyFHA.2516@.TK2MSFTNGP12.phx.gbl...
> OK - on server1 open up control panel, administrative tools, services.
> Find the sqlserveragent service (assuming the default instance), open the
> properties and make sure it uses a domain user that is a local
> administrator. If you are unclear about which account has these
> privileges, it's probably best to get your network admin to create an
> account for you. Once selected, restart the service.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Hello Mr Paul
still i got same problem , if have any privalage for domain account
thanks
Sam
"Perayu" wrote:

> I got the same error this morning. Could you please give me more detail on
> how to check the login account.
> Thanks.
> Perayu
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:uBf5LtLyFHA.1252@.TK2MSFTNGP09.phx.gbl...
>
>
|||Sam,
does this mean that you can't start the sql server agent at all? If so, make
sure that the account you're using for the sql server agent is added to the
sysadmin role. it shoyuld be in this role if your builtin\administrators are
there and the domain account is a local admin one, but many DBAs disable
this as it is seen as a security backdoor.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Replication Question

Hi,
I'm getting the error below when executing:
sp_addsubscription.
Prior to this I have, added the pub with sp_addpublication followed by
sp_addpublication_snapshot followed by sp_grant_publication_access followed
by sp_addarticle.
SQLServer 2000 SP3 contains the Publication and the Distribution agent is
also on a 2000 server SP3. I have just installed a new server with SP4 which
is my intended subscriber... but I can't get past this error. I have other
replication evvironments setup and they run fine.
This is the error I'm receiving:
Server: Msg 14070, Level 16, State 1, Procedure sp_changesubstatus, Line 993
Could not update the distribution database subscription table. The
subscription status could not be changed.
Server: Msg 14057, Level 16, State 1, Procedure sp_addsubscription, Line 971
The subscription could not be created.
[OLE/DB provider returned message: Unspecified error]
Thx
Something has gone quite wrong along the line so let me start by asking you a
few questions which will hopefully shed some light on the situation:
1) Are there any memory dumps in the LOG folder of any of the SQL Server
instances involved?
2) Are the Publisher and the Distributor on the same SQL200(sp3) instance
(sounds like that is the case but I want to make sure)?
3) Has MDAC on the Publisher\Distributor been updated recently? Or, what is
the version of sqloledb.dll on the Publisher\Distributor machine?
4) Can you add a simple publication from another test database successfully?
If not, which command is it failing on?
-Raymond
"Allen" wrote:

> Hi,
> I'm getting the error below when executing:
> sp_addsubscription.
> Prior to this I have, added the pub with sp_addpublication followed by
> sp_addpublication_snapshot followed by sp_grant_publication_access followed
> by sp_addarticle.
> SQLServer 2000 SP3 contains the Publication and the Distribution agent is
> also on a 2000 server SP3. I have just installed a new server with SP4 which
> is my intended subscriber... but I can't get past this error. I have other
> replication evvironments setup and they run fine.
> This is the error I'm receiving:
> Server: Msg 14070, Level 16, State 1, Procedure sp_changesubstatus, Line 993
> Could not update the distribution database subscription table. The
> subscription status could not be changed.
> Server: Msg 14057, Level 16, State 1, Procedure sp_addsubscription, Line 971
> The subscription could not be created.
> [OLE/DB provider returned message: Unspecified error]
> Thx
>

Replication Pull subscription problem

Iam trying to pull subscription and Iam getting same error over and over again. The error says:
****SQL Server Enterprise Merger could not create a pull subscription to publication "database name"
Error 15004: Name cannot be null *****
ThanksHi

Its because of non-existence or invalid server info on sysservers table. Please follow the instructions to fix the above problem.

exec sp_dropserver <your_local_server_name> -- subscriber

exec sp_dropserver <remote_server_name> -- publisher

exec sp_addserver <your_local_server_name>,local

exec sp_addserver <remote_server_name>

shutdown -- it is mandatory to take effect otherwise you still get the same error
go

Start the sql server with SQLSERVERAGENT

exec sp_helpserver -- it should display your local server and publisher

Then, try configure pull subscription

Cheers
Sekar|||What version does this happen in? I thought Microsoft fixed this problem a while ago.

Replication problem(integrity violation) - Cannot Sync up emulator -HELP!

Can somebody help please - i keep getting this erro when trying to sycn
up my emulator to my Merged Replication DB. When i look into he error
message via the VS debugger i can see this text in the SqlException
error:
"The row update or insert cannot be reapplied due to an integrity
violation. [,,,,,]"
Anybody know what this problem could be? I have recently dropped an
recreated a lot of my tables and changed some data types but did not
think it would effect me this badly.
I have also disabled and re-enabled my merge replication db many times.
I am sure all my connection settings are correct and the problem
appears to be deeper than that.
Recently i have changed the constraint on all of my tables to "Not to
enforce relationships on insert / update". It seems like this is what
the emulator is trying to do though'
Suggestions GREATLY appreciated!!!!
Thanks,
SteveHi
Is this any help?
http://support.microsoft.com/default.aspx?scid=kb;en-us;889521
You may want to try logging the updates:
http://support.microsoft.com/default.aspx?scid=kb;en-us;312292
John
"steroche" wrote:
> Can somebody help please - i keep getting this erro when trying to sycn
> up my emulator to my Merged Replication DB. When i look into he error
> message via the VS debugger i can see this text in the SqlException
> error:
> "The row update or insert cannot be reapplied due to an integrity
> violation. [,,,,,]"
>
> Anybody know what this problem could be? I have recently dropped an
> recreated a lot of my tables and changed some data types but did not
> think it would effect me this badly.
>
> I have also disabled and re-enabled my merge replication db many times.
> I am sure all my connection settings are correct and the problem
> appears to be deeper than that.
>
> Recently i have changed the constraint on all of my tables to "Not to
> enforce relationships on insert / update". It seems like this is what
> the emulator is trying to do though'
>
> Suggestions GREATLY appreciated!!!!
> Thanks,
> Steve
>|||Im afraid not. I have tried everything at this stage and its driving me
crazy!!!!!
I have re-published my database about 10times i'd say and i always get
an error.
Generally I get one of 2 errors. If i have already created the database
the errors are:
Message "Row data cannot be set. [,,,,,]"
NativeError 28572
and
Message "Invalid row handle"
NativeError 0
However if i dont have it created previous to running it and it creates
it itself I get the error:
message "Run"
NativeError 28557
Message "The row update or insert cannot be reapplied due to an
integrity violation. [,,,,,]
NativeError 28549
What the hell is going on' I have checked all the tables to make sure
that the checkbox "Enforce Relationship for replication" IS checked so
the NOT FOR REPLICATION bug doesn't apply here.
I have checked over all my datatypes and relationships and the data
that they contain to ensure that there can be no FK conflicts.
I know the table is being created as i can see it on the CE Query
analyser but it contains no tables so this probably gives rise to the
second error but why is it not creating the db properly in the first
place'
I heard that compacting the db might help - how do i do that'|||Hi
Unfortunately I am not an expert on replication!
Hilary Cotter who has replied in microsoft.public.sqlserver.replication
knows alot more about replication than I.
John
"steroche" wrote:
> Im afraid not. I have tried everything at this stage and its driving me
> crazy!!!!!
> I have re-published my database about 10times i'd say and i always get
> an error.
> Generally I get one of 2 errors. If i have already created the database
> the errors are:
> Message "Row data cannot be set. [,,,,,]"
> NativeError 28572
> and
> Message "Invalid row handle"
> NativeError 0
> However if i dont have it created previous to running it and it creates
> it itself I get the error:
> message "Run"
> NativeError 28557
> Message "The row update or insert cannot be reapplied due to an
> integrity violation. [,,,,,]
> NativeError 28549
> What the hell is going on' I have checked all the tables to make sure
> that the checkbox "Enforce Relationship for replication" IS checked so
> the NOT FOR REPLICATION bug doesn't apply here.
> I have checked over all my datatypes and relationships and the data
> that they contain to ensure that there can be no FK conflicts.
> I know the table is being created as i can see it on the CE Query
> analyser but it contains no tables so this probably gives rise to the
> second error but why is it not creating the db properly in the first
> place'
> I heard that compacting the db might help - how do i do that'
>|||Check that your subset filters and merge filters in the publication are
correctly constructed.
You are potentially sending down some data on a foreign key that is getting
filtered out.
Rob
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:F13AC93E-5354-4A2F-A066-2B3D22AA6D8F@.microsoft.com...
> Hi
> Unfortunately I am not an expert on replication!
> Hilary Cotter who has replied in microsoft.public.sqlserver.replication
> knows alot more about replication than I.
> John
> "steroche" wrote:
>> Im afraid not. I have tried everything at this stage and its driving me
>> crazy!!!!!
>> I have re-published my database about 10times i'd say and i always get
>> an error.
>> Generally I get one of 2 errors. If i have already created the database
>> the errors are:
>> Message "Row data cannot be set. [,,,,,]"
>> NativeError 28572
>> and
>> Message "Invalid row handle"
>> NativeError 0
>> However if i dont have it created previous to running it and it creates
>> it itself I get the error:
>> message "Run"
>> NativeError 28557
>> Message "The row update or insert cannot be reapplied due to an
>> integrity violation. [,,,,,]
>> NativeError 28549
>> What the hell is going on' I have checked all the tables to make sure
>> that the checkbox "Enforce Relationship for replication" IS checked so
>> the NOT FOR REPLICATION bug doesn't apply here.
>> I have checked over all my datatypes and relationships and the data
>> that they contain to ensure that there can be no FK conflicts.
>> I know the table is being created as i can see it on the CE Query
>> analyser but it contains no tables so this probably gives rise to the
>> second error but why is it not creating the db properly in the first
>> place'
>> I heard that compacting the db might help - how do i do that'
>>

Replication problem with stored procedure

I am working on a transaction replication, i have no problem with all the tables, but when adding the stored procedures, i got the error message:

Insert Error: Column name or number of supplied values does not match table definition.

The first column - wsSTUAPP_WEXP_iID 's "identity incremnet" definition of table tbl_wsSTUAPP_WEXP is set to 1.

The system works well in the publisher server, and the table itself has no problem for replication, but when adding the stored procedure to the replication article, the problem happens.

Please advise how to slove this problem. Below is the part of the store procedure and the error message:

THANK YOU IN ADVANCE!

STOREd PROCEDURE:

ALTER PROCEDURE [dbo].[sp_wsSetSTUAPPWEXP]

@.wsSTUAPP_WEXP_iID int

, @.wsSTUAPP_iID int

, @.wsSTUAPP_WEXP_iOrder int

, @.wsSTUAPP_WEXP_sEmployerName varchar(75)

, @.wsSTUAPP_WEXP_sJobTitle varchar(75)

, @.wsSTUAPP_WEXP_sJobDESC varchar(360)

, @.wsSTUAPP_WEXP_dStart datetime

, @.wsSTUAPP_WEXP_dEnd datetime

AS

IF NOT EXISTS(SELECT wsSTUAPP_WEXP_iID FROM tbl_wsSTUAPP_WEXP WHERE wsSTUAPP_WEXP_iID= @.wsSTUAPP_WEXP_iID)

BEGIN

INSERT INTO tbl_wsSTUAPP_WEXP

VALUES(

@.wsSTUAPP_iID

,@.wsSTUAPP_WEXP_iOrder

,@.wsSTUAPP_WEXP_sEmployerName

,@.wsSTUAPP_WEXP_sJobTitle

,@.wsSTUAPP_WEXP_sJobDESC

,@.wsSTUAPP_WEXP_dStart

,@.wsSTUAPP_WEXP_dEnd

)

END

ELSE

BEGIN

UPDATE tbl_wsSTUAPP_WEXP

SET wsSTUAPP_WEXP_iOrder= @.wsSTUAPP_WEXP_iOrder

,wsSTUAPP_WEXP_sEmployerName= @.wsSTUAPP_WEXP_sEmployerName

,wsSTUAPP_WEXP_sJobTitle= @.wsSTUAPP_WEXP_sJobTitle

,wsSTUAPP_WEXP_sJobDESC= @.wsSTUAPP_WEXP_sJobDESC

,wsSTUAPP_WEXP_dStart= @.wsSTUAPP_WEXP_dStart

,wsSTUAPP_WEXP_dEnd= @.wsSTUAPP_WEXP_dEnd

WHERE wsSTUAPP_WEXP_iID= @.wsSTUAPP_WEXP_iID

END

ERROR MESSAGE:

Command attempted:

CREATE PROCEDURE "dbo"."sp_wsSetSTUAPPWEXP"
@.wsSTUAPP_WEXP_iID int
, @.wsSTUAPP_iID int
, @.wsSTUAPP_WEXP_iOrder int
, @.wsSTUAPP_WEXP_sEmployerName varchar(75)
, @.wsSTUAPP_WEXP_sJobTitle varchar(75)
, @.wsSTUAPP_WEXP_sJobDESC varchar(360)
, @.wsSTUAPP_WEXP_dStart datetime
, @.wsSTUAPP_WEXP_dEnd datetime
AS

IF NOT EXISTS(SELECT wsSTUAPP_WEXP_iID FROM tbl_wsSTUAPP_WEXP WHERE wsSTUAPP_WEXP_iID= @.wsSTUAPP_WEXP_iID)
BEGIN
INSERT INTO tbl_wsSTUAPP_WEXP
VALUES(
@.wsSTUAPP_iID
,@.wsSTUAPP_WE
(Transaction sequence number: 0x00006D8E00000160000B00000000, Command ID: 12)

Error messages:

Insert Error: Column name or number of supplied values does not match table definition. (Source: MSSQLServer, Error number: 213)
Get help: http://help/213

Insert Error: Column name or number of supplied values does not match table definition. (Source: MSSQLServer, Error number: 213)
Get help: http://help/213

Hi Jim,

You are likely to be hitting the same issue as discussed in the following forum thread:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=493307&SiteID=1

and this is basically the unintended consequence of the interaction among the following factors:

1) Replicated identity columns are marked "not for replication" by default at the publisher when you setup replication through SSMS in SQL2005

2) Any insert statements into a table with an identity column marked 'not for replication' executed from a replication agent (distribution agent for example) must supply an explicit value for the identity column

3) In SQL2005, the server verifies the validity of insert statements in stored procedures and triggers during the creation of these objects and raises error if it determines that an embedded insert statement does not supply all the required values.

In more concrete terms, 1) will basically lead to all identity columns being marked 'not for replication' at both the publisher and the subscriber and given that your replicated stored procedure is going to be created through a distribution agent connection, 2) and 3) will lead the subscriber server into thinking that the insert statement embedded in your stored procedure would need to supply an explicit value for the identity column marked 'not for replication' and so it raises an error when an explicit identity value is found missing. Now, the stored procedure that you are replicating is probably intended to be executed outside of replication even though it is created through replication so the check is technically invalid despite the good intentions behind it.

The only workaround that I can think of is to use the undocumented system procedure 'sp_identitycolumnforreplication' to disable the 'not for replication' property on all the identity columns at the publisher and then re-initialize your subscriber using a new snaphsot. This is admittedly not a very good workaround but the good news is that this particular problem is fixed for snapshot processing in SP2. The bad news is that the same problem applies to our ddl replication logic (alteration of stored procedure e.g.) and we don't have a general solution in that part of replication yet. My suggestion would be to separate your schema objects (stored procedures) into a separate snapshot publication so changes to your stored procedures etc. can be refreshed through snapshot processing when SP2 comes out.

Hope that hope,

-Raymond

|||

The failure may be caused by implict column list in INSERT statement. Do you replicate all the columns in the table or are there any identity column in the replicated table? You can try to explicitly specify the column list in your INSERT statement.

Hope it helps.

Peng

|||

Thanks Raymond and Peng,

The problem has been sloved by the following

"use the undocumented system procedure 'sp_identitycolumnforreplication' to disable the 'not for replication' property on all the identity columns at the publisher"

I really appreciate it!

Jim

|||

Hello Raymond,

I failed to use 'sp_identitycolumnforreplication' to disable the 'not for replication' property on the identity columns. However, it was succeessful before. But it doesn't work any more.

I tried to restore the database, and re-install SQL server, no use.

How to fix 'sp_identitycolumnforreplication' ?

Here is my query:

declare @.int int

set @.int =object_id('tbl_wsSTUAPP_WEXP')

exec sys.sp_identitycolumnforreplication @.int,0

GO

Thank you!

Replication problem with stored procedure

I am working on a transaction replication, i have no problem with all the tables, but when adding the stored procedures, i got the error message:

Insert Error: Column name or number of supplied values does not match table definition.

The first column - wsSTUAPP_WEXP_iID 's "identity incremnet" definition of table tbl_wsSTUAPP_WEXP is set to 1.

The system works well in the publisher server, and the table itself has no problem for replication, but when adding the stored procedure to the replication article, the problem happens.

Please advise how to slove this problem. Below is the part of the store procedure and the error message:

THANK YOU IN ADVANCE!

STOREd PROCEDURE:

ALTER PROCEDURE [dbo].[sp_wsSetSTUAPPWEXP]

@.wsSTUAPP_WEXP_iID int

, @.wsSTUAPP_iID int

, @.wsSTUAPP_WEXP_iOrder int

, @.wsSTUAPP_WEXP_sEmployerName varchar(75)

, @.wsSTUAPP_WEXP_sJobTitle varchar(75)

, @.wsSTUAPP_WEXP_sJobDESC varchar(360)

, @.wsSTUAPP_WEXP_dStart datetime

, @.wsSTUAPP_WEXP_dEnd datetime

AS

IF NOT EXISTS(SELECT wsSTUAPP_WEXP_iID FROM tbl_wsSTUAPP_WEXP WHERE wsSTUAPP_WEXP_iID= @.wsSTUAPP_WEXP_iID)

BEGIN

INSERT INTO tbl_wsSTUAPP_WEXP

VALUES(

@.wsSTUAPP_iID

,@.wsSTUAPP_WEXP_iOrder

,@.wsSTUAPP_WEXP_sEmployerName

,@.wsSTUAPP_WEXP_sJobTitle

,@.wsSTUAPP_WEXP_sJobDESC

,@.wsSTUAPP_WEXP_dStart

,@.wsSTUAPP_WEXP_dEnd

)

END

ELSE

BEGIN

UPDATE tbl_wsSTUAPP_WEXP

SET wsSTUAPP_WEXP_iOrder= @.wsSTUAPP_WEXP_iOrder

,wsSTUAPP_WEXP_sEmployerName= @.wsSTUAPP_WEXP_sEmployerName

,wsSTUAPP_WEXP_sJobTitle= @.wsSTUAPP_WEXP_sJobTitle

,wsSTUAPP_WEXP_sJobDESC= @.wsSTUAPP_WEXP_sJobDESC

,wsSTUAPP_WEXP_dStart= @.wsSTUAPP_WEXP_dStart

,wsSTUAPP_WEXP_dEnd= @.wsSTUAPP_WEXP_dEnd

WHERE wsSTUAPP_WEXP_iID= @.wsSTUAPP_WEXP_iID

END

ERROR MESSAGE:

Command attempted:

CREATE PROCEDURE "dbo"."sp_wsSetSTUAPPWEXP"
@.wsSTUAPP_WEXP_iID int
, @.wsSTUAPP_iID int
, @.wsSTUAPP_WEXP_iOrder int
, @.wsSTUAPP_WEXP_sEmployerName varchar(75)
, @.wsSTUAPP_WEXP_sJobTitle varchar(75)
, @.wsSTUAPP_WEXP_sJobDESC varchar(360)
, @.wsSTUAPP_WEXP_dStart datetime
, @.wsSTUAPP_WEXP_dEnd datetime
AS

IF NOT EXISTS(SELECT wsSTUAPP_WEXP_iID FROM tbl_wsSTUAPP_WEXP WHERE wsSTUAPP_WEXP_iID= @.wsSTUAPP_WEXP_iID)
BEGIN
INSERT INTO tbl_wsSTUAPP_WEXP
VALUES(
@.wsSTUAPP_iID
,@.wsSTUAPP_WE
(Transaction sequence number: 0x00006D8E00000160000B00000000, Command ID: 12)

Error messages:

Insert Error: Column name or number of supplied values does not match table definition. (Source: MSSQLServer, Error number: 213)
Get help: http://help/213

Insert Error: Column name or number of supplied values does not match table definition. (Source: MSSQLServer, Error number: 213)
Get help: http://help/213

Hi Jim,

You are likely to be hitting the same issue as discussed in the following forum thread:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=493307&SiteID=1

and this is basically the unintended consequence of the interaction among the following factors:

1) Replicated identity columns are marked "not for replication" by default at the publisher when you setup replication through SSMS in SQL2005

2) Any insert statements into a table with an identity column marked 'not for replication' executed from a replication agent (distribution agent for example) must supply an explicit value for the identity column

3) In SQL2005, the server verifies the validity of insert statements in stored procedures and triggers during the creation of these objects and raises error if it determines that an embedded insert statement does not supply all the required values.

In more concrete terms, 1) will basically lead to all identity columns being marked 'not for replication' at both the publisher and the subscriber and given that your replicated stored procedure is going to be created through a distribution agent connection, 2) and 3) will lead the subscriber server into thinking that the insert statement embedded in your stored procedure would need to supply an explicit value for the identity column marked 'not for replication' and so it raises an error when an explicit identity value is found missing. Now, the stored procedure that you are replicating is probably intended to be executed outside of replication even though it is created through replication so the check is technically invalid despite the good intentions behind it.

The only workaround that I can think of is to use the undocumented system procedure 'sp_identitycolumnforreplication' to disable the 'not for replication' property on all the identity columns at the publisher and then re-initialize your subscriber using a new snaphsot. This is admittedly not a very good workaround but the good news is that this particular problem is fixed for snapshot processing in SP2. The bad news is that the same problem applies to our ddl replication logic (alteration of stored procedure e.g.) and we don't have a general solution in that part of replication yet. My suggestion would be to separate your schema objects (stored procedures) into a separate snapshot publication so changes to your stored procedures etc. can be refreshed through snapshot processing when SP2 comes out.

Hope that hope,

-Raymond

|||

The failure may be caused by implict column list in INSERT statement. Do you replicate all the columns in the table or are there any identity column in the replicated table? You can try to explicitly specify the column list in your INSERT statement.

Hope it helps.

Peng

|||

Thanks Raymond and Peng,

The problem has been sloved by the following

"use the undocumented system procedure 'sp_identitycolumnforreplication' to disable the 'not for replication' property on all the identity columns at the publisher"

I really appreciate it!

Jim

|||

Hello Raymond,

I failed to use 'sp_identitycolumnforreplication' to disable the 'not for replication' property on the identity columns. However, it was succeessful before. But it doesn't work any more.

I tried to restore the database, and re-install SQL server, no use.

How to fix 'sp_identitycolumnforreplication' ?

Here is my query:

declare @.int int

set @.int =object_id('tbl_wsSTUAPP_WEXP')

exec sys.sp_identitycolumnforreplication @.int,0

GO

Thank you!

Replication problem (please help if you can)

Since our outsourced database team made some changes to the database we
are getting the following from the Distribution Agent Error Details:
{call sp_MSget_repl_commands(5, ?, 0, 7500000)}
Violation of PRIMARY KEY constraint 'PK__@.snapshot_seqnos__7AB9340C'.
Cannot insert duplicate key in object '#79C50FD3'.
Violation of PRIMARY KEY constraint 'PK__@.snapshot_seqnos__7AB9340C'.
Cannot insert duplicate key in object '#79C50FD3'.
(Source: INTRANETSVR (Data source); Error number: 2627)
----
--
The it's a transactional replica that has been running happily for some
time now, and I am only a db admin with very little experience in
trouble shooting.
thanks in advance.
Kev
kevintomlinson@.hotmail.comOn the destination database run
select object_name(79C50FD3)
That should give you the name of the table that's having the problem.
If you are pushing from the distributor (simplest to administer) then look
at the distribution agent - it should be showing an error.
If you restart it then it should give the same error. I think you can get he
command from one of the windows there but I usually use the profiler on the
subscriber, start the agent and the last instruction recorded will be the on
e
in error.
Then you have to find out why this is causing a problem.
Could be that the index on the destination is not correct and doesn't match
the source (just change it) ot the destination data could be out of step wit
h
the source.
If that's the case then you have a choice between changing the data on the
destination of re-snapshotting (just the table if possible - depends on your
settup).
"Kev" wrote:

> Since our outsourced database team made some changes to the database we
> are getting the following from the Distribution Agent Error Details:
>
> {call sp_MSget_repl_commands(5, ?, 0, 7500000)}
> Violation of PRIMARY KEY constraint 'PK__@.snapshot_seqnos__7AB9340C'.
> Cannot insert duplicate key in object '#79C50FD3'.
> Violation of PRIMARY KEY constraint 'PK__@.snapshot_seqnos__7AB9340C'.
> Cannot insert duplicate key in object '#79C50FD3'.
> (Source: INTRANETSVR (Data source); Error number: 2627)
> ----
--
> The it's a transactional replica that has been running happily for some
> time now, and I am only a db admin with very little experience in
> trouble shooting.
> thanks in advance.
> Kev
> kevintomlinson@.hotmail.com
>

Replication problem "can not insert null into colum step_name in sysjobs".

When trying to create a new publication using the wizzard i get the error message

"cannot insert null into colum step_name in sysjobs".

Replication was working fine on the server before. It stopped working when i installed SP3, i removed the old replication and tried setting it up again but get the error above when creating the publication.

Any ideas would be extremely useful. I have been unable to find any details of this on the web.

Many Thanks
Ash.Are you sure the clean up was successful? What I mean is there maybe some replication setups still reside in the system tables. I would check sysjobs first to see if there are unrecognized jobs in it and go from there.|||Originally posted by joejcheng
Are you sure the clean up was successful? What I mean is there maybe some replication setups still reside in the system tables. I would check sysjobs first to see if there are unrecognized jobs in it and go from there.

Thanks for the reply joejcheng.

I've tried quite a few different things. Like you the first thing I thought was to look in sysjobs and sysjobsteps, however there isn't anything in there that looks untoward.

Like you I'm inclined to think its something that hasn't been completely removed. I'd do a reinstall but its a production server so this isn't an option.

Replication problem

I'm getting this error with a transactional replication
publication I'm working with, I think my distribution
database is hosed, I got another error cannot find object
syspublications, I'm just kind of wondering how it got
that way and how I can fix it...
TIA
SQL Server Enterprise Manager encountered errors creating
push subscriptions for the following Subscribers:
MNCOROLAPTST-V1: Error 2601: Cannot insert duplicate key
row in object 'MSsubscriptions' with unique
index 'ucMSsubscriptions'.
Could not update the distribution database subscription
table. The subscription status could not be changed.
The subscription could not be created.
The statement has been terminated.
BTW, has anyone noticed that the msnews.microsoft.com
server was down to day, or is that just me...?
Its just you
Your problem though sounds very unusual. How are you creating your
subscriptions? Via a script?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:112301c4a1b3$ae62e070$a301280a@.phx.gbl...
> I'm getting this error with a transactional replication
> publication I'm working with, I think my distribution
> database is hosed, I got another error cannot find object
> syspublications, I'm just kind of wondering how it got
> that way and how I can fix it...
> TIA
>
> SQL Server Enterprise Manager encountered errors creating
> push subscriptions for the following Subscribers:
> MNCOROLAPTST-V1: Error 2601: Cannot insert duplicate key
> row in object 'MSsubscriptions' with unique
> index 'ucMSsubscriptions'.
> Could not update the distribution database subscription
> table. The subscription status could not be changed.
> The subscription could not be created.
> The statement has been terminated.
>
> BTW, has anyone noticed that the msnews.microsoft.com
> server was down to day, or is that just me...?
|||Hello Hilary,
We typically use Enterprise Manager Replication >
Publications > Publication [Context Menu] Push New
subscription.. This was working without any problems
before our tech ops guys restored a database that happens
to contain the articles for the publication on the
server...
Can I blow away the distribution database and start
over...? Right now I cannot delete any pubs create any
subscriptions or anything else with replication it's
totally useless on this box..
Thanks, I appreciate your help!!
Dan

>--Original Message--
>Its just you
>Your problem though sounds very unusual. How are you
creating your
>subscriptions? Via a script?
>--
>Hilary Cotter
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>
>"Dan" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:112301c4a1b3$ae62e070$a301280a@.phx.gbl...
object[vbcol=seagreen]
creating[vbcol=seagreen]
key
>
>.
>
|||Now it all makes sense. An easy way around this is to recreate your
publications with a different name.
However, you will probably be best to disable replication (tools,
replication, disable publishing) and then by re-enabling your server for
replication.
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:034201c4a1bc$28d8b1b0$a501280a@.phx.gbl...[vbcol=seagreen]
> Hello Hilary,
> We typically use Enterprise Manager Replication >
> Publications > Publication [Context Menu] Push New
> subscription.. This was working without any problems
> before our tech ops guys restored a database that happens
> to contain the articles for the publication on the
> server...
> Can I blow away the distribution database and start
> over...? Right now I cannot delete any pubs create any
> subscriptions or anything else with replication it's
> totally useless on this box..
> Thanks, I appreciate your help!!
> Dan
>
> creating your
> message
> object
> creating
> key
|||That's exactly what I was looking for...
Thanks!!!
Dan

>--Original Message--
>Now it all makes sense. An easy way around this is to
recreate your
>publications with a different name.
>However, you will probably be best to disable replication
(tools,
>replication, disable publishing) and then by re-enabling
your server for
>replication.
>"Dan" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:034201c4a1bc$28d8b1b0$a501280a@.phx.gbl...
happens[vbcol=seagreen]
replication[vbcol=seagreen]
subscription
>
>.
>

Replication problem

I am trying to set up a pull subscription replication using SQL Server
Enterprise Manager. I get an error stating:
.....It cannot create named subscription because the publication does not
allow named pull subscription...
How do I enable the named pull subscription on the publisher?
Thanks in advance.
Hi Antonin,
You may have to enable Pull Subscription and Anonymous Subscription for the
Publication.
You may achieve this by
1. Click on Tools -> Replication -> Create and Manage Publication.
2. Expand the Published Database and select the publication name.
3. Click on Properties and Subscription
4. In the Publication Properties Dialog Box
5. Switch to Subscription Option tab
6. In this tab you will find two checkboxes under "Subscription Creation"
option,
"Allow Pull Subscription"
"Allow Anonymous Subscription"
Please tick both the check boxes to allow Pull Anonymous Subscription
for that publication.
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi Ashish,
Thanks for your reply. I followed your instructions and it worked OK.
However, there is Last Action on the publisher " A snapshot was not
generated because no subscription needed initialization.
Antonin
"Ashish Ruparel [MSFT]" <v-ashrup@.online.microsoft.com> wrote in message
news:TeZcQaXOEHA.1016@.cpmsftngxa10.phx.gbl...
> Hi Antonin,
> You may have to enable Pull Subscription and Anonymous Subscription for
the
> Publication.
> You may achieve this by
> 1. Click on Tools -> Replication -> Create and Manage Publication.
> 2. Expand the Published Database and select the publication name.
> 3. Click on Properties and Subscription
> 4. In the Publication Properties Dialog Box
> 5. Switch to Subscription Option tab
> 6. In this tab you will find two checkboxes under "Subscription Creation"
> option,
> "Allow Pull Subscription"
> "Allow Anonymous Subscription"
> Please tick both the check boxes to allow Pull Anonymous Subscription
> for that publication.
>
> HTH
> Ashish
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>

Replication problem

I am trying to set up a pull subscription replication using SQL Server
Enterprise Manager. I get an error stating:
....It cannot create named subscription because the publication does not
allow named pull subscription...
How do I enable the named pull subscription on the publisher?
Thanks in advance.Hi Antonin,
You may have to enable Pull Subscription and Anonymous Subscription for the
Publication.
You may achieve this by
1. Click on Tools -> Replication -> Create and Manage Publication.
2. Expand the Published Database and select the publication name.
3. Click on Properties and Subscription
4. In the Publication Properties Dialog Box
5. Switch to Subscription Option tab
6. In this tab you will find two checkboxes under "Subscription Creation"
option,
"Allow Pull Subscription"
"Allow Anonymous Subscription"
Please tick both the check boxes to allow Pull Anonymous Subscription
for that publication.
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Ashish,
Thanks for your reply. I followed your instructions and it worked OK.
However, there is Last Action on the publisher " A snapshot was not
generated because no subscription needed initialization.
Antonin
"Ashish Ruparel [MSFT]" <v-ashrup@.online.microsoft.com> wrote in message
news:TeZcQaXOEHA.1016@.cpmsftngxa10.phx.gbl...
> Hi Antonin,
> You may have to enable Pull Subscription and Anonymous Subscription for
the
> Publication.
> You may achieve this by
> 1. Click on Tools -> Replication -> Create and Manage Publication.
> 2. Expand the Published Database and select the publication name.
> 3. Click on Properties and Subscription
> 4. In the Publication Properties Dialog Box
> 5. Switch to Subscription Option tab
> 6. In this tab you will find two checkboxes under "Subscription Creation"
> option,
> "Allow Pull Subscription"
> "Allow Anonymous Subscription"
> Please tick both the check boxes to allow Pull Anonymous Subscription
> for that publication.
>
> HTH
> Ashish
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>