The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

    Mastodon

  • Twitter Updates

  • My Flickr Stream

    20140508-Delphi-2007--Project-Options--Cannot-Edit-Application-Title-HelpFile-Icon-Theming20140430-Fiddler-Filter-Actions-Button-Run-Filterset-now20140424-VMware-Fusion-6.0.3.-no-reclaimable
    More Photos

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,839 other subscribers

Archive for the ‘SQL’ Category

Next Entries »

SQL Server 2000: export SQL Query Analyzer result data to Excel/CSV and more

Posted by jpluimers on 2011/11/16

I just visited a client which is still using SQL Server 2000, and not upgraded their tool set, nor allows different tools to be installed.

Which means back to basics, re-adjusting the project planning and frantically trying to remember things from the past.

Boy am I spoiled with a current toolset :)

This goes from simple things like saving a result set from the SQL Query Analyzer:

  1. Select a cell
  2. Press Ctrl-A to select all rows
  3. Right click in the grid and select “Copy” (to copy the cells as CSV) or “Save As” (to export the cells as CSV)

along the absence of support for XML, MARS, error handling (not even talking about spatial data!) to the way that in SQL Server 2000 DTS (Data Transformation Services) has its own mind of date/time format handing while importing stuff.

But it sure helps setting aims for the scheduled migration process to the far more current SQL Server 2008 R2 :)
(Mental note: migrating the SQL Server 2000 DTS packages to SQL Server 2008 R2 will be a challange).

Oh, some of the SQL Queries that come in handy when moving stuff around in an OTAP/DTAP environment:

1. Selecting relevant DTS packages (that are always in the MSDB database)

select
name
from msdb..sysdtspackages -- 2005: sysdtspackages90; 2008: sysssispackages
where
name like '%my-app-ID%'

2. Selecting relevant objects from a database (watch the xtype values that can exist in SQL Server 2000)

using my-database
select 
name, xtype
from 
sysobjects
where 
name like '%my-app-id%'
and xtype in ('U', 'V', 'TR', 'P', 'X') -- tables/views/triggers/procedures/xprocedures only
order by xtype, name

Blast from the past :)

–jeroen

via: export sql query analyzer data to excel SQL Server.

PS: For the statistics, SQL Server 2000 has been EOL for a while; mainstream support ended in 2008, extended support ends in 2013.

Posted in CSV, Database Development, Development, Software Development, SQL, SQL Server, SQL Server 2000 | Leave a Comment »

I was baffled…

Posted by jpluimers on 2011/11/15

When I saw code like this in a production app, I was speachless:

			if (matcher.Trim().Length > 0)
			{
				if (eesteWhere){sqlWhere += "WHERE ";eesteWhere = false;}
				else{sqlWhere += "AND ";}
				sqlWhere += "m.matcher like '" + matcher.Trim() + "%' ";
			}

Not once, twice, but hundred of fragments like these. Not generated, but hand copy-pasted. And the client thought they were running stable, reliable apps :(

This is soo XSCD ‘Exploits of a Mom‘ (aka Bobby Tables):

The department that wrote the code has been closed a while ago, but some serious refactoring time needs to be invested here, as all applications delivered by that department are vulnerable to SQL Exploits.

–jeroen

Posted in .NET, C#, C# 2.0, Database Development, Development, Software Development, SQL, SQL Server | Leave a Comment »

Convert Microsoft Access JET SQL to SQL Server T-SQL Cheatsheet and Access JOIN parenthesis

Posted by jpluimers on 2011/03/14

There are truckloads of differences in SQL between Microsoft Access and SQL Server.

This Convert Microsoft Access JET SQL to SQL Server T-SQL Cheatsheet helps you convert between the two.

It doesn’t cover the parenthesis thing that Access needs on joins, but you can read on that here, here and here.

–jeroen

Posted in Access, Database Development, Development, SQL, SQL Server | Leave a Comment »

Stack Overflow ebooks

Posted by jpluimers on 2011/02/10

Greg Hewgill published a bunch Stack Overflow ebooks and StackExchange stats.
His readme explains a bit more on the books.
The blog he maintains makes up for some nice reading too.
Be sure to read the blog entry on the ebooks.

–jeroen

via Stack Overflow ebooks.

Posted in *nix, .NET, ASP.NET, C#, C# 2.0, C# 3.0, C# 4.0, Database Development, Delphi, Delphi for PHP, Development, HTML, HTML5, Java, Pingback, Power User, RegEx, Scripting, SocialMedia, Software Development, SQL, SQL Server, Stackoverflow, XML/XSD | Leave a Comment »

Formatted sourcecode in WordPress: uses SyntaxHighlighter 3.0; complete list of supported languages

Posted by jpluimers on 2011/01/18

In the past I wrote a few blog posts on posting sourcecode in WordPress.

Nick Hodges‘ last Flotsam and Jetsam blog post pointed me to the SyntaxHighlighter JavaScript that is used by WordPress and many other engines/sites.

Their site contains an even more elaborate list of supported languages.

I had the basic list right in my last post, but was missing all the aliases (which often are easier than the longer proper names).

This is the new table adapted from their list: Read the rest of this entry »

Posted in .NET, Batch-Files, C#, CSS, Database Development, Delphi, Delphi for PHP, Development, HTML, HTML5, Java, PowerShell, RegEx, Scripting, SQL, VBS, Web Development, WordPress, XML, XML/XSD, XSD | 5 Comments »

Next Entries »