I recently discovered something interesting about OLEDB connection strings for SQL Server 2005. It turns out you need to use a comma instead of a colon when specifying the port number. This can be a bit tricky, especially with named instances.
Even if you’re using the default port 1433, you might need to include it explicitly. Here’s an example of how the connection string should look:
In this example, ‘10.20.30.40’ is the server IP, ‘1433’ is the port number, ‘MyDatabase’ is the database name, and we’ve got the user credentials at the end.
Has anyone else run into this quirk before? I’d be curious to hear if there are any other gotchas with OLEDB connection strings that people have encountered.
Your observation about the comma usage in OLEDB connection strings is quite valuable. I’ve encountered similar issues in the past, particularly when dealing with non-standard port configurations. It’s worth noting that while this syntax works for OLEDB, other connection methods may differ. For instance, when using ADO.NET with the SqlClient provider, you would typically use a colon instead of a comma.
Additionally, for those working with named instances, the format slightly changes. You’d use ‘ServerName\InstanceName,Port’ in the Data Source part. Always test your connection strings thoroughly, as subtle differences can lead to frustrating debugging sessions.
oh wow, that’s interesting! i never knew about the comma thing. have you tried this with different sql server versions? i’m curious if it’s the same for newer ones too. maybe there’s a reason behind using comma instead of colon? it’d be cool to know the logic behind it!
yeah, that comma thing is weird! i’ve had issues with connection strings before but never noticed this. thanks for sharing! btw, have you tried using the SqlClient provider instead? it’s supposed to be more optimized for sql server. might be worth checking out if you haven’t already.