Create ms-SQL database

[TipGiver]

There is an old thread started by me: thread796-1205618

The database had only 2 tables. Below is shown what i got by using the ms-sql server management studo express, when i right clicked the table and selected script table as -> create to.

I would like to know: I want to create programmatically the database with the 2 tables
1. How? Do i have to save the 2 .sql files and somehow execute them?
2. The below starts with USE... which means that it will go search and find the db. By right clicking the database now, selecting script database as -> create to.

If i am correct, the .sql files should be 3. How do i execute these sql files? Furthermore.. how can i change the path?

USE [D:\MY VISUAL BASIC.NET PROJECTS\EE3.2\ENTOLESELEGXOU3\ENTOLESELEGXOU\DATABASE.MDF]
GO
/****** Object:  Table [dbo].[ComTable]    Script Date: 01/15/2007 19:30:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ComTable](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ReleaseDate] [nchar](10) COLLATE Latin1_General_CI_AI NULL,
	[Kriteria] [nvarchar](100) COLLATE Latin1_General_CI_AI NULL,
	[Observations] [nvarchar](300) COLLATE Latin1_General_CI_AI NULL,
	[Checkers] [nvarchar](200) COLLATE Latin1_General_CI_AI NULL,
	[BookDef] [nchar](1) COLLATE Latin1_General_CI_AI NULL,
	[AA] [int] NULL,
 CONSTRAINT [PK_ComTable] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

TIA

 

[RonRepp]

You can execute this just like a SQL string using ADO. However, I've had to drop the GO keyword before this works.

Dim Con as ADODB.Connection=New ADODB.Connection
dim ConString as String
Con.ConnectionString=ConString
Con.Open (ConString)

SQL="USE [D:\MY VISUAL BASIC.NET PROJECTS\EE3.2\ENTOLESELEGXOU3\ENTOLESELEGXOU\DATABASE.MDF] " & _
"SET ANSI_NULLS ON " & _
"SET QUOTED_IDENTIFIER ON " & _
"CREATE TABLE [dbo].[ComTable]( " & _
    "[ID] [int] IDENTITY(1,1) NOT NULL, " & _
    "[ReleaseDate] [nchar](10) COLLATE Latin1_General_CI_AI NULL, " & _
    "[Kriteria] [nvarchar](100) COLLATE Latin1_General_CI_AI NULL, " & _
    "[Observations] [nvarchar](300) COLLATE Latin1_General_CI_AI NULL, " & _
    "[Checkers] [nvarchar](200) COLLATE Latin1_General_CI_AI NULL, " & _
    "[BookDef] [nchar](1) COLLATE Latin1_General_CI_AI NULL, " & _
    "[AA] [int] NULL, " & _
"CONSTRAINT [PK_ComTable] PRIMARY KEY CLUSTERED " & _
"( " & _
    "[ID] ASC " & _
")WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] " & _
") ON [PRIMARY]"

Con.Execute (SQL)
Con.Close
Con=Nothing

I hope this helps

PS: You could also open this with a streamreader and concatenate the lines.

HTH


Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

 

[TipGiver]

Hi Ron,

It is not working for me. Even if it did, the USE would fail because the .mdf file does not exist.
I try it through the management studio.

For example:
I type: create database [c:\ee.mdf] and when i hit execute sql i get

Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\c:\ee.mdf.mdf" failed with the operating system error 123(error not found).
Msg 1802, Level 16, State 1, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

 

[TipGiver]

I am also searching the web for examples but no luck so far. I want to create an empty database somewhere in the disk, and then use the USE keyword and create the tables etc. The difficult part is how to create the db

 

[RonRepp]

I only use the keyword USE when it pertains to a DB within the server on which I am working, and only if I am scripting a Create Table\View.

Also, I've learned to use this bit of code at the beginning, in case the DB exists:

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = 'DB_NAME')
	DROP DATABASE [DB_NAME]

Afterwards you would script your other objects such as tables, views, logins, etc.

I hope this helps

Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

 

[RonRepp]

This would be a fully coded Create DB SQL. All you'd need to do is enclose it in quotes and concatenate the strings.

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'DBA_Name')
	DROP DATABASE [DBA_Name]


CREATE DATABASE [DBA_Name]  ON (NAME = N'DBA_Name', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\data\DBA_Name.mdf' , SIZE = 61, FILEGROWTH = 10%) LOG ON (NAME = N'DBA_Name_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\data\DBA_Name_log.LDF' , SIZE = 16, FILEGROWTH = 10%)
 COLLATE SQL_Latin1_General_CP1_CI_AS


exec sp_dboption N'DBA_Name', N'autoclose', N'false'


exec sp_dboption N'DBA_Name', N'bulkcopy', N'false'


exec sp_dboption N'DBA_Name', N'trunc. log', N'false'


exec sp_dboption N'DBA_Name', N'torn page detection', N'true'


exec sp_dboption N'DBA_Name', N'read only', N'false'


exec sp_dboption N'DBA_Name', N'dbo use', N'false'


exec sp_dboption N'DBA_Name', N'single', N'false'


exec sp_dboption N'DBA_Name', N'autoshrink', N'false'


exec sp_dboption N'DBA_Name', N'ANSI null default', N'false'


exec sp_dboption N'DBA_Name', N'recursive triggers', N'false'


exec sp_dboption N'DBA_Name', N'ANSI nulls', N'false'


exec sp_dboption N'DBA_Name', N'concat null yields null', N'false'


exec sp_dboption N'DBA_Name', N'cursor close on commit', N'false'


exec sp_dboption N'DBA_Name', N'default to local cursor', N'false'


exec sp_dboption N'DBA_Name', N'quoted identifier', N'false'


exec sp_dboption N'DBA_Name', N'ANSI warnings', N'false'


exec sp_dboption N'DBA_Name', N'auto create statistics', N'true'


exec sp_dboption N'DBA_Name', N'auto update statistics', N'true'


if( ( (@@microsoftversion / power(2, 24) = 8) and (@@microsoftversion & 0xffff >= 724) ) or ( (@@microsoftversion / power(2, 24) = 7) and (@@microsoftversion & 0xffff >= 1082) ) )
	exec sp_dboption N'DBA_Name', N'db chaining', N'false'


use [DBA_Name]

I hope this helps

Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

 

[TipGiver]

hello again.

I runs without errors until the last command which is the USE command.

With the USE i get:

Msg 911, Level 16, State 1, Line 67
Could not locate entry in sysdatabases for database 'DBA_Name'. No entry found with that name. Make sure that the name is entered correctly.

Even if i delete the USE... the db is not created

 

[RonRepp]

I just ran this code through query analyzer and it worked fine. This is SQL Server 2000.

I did get an error also on the USE keyword, so I just commented it out.

When I checked for the new DB, it appeared to have not been made until I refreshed the server. (Right click in the server [on tables or DB] and click Refresh)

I hope this helps.

Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

 

[RonRepp]

TipGiver:

Here's what I did: I took the SQL pasted above and placed it in an actual SQL ready for Query Analyzer. I removed all the GO statements and the USE statement at the bottom.

I then ran it through a form load event and it worked perfectly.

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ''open the sql statement
        Dim SR As StreamReader = New StreamReader(Application.StartupPath & "\DBA.sql")
        Dim SQL As String
        Dim CS As String = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
                "Persist Security Info=False;Initial Catalog=ThreeSource;" & _
                "Data Source=ONLINE_SERVER"
        Dim Con As ADODB.Connection = New ADODB.Connection

        Do While SR.Peek() >= 0
            SQL += SR.ReadLine() & vbCrLf
        Loop
        SR.Close()
        Con.ConnectionString = CS
        Con.Open(CS)
        Con.Execute(SQL)
        Con.Close()
        Con = Nothing
        


    End Sub

HTH

Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

 

[TipGiver]

Ron !

That exaclty i was going to post. Remove the Go and USE, create an sql connection without ofcource the attachDBfilename and execute the command:

My testing code is a little different than you, but it finally works !

        Dim cn As New System.Data.SqlClient.SqlConnection("Data Source=.\SQLEXPRESS;Integrated Security=True;User Instance=True;Pooling='FALSE'")

        cn.Open()

        Dim sr As New IO.StreamReader("c:\b.txt")
        Dim f As String = sr.ReadToEnd
        sr.Close()

        Dim c As New SqlClient.SqlCommand(f, cn)

        c.ExecuteNonQuery()
        cn.Close()

 

[TipGiver]

I should add that:

- What ever i did through the management studio did not work. I mean the try to create. Anything else, such as insert select, etc went ok, BUT the database should exist and by identified as a system database or ti exist in the database folder at the left.
- I used vb.net express for this demonstration (sr.ReadToEnd).

Regards!

 

[stevexff]

TipGiver

There is a management API for SQL Server, SQL-DMO.

http://msdn2.microsoft.com/en-us/library/aa174487(SQL.80).aspx

SQL Distributed Management Objects (SQL-DMO) encapsulate the objects found in Microsoft® SQL Server™ 2000 databases. SQL-DMO allows applications written in languages that support Automation or COM to administer all parts of a SQL Server installation. SQL-DMO is the application programming interface (API) used by SQL Server Enterprise Manager in SQL Server 2000; therefore, applications using SQL-DMO can perform all functions performed by SQL Server Enterprise Manager.

Might be worth a try?

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[RonRepp]

TipGiver:

I'm glad you got it.

Hope everything else works out for you.

Ron



Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

 

[TipGiver]

Ron,
I am glad too, although i dont know why it failed from the management tool.

Stecexff,
i'll take a look at it.

 

[RonRepp]

SQL DMO is a great tool; perfect for rifling through the collections of SQL Server. I haven't used it much except for pulling the collections; especially for tables/views. It is more reliable than the ADO OpenSchema command.

Check into it.

Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.