Questions, Answers, and Tips About SQL Server - 15 Nov 1999

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)GOSELECT * FROM   tempdb.INFORMATION_SCHEMA.COLUMNSWHERE   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 tempdbGOsp_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.

I'm adding a column to an existing table that has an update trigger. After I add this column, I want to update all existing rows with default values for the new column without running the update trigger. How do I disable the update trigger without dropping it before I finish updating the table?

In SQL Server 7.0, Microsoft enhanced the ALTER TRIGGER command to permit you to temporarily disable an update trigger without dropping it. For example, you can use the following command to disable an update trigger called trig_example on a table called trig1:

ALTER TABLE trig_example DISABLE TRIGGER trig1

SQL Server 7.0's ALTER TRIGGER command lets you disable one trigger or use the ALL keyword to disable all triggers for a table. During maintenance operations, you might want to use the ALTER TABLE command in a stored procedure (sp) that temporarily disables all triggers in a database.

I'm using Microsoft OLAP Server to help me analyze Microsoft Systems Management Server (SMS) data and file-system usage data that I've captured. I designed a simple cube, but I get the following error when I try to process the cube:

ODBC error: [Microsoft][ODBC Driver Manager]   Data source name not found and no default   driver specified;IM002; Time:8/23/99 2:53:09 PM

This error message seems to be saying that I didn't set up the Data Source Name (DSN) correctly for the ODBC connection that I defined in the OLAP Server's library folder. I know the DSN works because I used OLAP Manager to connect to the DSN to design and edit my cube. Any ideas about what might be causing this strange error message?

We don't know for sure, but our guess is that the DSN you're using is a User DSN rather than a System DSN. This error can result in an error screen similar to what Screen 1 shows.

When you're editing a cube, OLAP Manager is a user process. However, the MSSQLServerOLAPService process performs cube processing. This distinction is subtle but important. You can see the DSN from OLAP Manager, so you can build the cube. But User DSNs aren't visible to system processes, so the cube you built isn't visible to MSSQLServerOLAPService.

If this diagnosis is correct, your problem will disappear when you change your DSN entry from a User DSN to a System DSN. You can change the DSN by adding it to the System Data Sources window on the System DSN tab of the ODBC Data Source Administrator dialog box, as Screen 2 shows.

Can I change the ownership of a table's stored procedure (sp) after I create the table?

If you're using SQL Server 7.0, you can use the sp_changeobjectowner procedure to change the ownership of a table's sp after you create the table. SQL Server 7.0 Books Online (BOL) provides the following syntax for the sp_changeobjectowner procedure:

sp_changeobjectowner [@objname =]   '

In this syntax, object is the name of an existing table, view, or sp in the current database, and owner is the new owner.

SQL Server 6.x doesn't directly support changing the owner of an existing object. Microsoft's official method is to drop the object and recreate the object so that the correct user owns the table, view, or sp.

You can use an official method to tweak SQL Server 6.5's system tables to change the owner of a SQL Server 6.x table or sp. However, Microsoft doesn't recommend this method. To figure out how to do it, you can read SQL Server 7.0's Transact SQL (T-SQL) code for sp_changeobjectowner.