Find specific text in stored procedures

From http://databases.aspfaq.com/database/how-do-i-find-a-stored-procedure-containing-text.html

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%foobar%'
AND ROUTINE_TYPE='PROCEDURE'

select * from information_schema.routines
where ROUTINE_DEFINITION LIKE '%foobar%'

Writing text lines to a file

using (System.IO.StreamWriter sw = new System.IO.StreamWriter(@"c:\TestFile.txt", true))
{
sw.WriteLine(rawString);
}

Foreign Key

Image


ALTER TABLE ChildTable
ADD CONSTRAINT LoremIpsum
FOREIGN KEY (ChildColumn) REFERENCES ParentTable (ParentColumn)


ALTER TABLE [dbo].[UserProducts]
ADD CONSTRAINT [FK_UserProducts_Users]
FOREIGN KEY (UserID) REFERENCES [dbo].[Users](UserID)

Split stored procedures into subprocedures

Image


end

Exception Handling TRY-CATCH T-SQL

CREATE PROCEDURE UpdateClient
(
@ClientID int,
@Name varchar(50)
)
AS
BEGIN
declare @someID int

BEGIN TRY
BEGIN TRAN

SET @Name = NULLIF(@Name, '')

INSERT INTO ...
VALUES ...

SELECT @someID = scope_identity()

UPDATE ...
SET SomeID = @someID
WHERE ClientID = @ClientID

COMMIT TRAN
END TRY

BEGIN CATCH
ROLLBACK TRAN
EXEC ThrowException
END CATCH
END
GO

/*
*/

CREATE PROCEDURE ThrowException
AS
DECLARE @errorMessage nvarchar(4000),
@errorSeverity int

SELECT @errorMessage = ERROR_MESSAGE(),
@errorSeverity = ERROR_SEVERITY()

RAISERROR(@errorMessage, @errorSeverity, 1)
GO

DECLARE variables

in a stored procedure

declare @errorMessage nvarchar(4000),
@errorSeverity int

DECLARE
@ErrorMessage nvarchar(4000),
@ErrorNumber int,
@ErrorSeverity int,
@ErrorState int,
@ErrorLine int,
@ErrorProcedure nvarchar(200)

Add new columns to a table

-- add 2 columns
ALTER TABLE Users
ADD MinAmount decimal(18,2) NULL,
MaxAmount decimal(18,2) NULL
GO

related:
http://fcmendoza02.blogspot.com/2008/07/add-new-not-null-column.html

Remove column from table

-- Delete column
ALTER TABLE Users
DROP COLUMN Nickname
GO

Related:
http://fcmendoza02.blogspot.com/2008/11/remove-fk-from-table.html

Time Only

declare @date DateTime

set @date = GETDATE()

SELECT LEFT(CONVERT(VARCHAR(8), @date, 8), 5) AS TimeOnly

/*
TimeOnly
--------
15:04
*/

Grant Event Log permissions to an account

On Windows 2003 Server
go to HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\EventLog:



Then add permissions to a specific account.

related link: http://blogs.objectsharp.com/cs/blogs/bruce/archive/2003/11/03/180.aspx

aspnet_wp vs w3wp

from http://geekswithblogs.net/vivek/archive/2006/10/18/94436.aspx

The way an ASP.NET request is handled by IIS is quite different in IIS 6.0 when compared with 5.0. In 5.0, the ASP.NET worker process is handed off control by the aspnet_isapi extension in IIS. The aspnet_isapi dll runs in the inetinfo.exe process in IIS and functions what is known as the CLR host (a CLR host is the piece of unmanaged code which is responsible for loading the CLR into the memory). So aspnet_isapi “hands over” the processing to the worker process named aspnet_wp.exe, where the request passes through a series of HttpModules and an HttpHandler.

But in IIS 6.0, there is a driver named http.sys which listens to all the incoming requests (aspnet_isapi.dll is not in the picture at this point). The moment an ASP.NET specific request comes in, this driver starts an IIS 6.0 worker process (which is not related to ASP.NET at all) named w3wp.exe. This process now loads the aspnet_isapi.dll (CLR host) and the request follows through a similar sequence of HttpModules and HttpHandlers.

So the important thing to note here is that w3wp.exe is not an ASP.NET worker process (unlike aspnet_wp.exe) but instead specific to IIS 6.0.

Today

declare @today datetime
set @today = CAST(convert(varchar, GETDATE(), 101) as datetime)

SELECT @today AS Today

-- Result (year-month-day):
/*
Today
-----------------------
2009-01-08 00:00:00.000
*/

Create UNIQUE constraint

-- RecordNumber will now be a unique field

ALTER TABLE Clients
ADD CONSTRAINT UN_Clients_RecordNumber
UNIQUE (RecordNumber)

-- LocationID, PhoneNumber and StartDate will be unique per row

ALTER TABLE Clients
ADD CONSTRAINT UN_Clients
UNIQUE (LocationID, PhoneNumber, StartDate)

Mailbox unavailable. The server response was: 5.7.1 Unable to relay for ...

Mailbox unavailable. The server response was: 5.7.1 Unable to relay for ...





end.

Create an Index

CREATE INDEX [IX_Customers_StartDate] ON Customers (StartDate)