Posts
1145
Comments
890
Trackbacks
1
SQL
Public Service Reminder: Don’t Compress your SQL Server Folders

I have a couple of smaller machines that get ‘repaved’ from time to time and fairly consistently I need to compress some of the hard drives because of low disk space, and fairly consistently, I forget that SQL Server doesn’t like this, and so am fairly consistently momentarily confused when random SQL Server patches through Windows Update fail. And then I fairly consistently uncompress them and everything is fine. Fairly consistently. HTH.

posted @ Thursday, March 09, 2017 2:41 PM | Feedback (0)
Scaffold-DbContext doesn’t appear to work with EF Core 1.1

Are you surprised?  Me either. Given this NuGet Package Console command: Scaffold-DbContext -Connection "data source=MyServer;initial catalog=MyDb;integrated security=True" -Provider "Microsoft.EntityFrameworkCore.SqlServer" where I’ve obviously replace my actual server name and database name, one gets this error: Could not load file or assembly 'System.Data.SqlClient, Version=4.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'. The system cannot find the file specified. Well, that’s because EF Core 1.1 along with the SqlServer package requires SqlClient version 4.3.0 Fantastic.

posted @ Friday, November 25, 2016 11:42 AM | Feedback (0)
ADO.Net Entity Data Model: Getting column information from a stored procedure that returns data from temp tables

Way back machine on this one. You need to temporarily add: SET FMTONLY OFF To the beginning of the proc for the data model to be able to get the column info.

posted @ Tuesday, June 28, 2016 11:24 PM | Feedback (0)
Breaking news: Relational databases are no longer needed

I wasn’t aware of this: It's not a relational system and, frankly, relational systems simply aren't needed any more. The whole concept of normalization and relational "thinking" for lack of better words came about because of disk space (I was a former DBA). SQL databases are very good at what they do, they succeed because they stick with ACID and are quite fast; I don't think it has anything to do with how good they are at relational theory. But, he was a former DBA (apparently he isn’t a former DBA anymore), so he must know what he’s...

posted @ Sunday, April 19, 2015 12:52 AM | Feedback (0)
SQL: Removing Duplicate Rows Using a CTE

From here, for future reference: WITH cte AS ( SELECT[foo], [bar], row_number() OVER(PARTITION BY foo, bar ORDER BY baz) AS [rn] FROM TABLE ) DELETE cte WHERE [rn] > 1

posted @ Wednesday, April 01, 2015 10:43 AM | Feedback (0)
Are Postgres Foreign Data Wrappers any different from SQL Server Linked Servers?

Reading these things, I’m trying to see what the differences are.

posted @ Tuesday, March 24, 2015 10:43 AM | Feedback (0)
Repost: Self-maintaining, Contiguous Effective Dates in Temporal Tables

This is rather cool. Yes, I know, it will make some twitch like Inspector Dreyfus, and I can hear some of you say “For the love of God and all that is Holy, it uses triggers!  STONE HIM!!!!!!” But, cool it is, none the less. Enjoy.

posted @ Friday, March 13, 2015 10:28 AM | Feedback (0)
Flexible Architecture, done entirely wrong

Having worked on a similar system at a ‘too big to fail’ financial client, I know exactly what Ayende is talking about: Then the designers sat down and created the following system of classes: public class Table { public Guid TableId {get;set;} public List<FieldInformation> Fields {get;set;} public List<Reference> References {get;set;} public List<Constraint> Constraints {get;set;} } ………

posted @ Tuesday, March 03, 2015 9:08 PM | Feedback (0)
SQL Server Import Wizard still can’t handle basic text files?

Way back before there was indoor plumbing, there was this thing called DTS.  It was used to process data in SQL Server, a typical use case involved getting data from text files and loading them into tables. It sucked.  A lot.  You could program them graphically and/or in Visual Basic, but they were a bitch to write and maintain (somewhere on this blog there’s an entry of how to export them using Microsoft Word, which was necessary if you wanted to edit them in bulk since they had connection strings and whatnot embedded in them).  Migrating from one server...

posted @ Friday, February 20, 2015 9:45 AM | Feedback (0)
Repost: Speeding up Entity Framework in long iterative loops

I’ve run into this sort of thing before myself, but Rick has all the good details. Basically, if you don’t need change tracking, then you can easily turn it off and speed things up greatly.

posted @ Sunday, December 21, 2014 6:23 PM | Feedback (1)
Please don’t save your view models in your database

For various reasons, I was watching this to learn more about the latest version of Raven DB 3.o (basically, I have a big event/command store and I want to process the raw data into multiple different ‘buckets’ and am looking into various options, including SQL and NoSQL options). Before I get to my main point…. Normally, I wouldn’t pay much attention to mistakes/issues with the presentation itself.  Things happen.  Network connections go out, that sort of thing. However, if you are trying to give an example that is key to your overall presentation, and it is not...

posted @ Friday, December 12, 2014 11:20 PM | Feedback (2)
An old school way of writing application code

In this case, ‘old school’ means ‘generally bad’. Now, in certain circles, one can’t swing a dead cat and not hit someone who thinks that stored procedures are evil.  Yawn, yawn, yawn.  No, they aren’t, get over it (having said that, you should rarely use them, as there is almost always a better way of doing things). That being said, if you are going to write stored procedures, you almost definitely shouldn’t do them this way.  The mixing of insert/query/update code, the ’magic number’ error codes, the convoluted parameter checking, and this isn’t even an example that actually...

posted @ Tuesday, November 18, 2014 12:20 PM | Feedback (0)
Biggy and Massive and Humongous, Oh My!

So, Rob got bored and decided to create yet another data access thingy.  Subsonic wasn’t enough, so then there was Massive, and now there is Biggy.  I’m sure it has even more value than appears on first blush. Humongous is going to be the name of the next one he writes, whenever that is.  Next year, probably. Good code to read.  Enjoy.

posted @ Wednesday, February 26, 2014 11:52 PM | Feedback (0)
SSMS Tip: Auto-generate Insert Statements

Once again, leaving a note to myself. Do you remember when SQL Server’s import/export functionality didn’t suck?  Where, when you didn’t have direct connectivity between two instances, you could export data to a flat file from the source server and then import them into the target server, and not have to deal with annoying conversion or cast errors due to the nature of the data, the rotation of the Earth around the Sun, and other random problems? I ran into this issue when trying to copy reference data from one server’s reference tables into another’s (where the schema...

posted @ Tuesday, August 13, 2013 11:18 PM | Feedback (0)
Hibernating Rhinos’ Entity Framework Profiler Mini Review

This doesn’t really constitute a review, just a couple of comments, but whatever. In some previous posts, I had talked about my experience with Hibernating Rhinos’ Linq to Sql profiler.  Basically, it failed for me, didn’t even function properly, and Ayende was unable to help. Anyway, I’m working on something that uses Entity Framework, so I figured, what the hell, I’ve give it one more shot, I’ll just get the EF Profiler, and not the entire suite, and see how it works.  I can expense it. So, the first thing I did, obviously, was hook it up...

posted @ Tuesday, June 18, 2013 8:04 PM | Feedback (0)
Repost: Relational Database vs NoSQL Fanbois

This is basically NSFW, but if you’ve never taken a look, and are in to geek humor, you might want to take a look at this video.

posted @ Friday, January 25, 2013 3:29 PM | Feedback (0)
Useful SQL Script – Generate Insert Statements from Existing Data

Written by someone named Narayana Vyas Kondreddi in 2001-2, I found this very useful today.

posted @ Friday, September 21, 2012 2:59 PM | Feedback (4)
SQL Server: How to document and configure server instance settings

For my own future reference, from SimpleTalk.

posted @ Tuesday, January 31, 2012 2:07 PM | Feedback (0)
ProSQL Trump Cards

These are funny.  If you don’t understand why they are funny (which might not be for the obvious reasons one would think they are funny), don’t worry about it.

posted @ Wednesday, January 25, 2012 7:18 PM | Feedback (0)
Repost: MoreSQL

Here is a post that announces the MoreSQL movement. For true fun, read the comments.

posted @ Wednesday, January 25, 2012 12:36 PM | Feedback (0)
This is a semi-complex query?

Rob posted something about how to implement complex queries using Massive: “Here’s a semi-complex query that is all too common in the business case: var results - DynamicModel.Open(connectionString).Query(@" SELECT Orders.OrderNumber, Categories.Name FROM Products INNER JOIN Categories ON Categories.CategoryID = Products.CategoryID INNER JOIN OrderItems ON OrderItems.ProductID = Products.ID INNER JOIN Orders ON Orders.OrderID = OrderItems.OrderID WHERE Orders.OrderDate > @0 ", DateTime.Now.AddYears(-1)); ...

posted @ Tuesday, August 09, 2011 5:28 PM | Feedback (0)
SQL Query Troubleshooting Tip #1

If a query that has been running without a hitch suddenly crawls to a standstill for no apparent reason, try updating statistics. In SQL Server, run sp_updatestats. And if you are experienced with SQL Server, think of trying tip #1 first, not four hours later.

posted @ Wednesday, July 20, 2011 7:08 PM | Feedback (0)
SQL Server: When were my indexes and statistics updated?

From http://weblogs.sqlteam.com/joew/archive/2007/08/31/60316.aspx, use these queries to check when statistics for indexes were last updated. SELECT t.name AS Table_Name ,i.name AS Index_Name ,i.type_desc AS Index_Type ,STATS_DATE(i.object_id,i.index_id) AS Date_Updated FROM sys.indexes i JOIN sys.tables t ON t.object_id = i.object_id WHERE i.type > 0 ORDER BY t.name ASC ,i.type_desc ASC ,i.name ASC SELECT name AS stats_name ,STATS_DATE(object_id, stats_id) AS statistics_update_date, * FROM sys.stats order by STATS_DATE(object_id, stats_id)  

posted @ Thursday, November 04, 2010 9:13 AM | Feedback (0)
RethinkDB : Another challenge to NoSql Orthodoxy

Take a look at RethinkDB, a drop-in replacement for the MySql orthodoxy. A central theme of NoSql orthodoxy is that ACID can’t scale.  The fact that companies like Amazon and Google (you may have heard of them) have ‘abandoned’ ACID is, in my mind, enough proof anyone needs that the NoSql is a valid option that anyone who needs to consider data storage options (what an ugly sentence….but I digress). What I like about things like VoltDB and RethinkDB is that they are attempting to rise to the challenge.  Can you have your ACID cake and eat it...

posted @ Thursday, July 01, 2010 6:36 PM | Feedback (0)
NoSQL and the Six SQL Urban Myths

A very interesting article from the Hot Scalability site about VOLTDB, a next-generation RDBMS that is purported to scale beyond NoSQL implementations without giving up ACID.  It targets both the NoSQL style databases (listing the six SQL urban myths that its makers think NoSQL advocates fall prey to) as well as the ‘dinosaur’ traditional RDBMS’ like Oracle and SQL Server. The article is a very interesting read, giving a lot of pros and cons about VOLTDB, but if databases are your cup of tea, it’s definitely worth looking into. Check it out.

posted @ Monday, June 28, 2010 5:13 PM | Feedback (0)
RavenDB, and a brief design philosophy discussion with Ayende

Suppose you design a system that is chock full of interfaces, specifically things like some version of IRepository, where you have the ability to change out your backing store/database more easily. A common criticism of this sort of design is that it is unrealistic to think you actually will change your main backing store/database in a production system.  My own experience is that while it does happen (a current client project I am working on involves changing the backing database for a set of applications from SQL Server to Oracle, for instance), it doesn’t happen often, and you often...

posted @ Friday, May 21, 2010 7:44 PM | Feedback (1)
NoSQL in the Wild

Rob Conery has a post about how they run Tekpub using NoSQL that’s a great read. I’m not qualified to talk about Ruby code but that part is kind of irrelevant.  He hits all the right notes in talking about why they did the things they did (I could quibble about the cost ‘criticism’ of BizSpark, but that’s a business decision), especially in terms of separating reporting. It’s a good read, check it out.

posted @ Wednesday, May 19, 2010 8:20 PM | Feedback (0)
NoSQL links of interest

As I’ve mentioned before, one of the great things (at least in terms of how it relates to software development) about the Internet in general, and the blogosphere in particular, is that it offers a tremendous opportunity for someone to ‘fast-forward’ their skills if they know where to look (being vaguely intelligent and able to read quickly also helps). This definitely applies when it comes to NoSQL.  Beyond the mindless advocacy of some folks that think NoSQL applies everywhere, and before I have to create a system that needs to deal with the scalability issues of Amazon, I want...

posted @ Sunday, April 25, 2010 10:30 PM | Feedback (0)
Hello SSIS My Old Friend

A few years ago, Ayende had a post about why he hated SSIS, which brought about some responses (here and here and here) from some other folks.  It was a lot of fun had by all (sarcasm).  I happened to be doing some work prototyping SSIS and whether it could be used to replace some of the Perl-based implementations we had in place.  After only a little bit of effort, we clearly determined that SSIS couldn’t hack it (which is ironic, since getting SSIS to do many common tasks required a lot of hacking, but I digress). digression: I...

posted @ Tuesday, April 13, 2010 12:08 AM | Feedback (0)
NoSQL is the new Black

No one really talks about alt.net anymore.  In my mind, that’s a good thing.  Though some people apparently had different goals for all of that (setting up a foundation or some other silly thing), for other people, it was about taking the things that a small subset of .NET developers were doing and making them more mainstream (whatever that means).  Though (again) it is hardly a definitive sign of anything, the fact that people like Jeremy and Ayende have been publishing articles in MSDN (and no one thinks this is odd) is a good thing.  That Asp.NET MVC has unit...

posted @ Tuesday, March 30, 2010 10:19 PM | Feedback (2)
It’s OK to do Reporting off of a RDBMS

Well, that’s a little misleading.  It’s OK to do Reporting off of a RDBMS as long as you do it right, and you should consider other options before committing to it.  note: I’m using “Reporting” here in the traditional sense, not in the cqrs sense where pretty much anything that doesn’t involve a command is called “Reporting.”  Also, since I mostly know SQL Server, that’s what I’m going to be discussing here.  Also, yes, I know I’m glossing over a hell of a lot of stuff here. The ‘Problem’ Suppose you have your traditional transactional system...

posted @ Sunday, February 07, 2010 4:20 PM | Feedback (0)
Remote connection to a SQL Server 2005/2008 instance

If you’re like me, you might have had an occasion or two where you needed to connect to an instance of SQL Server from a workstation or server that wasn’t where the SQL Server was actually located.  Because of the gosh darn important security measures that have been implemented in more recent editions of Windows Server and SQL Server itself, this might not work well by default (not to be critical of gosh darn important security measures, as they are gosh darn important). As a note to myself, if you need to do this, there are a couple of...

posted @ Monday, December 14, 2009 9:28 PM | Feedback (0)
Linq to SQL Not Totally Dead, but it’s not at all well

Jim Wooley has a post that makes what I think is a really good analogy: Linq to SQL is to Entity Framework as Winforms is to WPF. That is to say, Microsoft isn’t pulling either Linq to SQL or Winforms from the framework, but isn’t really adding new features to them, just making bug fixes. Makes sense to me.

posted @ Thursday, June 04, 2009 6:09 PM | Feedback (0)
Linq to SQL Not Totally Dead

Though Microsoft has made it clear that EF is their preferred ORM type thingy of choice, Linq to SQL is still getting updated in .NET 4.0.  List of changes can be found here.

posted @ Tuesday, June 02, 2009 6:20 PM | Feedback (2)
Enough with the ‘Sprocs are evil’ BS m’key?

Derik has posted one of those ‘Sprocs are evil’ type rants (he did label it as a rant, so I have to give him props for that).  Probably unrelatedly, Jeremy Miller posted something that is sort of related (though his point was really a larger one).  Regardless, I have to respond. (Update: I mean responding in general, not line by line discussion of either Derik's or Jeremy's posts...more of a rant using them as a launching pad).  (Update #2: Derik wants to make it clear that he doesn't think sprocs are evil.  I think it is a semantic point, but...

posted @ Monday, March 02, 2009 1:14 AM | Feedback (7)
T-SQL: List of information_schema views

From here: http://haacked.com/archive/2006/07/05/bulletproofsqlchangescriptsusinginformation_schemaviews.aspx Name Returns CHECK_CONSTRAINTS Check Constraints ...

posted @ Monday, February 04, 2008 11:37 AM | Feedback (1)
Versioning Databases

K Allen Scott from OdeToCode has a great series on versioning databases.  Definite must read stuff for SQL development. http://odetocode.com/Blogs/scott/archive/2008/01/30/11702.aspx http://odetocode.com/Blogs/scott/archive/2008/01/31/11710.aspx http://odetocode.com/Blogs/scott/archive/2008/02/02/11721.aspx http://odetocode.com/Blogs/scott/archive/2008/02/02/11737.aspx http://odetocode.com/Blogs/scott/archive/2008/02/03/11746.aspx  

posted @ Monday, February 04, 2008 11:00 AM | Feedback (0)
SQL Enterprise Manager TaskPad Script Errors 'Fix'

Switch the view to something other than TaskPad, then back again. http://sqlserver2000.databases.aspfaq.com/why-do-i-get-script-errors-in-enterprise-manager-s-taskpad-view.html

posted @ Tuesday, July 31, 2007 2:21 PM | Feedback (0)
T-SQL: Using Microsoft Office Applications to Edit/Run/Debug DTS Packages

From Doug Waldron. For instance, using Microsoft Word: Save the DTS package to the file system.   Tools->Macro->Visual Basic Editor Pops a Visual Basic 6 Window. View->code Cut and past your code into the new window Tools->References   check Microsoft DTSPackage Object Libray   check Microsoft DTS Custome Tasks Object Library   check Microsoft DTSDataPump Scripting Object Library       F8 to step through   F9 to set break point   F5 to run

posted @ Friday, July 27, 2007 1:59 PM | Feedback (0)
T-SQL: Using Top 100% in Views

In SQL Server 2000, you can do something like: Create view myView as select top 100 percent column1, column2 from mytable order by column1 to allow for ordered views.  It isn't 'supposed' to work, but it does. It doesn't in SQL Server 2005.  However, this does: Create view myView as select top 99.9999999999999 percent column1, column2 from mytable order by column1 If you count, there are 13 decimal places.  If you add a 14th decimal place it doesn't work. Go figure. http://sqladvice.com/blogs/repeatableread/archive/2006/06/14/18682.aspx  

posted @ Friday, July 27, 2007 1:31 PM | Feedback (0)
T-SQL: READ COMMITTED and multi-counted rows

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/16/1345.aspx Snapshot isolation is the only way to get around the problem.

posted @ Tuesday, July 24, 2007 2:06 PM | Feedback (0)
T-SQL: NOLOCK and Page Splits

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=92888&DisplayTab=Article The upshot is that if a page split happens when doing a NOLOCK select, you can see the same row twice, or miss rows altogether. The fix is to always have an ORDER BY in the SELECT.

posted @ Tuesday, July 24, 2007 2:01 PM | Feedback (0)
SQL Server and Raid 5

http://sqlblog.com/blogs/linchi_shea/archive/2007/02/07/is-raid-5-really-that-bad.aspx Always make sure you are comparing apples to apples.

posted @ Tuesday, July 24, 2007 1:36 PM | Feedback (0)
T-SQL: Null in views

If you define a null in a view for a column (so, you always want to pass null for whatever reason), if you don't cast it, it will default to an int data type. To get around it, do something like: cast(isnull(columnName, 0) as varchar(8)) as columnName

posted @ Monday, April 09, 2007 3:01 PM | Feedback (0)
T-SQL: What is Halloween Protection?

From http://blogs.msdn.com/ianjo/archive/2006/01/31/521078.aspx: “ “Halloween protection" in database systems refers to a solution for a problem that can occur in update queries. The problem occurs when an update itself affects the rows selected for update.  For example, imagine a company wants to give every employee a 10% pay increase. If the update query is unlucky enough to walk the salary index then the same row may be selected, updated, the update moves the row ahead in the index, and then the row will be redundantly selected again and again.  This problem is corrected by isolating the rows chosen from the effects of the update...

posted @ Friday, March 30, 2007 11:04 AM | Feedback (0)
SQL Server 2005: Database Engine Tuning Advisor error - "Failed to find IPC port"

I've gotten this every once in a while when trying to launch it from Management Studio.  The 'fix' is to keep trying to launch it (sometimes you just try repeatedly, other times, you wait a couple of minutes and try again). Something about a 'timing issue' between the IPC communications layer and SQL Server.  Yeah, I don't know what that really means either, but that's what I found on various forums. Update: if you try to cancel a running analysis or kill it, it seems to go bye-bye for good till the next reboot.

posted @ Thursday, March 01, 2007 1:19 PM | Feedback (0)
T-SQL: Basics of Statistics in SQL Server 2005

http://www.developer.com/db/article.php/3622881 Includes this cool trick: “The STATS_STREAM option, so far as I know, remains undocumented. But we now know what the other two options do, thanks to the new blogging spirit of openness and a blog posting from the SQL Server Query Optimization Team. It turns out that ROWCOUNT and PAGECOUNT can be used to trick the query optimizer by letting you fake the internal metadata about a particular table - specifically the count of rows and count of pages that the table occupies. If you're going to experiment with this, please do not do so in a production database! For example, you can...

posted @ Thursday, March 01, 2007 12:49 PM | Feedback (0)
T-SQL: Mimicking Merge Statement in SQL Server 2005

http://sqlserver-tips.blogspot.com/2006/09/mimicking-merge-statement-in-sql.html

posted @ Thursday, February 08, 2007 1:36 PM | Feedback (0)
T-SQL: Find text in all dbs and jobs

set quoted_identifier off   DECLARE @name varchar(40) ,@cmd varchar(2000) ,@TEXT varchar(500),@Database_Name varchar(25),@TEXT2 varchar(500) set @Database_Name = 'CSAToday' Set @TEXT = 'getbloombergvalue' /*note...when seraching for system names, they may have [ ] around name use below to include*/ --Set @TEXT2 = '[[]SupplierDataBBDLDvdCash' -- -- if @Database_Name is not null -- SELECT name FROM master.dbo.sysdatabases -- where name = @Database_Name -- order by name -- else -- SELECT name FROM master.dbo.sysdatabases -- order by name -- set @cmd = 'SELECT name FROM master.dbo.sysdatabases -- order by name ' /*jobs*/ set @cmd =' select b.name as Job_Name ,a.command as Command from msdb..sysjobsteps a join msdb..sysjobs b on a.job_id = b.job_id where a.command like "%' + @TEXT + '%"' if @TEXT2 is not...

posted @ Wednesday, December 06, 2006 10:58 AM | Feedback (0)
Working with SQL Server 2005 System Views Across Databases

SELECT * FROM databasename.SYS.columns A JOIN databasename.SYS.OBJECTS B ON A.object_id = B.object_id WHERE b.NAME = tablename

posted @ Wednesday, November 01, 2006 9:59 AM | Feedback (0)
T-SQL: sqlcmd nocount oddity

osql is a command-line program to interact with Sql Server, and is useful for launching scheduled batch files of Sql commands. Well, it is deprecated, and sqlcmd is the new program to do the same thing. I've come across one oddity: it is common to include print statements so that the output log file is more 'human readable', and easier to grep through, etc. In weird combinations, calling: print 'print text' in a batch file will log out: “0 rows affected.”  This should never happen with a print statement, and you should be able to supress it with: 'set nocount on' However, that command will only produce the...

posted @ Friday, September 22, 2006 3:07 PM | Feedback (2)
Sql Server: Promoting .bas files into DTS packages using Microsoft Word

Delete the package on the destination server if it already exists. Open DTS package (design) Save As, Location Visual Basic File Edit visual basic file, .bas to make changes use these next lines to save to database and comment out execute goPackage.SaveToSQLServer “servername", , , 256  ' you can also use sa authentication 'goPackage.Execute To load Open MS Word Tools->Macros->Visual Basic editor Cut and paste .bas file into editor Tools->References               check Microsoft DTSPackage Object Libray             check Microsoft DTS Custome Tasks Object Library             check Microsoft DTSDataPump Scripting Object Library F5 to run  

posted @ Friday, September 15, 2006 3:25 PM | Feedback (0)
T-SQL: Determine primary keys and unique keys for all tables in a database...

http://blogs.msdn.com/sqltips/archive/2005/09/16/469136.aspx

posted @ Thursday, September 14, 2006 2:27 PM | Feedback (0)
SQL: Using a CSV with an IN sub-select

Handy function to have around: http://sqlteam.com/item.asp?ItemID=11499

posted @ Thursday, August 31, 2006 10:32 AM | Feedback (0)
T-SQL: Query using max in a self-reference

Since I always forget how to do this: delete [table name] from [table name] as t1 inner join ( select column1, max(column2) as datadate from [table name] group by column1 ) as t2 on t1.column1 = t2.column1 where t1.datadate <= dateadd(yy, -1, t2.datadate)

posted @ Thursday, August 10, 2006 12:15 PM | Feedback (0)
T-SQL: finding a table

If you know the server and the table name, but not which database it is in, run this: EXEC Oddly, you need to run it in Query Analyzer, not SQL Management Studio as the latter will start to execute it, but will through an error before it completes (at least in my situation it does...could be the number of databases). sp_MSForEachDB "SELECT name AS '?' FROM [?]..sysobjects where name = 'TableName'"

posted @ Friday, August 04, 2006 1:42 PM | Feedback (0)
SQL Server 2000: SPID blocking itself

This is one of those “it's a feature, not a bug” things that was introduced in SP4: http://support.microsoft.com/default.aspx/kb/906344 Technically, this makes sense, but if you are used to using sp_who2 to check for harmful blocking, this is really rather annoying, as it can look like a process is blocking itself for an extended period of time while it is still doing useful work.

posted @ Tuesday, May 23, 2006 4:26 PM | Feedback (0)
SQL Server 2005: under the default settings SQL Server does not allow remote connections

If you receive this method when attempting to connect to SQL Server 2005, after establishing that the server is running, open up the SQL Server Configuration Manager (found under Configuration Tools in the SQL Server 2005 start menu group) and check to see which protocols are enabled.  By default, only “Shared Memory” is enabled, and so you will need to enable TCP-IP and/or Named Pipes (depending on your network configuration).

posted @ Tuesday, May 09, 2006 9:58 AM | Feedback (0)
Post-build event for custom SSIS package component

Requires strong name. 1. "C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin\gacutil.exe" -u "componentName.dll" 2. copy "$(TargetPath)" "C:\Program Files\Microsoft SQL Server\90\DTS\PipelineComponents" /y 3. "C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin\gacutil.exe" -i "componentName.dll"   Change paths for other components (depending on the solution, you may or may not need to copy the dll).

posted @ Monday, May 08, 2006 10:40 AM | Feedback (0)
T-SQL: The shortest, fastest, and easiest way to compare two tables in SQL Server: UNION

This is cool, and runs quickly: http://weblogs.sqlteam.com/jeffs/archive/2004/11/10/2737.aspx

posted @ Tuesday, February 14, 2006 1:31 PM | Feedback (2)
T-SQL: Increase database size

USE master; GO ALTER DATABASE databasename MODIFY FILE     (NAME = filename,     SIZE = size); GO  

posted @ Friday, February 10, 2006 1:38 PM | Feedback (0)
T-SQL: Index Defragmentation Stored Procedure

Very helpful, from: http://www.sql-server-performance.com/tp_automatic_reindexing.asp ------ CREATE PROCEDURE sp_defragment_indexes @maxfrag DECIMAL AS /* T.Pullen This stored procedure checks index fragmentation in a database and defragments indexes whose scan densities fall below a specified threshold, @magfrag, which is passed to the SP. This SP was initially based on a code sample in SQL Server 2000 Books Online. Must be run in the database to be defragmented. */ -- Declare variables SET NOCOUNT ON DECLARE @tablename VARCHAR (128) DECLARE @execstr VARCHAR (255) DECLARE @objectid INT DECLARE @objectowner VARCHAR(255) DECLARE @indexid INT DECLARE @frag DECIMAL DECLARE @indexname CHAR(255) DECLARE @dbname sysname DECLARE @tableid INT DECLARE @tableidchar VARCHAR(255) --check this is being run in a user database SELECT @dbname = db_name() IF @dbname IN ('master', 'msdb', 'model', 'tempdb') BEGIN PRINT 'This...

posted @ Friday, February 10, 2006 1:19 PM | Feedback (0)
T-SQL: Other datetime functions

http://weblogs.asp.net/guerchele/archive/2004/05/04/125899.aspx

posted @ Thursday, January 26, 2006 2:19 PM | Feedback (0)
T-SQL: Date only, time only

SELECT dateonly = CONVERT(CHAR(8),column,112) FROM table SELECT timeonly = CONVERT(CHAR(8),column,8) FROM table 

posted @ Wednesday, January 18, 2006 11:35 AM | Feedback (1)
Slow Stored Procedure Execution

Suppose you have a stored procedure that runs fine in one environment, but slower in another.  You check to make sure all the underlying indexes exist.  You recompile it a couple of times.  Still, it doesn't perform as it should. Try: Update Statistics tablename

posted @ Wednesday, December 28, 2005 5:03 PM | Feedback (0)
DW and BI overview site

Another general site that describes the basics: http://www.1keydata.com/datawarehousing/datawarehouse.html

posted @ Tuesday, November 01, 2005 12:35 PM | Feedback (0)
T-SQL: Using arrays and lists

Very comprehensive article: http://www.sommarskog.se/arrays-in-sql.html

posted @ Thursday, October 27, 2005 9:32 AM | Feedback (0)
T-SQL: Select where date = max

select t.column1, t.column2, t.column3, t.date from table t with (nolock) where t.column2 like '%AAA%' and t.date = (select max(date) from table where column2 = t.column2) order by t.column2

posted @ Friday, October 14, 2005 2:31 PM | Feedback (1)