.NET Temporary table creation failed in SQL Server
.NET Temporary table creation failed in SQL Server
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
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
Re: .NET Temporary table creation failed in SQL Server
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
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.
Re: .NET Temporary table creation failed in SQL Server
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
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
Re: .NET Temporary table creation failed in SQL Server
a_sivababu,
Where did You found the TDMobile forum?
Where did You found the TDMobile forum?
Igor Ivanovic


Re: .NET Temporary table creation failed in SQL Server
Typo. it is not forum but 1 year customer Gupta online support for licensed TDMobile.
Siva
Siva
Re: .NET Temporary table creation failed in SQL Server
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
Can this be accepted as a bug since this is not working in OLEDB and ODBC connection string through SqlconnectDotNet()?
Thanks.
Siva A
Re: .NET Temporary table creation failed in SQL Server
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
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
Re: .NET Temporary table creation failed in SQL Server
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
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
Who is online
Users browsing this forum: [Ccbot] and 1 guest