How do I view the contents of a global temporary table that I created from an Active Server Pages (ASP) application? The table name looks like ##TempTable1. I don't need to view the table from the ASP application, but I want to see the table structure from SQL Server Enterprise Manager. From SQL Server Enterprise Manager, the tables collection in tempdb doesn't list any temporary tables.

SQL Server Enterprise Manager doesn't list temporary tables, but you can use a few tricks to obtain global temporary table structures. For example, the following script will give you the information you need:

CREATE TABLE ##Testb (col1 int identity)
GO
SELECT * FROM
   tempdb.INFORMATION_SCHEMA.COLUMNS
WHERE
   table_name = "##Testb"
GO

This example code illustrates how you can use INFORMATION_SCHEMA views to access system information without directly querying the underlying system tables.

However, this trick doesn't work well with local temporary tables. INFORMATION_SCHEMA.COLUMNS will contain information, but the table name will look like the following example:

#Testb______________000000000015

You can duplicate local temporary table names across different connections, so SQL Server must pad the name and make it unique for internal tracking purposes.

You can also use sp_help to see information about your global temporary table:

USE tempdb
GO
sp_help 'tempdb..##Testb'
GO

If you use this solution, make sure you're in tempdb because you can't specify a database identifier directly in sp_help. Alternatively, you can look at the text of sp_help or sp_columns and write a custom stored procedure (sp) that does exactly what you want.

I try to use SQL Agent to schedule a Data Transformation Services (DTS) package that runs Microsoft Access on screen so that I can see what's going on. I see the msaccess.exe process start in the task list, but Access never appears on screen. The task never finishes, and I have to manually stop the task and the job in SQL Server Enterprise Manager. If I run the package from SQL Server Enterprise Manager, everything works fine. What's the problem?

Sorry, but you can't run jobs that require a GUI through SQL Agent.

   Prev. page   [1] 2     next page



You must log on before posting a comment.

If you don't have a username & password, please register now.

Reader Comments

I have a SQL Server and a Sybase Server. I created a DTS Package to copy a table from Sybase to SQL Server. If I run this package twice, a row is duplicated. I want to update the rows contained in the destination table. How can I do this without duplicate data in the destination table?

Cesar Rodriguez- April 03, 2002

If you want to run DTS from Access save the DTS script and paste it into an Access module. By changing the appropriate variables it is easy to run a DTS process automatically by starting Access with the scheduler, run the module from the on_load event of the startup form and checking a persistance table in your database to see if the process has been run for the appropriate period.

jdhouse- November 16, 2005

Article Rating 4 out of 5

 
 

ADS BY GOOGLE