.NET Temporary table creation failed in SQL Server

General discussion forum about all databases other than SqlBase.
a_sivababu
India
Posts: 505
Joined: 10 Mar 2017, 08:46
Location: Chennai,India

.NET Temporary table creation failed in SQL Server

Post by a_sivababu » 07 Sep 2014, 15:44

Hi All,
I am currently using SQL Server local temporary table concept for most of my reporting data and this is working very much fine in win32 but if I change it to .NET then I get ODBC error "Invalid object Name #TEMP_TBL " while creating the local temp table. I tried OLEDB and ODBC driver with traditional and SqlConnectDotNet() but no luck.

Call SqlPrepareAndExecute(hSqlLocal,"CREATE TABLE #TEMP_TBL (t Varchar(10))")

However, If I create this with global temporary table then it works. for eg ##TEMP_TBL is working fine.

Tested in TD 6.2.2.1 with SQL Server 2008/2014 and OS - windows 7/8.1

Thanks!

Siva A

VEspinal

Re: .NET Temporary table creation failed in SQL Server

Post by VEspinal » 19 Sep 2014, 22:36

Actually, you can create both. You cannot see the local temporary table because it is gone when your session finishes.

For Global ones see attached picture

For Local ones, try the following:
!!CB!! 174
Set sSql1 = "CREATE TABLE #TMP_NEW_TEST1 (TEST_STRING char(20))"
If SqlPrepareAndExecute( hSql1, sSql1 )
Set s2 = "This is a test"
Set sSql1 = "INSERT INTO #TMP_NEW_TEST1 values (:s2)"
If SqlPrepareAndExecute( hSql1, sSql1 )
Set sSql1 = "SELECT TEST_STRING from #TMP_NEW_TEST1 into :s1"
If SqlPrepareAndExecute( hSql1, sSql1 )
Call SqlFetchNext( hSql1, nErr )
Set df1 = s1
Call SqlCommit( hSql1 )

HTH.

Regards,
Victor Espinal
You do not have the required permissions to view the files attached to this post.

a_sivababu
India
Posts: 505
Joined: 10 Mar 2017, 08:46
Location: Chennai,India

Re: .NET Temporary table creation failed in SQL Server

Post by a_sivababu » 20 Sep 2014, 10:37

Thanks for your response, Victor.
Both ODBC and OLEDB providers are not working with local temp table but global temp tables are working. I feel you might have tested for win32 application and not in .NET application. Can you please check once again?

And also, you can see the local temp table under the tempdb database if it successfully created.

In one of your response in TDMobile forum, I also see that you have suggested to use System.Data.SqlClient for the local temp table creation in TDMobile Application.

Can you please check and raise as a bug if ODBC and OLEDB are not working for temp table creation in .NET. This is important for us and all.

Thank you.

Regards,
Sivababu A

Igor Ivanovic
Site Admin
Site Admin
Croatia
Posts: 1473
Joined: 05 Mar 2017, 12:37
Location: Zagreb, Croatia

Re: .NET Temporary table creation failed in SQL Server

Post by Igor Ivanovic » 22 Sep 2014, 08:40

a_sivababu,

Where did You found the TDMobile forum?
Igor Ivanovic
Image

a_sivababu
India
Posts: 505
Joined: 10 Mar 2017, 08:46
Location: Chennai,India

Re: .NET Temporary table creation failed in SQL Server

Post by a_sivababu » 22 Sep 2014, 10:19

Typo. it is not forum but 1 year customer Gupta online support for licensed TDMobile.

Siva

a_sivababu
India
Posts: 505
Joined: 10 Mar 2017, 08:46
Location: Chennai,India

Re: .NET Temporary table creation failed in SQL Server

Post by a_sivababu » 29 Sep 2014, 11:08

Hi Victor,
Can this be accepted as a bug since this is not working in OLEDB and ODBC connection string through SqlconnectDotNet()?

Thanks.
Siva A

VEspinal

Re: .NET Temporary table creation failed in SQL Server

Post by VEspinal » 10 Oct 2014, 00:53

Hi Siva:

I tested with TD 6.2 SP1 .WPF, Windows 7 and did not work.

Tested with TD 6.2 SP2 .WPF, Windows 7 and worked!

If SqlConnectDotNet( hSql1, sConnectionString, "System.Data.SqlClient", 12 )

HTH

Regards,
Victor Espinal

a_sivababu
India
Posts: 505
Joined: 10 Mar 2017, 08:46
Location: Chennai,India

Re: .NET Temporary table creation failed in SQL Server

Post by a_sivababu » 10 Oct 2014, 14:39

Hi Victor,
Thanks. you are right. this is working fine for me too. My question is that, I am currently using below ODBC driver and not even OLEDB driver. Can I safely change to your connection and will all SqlError function will work with this properly. Based on my understanding, there is different SQLError SAL functions for ODBC and OLEDB but not sure what it will work for System.Data.SqlClient..

!!CB!! 227
! Call SqlConnectDotNet(hSqlLocal, 'Driver={SQL Server Native Client 11.0};Server=SERVER01;Database=test;Uid=sa;Pwd=CharLi12;', "", DBP_PROVIDER_ODBC )
Call SqlConnectDotNet( hSqlLocal, 'Provider=sqloledb;Data Source=SERVER01\\SQLEXPRESS;Initial Catalog=test;
User Id=sa;Password=CharLi12;', "", DBP_PROVIDER_SQLSERVER_OLEDB)

Thanks!
Siva A

Return to “General Discussion”

Who is online

Users browsing this forum: [Ccbot] and 1 guest