JDBC Call to StoredProc in Azure Database

  • Hi All,

    I'm attempting to read in a load of data from an Azure database into a Google Docs spread sheet using a JDBC connection. Everything works hunky dory until I start using temp tables. To prove the problem is temp table related, I've created the simplest possible stored proc, which just reads back @@Servername. When I do this directly (using the code below), it works fine and prints @@ServerName in cell A1. When a temp table is added, it breaks (see procedures below).

    I've had similar problems using SSRS / SSIS / Entity Framework in the past, where the application can't build a field list for the datasource when temp tables are present, in which case, the procedure can be adapted by putting a dummy select at the top temporarily, or by switching #Tables to @Table variables. Unfortunately neither of these workarounds seem to get me around this problem. Any suggestions will be most welcome. Any ideas?

    Thanks in advance,

    Neil

    Google Docs Script

    ============

    var stmt = conn.createStatement();

    var rs = stmt.executeQuery("exec [Reporting].[jdbc_test2]");

    var sheet = SpreadsheetApp.getActiveSheet();

    var cell = sheet.getRange('Sheet1!A1');

    cell.setValue(123);

    rs.next()

    cell.setValue(rs.getString(1))

    Working Procedure

    ============

    ALTER PROCEDURE [Reporting].[jdbc_Test2]

    AS

    BEGIN

    SELECT @@ServerName AS Name

    END

    GO

    Problem Procedure

    ============

    ALTER PROCEDURE [Reporting].[jdbc_Test2]

    AS

    BEGIN

    CREATE TABLE #Server (Name VARCHAR (250))

    INSERT INTO #Server

    SELECT @@ServerName

    SELECT * FROM #Server

    END

    GO

Viewing 0 posts

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