Solved

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

Posted on 2010-08-23
4
1,060 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
  • 2
4 Comments
 
LVL 12

Expert Comment

by:mcv22
ID: 33506868
0
 
LVL 4

Accepted Solution

by:
pbarry1 earned 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

864 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now