Showing posts with label error. Show all posts
Showing posts with label error. Show all posts

Thursday, March 29, 2012

Cannot export to Excel from Report Manager

When attempting to export to Excel from report manager we get the error
"Internet Explorer cannot download Format=Ecels from reports.server"
Internet Explorer was not able to open this Internet site. The requested
site is either unavailable or cannot be found. Please try again later."
This only happens when the person accessing reports manager is not a local
administrator. Local administrator privileges make the problem go away.
What is report manager trying to access that I need to give my user access
to? I cannot make the user a local admin.The problem does not just occur if exporting to excel. It's all file types.
Any file type we try to export to from report manager kicks back essentially
the same error we were getting when trying to export to excel. Oddly enough,
the development server which uses 2k3 server doesn't do this. The production
server that is running Server 2K does.
"Valleri_Collins" wrote:
> When attempting to export to Excel from report manager we get the error
> "Internet Explorer cannot download Format=Excel from reports.server"
> Internet Explorer was not able to open this Internet site. The requested
> site is either unavailable or cannot be found. Please try again later."
> This only happens when the person accessing reports manager is not a local
> administrator. Local administrator privileges make the problem go away.
> What is report manager trying to access that I need to give my user access
> to? I cannot make the user a local admin.

Cannot EXPORT a REPORT to EXCEL

Hi,
I created a report which works just fine. But when I try to export to excel,
i get the following error:
Reporting Services Erro
----
Exception of type
Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
thrown. (rrRenderingError) Get Online Help
Exception of type
Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
thrown.
Object reference not set to an instance of an object.
Any ideas?
Thanks
--
pmudif you're using RS2000 do u have sp2?
"pmud" wrote:
> Hi,
> I created a report which works just fine. But when I try to export to excel,
> i get the following error:
> Reporting Services Error
> ----
> Exception of type
> Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
> thrown. (rrRenderingError) Get Online Help
> Exception of type
> Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
> thrown.
> Object reference not set to an instance of an object.
> Any ideas?
> Thanks
> --
> pmud

Tuesday, March 27, 2012

cannot establish a connection in sql server 2005 express

hi all

here is the following error, what should i do.

TITLE: Connect to Server

Cannot connect to KANGALERT\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

Hi,

Please make sure you've followed all of the steps at http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx in order to enable remote connectivity.

Thanks

Cannot enable the Service Broker

When I try to enable the Service Broker I receive the following error message:
Cannot enable the Service Broker in database "msdb" because the Service
Broker GUID in the database (01F9916A-21C0-4EA1-A555-A9C7A020C718) does not
match the one in sys.databases (A7B475E6-5781-4FC2-B1E0-B0EF17FFE942).
ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 9776)
So as far as I can understand in the MSDB database the Service Broker GUID
is different from the one in the table sys.databases.
Is there a possibility to correct this so I will be able to enable the
Service Broker?
I want to enable it so I can configure the email part on this SQL server.
If the GUID itself can not be changed is there a way to rebuild the MSDB
database?
Thanks for the info,
Limmer
Try:
ALTER DATABASE MSDB SET NEW_BROKER
If SQL Agent is running, you will have to shut it down or this will hang.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Limmer" <Limmer@.discussions.microsoft.com> wrote in message
news:71240018-A03C-4F0D-830A-537F95BBB662@.microsoft.com...
> When I try to enable the Service Broker I receive the following error
> message:
> Cannot enable the Service Broker in database "msdb" because the Service
> Broker GUID in the database (01F9916A-21C0-4EA1-A555-A9C7A020C718) does
> not
> match the one in sys.databases (A7B475E6-5781-4FC2-B1E0-B0EF17FFE942).
> ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 9776)
> So as far as I can understand in the MSDB database the Service Broker GUID
> is different from the one in the table sys.databases.
> Is there a possibility to correct this so I will be able to enable the
> Service Broker?
> I want to enable it so I can configure the email part on this SQL server.
> If the GUID itself can not be changed is there a way to rebuild the MSDB
> database?
> Thanks for the info,
> Limmer
>
|||Thanks Roger,
This did the trick.
Limmer
"Roger Wolter[MSFT]" wrote:

> Try:
> ALTER DATABASE MSDB SET NEW_BROKER
> If SQL Agent is running, you will have to shut it down or this will hang.
>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Limmer" <Limmer@.discussions.microsoft.com> wrote in message
> news:71240018-A03C-4F0D-830A-537F95BBB662@.microsoft.com...
>
>

Cannot enable the Service Broker

When I try to enable the Service Broker I receive the following error messag
e:
Cannot enable the Service Broker in database "msdb" because the Service
Broker GUID in the database (01F9916A-21C0-4EA1-A555-A9C7A020C718) does not
match the one in sys.databases (A7B475E6-5781-4FC2-B1E0-B0EF17FFE942).
ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 9776)
So as far as I can understand in the MSDB database the Service Broker GUID
is different from the one in the table sys.databases.
Is there a possibility to correct this so I will be able to enable the
Service Broker?
I want to enable it so I can configure the email part on this SQL server.
If the GUID itself can not be changed is there a way to rebuild the MSDB
database?
Thanks for the info,
LimmerTry:
ALTER DATABASE MSDB SET NEW_BROKER
If SQL Agent is running, you will have to shut it down or this will hang.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Limmer" <Limmer@.discussions.microsoft.com> wrote in message
news:71240018-A03C-4F0D-830A-537F95BBB662@.microsoft.com...
> When I try to enable the Service Broker I receive the following error
> message:
> Cannot enable the Service Broker in database "msdb" because the Service
> Broker GUID in the database (01F9916A-21C0-4EA1-A555-A9C7A020C718) does
> not
> match the one in sys.databases (A7B475E6-5781-4FC2-B1E0-B0EF17FFE942).
> ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 9776)
> So as far as I can understand in the MSDB database the Service Broker GUID
> is different from the one in the table sys.databases.
> Is there a possibility to correct this so I will be able to enable the
> Service Broker?
> I want to enable it so I can configure the email part on this SQL server.
> If the GUID itself can not be changed is there a way to rebuild the MSDB
> database?
> Thanks for the info,
> Limmer
>|||Thanks Roger,
This did the trick.
Limmer
"Roger Wolter[MSFT]" wrote:

> Try:
> ALTER DATABASE MSDB SET NEW_BROKER
> If SQL Agent is running, you will have to shut it down or this will hang.
>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Limmer" <Limmer@.discussions.microsoft.com> wrote in message
> news:71240018-A03C-4F0D-830A-537F95BBB662@.microsoft.com...
>
>

Cannot enable the Service Broker

When I try to enable the Service Broker I receive the following error message:
Cannot enable the Service Broker in database "msdb" because the Service
Broker GUID in the database (01F9916A-21C0-4EA1-A555-A9C7A020C718) does not
match the one in sys.databases (A7B475E6-5781-4FC2-B1E0-B0EF17FFE942).
ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 9776)
So as far as I can understand in the MSDB database the Service Broker GUID
is different from the one in the table sys.databases.
Is there a possibility to correct this so I will be able to enable the
Service Broker?
I want to enable it so I can configure the email part on this SQL server.
If the GUID itself can not be changed is there a way to rebuild the MSDB
database?
Thanks for the info,
LimmerTry:
ALTER DATABASE MSDB SET NEW_BROKER
If SQL Agent is running, you will have to shut it down or this will hang.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Limmer" <Limmer@.discussions.microsoft.com> wrote in message
news:71240018-A03C-4F0D-830A-537F95BBB662@.microsoft.com...
> When I try to enable the Service Broker I receive the following error
> message:
> Cannot enable the Service Broker in database "msdb" because the Service
> Broker GUID in the database (01F9916A-21C0-4EA1-A555-A9C7A020C718) does
> not
> match the one in sys.databases (A7B475E6-5781-4FC2-B1E0-B0EF17FFE942).
> ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 9776)
> So as far as I can understand in the MSDB database the Service Broker GUID
> is different from the one in the table sys.databases.
> Is there a possibility to correct this so I will be able to enable the
> Service Broker?
> I want to enable it so I can configure the email part on this SQL server.
> If the GUID itself can not be changed is there a way to rebuild the MSDB
> database?
> Thanks for the info,
> Limmer
>|||Thanks Roger,
This did the trick.
Limmer
"Roger Wolter[MSFT]" wrote:
> Try:
> ALTER DATABASE MSDB SET NEW_BROKER
> If SQL Agent is running, you will have to shut it down or this will hang.
>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Limmer" <Limmer@.discussions.microsoft.com> wrote in message
> news:71240018-A03C-4F0D-830A-537F95BBB662@.microsoft.com...
> > When I try to enable the Service Broker I receive the following error
> > message:
> >
> > Cannot enable the Service Broker in database "msdb" because the Service
> > Broker GUID in the database (01F9916A-21C0-4EA1-A555-A9C7A020C718) does
> > not
> > match the one in sys.databases (A7B475E6-5781-4FC2-B1E0-B0EF17FFE942).
> > ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 9776)
> >
> > So as far as I can understand in the MSDB database the Service Broker GUID
> > is different from the one in the table sys.databases.
> > Is there a possibility to correct this so I will be able to enable the
> > Service Broker?
> > I want to enable it so I can configure the email part on this SQL server.
> >
> > If the GUID itself can not be changed is there a way to rebuild the MSDB
> > database?
> >
> > Thanks for the info,
> > Limmer
> >
>
>sql

Cannot Enable Remote Connections for AS 2005 on clustered instance

Hello SQL pros -

I am having a problem with enabling the remote connections for Analysis
Services 2005, Here's the gist....

--Error Information--
"To configure Analysis Services for Remote Connections, you must be an
adminsitrator for Analysis Services Windows Service (SQLSAC).

Additonal Info
A connection cannot be made. Ensure that service is running
(Microsoft.AnalysisServices)
No connection could be made because the target machine actively refused
it (System)."

--Actions Taken--
1) Analysis Services Windows Service was confirmed to be running.
2) Analysis Services Windows Service was stopped and restarted.
3) Analysis Services Windows Service (and SQL Accounts) confirmed to be
running under domain account with administrator priviledges.
4) Confirmed connectivity to Analysis Services instance through direct
connection (Windows Authentication)

--OS/System Information--
Windows 2000 Service Pack 5

--Clustering Roles--
Primary node for 1 SQL 2000 SP4 instance
Secondary node 1 SQL 2000 SP4 instance
Primary node for 1 Analysis Services 2000 instance
Primary node for 1 SQL 2005 instance, and primary
Primary node for 1 SQL 2005 Analysis Services Instance

I didn't find anything of relevance in the Windows logs or SQL logs.
So I am not sure where else to look. Is this a bug with 2005?

Any Suggestions to help troubleshoot are greatly appreciated!

thanks in advance.

ben

First.

Here some fresh blog post on how to troubleshoot connectivity problems with Analysis Services:

http://www.sqljunkies.com/WebLog/edwardm/

Second.
You saying
"4) Confirmed connectivity to Analysis Services instance through direct
connection (Windows Authentication)"

Analysis Services support only Windows Authentication when connecing through TCP/IP.

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

|||

Please also note when connecting to clustered Analysis Server you only need to specify "VirtualServer" as a server name , not "VirtualServer\Instace".

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

|||

ANTIVIRUS PROBLEM

uninstall your antivirus and try again

cannot edit this cell

when I fetch recods via enterprise manager and try to insert new
record I get the following error
"cannot edit this cell" but when I use "query analyzer" using T-sql commands I can execute DML operations .

it seems that there is no problem in the permissions ,I can not understand what is the problem .

can any one please help me it is very urgent since the problem is in one
of our production databases.

note that I use sql server 2000Your description does not sound this complicated - but check out the following article:

article (http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q288969&)|||Maybe the cell in the column is defined as an Identity column?|||give the sp_help 'tablename' and see the owner of the table may be it is the owners name through whch ur not entering to the table .

cannot edit text cell

I am trying to edit a long text cell in Enterprise Manager but when I select
"<long text>" and try to delete it, I get an error saying "cannot edit this
cell".

Could anyone tell me how to edit this cell?

Thanks, AmandaHi

When your text column has more than 900 characters it will not allow you to
enter it via EM.

You can use Query Analyser (which is usually easier and safer IMO) to issue
an update statement, or for more complicated changes look up UPDATETEXT in
books online.

John

"Amanda H" <amanda2@.southwind.org> wrote in message
news:K58Pd.1784$VI1.215392@.twister.southeast.rr.co m...
>I am trying to edit a long text cell in Enterprise Manager but when I
>select
> "<long text>" and try to delete it, I get an error saying "cannot edit
> this
> cell".
> Could anyone tell me how to edit this cell?
> Thanks, Amanda|||"Amanda H" <amanda2@.southwind.org> wrote in message
news:K58Pd.1784$VI1.215392@.twister.southeast.rr.co m...
>I am trying to edit a long text cell in Enterprise Manager but when I
>select
> "<long text>" and try to delete it, I get an error saying "cannot edit
> this
> cell".
> Could anyone tell me how to edit this cell?
> Thanks, Amanda

Use an UPDATE statement in Query Analyzer is probably the best thing to do -
EM isn't really a data manipulation or development tool, and it has a number
of 'features'.

http://www.aspfaq.com/show.asp?id=2455

Simonsql

Cannot edit jobs

i am get this error message every time I try to remove some old jobs that
were created on a different server:
error 14274: cannot add, update,or delete a job (or its step or
schedules) that originated from an msx server
What do I need to do to remove these jobs?
Either remove them from the master server. Or hack msdb..sysjobs (originating_server column to the
name for your server). Some info found here (adapt to your needs):
http://www.karaszi.com/SQLServer/inf...erver_name.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Takia" <Takia@.discussions.microsoft.com> wrote in message
news:527F2E42-BDF8-4968-B717-4BDA27347FDB@.microsoft.com...
>i am get this error message every time I try to remove some old jobs that
> were created on a different server:
> error 14274: cannot add, update,or delete a job (or its step or
> schedules) that originated from an msx server
> What do I need to do to remove these jobs?
>

Cannot edit jobs

i am get this error message every time I try to remove some old jobs that
were created on a different server:
error 14274: cannot add, update,or delete a job (or its step or
schedules) that originated from an msx server
What do I need to do to remove these jobs?Either remove them from the master server. Or hack msdb..sysjobs (originatin
g_server column to the
name for your server). Some info found here (adapt to your needs):
http://www.karaszi.com/SQLServer/in...server_name.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Takia" <Takia@.discussions.microsoft.com> wrote in message
news:527F2E42-BDF8-4968-B717-4BDA27347FDB@.microsoft.com...
>i am get this error message every time I try to remove some old jobs that
> were created on a different server:
> error 14274: cannot add, update,or delete a job (or its step or
> schedules) that originated from an msx server
> What do I need to do to remove these jobs?
>

Cannot edit jobs

i am get this error message every time I try to remove some old jobs that
were created on a different server:
error 14274: cannot add, update,or delete a job (or its step or
schedules) that originated from an msx server
What do I need to do to remove these jobs?Either remove them from the master server. Or hack msdb..sysjobs (originating_server column to the
name for your server). Some info found here (adapt to your needs):
http://www.karaszi.com/SQLServer/info_change_server_name.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Takia" <Takia@.discussions.microsoft.com> wrote in message
news:527F2E42-BDF8-4968-B717-4BDA27347FDB@.microsoft.com...
>i am get this error message every time I try to remove some old jobs that
> were created on a different server:
> error 14274: cannot add, update,or delete a job (or its step or
> schedules) that originated from an msx server
> What do I need to do to remove these jobs?
>

Cannot drop the table transactional replication error 3724

This thread is no longer active. I moved issue to a new, cleaner thread named: How can I get Transactional Republishing to work?

--

I've tried for more than a week to figure this one out, but have not found a solution. I'm missing some critical information that perhaps someone can share. I'm desperate and embarassed that this has kept me stuck for so long. Thanks for any assistance.

In my simplist scenario, I'm transactionally replicating a single table from database A to database B and then from database B to database C. All databases are on the same XP server (in this simplist version of the problem). This is SQL 2005 RTM. Publication A to B replicates fine. Publication B to C can't even get the snapshot to work because I get the error:

Command attempted:
drop Table "dbo"."MyReplTable"

(Transaction sequence number: 0x00004174000000E100A300000000, Command ID: 41)

Error messages:
Cannot drop the table 'dbo.MyReplTable' because it is being used for replication. (Source: MSSQLServer, Error number: 3724) Get help: http://help/3724 (fyi - there is no help here)

I have a simple example script where I've renamed real names to fake names, that is exactly what I run to simulate the problem. Obviously the script is wrong, but where? I used the wizard to create the whole script (I know that is my first mistake ;-) ). After I start the snapshot jobs, I get the error above.

I presume that the sp_addarticle option @.pre_creation_cmd = N'drop' is part of the problem. I don't have row filters. Also, I want to be able to replicate schema modifications when necessary (like add a column or drop a column)

Perhaps the sp_addpublication @.repl_freq = N'continuous' option in the AtoB publication prevents the BtoC publication from ever getting a chance to do anything, if it never lets go of the table.

If it helps, here is my script with hopefully helpfully renamed objects. My real world scenario has many articles, but this is a simple version - 2 publications each having 1 article (the same table).

/****** Scripting replication configuration for server MyInstance. Script Date: 3/14/2006 11:16:58 AM ******/
/****** Please Note: For security reasons, all password parameters were scripted with either NULL or an empty string. ******/

/****** Installing the server MyInstance as a Distributor. Script Date: 3/14/2006 11:16:58 AM ******/
use master
exec sp_adddistributor @.distributor = N'MyInstance', @.password = N''
GO
exec sp_adddistributiondb @.database = N'distribution', @.data_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data', @.data_file_size = 4, @.log_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data', @.log_file_size = 2, @.min_distretention = 0, @.max_distretention = 72, @.history_retention = 48, @.security_mode = 1
GO

use [distribution]
if (not exists (select * from sysobjects where name = 'UIProperties' and type = 'U '))
create table UIProperties(id int)
if (exists (select * from ::fn_listextendedproperty('SnapshotFolder', 'user', 'dbo', 'table', 'UIProperties', null, null)))
EXEC sp_updateextendedproperty N'SnapshotFolder', N'C:\ThisFolder\MY2005DBWorking\repldata', 'user', dbo, 'table', 'UIProperties'
else
EXEC sp_addextendedproperty N'SnapshotFolder', 'C:\ThisFolder\MY2005DBWorking\repldata', 'user', dbo, 'table', 'UIProperties'
GO

exec sp_adddistpublisher @.publisher = N'MyInstance', @.distribution_db = N'distribution', @.security_mode = 1, @.working_directory = N'C:\ThisFolder\MY2005DBWorking\repldata', @.trusted = N'false', @.thirdparty_flag = 0, @.publisher_type = N'MSSQLSERVER'
GO

use [DatabaseA]
exec sp_replicationdboption @.dbname = N'DatabaseA', @.optname = N'publish', @.value = N'true'
GO
-- Adding the transactional publication
use [DatabaseA]
exec sp_addpublication @.publication = N'PubAtoB', @.description = N'Transactional publication of database ''DatabaseA'' from Publisher ''MyInstance''.', @.sync_method = N'concurrent', @.retention = 0, @.allow_push = N'true', @.allow_pull = N'true', @.allow_anonymous = N'true', @.enabled_for_internet = N'false', @.snapshot_in_defaultfolder = N'true', @.compress_snapshot = N'false', @.ftp_port = 21, @.ftp_login = N'anonymous', @.allow_subscription_copy = N'false', @.add_to_active_directory = N'false', @.repl_freq = N'continuous', @.status = N'active', @.independent_agent = N'true', @.immediate_sync = N'true', @.allow_sync_tran = N'false', @.autogen_sync_procs = N'false', @.allow_queued_tran = N'false', @.allow_dts = N'false', @.replicate_ddl = 1, @.allow_initialize_from_backup = N'false', @.enabled_for_p2p = N'false', @.enabled_for_het_sub = N'false'
GO


exec sp_addpublication_snapshot @.publication = N'PubAtoB', @.frequency_type = 1, @.frequency_interval = 0, @.frequency_relative_interval = 0, @.frequency_recurrence_factor = 0, @.frequency_subday = 0, @.frequency_subday_interval = 0, @.active_start_time_of_day = 0, @.active_end_time_of_day = 235959, @.active_start_date = 0, @.active_end_date = 0, @.job_login = null, @.job_password = null, @.publisher_security_mode = 1


use [DatabaseA]
exec sp_addarticle @.publication = N'PubAtoB', @.article = N'MyReplTable', @.source_owner = N'dbo', @.source_object = N'MyReplTable', @.type = N'logbased', @.description = null, @.creation_script = null, @.pre_creation_cmd = N'drop', @.schema_option = 0x000000000803509F, @.identityrangemanagementoption = N'manual', @.destination_table = N'MyReplTable', @.destination_owner = N'dbo', @.vertical_partition = N'false', @.ins_cmd = N'CALL sp_MSins_dboMyReplTable', @.del_cmd = N'CALL sp_MSdel_dboMyReplTable', @.upd_cmd = N'SCALL sp_MSupd_dboMyReplTable'
GO

--BEGIN: Script to be run at Publisher 'MyInstance'--
use [DatabaseA]
exec sp_addsubscription @.publication = N'PubAtoB', @.subscriber = N'MyInstance', @.destination_db = N'DatabaseB', @.subscription_type = N'Push', @.sync_type = N'automatic', @.article = N'all', @.update_mode = N'read only', @.subscriber_type = 0
exec sp_addpushsubscription_agent @.publication = N'PubAtoB', @.subscriber = N'MyInstance', @.subscriber_db = N'DatabaseB', @.job_login = null, @.job_password = null, @.subscriber_security_mode = 1, @.frequency_type = 64, @.frequency_interval = 0, @.frequency_relative_interval = 0, @.frequency_recurrence_factor = 0, @.frequency_subday = 0, @.frequency_subday_interval = 0, @.active_start_time_of_day = 0, @.active_end_time_of_day = 235959, @.active_start_date = 20060314, @.active_end_date = 99991231, @.enabled_for_syncmgr = N'False', @.dts_package_location = N'Distributor'
GO
--END: Script to be run at Publisher 'MyInstance'--


use [DatabaseB]
exec sp_replicationdboption @.dbname = N'DatabaseB', @.optname = N'publish', @.value = N'true'
GO
-- Adding the transactional publication
use [DatabaseB]
exec sp_addpublication @.publication = N'PubBtoC', @.description = N'Transactional publication of database ''DatabaseB'' from Publisher ''MyInstance''.', @.sync_method = N'concurrent', @.retention = 0, @.allow_push = N'true', @.allow_pull = N'true', @.allow_anonymous = N'true', @.enabled_for_internet = N'false', @.snapshot_in_defaultfolder = N'true', @.compress_snapshot = N'false', @.ftp_port = 21, @.ftp_login = N'anonymous', @.allow_subscription_copy = N'false', @.add_to_active_directory = N'false', @.repl_freq = N'continuous', @.status = N'active', @.independent_agent = N'true', @.immediate_sync = N'true', @.allow_sync_tran = N'false', @.autogen_sync_procs = N'false', @.allow_queued_tran = N'false', @.allow_dts = N'false', @.replicate_ddl = 1, @.allow_initialize_from_backup = N'false', @.enabled_for_p2p = N'false', @.enabled_for_het_sub = N'false'
GO


exec sp_addpublication_snapshot @.publication = N'PubBtoC', @.frequency_type = 1, @.frequency_interval = 0, @.frequency_relative_interval = 0, @.frequency_recurrence_factor = 0, @.frequency_subday = 0, @.frequency_subday_interval = 0, @.active_start_time_of_day = 0, @.active_end_time_of_day = 235959, @.active_start_date = 0, @.active_end_date = 0, @.job_login = null, @.job_password = null, @.publisher_security_mode = 1


use [DatabaseB]
exec sp_addarticle @.publication = N'PubBtoC', @.article = N'MyReplTable', @.source_owner = N'dbo', @.source_object = N'MyReplTable', @.type = N'logbased', @.description = null, @.creation_script = null, @.pre_creation_cmd = N'drop', @.schema_option = 0x000000000803509F, @.identityrangemanagementoption = N'manual', @.destination_table = N'MyReplTable', @.destination_owner = N'dbo', @.vertical_partition = N'false', @.ins_cmd = N'CALL sp_MSins_dboMyReplTable', @.del_cmd = N'CALL sp_MSdel_dboMyReplTable', @.upd_cmd = N'SCALL sp_MSupd_dboMyReplTable'
GO

--BEGIN: Script to be run at Publisher 'MyInstance'--
use [DatabaseB]
exec sp_addsubscription @.publication = N'PubBtoC', @.subscriber = N'MyInstance', @.destination_db = N'DatabaseC', @.subscription_type = N'Push', @.sync_type = N'automatic', @.article = N'all', @.update_mode = N'read only', @.subscriber_type = 0
exec sp_addpushsubscription_agent @.publication = N'PubBtoC', @.subscriber = N'MyInstance', @.subscriber_db = N'DatabaseC', @.job_login = null, @.job_password = null, @.subscriber_security_mode = 1, @.frequency_type = 64, @.frequency_interval = 0, @.frequency_relative_interval = 0, @.frequency_recurrence_factor = 0, @.frequency_subday = 0, @.frequency_subday_interval = 0, @.active_start_time_of_day = 0, @.active_end_time_of_day = 235959, @.active_start_date = 20060314, @.active_end_date = 99991231, @.enabled_for_syncmgr = N'False', @.dts_package_location = N'Distributor'
GO
--END: Script to be run at Publisher 'MyInstance'--


May I ask why you need a republisher scenario, and not just have C subscriber to A?|||

The reason I ask this is because transactional replication is not designed for a republishing scenario. It can be done, but it involves a lot of hacks and so forth. Merge replication is typically used, and designed, for republishing scenario.

Maybe I should ask what your business needs are, and then we can discuss a solution.

|||

This is the nature of the system I've inherited. I'll represent the A,B,C level as servers because in reality, they often are a server with a single database.

The process thinking is that multiple Level B servers on other servers, spread the processing burden of moving the data from A to C. The C servers generally are physically clustered in the same general geographic area as the B server from which they source their data. Realistically, the A server might be feeding data to multiple B servers which in turn feed multiple C servers.

I hope that clarifies it for you. You can see that my little test scenario is much simplified from the physical reality. Our existing SQL 2000 replication makes use of both transactional and merge replication in this environment. I've only had light experience with transactional replication and not with either merge or republishing.

|||

Hi,

From your description, I assume you saw the error message from distribution agent, when replicating from B to C. Because you set @.pre_creation_cmd = N'drop', distribution agent needs to drop the table at C because it can replicate other transactions. But for some reason, this table is marked published.

So my question is: have you use DatabaseC as publisher? If it is the case, this may be the reason.

Peng

|||

Peng,

For the moment, I'm back to working on my actual script (vs the sample one). I can tell you that I do not have DatabaseC as a publisher in my actual script. However, I will need to make it a publisher because there is one more replication layer that I did not mention. Practically every table will be transactionally replicated to another server (DatabaseD) to support another process.

This is the way we are actually doing things in production now, in SQL 2000, without error.

Paul

|||I've just discovered that SQL 2000 replication was probably interfering with my SQL 2005 replication, so I removed SQL 2000 from my workstation (except for Virtual PC) and will try to work on this again tomorrow to see if anything has changed.

Cannot drop the table transactional replication error 3724

This thread is no longer active. I moved issue to a new, cleaner thread named: How can I get Transactional Republishing to work?

--

I've tried for more than a week to figure this one out, but have not found a solution. I'm missing some critical information that perhaps someone can share. I'm desperate and embarassed that this has kept me stuck for so long. Thanks for any assistance.

In my simplist scenario, I'm transactionally replicating a single table from database A to database B and then from database B to database C. All databases are on the same XP server (in this simplist version of the problem). This is SQL 2005 RTM. Publication A to B replicates fine. Publication B to C can't even get the snapshot to work because I get the error:

Command attempted:
drop Table "dbo"."MyReplTable"

(Transaction sequence number: 0x00004174000000E100A300000000, Command ID: 41)

Error messages:
Cannot drop the table 'dbo.MyReplTable' because it is being used for replication. (Source: MSSQLServer, Error number: 3724) Get help: http://help/3724 (fyi - there is no help here)

I have a simple example script where I've renamed real names to fake names, that is exactly what I run to simulate the problem. Obviously the script is wrong, but where? I used the wizard to create the whole script (I know that is my first mistake ;-) ). After I start the snapshot jobs, I get the error above.

I presume that the sp_addarticle option @.pre_creation_cmd = N'drop' is part of the problem. I don't have row filters. Also, I want to be able to replicate schema modifications when necessary (like add a column or drop a column)

Perhaps the sp_addpublication @.repl_freq = N'continuous' option in the AtoB publication prevents the BtoC publication from ever getting a chance to do anything, if it never lets go of the table.

If it helps, here is my script with hopefully helpfully renamed objects. My real world scenario has many articles, but this is a simple version - 2 publications each having 1 article (the same table).

/****** Scripting replication configuration for server MyInstance. Script Date: 3/14/2006 11:16:58 AM ******/
/****** Please Note: For security reasons, all password parameters were scripted with either NULL or an empty string. ******/

/****** Installing the server MyInstance as a Distributor. Script Date: 3/14/2006 11:16:58 AM ******/
use master
exec sp_adddistributor @.distributor = N'MyInstance', @.password = N''
GO
exec sp_adddistributiondb @.database = N'distribution', @.data_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data', @.data_file_size = 4, @.log_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data', @.log_file_size = 2, @.min_distretention = 0, @.max_distretention = 72, @.history_retention = 48, @.security_mode = 1
GO

use [distribution]
if (not exists (select * from sysobjects where name = 'UIProperties' and type = 'U '))
create table UIProperties(id int)
if (exists (select * from ::fn_listextendedproperty('SnapshotFolder', 'user', 'dbo', 'table', 'UIProperties', null, null)))
EXEC sp_updateextendedproperty N'SnapshotFolder', N'C:\ThisFolder\MY2005DBWorking\repldata', 'user', dbo, 'table', 'UIProperties'
else
EXEC sp_addextendedproperty N'SnapshotFolder', 'C:\ThisFolder\MY2005DBWorking\repldata', 'user', dbo, 'table', 'UIProperties'
GO

exec sp_adddistpublisher @.publisher = N'MyInstance', @.distribution_db = N'distribution', @.security_mode = 1, @.working_directory = N'C:\ThisFolder\MY2005DBWorking\repldata', @.trusted = N'false', @.thirdparty_flag = 0, @.publisher_type = N'MSSQLSERVER'
GO

use [DatabaseA]
exec sp_replicationdboption @.dbname = N'DatabaseA', @.optname = N'publish', @.value = N'true'
GO
-- Adding the transactional publication
use [DatabaseA]
exec sp_addpublication @.publication = N'PubAtoB', @.description = N'Transactional publication of database ''DatabaseA'' from Publisher ''MyInstance''.', @.sync_method = N'concurrent', @.retention = 0, @.allow_push = N'true', @.allow_pull = N'true', @.allow_anonymous = N'true', @.enabled_for_internet = N'false', @.snapshot_in_defaultfolder = N'true', @.compress_snapshot = N'false', @.ftp_port = 21, @.ftp_login = N'anonymous', @.allow_subscription_copy = N'false', @.add_to_active_directory = N'false', @.repl_freq = N'continuous', @.status = N'active', @.independent_agent = N'true', @.immediate_sync = N'true', @.allow_sync_tran = N'false', @.autogen_sync_procs = N'false', @.allow_queued_tran = N'false', @.allow_dts = N'false', @.replicate_ddl = 1, @.allow_initialize_from_backup = N'false', @.enabled_for_p2p = N'false', @.enabled_for_het_sub = N'false'
GO


exec sp_addpublication_snapshot @.publication = N'PubAtoB', @.frequency_type = 1, @.frequency_interval = 0, @.frequency_relative_interval = 0, @.frequency_recurrence_factor = 0, @.frequency_subday = 0, @.frequency_subday_interval = 0, @.active_start_time_of_day = 0, @.active_end_time_of_day = 235959, @.active_start_date = 0, @.active_end_date = 0, @.job_login = null, @.job_password = null, @.publisher_security_mode = 1


use [DatabaseA]
exec sp_addarticle @.publication = N'PubAtoB', @.article = N'MyReplTable', @.source_owner = N'dbo', @.source_object = N'MyReplTable', @.type = N'logbased', @.description = null, @.creation_script = null, @.pre_creation_cmd = N'drop', @.schema_option = 0x000000000803509F, @.identityrangemanagementoption = N'manual', @.destination_table = N'MyReplTable', @.destination_owner = N'dbo', @.vertical_partition = N'false', @.ins_cmd = N'CALL sp_MSins_dboMyReplTable', @.del_cmd = N'CALL sp_MSdel_dboMyReplTable', @.upd_cmd = N'SCALL sp_MSupd_dboMyReplTable'
GO

--BEGIN: Script to be run at Publisher 'MyInstance'--
use [DatabaseA]
exec sp_addsubscription @.publication = N'PubAtoB', @.subscriber = N'MyInstance', @.destination_db = N'DatabaseB', @.subscription_type = N'Push', @.sync_type = N'automatic', @.article = N'all', @.update_mode = N'read only', @.subscriber_type = 0
exec sp_addpushsubscription_agent @.publication = N'PubAtoB', @.subscriber = N'MyInstance', @.subscriber_db = N'DatabaseB', @.job_login = null, @.job_password = null, @.subscriber_security_mode = 1, @.frequency_type = 64, @.frequency_interval = 0, @.frequency_relative_interval = 0, @.frequency_recurrence_factor = 0, @.frequency_subday = 0, @.frequency_subday_interval = 0, @.active_start_time_of_day = 0, @.active_end_time_of_day = 235959, @.active_start_date = 20060314, @.active_end_date = 99991231, @.enabled_for_syncmgr = N'False', @.dts_package_location = N'Distributor'
GO
--END: Script to be run at Publisher 'MyInstance'--


use [DatabaseB]
exec sp_replicationdboption @.dbname = N'DatabaseB', @.optname = N'publish', @.value = N'true'
GO
-- Adding the transactional publication
use [DatabaseB]
exec sp_addpublication @.publication = N'PubBtoC', @.description = N'Transactional publication of database ''DatabaseB'' from Publisher ''MyInstance''.', @.sync_method = N'concurrent', @.retention = 0, @.allow_push = N'true', @.allow_pull = N'true', @.allow_anonymous = N'true', @.enabled_for_internet = N'false', @.snapshot_in_defaultfolder = N'true', @.compress_snapshot = N'false', @.ftp_port = 21, @.ftp_login = N'anonymous', @.allow_subscription_copy = N'false', @.add_to_active_directory = N'false', @.repl_freq = N'continuous', @.status = N'active', @.independent_agent = N'true', @.immediate_sync = N'true', @.allow_sync_tran = N'false', @.autogen_sync_procs = N'false', @.allow_queued_tran = N'false', @.allow_dts = N'false', @.replicate_ddl = 1, @.allow_initialize_from_backup = N'false', @.enabled_for_p2p = N'false', @.enabled_for_het_sub = N'false'
GO


exec sp_addpublication_snapshot @.publication = N'PubBtoC', @.frequency_type = 1, @.frequency_interval = 0, @.frequency_relative_interval = 0, @.frequency_recurrence_factor = 0, @.frequency_subday = 0, @.frequency_subday_interval = 0, @.active_start_time_of_day = 0, @.active_end_time_of_day = 235959, @.active_start_date = 0, @.active_end_date = 0, @.job_login = null, @.job_password = null, @.publisher_security_mode = 1


use [DatabaseB]
exec sp_addarticle @.publication = N'PubBtoC', @.article = N'MyReplTable', @.source_owner = N'dbo', @.source_object = N'MyReplTable', @.type = N'logbased', @.description = null, @.creation_script = null, @.pre_creation_cmd = N'drop', @.schema_option = 0x000000000803509F, @.identityrangemanagementoption = N'manual', @.destination_table = N'MyReplTable', @.destination_owner = N'dbo', @.vertical_partition = N'false', @.ins_cmd = N'CALL sp_MSins_dboMyReplTable', @.del_cmd = N'CALL sp_MSdel_dboMyReplTable', @.upd_cmd = N'SCALL sp_MSupd_dboMyReplTable'
GO

--BEGIN: Script to be run at Publisher 'MyInstance'--
use [DatabaseB]
exec sp_addsubscription @.publication = N'PubBtoC', @.subscriber = N'MyInstance', @.destination_db = N'DatabaseC', @.subscription_type = N'Push', @.sync_type = N'automatic', @.article = N'all', @.update_mode = N'read only', @.subscriber_type = 0
exec sp_addpushsubscription_agent @.publication = N'PubBtoC', @.subscriber = N'MyInstance', @.subscriber_db = N'DatabaseC', @.job_login = null, @.job_password = null, @.subscriber_security_mode = 1, @.frequency_type = 64, @.frequency_interval = 0, @.frequency_relative_interval = 0, @.frequency_recurrence_factor = 0, @.frequency_subday = 0, @.frequency_subday_interval = 0, @.active_start_time_of_day = 0, @.active_end_time_of_day = 235959, @.active_start_date = 20060314, @.active_end_date = 99991231, @.enabled_for_syncmgr = N'False', @.dts_package_location = N'Distributor'
GO
--END: Script to be run at Publisher 'MyInstance'--


May I ask why you need a republisher scenario, and not just have C subscriber to A?|||

The reason I ask this is because transactional replication is not designed for a republishing scenario. It can be done, but it involves a lot of hacks and so forth. Merge replication is typically used, and designed, for republishing scenario.

Maybe I should ask what your business needs are, and then we can discuss a solution.

|||

This is the nature of the system I've inherited. I'll represent the A,B,C level as servers because in reality, they often are a server with a single database.

The process thinking is that multiple Level B servers on other servers, spread the processing burden of moving the data from A to C. The C servers generally are physically clustered in the same general geographic area as the B server from which they source their data. Realistically, the A server might be feeding data to multiple B servers which in turn feed multiple C servers.

I hope that clarifies it for you. You can see that my little test scenario is much simplified from the physical reality. Our existing SQL 2000 replication makes use of both transactional and merge replication in this environment. I've only had light experience with transactional replication and not with either merge or republishing.

|||

Hi,

From your description, I assume you saw the error message from distribution agent, when replicating from B to C. Because you set @.pre_creation_cmd = N'drop', distribution agent needs to drop the table at C because it can replicate other transactions. But for some reason, this table is marked published.

So my question is: have you use DatabaseC as publisher? If it is the case, this may be the reason.

Peng

|||

Peng,

For the moment, I'm back to working on my actual script (vs the sample one). I can tell you that I do not have DatabaseC as a publisher in my actual script. However, I will need to make it a publisher because there is one more replication layer that I did not mention. Practically every table will be transactionally replicated to another server (DatabaseD) to support another process.

This is the way we are actually doing things in production now, in SQL 2000, without error.

Paul

|||I've just discovered that SQL 2000 replication was probably interfering with my SQL 2005 replication, so I removed SQL 2000 from my workstation (except for Virtual PC) and will try to work on this again tomorrow to see if anything has changed.

Sunday, March 25, 2012

Cannot drop the database 'RMAModule' because it is currently in use

How can I stop getting this error when I try to delete a database
dynamically?
I am using this code...
DROP DATABASE RMAModule
If I just wait a while and try it again it will work - not urgent, but a
hastle. Thanks!!Hi,
Execute the below code from Query Analyzer,
use master
go
alter database dbname set RESTRICTED_USER with rollback immediate
go
drop database dbname
Replace the dbname with the actual dbname which you have to drop.
Thanks
Hari
MCDBA
"michaaal" <res0gyio@.verizon.net> wrote in message
news:#EwrBBlDEHA.2424@.TK2MSFTNGP09.phx.gbl...
> How can I stop getting this error when I try to delete a database
> dynamically?
> I am using this code...
> DROP DATABASE RMAModule
> If I just wait a while and try it again it will work - not urgent, but a
> hastle. Thanks!!
>sql

Cannot Drop Server

I am receiving the following error when attempting to drop a server to rename
it
Server: Msg 20582, Level 16, State 1, Procedure sp_MSrepl_check_server, Line
39
Cannot drop server 'ZHQACCTSQL' because it is used as a Publisher in
replication.
Replication is not occuring on this server.
When I execute sp_get_distributor the results show that replication is not
occuring on this server.
Looking in msdb.dbo.MSdistpublishers I find an entry for this server.
My question is should I delete the record that is in this table to allow
renaming of the server?
It is possible this server has a remote distributor. Issue a call to
sp_helpdistributor to get the server which is acting as the distributor's
name.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"John Bradford" <John Bradford@.discussions.microsoft.com> wrote in message
news:72F385EA-A8D7-4A6C-A8A7-DCEC184AB88A@.microsoft.com...
>I am receiving the following error when attempting to drop a server to
>rename
> it
> Server: Msg 20582, Level 16, State 1, Procedure sp_MSrepl_check_server,
> Line
> 39
> Cannot drop server 'ZHQACCTSQL' because it is used as a Publisher in
> replication.
> Replication is not occuring on this server.
> When I execute sp_get_distributor the results show that replication is not
> occuring on this server.
> Looking in msdb.dbo.MSdistpublishers I find an entry for this server.
> My question is should I delete the record that is in this table to allow
> renaming of the server?
>
|||Thanks for the response.
This server does not have a remote distributor. sp_helpdistributor returns
no rows
I believe that this server was set up for replication at some time in the
past, and that the person who did this (no longer with the company) did not
do it properly or clean up after himself.
I believe that by removing the entries in msdb.dbo.MSdistpublishers and
msdb.dbo.MSdistributiondbs I can rename the server.
I just wanted some verification prior to doing this.
Thanks
"Hilary Cotter" wrote:

> It is possible this server has a remote distributor. Issue a call to
> sp_helpdistributor to get the server which is acting as the distributor's
> name.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "John Bradford" <John Bradford@.discussions.microsoft.com> wrote in message
> news:72F385EA-A8D7-4A6C-A8A7-DCEC184AB88A@.microsoft.com...
>
>
|||I would not attempt it that way. I would re-enable replication and then
disable it. This normally cleans up inconsistent metadata.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"John Bradford" <John Bradford@.discussions.microsoft.com> wrote in message
news:28AFB311-60CD-4AB9-A29E-FD0D2BD805BD@.microsoft.com...[vbcol=seagreen]
> Thanks for the response.
> This server does not have a remote distributor. sp_helpdistributor
> returns
> no rows
> I believe that this server was set up for replication at some time in the
> past, and that the person who did this (no longer with the company) did
> not
> do it properly or clean up after himself.
> I believe that by removing the entries in msdb.dbo.MSdistpublishers and
> msdb.dbo.MSdistributiondbs I can rename the server.
> I just wanted some verification prior to doing this.
> Thanks
> "Hilary Cotter" wrote:

Cannot Drop Server

I am receiving the following error when attempting to drop a server to renam
e
it
Server: Msg 20582, Level 16, State 1, Procedure sp_MSrepl_check_server, Line
39
Cannot drop server 'ZHQACCTSQL' because it is used as a Publisher in
replication.
Replication is not occuring on this server.
When I execute sp_get_distributor the results show that replication is not
occuring on this server.
Looking in msdb.dbo.MSdistpublishers I find an entry for this server.
My question is should I delete the record that is in this table to allow
renaming of the server?It is possible this server has a remote distributor. Issue a call to
sp_helpdistributor to get the server which is acting as the distributor's
name.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"John Bradford" <John Bradford@.discussions.microsoft.com> wrote in message
news:72F385EA-A8D7-4A6C-A8A7-DCEC184AB88A@.microsoft.com...
>I am receiving the following error when attempting to drop a server to
>rename
> it
> Server: Msg 20582, Level 16, State 1, Procedure sp_MSrepl_check_server,
> Line
> 39
> Cannot drop server 'ZHQACCTSQL' because it is used as a Publisher in
> replication.
> Replication is not occuring on this server.
> When I execute sp_get_distributor the results show that replication is not
> occuring on this server.
> Looking in msdb.dbo.MSdistpublishers I find an entry for this server.
> My question is should I delete the record that is in this table to allow
> renaming of the server?
>|||Thanks for the response.
This server does not have a remote distributor. sp_helpdistributor returns
no rows
I believe that this server was set up for replication at some time in the
past, and that the person who did this (no longer with the company) did not
do it properly or clean up after himself.
I believe that by removing the entries in msdb.dbo.MSdistpublishers and
msdb.dbo.MSdistributiondbs I can rename the server.
I just wanted some verification prior to doing this.
Thanks
"Hilary Cotter" wrote:

> It is possible this server has a remote distributor. Issue a call to
> sp_helpdistributor to get the server which is acting as the distributor's
> name.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "John Bradford" <John Bradford@.discussions.microsoft.com> wrote in message
> news:72F385EA-A8D7-4A6C-A8A7-DCEC184AB88A@.microsoft.com...
>
>|||I would not attempt it that way. I would re-enable replication and then
disable it. This normally cleans up inconsistent metadata.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"John Bradford" <John Bradford@.discussions.microsoft.com> wrote in message
news:28AFB311-60CD-4AB9-A29E-FD0D2BD805BD@.microsoft.com...[vbcol=seagreen]
> Thanks for the response.
> This server does not have a remote distributor. sp_helpdistributor
> returns
> no rows
> I believe that this server was set up for replication at some time in the
> past, and that the person who did this (no longer with the company) did
> not
> do it properly or clean up after himself.
> I believe that by removing the entries in msdb.dbo.MSdistpublishers and
> msdb.dbo.MSdistributiondbs I can rename the server.
> I just wanted some verification prior to doing this.
> Thanks
> "Hilary Cotter" wrote:
>

Cannot Drop Server

I am receiving the following error when attempting to drop a server to rename
it
Server: Msg 20582, Level 16, State 1, Procedure sp_MSrepl_check_server, Line
39
Cannot drop server 'ZHQACCTSQL' because it is used as a Publisher in
replication.
Replication is not occuring on this server.
When I execute sp_get_distributor the results show that replication is not
occuring on this server.
Looking in msdb.dbo.MSdistpublishers I find an entry for this server.
My question is should I delete the record that is in this table to allow
renaming of the server?It is possible this server has a remote distributor. Issue a call to
sp_helpdistributor to get the server which is acting as the distributor's
name.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"John Bradford" <John Bradford@.discussions.microsoft.com> wrote in message
news:72F385EA-A8D7-4A6C-A8A7-DCEC184AB88A@.microsoft.com...
>I am receiving the following error when attempting to drop a server to
>rename
> it
> Server: Msg 20582, Level 16, State 1, Procedure sp_MSrepl_check_server,
> Line
> 39
> Cannot drop server 'ZHQACCTSQL' because it is used as a Publisher in
> replication.
> Replication is not occuring on this server.
> When I execute sp_get_distributor the results show that replication is not
> occuring on this server.
> Looking in msdb.dbo.MSdistpublishers I find an entry for this server.
> My question is should I delete the record that is in this table to allow
> renaming of the server?
>|||Thanks for the response.
This server does not have a remote distributor. sp_helpdistributor returns
no rows
I believe that this server was set up for replication at some time in the
past, and that the person who did this (no longer with the company) did not
do it properly or clean up after himself.
I believe that by removing the entries in msdb.dbo.MSdistpublishers and
msdb.dbo.MSdistributiondbs I can rename the server.
I just wanted some verification prior to doing this.
Thanks
"Hilary Cotter" wrote:
> It is possible this server has a remote distributor. Issue a call to
> sp_helpdistributor to get the server which is acting as the distributor's
> name.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "John Bradford" <John Bradford@.discussions.microsoft.com> wrote in message
> news:72F385EA-A8D7-4A6C-A8A7-DCEC184AB88A@.microsoft.com...
> >I am receiving the following error when attempting to drop a server to
> >rename
> > it
> >
> > Server: Msg 20582, Level 16, State 1, Procedure sp_MSrepl_check_server,
> > Line
> > 39
> > Cannot drop server 'ZHQACCTSQL' because it is used as a Publisher in
> > replication.
> >
> > Replication is not occuring on this server.
> > When I execute sp_get_distributor the results show that replication is not
> > occuring on this server.
> >
> > Looking in msdb.dbo.MSdistpublishers I find an entry for this server.
> > My question is should I delete the record that is in this table to allow
> > renaming of the server?
> >
>
>|||I would not attempt it that way. I would re-enable replication and then
disable it. This normally cleans up inconsistent metadata.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"John Bradford" <John Bradford@.discussions.microsoft.com> wrote in message
news:28AFB311-60CD-4AB9-A29E-FD0D2BD805BD@.microsoft.com...
> Thanks for the response.
> This server does not have a remote distributor. sp_helpdistributor
> returns
> no rows
> I believe that this server was set up for replication at some time in the
> past, and that the person who did this (no longer with the company) did
> not
> do it properly or clean up after himself.
> I believe that by removing the entries in msdb.dbo.MSdistpublishers and
> msdb.dbo.MSdistributiondbs I can rename the server.
> I just wanted some verification prior to doing this.
> Thanks
> "Hilary Cotter" wrote:
>> It is possible this server has a remote distributor. Issue a call to
>> sp_helpdistributor to get the server which is acting as the distributor's
>> name.
>> --
>> Hilary Cotter
>> Director of Text Mining and Database Strategy
>> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
>> This posting is my own and doesn't necessarily represent RelevantNoise's
>> positions, strategies or opinions.
>> 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
>>
>> "John Bradford" <John Bradford@.discussions.microsoft.com> wrote in
>> message
>> news:72F385EA-A8D7-4A6C-A8A7-DCEC184AB88A@.microsoft.com...
>> >I am receiving the following error when attempting to drop a server to
>> >rename
>> > it
>> >
>> > Server: Msg 20582, Level 16, State 1, Procedure sp_MSrepl_check_server,
>> > Line
>> > 39
>> > Cannot drop server 'ZHQACCTSQL' because it is used as a Publisher in
>> > replication.
>> >
>> > Replication is not occuring on this server.
>> > When I execute sp_get_distributor the results show that replication is
>> > not
>> > occuring on this server.
>> >
>> > Looking in msdb.dbo.MSdistpublishers I find an entry for this server.
>> > My question is should I delete the record that is in this table to
>> > allow
>> > renaming of the server?
>> >
>>sql

Cannot Drop Index

I could not find a way to remove primary key from an index, thus could not
drop it.
Command:
drop index Romm_Inv.PK_Room_Inv
Error
Server: Msg 3723, Level 16, State 4, Line 1
An explicit DROP INDEX is not allowed on index 'Room_Inv.PK_Room_Inv'.
It is being used for PRIMARY KEY constraint enforcement.
The help utility refer to ALTER TABLE options to DROP CONSTRAINT but it did
not work.
Gracias. LaEsmeraldaALTER TABLE is what you are supposed to use. What statement did you use and
what was the error message?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
news:F00810B8-62EA-4E19-A1EF-71AA5C8F9AB8@.microsoft.com...
I could not find a way to remove primary key from an index, thus could not
drop it.
Command:
drop index Romm_Inv.PK_Room_Inv
Error
Server: Msg 3723, Level 16, State 4, Line 1
An explicit DROP INDEX is not allowed on index 'Room_Inv.PK_Room_Inv'.
It is being used for PRIMARY KEY constraint enforcement.
The help utility refer to ALTER TABLE options to DROP CONSTRAINT but it did
not work.
Gracias. LaEsmeralda|||Hola LaEsmeralda
You do need to use ALTER TABLE to remove a primary key constraint, which
will drop the index along with it.
What does 'did not work' mean? Can you show us the statement you were using
and the error message you received.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
news:F00810B8-62EA-4E19-A1EF-71AA5C8F9AB8@.microsoft.com...
>I could not find a way to remove primary key from an index, thus could not
> drop it.
> Command:
> drop index Romm_Inv.PK_Room_Inv
> Error
> Server: Msg 3723, Level 16, State 4, Line 1
> An explicit DROP INDEX is not allowed on index 'Room_Inv.PK_Room_Inv'.
> It is being used for PRIMARY KEY constraint enforcement.
> The help utility refer to ALTER TABLE options to DROP CONSTRAINT but it
> did
> not work.
> Gracias. LaEsmeralda|||I made it, Tom. Thanks a lot for quick help.
"Tom Moreau" wrote:
> ALTER TABLE is what you are supposed to use. What statement did you use and
> what was the error message?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
> news:F00810B8-62EA-4E19-A1EF-71AA5C8F9AB8@.microsoft.com...
> I could not find a way to remove primary key from an index, thus could not
> drop it.
> Command:
> drop index Romm_Inv.PK_Room_Inv
> Error
> Server: Msg 3723, Level 16, State 4, Line 1
> An explicit DROP INDEX is not allowed on index 'Room_Inv.PK_Room_Inv'.
> It is being used for PRIMARY KEY constraint enforcement.
> The help utility refer to ALTER TABLE options to DROP CONSTRAINT but it did
> not work.
> Gracias. LaEsmeralda
>

Cannot Drop Database

I am trying to drop a database, but keep getting the following error.

"cannot drop database 'blah' because it is currently being used for
replication".

This db is not currently being replicated, but once was. It is the
subscriber side of an old replication pair.

Can anyone tell what I have to do to make this go away?

Thanks,

TGru

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Hi

Have you checked that all the replication information has been removed?
See sp_removedbreplication in Books online and/or search Google/MSDN for
"Remove Replication"

John

"tgru" <tgru@.devdex.com> wrote in message news:4225fcc4$1_1@.127.0.0.1...
>I am trying to drop a database, but keep getting the following error.
> "cannot drop database 'blah' because it is currently being used for
> replication".
> This db is not currently being replicated, but once was. It is the
> subscriber side of an old replication pair.
> Can anyone tell what I have to do to make this go away?
> Thanks,
> TGru
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!