BilalShouman


hello I need to script my Db, tables and storedprocedures. so i can create another same DB

Dim con As New SqlConnection(DBClass.Config.DBString)
Dim cmdName As String = File.OpenText("pro.sql").ReadToEnd()
Dim cmd As New SqlCommand(cmdName, con)
cmd.CommandType = CommandType.Text
con.Open()
Try
cmd.ExecuteNonQuery()
MessageBox.Show("DB Created")
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try

con.Close()

Any suggestions




Re: Sql Script

Barry Andrew


Hi,

You may use the following;

Private Sub CreateDBBtn_Click(sender As Object, e As System.EventArgs)
' Create a connection
conn = New SqlConnection(ConnectionString)
' Open the connection
If conn.State <> ConnectionState.Open Then
conn.Open()
End If
Dim sql As String = "CREATE DATABASE mydb ON PRIMARY" + "(Name=test_data, filename = 'C:\mysql\mydb_data.mdf', size=3," + "maxsize=5, filegrowth=10%)log on" + "(name=mydbb_log, filename='C:\mysql\mydb_log.ldf',size=3," + "maxsize=20,filegrowth=1)"
ExecuteSQLStmt(sql)
msgbox("DB Created!")
End Sub 'CreateDBBtn_Click






Re: Sql Script

Barry Andrew

After you have run the above you may then use the following;

Dim sr As StreamReader = New StreamReader("C:\script.sql")
Dim script As String = sr.ReadToEnd

Dim SMOServer As Server = New Server
Dim db As Database = SMOServer.Databases("northwind")
db.ExecuteNonQuery(script)







Re: Sql Script

BilalShouman

what is the name space for Database and server
Im using vb.net 2003, sqlserver 2000

Thank you




Re: Sql Script

Barry Andrew

Imports System.Data
Imports System.Data.SQL





Re: Sql Script

BilalShouman

No NameSpace called System.Data.Sql
Second I told u im using vb.net 2003, and sqlserver

any help





Re: Sql Script

BilalShouman

No NameSpace called System.Data.Sql
Second I told u im using vb.net 2003, and sqlserver 2000

any help





Re: Sql Script

Barry Andrew

ok, you sure there is no system.data.sqlclient in 2003

I really dont remember.

What happens if you drop a table adapter on a form from the designer





Re: Sql Script

BilalShouman

Man u said

imports system.data.sql

dim db as database
dim server as server





Re: Sql Script

Barry Andrew

im very sorry, im not sure I understand this question.

Have you placed a table adapter on a form then looked at the top to see what is imported

Alternatively once you have placed a table adapter on the form, on the solution explorer click the "Show All Files" option and expand the references node, this will show you what you are referencing.





Re: Sql Script

BilalShouman

Are there any TableAdapters in vb.net 2003



Re: Sql Script

BilalShouman

Hi man sorry for bothering

Your Code:

Dim sr As StreamReader = New StreamReader("C:\script.sql")
Dim script As String = sr.ReadToEnd

Dim SMOServer As Server = New Server
Dim db As Database = SMOServer.Databases("northwind")
db.ExecuteNonQuery(script)

------------------------
Database and server are syntax errors, do I need to import anything, u said something about table adapter,
is there any table adapter in vb.net 2003

Plz help





Re: Sql Script

BilalShouman

My problem was solved

Imports System.IO
Imports SQLDMO --C:\Program Files\Microsoft SQL Server\80\Tools\Binn

Private Sub test()
Dim sr As StreamReader = New StreamReader("test.sql")
Dim script As String = sr.ReadToEnd
Dim SMOServer As New SQLServer
SMOServer.LoginSecure =
True
SMOServer.Connect("(local)")
Dim db As Database = SMOServer.Databases.Item("test")
db.ExecuteImmediate(script)
End Sub





Re: Sql Script

Barry Andrew

So your fine now and everything works