Cannot create publication after SP1 upgrade

After upgrading SQL Server 2005 with the recent service pack, I am unable to create a publication. When I right-click on [INSTANCE]/Replication/Local Publications and select "New Publication...," the mouse cursor turns into an hourglass, but the wizard never shows up, nor is any error displayed.

There is a logged error that's been showing up that may or may not be related. The error message is "Replication-(null): agent (null) scheduled for retry. Could not clean up the distribution history tables."

Any ideas on how we can restore replication functionality on our SQL Server instance?

Can you run profiler before invoking New Publication wizard to trace which statement is causing the problem? What kind of publications do you have before upgrade to SP1? Is distributor on the same machine as publisher?

Thanks,

Zhiqiang Feng

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

|||I ran the profiler, and I'm getting some strange output. Apparently, the command "select * from sysdatabases where name=N'distribution'" is being run over and over again. Strangely enough, there is a row returned from that command that points to where our distribution database was located, but the database isn't shown under the system databases in Management Studio.

Before upgrading to SP1, we were using merge publications with the distributor on the same machine as the publisher.

|||

Can you send sp1 upgrade log files and profiler trace to me? I will send you a separate email.

Thanks,

Zhiqiang Feng

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

|||

From the sp1 upgrade log file, looks like you have run SP1 upgrade twice, the first time it runs, SQLDEV01 machine is treated as a distributor and distribution database is upgrade successfully(from what the log file says). The 2nd time the sp1 upgrade runs, it didn't treat this machine as distributor and didn't upgrade distribution database (also you mentioned distribution database is no longer marked as system database, which is suspicious, we need to figure out why). Did you notice any error during sp1 upgrade? Why you run sp1 upgrade twice? Did you drop distributor or change replication settings after first sp1 upgrade?

Please also email a profiler trace for the New publication wizard so we can further diagnose the issue. When using profiler, make sure select following options (and unselect any options not mentioned below):

Error and Warnings --> ErrorLog, Exceptions, User Error Message

Stored Procedures --> RPC Starting, RPC Completed, SP Starting, SP Completed, SP Stmt Starting

TSQL --> SQL: Batch Starting, SQL: Batch Completed, SQL: StmtStarting

Thanks,

Zhiqiang Feng

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

|||It's finally working! I suspect that all the problems stemmed from there being an inconsistency in the sysdatabases table. I ran "DROP DATABASE distributor" under the master database, and afterward, I was able to set up the distributor, publishers, and subscribers without any problems.

Thank you, Zhiqiang Feng, for your email assistance.

Cannot create Merge Replication in 2005

Hi to All,
I had sql 2005 with sp3 and i was working succesfully with merge
replication on Sql ce.
Yestarday i upgrade to sql 2005 (first upgrate my sql200 with sp4)
Now i am here in sql 2005 trying to create a new merge replication on
windows mobile 2005 and i get to the final step this error
------
TITLE: New Publication Wizard
New Publication Wizard encountered one or more errors while retrieving
publication names.
For help, click:
http://go.microsoft.com/fwlink?ProdN...s&LinkId=20476
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or
batch. (Microsoft.SqlServer.ConnectionInfo)
Invalid column name 'snapshot_jobid'.
Invalid column name 'use_partition_groups'.
Invalid column name 'replicate_ddl'.
Invalid column name 'publication_number'.
Invalid column name 'allow_subscriber_initiated_snapshot'.
Invalid column name 'allow_web_synchronization'.
Invalid column name 'web_synchronization_url'.
Invalid column name 'allow_partition_realignment'.
Invalid column name 'retention_period_unit'.
Invalid column name 'decentralized_conflicts'.
Invalid column name 'generation_leveling_threshold'.
Invalid column name 'automatic_reinitialization_policy'.
Invalid column name 'snapshot_jobid'.
Invalid column name 'use_partition_groups'.
Invalid column name 'replicate_ddl'.
Invalid column name 'publication_number'.
Invalid column name 'allow_subscriber_initiated_snapshot'.
Invalid column name 'allow_web_synchronization'.
Invalid column name 'web_synchronization_url'.
Invalid column name 'allow_partition_realignment'.
Invalid column name 'retention_period_unit'.
Invalid column name 'decentralized_conflicts'.
Invalid column name 'generation_leveling_threshold'.
Invalid column name 'automatic_reinitialization_policy'. (Microsoft SQL
Server, Error: 207)
For help, click:
http://go.microsoft.com/fwlink?ProdN...7&LinkId=20476
------
Can anyone please help ?
Thanks
Savvas
What all did you do when you upgraded?
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
<savvaschr@.nodalsoft.com.cy> wrote in message
news:1139562228.104138.260770@.f14g2000cwb.googlegr oups.com...
> Hi to All,
> I had sql 2005 with sp3 and i was working succesfully with merge
> replication on Sql ce.
> Yestarday i upgrade to sql 2005 (first upgrate my sql200 with sp4)
> Now i am here in sql 2005 trying to create a new merge replication on
> windows mobile 2005 and i get to the final step this error
> ------
> TITLE: New Publication Wizard
> --
> New Publication Wizard encountered one or more errors while retrieving
> publication names.
> For help, click:
> http://go.microsoft.com/fwlink?ProdN...s&LinkId=20476
> --
> ADDITIONAL INFORMATION:
> An exception occurred while executing a Transact-SQL statement or
> batch. (Microsoft.SqlServer.ConnectionInfo)
> --
> Invalid column name 'snapshot_jobid'.
> Invalid column name 'use_partition_groups'.
> Invalid column name 'replicate_ddl'.
> Invalid column name 'publication_number'.
> Invalid column name 'allow_subscriber_initiated_snapshot'.
> Invalid column name 'allow_web_synchronization'.
> Invalid column name 'web_synchronization_url'.
> Invalid column name 'allow_partition_realignment'.
> Invalid column name 'retention_period_unit'.
> Invalid column name 'decentralized_conflicts'.
> Invalid column name 'generation_leveling_threshold'.
> Invalid column name 'automatic_reinitialization_policy'.
> Invalid column name 'snapshot_jobid'.
> Invalid column name 'use_partition_groups'.
> Invalid column name 'replicate_ddl'.
> Invalid column name 'publication_number'.
> Invalid column name 'allow_subscriber_initiated_snapshot'.
> Invalid column name 'allow_web_synchronization'.
> Invalid column name 'web_synchronization_url'.
> Invalid column name 'allow_partition_realignment'.
> Invalid column name 'retention_period_unit'.
> Invalid column name 'decentralized_conflicts'.
> Invalid column name 'generation_leveling_threshold'.
> Invalid column name 'automatic_reinitialization_policy'. (Microsoft SQL
> Server, Error: 207)
> For help, click:
> http://go.microsoft.com/fwlink?ProdN...7&LinkId=20476
> ------
> Can anyone please help ?
> Thanks
> Savvas
>

Cannot connect to sqlexpress from ssmse (ctp)

I have been using sqlexpress for several months and decided to upgrade the ssmse to the november ctp version. I uninstalled the old ssmse and installed the november release from ctp.

I verified that the tcp/ip and named pipes are enable in the config manager. I attempted using these connection strings:

\sqlexpress, (local)\sqlexpress, or server name\sqlexpress

All fail with the following message:

Cannot connect to WALLY1\sqlexpress

--
ADDITIONAL INFORMATION:

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. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476

BUTTONS:

OK

We need a little more information to troubleshoot your problem.

Are you running SSMSE and SQLExpress on the same machine? Are you able to connect to SQLExpress using SQLCMD?

Thanks,
Dan

|||Try to connect either with TCP/IP or named-pipes enabled and not with both the options enabled.
I have had this problem before and when used named-pipes alone it resolved the problem and ensure the ALIAS is defined properly and you can ping the server.|||

Are you running everything from the same machine or are you trying to connect to a remote SQL Server?

Cheers,
Dan

Cannot connect to SQL Server 2005 after Active Directory upgrade

Hi, we recently installed Active Directory here and I can no longer connect to our SQL Server 2005 box. Active Directory was installed on a different server and my development machine was added to the Active Directory. The AD server was also made a domain controller and my development machine was also added to the domain. The SQL Server sits outside the domain, and is NOT included in AD. Now when I attempt to connect via SQL authentication in my c# code, I get the following error:

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. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

If I attempt to connect to the SQL Server box using SQL authentication from SQL Server Management Studio, the connection attempt simply times out. However, if I change the protocol used to connect to Named Pipes, I am able to connect to the SQL box.

Can anyone tell me why I cannot connect to the SQL box using SQL authentication and TCP/IP anymore?

I apologize if this post is misplaced.

I'm moving your question to the database engine forum where I hope they can help...

Paul

|||What is your connection string? If it is "<machinename>\<instancename>", then you could try to change this to "<machinename>\<instancename,<portnumber>". Maybe this will help.