Can't drop column

I have a couple of questions relating to a merge
replication database.
1.I was unable to add a column to a table being
replicated using EM, but succeeded using
sp_repladdcolumn. When I tried to drop another column
using sp_repldropcolumn, I get the following
message: "ALTER TABLE DROP COLUMN failed
because 'FieldName' is currently replicated.
2.If I am unable to use EM to drop/add columns,
does this mean that my db is corrupt? How can I check if
my db is corrupt and which tools can I use?
3.The transaction log for a replicated db keeps
growing. The database is about 500 MB and the transaction
log is almost 4GB. I performed a complete backup and even
tried to shrink the log manually but the size did not
change.
I will appreciate any help I can get in resolving these
problems.
Thanks
Emma
is this column a pk, or part of a pk? are there and contraints on this
column?
It is unlikely your database is corrupt. Database base corrpuption errors
normally show up when you query a page telling you a page of the table or
index is inaccessible, your database is inaccessible. To check this run dbcc
checkdb
Regarding your ever expanding database, run dbcc open tran and see if there
are any old open transactions. If so figure out what they are doing and
evaluate killing them. The consider switching to the simple recovery model
and trying to shrink the database again several times. This will cause
locking so it is best to do this off hours. After you do this run a backup,
and then switch back to the full model.
"Emma" <eeemore@.hotmail.com> wrote in message
news:175b001c418c1$10cd47e0$a501280a@.phx.gbl...
> I have a couple of questions relating to a merge
> replication database.
> 1. I was unable to add a column to a table being
> replicated using EM, but succeeded using
> sp_repladdcolumn. When I tried to drop another column
> using sp_repldropcolumn, I get the following
> message: "ALTER TABLE DROP COLUMN failed
> because 'FieldName' is currently replicated.
> 2. If I am unable to use EM to drop/add columns,
> does this mean that my db is corrupt? How can I check if
> my db is corrupt and which tools can I use?
> 3. The transaction log for a replicated db keeps
> growing. The database is about 500 MB and the transaction
> log is almost 4GB. I performed a complete backup and even
> tried to shrink the log manually but the size did not
> change.
> I will appreciate any help I can get in resolving these
> problems.
> Thanks
> Emma
>
|||Hilary,
Thanks for your response. dbcc checkdb returned no error.
dbbc opentran returned the following and I don't know
what to do with it.
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (305:22434:1)
Thanks
Emma

>--Original Message--
>is this column a pk, or part of a pk? are there and
contraints on this
>column?
>It is unlikely your database is corrupt. Database base
corrpuption errors
>normally show up when you query a page telling you a
page of the table or
>index is inaccessible, your database is inaccessible. To
check this run dbcc
>checkdb
>Regarding your ever expanding database, run dbcc open
tran and see if there
>are any old open transactions. If so figure out what
they are doing and
>evaluate killing them. The consider switching to the
simple recovery model
>and trying to shrink the database again several times.
This will cause
>locking so it is best to do this off hours. After you do
this run a backup,
>and then switch back to the full model.
>"Emma" <eeemore@.hotmail.com> wrote in message
>news:175b001c418c1$10cd47e0$a501280a@.phx.gbl...
if
transaction
even
>
>.
>
|||Hilary,
In response to your first question, the column that I
can't drop is not a pk or part of a pk and there are no
constraints. There is a relationship between this table
and another table on another column.
Thanks
Emma

>--Original Message--
>is this column a pk, or part of a pk? are there and
contraints on this
>column?
>It is unlikely your database is corrupt. Database base
corrpuption errors
>normally show up when you query a page telling you a
page of the table or
>index is inaccessible, your database is inaccessible. To
check this run dbcc
>checkdb
>Regarding your ever expanding database, run dbcc open
tran and see if there
>are any old open transactions. If so figure out what
they are doing and
>evaluate killing them. The consider switching to the
simple recovery model
>and trying to shrink the database again several times.
This will cause
>locking so it is best to do this off hours. After you do
this run a backup,
>and then switch back to the full model.
>"Emma" <eeemore@.hotmail.com> wrote in message
>news:175b001c418c1$10cd47e0$a501280a@.phx.gbl...
if
transaction
even
>
>.
>

Cant delete a database

I've got a DB that had a problem restoring, and now it's status is showing (Loading). I'm unable to detach it, delete it, or do anything with it. If I delete it from Enterprise Manager then do a refresh, it shows back up again.

Is there a way to positively, absolutely nuke this database?I've had a similar issue on a cluster instance once, however I was able to drop the database. As you might know the information about the databases available on the instance is present in the master database, so if all else fails (and it appears so) you might need to restore the master database, and then try to delete the problem database.

May I suggest doing this procedure at a time when there are little or no activity on the server, and of course that you back up the master database on right before you do the restore.|||when a restore truly hangs in the mangler, I script a restore explicitly specifying WITH RECOVERY and that usually completes the restore. then I would drop the db.|||True, but that would work if the database is presented as (restoring) right? Or will it work when it says (loading) as well?

Can't debug proc after Sp2 on WinXP

Hi,
After installation of SP2 on my WinXP Prof I can't run debug in SQL Query
Analyzer.
Unable to connect to debugger on "SERVER" (Error = 0x800706ba). Ensure that
client-side components, such as SQLDBREG.EXE, are installed and registered on
"MYXP". Debugging disabled for connection nn.
Server is Win 2003 and SQL 2000 with Sp3
Before Sp2 everything was OK.
Thanks for the help.
ML
Make sure you have followed all the steps in the help topic
in books online: Troubleshooting the Transact-SQL Debugger
You should also check the following knowledge base article:
INF: T-SQL Debugger Is Turned Off By Default for Earlier
Clients After You Install SQL Server 2000 Service Pack 3
http://support.microsoft.com/?id=328151
-Sue
On Wed, 27 Oct 2004 05:13:03 -0700, MarekL
<MarekL@.discussions.microsoft.com> wrote:

>Hi,
>After installation of SP2 on my WinXP Prof I can't run debug in SQL Query
>Analyzer.
>Unable to connect to debugger on "SERVER" (Error = 0x800706ba). Ensure that
>client-side components, such as SQLDBREG.EXE, are installed and registered on
>"MYXP". Debugging disabled for connection nn.
>Server is Win 2003 and SQL 2000 with Sp3
>Before Sp2 everything was OK.
>Thanks for the help.
>ML

Can't connect to SQL Server through a box in the DMZ

Hello,
I am running an NT server in the DMZ and need to connect to SQL Server 2000
on another box. I am unable to try to ping the SQL box because ping is
disabled within the DMZ. Everytime I try to connect I get a Timed-out
error. Does anyone know how would I be able to connect to SQL Server?
Thanks in advance,
BelindaAssume the SQL server is in DMZ domain. You'll need trust relationship set
up betweenDMZ and your domain.
"Belinda Rodriguez" <rodrigub@.symbol.com> wrote in message
news:Oxe8ZhqVDHA.1780@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I am running an NT server in the DMZ and need to connect to SQL Server
2000
> on another box. I am unable to try to ping the SQL box because ping is
> disabled within the DMZ. Everytime I try to connect I get a Timed-out
> error. Does anyone know how would I be able to connect to SQL Server?
> Thanks in advance,
> Belinda
>|||Thank you for the response, but I was able to figure out the problem. The
login time-out was at 4 sec and I increased it to 20. I also added the ip
and server name to the host file, which seemed to do the trick.
Thanks again,
Belinda
"Richard Ding" <dingr@.cleanharbors.com> wrote in message
news:%236D0RVrVDHA.2052@.TK2MSFTNGP10.phx.gbl...
> Assume the SQL server is in DMZ domain. You'll need trust relationship set
> up betweenDMZ and your domain.
> "Belinda Rodriguez" <rodrigub@.symbol.com> wrote in message
> news:Oxe8ZhqVDHA.1780@.TK2MSFTNGP11.phx.gbl...
> > Hello,
> >
> > I am running an NT server in the DMZ and need to connect to SQL Server
> 2000
> > on another box. I am unable to try to ping the SQL box because ping is
> > disabled within the DMZ. Everytime I try to connect I get a Timed-out
> > error. Does anyone know how would I be able to connect to SQL Server?
> >
> > Thanks in advance,
> >
> > Belinda
> >
> >
>

Can't connect to Database Engine from Management Studio

I have found several posts on the internet on similar issues, but none
with this specific problem.
I am unable to connect to a Database Engine from SQL Server 2005
Management Studio when connecting from a WAN connection, but I can
connect remotely when connected to the same LAN as the server that
hosts this SQL Server database. Also, I do not have any problems
connecting to the Analysis Services on the same database [and same
server] from the WAN connection.
On the desktop computer there is no Microsoft firewall and on the
server the Microsoft firewall is also disabled.
I have changed the options on the Surface Area Connection to only
remote with TCP/IP and remote with TCP/IP and Named Pipes - this did
not make a difference.
The SQL Server Browser is running.
Obviously remote connections work, because I can connect to the
database engine when I am remote on the same LAN. Also I can connect
to Analysis Services when I am remote on the WAN.
The specific error message is:
Cannot connect to <Server>\<Instance Name>
Additional information:
An error has occured while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the
fact that under default settings SQL Server does not allow remote
connections. (provider: SQL Network Interfaces, error: 26 - Error
Locating Server/Instance Specified) ( Microsoft SQL Server)
Blake wrote:
> I have found several posts on the internet on similar issues, but none
> with this specific problem.
> I am unable to connect to a Database Engine from SQL Server 2005
> Management Studio when connecting from a WAN connection, but I can
> connect remotely when connected to the same LAN as the server that
> hosts this SQL Server database. Also, I do not have any problems
> connecting to the Analysis Services on the same database [and same
> server] from the WAN connection.
> On the desktop computer there is no Microsoft firewall and on the
> server the Microsoft firewall is also disabled.
> I have changed the options on the Surface Area Connection to only
> remote with TCP/IP and remote with TCP/IP and Named Pipes - this did
> not make a difference.
> The SQL Server Browser is running.
> Obviously remote connections work, because I can connect to the
> database engine when I am remote on the same LAN. Also I can connect
> to Analysis Services when I am remote on the WAN.
> The specific error message is:
> Cannot connect to <Server>\<Instance Name>
> Additional information:
> An error has occured while establishing a connection to the server.
> When connecting to SQL Server 2005, this failure may be caused by the
> fact that under default settings SQL Server does not allow remote
> connections. (provider: SQL Network Interfaces, error: 26 - Error
> Locating Server/Instance Specified) ( Microsoft SQL Server)
>
When connecting over the WAN, are you able to PING the server? Have you
tried connecting by specifying the IP address instead of the hostname?
This sounds like a network issue, possible name resolution.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Tracy McKibben wrote:
> Blake wrote:
> When connecting over the WAN, are you able to PING the server? Have you
> tried connecting by specifying the IP address instead of the hostname?
> This sounds like a network issue, possible name resolution.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
Thanks for your comments. Yes I can ping the server, I can connect
with a remote control software package to the same server and can use
Management Studio to connect to SQL Server Analysis Services. I don't
think this is a network [or firewall] issue because I can connect to
the server from every other method, unless there is something specific
network method that is used for Database Engine connections that are
blocked. I have presumed that when I set the connection to be TCP/IP
only it used a TCP connection and when the connection is set to TCP/IP
and Named Pipes it used Named Pipes. I see in the Registered Server
window you can specify the type of connection. This is very
confusing...
|||MS SQL-Server talks on ports when using TCP/IP. So, you need to make sure the
port itself is open. I beleive it uses 1433/tcp for accessing the server
(might be 1434, I can't remember).
Can you telnet to that port? Does Management Studio use a different port?
"Blake" wrote:

> Tracy McKibben wrote:
> Thanks for your comments. Yes I can ping the server, I can connect
> with a remote control software package to the same server and can use
> Management Studio to connect to SQL Server Analysis Services. I don't
> think this is a network [or firewall] issue because I can connect to
> the server from every other method, unless there is something specific
> network method that is used for Database Engine connections that are
> blocked. I have presumed that when I set the connection to be TCP/IP
> only it used a TCP connection and when the connection is set to TCP/IP
> and Named Pipes it used Named Pipes. I see in the Registered Server
> window you can specify the type of connection. This is very
> confusing...
>
|||On Jan 4, 2:48 pm, JayKon <Jay...@.discussions.microsoft.com> wrote:[vbcol=seagreen]
> MS SQL-Server talks on ports when using TCP/IP. So, you need to make sure the
> port itself is open. I beleive it uses 1433/tcp for accessing the server
> (might be 1434, I can't remember).
> Can you telnet to that port? Does Management Studio use a different port?
> "Blake" wrote:
>
>
>
Based on a comment from my customer on this database, I created a SQL
Server installation without using a named instance. Using the default
instance name - I am able to connect via Management Studio from a WAN
connection. I have since deleted the original NAMED instance,
recreated on the same computer as a DEFAULT instance and connecting
with Management Studio is a breeze.
Does anyone know how to report this to Microsoft, this is a but that
should be fixed?
|||Blake wrote:
> Based on a comment from my customer on this database, I created a SQL
> Server installation without using a named instance. Using the default
> instance name - I am able to connect via Management Studio from a WAN
> connection. I have since deleted the original NAMED instance,
> recreated on the same computer as a DEFAULT instance and connecting
> with Management Studio is a breeze.
> Does anyone know how to report this to Microsoft, this is a but that
> should be fixed?
>
If I understand this correctly, you had the following:
Machine named SERVER
SQL instance named SERVER\MYINSTANCE
When you were attempting to connect, what were you specifying as a
server name? To connect to SERVER\MYINSTANCE, you wouldn't specify
SERVER\MYINSTANCE as the server name, unless you have defined that as an
alias in the client configuration tool. You would specify a server name
of "SERVER,1433", replacing 1433 with the port number that the instance
is listening on. The name "SERVER\MYINSTANCE" isn't a valid hostname,
and won't be resolvable by your client tools.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||I had same problem...at leat I think so..
Have you tryed to add server name and IP to your host file?
Because that resolved my problem.
Information about host file
http://accs-net.com/hosts/how_to_use_hosts.html
Juha Sinkkonen
Agenteq Consulting Oy
sinkkonen22(at)hotmail.com
"Blake" wrote:

>
> On Jan 4, 2:48 pm, JayKon <Jay...@.discussions.microsoft.com> wrote:
> Based on a comment from my customer on this database, I created a SQL
> Server installation without using a named instance. Using the default
> instance name - I am able to connect via Management Studio from a WAN
> connection. I have since deleted the original NAMED instance,
> recreated on the same computer as a DEFAULT instance and connecting
> with Management Studio is a breeze.
> Does anyone know how to report this to Microsoft, this is a but that
> should be fixed?
>

Can't connect to Database Engine from Management Studio

I have found several posts on the internet on similar issues, but none
with this specific problem.
I am unable to connect to a Database Engine from SQL Server 2005
Management Studio when connecting from a WAN connection, but I can
connect remotely when connected to the same LAN as the server that
hosts this SQL Server database. Also, I do not have any problems
connecting to the Analysis Services on the same database [and same
server] from the WAN connection.
On the desktop computer there is no Microsoft firewall and on the
server the Microsoft firewall is also disabled.
I have changed the options on the Surface Area Connection to only
remote with TCP/IP and remote with TCP/IP and Named Pipes - this did
not make a difference.
The SQL Server Browser is running.
Obviously remote connections work, because I can connect to the
database engine when I am remote on the same LAN. Also I can connect
to Analysis Services when I am remote on the WAN.
The specific error message is:
Cannot connect to <Server>\<Instance Name>
Additional information:
An error has occured while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the
fact that under default settings SQL Server does not allow remote
connections. (provider: SQL Network Interfaces, error: 26 - Error
Locating Server/Instance Specified) ( Microsoft SQL Server)Blake wrote:
> I have found several posts on the internet on similar issues, but none
> with this specific problem.
> I am unable to connect to a Database Engine from SQL Server 2005
> Management Studio when connecting from a WAN connection, but I can
> connect remotely when connected to the same LAN as the server that
> hosts this SQL Server database. Also, I do not have any problems
> connecting to the Analysis Services on the same database [and same
> server] from the WAN connection.
> On the desktop computer there is no Microsoft firewall and on the
> server the Microsoft firewall is also disabled.
> I have changed the options on the Surface Area Connection to only
> remote with TCP/IP and remote with TCP/IP and Named Pipes - this did
> not make a difference.
> The SQL Server Browser is running.
> Obviously remote connections work, because I can connect to the
> database engine when I am remote on the same LAN. Also I can connect
> to Analysis Services when I am remote on the WAN.
> The specific error message is:
> Cannot connect to <Server>\<Instance Name>
> Additional information:
> An error has occured while establishing a connection to the server.
> When connecting to SQL Server 2005, this failure may be caused by the
> fact that under default settings SQL Server does not allow remote
> connections. (provider: SQL Network Interfaces, error: 26 - Error
> Locating Server/Instance Specified) ( Microsoft SQL Server)
>
When connecting over the WAN, are you able to PING the server? Have you
tried connecting by specifying the IP address instead of the hostname?
This sounds like a network issue, possible name resolution.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy McKibben wrote:
> Blake wrote:
> When connecting over the WAN, are you able to PING the server? Have you
> tried connecting by specifying the IP address instead of the hostname?
> This sounds like a network issue, possible name resolution.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
Thanks for your comments. Yes I can ping the server, I can connect
with a remote control software package to the same server and can use
Management Studio to connect to SQL Server Analysis Services. I don't
think this is a network [or firewall] issue because I can connect to
the server from every other method, unless there is something specific
network method that is used for Database Engine connections that are
blocked. I have presumed that when I set the connection to be TCP/IP
only it used a TCP connection and when the connection is set to TCP/IP
and Named Pipes it used Named Pipes. I see in the Registered Server
window you can specify the type of connection. This is very
confusing...|||MS SQL-Server talks on ports when using TCP/IP. So, you need to make sure th
e
port itself is open. I beleive it uses 1433/tcp for accessing the server
(might be 1434, I can't remember).
Can you telnet to that port? Does Management Studio use a different port?
"Blake" wrote:

> Tracy McKibben wrote:
> Thanks for your comments. Yes I can ping the server, I can connect
> with a remote control software package to the same server and can use
> Management Studio to connect to SQL Server Analysis Services. I don't
> think this is a network [or firewall] issue because I can connect to
> the server from every other method, unless there is something specific
> network method that is used for Database Engine connections that are
> blocked. I have presumed that when I set the connection to be TCP/IP
> only it used a TCP connection and when the connection is set to TCP/IP
> and Named Pipes it used Named Pipes. I see in the Registered Server
> window you can specify the type of connection. This is very
> confusing...
>|||On Jan 4, 2:48 pm, JayKon <Jay...@.discussions.microsoft.com> wrote:[vbcol=seagreen]
> MS SQL-Server talks on ports when using TCP/IP. So, you need to make sure
the
> port itself is open. I beleive it uses 1433/tcp for accessing the server
> (might be 1434, I can't remember).
> Can you telnet to that port? Does Management Studio use a different port?
> "Blake" wrote:
>
>
>
>
>
>
>
Based on a comment from my customer on this database, I created a SQL
Server installation without using a named instance. Using the default
instance name - I am able to connect via Management Studio from a WAN
connection. I have since deleted the original NAMED instance,
recreated on the same computer as a DEFAULT instance and connecting
with Management Studio is a breeze.
Does anyone know how to report this to Microsoft, this is a but that
should be fixed?|||Blake wrote:
> Based on a comment from my customer on this database, I created a SQL
> Server installation without using a named instance. Using the default
> instance name - I am able to connect via Management Studio from a WAN
> connection. I have since deleted the original NAMED instance,
> recreated on the same computer as a DEFAULT instance and connecting
> with Management Studio is a breeze.
> Does anyone know how to report this to Microsoft, this is a but that
> should be fixed?
>
If I understand this correctly, you had the following:
Machine named SERVER
SQL instance named SERVER\MYINSTANCE
When you were attempting to connect, what were you specifying as a
server name? To connect to SERVER\MYINSTANCE, you wouldn't specify
SERVER\MYINSTANCE as the server name, unless you have defined that as an
alias in the client configuration tool. You would specify a server name
of "SERVER,1433", replacing 1433 with the port number that the instance
is listening on. The name "SERVER\MYINSTANCE" isn't a valid hostname,
and won't be resolvable by your client tools.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I had same problem...at leat I think so..
Have you tryed to add server name and IP to your host file?
Because that resolved my problem.
Information about host file
http://accs-net.com/hosts/how_to_use_hosts.html
Juha Sinkkonen
Agenteq Consulting Oy
sinkkonen22(at)hotmail.com
"Blake" wrote:

>
> On Jan 4, 2:48 pm, JayKon <Jay...@.discussions.microsoft.com> wrote:
> Based on a comment from my customer on this database, I created a SQL
> Server installation without using a named instance. Using the default
> instance name - I am able to connect via Management Studio from a WAN
> connection. I have since deleted the original NAMED instance,
> recreated on the same computer as a DEFAULT instance and connecting
> with Management Studio is a breeze.
> Does anyone know how to report this to Microsoft, this is a but that
> should be fixed?
>

Can't connect to Database Engine from Management Studio

I have found several posts on the internet on similar issues, but none
with this specific problem.
I am unable to connect to a Database Engine from SQL Server 2005
Management Studio when connecting from a WAN connection, but I can
connect remotely when connected to the same LAN as the server that
hosts this SQL Server database. Also, I do not have any problems
connecting to the Analysis Services on the same database [and same
server] from the WAN connection.
On the desktop computer there is no Microsoft firewall and on the
server the Microsoft firewall is also disabled.
I have changed the options on the Surface Area Connection to only
remote with TCP/IP and remote with TCP/IP and Named Pipes - this did
not make a difference.
The SQL Server Browser is running.
Obviously remote connections work, because I can connect to the
database engine when I am remote on the same LAN. Also I can connect
to Analysis Services when I am remote on the WAN.
The specific error message is:
Cannot connect to <Server>\<Instance Name>
Additional information:
An error has occured while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the
fact that under default settings SQL Server does not allow remote
connections. (provider: SQL Network Interfaces, error: 26 - Error
Locating Server/Instance Specified) ( Microsoft SQL Server)Blake wrote:
> I have found several posts on the internet on similar issues, but none
> with this specific problem.
> I am unable to connect to a Database Engine from SQL Server 2005
> Management Studio when connecting from a WAN connection, but I can
> connect remotely when connected to the same LAN as the server that
> hosts this SQL Server database. Also, I do not have any problems
> connecting to the Analysis Services on the same database [and same
> server] from the WAN connection.
> On the desktop computer there is no Microsoft firewall and on the
> server the Microsoft firewall is also disabled.
> I have changed the options on the Surface Area Connection to only
> remote with TCP/IP and remote with TCP/IP and Named Pipes - this did
> not make a difference.
> The SQL Server Browser is running.
> Obviously remote connections work, because I can connect to the
> database engine when I am remote on the same LAN. Also I can connect
> to Analysis Services when I am remote on the WAN.
> The specific error message is:
> Cannot connect to <Server>\<Instance Name>
> Additional information:
> An error has occured while establishing a connection to the server.
> When connecting to SQL Server 2005, this failure may be caused by the
> fact that under default settings SQL Server does not allow remote
> connections. (provider: SQL Network Interfaces, error: 26 - Error
> Locating Server/Instance Specified) ( Microsoft SQL Server)
>
When connecting over the WAN, are you able to PING the server? Have you
tried connecting by specifying the IP address instead of the hostname?
This sounds like a network issue, possible name resolution.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy McKibben wrote:
> Blake wrote:
> > I have found several posts on the internet on similar issues, but none
> > with this specific problem.
> >
> > I am unable to connect to a Database Engine from SQL Server 2005
> > Management Studio when connecting from a WAN connection, but I can
> > connect remotely when connected to the same LAN as the server that
> > hosts this SQL Server database. Also, I do not have any problems
> > connecting to the Analysis Services on the same database [and same
> > server] from the WAN connection.
> > On the desktop computer there is no Microsoft firewall and on the
> > server the Microsoft firewall is also disabled.
> > I have changed the options on the Surface Area Connection to only
> > remote with TCP/IP and remote with TCP/IP and Named Pipes - this did
> > not make a difference.
> > The SQL Server Browser is running.
> >
> > Obviously remote connections work, because I can connect to the
> > database engine when I am remote on the same LAN. Also I can connect
> > to Analysis Services when I am remote on the WAN.
> >
> > The specific error message is:
> > Cannot connect to <Server>\<Instance Name>
> > Additional information:
> > An error has occured while establishing a connection to the server.
> > When connecting to SQL Server 2005, this failure may be caused by the
> > fact that under default settings SQL Server does not allow remote
> > connections. (provider: SQL Network Interfaces, error: 26 - Error
> > Locating Server/Instance Specified) ( Microsoft SQL Server)
> >
> When connecting over the WAN, are you able to PING the server? Have you
> tried connecting by specifying the IP address instead of the hostname?
> This sounds like a network issue, possible name resolution.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
Thanks for your comments. Yes I can ping the server, I can connect
with a remote control software package to the same server and can use
Management Studio to connect to SQL Server Analysis Services. I don't
think this is a network [or firewall] issue because I can connect to
the server from every other method, unless there is something specific
network method that is used for Database Engine connections that are
blocked. I have presumed that when I set the connection to be TCP/IP
only it used a TCP connection and when the connection is set to TCP/IP
and Named Pipes it used Named Pipes. I see in the Registered Server
window you can specify the type of connection. This is very
confusing...|||On Jan 4, 2:48 pm, JayKon <Jay...@.discussions.microsoft.com> wrote:
> MS SQL-Server talks on ports when using TCP/IP. So, you need to make sure the
> port itself is open. I beleive it uses 1433/tcp for accessing the server
> (might be 1434, I can't remember).
> Can you telnet to that port? Does Management Studio use a different port?
> "Blake" wrote:
> > Tracy McKibben wrote:
> > > Blake wrote:
> > > > I have found several posts on the internet on similar issues, but none
> > > > with this specific problem.
> > > > I am unable to connect to a Database Engine from SQL Server 2005
> > > > Management Studio when connecting from a WAN connection, but I can
> > > > connect remotely when connected to the same LAN as the server that
> > > > hosts this SQL Server database. Also, I do not have any problems
> > > > connecting to the Analysis Services on the same database [and same
> > > > server] from the WAN connection.
> > > > On the desktop computer there is no Microsoft firewall and on the
> > > > server the Microsoft firewall is also disabled.
> > > > I have changed the options on the Surface Area Connection to only
> > > > remote with TCP/IP and remote with TCP/IP and Named Pipes - this did
> > > > not make a difference.
> > > > The SQL Server Browser is running.
> > > > Obviously remote connections work, because I can connect to the
> > > > database engine when I am remote on the same LAN. Also I can connect
> > > > to Analysis Services when I am remote on the WAN.
> > > > The specific error message is:
> > > > Cannot connect to <Server>\<Instance Name>
> > > > Additional information:
> > > > An error has occured while establishing a connection to the server.
> > > > When connecting to SQL Server 2005, this failure may be caused by the
> > > > fact that under default settings SQL Server does not allow remote
> > > > connections. (provider: SQL Network Interfaces, error: 26 - Error
> > > > Locating Server/Instance Specified) ( Microsoft SQL Server)
> > > When connecting over the WAN, are you able to PING the server? Have you
> > > tried connecting by specifying the IP address instead of the hostname?
> > > This sounds like a network issue, possible name resolution.
> > > --
> > > Tracy McKibben
> > > MCDBA
> > >http://www.realsqlguy.com
> > Thanks for your comments. Yes I can ping the server, I can connect
> > with a remote control software package to the same server and can use
> > Management Studio to connect to SQL Server Analysis Services. I don't
> > think this is a network [or firewall] issue because I can connect to
> > the server from every other method, unless there is something specific
> > network method that is used for Database Engine connections that are
> > blocked. I have presumed that when I set the connection to be TCP/IP
> > only it used a TCP connection and when the connection is set to TCP/IP
> > and Named Pipes it used Named Pipes. I see in the Registered Server
> > window you can specify the type of connection. This is very
> > confusing...
Based on a comment from my customer on this database, I created a SQL
Server installation without using a named instance. Using the default
instance name - I am able to connect via Management Studio from a WAN
connection. I have since deleted the original NAMED instance,
recreated on the same computer as a DEFAULT instance and connecting
with Management Studio is a breeze.
Does anyone know how to report this to Microsoft, this is a but that
should be fixed?|||Blake wrote:
> Based on a comment from my customer on this database, I created a SQL
> Server installation without using a named instance. Using the default
> instance name - I am able to connect via Management Studio from a WAN
> connection. I have since deleted the original NAMED instance,
> recreated on the same computer as a DEFAULT instance and connecting
> with Management Studio is a breeze.
> Does anyone know how to report this to Microsoft, this is a but that
> should be fixed?
>
If I understand this correctly, you had the following:
Machine named SERVER
SQL instance named SERVER\MYINSTANCE
When you were attempting to connect, what were you specifying as a
server name? To connect to SERVER\MYINSTANCE, you wouldn't specify
SERVER\MYINSTANCE as the server name, unless you have defined that as an
alias in the client configuration tool. You would specify a server name
of "SERVER,1433", replacing 1433 with the port number that the instance
is listening on. The name "SERVER\MYINSTANCE" isn't a valid hostname,
and won't be resolvable by your client tools.
--
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Can''t connect in Sql Express on local machine

I have SQL Express running on my laptop but are unable to connect to the default database paulm-laptop\sqlexpress

When I try to connect I get the error message

TITLE: Microsoft SQL Server Management Studio Express
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)

Everything is on my laptop whcih is running Vista Business as an administrator

Any ideas why,

Thanks Paul

Did you enable remote connections for your SQL Server Instance ? If not, have a look at my screencast for more information how to do this.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Since you are on Vista, you should make sure that you are running Managment Studio as Administrator (right click on the shortcut and on Compatibility, choose Run as Administrator) or you can just right click on the shortcut and choose Run as Administrator each time you run it. I have seen this error when running it without it.