Archive for the ‘Database Development’ Category
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:
The ProviderManifestToken is usually at this place:
/edmx:Edmx/edmx:Runtime/edmx:StorageModels/Schema/@ProviderManifestToken
Note this is not a full XPath query to it (as you’d have to add the namespaces see also namespaces in XPath), that would be the one below this post’s signature.
From the ProviderManifestToken documentation:
ProviderManifestToken Schema Attribute
ProviderManifestToken is a required attribute of the Schema element in SSDL. This token is used to load the provider manifest for offline scenarios. For more information about ProviderManifestToken attribute, see Schema Element (SSDL).
SqlClient can be used as a data provider for different versions of SQL Server. These versions have different capabilities. For example, SQL Server 2000 does not support varchar(max) and nvarchar(max) types that were introduced with SQL Server 2005.SqlClient produces and accepts the following provider manifest tokens for different versions of SQL Server.
- “2000″ for SQL Server 2000
- “2005″ for SQL Server 2005
- “2008″ for SQL Server 2008
Note: Starting with Visual Studio 2010, the Entity Data Model Tools do not support SQL Server 2000.
Finally, with SQL Server 2000, be sure to set multipleactiveresultsets=false in your connectionstrings to disable MARS (Multiple Active Result Sets) which SQL Server 2000 does not support.
–jeroen
via: SqlClient for the Entity Framework.
Full XPath query would either have you to add namespaces using an XmlNameSpaceManager instance, or use the full namespace urls like here:
//*[namespace-uri()='http://schemas.microsoft.com/ado/2009/02/edm/ssdl']/@ProviderManifestToken
or more precisely
/*[local-name()='Edmx' and namespace-uri()='http://schemas.microsoft.com/ado/2008/10/edmx']/*[local-name()='Runtime' and namespace-uri()='http://schemas.microsoft.com/ado/2008/10/edmx']/*[local-name()='StorageModels' and namespace-uri()='http://schemas.microsoft.com/ado/2008/10/edmx']/*[local-name()='Schema' and namespace-uri()='http://schemas.microsoft.com/ado/2009/02/edm/ssdl']/@ProviderManifestToken
or on multiple lines:
[sourececode language='text']
/*[local-name()='Edmx' and namespace-uri()='http://schemas.microsoft.com/ado/2008/10/edmx']
/*[local-name()='Runtime' and namespace-uri()='http://schemas.microsoft.com/ado/2008/10/edmx']
/*[local-name()='StorageModels' and namespace-uri()='http://schemas.microsoft.com/ado/2008/10/edmx']
/*[local-name()='Schema' and namespace-uri()='http://schemas.microsoft.com/ado/2009/02/edm/ssdl']
/@ProviderManifestToken
[/sourcecode]
for this .edmx file (hoping that WordPress doesn’t mess with the xml as it usually does).
[sourececode language='xml']
[/sourcecode]
Yup, it did it again; please see this file for the XML.
–EOM–
Like this:
Be the first to like this post.
Posted in .NET, 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 »
Like this:
Be the first to like this post.
Posted in .NET, Batch-Files, 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:
Like this:
Be the first to like this post.
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 »
Like this:
Be the first to like this post.
Posted in .NET, C#, C# 2.0, C# 3.0, C# 4.0, Database Development, Development, Software Development, SQL Server, SQL Server 2000, SQL Server 2008 R2 | 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 »
Like this:
Be the first to like this post.
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 | 3 Comments »
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:
Like this:
Be the first to like this post.
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. 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
Like this:
Be the first to like this post.
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 2011/12/08
I recently wrote this script for like the upteenth time, so now it is on bo.codeplex.com and in my BIN directory on Windows and in my sh directory on my Mac.
Note for the scripts in this post:
Windows
restart-InterBase.cmd:
net stop IBG_gds_db
net stop IBS_gds_db
net start IBG_gds_db
net start IBS_gds_db
It restarts only the default instance.
Usually it is enough to restart the Guarduan (IBG_gds_db), but sometimes that hangs, so I restart both the Guardian and the DB service (IBS_gds_db).
You can do the same with Firebird of course, and adapt for non-default instances: just find the right service names using a script like this:
sc query
then search the output for entries matching InterBase or Firebird like these:
SERVICE_NAME: IBG_gds_db
DISPLAY_NAME: InterBase XE Guardian gds_db
TYPE : 10 WIN32_OWN_PROCESS
STATE : 4 RUNNING
(STOPPABLE, NOT_PAUSABLE, IGNORES_SHUTDOWN)
WIN32_EXIT_CODE : 0 (0x0)
SERVICE_EXIT_CODE : 0 (0x0)
CHECKPOINT : 0x0
WAIT_HINT : 0x0
SERVICE_NAME: IBS_gds_db
DISPLAY_NAME: InterBase XE Server gds_db
TYPE : 10 WIN32_OWN_PROCESS
STATE : 4 RUNNING
(STOPPABLE, NOT_PAUSABLE, IGNORES_SHUTDOWN)
WIN32_EXIT_CODE : 0 (0x0)
SERVICE_EXIT_CODE : 0 (0x0)
CHECKPOINT : 0x0
WAIT_HINT : 0x0
Mac OS X
You can find the name of your Interbase service when you look at the user-installed services:
bash-3.2$ cd /Library/StartupItems/InterBase_gds_db/
bash-3.2$ ls -l
total 16
-rwxr-xr-x 1 root wheel 636 Oct 21 15:44 InterBase_gds_db
-rwxr-xr-x 1 root wheel 204 Sep 14 2007 StartupParameters.plist
bash-3.2$ cat StartupParameters.plist
{
Description = "InterBase Server";
Provides = ("InterBase Database Server");
Requires = ("DirectoryServices");
Uses = ("Disks", "NFS");
OrderPreference = "None";
}
bash-3.2$
You can restart the InterBase Database Server using this command:
sudo SystemStarter -vdD restart "InterBase Database Server"
The SystemStarter -vdD parameters make the output add verbose, debugging information and dependencies.
–jeroen
Like this:
Be the first to like this post.
Posted in Database Development, Development, InterBase | Leave a Comment »
Posted by jpluimers on 2011/12/07
Usually getting queries right with SQL Server comes down to handling NULL correctly.
For instance, on this table:
MYTABLE
| ID |
LAST_NAME |
| 6 |
COUNT(*) |
| 1 |
FOO |
| 2 |
BAR |
| 3 |
FOO |
| 4 |
**NULL** |
| 5 |
BAR |
| 6 |
FOO |
What are the results of these SELECT statements
SELECT COUNT (*)
FROM MYTABLE
SELECT COUNT (*)
FROM MYTABLE
WHERE (LAST_NAME = 'FOO')
SELECT COUNT (*)
FROM MYTABLE
WHERE (LAST_NAME <> 'FOO')
SELECT COUNT (*)
FROM MYTABLE
WHERE NOT (LAST_NAME = 'FOO')
You might think they are like these, as LASTNAME <> ‘FOO’ looks like the inverse of LASTNAME = ‘FOO’:
But in fact the results are these:
When you look closely to the SQL statements below, you will see that the inverse of EQUALS contains an IS NULL, the same for the inverse of NOT EQUALS:
SELECT COUNT (*)
FROM MYTABLE
-- inverse of NOT EQUALS
SELECT COUNT (*)
FROM MYTABLE
WHERE (LAST_NAME = 'FOO') OR (LAST_NAME IS NULL)
SELECT COUNT (*)
FROM MYTABLE
WHERE (LAST_NAME <> 'FOO')
SELECT COUNT (*)
FROM MYTABLE
WHERE (LAST_NAME = 'FOO')
-- inverse of EQUALS
SELECT COUNT (*)
FROM MYTABLE
WHERE (LAST_NAME <> 'FOO') OR (LAST_NAME IS NULL)
-- inverse of EQUALS
SELECT COUNT (*)
FROM MYTABLE
WHERE (NOT (LAST_NAME = 'FOO')) OR (LAST_NAME IS NULL)
Lesson learned: always take into account NULL when trying to formulate your SQL comparisons well.
–jeroen
Like this:
Be the first to like this post.
Posted in Database Development, Development, SQL Server, SQL Server 2000, SQL Server 2008 | Leave a Comment »
Posted by jpluimers on 2011/11/29
A co worker at a client had trouble importing textual date strings into Microsoft access.
Since many databases like yyyy-mm-dd as a universal import date format, I suggested trying that and searching for some backing on the Internet.
Indeed this StackOverflow answer confirmed my gues.
–jeroen
Via: vba – Parse data and time string to Access Date value – Stack Overflow.
Like this:
Be the first to like this post.
Posted in Access, Database Development, Development, Software Development | Leave a 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.
Like this:
Be the first to like this post.
Posted in Database Development, Development, SQL, SQL Server, SQL Server 2000 | Leave a Comment »
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
Like this:
Be the first to like this post.
Posted in .NET, C#, C# 2.0, Database Development, Development, Software Development, SQL, SQL Server | Leave a Comment »
Posted by jpluimers on 2011/10/19
A while ago, a friend asked my why Excel sometimes doesn’t import CSV files correctly. Recently, I was reminded because NB Business Card sends their invoice as Excel CSV. And expected CSV to be universal. Which it isn’t. Besides pople doing all sorts of crazy things with CSV (like What would happen if you defined your system CSV delimiter being a Quotation), Excel CSV isn’t universal either, as it depends on a crucial entry your Windows regional settings: the “List Delimiter”. Two series of screeen shots show this: The first for Windows XP and systems like it:
And for Windows 7 and similar systems:
A few tips: When you export CSV, be sure to use this setting from the registry. Or at least allow your users to specify it in your application, because: when you are not using Windows, you might be out of luck as there is no operating system neutral way of querying this value. And when you get some CSV and cannot import it in Excel, make sure you change your list delimiter to match it. And don’t forget to restore it to the original setting when you are done: it is a user session wide setting, so it applies to all applications (including the ones that do watch it). –jeroen
Like this:
Be the first to like this post.
Posted in CSV, Database Development, Development, Power User, Software Development | 3 Comments »
Posted by jpluimers on 2011/09/13
Almost a year ago there was a post on the MSDN forums titled EF4 – update model from database ignores some changes.
These are the changes it ignores that I found so far:
- Column renames
- Column data type changes
- Changed foreign key relations
Have you found others?
Do which EF versions are worse/better in this respect?
Note that the EF support in Visual Studio 2010 does not warn you if the the model is incompatible with your database.
You will get errors like this at run-time:
System.InvalidOperationException: The 'Size' property on 'ParentEntity' could not be set to a 'Int64' value. You must set this property to a non-null value of type 'Int32'.
at System.Data.Common.Internal.Materialization.Shaper.ErrorHandlingValueReader`1.GetValue(DbDataReader reader, Int32 ordinal)
at System.Data.Common.Internal.Materialization.Shaper.GetPropertyValueWithErrorHandling[TProperty](Int32 ordinal, String propertyName, String typeName)
at lambda_method(Closure , Shaper )
at System.Data.Common.Internal.Materialization.Shaper.HandleEntityAppendOnly[TEntity](Func`2 constructEntityDelegate, EntityKey entityKey, EntitySet entitySet)
at lambda_method(Closure , Shaper )
at System.Data.Common.Internal.Materialization.Coordinator`1.ReadNextElement(Shaper shaper)
at System.Data.Common.Internal.Materialization.Shaper`1.SimpleEnumerator.MoveNext()
The workaround is simple but tedious:
- check all the errors, (so you need a thorough testing scheme in place)
- make a list of all the entities involved
- for each entity:
- note all the manual changes you did
- delete it from the model
- add it to the model
- re-apply the manual changes you did
–jeroen
Like this:
Be the first to like this post.
Posted in .NET, Database Development, Development, EF Entity Framework, Software Development | 2 Comments »
Posted by jpluimers on 2011/05/18
When you (or the one that installed SQL Server 2008) forgot to add an administrator, it is still possible to add one.
The How do you add an administrator user to SQL Server 2008 question on Server Fault pointed me to the right page SQL Server 2008: Forgot to add an Administrator account? by Chris Randall.
Note that you might want to run SQL Server in Single User Mode; the first answer on the above question doesn’t mention that, but the second does (and pointed me to Chris Randall’s page).
–jeroen
via: How do you add an administrator user to SQL Server 2008? – Server Fault.
Like this:
Be the first to like this post.
Posted in Database Development, Development, SQL Server | Leave a Comment »
Posted by jpluimers on 2011/05/05
The Entity Framework needs you to have a connection string in your App.Config.
It is good practice having your Entity Framework model in a separate assembly.
When using it in your main app, be aware that the connection string that belongs to your model needs to be in the App.config of your main app.
If not, you get an exception like this:
System.ArgumentException was unhandled
Message=The specified named connection is either not found in the configuration, not intended to be used with the EntityClient provider, or not valid.
Source=System.Data.Entity
StackTrace:
at System.Data.EntityClient.EntityConnection.ChangeConnectionString(String newConnectionString)
at System.Data.EntityClient.EntityConnection..ctor(String connectionString)
at System.Data.Objects.ObjectContext.CreateEntityConnection(String connectionString)
at System.Data.Objects.ObjectContext..ctor(String connectionString, String defaultContainerName)
The clue is the contents of the defaultContainerName parameter: you will see that in the App.config of your Entity Framework assembly.
Copy that over to the App.config of your main assembly, then make sure it points to your right database (if you use OTAP), then go
Your App.config then looks like this:
<?xml version="1.0" encoding="utf-8"?>
<configuration>
<connectionStrings>
<add name="MyEntities" connectionString="metadata=res://*/My_EntityModel.csdl|res://*/My_EntityModel.ssdl|res://*/My_EntityModel.msl;provider=System.Data.SqlClient;provider connection string="data source=my-sql-server;initial catalog=my-database;persist security info=True;user id=my-user;password=my-password;multipleactiveresultsets=True;App=EntityFramework"" providerName="System.Data.EntityClient" />
</connectionStrings>
</configuration>
–jeroen
Like this:
Be the first to like this post.
Posted in .NET, C#, Database Development, Development, EF Entity Framework, Software Development, SQL Server | Leave a Comment »
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
Like this:
Be the first to like this post.
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
Like this:
Be the first to like this post.
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.
Like this:
Be the first to like this post.
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, JavaScript, 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 »
Like this:
Be the first to like this post.
Posted in .NET, Batch-Files, C#, CSS, Database Development, Delphi, Delphi for PHP, Development, HTML, HTML5, Java, JavaScript, 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 »
Like this:
Be the first to like this post.
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 ServerProperty.
Since I absolutely love sqlcmd, I extended my set of SQL Server batch files with this one showing you some of the ServerProperty values: Read the rest of this entry »
Like this:
Be the first to like this post.
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”.
O 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 »
Like this:
Be the first to like this post.
Posted in Database Development, Delphi, Development, Software Development, SQL Server | 3 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
Like this:
Be the first to like this post.
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 »
Like this:
Be the first to like this post.
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
Like this:
Be the first to like this post.
Posted in Database Development, Development, Google, Power User, SQL Server | Leave a Comment »
Posted by jpluimers on 2010/08/24
Recently, I had to restore StarTeam 2005 on a Windows 2003 Server.
An out-of-the-box install using MSDE 2000 does not want to run as a service.
This post shows you how I solved that problem. Read the rest of this entry »
Like this:
Be the first to like this post.
Posted in Database Development, Development, Source Code Management, SQL Server, StarTeam | Leave a Comment »
Posted by jpluimers on 2010/08/19
In my VM’s, I often run different instances and/or versions of SQL Server.
Finding the right instance of SQL server, and the right version of SQLCMD.EXE / OSQL.EXE can be a pain.
That’s why I have written the two batch-files shown below for that.
They are not perfect, but they do work for me, and show a few interesting batch-file tricks.
find-sql-sqlcmd.bat
This finds the most up-to-date SQLCMD.EXE (or OSQL.EXE for SQL Server 2000) and puts the location of it in the sqlcmd environment variable.
@echo off
rem find the highest version of SQLCMD.EXE or OSQL.EXE and put it in the sqlcmd environment variable
rem this prefers SQLCMD.EXE over OSQL.EXE
set sqlcmd=
for %%d in ("%ProgramFiles%", "%ProgramFiles(x86)%") do for %%v in (80, 90, 100) do for %%f in (OSQL, SQLCMD) do (
call :sqlcmdtest "%%~d\Microsoft SQL Server\%%v\Tools\Binn\%%f.EXE" %1
)
if !!==!%sqlcmd%! for %%f in (OSQL, SQLCMD) do (
call :find-on-path %%f.EXE
)
if !%1!==!! echo SQLCMD: %sqlcmd%
goto :exit
rem be carefull not to specify the .EXE in the %%f FOR loop-expression, otherwise it tries to dine SQLCMD.EXE and OQSL.EXE in the current directory
rem http://msdn.microsoft.com/en-us/library/ms178653.aspx
rem 80 = SQL Server 2000
rem 90 = SQL Server 2005
rem 100 = SQL Server 2008 and 2008 R2
:find-on-path
set sqlcmd=%~f$PATH:1
if not ""=="%sqlcmd%" set sqlcmd="%sqlcmd%"
goto :exit
:sqlcmdtest
if exist %1 if !%2!==!! echo found %1
if exist %1 set sqlcmd=%1
:exit
Tricks used:
- Clearing an environment varible by asigning an empty string to it
(set sqlcmd=)
- Nested for loops
Note that when working with string literals in for loops, you should not put any file extension on it (if you do, the for loop will only match filenames).
So: In stead of having (OSQL.EXE, SQLCMD.EXE), you see (OSQL, SQLCMD) in the for loop, and the .EXE is concatenated later on.
- Splitting the body of the for loop over multiple lines using parenthesis ().
- Put the value you prefer at the end of the for loop
(so the last value that is found will be put in the sqlcmd environment variable)
- The mapping of SQL Server verions to numbers used in the directories (see also the documentation of the SQL Server sp_dbcmptleve stored procedure):
80 = SQL Server 2000
90 = SQL Server 2005
100 = SQL Server 2008
find-sql-instance.bat
This batch file finds the SQL Server instances on the local machines from the naming of the SQL Server services that are running.
Note that it won’t work if you choose custom names for your SQL Server services (but that will probably break a lot of other stuff out there as well).
@echo off
rem find best matching instance of SQL Server on this machine
set sqlinstance=
set sqlservice=
for /f "usebackq tokens=1,2,3 delims=:$" %%i in (`sc query`) do (
rem %%j is " MSSQL" because we dropped space as a delimiter
if "%%i"=="SERVICE_NAME" call :bare-service %%j %%k
)
if !%1!==!! echo SQL Instance=%sqlinstance%
if !%1!==!! echo SQL Service=%sqlservice%
goto :exit
:bare-service
rem %1 equals "MSSQL" because of the command-line parsing trims spaces
rem the order is important: we favour named instances over unnamed:
if "%1"=="MSSQLSERVER" call :process-instance %1 .
if "%1"=="MSSQL" call :process-instance MSSQL$%2 .\%2
goto :exit
:process-instance
if !%1!==!! echo found service "%1" providing instance "%2"
if "%sqlinstance%"=="" set sqlinstance=%2& set sqlservice=%1
for /f "usebackq tokens=1,2,3,4" %%s in (`sc query %1`) do (
if "%%s"=="STATE" if !%1!==!! echo state of %1 is %%v
)
goto :exit
:exit
Tricks used:
- use the for /f command to parse the output of sc query;
note the use of the usebackq, tokens and delims parameters in this command,
and the use of `backquotes` to parse the output of `sc query` which outputs fragments like shown below.
- Use the ampersand (&) to run two commands on one line
(the set sqlinstance=%2 and set sqlservice=%1).
- the sc query command, which queries the Service Controller for the configured services
- The use of call :bare-service to call a subroutine at label :bare-service inside a for loop
- The fact that a leading space in for loop variable %%j is trimmed when calling the :bare-service label.
sc query sample fragment:
SERVICE_NAME: MSSQL$SQLEXPRESS
DISPLAY_NAME: SQL Server (SQLEXPRESS)
TYPE : 10 WIN32_OWN_PROCESS
STATE : 4 RUNNING
(STOPPABLE,PAUSABLE,ACCEPTS_SHUTDOWN)
WIN32_EXIT_CODE : 0 (0x0)
SERVICE_EXIT_CODE : 0 (0x0)
CHECKPOINT : 0x0
WAIT_HINT : 0x0
SQLRUN – tying it together
My final batch-file ties both together:
set sqlrun=
call %~dp0sql-find-sqlcmd.bat %*
call %~dp0sql-find-instance.bat %*
if not !!==!%sqlcmd%! if not !!==!%sqlinstance%! set sqlrun=%sqlcmd% -S %sqlinstance% -E
if !%1!==!! echo SQLRUN=%sqlrun%
The output of that batch-file is like this:
found "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\OSQL.EXE"
found "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE"
found "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\OSQL.EXE"
found "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE"
SQLCMD: "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE"
found ".\SQLEXPRESS"
state of service MSSQL$SQLEXPRESS with SQL Server instance .\SQLEXPRESS is RUNNING
found "MSSQLSERVER" (.)
state of service MSSQLSERVER with SQL Server instance . is RUNNING
SQL Instance=.\SQLEXPRESS
SQLRUN="C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE" -S .\SQLEXPRESS -E
Hope this helps a few people.
–jeroen
Like this:
One blogger likes this post.
Posted in CommandLine, Database Development, Development, Software Development, SQL Server | 3 Comments »
Posted by jpluimers on 2010/08/01
Has it been that long ago? Yes, yesterday, Firebird celebrated its 10th anniversary as an open source project.
It was stable for almost 2 years, and then Firebird 1.0 was released more than 8 years go.
The next version, 1.5, also took less than 2 years, and was released more than 6 years ago.
Version 2.0 was a major release, and took a bit longer: 2 years ago.
Version 2.5 is taking the same speed as 1.5 did, and is about to be released.
Work has already begun on 3.0, and a lot of new features are in the pipeline, including a new ODS.
–jeroen
Via: Firebird – The RDBMS that’s going where you’re going.
Like this:
Be the first to like this post.
Posted in Database Development, Development, Firebird | Leave a Comment »
Posted by jpluimers on 2010/07/29
One of the toughest parts on creating a new CodePlex project is choosing a license.
As Jeff Attwood wrote a couple of years ago, choosing a license – any license – is important, because if you don’t, you declare an implicit copyright without explaining how others could use your code.
In addition to that, Jeff published a list of licenses with a one-line phrase describing them, so it becomes easier to start making a choice.
Last year, ShreevastaR extended that list in his answer to this StackOverflow.com question on CodePlex licensing.
Brian Campbell did the same a few months later on another StackOverflow question about CodePlex licensing.
There are many more StackOverflow.com threads like those 2, and they give similar results.
The reason I want to put up a CodePlex project, is to put my sample code for conferences, blog articles and course examples on-line so they are easier to share with other people.
Most is from Visual Studio or Delphi projects using languages C#, VB.NET and Delphi.
Some of it are batch-files, XSD, XSLT, or other small snippets to get stuff working. Read the rest of this entry »
Like this:
Be the first to like this post.
Posted in .NET, Access, CodePlex, Database Development, Delphi, Development, Firebird, InterBase, Software Development, SQL Server | 4 Comments »
