Solution:
- Click on the VM [Virtual Machine] in Hyper-V manager
- Click on the action menu, select insert integration disk
- log into the VM
- from the DVD disk you see for Integration manager, run the setup.exe
- You will need to reboot your VM.
See below:
SQL Server 2014 CTP1 Setup
One important thing to note, if you choose to install this CTP is that it will not install on a host with other SQL server instances as shown below, after a check – SQL Server System Configuration Check.
SQL Server – List all SQL Login accounts
Short script to list all SQL login accounts [non windows NT accounts]
select
name
from
master..syslogins
where
isntname = 0
and
name not like ‘##%’
order by
loginname;
SQL Server Deadlock Detection Script
use master
go
select
cntr_value
from
sysperfinfo
where
counter_name = ‘Number of Deadlocks/sec’
order by
cntr_value
Related error log messages might show up like that shown below:
Source spid29s
Message Failure to send an event notification instance of type ‘DEADLOCK_GRAPH’ on conversation handle ‘{E96F2738-83CF-E111-8D19-00237D585B2C}’. Error Code = ‘8429’.
Microsoft SSIS 2012 Data Import – WA State Corporation Data
Below are screenshots of SSIS – Microsoft SQL Server Integration Services 2012 importing data from Washington State Secretary of State Corporations public data file. Click here for the source link.
Below is the target table creation T-SQL code
USE [CorpWA]
GO
/****** Object: Table [dbo].[Corporations] Script Date: 8/2/2013 12:16:57 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Corporations](
[UBI] [varchar](50) NULL,
[BusinessName] [varchar](50) NULL,
[Category] [varchar](50) NULL,
[StateOfIncorporation] [varchar](50) NULL,
[DateOfIncorporation] [varchar](50) NULL,
[ExpirationDate] [varchar](50) NULL,
[Duration] [varchar](50) NULL,
[DissolutionDate] [varchar](50) NULL,
[RecordStatus] [varchar](50) NULL,
[Type] [varchar](50) NULL,
[RegisteredAgentName] [varchar](50) NULL,
[RegisteredAgentAddress] [varchar](50) NULL,
[RegisteredAgentCity] [varchar](50) NULL,
[RegisteredAgentState] [varchar](50) NULL,
[RegisteredAgentZip] [varchar](50) NULL,
[AlternateAddress] [varchar](50) NULL,
[AlternateCity] [varchar](50) NULL,
[AlternateState] [varchar](50) NULL,
[AlternateZip] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [CorpWA]
GO
/****** Object: Table [dbo].[GoverningPersons] Script Date: 8/2/2013 12:20:44 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[GoverningPersons](
[UBI] [varchar](50) NULL,
[Title] [varchar](100) NULL,
[FirstName] [varchar](100) NULL,
[MiddleName] [varchar](100) NULL,
[LastName] [varchar](100) NULL,
[Address] [varchar](100) NULL,
[City] [varchar](50) NULL,
[State] [varchar](50) NULL,
[Zip] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
Results – Table Report
FEP 2010: Basic Understanding of SQL Server Integration Services (SSIS) Package/SQL Job Agent
Detailed Video on SSIS with respect to Microsoft Forefront Endpoint Protection 2010 anti-virus program:
Maximum Request Length Exceeded when Deploying a Model to SSRS
Error “Maximum request length exceeded” in Reporting Services
This error came up while trying to upload a 3.9Mb SSRS report to a new SQL 2008 R2 reporting server.
Solution:
open the web.config file found in:
C:Program FilesMicrosoft SQL ServerMSRS10_50.#####Reporting ServicesReportServer
#### stands for your instance name
Modify line 40 or the line with below:
to:
Restart just the reporting service. There is no need to restart the database service or reboot the server box.
You should now be able to upload reports up to 15000kb ie 15mb in this case. You can modify the value as needed.
Restore msdb ssytem database from backup
Quick steps to restore the SQL Server system database – msdb from a valid backup file, especially useful if you run SSIS packages stored in the msdb database and SQL server agent jobs. Doing this will bring back all SSIS packages as well as existing job schedules.
– Set msdb database to restricted access
USE [master]
GO
ALTER DATABASE [msdb] SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
GO
– shut down the SQL server agent service for the SQL instance you are running
– restore the msdb database from a good backup file
– restart the SQL server agent service
SQL Server 2014
“Microsoft SQL Server 2014 builds on the mission-critical capabilities delivered in the prior release by providing breakthrough performance, availability and manageability for your mission critical applications. SQL Server 2014 delivers new in-memory capabilities built into the core database for OLTP and data warehousing, which complement our existing in-memory data warehousing and BI capabilities for the most comprehensive in-memory database solution in the market.”
Truncate Requires Control Rights
Below is an output from a sql login session, the sql login account has select permissions, and attempts to truncate [quickly delete rows without logging each transaction] the table which has over 10,000 rows of data.
SQL Code
truncate table [AdventureWorks2012].[dbo].[MyList]
Result ==========
Msg 1088, Level 16, State 7, Line 1 Cannot find the object “MyList” because it does not exist or you do not have permissions.
Solution
use [AdventureWorks2012]
GO
GRANT CONTROL ON [dbo].[MyList] TO [myuid]
GO
Result
In the above example, myuid is the name of the sql login account