Insert data from one DB into another on a different server - servers not linked

» php » Insert data from one DB into another on a different server - servers not linked

By : user2957002

Date : November 22 2020, 03:03 PM

fixed the issue. Will look into that further I am trying to copy some selected data from one DB, set on a server that is expiring on Jan 1st, into another so that I can preserve it. The second DB is on a different server, different host etc - I no longer have access to the first one, but only know the various locations of the items I require.

code :

// Connection 1
$link = mysql_connect('XXXXaddressXXXX', 'XXXXuserXXXX', 'XXXXXXpwXXXXX');  //edited out the access details for security
mysql_select_db('sfi_db', $link) or die("Can't connect to first database");

// Connection 2
$dBIP='XXXAddressOfSecondServerXXXX';
$dBADN='XXXXUserofSecondServerXXXX';
$dBPWD='XXXXPWofSecondServer';
$dBDBS="XXXXDBnameXXXX";

$CONNECTW=mysql_connect($dBIP,$dBADN,$dBPWD);
mysql_select_db($dBDBS, $CONNECTW)  or die("Can't connect to second database");;


$query = "SELECT t.entry_id, t.title, t.year, t.month, t.day, d.field_id_1, d.field_id_2, d.field_id_3 FROM sfi_db.exp_channel_titles t LEFT JOIN sfi_db.exp_channel_data d ON t.entry_id = d.entry_id WHERE d.field_id_5='Latest-Updates'";
$results=mysql_query($query,$link); 
while($row=mysql_fetch_row($results)){
    $id=$row[0];
    $title = $row[1];
    $year = $row[2];
    $month = $row[3];
    $day = $row[4];
    $summary = $row[5];
    $body = $row[6];
    $image = $row[7];


    mysql_query("INSERT INTO $dBDBS.newsimport (id,title,year,month,day,summary,body,image_url) VALUES ('$id','$title','$year','$month','$day','$summary','$body','$image')",$CONNECTW);

    }

Share :

Move data accross SQL Servers with DTS packages or linked servers

By : user446909

Date : March 29 2020, 07:55 AM

Any of those help you can use BCP to export and then import data.

How to access Postgres data from SQL Server (linked servers)

By : Naren

Date : March 29 2020, 07:55 AM

this one helps. OPENQUERY is definitely a way to do it; what issues are you having with dynamic SQL?
Perhaps create a temp table in server 1, import a filtered subset of the PostgreSQL data using a parameterized query into that temp table, and then join onto your temp table in your final SQL query.

How to create a table using the INSERT INTO clause using linked servers in SQL Server Management Studio

By : Sakshi

Date : March 29 2020, 07:55 AM

With these it helps There are two mistakes in your query
1.INTO clause support maximum of 2 prefixes. You cannot include SERVER NAME

code :

DATABASE_NAME.SCHEMA_NAME.TABLE_NAME

SELECT *
INTO [Reporting].[dbo].[NewTable]
FROM [linked].[Main].[dbo].[Orders];

Test connection of linked servers and return connected linked server(s)

By : Dimitris T

Date : March 29 2020, 07:55 AM

Does that help Changed a few things in your code... give this a shot.
IsOff just means the connection failed. I added a where clause to look at Linked Servers only I've never seen a cursor as a variable so I removed that and the other variables I didn't need Used a TRY/CATCH since sys.sp_testlinkedserver raises and exception if it doesn't success. Return the reason why it failed Stored all the results into a table variable and only returned the failures

code :

DECLARE @name NVARCHAR(100)

declare @table table (IsOff int, ServerName varchar(100), TheError varchar(4000))


DECLARE getid CURSOR FOR
SELECT  name FROM sys.servers where is_linked = 1


OPEN getid

FETCH NEXT FROM getid INTO @name
WHILE @@FETCH_STATUS = 0

BEGIN

    begin try
        exec sys.sp_testlinkedserver @name
    end try

    begin catch
        insert into @table
        values
        (1,@name,ERROR_MESSAGE())
    end catch

FETCH NEXT FROM getid INTO @name

END

CLOSE getid
DEALLOCATE getid

select ServerName, TheError from @table where IsOff = 1

How do you remove linked server metadata cache when there are no defined linked servers?

By : Adam W. P. Hackett

Date : March 29 2020, 07:55 AM

I wish this helpful for you Backup the data and drop the offending table in the Progress OpenEdge system. Create a new table and load the data.
Now, the issues is resolved.