Geeks With Blogs

The Wrecking Bawl Destructuring query language, one keyword at a time. SQL Server
importing ssis flat files in redshift using copy
After trying a million combinations, I finally figured out how to export data in SSIS using an OLE DB source (SQL Server) and a flat file destination. In the end all I really should have done was use "ENCODING UTF16" in the COPY command in Redshift.  None of the settings I changed in SSIS actually helped, aside from making sure the Unicode box was checked in the General tab of the Flat File Destination settings.

Posted On Monday, December 18, 2017 10:59 AM

creating a t-sql script to enable query store and auto tuning for each user database
from sys.databases 
where name not in ('master','model','msdb','tempdb')

Posted On Tuesday, December 12, 2017 5:02 AM

sql server won't start as clustered resource after service pack upgrade
I updated my production SQL Server 2012 cluster from SP1 to SP3 CU1 last night and had to spend an hour trying to figure out why the SQL Server Engine service wouldn't start for one of my two instances. Weirdly the other instance worked fine after the upgrade. The error logs were no help at all. It was only by the magic of the gods that I happened upon a registry entry that still had the old patch level in it.HKEY_LOCAL_MACHINE\SOFTW... SQL Server\<instance>\Clu... ......

Posted On Tuesday, March 1, 2016 10:26 AM

ssis 2012 timeouts
I started getting timeouts from SSIS 2012 using the SSISDB and I couldn't make any sense of them, they seemed random. When things were bad I couldn't even expand the Integration Services Catalogs node in SSMS. Just by sheer luck I figured out the problem: too much data was being logged in SSISDB and the data file was up to 180 GB, with the log file at 500 GB. I switched it to simple mode and shrank the log file, then changed the retention period to 90 days instead of 365. Now I need to see what else ......

Posted On Friday, May 30, 2014 6:42 AM

ssis package slow after upgrade to 2012
I had an SSIS 2008 package that copied new/changed data from the production database to an archive database every 2 minutes using Change Tracking, a staging database, etc. The package was stored in the SSIS repository in the SQL Server 2008 instance where the archive database was stored. I upgraded the package to SSIS 2012 and deployed it to the SQL Server 2012 instance where the production database was stored. The package took around 20 seconds to run on the old server but over 7 minutes to run ......

Posted On Friday, March 7, 2014 10:53 AM

generating complete insert statements for sql server table data
Ever have to create insert statements for a SQL Server table but can't figure out how to do so without hitting a character limit?  The only way I found (with rows that are over 50,000 characters) was to use sqlcmd with the -y parameter set to 0.

Posted On Tuesday, September 17, 2013 4:54 AM

generating temporary table for left join with date range
Want to create a SQL Server query using a table created at runtime that just has month numbers and year numbers? I did, so here's how I did it:DECLARE @StartDate datetime = '9/1/12', @EndDate datetime = '8/1/13'CREATE TABLE #cal (TheYear int, TheMonth int)INSERT #calSELECT YEAR(dateadd(month, number, @StartDate)), MONTH(dateadd(month, number, @StartDate))FROM (SELECT DISTINCT number FROM master.dbo.spt_values WHERE name IS NULL) nWHERE DATEADD(month, number, @StartDate) < @EndDateYou can use a ......

Posted On Wednesday, August 7, 2013 11:19 AM

ssis error deploying package
I was getting the error below when trying to deploy to my SQL 2008 R2 cluster, which was odd since I had never had any trouble before. The problem ended up being that when I double clicked on the SSISDeploymentManifest file my computer was using the SQL 2012 version of the deployment utility because I had recently installed it. Once I used the 2008 utility everything worked fine.Exception details: Microsoft.SqlServer.Dts.Run... Storing or modifying packages in SQL Server requires ......

Posted On Monday, May 20, 2013 11:13 AM

index stats histograms for puzzling sql server query plan
Statistics for INDEX 'IX_DWOrderStatus_DWDateEnd... Updated Rows Rows Sampled Steps Density Average Key Length String ......

Posted On Friday, April 12, 2013 12:00 PM

tfs backup console - adminpthelper.exe has stopped working
I got this message from the 2012 version of the console after installing the Power Tools found here:

The problem was that I was not a sysadmin in the SQL instance, once I fixed that the error went away.

Posted On Friday, February 15, 2013 12:04 PM

Copyright © Alex Bransky | Powered by: