Page 1 of 1

Transferring blob from One MSSQL Database to Another

Posted: Fri Aug 04, 2006 12:19 pm
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]

Posted: Fri Aug 04, 2006 1:45 pm
by RobertGonzalez
Do you have to use code to do this or can you DTS it?

Posted: Sat Aug 05, 2006 8:00 am
by carolinaseo
I don't have DTS access at this point... :(

Posted: Sat Aug 05, 2006 11:31 am
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.

Posted: Sat Aug 05, 2006 1:16 pm
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.

Posted: Sat Aug 05, 2006 5:13 pm
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.

Posted: Sun Aug 06, 2006 12:20 pm
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.

Posted: Sun Aug 06, 2006 2:45 pm
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.