April 9, 2008 at 1:37 pm
Team...
I am actually confused over the terms SQL-Server, SQLExpress, MASTER, default, server and instance.
I am having trouble getting MS-Access table-data exported / upsizing to an existing SQL-server/Express MDF database.
I installed SQL-Express and I have SQL-Server on my computer.
I installed 'Microsoft SQL Server Management Studio Express'.
The server seems to be ..mycomputername..\SQLEXPRESS.
There appears to be a master.mdf in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data as well as model.mdf, msdbdata.mdf, mssqlsystemresource.mdf and tempdb.mdf AND JOHN1.MDF, JOHN2.MDF and JOHN3.MDF -- these john-mdfs are my attempts at doing things.
I have "copied" JOHN1.mdf to my ASP-source-folder c:\..mydocs..\ASP-PROJECTS\PROJ001\APP_DATA folder.
MSSMSE does not seem to want to connect to my APP_DATA\JOHN1.MDF. It only wants to connect to the databases in the MSSQL.1\MSSQL\DATA folder not to any other folder in ..mydocs..\..anyfolder..\..anyname.mdf.
Being used to MS-Access, I can 'double-click' on any MDB file and ACCESS opens the database and I can 'see-and-do-things'.
But when I 'double-click' on any MDF-file, Windows complains that there is no application for this file-type -- 'huh?' bizarre.
I feel lost with SQL-server, DSN(s) , exporting/upsizing from MS-access.
I think I am missing a crucial piece of knowledge that has eluded me.
Any help will be greatly appreciated.
Thanks...John
PS :cool:-- I created a thread about cannot export from MS-Access in the MS-ACCESS forum.
April 9, 2008 at 2:07 pm
wow...lots to work with.
Let's see if we can start with a few:
SQL Server is just the name of a product family. In the 2005 version of those products, the free edition of SQL Server 2005 is called SQL Server Express Edition (often shortened to SQL Express).
When you install SQL Server, you create what is called a SQL Server "instance". Each separate install is its own instance. Meaning - it's a set of database all running under a single Windows Service, sharing or competing for resources, etc.... You can have more than one per machine.
SQL Server Instances have names. If you don't specify a name, it's what's called the "default" instance. It is usually addressed by pointing at your machine's name. Specifying an actual name for an instance results in what is called a "named instance". When you pick "create a named instance" during the SQL Server Express installation, SQLEXPRESS would be the default name suggested by the installation. (Trying to word it a different way - an instance to SQL server is kind of like a web site is to IIS. Each server can run several off of a single machine, etc...)
In order to operate, a SQL Server needs several system databases to do its work: they are Master, MSDB, Model and TempDB.
There are two basic ways to conect databases for use by an application:
- the most common way to have a production-like scenario is to attach a database to a SQL Server instance. Every time that instance starts up, it starts making that database available for querying, adding or modifying data, etc..... That's the traditional method, by far the most common and by far more performant than option #2
- the second, less common way, which unfortunately seems to be a common development technique, is what is called a "user database", where the database is dynamically attached to the instance when someone needs something from that database. Once the database stopps being queried, the database is then detached and released from the instance. This should be reserved only for very small database with almost no traffic or activity, etc.....
-------------------------------------------------------
I'd spend some time reading through Books Online. Should help clear out some of these things.. Start here.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 9, 2008 at 2:14 pm
As to what you should do now:
- put the johnxxx.mdf files back where you found them.
- start SQL Server Management Studio, and connect to your instance (machinename\SQLEXPRESS)
- once you've connected, open up the databases folder. If you see your JOHNX files, then great; if not - then right-click on the databases folder, and pick "attach". You should get the option to pick those Johnxxx files. You'll have to attach them one at a time.
- once they're attached, take a look through them to see what was imported. You should have tables, etc......
Once the files are attached, set up your OLE-DB or ODBC connection to talk to them from the control panel icon under Admin Tools. OLE-DB or "Native SQL CLient" connections are preferrable.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 9, 2008 at 2:30 pm
John (4/9/2008)
Team...I am actually confused over the terms SQL-Server, SQLExpress, MASTER, default, server and instance.
I am having trouble getting MS-Access table-data exported / upsizing to an existing SQL-server/Express MDF database.
I installed SQL-Express and I have SQL-Server on my computer.
I installed 'Microsoft SQL Server Management Studio Express'.
The server seems to be ..mycomputername..\SQLEXPRESS.
There appears to be a master.mdf in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data as well as model.mdf, msdbdata.mdf, mssqlsystemresource.mdf and tempdb.mdf AND JOHN1.MDF, JOHN2.MDF and JOHN3.MDF -- these john-mdfs are my attempts at doing things.
I have "copied" JOHN1.mdf to my ASP-source-folder c:\..mydocs..\ASP-PROJECTS\PROJ001\APP_DATA folder.
MSSMSE does not seem to want to connect to my APP_DATA\JOHN1.MDF. It only wants to connect to the databases in the MSSQL.1\MSSQL\DATA folder not to any other folder in ..mydocs..\..anyfolder..\..anyname.mdf.
Being used to MS-Access, I can 'double-click' on any MDB file and ACCESS opens the database and I can 'see-and-do-things'.
But when I 'double-click' on any MDF-file, Windows complains that there is no application for this file-type -- 'huh?' bizarre.
I feel lost with SQL-server, DSN(s) , exporting/upsizing from MS-access.
I think I am missing a crucial piece of knowledge that has eluded me.
Any help will be greatly appreciated.
Thanks...John
PS :cool:-- I created a thread about cannot export from MS-Access in the MS-ACCESS forum.
Just to re-hash and so on some of the stuff Matt said:
"server" is a piece of software that other software talks to to accomplish things, the idea being that it "serves up data/code/whatever", like being served food. The word can also mean a computer that has server software on it.
SQL Server is software that "serves up" databases. SQL is a language used to talk to databases, so this makes sense.
SQLExpress is the free version of SQL Server. There are other version with more features and different names.
MASTER is one of the database SQL Server uses for its own internal work. It's kind of a database of databases.
default is just what it says, it's the name/whatever, that will be used unless you tell the computer otherwise. Like in Access, you have a default field size for text data (usually 50 characters is the default).
"instances" are just copies of the server software on the server computer. Just like you can open multiple Access databases at the same time, you can open multiple copies of SQL server at the same time.
The main difference between server software and application software is that server software doesn't have a "front end" that you see. SQL keeps running (in most cases), even if you shut down Management Studio and any other applications that connect to it. Access, Word, etc., if they aren't open, they aren't running. Those are applications. SQL, IIS, several other things, keep running in the background. Server software is a type of "service software". All services run in the background and talk to other software.
(If that's not clear, please let me know. If I'm overdoing this, just ignore me. I'm basing this on what I had to learn the hard way when I went from building an Access database to using Access to connect to SQL Server. It wasn't easy.)
As Matt mentioned, leave the MDF and LDF files alone for now. You don't use those the way you do an MDB or ADP file. You don't need to double-click them to do stuff.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 9, 2008 at 4:58 pm
Gents -- Thank you for your 3-replies -- I learned a lot from your answers. I understand the server-concepts and I am much clearer on things -- and it makes sense, now, why things were not working too well before.
1) It appears that all of the DATABASES that I want to work with MUST be in the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data folder.
2) It appears that once I do that, I should be able to access the DB from the SQL Server Management Studio. I also think MS-ACCESS will be able to export tables better to this new arrangement.
3) However, I am puzzled as to how am I going to develop ASP.net apps with the DATABASE NOT in the APP_DATA folder under the ASPNET-source-code-folder(s) -- can anyone point me to the answer for this dilema??
I shall try all of your suggestions and get back to this forum to advise my progress -- I cannot be alone in this...
Thanks to all...John
April 9, 2008 at 7:06 pm
John (4/9/2008)
Gents -- Thank you for your 3-replies -- I learned a lot from your answers. I understand the server-concepts and I am much clearer on things -- and it makes sense, now, why things were not working too well before.1) It appears that all of the DATABASES that I want to work with MUST be in the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data folder.
2) It appears that once I do that, I should be able to access the DB from the SQL Server Management Studio. I also think MS-ACCESS will be able to export tables better to this new arrangement.
3) However, I am puzzled as to how am I going to develop ASP.net apps with the DATABASE NOT in the APP_DATA folder under the ASPNET-source-code-folder(s) -- can anyone point me to the answer for this dilema??
I shall try all of your suggestions and get back to this forum to advise my progress -- I cannot be alone in this...
Thanks to all...John
You're getting there. As to yourt points:
1. Close, but not quite. Having them here instead of in the app_data directory is BETTER. Better for performance, better for security, and better for being able to manage it/edit it/profile it using SSMS. It's not that you COULDN'T make it work the other way, you SHOULDN'T.
2. pretty much right on the money.
3. From within your ASP.NET project, click on View, Server Explorer. Within that window, right-click on add data connection in the icons. Hit the down arrow on the server list and pick your machine and instance out. go from there.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 10, 2008 at 12:39 am
John (4/9/2008)
1) It appears that all of the DATABASES that I want to work with MUST be in the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data folder.
Only because that's where the SQL engine knows that the Database files are for those particular databases. When you create a database in SQL Server, you can specify the location of the database files. They can be anywhere on the drive. Once the database is created, you cannot just move the files on disk, as the SQL engine will no know where they've moved to and will give errors is you try and access the DB
It's one of the big differences between Access and SQL Server. Access is a file-based database. SQL Server is not. It's a server, it runs as a service and for anyone accessing the server, the exact ocation of the database files is irrelivant.
If you're doing ASP, the database files should not be in the same directory as the ASP files. You don't want someone downloading your database.
3) However, I am puzzled as to how am I going to develop ASP.net apps with the DATABASE NOT in the APP_DATA folder under the ASPNET-source-code-folder(s) -- can anyone point me to the answer for this dilema??
A connection string to an access DB specifies the path, because in access the file is the database.
Because SQL's running as a service, the connection string points at a server and a database. The database could even be on another machine entirely. The SQL engine knows where the database file is and how to access it.
An MS Access connection string looks something like this
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;User Id=admin;Password=;
A SQL Server connection string looks something like this
Driver={SQL Native Client};Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;
Does that clear anything up?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply