November 25, 2014 at 5:06 am
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