Link servers from SQL 2008 R2 to SQL 2000 (32bit)

Solutiion is from http://sqlblog.com/blogs/roman_rehak/archive/2009/05/10/issue-with-64-bit-sql-server-using-sql-2000-linked-server.aspx

Issue with 64-bit SQL Server using SQL 2000 linked server

Recently we started adding SQL Server 2008 64-bit servers to our production set and we ran into the following issue. When we ran queries on a linked 2000 server, we were getting the following error:
OLE DB provider "SQLNCLI10" for linked server "XXXXXX" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI10" for linked server "XXXXXX". The provider supports the interface, but returns a failure code when it is used.
This article from MS website describes the issue pretty well, although it says the issue applies to 2005 but we are using 2008. As suggested, we ran the Instcat.sql file on our development system first, and we ended up getting errrors left and right, so we didn't dare to run it on our main production server. In the end, this workaround worked for us - we needed to create a procedure in the master database on the linked 2000 server. The proc is called sp_tables_info_rowset_64 and it is needed because it is called by 64-bit servers when running remote queries.
 Here is the text of the proc in case you ever need to do the same, create it in the master database:

create procedure sp_tables_info_rowset_64

      @table_name sysname,

      @table_schema     sysname = null,  

      @table_type nvarchar(255) = null

as

  declare @Result int set @Result = 0

  exec @Result = sp_tables_info_rowset @table_name, @table_schema, @table_type
go

Use while loop through table

Needed a solution to loop through a sql resultset without a temp table / table variable / cursor.
Found this question: http://stackoverflow.com/q/1578198/44597

My answer: http://stackoverflow.com/questions/1578198/can-i-loop-through-a-table-variable-in-t-sql/7417780#7417780

declare @id int

        SELECT @id = min(fPat.PatientID)
        FROM tbPatients fPat
        WHERE (fPat.InsNotes is not null AND DataLength(fPat.InsNotes)>0)

while @id is not null
begin
    SELECT fPat.PatientID, fPat.InsNotes
    FROM tbPatients fPat
    WHERE (fPat.InsNotes is not null AND DataLength(fPat.InsNotes)>0) AND fPat.PatientID=@id
    
    SELECT @id = min(fPat.PatientID)
    FROM tbPatients fPat
    WHERE (fPat.InsNotes is not null AND DataLength(fPat.InsNotes)>0)AND fPat.PatientID>@id
        
end    

Find Replace Characters in Text field in SQLServer 2000 database

Based on article posted at http://sqlserver2000.databases.aspfaq.com/how-do-i-handle-replace-within-an-ntext-column-in-sql-server.html

I needed to write a find and replace for CHAR(146) ` in a text field.  Above article for fort nText and the same solution for text worked with nText with the following changes:
   -  VARCHAR(32) from nVARCHAR(32)
   -  use @lenOldString = DATALENGTH(@oldString) instead of SET @lenOldString = DATALENGTH(@oldString)/2.

DECLARE
@TextPointer BINARY(16),
@TextIndex INT,
@oldString VARCHAR(32),
@newString VARCHAR(32),
@lenOldString INT,
@currentDataID INT;

SET @oldString = '’';
SET @newString = '''';

IF CHARINDEX(@oldString, @newString) > 0
BEGIN
PRINT 'Quitting to avoid infinite loop.';
END
ELSE
BEGIN

--Need the for nText fields
--SET @lenOldString = DATALENGTH(@oldString)/2

--Use this for text fields
SET @lenOldString = DATALENGTH(@oldString)

DECLARE irows CURSOR
LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
SELECT
DataID
FROM
dbo.tbData
WHERE
PATINDEX('%'+@oldString+'%', TextData) > 0;

OPEN irows;

FETCH NEXT FROM irows INTO @currentDataID;
WHILE (@@FETCH_STATUS = 0)
BEGIN

SELECT
@TextPointer = TEXTPTR(TextData),
@TextIndex = PATINDEX('%'+@oldString+'%', TextData)
FROM
dbo.tbData
WHERE
DataID = @currentDataID;

SELECT @TextPointer, @TextIndex

WHILE
(
SELECT
PATINDEX('%'+@oldString+'%', TextData)
FROM
dbo.tbData
WHERE
DataID = @currentDataID
) > 0
BEGIN


SELECT
@TextIndex = PATINDEX('%'+@oldString+'%', TextData)-1
FROM
dbo.tbData
WHERE
DataID = @currentDataID;

UPDATETEXT dbo.tbData.TextData @TextPointer @TextIndex @lenOldString @newString;
END
FETCH NEXT FROM irows INTO @currentDataID;
END

CLOSE irows;
DEALLOCATE irows;


END

Query Analyzer

Tutorals

• http://www.quackit.com/sql_server/tutorial/sql_query_analyzer.cfm
• http://www.uncg.edu/cmp/DatabaseProjects/Query%20Analyzer/QueryAnalyzer.htm

Short-cut keys

CTRL-SHIFT-F2         -- Clear all bookmarks. 
CTRL+F2               -- Insert or remove a bookmark (toggle). 
F2                    -- Move to next bookmark. 
SHIFT+F2              -- Move to previous bookmark. 
ALT+BREAK             -- Cancel a query. 
CTRL+O                -- Connect. 
CTRL+F4               -- Disconnect. 
CTRL+F4               -- Disconnect and close child window. 
ALT+F1                -- Database object information. 
CTRL+SHIFT+DEL        -- Clear the active Editor pane. 
CTRL+SHIFT+C          -- Comment out code. 
CTRL+C or Ctrl+Insert -- Copy
CTRL+X or Shift+Del   -- Cut
SHIFT+TAB             -- Decrease indent. 
CTRL+DEL              -- Delete through the end of a line in the Editor pane. 
CTRL+F                -- Find. 
CTRL+G                -- Go to a line number. 
TAB                   -- Increase indent. 
CTRL+SHIFT+L          -- Make selection lowercase. 
CTRL+SHIFT+U          -- Make selection uppercase. 
CTRL+V or Shift+Insert -- Paste. 
CTRL+SHIFT+R          -- Remove comments. 
F3                    -- Repeat last search or find next. 
CTRL+H                -- Replace. 
CTRL+A                -- Select all. 
CTRL+Z                -- Undo. 
F5 or Ctrl + E        -- Execute a query. 
F1                    -- Help for Query Analyzer. 
SHIFT+F1              -- Help for the selected Transact-SQL statement. 
F6                    -- Switch between query and result panes. 
Shift+F6              -- Switch panes. 
CTRL+W                -- Window Selector. 
CTRL+N                -- New Query window. 
F8                    -- Object Browser (show/hide). 
F4                    -- Object Search. 
CTRL+F5               -- Parse the query and check syntax. 
CTRL+P                -- Print
CTRL+D                -- Display results in grid format. 
CTRL+T                -- Display results in text format. 
CTRL+B                -- Move the splitter. 
CTRL+SHIFT+F          -- Save results to file. 
CTRL+R                -- Show Results pane (toggle). 
CTRL+S                -- Save
CTRL+SHIFT+INSERT     -- Insert a template. 
CTRL+SHIFT+M          -- Replace template parameters. 
CTRL+L                -- Display estimated execution plan. 
CTRL+K                -- Display execution plan (toggle ON/OFF). 
CTRL+I                -- Index Tuning Wizard. 
CTRL+SHIFT+S          -- Show client statistics 
CTRL+SHIFT+T          -- Show server trace. 
CTRL+U                -- Use database

Show Stats and Execute plan in query window

The results of these commands are displayed after a query is executed from Query Analyzer, generally after the results of the query are displayed. They include:

• SET SHOWPLAN_TEXT ON: Returns estimated (not actual, as the query is not run) detailed information on how the query will run.
• SET SHOWPLAN_ALL ON: Returns estimated (not actual, as the query is not run) detailed information on how the query will run, plus additional information, such as the estimated number of rows, I/O, CPU, and the average size of a the query.
• SET STATISTICS IO ON: Shows the number of scans, logical reads, and physical reads performed during the query. Returns actual data based on a query that has run.
• SET STATISTICS TIME ON: Shows the amount of time (in milliseconds) needed to parse, compile, and execute a query. Returns actual data based on a query that has run.
• SET STATISTICS PROFILE ON: Shows a recordset that represents a profile of the query execution. Returns actual data based on a query that has run.

You will not want to run the first two commands listed above at the same time as the others because the first two commands are based on estimated data, while the last three are based on real data.
If you are using SQL Server 2000, using these commands are less needed as you can get all of the same type of data other ways from within Query Analyzer.

Why stored procedures recompile

Some articles how to minimize stored proc recompiles:

Identifying Stored Procedure Recompilation Problems in SQL Server 2000

Minimizing Stored Procedure Recompilation Problems in SQL Server 2000

Optimizing SQL Server Stored Procedures to Avoid Recompiles

List constrainsts on a table

SELECT  sc.constid ConstraintID,
  so.name TableName,
  CASE WHEN sc.colid = 0 THEN '' ELSE s.name END ColumnName,
  CASE
   WHEN sc.Status & 32 > 0 then 'Table-level'
   WHEN sc.Status & 16 > 0 then 'Column-level'
   WHEN sc.Status &  5 > 0 then 'DEFAULT'
   WHEN sc.Status &  4 > 0 then 'CHECK'
   WHEN sc.Status &  3 > 0 then 'FOREIGN KEY'
   WHEN sc.Status &  2 > 0 then 'UNIQUE KEY'
   WHEN sc.Status &  1 > 0 then 'PRIMARY KEY'
  END + ' constraint'
FROM  sysconstraints sc
INNER JOIN sysobjects so ON so.id = sc.id AND so.xtype = 'u'
LEFT JOIN syscolumns s ON s.colid = sc.colid AND so.id = s.id

Running Deadlock trace in SQL server 2000

Turn on deadlock trace
DBCC TRACEON(1204,1222)

DBCC TRACEON (3604)
DBCC TRACEON (1204)
To turn off deadlock tracing and logging:
DBCC TRACEOFF (3604, 1204)

This will enable deadlock tracing for all existing connections and new. You can check out KB832524 for more details. Trace flag 1204 reports deadlock information formatted by each node involved in the deadlock. Trace flag 1222 formats deadlock information, first by processes and then by resources.

Use SQL Profiler to trace deadlock events and get the resource ID of the table or index under contention. The steps to do this are:

1. Start SQL profiler
2. On the Trace Properties dialog box, on the General tab, check Save to file and specify a path to save the trace
3. Click the Events tab, only add Locks\Lock:deadlock and Locks\Lock:deadlock chain
4. Click the Data columns tab, add DatabaseID, IndexID, ObjectID

This trace will record all deadlocks on this SQL Server instance, along with the ID of the source table of contention

SQL Server 2000 DBCC Traceon flags

SQL Server 2000: Some useful trace flags

SQL Server 2000: Some useful trace flags

Alexander Chigrik
chigrik@mssqlcity.com

---

Introduction

In this article, I want to tell you, what should you know about trace flags, and how you can use some useful trace flags in SQL Server 2000 for administering and monitoring.

Trace flags are used to temporarily set specific server characteristics or to switch off a particular behavior. You can set trace flags by using DBCC TRACEON command or by using the -T option with the sqlservr command- line executable. After activated, trace flag will be in effect until you restart server, or until you deactivate trace flag by using DBCC TRACEOFF command.

Trace flags

You can use DBCC TRACESTATUS command to get the status information for the particular trace flag(s) currently turned on. This is the syntax from SQL Server Books Online:

DBCC TRACESTATUS (trace# [,...n])

To get the status information for all trace flags currently turned on, you can use -1 for trace#.

This is the example:

DBCC TRACESTATUS(-1)

You can use DBCC TRACEON command to turn on the specified trace flag. This is the syntax from SQL Server Books Online:

DBCC TRACEON (trace# [,...n])

If you want to turn off the specified trace flag(s), you can use DBCC TRACEOFF command.
This is the syntax from SQL Server Books Online:

DBCC TRACEOFF (trace# [,...n])

1. Trace flag -1 (undocumented).

This trace flag sets trace flags for all client connections, rather than for a single client connection. Is used only when setting trace flags using DBCC TRACEON and DBCC TRACEOFF. The setting of the Trace flag -1 is not visible with DBCC TRACESTATUS command, but work without problems.
This trace flag was documented in SQL Server 6.5 Books Online, but was not documented in SQL Server 7.0 and SQL Server 2000.

2. Trace flag 1204 (undocumented).

This trace flag returns the type of locks participating in the deadlock and the current command affected. This trace flag was documented in SQL Server 7.0 Books Online, but was not documented in SQL Server 2000.

3. Trace flag 1205 (undocumented).

This trace flag returns more detailed information about the command being executed at the time of a deadlock. This trace flag was documented in SQL Server 7.0 Books Online, but was not documented in SQL Server 2000.

4. Trace flag 1807 (undocumented).

You cannot create a database file on a mapped or UNC network location. This opportunity is generally unsupported under SQL Server 7.0 and SQL Server 2000. You can bypass this by turn on trace flag 1807.

5. Trace flag 3604 (undocumented).

One of the most used trace flag. Trace flag 3604 sends trace output to the client. This trace flag is used only when setting trace flags with DBCC TRACEON and DBCC TRACEOFF. Trace flag 3604 was documented in SQL Server 6.5 Books Online and in SQL Server 7.0 Books Online, but was not documented in SQL Server 2000.

6. Trace flag 3605 (undocumented).

In comparison with Trace flag 3604, this trace flag sends trace output to the error log. Trace flag 3605 was documented in SQL Server 6.5 Books Online and in SQL Server 7.0 Books Online, but was not documented in SQL Server 2000.

7. Trace flag 3608 (undocumented).

This trace flag skips automatic recovery (at startup) for all databases except the master database. Trace flag 3608 was documented in SQL Server 6.5 Books Online, but was not documented in SQL Server 7.0 and SQL Server 2000.

8. Trace flag 4022.

If turns on, then automatically started procedures will be bypassed. This trace flag described in CREATE PROCEDURE statement in the SQL Server Books Online.

9. Trace flag 8202 (undocumented).

This trace flag used to replicate UPDATE as DELETE/INSERT pair. Let me to describe.
UPDATE commands at the publisher can be run as an "on-page DELETE/INSERT" or a "full DELETE/INSERT".
If the UPDATE command is run as an "on-page DELETE/INSERT," the Logreader send UDPATE command to the subscriber, If the UPDATE command is run as a "full DELETE/INSERT," the Logreader send UPDATE as DELETE/INSERT Pair. If you turn on trace flag 8202, then UPDATE commands at the publisher will be always send to the subscriber as DELETE/INSERT pair.

Literature

1. SQL Server Books Online

2. INF: Trace Flag to Replicate UPDATE as DELETE/INSERT Pair

Truncate Transaction log SQL Server 2000

Get database name:
SELECT * FROM master.dbo.sysdatabases

Get File group ID
Use [db_name]
select * from dbo.sysfiles

Truncate and shrink Transaction file:
Use [db_name]
BACKUP LOG [db_name] WITH TRUNCATE_ONLY
DBCC SHRINKFILE (fileID)

SQL Server 2000 sysindexes Table Columns

, sysSource

Column name	Data type	Description
id	int	ID of table (for indid = 0 or 255). Otherwise, ID of table to which the index belongs.
status	int	Internal system-status information.
first	binary(6)	Pointer to the first or root page.
indid	smallint	ID of index: 0 = Heap = Table Data (not Index) 1 = Clustered Index 2 ... 254 = Nonclustered Index 255 = Entry for tables that have text or image data
root	binary(6)	For indid >= 1 and < indid =" 0" indid =" 255,">
minlen	smallint	Minimum size of a row.
keycnt	smallint	Number of keys.
groupid	smallint	Filegroup ID on which the object was created.
dpages	int	For indid = 0 or indid = 1, dpages is the count of data pages used. For indid=255, it is set to 0. Otherwise, it is the count of index pages used.
reserved	int	For indid = 0 or indid = 1, reserved is the count of pages allocated for all indexes and table data. For indid = 255, reserved is a count of the pages allocated for text or image data. Otherwise, it is the count of pages allocated for the index.
used	int	For indid = 0 or indid = 1, used is the count of the total pages used for all index and table data. For indid = 255, used is a count of the pages used for text or image data. Otherwise, it is the count of pages used for the index.
rowcnt	bigint	Data-level rowcount based on indid = 0 and indid = 1. For indid = 255, rowcnt is set to 0.
rowmodctr	int	Counts the total number of inserted, deleted, or updated rows since the last time statistics were updated for the table.
xmaxlen	smallint	Maximum size of a row.
maxirow	smallint	Maximum size of a nonleaf index row.
OrigFillFactor	tinyint	Original fillfactor value used when the index was created. This value is not maintained; however, it can be helpful if you need to re-create an index and do not remember what fillfactor was used.
reserved1	tinyint	Reserved.
reserved2	int	Reserved.
FirstIAM	binary(6)	Reserved.
impid	smallint	Reserved. Index implementation flag.
lockflags	smallint	Used to constrain the considered lock granularities for an index. For example, a lookup table that is essentially read-only could be set up to do only table level locking to minimize locking cost.
pgmodctr	int	Reserved.
keys	varbinary(816)	List of the column IDs of the columns that make up the index key.
name	sysname	Name of table (for indid = 0 or 255). Otherwise, name of index.
statblob	image	Statistics BLOB.
maxlen	int	Reserved.
rows	int	Data-level rowcount based on indid = 0 and indid = 1, and the value is repeated for indid >1. For indid = 255, rows is set to 0. Provided for backward compatibility.

Find Primary Key in SQL 2000

In SQL Server 2000 it is difficult to see as primary key and a foreign key.

Try running this script, which will give you all primary key on all tables in the database including column information.

SELECT A.TABLE_NAME, A.CONSTRAINT_NAME, B.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS A, INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE B
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
ORDER BY A.TABLE_NAME

T-SQL Determine Database ID

SELECT name, DB_ID(name) AS DB_ID
FROM master.dbo.sysdatabases
ORDER BY dbid