connecting to MSDE on Windows 2003 SBS Server

I have an application (Point of Sale System) that utilizes a MSDE
database. The server came loaded with MSDE instances already and I
installed one for the POS System. The client PCs can see the other 3
MSDE instances, but cannot see the one I installed. Any ideas on how
the new instance can appear on the network. The new instance was not
named. The other three were named instances of MSDE.
hi,
auschnet@.gmail.com wrote:
> I have an application (Point of Sale System) that utilizes a MSDE
> database. The server came loaded with MSDE instances already and I
> installed one for the POS System. The client PCs can see the other 3
> MSDE instances, but cannot see the one I installed. Any ideas on how
> the new instance can appear on the network. The new instance was not
> named. The other three were named instances of MSDE.
please verify the required/desired network protocols are enabled... MSDE
installs by default disabling them and you can override this behavior
providing the
DISABLENETWORKPROTOCOLS=0
parameter to the setup.exe boostrap installer, or later, at run time, using
the Server Network Utility (svrnetcn.exe) and enabling the preferred network
protocol..
additionally, WinXP sp2 ships with a built-in Firewall that's enabled by
default, preventing network connections to... if this is a case, you have to
specify an exception for the service or for the MSDE used port, usually
specifying a subnet or individual remot IP addreses to restrict
connections...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

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.

Connecting a user from a backup database to a new login

All,
In SQL Server 2000, I loaded a backup file into a new local database.
The database has an existing user which owns several stored procedures,
tables, etc.
Using SQL Server authentication, I want to create a login of the same
name as the user, so that when I login, I have access to the objects
owned by that user without having to prefix the objects with the owner
name.
For instance exec sp_mystoredproc instead of exec
ownername.sp_mystoredproc. (In fact, several of the stored procedures
exec other procedures without the owner name prefix and to go add the
owner will be a tedious process)
When I try to add login and grant the login name to the existing user
it tells me it already exists.
I cannot drop the existing user because it owns a whole bunch of
objects.
What can I do to work around this? How do I connect a login to an
existing database user? Is there a system table where I can just map
the two?
Brad> What can I do to work around this? How do I connect a login to an
> existing database user? Is there a system table where I can just map
> the two?
See the sp_change_users_login stored procedure in the Books Online. Also,
the Best Practice is to always schema-qualify objects.
Hope this helps.
Dan Guzman
SQL Server MVP
"brad" <brad.eckrose@.gmail.com> wrote in message
news:1168719405.103237.233140@.38g2000cwa.googlegroups.com...
> All,
> In SQL Server 2000, I loaded a backup file into a new local database.
> The database has an existing user which owns several stored procedures,
> tables, etc.
> Using SQL Server authentication, I want to create a login of the same
> name as the user, so that when I login, I have access to the objects
> owned by that user without having to prefix the objects with the owner
> name.
> For instance exec sp_mystoredproc instead of exec
> ownername.sp_mystoredproc. (In fact, several of the stored procedures
> exec other procedures without the owner name prefix and to go add the
> owner will be a tedious process)
> When I try to add login and grant the login name to the existing user
> it tells me it already exists.
> I cannot drop the existing user because it owns a whole bunch of
> objects.
> What can I do to work around this? How do I connect a login to an
> existing database user? Is there a system table where I can just map
> the two?
> Brad
>