[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
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,153 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

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.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

650 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