Archive for the ‘Database Development’ Category
Posted by jpluimers on 2012/08/28
Dang; I thought this had long left the posting queue, but somehow it ended in the drafts (:
Since then, many more event videos made it to Channel 9, including Build 2011, and TechDays 2012.
Anyway, here it is:
Microsoft’s PDC 2010 was held at the end of October 2010 in Redmond, WA, USA.
For the people that could not attend, it is very nice to view the sessions using the PDC10 player (it seems still people didn’t learn and start stripping the century parts from years again!).
Even if you are not using Visual Studio, .NET Azure or other Microsoft Technologies, there are a lot of interesting sessions showing the directions that Microsoft is taking.
Comparing that to what you do today is always a good thing to do: it helps you reflect, an important part of your personal development.
A few things I found interesting (in no particular order):
- Asynchrony support in C# 5 and VB.NET 11 based on the Task Parallel Library
- The choice to favour HTML 5 over SilverLight, even though Internet Explorer 9 and Microsoft’s HTML 5 authoring/development tools are far from ready
- Azure reporting: is reporting the next big thing on clouds?
- Offline versus online in the cloud world
- NuPack – does it bring package management to the same level as Ruby or *nix?
- XNA for XBox, Windows and Windows Phone
Enjoy!
–jeroen
Posted in .NET, .NET 4.5, C#, C# 4.0, C# 5.0, Channel9, Cloud Development, Database Development, Delphi, Development, HTML, HTML5, Mobile Development, SilverLight, SocialMedia, Software Development, Visual Studio 11, Visual Studio 2010, Visual Studio and tools, Web Development, Windows Azure, Windows Phone Development, XNA | 2 Comments »
Posted by jpluimers on 2012/08/21
Few people know about the INFORMATION_SCHEMA views that have been there since SQL-92.
Two funny things about that standard:
A few reasons I can imagine not many people know about the INFORMATION_SCHEMA:
- Not all relational database servers implement them, and of the ones that do implement them, not all versions implement all views.
That’s what this article is about; currently it lists only SQL Server 2000 and 2008 R2 (tried both) and PostgreSQL (verified docs), but I will update it as soon as I have run the script on other versions of SQL server and database vendors.
- It is hard to find the official ISO standards in a public way, and ISO itself sometimes puts things online, and at other times wants money for it
I remember the Y2K preparation era where the ISO-8601 standard was freely available at http://www.iso.ch/markete/8601.pdf, soon after the Year 2000, the PDF got locked behind a payment engine.
ISO suffers from heavy link rot too, for instance the ISO 3166 country codes used to be at http://www.iso.org/iso/prods-services/iso3166ma, but are now at http://www.iso.org/iso/home/standards/country_codes.htm. What about HTTP 303 or 302 redirect here guys?
Since SQL-92, the INFORMATION_SCHEMA (and its twin DEFINITION_SCHEMA) have been extended. The last extension in 2008. Together they allow SQL databases to be self-describing (I think no vendor has attained that) and the structures queryable in a standard way
In fact that is the main purpose: these views in INFORMATION_SCHEMA are a very convenient standard way to query – in a vendor agnostic way – about tables, views, columns, etc. Read the rest of this entry »
Posted in Database Development, Development, Firebird, InterBase, ISO 8601, MySQL, OracleDB, PostgreSQL, Power User, SQL, SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 7, Sybase | Leave a Comment »
Posted by jpluimers on 2012/08/15
A few weeks ago, Bill Karwin did a must watch webinar on the prevention SQL Injection titled “SQL Injection Myths and Fallacies“.
Bill Karwin (twitter, new blog, old blog, Amazon) is famous for much work in the SQL database community, including InterBase/Firebird, mySQL, Oracle and many more.
He also:
Anyway, his webinar is awesome. Be sure to get the slides, watch the replay, and read the questions follow up.
Watching it you’ll get a better understanding of defending against SQL injection.
A few very valuable points he made: Read the rest of this entry »
Posted in .NET, .NET 3.5, .NET 4.5, .NET ORM, ASP.NET, Batch-Files, C#, C# 1.0, C# 2.0, C# 3.0, C# 4.0, C# 5.0, C++, Cloud Development, COBOL, CommandLine, Database Development, Delphi, Delphi for PHP, Delphi x64, Delphi XE2, Development, EF Entity Framework, F#, Firebird, FireMonkey, History, InterBase, iSeries, Java, JavaScript/ECMAScript, Jet OLE DB, LINQ, LLBLGen, MEF, Microsoft Surface, Mobile Development, PHP, PowerShell, Prism, Scripting, SharePoint, SilverLight, Software Development, SQL, SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 7, VB.NET, VBS, Visual Studio 11, Visual Studio 2002, Visual Studio 2003, Visual Studio 2005, Visual Studio 2008, Visual Studio 2010, Visual Studio and tools, Web Development, Windows Azure, WinForms, WPF, XAML, xCode/Mac/iPad/iPhone/iOS/cocoa | 1 Comment »
Posted by jpluimers on 2012/07/31
Close to 5 months after SQL Server 2012 got released to manufacturing, SQL Server 2008 R2 Service Pack 2 became available last week (July 26th, 2012).
A few links:
–jeroen
Posted in Database Development, Development, SQL Server, SQL Server 2008 R2, SQL Server 2012 | Leave a Comment »
Posted by jpluimers on 2012/06/12
With old installations like Reporting Services 2000, you will often see that a few the reports on the server don’t match the ones in your version control system.
That’s why you sometimes want to download the .RDL files.
User nsaini shows on the SQLTeam forum:
It is easy …..
- Go to Report on Report Manager,
- Click properties of report,
- under the report definition you will see Edit and Update link…
- if you click on Edit you can open or Save the RDL files.
Tara Kizer shows how you get into the report manager: through this URL http://yourserver/reports
Easy :)
There are similar steps for SQL Server 2005 and SQL Server 2008.
If you have to do many of them, you can use Reporting Services Scripter to do it.
Even easier :)
–jeroen
via: SQL Server Forums – Copy .rdl from ReportServer.
Posted in Database Development, Development, Reporting Services, SQL Server, SQL Server 2000 | Leave a Comment »
Posted by jpluimers on 2012/05/23
Yes. Dorothy. There are people using the ADO .NET Entity Framework with SQL Server 2000 in parallel of moving towards a more modern Microsoft SQL Server version.
Entity Framework is lovely for developing data-centric applications.
By default, Visual Studio 2010 will target SQL Server 2008 as a database. That is fine, but it is kind of invisible it does: there is no property or dialog where you can change this.
What you have to change in order to have the Entity Framework send SQL Server 2000 compatible queries is to:
- Right click your .edmx file
- Choose “Open with”
- Choose the “XML (text) editor”
- Find the ProviderManifestToken attribute
- Change the value (usually from “2008”) into “2000”
- Save the .edmx file
- Build and run your application
A few caveats:
Read the rest of this entry »
Posted in .NET, .NET ORM, C#, C# 2.0, C# 3.0, C# 4.0, Development, EF Entity Framework, Software Development, SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 | Leave a Comment »
Posted by jpluimers on 2012/05/15
When in a DTAP environment, you cannot always have complete clean boundaries. Issues in production don’t reproduce in acceptance, you cannot develop in production, etc.
So sometimes you have to simulate or connect to Test or Acceptance Database Servers from a Develop workstation.
There it can get hairy to keep track of which applications connect to which database server.
That’s where the below batch file comes in handy: it scans your systems on connections to common TCP ports used by SQL server, then for each connection give you some process details (or – if you add a commandline parameter – all details that TLINK can get).
The batch file uses the built in tools tasklist, netstat, find and sc (the latter to show information on the local running SQL Services).
It also uses TLIST, which can be a bit awkward to get.
Read the rest of this entry »
Posted in .NET, Batch-Files, CSV, Database Development, Development, Scripting, Software Development, SQL, SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 7 | Leave a Comment »
Posted by jpluimers on 2012/04/25
I’ve been really busy lately with a couple of migration projects, so I totally missed the various SQL Server 2012 is Generally Available! announcements.
Which is important because for one client, this opens the possibility of move from SQL Server 2000 right to SQL Server 2012 in stead of SQL Server 2008 R2 and make a more smooth path for Azure.
–jeroen
via:
Posted in Database Development, Development, SQL Server, SQL Server 2000, SQL Server 2008 R2, SQL Server 2012 | Leave a Comment »
Posted by jpluimers on 2012/04/24
While transitioning from SQL Server 2000 to 2008, I recently had the “A severe error occurred on the current command. The results, if any, should be discarded.” occurring on SQL Server 2000 in the form as shown at the bottom of this message.
Many of the search results point you into the area of atabase corruption, or in using NVARCAR parameters with SQL Server 2000 or SQL Server 2005 (the app didn’t use NVARCAR, nor did it use large VARCHAR parameters).
The cool thing on the SQL Server Forums – System.Data.SqlClient.SqlException: A severe error occurred on the current command post was that it summed up causes, and asked for more:
Posted – 06/17/2004 : 15:05:20
Rashid writes “Hi: Gurus I am getting these errors when I try to execute my application. According to MS knowledge base (http://support.microsoft.com/default.aspx?scid=kb;en-us;827366) these errors happen due to following resons
- You use a SqlClient class in a Finalize method or in a C# destructor.
- You do not specify an explicit SQLDbType enumeration when you create a SqlParameter object. When you do not specify an explicit SQLDbType, the Microsoft .NET Framework Data Provider for SQL Server (SqlClient) tries to select the correct SQLDbType based on the data that is passed. SqlClient is not successful.
- The size of the parameter that you explicitly specify in the .NET Framework code is more than the maximum size that you can use for the data type in Microsoft SQL Server.
None of these are true in my case. Are there any other reasons that can cause these problems..
There is one more: sending huge SQL Statements to your SQL Server is always a bad idea and gives this error too. Read the rest of this entry »
Posted in .NET, C#, C# 2.0, C# 3.0, C# 4.0, Database Development, Development, Encoding, Software Development, SQL Server, SQL Server 2000, SQL Server 2008 R2, Unicode | Leave a Comment »
Posted by jpluimers on 2012/04/17
If you followed my blog, you probably already guessed that I’m assisting a client to prepare for a big SQL Server 2000 to SQL Server 2008 R2 migration. When not, you know now :)
I’m a fan of commandline, tools, and preconfigured settings. Which means that I’m in the midst of reconfiguring all my shortcuts to SQL Server 2000 tools to SQL Server 2008 equivalents.
When inspecting and changing LNK shortcut files, two tools are important:
- dumplnk: dumps a shortcut lnk file from the commandline
- shurtcut: creates a shortcut lnk file from the commandline
One of tools I many shortcuts for is the ISQLW aka SQL Query Analyzer, which I use far more than the SQL Server Enterprise Manager (more on SSEM in a future post).
Depending on the mode of authentication you use to connect to your SQL Server, there basically are two forms of shortcuts:
- SQL Server Authentication (using SQL Server username and password):
"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\isqlw.exe" /S"servername[\instancename]" /U"username" /P"password"
- Windows Authentication (using the credentials of the currently logged in windows user):
"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\isqlw.exe" /S"servername[\instancename]" /E
The former is less secure (so better to only store those shortcuts in a place that no other users can access).
From the ISQLW.exe syntax documentation:
Syntax
isqlw
[-?] |
[
[-S server_name[\instance_name]]
[-d database]
[-E] [-U user] [-P password]
[{-i input_file} {-o output_file} [-F {U|A|O}]]
[-f file_list]
[-C configuration_file]
[-D scripts_directory]
[-T template_directory]
]
The parameters /S, /U, /P and /E are very similar to the -S, -U, -P and -E ones from SSMS.exe (SQL Server Management Studio) with one distinction: there MUST be a space between each parameter and the value:
- SQL Server Management Studio (using SQL Server username and password):
"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe" -S "servername[instancename]" -U "username" -P "password"
- SQL ServerManagement Studio (using credentials for the currently logged in windows user):
"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe" -S "servername[instancename]" -E
Read the rest of this entry »
Posted in Batch-Files, Database Development, Development, Power User, Scripting, Software Development, SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 | 5 Comments »
The Wiert Corner - irregular stream of stuff 