Pushing Data to a Different server on Different network

  • I have 2 servers .Server1 (xp) has SQL server 2005 express version installed in it and has databases and tables. Server 2 (windows 2003)is SQL server 2000 standard edition on a different network .

    I need to push data from server1 to server2 every night . I cannot use linked server connection since I have the express version . I have set up a DSN connection to server2.

    But I don't know how to use that in a T-SQL .

    From Server2 I can connect to Server1 and pull data using linked server . But our security rules won't allow me to do that.

    Network people asked me to connect from server1 to server2 and push data. They are going to turn on the firewall so that only server1 can talk to server2 not viceversa.

    Please help..

    thanks

  • Are you doing a full incremental refresh? Without linked servers I think you need a windows service or application that transfers the data.

  • Server1 (2005 Express is a temparory storage. So I can clean the table after I push the data to 2000.

    I thought If I can connect to 2000 using T-SQL commands , I can put everything in a batch file and execute it every night.

  • Can you use openrowset, read more abour Openrowset here. Click Here

    This connection does not requires a Linked server, you can run quires directly onto secondary.

    Hope you can use this feature on Express... not sure if Express gives this feature...

    Hope this helps,

    Imran Mohammed.

  • Thanks. It works just fine..

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply