Database in project

I'm using C#.Net 1.1 and i have a sample database inside my project (the actual database is in a folder in the project) and I simply want to connect tot the database inside the project and select and process data. i'm normally connecting to external databases so i'm kind of at a loss as to how i should connect to the database in the project (it's the Northwind Database and I'm using sql server 200).

any advice greatly appreciated.

In asp.net 2.0 you would add the database to the app_code folder

In asp.net 1.1 youll need to attach the db to sql server and query using a connection string.

Do you need help in attaching the db to sql server?

Cheers
Gregor

|||Yes, how do I attach the database to sql server?|||

Hi Myron

Do you have the database as an mdf file or do you have database scripts?

Cheers
Gregor

|||i have the database as an mdf file (Northwind.mdf)|||

The latest on this: in VS 2003 i went to Server Explorer to "Add Connection" . I am entering the following values (please not <addconnection> tags indicate start and end:

<ADDConnection>

Select or enter server name: (localhost)

Enter information to log onto server: chose "Use Windows NT integrated security)

Select the database on the server:

Attach a db file...: Northwind

Using the filename: C:\SQL Server 2000 Sample Databases\NORTHWND.MDF

</AddConnection>

But when i go to test the connection I am getting the following error:

<ERROR>

Test connection failed because of an error in initializing provider. [DBNETLIB].[ConnectionOpen (Connect()).]SqlServer does not exist or access denied.

</ERROR>

do you know that this means? the db exists so i now assume that I have to check the rights on the database...

Any help on this greatly appreciated

Database creation

I downloaded the sample database AdventureWorks from microsoft . When I run the download it created the data and log files under data folder in MSQLalong with other data and log files of master databases etc. However I do not see this database in SQLserver management studio. Can some one help me how to fix this so that I will be able to see this database in management studio.

I am downloading this for SQL server reporting purpose

Hi Kash,

The sample database installation delivers the database files to your machine, but does not "attach" the database to a specific SQL instance.

To attach the database you could do one of the following:

(1) Execute the following query:

USE master
GO
EXEC sp_attach_db 'AdventureWorksDW', '<path to your MDF file>AdventureWorksDW_Data.mdf', '<path to your LDF file>AdventureWorksDW_Log.ldf'

(you could use whatever name you like and also - make sure the MDF and LDF file names are correct, too)

(2) In SQL Management Studio, right-click the Databases folder, select "Attach". Navigate to the folder where the MDF file is installed and select it.

With either steps, the database is attached, recovered and comes online. BTW - this is a good way to move or rename a database: detach, move, attach (possibly under different name).

HTH,

Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Database create error due to folder access right.

I have an application which will create a SQL database(using CREATE DATABASE) and the database files are supposed to be placed in c:\program files\myApp\data\myDatabase.mdf
However, when I try to do so, I get the following error:

System.ApplicationException: System.Data.SqlClient.SqlException: CREATE DATABASE failed. Some file names listed could not be created. Check related errors.CREATE FILE encountered operating system error 5(Access Denied) while attempting to open or create the physical file 'C:\Program Files\myApp\data\myDatabase.mdf'.

My window login already have administrator role.

The problem can be solved if I manually add full access right to my login in window exploder. However, how can I do this programmatically? Or what is the proper way to create database located under my application folder?The account that sql runs as needs to have the security privledge to access the directory or file.

thanks,
mark|||Sorry that I am using SQL authenication and provide the sa password in the connection string. How can I create database in my application folder?|||As Mark pointed out, you will need to grant write access to that directory to the SQL Server service account. The error is not related to the permissions of the account that you use to connect to SQL Server when you execute the CREATE DATABASE statement.

To find out the service account, execute Start->Run->services.msc and then look for the SQL Server service entry; you should find in it the name of the service account.

Thanks
Laurentiu|||I see. Thank you very much.
If I would like to include SQL Server Express as a pre-requirite of my application, can I grant my app folder write access right of the SQL Server service account during installation?
Thanks again.|||

Are you referring to the SQL Server Express (SSE) installation or to your application installation?
In the first case, I don't think there is a way to grant access at installation time, unless the data folder is the same as your app data folder. For the second case, you can determine the SSE service account and grant it permissions to your folder at the time you install your application.

Thanks
Laurentiu

|||Anyone have a code sample of how to determine the SSE service account programmatically?

Database create error due to folder access right.

I have an application which will create a SQL database(using CREATE DATABASE) and the database files are supposed to be placed in c:\program files\myApp\data\myDatabase.mdf
However, when I try to do so, I get the following error:

System.ApplicationException: System.Data.SqlClient.SqlException: CREATE DATABASE failed. Some file names listed could not be created. Check related errors.CREATE FILE encountered operating system error 5(Access Denied) while attempting to open or create the physical file 'C:\Program Files\myApp\data\myDatabase.mdf'.

My window login already have administrator role.

The problem can be solved if I manually add full access right to my login in window exploder. However, how can I do this programmatically? Or what is the proper way to create database located under my application folder?The account that sql runs as needs to have the security privledge to access the directory or file.

thanks,
mark|||Sorry that I am using SQL authenication and provide the sa password in the connection string. How can I create database in my application folder?|||As Mark pointed out, you will need to grant write access to that directory to the SQL Server service account. The error is not related to the permissions of the account that you use to connect to SQL Server when you execute the CREATE DATABASE statement.

To find out the service account, execute Start->Run->services.msc and then look for the SQL Server service entry; you should find in it the name of the service account.

Thanks
Laurentiu|||I see. Thank you very much.
If I would like to include SQL Server Express as a pre-requirite of my application, can I grant my app folder write access right of the SQL Server service account during installation?
Thanks again.|||

Are you referring to the SQL Server Express (SSE) installation or to your application installation?
In the first case, I don't think there is a way to grant access at installation time, unless the data folder is the same as your app data folder. For the second case, you can determine the SSE service account and grant it permissions to your folder at the time you install your application.

Thanks
Laurentiu

|||Anyone have a code sample of how to determine the SSE service account programmatically?

Database Create error

I am trying to create a database in my application's installation folder and it will not create because the folder does not have the permissions for the service account added as full control. I can't figure out how to get the service account name in my program so that I can add it to the folder permissions. The service name is a long name and has the computer name and the SQL server name included in it. For example: SQLServer2005MSSQLUser$<Ccmputer Name>$SQLEXPRESS. I'm assuming this pattern is always followed. I guess my question is can I query the database for this information and then use it to add the appropriate permissions to my application's install folder?

The pattern for the service account names you described is correct for the default SQL Express settings. I would like to generalize this pattern for all the 3 groups SQL Server 2005 may create on the computer:

* Sql Server group:

SQLServer2005MSSQLUser$<<machine_name>>$<<instance_name>>

* Agent group:

SQLServer2005SQLAgentUser$<<machine_name>>$<<instance_name>>

* Full text search group:

SQLServer2005MSFTEUser$<<machine_name>>$<<instance_name>>

You can use these patterns directly by inserting the machine name and SQL Server instance name (the default instance name is called MSSQLSERVER ).

An alternative method would be to use the following query:

SELECT name, sid FROM sys.server_principals WHERE name like '%SQLServer2005MSSQLUser%'

Be aware that a system administrator may drop this server principal from the server, invalidating this query to obtain the Windows service group.

I hope this information was useful. Let us know if you have any further questions or feedback.

Thanks a lot,

Raul Garcia

SDE/T

SQL Server Engine