Archive for the ‘Database Development’ Category
Posted by jpluimers on 2013/08/20
Just found out that the Firebird gbak tool for backup/restore, has some interesting parameters.
This is what pointed me to id: Firebird News » Firebird SQL’s backup/restore uses magic words for stdin and stdout – Check the gbak manual update.
Just read the below quotes from the documentation.
Gbak Caveats:
Use of ‘stdin’ or ‘stdout’ File Names
Gbak recognizes the literal strings ‘stdin’ and ‘stdout’ as source or destination filenames. In POSIX systems, when the standard input and/or standard output channels are used, it is not permitted to execute seek operations on these channels. Using ‘stdin’ or ‘stdout’ as file names with gbak will force gbak to use processing that will not seek on the input or output channels, making them suitable for use in pipes
Backup & Restore Recipes:
Create a Database Clone Without a Dump File.
You may use gbak to create a clone of a database, on the same server, without needing to create a potentially large dump file. To do this, you pipe the output of a gbak backup directly to the input of a gbak restore, as follows.
tux> # Clone a test database to the same server, without requiring a dump file.
tux> gbak -backup emptest stdout | gbak -replace stdin emptest_2
–jeroen
via: Firebird News » Firebird SQL’s backup/restore uses magic words for stdin and stdout – Check the gbak manual update.
Posted in Database Development, Development, Firebird | Leave a Comment »
Posted by jpluimers on 2013/07/17
I still see a truckload of projects that connect to SQL Server do this using OLE DB or the Native SQL Client.
All OLE DB access to SQL Server has been deprecated, not only from regular access, but also from SSIS.
These are the only ways you should connect to SQL Server:
- SqlClient (managed code)
- JDBC (Java)
- ODBC (for native code)
For instance, these have been deprecated (for each one, I linked to the oldest SQL Server version where they were made available for):
- SQLOLEDB
- SQLNCLI
- SQLNCLI10
- SQLNCLI11
- SQLXMLOLEDB.3.0
- SQLXMLOLEDB.4.0
–jeroen
via: RIP OLE DB.
Posted in Database Development, Development, SQL Server | Leave a Comment »
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 2013/01/18
In most database index nodes are doubly linked to allow bi-directional scans. http://is.gd/8CMb7w, however not for InterBase and FireBird, there the reverse link isn’t used because it can be inconsistent due to write order of index pages.
The result is that in Firebird and InterBase, indexes are single-directional (either ascending or descending).
This is for your safety: it guarantees index consistency, even if because of EMP, your machine suddenly reboots after your tank fired a missile.
–jeroen
via Twitter / Avalanche1979: @SQLPerfTips For Firebird the ….
(Wow, did I really wrote 1200 blog posts?)
Posted in Database Development, DB2, Development, Firebird, InterBase, MySQL, OracleDB, PostgreSQL, 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 2013/01/17
Too bad the new StackOverflow rules actively discourage the kind of open questions like “c# – What are your thoughts on Raven DB?“.
They are being closed as “not constructive by casperOne♦ Nov 29 ’11 at 5:25”, which is a shame as these kinds of questions often reveal very valuable and balanced answers.
Right now I’m resarching what RavenDB could mean for storing documents. And yes, I know about the RavenDB licensing model: free for open source, pay for commercial use.
–jeroen
via: c# – What are your thoughts on Raven DB? – Stack Overflow.
Posted in .NET, C#, C# 4.0, C# 5.0, Database Development, Development, Pingback, Software Development, Stackoverflow | Leave a Comment »
Posted by jpluimers on 2013/01/02
On development machines it can be comfortable to add the local Administrators group to SQL Server, and make them equivalent to SA (SQL Server Administrator).
This especially as SA is disabled by default when you install using Windows Authentication mode (which is the default). You can Change Server Authentication Mode to include SQL Server mode, but then you still have to enable SA (you can even rename SA)
This is basically what you want to do:
CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE=[master];
EXEC master..sp_addsrvrolemember @loginame = N'BUILTIN\Administrators', @rolename = N'sysadmin';
GRANT CONTROL SERVER TO [BUILTIN\Administrators];
There are a few gotchas here:
- The name of the group BUILTIN\Administrators depends on the localization of your system.
This is one of the reasons I usually advise clients to have server systems run on the most common ENU (English USA) localization of Windows.
Another reason is that it is far easier to find information ENU English Messages back on the internet.
- You need to be SQL Server Administrator to begin with.
There is a little trick to get this done: you can stop the SQL Server service, then restart SQL Server it in single-user mode.
In single-user mode, members from the BUILTIN\Administrators group can connect as a member of the sysadmin fixed server role.
- If you want to access SQL Server as member from BUILTIN\Administrators, you need to run your SQL client tools with the UAC elevated permission (otherwise the Administrative bit is not set, and the BUILTIN\Administrators is not recognized).
That’s what the batch file below solves.
You need to start it as member of BUILTIN\Administrators with elevated privilege (the easiest way is to run it from an elevated command prompt).
It will: Read the rest of this entry »
Posted in Batch-Files, Database Development, Development, Scripting, Software Development, SQL Server, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 | Leave a Comment »
Posted by jpluimers on 2012/12/25
In SQL Server 2000 and up, the easiest way to re-add a user that got orphaned is with a script like below.
The script does not correct the SID, but in stead sets new permissions (in this case, db_datareader and db_datawriter). Most of the times that is not a problem.
The script uses these stored procedures:
- sp_dropuser – drops a user from the current database
- sp_droplogin – drops a login from the current server
- sp_addlogin – adds a login to the database server
- sp_adduser – adds a user to the current database (you can add both a SQL user – with name and password – and a Windows user)
- sp_addrolemember – adds a member to a certain role
More modern versions have alternatives to these stored procedures, but the stored procedures work with the widest ranges of SQL Server versions.
-- Execute this script as SA or DB Administrator
use MyDatabase -- the databae where you want the user to be re-added to
-- you cannot perform a 'use [MyDatabase]' without destroying the context (and declared variables)
-- http://stackoverflow.com/questions/9165513/sql-server-change-current-database-via-variable
-- it is possible with exec (@exec_stmt) (as sp_droplogin does it), but it is a bit cumbersome for a relatively simple script
declare @loginname sysname
declare @passwd sysname
set @loginname = 'MyUser'
set @passwd = 'MyPassword'
-- from current database
exec sp_dropuser @loginname
-- from server
exec sp_droplogin @loginname
-- to current server
exec sp_addlogin @loginname, @passwd
-- to current database
exec sp_adduser @loginname
-- roles to add the user to
exec sp_addrolemember db_datareader, @loginname
exec sp_addrolemember db_datawriter, @loginname
If the user didn’t exist in the database, or didn’t exist as a login on the server, you can get two errors like these: Read the rest of this entry »
Posted in Database Development, Development, 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/12/21
Interesting; is on my research list to see if the installation process gets easier and more standardized.
SQL Server FineBuild Introduction
FineBuild provides 1-click install and best-practice configuration of SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, and SQL Server 2005.
–jeroen
via: SQL Server FineBuild.
Posted in Database Development, Development, Power User, Reporting Services, SQL Server, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, Windows, Windows 7, Windows 8, Windows Server 2003, Windows Server 2003 R2, Windows Server 2008, Windows Server 2008 R2, Windows Vista, Windows XP | Leave a Comment »
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/11/27
I could only find the System Error Messages overview for:
Are there any such links for SQL Server 2005 and up?
The odd thing is online lists of Database Engine Error Severities are available for multiple versions of SQL Server: 2005, 2008 and 2008 R2.
–jeroen
Posted in Database Development, Development, SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 7 | 1 Comment »
The Wiert Corner - irregular stream of stuff 