[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 617
  • Last Modified:

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


0
Fordraiders
Asked:
Fordraiders
  • 2
1 Solution
 
devlab2012Commented:
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

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now