473,692 Members | 3,149 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Stored Procedure to upload data from one db to remote db

I have a situation where I want to take data from a local sql server
2000 db and update a remote database. I have the sql all set, was
wondering if this can be done in a timed interval with a stored
procedure on the local db.

Thanks in advance for your time
Dean-O
Jul 20 '05 #1
4 4098

"rockie12" <ro******@dtnsp eed.net> wrote in message
news:d1******** *************** ***@posting.goo gle.com...
I have a situation where I want to take data from a local sql server
2000 db and update a remote database. I have the sql all set, was
wondering if this can be done in a timed interval with a stored
procedure on the local db.

Thanks in advance for your time
Dean-O


It depends what data you want to copy. If you want to copy all changes from
the source database to the target, then you could look at log shipping or
replication. If you want to copy only a subset of data, then replication
would still be an option (you can replicate data conditionally), but you
might find it easier to write your own procedure.

Also, you need to bear in mind what the network connection is like. If both
servers are on the same LAN, then you could use linked servers to INSERT
directly from one to the other. But if you have a less reliable connection,
then you would probably want a different approach.

If this isn't helpful, you might want to give some more detail about exactly
what you need to do. You could also post in
microsoft.publi c.sqlserver.rep lication if you're interested in that
possibility.

Simon
Jul 20 '05 #2
Hi

Simon has given you some alternatives, other alternatives may be DTS or
running the SQL (as a stored procedure). With SQL Server, if you wish to
schedule a timed event then usually SQLServerAgent is used to schedule
these. Agent jobs can be configured using Enterprise manager or using the
stored procedures sp_add_job, sp_add_jobstep and sp_add_jobsched ule.

Each step can run a specific type of process (subsystem) such as 'CMDEXEC' -
an operating system command or executable. This could be used to run DTSRun
which can start a DTS job another alternative solution! 'TSQL' - a
transact-SQL statement (which can be a stored procedure).

John

"rockie12" <ro******@dtnsp eed.net> wrote in message
news:d1******** *************** ***@posting.goo gle.com...
I have a situation where I want to take data from a local sql server
2000 db and update a remote database. I have the sql all set, was
wondering if this can be done in a timed interval with a stored
procedure on the local db.

Thanks in advance for your time
Dean-O

Jul 20 '05 #3
More detail on the situation. The databases are one different
servers. I want to set up a situation where the remote database will
have a procedure that will run when the home base database is
available and update the home base database. Sort of a sync function.

I am new to sql server. I have done much with MySql and have taken
care of this type of function with a Java program that runs as a
windows event.

Thanks in advance
Dean-o

"John Bell" <jb************ @hotmail.com> wrote in message news:<fw******* **************@ news-text.cableinet. net>...
Hi

Simon has given you some alternatives, other alternatives may be DTS or
running the SQL (as a stored procedure). With SQL Server, if you wish to
schedule a timed event then usually SQLServerAgent is used to schedule
these. Agent jobs can be configured using Enterprise manager or using the
stored procedures sp_add_job, sp_add_jobstep and sp_add_jobsched ule.

Each step can run a specific type of process (subsystem) such as 'CMDEXEC' -
an operating system command or executable. This could be used to run DTSRun
which can start a DTS job another alternative solution! 'TSQL' - a
transact-SQL statement (which can be a stored procedure).

John

"rockie12" <ro******@dtnsp eed.net> wrote in message
news:d1******** *************** ***@posting.goo gle.com...
I have a situation where I want to take data from a local sql server
2000 db and update a remote database. I have the sql all set, was
wondering if this can be done in a timed interval with a stored
procedure on the local db.

Thanks in advance for your time
Dean-O

Jul 20 '05 #4
rockie12 (ro******@dtnsp eed.net) writes:
More detail on the situation. The databases are one different
servers. I want to set up a situation where the remote database will
have a procedure that will run when the home base database is
available and update the home base database. Sort of a sync function.


A simple-minded approach is to use linked servers:

UPDATE HOMESRV.databas e.dbo.tbl
SET col1 = local.col1,
col2 = local.col2,
...
FROM HOMESRV.databas e.dbo.tbl home
JOIN localtbl local ON home.keycol = local.keycol

You may need to add INSERT and DELETE as well.

You set up a linked server with sp_addlinkedser ver.

I'm not really sure whether this is the best way. Maybe replication is
better. In that case, you don't have to bother about starting the
process once you get connected. Then again, replication takes some time
to set up.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
1384
by: Luis | last post by:
I'm using a SQL Server 2000 stored procedure similar to the one below to upload data to a database. This data is collected from the user on a number of asp pages and stored in session variables (that's the way I inherited the system...). When the user has captured all the info and clicks the submit button the stored procedure is called and it uploads the data to the database. This normally works perfectly except for a few occassions when...
0
5578
by: Charley | last post by:
Hi, My local procedure needs to call a remote stored procedure which dblink name is built at the run time depending on the input parameter to the local procedure. Therefore depending on the input parameter, the remote procedure may reside at different remote databases. Try to avoid hardcode value in "if/elsif". Thinking to use dbms_sql and pass the dynamically built remote procedure to it but somehow it doesn't work. Any idea?
1
8618
by: sbh | last post by:
I need to copy data from a table on one Oracle server to another. Scenario: Need to create a stored procedure in server a, database aa that will copy data from server b, database bb, table bbb to server a, database aa, table aaa. Let's say I have a user on server b, database bb named userB (password pw) that has rights to table bbb. Can anyone help me with the syntax - just for the connection part? Is this possible with a stored...
0
6697
by: Nashat Wanly | last post by:
HOW TO: Call a Parameterized Stored Procedure by Using ADO.NET and Visual C# .NET View products that this article applies to. This article was previously published under Q310070 For a Microsoft Visual Basic .NET version of this article, see 308049. For a Microsoft Visual C++ .NET version of this article, see 310071. For a Microsoft Visual J# .NET version of this article, see 320627. This article refers to the following Microsoft .NET...
2
2396
by: Tim.D | last post by:
Hello people, Thanks to Serge, PM, Knut and a host of others I have now successfully completed my very first SQL Stored Procedure. Takes some 13mins or so to run, but that was expected as it does a fairly significant number of small single select and inserts from various cursors. I am very pleased with the results and can find no errors after a few days debugging a couple of never ending loops. Interestingly though, while processing...
2
5451
by: Dino L. | last post by:
How can I run stored procedure (MSSQL) ?
18
4344
by: Jen | last post by:
I'm using Microsoft's own VB.NET FTP Example: http://support.microsoft.com/default.aspx?scid=kb;en-us;832679 I can get the program to create directories, change directories, etc., but I can't get it to upload a file to the FTP server. I just get a "Cannot connect to remote server" error after this TRY: s = New Socket(AddressFamily.InterNetwork, SocketType.Stream, ProtocolType.Tcp)
5
1801
by: Pedro Vera | last post by:
I am helping somebody setup one of the asp.net starter kits. I converted it from sql express to sql server with no real issues, and I got it running local perfectly. On my first attempt to run it remotely I noticed that a lot of the queries had dbo. hardcoded and that SQL server on the remote server was not allowing my sql account access to these. I removed all mentions of dbo. and again it still runs local. I have posted it to the...
14
4589
by: jehugaleahsa | last post by:
Hello: I am working with Oracle .NET Stored Procedures. I would like to know how to return the results of a SELECT statement. I have tried returning a OracleRefCursor and a DataTable, but nothing seems to work. What data type must I return for this to be accepted as .NET stored procedure?
0
8611
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9094
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8970
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8814
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8812
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6468
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4332
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4565
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2989
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.