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

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

Visual Studio 2005 - Package Load Failure

Microsoft Visual Studio 2005

Package Load Failure

Package 'Windows Forms Designer Resources Package' has failed to load properly ( GUID = {7B5D447B-0B12-41EA-A84E-C822034422D4} ). Please contact package vendor for assistance. Application restart is recommended, due to possible environment corruption. Would you like to disable loading this package in the future? You may use 'devenv /resetskippkgs' to re-enable package loading.

run devenv /resetsettings in Visual Studio Command Prompt to reset Visaul Studio.

SQL Server 2005 - Unable to start mail session (reason: No mail profile defined)

1. Go to SQL server Management Studio
2. Right Click on SQL Server Agent
3. Click on Properties
4. Click on Alert System
5. Check the Enable mail profile under the Mail session section
6. Restart SQL Server Agent