Multiple items in one row

Okay, I am pretty new at this, so this may be obvious, but here's what I want to do.

I have an Access database that can be used to reserve resources. The user inputs a date range and then checks off what they want to reserve. It looks like this:

StartDate .............EndDate .........Laptop......Camera........Projector

10/29/2007...........10/29/2007........................X
10/29/2007...........10/31/2007..........X
10/31/2007.......... 11/01/2007........................X..................X

Of course there is other info, and a return date, but none of that is needed for this report.

I want the report to show what is available. A blank means the item is available and the X means it is unavailable. It should look like this:

Date...............Laptop.............Camera..........Projector
10/29/07.............X.....................X
10/30/07.............X
10/31/07.............X.....................X..................X
11/1/07...............X.....................X

The dates in the report come from a date table which make up Group Header 1.

This is a sample of the formula I used to get the X's to show up in the Detail section.

if {DateTable.Date}>={Reservation.StartDate}and{DateTable.Date}<={reservation.EndDate}
then if {reservation.Laptop}=true
then "X"

This works but the x's from different reservations show up on different lines. I was able to get the first detail line to match up with the date, by choosing Underlay Following Sections, but the others still show up in a different line. For example 10/31/07 looks like this:

Date.........Laptop.........Camera.............Projector
10/31/07.........X
....................................X.....................X

Your help is much appreciated!Try to create 1 formula for each of the fields you need to be shown as 'X',

for example:

@.Laptop:
if isnull(your_table.Laptop) then 'X'

@.Camera:
if isnull(your_table.Camera) then 'X'
.
.
.

Use them instead of the fields.|||I am not using fields. I am using a formula for each item. For example @.laptop looks like this:

if {DateTable.Date}>={Reservation.StartDate}and{DateTable.Date}<={reservation.EndDate}
then if {reservation.Laptop}=true
then "X"

I just want all the x's to appear on the same row as the date so if one person reserves something on 10/31 and someone else has reserved something else on the same day, (which is represented on two rows in the database) I want those items to have an X under them on the 10/31 row. I can get the first one to get up to the date row by using the Underlay, but I can not get the other rows up.

I've tried putting the details in with the group header, but everything disappears.

Maybe I should change the database? I don't know. Let me know what you think!

multiple instances of spid's

I have sql server 2000 setup on 2003. When users are running queries agains
t
SQL Server from MS Access it opens up 10 - 18 instances for that user instea
d
of just 1 instance. Why would this happen and how can I fix it.
Your help/suggestion is greatly appreciated.
Thank youhelpplease wrote:
> I have sql server 2000 setup on 2003. When users are running queries
> against SQL Server from MS Access it opens up 10 - 18 instances for
> that user instead of just 1 instance. Why would this happen and how
> can I fix it.
> Your help/suggestion is greatly appreciated.
> Thank you
Sounds like an application design issue, not a SQL Server issue. You're
not saying you are seeing the same spid in sysprocesses listed multiple
times, are you? Either the application is programmed to perform multiple
connections to the database or this is happening automatically; possibly
because of active result sets in the application. Not an Access expert.
Probably better to ask this question in an Access ng.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Yes, I am seeing the same spid in sysprocesses listed multiple times.
What could be the problem?
Thank you
Divya
"David Gugick" wrote:

> helpplease wrote:
> Sounds like an application design issue, not a SQL Server issue. You're
> not saying you are seeing the same spid in sysprocesses listed multiple
> times, are you? Either the application is programmed to perform multiple
> connections to the database or this is happening automatically; possibly
> because of active result sets in the application. Not an Access expert.
> Probably better to ask this question in an Access ng.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||helpplease wrote:
> Yes, I am seeing the same spid in sysprocesses listed multiple times.
> What could be the problem?
> Thank you
> Divya
>
That's probably because the same spid is running in multiple threads on
multiple CPUs. 10-18 instances of a SPID sounds high. Not sure what is
really going on. What version (and SP) of SQL Server are you using? How
many CPUs?
David Gugick
Quest Software
www.imceda.com
www.quest.com|||SQL Server 2000, Service Pack 3.
I have 4 Processors.
Thank you
"David Gugick" wrote:

> helpplease wrote:
> That's probably because the same spid is running in multiple threads on
> multiple CPUs. 10-18 instances of a SPID sounds high. Not sure what is
> really going on. What version (and SP) of SQL Server are you using? How
> many CPUs?
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||Any Suggestions on this one.
Thank you
"helpplease" wrote:
[vbcol=seagreen]
> SQL Server 2000, Service Pack 3.
> I have 4 Processors.
> Thank you
> "David Gugick" wrote:
>

multiple instances of spid's

I have sql server 2000 setup on 2003. When users are running queries against
SQL Server from MS Access it opens up 10 - 18 instances for that user instead
of just 1 instance. Why would this happen and how can I fix it.
Your help/suggestion is greatly appreciated.
Thank you
helpplease wrote:
> I have sql server 2000 setup on 2003. When users are running queries
> against SQL Server from MS Access it opens up 10 - 18 instances for
> that user instead of just 1 instance. Why would this happen and how
> can I fix it.
> Your help/suggestion is greatly appreciated.
> Thank you
Sounds like an application design issue, not a SQL Server issue. You're
not saying you are seeing the same spid in sysprocesses listed multiple
times, are you? Either the application is programmed to perform multiple
connections to the database or this is happening automatically; possibly
because of active result sets in the application. Not an Access expert.
Probably better to ask this question in an Access ng.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Yes, I am seeing the same spid in sysprocesses listed multiple times.
What could be the problem?
Thank you
Divya
"David Gugick" wrote:

> helpplease wrote:
> Sounds like an application design issue, not a SQL Server issue. You're
> not saying you are seeing the same spid in sysprocesses listed multiple
> times, are you? Either the application is programmed to perform multiple
> connections to the database or this is happening automatically; possibly
> because of active result sets in the application. Not an Access expert.
> Probably better to ask this question in an Access ng.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
|||helpplease wrote:
> Yes, I am seeing the same spid in sysprocesses listed multiple times.
> What could be the problem?
> Thank you
> Divya
>
That's probably because the same spid is running in multiple threads on
multiple CPUs. 10-18 instances of a SPID sounds high. Not sure what is
really going on. What version (and SP) of SQL Server are you using? How
many CPUs?
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||SQL Server 2000, Service Pack 3.
I have 4 Processors.
Thank you
"David Gugick" wrote:

> helpplease wrote:
> That's probably because the same spid is running in multiple threads on
> multiple CPUs. 10-18 instances of a SPID sounds high. Not sure what is
> really going on. What version (and SP) of SQL Server are you using? How
> many CPUs?
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
|||Any Suggestions on this one.
Thank you
"helpplease" wrote:
[vbcol=seagreen]
> SQL Server 2000, Service Pack 3.
> I have 4 Processors.
> Thank you
> "David Gugick" wrote:
sql

multiple instances of spid's

I have sql server 2000 setup on 2003. When users are running queries against
SQL Server from MS Access it opens up 10 - 18 instances for that user instead
of just 1 instance. Why would this happen and how can I fix it.
Your help/suggestion is greatly appreciated.
Thank youhelpplease wrote:
> I have sql server 2000 setup on 2003. When users are running queries
> against SQL Server from MS Access it opens up 10 - 18 instances for
> that user instead of just 1 instance. Why would this happen and how
> can I fix it.
> Your help/suggestion is greatly appreciated.
> Thank you
Sounds like an application design issue, not a SQL Server issue. You're
not saying you are seeing the same spid in sysprocesses listed multiple
times, are you? Either the application is programmed to perform multiple
connections to the database or this is happening automatically; possibly
because of active result sets in the application. Not an Access expert.
Probably better to ask this question in an Access ng.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Yes, I am seeing the same spid in sysprocesses listed multiple times.
What could be the problem?
Thank you
Divya
"David Gugick" wrote:
> helpplease wrote:
> > I have sql server 2000 setup on 2003. When users are running queries
> > against SQL Server from MS Access it opens up 10 - 18 instances for
> > that user instead of just 1 instance. Why would this happen and how
> > can I fix it.
> >
> > Your help/suggestion is greatly appreciated.
> >
> > Thank you
> Sounds like an application design issue, not a SQL Server issue. You're
> not saying you are seeing the same spid in sysprocesses listed multiple
> times, are you? Either the application is programmed to perform multiple
> connections to the database or this is happening automatically; possibly
> because of active result sets in the application. Not an Access expert.
> Probably better to ask this question in an Access ng.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||helpplease wrote:
> Yes, I am seeing the same spid in sysprocesses listed multiple times.
> What could be the problem?
> Thank you
> Divya
>
That's probably because the same spid is running in multiple threads on
multiple CPUs. 10-18 instances of a SPID sounds high. Not sure what is
really going on. What version (and SP) of SQL Server are you using? How
many CPUs?
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||SQL Server 2000, Service Pack 3.
I have 4 Processors.
Thank you
"David Gugick" wrote:
> helpplease wrote:
> > Yes, I am seeing the same spid in sysprocesses listed multiple times.
> >
> > What could be the problem?
> >
> > Thank you
> > Divya
> >
> That's probably because the same spid is running in multiple threads on
> multiple CPUs. 10-18 instances of a SPID sounds high. Not sure what is
> really going on. What version (and SP) of SQL Server are you using? How
> many CPUs?
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||Any Suggestions on this one.
Thank you
"helpplease" wrote:
> SQL Server 2000, Service Pack 3.
> I have 4 Processors.
> Thank you
> "David Gugick" wrote:
> > helpplease wrote:
> > > Yes, I am seeing the same spid in sysprocesses listed multiple times.
> > >
> > > What could be the problem?
> > >
> > > Thank you
> > > Divya
> > >
> >
> > That's probably because the same spid is running in multiple threads on
> > multiple CPUs. 10-18 instances of a SPID sounds high. Not sure what is
> > really going on. What version (and SP) of SQL Server are you using? How
> > many CPUs?
> >
> > --
> > David Gugick
> > Quest Software
> > www.imceda.com
> > www.quest.com
> >
> >

multiple fields in SELECT WHERE IN

Hi All

I'm in the process of trying to write a query (in access 2000) that copys data between tables, then updates related tables.

The problem:

One of the tables is linked on 2 fields, and I need to do a where not in query, so I can't use a join.

Is it possible to do something along the lines of:

select * from table1 where field1, field2 not in (select ref1, ref2 from table2)

Any help much appreciated

Mark MiddlemistYES YOU CAN

select * from table1 where field1, field2 not in (select ref1, ref2 from table2)

this works fine, but check that field1 and ref1 should have same datatype etc.,|||This won't be upgradable to sql server though.
You could

select * from table1
where not exists
(select * from table2 where table1.field1 = table2.ref1 and table1.field2 = table2.ref2)

or

select table1.*
from table1 left outer join table2
on table1.field1 = table2.ref1 and table1.field2 = table2.ref2
where table1.field1 is null

these will still bring back results if the fields allow null whereas the in clause may well not.
In all cases the results may not be correct for nullable fields.

Multiple Databases accessed by Multiple reports by Multiple users...?

Hello All,
I am somewhat new to MS Reporting Services and I've got a situation
where I have different users needing to access the same report (rdl)
but from different databases?
Ideally I would want to have each report pointing to all databases
dynamically.
Do you know how I would go about setting this up? Multiple reports
pointing to different datasources? Passing a parameter which
determines a specific datasource? - Is it even possible to pass a
parameter to the report to determine which data source to use?
Thanks for your help in advance
MagAre you using RS 2000 or RS 2005? In RS 2005 you can use data source
expressions (search books online under expressions, data sources). If RS
2000 it is much harder and a total pain and I strongly suggest going to RS
2005.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"mag" <mestafanos@.gmail.com> wrote in message
news:1137700476.286569.181810@.g14g2000cwa.googlegroups.com...
> Hello All,
> I am somewhat new to MS Reporting Services and I've got a situation
> where I have different users needing to access the same report (rdl)
> but from different databases?
> Ideally I would want to have each report pointing to all databases
> dynamically.
> Do you know how I would go about setting this up? Multiple reports
> pointing to different datasources? Passing a parameter which
> determines a specific datasource? - Is it even possible to pass a
> parameter to the report to determine which data source to use?
> Thanks for your help in advance
> Mag
>

Multiple database access problem

Hi ,

Im developing a web application which would make access to multiple distributive databases. The problem is...the database from all the third parties may have different standard.
For instance...a user wish to retrive a record from many databases, he input "Pentium 4" ....but some database may have different standard, they may have a record with name "P4" instead of "Pentium 4"...so my question is , how should i make it standard ?? thanksEither use translation tables or enforce standard values through the use of foreign keys to static lookup lists.|||Great, would appreciate if you could describe more in detail ...thanks a lot|||A translation table is a table in a central database that contains the datasource, foreignvalue, and commonvalue for all the elements that need to be combined across databases.

Lookup tables are tables within each database that limit the user's choices to a selection of consistent values.

Multiple company

Hi. We have a SQL database for one company with multi user access. I am
curious as to best practice solutions for expanding a db to multiple company
access.
Some parts of the db need to be shared, others are private to the company
logged in.
If every time a query was executed it required company filtering, it seems a
waste having company parameters in every minor query, and would require
entire code rewrite. Not being a programmer I don't know what options sql
server may offer, but I know enough about sql to have these sort of questions
distract your thinking until answered...
I hope this is the correct forum for general questions such as this.
Thanks in advance.
swasswas
Create a LOGIN for those 'outside' companies. Grant the database access to
this LOGIN .
You can create VIEWS to show the data that only this LOGIN needs and GRANT
it SELECT ON the VIEW (For more details please refer to the BOL)
As well , you can GRANT an EXECUTE permissions to the LOGIN for STORED
PROCEDURES and not granting permissions on underlaying tables
"swas" <swas@.discussions.microsoft.com> wrote in message
news:C49B53B0-9483-4E87-9166-109ED0495E24@.microsoft.com...
> Hi. We have a SQL database for one company with multi user access. I am
> curious as to best practice solutions for expanding a db to multiple
> company
> access.
> Some parts of the db need to be shared, others are private to the company
> logged in.
> If every time a query was executed it required company filtering, it seems
> a
> waste having company parameters in every minor query, and would require
> entire code rewrite. Not being a programmer I don't know what options sql
> server may offer, but I know enough about sql to have these sort of
> questions
> distract your thinking until answered...
> I hope this is the correct forum for general questions such as this.
> Thanks in advance.
>
> swas

Multiple company

Hi. We have a SQL database for one company with multi user access. I am
curious as to best practice solutions for expanding a db to multiple company
access.
Some parts of the db need to be shared, others are private to the company
logged in.
If every time a query was executed it required company filtering, it seems a
waste having company parameters in every minor query, and would require
entire code rewrite. Not being a programmer I don't know what options sql
server may offer, but I know enough about sql to have these sort of question
s
distract your thinking until answered...
I hope this is the correct forum for general questions such as this.
Thanks in advance.
swasswas
Create a LOGIN for those 'outside' companies. Grant the database access to
this LOGIN .
You can create VIEWS to show the data that only this LOGIN needs and GRANT
it SELECT ON the VIEW (For more details please refer to the BOL)
As well , you can GRANT an EXECUTE permissions to the LOGIN for STORED
PROCEDURES and not granting permissions on underlaying tables
"swas" <swas@.discussions.microsoft.com> wrote in message
news:C49B53B0-9483-4E87-9166-109ED0495E24@.microsoft.com...
> Hi. We have a SQL database for one company with multi user access. I am
> curious as to best practice solutions for expanding a db to multiple
> company
> access.
> Some parts of the db need to be shared, others are private to the company
> logged in.
> If every time a query was executed it required company filtering, it seems
> a
> waste having company parameters in every minor query, and would require
> entire code rewrite. Not being a programmer I don't know what options sql
> server may offer, but I know enough about sql to have these sort of
> questions
> distract your thinking until answered...
> I hope this is the correct forum for general questions such as this.
> Thanks in advance.
>
> swas

Multiple backups happening

I had some problems configuring a backup of a sql server. The problem
was access rights to the network share. Now when I resolved it, my SQL
server is backing itself twice on the same share with two different
file names. There is a great possibility of my trying to configure the
SQL backup more than once since it failed the first few times.
Now I suspect that I did configured the backup more than once and as a
result it is backing itself twice.
Now is there a way I can view and delete all the backups configured for
a single database.
The problem may be something very different from what I feel becuase I
am very very new to SQL and its properties.
Thanks in advance
GauravYou can Enterprise Manager and Go to Management -> Sql Server Agent
->Jobs. You will find a list of all the jobs(includes backup). you
can delete the job from there. Hope your problem gets resolved|||Check out your maintenance plans and your SQL Server agent jobs.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gaurav" <gaurav081@.gmail.com> wrote in message
news:1146664173.026249.251370@.y43g2000cwc.googlegroups.com...
>I had some problems configuring a backup of a sql server. The problem
> was access rights to the network share. Now when I resolved it, my SQL
> server is backing itself twice on the same share with two different
> file names. There is a great possibility of my trying to configure the
> SQL backup more than once since it failed the first few times.
> Now I suspect that I did configured the backup more than once and as a
> result it is backing itself twice.
> Now is there a way I can view and delete all the backups configured for
> a single database.
> The problem may be something very different from what I feel becuase I
> am very very new to SQL and its properties.
> Thanks in advance
> Gaurav
>

Multiple backups happening

I had some problems configuring a backup of a sql server. The problem
was access rights to the network share. Now when I resolved it, my SQL
server is backing itself twice on the same share with two different
file names. There is a great possibility of my trying to configure the
SQL backup more than once since it failed the first few times.
Now I suspect that I did configured the backup more than once and as a
result it is backing itself twice.
Now is there a way I can view and delete all the backups configured for
a single database.
The problem may be something very different from what I feel becuase I
am very very new to SQL and its properties.
Thanks in advance
GauravYou can Enterprise Manager and Go to Management -> Sql Server Agent
->Jobs. You will find a list of all the jobs(includes backup). you
can delete the job from there. Hope your problem gets resolved|||Check out your maintenance plans and your SQL Server agent jobs.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gaurav" <gaurav081@.gmail.com> wrote in message
news:1146664173.026249.251370@.y43g2000cwc.googlegroups.com...
>I had some problems configuring a backup of a sql server. The problem
> was access rights to the network share. Now when I resolved it, my SQL
> server is backing itself twice on the same share with two different
> file names. There is a great possibility of my trying to configure the
> SQL backup more than once since it failed the first few times.
> Now I suspect that I did configured the backup more than once and as a
> result it is backing itself twice.
> Now is there a way I can view and delete all the backups configured for
> a single database.
> The problem may be something very different from what I feel becuase I
> am very very new to SQL and its properties.
> Thanks in advance
> Gaurav
>

Multiple applications Accessing the BDE

Hi,
There are multiple applications accessing BDE on the same machine.
Both work fine independently but when they access BDE concurrently..One
app stops working when i.e the app that is installed 2nd stops
working.It freezes..
Any Ideas'
AndzHi
Are these using the same BDE entry? If so have you tried using a different
one per application?
John
"andz" wrote:

> Hi,
> There are multiple applications accessing BDE on the same machine.
> Both work fine independently but when they access BDE concurrently..One
> app stops working when i.e the app that is installed 2nd stops
> working.It freezes..
>
> Any Ideas'
> Andz
>|||Hi John,
Many thanks for ur reply.
Did you mean different entries for Borland in the registry or different
install locations?
The same setup works on another workstation.
We have tried two seperate installs of BDE in different locations- but
just one entry in the registry.
Andz
John Bell wrote:
[vbcol=seagreen]
> Hi
> Are these using the same BDE entry? If so have you tried using a different
> one per application?
> John
> "andz" wrote:
>|||Hi
I was meaning as different databases in the same installation of BDE, you
can use the save as option to save the same settings under a different name.
I assume that you have checked each entry setting against a version you know
will work and that the versions of BDE are the same? The openmode should be
read/write and you are using the Native SQL Server driver?
John
"andz" wrote:

> Hi John,
> Many thanks for ur reply.
> Did you mean different entries for Borland in the registry or different
> install locations?
> The same setup works on another workstation.
> We have tried two seperate installs of BDE in different locations- but
> just one entry in the registry.
> Andz
> John Bell wrote:
>
>

Multiple applications Accessing the BDE

Hi,
There are multiple applications accessing BDE on the same machine.
Both work fine independently but when they access BDE concurrently..One
app stops working when i.e the app that is installed 2nd stops
working.It freezes..
Any Ideas'
AndzHi
Are these using the same BDE entry? If so have you tried using a different
one per application?
John
"andz" wrote:
> Hi,
> There are multiple applications accessing BDE on the same machine.
> Both work fine independently but when they access BDE concurrently..One
> app stops working when i.e the app that is installed 2nd stops
> working.It freezes..
>
> Any Ideas'
> Andz
>|||Hi John,
Many thanks for ur reply.
Did you mean different entries for Borland in the registry or different
install locations?
The same setup works on another workstation.
We have tried two seperate installs of BDE in different locations- but
just one entry in the registry.
Andz
John Bell wrote:
> Hi
> Are these using the same BDE entry? If so have you tried using a different
> one per application?
> John
> "andz" wrote:
> > Hi,
> >
> > There are multiple applications accessing BDE on the same machine.
> > Both work fine independently but when they access BDE concurrently..One
> >
> > app stops working when i.e the app that is installed 2nd stops
> > working.It freezes..
> >
> >
> > Any Ideas'
> >
> > Andz
> >
> >|||Hi
I was meaning as different databases in the same installation of BDE, you
can use the save as option to save the same settings under a different name.
I assume that you have checked each entry setting against a version you know
will work and that the versions of BDE are the same? The openmode should be
read/write and you are using the Native SQL Server driver?
John
"andz" wrote:
> Hi John,
> Many thanks for ur reply.
> Did you mean different entries for Borland in the registry or different
> install locations?
> The same setup works on another workstation.
> We have tried two seperate installs of BDE in different locations- but
> just one entry in the registry.
> Andz
> John Bell wrote:
> > Hi
> >
> > Are these using the same BDE entry? If so have you tried using a different
> > one per application?
> >
> > John
> >
> > "andz" wrote:
> >
> > > Hi,
> > >
> > > There are multiple applications accessing BDE on the same machine.
> > > Both work fine independently but when they access BDE concurrently..One
> > >
> > > app stops working when i.e the app that is installed 2nd stops
> > > working.It freezes..
> > >
> > >
> > > Any Ideas'
> > >
> > > Andz
> > >
> > >
>

Multiple Access databases as data source

I've read already a lot of threads, dealing with several databases on
different servers as data source. They all recommend to use linked servers.
But what, if we have to deal with Access databases?Actually, most advice I've seen is NOT to use linked servers. You can
connect directly via ODBC or OLEDB to Access.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Niklas" <Niklas@.discussions.microsoft.com> wrote in message
news:7424F35B-C52C-4104-9C6D-91AC0F439898@.microsoft.com...
> I've read already a lot of threads, dealing with several databases on
> different servers as data source. They all recommend to use linked
> servers.
> But what, if we have to deal with Access databases?|||The recommendation for linked servers is if you are trying to join two the
results from two different servers. If you want to do that you need to have
a stored procedure and use temp tables and do the join there (and use linked
servers, note you should use openquery and not the four part naming for
performance reasons). If you are not doing a join then definitely do not use
linked servers. Just set up multiple data sources (shared is best in my
opinion).
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Niklas" <Niklas@.discussions.microsoft.com> wrote in message
news:7424F35B-C52C-4104-9C6D-91AC0F439898@.microsoft.com...
> I've read already a lot of threads, dealing with several databases on
> different servers as data source. They all recommend to use linked
> servers.
> But what, if we have to deal with Access databases?|||Thanks Bruce,
I have to join the results of several servers, unfortunately I've to deal
with Access ...
"Bruce L-C [MVP]" wrote:
> The recommendation for linked servers is if you are trying to join two the
> results from two different servers. If you want to do that you need to have
> a stored procedure and use temp tables and do the join there (and use linked
> servers, note you should use openquery and not the four part naming for
> performance reasons). If you are not doing a join then definitely do not use
> linked servers. Just set up multiple data sources (shared is best in my
> opinion).
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Niklas" <Niklas@.discussions.microsoft.com> wrote in message
> news:7424F35B-C52C-4104-9C6D-91AC0F439898@.microsoft.com...
> > I've read already a lot of threads, dealing with several databases on
> > different servers as data source. They all recommend to use linked
> > servers.
> > But what, if we have to deal with Access databases?
>
>|||Here is what you can do very easily with Access. In one of your Access
databases just add a link to the table in the other database. When you do a
join Access handles the join. You are better off to use Access for this
(especially if the data is in Access databases) than to create a linked
server in SQL Server.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Niklas" <Niklas@.discussions.microsoft.com> wrote in message
news:115ED942-3D21-46B4-AE80-CA93132E4845@.microsoft.com...
> Thanks Bruce,
> I have to join the results of several servers, unfortunately I've to deal
> with Access ...
> "Bruce L-C [MVP]" wrote:
>> The recommendation for linked servers is if you are trying to join two
>> the
>> results from two different servers. If you want to do that you need to
>> have
>> a stored procedure and use temp tables and do the join there (and use
>> linked
>> servers, note you should use openquery and not the four part naming for
>> performance reasons). If you are not doing a join then definitely do not
>> use
>> linked servers. Just set up multiple data sources (shared is best in my
>> opinion).
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Niklas" <Niklas@.discussions.microsoft.com> wrote in message
>> news:7424F35B-C52C-4104-9C6D-91AC0F439898@.microsoft.com...
>> > I've read already a lot of threads, dealing with several databases on
>> > different servers as data source. They all recommend to use linked
>> > servers.
>> > But what, if we have to deal with Access databases?
>>