CodePlex Home
Register | Sign In | CodePlex Home

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 3:32 AM by Hvasishth, version 7
Comments
Odegaard Jan 11 2007 at 5: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!).

limingxu22102 Jan 11 2007 at 11: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.

TheMenace Jan 17 2007 at 8: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.

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

mcdermon Apr 25 2007 at 12:14 PM 
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?

sdpcalifornia May 29 2007 at 9: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

DavidM Jun 21 2007 at 10: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'

DavidM Jun 22 2007 at 6: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.

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

thegreensurfer Apr 30 2008 at 11: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

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

itorres008 Sep 25 2008 at 1: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

itorres008 Sep 25 2008 at 2: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

bbrooks Apr 29 at 8: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)

raghad84 Sep 7 at 12:10 PM 
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
Updating...
© 2006-2009 Microsoft | About CodePlex | Privacy Statement | Terms of Use | Code of Conduct | Advertise With Us | Version 2009.10.27.15987