How to make tables & stored procedures in MSDE?

Hello, I am a beginner using MSDE & Visual Basic.NET standard version (not Visual Studio.NET) . MSDE was downloaded from microsoft.com yesterday, which is sql2ksp3.exe.

The problem is I don't know where to input data to set up tables or stored procedures after configured the MSDE. The MS Desktop Engine pops me "configuration sucessfully", then I can just open a small window with the title of "SQL Server Service Manager", telling me SQL Server is running. But I can't find any SQL application in the Start->All Programs-> for me to run.

What's the human interface of MSDE? Can anybody tell me how you deal with MSDE?

And in my VB.NET's Server Explorer, I find there is only "Data connections" icon which allows me to connect to existing database, and no "Servers" icon as showed in many Visual Studio instruction books, which make me no chance to create new database.

My Windows system is XP professional (SP2) with IIS, .NET framework 1.1 and Server Extensions Adminstrator. What's my problem? Did I configure wrong somewhere? I want to make my computer as a local server. And does the combination of VB.NET + MSDE work?

Thanks a lot for your help! Have a good weekend!

Henry Li

There is no program that comes with msde for managing and creating databases. You can use visual studio 2003's server explorer for that. Here is an example of how to create a database with vb.net

http://www.vb-tips.com/default.aspx?ID=73eab21d-db5f-46b2-8eea-6680e677e994

|||Hello Ken,
Thanks a lot for your quick response, it's the first time for me to join this forum, and first time to get response.

Do you mean I can only use codes to generate database tables and stored procedures? Are all these tables located under the MYPC.master.dbo icon in the Server Explorer? Is there any simpler way that I just right-click something then it gives me an option to "New tables" or "New Database"? (But I don't have the Servers icon in the Server Explorer, did I configure wrong for my VB.NET package?)

And does the MSDE have interface window like Access that can give you a straight-forward and nice looking window to input table data? Can MSDE work independantly without VS.NET's help? I really have no idea how MSDE itself look like. By the way, does SQL Server Express have windows and menus to manipulate data?

I hope my questions are not too many. Thanks a lot in advance for your help!

Best wishes,
Henry Li|||Hi,
You can create databases, tables, etc in visual studio 2003 server explorer also. Right click on databases and select new database. The Sql Server Express is the free 2005 version of msde. You can download a management console for the express edition.
Ken|||

Dear Ken,
You don't really understand my problems yet.
I am using VB.NET package, not VS.NET. The interface and menu are similar, but when I did a help search of "Server Explorer" within it, it told me VB.NET doesn't have the Server node within the Server Explorer, only VS.NET package has it. I just have the first node, which is "Data Connection".

I ran your code in a new project "test1". I pasted it on the WebForms1.aspx.vb, then built it. It generated errors as below:

Build started: Project: test1, Configuration: Debug .NET

Preparing resources...
Updating references...
Performing main compilation...
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(24) : error BC30002: Type 'SqlConnection' is not defined.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(26) : error BC30182: Type expected.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(27) : error BC30691: 'EventArgs' is a type in 'System' and cannot be used as an expression.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(27) : error BC30800: Method arguments must be enclosed in parentheses.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(27) : error BC30205: End of statement expected.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(33) : error BC30002: Type 'SqlConnection' is not defined.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(48) : error BC30002: Type 'SqlCommand' is not defined.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(54) : error BC30451: Name 'MessageBox' is not declared.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(61) : error BC30456: 'Text' is not a member of 'test1.WebForm1'.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(88) : error BC30002: Type 'SqlCommand' is not defined.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(92) : error BC30002: Type 'SqlException' is not defined.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(93) : error BC30451: Name 'MessageBox' is not declared.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(113) : error BC30002: Type 'SqlCommand' is not defined.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(123) : error BC30002: Type 'SqlException' is not defined.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(124) : error BC30451: Name 'MessageBox' is not declared.
Building satellite assemblies...
Satellite assemblies could not be built because the main project output is missing.

- Done -

Build: 0 succeeded, 1 failed, 0 skipped
I am so dissapointed with VB.NET standard edition, as it seems it can't generate new database, it can just connect to existing database that are already built up.
So I am curious whether MSDE can work independently to let me set up new database, but you ignored my question. I really feel depressed about this combination of VB.NET + MSDE.
You may search on your VS.NET help function for "Server Explorer", it will tell you VB.NET package doesn't have server node.

Thanks & Best regards,
Henry Li

|||Hello Ken,
Visual Basic.NET Standard Edition package is cheaper than VS.NET package. It has less functions than VS.NET package, and cost me $114.
On its package box it mentions it can't build device-based applications and solutions, can't build/debug/deploy powerful server software while VS.NET Professional can.

You said SQL Server Express 2005 is a new version of MSDE, I am just wondering whether it can work like Oracle Plus or Access which can let you have some windows to key in some data?

Thanks a lot!
Henry|||You need a imports system.data.sqlclient at the top of the code file.|||Sorry I thought vb.net standard had the server explorer. Here is a link to download the free sql server 2005 express.

http://msdn.microsoft.com/vstudio/express/sql/

Here is a link to the free management studio for sql server 2005 express

http://www.microsoft.com/downloads/details.aspx?FamilyId=82AFBD59-57A4-455E-A2D6-1D4C98D40F6E&displaylang=en|||Hello Ken,
Thanks a lot! This "imports system.data.sqlclient" really helps!
Now it reduces to only 2 errors left, which are:
--
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(54) : error BC30451: Name 'MessageBox' is not declared. (under the sub createDataBase()->Try/Catch)
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(61) : error BC30456: 'Text' is not a member of 'test1.WebForm1'. (under the sub CreateClientsTable() ->me.Text)
--

I think I might need another line of "imports something"?
Sorry I am a beginner of programming in sql. And want to try your code to see if it can pass through to connect to my MSDE.

By the way, I've downloaded the SQL Server Express and the management console but haven't installed them, and still downloading the 440MB Studio Web Develop Express under the Visual Studio 2005 Express which was issued for free download just 2 weeks ago. I hope this can take place of my VB.NET package.

Thanks a lot again!

Best regards,
Henry|||I wrote that as windows forms application. Messagebox.show is not supported in web application's neither is me.text. Try changing messagebox.show to response.write and me.text = to response.write("The string" & "<br>")|||Hi, I am new one in this forum. About question: "How to make tables & stored procedures in MSDE?", try with command line tool - osql. If you need more help, I am here. Or search MS site about it.

Stevan
|||Hello Ken,
I've downloaded the Visual Web Developer 2005 Express which includes the SQL Server Express, so I installed them both.
The SQL Server 2005 Express is similar to the MSDE, although it has a configuration console, it still can't handle database.
Then I downloaded the SQL Management Studio Express, it's really good! It gives me free choices to deal with security, log-in, creation of new databases!

Then I created a database in the SQLEXPRESS via the management studio console, then in the Visual Web Dev Express I created connection with that new database, it connects!

Now I can creat tables and stored procedures either directly in the SQLEXPRESS's database (via the mgt studio), or indirectly in the Visual Web Dev Express within the database explorer window.

Now I don't use the VB.NET package anymore except if I need to create some windows exe files.

Thank you for your kindness!

Best wishes,
Henry Li|||Thank you Stevan,
I found my problem was some basic things that maybe many people took it for granted while I didn't know.
Actually I need to make a database in the MSDE first, and MSDE has no interface to let me set it up. I need the management software such as the SQL Management Studio 2005 Express.
Now I found this Mgt Studio Express is very useful, it can handle MSDE and SQLEXPRESS. I created some databases with the help of Mgt Studio Express, then I can create tables or procedures!

Best wishes,
Henry|||

Hi,

You can generate tables with osql command, i just need to indicate the command as following:

osql -U user -S server\instancename

then

1> create database test

2> GO

1>use test

2>GO

then you're inside of test database and you are ready to generate your table according you're needed

How to make tables & stored procedures in MSDE?

Hello, I am a beginner using MSDE & Visual Basic.NET standard version (not Visual Studio.NET) . MSDE was downloaded from microsoft.com yesterday, which is sql2ksp3.exe.

The problem is I don't know where to input data to set up tables or stored procedures after configured the MSDE. The MS Desktop Engine pops me "configuration sucessfully", then I can just open a small window with the title of "SQL Server Service Manager", telling me SQL Server is running. But I can't find any SQL application in the Start->All Programs-> for me to run.

What's the human interface of MSDE? Can anybody tell me how you deal with MSDE?

And in my VB.NET's Server Explorer, I find there is only "Data connections" icon which allows me to connect to existing database, and no "Servers" icon as showed in many Visual Studio instruction books, which make me no chance to create new database.

My Windows system is XP professional (SP2) with IIS, .NET framework 1.1 and Server Extensions Adminstrator. What's my problem? Did I configure wrong somewhere? I want to make my computer as a local server. And does the combination of VB.NET + MSDE work?

Thanks a lot for your help! Have a good weekend!

Henry Li

There is no program that comes with msde for managing and creating databases. You can use visual studio 2003's server explorer for that. Here is an example of how to create a database with vb.net

http://www.vb-tips.com/default.aspx?ID=73eab21d-db5f-46b2-8eea-6680e677e994

|||Hello Ken,
Thanks a lot for your quick response, it's the first time for me to join this forum, and first time to get response.

Do you mean I can only use codes to generate database tables and stored procedures? Are all these tables located under the MYPC.master.dbo icon in the Server Explorer? Is there any simpler way that I just right-click something then it gives me an option to "New tables" or "New Database"? (But I don't have the Servers icon in the Server Explorer, did I configure wrong for my VB.NET package?)

And does the MSDE have interface window like Access that can give you a straight-forward and nice looking window to input table data? Can MSDE work independantly without VS.NET's help? I really have no idea how MSDE itself look like. By the way, does SQL Server Express have windows and menus to manipulate data?

I hope my questions are not too many. Thanks a lot in advance for your help!

Best wishes,
Henry Li|||Hi,
You can create databases, tables, etc in visual studio 2003 server explorer also. Right click on databases and select new database. The Sql Server Express is the free 2005 version of msde. You can download a management console for the express edition.
Ken|||

Dear Ken,
You don't really understand my problems yet.
I am using VB.NET package, not VS.NET. The interface and menu are similar, but when I did a help search of "Server Explorer" within it, it told me VB.NET doesn't have the Server node within the Server Explorer, only VS.NET package has it. I just have the first node, which is "Data Connection".

I ran your code in a new project "test1". I pasted it on the WebForms1.aspx.vb, then built it. It generated errors as below:

Build started: Project: test1, Configuration: Debug .NET

Preparing resources...
Updating references...
Performing main compilation...
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(24) : error BC30002: Type 'SqlConnection' is not defined.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(26) : error BC30182: Type expected.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(27) : error BC30691: 'EventArgs' is a type in 'System' and cannot be used as an expression.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(27) : error BC30800: Method arguments must be enclosed in parentheses.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(27) : error BC30205: End of statement expected.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(33) : error BC30002: Type 'SqlConnection' is not defined.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(48) : error BC30002: Type 'SqlCommand' is not defined.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(54) : error BC30451: Name 'MessageBox' is not declared.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(61) : error BC30456: 'Text' is not a member of 'test1.WebForm1'.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(88) : error BC30002: Type 'SqlCommand' is not defined.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(92) : error BC30002: Type 'SqlException' is not defined.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(93) : error BC30451: Name 'MessageBox' is not declared.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(113) : error BC30002: Type 'SqlCommand' is not defined.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(123) : error BC30002: Type 'SqlException' is not defined.
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(124) : error BC30451: Name 'MessageBox' is not declared.
Building satellite assemblies...
Satellite assemblies could not be built because the main project output is missing.

- Done -

Build: 0 succeeded, 1 failed, 0 skipped
I am so dissapointed with VB.NET standard edition, as it seems it can't generate new database, it can just connect to existing database that are already built up.
So I am curious whether MSDE can work independently to let me set up new database, but you ignored my question. I really feel depressed about this combination of VB.NET + MSDE.
You may search on your VS.NET help function for "Server Explorer", it will tell you VB.NET package doesn't have server node.

Thanks & Best regards,
Henry Li

|||Hello Ken,
Visual Basic.NET Standard Edition package is cheaper than VS.NET package. It has less functions than VS.NET package, and cost me $114.
On its package box it mentions it can't build device-based applications and solutions, can't build/debug/deploy powerful server software while VS.NET Professional can.

You said SQL Server Express 2005 is a new version of MSDE, I am just wondering whether it can work like Oracle Plus or Access which can let you have some windows to key in some data?

Thanks a lot!
Henry|||You need a imports system.data.sqlclient at the top of the code file.|||Sorry I thought vb.net standard had the server explorer. Here is a link to download the free sql server 2005 express.

http://msdn.microsoft.com/vstudio/express/sql/

Here is a link to the free management studio for sql server 2005 express

http://www.microsoft.com/downloads/details.aspx?FamilyId=82AFBD59-57A4-455E-A2D6-1D4C98D40F6E&displaylang=en|||Hello Ken,
Thanks a lot! This "imports system.data.sqlclient" really helps!
Now it reduces to only 2 errors left, which are:
--
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(54) : error BC30451: Name 'MessageBox' is not declared. (under the sub createDataBase()->Try/Catch)
c:\inetpub\wwwroot\test1\WebForm1.aspx.vb(61) : error BC30456: 'Text' is not a member of 'test1.WebForm1'. (under the sub CreateClientsTable() ->me.Text)
--

I think I might need another line of "imports something"?
Sorry I am a beginner of programming in sql. And want to try your code to see if it can pass through to connect to my MSDE.

By the way, I've downloaded the SQL Server Express and the management console but haven't installed them, and still downloading the 440MB Studio Web Develop Express under the Visual Studio 2005 Express which was issued for free download just 2 weeks ago. I hope this can take place of my VB.NET package.

Thanks a lot again!

Best regards,
Henry|||I wrote that as windows forms application. Messagebox.show is not supported in web application's neither is me.text. Try changing messagebox.show to response.write and me.text = to response.write("The string" & "<br>")|||Hi, I am new one in this forum. About question: "How to make tables & stored procedures in MSDE?", try with command line tool - osql. If you need more help, I am here. Or search MS site about it.

Stevan|||Hello Ken,
I've downloaded the Visual Web Developer 2005 Express which includes the SQL Server Express, so I installed them both.
The SQL Server 2005 Express is similar to the MSDE, although it has a configuration console, it still can't handle database.
Then I downloaded the SQL Management Studio Express, it's really good! It gives me free choices to deal with security, log-in, creation of new databases!

Then I created a database in the SQLEXPRESS via the management studio console, then in the Visual Web Dev Express I created connection with that new database, it connects!

Now I can creat tables and stored procedures either directly in the SQLEXPRESS's database (via the mgt studio), or indirectly in the Visual Web Dev Express within the database explorer window.

Now I don't use the VB.NET package anymore except if I need to create some windows exe files.

Thank you for your kindness!

Best wishes,
Henry Li|||Thank you Stevan,
I found my problem was some basic things that maybe many people took it for granted while I didn't know.
Actually I need to make a database in the MSDE first, and MSDE has no interface to let me set it up. I need the management software such as the SQL Management Studio 2005 Express.
Now I found this Mgt Studio Express is very useful, it can handle MSDE and SQLEXPRESS. I created some databases with the help of Mgt Studio Express, then I can create tables or procedures!

Best wishes,
Henry|||

Hi,

You can generate tables with osql command, i just need to indicate the command as following:

osql -U user -S server\instancename

then

1> create database test

2> GO

1>use test

2>GO

then you're inside of test database and you are ready to generate your table according you're needed

How to make auto-increment ID?

I've created a table. How do I modify the ID so that it isauto-increment? I'm using Microsoft SQL Server Management Studio toaccess my tables.You need to make the integer column an IDENTITY column. There is an option for that in the design mode for the table in SQL Server Management Studio.|||Thank you so much! I got it working now.

How to locate the database folder in sql 2005

We want to locate the database folder/files (where the databases are stored) like SQL Server Management Studio UI does,
when you click on attach database / Add.

The question is how to retrieve this folder/files programmatically (C# or VB, SMO?).

For example we want our application client to connect remotely to an SQL Server and attach a new database, using the folder/files obtained from the retrieved method.

If you run Profiler while you use SSMS you will see the code that it runs to populate the dialogs you open.

The code is t-sql so you will have to write your own C to do the same thing

|||

We tried profiler but we didn't find something to help us.

Thank you Anyway.

|||

Perhaps this will point you in the right direction:

SELECT physical_name
FROM sys.master_files
WHERE name = 'Northwind'

A little string manipulation and you should be fine.

|||Thank you but this is for a database already attached.|||

So..., if the database is not attached, then you can put it just about anywhere you wish.

There are 'default' locations, and there are the locations you decide to use. If you are not concerned about currently attached databases, and their locations, then what is the issue?

All servers will have at least master, tempdb, model, and msdb databases. If you can find where they are stored, then you can store your database there too.

|||I don't know the folder and the name of the database. That is why I want to locate it.|||

Remove the WHERE clause and get a list of all databases attached to the server, and their locations.

SELECT
name,
physical_name
FROM sys.master_files

Now... how to determine which one of these listed databases is the database you are seeking since you don't have a database name... You will be very, very, very lucky if there is nothing more than master, tempdb, model, msdb, -AND only the one database you seek.

Did I say, really, really, lucky...

Perhaps adding the following WHERE clause:

WHERE name = db_name()

But that may not always work, the (file) 'name' could be different from the logical database name; an improved query is:

SELECT
db_name( database_id )
name,
physical_name
FROM sys.master_files
WHERE database_id = db_id()

This last query should give you the logical database name, the filename, and the filepath.

|||

Verry verry sorry..

I don't know the folder and the name of the database. The database is NOT attached. That is why I want to locate it.

|||

GeoB wrote:

Verry verry sorry..

I don't know the folder and the name of the database. The database is NOT attached. That is why I want to locate it.

That would have been GOOD information to have earlier.

Since there is no certainly as to how a database file is named (it does not have to have a [mdf] suffix), you may have quite a problem there.

|||

When use SQL Server Management Studio UI you do know where the databases are stored ok?

I want to make something like that: http://www.kenix.eu/georgebakogiannis/LocateDatabaseFiles.png

|||

If you have a default instance on the box, then the default data and log locations are held in the registry at these locations:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\DefaultData
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\DefaultLog

However there's no guarantee that all database and log files will be found at these locations.

Chris

How to locate the database folder in sql 2005

We want to locate the database folder/files (where the databases are stored) like SQL Server Management Studio UI does,
when you click on attach database / Add.

The question is how to retrieve this folder/files programmatically (C# or VB, SMO?).

For example we want our application client to connect remotely to an SQL Server and attach a new database, using the folder/files obtained from the retrieved method.

If you run Profiler while you use SSMS you will see the code that it runs to populate the dialogs you open.

The code is t-sql so you will have to write your own C to do the same thing

|||

We tried profiler but we didn't find something to help us.

Thank you Anyway.

|||

Perhaps this will point you in the right direction:

SELECT physical_name
FROM sys.master_files
WHERE name = 'Northwind'

A little string manipulation and you should be fine.

|||Thank you but this is for a database already attached.|||

So..., if the database is not attached, then you can put it just about anywhere you wish.

There are 'default' locations, and there are the locations you decide to use. If you are not concerned about currently attached databases, and their locations, then what is the issue?

All servers will have at least master, tempdb, model, and msdb databases. If you can find where they are stored, then you can store your database there too.

|||I don't know the folder and the name of the database. That is why I want to locate it.|||

Remove the WHERE clause and get a list of all databases attached to the server, and their locations.

SELECT
name,
physical_name
FROM sys.master_files

Now... how to determine which one of these listed databases is the database you are seeking since you don't have a database name... You will be very, very, very lucky if there is nothing more than master, tempdb, model, msdb, -AND only the one database you seek.

Did I say, really, really, lucky...

Perhaps adding the following WHERE clause:

WHERE name = db_name()

But that may not always work, the (file) 'name' could be different from the logical database name; an improved query is:

SELECT
db_name( database_id )
name,
physical_name
FROM sys.master_files
WHERE database_id = db_id()

This last query should give you the logical database name, the filename, and the filepath.

|||

Verry verry sorry..

I don't know the folder and the name of the database. The database is NOT attached. That is why I want to locate it.

|||

GeoB wrote:

Verry verry sorry..

I don't know the folder and the name of the database. The database is NOT attached. That is why I want to locate it.

That would have been GOOD information to have earlier.

Since there is no certainly as to how a database file is named (it does not have to have a [mdf] suffix), you may have quite a problem there.

|||

When use SQL Server Management Studio UI you do know where the databases are stored ok?

I want to make something like that: http://www.kenix.eu/georgebakogiannis/LocateDatabaseFiles.png

|||

If you have a default instance on the box, then the default data and log locations are held in the registry at these locations:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\DefaultData
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\DefaultLog

However there's no guarantee that all database and log files will be found at these locations.

Chris

How to limit number of rows displayed in a report from a report designer

How to limit number of rows displayed in a report, i' am using report project in Visual studio 2005 to design my reports

How to use page navigation once i deploy the report on to report server..?

Hello Kkreddy:

I have searched it for you.

I am sorry I have not found a good answer for you.

Your problem seems to be a pure report server problem.

I will move your thread to the report server forum.

I believe you can get a more rapid and precise answer there.

Thank you very much.

How to limit number of rows displayed in a report from a report designer

How to limit number of rows displayed in a report, i' am using report project in Visual studio 2005 to design my reports

How to use page navigation once i deploy the report on to report server..?

Hello Kkreddy:

I have searched it for you.

I am sorry I have not found a good answer for you.

Your problem seems to be a pure report server problem.

I will move your thread to the report server forum.

I believe you can get a more rapid and precise answer there.

Thank you very much.