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
<%@ 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://<>/< >.sql";
// Connection string to the server you want to execute against
string connectionString = @"<>";
// Timeout of batches (in seconds)
int timeout = 600;
%>
Executing T-SQL
<%
SqlConnection conn = null;
try
{
this.Response.Write(String.Format("Opening url {0}
", fileUrl));
// read file
WebRequest request = WebRequest.Create(fileUrl);
using (StreamReader sr = new StreamReader(request.GetResponse().GetResponseStream()))
{
this.Response.Write("Connecting to SQL Server database...
");
// 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()));
}
}
}
%>
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.
