Archive for the ‘SQL’ Category
Posted by jpluimers on 2013/02/12
This is way cool, and has been there for a long time, and I completely missed it until recently (:
On the Stack Exchange Data Explorer, you can write your own queries for any of the StackExchange sites as they share a common database infrastructure.
The queries can even contain an execution plan, and given the large number of questions (the total of Questions (table Posts) is total over 10 million now: select count(*) as QuestionCount from Posts as Questions).
There are many examples, for instance this one by sam.saffron and TLama that lists posts outside the Delphi area: Read the rest of this entry »
Posted in Database Development, Delphi, Development, SQL, SQL Server | 2 Comments »
Posted by jpluimers on 2012/11/29
Great post: SQL Server: JOIN vs IN vs EXISTS – the logical difference.
Be aware of the three valued logic when NULL gets involved.
EXIST comes closes to what you expect.
And be aware that with IN, you cannod pass one parameter containing more than 1 IN value: sql server 2005 – Passing multiple values for one SQL parameter – Stack Overflow..
–jeroen
Posted in Database Development, 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/09/26
Database Workbench is my tool of choice for doing database work: it supports many backends in a consistent manner, and behaves a lot like Delphi (like running and debugging stored procedures).
Yesterday the free Lite Editions of Version 4.3.1 got released:
Database Workbench 4.3.1 free Lite Editions released
This new release of Database Workbench brings new features and enhancements, as requested by our users.
The free Lite Editions are now available.
More information about Database Workbench is available at the Database Workbench page, download your copy today via our downloads page, pricing information is available, the limited Lite Editions are available for free.
Changes in this release
- The full details and list of changes are available here and here.
- New
- MySQL Stored Procedure, Function and Trigger Debugging (Pro Edition only)
- InterBase and Firebird syntax check in Trigger Editor (Pro Edition only)
- Incremental search of data in SQL, Table and View Editor
Changes
- More compact taskbar
- MySQL support for BINARY and VARBINARY datatypes
- MySQL error fixed when not having access to mysql.procs
- MySQL fix for fetching foreign key information
- Data Import and Export fixes
- Windows 7 event log error by SideBySide fixed
And much more…
–jeroen
via News @ Upscene Productions.
Posted in Database Development, Delphi, Development, Firebird, InterBase, MySQL, OracleDB, PostgreSQL, Software Development, SQL, SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 7 | 1 Comment »
Posted by jpluimers on 2012/09/12
Every once in a while I need something like this, and I always forget the best way to do it: pad numbers so they start with leading zeros so they appear fixed with.
The SQLAuthority phrases it:
There is no ready made function in SQL Server which offers this solution but we can quickly write up something very simple.
SQLUSA has some more ways to do it, and all basically come down to this:
- Convert your number to VARCHAR (use CAST or CONVERT) .
- Prepend the converted number with the maximum number of zeros you require (either with a fixed length string like
'00000000', or use the REPLICATE function).
- Take the right most characters of the required length using the RIGHT function.
You can do similar things with LEFT and padding (for instance with spaces to the right).
One example:
SELECT RIGHT('0000000000' + CAST(31415 AS VARCHAR(10)), 10) AS PaddedPiInteger
SELECT LEFT(CAST(31415 AS VARCHAR(10)) + ' ', 10) AS PaddedPiInteger
–jeroen
via SQL SERVER – Pad Ride Side of Number with 0 – Fixed Width Number Display « SQL Server Journey with SQL Authority.
Posted in Database Development, Development, SQL, SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2012, SQL Server 7 | Leave a Comment »
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/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/02/24
I’m in the midst of a big migration traject.
One class of systems involved in the migration is SQL Server 2000 databases (yes, I know that SQL Server 2000 has been in Extended Support mode since April 8, 2008, that’s one of the grounds for migration).
Sometimes, you need SQL Server Enterprise Manager (a plugin to the Microsoft Management Console) to work on those databases to see what needs to be migrated (yes, this should be in the documentation, of which the ultimate form is the system itself ;-).
SQL Server Enterprise Manager has a nice feature that you can group SQL Server registration into SQL Server Groups.
However, it also has a limitation: you cannot add the same SQL Server to multiple SQL Server Groups. That is: unless you create an alias on the SQL Server, as aliases are not counted in the “unique” criterion.
Below are the top 2 links I found using “multiple databases” “sql server” “enterprise manager”. Since I’m not sure the content of those will last, below are the full quotes.
Besides adding a SQL Server Alias (which has other uses as well), the most important steps are “6” in the first quote, and “1.6” in the second quote: your alias needs to point to the same IP address (or hostname) as your regular server.
How do I register multiple databases with MS SQL Enterprise Manager?
MS Enterprise Manager does not allow you to register the same database server multiple times. It is sometimes useful to create multiple registrations, if you want to connect to different databases on the same server. To workaround this, follow the steps below.
- Go to Start – Programs – Microsoft SQL Server – Select “SQL Server Client Network Utility”
- Go to Alias Tab
- Click “Add”
- In the “Server alias:” text box, enter a name you want to use to distinguish this database server
- In the “Network libraries:” section, select “TCP/IP”
- In the “Connection parameters” section, change the “Server name:” to your database server, i.e.; mssql01.discountasp.net
- Click OK
Once the Alias is created, you can register it using the same method described in this Knowledge Base article.
How can I open multiple databases in SQL Server Enterprise Manager?
- Create SQL Server Alias
(Please repeat the following steps if you want to open mutiple databases)
- Open SQL Server Client Network Utility.
- When Client Network Utility opens, click on the Alias tab.
- Click on the Add button.
- In the Server Alias text box enter a name for your SQL connection.
- Select TCP/IP under Network libraries
- Enter the IP address of the Server your SQL database is located on, in the Server name text box.
- Click on the OK button.
- Your Server alias will now be configured, click on the Apply button.
- Click on the OK button.
- Create SQL Server Connection in Enterprise Manager
(Please repeat following steps if you want to open mutiple databases)
- Open SQL Server Enterprise Manager.
- When Enterprise Manager opens, right click SQL Server Group and select New SQL Server Registration.
- The Register SQL Server wizard will now open.
- Click on the Next button.
- From the Available Servers drop down menu, select the server alias you created earlier.
- Click on the Add button.
- The server should now be listed under Added servers, click on the Next button.
- When the next dialog box appears select The SQL Server login information that was assigned to me by the system administrator see above.
- Click on the Next button.
- Enter your Login name and Password ? this will be used every time you connect to your SQL server.
- Click on the Next button.
- Ensure that Add the SQL Servers to an existing SQL Server group is selected and SQL Server Group is selected for Group name.
- Click on the Next button.
- When the next dialog box opens you can click on the Finish button.
- Your SQL Connection will now be created.
–jeroen
via:
Posted in Database Development, Development, SQL, SQL Server, SQL Server 2000 | 1 Comment »
Posted by jpluimers on 2012/01/12
When moving databases across servers, you face the same problem as when upgrading servers: users are server specific, but permissions are databases specific. They are not bound by UserName, but through a SID (security identifier).
When adding the same UserName entries to a new server that already has the databases restored, you get error messages like these:
Error 21002 [SQL-DMO] User ‘account’ already exists
When adding the same UserName entries, then restoring the databases, it won’t work, because the matching SIDs don’t exist.
There are many sites explaining the “Error 21002” and pointing to sp_change_users_login.
But sp_change_users_login (and the ALTER USER UserName WITH LOGINI = UserName2 in SQL Server 2005 and higher) is not the actual fix to the problem: it will re-add the user with a new SID, then correct the SID in the database currently in use.
Actually you are after “Orphaned” users: users that are defined in the various databases, but not present in the user list on the SQL Server instance. Running sp_change_users_login with the “Report’ parameter on the “new” server will show a list of orphaned users.
An even better way to show Orphaned Users is by running the ShowOrphanUsers script (thanks Vyas, wish more people republish your stuff with attribution!). In my own version (sourcecode is below), I have added an extra UserSID column of type varbinary(85).
In order to transfer users to a new server, you need a sp_help_revlogin stored procedure. Depending on the version of your SQL Server (7/2000/2005/2008), you need a slightly different version of a script that creates sp_help_revlogin for you. All versions are available at NetNerds.net.
–jeroen
via: Migrate/Transfer SQL Server 2008/2005/2000/7 Logins to SQL Server 2008.
USE master;
GO
IF OBJECT_ID ('dbo.ShowOrphanUsers') IS NOT NULL
DROP PROCEDURE dbo.ShowOrphanUsers
GO
CREATE PROC dbo.ShowOrphanUsers
AS
BEGIN
CREATE TABLE #Results
(
[Database Name] sysname COLLATE Latin1_General_CI_AS,
[Orphaned User] sysname COLLATE Latin1_General_CI_AS,
[UserSID] varbinary(85)
)
SET NOCOUNT ON
DECLARE @DBName sysname, @Qry nvarchar(4000)
SET @Qry = ''
SET @DBName = ''
WHILE @DBName IS NOT NULL
BEGIN
SET @DBName =
(
SELECT MIN(name)
FROM master..sysdatabases
WHERE name NOT IN
(
'master', 'model', 'tempdb', 'msdb',
'distribution', 'pubs', 'northwind'
)
AND DATABASEPROPERTY(name, 'IsOffline') = 0
AND DATABASEPROPERTY(name, 'IsSuspect') = 0
AND name > @DBName
)
IF @DBName IS NULL BREAK
SET @Qry = ' SELECT ''' + @DBName + ''' AS [Database Name],
CAST(name AS sysname) COLLATE Latin1_General_CI_AS AS [Orphaned User],
SID AS [UserSID]
FROM ' + QUOTENAME(@DBName) + '..sysusers su
WHERE su.islogin = 1
AND su.name <> ''guest''
AND NOT EXISTS
(
SELECT 1
FROM master..sysxlogins sl
WHERE su.sid = sl.sid
)'
INSERT INTO #Results EXEC (@Qry)
END
SELECT *
FROM #Results
ORDER BY [Database Name], [Orphaned User]
END
GO
Posted in Database Development, Development, SQL, SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 7 | 1 Comment »
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:
- Select a cell
- Press Ctrl-A to select all rows
- 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 »
The Wiert Corner - irregular stream of stuff 