-- show all current users
sp_who2

-- create SQL login
EXEC sp_addlogin 'webuser', 'password'

-- change SQL login password
EXEC sp_password 'password', 'newpassword'

-- grant SQL login to Windows account
EXEC sp_grantlogin 'WEBSQL01\cjanco'

-- remove SQL login from Windows account
EXEC sp_revokelogin 'WEBSQL01\cjanco'

-- grant access to a DB for SQL account or Windows account
USE store
EXEC sp_grantdbaccess 'webuser'

-- show all db info
EXEC sp_helpdb

-- create new db
CREATE DATABASE [Commerce]

-- rename db
EXEC sp_renamedb 'MyDB', 'MyDB2'

-- delete db
DROP DATABASE MyDB

-- create table
CREATE TABLE Customers
(
	CustID char(8) NOT NULL,
	CustName varchar (30) NOT NULL,
	Email varchar (50) NOT NULL
)

-- create table
IF EXISTS 
	(
	SELECT * 
	FROM dbo.sysobjects 
	WHERE Id = OBJECT_Id(N'[dbo].[tArticles]') 
	AND OBJECTPROPERTY(Id, N'IsUserTable') = 1)
	DROP TABLE [dbo].[tArticles]
GO

CREATE TABLE [dbo].[tArticles]
(
	[ArticleId] INT IDENTITY (1, 1) NOT NULL,
	[ArticleTypeId] INT NOT NULL,
	[Name] VARCHAR(50) NOT NULL,
	[Pattern] VARCHAR(200) NULL,
	[Disabled] BIT NOT NULL,
	[CDate] DATETIME NOT NULL,
	[UDate] DATETIME NOT NULL,
	[PostToWeb] BIT NOT NULL
	CONSTRAINT [pk_tArticles] PRIMARY KEY NONCLUSTERED
	(
		[ArticleId]
	)
)
GO

EXEC SP_BINDEFAULT N'[dbo].[dfltDisabled]', N'[tArticles].[Disabled]'
EXEC SP_BINDEFAULT N'[dbo].[dfltSysDate]', N'[tArticles].[CDate]'
EXEC SP_BINDEFAULT N'[dbo].[dfltSysDate]', N'[tArticles].[UDate]'
EXEC SP_BINDEFAULT N'[dbo].[dfltOne]', N'[tArticles].[PostToWeb]'
GO

-- change SA password

osql -U sa

enter password

sp_password @old = null, @new = 'complexpwd',  @loginame ='sa' 

go

-- Check if CardNumber has a value
SELECT @CardNumber = LTRIM(RTRIM(@CardNumber))
IF (@CardNumber = '' OR @CardNumber = 0)
BEGIN
	SELECT @CardNumber = NULL
END

-- Check if CustomerName has a value
SELECT @CustomerName = LTRIM(RTRIM(@CustomerName))
IF (@CustomerName = '')
BEGIN
	SELECT @CustomerName = NULL
END

SELECT...
WHERE...
AND C.CardSN = ISNULL(@CardNumber, C.CardSN)
AND C.Name = ISNULL(@CustomerName, C.Name)

AND C.Name LIKE ISNULL('%' + @CustomerName + '%', C.Name)

-- Export
bcp pubs.dbo.authors out c:\temp\authors.bcp

-- Import
bcp BKMulti.dbo.tbl_Member in C:\Websites\BKMulti\IMPORT\BKM_Students.txt -T

BULK INSERT pubs..publishers2 
FROM 'c:\newpubs.txt'
WITH (
   DATAFILETYPE = 'char',
   FIELDTERMINATOR = ',',
   ROWTERMINATOR = '\n'
)

ALTER TABLE NP_Enrollment 
    ADD CONSTRAINT IX_Email UNIQUE (NP_Email)

SELECT 
	COUNT(ProductId)
FROM
	tbl_OrderDetails
WHERE
	ProductID = 36

-- Count with alias
SELECT tblRegistrationWorkshop.Workshop_ID, Count(tblRegistrationWorkshop.Workshop_ID) AS CountOfWorkshop_ID
FROM tblRegistrationWorkshop
GROUP BY tblRegistrationWorkshop.Workshop_ID
ORDER BY tblRegistrationWorkshop.Workshop_ID

-- Count with alias, join
SELECT tblRegistrationWorkshop.Workshop_ID, tblEventWorkshop.Workshop_Name, 
Count(tblRegistrationWorkshop.Workshop_ID) AS CountOfWorkshop_ID
FROM tblEventWorkshop 
INNER JOIN tblRegistrationWorkshop ON tblEventWorkshop.Workshop_ID = tblRegistrationWorkshop.Workshop_ID
GROUP BY tblEventWorkshop.Workshop_ID, tblRegistrationWorkshop.Workshop_ID, tblEventWorkshop.Workshop_Name

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER  FUNCTION geteventnarrative (@EVENT_ID INTEGER)
RETURNS VARCHAR(8000)
AS

BEGIN
 DECLARE @NARR VARCHAR(48), @FULLNARR VARCHAR(8000)

 DECLARE narr_cursor CURSOR
 FOR SELECT en.narr
    FROM cm_events AS e INNER JOIN cm_eventnarr AS en
      ON e.ievent = en.ievent JOIN cm_caseevents AS ce
      ON e.ievent = ce.ievent
    WHERE ce.icaseevent = @EVENT_ID
    ORDER BY en.narrline
 FOR READ ONLY

 OPEN narr_cursor
 FETCH NEXT FROM narr_cursor INTO @NARR

 WHILE (@@FETCH_STATUS <> -1)
  BEGIN
    IF (@@FETCH_STATUS <> -2)
      BEGIN
        SET @FULLNARR = COALESCE(@FULLNARR,'') + ' ' + COALESCE(@NARR,'')
        SET @FULLNARR = LTRIM(RTRIM(@FULLNARR))
      END
    FETCH NEXT FROM narr_cursor INTO @NARR
  END

 CLOSE narr_cursor
 DEALLOCATE narr_cursor

 RETURN @FULLNARR
END


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

AND Trxn_Date BETWEEN @StartDate AND @EndDate

SELECT DATEADD(month, 2, OrderDate) 
FROM Orders

UPDATE tbl_Schedule
SET ScheduleStatus = 0
WHERE DATEDIFF(dd, ScheduleExpires, GetDate()) > 1

DELETE * FROM Products WHERE ProductPrice = 10.00

--(1)
use master
	go
	sp_detach_db 'myDB'
	go

--(2)
--Move the .mdf and ldf files to new location

--(3)
use master
	go
	sp_attach_db 'myDB','E:\Sql2k\Data\myDB.mdf','D:\Sql2k\Data\myDB.ldf'
	go

-- DTS RUN: SCHEDULED TASK
dtsrun /S 12bravosql /N "CUSTOMER IMPORT" /E

-- DTS RUN: COMMAND
dtsrun /S 12bravosql /N "CUSTOMER IMPORT" /UEGOV\Administrator /P mypass_ /E

SELECT DISTINCT au_fname, au_lname, state
FROM authors
WHERE EXISTS
	(
	SELECT *
	FROM stores
	WHERE state = authors.state
	)

CREATE PROCEDURE demo 
	@list VARCHAR(2000) 
AS 
DECLARE @sql VARCHAR(8000) 

@sql = 'SELECT * FROM table WHERE ID IN (' + @list + ')' 
EXEC ( @sql ) 
GO 

ALTER FUNCTION FormatDate(@Date datetime)
	RETURNS varchar(10)
AS
BEGIN
	DECLARE @NewDate varchar(10)
	
	SET @NewDate = CONVERT(varchar, MONTH(@Date)) + '/' +
	CONVERT(varchar, DAY(@Date)) + '/' + CONVERT(varchar, YEAR(@Date))

	RETURN @NewDate
END

HAVING... was added to SQL because the WHERE keyword could not be used against aggregate functions 
(like SUM), and without HAVING... it would be impossible to test for result conditions.

SELECT Company, SUM(Amount) 
FROM Sales
GROUP BY Company
HAVING SUM(Amount) > 10000

use database_name
go
sp_helpfile
go

-- select
SELECT DISTINCT au_fname, au_lname, state
FROM authors
WHERE state IN
	(
	SELECT state 
	FROM stores
	)

-- update	
UPDATE company_user
SET user_role = (user_role + 12)
WHERE user_id IN 
	(
	SELECT user_id
	FROM company_user u
	INNER JOIN company_info i ON u.company_id = i.company_id
	WHERE i.ext_systemno <> 18
	AND i.company_type = 11
	)

INSERT INTO Products (ProductName, ProductPrice) VALUES ('hat', 10.00)

INSERT INTO Products (ProductName, ProductPrice) VALUES ( '" & product_name & "', " & product_price & " )

INSERT INTO LMD_Merchant_Access (User_ID, Company_ID)
SELECT User_ID, Company_ID
FROM LMD_Company_User

SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name = table_name2.column_name

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.P_Id = Orders.P_Id
ORDER BY Persons.LastName

The INNER JOIN keyword return rows when there is at least one match in both tables. If there are rows 
in "Persons" that do not have matches in "Orders", those rows will NOT be listed.

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.P_Id = Orders.P_Id
ORDER BY Persons.LastName

The LEFT JOIN keyword returns all the rows from the left table (Persons), even if there are no matches 
in the right table (Orders).

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
RIGHT JOIN Orders
ON Persons.P_Id = Orders.P_Id
ORDER BY Persons.LastName

The RIGHT JOIN keyword returns all the rows from the right table (Orders), even if there are no 
matches in the left table (Persons).

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
FULL JOIN Orders
ON Persons.P_Id = Orders.P_Id
ORDER BY Persons.LastName

The FULL JOIN keyword returns all the rows from the left table (Persons), and all the rows from the 
right table (Orders). If there are rows in "Persons" that do not have matches in "Orders", or if there 
are rows in "Orders" that do not have matches in "Persons", those rows will be listed as well.

SELECT E_Name FROM Employees_Norway
UNION
SELECT E_Name FROM Employees_USA

The UNION operator is used to combine the result-set of two or more SELECT statements. The UNION command 
selects only distinct values. To list all, use UNION ALL.

SELECT m.mmatter, country.udvalue AS 'Country', apptype.udvalue AS
'Application Type', busunit.udvalue AS 'Business Unit', projname.udvalue AS 'Project Name',
projnum.udvalue AS 'Project Number', costcent.udvalue AS 'Cost Center',
ordnum.udvalue AS 'Int. Order Number', strategic.udvalue AS 'Strategic
Revenue Group'
FROM matter AS m
LEFT OUTER JOIN udf AS country ON m.mmatter = country.udjoin
AND country.udtype = 'MT'
AND country.udfindex = 8
LEFT OUTER JOIN udf AS apptype ON m.mmatter = apptype.udjoin
AND apptype.udtype = 'MT'
AND apptype.udfindex = 226
LEFT OUTER JOIN udf AS busunit ON m.mmatter = busunit.udjoin
AND busunit.udtype = 'MT'
AND busunit.udfindex = 227
LEFT OUTER JOIN udf AS projname ON m.mmatter = projname.udjoin
AND projname.udtype = 'MT'
AND projname.udfindex = 228
LEFT OUTER JOIN udf AS projnum ON m.mmatter = projnum.udjoin
AND projnum.udtype = 'MT'
AND projnum.udfindex = 229
LEFT OUTER JOIN udf AS costcent ON m.mmatter = costcent.udjoin
AND costcent.udtype = 'MT'
AND costcent.udfindex = 230
LEFT OUTER JOIN udf AS ordnum ON m.mmatter = ordnum.udjoin
AND ordnum.udtype = 'MT'
AND ordnum.udfindex = 231
LEFT OUTER JOIN udf AS strategic ON m.mmatter = strategic.udjoin
AND strategic.udtype = 'MT'
AND strategic.udfindex = 232
WHERE m.mjnum = '27475BRAVO'

How to transfer logins and passwords from SQL Server 7.0 to SQL Server 2000 or between servers that 
are running SQL Server 2000 To transfer logins and passwords from a SQL Server 7.0 server to an
instance of SQL Server 2000, or between two instances of SQL Server 2000, you can use the new DTS 
Package Transfer Logins Task in SQL Server 2000. To do this, follow these steps: 

1. Connect to the SQL Server 2000 destination server, move to the Data Transformation Services in 
SQL Server Enterprise Manager, expand the folder, right-click Local Packages, and then click New Package.

2. After the DTS package designer opens, click Transfer Logins Task on the Task menu. Complete the information 
about the Source, Destination and Logins tabs as appropriate.

Important The SQL Server 2000 destination server cannot be running the 64-bit version of SQL Server 2000. DTS 
components for the 64-bit version of SQL Server 2000 are not available. If you are importing logins from an 
instance of SQL Server that is on a separate computer, your instance of SQL Server will must be running under 
a Domain Account to complete the task.

Note The DTS method will transfer the passwords but not the original SID. If a login is not created by using 
the original SID and user databases are also transferred to a new server, the database users will be orphaned 
from the login. To transfer the original SID and bypass the orphaned users, follow the steps in the "A complete
resolution to transfer logins and passwords between different versions of SQL Server" section.

SELECT FROM SQL02.store.dbo.tbl_products

UPDATE cj_main.dbo.tbl_rates
SET cj_main.dbo.tbl_rates.cost = tempdb.dbo.temp_import_profitability.cost
FROM tempdb.dbo.temp_import_profitability
WHERE cj_main.dbo.tbl_rates.tkinit = tempdb.dbo.temp_import_profitability.tkinit

SELECT *
FROM tbl_member WITH (NOLOCK)

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

-- Pass in @col param value as one of 3 values
DECLARE @col VARCHAR(9) 
SET @col = 'firstname' 
 
IF @col IN ('firstname', 'email', 'salary') 
    SELECT * FROM blat 
        ORDER BY CASE @col 
            WHEN 'firstname' THEN firstname 
            WHEN 'email' THEN email 
            WHEN 'Salary' THEN CONVERT(varchar(50), Salary)
        END 
ELSE 
    SELECT * FROM blat 
GO


-- Another way
Private Property SortField() As String
	Get
		Dim f_oSortField As Object = ViewState("SortField")
		Dim f_sSortField As String
		If f_oSortField Is Nothing Then
			f_sSortField = "CompanyName"
		Else
			f_sSortField = ViewState("SortField")
		End If
		Return f_sSortField
	End Get

	Set(ByVal Value As String)
		ViewState("SortField") = Value
	End Set
End Property

ORDER BY
CASE WHEN @sortField = 'CustomerID DESC' THEN CustomerID ELSE 0 END DESC, 
CASE WHEN @sortField = 'CustomerID' THEN CustomerID ELSE 0 END ASC,
CASE WHEN @sortField = 'CompanyName DESC' THEN CompanyName ELSE '' END DESC,
CASE WHEN @sortField = 'CompanyName' THEN CompanyName ELSE '' END ASC,
CASE WHEN @sortField = 'DateCreated DESC' THEN DateCreated ELSE '1/1/1900' END DESC, 
CASE WHEN @sortField = 'DateCreated' THEN DateCreated ELSE '1/1/1900' END ASC 


-- Order by non-alphabetically
ORDER BY (CASE [ItemStatus] 
	WHEN 'Available' THEN 1 
	WHEN 'Coming Soon' THEN 2
	WHEN 'Coming Soon/No Price Yet' THEN 3
	WHEN 'Approval Pending' THEN 4
	WHEN 'Approval Pending/No Price Yet' THEN 5
	ELSE 6 
	END )

PARAMETERS ID Long

SELECT tblPerson.Person_First_Name, tblPerson.Person_Last_Name
FROM tblPerson 
INNER JOIN tblRegistration ON tblPerson.Person_ID = tblRegistration.Person_ID
WHERE (((tblRegistration.Event_ID) = [ID]))
ORDER BY tblPerson.Person_Last_Name

(1) Create a new DB in Enterprise Manager.
(2) Right Click on New DB and select "All Tasks > Restore Database".
(3) Keep the "Database" radio checked and select the DB that you want to restore from in the 
    "Show backups of database:" dropdown.
(4) Verify the checked backup files.
(5) Switch to the "Options" tab and check "Force restore over existing database".
(6) Verify the "Restore database files as:" section.
(7) Click "Ok".

SELECT ProductName 
FROM Products 
WHERE ProductPrice > 10.00 
AND ProductPrice < 30.00

SELECT ProductName 
FROM Products 
WHERE Category = 'video games' 
ORDER BY ProductName

SELECT DISTINCT Category 
FROM Products 
ORDER BY Category

SELECT TOP 5 * 
FROM Products

INSERT INTO STORE_INVOICE (cust_id, bill_period, line_sequence, inv_item, inv_amt, username)
SELECT bill_cust_id, p_CURRENT_BILL_PERIOD, line_sequence, inv_item, inv_amt, p_in_USERNAME
FROM STORE_COMMENT 
WHERE bill_cust_id = p_BILL_CUST_ID 
AND bill_period = p_BILL_PERIOD

SELECT *
INTO Persons_Backup
FROM Persons 

We can also use the IN clause to copy the table into another database:

SELECT *
INTO Persons_Backup IN 'Backup.mdb'
FROM Persons 

We can also copy only a few fields into the new table:

SELECT LastName,FirstName
INTO Persons_Backup
FROM Persons

Using a join:

SELECT Persons.LastName,Orders.OrderNo
INTO Persons_Order_Backup
FROM Persons
INNER JOIN Orders
ON Persons.P_Id = Orders.P_Id

SELECT OrderNumber, OrderDate, OrderStatus, Exported, ExportedDate,
ExportedYesNo = CASE Exported
   WHEN '1' THEN 'yes'
            ELSE 'no'
         END
FROM Orders 

--(1) Backup Log

-- (a)
BACKUP LOG TRACKIT70_DATA2 TO DISK='C:\Back\TRACKIT70_DATA2_log.bak'

-- (b) Backup Log with Truncate
BACKUP LOG TRACKIT70_DATA2 WITH TRUNCATE ONLY

--(2) Shrink

-- (a)
DBCC SHRINKFILE (TRACKIT65_DATA_log, 500) WITH NO_INFOMSGS

-- (b)
DBCC SHRINKFILE (2, 2048) WITH NO_INFOMSGS

ORDER BY  
	CASE WHEN @sortField = 'CardSN' THEN C.CardSN ELSE 0 END DESC, 
	CASE WHEN @sortField = 'LastUsed' THEN C.LastUsed ELSE '1/1/1900' END DESC,
	CASE WHEN @sortField = 'Name' THEN C.Name ELSE '' END ASC
END

(1) Open Notepad and save as .udl file and close the file

(2) Double click on the file and open 'Provider' tab

(3) Look for 'SQL Native Client' in the list of OLE DB Provider(s)

(4) If it is not there, go to ...

	http://www.microsoft.com/downloads/details.aspx?FamilyID=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en

	(Microsoft SQL Server Native Client)

(5) Install it

(6) Use the following connection string in your .Net app to access SQL 2005...

	<add key="ConnectionString"
	value="Provider=SQLNCLI.1;Password=111111;Persist Security
	Info=True;User ID=sa;Initial Catalog=IFIX;Data Source=111.111.111.111"/>

ALTER Procedure sp_ProductsByCategory
(
    @CategoryID int
)
AS

SELECT 
    tbl_Products.ProductID,
    tbl_Products.ProductName,
    tbl_Schedule.ScheduleStartDate, 
    tbl_Schedule.ScheduleEndDate,
    tbl_Schedule.ScheduleStatus,
    tbl_Products.UnitCost,
    tbl_Schedule.ScheduleID
FROM tbl_Products 
INNER JOIN tbl_Schedule ON tbl_Products.ProductID = tbl_Schedule.ProductID
WHERE CategoryID = @CategoryID
ORDER BY ProductName, ProductNumber

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


ALTER Procedure usp_UserDelete
/*
Name: 		usp_UserDelete

Author:		Charles Janco

Date: 		04/18/2005

Description: 	Delete user.
_______________________________________________________________________
	04/18/2005	CGJ	Initial Release
*/
(
	@User_Name varchar(20)
)
AS

DECLARE	@User_ID int

SELECT	@User_ID = U.User_ID
FROM	tbl_Company_User U
WHERE	U.User_Name = @User_Name
IF (@@ROWCOUNT != 1)
	BEGIN
	RETURN 1
	END
DELETE tbl_User_Access WHERE User_ID = @User_ID
DELETE tbl_Company_User WHERE User_ID = @User_ID
RETURN 0


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SELECT tkinit, mmatter, tworkdt,
      SUM(CASE SUBSTRING(period,1,2)
            WHEN '01' THEN janhrs
            WHEN '02' THEN febhrs
            WHEN '03' THEN marhrs
            WHEN '04' THEN aprhrs
            WHEN '05' THEN mayhrs
            WHEN '06' THEN junhrs
            WHEN '07' THEN julhrs
            WHEN '08' THEN aughrs
            WHEN '09' THEN sephrs
            WHEN '10' THEN octhrs
            WHEN '11' THEN novhrs
            WHEN '12' THEN dechrs
          END) AS tworkhrs
FROM Reports

SQL Server uses a table to store the object information for each database. This table, sysobjects, 
can be queried just like any other table. If you wanted to display the tables in your database on 
a web page. All of the tables in your database are stored in the sysobjects table, so all you need 
to do is properly query the sysobjects table

IF EXISTS 
	(
	SELECT * FROM dbo.sysobjects 
	WHERE Id = OBJECT_Id(N'[dbo].[spSearch]') AND OBJECTPROPERTY(Id, N'IsProcedure') = 1)
	DROP PROCEDURE [dbo].[spSearch]
GO

CREATE PROCEDURE [dbo].[procGetUserForSearch]
(
	@Id VARCHAR(1500)
)
WITH ENCRYPTION 
AS
BEGIN
	SET NOCOUNT ON
	...
	FOR XML EXPLICIT, BINARY BASE64
END
GO

IF EXISTS 
	(
	SELECT * from dbo.sysobjects 
	WHERE id = object_id(N'[dbo].[dfltOne]') AND OBJECTPROPERTY(id, N'IsDefault') = 1)
	DROP DEFAULT [dbo].[dfltOne]
GO

CREATE DEFAULT [dbo].[dfltOne] AS 1
GO

UNLOAD TO 'clrefer' DELIMITER '!'
SELECT clnum, clrefer
FROM client
WHERE clrefer IS NOT NULL

UPDATE Products SET ProductName = 'shirt' WHERE ProductPrice = 20.00

sp_who2

DECLARE	@User_ID int
SET @User_ID = 1927

SELECT Merchant_ID
FROM MerchantAccess
WHERE User_ID = @User_ID