How to upload/import multiple .trc files to a trace table in SQL Server

1. Bring each one of the trc files to the database 
SELECT * INTO trace_folder_file1 
FROM ::fn_trace_gettable('c:\Program Files\Microsoft SQL Server\MSSQL10_50\MSSQL\Log\log_1.trc', 5) 
SELECT * INTO trace_folder_file2 
FROM ::fn_trace_gettable('c:\Program Files\Microsoft SQL Server\MSSQL10_50\MSSQL\Log\log_2.trc', 5) 

2. Query the table to identify queries based on the information you need.
In this case it will bring all the queries that have something in the duration column 

SELECT * FROM ( 
SELECT * FROM [dbo].[trace_folder_file1] 
UNION ALL 
SELECT * FROM [dbo].[trace_folder_file1] ) 
AS DT 
WHERE [Duration] > 0

Database does not exist when configuring Log Shipping for a database in SQL Server 2005

When you are trying to configure a Log Shipping for a database in SQL Server 2005 and you get the error: 

Database does not exist as log shipping primary.

1. Validate the server name on both primary and secondary instance:

SELECT @@servername
EXEC sp_helpserver

2. If you are getting NULL as a result of the SELECT then add the server to the instance:

sp_addserver '\', local

3. If you get the error:

Msg 15028, Level 16, State 1, Procedure sp_MSaddserver_internal, Line 89
The server '\' already exists.

4. You should need to drop the server first and then add it again:

sp_dropserver '\'

5. And then add the server name:

sp_addserver '\', local

Finally try again running the Log Shipping configuration script or run it from the wizard. 

SQLCMD.EXE on SQL 2008 doesn’t work – HResult 0×2, Level 16, State 1

When you are using SQL Server Express 2008 w/ Advanced Services and get the following error trying to execute sqlcmd:

HResult 0×2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or instance-specific error has occurred while establishing
a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured
to allow remote connections. For more information see SQL Server Books Online.
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.

1. Enable named pipes and TCP/IP on Network protocols
2. Restart the service
3. If you are still having problemas, change the named pipe to the following.
\\.\pipe\sql\query

SQL Server 2008 - SQL Server Management Studio

When you are using the table designer and trying to set a column as primary key you get an error saying "Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made change to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created"

To solve the problem go to:
1. Tools menu option in SQL Server Management Studio
2. Options
3. Designers
4. Un-check the Prevent saving changes that require table re-creation

SQLServer 2005 Drop database before removing log shipping

When you have removed a database before cleaning up the log shipping configuration and getting the following error on the Application Event Viewer:

The log shipping primary database . has backup threshold of 60 minutes and has not performed a backup log operation for minutes. Check agent log and logshipping monitor information.

1. Get the primary and secondary database names and secondary servers from the following tables:

select * from msdb.dbo.log_shipping_primary_databases
select * from msdb.dbo.log_shipping_primary_secondaries

2. Run the following store procedures on the primary server

USE master
EXEC sp_delete_log_shipping_primary_database @database= ''
EXEC sp_delete_log_shipping_primary_secondary @primary_database='',@secondary_server='',@secondary_database ='secondarydbname'

3. If the secondary server and database are still active execute:
USE master
EXEC sp_delete_log_shipping_secondary_database @secondary_database=''
EXEC sp_delete_log_shipping_secondary_primary @primary_database='',@primary_server=''

4. Make sure it got deleted from the monitoring database

select * from msdb.dbo.log_shipping_monitor_primary

SQLServer 2005 - How to convert from int to binary

CREATE FUNCTION [dbo].[FU_CONVERT_INTtoBIN] (@pincoming_number int)
RETURNS varchar(200)
as
BEGIN
-- 08/31/09 Converts from integer to binary JairoH

DECLARE @vbin_number VARCHAR(200)
SET @vbin_number = ''

IF @pincoming_number <> 0
BEGIN
WHILE @pincoming_number <> 0
BEGIN
SET @vbin_number = SUBSTRING('0123456789', (@pincoming_number % 2) + 1, 1) + @vbin_number
SET @pincoming_number = @pincoming_number / 2
END
END
ELSE
BEGIN
SET @vbin_number = 0
END

RETURN @vbin_number

END

SQL Server 2005 - SQLServer Profiler - Event IDs

ID Description
-- -------------
0 Reserved
1 Reserved
2 Reserved
3 Reserved
4 Reserved
5 Reserved
6 Reserved
7 Reserved
8 Reserved
9 Reserved
10 RPC:Completed
11 RPC:Starting
12 SQL:BatchCompleted
13 SQL:BatchStarting
14 Login
15 Logout
16 Attention
17 ExistingConnection
18 ServiceControl
19 DTCTransaction
20 Login Failed
21 EventLog
22 ErrorLog
23 Lock:Released
24 Lock:Acquired
25 Lock:Deadlock
26 Lock:Cancel
27 Lock:Timeout
28 DOP Event
29 Reserved
30 Reserved
31 Reserved
32 Reserved
33 Exception
34 SP:CacheMiss
35 SP:CacheInsert
36 SP:CacheRemove
37 SP:Recompile
38 SP:CacheHit
39 SP:ExecContextHit
40 SQL:StmtStarting
41 SQL:StmtCompleted
42 SP:Starting
43 SP:Completed
44 SP:StmtStarting
45 SP:StmtCompleted
46 Object:Created
47 Object:Deleted
48 Reserved
49 Reserved
50 SQL Transaction
51 Scan:Started
52 Scan:Stopped
53 CursorOpen
54 Transaction Log
55 Hash Warning
56 Reserved
57 Reserved
58 Auto Update Stats
59 Lock:Deadlock Chain
60 Lock:Escalation
61 OLE DB Errors
62 Reserved
63 Reserved
64 Reserved
65 Reserved
66 Reserved
67 Execution Warnings
68 Execution Plan
69 Sort Warnings
70 CursorPrepare
71 Prepare SQL
72 Exec Prepared SQL
73 Unprepare SQL
74 CursorExecute
75 CursorRecompile
76 CursorImplicitConversion
77 CursorUnprepare
78 CursorClose
79 Missing Column Statistics
80 Missing Join Predicate
81 Server Memory Change
82 User Configurable 0
83 User Configurable 1
84 User Configurable 2
85 User Configurable 3
86 User Configurable 4
87 User Configurable 5
88 User Configurable 6
89 User Configurable 7
90 User Configurable 8
91 User Configurable 9
92 Data File Auto Grow
93 Log File Auto Grow
94 Data File Auto Shrink
95 Log File Auto Shrink
96 Show Plan Text
97 Show Plan ALL
98 Show Plan Statistics
99 Reserved
100 RPC Output Parameter
101 Reserved
102 Audit Statement GDR
103 Audit Object GDR
104 Audit Add/Drop Login
105 Audit Login GDR
106 Audit Login Change Property
107 Audit Login Change Password
108 Audit Add Login to Server Role
109 Audit Add DB User
110 Audit Add Member to DB
111 Audit Add/Drop Role
112 App Role Pass Change
113 Audit Statement Permission
114 Audit Object Permission
115 Audit Backup/Restore
116 Audit DBCC
117 Audit Change Audit
118 Audit Object Derived Permission