Geeks With Blogs

News
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
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

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

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

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

arithmetic overflow in VS2010
For anybody getting an arithmetic overflow error in Microsoft.Data.Schema.TSqlTasks.targets in Visual Studio 2010, what resolved the problem for me was deleting the schema comparison file in my database project and then getting it from TFS again.  I guess it must have been corrupted somehow. 

UPDATE:  That only worked temporarily, the error came back when getting latest from TFS and alternatively when opening a schema comparison in a different database project.  Ugh.

Posted On Wednesday, June 20, 2012 4:29 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

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

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

recursive history in tfs

For those of you who, like me, transitioned from Subversion and TortoiseSVN to TFS, you might be confused as to how to view the history of a folder recursively.  My problem was that I was wanting to see the recursive history in the Solution Explorer rather than the Source Control Explorer.

Posted On Friday, August 6, 2010 8:45 AM

creating a list of consecutive integers in c#
If there's already a way to get a List<int> of consecutive integers without a loop in C#, I don't know what it is, so I created a method for it. public static List<int> GetIntegerListFromRangeUsin... start, int end) { if (end < start) { throw new ArgumentException("Faulty parameter(s) passed: lower bound cannot be less than upper bound."); } List<int> returnList = new List<int>(end - start + 1); for(int i = start; i <= end; i++) { returnList.Add(i); } return returnList; ......

Posted On Wednesday, April 28, 2010 7:17 AM

windows 7 and atheros nic
I really like Windows 7 so far, amazingly, but I was about ready to hate it because my computer kept freezing when I would try to copy large files to the network. After about a week I realized that even though Windows Update is telling me it has no updates for me, if I force it to check for new updates it will find them (until I install the ones it finds). After installing the latest driver for my Atheros NIC, the problem seems to have disappeared (I could recreate it until now, so I'm pretty sure ......

Posted On Monday, November 23, 2009 12:47 PM

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

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

sharepoint 2007 (moss) system account
I spent the past day trying to figure out why I couldn't log in as myself, Sharepoint would always just say I was logged in as System Account. After a ton of research and changing things I ran into this: To fix this, you must run the following commands: stsadm -o updatefarmcredentials -identitytype NetworkService followed by: iisreset http://social.technet.micro... That solved my problem. I'm using NTLM authentication and ......

Posted On Wednesday, April 15, 2009 7:40 AM

powershell cut and paste
Here's a Powershell script I whipped up to copy all bak files from C:\TEMP to Y:\DEST. It checks to make sure the file is present at the destination before it deletes it from the source. I placed it in a ps1 (the extension for Powershell files) file and scheduled a task to execute a batch file that executes the ps1 file. The reason I had to check for null is that Powershell is kinda dumb and if the source folder is empty it picks it up as a child and performs the Copy-item. $oldPath = "C:\TEMP" $newPath ......

Posted On Wednesday, April 15, 2009 4:57 AM

ajax loader in user control within master page
I spent hours trying to figure out why my ASP.Net page, which uses a master page with a RadGrid surrounded by a RadAjaxPanel with an AjaxLoadingPanel, would not show the loading message. I would click on the link to the page in the site menu, then IE would sit and spin for a while until it loaded the entire page, rather than loading the page right away but showing the loading spinner until the data was returned. What I had to do was add this javascript (w/jQuery) to the user control that the grid ......

Posted On Thursday, April 9, 2009 4:58 AM

comcast blocking port 25?
Even though the email server I'm running at home only sends and receives around 10 emails per month and has never been open for relay, it seems as though Comcast has started blocking port 25.  The technician I've chatted with says otherwise, but either he was wrong or not telling the truth.  I'm just putting that out there in case others have the same problem.

Posted On Monday, March 2, 2009 8:23 AM

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

resharper clean-up gotcha
Be careful when using Cleanup Code on a web solution in Visual Studio.  It may change the Inherits string in your Global.asax file in order to remove unnecessary namespaces, which apparently are actually necessary for the site to start up.

Posted On Tuesday, February 3, 2009 7:34 AM

resharper and auto-property gotcha
Be very careful when allowing Resharper to convert properties to auto-properties. In some cases it will re-initialize fields that were already initialized earlier on in the constructor. http://www.jetbrains.net/ji... ......

Posted On Thursday, August 7, 2008 11:10 AM

generic typing and scope_identity()
I recently ran into an interesting problem. I had created a data access method that used generic typing to execute a scalar SQL Server stored procedure (below), and in the stored procedure was returning SCOPE_IDENTITY() for an integer identity column. I was passing in a type of "int" to the method (as <T>) since that's what I was expecting back, but I kept getting back null. After some frustration and bewilderment I discovered that SCOPE_IDENTITY() always returns a decimal, and C# doesn't want ......

Posted On Wednesday, August 6, 2008 5:38 AM

best quote ever

"So they told me that using the download page to download something was not something they anticipated."

--Bill Gates 

Posted On Friday, June 27, 2008 11:07 AM

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

CPU percent limit in .Net

Why doesn't the .Net framework have anything that lets you set a maximum CPU usage for an application?  Is it something Windows can't handle?  I'd really like to be able to say, "I don't care if this app gets stuck in a loop or whatever, it will never get above 25% CPU."

Posted On Friday, February 1, 2008 11:29 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

RadGrid and grouping message
If you're using telerik's RadGrid and you're seeing a message in the UI that says "Drag a column header and drop it here to group by that column" and you want to hide that message, here's what you do: myRadGrid.GroupPanel.Text = ""; This page just confused me rather than helping me: http://www.telerik.com/comm... ......

Posted On Wednesday, December 26, 2007 12:06 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

ajax rocks
I now believe that anyone who is developing an ASP.Net application and is not in an extreme time crunch is crazy not to use Ajax.

Posted On Tuesday, October 9, 2007 5:24 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

settings transfer from designer to code in composite control in asp.net

If you want to build your own composite control for ASP.Net and you want to create in such a way that when you set a property in the designer window, the setting is reflected in the aspx code, placing this attribute on each public property is the key:

[DesignerSerializationVisibility(DesignerSerializationVisibility.Content)]

Posted On Tuesday, April 3, 2007 7:05 PM

small company, relaxed atmosphere, all the Chinese food you can eat...
My company is hiring for three different ASP.Net-related positions in Downtown Seattle (the International District to be precise). Drop me a line if you might be interested. Software Test Engineeer Software Engineer Configuration Management Engineer ......

Posted On Wednesday, March 21, 2007 3:46 PM

getting web app assembly from composite control
If you have a composite control you've created for use in ASP.Net, and you want to use that control to display the name and version of the web application's assembly, there's something you need to know: As far as I can tell, there's no way to get the correct assembly if you're only embedding the control in the HTML code. In this case, GetCallingAssembly() returns System.Web rather than the app name. But if you add the control from the codebehind, or if you add a method to the control that gets called ......

Posted On Thursday, March 15, 2007 7:45 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

AJAX: Unknown server tag 'asp:ScriptManager'.
I just spent an unhealthy amount of time trying to figure out this error: Unknown server tag 'asp:ScriptManager'. I was baffled because I already had a reference to AjaxControlToolkit.dll in my project, and I had what I thought was a web.config ready to handle any AJAX requests. It turned out adding the section below to system.web fixed the problem. <pages> <controls> <add tagPrefix="asp" namespace="System.Web.UI" assembly="System.Web.Extens... Version=1.0.61025.0, Culture=neutral, ......

Posted On Wednesday, May 16, 2007 11:54 AM

serial for dinner and a cookie for dessert
Can anybody explain serializability to me?  Can any class be made serializable, but it's just better not to for some?

Posted On Tuesday, May 15, 2007 4:11 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

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

AbstractConstraint - inheriting Rhino Mocks objects to override comparisons
As you may have discovered, there are certain objects in .Net are not easily comparable. For example, if you create two objects of type StringDictionary and add exactly the same keys/values to both and then compare them, it will say they're not equal. This can be a problem when unit testing. Here's some sample code in C# that should help you compare the StringDictionary and FileInfo classes using Rhino's AbstractConstraint class. public class StringDictionaryConstraint : AbstractConstraint { private ......

Posted On Wednesday, February 21, 2007 8:42 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

unit testing: .Net mock object frameworks

Does anybody have an opinion on which of these frameworks for creating mock objects is the best?  What do you like or dislike about each?

  • NMock
  • DotNetMock
  • EasyMock .NET
  • Rhino Mocks
  • TypeMock .NET
  • NUnit Mocks
  • NMockLib
  • Attach
  • Posted On Tuesday, February 13, 2007 4:06 PM

    extracting a complete zipped archive using C#
    I struggled with this for many hours, partly because I wanted to use a class native to .Net 2.0, like GZipStream or DeflateStream, but neither one appeared to be able to handle extracting all directories and files from an archive rather than one single file. So I ended up using SharpZipLib, which seems to work nicely. Here's a code sample if you're looking to decompress an archive with more than just one file (don't forget "using ICSharpCode.SharpZipLib.Zip... public void ExtractAll() { // uncompress ......

    Posted On Friday, February 9, 2007 5:18 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

    Perific wireless mouse
    So I decided a while back to get a new mouse that would ease my shoulder and hand pain, and I happened upon this. Well I've been using it for a few weeks now on my Dell 4550 with Windows XP, and I thought I'd share my thoughts on the Perific wireless mouse for anybody considering a new mouse. Pros If your posture is good (or if you start trying harder to straighten your shoulders), it really does seem like it's good for the shoulder and whatnot. It's very easy to use it in your hand rather than on ......

    Posted On Tuesday, January 30, 2007 8:12 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

    C# loops: Performance Iterating Generic Lists

    This is a very interesting post, the important thing to take away being that using "for" rather than "foreach" seems to be noticeably faster in big lists.

    http://www.thinksharp.org/?p=50

    Posted On Friday, January 19, 2007 10:12 PM

    What separates an expert from a guru?

    The brain of an expert has been trained to access a good deal of the knowledge that he or she has attained through study and experience in his or field.  The brain of a guru can readily access almost anything about his or her field that he or she has learned, but at times can also see beyond that to find solutions that are new or unheard of.

    Posted On Wednesday, January 17, 2007 6:18 PM

    ListBox bug in C#?
    I don't know if this would be considered a bug or not, or if I'm just missing something, but when you use ListBox.DataSource in Windows.Forms (.Net 2.0), and you add or remove an item from an ArrayList that is set as the DataSource, there is no method to refresh the listbox. The only thing I know that works is setting the DataSource to null and then setting it back to the ArrayList. That just doesn't seem right. addendum (the next day): I may have found another bug! When I move an item up or down ......

    Posted On Wednesday, January 17, 2007 1:15 AM

    new words
    It's always bothered me that there's not a single word for multiples of ten and a single word for multiples of five. It bothers me because society determined long ago that these "round" numbers were far more important than others (e.g. the 75th anniversary gets far more attention than the 74th). To that end, I'm creating two new words. decager: any integer containing a zero in the ones column; examples are 0, 10, -20, 250, 370 quinteger: any integer containing a zero or a five in the ones column; ......

    Posted On Tuesday, January 16, 2007 4:52 PM

    missing Windows versions?
    Did anybody ever figure out what happened to the 91 versions of Windows that should have been between Windows 3.0 and Windows 95?  I'm pretty sure I never saw them...

    Posted On Thursday, January 11, 2007 9:46 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

    .Net and animated GIFs in Windows forms

    Apparently when you use an animated GIF as a background image in a control (e.g. button or picturebox), the image lacks animation, i.e. it just sits there with its starting layer.  This is kind of annoying, but I was able to work around it in this particular instance by using PictureBox.Image.  I suppose if I were really desperate I could have used Thread.Sleep with a bunch of different images on rotation to make it look like an animated GIF.

    Posted On Monday, December 18, 2006 10:32 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

    .Net console apps and \"
    I discovered something weird with .Net console apps. Say I have an app called testapp and want to run it from a command line like this: C:\>testapp /s Server /f "C:\testfolder\testfile\" >output.txt That won't work the way you might expect it to. The framework doesn't treat \" (backslash followed by a double quote) the way it should (in my opinion) and so the last argument is "C:\testfolder\testfile\" >output.txt I just throw up a message in my app telling the user not to end the path with ......

    Posted On Friday, December 8, 2006 5:00 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