Connecting to remote SQL Server

I have SQL Server 2000 (Developer) on my computer. So I have SQL
Server Enterprise Manager + Tools (such as Query Analyzer).
I am doing some work on a remote server, mostly ASP pages (not
ASP.NET). There is a SQL Server database on that server. I would like
to use Enterprise Manager or Query Analyzer to access that database.
My main development environment is Dreamweaver MX2004. In Dreamweaver,
I was able to setup the database (on the databases tab) using a
"Custom Connection String", listed below (some info obfuscated for
security):
"Provider=SQLOLEDB.1;Persist Security Info=False;Initial
Catalog=funeral;Data Source=xx.xx.xx.xxx;Locale
Identifier=1033;Connect Timeout=15;Use Procedure for Prepare=1;Auto
Translate=True;Packet Size=4096;User Id=XXX;PASSWORD=XXXXXXXX;"
I don't know if it is possible to use the other tools to manage this
database. I've tried the following:
1. In Enterprise Manager, I've tried just putting the ip address as
the server, and "Use SQL Server authentication" from the user ID and
Password above, but I get the following error:
"SQL Server registration failed because of the connection failure
below... Cannot open user default database. Login failed"
2. Created a udl file. In the Provider, selected the "Microsoft OLE DB
Provider for SQL Server" Entered teh IP address and username/password
on the Connection Tab. I get the same error as #1 above.
Any insight would be greatly appreciated.
"Bryce Fischer" <spamtrap@.berzerker-soft.com> wrote in message
news:3btpo01us397pf1gmfl4bt9c8k9dcrv05e@.4ax.com...
> I have SQL Server 2000 (Developer) on my computer. So I have SQL
> Server Enterprise Manager + Tools (such as Query Analyzer).
> I am doing some work on a remote server, mostly ASP pages (not
> ASP.NET). There is a SQL Server database on that server. I would like
> to use Enterprise Manager or Query Analyzer to access that database.
> My main development environment is Dreamweaver MX2004. In Dreamweaver,
> I was able to setup the database (on the databases tab) using a
> "Custom Connection String", listed below (some info obfuscated for
> security):
> "Provider=SQLOLEDB.1;Persist Security Info=False;Initial
> Catalog=funeral;Data Source=xx.xx.xx.xxx;Locale
> Identifier=1033;Connect Timeout=15;Use Procedure for Prepare=1;Auto
> Translate=True;Packet Size=4096;User Id=XXX;PASSWORD=XXXXXXXX;"
> I don't know if it is possible to use the other tools to manage this
> database. I've tried the following:
> 1. In Enterprise Manager, I've tried just putting the ip address as
> the server, and "Use SQL Server authentication" from the user ID and
> Password above, but I get the following error:
> "SQL Server registration failed because of the connection failure
> below... Cannot open user default database. Login failed"
>
User XXX has a login on the remote server, but does not have a user in the
database he is configured to use. To administer the remote database, User
XXX must be a member of the sysadmin role on the remote server
http://snodland.blogspot.com
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.788 / Virus Database: 533 - Release Date: 01/11/2004
|||On Sat, 6 Nov 2004 22:04:54 -0000, "Bob Simms"
<bob_simms@.somewhere.com> wrote:

>User XXX has a login on the remote server, but does not have a user in the
>database he is configured to use. To administer the remote database, User
>XXX must be a member of the sysadmin role on the remote server
Thanks for the reply.
Is it possible to use Query Analyzer or Access to connect to the
server given the information in my original post? I don't need
administrator-like functionality. As I mentioned, in Dreamweaver, I'm
able to create a connection, and view the tables and data (not to be
confused with creating the connection on the ASP pages, but in
Dreamweaver itself.) Dreamweaver lets you browse the tables, and data,
it doesn't let you run SQL Statements, which is why I'd like to use
other tools I have.
TIA.
|||On Sat, 06 Nov 2004 20:21:58 -0500, Bryce Fischer
<spamtrap@.berzerker-soft.com> wrote:

>On Sat, 6 Nov 2004 22:04:54 -0000, "Bob Simms"
><bob_simms@.somewhere.com> wrote:
>
>Thanks for the reply.
>Is it possible to use Query Analyzer or Access to connect to the
>server given the information in my original post? I don't need
>administrator-like functionality. As I mentioned, in Dreamweaver, I'm
>able to create a connection, and view the tables and data (not to be
>confused with creating the connection on the ASP pages, but in
>Dreamweaver itself.) Dreamweaver lets you browse the tables, and data,
>it doesn't let you run SQL Statements, which is why I'd like to use
>other tools I have.
Well... After some persistence, I figured it out. In Access, and
Visual Studio, I was able to bring up the Data Link Properties dialog,
and enter the information, and the one step I was not doing, was
entering in the catalog in the "Select the database on the server:"
field.
I was able to accomplish the same thing in Visual Studio, Server
Explorer, Data Connections.

Connecting to remote SQL Server

I have SQL Server 2000 (Developer) on my computer. So I have SQL
Server Enterprise Manager + Tools (such as Query Analyzer).
I am doing some work on a remote server, mostly ASP pages (not
ASP.NET). There is a SQL Server database on that server. I would like
to use Enterprise Manager or Query Analyzer to access that database.
My main development environment is Dreamweaver MX2004. In Dreamweaver,
I was able to setup the database (on the databases tab) using a
"Custom Connection String", listed below (some info obfuscated for
security):
"Provider=SQLOLEDB.1;Persist Security Info=False;Initial
Catalog=funeral;Data Source=xx.xx.xx.xxx;Locale
Identifier=1033;Connect Timeout=15;Use Procedure for Prepare=1;Auto
Translate=True;Packet Size=4096;User Id=XXX;PASSWORD=XXXXXXXX;"
I don't know if it is possible to use the other tools to manage this
database. I've tried the following:
1. In Enterprise Manager, I've tried just putting the ip address as
the server, and "Use SQL Server authentication" from the user ID and
Password above, but I get the following error:
"SQL Server registration failed because of the connection failure
below... Cannot open user default database. Login failed"
2. Created a udl file. In the Provider, selected the "Microsoft OLE DB
Provider for SQL Server" Entered teh IP address and username/password
on the Connection Tab. I get the same error as #1 above.
Any insight would be greatly appreciated."Bryce Fischer" <spamtrap@.berzerker-soft.com> wrote in message
news:3btpo01us397pf1gmfl4bt9c8k9dcrv05e@.
4ax.com...
> I have SQL Server 2000 (Developer) on my computer. So I have SQL
> Server Enterprise Manager + Tools (such as Query Analyzer).
> I am doing some work on a remote server, mostly ASP pages (not
> ASP.NET). There is a SQL Server database on that server. I would like
> to use Enterprise Manager or Query Analyzer to access that database.
> My main development environment is Dreamweaver MX2004. In Dreamweaver,
> I was able to setup the database (on the databases tab) using a
> "Custom Connection String", listed below (some info obfuscated for
> security):
> "Provider=SQLOLEDB.1;Persist Security Info=False;Initial
> Catalog=funeral;Data Source=xx.xx.xx.xxx;Locale
> Identifier=1033;Connect Timeout=15;Use Procedure for Prepare=1;Auto
> Translate=True;Packet Size=4096;User Id=XXX;PASSWORD=XXXXXXXX;"
> I don't know if it is possible to use the other tools to manage this
> database. I've tried the following:
> 1. In Enterprise Manager, I've tried just putting the ip address as
> the server, and "Use SQL Server authentication" from the user ID and
> Password above, but I get the following error:
> "SQL Server registration failed because of the connection failure
> below... Cannot open user default database. Login failed"
>
User XXX has a login on the remote server, but does not have a user in the
database he is configured to use. To administer the remote database, User
XXX must be a member of the sysadmin role on the remote server
http://snodland.blogspot.com
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.788 / Virus Database: 533 - Release Date: 01/11/2004|||On Sat, 6 Nov 2004 22:04:54 -0000, "Bob Simms"
<bob_simms@.somewhere.com> wrote:

>User XXX has a login on the remote server, but does not have a user in the
>database he is configured to use. To administer the remote database, User
>XXX must be a member of the sysadmin role on the remote server
Thanks for the reply.
Is it possible to use Query Analyzer or Access to connect to the
server given the information in my original post? I don't need
administrator-like functionality. As I mentioned, in Dreamweaver, I'm
able to create a connection, and view the tables and data (not to be
confused with creating the connection on the ASP pages, but in
Dreamweaver itself.) Dreamweaver lets you browse the tables, and data,
it doesn't let you run SQL Statements, which is why I'd like to use
other tools I have.
TIA.|||On Sat, 06 Nov 2004 20:21:58 -0500, Bryce Fischer
<spamtrap@.berzerker-soft.com> wrote:

>On Sat, 6 Nov 2004 22:04:54 -0000, "Bob Simms"
><bob_simms@.somewhere.com> wrote:
>
>Thanks for the reply.
>Is it possible to use Query Analyzer or Access to connect to the
>server given the information in my original post? I don't need
>administrator-like functionality. As I mentioned, in Dreamweaver, I'm
>able to create a connection, and view the tables and data (not to be
>confused with creating the connection on the ASP pages, but in
>Dreamweaver itself.) Dreamweaver lets you browse the tables, and data,
>it doesn't let you run SQL Statements, which is why I'd like to use
>other tools I have.
Well... After some persistence, I figured it out. In Access, and
Visual Studio, I was able to bring up the Data Link Properties dialog,
and enter the information, and the one step I was not doing, was
entering in the catalog in the "Select the database on the server:"
field.
I was able to accomplish the same thing in Visual Studio, Server
Explorer, Data Connections.

Connecting to remote SQL Server

I have SQL Server 2000 (Developer) on my computer. So I have SQL
Server Enterprise Manager + Tools (such as Query Analyzer).
I am doing some work on a remote server, mostly ASP pages (not
ASP.NET). There is a SQL Server database on that server. I would like
to use Enterprise Manager or Query Analyzer to access that database.
My main development environment is Dreamweaver MX2004. In Dreamweaver,
I was able to setup the database (on the databases tab) using a
"Custom Connection String", listed below (some info obfuscated for
security):
"Provider=SQLOLEDB.1;Persist Security Info=False;Initial
Catalog=funeral;Data Source=xx.xx.xx.xxx;Locale
Identifier=1033;Connect Timeout=15;Use Procedure for Prepare=1;Auto
Translate=True;Packet Size=4096;User Id=XXX;PASSWORD=XXXXXXXX;"
I don't know if it is possible to use the other tools to manage this
database. I've tried the following:
1. In Enterprise Manager, I've tried just putting the ip address as
the server, and "Use SQL Server authentication" from the user ID and
Password above, but I get the following error:
"SQL Server registration failed because of the connection failure
below... Cannot open user default database. Login failed"
2. Created a udl file. In the Provider, selected the "Microsoft OLE DB
Provider for SQL Server" Entered teh IP address and username/password
on the Connection Tab. I get the same error as #1 above.
Any insight would be greatly appreciated."Bryce Fischer" <spamtrap@.berzerker-soft.com> wrote in message
news:3btpo01us397pf1gmfl4bt9c8k9dcrv05e@.4ax.com...
> I have SQL Server 2000 (Developer) on my computer. So I have SQL
> Server Enterprise Manager + Tools (such as Query Analyzer).
> I am doing some work on a remote server, mostly ASP pages (not
> ASP.NET). There is a SQL Server database on that server. I would like
> to use Enterprise Manager or Query Analyzer to access that database.
> My main development environment is Dreamweaver MX2004. In Dreamweaver,
> I was able to setup the database (on the databases tab) using a
> "Custom Connection String", listed below (some info obfuscated for
> security):
> "Provider=SQLOLEDB.1;Persist Security Info=False;Initial
> Catalog=funeral;Data Source=xx.xx.xx.xxx;Locale
> Identifier=1033;Connect Timeout=15;Use Procedure for Prepare=1;Auto
> Translate=True;Packet Size=4096;User Id=XXX;PASSWORD=XXXXXXXX;"
> I don't know if it is possible to use the other tools to manage this
> database. I've tried the following:
> 1. In Enterprise Manager, I've tried just putting the ip address as
> the server, and "Use SQL Server authentication" from the user ID and
> Password above, but I get the following error:
> "SQL Server registration failed because of the connection failure
> below... Cannot open user default database. Login failed"
>
User XXX has a login on the remote server, but does not have a user in the
database he is configured to use. To administer the remote database, User
XXX must be a member of the sysadmin role on the remote server
http://snodland.blogspot.com
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.788 / Virus Database: 533 - Release Date: 01/11/2004|||On Sat, 6 Nov 2004 22:04:54 -0000, "Bob Simms"
<bob_simms@.somewhere.com> wrote:
>> 1. In Enterprise Manager, I've tried just putting the ip address as
>> the server, and "Use SQL Server authentication" from the user ID and
>> Password above, but I get the following error:
>> "SQL Server registration failed because of the connection failure
>> below... Cannot open user default database. Login failed"
>User XXX has a login on the remote server, but does not have a user in the
>database he is configured to use. To administer the remote database, User
>XXX must be a member of the sysadmin role on the remote server
Thanks for the reply.
Is it possible to use Query Analyzer or Access to connect to the
server given the information in my original post? I don't need
administrator-like functionality. As I mentioned, in Dreamweaver, I'm
able to create a connection, and view the tables and data (not to be
confused with creating the connection on the ASP pages, but in
Dreamweaver itself.) Dreamweaver lets you browse the tables, and data,
it doesn't let you run SQL Statements, which is why I'd like to use
other tools I have.
TIA.|||On Sat, 06 Nov 2004 20:21:58 -0500, Bryce Fischer
<spamtrap@.berzerker-soft.com> wrote:
>On Sat, 6 Nov 2004 22:04:54 -0000, "Bob Simms"
><bob_simms@.somewhere.com> wrote:
>> 1. In Enterprise Manager, I've tried just putting the ip address as
>> the server, and "Use SQL Server authentication" from the user ID and
>> Password above, but I get the following error:
>> "SQL Server registration failed because of the connection failure
>> below... Cannot open user default database. Login failed"
>>User XXX has a login on the remote server, but does not have a user in the
>>database he is configured to use. To administer the remote database, User
>>XXX must be a member of the sysadmin role on the remote server
>Thanks for the reply.
>Is it possible to use Query Analyzer or Access to connect to the
>server given the information in my original post? I don't need
>administrator-like functionality. As I mentioned, in Dreamweaver, I'm
>able to create a connection, and view the tables and data (not to be
>confused with creating the connection on the ASP pages, but in
>Dreamweaver itself.) Dreamweaver lets you browse the tables, and data,
>it doesn't let you run SQL Statements, which is why I'd like to use
>other tools I have.
Well... After some persistence, I figured it out. In Access, and
Visual Studio, I was able to bring up the Data Link Properties dialog,
and enter the information, and the one step I was not doing, was
entering in the catalog in the "Select the database on the server:"
field.
I was able to accomplish the same thing in Visual Studio, Server
Explorer, Data Connections.

Connecting to named instance fails for Enterprise Manager but works with Query Analyzer

Hi,
We have just had a 64-bit SQL Server 2000 cluster installed in our
network and I am having difficulties connecting to it.
The named instance I am trying to access is 192.168.0.108\DX
I can connect to it from a machine on the same subnet using Enterprise
Manager, but not from a machine on another subnet (192.168.14.0), when
attempiting EM just stops responding. The weird thing is that I can
connect using Query Analyzer, but not EM (obviously using the same
connection details).
Does anybody have any suggestions to what might be causing this
behaviour?
The client computer I use is a Windows XP Professional with SP2 and
client connectivity tools for SQL Server 2000. I have tried disabling
the firewall, which didn't help. I can connect to another SQL Server
(default instance, 32-bit, non-clustered) on the other network with
EM.
Regards,
Ville Tuomola
Have you tried pinging the server when you can't connect?
Have you checked the registration/connection properties for
the server in Enterprise Manager? Are you sure you aren't
just getting a login timeout? You can increase the login
timeout from the menu go to Tools then Options. On the
Advanced tab, increase the login timeout to a value higher
than the default 4 seconds.
-Sue
On 7 Oct 2004 01:11:35 -0700, vtuomola@.gmail.com (Ville
Tuomola) wrote:

>Hi,
>We have just had a 64-bit SQL Server 2000 cluster installed in our
>network and I am having difficulties connecting to it.
>The named instance I am trying to access is 192.168.0.108\DX
>I can connect to it from a machine on the same subnet using Enterprise
>Manager, but not from a machine on another subnet (192.168.14.0), when
>attempiting EM just stops responding. The weird thing is that I can
>connect using Query Analyzer, but not EM (obviously using the same
>connection details).
>Does anybody have any suggestions to what might be causing this
>behaviour?
>The client computer I use is a Windows XP Professional with SP2 and
>client connectivity tools for SQL Server 2000. I have tried disabling
>the firewall, which didn't help. I can connect to another SQL Server
>(default instance, 32-bit, non-clustered) on the other network with
>EM.
>Regards,
>Ville Tuomola

Connecting to MSDE

I am trying to connect to a MSDE database on server from my desktop using query analyzer. I keep getting the generic "SQL Server does not exist or access denied'

I have verified the following:
- the login/password is valid because when I access osql from server I can gain access
- the registry for Loginmode appears OK - set to 2 - mixed mode

What else do I need to check...

Thanks, kgaskeDid MSDE install as a named instance? Check the service, and see if it is listed as MSSQL$foo. If it is, you connect as servername\foo.|||No MSDE is not a named instance. Checked the service..|||Can you connect to the server from the server? Maybe no network protocols were enabled.|||Can you connect to the server from the server? Maybe no network protocols were enabled.

I'll second this; what version of MSDE is it? What's the operating system. By default, MSDE 2000 installed on WXP or Win2003 will not enable IP connections. You have to install SP3 (minimum) or SP4. I also recall (from some dim memory) that by default, MSDE does not enable anything but shared memory (ie, localhost) connections.

Regards,

hmscott

Connecting to Access

Hello,
I am trying to connect to an Access database though Query Analyzer. The
help file is minimally helpful on this. This is what I have so far:
-- add the database as a linked server
execute sp_addlinkedserver Library,
'Access 2002',
'Microsoft.Jet.OLEDB.4.0',
'D:\today\Andrew\Library.mdb'
-- add a login to that server
execute sp_addlinkedsrvlogin Library,
false,
'EBSHQ\Andrew',
'Admin',
NULL
-- show servers
execute sp_linkedservers
-- do something
execute sp_tables_ex 'Library'
Here's where I get an error:
Bah, the server's out for a bit. I'll post the error when it comes back.
Without the error message, does anything above look wrong or out of
place?
Thanks,
AndrewHi
If you get something like:
Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 20
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: Disk or network error.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
0x80004005 indicates a permissions issue. You could have specified the wrong
file name as the linked server or the permission are such that you can not
see/open it.
John
"Andrew Clark" <lark047@.hotmail.com> wrote in message
news:1108168266. 8c16687f2eb892e2e3e7ff72d9cb5c72@.teranew
s...
> Hello,
> I am trying to connect to an Access database though Query Analyzer. The
> help file is minimally helpful on this. This is what I have so far:
> -- add the database as a linked server
> execute sp_addlinkedserver Library,
> 'Access 2002',
> 'Microsoft.Jet.OLEDB.4.0',
> 'D:\today\Andrew\Library.mdb'
> -- add a login to that server
> execute sp_addlinkedsrvlogin Library,
> false,
> 'EBSHQ\Andrew',
> 'Admin',
> NULL
> -- show servers
> execute sp_linkedservers
> -- do something
> execute sp_tables_ex 'Library'
> Here's where I get an error:
> Bah, the server's out for a bit. I'll post the error when it comes back.
> Without the error message, does anything above look wrong or out of
> place?
> Thanks,
> Andrew
>|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in
news:eURHWJREFHA.1012@.TK2MSFTNGP14.phx.gbl:

> Hi
> If you get something like:
> Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 20
> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
> [OLE/DB provider returned message: Disk or network error.]
> OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
> IDBInitialize::Initialize returned 0x80004005: ].
> 0x80004005 indicates a permissions issue. You could have specified the
> wrong file name as the linked server or the permission are such that
> you can not see/open it.
> John
>
>
>
I think that might have been it, but I will have to check on monday
Andrew

connecting in a script to another db

hi.

I've tried to use the exec sql connect to ... in query analyzer, but could not
get it to parse.

Can someone show me how in a script, say the middle, I can connect to another
database and execute the remaining script lines there.

This way I can have a script update two separate db's at once.

Thanks
Jeff

Jeff KishJeff,

You can specify the from clause of SQL statements as
DATABASE.OWNER.TABLE, as long as they are on the same SQL Server. You
may also utilize the USE DATABASE command to change which database you
script will execute in. But, if you have different servers you want to
access, then that will not work unless you have set up Linked Servers.
In which case you would add the servername to the beginning of your
sting: SERVERNAME.DATABASE.OWNER.TABLE.

Update Table1 set C1 = 'YES' from Table1 T1, Table2 T2 where
T1.ReportID = T2ReportID

use DB2 --This Example will use both Databases to update the column

Update Table 4 set C5 = 'Report Printed' from Table4 T4, DB1.DBO.Table1
T1 where T4.ReportID = T1.ReportID

Steve|||On 23 Mar 2006 13:16:41 -0800, "sdyckes" <stephendyckes@.gmail.com> wrote:

>Jeff,
> You can specify the from clause of SQL statements as
>DATABASE.OWNER.TABLE, as long as they are on the same SQL Server. You
>may also utilize the USE DATABASE command to change which database you
>script will execute in. But, if you have different servers you want to
>access, then that will not work unless you have set up Linked Servers.
>In which case you would add the servername to the beginning of your
>sting: SERVERNAME.DATABASE.OWNER.TABLE.
>Update Table1 set C1 = 'YES' from Table1 T1, Table2 T2 where
>T1.ReportID = T2ReportID
>use DB2 --This Example will use both Databases to update the column
>Update Table 4 set C5 = 'Report Printed' from Table4 T4, DB1.DBO.Table1
>T1 where T4.ReportID = T1.ReportID
>Steve
Thanks Steve. They will be on the same SQL Server, so I can run with this.

Regards
Jeff Kish

Connect to sql server through internet

Hi,
Can someone please tell me how to do that?
I tried to connect to a sql server through internet by using sql query analyzer but it failed.
I searched for information in the internet but came to no avail.
Can someone please shed some light?
Thanks!I don't know of any "one size fits all" solution for connectivity problems. Without understanding what ISN'T working, it is tough to figure out what will work.

The simplest answer is to set up VPN if that is a choice (such as when you want to connect to a work server from home). This can make things as simple as when you want to connect to a SQL Server on another machine in the office.

If that isn't the case, things get a bit more complicated. There are usually about 1000 pieces involved in connecting two arbitrary computers over the internet. There are usually only one or two of them that work against the connection, but finding them is a needle in a haystack.

-PatP|||Are you wanting to do this for work, for a SQL Server at home, or for a SQL Server from an web host?

Connect to SQL Server dialog

Hi,
Is it possible to avoid the last used server being in Query Analyzer
'Connect to SQL Server' dialog by default(when I click on File/Connect
in Query Analyzer) - I don't want to apply 'truncate table' scripts to
a production server by mistake!
Thanks<polinaskulski@.aol.com> wrote in message
news:1120572207.464620.272820@.g49g2000cwa.googlegr oups.com...
> Hi,
> Is it possible to avoid the last used server being in Query Analyzer
> 'Connect to SQL Server' dialog by default(when I click on File/Connect
> in Query Analyzer) - I don't want to apply 'truncate table' scripts to
> a production server by mistake!
> Thanks

I don't think that's possible - if you're concerned about running scripts in
the wrong place, then you can reference @.@.SERVERNAME in your scripts, or
always begin a transaction and require a manual COMMIT or ROLLBACK. Another
option is to use a dedicated administrator account for deployments to
production servers.

But all those approaches have drawbacks as well, and in the end there's no
catch-all substitute for being careful about what you're doing.

Simon