Category: Microsoft SQL Server

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.

8-1-2013 11-59-08 PM 8-1-2013 11-58-58 PM 8-1-2013 11-58-40 PM 8-2-2013 12-02-16 AM 8-2-2013 12-01-27 AM 8-1-2013 11-59-41 PM 8-2-2013 12-00-00 AM

 

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

8-2-2013 12-21-48 AM

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

Click here to learn more

“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.”

Importing Edgar Database

We will be showing how to import database data from Edgar, with SQL code as well as SSIS Import Wizard configuration settings.

“Since 1934, the SEC has required disclosure in forms and documents. In 1984, EDGAR began collecting electronic documents to help investors get information”

First import the latest master.idx from http://www.sec.gov/edgar/indices/fullindex.htm and edit the file as shown below.

1-24-2013 5-27-40 PM 1-24-2013 5-28-05 PM

Create a database called Edgar

Create a table called master1 using T-SQL code below

Right click on Edgar database,click Tasks, then Import dat and use settings below.

 

USE [Edgar]
GO

/****** Object: Table [dbo].[master_02102013] Script Date: 2/10/2013 1:41:59 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[master_02102013](
[MasterID] [int] IDENTITY(1,1) NOT NULL,
[CIK] [varchar](50) NULL,
[Company Name] [varchar](200) NULL,
[Form Type] [varchar](50) NULL,
[Date Filed] [varchar](50) NULL,
[Filename] [varchar](50) NULL,
CONSTRAINT [PK_master_02102013] PRIMARY KEY CLUSTERED
(
[MasterID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

1-24-2013 5-56-23 PM 1-24-2013 5-55-57 PM 1-24-2013 5-55-45 PM 1-24-2013 5-55-20 PM 1-24-2013 5-54-57 PM 1-24-2013 5-53-34 PM 1-24-2013 5-57-19 PM 1-24-2013 5-57-03 PM 1-24-2013 5-56-48 PM

Make sure Identity insert is checked off. Also make sure you select delete existing rows NOT append row data.

Example Search T-SQL Query after import is completed.

SELECT TOP 100000 [MasterID]
,[CIK]
,[Company Name]
,[Form Type]
,[Date Filed]
,[Filename]
FROM [Edgar].[dbo].[master]

Result

1-24-2013 9-07-07 PM

Coming soon – a script, to extract ftp filepaths from above results and possibly a script [in python] tp download from ftp.sec.gov all the files into appropriate columns in our database, which currently houses 53,398 rows of data.

————–

Scriot for downloading ONE file, from say data in first row of table created above. Save this as test.scr

anonymous
<your email address goes in here>
cd edgar/data/1000045/
ls *.txt
get 0001193125-13-046001.txt
quit

Script for a batchfile to execute above. Save as, for example: gosec.bat

cls
ftp -s:test.scr ftp.sec.gov

 

 

Microsoft® SQL Server 2012 Feature Pack

Click here for this useful set of tools.

The Microsoft® SQL Server® 2012 Feature Pack is a collection of stand-alone packages which provide additional value for Microsoft® SQL Server® 2012.  It includes the latest versions of:

  • Tool and components for Microsoft® SQL Server® 2012.
  • Add-on providers for Microsoft® SQL Server® 2012.

An important component of this feature pack is:

Microsoft® SQL Server® 2012 Native Client

          Microsoft SQL Server Native Client (SQL Server Native Client) is a single dynamic-link library (DLL) containing both the SQL OLE DB provider and SQL ODBC driver. It contains run-time support for applications using native-code APIs (ODBC, OLE DB and ADO) to connect to Microsoft SQL Server 2005, 2008, 2008 R2, and SQL Server 2012. SQL Server Native Client should be used to create new applications or enhance existing applications that need to take advantage of new SQL Server 2012 features. This redistributable installer for SQL Server Native Client installs the client components needed during run time to take advantage of new SQL Server code name ‘Denali’ features, and optionally installs the header files needed to develop an application that uses the SQL Server Native Client API.