Solved

Running scripts from vb.net to Sql Server 2005 express

Posted on 2011-03-04
3
595 Views
Last Modified: 2012-05-11
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
Comment
Question by:fordraiders
  • 2
3 Comments
 
LVL 13

Accepted Solution

by:
devlab2012 earned 500 total points
ID: 35037055
have you tried CommandTimeout property of SqlCommand object e.g.
cmd.CommandTimeout = 120;
0
 
LVL 3

Author Comment

by:fordraiders
ID: 35037422
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
 
LVL 3

Author Closing Comment

by:fordraiders
ID: 35037745
That worked Thanks !
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql help 5 53
sql Audit table 3 67
SQL query to summarize items per month 5 60
Help with simplifying SQL 6 53
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question