Monday, August 31, 2009

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

Monday, August 3, 2009

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

SQL Server 2005 - SQLServer Profiler - Cannot import system monitor counter log

When running a trace and want to import corresponding performance log data in the profiler. The option is disabled in the profiler at File -> Import performance Data

1. Start System Monitor. Saving it to a TextFile (Comma delimited)
2. Start SQL Server Profiler with add both StartTime and EndTime data columns.
3. Stop both.
4. Open the trace file on Profiler
5. Import Performance Data