|
@@Error
|
Keywords:
SQL, @@Error, @@Identity, error check
|
UPDATE LJ_Enrollment SET LawJournalPackage = @LawJournalPackage, LawJournalExpireDate = @LawJournalExpireDate WHERE LJ_Email = @Email
If @@Error <> 0 Begin Set @OrderID = 0 End Else Begin Set @OrderID = @@Identity End
|
|
Bulk Copy Program (BCP)
|
Keywords:
SQL, BCP, bulk copy, SQL Express, export, import
|
SQL Express does not provide an Import/Export wizard. The BCP Utility allows you to do this through command line. -- 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
|
Keywords:
SQL, bulk insert
|
BULK INSERT pubs..publishers2
FROM 'c:\newpubs.txt'
WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
|
|
Case When
|
Keywords:
SQL, case, when, then, sort
|
-- 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 )
|
|
Chain Replace
|
Keywords:
sql, chain, string, replace, chain replace
|
sql = "SELECT Website, MerchantName FROM Merchants WITH(NOLOCK) WHERE (REPLACE(REPLACE(Website, 'http://', ''), 'www.', '')) = '" & searchFor.Replace("http://", "").Replace("www", "") & "'"
|
|
Change Password
|
Keywords:
SQL, sp_password, SA, change password
|
-- change SA password
osql -U sa
enter password
sp_password @old = null, @new = 'complexpwd', @loginame ='sa'
go
|
|
Columns: Get All
|
Keywords:
SQL, columns, get all, sp_columns
|
exec sp_columns offices
|
|
Conditional And
|
Keywords:
SQL, conditional and, ISNULL, LTRIM, RTRIM
|
-- 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)
|
|
Conditional AND With LIKE
|
Keywords:
SQL, conditional and, like
|
AND C.Name LIKE ISNULL('%' + @CustomerName + '%', C.Name)
|
|
Constraint
|
Keywords:
SQL, constraint, unique
|
ALTER TABLE NP_Enrollment
ADD CONSTRAINT IX_Email UNIQUE (NP_Email)
|
|
Constraint: Foreign Key
|
Keywords:
sql, constraint, foreign key
|
ALTER TABLE tbl_PackageToDestination ADD FOREIGN KEY (iPackageID) REFERENCES tbl_Package(iPackageID)
|
|
Constraint: Unique: Multiple Columns
|
Keywords:
constraint, unique, multiple columns
|
ALTER TABLE offices ADD UNIQUE(OfficeId, MemberId)
|
|
Count
|
Keywords:
SQL, count, alias, group by, join
|
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
|
|
Cursor
|
Keywords:
SQL, cursor, function, @@Fetch, coalesce, deallocate
|
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
|
|
Database Commands
|
Keywords:
SQL, commands, sp_helpdb, sp_renamedb
|
-- 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
|
|
Date: Between
|
Keywords:
date, between
|
AND Trxn_Date BETWEEN @StartDate AND @EndDate
|
|
Date: DateAdd
|
Keywords:
SQL, date, DateAdd
|
SELECT DATEADD(month, 2, OrderDate)
FROM Orders
|
|
Date: DateDiff
|
Keywords:
SQL, date, DateDiff
|
UPDATE tbl_Schedule
SET ScheduleStatus = 0
WHERE DATEDIFF(dd, ScheduleExpires, GetDate()) > 1
|
|
Date: Format
|
Keywords:
SQL, date, format, convert
|
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
|
|
Delete
|
Keywords:
SQL, delete
|
DELETE * FROM Products WHERE ProductPrice = 10.00
|
|
Desktop Shortcut
|
Keywords:
SQL, desktop, shortcut, ssms.exe
|
"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"
|
|
Detach
|
Keywords:
SQL, detach, sp_attach, sp_detach
|
--(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
|
|
Do Not Display Seconds and Display AM PM
|
Keywords:
sql, time, am pm, time datatype
|
Convert(varchar(20), E.Time, 0) AS Time
|
|
DTS
|
Keywords:
SQL, DTS
|
-- 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
|
|
Error: "Login failed for user...Not associated with a trusted..."
|
Keywords:
SQL, error, login failed, trusted SQL server connection
|
"Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection."
SOLUTION: If Windows Authentication mode is selected during installation, the sa login is disabled.
|
|
Error: SQL: "Saving changes is not permitted"
|
Keywords:
error, sql, saving changes
|
ERROR Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created.
SOLUTION To change this option, on the Tools menu, click Options, expand Designers, and then click Table and Database Designers. Select or clear the Prevent saving changes that require the table to be re-created check box.
|
|
Error: SQL: "String or binary data would be truncated"
|
Keywords:
SQL, error, String or binary data would be truncated, ANSI_WARNINGS
|
ERROR: "String or binary data would be truncated"
SOLUTION: -- Add the following to your stored proc... SET ANSI_WARNINGS OFF
-- When set to ON, if null values appear in aggregate functions, such as SUM, AVG, MAX, MIN, STDEV, -- STDEVP, VAR, VARP, or COUNT, a warning message is generated. When set to OFF, no warning is issued.
|
|
Exec
|
Keywords:
SQL, exec
|
CREATE PROCEDURE demo @list VARCHAR(2000) AS DECLARE @sql VARCHAR(8000)
@sql = 'SELECT * FROM table WHERE ID IN (' + @list + ')' EXEC ( @sql ) GO
|
|
Exists
|
Keywords:
SQL, exists, select
|
SELECT DISTINCT au_fname, au_lname, state
FROM authors
WHERE EXISTS
(
SELECT *
FROM stores
WHERE state = authors.state
)
|
|
Having
|
Keywords:
SQL, having, group bt
|
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
|
|
HelpFile
|
Keywords:
SQL, sp_helpfile, log files
|
This command will give you all the info related to the database and log files. use database_name
go
sp_helpfile
go
|
|
In
|
Keywords:
SQL, IN
|
-- 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
|
Keywords:
SQL, insert
|
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
|
|
Insert From Another Table
|
Keywords:
SQL insert, from another table
|
INSERT INTO MerchantNotes (AppId, Code, Description, DateTimeCreated, CreatedBy, MCMoney, MCMoneyID, MCMoney_NoteID)
SELECT EDGE_AppId, EDGE_Code, Notes, Date, 'MeritCard Money', 1, Customer_ID, ID FROM Customer_Notes WHERE EDGE_AppId is not NULL
|
|
Insert or Update
|
Keywords:
SQL, stored procedure, proc, insert or update
|
SET NOCOUNT ON;
IF EXISTS ( SELECT PlacementID FROM MyTable (NOLOCK) WHERE PlacementID = @PlacementID ) UPDATE MyTable SET MapKey = ISNULL(@MapKey,MapKey) , iThemeID= ISNULL(@iThemeID, iThemeID) WHERE PlacementID = @PlacementID ELSE BEGIN INSERT INTO MyTable (MapKey, iThemeID, PlacementID) VALUES (@MapKey, @iThemeID, @PlacementID)
RETURN @@ERROR END
|
|
ISNULL
|
Keywords:
SQL, ISNULL
|
SELECT ProductName , bIsPaid , dtExpires , ISNULL(bIsUnPublished, 0) AS bIsUnPublished FROM Subscription
|
|
Join
|
Keywords:
SQL, join
|
JOIN: Return rows when there is at least one match in both tables LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table FULL JOIN: Return rows when there is a match in one of the tables INNER JOIN: keyword return rows when there is at least one match in both tables
-- INNER 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.
-- LEFT 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).
-- RIGHT 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).
-- FULL 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.
|
|
Length
|
Keywords:
SQL, length
|
select * from MyTable (nolock) where len(brands) >500
|
|
Linked Server
|
Keywords:
SQL, linked server
|
SELECT FROM SQL02.store.dbo.tbl_products
|
|
Loop
|
Keywords:
SQL, Loop, while
|
DECLARE @count INT SET @count = 0 WHILE (@count < 3) BEGIN select * from offices where officeid = 1000
SET @count = (@count + 1) END
|
|
Merge Data
|
Keywords:
SQL, merge data
|
-- Merge data from 2 different tables 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
|
|
Move Data From Another Table
|
Keywords:
sql, move data, another table
|
update t1 set agentid = t3.agentid from Residuals t1 inner join merchants t2 on t1.mid=t2.mid inner join aspnet_users t4 on t2.salesrep=t4.username inner join agents t3 on t4.userid=t3.userid
|
|
Multiple Left
|
Keywords:
SQL, multiple left join
|
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'
|
|
Nolock
|
Keywords:
SQL, nolock
|
Using NOLOCK asks SQL Server to ignore locks and read directly from the tables. This means you completely circumvent the lock system, which is a major performance and scalability improvement. However, you also completely circumvent the lock system, which means your code is living dangerously. You might read the not-necessarily-valid uncommitted modifications of a running transaction. This is a calculated risk.
SELECT *
FROM tbl_member WITH (NOLOCK)
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
|
|
Optional Parameter
|
Keywords:
SQL, optional, parameter, output
|
PROC [dbo].[ProfileSubscription_OUT] @iMemberID int, @ProductName varchar(64) = NULL OUTPUT
|
|
Order By Case: Sort
|
Keywords:
SQL, sort, orde by case
|
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
|
|
Query
|
Keywords:
SQL, query
|
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
|
|
Replace Bulk
|
Keywords:
sql, replace, bulk, charindex
|
Update merchants Set FederalTaxId = replace(FederalTaxId, '-', '') where appid In ( select appid from merchants where charindex('-',Federaltaxid) > 0
|
|
Restore
|
Keywords:
SQL, restore
|
-- Restore from an existing DB to a new DB (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".
|
|
RowId
|
Keywords:
SQL, RowId, row_number
|
select row_number() over(order by officeid) as RowId from offices where officeid = 1000
|
|
Select
|
Keywords:
SQL, select
|
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
|
|
Select Case
|
Keywords:
SQL, select case
|
SELECT OrderNumber, OrderDate, OrderStatus, Exported, ExportedDate, ExportedYesNo = CASE Exported WHEN '1' THEN 'yes' ELSE 'no' END FROM Orders
|
|
Select Into
|
Keywords:
SQL, select into, copy data, move data, backup
|
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
|
|
ShrinkFile
|
Keywords:
SQL, shrink, shrinkfile, truncate, log
|
--(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
|
|
SQL CharIndex
|
Keywords:
sql, charindex
|
SQL IN AND Charindex('M.SalesRep,', '@AgentString') > 0
This function will return you a table variable from a string
http://blogs.microsoft.co.il/blogs/itai/archive/2009/02/01/t-sql-split-function.aspx
So your SQl would be
Declare @t varchar(1000) SET @t = 'test1, test2'
Declare @tab TABLE ( ID INT IDENTITY(1,1), Data NVARCHAR(MAX) )
SELECT @tab= dbo.split(@t)
Then run a cursor over @tab to read each row and operate on the data.
|
|
SQL Native Client
|
Keywords:
SQL, native client, .udl, provider
|
(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"/>
|
|
Stored Procedure
|
Keywords:
SQL, stored procedure, SP
|
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
|
|
Stored Procedure: Return Parameter
|
Keywords:
SQL, stored procedure, proc, SP, parameter, return, @@ROWCOUNT
|
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
|
|
Sub Query
|
Keywords:
sql, sub query, select
|
SELECT m.MerchantName, m.MID, (SELECT UserId FROM aspnet_users u WHERE m.SalesRep = u.username) AS 'UserId', m.Office, m.SalesRep, o.Platform, m.FrontEnd, m.MicrosFee, m.AcctFeeAuthNet, m.Status FROM Merchants m LEFT OUTER JOIN MerchantsOwner o on m.appid = o.appid ORDER BY MID desc
|
|
Sub Select
|
Keywords:
sub select, select, SQL, count, join
|
SELECT B.OfficeId, B.OfficeName, O.OfficeStatus, B.SalesTrans, B.BCRDVol, B.AgentPayout, (SELECT COUNT(M.AppId) FROM Merchants M WHERE O.OfficeName = M.Office AND M.DateReceived BETWEEN '11/1/2010' AND '11/30/2010') AS 'Accounts' FROM OfficeBullsheet B INNER JOIN Offices O ON O.OfficeId = B.OfficeId WHERE B.ReportDate = '11/1/2010' ORDER BY B.BCRDVol DESC
|
|
SubString
|
Keywords:
SQL, SubString, SUM, WHEN
|
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
|
|
SysObjects Table
|
Keywords:
SQL, sysobjects
|
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
|
|
SysObjects: Default
|
Keywords:
SQL, sysobjects, default
|
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
|
|
Table Commands
|
Keywords:
SQL, create table, sysobjects
|
-- 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
|
|
Transfer Logins
|
Keywords:
SQL, users, transfer logins
|
Transfer logins between 2 SQL 2000 servers. 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.
|
|
Truncate
|
Keywords:
sql, truncate, sys.tables
|
Select 'truncate table ' + name from sys.tables where name like 'tbl[_]%'
|
|
Union
|
Keywords:
SQL, union
|
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.
|
|
Unique Constraint
|
Keywords:
sql, unique, constraint, alter table
|
alter table offices Add Unique(OfficeId)
|
|
Unload
|
Keywords:
SQL, unload
|
Use this statement to export data from a database table into an external ASCII-format file. UNLOAD TO 'clrefer' DELIMITER '!'
SELECT clnum, clrefer
FROM client
WHERE clrefer IS NOT NULL
|
|
Update
|
Keywords:
SQL, update
|
UPDATE Products SET ProductName = 'shirt' WHERE ProductPrice = 20.00
|
|
Update one Table with Value from Another Table
|
Keywords:
SQL, update, Update one Table with Value from Another Table, insert into, import
|
-- (1) Update
UPDATE tbl_Cust_Notes SET EDG_AppId = ( SELECT Merchants.AppId FROM tbl_Merchants WHERE Merchants.MCMoneyId = 1783091 ) WHERE Customer_ID = 1783091
-- (2) Import
INSERT INTO tbl_MerchNotes (AppId, Code, Description, DateTimeCreated, CreatedBy, MCMoney, MCMoneyID, MCMoney_NoteID)
SELECT EDG_AppId, EDG_Code, Notes, Date, 'MeritCard Money', 1, Customer_ID, ID FROM tbl_Cust_Notes WHERE Customer_ID IN (1783091)
|
|
Update: Where Exists
|
Keywords:
SQL update, where exists
|
UPDATE Customer_Notes SET EDGE_AppId = (SELECT Merchants.AppId FROM Merchants WHERE Merchants.MCMoneyId = Customer_Notes.Customer_Id) WHERE EXISTS ( SELECT Merchants.AppId FROM Merchants WHERE Merchants.MCMoneyId = Customer_Notes.Customer_ID);
|
|
Users
|
Keywords:
SQL, users
|
-- 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'
|
|
Users: Show All
|
Keywords:
SQL, users, sp_who2
|
sp_who2
|
|
Variable
|
Keywords:
SQL, variable
|
DECLARE @User_ID int
SET @User_ID = 1927
SELECT Merchant_ID
FROM MerchantAccess
WHERE User_ID = @User_ID
|
|