Solved

MSSQL connection string

Posted on 2008-10-12
22
528 Views
Last Modified: 2013-12-17
Hi,

I use the following string to connect to MSSQL 2005 server (not express).

server=companysqlserver;database=companydatabase;user id=company;password=data

It works fine.

When I need to connect to a MSSQL2005 express with the machine and instance name:
COMPANYSQLSERVER\SQLEXPRESS

server=COMPANYSQLSERVER\SQLEXPRESS;database=companydatabase;user id=company;password=data

It cannot be connected.

I also tried
server=COMPANYSQLSERVER\\SQLEXPRESS;database=companydatabase;user id=company;password=data

and

server=.\SQLEXPRESS;database=companydatabase;user id=company;password=data


I use VC# and ASP.NET
0
Comment
Question by:techques
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 6
  • 2
  • +2
22 Comments
 
LVL 18

Expert Comment

by:jmoss111
ID: 22699922
Maybe this will help you with your connect string:

http://connectionstrings.com/?carrier=sqlserver2005

Jim
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 22699935
And the error message is ...?
0
 

Author Comment

by:techques
ID: 22699941
cannot connect to DB
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:techques
ID: 22699944
I read http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/Q_22846250.html

Seems SQL server is different from SQL server express which uses the following to connect:

sConnString = "Provider=SQLOLEDB;Data Source=Kobe\SQLEXPRESS;Initial Catalog=taghistory;User ID=sa;Password=YourPassword"

Should I change it?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 22699959
>>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.
0
 
LVL 35

Expert Comment

by:David Todd
ID: 22700003
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\\Sql2005;database=PubsNew;Integrated Security=SSPI";
       
        }

HTH
  David
0
 
LVL 26

Expert Comment

by:Anurag Thakur
ID: 22700035
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
0
 

Author Comment

by:techques
ID: 22700114
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.


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   
{   
}   
}   

Open in new window

0
 
LVL 26

Expert Comment

by:Anurag Thakur
ID: 22700173
you can check the setting by opening "SQL Server Configuration Manager"
0
 

Author Comment

by:techques
ID: 22700189
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
0
 

Author Comment

by:techques
ID: 22711352
any help?
0
 
LVL 26

Expert Comment

by:Anurag Thakur
ID: 22711499
Default Instance Connection String - "server=companysqlserver;database=companydatabase;user id=company;password=data"

SQL Express connection string "server=COMPANYSQLSERVER\SQLEXPRESS;database=companydatabase;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
0
 

Author Comment

by:techques
ID: 22711666
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.

0
 
LVL 26

Expert Comment

by:Anurag Thakur
ID: 22711975
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
0
 

Author Comment

by:techques
ID: 22712366
I am not sure what did you mean because I cannot only login the sql server:
COMPANYSQLSERVER\SQLEXPRESS
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'
0
 
LVL 26

Expert Comment

by:Anurag Thakur
ID: 22712606
its a typo
meant to say "Now connect to the sqlexpress instance"
0
 

Author Comment

by:techques
ID: 22721530
So, what is the connection string for sqlexpress?
0
 
LVL 26

Expert Comment

by:Anurag Thakur
ID: 22722310
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.
0
 

Author Comment

by:techques
ID: 22732956
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?    

0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22733125
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
0
 

Author Comment

by:techques
ID: 22748986
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





0
 

Accepted Solution

by:
techques earned 0 total points
ID: 22758489
I fix it finally.

server=210.172.64.15\SQLEXPRESS,1329;database=companydatabase;user id=company;password=data

0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question