Running scripts from vb.net to Sql Server 2005 express

vb.net 2008
sql server express sp4
running sql serve express on my one machine



I have a button on a form that executes this code:

Private Sub Button25_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button25.Click
        Dim connectionString As String = "Server=.\SQLEXPRESS;AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Cart_Data.mdf;Initial Catalog=iCart_Data;Persist Security Info=True;User ID=sa;Password=veeger;Connect Timeout=0;User Instance=False"
        Dim conn As New SqlClient.SqlConnection(connectionString)
       
        conn.Open()

        ExecuteSQLScript("C:\Program Files\CrsEnt\Scripts\Truncate and shrink All Databases2.sql", conn)
       

        conn.Close()
        conn = Nothing
       
        MsgBox("Completed Updating Sql Server", MsgBoxStyle.Information)
    End Sub



The script is here:


USE [DESCMATCH]
GO
DBCC SHRINKFILE (DescMatch_Data, 100)
GO
DBCC SHRINKDATABASE ( 'DescMatch' ,NOTRUNCATE)
DBCC SHRINKDATABASE ( 'DescMatch' ,TRUNCATEONLY )
GO
use [DescMatchGisXref]
GO
DBCC SHRINKFILE (DescMatchGisXref, 100)
GO
DBCC SHRINKDATABASE ( 'DescMatchGisXref' ,NOTRUNCATE)
DBCC SHRINKDATABASE ( 'DescMatchGisXref' ,TRUNCATEONLY )

Problem:
I get a timeout error in .net but the process runs fine in sql server 2005 express.

I have set all the properties in sql server to not time out.


Thanks
fordraiders


LVL 3
FordraidersAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
devlab2012Connect With a Mentor Commented:
have you tried CommandTimeout property of SqlCommand object e.g.
cmd.CommandTimeout = 120;
0
 
FordraidersAuthor Commented:
ok where exactly should I place it: from  here:

Dim cmd As New SqlClient.SqlCommand
        Dim Reader As System.IO.StreamReader

        Try
            cmd.CommandType = CommandType.Text
            cmd.Connection = conn

            Reader = New System.IO.StreamReader(Filename)
            Dim s As String = Reader.ReadToEnd
            s = Replace(s, "GO", "~") 'Replace GO with a "~". Split only works with char
            Dim delimiter() As Char = "~".ToCharArray
            Dim SQL() As String = s.Split(delimiter) 'Now split the different SQL statements into an array
            For I As Integer = 0 To UBound(SQL) 'Loop through array, executing each statement separately
                cmd.CommandText = SQL(I)
                cmd.ExecuteNonQuery()
            Next

            Reader.Close()
            Reader = Nothing
            cmd = Nothing

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
0
 
FordraidersAuthor Commented:
That worked Thanks !
0
All Courses

From novice to tech pro — start learning today.