Archive for the ‘Database Development’ Category
Posted by jpluimers on 2011/04/28
when developing embedded Windows software, SQL Express can be a good option.
However, embedded usually means you can’t install a big tool-set (like SQL Server Management Studio or SQL Server Profiler) on the target system.
So if you want to monitor or profile it, you need remote access to your SQL Express instance (usually SQLEXPRESS).
The How to: Configure Express to accept remote connections – SQL Server Express WebLog article on the SQL Server Express WebLog and the KB article How to configure SQL Server 2005 to allow remote connections explain the details.
This is a short summary:
- Enable TCP/IP using the SQL Server Configuration Manager
(you can do this using the registry too)
- Enable the SQL Browser using the SQL Server Configuration Manager
- Open your firewall for the TCP ports (more difficult) or SQL Server process (easier)
- Enable mixed mode login authentication from the registry:
HKLM/Software/Microsoft/MSSQLServer/MSSQLServer/LoginMode = 1 integrated security, 2 mixed.
- Make sure that SA can login, and give it a strong password using this in SQLCMD:
ALTER LOGIN sa ENABLE
ALTER LOGIN sa WITH PASSWORD=’Som3StrongP@assword’
- Restart the SQL Server Service
- Start the SQL Browser Service
Finally connect to your SQL Express server by using SQLCMD:
SQLCMD –S machinename\SQLEXPRESS –Usa –PSom3StrongP@assword
–jeroen
Posted in Database Development, Development, SQL Server | Leave a Comment »
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 »
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 »
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 »
Posted by jpluimers on 2010/10/28
I always forget the syntax, because different DBMS systems use different ways of doing this.
This is the SQL Server way of doing some date calculations: Read the rest of this entry »
Posted in Database Development, Development, SQL Server | Leave a Comment »
Posted by jpluimers on 2010/10/20
When running different SQL server versions and editions, you often want to know what a certain instance is about.
For that, SQL Server T-SQL provides [WayBack] ServerProperty.
Since I absolutely love [WayBack] sqlcmd, I extended my set of SQL Server batch files with this one showing you some of the [WayBack] ServerProperty values: Read the rest of this entry »
Posted in Database Development, Development, SQL Server | Leave a Comment »
Posted by jpluimers on 2010/10/19
Recently, I had the error message “Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done” occur to me when connecting through the “Microsoft OLE DB Provider for SQL Server”.
I got the below message (in this case in Delphi, but I have seen lots of other people having the same issue using other environments too): Read the rest of this entry »
Posted in Database Development, Delphi, Development, Software Development, SQL Server | 5 Comments »
Posted by jpluimers on 2010/09/21
When restoring a backup made in SQL Server 2005 to a SQL Server 2008 environment, you don’t see the underlying upgrade of the on-disk physical version in the UI (you only see the percentage completion moving from 0% to 100%).
When performing the same on the command-line you can actually follow the physical version gets updated:
C:\SCRIPT>"%sqlcmd%" -S . -E -Q "RESTORE DATABASE [MyDBA] FROM DISK = N'C:\BACKUP\MyDB.bak' WITH REPLACE, STATS = 10, MOVE N'MyDB' to N'C:\DATA\MyDB.mdf', MOVE N'MyDB_log' to N'C:\DATA\MyDB_log.ldf' ;"
19 percent processed.
29 percent processed.
39 percent processed.
49 percent processed.
59 percent processed.
69 percent processed.
78 percent processed.
88 percent processed.
98 percent processed.
100 percent processed.
Processed 1296 pages for database 'MyDBA', file 'MyDB' on file 1.
Processed 2 pages for database 'MyDBA', file 'MyDB_log' on file 1.
Converting database 'MyDBA' from version 611 to the current version 655.
Database 'MyDBA' running the upgrade step from version 611 to version 621.
Database 'MyDBA' running the upgrade step from version 621 to version 622.
Database 'MyDBA' running the upgrade step from version 622 to version 625.
Database 'MyDBA' running the upgrade step from version 625 to version 626.
Database 'MyDBA' running the upgrade step from version 626 to version 627.
Database 'MyDBA' running the upgrade step from version 627 to version 628.
Database 'MyDBA' running the upgrade step from version 628 to version 629.
Database 'MyDBA' running the upgrade step from version 629 to version 630.
Database 'MyDBA' running the upgrade step from version 630 to version 631.
Database 'MyDBA' running the upgrade step from version 631 to version 632.
Database 'MyDBA' running the upgrade step from version 632 to version 633.
Database 'MyDBA' running the upgrade step from version 633 to version 634.
Database 'MyDBA' running the upgrade step from version 634 to version 635.
Database 'MyDBA' running the upgrade step from version 635 to version 636.
Database 'MyDBA' running the upgrade step from version 636 to version 637.
Database 'MyDBA' running the upgrade step from version 637 to version 638.
Database 'MyDBA' running the upgrade step from version 638 to version 639.
Database 'MyDBA' running the upgrade step from version 639 to version 640.
Database 'MyDBA' running the upgrade step from version 640 to version 641.
Database 'MyDBA' running the upgrade step from version 641 to version 642.
Database 'MyDBA' running the upgrade step from version 642 to version 643.
Database 'MyDBA' running the upgrade step from version 643 to version 644.
Database 'MyDBA' running the upgrade step from version 644 to version 645.
Database 'MyDBA' running the upgrade step from version 645 to version 646.
Database 'MyDBA' running the upgrade step from version 646 to version 647.
Database 'MyDBA' running the upgrade step from version 647 to version 648.
Database 'MyDBA' running the upgrade step from version 648 to version 649.
Database 'MyDBA' running the upgrade step from version 649 to version 650.
Database 'MyDBA' running the upgrade step from version 650 to version 651.
Database 'MyDBA' running the upgrade step from version 651 to version 652.
Database 'MyDBA' running the upgrade step from version 652 to version 653.
Database 'MyDBA' running the upgrade step from version 653 to version 654.
Database 'MyDBA' running the upgrade step from version 654 to version 655.
RESTORE DATABASE successfully processed 1298 pages in 0.738 seconds (13.736 MB/sec).
Ain’t command-lines cool :-)
A few notes on version numbers:
655 – 2008
661 – 2008 R2
http://www.sqlskills.com/BLOGS/PAUL/post/Search-Engine-QA-13-Difference-between-database-version-and-database-compatibility-level.aspx
http://www.sqlskills.com/BLOGS/PAUL/post/SQL-Server-2008-R2-bumps-the-database-version.aspx
http://serverfault.com/questions/58039/sql-server-2008-2008r2-backup-compatibility
http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/f8f8db38-3c20-45e4-ae8b-4cc097eb7f0d
–jeroen
Posted in CommandLine, Database Development, Development, Software Development, SQL Server | Leave a Comment »
Posted by jpluimers on 2010/09/15
SQL Server used to support the “ALTER TABLE TableName ALTER COLUMN ColumnName DROP DEFAULT”:
In the current Books Online version for the SQL Server 2005 version of ALTER TABLE documentation, the “DROP DEFAULT” has been disappeared.
In the SQL Server 2005 documentation, Microsoft already indicated that the “DROP DEFALT” would be removed in a future version.
I was surprised it already had disappeared in SQL Server 2005 Service Pack 3, especially since it is documented to be removed after SQL Server 2008 R2.
By removing this feature, SQL Server is moving further away from the SQL-92 standard.
Anyway: When you try to perform a ALTER TABLE TableName ALTER COLUMN ColumnName DROP DEFAULT, now you get this error message:
Incorrect syntax near the keyword ‘DEFAULT’.
Below is how I approached towards a solution. Read the rest of this entry »
Posted in Database Development, Development, SQL Server | Leave a Comment »
Posted by jpluimers on 2010/09/07
When searching on MSDN for SQL Server syntax related things, you often get results matching other languages or frameworks.
For instance, searching for INSERT site:msdn.microsoft.com gets other results having to do with the .NET Framework. Those usually are not relevant to SQL Server.
Google search can be tweaked to limit your search results: there is a nice Google search help page on this.
The above search phrase already includes a the site: prefix to limit the results to the msdn.microsoft.com domain.
There are a few tricks to limit the search phrase even further:
- Add “SQL Server” to your search phrase:
“SQL Server” INSERT site:msdn.microsoft.com
Microsoft always had the “SQL Server” in their Books Online topics.
This way, you will find SQL Server 2000 and SQL Server Compact Edition information now as well as more current SQL Server versions.
- Add “Transact-SQL” to your search phrase:
“Transact-SQL” INSERT site:msdn.microsoft.com
Microsoft started to suffix SQL Server T-SQL keywords with “(Transact-SQL)” for the Books Online in November 2008 (covering SQL Server 2005) to make finding results easier.
This way you will favour topics for SQL Server 2005 and up.
Luckily the MSDN site has done a lot of SEO, so even if you don’t add these two to your search phrase, SQL Server relevant results end up pretty high in the result list.
They strive to get the SQL Server 2008 R2 Books Online topics as high in the ranking as possible.
If you want to search for specific versions of SQL Server, then it is easiest to use this Microsoft SQL Server Library page to begin your search.
Conclusion:
Depending on what kind of search results you are interested in, you might want to extend your search phrase a bit.
–jeroen
Posted in Database Development, Development, Google, Power User, SQL Server | Leave a Comment »
The Wiert Corner - irregular stream of stuff 