Use publishing to transfer MSSQL data (2) [figure] _ MySQL

Use publishing to transfer MSSQL data (2) [figure] Next, open it on the WordPad and change the database owner to dbo.

In this way, all accounts are changed to dbo.

In the next step, the script name is sqlscript.txt. it is best not to call it sqlscript. SQL, which will be described below.

Then, place the script to the website space through ftp.

Write a script. for example, name it runsql. aspx, and then run the script to restore the database.

<%

// 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://www.sohu.com/sqlscript.txt ";

// Connection string to the server you want to execute against

String connectionString = @ "Data Source = 11.1.1.1;

User ID = hdd; Password = dd; Initial Catalog = s603 ";

// Timeout of batches (in seconds)

Int timeout = 20000;

%>

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 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 (@ "cocould not close the connection. Error was {0}", e. ToString ()));

}

}

}

%>

Note:

String fileUrl = @ "http://www.sohu.com/sqlscript.txt ";

It is the user script address, because it is forbidden to obtain SQL in many spaces.

String fileUrl = @ "http://www.sohu.com/sqlscript. SQL ";

The system may fail to run. In this way, the database migration is completed.