Sunday, August 9, 2009

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%'

Thursday, July 9, 2009

Writing text lines to a file

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

Tuesday, June 9, 2009

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)

Tuesday, March 24, 2009

Wednesday, February 25, 2009

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

Tuesday, February 24, 2009

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)

Tuesday, January 27, 2009

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

Friday, January 23, 2009

Time Only

declare @date DateTime

set @date = GETDATE()

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

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

Wednesday, January 21, 2009

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

Tuesday, January 20, 2009

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.

Thursday, January 8, 2009

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
*/

Tuesday, January 6, 2009

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)

Monday, January 5, 2009

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.

Saturday, January 3, 2009

Create an Index

CREATE INDEX [IX_Customers_StartDate] ON Customers (StartDate)