Hi All,
I am running SQL2000. Can anyone tell me how I can use the contents of
a table as parameters for a stored procedure that resides on a
different SQL server?
I have a table:
Customers:
Cust_ID
Cust_Name
Cust_Contact
Cust_Phone
I need to execute a stored procedure and pass each of the above as
parameters:
@CustID, @CustName, @CustContact,@C ustPhone
The stored procedure also returns an error code.
Thanks,
Danny 3 2065
I need to execute a stored procedure and pass each of the above as
parameters:
One method is to select the values into local variables and then pass those
as parameters. For example:
SELECT
@CustID = CustID,
@CustName = CustName,
@CustContact = CustContact,
@CustPhone = CustPhone
FROM dbo.Customers
WHERE CustID = 1
EXEC @ReturnCode = dbo.MyProcedure
@CustID = @CustID,
@CustName = @CustName,
@CustContact = @CustContact,
@CustPhone = @CustPhone
--
Hope this helps.
Dan Guzman
SQL Server MVP
<Da***********@ gmail.comwrote in message
news:11******** **************@ j27g2000cwj.goo glegroups.com.. .
Hi All,
I am running SQL2000. Can anyone tell me how I can use the contents of
a table as parameters for a stored procedure that resides on a
different SQL server?
I have a table:
Customers:
Cust_ID
Cust_Name
Cust_Contact
Cust_Phone
I need to execute a stored procedure and pass each of the above as
parameters:
@CustID, @CustName, @CustContact,@C ustPhone
The stored procedure also returns an error code.
Thanks,
Danny
On Jan 29, 10:40 pm, "Dan Guzman" <guzma...@nospa m-
online.sbcgloba l.netwrote:
I need to execute a stored procedure and pass each of the above as
parameters:
One method is to select the values into local variables and then pass those
as parameters. For example:
SELECT
@CustID = CustID,
@CustName = CustName,
@CustContact = CustContact,
@CustPhone = CustPhone
FROM dbo.Customers
WHERE CustID = 1
EXEC @ReturnCode = dbo.MyProcedure
@CustID = @CustID,
@CustName = @CustName,
@CustContact = @CustContact,
@CustPhone = @CustPhone
--
Hope this helps.
Dan Guzman
SQL Server MVP
<Daniel.Pea...@ gmail.comwrote in message
news:11******** **************@ j27g2000cwj.goo glegroups.com.. .
Hi All,
I am running SQL2000. Can anyone tell me how I can use the contents of
a table as parameters for a stored procedure that resides on a
different SQL server?
I have a table:
Customers:
Cust_ID
Cust_Name
Cust_Contact
Cust_Phone
I need to execute a stored procedure and pass each of the above as
parameters:
@CustID, @CustName, @CustContact,@C ustPhone
The stored procedure also returns an error code.
Thanks,
Danny- Hide quoted text -
- Show quoted text -
Thanks for that Dan,
Will this not just process the first record CustID=1? How would I go
about processing the whole table? Do I have to build a client
application to process a loop or can I proccess this on the SQL
server? Da***********@g mail.com (Da***********@ gmail.com) writes:
Will this not just process the first record CustID=1? How would I go
about processing the whole table? Do I have to build a client
application to process a loop or can I proccess this on the SQL
server?
You could set up a cursor. However, using loops is not a very efficient
use of SQL Server. It may be better to replace the stored procedure
with statements that operates on the entire table at once. Since the
stored procedure is another server, this is not exactly trivial, depending
a bit of what's in that remote procedure.
The way to write a cursor would be:
DECLARE custcur INSENSITIVE CURSOR FOR
SELECT CustID, CustName, CustContact, CustPhone
FROM Customers
OPEN custcur
WHILE 1 = 1
BEGIN
FETCH custcur INTO @CustID, @CustName, @CustContact, @CustPhone
IF @@fetch_status <0
BREAK
EXEC SERVER.db.dbo.r emote_sp @CustID, @CustName, @CustContact, @CustPhone
END
DEALLOCATE custcur
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: rvdw |
last post by:
Hi All,
I've a serious problem with executing stored procedures (SQL2000) from
an Access db (version 97). After executing a stored procedure ,
msaccess hangs. The whole call to the procedure is running fine, but
immediatly after this msaccess hangs, when the focus goes back to the
calling form ?
Has anyone any idea what i can test or look at.
Thanks a lot !!!!!!
|
by: Eugene Anthony |
last post by:
Method 1:
set rs = Server.CreateObject("ADODB.Recordset")
objConn.usp_RetrieveCategories rs
Method 2:
set rs = objConn.Execute("usp_RetriveCategories")
Which method is considered to efficient. Is it method 1 or method 2?.
|
by: rvdw |
last post by:
Hi All,
I've a serious problem with executing stored procedures (SQL2000) from
an Access db (version 97). After executing a stored procedure ,
msaccess hangs. The whole call to the procedure is running fine, but
immediatly after this msaccess hangs, when the focus goes back to the
calling form ?
Has anyone any idea what i can test or look at.
Thanks a lot !!!!!!
|
by: Tim Marshall |
last post by:
I was following the thread "Re: Access Treeview - Is it Safe Yet?" with
interest and on reading the post describing Lauren Quantrell's
SmartTree, I've run into something I don't understand: Stored
Procedures. I thought stored pricedures were an Oracle/MS SQL Server
thing and don't know how they work with Access Jet. I've looked at some
of the help on stored procedures in A2003, but really don't understand
what's going on.
Can someone...
|
by: Ville Huovinen |
last post by:
Platform: Windows 2003 Server (MS SQL Server 2003 SP3)
Language: C#
Problem:
My stored procedures times out randomly, some proces works fine when using
them from C#, and some generate SqlException which states that the server
has timed out. The problem isn't in server, neither in stored procedures
because they work fine in Sql Server's enterprise manager, but when calling
from code they are slow and take about 60 seconds before...
| |
by: A1 Ronen |
last post by:
Hi all
I got problem regarding executing all stored procedure through
common procedures where we have different parameter with different
names, type and data type
The Function is as follows
Where strProcString is procedure name and strParamString is all
parameters concatenated by @
|
by: Goog79 |
last post by:
Hi everyone,
first time here, so I'm sorry if this has been covered already ages
ago. :(
I am trying to learn T-SQL and Stored Procedures and bought the book
on these topics by Djan Sunderic, Publisher McGraw Hill/Osborne. I'm
already stuck on my first Stored Procedure and getting error messages
that I cannot understand. I've already tried Google and Microsoft
online to no avail. I do have the .NET Framework on my system and use
|
by: SQLusername |
last post by:
I am having trouble executing a series of 4 stored procedures from VB. The connection code connects and the first 3 stored procedures run through, although the 4th procedure stops running mid execution. No errors are reported to VB. When I run the series of procedures in the SQL Server Query Analyzer everything completes as it should. Anyone have any suggestions on what could be the problem?
|
by: debasisdas |
last post by:
This thread contains some of the sample code showing the method of executing Oracle stored procedures and functions from VB .
Hope the user finds them useful.
Oracle Procedure with only IN type as parameter mode.
========================================
CREATE OR REPLACE PROCEDURE DEPTINS
(
DNO DEPT.DEPTNO%TYPE,
DN DEPT.DNAME%TYPE,
|
by: Carlton Kirby |
last post by:
I need to execute a job on a SQL Express 2005 instance (no SQLAgent).
The job will be executed manually by a user, so it doesn't need to be
scheduled to run automatically. I thought I could execute the job
through a stored procedure, but it appears that SQL Agent is necessary
even for that.
The job was given to me by a software vendor to add EDI capabilities
to an accounting package, but the assumption was made that a full
version of...
|
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...
| |
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...
|
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...
|
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,...
|
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...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
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...
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |