Shrink log file script for MSSQL Server

When trying to shrink huge log files, because of the missing backup of transaction log and corresponding option to truncate log. So first backup your databases and replace your_database with one you want to.


 USE  Your_Database
 GO
 DBCC SHRINKFILE(Your_Database_log, 10)
 GO
 BACKUP LOG  Your_Database WITH TRUNCATE_ONLY
  GO
 DBCC SHRINKFILE(Your_Database_log, 10)
 GO

Apply to MSSQL Server 2005

SQL Trigger

Triggers in MSSQL are very useful but we need to take some precautions about it because of use of nested triggers the SQL Server only go up to 32 nested triggers. Triggers can be used in different situations like:

• Insert, Update, Delete  

Using the AFTER keyword that is default or using FOR keyword

Example using FOR and deleted :

CREATE TRIGGER Save_Deleted_records

ON dbo.able_Test

FOR DELETE

AS

INSERT deleted_records_mark

SELECT * FROM deleted

more on microsoft Web Site:http://msdn.microsoft.com/en-us/library/ms190739(SQL.90).aspx

Example using a AFTER Keyword

Create TRIGGER dbo.UpdateTrigger ON dbo.Table3 

AFTER INSERT AS 

 BEGIN 

   SET NOCOUNT ON; 

     declare @ticket  as varchar(8) 

     set @ticket = (select callid from inserted)

     update subset set uticketrem = @ticket where custtype<>'Facility' and callid=@ticket

END

• Instead of Insert, Update, Delete 

these means that you can do other operations like adding or deleting records


CREATE TRIGGER Example_Trigger on Table1
INSTEAD OF INSERT
AS
BEGIN
  -- Instead of inserting you can do other operations
  INSERT INTO Table45
       SELECT RowMatCol, Date_Doc
       FROM inserted
END


More on Microsoft Web Site here: http://technet.microsoft.com/en-us/library/ms175089.aspx

Happy Christmas and an Happy New Year 

Sponsored by IT Tech BuZ

SQL Function to Concatenate DateTime

Today I bring to you a useful SQL function to get a string like YYYYMMDDHHMMSS (example for 30-10-2009  20:01:01 will be 20091030200101).

Use YourDatabase
Create function [dbo].[F_Datetime]()
returns varchar(50)
as
begin
declare @datanext varchar(50)
select @datanext =
(select (substring ((select convert(varchar(10),getdate(),103)),7,4)))+  -- Year
(select (substring ((select convert(varchar(10),getdate(),103)),4,2)))+ -- Month
((select (substring ((select convert(varchar(10),getdate(),103)),1,2)))+ -- Day
replace((select convert(varchar(8),getdate(),108) ),':','')) -- hour and minutes HHMMSS

return @datanext
end  



Then you can use like these:

Use YourDatabase
Go
Select  [dbo].[F_Datetime]()


Then your result looks like these:


20091030203830

Viewing Jobs running, using a MSSQL Statement

Sometimes we need to view which jobs are running in different contexts like administrative proposes or for data update and we need to see what´s is happening.

USE msdb ;


GO


EXEC dbo.sp_help_job;



Go


USE msdb ;



GO


EXEC dbo.sp_help_job



@job_name=N'MaintenancePlanBackup',   -- Specify the name of your job

@job_aspect=N 'ALL';


GO
/*
Using Multiserver
*/


USE msdb ;

GO


EXEC dbo.sp_help_job

@job_type=N'MULTI-SERVER',
@owner_login_name=N'sa', @enabled=1 , @execution_status=1;


GO


View this link for more information
http://msdn.microsoft.com/en-us/library/ms186722.aspx



/*
View Activity Jobs
*/


USE msdb

GO


EXEC  dbo.sp_help_jobactivity ;


GO


Related Posts:

• Error Handling SP 
• SQL Date Manipulation Functions and Examples
• Database Triggers
• Create SP with Cursor
• Stored Procedure to Copy Files

Error Handling SP with Transaction

Let´s see best practices implemented on creating Stored procedures. So when we create the procedure we must implement some kind of error handling to control. So let´s see what is the syntax:

Create Procedure Nametheprocedure
-- Parameters input or output
As
BEGIN TRY
Begin Transaction
-- Commands
commit transaction -- If it runs without error commit the transaction

END TRY

BEGIN CATCH

SELECT ERROR_NUMBER(), ERROR_MESSAGE()

Rollback
END CATCH

GO

So these is the syntax and bellow is an example



USE AdventrureWorks
go
CREATE PROCEDURE Production.AddReview
@ProductID int,
@ReviewerName nvarchar(50),
@EmailAddress nvarchar(50),
@Rating int,
@Comments nvarchar(3850)
as


BEGIN TRY
Begin transaction;
INSERT Production.ProductReview (ProductID, ReviewerName, EmailAddress, Rating, Comments)
VALUES (@ProductID, @ReviewerName, @EmailAddress, @Rating, @Comments)
commit transaction;
END TRY

BEGIN CATCH

SELECT ERROR_NUMBER(), ERROR_MESSAGE()
rollback transaction;
END CATCH

GO

-- Test stored procedure with error handling
EXECUTE Production.AddReview 715, 'Doug', 'doug@siteexample.com', 10, 'Great product!'
GO

See you Soon :)

Related Posts:

• Stored procedure to copy files

• SQL date manipulation

• Database Triggers

• Create SP with Cursor

SQL Date Manipulation Functions and Examples

Date manipulation in SQL Server there are several functions, lets see same examples of that:

-- Example 1

select getdate()

these function returns current date time like 2009-07-26 14:55:03.210

-- Example 2

select GETDATE()+60

On these example we add 60 days and the result is 2009-09-24 14:56:40.210

-- Example 3

select DATEADD(M,2,getdate()) -- M month, y year, d day

The DateAdd function adds months, years or days to date supplied

So you can see that adding 2 month to the current date and the result is 2009-09-26 14:57:48.117

see the differente results between example 2 and 3 ( 2 days), because in the example 2 we add 60 days not 2 month.

-- Example 4

select DATEDIFF(DAY,(DATEADD(M,2,getdate())),(GETDATE()+60))

The function DateDiff calculates the difference between to dates and returns int, using the examples that you saw the result is -2

-- Example 5

SET DATEFORMAT dmy;

GO

DECLARE @datevar datetime

Set @datevar=GETDATE()

select @datevar

On this example you will saw how to format the date to British format

-- Example 6

select substring((convert(varchar(10),@datevar,127)),1,10)

result 2009-07-26

Converting date type to output varchar(10) using parameter 127 ( yyyy-mm-dd)

-- Example 7

select substring((convert(varchar(10),@datevar,103)),1,10)

result 26/07/2009

Converting date type to output varchar(10) using parameter 103 ( yyyy-mm-dd)

See more about convert function in http://msdn.microsoft.com/en-us/library/aa226054(SQL.80).aspx

See more on Date on http://msdn.microsoft.com/en-us/library/aa258863(SQL.80).aspx

Post that can help you avoid some problems:

• How to solve problems with Identity 
• About SQL Bulk Insert
• Create Stored Procedure with Cursor

Anniversary of IT Tech Buz EN Technical

I would like to thanks our readers, sponsors and My wife and son for one more year with in a time of change.I see that our visitors are growing on a daily basis for me it's really a pleasure.
To improve I will need some feedback, only 2 questions !!!

Database Triggers

One more post about SQL Tips and Tricks that i think that have is own value. Today I write about Database Triggers, they are used for security reasons and audit operations, let's see the example.

Use Example_DB
go
Create TRIGGER [DBTRG_Test_Drop_Create]
ON DATABASE
FOR DDL_TABLE_VIEW_EVENTS
AS
DECLARE @data xml
DECLARE @cmd nvarchar(350)
DECLARE @logMsg nvarchar(400)

SET @data = eventdata()
SET @cmd = @data.value
('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(350)')
SET @logMsg = @cmd + ' (' + SYSTEM_USER + ' on ' + HOST_NAME() + ')'

RAISERROR (@logMsg, 10, 1) WITH LOG

-- To visualize the command uncomment the line bellow
-- select @cmd


-- Here we don't allow command that begin with "Create"
-- if the condition is true we do rollback

if left(@cmd,6)='Create'
rollback

-- Here we don't allow command that begin with "Drop"
-- if the condition is true we do rollback

if left(@cmd,4)='drop'
rollback

Note: We can create a table with type specified in variables and insert the attemps of create and drop objects.

Related Posts:

• Stored procedure to copy files

• SQL date manipulation

• Database Triggers

• Create SP with Cursor

Stored Procedure to Copy Files

Stored procedure to help you out to copy files see the code bellow

USE [aspnetdb] -- Replace with your DB

GO

/****** Object: StoredProcedure [dbo].[usp_copyfile] Script Date: 04/21/2009 09:22:20 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

Create procedure [dbo].[usp_copyfile]

@msg as varchar(200),

@input as varchar(100)

,@retorno as varchar(200) output

as

Begin

declare @drive as varchar(100)

declare @drive_dest as varchar(100)

declare @path as varchar(800)

declare @path_dest as varchar(800)

declare @cmd as varchar(800)

declare @ext as char(3)

set @path_dest='\Dest_Path\'

-- Para Exemplo, não esquecer barra de directorio final

-- Do not forget of blackslash

--

set @path='\PROGRA~1\DATA\'

set @drive='c:'

set @drive_dest='d:'

set @cmd='copy '+@drive+@path+@msg+@ext

set @cmd=@cmd+@drive_dest+@path_dest+ltrim(rtrim(@input))+(select cast(day(getdate()) as varchar(2)))

+(select cast(month(getdate()) as varchar(2)))

+(select cast(year(getdate()) as varchar(4)))

+replace((select convert(varchar(8),getdate(),108)),':','')+@ext

-- Para Testar Caminhos e Visualizar

-- For testing paths and to show

-- select @cmd

exec master..xp_cmdshell @cmd ,no_output

Set @retorno=@drive_dest+@path_dest+ltrim(rtrim(@input))+(select cast(day(getdate()) as varchar(2)))

+(select cast(month(getdate()) as varchar(2)))

+(select cast(year(getdate()) as varchar(4)))

+replace((select convert(varchar(8),getdate(),108)),':','')+@ext

end

Related Posts:

• Stored procedure to copy files

• SQL date manipulation

• Database Triggers

• Create SP with Cursor

More on Crystal Reports SQL Expressions and WhilePrintingRecords

I decided to talk about more on Crystal Reports and about SQL expressions fields that can be tricky I have made some screenshoots that can help YOU. See below

Let's see Closer the intruction remenber on

Convert text to varchar

• The formula bellow sums field1 group by field2

Whileprintingrecords;
sum ({table1.field1},{table1.field2})

• Sums two fields in the bellow example
  

WhilePrintingRecords;
({file.Qty1} + {file.Qty2})

more on these useful  Link


Related Posts:

• Crystal SQL Expressions
• Crystal Reports While Printing