SQL syntax

[AlanLinden]

Ok, so I have been able to create a databse via code using the following:

 Try
            
            Dim oFile As System.IO.File
            Dim oRead As System.IO.StreamReader
            Dim EntireFile As String
            oRead = oFile.OpenText("C:\aTest.txt")
            EntireFile = oRead.ReadToEnd()
            Dim aNew As New cxDbAccess
            aNew.dataAccessWithSP(EntireFile)
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try

And the textfile has the syntax "CREATE DATABASE Tester".

So, how would I use another textfile, or perhaps the same one with the DB create, to make tables and stored procedures? This is most likely a simple solution, but I haven't been able to get it. Any and all help appreciated.

 

[Stonkie]

There is a way to do almost everything db related using SQL and the syntax is not so complex but what I usually do is I open Sql Server Management Studio, make the database the way I want it, then right click on the database -> tasks -> create scripts... Then I just export what I need into an SQL query that will create the tables, relations, etc.

If that's not enough, you can still edit the SQL from there.

 

[AlanLinden]

Fair enough, but I did indeed use the scripting tool, but I get an error. OK, how does one specify that they want to create tables and other things in a certain database? I tried "USE dbName" but that doesn't work. I tried putting the create table script right after the create DB line, and that doesn't work. So, what next? Arrgh, I know this answer is right under my nose, but I canna find it.

 

[Stonkie]

If you are using Sql Server Management Studio 2005, there is a combobox over the script that allows you to tell it which database to run the script on.

Otherwise, in your application, you can set it in the connection string or I think there is a DataBase property on the Connection object.

Then, in the SQL, this seems to be the way, but I never used this myself : http://doc.ddart.net/mssql/sql70/ua-uz_7.htm

 

[naveentp]

Try using the osql utility to run the scripts to create the tables, views, constraints and other objects.... you can check this link for reference on osql

http://technet.microsoft.com/en-us/library/aa213090.aspx

and for handling the use database name problem: you can try using fully qualified name in the create syntax like CREATE TABLE databasename.dbo.tablename...not sure of this...