Geeks With Blogs

News
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
select 'ALTER DATABASE [' + name + '] SET QUERY_STORE = ON ALTER DATABASE [' + name + '] SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON )'
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:
http://visualstudiogallery.msdn.microsoft.com/b1ef7eb2-e084-4cb8-9bc7-06c3bad9148f

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

ssis: data flow path editor
Ever wonder why when you click on the green line in SSIS the Data Flow Path Editor doesn't actually let you edit anything?  I did.  Then I realized I needed to make the changes I wanted in the source task by right-clicking it and selecting Show Advanced Editor.

Posted On Friday, December 7, 2012 12:34 PM

ssrs: the report execution has expired or cannot be found
Today I got an exception in a report using SQL Server Reporting Services 2008 R2, but only when attempting to go to the last page of a large report: The report execution sgjahs45wg5vkmi05lq4zaee has expired or cannot be found.;Digging into the logs I found this:library!ReportServer_0... e ERROR: Throwing Microsoft.ReportingServices... , An error occurred within the report server database. This may be due to a connection failure, ......

Posted On Thursday, December 6, 2012 10:06 AM

export blobs from SQL Server table into their respective files
In case anyone is storing their files as blobs in SQL Server and is interested in the best way to export them, check this out:

Posted On Thursday, May 3, 2012 7:08 AM

SQL Server... help!
I have two Great Plains databases, let's call them DB1A and DB1B, that are schematically identical and the data is 99% identical but DB1B has a tiny bit more data. I'm trying to optimize a very complex query that uses views nested in views and is way too long to post all of here. The query was averaging 1 min 43 sec before I started. I noticed that all of the tables involved were heaps (Microsoft's fault, not mine) so I started converting them to clustered one by one, checking the plans and parallelism ......

Posted On Wednesday, February 29, 2012 10:43 AM

indexes and deadlocks

I recently discovered that creating indexes in one of my production databases was causing deadlocks.  My problem was that I wasn't using ONLINE=ON when I was creating the index.  Check out the BOL for more information.

Posted On Friday, June 24, 2011 6:27 AM

removing an ssrs instance from a scale-out deployment
If you're like me you had at one time connected one of your Reporting Services instances to a report server database that was already in use by another instance. This allows the instance to show up in the Scale-out Deployment section of the Reporting Services Configuration Manager. My problem was that the server that got joined to the original server was no longer available as it had been repurposed, and when I clicked Remove Server to remove it from my scale-out it would fail because it couldn't ......

Posted On Thursday, March 3, 2011 7:49 AM

migrating sharepoint databases
If you're wondering how to migrate your SharePoint databases to a new server, this Microsoft article is actually pretty useful, though still overly complex like most of their other articles. http://technet.microsoft.co... The one thing I would change is that they seem to recommend installing SQL Server Configuration Manager on web servers, when all that was needed in my case was to add an entry to the hosts file on the SharePoint web server that used the IP address of the ......

Posted On Wednesday, February 16, 2011 10:55 AM

workspaces in sql mgmt studio
How is it that you still can't save workspaces in SQL Server Management Studio as of version 2008?  It seems like such a simple thing to implement.  Quite often I create a bunch of different queries during a short period that are specific to a task I'm working on and that won't be needed once the task is completed, and rather than having to save each one it would be nice if I could just save a workspace file that knows which queries were open.

Posted On Wednesday, July 1, 2009 10:46 AM

drop and recreate sql server indexes
I have a real-time reporting server with a database which is basically a copy of my production database, but it's populated using transactional replication and replication only copies the primary keys over, i.e. none of the secondary indexes are included. So what I do is every time I need to recreate replication--which is every deployment with schema changes--I script out DROPs and CREATEs for all the reporting indexes I've created and then run the script after replication has been recreated. The ......

Posted On Wednesday, January 4, 2012 9:22 AM

annoying SSRS bug fix for building

If you're like me you've been using SSRS 2008 for a long time with TFS and have constantly had to delete the bin folder in order for builds to work.  This has irritated me to no end.  Well today is my lucky day!  There is a hotfix that actually fixes the bug!    Go here:  http://support.microsoft.com/kb/2438347

Posted On Thursday, June 30, 2011 6:19 AM

attaching adventureworks2008 on full version of sql server
For anybody that bought the SQL Server 2008 Self-Paced Training book for 70-433, if you are running a full version of SQL Server 2008 rather than the express edition, you will have problems attaching the AdventureWorks2008 database (file activation error). The trick is to use a SQL command, like so: USE [master] GO CREATE DATABASE [AdventureWorks2008] ON ( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQ... ), ( FILENAME = N'C:\Program Files\Microsoft ......

Posted On Tuesday, June 9, 2009 12:39 PM

tinyint surprise
I just discovered something rather surprising. If you return a tinyint from a stored procedure as part of a dataset, and bind that dataset to a dataview, and then do something like this: <asp:Label runat="server" id="statusLabel" Text='<%# GetStatusText( (int)DataBinder.Eval(Contai... "DataItem.status")) %>'> </asp:Label> You will get an error about an invalid cast. If the stored procedure returns an int instead of tinyint, it works just fine. How can ASP.Net 3.5 not be able to ......

Posted On Tuesday, February 10, 2009 1:05 PM

activerecord and executereader exceptions
About once a day I was getting the following error from an ASP.Net web page that uses ActiveRecord: Exception Details: System.Data.SqlClient.SqlEx... A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.) I would subsequently get this error upon refreshing the page: Exception Details: System.InvalidOperationExce... ExecuteReader requires an open and available Connection. ......

Posted On Wednesday, May 28, 2008 8:46 AM

ssas: define reference and materialize

Apparently you have to watch out when you select Materialize in a referenced dimension in SSAS 2005.  I just ran into a situation where data was getting duplicated for no good reason, and unchecking Materialize fixed it.

http://www.biblogs.com/2006/12/23/materialize-option-in-a-reference-dimension/

Posted On Thursday, April 10, 2008 10:25 AM

granularity error in SSAS MDX drillthrough query
I was trying to create an MDX drillthrough this morning in my SSAS 2005 cube, and I got this error: Errors from the SQL query module: The attribute 'Applicant Name' in the dimension 'Dim Candidate' is outside the granularity of the measure group 'Fact Requisitions' and cannot be returned as a column. I only got one result from Google for that error message and it wasn't helpful at all. After trying a million different things with partitions and actions and whatnot, I finally figured out the problem: ......

Posted On Wednesday, April 9, 2008 10:44 AM

adding a fact table to a SSAS 2005 cube
So you're trying to add a fact table to a cube that you've already created, but you can't figure out how to do so without recreating the cube from scratch? Here's what you do: In the Measures pane in the Cube Structure tab, right-click and select New Measure Group. Then select the fact table, and voila! (This is assuming you've already put the fact table in the data source view.) addendum: Looks like the standard way to add a dimension to the cube is to right-click on the cube name in the Dimensions ......

Posted On Monday, February 11, 2008 9:28 AM

SSAS cube: "attribute key not found" error
I just spent over an hour trying to figure out why my data tables were correct but I was getting an error about an attribute key not being found (i.e. an unlinkable foreign key).   I finally figured out that processing one dimension at a time before processing the cube can get fix whatever is out of sync.  [Right-click on the dimension in Solution Explorer to process it separately.]

Posted On Wednesday, February 6, 2008 11:05 AM

SQL Server 2005 splash screen
Is the SQL Server 2005 splash screen supposed to look like Arkanoid or like Tetris?

Posted On Friday, January 25, 2008 12:38 PM

fastest way to show SQL data in a web page
I'm curious what people think is that fastest way to show SQL Server data in a web page. Assuming you create the virtual web in IIS yourself, I'm hoping somebody knows of something that lets you create an xml file with connection settings and a query file, then the product automagically puts the data in a grid on the web page. Granted once you're used to Visual Studio it doesn't take terribly long to do such a thing, but I'd still like something faster, maybe something that can automagically create ......

Posted On Wednesday, November 21, 2007 11:45 AM

how do you handle huge databases?
I'm used to working with SQL Server databases that are somewhat small (10 GB or less, usually a lot less), so I'm curious how people deal with huge databases, like those that hold Amazon's transactions or whatever.  Do you have to have the latest and greatest hardware running with clustered servers?  Do you have to do a lot of database partitioning?  How do you handle upgrades?  Do you do then in the middle of the night so that you can take the db down briefly?

Posted On Wednesday, August 22, 2007 4:55 AM

ALTER TABLE difference in SQL 2005
I think I may have found an undocumented difference between SQL Server 2000 and SQL Server 2005. When you attempt to add a non-null column without a default to an empty table in SQL Server 2000, for some stupid reason it won't let you if you use ALTER TABLE ADD [column]. You are forced to drop and recreate the table with the new column added to the CREATE TABLE statement. This appears to be different than SQL Server 2005, which wisely does allow such a command to execute without error. I haven't ......

Posted On Tuesday, August 7, 2007 5:23 AM

XML parsing error: An invalid character was found in text content.
So I was writing a script to pull out some old xml data, and I happened upon this error: XML parsing error: An invalid character was found in text content. It probably would have taken me centuries to figure out what character it didn't like, so I tried concatenating the following line to the front of the xml column in the SELECT statement I used to populate the cursor for openxml, and voila, no more error! I guess bumbles really do bounce. '<?xml version=''1.0'' encoding=''iso-8859-1''?>' ......

Posted On Friday, June 29, 2007 11:26 AM

last modified date for stored procedure

I can't believe I just discovered the last_altered column in information_schema.routines in SQL Server 2005, all this time I thought there was no way to get the last modified date for a stored procedure.  Is there a catch?

Is Christmas really boring for the 7% of males who are red-green colorblind?

 

Posted On Monday, June 11, 2007 11:42 AM

SQL: OPENXML and case sensitivity
If you don't want to waste a lot of time figuring out why the results of your OPENXML query are all NULL, never forget that it's case sensitive.  I'm sure it's already obvious to those who use XML often and are acutely aware that it's case sensitive, but if you spend most of your time doing SQL you tend to forget about case sensitivity.

Posted On Thursday, June 7, 2007 8:43 AM

SQL Profiler bug
I discovered the most fascinating and annoying bug in version 9.00.1399.06 (the RTM version) of SQL Profiler 2005 today. When I would execute a stored procedure from ASP.Net, the date parameters shown in the Profiler window would always have two single quotes on both sides, e.g. ''2/2/2002 22:00:00'' (yes, those are single quotes, not double) This of course returns an error if you copy the statement and try to run it in Query Analyzer. I was baffled for hours. How could this be happening? And why ......

Posted On Thursday, May 31, 2007 11:31 AM

GETUTCDATE()
I often forget about GETUTCDATE() in T-SQL.  Not sure why.  It's very useful.

Posted On Wednesday, May 2, 2007 12:15 PM

SQL job command - max chars
If you like to paste scripts into the command window when creating SQL Server 2000 jobs in Enterprise Manager, you should be aware of the 3200-character limit.  You won't get a warning unless your script does not parse correctly because of the spot where it was cut off.  You will get a warning if you choose Open to open a script file.

Posted On Friday, April 20, 2007 5:20 AM

SqlBulkCopy
I just discovered the SqlBulkCopy class in .Net 2.0, and it seems awesome!  Now I don't have to shell out to BCP anymore!  We'll see though, I haven't done any performance testing yet...

Posted On Tuesday, April 10, 2007 10:06 PM

T-SQL and GROUP BY ALL

How is it possible that I'm just learning about the "GROUP BY ALL" option?  It sounds like it would save a lot of time.  It worries me though that Microsoft says not to use it anymore because it will be removed from a future version.  Is there a good alternative?

 

Posted On Monday, April 9, 2007 6:37 PM

plural of index

Posted On Friday, March 9, 2007 8:02 PM

beware ths SQL null
It's amazing that to this day I still forget sometimes that any comparison with NULL is "unknown." Case in point, I have 105 tables in my database, but when I run the query below I only get 84 rows (assume for now that I only have one key per table), which briefly baffled me. The reason for this is that t2.table_name is NULL in 21 cases, and when SQL Server attempts to compare 'dtproperties' to NULL, it doesn't get TRUE or FALSE, it gets UNKNOWN. Thanks to Michael Coles for reminding me of this, ......

Posted On Thursday, March 8, 2007 7:46 PM

The More You Know (cue music): SQL Query Analyzer
If you've never passed a date as a parameter to a stored procedure while attempting to use the debugger in SQL Query Analyzer, you probably don't know that the date must be in a certain format (ain't nothin' but an ODBC thing) in order for things to work. If the date is not in the format [YYYY-MM-DD] or something similar (my new motto: dashes not slashes, or hyphens not... nothing really rhymes with hyphens), then you will get an error like this: [Microsoft][ODBC SQL Server Driver]Invalid character ......

Posted On Tuesday, February 27, 2007 7:30 PM

another T-SQL gotcha
One funny (or annoying) thing about T-SQL is that it does not allow you to declare variables (or create temp tables) with the same name in mutually exclusive units of code. Here's an example of what I mean: DECLARE @test bitSET @test = 0 IF (@test = 0) BEGIN DECLARE @monkey int ENDELSE BEGIN DECLARE @monkey int END If you run that in Query Analyzer you'll get the following message: Server: Msg 134, Level 15, State 1, Line 11The variable name '@monkey' has already been declared. Variable names must ......

Posted On Monday, February 26, 2007 11:33 PM

SQL Server 2005 - new discoveries
I just discovered a couple differences in the way SQL Management Studio (SS2005) creates change scripts versus Enterprise Manager (SS2000). The first is that SS2005 places a GO statement after each BEGIN TRANSACTION. SS2005 also replaces 'user' with 'SCHEMA' for level0type when calling sp_addextendedproperty. I'm not exactly sure what the first one accomplishes, but I imagine the second one is because schemas mean so much more and are so much more useful in SQL Server 2005. That reminds me, what ......

Posted On Tuesday, February 13, 2007 7:45 PM

SQL Server and daylight savings changes
In case you don't already know, Daylight Savings Time starts three weeks earlier and ends four weeks later this year than in previous years. This does not affect SQL Server directly because it uses the Windows clock as its clock, but you might have some stored procedures, views, or functions that try to calculate DST based on the old dates. If you think you might, try running the following on each of your databases. Feel free to change the "LIKE" arguments or add "OR" clauses. -- proceduresSELECT ......

Posted On Tuesday, January 30, 2007 8:53 PM

mssqlsystemresource

This is a great explanation of the mssqlsystemresource database in SQL Server 2005 and how to look at what's inside it even though it's hidden.

http://www.aspfaq.com/sql2005/show.asp?id=28

Posted On Thursday, January 25, 2007 4:25 PM

checking for RESTORE permissions with C#/SQL
In case anyone's interested, here's a line of C# that lets you check to see whether the selected user (Windows or SQL) has RESTORE permissions on a specific database. It uses SMO, and you'll need to use System.Security.Principal to get the Windows user logged on to your application, if not using SQL authentication. Pull out the stuff in quotes if you want to try it in Query Analyzer. This page is helpful in understanding the RESTORE command and who should have permissions to run it. Boolean canRestore ......

Posted On Wednesday, January 10, 2007 8:20 PM

compatibility mode
This is an interesting post about how compatibility mode in SQL Server 2005 can really make a difference: http://mapamdug.blogspot.co... If anybody has run into any other gotchas or whatever related to the compatibility mode, I'd love to hear about it. I once tried upgrading a db from 80 to 90 and got so many errors it was unbelievable, mainly because of "incorrect syntax" or "ambiguous column name" in stored procedures. The following is from the BOL, I'm posting ......

Posted On Thursday, January 4, 2007 4:15 PM

casting SQL parameters
A coworker and I discovered the other day that we got an error when we tried using CAST on a parameter in the same line as the procedure call. For example: EXEC [procname] @param1, CAST(@param2 AS DATETIME) result: Invalid syntax near 'cast'. That statement didn't work till we separated it into two lines, like so: SET @param2 = CAST(@stringdatevar AS DATETIME) EXEC [procname] @param1, @param2 I haven't been able to google up any proof that the first call should cause an error, so it would be greatly ......

Posted On Tuesday, December 12, 2006 8:41 PM

SET NOCOUNT ON with ASP.Net?
I'm curious what percentage of developers use SET NOCOUNT ON in their stored procedures on a regular basis, particularly when they don't know exactly what code (particularly ASP.Net code) will be used to call the procedure. My general practice has been to not use it just because I don't know when my fellow coders will use SQLDataReader.RecordsAffected or SQLDataAdapter.Update() (thanks again Jon), but I often wonder if I should just always include it and then just change it when I get a complaint. ......

Posted On Tuesday, December 12, 2006 7:57 PM

unknown chars in SQL Server
I imported a text file into SQL Server 2005 today and ended up with squares at the end of the last column. Turns out the text file, despite being tab delimited, had a tab prior to CR/LF at the end of each row. I was confused at first as to how to get rid of the squares, but then I figured it out. SELECT ASCII(RIGHT(column,1)) FROM table --gives 9, which is TAB in the ASCII table UPDATE table SET column = REPLACE(column, CHAR(9), '') --replaces tabs with nothing ......

Posted On Monday, December 11, 2006 4:21 PM

SQL Server 2005 service packs
It took me a month, but I finally figured out how to get SP1 for SQL Server 2005 installed on my test server: by installing SP2. I was getting errors (something about Debug registry and some other stuff) from setup on the Database Engine upgrade for SP1. So I tried going directly to SP2, and got a new error. Turns out I had to delete the msi file from C:\Windows\Installer that was mentioned in the log file. Not sure if that was the problem for SP1, but SP2 is cumulative, so whatever. UPDATE Feb. ......

Posted On Friday, December 8, 2006 9:10 PM

bit fields in SQL Server 2005
Yesterday a coworker asked why she got an error when trying to enter a "1" in a bit column in a table in Management Studio. I figured she must have entered an extra space or something. I was wrong. Turns out that in SQL Server 2005, at least when entering data using Management Studio, you have to enter true or false! Who thought that was a good idea? There must be something I'm not understanding. Why Microsoft?! Why must I type three or four extra characters?! Why won't you let me be lazy ......

Posted On Friday, December 8, 2006 4:45 PM

Copyright © Alex Bransky | Powered by: GeeksWithBlogs.net