<< A B C D E F G H I J K L M N O P Q R S T U V W X Y Z #

–A–

Access and SQL Server, differences

agent, SQL Server Agent - permission to see

use msdb

EXECUTE sp_addrolemember

@rolename = 'SQLAgentReaderRole',

@membername = 'domain\user'

(need to use msdb)

ALTER DATABASE failed because a lock could not be placed on database - You might get this error if you run a command to take a database offline. Need to find out who's preventing you from getting a lock. Run: EXEC sp_who2 to find out who's clogging up your database followed by kill <SPID> to actually kill the offender

append records

insert into DestinationTable
select OrigID , 'xx' as Source, CompanyName, LastName, FirstName, Address, City, ST, ZipCode, Phone, Fax, email
from SourceTable

or, if you only want to specify some of the fields, leaving the others to null or whatever their default is:

insert into Customer (Address, Apartment, CITY, STATE, ZIP_CODE, ZIP_Plus4, REFERRAL)
SELECT distinct Address, Apartment, CITY, STATE, ZIP_CODE, ZIP_Plus4, 'xx' as REFERRAL
from Source

–B–

backup directory, default - C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Backup

backup, schedule – here Login to Sql Management studio and connect to the required database.  Expand the Management Node from the object explorer, and then select the maintenance plan node.  Right click the maintenance plan and then select “new maintenance plan” or the wizard.  I could only ever use the wizard; nothing happened when I simply tried a new maintenance plan. Anyway, it failed with error #22022 (see below)

backup, location of last

select database_name, case type when 'L' then 'Log' else 'Data' end as Backuptype,

   physical_device_name, backup_start_date

from msdb.dbo.backupset a

   join msdb..backupmediaset b on a.media_set_id = b.media_set_id

   join msdb.dbo.backupmediafamily c on a.media_set_id = c.media_set_id

where type in ('L','D') -- L = Logbackup, D = Databackup

and backup_start_date > getdate()-10

--and 'your_db_name' = database_name

order by backup_start_date desc, database_name asc, Backuptype

backup failed with error # 22022 – need to try one of the 3 methods described here.  The 3rd one worked for me.  1st one did NOT.

First go through this sequence

Open SQL Server Configuration Manager.

In SQL Server Configuration Manager, click SQL Native Client Configuration, right-click Aliases, and then click New Alias.

In the Alias - New dialog box, select Named Pipes in the Protocol list.

In the Alias Name box, specify the name of the alias.

In the Server box, specify the instance of SQL Server 2005, and then click OK.

Open SQL Server Management Studio, and then connect the instance of SQL Server 2005.

Right-click SQL Server Agent, and then click Properties.

In the SQL Server Agent Properties dialog box, click Connection.

In the Alias local host server box, type the name of the alias that you specified in step 4, and then click OK.

In SQL Server Management Studio, right-click SQL Server Agent, and then click Restart.

This still didn’t work ‘til I enabled Named Pipes

In SQL Server Configuration Manager, in the console pane, expand SQL Server 2005 Network Configuration.

In the console pane, click Protocols for <instance name>.

In the details pane, right-click the protocol you want to change, and then click Enable or Disable.

In the console pane, click SQL Server 2005 Services.

Inthe details pane, right-click SQL Server (<instance name>), and then click Restart, to stop and restart the SQL Server service.

blanks, strip – ltrim(rtrim(@TempValue)) – see also special characters, strip out

–C–

carriage return

find

select count(*) from ExportContacts

where companyCode like '%'+char(13)+'%' or companyCode like '%'+char(10)+'%'

replace

update companyCode set companyCode = replace(replace(companyCode,char(10),''),char(13),'')

from companyCode where charindex(char(13), companyCode)<>0

cascade delete

You may have to first drop the existing foreign key constraint

ALTER TABLE dbo.T2

DROP CONSTRAINT FK_T1_T2

Once that's gone, you can go ahead and add the foreign key constraint with the cascade delete

ALTER TABLE dbo.T2

ADD CONSTRAINT FK_T1_T2_Cascade

FOREIGN KEY (EmployeeID) REFERENCES dbo.T1(EmployeeID) ON DELETE CASCADE

category, trim each category proportionally – see NTILE to trim each category proportionally

compare tables from 2 different databases – see join tables from 2 different databases

compare two tables - using a join

here's an example from here, assuming we are comparing tables A and B, and the primary key of both tables is ID:

SELECT 'Table A' as TableName, A.ID, A.COL1, A.COL2, A.COL3, ...

FROM A

UNION ALL

SELECT 'Table B' as TableName, B.ID, B.COL1, B.COl2, B.COL3, ...

FROM B

)tmp

GROUP BY ID, COL1, COL2, COL3 ...

HAVING COUNT(*) = 1

ORDER BY ID

The above returns all rows in either table that do not completely match all columns in the other. In addition, it returns all rows in either table that do not exist in the other table. It handles nulls as well, since GROUP BY normally consolidates NULL values together in the same group. If both tables match completely, no rows are returned at all.

The MIN() aggregate function used on the TableName column is just arbitrary -- it has no effect since we are only returning groups of rows in which there has been no consolidation with the GROUP BY (note the HAVING clause).

command line SQL – see sqlcmd

concatenate –

  1. when everything is varchar, very simple: +
  2. when attempting to concatenate an integer to varchar:

Note = Note + ', #' +  cast(@CustomerGoingAway as varchar(10))>

  1. when attempting to concatenate a memo or ntext to varchar:

DECLARE @ptrval binary(16)
SELECT @ptrval = TEXTPTR(SalesNote)
   FROM CustomerNote
   where CustomerFK = @CustomerGoingAway
-- the "null" 1st arg means tack it on the end (0 would put at beginning); 0 2nd arg deletes no data
UPDATETEXT CustomerNote.SalesNote @ptrval null 0 @strCustomerGoingAway;

concatenate several records of a related table into one field –

SELECT dupes.howMany as howManyFMOs, c.COMPANY_NAME, Dupes.CustomerFK,

   STUFF((SELECT ', ' + x.f AS [text()]

      FROM (select distinct CustomerFK, (ISNULL(FMO,'none')) as f

      from Project) x

   WHERE x.CustomerFK = Dupes.CustomerFK

   FOR XML PATH('')), 1, 1, '' ) AS whichFMOs

FROM

   (SELECT count(ISNULL(FMO,'none')) as howMany, CustomerFK

   FROM (SELECT DISTINCT FMO, CustomerFK

     FROM dbo.Project AS p) as PossibleDupes

      group by CustomerFK

      having count(ISNULL(FMO,'none'))>1) as Dupes

join Customer c on c.ID=Dupes.CustomerFK

order by howMany desc

connection actively refused - see error 10061

connections, view active – listing from here:

Instructions here:

contiguous records, find

select top 5 l.ID as start,
  (
    select min(a.ID) as ID
    from timesheet as a
        left outer join timesheet as b on a.ID = b.ID - 1
    where b.ID is null
        and a.ID >= l.ID
  ) as endID
from timesheet as l
    left outer join timesheet as r on r.ID = l.ID - 1
where r.ID is null
order by start desc

contiguous records, find gap – if ID is supposed to start off as contiguous and you want to find gaps where records were deleted

select top 5 start, stop, stop-start+1 as diff from (
  select m.ID + 1 as start,
   (select min(ID) - 1 from timesheet as x where x.ID > m.ID) as stop
  from timesheet as m
    left outer join timesheet as r on m.ID = r.ID - 1
  where r.ID is null
) as x
where stop is not null
order by stop desc

constraint, rename

sp_rename 'PK_ExportCompany', 'PK_ExportCompany_old'>

conversion failed when converting the varchar value ‘sample text’ to data type int.  I get this when trying to cast(SomeField) as int.  User-defined function:

CREATE FUNCTION dbo.UDF_ParseNumericChars
(
@string VARCHAR(8000)
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @IncorrectCharLoc SMALLINT
SET @IncorrectCharLoc = PATINDEX('%[^0-9]%', @string)
WHILE @IncorrectCharLoc > 0
BEGIN
SET
@string = STUFF(@string, @IncorrectCharLoc, 1, '')
SET @IncorrectCharLoc = PATINDEX('%[^0-9]%', @string)
END
SET
@string = @string
RETURN @string
END

GO

copy a table

SELECT * INTO newtable FROM originaltable

create table directly from a query – see table, create from a query

cross tab queries – list of 3rd party

links to code samples:

Dynamic Crosstab Queries – the best solution I've found.  Slightly modified form below (added @whereclause parameter):

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[sp_CrossTab]
  @table       AS sysname,        -- Table to crosstab
  @onrows      AS nvarchar(128),  -- Grouping key values (on rows)
  @onrowsalias AS sysname = NULL, -- Alias for grouping column
  @oncols      AS nvarchar(128),  -- Destination columns (on columns)
  @sumcol      AS sysname = NULL, -- Data cells
  @whereclause AS nvarchar(128) = NULL  -- where clause
AS
DECLARE

  @sql AS varchar(8000),
  @NEWLINE AS char(1)
 
SET @NEWLINE = CHAR(10)
 
-- step 1: beginning of SQL string
SET @sql =
  'SELECT' + @NEWLINE +
  '  ' + @onrows +
  CASE
    WHEN @onrowsalias IS NOT NULL THEN ' AS ' + @onrowsalias
    ELSE ''

  END
CREATE TABLE
#keys(keyvalue nvarchar(100) NOT NULL PRIMARY KEY)
 
DECLARE @keyssql AS varchar(1000)
SET @keyssql =
  'INSERT INTO #keys ' +
  'SELECT DISTINCT CAST(' + @oncols + ' AS nvarchar(100)) ' +
  'FROM ' + @table + @NEWLINE +
  CASE
    WHEN
@whereclause IS NOT NULL THEN @whereclause + @NEWLINE
    ELSE ''
  END
 
--PRINT @keyssql  + @NEWLINE -- For debug
EXEC (@keyssql)
 
DECLARE @key AS nvarchar(100)
SELECT @key = MIN(keyvalue) FROM #keys
 
WHILE @key IS NOT NULL
BEGIN
  SET
@sql = @sql + ',' + @NEWLINE +
    '  SUM(CASE CAST(' + @oncols +
      ' AS nvarchar(100))' + @NEWLINE +
    '   WHEN N''' + @key +
      ''' THEN ' + CASE
                     WHEN @sumcol IS NULL THEN '1'
                     ELSE '1' --@sumcol
                   END + @NEWLINE +
    '   ELSE 0' + @NEWLINE +
    ' END) AS [' + @key + ']'
 
  SELECT @key = MIN(keyvalue) FROM #keys
  WHERE keyvalue > @key
END
 
SET
@sql = @sql     + @NEWLINE +
  'FROM ' + @table  + @NEWLINE +
  CASE
    WHEN @whereclause IS NOT NULL THEN @whereclause + @NEWLINE
    ELSE ''
  END +
  'GROUP BY ' + @onrows + @NEWLINE +
  'ORDER BY ' + @onrows
 
--PRINT @sql  + @NEWLINE -- For debug
EXEC (@sql)

Microsoft SQL Server - Dynamic Cross-Tabs/Pivot Tables

A simple way to perform crosstab operations

Some third party applications:

Rac for SQL2K

Sql.Net

The Query Tool

xp_ags_crosstab

–D–

databases, join tables from 2 different – see join tables from 2 different databases

date, convert string to - CONVERT(datetime, MeetingDate + ' ' + MeetingTime, 102)

date, determine whether filled or not

SELECT CASE WHEN DATEDIFF(dd, 6 / 1 / 2006, BlanksSent) > 0 THEN 1 ELSE 0 END AS Expr2
FROM  dbo.Project

date, format change – from here:

create FUNCTION dbo.FormatDateTime (@dt DATETIME, @format VARCHAR(16))<
RETURNS VARCHAR(64)
AS
BEGIN
DECLARE
@dtVC VARCHAR(64)

SELECT @dtVC = CASE @format

WHEN 'LONGDATE' THEN DATENAME(dw, @dt) + ',' + SPACE(1) + DATENAME(m, @dt) + SPACE(1)

+ CAST(DAY(@dt) AS VARCHAR(2)) + ',' + SPACE(1) + CAST(YEAR(@dt) AS CHAR(4))

WHEN 'LONGDATEANDTIME' THEN DATENAME(dw, @dt) + ',' + SPACE(1) + DATENAME(m, @dt)

+ SPACE(1) + CAST(DAY(@dt) AS VARCHAR(2)) + ',' + SPACE(1) + CAST(YEAR(@dt) AS CHAR(4))

+ SPACE(1) + RIGHT(CONVERT(CHAR(20), @dt - CONVERT(DATETIME, CONVERT(CHAR(8), @dt, 112)), 22), 11)

WHEN 'SHORTDATE' THEN LEFT(CONVERT(CHAR(19), @dt, 0), 11)

WHEN 'SHORTDATEANDTIME' THEN REPLACE(REPLACE(CONVERT(CHAR(19), @dt, 0), 'AM', ' AM'), 'PM', ' PM')

WHEN 'UNIXTIMESTAMP' THEN CAST(DATEDIFF(SECOND, '19700101', @dt) AS VARCHAR(64))

WHEN 'YYYYMMDD' THEN CONVERT(CHAR(8), @dt, 112)

WHEN 'YYYY-MM-DD' THEN CONVERT(CHAR(10), @dt, 23)

WHEN 'YYMMDD' THEN CONVERT(VARCHAR(8), @dt, 12)

WHEN 'YY-MM-DD' THEN STUFF(STUFF(CONVERT(VARCHAR(8), @dt, 12), 5, 0, '-'), 3, 0, '-')

WHEN 'MMDDYY' THEN REPLACE(CONVERT(CHAR(8), @dt, 10), '-', SPACE(0))

WHEN 'MM-DD-YY' THEN CONVERT(CHAR(8), @dt, 10)

WHEN 'MM/DD/YY' THEN CONVERT(CHAR(8), @dt, 1)

WHEN 'MM/DD/YYYY' THEN CONVERT(CHAR(10), @dt, 101)

WHEN 'DDMMYY' THEN REPLACE(CONVERT(CHAR(8), @dt, 3), '/', SPACE(0))

WHEN 'DD-MM-YY' THEN REPLACE(CONVERT(CHAR(8), @dt, 3), '/', '-')

WHEN 'DD/MM/YY' THEN CONVERT(CHAR(8), @dt, 3)

WHEN 'DD/MM/YYYY' THEN CONVERT(CHAR(10), @dt, 103)

WHEN 'HH:MM:SS 24' THEN CONVERT(CHAR(8), @dt, 8)

WHEN 'HH:MM 24' THEN LEFT(CONVERT(VARCHAR(8), @dt, 8), 5)

WHEN 'HH:MM:SS 12' THEN LTRIM(RIGHT(CONVERT(VARCHAR(20), @dt, 22), 11))

WHEN 'HH:MM 12' THEN LTRIM(SUBSTRING(CONVERT(VARCHAR(20), @dt, 22), 10, 5) + RIGHT(CONVERT(VARCHAR(20), @dt, 22), 3))

ELSE 'Invalid format specified'

END

RETURN @dtVC

END

GO

Sample usage:

DECLARE @now DATETIME

SET @now = GETDATE()

 

PRINT dbo.FormatDateTime(@now, 'LONGDATE')

PRINT dbo.FormatDateTime(@now, 'LONGDATEANDTIME')

PRINT dbo.FormatDateTime(@now, 'SHORTDATE')

PRINT dbo.FormatDateTime(@now, 'SHORTDATEANDTIME')

PRINT dbo.FormatDateTime(@now, 'UNIXTIMESTAMP')

PRINT dbo.FormatDateTime(@now, 'YYYYMMDD')

PRINT dbo.FormatDateTime(@now, 'YYYY-MM-DD')

PRINT dbo.FormatDateTime(@now, 'YYMMDD')

PRINT dbo.FormatDateTime(@now, 'YY-MM-DD')

PRINT dbo.FormatDateTime(@now, 'MMDDYY')

PRINT dbo.FormatDateTime(@now, 'MM-DD-YY')

PRINT dbo.FormatDateTime(@now, 'MM/DD/YY')

PRINT dbo.FormatDateTime(@now, 'MM/DD/YYYY')

PRINT dbo.FormatDateTime(@now, 'DDMMYY')

PRINT dbo.FormatDateTime(@now, 'DD-MM-YY')

PRINT dbo.FormatDateTime(@now, 'DD/MM/YY')

PRINT dbo.FormatDateTime(@now, 'DD/MM/YYYY')

PRINT dbo.FormatDateTime(@now, 'HH:MM:SS 24')

PRINT dbo.FormatDateTime(@now, 'HH:MM 24')

PRINT dbo.FormatDateTime(@now, 'HH:MM:SS 12')

PRINT dbo.FormatDateTime(@now, 'HH:MM 12')

PRINT dbo.FormatDateTime(@now, 'goofy')

date portion of GETDATE() – DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)

day, subtract 3 days from today’s date – DATEADD(d, - 3, GETDATE())

day, beginning of today – DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)

day, end of (or, to be more precise, beginning of tomorrow) – DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 1)

day portion of date, with zeroes padded to left - RIGHT(REPLICATE('0', 2) + CONVERT(varchar(2), DATENAME(Day, theDate)), 2) AS theDay

days elapsed - (DATEDIFF (d, getDate(), EST_MAILING_DATE)) > 6

deadlocks, find - sp_who2

debug print – print statement (will work in stored procedures, will NOT work in functions)

printue'>print 'HowManyPresentersThisCustomer ' + cast(@HowManyPresentersThisCustomer as varchar(5))

decimal, view doesn’t display after a calculation like division – need to use the CAST statement:

SELECT id,

AVG(CAST(qty AS numeric(12,2))) AS avg_qty

FROM sales

If you divide, it seems you must use the cast statement in the numerator as well as for the whole evaluated expression:

SELECT TOP (100) PERCENT P1.ID, P1.CustomerFK, SUM(ISNULL(dbo.RestaurSeminar.Count, 0)) AS TotAttendees, P1.NUM_ACTUALLY_MAILED,

   P1.PROJECT_NAME, P1.PRINTED, P1.EST_MAILING_DATE, P1.ACCOUNT_MANAGER, P1.NUM_TO_MAIL,

   CAST(CASE WHEN P1.[NUM_ACTUALLY_MAILED] = 0 THEN 0 ELSE CAST(SUM(dbo.RestaurSeminar.[COUNT]) AS decimal(6, 4))

   / P1.[NUM_ACTUALLY_MAILED] END AS decimal(6, 4)) AS results

FROM dbo.Customer INNER JOIN

   dbo.Project AS P1 ON dbo.Customer.ID = P1.CustomerFK INNER JOIN

   dbo.Project ON dbo.Customer.ID = dbo.Project.CustomerFK LEFT OUTER JOIN

   dbo.RestaurSeminar ON P1.ID = dbo.RestaurSeminar.ProjectFK

GROUP BY P1.ID, P1.PRINTED, dbo.Project.ID, P1.CustomerFK, P1.NUM_ACTUALLY_MAILED, P1.PROJECT_NAME, P1.PRINTED, P1.EST_MAILING_DATE,

           P1.ACCOUNT_MANAGER, P1.NUM_TO_MAIL, dbo.RestaurSeminar.Count

HAVING (dbo.Project.ID = 3232)

ORDER BY P1.PRINTED DESC

delete inner join

delete dbo.FirstTable

FROM   dbo.SecondTable INNER JOIN dbo.FirstTable

ON dbo.SecondTable.MaxID = dbo.FirstTable.ID

dense rank

SELECT TOP 50
   dense_rank() OVER (ORDER BY RIGHT(replicate('0', 2) + cast(COUNT(c1.ID) AS varchar(3)), 3) + ' ' +
         cast(CONVERT(CHAR(8), MAX(p1.EST_MAILING_DATE), 112) AS varchar(30)) DESC) AS 'RANK',
   COUNT(c1.ID) AS NumMailings, c1.COMPANY_NAME,
   MAX(p1.EST_MAILING_DATE) AS MostRecentEMD
FROM dbo.Customer AS c1 INNER JOIN
       dbo.Project AS p1 ON c1.ID = p1.CustomerFK
GROUP BY c1.COMPANY_NAME
HAVING max(p1.EST_MAILING_DATE) > '1/1/8'
ORDER BY RIGHT(replicate('0', 2) + cast(COUNT(c1.ID) AS varchar(3)), 3) + ' ' +
   cast(CONVERT(CHAR(8), MAX(p1.EST_MAILING_DATE), 112) AS varchar(30)) DESC

display table info –

Select * From Information_Schema.Columns Where Table_Name = 'Customer'

or, for considerably more info:

sp_help Customer

Docmd.TransferText – convert this command from Access to SQL server – from here

Dim rs As New ADODB.Recordset
Dim strMsg As String
Dim strSQL As String
Dim strText As String
Dim strFileText As String
Dim strHeaderText As String
Dim fs
Dim a
Dim CommandText As String
Dim QueryName As String
Dim cn As ADODB.Connection
Set cn = CurrentProject.Connection
QueryName = "dbo.[" & QueriesCB.Value & "]"
strSQL = "SELECT * FROM " & QueryName
 
rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly
'rs.Open CommandText, CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdText
 
If rs.RecordCount = 0 Then
 DoCmd.Hourglass False
 MsgBox "No Records to Export!"
 Exit Sub
End If
 
strHeaderText = strHeaderText & """First""" & ","
strHeaderText = strHeaderText & """Last""" & ","
strHeaderText = strHeaderText & """Email"""
strFileText = strFileText & strHeaderText & vbCrLf
 
rs.MoveFirst
Do While Not rs.EOF
   strText = strText & """" & rs(0) & """" & ","
   strText = strText & """" & rs(1) & """" & ","
   strText = strText & """" & rs(2) & """"
   strFileText = strFileText & strText & vbCrLf
   strText = ""
   rs.MoveNext
Loop
 
Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.CreateTextFile("C:\Program Files\xx\xx.csv", True)
a.WriteLine (strFileText)
a.Close
rs.Close
Set rs = Nothing
 
'DoCmd.TransferText acExportDelim, , QueriesCB.Value, "C:\Program Files\xx\xx.csv", True

DOS command, get SQL from - see sqlcmd

drop index, test to see whether it exists first

IF EXISTS (SELECT name FROM sysindexes WHERE name = 'IX_yourTable_companyCode') DROP INDEX IX_yourTable_companyCode ON yourTable

duplicates, get rid of

delete from dbo.ProblemTable

where ID in

(SELECT  MAX(ID) AS ID

FROM   dbo.ProblemTable

GROUP BY Email

HAVING  (COUNT(Email) > 1))

–E–

elapsed days - (DATEDIFF (d, getDate (), EST_MAILING_DATE)) > 6

elapsed time in days, hours, minutes, seconds

select top 10 HowMany = count(id), oldest=min(dateadded), newest=max(dateadded),

timeBetweenFirstAndLast =

case cast(datepart(day, max(dateadded) - min(dateadded)) - 1 as varchar(10)) when 0 then ''

  else cast(datepart(day, max(dateadded) - min(dateadded)) - 1 as varchar(10)) + ' days ' end +

case cast(datepart(hour, max(dateadded) - min(dateadded)) as varchar(10)) when 0 then ''

  else cast(r:gray'>(datepart(hour, max(dateadded) - min(dateadded)) as varchar(10)) + ' hours ' end +

case cast(datepart(minute, max(dateadded) - min(dateadded)) as varchar(10)) when 0 then

  else cast(r:gray'>(datepart(minute, max(dateadded) - min(dateadded)) as varchar(10)) + ' minutes ' end +

cast(datepart(second, max(dateadded) - min(dateadded)) as varchar(10)) + ' seconds'

, host

from LogException

where message='xxx'

and datecreated>='2013-06-01 16:14'

and eventType not in ('INFO', 'DEBUG')

group by host

having count(id) > 1

order by count(id) desc

email, split out email part (as opposed to domain part) and then split out first, last name if separated by periods.  Argh!  What a mess!

SELECTe'>SELECT  email, LEFT(email, CHARINDEX('@', email) - 1) AS EmailPart,
CASE WHEN (CHARINDEX('.', LEFT(email, CHARINDEX('@', email) - 1)) > 0)
  THEN LEFT(LEFT(email, CHARINDEX('@', email) - 1), CHARINDEX('.', LEFT(email, CHARINDEX('@', email) - 1)) - 1)
  ELSE '' END AS FirstName,
CASE WHEN (CHARINDEX('.', LEFT(email, CHARINDEX('@', email) - 1)) > 0)
  THEN SUBSTRING(LEFT(email, CHARINDEX('@', email) - 1), CHARINDEX('.', LEFT(email, CHARINDEX('@', email) - 1)) + 1,
   LEN(LEFT(email, CHARINDEX('@', email) - 1)) - LEN(CASE WHEN (CHARINDEX('.',
          LEFT(email, CHARINDEX('@', email) - 1)) > 0)
         THEN LEFT(LEFT(email, CHARINDEX('@', email) - 1), CHARINDEX('.', LEFT(email, CHARINDEX('@', email)
           - 1)) - 1) ELSE '' END) - 1) ELSE '' END AS LastName
FROM dbo.ListTwo

email when job fails

Create a Database Mail profile

Enable Database Mail

make sure you add an operator (under SQL Server Agent)

also check

Select top 50 *

from msdb.dbo.sysmail_mailitems

order by mailitem_id desc

 

Select top 50 *

from msdb.dbo.sysjobhistory

order by instance_id desc

 

EXECUTE msdb.dbo.sp_notify_operator @name=N'PortalAdmin',@body=N'Test Message'

empty string, test for in ntext datatype field – DataLength() = 0

export - right click database, tasks, export data.  When I tried this, I got errors:

–F–

find location of a small string in a bigger string - CHARINDEX - SELECT SYSTEM_USER AS 'Login Name', CHARINDEX( '\', SYSTEM_USER) AS backslash_position,

field, drop -

if exists(select * from sys.columns where Name = N'nameFull' and Object_ID = Object_ID(N'GCP_FMExportContact'))

ALTER> TABLE GCP_FMExportContact DROP COLUMN nameFull

field length, maximum - select max(len(extension)) from Person

field lengths of a table - see also table fields, list for a query that has this and more info on fields

SELECT TableName = OBJECT_NAME(c.OBJECT_ID), ColumnName = c.name, DataType = t.name, MaxLength = c.max_length

FROM sys.columns AS c

  JOIN sys.types AS t

ON c.user_type_id=t.user_type_id

WHERE OBJECT_NAME(c.OBJECT_ID) = 'MyTable'

fields in a database, list all

Using OBJECT CATALOG VIEWS

SELECT T.NAME AS [TABLE NAME], C.NAME AS [COLUMN NAME], P.NAME AS [DATA TYPE], P.MAX_LENGTH AS[SIZE],

CAST(P.PRECISION AS VARCHAR) +'/'+ CAST(P.SCALE AS VARCHAR) AS [PRECISION/SCALE]

FROM SYS.OBJECTS AS T

JOIN SYS.COLUMNS AS C

ON T.OBJECT_ID=C.OBJECT_ID

JOIN SYS.TYPES AS P

ON C.SYSTEM_TYPE_ID=P.SYSTEM_TYPE_ID

WHERE T.TYPE_DESC='USER_TABLE';

or Using INFORMATION SCHEMA VIEWS

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION,

COLUMN_DEFAULT, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,

NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE,

DATETIME_PRECISION

FROM INFORMATION_SCHEMA.COLUMNS

fields in a table, list - see table fields, list

first word – return the first word of a multi-worded expression.  The CASE statement is required so as not to bomb out on expressions that contain only one word.  The expression below will get rid of middle initials, nicknames in parenthesis, etc.

CASE CHARINDEX(' ', dbo.Person.FirstName) WHEN 0 THEN FirstName ELSE SUBSTRING(dbo.Person.FirstName, 1, CHARINDEX(' ', dbo.Person.FirstName) - 1) END AS [First Name]

first name, untangling when extra initials, nicknames, etc. mixed in

Select FirstName,
CASE
   WHEN FirstName IS NULL or len (FirstName) = 1
   THEN 'Agent'
   ELSE
      -- Even though FirstName is supposed to be one monolithic name, often they'll
      -- throw in middle or first initials or a nick name.
      -- If we're going to do a mail merge where we're greeting them by first name,
      -- that'll look dumb.

    (CASE CHARINDEX(' ', FirstName)
        WHEN 0
        THEN FirstName    -- no spaces?  We're good to go with no further work.
        ELSE
           -- Sometimes you have first initial followed by name, other times
           -- name followed by middle initial.  We want the main part of the
           -- name without the initial either way.

           case when len(FirstName)  < CHARINDEX(' ', FirstName) * 2
              -- First, see whether the first part takes up at least half
              -- the whole string.  If it does, the last part is probably
              -- a middle initial and we can just use the first part and
              -- discard the middle initial
              then
                 -- Sometimes all you have is two initials separated by a space,
                 -- like "B J".  In this case, it'd look kinda dumb to just
                 -- show "B".  So for such short instances, display both initials.

                 case when len(FirstName) < 4
                       then FirstName
                       else SUBSTRING(FirstName, 1, CHARINDEX(' ', FirstName))
                 end
              else
                 -- Just because the first part is smaller than the last part,
                 -- we still don't just automatically use the last part.
                 -- Sometimes you get two fully formed names, with the second
                 -- being the middle name.  In that case we'd want the first part.
                 -- So look to see if the first part looks like
                 -- 1. an initial all by itself
                 --    (first place we'd see a space would be 2)
                 -- 2. initial followed by a period or two-letter title (like 'Dr')
                 --    (first place we'd see a space would be 3)
                 -- 3. two-letter title (like 'Dr.') followed by a period
                 --    (first place we'd see a space would be 4)
                 -- If the first place we see a space is <= 3, then that first part
                 -- is probably an initial and we'll use the 2nd part.  Otherwise
                 -- stick with the first part.

                 case when CHARINDEX(' ', FirstName) <= 4

                       then substring(FirstName,charindex(' ',FirstName)+1,len(FirstName))
                       else SUBSTRING(FirstName, 1, CHARINDEX(' ', FirstName))
              end
           end
    END
)
END AS First
-- This doesn't properly display things like 'Gary & Jane' or catch the nickname a human
-- would use like choosing 'Hank' in 'Henry "Hank"' or deal well with Chinese names
-- like 'Hu Ling Fu' where which is right is anybody's guess

From Person

float field, try to convert to int - get a TABLOCK error - this only happened when I was in Access linked to the SQL. When I did in SQL itself, went away.

foreign key, allow to be null – set the “default value or binding” for the parent table’s key field to (NULL)

foreign key constraint, remove all and then add them all back again

/* Disable_Enable_ForeignKeys.sql -- Generate SQL statements to disable or enable all foreign key

  constraints in the current database */

 

/* Show foreign keys that are disabled or untrusted in the current database */

select * from sys.foreign_keys where is_disabled = 1

select * from sys.foreign_keys where is_not_trusted = 1

 

/* Generate sql statements to disable all foreign key constraints in the current database */

set nocount on

SELECT 'ALTER TABLE [' + s.name + '].[' + o.name + '] NOCHECK CONSTRAINT [' + i.name + ']'

FROM sys.foreign_keys i

INNER JOIN sys.objects o ON i.parent_object_id = o.object_id

INNER JOIN sys.schemas s ON o.schema_id = s.schema_id

WHERE 1=1

AND i.is_not_for_replication = 0

AND i.is_disabled = 0

 

/* Generate sql statements to re-enable disabled or untrusted foreign key constraints

in the current database */

set nocount on

SELECT 'ALTER TABLE [' + s.name + '].[' + o.name + '] WITH CHECK CHECK CONSTRAINT [' + i.name + ']'

FROM sys.foreign_keys i

INNER JOIN sys.objects o ON i.parent_object_id = o.object_id

INNER JOIN sys.schemas s ON o.schema_id = s.schema_id

WHERE 1=1

AND i.is_not_for_replication = 0

AND (i.is_disabled = 1 OR i.is_not_trusted = 1)

full outer join – see outer join, full

fuzzy lookup – see here

Open Business Intelligence Development Studio.

Create a new Integration Services Project, add a new package – sounds simple, right?  Well, go here for instructions.  In short, On the File menu, point to New, and click Project to create a new Integration Services project.  In the New Project dialog box, select Integration Services Project in the Templates pane.  If you don’t see “Integration Services Project” as one of the choices here, you’re in trouble and you need to fix this problem before you go any further!  Also, as a hint, make sure you select the “Business Intelligence Projects” folder in the left pane and NOT the “Visual Studio Solutions” folder in the “Other Project Types”

click the Data Flow tab, and then accept the add a data flow item option.

From the Control Flow Items section in the Toolbox, drag a Data Flow Task onto the control Flow surface. Double-click the new Data Flow Task or select the Data Flow tab.

On the Data Flow surface, drag the OLE DB Source adapter from the Data Flow Sources section of the Toolbox. Drag a Fuzzy Lookup Transformation from the Data Flow Transformations section of the Toolbox and an OLE DB Destination adapter from the Data Flow Destinations section. Select the Source and drag the green arrow to the Fuzzy Lookup to create a path between the two. Also create a path between Fuzzy Lookup and the Destination by selecting Fuzzy Lookup and dragging the green arrow to the Destination.

Double-click the OLE DB Source transform and configure it to point at your new data by selecting a connection and the input table that contains reference data that incoming records will be matched to.

Double-click Fuzzy Lookup to open the custom user interface (UI). From the Reference table name drop-down menu, select the connection and table to which you want the transform, to your already warehoused reference data, to point.

On the Columns tab, drag items you want to compare from Available Input Columns (from the OLE DB source) to Available Lookup Columns& (from the reference table). For example, you might want to compare StreetAddress in the input with Address in the reference table.

Select the check boxes for all items in Available Lookup Columns, and then click OK.

Point the OLE DB Destination to a connection for which you can write a new table, and then clickNew. Accept the default creation statement, and you are now ready to run Fuzzy Lookup.

To run the package you just created, right-click its name in the Solution Explorer window, and then select Execute.

–G–

gap, find in contiguous records,– see contiguous records, find gap

group and include an ntext (memo) field

Let’s say SalesNote below is an ntext (memo) field.  But we want the max of EST_MAILING_DATE and MAILED.  How do we group when we get errors when trying to work with the ntext field?  Coalesce.

SELECT p.FirstName, p.LastName, p.eMail, cn.SalesNote, pr.maxEMD AS RecentEMD, pr.maxMailed AS RecentMailed

FROM dbo.Customer AS c INNER JOIN

   dbo.Person AS p ON p.CustomerFK = c.ID INNER JOIN

   dbo.CustomerNote AS cn ON c.ID = cn.CustomerFK LEFT OUTER JOIN

    (SELECT     CustomerFK, MAX(EST_MAILING_DATE) AS maxEMD, MAX(MAILED) AS maxMailed

      FROM          dbo.Project

      GROUP BY CustomerFK) AS pr ON c.ID = pr.CustomerFK

WHERE  (cn.SalesNote LIKE '%' + 'sent' + '%')

The thing in parens is a derived table. Logically it is a temp table within the table, but it never materialized.

grant permissions

GRANT SELECT ON OBJECT::ExportCompany TO FMExport

GRANT INSERT ON OBJECT::ExportCompany TO FMExport

GRANT UPDATE ON OBJECT::ExportCompany TO FMExport

group by bands or ranges of numbers

SELECT  '1K - 3500' AS NumMailedRange, COUNT(ID) AS HowMany

FROM      dbo.Project AS p

WHERE  (YEAR(MAILED) = 2011) AND (NUM_ MAILED BETWEEN 1000 AND 3500)

UNION

SELECT  '3501 - 5500' AS NumMailedRange, COUNT(ID) AS HowMany

FROM      dbo.Project AS p

WHERE  (YEAR(MAILED) = 2011) AND (NUM_ MAILED BETWEEN 3501 AND 5500)

group by every 10 minutes

select which10MinuteSegment = STUFF(CONVERT(VARCHAR(5), dateadded, 10), 3, 1, '-') + ' ' +

cast(RIGHT(REPLICATE('0', 2) + CONVERT(varchar(2),DATEPART(HOUR, dateadded)),2) as varchar(4)) + ':' +

cast(DATEPART(minute, dateadded)/10 as varchar(2)) + '0',

howMany=count(id)

from gidcrm_log..LogException

where dateadded between DATEADD(hh, - 6, GETDATE()) and getdate()

group by STUFF(CONVERT(VARCHAR(5), dateadded, 10), 3, 1, '-') + ' ' +

cast(RIGHT(REPLICATE('0', 2) + CONVERT(varchar(2),DATEPART(HOUR, dateadded)),2) as varchar(4)) + ':' +

cast(DATEPART(minute, dateadded)/10 as varchar(2)) + '0'

order by STUFF(CONVERT(VARCHAR(5), dateadded, 10), 3, 1, '-') + ' ' +

cast(RIGHT(REPLICATE('0', 2) + CONVERT(varchar(2),DATEPART(HOUR, dateadded)),2) as varchar(4)) + ':' +

cast(DATEPART(minute, dateadded)/10 as varchar(2)) + '0'

group by minute

select DATEPART(MINUTE, datecreated), message, UserName, count(message) as count

fr logexception

whereue'>where datecreated between '7/2/13 23:00' and '7/3/13 0:00'

and eventType = 'ERROR'<

group by EventType , message, UserName, DATEPART(MINUTE, datecreated)

order by DATEPART(MINUTE, datecreated), count(message) desc;

group by month

SELECT  month, COUNT(*) AS HowMany

FROM   (SELECT  CONVERT(char(6), MAILED, 112) AS month

          FROM dbo.Project) AS a

GROUP BY month

group by week – GROUP BY DATEPART(WEEK,t1.timeStamp).  For an example of a stored procedure emulating a cross-tab query.

group, trim number in each category of the group proportionally – see NTILE to trim each category proportionally

–H–

how many years ago - DATEDIFF(year , c.DATE_RECORD_ENTERED, GETDATE()) AS NumberOfYears

–I–

ID of SQL Server instance doesn't match ID of database - this is common if you restore a database from another server. (You might also want to look at user already exists in current database (error 15023))

run

exec sp_help_revlogin

from the master database of the server you which has the “good” ID you want to match in your new, destination db.   SQL Server installs don't automatically come with this “sp_help_revlogin” stored procedure out of the box.   You must dig it up from somewhere.  Run this stored procedure on the server with the “good” ID.  It should produce a bunch of entries that look something like this:

CREATE LOGIN [userthatneedsfix]

WITH PASSWORD = 0x0200C40E41234D4A35B33B5533FF3F7D075ED0D94433BDC1DB4E7A805467F578626401EE5E8E44B68B42DF5A23766AEC3B1FBDAFB24AFC52A5A5AD17C33E82F8D9D3FE07F1D2 HASHED,

SID= 0x79DA24905409248AA28A44982051A41F, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF

along with a whole bunch of other IDs.   Copy this code to use on your destination database.   Before you actually run this code, first delete the “bad” ID on your destination database first.

You might get a complaint if this particular user happens to own the database. This will especially be a problem if this user is the only owner of the database.  In which case, run

ALTER AUTHORIZATION on database::yourdb to someotheruser

iif – change to CASE or if…else

Change MS Access

Select iif(field>10,"large", "small") as Size from Table

To SQL Server CASE command

SELECT Size =

  CASE field

  WHEN field > 10 THEN "large"

  ELSE "small"

END

from Table

or change

Select IIf([PROOF_APPROVED] Is Not Null

And [NAMES_RECEIVED] Is Not Null,1,0) AS Production

From Project

to

SELECT     CASE

  WHEN PROOF_APPROVED IS NOT NULL AND NAMES_RECEIVED IS NOT NULL

  THEN 1

  ELSE 0

  END AS Production

FROM          dbo.Project

if…else – note that there is no "then" – here

example:

if exists(select * from Northwind.dbo.Customers

      where CustomerId = 'ALFKI')

    Print 'Need to update Customer Record ALFKI'

else

    Print 'Need to add Customer Record ALFKI'

increment a field autmatically starting at the highest version already in that field (for select into)

select (SELECT MAX(ID)

FROM ws_retailer) + ROW_NUMBER() OVER (order by ), [Business Name]ess Name],

Address, City, State, Zip, Country, Website from test123 where id is null;

index creation times out using GUI - make a script by right-clicking in white area, "Generate change script", just select the "Alter table … add constraint" part and run that.

This trick is from is from here

index, test to see whether it exists before dropping dropping

IF:blue'>IF EXISTS (SELECT name FROM sysindexes WHERE name = 'IX_yourTable_companyCode') DROP INDEX IX_yourTable_companyCode ON yourTable

…and then to rebuild it:

CREATE NONCLUSTERED INDEX IX_yourTable_companyCode ON yourTable (companyCode)

indices - this seems to find only primary keys - and only deals with constraints

select t.TABLE_SCHEMA, t.TABLE_NAME,
   case objectproperty(object_id(t.TABLE_NAME), 'TableHasClustIndex')
     when 1 then 'Has clustered index'
     when 0 then 'Does not have clustered index'
   end,
   tc.CONSTRAINT_NAME as 'PK Name',
   kcu.COLUMN_NAME as 'PK column',
   kcu.ORDINAL_POSITION
from INFORMATION_SCHEMA.TABLES as t
left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc
  on (t.TABLE_SCHEMA = tc.TABLE_SCHEMA and
    t.TABLE_NAME   = tc.TABLE_NAME   and
    tc.CONSTRAINT_TYPE = 'PRIMARY KEY')
left outer join INFORMATION_SCHEMA.KEY_COLUMN_USAGE as kcu
  on (tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME)
where t.TABLE_TYPE = 'BASE TABLE'
order by t.TABLE_SCHEMA, t.TABLE_NAME, kcu.ORDINAL_POSITION

inner join, delete

delete FirstTable
FROM   SecondTable INNER JOIN FirstTable
ON SecondTable.MaxID = FirstTable.ID

or (with a "where" clause)

DELETE table1

from table1 t INNER JOIN table2 r

ON r.RecordNumber = t.testPkgId

and (r.[New Name] = N'bob')

insert into existing table

insert into Leads

(FirstName,Phone,eMail)

values ('Bob Smith','123456789','bob@smith.com')

insert into a new table

SELECT DISTINCT ID, street, apartment, city, state1, zip, zip4

INTO     RMCAddrOnly

FROM     dbo.goodAddresses

Instr equivalent in SQL Server: CHARINDEX

change

SELECT Left([email],InStr([email],"@")-1) AS EmailPart

FROM SalesArt_Combined

to

SELECT LEFT(email, CHARINDEX('@', email) - 1) AS EmailPart

FROM    SalesArt_Combined

integers only display in view even though you want some decimal values – see decimal, view doesn’t display after a calculation

is Null equivalent - ISNULL(fieldA,0)

–J–

job alerts

Create a Database Mail profile - I

Enable Database Mail

join, inner, delete

delete FirstTable

FROM   SecondTable INNER JOIN FirstTable

ON SecondTable.MaxID FirstTable.ID

join, outer full – see outer join, full

join tables from 2 different databases– both the databases have to be on the same server for this to work:

SELECT     new.doNotEMail, old.doNotEmail as oldDoNotEmail,

            new.WhyNotEmail, old.whynotemail as oldWhyNotEmail,

            new.WhenDecidedNotEmail, old.WhenDecidedNotEmail as oldWhenDecidedNotEmail

FROM         dbo.Person as new inner join SC012412.dbo.Person as old

on new.ID = old.ID

where new.doNotEMail <> old.doNotEMail

or (new.doNotEMail is null and old.doNotEMail is not null)

or (new.doNotEMail is not null and old.doNotEMail is  null)

here’s how to update:

UPDATE Person

SET WhyNotEmail = old.WhyNotEmail, doNotEMail=old.doNotEMail,

WhenDecidedNotEmail=old.WhenDecidedNotEmail

FROM SCold.dbo.Person old INNER JOIN Person new

ON  old.ID = new.ID

where (new.doNotEMail is null and old.doNotEMail is not null)

and old.WhyNotEmail = 'Blocked'

–K–

key, restart numbering for (identity) -

dbcc checkident("YourTable",reseed,1)

–L–

last month’s stuff

SELECT  CASE WHEN Customer.FMO = 'Bob' THEN 'Bob' ELSE 'not Bob' END AS FMO, COUNT(dbo.Project.ID) AS ProjCount

FROM      dbo.Project INNER JOIN dbo.Customer ON dbo.Project.CustomerFK = dbo.Customer.ID

WHERE  (dbo.Project.ACCOUNT_MANAGER = N'Sam')

AND (dbo.Project.MAILED between

dateadd(mm, -1, convert(datetime, convert(char(7), getdate(), 120) + '-01'))– the first day of last month

and convert(datetime, convert(char(7), getdate(), 120) + '-01')) – first day of this month

GROUP BY CASE WHEN Customer.FMO = 'Bob' THEN 'Bob' ELSE 'not Bob' END

length of ntext – DataLength()

length of varchar – len()

length of a field, maximum - select max(len(extension)) from Person

line numbers

Tools → Options → TextEditor → All Languages → Line Numbers check box available under Display section.

linked database, copy table to -

Here's a few things that do NOT seem to work

If you DON'T have a table already on the remote server, when you try:

insert into [db0.global-id.colo].gidcrm_test.dbo.GCP_Temp_20130613b_FMExport

select *

from GCP_FMExport

you get:

Msg 7314, Level 16, State 1, Line 1

The OLE DB provider "SQLNCLI11" for linked server "db0.global-id.colo" does not contain the table ""gidcrm_test"."dbo"."GCP_Temp_20130613b_FMExport"". The table either does not exist or the current user does not have permissions on that table.

list all views - Select TABLE_NAME From Information_Schema.Tables where TABLE_TYPE = 'VIEW'

local server, cannot connect to

from here, from command line

dcomcnfg

From the window that pops up, services, computers, my computer, DCOM config, find the MsDtsServer100, right click to get “Properties, click the “Security” tab, click “Edit” in the “Launch” and Activate Permissions. Add the local SQL Agent User group and enable local launch and Local activation.  I couldn't get this to work because I couldn't add “SQLAgent” or “SQL Agent”

Also tried futzing around in the SQL Server Configuration Manager.  To no avail.

logged in, who am I logged in as?

SELECT SYSTEM_USER AS 'Login Name'

or

SELECT substring(SYSTEM_USER,CHARINDEX( '\', SYSTEM_USER)+1,len(SYSTEM_USER)) as log2

log in of SQL Server instance doesn't match log in of database - see ID of SQL Server instance doesn't match ID of database. But you might also want to look at user already exists in current database (error 15023)

longest string in a column, find

SELECT top 1 FMO,len(FMO) as len

  FROM Customer

ORDER BY len DESC

loop through T-SQL Result Set – without using a cursor

Declare @au_id Varchar(20)

Select @au_id = Min(au_id) from authors

While @au_id IS NOT NULL

Begin

  Select au_id, au_lname, au_fname from authors Where au_id = @au_id

  Select @au_id = min(au_id) from authors where au_id > @au_id

End

–M–

match ID of SQL Server instance to ID of database - see ID of SQL Server instance doesn't match ID of database or perhaps user already exists in current database (error 15023)

maximum length of a field - select max(len(extension)) from Person

maximum of two fields

SELECT TOP (100) PERCENT CustomerFK, MAX(date) AS MaxDate

FROM (SELECT CustomerFK, MAILED AS date

  FROM Project

  UNION ALL

  SELECT CustomerFK, EST_MAILING_DATE AS date

  FROM     dbo.Project AS p2) AS CombProjDates

GROUP BY CustomerFK

ORDER BY CustomerFK

memo (ntext) field, group on – see group on an ntext (memo) field

Mid (in Access) equivalent: SUBSTRING(expression, startposition, length)

month, first day of current month – SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(getdate())-1),getdate()),101) AS Date_Value, 'First Day of Current Month' AS Date_Type

month, first day of next month – SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,getdate()))-1),DATEADD(mm,1,getdate())),101) ,'First Day of Next Month'

month, group by

SELECT month, COUNT(*) AS HowMany

FROM (SELECT   CONVERT(char(6), MAILED, 112) AS month

  FROM Project) AS a

GROUP BY month

month, last month’s stuff – see last month’s

month, last day of current month – SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,getdate()))),DATEADD(mm,1,getdate())),101) , 'Last Day of Current Month'

month, last day of previous month – SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(getdate())),getdate()),101) , 'Last Day of Previous Month'

But this actually gets midnight (the very beginning) of the last day of last month. If you’re trying to get right up to the end of the month (midnight of the first day of this month), might want to try the the somewhat convoluted:

selectDATEADD(dd, DATEDIFF(dd, 0,DATEADD(MONTH, DATEDIFF(MONTH, - 1, GETDATE()) - 1, - 1)), 1)

I’m sure there's a more straightforward way to accomplish the same thing by not finding the last day of last month and then adding a day but instead just finding midnight of first day of this month…

month, subtract 3 months from today’s date - DATEADD(m, - 3, GETDATE())

month crosstab – see Rozenshtein Method

most recent record for each person

SELECT  ID, employeeFK, statusDate, description

FROM statusReport AS T

WHERE  (statusDate =

    (SELECT     MAX(statusDate) AS MostRecent

    FROM          dbo.statusReport

    WHERE      (employeeFK = T.employeeFK)))

MUST_CHANGE is on

USE Master

GO

ALTER LOGIN UserName WITH PASSWORD = 'password'

GO

ALTER LOGIN UserName WITH

CHECK_POLICY = OFF,

CHECK_EXPIRATION = OFF;

–N–

network-related or instance-specific error occurred while establishing a connection to SQL Server - see error 10061

now() – GETDATE()

ntext (memo) field, group on – see coalesce – a way to group on an ntext (memo) field

NTILE to trim each category proportionally

We have a list of 9708 people spread out across 32 zip codes.  We want to shrink that down to 8000 people spread out across the same 32 zip codes, keeping roughly the same proportion of people in each zip code.  There are 1425 people in the zip code with the greatest number of people and only one person in the zip code with the smallest number of people.  So start with a view

WITH SlicedData AS

(

  SELECTZIP, INDIVIDUALNAME, NTILE(36) OVER (PARTITION BY Zip

  ORDER BY COUNT(INDIVIDUALNAME) DESC

) AS 'Ntile'

FROM List

GROUP BY ZIP, INDIVIDUALNAME)

  SELECT ZIP, INDIVIDUALNAME

   FROM  SlicedData

   WHERE Ntile > 6

I had to fiddle with the numbers by trial and error ‘til I found the magic “35” as total number of slices and “6” as the number of slices we’re discarding that worked reasonably well in this case.  In other words, we’re lopping off the top 6/35th records from each zip code.  This final choice of “35” and “6” yields 8003 records over 29 zip codes – pretty darn close to our 8000 target.

I started out with “20” and “3” but changing the “3” around gave increments that were too big.  For instance, “3” gave me 8204 records but “4” gave me 7709.  This swing of several hundred results for each increment of the “Ntile” was too great.

Notice we lost 3 zip codes.  The ones lost were those where there were only one or two people in that zip code.  It probably lops off any zip codes with count of 6 or fewer addresses in this case (6/35 is about 1/6th).  I also wasn’t able to get exactly 8000 records.  I might have been able to get a number closer to 8000 by using a greater number of slices.  But I suspect that would have “penalized” (got rid of entirely) more zip codes with numbers of addresses smaller than the number of slices.  Or maybe not.  But 8003 records over 29 zip codes was close enough for me so I stopped fiddling.

It seems if you save this view (as “ListTop8000” in this case), get out, and then re-open, it gets rid of the “WITH SlicedData AS” and changes it into something that won’t run anymore:

SELECT     ZIP, INDIVIDUALNAME, NTILE(35) OVER (PARTITION BY Zip

ORDER BY COUNT(INDIVIDUALNAME) DESC) AS 'NTile'

FROM List

GROUP BY ZIP, INDIVIDUALNAME)

  SELECT ZIP, INDIVIDUALNAME

   FROM  SlicedData

   WHERE NTile > 6

which, when run, complains:

Msg 102, Level 15, State 1, Line 4

Incorrect syntax near ')'.

Strangely, though, when I included this saved query into a join statement (‘cause I wanted more than just “INDIVIDUALNAME” but didn’t want to mess with grouping on all the other fields), it seemed to work just fine:

SELECT b.PREFIXTTL, b.INDIVIDUALNAME, b.FIRSTNAME, b.MIDDLENAME, b.LASTNAME, b.ADDRESS, b.ADDRESS2LINE, b.CITY, b.STATE, b.ZIP,

         RIGHT('0000' + RTRIM(b.ZIP4), 4) AS zip4

FROM dbo.List AS b INNER JOIN

      dbo.ListTop8000 AS s ON s.INDIVIDUALNAME = b.INDIVIDUALNAME

even though it complained when run on its own.  So go figure.

What if we have 9-digit zip, but only want to sort on 5-digit zip?

WITH SlicedData AS

(

SELECT SUBSTRING(ZIP, 1, 5) AS zip2, VARHDR7, NTILE(25) OVER (PARTITION BY SUBSTRING(ZIP, 1, 5)

     ORDER BY COUNT(VARHDR7) DESC

) AS 'Ntile'

FROM         [032312BradsList]

GROUP BY SUBSTRING(ZIP, 1, 5), VARHDR7)

    SELECT     zip2, VARHDR7

     FROM         SlicedData

     WHERE     Ntile > 2

Null, is Null equivalent - ISNULL(fieldA,0).  Using a CASE statement:

Select FirstName,

   CASE

      WHEN FirstName IS NULL

      THEN 'Agent'

      ELSE FirstName

   END AS First

From Person

number rows – see rank

nz equivalent - ISNULL

–O–

offline, take database offline ( and why it might seem to be taking forever)–

EXEC sp_dboption N'mydb', N'offline', N'true'

or

ALTER DATABASE [mydb] SET OFFLINE WITH

ROLLBACK AFTER 30 SECONDS

or

ALTER DATABASE [mydb] SET OFFLINE WITH

ROLLBACK IMMEDIATE

Using the alter database statement (SQL Server 2k and beyond) is the preferred method. The rollback after statement will force currently executing statements to rollback after N seconds. The default is to wait for all currently running transactions to complete and for the sessions to be terminated. Use the rollback immediate clause to rollback transactions immediately.

If, instead, you went through the front end to try to take it and you're wondering why it's taking forever, it's because someone's still logged on:

USE master

SELECT * FROM sys.sysprocesses WHERE dbid = DB_ID('MyDB')

Find which spid is using the database:

EXEC sp_who2

Kill whichever spid it was. In this case, 72.

kill 72

outer join, full

SQL Server can handle a full outer join (ANSI 92 standard syntax):

SELECT

FROM employee

   FULL OUTER JOIN

   department

     ON employee.DepartmentID = department.DepartmentID

+-----------+--------------+----------------+--------------+

| LastName  | DepartmentID | DepartmentName | DepartmentID |

+-----------+--------------+----------------+--------------+

| Smith     |           34 | Clerical       |           34 |

| Jones     |           33 | Engineering    |           33 |

| Robinson  |           34 | Clerical       |           34 |

| Jasper    |           36 | NULL           |         NULL |

| Steinberg |           33 | Engineering    |           33 |

| Rafferty  |           31 | Sales          |           31 |

| NULL      |         NULL | Marketing      |           35 |

+-----------+--------------+----------------+--------------+

If you’re using something like Access which can’t handle full outer joins, fudge it with a union of a right and left join:

SELECT

   employee.LastName,

   employee.DepartmentID,

   department.DepartmentName,

   department.DepartmentID

FROM employee

   LEFT JOIN

   department

     ON employee.DepartmentID = department.DepartmentID

UNION

SELECT

   employee.LastName,

   employee.DepartmentID,

   department.DepartmentName,

   department.DepartmentID

FROM employee

   RIGHT JOIN

   department

     ON employee.DepartmentID = department.DepartmentID

     WHERE employee.DepartmentID IS NULL

output from stored procedures, three common ways to return data from stored procedures: OUTPUTing variables, temp tables and the RETURN statement.  Return:

declare @ReturnValue char(20)

exec @ReturnValue = todayTomorrowOrOther '9/17/14'

Select ReturnValue=@ReturnValue

owner, change

USE pubs

GO

EXEC sp_changedbowner 'John'

GO

–P–

pad with zeros – here’s how to pad a zero as necessary to the left of a 1-digit day to get it to sort right

SELECT right(replicate('0',2)+ convert(varchar(2),DATENAME(Day,theDate)),2) as theDay

FROM [Management-PMMileStones]

where DATENAME(MONTH,theDate)+ ' ' + DATENAME(YEAR,theDate) is not null

and DATENAME(YEAR,theDate) = '2014'

and DATENAME(MONTH,theDate) = 'February'

order by theDay

permissions, grant - see also grant permissions

permissions, grant to a group - GRANT CREATE PROCEDURE TO [Domain\Group]

permissions, view

for what the user logged in can do to all objects

select sys.schemas.name 'Schema'

, sys.objects.name Object

, sys.database_principals.name username

, sys.database_permissions.type permissions_type

, sys.database_permissions.permission_name

, sys.database_permissions.state permission_state

, sys.database_permissions.state_desc

, state_desc + ' ' + permission_name + ' on ['+ sys.schemas.name + '].[' + sys.objects.name + '] to [' + sys.database_principals.name + ']' COLLATE LATIN1_General_CI_AS

from sys.database_permissions

join sys.objects on sys.database_permissions.major_id = sys.objects.object_id

join sys.schemas on sys.objects.schema_id = sys.schemas.schema_id

join sys.database_principals on sys.database_permissions.grantee_principal_id = sys.database_principals.principal_id

WHERE sys.database_principals.name = 'GCP_FMExport'

order by 1, 2, 3, 5

or to just see what permissions he has on the database he's in:

SELECTprin.[name] [User], sec.state_desc + ' ' + sec.permission_name [Permission]

FROM[sys].[database_permissions] sec

JOIN [sys].[database_principals] prin

ON sec.[grantee_principal_id] = prin.[principal_id]

WHEREsec.class = 0

ORDERBY [User], [Permission];

proportionally trim each category within a group – see NTILE to trim each category proportionally

–Q–

queries, show all - Select TABLE_NAME From Information_Schema.Tables where TABLE_TYPE = 'VIEW'

quotes, single, enclose inside of single quotes – use two of ‘em: SELECT COMPANY_NAME FROM Customer WHERE Affiliation = N'Sam''s Club'

–R–

random selection of rows from a table – more here

SELECT TOP 10 * FROM Testimonials ORDER By NEWID()

rank – see also dense rank

SELECT TOP (50) COUNT(p1.CustomerFK) AS NumMailings, c1.COMPANY_NAME, MAX(p1.MAILED) AS MostRecentMailed, MAX(p1.EST_MAILING_DATE)

AS MostRecentEMD, p1.CustomerFK, f.Restricted, Row_Number() OVER (ORDER BY COUNT(p1.CustomerFK) DESC) AS RunningCount

FROM  dbo.Customer AS c1 INNER JOIN

     dbo.Project AS p1 ON c1.ID = p1.CustomerFK LEFT OUTER JOIN

     dbo.FMO AS f ON c1.FMO = f.FMO

GROUP BY c1.COMPANY_NAME, p1.CustomerFK, f.Restricted

HAVING  (f.Restricted IS NULL OR f.Restricted = 0)

ORDER BY NumMailings DESC

What if you want a bunch of categories, each ordered?

SELECT ROW_NUMBER() OVER (PARTITION BY AcctMgr

ORDER BY HowMany DESC) AS row, AcctMgr, Newest, HowMany, COMPANY_NAME

FROM CUSTOMER

records, default maximum returned – see also Access records, default maximum returned

rename database - from here

EXEC master..sp_renamedb 'CoreDB','ProductsDB'

Once the above T-SQL has executed successfully the database name will change however the Logical Name and File Name will not change. You can verify this by executing the T-SQL below:

USE master

GO

/* Identify Database File Names */

SELECT

name AS [Logical Name],

physical_name AS [DB File Path],

type_desc AS [File Type],

state_desc AS [State]

FROM sys.master_files

WHERE database_id = DB_ID(N'ProductsDB')

GO

follow the below steps which will not only rename the database, but at the same time will also rename the Logical Name and File Name of the database. This first set of commands put the database in single user mode and also modifies the logical names.

/* Set Database as a Single User */

ALTER DATABASE CoreDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

/* Change Logical File Name */

ALTER DATABASE [CoreDB] MODIFY FILE (NAME=N'CoreDB', NEWNAME=N'ProductsDB')

GO

ALTER DATABASE [CoreDB] MODIFY FILE (NAME=N'CoreDB_log', NEWNAME=N'ProductsDB_log')

Now we need to detach the database, so we can rename the physical files. If the database files are open you will not be able to rename the files.

/* Detach Current Database */

USE [master]

GO

EXEC master.dbo.sp_detach_db @dbname = N'CoreDB'

GO

Once xp_cmdshell is enabled you can use the below script to rename the physical files of the database.

/* Rename Physical Files */

USE [master]

GO

EXEC xp_cmdshell 'RENAME "C:\Program Files\Microsoft SQL ServerMSSQL10.SQL2008\MSSQL\DATA\CoreDB.mdf", "ProductsDB.mdf"'

GO

EXEC xp_cmdshell 'RENAME "C:\Program Files\Microsoft SQL ServerMSSQL10.SQL2008\MSSQL\DATA\CoreDB_log.ldf", "ProductsDB_log.ldf"'

GO

Once the above step has successfully executed then the next step will be to attach the database, this can be done by executing the T-SQL below:

/* Attach Renamed ProductsDB Database Online */

USE [master]

GO

CREATE DATABASE ProductsDB ON

( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\ProductsDB.mdf' ),

( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\ProductsDB_log.ldf' )

FOR ATTACH

GO

restore database

Step 1: Retrieve the Logical file name of the database from backup.

RESTORE FILELISTONLY

    FROM DISK = 'D:BackUpYourBaackUpFile.bak'

GO

Step 2: Use the values in the LogicalName Column in following Step.

    ----Make Database to single user Mode

ALTER DATABASE YourDB

    SET SINGLE_USER WITH

ROLLBACK IMMEDIATE

 

   ----Restore Database

   RESTORE DATABASE YourDB

   FROM DISK = 'D:BackUpYourBaackUpFile.bak'

      WITH MOVE 'YourMDFLogicalName' TO 'D:DataYourMDFFile.mdf',

      MOVE 'YourLDFLogicalName' TO 'D:DataYourLDFFile.mdf'

/*If there is no error in statement before database will be in multiuser

mode.

If error occurs please execute following command it will convert

database in multi user.*/

ALTER DATABASE YourDB SET MULTI_USER

GO

restore directory, default - Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Backup

row numbers – see rank

–S–

Saving changes is not permitted. The changes that you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created.

To change the Prevent saving changes that require the table re-creation option, follow these steps:

  1. Open SQL Server Management Studio (SSMS).
  2. On the Tools menu, click Options.
  3. In the navigation pane of the Options window, click Designers.
  4. Select or clear the Prevent saving changes that require the table re-creation check box, and then click OK.

Note If you disable this option, you are not warned when you save the table that the changes that you made have changed the metadata structure of the table. In this case, data loss may occur when you save the table.

scalar function

To create:

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

create FUNCTION [dbo].[todayTomorrowOrOther] (@dteDateInQuestion datetime)

RETURNS char(20)

AS

BEGIN

DECLARE @ReturnValue char (20)

set @ReturnValue =

CASE

WHEN (datediff(d, getDate(), @dteDateInQuestion)) < 0 THEN ' late'

WHEN (datediff(d, getDate(), @dteDateInQuestion)) = 0 THEN '0 - today'

WHEN (datediff(d, getDate(), @dteDateInQuestion)) = 1 THEN '1 - tomorrow'

WHEN (datediff(d, getDate(), @dteDateInQuestion)) = 2 THEN '2 days'

WHEN (datediff(d, getDate(), @dteDateInQuestion)) between 2 AND 7 THEN '3-6 days'

WHEN (datediff(d, getDate(), @dteDateInQuestion)) > 6 THEN 'week or more'

ELSE 'undetermined'

END

RETURN @ReturnValue

END

To use

SELECT   dbo.todayTomorrowOrOther(EST_MAILING_DATE) AS EMD

   dbo.Project

select, grant permissions to a group\user to a table, - GRANT SELECT ON SysComments to [group\user]

show all views - Select TABLE_NAME From Information_Schema.Tables where TABLE_TYPE = 'VIEW'

shrink each category within a group proportionally – see NTILE to trim each category proportionally

siblings, find (see also update inner join with sub-select) – for this example we find siblings where one sibling has one field (State, in this case) filled out all the other siblings which have that same field missing

select distinct ProjectFK, state

from RestaurSeminar as r1

where r1.ProjectFK in

(select r2.ProjectFK from RestaurSeminar as r2

   where ((select count(distinct r3.state) from RestaurSeminar as r3 -- 1. at least one record has state

            where r2.ProjectFK = r3.ProjectFK                           -- filled in.  Can be more, but state

            and r3.state is not null and r3.Restaurant is not null) =1) -- must be the same

   and   ((select count(*) from RestaurSeminar as r4

         where r2.ProjectFK = r4.ProjectFK                                 -- 2. at least one other record

         and r4.state is null and r4.Restaurant is not null) >0)           -- with state field missing

   and   ((select count(distinct r5.Restaurant) from RestaurSeminar as r5

         where r2.ProjectFK = r5.ProjectFK) =1))                           -- 3. at least 1 restaurant field filled

and r1.state is not null

single quotes, enclose inside of single quotes – use two of ‘em:

SELECT COMPANY_NAME FROM Customer WHERE Affiliation = N'Sam''s Club'

show size of all tables - see tables, show size of all

space, trim – all SQL server seems to have are LTRIM() and RTRIM().  Pretty worthless, eh?  Can’t get rid of tabs, carriage returns, etc.  So, create a custom function

create function dbo.UDF_Trim(@input varchar(8000)) returns varchar(8000) as

begin

declare @charstotrim varchar(100)

select @charstotrim ='’'+ '‘'+char(9)+char(10)+char(13)

while charindex(left(reverse(@input),1),@charstotrim) >0

select @input=left(@input,datalength(@input)-1)

while charindex(left(@input,1),@charstotrim) >0

select @input=substring(@input,2,datalength(@input)-1)

return @input

end

special characters, list

DECLARE @i int

SET @i =0

WHILE @i < 255

BEGIN

 PRINT CONVERT(varchar, @i) + ' - >' + CHAR(@i) + '<'

 SET @i = @i + 1

END

special characters, strip out.   Things like CRLF, for instance.

UPDATE  Leads

SET eMail = REPLACE(eMail, SUBSTRING(eMail, PATINDEX('%[^a-zA-Z0-9@. '''''']%', eMail), 1), '')

WHERE PATINDEX('%[^a-zA-Z0-9@. '''''']%', eMail) <> 0

Will take out special characters from email except single quote, @ sign.  Not sure this takes out all unprintable characters.

split a string based on a space

substring(project_name,charindex(' ',project_name)+1,len(project_name))

gets last half of string separated by space

substring(project_name,1,charindex(' ',project_name)-1)

gets 1st half of string separated by space, but problems if no space present at all

CASE CHARINDEX(' ', FirstName)

WHEN 0

THEN FirstName

ELSE SUBSTRING(FirstName, 1, CHARINDEX(' ', FirstName))

gets beginning of string before a space if there is a space or just the string itself if there is no space

SQL Server Agent, permissions to see

use msdb

EXECUTE sp_addrolemember

@rolename = 'SQLAgentReaderRole',

@membername = 'domain\user'

(need to use msdb) or

exec sp_addrolemember 'SQLAgentUserRole', 'domain\user'

may first need to

exec sp_adduser 'domain\user', 'domain\user', 'public'

SQL Server Express Utility – SSEUtil is a tool that lets you easily interact with SQL Server.  Download.

sqlcmd

Briefly, type in "sqlcmd" at the command prompt. This will give you a line number like "1>". Type in how many ever lines you want. For example, type, "select @@servername" followed by return. Type "go" at the next line. Exit by typing "exit" followed by return.

stored procedure, call from within select statement – you can’t, but you can create a scalar function and that’ll probably do what you need anyway

stored procedure, can’t edit because you get a "Syntax error in TextHeader of StoredProcedure" error

sp_helptext N'spYourProcName'

so you can at least see the dang thing

stored procedure, find a string among all stored procedures

SELECT OBJECT_NAME(id)

FROM SYSCOMMENTS

WHERE [text] LIKE '%someString%'

AND OBJECTPROPERTY(id, 'IsProcedure') = 1

GROUP BY OBJECT_NAME(id)

stored procedure, grant permissions to create to a group - GRANT CREATE PROCEDURE TO [Domain\Group]

stored procedure output – see output from stored procedures

stored procedure results, feed into another stored procedure – from here:

So long as the stored procedure produces only a single result, the technique for using the output of one stored procedure in another is pretty straightforward. The technique is to use a temporary table to hold the results of the stored procedure and an INSERT EXEC statement to execute the stored procedure and save the results. Once the results are in the temporary table they can be used like any other table data.

Here's an example procedure that we might like to reuse:

CREATE PROC  usp_Demo_AllAuthors as

    select * from pubs..authors

GO

Now here's a stored procedure that uses the results of usp_Demo_AllAuthors:

CREATE proc usp_Demo_SPUser as

  CREATE TABLE #Authors (

   au_id varchar(11) NOT NULL PRIMARY KEY CLUSTERED,

   au_lname varchar (40) NOT NULL,

   au_fname varchar (20) NOT NULL,

   phone char (12) NOT NULL,

   address varchar (40) NULL,

   city varchar (20) NULL,

   state char (2) NULL,

   zip char (5) NULL,

   contract bit NOT NULL

  )

 

  -- Execute usp_Demo_AllAuthors storing the

  -- results in #Authors

  insert into #Authors

   exec usp_Demo_AllAuthors

 

  -- Here we use the #Authors table.  This example only

  -- only selects from the temp table but you could do much

  -- more such as use a cursor on the table or join with

  -- other data.

  SELECT au_fName + ' ' + au_lname as [name]

       , address+', '+city+', '+state+' '+zip [Addr]

      from #Authors

 

  DROP TABLE #Authors

GO

string, find location of in a bigger string - CHARINDEX - SELECT SYSTEM_USER AS 'Login Name', CHARINDEX( '\', SYSTEM_USER) AS backslash_position

strip blanks – ltrim(rtrim(@TempValue))

SUBSTRING(expression, start, length)

string, find longest in a column – see longest string in a column, find

–T–

table, create from a query

SELECT DISTINCT RestaurSeminar.Restaurant, RestaurSeminar.City, RestaurSeminar.State INTO Restaurant

FROM RestaurSeminar;

table fields, list

SELECT c.name 'Column Name', t.Name 'Data type', c.max_length 'Max Length', c.precision, c.scale, c.is_nullable, ISNULL(i.is_primary_key, 0) 'Primary Key'

FROM sys.columns c

  INNER JOIN sys.types t ON c.system_type_id = t.system_type_id

  LEFT OUTER JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id

  LEFT OUTER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id

WHERE c.object_id = OBJECT_ID('invoice')

table, grant permissions to - GRANT SELECT ON SysComments to [group\user]

table info, display – Select * From Information_Schema.Columns Where Table_Name = 'Customer'

tables from 2 different databases, join – see join tables from 2 different databases

tables, show all –

Select TABLE_NAME

From Information_Schema.Tables

where TABLE_TYPE = 'BASE TABLE'

order by TABLE_NAME asc

Or

SELECT [TableName] = so.name, [RowCount] = MAX(si.rows)

FROM sysobjects so, sysindexes si

WHERE so.xtype = 'U' AND si.id = OBJECT_ID(so.name) AND si.rows > 0

GROUP BY so.name

ORDER BY 2 ASC

tables, show size of all

SELECT t.NAME AS TableName, s.Name AS SchemaName, p.rows AS RowCounts,

  SUM(a.total_pages) * 8 AS TotalSpaceKB, SUM(a.used_pages) * 8 AS UsedSpaceKB,

  (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB

FROM sys.tables t

  INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id

  INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id

  INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id

  LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id

WHERE t.NAME NOT LIKE 'dt%'

  AND t.is_ms_shipped = 0

  AND i.OBJECT_ID > 255

GROUP BY t.Name, s.Name, p.Rows

ORDER BY t.Name

take database offline is taking too long - try the following code from a command prompt instead

ALTER DATABASE yourdb SET OFFLINE WITH ROLLBACK IMMEDIATE

You might get an error message complaining that you don't have exclusive use of the database: ALTER DATABASE failed because a lock could not be placed on database to solve that, see ALTER DATABASE failed because a lock could not be placed on database

today, beginning of today – DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)

tomorrow, beginning of tomorrow – DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 1)

transfer logins and passwords between instances of SQL Server - sp_help_revlogin

transpose –

use the PIVOT command

RAC - The Relational Application Companion 3rd party tool

How to rotate a table in SQL Server

trim each category within a group proportionally – see NTILE to trim each category proportionally

trim space– see space, trim

–U–

union

SELECT au_lname FROM authors

UNION ALL

SELECT lname FROM Employee

ORDER BY au_lname

This will return all the records in the AUTHORS table and then all the records in the EMPLOLYEE table in one result set. The UNION will remove duplicates from the result set by default. You can use the UNION ALL to keep the duplicates in the result set.

The ORDER BY clause applies to the entire result. You can only have one ORDER BY clause in a UNION query and it must be part of the last SELECT statement. The first SELECT statement defines the column names. I usually try to alias all my columns to the same name for easier reading.

update example –

update Customer

set MagTitle = 'Rolling Stone'

where MagTitle = 'RS'

or

update table1 set a=t2.a, b=t2.b

from table2 t2

where table1.id=table2.id

update inner join

This works in MS Access but not in SQL Server:

update TableOne

  inner join TableTwo on TableOne.commonID = TableTwo.commonID

  set TableOne.field1 = TableTwo.field2

or this also works in MS Access but not in SQL Server (better for when you have extra “and” restrictions you need to add to the “where”):

update TableOne, TableTwo

  set TableOne.field1 = TableTwo.field2

  where TableOne.commonID = TableTwo.commonID

this works in SQL Server but not in MS Access

UPDATE source

SET NewCustID = u.NewCustID

FROM UniqueNewCustomers u INNER JOIN source s

ON  u.MinOfID = s.ID

or on two tables from 2 different databases where “SCOld” is the “foreign” database:

UPDATE Person

SET WhyNotEmail = old.WhyNotEmail, doNotEMail=old.doNotEMail,

WhenDecidedNotEmail=old.WhenDecidedNotEmail

FROM SCold.dbo.Person old INNER JOIN Person new

ON  old.ID = new.ID

where (new.doNotEMail is null and old.doNotEMail is not null)

and old.WhyNotEmail = 'Blocked'

I had heard somewhere this supposedly works in SQL Server.  But it didn’t work for me.

update tableOne

set tableOne.field1=tableTwo.field2

from table  One, tableTwo

where tableOne.commonID=tableTwo.commonID

update inner join with sub-select.  Here’s an example where we find siblings where one sibling has one field (State, in this case) filled out and we want to assign that same value to all its siblings which have that same field missing

UPDATE r

SET r.State = r0.State

from RestaurSeminar as r,

(

select distinct ProjectFK, state

from RestaurSeminar as r1

where ProjectFK in

(select r2.ProjectFK from RestaurSeminar as r2

   where ((select count(distinct r3.state) from RestaurSeminar as r3 -- 1. at least one record has state

      where r2.ProjectFK = r3.ProjectFK                                 -- filled in.  Can be more, but state

      and r3.state is not null and r3.Restaurant is not null) =1) -- must be the same

   and   ((select count(*) from RestaurSeminar as r4

      where r2.ProjectFK = r4.ProjectFK                                 -- 2. at least one other record

      and r4.state is null and r4.Restaurant is not null) >0)           -- with state field missing

   and   ((select count(distinct r5.Restaurant) from RestaurSeminar as r5

      where r2.ProjectFK = r5.ProjectFK) =1))                           -- 3. at least 1 restaurant field filled

and state is not null

) as r0

where r.ProjectFK = r0.ProjectFK

update query, test – use begin transaction and rollback transaction:

begin transaction

 

UPDATE FillTheRoom

SET FillTheRoom.ProjectFK = [Project].[ID]

from FillTheRoom, Person, Project, Customer

WHERE     (dbo.FillTheRoom.ProjectFK IS NULL)

and SUBSTRING(dbo.FillTheRoom.AgentName, CHARINDEX(' ', dbo.FillTheRoom.AgentName) + 1, LEN(dbo.FillTheRoom.AgentName))

  = dbo.Person.LastName

AND SUBSTRING(dbo.FillTheRoom.AgentName, 1, CHARINDEX(' ', dbo.FillTheRoom.AgentName) - 1)

  = dbo.Person.FirstName

and dbo.Customer.ID = dbo.Person.CustomerFK

and dbo.Customer.ID = dbo.Project.CustomerFK

AND dbo.FillTheRoom.MailingDate = dbo.Project.EST_MAILING_DATE

 

rollback transaction

user already exists in current database (error 15023) What you might really want instead is: ID of SQL Server instance doesn't match ID of database. (see also here where there are 3 stored procedures to deal with orphan users in a bulk fashion)

First of all run following T-SQL Query in Query Analyzer. This will return all the existing users in database in result pan (whatever the heck a "pan" is).pan" is).

USE YourDB

EXEC sp_change_users_login 'Report'

Run following T-SQL Query in Query Analyzer to associate login with the username. ‘Auto_Fix’ attribute will create the user in SQL Server instance if it does not exist. In following example ‘someuser’ is UserName, ‘pwd’ is Password. Auto-Fix links a user entry in the sysusers table in the current database to a login of the same name in sysxlogins.

EXEC sp_change_users_login 'Auto_Fix' ,'someuser', NULL,'pwd'

Run following T-SQL Query in Query Analyzer to associate login with the username. ‘Update_One’ links the specified user in the current database to login. login must already exist. user and login must be specified. password must be NULL or not specified.

EXEC sp_change_users_login 'update_one', 'someuser', 'someuser'

Delete the old version of the user

EXEC sp_dropuser 'someuser'

Create the same user again in the database without any error.

–V–

view doesn’t display decimal values – see decimal, view doesn’t display after a calculation

views, show all - Select TABLE_NAME From Information_Schema.Tables where TABLE_TYPE = 'VIEW'

–W–

week, first day of last week – select dateadd(wk, datediff(wk, 0, getdate()) - 1, 0)

week, first day of this week – select dateadd(wk, datediff(wk, 0, getdate()), 0)

week, last day of last week – select dateadd(day, -1 - (datepart(dw, getdate()) + @@datefirst - 2) % 7, getdate()) AS LastSunday

week, last day of this week – select dateadd(wk, datediff(wk, 0, getdate()), 0) + 6 AS NextSunday

who am I logged in as?  SELECT SYSTEM_USER AS 'Login Name'

white space, trim – see space, trim

word first – see first word

–X–

–Y–

yesterday – select dateadd(day,-1,getdate())

–Z–

zeros, pad with– see pad with zeros

–No's–

10061 - "A network-related or instance-specific error occurred while establishing a connection to SQL. Server The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. No connection could be made because the target machine actively refused it."

Sounds scary, eh?  I sometimes get this when connecting to my local instance.  All I've ever had to do was simply start the "SQL Server" service on my local machine. Even though it's supposed to start automatically, sometimes it doesn't seem to

15023 User already exists in current database - see user already exists in current database

22022 – see backup failed with error # 22022