473,898 Members | 2,969 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

SQL server multi-statement table function w/ openquery

zachster17
30 New Member
Hello all,

I'm trying to write a multi-statement table function that returns a table of addresses from a remote database (Oracle) using OpenQuery and I'm having a hard time getting it to work with the 1 variable constraint it has (provider ID)

The code is below:

Expand|Select|Wrap|Line Numbers
  1. SET ANSI_NULLS ON
  2. GO
  3. SET QUOTED_IDENTIFIER ON
  4. GO
  5.  
  6. CREATE FUNCTION pmf_udfProviderChangeAddressTable
  7. (
  8.     -- Add the parameters for the function here
  9.     @ProviderID varchar(20)
  10. )
  11. RETURNS @AddressTable TABLE
  12. (
  13.     -- Add the column definitions for the TABLE variable here
  14.     ContactType varchar(4),
  15.     ContactAddr1 varchar(100),
  16.     ContactAddr2 varchar(100),
  17.     ContactCity varchar(40),
  18.     ContactState varchar(3),
  19.     ContactZip varchar(10),
  20.     ContactPhone varchar(20),
  21.     ContactFax varchar(20)
  22. )
  23. AS
  24. BEGIN
  25.  
  26.     DECLARE @SQL As varchar(500)
  27.     SET @SQL = 'SELECT CONTACT_TYPE As ContactType, FIRM_NAME As ContactAddr1,
  28.     ADDRESS_LINE1 As ContactAddr2, CITY AS ContactCity, STATE As ContactState, ZIP_CODE As ContactZip,
  29.     PHONE_NBR As ContactPhone, FAX_NBR As ContactFax FROM NET$.CONTACTS
  30.     WHERE PROV_ID = ''' + @ProviderID + ''' AND
  31.     (CONTACT_TYPE = ''01'' OR CONTACT_TYPE = ''02'')'
  32.  
  33.  
  34.     -- Add the SELECT statement with parameter references here
  35.     EXEC('INSERT INTO @AddressTable SELECT * FROM OPENQUERY(ENCP, ' + @SQL + ')'
  36.  
  37.     RETURN 
  38. END
  39. GO
  40.  
I keep getting this error:

Msg 156, Level 15, State 1, Procedure pmf_udfProvider ChangeAddressTa ble, Line 33
Incorrect syntax near the keyword 'RETURN'.


Let me know if you need any more info.

Thank you!

Zach
Feb 19 '08 #1
1 4383
ck9663
2,878 Recognized Expert Specialist
I think you're missing one close parenthesis on your EXEC statement. You have ')' to close the OPENQUERY, but missing one to close the EXEC statement.

-- CK
Feb 19 '08 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

7
3319
by: rdh | last post by:
Hi all, I am in process of developing a Server in C++ supporting multiple protocols. The server will be exposing various functionalities, and the clients can communicate over any of the protocols may be TCP, IPX, SAP, NETBEUI to access the server to access the functionalities exposed. The server doesnot know in advance which client is using what protocol. ALSO, ALL THE INTERACTION WILL BE MADE OVER XML. example my server has...
0
4557
by: Zorba.GR | last post by:
IBM DB2 Connect Enterprise Edition v8.2, other IBM DB2 (32 bit, 64 bit) (MULTiOS, Windows, Linux, Solaris), IBM iSoft Commerce Suite Server Enterprise v3.2.01, IBM Tivoli Storage Resource Manager Express Edition v1.3.2 Win, IBM Tivoli System Automation v1.2.0 Linux, IBM Tivoli Workload Scheduler Virtualized Data Centers v8.2 , other IBM Tivoli CDs, WEBSPHERE EVERYPLACE MOBILE PORTAL v5.0 - ALTIUM , other IBM WebSphere Business CDs...
10
2379
by: Zeng | last post by:
I just realized that there are 2 modes for garbage collection modes: server gs and workstation gs. Would someone know how I can go about changing the mode for my web application written in C#? I want to make sure it's using server gs mode. Thank you for any comment or advice. -zeng
17
9246
by: Danieltbt05 | last post by:
just installed SQL server 2000 and using my client , i can't locate the server. I used SQL query analyzer to search but no servers were found. Error message is as below Server : Msg17,level 16, state1 SQL server does not exist or denied access.
2
4899
by: NiponW | last post by:
Hi, I have SQL SERVER 2000 SP4 Enterprise , Windows 2003 Enterprise on Xeon 4 Processors (now with multi-threading CPU) and I have questions which seem weirds to me (used to have the same config without Multi-Threading) as following: 1. SQL Server see 8 CPUs. Is this because of multi-threading CPUs ? 2. when use SP_WHO2 ACTIVE ,
4
2231
by: dorpnospam | last post by:
We have an old but very critical application that was written in VB 6 against Access 95 dbs. We need to ditch this decrepit old unstable db platform but we are trying to determine the best migration path to SQL Server. I am researching options. I started by upgrading a sample db to Access 2003 and update the VB 6 app to use DAO 3.6. That works well. Next I tried upsizing the application to SQL Server and that works as well but it...
9
3020
by: TC | last post by:
Like a lot of database developers, I often choose Jet for the back- end. I'm starting to worry about what will happen when Jet is deprecated. Ostensibly, Jet users like me must switch to SQL Server (or MSDE / SQL Express), but there's something I just don't understand. Without Jet, how will we create file server database applications? In other words, how will we create multi-user apps which use a file server to share data and don't...
0
1696
by: uninvitedm | last post by:
I'm making a JSP page where (ideally) you select files to upload, each selected file is added to a listbox. The form is submit, (along with some other parameters), and the files are uploaded. They arent uploaded (written) tradionally to a server, I just need the file's input stream to pass to a method elsewhere (which will upload to a database). The page has a file-input type to locate files to upload. Once a file is selected (onChange()),...
2
5031
by: praveenkumarvpk | last post by:
Hi friends Please help me! Following is my servlet-code import java.util.Enumeration; import javax.servlet.*; import javax.servlet.http.*; import java.io.*; import com.oreilly.servlet.MultipartRequest;
14
2669
by: Guillermo_Lopez | last post by:
Hello, Our company has developed several Access applications for our clients and we wish to expand to use a database server. We wish to use Access as the front end application and SQL Server 2005 as the back end Databes Server. Making a Server-Client application database. I'm very new to SQL Server so my concern is how the licensing works for a developer like us. I am aware that we can purchase the Developer edition for $49 USD. Our...
0
9993
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
10857
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
10946
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
9658
agi2029
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...
0
7187
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5877
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
6076
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4705
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
3
3303
bsmnconsultancy
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...

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.