Could not find stored procedure 'sp_MSint_t_ElecConnData'

Question

Post reply

Could not find stored procedure 'sp_MSint_t_ElecConnData'

Viewing 15 posts - 16 through 30 (of 39 total)

You must be logged in to reply to this topic. Login to reply

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 1

Can you run this command on the distributor?

Use Master

go

Select @@Servername

Check if it gives the right name.

-Roy

Patrick Kenyon SSC Veteran Points: 296 More actions · Answer 2

Yep, that seems to work just fine. As a side note, I checked into the jobs that were failing and I am getting this error:

"Agent message code 20084. The process could not connect to Distributor"

Patrick

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 3

Log on to your publisher and then try connecting to your distributor using SSMS. I have a bad feeling that your connections from the publisher to distributor is somehow getting blocked. Could be the ports are not open or a firewall is blocking the traffic.

-Roy

Patrick Kenyon SSC Veteran Points: 296 More actions · Answer 4

Ah! The repl_distributor in the linked servers is using distributor_admin as its login, whereas everything else is pointed to ALPHA\SqlAgent2005Prod. Is that OK, or do I need to make sure that this distributor_admin is created on both servers or is distributor_admin just a facade for different credentials? Am I way off base here?

Patrick

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 5

Add the distributor admin to the sysadmin group. On both the publisher and the distributor. When you add an article, the Distributor_admin adds a row in the syspublication table. For that it uses a RPC Link connection using the Distributor_admin.

-Roy

Patrick Kenyon SSC Veteran Points: 296 More actions · Answer 6

I've been through everything that I can possibly fathom being used as a login and I'm still getting the following:

"

2010-11-30 18:49:04.606 Status: 0, code: 1007, text: 'The process could not connect to server 'DTW-SQLSHADOW02'.'.

2010-11-30 18:49:04.606 The process could not connect to server 'DTW-SQLSHADOW02'."

Patrick

Patrick Kenyon SSC Veteran Points: 296 More actions · Answer 7

I should probably clarify that is my distributor.

Patrick

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 8

In your SQL Server Log and Event viewer, that is all the errors details you get? Or are there more errors? Also we need to find out what is failing. Can you start up profiler and then try restarting the log reader agent? I would like to know if there is something wrong with the call.

-Roy

Patrick Kenyon SSC Veteran Points: 296 More actions · Answer 9

Thanks for pointing me to the event viewer. Here is the error it's been throwing for 5 hours:

" ALPHA\dtwservice

Reason: Failed to open the database specified in the login properties.

[CLIENT: <local machine>] "

I suspected that was that was a possible login when you sent me back last time and gave it sysadmin on both servers.

I did my first trace and I was seeing the same error. Where do I go to investigate that? I have no idea where the issue could possibly be.

Patrick

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 10

On the distributor Database, ALPHA\dtwservice, what is the Default database set for it? Make sure that the default DB is Master. Since it is a sysadmin in all the instances, just make the default database as master.

Also check one thing, if there is any Deny rule set for this user.

-Roy

Patrick Kenyon SSC Veteran Points: 296 More actions · Answer 11

Roy,

Thanks for hanging with me all day on this! I assumed that the logins always defaulted to master, but apparently that's not the case.

Unfortunately, that gets me back to where I started where the distributor can't find the replication SPs again. This time it's sp_MSins_{table name} . I assume that it'd be any of the SPs used for replication, at this point. What am I not seeing here?

Error: "Replication Distribution Subsystem

STT_SQL-TradePrograms-PursueReadOnly-DTW-SQLSHADOW02-10

Could not find stored procedure 'sp_MSins_dboDocumentNotes'. "

Patrick

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 12

Check in your Subscriber database if the stored proc sp_MSins_dboDocumentNotes exists in your replicated database and the service used in the distributor and the distributor_admin have DBO rights on the Subscribers replicated database.

-Roy

Patrick Kenyon SSC Veteran Points: 296 More actions · Answer 13

Yes, it all does. This is how we started. 🙂

Patrick

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 14

Now run the profiler and find out the exact cause of the problm. I have seen case where it sends two commands for the same SP.

Also run the sp_browsereplcmds and find out more details regarding it.

-Roy

Patrick Kenyon SSC Veteran Points: 296 More actions · Answer 15

Thanks for pointing me to sp_browsereplcmds. Is it possible that replication would get hung up if it's trying to insert a PK into a table that already has that key, hence it giving me "Message: if @@trancount > 0 rollback tran

2010-12-01 13:27:45.078 Category:NULL

Source: Microsoft SQL Server Native Client 10.0

Number: 2812

Message: Could not find stored procedure 'sp_MSins_dboSTTDocumentNotes'.", but in actuality it can find the SP, but keeps trying to insert a duplicate key?

That's about the only thing I've got and that's just because I tried to execute the first line that fails on the sp_browsereplcmds. Maybe I'm getting ahead of myself?

Patrick