Windows Explorer settings I like to use. Puts you where you need to go when you open it.
%SystemRoot%\explorer.exe /n, /e, /select, E:\MSSQL
SQL limitations columns sizes etc.
Find it here.
SQL Server Architecture (SQL Server 2000)
Maximum Capacity Specifications
Topic last updated — January 2004
The first table specifies maximum capacities that are the same for all editions of Microsoft® SQL Server™ 2000. The second and third tables specify capacities that vary by edition of SQL Server 2000 and the operating system.
This table specifies the maximum sizes and numbers of various objects defined in Microsoft SQL Server databases, or referenced in Transact-SQL statements. The table does not include Microsoft SQL Server 2000 Windows® CE Edition.
Can’t drop or edit SQL Agent job after changing server name
Having trouble editing or dropping a job on a SQL Server that you renamed the NT server for?
Simply change the field below by running this query and every will work out.
UPDATE msdb..sysjobs
SET originating_server = @@SERVERNAME
-- select *
FROM msdb..sysjobs WHERE originating_server = 'sql1\i2k'
T-SQL Searching using Wildcard Characters
Searching for Wildcard Characters
You can search for wildcard characters. There are two methods for specifying a character that would ordinarily be a wildcard:
Use the ESCAPE keyword to define an escape character. When the escape character is placed in front of the wildcard in the pattern, the wildcard is interpreted as a character. For example, to search for the string 5% anywhere in a string, use:
WHERE ColumnA LIKE '%5/%%' ESCAPE '/'
In this LIKE clause, the leading and ending percent signs (%) are interpreted as wildcards, and the percent sign preceded by a slash (/) is interpreted as the % character.
Use square brackets ([ ]) to enclose the wildcard by itself. To search for a hyphen (-), instead of using it to specify a search range, use the hyphen as the first character inside a set of brackets:
WHERE ColumnA LIKE '9[-]5'
The following table shows the use of wildcards enclosed in square brackets.
Symbol Meaning
LIKE '5[%]'
5%
LIKE '5%'
5 followed by any string of 0 or more characters
LIKE '[_]n'
_n
LIKE '_n'
an, in, on (and so on)
LIKE '[a-cdf]'
a, b, c, d, or f
LIKE '[-acdf]'
-, a, c, d, or f
LIKE '[ [ ]'
[
LIKE ']'
]
When string comparisons are performed with LIKE, all characters in the pattern string are significant, including every leading and trailing blank (space). If a comparison to return all rows with a string LIKE 'abc ' (abc followed by a single space) is requested, a row in which the value of that column is abc (abc without a space) is not returned. The reverse, however, is not true. Trailing blanks in the expression to which the pattern is matched are ignored. If a comparison to return all rows with a string LIKE 'abc' (abc without a space) is requested, all rows that start with abc and have zero or more trailing blanks are returned.
List Database Recovery Mode on a server
A handy script to have around for a nice report.
SELECT LEFT(name,50) DatabaseName, CONVERT(VARCHAR(50),DATABASEPROPERTYEX(name, 'recovery')) RecoveryMode
FROM sysdatabases
WHERE name NOT IN ('model','tempdb')
ORDER BY name
Rename a SQL Server, steps.
You've renamed the server itself. Now it's time to rename SQL instances from within SQL. You'll need to reboot the services after the rename.
Let's see what the whole picture is now by running this.
EXEC sp_helpserver
Get the old servername to use to drop it.
SELECT srvname AS OldServerName FROM master.dbo.sysservers WHERE srvid=0
Check the name of the server as it is now to use to create the new SQL instance name.
EXEC master.dbo.xp_getnetname
Now we have enough information to change the name. e.g.
EXEC master.dbo.sp_dropserver 'SQLOld\I2K'
EXEC master.dbo.sp_addserver 'SQLNew\I2K','local'
Running this should show the old server name until we restart the SQL Services.
SELECT @@SERVERNAME
— results in SQLOld\I2K
Stop and restart the SQL Server services.
— results in SQLNew\I2K