connecting to SQL DATABASE ENGINE

hello everybody!!!

I am very new to SQL SERVER 2005 EXPRESS EDITION

while connecting to DATABASE ENGINE i got this error message.....

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (.Net SqlClient Data Provider)

any solution please?

Hi,

If you are trying on a local machine.

login properly :

server instance: computer_name\SQLSERVER_INSTANCE_NAME

user name : computer_name\user_name

If trying on a remote machine:

have a look in the following link:

http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

Hope it'll help.

|||

this error is very common... You check whether the instance is configured for Remote connection

Start --Microsoft sql server 2005-- Configuration Tools -- SQL Server Surface Area Configuration -- Surface Area configuration for services and connections -- Select Remote Connection -- Select Local and remote connection Radio button and using both TCP/IP and named pipes

ALso check whether the firewall is enabled. if yes create exception for 1433 and 1434

Madhu

sqlsql

Connecting SQL Server2000 using Static IP over Internet

Hi Everybody

I am having same problem. I am trying to connect my vb6 application with SQL Server 2000. My database is on database server machine which has ID ADMIN and SQL Server is using default instance. Database instance name is DBPIMS. When I try to connect this database within any machine of LAN, it is working fine. But If I want to connect from my home or some other place through internet, it is not cannecting database to database.. I've 2wire router installed at my office. & I've done port forwarding for port 1433. Also I've one static IP which I am using for connection. My connection string is

"Provider=SQLOLEDB.1;Password=xxxx;Persist Security Info=True;User ID=xxx;Initial Catalog=<atabase instance name;Data Source=" & static IP addres

But it couldn't get connected to database. Please can anybody help me. I am in urgent need to solve this as its been since long I am trying find its solution.

Thanks in advance

whitch is service pack instaled on your sql server?

Connecting and Getting data from 8 tables in one sql statement

Hi everybody,

I like to get data from 8 tables to be loaded in a dropdownlist or combobox control. Desired Result is need to be filtered by Region, Company, ProjectOffice, Country, and Location and still get all rows from 3 tables after applying filters. How can this be done in one single sql statement?

I have 3 separate sql statements which needed to be only one.

Select C.CompanyID, C.[Name] As 'Company', P.ProjectOfficeID, P.[Name] As 'Project Office', P.RegionID,
R.[Name] As 'Region'
From ProjectCompany E Left Join ProjectOffice P On E.ProjectOfficeID = P.ProjectOfficeID
Left Join Company C On E.CompanyID = C.CompanyID
Left Join Region R On P.RegionID = R.RegionID Order By E.ProjectOfficeID

Select P.ProjectOfficeID, P.[Name] As 'Project Office', P.RegionID, R.[Name] As 'Region',
C.CountryID, C.[Name] As 'Country'
From ProjectOfficeCountry O Left Join ProjectOffice P On O.ProjectOfficeID = P.ProjectOfficeID
Left Join Country C On O.CountryID = C.CountryID
Left Join Region R On P.RegionID = R.RegionID

Select P.ProjectOfficeID, P.[Name] As 'Project Office', P.RegionID, R.[Name] As 'Region',
W.LocationID, W.[Name] As 'Location'
From ProjectOfficeLocation L Left Join ProjectOffice P On L.ProjectOfficeID = P.ProjectOfficeID
Left Join Location W On L.LocationID = W.LocationID
Left Join Region R On P.RegionID = R.RegionID

Table structures (some columns)

Region table
RegionID Name

ProjectOffice table

ProjectOfficeID Name RegionID

Location Table

LocationID Name

Country table

CountryID Name

Company table

CompanyID Name

ProjectCompany table (All rows)

ID ProjectOfficeID CompanyID

ProjectOfficeCountry table (All rows)

ID ProjectOfficeID CountryID

ProjectOfficeLocation table (All rows)

ID ProjectOfficeID LocationID

maybe something like this ?

Select 'A' as [type], C.CompanyID, C.[Name] As 'Company', P.ProjectOfficeID, P.[Name] As 'Project Office', P.RegionID,
R.[Name] As 'Region'
From ProjectCompany E Left Join ProjectOffice P On E.ProjectOfficeID = P.ProjectOfficeID
Left Join Company C On E.CompanyID = C.CompanyID
Left Join Region R On P.RegionID = R.RegionID Order By E.ProjectOfficeID

UNION ALL

Select 'B' as [type], P.ProjectOfficeID, P.[Name] As 'Project Office', P.RegionID, R.[Name] As 'Region',
C.CountryID, C.[Name] As 'Country'
From ProjectOfficeCountry O Left Join ProjectOffice P On O.ProjectOfficeID = P.ProjectOfficeID
Left Join Country C On O.CountryID = C.CountryID
Left Join Region R On P.RegionID = R.RegionID

UNION ALL

Select 'C' as [type], P.ProjectOfficeID, P.[Name] As 'Project Office', P.RegionID, R.[Name] As 'Region',
W.LocationID, W.[Name] As 'Location'
From ProjectOfficeLocation L Left Join ProjectOffice P On L.ProjectOfficeID = P.ProjectOfficeID
Left Join Location W On L.LocationID = W.LocationID
Left Join Region R On P.RegionID = R.RegionID

|||

Thanks carlop for replying,

If I did that then I could not differentiate between CompanyID and ProjectID or Company From Project Office or Location From Region, there should be a better way to do this...

Thanks.

|||

I really don’t understand why you need in single SQL Statement. Are you trying to fetch those result in single batch (single hit to server).

If you don’t use the union all then you have to use the JOIN. It may lead your (desired) result set to duplicates (for region & country) and it is not helpful on Combobox binding.

|||

ManiD,

Trying to get say data for Company, filtered by Region,Country, ProjectOffice, Company and Location, then data will be used to fill the combobox or dropdownlist control.

|||

are you like to hit the db server once to get those data.

or

you need to have all the data in columns..?

|||

ManiD,

Well, that is ideal to get all columns I needed and without duplicates. I have used several sql statements to achive same result, I wonder if it can be done using one single sql statement.

|||

While I agree with the other posters that this is not a very good idea, you could probably use union to put all of the data in a single result:

Select C.CompanyID, C.[Name] As 'Company', P.ProjectOfficeID, P.[Name] As 'Project Office', P.RegionID,
R.[Name] As 'Region', 'PURPOSE1' as purpose
From ProjectCompany E Left Join ProjectOffice P On E.ProjectOfficeID = P.ProjectOfficeID
Left Join Company C On E.CompanyID = C.CompanyID
Left Join Region R On P.RegionID = R.RegionID

UNION ALL

Select P.ProjectOfficeID, P.[Name] As 'Project Office', P.RegionID, R.[Name] As 'Region',
C.CountryID, C.[Name] As 'Country', 'PURPOSE2' as purpose
From ProjectOfficeCountry O Left Join ProjectOffice P On O.ProjectOfficeID = P.ProjectOfficeID
Left Join Country C On O.CountryID = C.CountryID
Left Join Region R On P.RegionID = R.RegionID

Note that you would also need to genericize the other names, like the id, the name, etc. I almost feel wrong in suggesting this, because it seems so much cleaner to make N result sets and get the data one set at a time (much cleaner and certainly eaiser to implement.)

|||

Thanks for the reply Louis.

It seems there is no exact way getting all data needed in just one sql statement. I would stick to using multiple sql statements in getting the results.

Thanks.

I think the issue in this thread is closed.

Connecting and Getting data from 8 tables in one sql statement

Hi everybody,

I like to get data from 8 tables to be loaded in a dropdownlist or combobox control. Desired Result is need to be filtered by Region, Company, ProjectOffice, Country, and Location and still get all rows from 3 tables after applying filters. How can this be done in one single sql statement?

I have 3 separate sql statements which needed to be only one.

Select C.CompanyID, C.[Name] As 'Company', P.ProjectOfficeID, P.[Name] As 'Project Office', P.RegionID,
R.[Name] As 'Region'
From ProjectCompany E Left Join ProjectOffice P On E.ProjectOfficeID = P.ProjectOfficeID
Left Join Company C On E.CompanyID = C.CompanyID
Left Join Region R On P.RegionID = R.RegionID Order By E.ProjectOfficeID

Select P.ProjectOfficeID, P.[Name] As 'Project Office', P.RegionID, R.[Name] As 'Region',
C.CountryID, C.[Name] As 'Country'
From ProjectOfficeCountry O Left Join ProjectOffice P On O.ProjectOfficeID = P.ProjectOfficeID
Left Join Country C On O.CountryID = C.CountryID
Left Join Region R On P.RegionID = R.RegionID

Select P.ProjectOfficeID, P.[Name] As 'Project Office', P.RegionID, R.[Name] As 'Region',
W.LocationID, W.[Name] As 'Location'
From ProjectOfficeLocation L Left Join ProjectOffice P On L.ProjectOfficeID = P.ProjectOfficeID
Left Join Location W On L.LocationID = W.LocationID
Left Join Region R On P.RegionID = R.RegionID

Table structures (some columns)

Region table
RegionID Name

ProjectOffice table

ProjectOfficeID Name RegionID

Location Table

LocationID Name

Country table

CountryID Name

Company table

CompanyID Name

ProjectCompany table (All rows)

ID ProjectOfficeID CompanyID

ProjectOfficeCountry table (All rows)

ID ProjectOfficeID CountryID

ProjectOfficeLocation table (All rows)

ID ProjectOfficeID LocationID

maybe something like this ?

Select 'A' as [type], C.CompanyID, C.[Name] As 'Company', P.ProjectOfficeID, P.[Name] As 'Project Office', P.RegionID,
R.[Name] As 'Region'
From ProjectCompany E Left Join ProjectOffice P On E.ProjectOfficeID = P.ProjectOfficeID
Left Join Company C On E.CompanyID = C.CompanyID
Left Join Region R On P.RegionID = R.RegionID Order By E.ProjectOfficeID

UNION ALL

Select 'B' as [type], P.ProjectOfficeID, P.[Name] As 'Project Office', P.RegionID, R.[Name] As 'Region',
C.CountryID, C.[Name] As 'Country'
From ProjectOfficeCountry O Left Join ProjectOffice P On O.ProjectOfficeID = P.ProjectOfficeID
Left Join Country C On O.CountryID = C.CountryID
Left Join Region R On P.RegionID = R.RegionID

UNION ALL

Select 'C' as [type], P.ProjectOfficeID, P.[Name] As 'Project Office', P.RegionID, R.[Name] As 'Region',
W.LocationID, W.[Name] As 'Location'
From ProjectOfficeLocation L Left Join ProjectOffice P On L.ProjectOfficeID = P.ProjectOfficeID
Left Join Location W On L.LocationID = W.LocationID
Left Join Region R On P.RegionID = R.RegionID

|||

Thanks carlop for replying,

If I did that then I could not differentiate between CompanyID and ProjectID or Company From Project Office or Location From Region, there should be a better way to do this...

Thanks.

|||

I really don’t understand why you need in single SQL Statement. Are you trying to fetch those result in single batch (single hit to server).

If you don’t use the union all then you have to use the JOIN. It may lead your (desired) result set to duplicates (for region & country) and it is not helpful on Combobox binding.

|||

ManiD,

Trying to get say data for Company, filtered by Region,Country, ProjectOffice, Company and Location, then data will be used to fill the combobox or dropdownlist control.

|||

are you like to hit the db server once to get those data.

or

you need to have all the data in columns..?

|||

ManiD,

Well, that is ideal to get all columns I needed and without duplicates. I have used several sql statements to achive same result, I wonder if it can be done using one single sql statement.

|||

While I agree with the other posters that this is not a very good idea, you could probably use union to put all of the data in a single result:

Select C.CompanyID, C.[Name] As 'Company', P.ProjectOfficeID, P.[Name] As 'Project Office', P.RegionID,
R.[Name] As 'Region', 'PURPOSE1' as purpose
From ProjectCompany E Left Join ProjectOffice P On E.ProjectOfficeID = P.ProjectOfficeID
Left Join Company C On E.CompanyID = C.CompanyID
Left Join Region R On P.RegionID = R.RegionID

UNION ALL

Select P.ProjectOfficeID, P.[Name] As 'Project Office', P.RegionID, R.[Name] As 'Region',
C.CountryID, C.[Name] As 'Country', 'PURPOSE2' as purpose
From ProjectOfficeCountry O Left Join ProjectOffice P On O.ProjectOfficeID = P.ProjectOfficeID
Left Join Country C On O.CountryID = C.CountryID
Left Join Region R On P.RegionID = R.RegionID

Note that you would also need to genericize the other names, like the id, the name, etc. I almost feel wrong in suggesting this, because it seems so much cleaner to make N result sets and get the data one set at a time (much cleaner and certainly eaiser to implement.)

|||

Thanks for the reply Louis.

It seems there is no exact way getting all data needed in just one sql statement. I would stick to using multiple sql statements in getting the results.

Thanks.

I think the issue in this thread is closed.