Workaround needed for SQL Server 2000 limitations, when data collected from many servers

  • Dear All,

    I have the following problem:

    A client (asp code) is calling a stored procedure on Server1.

    This sp (returns a recordset) collects some data from Server1, and in addition it needs some other data from Server2.

    Server2 then gets the data (a recordset) from an Oracle database. Server1 (nor the client) is not allowed to query from the Oracle, access is granted only for Server2.

    Server1 and Server 2 are SQL Server 2000.

    Server 2 gets the data from the Oracle using OPENQUERY combined with sp_executesql, because the OPENQUERY cannot be used with dynamic built sql statement.

    I tried to put the returned value coming from Oracle into a varchar(8000) variable/output parameter, but sometimes 8000 is too small.

    The SP on Server2 can return a recordset, but I cannot capture it in the SP on Server1.

    I cannot use a function on Server2, as functions cannot execute sp_executesql.

    I tried also somehow make it work with text data type (and xml), but I cannot put the recordset content on Server2 into the text or ntext variable.

    This is also not working:

    create table #tmp (col1 varchar(50), col2 int)

    insert #tmp exec SpOnServer2 'param1','param2'

    select * from #tmp

    drop table #tmp

    because I get this error message:

    Server: Msg 7391, Level 16, State 1, Line 2

    The operation could not be performed because the OLE DB provider 'MSDAORA' was unable to begin a distributed transaction.

    OLE DB error trace [OLE/DB Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x8004d01b].

    Basically I do not need a transaction here, but can I skip it somehow?

    Is there a solution or workaround to pass the result to the SP on Server1?

    Many thanks!

  • This is going a bit out of my realm here, as I consider this more of a DBA question, but since nobody else is biting, I'll give it a shot:

    A "Distributed Transaction" isn't a transaction in the sense that you're thinking of. That's actually a server option. You need to modify the configuration of your servers to allow distributed transactions.

    There are probably other workarounds if that's not an option for you, but to pick a viable one, we need to know a bit more about this task. Is concurrency an issue?

    Seth Phelabaum


    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • What is really annoying me is that I can see the result many ways on Server2, if I execute e.g. in Query Analyzer:

    1. exec SPOnServer2 'param1', 'param2'

    2. or I can "print" the result in the Messages tab

    But I cannot reach the same result e.g. in an SP on Server1.

    But thanks for the hint, I am not familiar with the 'Distributed Transaction' settings, I need to look after. I hope there is an other way because we are not allowed to modify the settings of the Oracle server (currently I am not sure where we need to change the 'Distributed Transaction' settings)

  • Hi,

    I tried to understand what you wanted to achieve, I guess you need to do this..

    Create a Linked Server on Server1 with the security permissions that can execute SP's on the Database in Server2.

    When you are successfully able to execute the SP from Server1 then you need to write script (SP)

    to use the Data returned by SP on Server2 and use Union / Inner Join the record set that you have on Server1.


    Bru Medishetty

    Blog -- LearnSQLWithBru

    Join on Facebook Page Facebook.comLearnSQLWithBru

    Twitter -- BruMedishetty

  • Sorry, I know my problem description is not so simply, but the problem is also a bit more difficult:)

    So the data needs to go on this way back:

    OracleDB -> Server2 -> Server1 -> client(asp page)

    There is no problem with creating the linked servers, and calling one SP on the other server. So SPOnServer1 can call SPOnServer2, there is no permission issue.

    The problem is that I cannot send back the result from the Oracle to Server1, as it does not fit into a varchar(8000) output parameter, and I have not found any other working way to pass the data back.

  • The value that you fetch from Oracle to Server2, is it a single record? or a record set?


    Bru Medishetty

    Blog -- LearnSQLWithBru

    Join on Facebook Page Facebook.comLearnSQLWithBru

    Twitter -- BruMedishetty

  • It is a recordset.

    But in SPOnServer2 I can put it into a varchar(8000) variable (in XML format), which is accceptable only if the data fits into 8000 chars.

    SPOnServer2 is very simply at the moment, its like (code is simplified at some points):

    CREATE PROCEDURE SPOnServer2

    @param1 varchar(50),

    @param2 varchar(50),

    @result varchar(8000) output

    AS

    set nocount on

    SET @result=''

    Declare @SQL nvarchar(4000)

    SET @SQL='

    Declare @Tmp table (Col1 varchar(50), Col2 varchar(50))

    INSERT INTO @Tmp SELECT * FROM OPENQUERY(srv,''

    select col1, col2 from table where <conditions coming here>

    '')

    SELECT @result=left(@result+''<R C1="''+Col1+''" C2="''+Col2+''"/>'',7983) from @Tmp'

    exec sp_executesql @SQL, N'@result varchar(7983) output', @result output

    SET @result='<Result>'+@result+'</Result>'

    GO

    This can be called from the SPOnServer1, and the recordset can be recreated from the @result output parameter.

    But sometimes the result do not fit into varchar(8000)..

  • So you get multiple records from Oracle and you try to concatenate them into 1 single Value. 1st that right?

    I don't know if you have done this or not, why not use TEXT Datatype ?


    Bru Medishetty

    Blog -- LearnSQLWithBru

    Join on Facebook Page Facebook.comLearnSQLWithBru

    Twitter -- BruMedishetty

  • I tried to find a way to put the result into a text, but I have not found the way, how could I achieve that.

    As I know I cannot declare text variables inside the SP, but I can create a "text output" parameter in the SP.

    So this part is ok, I can have a variable, which is big enough, but how can I put the recordset into the text variable? If this worked, this would be really great.

  • Well, I have not tried the same that you are looking at but, I suggest give it a try concatenating recursively to the same Text Column from by retrieving the 1 row from the recordset at a time.


    Bru Medishetty

    Blog -- LearnSQLWithBru

    Join on Facebook Page Facebook.comLearnSQLWithBru

    Twitter -- BruMedishetty

  • Do you mean I create a #temp table with a text column, and I concatenate the result there in 1 row?

    This part of the task is ok, but how can I pass the result back?

    I cannot take out the result from the #temp table and put it into a text output parameter, can I?

    I also cannot make a SELECT * from #temp in the SPonServer2, because then I get the error:

    The operation could not be performed because the OLE DB provider 'MSDAORA' was unable to begin a distributed transaction.

    OLE DB error trace [OLE/DB Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x8004d01b].

    So here I am totally stucked.

  • Have you looked into Distributed Transactions like I initially mentioned? Here's a KB article:

    http://support.microsoft.com/kb/280106

    If possible, you should address the actual problem, not create a convoluted work-around.

    Seth Phelabaum


    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • Thanks for this link, I have checked it and it seems the actual registry settings are not valid. In the past the Oracle client was upgraded but the registry settings are still belonging to the old version.

    Hopefully this will solve the issue, many thanks for the comments.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply