Cannot drop the table "xyz" as it is being used for replication

I have a replication setup running on 2 Win2K Server PCs with SQL
Server 2000.
Both the PCs have a common DB installed on them.
The setup takes care of starting one PC as primary and other as
backup.
It also starts the SQL replication (transactional), where both the PCs
are configured as publisher, distributor and subscriber.
Now the replication service creates a publication ONLY ON
PRIMARY and a subscription ONLY ON BACKUP. The replication starts.
When a switch-over of primary and backup is done, the existing
publication and subscription are deleted. A new publication is created
on new PRIMARY and a new subscription on the new BACKUP. Again,
replication works without a problem.
HOWEVER, sometimes, (I'm not able to reproduce & it is very
infrequent) on a switch-over I get thee error--"Cannot drop the table
"xyz" as it is being used for replication." The table is the one I
used for replication. Also, the primary keys exist on this table in
this case even though it's a part of subscription. The only solution
till now is to delete the entire replication on both machines and
re-create it.
I'm not using this DB anywhere else for any other purpose. Any
clues, why am I getting this problem occasionaly?
Thanks in advance.
Nelabh,
it's sometimes the case that some replication metadata is left hanging
around in system tables. sp_removedbreplication usually fixes these issues,
or the more granular sp_MSunmarkreplinfo may also be of interest.
HTH,
Paul Ibison

Cannot delete Maintenance Plan job

Hi
I had a Maintenance Plan with a scheduled job setup in SQL 2005. I have
deleted the Plan without deleting the scheduled job. Now I cannot delete the
job without the Plan. Any help will be great appreciated.
I have deleted the job by setting up another maintenance plan. After the new
maintenance plan ran, the orphaned job was deleted. Sorry, I think I should
have posted this on another newsgroup. Thanks!
"Yuhong" wrote:

> Hi
> I had a Maintenance Plan with a scheduled job setup in SQL 2005. I have
> deleted the Plan without deleting the scheduled job. Now I cannot delete the
> job without the Plan. Any help will be great appreciated.

Cannot connect to to the other server when setting up replication in SQL 2005

Im trying to setup replication between two 2005 standard editions. They are on the same LAN and installed equally + SP1. They are installed as named instances (with the same instance name MSSQL01). Remote access is allowed and TCP/IP and shared memory and I can connect between them.

SQL1\MSSQL01 is configured as the publisher & distributor and the snapshot is done. When Im using the subscriber wizard from SQL1 to create the subscriptions and add SQL2\MSSQL01 as a subscriber the connection fails to the remote server.

The error message is:

TITLE: Connect to Server

Cannot connect to SQL2\MSSQL01.

ADDITIONAL INFORMATION:

Failed to connect to server SQL2\MSSQL01. (Microsoft.SqlServer.ConnectionInfo)

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)

When I do a manual connection to SQL2 using the server managemnet studio on SQL1 I cannot connect using the instance name e.g. SQL2\MSSQL01, it works only when only using the server name e.g. SQL2. Then I have full access both when using a windows or SQL user.

When trying with using the server name only when setting up the subscription, I got a error saying that I need to use the full name.

TITLE: Connect to Server

Cannot connect to TFMHQARNDE07.

ADDITIONAL INFORMATION:

SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported. Specify the actual server name, ' SQL2\MSSQL01'. (Replication.Utilities)

I can also mention that theese servers are cloned from another server and I needed to drop the old servername and add the new servername (sp_addserver) on both servers to get the publisher to work.

When I try to setup the subscription from SQL2 and add SQL1 as a publisher I get the same error.

Any one have any idea what it can be?

I found the problem. I didnt have the SQL Browser service runnning which looks to be needed when using instances. After starting it, it works to connect.

Cannot connect to SQL express database ??

Dear all,
I have setup a database under SQLexpress 2005 which is install on a Vista OS
.
I can manage without any problem my database from the VIsta workstation.
Then on an other PC, I am trying to create a connection to that databse with
.NET 1.1, but no luck
I ma using similar conenction string as for an SQL server databse excpet
that Server="MyWorkStationName\SQLEXPRESS".
I have place the conenctionstring in a config file as follow :
" Database=Line_Config;Server=MyWorkStatio
nName\SQLEXPRESS;uid=rptuser;Pwd=re
porting"
What is wrong that prebvent me from connection to database ?
Is it possible to connect to an SQLExpress database located on an other PC ?
thnaks for help
regards
sergeProbably either the SQL Server Express instance has not enabled remote
connections, or a firewall is blocking the connection.
Run through the short tutorial in Books Online and see if that solves the
problem. The tutorial is also online at
http://msdn2.microsoft.com/en-us/library/ms345318.aspx Note that lesson 2 is
Connecting from Another Computer.
--
Rick Byham (MSFT)
This posting is provided "AS IS" with no warranties, and confers no rights.
"calderara" <calderara@.discussions.microsoft.com> wrote in message
news:067F10D8-72B1-4858-AE9F-E5EEF213BEF1@.microsoft.com...
> Dear all,
> I have setup a database under SQLexpress 2005 which is install on a Vista
> OS.
> I can manage without any problem my database from the VIsta workstation.
> Then on an other PC, I am trying to create a connection to that databse
> with
> .NET 1.1, but no luck
> I ma using similar conenction string as for an SQL server databse excpet
> that Server="MyWorkStationName\SQLEXPRESS".
> I have place the conenctionstring in a config file as follow :
>
> " Database=Line_Config;Server=MyWorkStatio
nName\SQLEXPRESS;uid=rptuser;Pwd=
reporting"
> What is wrong that prebvent me from connection to database ?
> Is it possible to connect to an SQLExpress database located on an other PC
> ?
> thnaks for help
> regards
> serge|||Thnaks for your reply...
I have check seetings as describe in this link.
What happen is that I have SQL express install on a Vista machine
Then I set use of tcp port from the surface configuration tool and set port
to 1500
From a simple remote machine running w2000, I try to establish an ODBC
connection to my VIsta machine where SQL express is installed.
For that I have install on my 2000 machine the SQL native client for 2005
and try to define a DSN name but when trying to connect...no way...
Any ideas ?
regards
serge
"Rick Byham, (MSFT)" wrote:

> Probably either the SQL Server Express instance has not enabled remote
> connections, or a firewall is blocking the connection.
> Run through the short tutorial in Books Online and see if that solves the
> problem. The tutorial is also online at
> http://msdn2.microsoft.com/en-us/library/ms345318.aspx Note that lesson 2
is
> Connecting from Another Computer.
> --
> Rick Byham (MSFT)
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "calderara" <calderara@.discussions.microsoft.com> wrote in message
> news:067F10D8-72B1-4858-AE9F-E5EEF213BEF1@.microsoft.com...
>|||Did you stop and restart SQL Server 2005 Express Edition after setting 1500
as the fixed port?
Are you attempting to connect to the Express Edition by name in the format
computer_name\SQLExpress ?
Is the SQL Server Browser service running?
Can you connect to the Express Edition by the port number using
computer_name,1500 or by IP address in the format 127.025.144.315,1500 ?
--
Rick Byham (MSFT)
This posting is provided "AS IS" with no warranties, and confers no rights.
"calderara" <calderara@.discussions.microsoft.com> wrote in message
news:F1AB79D1-899E-40C6-BE14-21D0305C36CA@.microsoft.com...[vbcol=seagreen]
> Thnaks for your reply...
> I have check seetings as describe in this link.
> What happen is that I have SQL express install on a Vista machine
> Then I set use of tcp port from the surface configuration tool and set
> port
> to 1500
> From a simple remote machine running w2000, I try to establish an ODBC
> connection to my VIsta machine where SQL express is installed.
> For that I have install on my 2000 machine the SQL native client for 2005
> and try to define a DSN name but when trying to connect...no way...
> Any ideas ?
> regards
> serge
> "Rick Byham, (MSFT)" wrote:
>|||OK it works now rick...
I have made 2 mistakes...
Frist of all I forgot to open the port 1500 in my windows firewall.
Then I was accessing my server by using <myPCname>\SQLEXPRESS as I use to do
normally with SQL server.
SO in order to work I have to connect using tcp:<myPCname>\SQLEXPRESS,1500
But I like whem I undersatnd things. This connection string using sytax
above is use like that only for this express edition ? I guess not, I mean I
have made many connection like that with SQL server ( On a real server sure)
and I was juist using the instance and Pc name.
Or may be it would work also if I add left the default setting when you use
TCP as port 1443 and let SQL as dynamic mode and then just open the port 144
3
on my firewall. Then use as connection string :<myPCname>\SQLEXPRESS.
But of course dynamic means dynamic.
Does it means that this dynamic TP port seting should never be use with SQL
server ?
regards
serge
"Rick Byham, (MSFT)" wrote:

> Did you stop and restart SQL Server 2005 Express Edition after setting 150
0
> as the fixed port?
> Are you attempting to connect to the Express Edition by name in the format
> computer_name\SQLExpress ?
> Is the SQL Server Browser service running?
> Can you connect to the Express Edition by the port number using
> computer_name,1500 or by IP address in the format 127.025.144.315,1500 ?
> --
> Rick Byham (MSFT)
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "calderara" <calderara@.discussions.microsoft.com> wrote in message
> news:F1AB79D1-899E-40C6-BE14-21D0305C36CA@.microsoft.com...
>|||Connecting to the server name (<myPCname>\SQLEXPRESS) should work, if the
SQL Server Browser service is running. However, yes, you are correct that if
SQL Server Express Edition is using dynamic ports, then the port could
change to one that is blocked by the firewall. So you will need to switch
SQL Server Express Edition to a fixed port.
--
Rick Byham (MSFT)
This posting is provided "AS IS" with no warranties, and confers no rights.
"calderara" <calderara@.discussions.microsoft.com> wrote in message
news:B778178D-FF1E-4827-981E-369B3C5C9AFE@.microsoft.com...[vbcol=seagreen]
> OK it works now rick...
> I have made 2 mistakes...
> Frist of all I forgot to open the port 1500 in my windows firewall.
> Then I was accessing my server by using <myPCname>\SQLEXPRESS as I use to
> do
> normally with SQL server.
> SO in order to work I have to connect using tcp:<myPCname>\SQLEXPRESS,1500
> But I like whem I undersatnd things. This connection string using sytax
> above is use like that only for this express edition ? I guess not, I mean
> I
> have made many connection like that with SQL server ( On a real server
> sure)
> and I was juist using the instance and Pc name.
> Or may be it would work also if I add left the default setting when you
> use
> TCP as port 1443 and let SQL as dynamic mode and then just open the port
> 1443
> on my firewall. Then use as connection string :<myPCname>\SQLEXPRESS.
> But of course dynamic means dynamic.
> Does it means that this dynamic TP port seting should never be use with
> SQL
> server ?
> regards
> serge
> "Rick Byham, (MSFT)" wrote:
>

Cannot connect to SQL express database ??

Dear all,
I have setup a database under SQLexpress 2005 which is install on a Vista OS.
I can manage without any problem my database from the VIsta workstation.
Then on an other PC, I am trying to create a connection to that databse with
..NET 1.1, but no luck
I ma using similar conenction string as for an SQL server databse excpet
that Server="MyWorkStationName\SQLEXPRESS".
I have place the conenctionstring in a config file as follow :
"Database=Line_Config;Server=MyWorkStationName\SQL EXPRESS;uid=rptuser;Pwd=reporting"
What is wrong that prebvent me from connection to database ?
Is it possible to connect to an SQLExpress database located on an other PC ?
thnaks for help
regards
serge
Probably either the SQL Server Express instance has not enabled remote
connections, or a firewall is blocking the connection.
Run through the short tutorial in Books Online and see if that solves the
problem. The tutorial is also online at
http://msdn2.microsoft.com/en-us/library/ms345318.aspx Note that lesson 2 is
Connecting from Another Computer.
Rick Byham (MSFT)
This posting is provided "AS IS" with no warranties, and confers no rights.
"calderara" <calderara@.discussions.microsoft.com> wrote in message
news:067F10D8-72B1-4858-AE9F-E5EEF213BEF1@.microsoft.com...
> Dear all,
> I have setup a database under SQLexpress 2005 which is install on a Vista
> OS.
> I can manage without any problem my database from the VIsta workstation.
> Then on an other PC, I am trying to create a connection to that databse
> with
> .NET 1.1, but no luck
> I ma using similar conenction string as for an SQL server databse excpet
> that Server="MyWorkStationName\SQLEXPRESS".
> I have place the conenctionstring in a config file as follow :
>
> "Database=Line_Config;Server=MyWorkStationName\SQL EXPRESS;uid=rptuser;Pwd=reporting"
> What is wrong that prebvent me from connection to database ?
> Is it possible to connect to an SQLExpress database located on an other PC
> ?
> thnaks for help
> regards
> serge
|||Thnaks for your reply...
I have check seetings as describe in this link.
What happen is that I have SQL express install on a Vista machine
Then I set use of tcp port from the surface configuration tool and set port
to 1500
From a simple remote machine running w2000, I try to establish an ODBC
connection to my VIsta machine where SQL express is installed.
For that I have install on my 2000 machine the SQL native client for 2005
and try to define a DSN name but when trying to connect...no way...
Any ideas ?
regards
serge
"Rick Byham, (MSFT)" wrote:

> Probably either the SQL Server Express instance has not enabled remote
> connections, or a firewall is blocking the connection.
> Run through the short tutorial in Books Online and see if that solves the
> problem. The tutorial is also online at
> http://msdn2.microsoft.com/en-us/library/ms345318.aspx Note that lesson 2 is
> Connecting from Another Computer.
> --
> Rick Byham (MSFT)
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "calderara" <calderara@.discussions.microsoft.com> wrote in message
> news:067F10D8-72B1-4858-AE9F-E5EEF213BEF1@.microsoft.com...
>
|||Did you stop and restart SQL Server 2005 Express Edition after setting 1500
as the fixed port?
Are you attempting to connect to the Express Edition by name in the format
computer_name\SQLExpress ?
Is the SQL Server Browser service running?
Can you connect to the Express Edition by the port number using
computer_name,1500 or by IP address in the format 127.025.144.315,1500 ?
Rick Byham (MSFT)
This posting is provided "AS IS" with no warranties, and confers no rights.
"calderara" <calderara@.discussions.microsoft.com> wrote in message
news:F1AB79D1-899E-40C6-BE14-21D0305C36CA@.microsoft.com...[vbcol=seagreen]
> Thnaks for your reply...
> I have check seetings as describe in this link.
> What happen is that I have SQL express install on a Vista machine
> Then I set use of tcp port from the surface configuration tool and set
> port
> to 1500
> From a simple remote machine running w2000, I try to establish an ODBC
> connection to my VIsta machine where SQL express is installed.
> For that I have install on my 2000 machine the SQL native client for 2005
> and try to define a DSN name but when trying to connect...no way...
> Any ideas ?
> regards
> serge
> "Rick Byham, (MSFT)" wrote:
|||OK it works now rick...
I have made 2 mistakes...
Frist of all I forgot to open the port 1500 in my windows firewall.
Then I was accessing my server by using <myPCname>\SQLEXPRESS as I use to do
normally with SQL server.
SO in order to work I have to connect using tcp:<myPCname>\SQLEXPRESS,1500
But I like whem I undersatnd things. This connection string using sytax
above is use like that only for this express edition ? I guess not, I mean I
have made many connection like that with SQL server ( On a real server sure)
and I was juist using the instance and Pc name.
Or may be it would work also if I add left the default setting when you use
TCP as port 1443 and let SQL as dynamic mode and then just open the port 1443
on my firewall. Then use as connection string :<myPCname>\SQLEXPRESS.
But of course dynamic means dynamic.
Does it means that this dynamic TP port seting should never be use with SQL
server ?
regards
serge
"Rick Byham, (MSFT)" wrote:

> Did you stop and restart SQL Server 2005 Express Edition after setting 1500
> as the fixed port?
> Are you attempting to connect to the Express Edition by name in the format
> computer_name\SQLExpress ?
> Is the SQL Server Browser service running?
> Can you connect to the Express Edition by the port number using
> computer_name,1500 or by IP address in the format 127.025.144.315,1500 ?
> --
> Rick Byham (MSFT)
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "calderara" <calderara@.discussions.microsoft.com> wrote in message
> news:F1AB79D1-899E-40C6-BE14-21D0305C36CA@.microsoft.com...
>
|||Connecting to the server name (<myPCname>\SQLEXPRESS) should work, if the
SQL Server Browser service is running. However, yes, you are correct that if
SQL Server Express Edition is using dynamic ports, then the port could
change to one that is blocked by the firewall. So you will need to switch
SQL Server Express Edition to a fixed port.
Rick Byham (MSFT)
This posting is provided "AS IS" with no warranties, and confers no rights.
"calderara" <calderara@.discussions.microsoft.com> wrote in message
news:B778178D-FF1E-4827-981E-369B3C5C9AFE@.microsoft.com...[vbcol=seagreen]
> OK it works now rick...
> I have made 2 mistakes...
> Frist of all I forgot to open the port 1500 in my windows firewall.
> Then I was accessing my server by using <myPCname>\SQLEXPRESS as I use to
> do
> normally with SQL server.
> SO in order to work I have to connect using tcp:<myPCname>\SQLEXPRESS,1500
> But I like whem I undersatnd things. This connection string using sytax
> above is use like that only for this express edition ? I guess not, I mean
> I
> have made many connection like that with SQL server ( On a real server
> sure)
> and I was juist using the instance and Pc name.
> Or may be it would work also if I add left the default setting when you
> use
> TCP as port 1443 and let SQL as dynamic mode and then just open the port
> 1443
> on my firewall. Then use as connection string :<myPCname>\SQLEXPRESS.
> But of course dynamic means dynamic.
> Does it means that this dynamic TP port seting should never be use with
> SQL
> server ?
> regards
> serge
> "Rick Byham, (MSFT)" wrote:

Cannot Connect to SQL 2000 on Win2k3 Server

I am having great difficulty connecting to my sql box, I
vpn'd in from work and connected via remote desktop,
setup the SQL connection locally, and connected fine,
which I am wondering if it was cause I was VPN'd in, but
I disconnected from remote desktop and was still able to
hit my sql server in enterprise manager. Then when I got
home tonight, logged on my XP machine and I cannot get my
sql registration to connect.
SQL Server does not exist or access denied ConnectionOpen
(Connect()
I have no idea what this means...I am just learning SQL
so this is all new to me...please help...I am on a home
network behind a linksys router, both my XP box and the
Win2k3 box are behind the router which has a dynamic ip
for the XP box, and a static one for the 2k3 box.On Windows 2003, if there are any instances of SQL Server installed that
are not on SP3 or higher, TCP will be disabled (there will be messages in
the event logs indicating this). In that case remote connections would fail
because by default clients try to connect with TCP. Upgrade all instances
on the box to SP3 then retry connecting. You can also try to connect from
the remote machines using named pipes (add np: before the machine\instance
in your connection string).
328306 INF: Potential Causes of the "SQL Server Does Not Exist or Access
http://support.microsoft.com/?id=328306
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.

Cannot connect remotely to MSDE2000

Hi
I uninstalled SQL7 from a machine and then installed
MSDE2000 (using setup disablenetworkprotocols=0
securitymode=sql sawpw=x)
I can connect localy without a problem but as soon as I
try and connect (via EM or even ODBC) remotely, I get a
network library error. I've tried connecting with TCP and
Named Pipes protocols.
Any ideas?
H
Hi,
As a first step, try to see if the tcp protocol really is enabled. Run
svrnetcn.exe on the MSDE box and make sure it is.
HTH,
Greg Low (MVP)
MSDE Manager SQL Tools
www.whitebearconsulting.com
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:2ae5901c4685b$49dc6630$a601280a@.phx.gbl...
> Hi
> I uninstalled SQL7 from a machine and then installed
> MSDE2000 (using setup disablenetworkprotocols=0
> securitymode=sql sawpw=x)
> I can connect localy without a problem but as soon as I
> try and connect (via EM or even ODBC) remotely, I get a
> network library error. I've tried connecting with TCP and
> Named Pipes protocols.
> Any ideas?
> H