Re: Quick Question on SQL Server 2005 Express

First, you can't attach a database in a remote machine in your network as Andrew has already mantioned.

Second, you can't attach the sames database more than one instance of SQL Server at once. One database can be used only by one SQL Server instance and as far as I understand the database in question is already attached to a SQL Server instance.

Why not installing only SQL Server Management Studio and connect to your remote instance and perform actions this way? Just like connecting your database in a web hosting company which hosts your web site and database. Create an Alias in SQL Server Configuration Manager and use the IP Address (and it's instance name if it's a Named Instance) of your remote SQL Server instance (The Enterprise Edition one) and use its port number. Ensure you configure your firewall locally and on your remote SQL Server server to let you in and out. Create a Login as User for the database in question for your user in the office. And he'll be able to connect to your remote SQL Server instance using only SQL Server Management Studio to perform actions you desire according to the rights he has been given.

So, you don't need to install an instance of SQL Server on the machine of your user in the office, you only need to tools to reach to your remote SQL Server instance.

--
Ekrem Önsoy



"David Clifford" <someone@microso> wrote in message news:O%23wjdvB3IHA.1196@xxxxxxxxxxxxxxxxxxxxxxx
Hello Andrew. Sorry for this confusion. To start again:

We have an SQL Server 2005 Enterprise Edition located in a building that is away from our office. "Our" database is registered on that server, and the plan is that all backups/shrinking etc will be done offsite by the people who manage the Enterprise SQL Server 2005 Package. It is preferred this way because our organization has a dedicated SQL Server team who look after DOZENS of other SQL Server databases that are attached to the Enterprose Edition of SQL Server.

My Visual Basic application accesses this remote Enterprise SQL Server database via an ODBC link over a LAN. All works well.

But, my client wants to alter tables/write views/stored procedures etc., etc. So, instead of leaving his office to go to the other building to use the Management console on the Enterprise Edition SQL Server 2005, he want to "link" to the database on HIS office based computer over the LAN using SQL Server 2005 Express and SQL Server Express Management Console.

I installed SQL Server 2005 Express on his machine and tried to attach the database that is registered on Enterprise Edition SQL Server 2005 using the Express Management console, but all I could see in the Drive List box were the two local drives C: and D:, and the Master and temp SQL Server databases that were installed with the Express SQL Server edition. In the drive list box, the network drives were not shown, hence, I could not "see" our database on the network in it's folder. I could see them in Windows Explorer, but not in the Express Management console.

My question is: Why not? Is it not possible to use the Express SQL Server edition attach to our database that is located in the other building in order to locally write Views etc? If we can't, then we are going to have to trek back and forth to write views/stored proceedures etc.

My application work just fine as it connects to the Enterprise SQL Server database via an ODBC link, and all is well.

Sorry for this rambling, but the question is, I think, pretty fundamental in that we want to be able to control our centrally place database in our remote office.

I really hope that this makes sense to you.

In the SQL Server Configuration Manager, I have Enabled TCP/IP.

Regards

David Clifford

"Andrew J. Kelly" <sqlmvpnooospam@xxxxxxxxxxxx> wrote in message news:uKKv4962IHA.2580@xxxxxxxxxxxxxxxxxxxxxxx
It's not clear to me what you are asking. You need to separate the two parts here. You have the server which will host the database and the SQL Server Service. The Client can live anywhere as long as it has TCP access to the server. The client can be SQL Server Management Studio, a .net app, a web server etc. it doesn't matter as it is simply talking to the server. You can also have a client on the same machine as the server but that is not a requirement. So if you have a desktop that will be a remote client to a SQL Server on another box that is fine. But it can also be a client to a local copy of SQL Server on that desktop as well. You can use the same client (SSMS) to connect to both and do what you want as long as you have the proper permissions.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"David Clifford" <someone@microso> wrote in message news:u%23lnfg42IHA.4500@xxxxxxxxxxxxxxxxxxxxxxx
Thank you for that prompt reply Andrew.

It is what I suspected.
My main problem then is, if the SQL Database is located at a remote location..another building for example, then my client will NOT be able to run queries/change tables/etc using the Management Consule for a SQL Server 2005 instance on HIS local machine? Sorry for the rather basic question, but this is all sort of new to me

Regards

David

"Andrew J. Kelly" <sqlmvpnooospam@xxxxxxxxxxxx> wrote in message news:eeWb6H42IHA.2424@xxxxxxxxxxxxxxxxxxxxxxx
That is correct. None of the versions of SQL Server support the database files residing on a networked share. But you don't attach the db from the server to a client anyway. The client should not be the sqls server service itself, it should be an application suing the client / server approach. The client talks to the server and hence the db thru the SQL Server service on the server and thus the client has no need to access the files directly.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"David Clifford" <someone@microso> wrote in message news:%23xkWz432IHA.4988@xxxxxxxxxxxxxxxxxxxxxxx
Hello All

A quick question. We are moving an Access database over to SQL Server 2005. No problems. The SQL Server database will be located on it's own server. To test the system, I installed SQL Server 2005 Express onto the server, got the database attached etc. No problems. I installed the same Express version onto a client computer so that the operator could use the database from his workstation, tried to attach the database, but the network drive was not listed in the file dropdown box..only the local C: and D: drives. My gut says that Express won't look at network drives, anyone care to comment?

Thank you

David.








.

Relevant Pages

  • Re: TS vs VPN
    ... Using TS, w/o VPN ... The remote client connects to your local TS via Remote Desktop. ... "Foo" accesses the SQL server, which is nearby in a protected part of the network. ...
    (microsoft.public.windows.terminal_services)
  • Re: Reaching Access 2003 datas via SQL Server 2005 64 bits
    ... even with the procedure to create the invoice number I still ... One client enters in the store and asks for a product. ... formats of Access database files known as MDB and ACCDB and/or Access JET. ... (I have no problem to reach Sql Server 2005 tables from Microsoft ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Access 2007->SQL Server2005 "connection was forcibly closed",G
    ... an Access database in former times this was a lot of work which took me ... closed connections - but all of these errors are in the version which used ... the SQL Server 2000 and everything worked ... client for programming easy, fast and good applications. ...
    (microsoft.public.sqlserver.connect)
  • Re: Permissions problem on SBS 2003 R2 for SQL Server 2005 clients
    ... use SQL Server authentication instead. ... I've been testing a SQL Server 2005 Express database in a multiuser ... environment using Window Small Business Server 2003 R2 with Windows XP ... On a given client computer to which I may have logged on with Windows ...
    (microsoft.public.windows.server.sbs)
  • Re: Quick Question on SQL Server 2005 Express
    ... I have not seen any documentation yet that states connecting other editions of SQL Server using SSMSE would break any license agreement. ... As Ekrem stated they should have a licensed edition of the full client tools and then all they have to do is register the server in the registered servers dialog and they should be all set. ... I have installed SQL Server 2005 Express, and now I want to connect to the remote database and use the Management Studio to manipulate the database. ...
    (microsoft.public.sqlserver.setup)
Loading