Sql Server, what are you getting at?

Some of SSIS stuff: variables and its assignations

2007-12-11T03:21:00.000-08:00

Some of SSIS stuff: VARIABLES and its ASSIGNATIONS.

For a file connection log:

(ConnectionString)
@[User::RutaLog]+ @[System::PackageName] + ".log"

Where RutaLog stands for a string value which contains the path for you .LOG file

For a OLEDB Connection:
(InitialCatalog)
@[User::BD]

Where BD stands for string value which contains the name of your database

For a properties such as MessageSource (Send Mail Task) or whatever similar:

" El Modelo " + @[User::MODELO] + " del ejercicio " + (DT_WSTR,4)@[User::EJERCICIO] + " se ha cargado . \nSe han insertado " + (DT_WSTR, 8) @[User::REGISTROS] + " registros . \nSe puede comprobar desde la aplicación NS"

REMARK: I warn you the use for '\n' literals inside the main literal.

Raising EM through Microsof Management Console

2007-12-11T03:18:00.000-08:00

From DOS session:

"C:\Program Files\Microsoft SQL Server\80\Tools\BINN\SQL Server Enterprise Manager.MSC"

How many transactions has been commited in my database?

2007-12-11T01:26:00.000-08:00

Just a beta code. Then, as you like you can schedule that stored procedure in a job
It uses internal esctructures such as LOP_BEGIN_XACT and are not documented in public forums.

CREATE TABLE [dbo].[TRA_TRANSACCIONESMENSUAL]
( [ID] [int] IDENTITY (1, 1) NOT NULL ,
[OPERATION] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TOTAL] [int] NULL,
[BASE_DATOS] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FECHA] [datetime] NULL )
ON [PRIMARY]GO

CREATE PROCEDURE TRA_ListaTransaccionesMensuales
DECLARE @BD AS CHAR(25)
DECLARE @sql2 AS CHAR(500)

DECLARE cursorbd CURSOR FAST_FORWARD FOR
SELECT NAME FROM master.dbo.SYSDATABASES

OPEN cursorbd
FETCH NEXT FROM cursorbd INTO @BD
WHILE @@fetch_status = 0
BEGIN

SET @sql2= ('USE ' + @BD + ' INSERT INTO MASTER.DBO.TRA_TRANSACCIONESMENSUAL(OPERATION,TOTAL,FECHA) SELECT Operation, COUNT(*), GETDATE() FROM ::fn_dblog(null,null) WHERE Operation IN (''LOP_BEGIN_XACT'', ''LOP_COMMIT_XACT'') GROUP BY OPERATION UPDATE MASTER.DBO.TRA_TRANSACCIONESMENSUAL SET BASE_DATOS = ''' + UPPER(ltrim(Rtrim(@BD))) + ''' WHERE [id] = (SELECT SCOPE_IDENTITY()) OR [id] = (SELECT SCOPE_IDENTITY() -1 )')
-- print @sql2

EXEC (@sql2)
FETCH NEXT FROM cursorbd INTO @BD

END
CLOSE cursorbd
DEALLOCATE cursorbd

Querying for random results

2007-12-11T01:17:00.000-08:00

--You can catch up rows at random by means of NEWID() function (either Sql2k or Sql25k)

SELECT TOP 1 *
FROM
ORDER BY NEWID()

--And this one is useful when you want to obtain a LONG random value (either Sql2k or Sql25k)

SELECT right(cast(rand(checksum(newid())) as decimal(15, 15)), 15)

How can I see data stored in MDF data pages ?

2007-12-11T01:12:00.000-08:00

-- Just an example using a simple word ('pablo')

USE tempdb

CREATE TABLE p1(
nom CHAR(20)
)

INSERT INTO p1(nom) VALUES('pablo')
SELECT * FROM p1

SELECT *
FROM sysindexes
WHERE id = OBJECT_ID('p1')

--It will do conversion for FIRST (0xnn) field to DECIMAL
DBCC TRACEON(3604)

DBCC PAGE (tempdb,1,34,1)

--disabling trace indicators DBCC TRACESTATUS (3604)
DBCC TRACEoff (3604)

Sql25k.SSIS: SMTP mail from the Script Task

2006-07-13T09:25:00.000-07:00

A lot of people need obviously to send email. That's fine. But we might know that SMTP Mail task doesn't support sending email when you use http protocol.However, it's possible to use the script task in order to achieve this.

Imports System
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Net.Mail

Public Class ScriptMain

Public Sub Send_Email()

Dim Message As MailMessage
Dim Client-SMTP As SmtpClient

Message=New MailMessage(evivesg@cam.es,"evivesg@cam.es", "test1", "xxxxxxxxxxx")

--to allocate IP ADRESS Client-SMTP = New SmtpClient("nnn.nnn.n.nn")

Client-SMTP.Credentials = CredentialCache.DefaultNetworkCredentials
Client-SMTP.Send(Message)

Dts.TaskResult = Dts.Results.Success
End Sub

End Class

Sql25k: Undocumented stored procedures

2006-07-13T09:08:00.000-07:00

Some of them are fully available. Sp's such as sp_MSforeachdb or sp_Mshelpcolumns are available yet on Sql Management Studio whereas other such as sp_MSgettools_path not.

Why happen this?

Retrieving results from system stored procedures

2005-08-01T05:51:00.000-07:00

For instance:

INSERT INTO #sp_helpprotect_results
EXEC sp_helprotect NULL

Don't forget create a table structure with the same number of fields

Obtaining the size of the MDF file

2005-08-01T05:43:00.000-07:00

SELECT size*8192 as FileSize
FROM master..sysaltfiles
WHERE fileid=1
AND dbid=DB_ID('')

The faster way to know if an object exists or not

2005-08-01T05:38:00.000-07:00

SELECT OBJECT_ID('Prueba1SQL..testenr')

Avoiding locks in tables

2005-06-06T11:32:00.000-07:00

Using the 'nolock' clause we are avoiding create collisions or jam
if in that moment another task is updating the same table

update tblTable
set sinStepCodeId = 2
from tblTable
where sinStatusid in(2)

go

Select count(*) from tblTable nolock

go
...

How can we obtain all the current databases?

2005-06-06T10:26:00.000-07:00

To list the databases available in your SQL Server and show the path of
the .MDF data files:

SELECT [name] [Database], [filename] [File Path]
FROM master..sysdatabases

Obtaining all the fields in a table

2005-06-06T10:12:00.000-07:00

Many times we need to know the name of an field from Query Analyzer and we have open
Enterprise Manager with another task with which sometimes is difficult open another MMC->Enterprise Manager.

Forget to do this:

sp_help [table]

And try this query:

Select top 0 * from [table]

So this way you obtain each of the fields, just the name.

Deleting duplicates

2005-06-06T08:26:00.000-07:00

How can we delete duplicate rows in a table of way fast and safe?

It's easy, if our table own defined a identity field then:

Delete from table
Where identity_field not in ( SELECT MAX( identity_field) FROM table GROUP BY [field1],[field2]...)

About me

2005-06-06T07:51:00.000-07:00

I was born in Barcelona thirty five years ago and I am currently living in Alicante with my wife and my little son. Without a doubt, they are my inspiration and my happiness.

Since I began to work in IT almost ten years ago I have worked in lots of positions such as Help Desk second level operator, programmer or project leader. I am very keen of data, its particularities as well as your inherent properties. I love SQL, overall SQL93 although I have worked with AS-400 or Informix.

I am consider myself a home-loving person and when I am not working in my office or destroying any system I am fully focused with my little hero called Pablo and my dear wife.