darylbewise
Programmer
Hello all,
If this is the wrong place to be posting such a question, I am sorry.
I have just swapped servers from a single server solution which worked with MSSQL Express edition to a Master-slave solution which uses MSSQL Workgroup.
I need to update certain database tables on a daily basis. On the old server that used MSSQL Express, I used a asp.NET script (see code below) that ran and updated the required database tables.
However, when I run the same script on the new server, the script will run but never end. The database tables will be removed, but the new tables will never be added.
What is the best way to be updating MSSQL database tables using MSSQL workgroup?
Import code that worked on old server:
If this is the wrong place to be posting such a question, I am sorry.
I have just swapped servers from a single server solution which worked with MSSQL Express edition to a Master-slave solution which uses MSSQL Workgroup.
I need to update certain database tables on a daily basis. On the old server that used MSSQL Express, I used a asp.NET script (see code below) that ran and updated the required database tables.
However, when I run the same script on the new server, the script will run but never end. The database tables will be removed, but the new tables will never be added.
What is the best way to be updating MSSQL database tables using MSSQL workgroup?
Import code that worked on old server:
Code:
<%
// Sample code for executing a T-SQL file using an ASP.NET page
// Copyright (C) Microsoft Corporation, 2007. All rights reserved.
// Written as a sample with use in conjuction with the SQL Server Database Publishing Wizard
// For more information visit [URL unfurl="true"]http://www.codeplex.com/sqlhost/[/URL]
// **************************************************************************
// Note: Please ensure that you delete this page once your database has been published to the remote server
// **************************************************************************
%>
<%@ Page Language="C#" AutoEventWireup="true" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.IO" %>
<%@ Import Namespace="System.Net" %>
<%
// **************************************************************************
// Update these variables here
// **************************************************************************
// Url of the T-SQL file you want to run
string fileUrl = Server.MapPath("SQLFILE.sql");
// Connection string to the server you want to execute against
string connectionString = @"server=SERVER;Database=DATABASE;uid=USER;pwd=PASSWORD;";
// Timeout of batches (in seconds)
int timeout = 600;
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "[URL unfurl="true"]http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">[/URL]
<html xmlns="[URL unfurl="true"]http://www.w3.org/1999/xhtml"[/URL] >
<head runat="server">
<title>Executing T-SQL</title>
</head>
<body>
<form id="form1" runat="server">
<div>
</div>
</form>
<%
SqlConnection conn = null;
try
{
this.Response.Write(String.Format("Opening url {0}<BR>", fileUrl));
// read file
WebRequest request = WebRequest.Create(fileUrl);
using (StreamReader sr = new StreamReader(request.GetResponse().GetResponseStream()))
{
this.Response.Write("Connecting to SQL Server database...<BR>");
// Create new connection to database
conn = new SqlConnection(connectionString);
conn.Open();
while (!sr.EndOfStream)
{
StringBuilder sb = new StringBuilder();
SqlCommand cmd = conn.CreateCommand();
while (!sr.EndOfStream)
{
string s = sr.ReadLine();
if (s != null && s.ToUpper().Trim().Equals("GO"))
{
break;
}
sb.AppendLine(s);
}
// Execute T-SQL against the target database
cmd.CommandText = sb.ToString();
cmd.CommandTimeout = timeout;
cmd.ExecuteNonQuery();
}
}
this.Response.Write("T-SQL file executed successfully");
}
catch (Exception ex)
{
this.Response.Write(String.Format("An error occured: {0}", ex.ToString()));
}
finally
{
// Close out the connection
//
if (conn != null)
{
try
{
conn.Close();
conn.Dispose();
}
catch (Exception e)
{
this.Response.Write(String.Format(@"Could not close the connection. Error was {0}", e.ToString()));
}
}
}
%>
</body>
</html>
