Uneven Technology: January 2015

Wednesday, January 28, 2015

MSSQL - Permission Issue on OPENROWSET

Got error when running OPENROWSET to read data from external Excel file:

SELECT *
FROM OPENROWSET ('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=C:\TEMP\test.xlsx;HDR=YES;IMEX=1',
'SELECT * FROM [Excel$]') FC

Msg 7303, Level 16, State 1, Server MyServer, Line 1

Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".

SOLUTION 1:

As it turns out when running the sql with any domain account the sql will need to have access to the folder “C:\Users\someaccount\AppData\Local\Temp” where the someaccount is the logon account used by SQL service. Giving full control for users who need to run the sql to this folder on the SQL server solved the issue. An easy way to debug which folder needs permission is to use PROCMON.EXE to monitor the file got rejected.

SOLUTION 2:

SET Temp=C:\Temp and SET Tmp=C:\Temp; this cause all user to use the given folder for temp files when running reports.

http://blogs.msdn.com/b/spike/archive/2008/07/23/ole-db-provider-microsoft-jet-oledb-4-0-for-linked-server-null-returned-message-unspecified-error.aspx

SOLUTION 3:

DBCC FREESYSTEMCACHE ('ALL');

DBCC FREESESSIONCACHE

SOLUTION 4:

http://www.johnsoer.com/blog/?p=538

Tuesday, January 13, 2015

MSSQL - View SQL Server Execution History

Last runs SQL scripts from execution history. This script can only work without server restart

USE AdventureWorks2012

SELECT
execquery.last_execution_time AS [Date Time]
,execsql.text AS [Script]
FROM sys.dm_exec_query_stats AS execquery
CROSS APPLY sys.dm_exec_sql_text(execquery.sql_handle) AS execsql
ORDER BY execquery.last_execution_time DESC

Friday, January 9, 2015

MSSQL - Sotred Procedure Template

Use this template to create Stored Procedure:

-- EXEC USP_Test

USE DBName
GO

SET QUOTED_IDENTIFIER OFF
GO

IF OBJECT_ID('dbo.USP_Test') IS NULL EXEC('CREATE PROCEDURE dbo.USP_Test AS DUMMY:')
GO

GRANT EXECUTE ON USP_Test TO [Domain\UserName]
GO

ALTER PROCEDURE USP_Test
AS

MSSQL - Shrink All DBs

-- Don’t do this unless you have to.
EXEC sp_MSForEachDB 'DBCC SHRINKDATABASE (''?'' , 0)'

MSSQL - Shrink TempDB

--DON'T DO THIS UNLESS WITH GOOD REASON

-- check db size
SELECT name, size FROM sys.master_files WHERE database_id = DB_ID(N'tempdb');

--How to shrink tempdb using DBCC
DBCC SHRINKDATABASE(tempdb, 10);
DBCC SHRINKFILE(tempdev, 5);

--How to shrink tempdb using ALTER DATABASE
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, SIZE=100Mb);
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, SIZE=100Mb);
GO

MSSQL - List Running SQL Process

The script below lists all running sql and its session_id. Use session_id to kill the process.

-- List running processes
DECLARE @DatabaseName VARCHAR(50) = DB_NAME()
SELECT * FROM MASTER.dbo.SysProcesses WHERE DBId = DB_ID(@DatabaseName)

-- List SQL for running processes

SELECT

sqltext.TEXT

    ,req.session_id
    ,req.status
    ,req.command
    ,req.cpu_time
    ,req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

KILL [session_id]

MSSQL - Read and Sort Column Names

USE AdventureWorks2012

DECLARE @TBLName nvarchar(128)
SET @TBLName = N'Person'

SELECT name, object_id FROM sys.tables WHERE name = @TBLName

SELECT name, column_id, object_id
FROM sys.columns
WHERE object_id IN (SELECT object_id FROM sys.tables WHERE name = @TBLName)
ORDER BY name

MSSQL - Run SQL and Email Result as HTML

USE AdventureWorks2012

DECLARE @xml NVARCHAR(MAX), @body NVARCHAR(MAX)
SET @xml = CAST((SELECT
                     ISNULL(PersonType, '') 'td',''
                    ,ISNULL(Title, '') 'td',''
                    ,ISNULL(FirstName, '') 'td',''
                    ,ISNULL(LastName, '') 'td'
                FROM Person.Person
                FOR XML PATH('tr'), ELEMENTS) AS NVARCHAR(MAX))

IF @xml IS NOT NULL
BEGIN
    SET @body ='<html><body><H3>Header</H3>
                <table border=1>
                    <tr>
                        <th>Type</th>
                        <th>Title</th>
                        <th>FirstName</th>
                        <th>LastName</th>
                    </tr>'
    SET @body = @body + @xml + '</table><br><a href="http://yahoo.com">This is a link</a></body></html>'

    EXEC DataWarehouse.dbo.sp_SQLNotify 'FromEmail', 'ToEmail', 'Subject', @body, ''
END
　

SSRS - Windows Command to Run Report

To run a report and save result to Excel

rs -i "C:/Temp/Report.rss"
    -s http://localhost/reportserver
    -e Exec2005
    -v ReportPath="/FolderName/ReportName"
    -v OutputFileName="OutputFilePath"
    -v Parameter1="' + @Parameter

MSSQL - Addhoc Select from An Excel File

To read data from external Excel file:

SELECT *
FROM OPENROWSET ('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=C:\Temp\SomeExcelFile.xlsx;HDR=YES;IMEX=1',
'SELECT * FROM [Sheet1$]')

MSSQL - Using Excel File as A LinkedServer

Add Excel file as a LinkedServer:

/****** Object: LinkedServer [EXCELFILE] Script Date: 9/9/2013 11:31:34 AM ******/
EXEC master.dbo.sp_dropserver @server=N'EXCELFILE', @droplogins='UserName'
GO

EXEC master.dbo.sp_addlinkedserver @server = N'EXCELFILE', @srvproduct=N'Excel', @provider=N'Microsoft.ACE.OLEDB.12.0', @datasrc=N'C:\TEMP\SomeExcelFile.xlsx', @provstr=N'Excel 12.0'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'EXCELFILE', @useself=N'False', @locallogin=NULL, @rmtuser=NULL, @rmtpassword=NULL
GO

INSERT INTO WebEx.dbo.WebEx_Host SELECT * FROM [EXCELFILE]...[Sheet1$]
GO