Solved

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

Posted on 2010-08-23
4
1,091 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 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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

707 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