Create Temporary tables/delclare table not working for our environment

Question

Hi,

we are going from:

.NET --ODBC--> ODBC-JDBC Bridge--> JDBC Driver for SqlServer --> SqlServer

Everything works however creating temporary tables is failing:

CREATE TABLE #tempDefault (
            DefaultKey VARCHAR(255)
            ,DefaultValue TEXT
)

a work around would be to use declare table:

DECLARE @tempDefault TABLE
(
            DefaultKey VARCHAR(255)
            ,DefaultValue TEXT
)

This works however inserts into the declared table throw an exception:

INSERT INTO @tempDefault  values (‘1’, ‘2’)
 Or
 INSERT INTO @tempDefault (DefaultKey, DefaultValue) values (‘1’, ‘2’)
Or
INSERT INTO @tempDefault

(DefaultKey, DefaultValue)

SELECT DefaultKey

            ,DefaultValue

FROM tblHostSystemDefault

WHERE HostID = @HostID

Microsoft.Data.Odbc.OdbcException: ERROR [IM001] [Microsoft][ODBC Driver Manager] Driver does not support this function

Anyone got ideas on a work around? or know why these calls don't work through JDBC?

Thanks

 

Answer 1

Could you just post how you call this script to execute ?

It would be nice to the batch as a whole, rather than only these snippet, thanks.

 

HTH, jens Suessmeyer.

Answer 2

Hi Jens,

Here is the code that is run:
          
try
            dim cmd as OdbcCommand = conn.CreateCommand
            cmd.CommandText = sql
            cmd.Connection = conn
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Prepare()

           
           
            Dim reader As OdbcDataReader = cmd.ExecuteReader()       
           

            dt = CreateDataTable(reader)

            While reader.Read()
                dim dr as DataRow = dt.NewRow
                For i as Integer = 0 to (reader.FieldCount -1)
                    dr(i) = CType(reader.GetValue(i),Object)                   
                Next
                dt.Rows.Add(dr)
            End While
           
        Catch e As Exception

...

thanks

Answer 3

FYI, the resolution of this problem was the bridge. The bridge had some issues with the calls and after contacting the vendor I was able to resolve this problem.