cannot delete subscriptions

To test SQL2000 replication, I enabled replications for NorthWind between
ServerA and ServerB. ServerA was the publisher and distributor, ServerB was
the subscriber.
After test, I deleted everything about replication on ServerA, at last, I
disabled replication. However, when I ckeck ServerB, in the
Replication\Subscriptions container, There is a "ServerA:NorthWind:" object.
I cannot remove it. If I right click this item, it only has a "set update
method..." menu which is grayed out, and another "help" menu. It looks like
a phantom object.
I then re-configure the server as a subscriber again (subscribe test DB and
set ServerA as a publisher), the Replication\Subscriptions container now has
another object "ServerA:test:test", and the content menu looks normal, which
includes "New pull subscription...", "Reinitialize", "delete"...etc. menu.
How can I delete the "ServerA:NorthWind:" object?
OK, it looks like you have lingering metadata. If you can, can you disable
replication one more time.
then run the following script
create table whack_me ([database] sysname, [table] sysname, constraint
Whack_me_pk primary key([database], [table]))
GO
insert into whack_me([database],[table])
exec sp_msforeachdb 'select [database]=''?'',[table]=''
MSreplication_subscriptions'' from ?.dbo.MSreplication_subscriptions where
publisher=@.@.servername'
insert into whack_me([database],[table])
exec sp_msforeachdb 'select [database]=''?'',[table]=''
MSsubscription_agents'' from ?.dbo.MSsubscription_agents where
publisher=@.@.servername'
whack_me will have a list of the lingering subscriptions and the databases
they are in. Delete the contents of these tables where the publisher
=@.@.servername
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"MLi" <lige888@.gmail.com> wrote in message
news:uxGiwzXEGHA.3004@.TK2MSFTNGP15.phx.gbl...
> To test SQL2000 replication, I enabled replications for NorthWind between
> ServerA and ServerB. ServerA was the publisher and distributor, ServerB
> was the subscriber.
> After test, I deleted everything about replication on ServerA, at last, I
> disabled replication. However, when I ckeck ServerB, in the
> Replication\Subscriptions container, There is a "ServerA:NorthWind:"
> object. I cannot remove it. If I right click this item, it only has a "set
> update method..." menu which is grayed out, and another "help" menu. It
> looks like a phantom object.
> I then re-configure the server as a subscriber again (subscribe test DB
> and set ServerA as a publisher), the Replication\Subscriptions container
> now has another object "ServerA:test:test", and the content menu looks
> normal, which includes "New pull subscription...", "Reinitialize",
> "delete"...etc. menu.
> How can I delete the "ServerA:NorthWind:" object?
>
|||I ran the script on ServerA and got a bunch of errors:
Invalid object name 'Northwind.dbo.MSsubscription_agents'.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'master.dbo.MSsubscription_agents'.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'model.dbo.MSsubscription_agents'.
Msg 208, Level 16, State 1, Line 1
....
I just want to delete everything about NorthWind replication, any easy way?
like delete all rows about NorthWind in some particular table?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:Oxgn9BaEGHA.216@.TK2MSFTNGP15.phx.gbl...
> OK, it looks like you have lingering metadata. If you can, can you disable
> replication one more time.
> then run the following script
> create table whack_me ([database] sysname, [table] sysname, constraint
> Whack_me_pk primary key([database], [table]))
> GO
> insert into whack_me([database],[table])
> exec sp_msforeachdb 'select [database]=''?'',[table]=''
> MSreplication_subscriptions'' from ?.dbo.MSreplication_subscriptions
> where publisher=@.@.servername'
> insert into whack_me([database],[table])
> exec sp_msforeachdb 'select [database]=''?'',[table]=''
> MSsubscription_agents'' from ?.dbo.MSsubscription_agents where
> publisher=@.@.servername'
> whack_me will have a list of the lingering subscriptions and the databases
> they are in. Delete the contents of these tables where the publisher
> =@.@.servername
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "MLi" <lige888@.gmail.com> wrote in message
> news:uxGiwzXEGHA.3004@.TK2MSFTNGP15.phx.gbl...
>
|||in the table whack_me you will find a list of databases and tables. Go
through these databases and tables and delete the contents of them.
Ignore the error messages.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"MLi" <lige888@.gmail.com> wrote in message
news:u1r7y4mEGHA.4036@.TK2MSFTNGP09.phx.gbl...
>I ran the script on ServerA and got a bunch of errors:
> Invalid object name 'Northwind.dbo.MSsubscription_agents'.
> Msg 208, Level 16, State 1, Line 1
> Invalid object name 'master.dbo.MSsubscription_agents'.
> Msg 208, Level 16, State 1, Line 1
> Invalid object name 'model.dbo.MSsubscription_agents'.
> Msg 208, Level 16, State 1, Line 1
> ...
> I just want to delete everything about NorthWind replication, any easy
> way? like delete all rows about NorthWind in some particular table?
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:Oxgn9BaEGHA.216@.TK2MSFTNGP15.phx.gbl...
>
|||sp_removedbreplication
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"MLi" <lige888@.gmail.com> wrote in message
news:u1r7y4mEGHA.4036@.TK2MSFTNGP09.phx.gbl...
>I ran the script on ServerA and got a bunch of errors:
> Invalid object name 'Northwind.dbo.MSsubscription_agents'.
> Msg 208, Level 16, State 1, Line 1
> Invalid object name 'master.dbo.MSsubscription_agents'.
> Msg 208, Level 16, State 1, Line 1
> Invalid object name 'model.dbo.MSsubscription_agents'.
> Msg 208, Level 16, State 1, Line 1
> ...
> I just want to delete everything about NorthWind replication, any easy
> way? like delete all rows about NorthWind in some particular table?
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:Oxgn9BaEGHA.216@.TK2MSFTNGP15.phx.gbl...
>
|||With all due respect Mike the OP did this to begin with.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:ecQUaGpEGHA.1312@.TK2MSFTNGP09.phx.gbl...
> sp_removedbreplication
> --
> Mike
> Mentor
> Solid Quality Learning
> http://www.solidqualitylearning.com
>
> "MLi" <lige888@.gmail.com> wrote in message
> news:u1r7y4mEGHA.4036@.TK2MSFTNGP09.phx.gbl...
>
|||I'm sorry Hilary, I guess I was just too stupid to realize that. Thank you
for helping me understand my stupidity.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ulLILIsEGHA.3708@.TK2MSFTNGP10.phx.gbl...
> With all due respect Mike the OP did this to begin with.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
> news:ecQUaGpEGHA.1312@.TK2MSFTNGP09.phx.gbl...
>
|||Just found another way to delete this lingering object-- by SQL 2005
Management Studio
The object has a "delete" context menu
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:ecQUaGpEGHA.1312@.TK2MSFTNGP09.phx.gbl...
> sp_removedbreplication
> --
> Mike
> Mentor
> Solid Quality Learning
> http://www.solidqualitylearning.com
>
> "MLi" <lige888@.gmail.com> wrote in message
> news:u1r7y4mEGHA.4036@.TK2MSFTNGP09.phx.gbl...
>

Cannot Delete Maintenance Plans - SQL 2005

A consultant created a maintenance plan that isn't in use and is not
enabled. I want to delete the plan but get the following error.
TITLE: Microsoft SQL Server Management Studio
--
Drop failed for Job 'Maintenance II'. (Microsoft.SqlServer.Smo)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+Job&LinkId=20476
--
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
--
The DELETE statement conflicted with the REFERENCE constraint
"FK_subplan_job_id". The conflict occurred in database "msdb", table
"dbo.sysmaintplan_subplans", column 'job_id'.
The statement has been terminated. (Microsoft SQL Server, Error: 547)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=547&LinkId=20476
--
BUTTONS:
OK
--
I"m such a noob, how can i delete this plan?
tia,
Randy>From what I can tell from the error msg, the job you are trying to
delete has a subplan that references it and you can't delete it until
you get rid of the subplan.
How I would do this:
1.) get job id #
2.) look up job id# in the system table in question
3.) find the subplan for the job id #
4.) delete subplan
4.) delete job
proceed with caution and don't delete anything unless 100% sure !
you should back up your jobs first, too in case something blows up
perhaps there is an easier way to do this through the menus in
Management Studio
Randy H wrote:
> A consultant created a maintenance plan that isn't in use and is not
> enabled. I want to delete the plan but get the following error.
> TITLE: Microsoft SQL Server Management Studio
> --
> Drop failed for Job 'Maintenance II'. (Microsoft.SqlServer.Smo)
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+Job&LinkId=20476
> --
> ADDITIONAL INFORMATION:
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> The DELETE statement conflicted with the REFERENCE constraint
> "FK_subplan_job_id". The conflict occurred in database "msdb", table
> "dbo.sysmaintplan_subplans", column 'job_id'.
> The statement has been terminated. (Microsoft SQL Server, Error: 547)
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=547&LinkId=20476
> --
> BUTTONS:
> OK
> --
> I"m such a noob, how can i delete this plan?
> tia,
> Randy|||Is there an easier way through the menus?
I am not having any luck finding the sub plan for the job id #
<tootsuite@.gmail.com> wrote in message
news:1160078499.215907.169040@.k70g2000cwa.googlegroups.com...
> >From what I can tell from the error msg, the job you are trying to
> delete has a subplan that references it and you can't delete it until
> you get rid of the subplan.
> How I would do this:
> 1.) get job id #
> 2.) look up job id# in the system table in question
> 3.) find the subplan for the job id #
> 4.) delete subplan
> 4.) delete job
> proceed with caution and don't delete anything unless 100% sure !
> you should back up your jobs first, too in case something blows up
> perhaps there is an easier way to do this through the menus in
> Management Studio
>
> Randy H wrote:
>> A consultant created a maintenance plan that isn't in use and is not
>> enabled. I want to delete the plan but get the following error.
>> TITLE: Microsoft SQL Server Management Studio
>> --
>> Drop failed for Job 'Maintenance II'. (Microsoft.SqlServer.Smo)
>> For help, click:
>> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+Job&LinkId=20476
>> --
>> ADDITIONAL INFORMATION:
>> An exception occurred while executing a Transact-SQL statement or batch.
>> (Microsoft.SqlServer.ConnectionInfo)
>> --
>> The DELETE statement conflicted with the REFERENCE constraint
>> "FK_subplan_job_id". The conflict occurred in database "msdb", table
>> "dbo.sysmaintplan_subplans", column 'job_id'.
>> The statement has been terminated. (Microsoft SQL Server, Error: 547)
>> For help, click:
>> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=547&LinkId=20476
>> --
>> BUTTONS:
>> OK
>> --
>> I"m such a noob, how can i delete this plan?
>> tia,
>> Randy
>|||Here is the SQL to fix this:
http://www.gudenas.com/2007/04/20/sql-server-2005-delete-maintenance-plan-error
From http://www.developmentnow.com/g/118_2006_10_0_0_831628/Cannot-Delete-Maintenance-Plans--SQL-2005.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.comsql

Cannot Debug SQL 2005 Stored Procedures in VS 2005

I am having trouble in consistently debugging T-SQL stored procedures in SQL
2005 using VS 2005 Pro. I have enabled debugging on the connection in the
Server Explorer and enable CLR Integration in the Surface Area Configuration
tool. At one point I could step into the sproc but now I cannot. What has
gone wrong. How can I be able to consistently debug T-SQL sprocs?
Any input would be appreciated."=?Utf-8?B? RWFnbGVSZWRASGlnaEZseWluZ0JpcmRzLmNvbQ==
?="
<EagleRedHighFlyingBirdscom@.discussions.microsoft.com> wrote in
news:2EC86A08-79A4-44CC-ABDF-72DAE46D1BF0@.microsoft.com:

> I am having trouble in consistently debugging T-SQL stored procedures
> in SQL 2005 using VS 2005 Pro. I have enabled debugging on the
> connection in the Server Explorer and enable CLR Integration in the
> Surface Area Configuration tool. At one point I could step into the
> sproc but now I cannot. What has gone wrong. How can I be able to
> consistently debug T-SQL sprocs?
>
Hmm, in order to debug T-SQL proc's you should not have to do any
enabling at all.
1. Open VS 2005
2. Open Server Explorer
3. Create a connection to the database the proc is in
4. Expand the database and drill down to Stored Procedures, expand.
5. Right click on the proc and choose "Step Into Stored Procedure".
6. Debug
What happens for you when you try the above?
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||The debugger starts but when I either step into or step to the next statemen
t
in the proc the proc runs to completion. I have set a breakpoint in the pro
c
only to have it disabled when debugging starts.
"EagleRed@.HighFlyingBirds.com" wrote:

> I am having trouble in consistently debugging T-SQL stored procedures in S
QL
> 2005 using VS 2005 Pro. I have enabled debugging on the connection in the
> Server Explorer and enable CLR Integration in the Surface Area Configurati
on
> tool. At one point I could step into the sproc but now I cannot. What ha
s
> gone wrong. How can I be able to consistently debug T-SQL sprocs?
> Any input would be appreciated.|||Here's some additional information. The disabled breakpoint displays a
tooltip that says the breakpoint will not be hit because the object
containing the breakpoint has not been loaded. What's going on? I have bee
n
able to debug and hit breakpoints, but I cannot do it consistently and right
now it simply isn't working.
"Niels Berglund" wrote:

> "=?Utf-8?B? RWFnbGVSZWRASGlnaEZseWluZ0JpcmRzLmNvbQ==
?="
> <EagleRedHighFlyingBirdscom@.discussions.microsoft.com> wrote in
> news:2EC86A08-79A4-44CC-ABDF-72DAE46D1BF0@.microsoft.com:
>
> Hmm, in order to debug T-SQL proc's you should not have to do any
> enabling at all.
> 1. Open VS 2005
> 2. Open Server Explorer
> 3. Create a connection to the database the proc is in
> 4. Expand the database and drill down to Stored Procedures, expand.
> 5. Right click on the proc and choose "Step Into Stored Procedure".
> 6. Debug
> What happens for you when you try the above?
> Niels
> --
> ****************************************
**********
> * Niels Berglund
> * http://staff.develop.com/nielsb
> * nielsb@.no-spam.develop.com
> * "A First Look at SQL Server 2005 for Developers"
> * http://www.awprofessional.com/title/0321180593
> ****************************************
**********
>

Cannot create a new Oracle Publisher

I got this error when create a Oracle Publisher:

Oracle server cannot be enabled as a Publisher because of the following error:

Additional inlonnation:

Unable to connect to Oracle database server 'abc’ using the Microsoft OLEDB provider MsDAORA.

For addition ilorrnation, see SQL Server Eiror 21627 in Troubleshooting Oracle Publishers in sQL Server Books 0nline. (Microsoft sQL Server, Error: 21627)

The login works fine in SQL Plus.

My test server:

SQL Server 2005 Developer w/ SP2

Oracle Client installed on SQL Server DB

MSDAORA installed

Please help.

Regards,

d-cpt

What version of Oracle client are you using?

What version of Oracle server are you connecting to?

Regards,

Gary

|||

Thank you for reply to my post.

Just back from a trip...

The Oracle client version is 10g

The Oracle DB version is 8.

Regards,

dcpt

|||

I believe you need to use the Oracle client driver to connect to Oracle publisher. It seems like you are using the Microsoft Oracle Oledb driver. Give that a shoot.

Regards,

Gary

Cannot create a new Oracle Publisher

I got this error when create a Oracle Publisher:

Oracle server cannot be enabled as a Publisher because of the following error:

Additional inlonnation:

Unable to connect to Oracle database server 'abc’ using the Microsoft OLEDB provider MsDAORA.

For addition ilorrnation, see SQL Server Eiror 21627 in Troubleshooting Oracle Publishers in sQL Server Books 0nline. (Microsoft sQL Server, Error: 21627)

The login works fine in SQL Plus.

My test server:

SQL Server 2005 Developer w/ SP2

Oracle Client installed on SQL Server DB

MSDAORA installed

Please help.

Regards,

d-cpt

What version of Oracle client are you using?

What version of Oracle server are you connecting to?

Regards,

Gary

|||

Thank you for reply to my post.

Just back from a trip...

The Oracle client version is 10g

The Oracle DB version is 8.

Regards,

dcpt

|||

I believe you need to use the Oracle client driver to connect to Oracle publisher. It seems like you are using the Microsoft Oracle Oledb driver. Give that a shoot.

Regards,

Gary

Cannot connect to SQL Server 2005 Express with Management Studio Express

On my home machine without permanent network connections enabled, I cannot get the Management Studio connect to the database server. Always get this error:

Cannot connect ot MPLIAM\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, erro: 26 - Error Locating Server/Instance Specified)(Microsoft SQL Server)

I have used the SQL Surface Area Configuration Tool to reset the defaults to allow remote connections, stopped and restarted the server, but still get the same message.

Please help.

Hi Michael,

I think this is a duplicate of another post from you, but I'll answer here too.

You don't say if you're actually trying to connect remotely or not. If you're connecting remotely, the issue is likely your firewall is blocking the connection. Check out the FAQ at the top of this forum for a link to the article that discuss the complete steps to allow remote connections.

Mike

|||

Can you ping \\MPLIAM?

Try usingthe IP address rather than the server name.

|||

Sorry. I lost the other post.

I'm just trying to connect on my own local machine to the server which is installed on my own local machine.

I have McAffee anti-virus software installed. There's no telling what that monstrosity is doing to shut down things -- all in the line of 'enhanced security', of course.

I am just about at my wits end trying to get this thing to work. I neglected to mention that when I first installed the server and the management studio, at least the latter connected to the former. Something changed somewhere along the way so that now it doesnt work. I've checked the Service in the Control Panel and it seems to be running. I even stopped and restarted it several times with no success. I used the config manager to configure the 'surface area' (love that one) so that remote connections are enabled. Still no luck.

What to do next?

|||

ping mpliam

Pinging MPLIAM [169.254.101.152] with 32 bytes of data:

Reply from 169.254.101.152: bytes=32 time<1ms TTL=128
Reply from 169.254.101.152: bytes=32 time<1ms TTL=128
Reply from 169.254.101.152: bytes=32 time<1ms TTL=128
Reply from 169.254.101.152: bytes=32 time<1ms TTL=128

Ping statistics for 169.254.101.152:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 0ms, Maximum = 0ms, Average = 0ms

Using Server Name: 169.254.101.152 in the Managment Studio

'cannot connect to database'

Any other ideas ?

|||

(a) check whether the service started or not from Configuration Manager Window

(b) Check whether the protocols are enabled or not from SQL Configuration Manager window

(c) Check which all are the client protocol enabled

Madhu

|||

In the other thread I had suggest trying a connection via SQLCmd, just to rule out any Management Studio weirdness, are you able to do that? To reiterate, open a command prompt and type the following:

SQLCmd -S .\SQLEXPRESS -E -Q "SELECT @.@.Version"

This should return version information about your server if it works. If it fails, please post back the complete error message. As a second step should you encounter failure, please check the error log after the attempted connection to see if there is additional informaton there. The error log should be at C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG.

It's interesting that you say this worked initially but connectons started failing later. This would indeed suggest that "something else" is impacting this, but I've no idea what. In general I wouldn't expect any AV program to prevent connections to SQL, but we should probably test this. As a temprorary troubleshooting step, disable McAffe and then try your connection again. Regardless of the result, turn McAffe back on once you've completed the test, but let us know if that impacts anything.

Finally, could you copy the test from the "Server" text box in the Management Studio Connection dialog and paste it into your reply?

Mike

|||

Thanks for your response, Mike.

I apologize for taking so long to respond. In any case, when I attempted at the Command Prompt:

SQLCmd -S .\SQLEXPRESS -E -Q "SELECT @.@.Version"

I received the following message (preceeded by a bunch of dashed lines):

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

(1 rows affected)

The Error Log was quite extensive and, since I have been fooling with SQL Server quite a bit, I am uncertain which section pertains. But I interpret the above to indicate that the installation is working.

Here's a little bit of the most recent part of the Error Log:

2007-03-18 09:00:44.95 Server Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

2007-03-18 09:00:44.95 Server (c) 2005 Microsoft Corporation.
2007-03-18 09:00:44.95 Server All rights reserved.
2007-03-18 09:00:44.96 Server Server process ID is 552.
2007-03-18 09:00:44.96 Server Logging SQL Server messages in file 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
2007-03-18 09:00:44.96 Server This instance of SQL Server last reported using a process ID of 236 at 3/17/2007 10:54:30 PM (local) 3/18/2007 5:54:30 AM (UTC). This is an informational message only; no user action is required.
2007-03-18 09:00:44.98 Server Registry startup parameters:
2007-03-18 09:00:44.98 Server -d c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
2007-03-18 09:00:44.98 Server -e c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
2007-03-18 09:00:44.98 Server -l c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
2007-03-18 09:00:45.06 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2007-03-18 09:00:45.06 Server Detected 1 CPUs. This is an informational message; no user action is required.
2007-03-18 09:00:45.67 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
2007-03-18 09:00:45.70 Server Database Mirroring Transport is disabled in the endpoint configuration.
2007-03-18 09:00:45.71 spid5s Starting up database 'master'.
2007-03-18 09:00:46.04 spid5s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
// .....

The Server Text Box from the Management Studio (if I understand your request) looks as follows:

Connect to Server

Server type: Database Engine (disabled)
Server name: MPLIAM\SQLEXPRESS
Authentication: Windows Atuhentication
User name: MBPLIAM\Michael B Pliam (disabled)
Password: (blank and disabled)

Attempting to connect results in:

Connect to Server
Cannot connect to MPLIAM\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)

I will try to disable McAffee Virus Protection and get back to you if it makes any difference (Assuming that I can figure out how to disable it - it's sort of like getting rid of a bad cold.)

Thanks again for your help. Again, I wish to apologize for having lost this thread.

|||

You are correct that the output of SQLCmd indicates that SQL Express is working fine and can be accessed locally with no problems. This would suggest a specific problem with Management Studio. I don't see any problem with the information you supplied, but the error message indicates that the specific instance name you've supplied is incorrect.

Replace the value in the 'Server name' text box with .\SQLEXPRESS and see if that makes any difference. If this works, it would mean that your machine name is not actually MPLIAM. If it doesn't work, we may to punt this over to the tools guys to see if they have any ideas.

Mike

|||

Problem solved !

Using .\SQLEXPRESS instead of MPLIAM\SQLEXPRESS allowed the Management Studio to connect to the server.

I still do not understand what the problem was.

My 'host' machine is definitely 'MPLIAM'.

If I 'ping MPLIAM' from a Command Line prompt (as I believe I posted before), I receive the response:

Pinging MPLIAM [169.254.101.152] with 32 bytes of data:

Reply from 169.254.101.152: bytes=32 time<1ms TTL=128
Reply from 169.254.101.152: bytes=32 time<1ms TTL=128
Reply from 169.254.101.152: bytes=32 time<1ms TTL=128
Reply from 169.254.101.152: bytes=32 time<1ms TTL=128

Ping statistics for 169.254.101.152:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 0ms, Maximum = 0ms, Average = 0ms

Further, MSDN provides C++ source code to determine the host machine name and volume. For those interested, see:

http://windowssdk.msdn.microsoft.com/en-us/library/ms738527.aspx

Running that program reveals

MPLIAM
3758647270

the host name and volume number respectively.

When Management Studio did connect (as noted above), the connection text box header has the following information:

Summary

.\SQLEXPRESS (SQL Server 9.0.1399 - MPLIAM\Michael B Pliam)
MPLIAM\SQLEXPRESS 5 Items(s)

Mike, I want to thank you for your patience in helping me to solve this problem. If my tone at times appeared intemperant, it was simply my frustration at being unable to get this program to work. As is usually the case, it was probably my fault that things were awry. I must have changed something without being aware of it. Once things go bad, I have a tendency to start changing everything in sight instead of cautiously exploring the nature of the problem.

Regards

|||

I'm glad that it's working. It's odd that the machine name is not being recognized, but if you're happy, I'm happy.

Mike

|||Hi,

I've been struggling with a similar problem. Wasn't able to connect remotely and everything was setup correctly on the Surface Area Configuration. I could connect locally though, but not remotely. Used the exact same Windows User each time, and no firewall in between client and server. The eventual resolution was something really simple and stupid on my part.

The SQL Server browser service wasn't running, all that I did was start it and suddenly I could connect remotely... which is strange because I've never had this issue with any other SQL server I've worked with...

anyway hope it helps...

Cannot connect to SQL Server 2005 Express with Management Studio Express

On my home machine without permanent network connections enabled, I cannot get the Management Studio connect to the database server. Always get this error:

Cannot connect ot MPLIAM\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, erro: 26 - Error Locating Server/Instance Specified)(Microsoft SQL Server)

I have used the SQL Surface Area Configuration Tool to reset the defaults to allow remote connections, stopped and restarted the server, but still get the same message.

Please help.

Hi Michael,

I think this is a duplicate of another post from you, but I'll answer here too.

You don't say if you're actually trying to connect remotely or not. If you're connecting remotely, the issue is likely your firewall is blocking the connection. Check out the FAQ at the top of this forum for a link to the article that discuss the complete steps to allow remote connections.

Mike

|||

Can you ping \\MPLIAM?

Try usingthe IP address rather than the server name.

|||

Sorry. I lost the other post.

I'm just trying to connect on my own local machine to the server which is installed on my own local machine.

I have McAffee anti-virus software installed. There's no telling what that monstrosity is doing to shut down things -- all in the line of 'enhanced security', of course.

I am just about at my wits end trying to get this thing to work. I neglected to mention that when I first installed the server and the management studio, at least the latter connected to the former. Something changed somewhere along the way so that now it doesnt work. I've checked the Service in the Control Panel and it seems to be running. I even stopped and restarted it several times with no success. I used the config manager to configure the 'surface area' (love that one) so that remote connections are enabled. Still no luck.

What to do next?

|||

ping mpliam

Pinging MPLIAM [169.254.101.152] with 32 bytes of data:

Reply from 169.254.101.152: bytes=32 time<1ms TTL=128
Reply from 169.254.101.152: bytes=32 time<1ms TTL=128
Reply from 169.254.101.152: bytes=32 time<1ms TTL=128
Reply from 169.254.101.152: bytes=32 time<1ms TTL=128

Ping statistics for 169.254.101.152:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 0ms, Maximum = 0ms, Average = 0ms

Using Server Name: 169.254.101.152 in the Managment Studio

'cannot connect to database'

Any other ideas ?

|||

(a) check whether the service started or not from Configuration Manager Window

(b) Check whether the protocols are enabled or not from SQL Configuration Manager window

(c) Check which all are the client protocol enabled

Madhu

|||

In the other thread I had suggest trying a connection via SQLCmd, just to rule out any Management Studio weirdness, are you able to do that? To reiterate, open a command prompt and type the following:

SQLCmd -S .\SQLEXPRESS -E -Q "SELECT @.@.Version"

This should return version information about your server if it works. If it fails, please post back the complete error message. As a second step should you encounter failure, please check the error log after the attempted connection to see if there is additional informaton there. The error log should be at C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG.

It's interesting that you say this worked initially but connectons started failing later. This would indeed suggest that "something else" is impacting this, but I've no idea what. In general I wouldn't expect any AV program to prevent connections to SQL, but we should probably test this. As a temprorary troubleshooting step, disable McAffe and then try your connection again. Regardless of the result, turn McAffe back on once you've completed the test, but let us know if that impacts anything.

Finally, could you copy the test from the "Server" text box in the Management Studio Connection dialog and paste it into your reply?

Mike

|||

Thanks for your response, Mike.

I apologize for taking so long to respond. In any case, when I attempted at the Command Prompt:

SQLCmd -S .\SQLEXPRESS -E -Q "SELECT @.@.Version"

I received the following message (preceeded by a bunch of dashed lines):

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

(1 rows affected)

The Error Log was quite extensive and, since I have been fooling with SQL Server quite a bit, I am uncertain which section pertains. But I interpret the above to indicate that the installation is working.

Here's a little bit of the most recent part of the Error Log:

2007-03-18 09:00:44.95 Server Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

2007-03-18 09:00:44.95 Server (c) 2005 Microsoft Corporation.
2007-03-18 09:00:44.95 Server All rights reserved.
2007-03-18 09:00:44.96 Server Server process ID is 552.
2007-03-18 09:00:44.96 Server Logging SQL Server messages in file 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
2007-03-18 09:00:44.96 Server This instance of SQL Server last reported using a process ID of 236 at 3/17/2007 10:54:30 PM (local) 3/18/2007 5:54:30 AM (UTC). This is an informational message only; no user action is required.
2007-03-18 09:00:44.98 Server Registry startup parameters:
2007-03-18 09:00:44.98 Server -d c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
2007-03-18 09:00:44.98 Server -e c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
2007-03-18 09:00:44.98 Server -l c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
2007-03-18 09:00:45.06 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2007-03-18 09:00:45.06 Server Detected 1 CPUs. This is an informational message; no user action is required.
2007-03-18 09:00:45.67 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
2007-03-18 09:00:45.70 Server Database Mirroring Transport is disabled in the endpoint configuration.
2007-03-18 09:00:45.71 spid5s Starting up database 'master'.
2007-03-18 09:00:46.04 spid5s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
// .....

The Server Text Box from the Management Studio (if I understand your request) looks as follows:

Connect to Server

Server type: Database Engine (disabled)
Server name: MPLIAM\SQLEXPRESS
Authentication: Windows Atuhentication
User name: MBPLIAM\Michael B Pliam (disabled)
Password: (blank and disabled)

Attempting to connect results in:

Connect to Server
Cannot connect to MPLIAM\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)

I will try to disable McAffee Virus Protection and get back to you if it makes any difference (Assuming that I can figure out how to disable it - it's sort of like getting rid of a bad cold.)

Thanks again for your help. Again, I wish to apologize for having lost this thread.

|||

You are correct that the output of SQLCmd indicates that SQL Express is working fine and can be accessed locally with no problems. This would suggest a specific problem with Management Studio. I don't see any problem with the information you supplied, but the error message indicates that the specific instance name you've supplied is incorrect.

Replace the value in the 'Server name' text box with .\SQLEXPRESS and see if that makes any difference. If this works, it would mean that your machine name is not actually MPLIAM. If it doesn't work, we may to punt this over to the tools guys to see if they have any ideas.

Mike

|||

Problem solved !

Using .\SQLEXPRESS instead of MPLIAM\SQLEXPRESS allowed the Management Studio to connect to the server.

I still do not understand what the problem was.

My 'host' machine is definitely 'MPLIAM'.

If I 'ping MPLIAM' from a Command Line prompt (as I believe I posted before), I receive the response:

Pinging MPLIAM [169.254.101.152] with 32 bytes of data:

Reply from 169.254.101.152: bytes=32 time<1ms TTL=128
Reply from 169.254.101.152: bytes=32 time<1ms TTL=128
Reply from 169.254.101.152: bytes=32 time<1ms TTL=128
Reply from 169.254.101.152: bytes=32 time<1ms TTL=128

Ping statistics for 169.254.101.152:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 0ms, Maximum = 0ms, Average = 0ms

Further, MSDN provides C++ source code to determine the host machine name and volume. For those interested, see:

http://windowssdk.msdn.microsoft.com/en-us/library/ms738527.aspx

Running that program reveals

MPLIAM
3758647270

the host name and volume number respectively.

When Management Studio did connect (as noted above), the connection text box header has the following information:

Summary

.\SQLEXPRESS (SQL Server 9.0.1399 - MPLIAM\Michael B Pliam)
MPLIAM\SQLEXPRESS 5 Items(s)

Mike, I want to thank you for your patience in helping me to solve this problem. If my tone at times appeared intemperant, it was simply my frustration at being unable to get this program to work. As is usually the case, it was probably my fault that things were awry. I must have changed something without being aware of it. Once things go bad, I have a tendency to start changing everything in sight instead of cautiously exploring the nature of the problem.

Regards

|||

I'm glad that it's working. It's odd that the machine name is not being recognized, but if you're happy, I'm happy.

Mike

|||Hi,

I've been struggling with a similar problem. Wasn't able to connect remotely and everything was setup correctly on the Surface Area Configuration. I could connect locally though, but not remotely. Used the exact same Windows User each time, and no firewall in between client and server. The eventual resolution was something really simple and stupid on my part.

The SQL Server browser service wasn't running, all that I did was start it and suddenly I could connect remotely... which is strange because I've never had this issue with any other SQL server I've worked with...

anyway hope it helps...