Script level upgrade for database master failed because upgrade step SSIS_hotfix_install.sql

After update windows including SQL 2012 SP2 unable to start SQL service/instance

We have SQL 2012 Ent 64 bit SP1

Window 2012 R2 Datacenter

After update windows including SQL 2012 SP2 unable to start SQL service/instance and following event log

Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.

Script level upgrade for database 'master' failed because upgrade step 'SSIS_hotfix_install.sql' encountered error 945, state 2, severity 25. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

Solution:

Start SQL Server service with Trace Flag 902: 

Right click on the command prompt and run as Administrator

When named instance

>Net Start MSSQL$InstanceName /T902

Or when default instance:

>Net Start MSSQLSEVER /T902

Open SQL Server Management Studio, go to Availability Group and remove SSISDB from the availability databases

Stop SQL Server services:  

>Net Stop MSSQL$InstanceName or >Net Stop MSSQLSERVER when default instance

Start SQL server service from SQL Server configuration manager

Add SSISDB back to Availability Group 

SSRS not sorting correctly

SSRS not sorting correctly.

When you have issues and try modifying group properties and tablix sort property, and still the report doesn't take the sorting you select on the groups or the query.  You just need to right click on the report name on the Solution Explorer window and select View Code. 

This option will show you the xml version of the report where you can find the property SortExpressions

This entry will show up multiple times you just need to find the one that the report is using and change it to the field you need, save the xml version then close it to go back to the Designer mode.

I found the solution on the following blog which I recommend in case you're still having issues.

http://stackoverflow.com/questions/29515934/ssrs-not-sorting-correctly

Thanks.

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

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