Topic: MS SQL server EXPRESS connection string
MS SQL server EXPRESS connection string
Hi everyone,
At various occasions I have been fiddeling around with MS SQL server for projects or for testing compatibility in DataCleaner. I realized that the template Connection string we provide is not always the right one, so I decided to give a short update for anyone looking for SQL server connection string for DataCleaner.
If you are running express edition, then you will most likely have an instance called SQLEXPRESS.
By default only the database service is running, but to connect to a named instance you also need to start the Server Browser service! This was trick no. 1. When that service is started, connect using this connection string (give a database named "my_database"):
If you're on the other hand connecting through TCP/IP to a server, then the current template Connection string in DataCleaner is useful:
In DataCleaner 2.4 we will add multiple templates to fix this misunderstanding. Hopefully that will make it a lot easier to connect to MS SQL server! :)
At various occasions I have been fiddeling around with MS SQL server for projects or for testing compatibility in DataCleaner. I realized that the template Connection string we provide is not always the right one, so I decided to give a short update for anyone looking for SQL server connection string for DataCleaner.
If you are running express edition, then you will most likely have an instance called SQLEXPRESS.
By default only the database service is running, but to connect to a named instance you also need to start the Server Browser service! This was trick no. 1. When that service is started, connect using this connection string (give a database named "my_database"):
jdbc:jtds:sqlserver://localhost/my_database;instance=SQLEXPRESS;useUnicode=true;characterEncoding=UTF-8
If you're on the other hand connecting through TCP/IP to a server, then the current template Connection string in DataCleaner is useful:
jdbc:jtds:sqlserver://hostname:1433/my_database;useUnicode=true;characterEncoding=UTF-8
In DataCleaner 2.4 we will add multiple templates to fix this misunderstanding. Hopefully that will make it a lot easier to connect to MS SQL server! :)
Just downloaded DataCleaner and I am unable to connect to SQLexpress at all!
can you tell me what I should put in all of the textboxes please
My server name is .\SQLEXPRESS Is that the datastore name?
I have tried both connection strings but neither works.
Mike
can you tell me what I should put in all of the textboxes please
My server name is .\SQLEXPRESS Is that the datastore name?
I have tried both connection strings but neither works.
Mike
Did you start the sql server broqser service also? The datastore name is just your own name for it, only the URL decides how to connect.
Yes That is started OK
I am Getting this error
org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Unknown server host name '<MAIN>'.)
I am Getting this error
org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Unknown server host name '<MAIN>'.)
Hi,
I am now also guessing that your hosname should be "MAIN" and not "<MAIN>", correct?
If so simply remove the triangular brackets from the connection string. Maybe you misplaced them there because the template says something like "<hosname>", but the idea is that you replace that whole token with your hostname, not just what's inside the brackets.
I am now also guessing that your hosname should be "MAIN" and not "<MAIN>", correct?
If so simply remove the triangular brackets from the connection string. Maybe you misplaced them there because the template says something like "<hosname>", but the idea is that you replace that whole token with your hostname, not just what's inside the brackets.
sorry to be a nuisance but it's obvious i dont know what i'm doing ;)
I am getting a refused connect now. my connection string now looks like this
jdbc:jtds:sqlserver://MAIN/SQLEXPRESS;useUnicode=true;characterEncoding=UTF-8
I am getting a refused connect now. my connection string now looks like this
jdbc:jtds:sqlserver://MAIN/SQLEXPRESS;useUnicode=true;characterEncoding=UTF-8
Hmm I think since you are using the SQLEXPRESS version you should be looking for something like this:
Also remember to have the SQL Server Browser service started in Windows Services.
jdbc:jtds:sqlserver://localhost/my_database;instance=SQLEXPRESS;useUnicode=true;characterEncoding=UTF-8but replace "localhost" and "my_database" with whatever is correct in your situation.
Also remember to have the SQL Server Browser service started in Windows Services.
Thanks
I have tried every possible way to connect - get various errors but not able to connect thanks for your help but I cant waste any more time on this.
I have tried every possible way to connect - get various errors but not able to connect thanks for your help but I cant waste any more time on this.
Can you access the MSSQL instance in the Configuration Tool program and see if all protocols are enabled? And if so what port is used for TCP/IP ? In many cases after installation of MSSQL Express you need to adjust the settings; allow remote connections, set-up protocol and port numbers etc. etc. In all cases I have encountered I was able to connect to MSSQL but you need some fiddling on the server side in most cases.
Thanks I have done that now and i seem to be getting nearer!
I just get a 'login failed for user 'Main\Mike'
now this is the same login I use with SQL Management studio
which is using windows authentication
so I assume i will have to to add new Database accounts and Users and set a new new owner of my db?
I just get a 'login failed for user 'Main\Mike'
now this is the same login I use with SQL Management studio
which is using windows authentication
so I assume i will have to to add new Database accounts and Users and set a new new owner of my db?
Yes. If there is a database then you need to add a 'user' that can access the database having sufficient rights. That user will identify itself using 'MSSQL server authentication'. But, that autenthication modus must be enabled in the server as well (normally this is a decision to take during the setup of MSSQL) but it can be changed afterwards in the Management studio where you select the 'properties' of the EXPRESS instance and enable it.
Log in by clicking the login link at the top of the screen
