MSDE + SQL Scrip + Store Procedure?

[wangdong]

I am currently developing a VB.NET application with a SQL Server 2000 installed. Now, I want to deploy my application to another single user computer, but this time I want to use the MSDE as a database engine. The question is:

What is the best way to create all the tables? Is there any way to run the SQL Server generated SQL script?

How to create procedure?

If anyone can give a clue, it would be very helpful.

Thank you.

 

[shatch]

I would generate the SQL script as text and once MSDE has been installed on the other computer, run the script using oSQL

For example

osql /S (local) /E /i createappdata.sql /o createappdata.txt

What this means

/S (local) - use the server on the local machine
/E - integrated windows authentication
/i xxxx - Input script (to be run)
/o xxxx - Output file (results - useful for debugging)

Hope that helps

 

[wangdong]

Is that include all the stored procedure files as well?

 

[shatch]

It will if you choose to script those with the database.

Right click on DB in enterprise manager
All tasks > Generate SQL Script
Click SHow All
Check Script all objects

That will include views, stored procs, tables, functions, etc, etc

 

[AccordingToDale]

If you already have the database created on an instance of SQL Server, you can detach the db, copy it, move it to the new machine and attach it to the instance of MSDE. It's pretty quick.

Dale

 

[wangdong]

How do I know if a table is built sucessfully?

I've got a txt result file displayed as

1> 2> 3> 1> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 1> 2>

Also, If the tables have the relationship links between then, do I have to creat the table from the bottom level?

 

[shatch]

It looks like everything worked OK, the test file would contain error messages if it hadnt. The relationships would have been created already if you chose to include them in the SQL sript generated from the exisitng database. If you didnt, recreate the SQL script and check the relationships, and primary/foreign keys boxes

Simon

 

[wangdong]

I did everything again. I used the command line:

osql /s(KING) /E /i dbo.tb_Dir.TAB /o rslt.txt

to create table. However, when I run the program, I have still got this erro message:

Object Name 'tb_Dir' invalid.

I think the database is create fine, the only problem is the table creation doesn't work.

Does anyone know how to check the existing tables on MSDE?

 

[AccordingToDale]

You can use SQL CLient tools (EM or QA) to connect to the instance of MSDE.

Or you can use osql as a command line editor.

One thing I notice about your osql line:

osql /s(KING) /E /i dbo.tb_Dir.TAB /o rslt.txt

/i - should be the name of the input file - the script you want to run. is that the actual name of your script??

Dale

 

[wangdong]

Yes, dbo.tb_Dir.TAB is the actual SQL script, which is generated from SQL Server 2000.

 

[shatch]

Can you try renaming the file so that there is no dbo. at the beginning, there is a possibility that this is causing problems

 

[chrissie1]

why not just make a program that reads the textfile and then splits it up, on the "go" elements and then executes each command via an sqlcommand. you would only need to do this once and can be done via the installer.

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting