|
 |
1 - Shows show top 20 queries by logical reads...
2 - How to import .xlsm files into SQL using SSIS
Use an OLE DB Source rather than Excel
New connection etc
Select Microsoft 12.0 Access database engine ole db provider
Hit data links
Hit select 'all' in data link properties
Enter location of file in source
Enter "Excel 12.0 Macro;HDR=YES" in extended properties
New connection etc
Select Microsoft 12.0 Access database engine ole db provider
Hit data links
Hit select 'all' in data link properties
Enter location of file in source
Enter "Excel 12.0 Macro;HDR=YES" in extended properties
3 - Running a SQL Job using a .bat file
Very useful article on SQL Server Central / TekTips
4 - Running an SSIS Job using VB.Net
I wrote this a while ago but still very useful, client machine does not need SQL/VB of any shape or form installed on their remote machine. You need to set some permissions for users on the SQL Server MSDB Database (In the Object Explorer expand Databases > System Databases > msdb > Security > select (or create user) > General tab > add the three SQLAgent options under 'Role Members')
| launch SSIS Job remotely using a VB button |
5 - File of UK/US postcodes, city with latitude and longitude
Import into SQL Server and use google API for maps in QlikView
| Latitude and longitude lookup |
6 - Working with System.Runtime.InteropServices.COMException
Got this message when running a script task which opens, reads and closes data from an Excel file. Easy fix but took a long time to discover! "System.Runtime.InteropServices.COMException (0x800A03EC): Microsoft Office Excel cannot access the file '\\eclipse\root\templates\accounts\EPAYMENT.xls'. There are several possible reasons: ? The file name or path does not exist. ? The file is being used by another program. ? The workbook you are trying to save has the same name as a currently open workbook" Thanks to H Ogawa for the tip!
This solution is ...
・Windows 2008 Server x64 Please make this folder: C:\Windows\SysWOW64\config\systemprofile\Desktop
・Windows 2008 Server x86 Please make this folder: C:\Windows\System32\config\systemprofile\Desktop
This solution is ...
・Windows 2008 Server x64 Please make this folder: C:\Windows\SysWOW64\config\systemprofile\Desktop
・Windows 2008 Server x86 Please make this folder: C:\Windows\System32\config\systemprofile\Desktop
7 - Three gotchas to avoid for "failure tasks" in SSIS control flow
1 – The child containers property needs to be set to: “FailParentOnFailure = True”
2 – The parent container property needs to be set to: “MaximumErrorCount = 1”
3 – Failure pipe needs to be set to: “logical OR” – (dashed instead of solid)
2 – The parent container property needs to be set to: “MaximumErrorCount = 1”
3 – Failure pipe needs to be set to: “logical OR” – (dashed instead of solid)
8 - Adding ID column to import in SSIS
This video is a gem... shows exactly how to set up an extra column (for example ID column) when importing an Excel file into a SQL Server table. Downloaded from Simon Abin's blog, the orginal file can be downloaded here (or just click the file below).
This video is a gem... shows exactly how to set up an extra column (for example ID column) when importing an Excel file into a SQL Server table. Downloaded from Simon Abin's blog, the orginal file can be downloaded here (or just click the file below).
| SSIS Row number script.wmv |
9 - SQL Job Agent Configuration
SSIS / DTS package works when executed but fails when scheduled in Job Agent? This link and microsoft video will guide you through the painful process!
SSIS / DTS package works when executed but fails when scheduled in Job Agent? This link and microsoft video will guide you through the painful process!
| SQL Server Agent Configure.wmv |
10 - Summary & diagrams of the various SQL Server joins & terms
11 - Set up email notification of SSIS package errors
12 - Great SP to search a database for a value in any table
run the query to create the SP then run like this dbo.searchalltables 'hello' (check "use db" line at top!)
| searchalltables.sql |
13 - Creating extra records based on multi-values in one field
This was a quick fix to solve a very long manual process.
This solution will help if a field has multi-values (ie for some records division = 'A,B,C' and for
some others division = 'B,C,D,E') and you want to create a new record for each division
This solution will help if a field has multi-values (ie for some records division = 'A,B,C' and for
some others division = 'B,C,D,E') and you want to create a new record for each division
14 - Kill all sessions connections
USE master;
GO
ALTER DATABASE [YourDBName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE [YourDBName] SET MULTI_USER;
GO
Thanks to SQL Authority
GO
ALTER DATABASE [YourDBName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE [YourDBName] SET MULTI_USER;
GO
Thanks to SQL Authority
15 - Test and drop a table before creating it to avoid debug tangles
Temp table
IF EXISTS ( SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID('tempdb..#temp'))
BEGIN DROP TABLE #temp END
IF EXISTS ( SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID('tempdb..#temp'))
BEGIN DROP TABLE #temp END
16 - Trace and track
Add a trace to a server to track logins / activity:
Set up the trace: program files > SQL Server > Performance Tools > SQL Server Profiler > File >
New Trace > Name: MyTrace > tick save to table > Events Selection > Tick show all columns >
select DatabaseName > tick relevant events > Time period < run
Here is my on-going project for analysing traces.
Set up the trace: program files > SQL Server > Performance Tools > SQL Server Profiler > File >
New Trace > Name: MyTrace > tick save to table > Events Selection > Tick show all columns >
select DatabaseName > tick relevant events > Time period < run
Here is my on-going project for analysing traces.
17 - Query to extract details of all .mdf and .ldf files on a server - click me
Thanks to Ken Simmons
Thanks to Ken Simmons
18 - Easy edits! - the manual way
An amazingly simple way to edit specific rows using the SSMS UI.
1 - Right click on the table in the tree and select 'Edit top 200 rows'
2 - Right click on the data and select pane > SQL
3 - SQL code will appear in the top panel
4 - Add where clauses as required to limit down your data
5 - Edit manually
An amazingly simple way to edit specific rows using the SSMS UI.
1 - Right click on the table in the tree and select 'Edit top 200 rows'
2 - Right click on the data and select pane > SQL
3 - SQL code will appear in the top panel
4 - Add where clauses as required to limit down your data
5 - Edit manually
19 - Trace me! - click me
Setting up a trace by TSQL / SP to avoid leaving the UI running
How to load a trace data file into an SQL table
How to set a trace to auto restart on server restart
Thanks to Microsoft
Setting up a trace by TSQL / SP to avoid leaving the UI running
How to load a trace data file into an SQL table
How to set a trace to auto restart on server restart
Thanks to Microsoft
20 - Easy field list
Simplest tip ever! Click and drag the 'columns' folder of a table onto a query window for a quick and easy list of tables, comma separated.
Simplest tip ever! Click and drag the 'columns' folder of a table onto a query window for a quick and easy list of tables, comma separated.
21 - Forgotten 'sa' password
1 - Check which services use this account.
Right click on server > Properties > Security > Login auditing > Both failed and successful logins
1 - Check which services use this account.
Right click on server > Properties > Security > Login auditing > Both failed and successful logins
22 - LDAP query to extract users from Active Directory
Uses a three layer loop on username to avoid the 1000 row limit
(replace the aaaaaa and bbbbbb markers with the domain name)
Uses a three layer loop on username to avoid the 1000 row limit
(replace the aaaaaa and bbbbbb markers with the domain name)
| ad_extract.sql |
