Microsoft SQL Server

Microsoft Sql Server

Database name with hyphens

Sharepoint likes to use hyphens in database names, much to the displeasure of Sql Server.

Instead of use mydb-abdc-1234-asdf-0987; do use [mydb-abdc-1234-asdf-0987];

Procedure expects parameter ‘@parameters’ of type ‘ntext/nchar/nvarchar’

The sp_executesql Stored Procedure takes nvarchars as its parameters so…

If you are doing this:

EXEC sp_executesql @SQL, '@MyParamOut INT OUTPUT', @MyParamOut=@Foo

Do this instead:

EXEC sp_executesql @SQL, N'@MyParamOut INT OUTPUT', @MyParamOut=@Foo

NOLOCK and READPAST

For read only queries who favour speed over accuracy, try using these options to speed your query.

SELECT * FROM FOO WITH(NOLOCK)

SELECT * FROM FOO WITH(READPAST)

There is a good article at http://articles.techrepublic.com.com/5100-10878_11-6185492.html

What Version of SQL Server am I running?

When you need to know if you’re on workgroup / standard / enterprise edition? See KB321185.

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

What user is SQL Server running as?

Swiped from http://www.sqlmag.com/article/permissions/discovering-your-sql-server-user-account.aspx

Listing 1: The sp_get_sqlserver_service_account Stored Procedure
USE master
GO

IF (object_id(‘[dbo].[sp_get_sqlserver_service_account]’) IS NOT NULL AND
objectproperty(object_id(‘[dbo].[sp_get_sqlserver_service_account]’), N’IsProcedure’) =
1)
DROP PROC [dbo].[sp_get_sqlserver_service_account]
GO

CREATE PROC [dbo].[sp_get_sqlserver_service_account]
AS

— =============================================
— Variables
— =============================================
DECLARE @rc int,
@machinename nvarchar(128),
@instancename nvarchar(128),
@profile nvarchar(128),
@regkey nvarchar(256),
@accnt nvarchar(128)

IF NOT ((charindex(N’7.00′, @@version, 0) > 0) OR (charindex(N’8.00′, @@version, 0) >
0))
BEGIN
RAISERROR(N’sp_get_sqlserver_service_account only support SQL Server 7.0 and 2000′, 16, 1)
WITH log, nowait
END

IF (charindex(N’7.00′, @@version, 0) > 0)
BEGIN
SELECT @instancename = NULL
END

IF (charindex(N’8.00′, @@version, 0) > 0)
BEGIN
SELECT @machinename = CONVERT(nvarchar(128), serverproperty(‘MachineName’)),
@instancename = CONVERT(nvarchar(128),
serverproperty(‘InstanceName’))
END

— =============================================
— Check if not running SQL Server as LocalSystem
— @rc 0 = LocalSystem, 1 = running under account
— =============================================
EXEC @rc = master.dbo.xp_MSLocalSystem @machinename, @instancename
IF (@rc = 0)
BEGIN
PRINT ‘SQL Server is running under the LocalSystem account’
RETURN
END

— =============================================
— Get account name running SQL Server
— =============================================
IF (@instancename IS NULL)
BEGIN
SELECT @regkey = N’SYSTEM\CurrentControlSet\Services\MSSQLServer’
END
ELSE
BEGIN
SELECT @regkey = N’SYSTEM\CurrentControlSet\Services\MSSQL$’ + @instancename
END

EXEC master.dbo.xp_regread N’HKEY_LOCAL_MACHINE’,
@regkey,
N’ObjectName’,
@accnt OUTPUT,
N’no_output’

PRINT ‘SQL Server is running under account ‘ + @accnt
RETURN
GO

EXEC sp_get_sqlserver_service_account

Backup and Restore topics

http://technet.microsoft.com/en-us/library/aa337534%28SQL.90%29.aspx

Backup database to a UNC Path

See http://technet.microsoft.com/en-us/library/ms191304%28SQL.90%29.aspx

And be careful when using FORMAT !!

USE t_foo
GO
BACKUP DATABASE t_foo
TO DISK = '\\server\share$\backups\t_foo.bak'
WITH FORMAT,
NAME = 'Full Backup of t_foo'
GO

Error 5 when backing up database to UNC Path

It doesn’t matter who *you* are logged in as, it is the service account for SQL Server service that matters.

Grant the SQL Server Service account write permission to your folder.

Restore Database (Simple)

Find out the logical names from your .bak file:

RESTORE FILELISTONLY
FROM DISK='c:\backups\mydb_backup_20090101.bak'
GO

Restore the database:

-- Put it in single user mode --
...

— Restore (moving to different filenames if necessary —
— this allows you to restore a backup of database A —
— on top of database B) —
USE master;
GO
RESTORE DATABASE mydb
FROM DISK = ‘c:\backups\mydb_backup_20090101.bak’
WITH MOVE ‘mydb_data_logicalname’ TO ‘c:\data\mydb.mdf’,
MOVE ‘mydb_log_logicalname’ TO ‘c:\data\mydb.ldf’

— Put it in multi user mode —

Single User Mode

— Put it in single user mode —
ALTER DATABASE mydb
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
GO

Multiple User Mode

— Put it in multiple user mode —
ALTER DATABASE mydb
SET MULTI_USER
GO

Transaction log usage

How big do my transaction logs need to be? Why not check how much space they are using?

DBCC SQLPERF (LOGSPACE)

http://technet.microsoft.com/en-us/library/ms189768.aspx

http://www.mssqltips.com/tip.asp?tip=1225

Shrink the SQL Transaction Log for the Funk Database

N.B. Sets the recovery model to ‘simple’ to truncate the existing log before shrinking the file. You should set the recovery model back to your chosen model at the end of the script.

USE funk_db;
GO

ALTER DATABASE funk_db
SET RECOVERY SIMPLE;
GO

DBCC SHRINKFILE( funk_db_log, 1024 );
GO

/*
SET YOUR DESIRED RECOVERY MODEL HERE
*/

Overview of recovery models for your databases

What recovery model do all my databases use?

SELECT name, log_reuse_wait_desc FROM sys.databases;

Kill all connections to specified database

USE master
go

DECLARE @dbname sysname

SET @dbname = ‘name of database you want to drop connections from’

DECLARE @spid int
SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname)
WHILE @spid IS NOT NULL
BEGIN
EXECUTE (‘KILL ‘ + @spid)
SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname) AND spid > @spid
END

Microsoft Word Mail Merge SQL Server

Sub ConfigureMailMerge(clientId As Long, dsnFile As String, viewName As String)

Debug.Print “Call ConfigureMailMerge(” & Str(clientId) & “, ” & dsnFile & “, ” & viewName & “)”


Dim sql As String
‘sql = “SELECT * FROM ” & viewName & ” WHERE clientid=” & Trim(Str(clientId)) & “”
sql = “SELECT * FROM “”” & viewName & “”” WHERE clientid=” & Trim(Str(clientId)) & “”
‘sql = “SELECT * FROM ” & MYVIEW & ” WHERE clientid='” & Trim(Str(clientId)) & “‘”
Debug.Print sql

Dim sConn As String
sConn = “FILEDSN=” & dsnFile & “;”

ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource _
Name:=dsnFile, _
SQLStatement:=sql

Debug.Print “Datasource Record Count = ” & ActiveDocument.MailMerge.DataSource.RecordCount

Debug.Print “Return ConfigureMailMerge”

End Sub