?
Solved

How do I run Firebird SQL Select Statements from a MS SQL Server Stored Procedure

Posted on 2010-08-23
4
Medium Priority
?
1,113 Views
Last Modified: 2012-06-21
Hi.  I am writing a stored procedure in SQL Server that I plan to use to execute some customer-provided Firebird SQL Select Statements.  I have not seen these Firebird SQL Statements, and I assume for purposes of this question that what they do is irrelevant.  If this assumption is incorrect, please advise and I will attempt to spell out the purpose of the Firebird SQL Statements.

What I am trying to do is "wrap" these Firebird SQL Statements inside my stored procedure, which will be executed by MS SQL Server 2005.  I have an ODBC connection to the Firebird database, and I have created a linked server environment in MS SQL Server that successful connects to the Firebird database.  Can someone show me how to call or use those Firebird statements within my stored procedure?  Thanks.

A high level example:

if @IsLookup = 1
     BEGIN
        SET @DataToFirebird = @AccountNumber
        --At this point I want to know how to call the Firebird scripts
        --Now, I will use returned data from the Firebird scripts in my stored procedure
      END

Again, thanks, and please let me know what information is needed; I am sure I have left out pertinent information, but I am not familiar at all with Firebird, and only some experience with SQL Server.
0
Comment
Question by:jhstevens
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 12

Expert Comment

by:mcv22
ID: 33506868
0
 
LVL 4

Accepted Solution

by:
pbarry1 earned 2000 total points
ID: 33550226
Hi,

there's an easy way to do it.  Just use the "EXEC ('Firebird SQL Statement') AT YourFirebirdLinkedServerName" syntax

Here's a sample:

--Create a temporary table
CREATE TABLE #test (c1 VARCHAR(255))

-- Insert the result in the temporary table #test.  Note: in this sample, the Firebird statement returns only a column, but you can return more if you want.  Just adjust the #test definition and the insert)
INSERT INTO #test (c1)
  EXEC ('Your Firebird SQL statement') AT YourFirebirdLinkedServer
 
-- Display the results
SELECT * FROM #test

-- Delete the temporary table
DROP TABLE #test
0
 

Author Comment

by:jhstevens
ID: 33588669
I wanted to dig a bit deeper here.  I have tested the accepted solution and it works perfectly.  What I would like to know is if it is possible to set the return value from "EXEC ('Your Firebird SQL statement') AT YourFirebirdLinkedServer" into a variable as opposed to a temp table.  The reason is that the stored procedure may be called along multiple threads, and this will (I believe) cause a problem with the temp tables (creating an existing table, dropping, etc).  So, is is possible to insert this return value into a variable?  Thanks.

Heath
0
 
LVL 4

Expert Comment

by:pbarry1
ID: 33589510
Hi, here's how to do it with a variable:

-- Declare a variable of type TABLE. Will contain your firebird resultset containing the return value
DECLARE @vResult TABLE (ReturnValue INT)
-- Declare a variable of type INT.  Will contain your return code (assuming that the firebird resultset contains only a return code value)
DECLARE @vReturnCode int

-- Insert the FirebirdResultset into the TABLE variable
INSERT INTO @vResult (ReturnValue)
  EXEC ('Your Firebird SQL Statement') AT YourFirebirdLinkedServer
 
-- Put return value in a variable that is easy to handle as a real return code
SELECT TOP 1 @vReturnCode = ReturnValue FROM @vResult

-- Display the return code
PRINT STR(@vReturnCode)

0

Featured Post

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question