IT Tech BuZ - SQL - Crystal

Shrink log file script for MSSQL Server

2011-09-05T10:28:00.000+01:00

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

2009-12-18T18:33:00.000Z

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

2009-10-30T20:41:00.000Z

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

2009-09-07T17:19:00.034+01:00

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 with Transaction

2009-08-28T15:54:00.009+01:00

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

2009-07-26T14:17:00.016+01:00

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;

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:

Anniversary of IT Tech Buz EN Technical

2009-06-21T00:33:00.003+01:00

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 !!!

A carregar...

Database Triggers

2009-06-05T23:30:00.005+01:00

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

2009-04-21T12:16:00.007+01:00

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

USE [aspnetdb] -- Replace with your DB

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

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

Create procedure [dbo].[usp_copyfile]

@msg as varchar(200),

@input as varchar(100)

,@retorno as varchar(200) output

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

2009-03-04T11:46:00.016Z

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:

The New Logo - IT Tech BuZ

2009-01-22T23:04:00.003Z

Please make your comments about the new look of the Logo

Create SP with Cursor

2009-01-18T18:43:00.005Z

These is my first post in the New Year of 2009, because i had lot's of thing in my professional side, you already notice the change in the logo and in the name of the Blog, i had register the domain www.ITTechBuZ.com e novo logo.
The logo, pretends to give the communication as the major role between Business and IT.
This blog continues to post for Tech people. Today I post one more of the saga of SQL Tip and Tricks about a Store procedure (SP) with a cursor inside to update maintance contracts

--
-- Creates SP com o nome sp_update_onsite
--
create procedure [dbo].[sp_update_onsite]
as Begin

--
-- Inserts ticket in a table when the status is 'Closed'
-- I use an Insert with a Select
--

INSERT INTO [HEAT].[dbo].[Tickets] ([CallId] ,[CallType] ,[CriData] ,[Valor] ,[Saldo] ,[UIDMANUT])

select d.callid,c.calltype,c.closeddate, d.horas_gastas, d.horas_saldo
,d.uidmanut,c.custid
from detail d
inner join calllog c on c.callid=d.callid
inner join subset s on s.callid=c.callid
inner join config cfg on cfg.u_idreg=d.uidmanut
where (c.calltype='Onsite' or c.calltype='Packs') and c.callstatus='Closed' and c.actualiza<>'SIM'
and c.callid not in (select callid from tickets )

--
--
-- Cursor used to update
--

-- Declares the Cursor Cursor_Tickets
-- and Select where it runs the Cursor

DECLARE Cursor_tickets CURSOR for
SELECT t.CallId,c.custid,t.Valor,t.UIDMANUT
from tickets t
inner join calllog c on c.callid=t.callid
where t.[CallType]='Onsite' and c.actualiza <>'SIM'

--
-- Declares the variables to pass througth
--

Declare @Callid varchar(8)
declare @custid varchar(50)
declare @valor decimal(17,2)
declare @uidmanut varchar(25)

set @callid=''
set @custid=''
set @valor=0
set @uidmanut=''

Open Cursor_tickets /* abrir o cursor */

fetch next from Cursor_tickets
into @callid,@custid,@valor,@uidmanut
while @@fetch_Status=0
begin

--- Updates hours spent
--- select @valor,@custid,@uidmanut (for Testing purposes)

update config set horas_gastas=isnull(@valor,0)+(isnull(horas_gastas,0)) ,horas_saldo=isnull(horas_saldo,0)-isnull(@valor,0)
where u_idreg=@uidmanut and custid=@custid

set @callid=''
set @custid=''
set @valor=0
set @uidmanut=''

fetch next from Cursor_Tickets
into @callid,@custid,@valor,@uidmanut
end
close Cursor_tickets
Deallocate Cursor_tickets

--
-- In the end updates the table for not processing agains
--

update calllog set actualiza='SIM' where callid in (select callid from tickets) and actualiza<>'SIM'

end

Merry Christmas and Happy New Year

2008-12-21T00:03:00.004Z

I whish you all a merry christmas and a better New Year.

New Toolbar Feature

2008-11-21T22:20:00.004Z

I was surfing around and i rediscover a free online toolbar for those who want just click on the image bellow or go to http://jconline.ourtoolbar.com/

Yes you can make your own !!!

Create trigger for a table After Inserted

2008-11-18T14:17:00.005Z

These sp creates automatically a trigger when invoked by job in SQL Server, these is very usefull and handy.

Create procedure [dbo].[sp_creates_trigger]
as
begin

declare @text nvarchar (800 ) -- Declares a variable called texto

set @text='CREATE TRIGGER [subset_trig] ON [dbo].[Subtable] AFTER INSERT AS BEGIN SET NOCOUNT ON; declare @tfield as varchar(8) set @tfiled = (select id_num from inserted)
update subtable set ufield = @tifield where customer<>''Web'' and id_num=@tfield
END'
-- set the trigger in table subtable for an action After Inserted that updates the table
exec sp_executesql @texto -- executes sp_exceutesql with the text command
end

Google Analytics Blog: More Enterprise-Class Features Added To Google Analytics

2008-10-23T12:31:00.003+01:00

Google Analytics Blog: More Enterprise-Class Features Added To Google Analytics

Still not available to all users but a huge step forward see the video

But we need to track an user and see what is the navigation of the content for example:

city ->New York -> Content visited
region ->California -> Content visited
Network location -> Tv Cabo Portugal-> Content visited
and so one with all the metrics including goal's, % exit's and Bounce Rate.

And with Advance segmentation it's huge step forward

Email Tracking with Google Analytics

2008-09-30T12:27:00.006+01:00

Never was so simple to track your email such as Newsletter so i am going to explain you just how easy if you already don´t know, if i make a newsletter your links have to go like these

http://jorge.m.cunha.googlepages.com/newsletter3.html?utm_source=Newsletter&utm_medium=email&utm_content=Newsletter&utm_campaign=Newsletter3

so after the your page link or your link you to add ?utm_source=Newsletter&utm_medium=email&utm_content=Newsletter&utm_campaign=Newsletter3

- Where is green you have to made changes to your

- utm_source=Newsletter (Which action)

- utm_medium=email (Which medium)

- utm_content=Newsletter (What kind of content)

- utm_campaign=Newsletter_561 ( Which campaign)

Then you can see the results in your Google Analytics account

Don´t forget to publish the html file in your site and include the script of Google Analytics to track your page

Renaming SQL SERVER

2008-09-15T20:59:00.005+01:00

When you need to change the name of the server in the operating system and you need to change the name of the SQL Server

First of all Backup your databases
select @@SERVERNAME then it returns the the name of the SQL Server
sp_dropserver 'jmc-pc' name of the server
go
sp_addserver 'ws-jmc', local
go
repeat step 2 after restarting SQL Server to check if the name of SQL Server is OK
More Information in http://msdn.microsoft.com/en-us/library/ms143799.aspx

Related Posts:

Stored procedure to copy files

SQL date manipulation

Database Triggers

Create SP with Cursor

My Holidays

2008-08-31T15:54:00.002+01:00

I have been on holiday in Trás os Montes, Portugal and i share some photos

In Pinhão

Rio de Onor

Museum of Iberian Masc

Bragança seen from the Castel

Miranda Do Douro

Trancoso

How to Solve problems in SQL with Identity

2008-07-30T21:42:00.006+01:00

I was working with identity and i had found an problem and a solution to solve these kind of problems.

To see which number is on the identity column:

--
-- See what is the Identity in the table Articles
--
DBCC CHECKIDENT

(

articles

)
Response is:
Checking identity information: current identity value '6', current column value '6'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

To setup with a new number on the identity column

DBCC CHECKIDENT

(

articles,reseed ,6000

)

it returns

Checking identity information: current identity value '1000', current column value '6000'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Note:
Don´t use truncate to delete table records it´s faster but it resets the identity column use instead the delete statement

Select Excel

2008-07-14T20:26:00.006+01:00

I am going to show on select i use to read excel files XLS

Example:
Select *
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=D:\import\Excel_file.xls;Extended Properties=Excel 8.0')...Sheet1$

To do a proper use of these you must have your excel with headings and these heading must be unique and where you see Sheet1$ is the name of your sheet in your excel remember to keep these name simple and without spaces. The columns must be well formated or you don´t get the results you expect.

Help the world go to http://www.ami.pt

Crystal Reports Tip While Printing Records

2008-06-05T22:52:00.007+01:00

Crystal Reports

These tip is very useful when you need to evaluate on run time total's or if specify page footer only prints while the condition is true for example:

WhilePrintingRecords; Booleanvar myvar true

So you can do special calculations.

Crystal Reports SQL Expressions

2008-05-28T17:57:00.001+01:00

Very nice explantion about SQL expressions in Crystal

The Crystal Reports Underground News: "Doing a SELECT in a SQL Expression field:

When teaching SQL expression fields I have always tried to stress that SQL Expressions are different from SQL Statements. A SQL Expression is a column in the report, where a SQL statement is a full query. My short version of this was to say 'a SQL Expression can't do a 'SELECT'. Well I recently learned that this is not precisely true. Under certain situations, a SQL Expression CAN do a completely separate select from the main report.

The main limitation is that it can only return a single value. So you probably will need a summary function. The following example comes from the Xtreme Sample Database:
(SELECT Max ( Orders.`Order ID`)
FROM `Orders` Orders)

Normally a CR SQL Expression would error on the SELECT, but if you put this expression in parentheses, Crystal will pass it to the database as a separate query. Amazingly, the column being queried does not even have to come from one of the tables in the report, but can be from another table in the database. In the past I would have recommended doing this via subreport. The advantage of a SQL Expression is that the value returned can be used to control things like Selecting, Sorting and Grouping in the report. To learn more about using complex SQL Expressions see my Experts Guide to SQL Expressions, Options and Commands."

Vista Status

2008-05-22T00:20:00.004+01:00

Nowadays Vista has become more stable, in my
experience with Vista in English and in Portuguese I think that company's now can put in to dos list an adoption on Vista but we to consider some details first:

- Hardware
Hardware requirements to a smooth the PC's need at least 2 Gb of Ram and processor Duo core or smilar. You have to check if the hardware is Vista Compliant and check if you the drivers. You can follow these link
http://www.microsoft.com/downloads/details.aspx?
FamilyId=67240B76-3148-4E 49-943D-4D9EA7F77730&displaylang=en
To do an inventory in your network such as hardware inventory, compatibility list and reporting.
For those who want to do on single XP PC you can follow these link for microsoft http://www.microsoft.com/downloads/details.aspx?
displaylang=en&FamilyID=42b5ac83-c24f-4863-a389-3ffc194924f8

- Software

In these area you to make an inventory and see if any software that you have isn't compatible with Vista and you must have a special concern regarding legacy applications.
The next step is to do pilot critical application to your business to make these transition smooth like river with no obstacles

If your site PC's are for the major Vendors you should consider to go to the vendor website.

the version of Vista that i recommend is Vista Business
of course in English, because is the most tested and native for the OS

JC

Friend Connect, Open Social, and Ning

2008-05-14T14:16:00.002+01:00

Good post relating social Networking

read more | digg story