Geeks With Blogs

The Wrecking Bawl Destructuring query language, one keyword at a time. all tech stuff SQL Server, C#, .Net, etc.
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

postgres vip pointing to two servers
ERROR: cannot execute DELETE in a read-only transactionI kept getting errors like that but I didn't think anything had changed with my company's PostgreSQL installation. It turned out that there was a VIP that got applied to a secondary server (slave perhaps, made it a duplicate VIP) so nine times out of ten I was hitting the wrong server. I hope this helps somebody in the future since I found no help on Google ......

Posted On Monday, April 14, 2014 4:18 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

droid wipe successfully speeds up phone
My Droid Razr was driving me crazy with how slow it was to respond most of the time and how quickly the battery would get drained.  I finally gave in and did a hard reset on it, which was a little annoying because I thought my apps would get restored and they didn't, but I'm very glad I did it.  My phone can finally make it most of the day without being charged and is as responsive as the day I got it.

Posted On Tuesday, January 7, 2014 12:15 PM

generic error in access 2013
Today I got an error in Access 2013 saying "Cannot open database ''.  It maybe not be a database that your application recognizes, or the file may be corrupt."

After doing some digging I figured out that Access 2013 has a database limit of around 2 GB, so when I was trying to import data that would push me past that limit, instead of giving me a useful error it gave me the message above.  Thank you Microsoft.

Posted On Friday, September 27, 2013 11:03 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

Copyright © Alex Bransky | Powered by: