.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Which connection owns global temporary table?

Posted By:      Posted Date: August 30, 2010    Points: 0   Category :Sql Server
Global temporary table exists over time of connection in which was created. I have several connections (sessions) open in management studio. How to check using sql statement, system views, etc... if global temporary table was created in current session?

View Complete Post

More Related Resource Links

Temporary tables in SQL Server vs. table variables

When writing T-SQL code, you often need a table in which to store data temporarily when it comes time to execute that code. You have four table options: normal tables, local temporary tables, global temporary tables and table variables. I'll discuss the differences between using temporary tables in SQL Server versus table variables.

How to change connection string of a pivot table pointing to SSAS 2005 cube using excel 2003?

Hi All,I am not sure if I should have posted this query to Excel 2003 forum. But posting it here as it applies to SSAS 2005 as well.Ok, let me give the background before I tell the actual problem.We have users on ABC domain and the SSAS server is also on ABC domain. Users on this domain can acess the excel pivots by connecting to cube to browse the data. They leave the Userid & password field blank while they setup the connection string and it works fine. Thanks to windows authentication that takes the credentials of user logged in. Let's say I have two users A and B, they login to ABC domain with their own windows ids.  Now when user A creates a excel file having a cube pivot and then sends this file to user B, user B can refresh and modify the same excel file (he can select new measures to pivot, new hierarchies in filters and so on).Now, let's say I have another user, user C. He has excel 2003 installed on his PC and cannot migrate to excel 2007. He is on different domain XYZ but have a valid windows userid on domain ABC. The domain ABC & XYZ can not be setup to have trusted relationship. Now, when user A sends the same excel file to user C. When user C opens the file and try to refresh it or try to modify the pivot by selecting/deselecting any elements, he gets below error prompt:" An error was encountered in the transport layer." and "Errors in the

How to declare a global connection object?

Following the post on declaring a global variable 'theDate' with a defined date format to be used across the web pages, I now encounter another tricky question: Is there a way to declare a global connection object so that I can avoid repeating the same statements (SqlConnection myconn = new SqlConnection(Class1.conn); myconn.Open();) in all the coding page that require a database connection? Thanks.

How to make a connection between a word in the cell (href)? and a rule of the same or another table

 HelloHow to make a connection between a word in the cell (href)? and a rule of the same or another table with sql. Is there also finished editors?

How to create a temporary table in C#?

Hello Friends,Can i make a bulk insert into the temporary table by calling a stored procedure from the front end.Am using BulkCopy command to write data to server bulkcopy.destinationtablename="#TempTableName";(here i want to give the tempoary tablename which is created in the database)bulkcopy.Writetoserver(datatable dt);How to do this?(OR)Is there anyway to create a temporary table from C# and set the destinationtable as #TemPTableNameand then write to server.Please do the needful.Thanks,Suchitra

OPENQUERY throws Error 7357 when the source SP uses temporary table.

Hello Everybody / Anybody,
Sorry but exiting problem!
The Problem: OPENQUERY throwing error [Error 7357]when the source SP uses temporary table.
Description : Need to validate data against master list. My combo on UI has a source Stored Proc(contains a temp table in it).
I'm importing data from Excel. Before import, I want to validate it against my master list values.
[say field Priority has master values "High, Medium,Low".] and in excel user has added 'ComplexHigh' under priority field]
In this case, my import validator StoredProc should not accept value 'ComplexHigh' as it is not present in my Priority master list]
I'm preparing a temp table tabName  containing o/p of SP, it works fine zakkas if my SP  usp_SelectData does not contain temp table. 
I think you got what the situation is!! Woh!

How to grab ip address of remote connection to store in table


Hey guys move if in wrong area


I am trying to create a bit of a VPN program using sql server.

I have figured out how to have a form grab the users ip address and port from a tcp connection and store it into a table.

Now I want to integrate this directly into the sql server.

I know you can filter connections by IP using login triggers but is there a way to do the same when a command is issued to the database.

create and open global database connection object


Hi to everyone:

I`m trying to install the allinta cms software www.allinta.com, I have already create a MySQL Data Base, but when I try to launch my cmsadmin I t appears an error

Microsoft VBScript runtime error '800a01f4'

Variable is undefined: 'dbConnectionStr'

/cmsadmin/inc/db_connection.asp, line 75

IT  seems to be the Object Connection, could somebody help me to give the right code instruction please, I don´t have an idea on how to make it.

At this moment I opened in dreamweaver and it´s

Set objConn = Server.CreateObject("ADODB.Connection")

Difference between Temporary Table and Table Variable in SQL 2005 on Reporting perspective ?


Hi friends,

I refered technical difference between Temporary Table and Table Variable.

I would like to know the difference, on SSRS Reports perspective ?

how it would affect performance of report , if we used Temp Table OR Table Variable ?



how to insert value into a Identity column in temporary table using dataflow task



I have requirement.

I have a temporary table with and identity column,table will get populated with data on the execution of a dataflow task except the identity Column.now I need to insert value into identity column.

How can I acheive this.


Thanks, A2H

temporary table stored procedure help please


hi i have the following, but i want to define @r1 and @r2 etc as querystrings.. how do i do this?:

ID bigint,
r1d smallint, r2d smallint, dif int )

INSERT INTO @diff (ID, r1d, r2d)
SELECT     ID, r1, r2
FROM     dbo.type

UPDATE     @diff
SET     r1d = r1d-@r1 //(r1 is querystring and how make result show as positive number?)
    r2d = r2d-@r2
    dif = r1d+r2d

FROM @diff

temporary table stored procedure



how do i dyamically create a temporary table with a stored procedure?

i have a datasource on a page which returns all rows from 'type'. columns are url, b1,b2,b3

after this has been returned, i want a stored procedure to make a table with a row for each of the rows returned from sqldatasource1, where b1d,b2d,b3d = b1 -(subtraction) value of 'b1' querystring, b2 -(subtract) value of 'b2' querystring, b3 -(subtract) value of 'b3' querystring, all results to be shown as positive, even though results may sometimes be negative numbers.

then i want another column on this table to be called 'dif' and it is the total each row, (b1d,b2d and b3d)

then i want the row with the lowest 'dif' (random of the lowest if there are multiple with the same lowest dif) be selected (not from this table, but the row it was calculated from/refering from on the 'type' table).

is this possible? can someone help me with this? i have never creted a stored procedure before.. thanks so much if you can help

SSIS importing from a global temp table to another database


I have a work flow as followed but haven't been had any success after the 1st step.

1. run a query to create a global temp table from ServerA.databaseX

2. pull the data from the global temp table to the ServerB.databaseY

I think the problem is that after the step1 excuted, the session closes and the global temp table gets deleted. So when the step 2 tries to access the global temp table it's no longer there.

I tried to pull the data using the direct query method (no temp table) but it took too long - more than 45 minutes. If i have to do the above steps manually, it wouldn't take more than 15 minutes.

I dont have access to create a local table in the destination server/database.

Is there a way to keep the session alive so i can pull data from the global temp table.

I appreciate any help you can provide.

Slow response loading XML Document to SQL Temporary Table


My customer's application is creating an XML document and then loading the data in to a temporary table. In development the insert completes in less than a second. In production the insert requires 20 or more seconds depending on the size of the document. The sql server version is 2005 and the builds are the same. The Explain Plan for the production server shows an extra step (Table Spool) with an estimated row count of 10,000. Development does not have this step and the estimated number of rows is only 900. I have been able to reproduce both query behaviors on SQL 2008.

Have I missed a configuration parameter on either the OS Or SQL?


Because of limitations, my script will only generate 50 records:

CREATE TABLE #CIM (ID int identity(1,1), lCIMId int, lColorID int, lChemicalId1 int, lChemicalId2 int, Reactions xml)
declare @xmlCIM as XML
set @xmlCIM='<ArrayOfCIMXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <CIMXML lColorId="3" lChemicalId1="1" lChemicalId2="1025">
      <Reaction lReactionId="15" />
  <CIMXML lColorId="3" lChemicalId1="2" lChemicalId2="1025">

Temporary table in table-valued functions can not be dropped



I have created a table-valued function like:

   @List nvarchar(max)
RETURNS @RtnValue table
   column varchar(255)

SELECT column
   SELECT column
) AS #C
ON A.column = #C.column
WHERE column = @List


However, I find that the #C won't be dropped in the tempdb after the function execution. It can't be remove unless I restarted the server.


If I added:

Drop Table #C

in the function, there will be an error message saying "Cannot access temporary tables from within a function".


Please advice/suggest how can I remove the temp table after the function execution. I am using MSSQL 2005.

P.S. I know that I can use table variable instead, but I don't want to do this since it will involve lots of changes for me. I wish I can just drop the temp table by script or "manually".






Export Visio Database Table Names to Excel

If you use the Enterprise Architect edition of Microsoft Visio for data modeling regularly, then there is a good chance that at some point you've wanted to export just the table names into Excel. You might want to do this to map logical ERD entities to physical data model tables, track project status by entity, or track overlap between database versions.

Insert value using Table Value Functions

a real gem in Sql Server 2008. mostly people still using Stored procedure may be they shifted to SQL Server but they are not using TVF right now.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend