techques
asked on
MSSQL connection string
Hi,
I use the following string to connect to MSSQL 2005 server (not express).
server=companysqlserver;da tabase=com panydataba se;user id=company;password=data
It works fine.
When I need to connect to a MSSQL2005 express with the machine and instance name:
COMPANYSQLSERVER\SQLEXPRES S
server=COMPANYSQLSERVER\SQ LEXPRESS;d atabase=co mpanydatab ase;user id=company;password=data
It cannot be connected.
I also tried
server=COMPANYSQLSERVER\\S QLEXPRESS; database=c ompanydata base;user id=company;password=data
and
server=.\SQLEXPRESS;databa se=company database;u ser id=company;password=data
I use VC# and ASP.NET
I use the following string to connect to MSSQL 2005 server (not express).
server=companysqlserver;da
It works fine.
When I need to connect to a MSSQL2005 express with the machine and instance name:
COMPANYSQLSERVER\SQLEXPRES
server=COMPANYSQLSERVER\SQ
It cannot be connected.
I also tried
server=COMPANYSQLSERVER\\S
and
server=.\SQLEXPRESS;databa
I use VC# and ASP.NET
And the error message is ...?
ASKER
cannot connect to DB
ASKER
I read https://www.experts-exchange.com/questions/22846250/Creating-connection-string-string-to-SQL-Express.html
Seems SQL server is different from SQL server express which uses the following to connect:
sConnString = "Provider=SQLOLEDB;Data Source=Kobe\SQLEXPRESS;Ini tial Catalog=taghistory;User ID=sa;Password=YourPasswor d"
Should I change it?
Seems SQL server is different from SQL server express which uses the following to connect:
sConnString = "Provider=SQLOLEDB;Data Source=Kobe\SQLEXPRESS;Ini
Should I change it?
>>cannot connect to DB<<
Yes, I understand that you cannot connect. What we need is the exact error number and message you get when you attempt to connect.
Yes, I understand that you cannot connect. What we need is the exact error number and message you get when you attempt to connect.
Hi,
I typed in this example
http://msdn.microsoft.com/en-us/library/dw70f090(VS.80).aspx
I changed the GetConnectionString to
static private string GetConnectionString() {
return "server=myWorkStation\\Sql 2005;datab ase=PubsNe w;Integrat ed Security=SSPI";
}
HTH
David
I typed in this example
http://msdn.microsoft.com/en-us/library/dw70f090(VS.80).aspx
I changed the GetConnectionString to
static private string GetConnectionString() {
return "server=myWorkStation\\Sql
}
HTH
David
two things comming to my mind
1. is the sql server express instance on the machine is running
2. are the sql server 2005 network protocols for the sqlexpress instance enabled
1. is the sql server express instance on the machine is running
2. are the sql server 2005 network protocols for the sqlexpress instance enabled
ASKER
how to check those 2 things? I can login to the MSSQL 2005 server express and select data.
The following is the code to throw exception and it said "Thread Terminated"
I did not see this error before.
The following is the code to throw exception and it said "Thread Terminated"
I did not see this error before.
try
{
this.Add();
}
catch (Exception ex)
{
Response.Redirect("error.aspx?error1=" + ex.Message, true);
}
private void Add()
{
try
{
SqlHelper.ExecuteNonQuery(DBConnection.ConnString, CommandType.Text, this.SQL());
}
catch
{
Response.Redirect("dberror.htm");
}
finally
{
}
}
you can check the setting by opening "SQL Server Configuration Manager"
ASKER
I checked
1. is the sql server express instance on the machine is running
2. are the sql server 2005 network protocols for the sqlexpress instance enabled
both are yes
1. is the sql server express instance on the machine is running
2. are the sql server 2005 network protocols for the sqlexpress instance enabled
both are yes
ASKER
any help?
Default Instance Connection String - "server=companysqlserver;d atabase=co mpanydatab ase;user id=company;password=data"
SQL Express connection string "server=COMPANYSQLSERVER\S QLEXPRESS; database=c ompanydata base;user id=company;password=data"
seeing both the strings i can see that the databse is called CompanyDatabase
is this database available in both the instances or you are trying to connect to the companydatabase installed under Default instance from Sql Express instance.
Just a thought
SQL Express connection string "server=COMPANYSQLSERVER\S
seeing both the strings i can see that the databse is called CompanyDatabase
is this database available in both the instances or you are trying to connect to the companydatabase installed under Default instance from Sql Express instance.
Just a thought
ASKER
Yes, I need to connect to CompanyDatabase database
Actually, I do not know what is instance.
It should be default instance.
It is the forth day, still cannot find the solution to connect to it.
Actually, I do not know what is instance.
It should be default instance.
It is the forth day, still cannot find the solution to connect to it.
Open sql management studio
Connect to the default instance and see if the companydatabase is there (i think it was existing there)
Not connect to the sqlexpress instance and see if companydatabase exists there (i think it does not exist there thats why you are not able to connect)
If its not there then you might have to create or restore the db on the instance
All the instances in sql server are different and they do not share information amongst themselves and not even the databases
Connect to the default instance and see if the companydatabase is there (i think it was existing there)
Not connect to the sqlexpress instance and see if companydatabase exists there (i think it does not exist there thats why you are not able to connect)
If its not there then you might have to create or restore the db on the instance
All the instances in sql server are different and they do not share information amongst themselves and not even the databases
ASKER
I am not sure what did you mean because I cannot only login the sql server:
COMPANYSQLSERVER\SQLEXPRES S
So that I must see the companydatabase DB.
If I login with COMPANYSQLSERVER without \SQLEXPRESS, I cannot login.
So, I am not sure what did you mean 'Not connect to the sqlexpress instance'
COMPANYSQLSERVER\SQLEXPRES
So that I must see the companydatabase DB.
If I login with COMPANYSQLSERVER without \SQLEXPRESS, I cannot login.
So, I am not sure what did you mean 'Not connect to the sqlexpress instance'
its a typo
meant to say "Now connect to the sqlexpress instance"
meant to say "Now connect to the sqlexpress instance"
ASKER
So, what is the connection string for sqlexpress?
the question i asked was if you have a default sql server instance and a sqlexperss instance installed on the same server do you have companydatabase running on both of the instances - if you can give us the answer to this question we can give you the connection string.
in my previous comment i tried to ask the same question (ID:22711975) because to access a database from an instance the database should be available on that instance and the databases are not shared across instances
each sql instance is different from the other instance and so even the databases are.
in my previous comment i tried to ask the same question (ID:22711975) because to access a database from an instance the database should be available on that instance and the databases are not shared across instances
each sql instance is different from the other instance and so even the databases are.
ASKER
I checked the default instance in regedit
Microsoft SQL Server
Instance Names
SQL
SQLEXPRESS MSSQL.1
So, both default and sqlexpress instances are the same.
Then, what is the connection string should be?
Microsoft SQL Server
Instance Names
SQL
SQLEXPRESS MSSQL.1
So, both default and sqlexpress instances are the same.
Then, what is the connection string should be?
It appears that you have two instances: SQL and SQLEXPRESS. The login without \SQLEXPRESS that doesn't work says thats a named instance and if you have anywhere in the setup or surface area configuration of Express to listen on 1433 you might have problems connecting. Go to www.connectionstrings.com like I said in my earlier post any you will find all the options for 2000 and 2005.
Jim
Jim
ASKER
Hi
I read
http://blogs.msdn.com/sql_protocols/archive/2007/05/13/sql-network-interfaces-error-26-error-locating-server-instance-specified.aspx
and
http://blogs.msdn.com/sql_protocols/archive/2006/02/27/unable-to-connect-to-a-sql-server-named-instance-on-a-cluster.aspx
Set the firewall exception, enable tcp/ip, all those steps were done.
However, I still cannot use the connectionstring to connect to DB
even I use aspnet_regsql.exe and it threw error: 40 - cannot open SQL Server connection
C:\>osql -E -S cluster\inst
[SQL Native Client]SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
[SQL Native Client]Login timeout expired
SQL Browser service is running on the server
I read
http://blogs.msdn.com/sql_protocols/archive/2007/05/13/sql-network-interfaces-error-26-error-locating-server-instance-specified.aspx
and
http://blogs.msdn.com/sql_protocols/archive/2006/02/27/unable-to-connect-to-a-sql-server-named-instance-on-a-cluster.aspx
Set the firewall exception, enable tcp/ip, all those steps were done.
However, I still cannot use the connectionstring to connect to DB
even I use aspnet_regsql.exe and it threw error: 40 - cannot open SQL Server connection
C:\>osql -E -S cluster\inst
[SQL Native Client]SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
[SQL Native Client]Login timeout expired
SQL Browser service is running on the server
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
http://connectionstrings.com/?carrier=sqlserver2005
Jim