Transferring blob from One MSSQL Database to Another

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
carolinaseo
Forum Newbie
Posts: 4
Joined: Sun Jul 02, 2006 9:07 am
Location: Sneads Ferry, North Carolina USA
Contact:

Transferring blob from One MSSQL Database to Another

Post by carolinaseo »

Everah | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Please HELP!!!    

I have the current need to move MS SQL Server 2005 data from an external server to a local host (e.g. shared) windows server. The environment is PHP and I do have the connections created for both external and internal databases.

The question is how to transfer the image blob to the local MS Sql Server database from the external MS Sql Server database. I am already able to process all the other fields within the database except the image (e.g. blob) field.

The jpg Ole header (e.g. JFIF exists there)  data is within the file. Can anyone show me how to do this? 


Example code selecting the data below:

Code: Select all

// Specify the login info here. (later move to external file)
// Select from database first
$msserver = '999.99.999.999'; 
$usrname = 'myusername'; 
$passwrd = 'mypassword'; 
$database = 'mydatabase'; 
$table = 'mytable'; 
// Build SQL statement here that has the blob field (medium_photo)
// medium_photo is a jpg file 320 x 208
$sql = 'SELECT TOP 100 id, medium_photo FROM ' . $table;

// Open the MS Sql Server Connection
$result = opensqldata($msserver,$usrname,$passwrd,$database, $sql);

// $result declared global within opensqldata function
$nrows = mssql_num_rows($result);


function opensqldata($msserver,$usrname,$passwrd,$database, $sql) {

global $msserver, $usrname, $passwrd, $database, $sql, $result, $dbcon;
// Open the MS Sql Server Connection
$dbcon = mssql_connect($msserver,$usrname,$passwrd); 
if ($dbcon) {
echo "<font color='blue'>Connection to $msserver was successful.</font><hr>"; 
} else {
echo "<font color='red'>Error: ".mssql_get_last_message()."</font><hr>"; 
} // End if ($dbcon)

// Specify database here
mssql_select_db($database);

// Build SQL statement here
// $sql = 'SELECT TOP 10 * FROM ' . $sel_table;
$result = mssql_query($sql);
return $result;

} // End function open-sql-data

Everah | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Do you have to use code to do this or can you DTS it?
carolinaseo
Forum Newbie
Posts: 4
Joined: Sun Jul 02, 2006 9:07 am
Location: Sneads Ferry, North Carolina USA
Contact:

Post by carolinaseo »

I don't have DTS access at this point... :(
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Can you data dump your database and install it by SQL file on the other server? Using code to do this is possible, but I would consider it a last resort with all the data transferring tools available.
carolinaseo
Forum Newbie
Posts: 4
Joined: Sun Jul 02, 2006 9:07 am
Location: Sneads Ferry, North Carolina USA
Contact:

Post by carolinaseo »

The external server belongs to someone else (e.g. other large business). No, I cannot do a data dump. The only course of action to extract the data is via a data connection.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

I supposed then, that you are going to have to connect to your current DB, pull what you need to pull, connect to the other db and insert what needs to be inserted. Seems like a lot of work, but if that is what you need to do, then I guess that is what you need to do.
carolinaseo
Forum Newbie
Posts: 4
Joined: Sun Jul 02, 2006 9:07 am
Location: Sneads Ferry, North Carolina USA
Contact:

Post by carolinaseo »

Agreed. The question is how to get the image blob data from the external MS Sql Server to the internal MS Sql Server so that it works properly. Just performing a query does not allow the insert of the results into the internal database.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

If you external DB is accessible by a network address, you can connect to both from the web server that is housing the local db. Then it is just a matter of running a script that grabs from one and inserts into the other.
Post Reply