Upload T-SQL and execute at your hosting provider using an ASP.NET page

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 this file.

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

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.

Last edited May 24, 2007 at 2:32 AM by Hvasishth, version 7

Comments

anilkumar200686 Feb 6, 2013 at 10:47 AM 
i got error like this please give the solution


Opening url http://bimabadi.host-ed.me/LICPREMIUMS.MDF.sql
Connecting to SQL Server database...
An error occured: System.Data.SqlClient.SqlException: Server does not exist or connection refused. ---> Mono.Data.Tds.Protocol.TdsInternalException: Server does not exist or connection refused. ---> System.Net.Sockets.SocketException: Connection refused at System.Net.Sockets.Socket.Connect (System.Net.EndPoint remoteEP) [0x00000] in :0 at System.Net.Sockets.Socket+Worker.Connect () [0x00000] in :0 --- End of inner exception stack trace --- at Mono.Data.Tds.Protocol.TdsComm..ctor (System.String dataSource, Int32 port, Int32 packetSize, Int32 timeout, TdsVersion tdsVersion) [0x00000] in :0 at Mono.Data.Tds.Protocol.Tds..ctor (System.String dataSource, Int32 port, Int32 packetSize, Int32 timeout, TdsVersion tdsVersion) [0x00000] in :0 at Mono.Data.Tds.Protocol.Tds70..ctor (System.String server, Int32 port, Int32 packetSize, Int32 timeout, TdsVersion version) [0x00000] in :0 at Mono.Data.Tds.Protocol.Tds80..ctor (System.String server, Int32 port, Int32 packetSize, Int32 timeout) [0x00000] in :0 at Mono.Data.Tds.Protocol.TdsConnectionPoolManager.CreateConnection (Mono.Data.Tds.Protocol.TdsConnectionInfo info) [0x00000] in :0 at Mono.Data.Tds.Protocol.TdsConnectionPool.GetConnection () [0x00000] in :0 at System.Data.SqlClient.SqlConnection.Open () [0x00000] in :0 --- End of inner exception stack trace --- at System.Data.SqlClient.SqlConnection.Open () [0x00000] in :0 at (wrapper remoting-invoke-with-check) System.Data.SqlClient.SqlConnection:Open () at ASP.runsql_aspx.__RenderTree (System.Web.UI.HtmlTextWriter __output, System.Web.UI.Control parameterContainer) [0x00000] in :0

honest2men May 22, 2010 at 8:34 AM 
i got this error while trying to execute the script can somebody help me out on how to set my connection string
Opening url C:\Inetpub\vhosts\chosenfamilyint.org\httpdocs\App_data\c3f.sql
Connecting to SQL Server database...
An error occured: System.Data.SqlClient.SqlException: Login failed for user 'chosenfa password=SN2ojYtap'. at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at ASP.runsql_aspx.__Render__control1(HtmlTextWriter __w, Control parameterContainer)

cucsoi Mar 31, 2010 at 4:23 AM 
Replace the <<YOUR_SCRIPTFILE>> marker and associated connection-string markers with the correct values for your hosted configuration. Note that unless you know the fully qualified path of the .SQL file, you'll probably want to use ASP.NET's Server.MapPath(fileName) method to calculate the absolute path of the relative .SQL file path in your web application. For example:


// Filename of the T-SQL file you want to run
string fileName = Server.MapPath("personal.SQL");

// Connection string to the server you want to execute against
string connectionString = @"Server=server123;User ID=user123;Password=password123;Initial Catalog=MyDBName123";

// Timeout of batches (in seconds)
int timeout = 600;

Full guide:
http://weblogs.asp.net/scottgu/archive/2007/01/11/tip-trick-how-to-upload-a-sql-file-to-a-hoster-and-execute-it-to-deploy-a-sql-database.aspx

harshul Mar 13, 2010 at 12:20 PM 
Problem / Issue :
There is something odd I came across when I run the script.
The newline characters in the script were getting replaced with some junk characters so it was not executed well and was getting an error like this -
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause ...

Solution: I think this is due to the encoding of my SQL file but could not figure out and tried various encoding formats viz Unicode, UTF-8 etc. No luck with any of them.
So, finally I just make my SQL Script file into .TXT file and everything run smoothly without any error!
It took me a day to figure out about whats wrong with my script! I hope this may helpful to someone!

raghad84 Sep 7, 2009 at 11:10 AM 
Hello,
I have the same error :Opening url C:\inetpub\wwwroot\newClub.mdf.sql
Connecting to SQL Server database...
An error occured: System.Data.SqlClient.SqlException: The SELECT permission was denied on the object 'objects', database 'mssqlsystemresource', schema 'sys'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at ASP.runsql_aspx.__Render__control1(HtmlTextWriter __w, Control parameterContainer)

can any one help in th solution,
Thanks

bbrooks Apr 29, 2009 at 7:28 PM 
I am getting a frustrating error message i cant fix please help. here it is

Opening url C:\inetpub\wwwroot\newClub.mdf.sql
Connecting to SQL Server database...
An error occured: System.Data.SqlClient.SqlException: The SELECT permission was denied on the object 'objects', database 'mssqlsystemresource', schema 'sys'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at ASP.runsql_aspx.__Render__control1(HtmlTextWriter __w, Control parameterContainer)

itorres008 Sep 25, 2008 at 1:10 PM 
404 - not found for the whatever.sql
In my case, I see the file is clearly there, so I thought the server might not "serve" it because of the file extension (mime/type).
So I renamed the whatever.sql to whatever.txt and the file was found and processed. Note that the file is parsed and processed by the runsql.aspx as a text file anyway, so the file extension is not important.

HTH
Ivan Torres
thegrennsurfer and panjkov

itorres008 Sep 25, 2008 at 12:42 PM 
I was able to use this method succesfully. I had used the Wizard many times, but for some (as yet unknown) reason the process failed today around 12 times with timeout errors, TDS errors and others using diferent source databases and target servers. I needed a soolutions and tried this.

My observations in hopes it helps someone later.
- The script generation, uploading and execution was 3 times faster than the Wizard (which takes 50 miutes) my .sql file was only 3.4 Mb and uploaded quickly
- On execution of runsql.aspx I found the presence of config.web, global.asax and anything that executes in your application can prevent your aspx to even execute. (I tried it on a clean app folder and it worked, but need to fnid out exactly which files to rename and prevent from executing in order to use the procedure regularly. Or run from another web in the server)
- I needed a config.web with only the option to show errors on (<customErrors mode="Off"/>) to allow for debugging

Thanks for the tip and the aspx!

Ivan Torres

panjkov Aug 13, 2008 at 10:14 PM 
i have same error as thegreensurfer. my database is in app_data.

thegreensurfer Apr 30, 2008 at 10:47 AM 
I get error message:

Opening url http://****.com/Registrations.sql
An error occured: System.Net.WebException: The remote server returned an error: (404) Not Found. at System.Net.HttpWebRequest.GetResponse() at ASP.runsql_aspx.__Render__control1(HtmlTextWriter __w, Control parameterContainer)

please can you help

pauljames Feb 11, 2008 at 10:47 AM 
What if my site was built with lanuage vb ...your page is in c#

DavidM Jun 22, 2007 at 5:27 AM 
Solved it! Appears the RunSQL.aspx file doesn't like the 1.x framework. However, it ran perfectly on the 2.0 framwork. Good luck.

DavidM Jun 21, 2007 at 9:37 PM 
Exact same issue as mcdermon from April 25 ... how about posting a solution to this. Thanks.

error message: 'System.IO.StreamReader' does not contain a definition for 'EndOfStream'

sdpcalifornia May 29, 2007 at 8:04 AM 
Hello, I get the following error. Any thoughts?
"An error occured: System.UriFormatException: Invalid URI: The format of the URI could not be determined. at System.Uri.CreateThis(String uri, Boolean dontEscape, UriKind uriKind) at System.Net.WebRequest.Create(String requestUriString) at ASP.runsql_aspx.__Render__control1(HtmlTextWriter __w, Control parameterContainer) "
Thanks

mcdermon Apr 25, 2007 at 11:14 AM 
Hello. I am a complete newbie at this! I am getting this error message when I run the page:-

'System.IO.StreamReader' does not contain a definition for 'EndOfStream'

at line 58 of the page. I have entered in my details, but I cannot get it to run. I am unfamiliar with C# as I normally code in VB. Any suggestions?

alexd Jan 19, 2007 at 6:48 PM 
Odegaard: We are ASP.NET newbies.... so forgive us for the improper code. :)

TheMenace Jan 17, 2007 at 7:21 AM 
Odegaard -> I think for a script like this, inline scripting is fine, not for a larger application but for a small utility, no problem. Furthermore it's easy for Rookie programmers to understand.

limingxu22102 Jan 11, 2007 at 10:21 PM 
this script won't work in a hosting enviornment, you need to grant access to the db user running the script

I posted the code here http://limingxu.com/blogs/xu_web_chronicle/archive/2007/01/11/217.aspx, you guys should integrate this into th runsql. At least for ppl like me.

Odegaard Jan 11, 2007 at 4:15 PM 
Maybe you should read up in the <script runat="server"> tag.
Inline scripting is soooooo 90's (and has no place in an ASP.NET page!).