With this approach, you can use the Database Publishing Wizard to generate a T-SQL file from your local database. Then, you can upload the script to your hosting provider, and use the sample ASP.NET page provided to execute the code below.
This approach is useful in the following circumstances:
* Your hosting provider has not deployed the Database Publishing Services, enabling simple publishing of your SQL Server database
* Your hosting provider does not have a T-SQL script execution window or the T-SQL script generated by the Database Publishing Wizard is too large to paste into the T-SQL script execution window
Here is the code, just copy it then paste to your RunSQL.aspx
<%
// 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 http://www.codeplex.com/sqlhost/
// **************************************************************************
// 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 = @"http://<<YourDomainName>>/<<YourFileName>>.sql";
// Connection string to the server you want to execute against
string connectionString = @"<<Your Connection String>>";
// Timeout of batches (in seconds)
int timeout = 600;
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<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>
Below are the instructions to use this approach:
1. Run the Database Publishing Wizard to generate a T-SQL script file for your local database
2. Using FTP (or another approach if applicable), upload this T-SQL file to your hosting account
3. Download the sample ASP.NET page by clicking on this link: RunSQL.aspx
4. Edit the ASPX page and change the values of the variables fileUrl and connectionString as follows:
1. fileUrl should be the url of the T-SQL file you uploaded. For example if your domain name is www.mydomain.Com, then the url would be http://www.mydomain.com/File.Sql
2. connectionString should be the connection string of your hosted SQL Server database
5. Upload the ASPX page to your hosting account
6. Point your web browser to the ASPX page you uploaded. When this page has completed loading, your database should now be populated in the remote SQL Server database
7. Important: Delete the T-SQL file and ASPX page in your hosting account. This will prevent others from reading your data or tampering with your database.
Reference URL: Upload T-SQL and execute at your hosting provider using an ASP.NET page
