I currently have a database through a company that has been upkeeping SQL an
d
storing data. I am cancelling subscription and want to retrieve data. They
are sending me a backup DVD with all the files. How easy is it to use
Microsoft SQL and can the files be transfered to another program such as
Access or Excel'
ThanksAre you saying that you have data stored in a SQL Server database, which is
hosted by other company, and you now want to sotre your data by yourself? If
so, the best way to do is to set up your own SQL Server, have you data on
the other SQL Server backed up and restore it on your own SQL Server. (or
use detach/attach database from/to SQL Server).
There are different edition of SQL Server, from very expensive Enterprise
Edition, to free version of SQL Server Express/MSDE. If your data value is
not so big (2G or 4G), you can use MSDE or SQL Server Express for free. Of
course, some basic knowledge on SQL Server is required (I mean a bit more
and complicated that required by using Access).
If you want those data being transferred to Access DB, then you can ask your
SQL Server host to export it to a *.mdb file. However, in general, only data
can be transferred, server object used in that database (view, sp... they
may contain critical logics for using the database) would not be
transferred. So, you would need to build a sort of front end app inorder to
use your data. Unless the only thing you need is the data.
"Eddie" <Eddie@.discussions.microsoft.com> wrote in message
news:389AA3B8-1996-487C-A65D-29F4E5404246@.microsoft.com...
>I currently have a database through a company that has been upkeeping SQL
>and
> storing data. I am cancelling subscription and want to retrieve data.
> They
> are sending me a backup DVD with all the files. How easy is it to use
> Microsoft SQL and can the files be transfered to another program such as
> Access or Excel'
> Thanks|||You are taking a big step backward going from SQL Server to MS Access and an
even bigger step if you go to Excel.
Microsoft SQL Server is an enterprise level relational database management
system.
Microsoft Access is a database engine but it is not quite enterprise level.
MS Excel is can be used to display information to management types and
create pretty reports, but it is by no means an enterprise storage
technology.
IF your host sends you the correct information -- backup files created via
the T-SQL BACKUP command OR database and log files after they ran
sp_detach_db -- you should be able to restore the database(s) to a SQL
Server in your environment.
SQL Server does not have "files" (like Excel). It is more similar to MS
Access in that it has tables within one container (a database).
Keith Kratochvil
"Eddie" <Eddie@.discussions.microsoft.com> wrote in message
news:389AA3B8-1996-487C-A65D-29F4E5404246@.microsoft.com...
>I currently have a database through a company that has been upkeeping SQL
>and
> storing data. I am cancelling subscription and want to retrieve data.
> They
> are sending me a backup DVD with all the files. How easy is it to use
> Microsoft SQL and can the files be transfered to another program such as
> Access or Excel'
> Thanks|||The company is not using the system to it's fullest potential. The users ar
e
not computer savvy and would prefer a more simple system ie: Access. They
don't want to be paying the fees associated to a program they don't use.
They have 7 years of data they need to access and I have to figure out how.
Once we access the data and can retrieve the reports and pictures we won't b
e
using the system. We want to transfer it to Access.
Thanks
"Norman Yuan" wrote:
> Are you saying that you have data stored in a SQL Server database, which i
s
> hosted by other company, and you now want to sotre your data by yourself?
If
> so, the best way to do is to set up your own SQL Server, have you data on
> the other SQL Server backed up and restore it on your own SQL Server. (or
> use detach/attach database from/to SQL Server).
> There are different edition of SQL Server, from very expensive Enterprise
> Edition, to free version of SQL Server Express/MSDE. If your data value is
> not so big (2G or 4G), you can use MSDE or SQL Server Express for free. Of
> course, some basic knowledge on SQL Server is required (I mean a bit more
> and complicated that required by using Access).
> If you want those data being transferred to Access DB, then you can ask yo
ur
> SQL Server host to export it to a *.mdb file. However, in general, only da
ta
> can be transferred, server object used in that database (view, sp... they
> may contain critical logics for using the database) would not be
> transferred. So, you would need to build a sort of front end app inorder t
o
> use your data. Unless the only thing you need is the data.
> "Eddie" <Eddie@.discussions.microsoft.com> wrote in message
> news:389AA3B8-1996-487C-A65D-29F4E5404246@.microsoft.com...
>
>|||The company is not using the system to it's fullest potential. The users are
not computer savvy and would prefer a more simple system ie: Access. They
don't want to be paying the fees associated to a program they don't use.
They have 7 years of data they need to access and I have to figure out how.
Once we access the data and can retrieve the reports and pictures we won't b
e
using the system. We want to transfer it to Access.
Thanks
"Keith Kratochvil" wrote:
> You are taking a big step backward going from SQL Server to MS Access and
an
> even bigger step if you go to Excel.
> Microsoft SQL Server is an enterprise level relational database management
> system.
> Microsoft Access is a database engine but it is not quite enterprise level
.
> MS Excel is can be used to display information to management types and
> create pretty reports, but it is by no means an enterprise storage
> technology.
>
> IF your host sends you the correct information -- backup files created via
> the T-SQL BACKUP command OR database and log files after they ran
> sp_detach_db -- you should be able to restore the database(s) to a SQL
> Server in your environment.
>
> SQL Server does not have "files" (like Excel). It is more similar to MS
> Access in that it has tables within one container (a database).
>
> --
> Keith Kratochvil
>
> "Eddie" <Eddie@.discussions.microsoft.com> wrote in message
> news:389AA3B8-1996-487C-A65D-29F4E5404246@.microsoft.com...
>
>|||Eddie,
You can certainly use Access as the front-end program for
your users, even if you maintain the data in SQL Server.
SQL Server in fact has no front-end, really - just a set of
management tools, so you have many options for your
users.
Since no one has mentioned it, don't overlook the Workgroup
Edition of SQL Server, which might be good for your needs.
http://www.microsoft.com/sql/prodin...e-features.mspx
Steve Kass
Drew University
Eddie wrote:
[vbcol=seagreen]
>The company is not using the system to it's fullest potential. The users a
re
>not computer savvy and would prefer a more simple system ie: Access. They
>don't want to be paying the fees associated to a program they don't use.
>They have 7 years of data they need to access and I have to figure out how.
>Once we access the data and can retrieve the reports and pictures we won't
be
>using the system. We want to transfer it to Access.
>Thanks
>"Norman Yuan" wrote:
>
>|||Maybe SQL Server Express combined with MS Access front-ends is the answer.
SS Express is free.
Randall Arnold
"Eddie" <Eddie@.discussions.microsoft.com> wrote in message
news:49490C53-3ADC-418F-8E8F-A7468A98DEE4@.microsoft.com...[vbcol=seagreen]
> The company is not using the system to it's fullest potential. The users
> are
> not computer savvy and would prefer a more simple system ie: Access. They
> don't want to be paying the fees associated to a program they don't use.
> They have 7 years of data they need to access and I have to figure out
> how.
> Once we access the data and can retrieve the reports and pictures we won't
> be
> using the system. We want to transfer it to Access.
> Thanks
> "Keith Kratochvil" wrote:
>|||So, summarize what you want:
1. You have data stored in database of someone's SQL Server;
2. You need to get all data out in this database and svae them in some
formats you choose;
3. You do not need application related server objects/logics in the database
(views, stored procedures, UDFs) once you retrieved the data;
4. The SQL Server host would not do the data retrieving for you. They can
only give you the backup or data file of the database.
Since you want to transfer data from SQL Server to Access (more accurately,
Jet database), there may be some degree of data or data type or data
accuracy loss because of the differences of the two data engine.
Here is what I would do:
1. Get full backup or detached database file (latter would be better);
2. Install a MSDE, which is free version of SQL Server, if the data file of
the database (*.mdf) is less than 2GB.
3. Restore/attach the databse to MSDE.
4. Use Access as tool to connect to MSDE and start retrieve data. You can
create an Access ADP project to use as MSDE front end UI. Then you can open
each table to export, or write some macro/VBA code to retireve specific data
and output to specific format.
5. Since I have the database on my onw, no-cost MSDE engine, I can try and
try until satisfied totally (of course do it on a copy of the data).
6, Do the final retrieving and then uninstall the MSDE.
"Eddie" <Eddie@.discussions.microsoft.com> wrote in message
news:E426270B-5851-456C-B190-1D747BAFA433@.microsoft.com...[vbcol=seagreen]
> The company is not using the system to it's fullest potential. The users
> are
> not computer savvy and would prefer a more simple system ie: Access. They
> don't want to be paying the fees associated to a program they don't use.
> They have 7 years of data they need to access and I have to figure out
> how.
> Once we access the data and can retrieve the reports and pictures we won't
> be
> using the system. We want to transfer it to Access.
> Thanks
> "Norman Yuan" wrote:
>
Showing posts with label retrieve. Show all posts
Showing posts with label retrieve. Show all posts
Wednesday, March 21, 2012
Purchasing Microsoft SQL Server for small company
I currently have a database through a company that has been upkeeping SQL and
storing data. I am cancelling subscription and want to retrieve data. They
are sending me a backup DVD with all the files. How easy is it to use
Microsoft SQL and can the files be transfered to another program such as
Access or Excel'
ThanksAre you saying that you have data stored in a SQL Server database, which is
hosted by other company, and you now want to sotre your data by yourself? If
so, the best way to do is to set up your own SQL Server, have you data on
the other SQL Server backed up and restore it on your own SQL Server. (or
use detach/attach database from/to SQL Server).
There are different edition of SQL Server, from very expensive Enterprise
Edition, to free version of SQL Server Express/MSDE. If your data value is
not so big (2G or 4G), you can use MSDE or SQL Server Express for free. Of
course, some basic knowledge on SQL Server is required (I mean a bit more
and complicated that required by using Access).
If you want those data being transferred to Access DB, then you can ask your
SQL Server host to export it to a *.mdb file. However, in general, only data
can be transferred, server object used in that database (view, sp... they
may contain critical logics for using the database) would not be
transferred. So, you would need to build a sort of front end app inorder to
use your data. Unless the only thing you need is the data.
"Eddie" <Eddie@.discussions.microsoft.com> wrote in message
news:389AA3B8-1996-487C-A65D-29F4E5404246@.microsoft.com...
>I currently have a database through a company that has been upkeeping SQL
>and
> storing data. I am cancelling subscription and want to retrieve data.
> They
> are sending me a backup DVD with all the files. How easy is it to use
> Microsoft SQL and can the files be transfered to another program such as
> Access or Excel'
> Thanks|||You are taking a big step backward going from SQL Server to MS Access and an
even bigger step if you go to Excel.
Microsoft SQL Server is an enterprise level relational database management
system.
Microsoft Access is a database engine but it is not quite enterprise level.
MS Excel is can be used to display information to management types and
create pretty reports, but it is by no means an enterprise storage
technology.
IF your host sends you the correct information -- backup files created via
the T-SQL BACKUP command OR database and log files after they ran
sp_detach_db -- you should be able to restore the database(s) to a SQL
Server in your environment.
SQL Server does not have "files" (like Excel). It is more similar to MS
Access in that it has tables within one container (a database).
Keith Kratochvil
"Eddie" <Eddie@.discussions.microsoft.com> wrote in message
news:389AA3B8-1996-487C-A65D-29F4E5404246@.microsoft.com...
>I currently have a database through a company that has been upkeeping SQL
>and
> storing data. I am cancelling subscription and want to retrieve data.
> They
> are sending me a backup DVD with all the files. How easy is it to use
> Microsoft SQL and can the files be transfered to another program such as
> Access or Excel'
> Thanks|||The company is not using the system to it's fullest potential. The users are
not computer savvy and would prefer a more simple system ie: Access. They
don't want to be paying the fees associated to a program they don't use.
They have 7 years of data they need to access and I have to figure out how.
Once we access the data and can retrieve the reports and pictures we won't be
using the system. We want to transfer it to Access.
Thanks
"Norman Yuan" wrote:
> Are you saying that you have data stored in a SQL Server database, which is
> hosted by other company, and you now want to sotre your data by yourself? If
> so, the best way to do is to set up your own SQL Server, have you data on
> the other SQL Server backed up and restore it on your own SQL Server. (or
> use detach/attach database from/to SQL Server).
> There are different edition of SQL Server, from very expensive Enterprise
> Edition, to free version of SQL Server Express/MSDE. If your data value is
> not so big (2G or 4G), you can use MSDE or SQL Server Express for free. Of
> course, some basic knowledge on SQL Server is required (I mean a bit more
> and complicated that required by using Access).
> If you want those data being transferred to Access DB, then you can ask your
> SQL Server host to export it to a *.mdb file. However, in general, only data
> can be transferred, server object used in that database (view, sp... they
> may contain critical logics for using the database) would not be
> transferred. So, you would need to build a sort of front end app inorder to
> use your data. Unless the only thing you need is the data.
> "Eddie" <Eddie@.discussions.microsoft.com> wrote in message
> news:389AA3B8-1996-487C-A65D-29F4E5404246@.microsoft.com...
> >I currently have a database through a company that has been upkeeping SQL
> >and
> > storing data. I am cancelling subscription and want to retrieve data.
> > They
> > are sending me a backup DVD with all the files. How easy is it to use
> > Microsoft SQL and can the files be transfered to another program such as
> > Access or Excel'
> >
> > Thanks
>
>|||The company is not using the system to it's fullest potential. The users are
not computer savvy and would prefer a more simple system ie: Access. They
don't want to be paying the fees associated to a program they don't use.
They have 7 years of data they need to access and I have to figure out how.
Once we access the data and can retrieve the reports and pictures we won't be
using the system. We want to transfer it to Access.
Thanks
"Keith Kratochvil" wrote:
> You are taking a big step backward going from SQL Server to MS Access and an
> even bigger step if you go to Excel.
> Microsoft SQL Server is an enterprise level relational database management
> system.
> Microsoft Access is a database engine but it is not quite enterprise level.
> MS Excel is can be used to display information to management types and
> create pretty reports, but it is by no means an enterprise storage
> technology.
>
> IF your host sends you the correct information -- backup files created via
> the T-SQL BACKUP command OR database and log files after they ran
> sp_detach_db -- you should be able to restore the database(s) to a SQL
> Server in your environment.
>
> SQL Server does not have "files" (like Excel). It is more similar to MS
> Access in that it has tables within one container (a database).
>
> --
> Keith Kratochvil
>
> "Eddie" <Eddie@.discussions.microsoft.com> wrote in message
> news:389AA3B8-1996-487C-A65D-29F4E5404246@.microsoft.com...
> >I currently have a database through a company that has been upkeeping SQL
> >and
> > storing data. I am cancelling subscription and want to retrieve data.
> > They
> > are sending me a backup DVD with all the files. How easy is it to use
> > Microsoft SQL and can the files be transfered to another program such as
> > Access or Excel'
> >
> > Thanks
>
>|||Maybe SQL Server Express combined with MS Access front-ends is the answer.
SS Express is free.
Randall Arnold
"Eddie" <Eddie@.discussions.microsoft.com> wrote in message
news:49490C53-3ADC-418F-8E8F-A7468A98DEE4@.microsoft.com...
> The company is not using the system to it's fullest potential. The users
> are
> not computer savvy and would prefer a more simple system ie: Access. They
> don't want to be paying the fees associated to a program they don't use.
> They have 7 years of data they need to access and I have to figure out
> how.
> Once we access the data and can retrieve the reports and pictures we won't
> be
> using the system. We want to transfer it to Access.
> Thanks
> "Keith Kratochvil" wrote:
>> You are taking a big step backward going from SQL Server to MS Access and
>> an
>> even bigger step if you go to Excel.
>> Microsoft SQL Server is an enterprise level relational database
>> management
>> system.
>> Microsoft Access is a database engine but it is not quite enterprise
>> level.
>> MS Excel is can be used to display information to management types and
>> create pretty reports, but it is by no means an enterprise storage
>> technology.
>>
>> IF your host sends you the correct information -- backup files created
>> via
>> the T-SQL BACKUP command OR database and log files after they ran
>> sp_detach_db -- you should be able to restore the database(s) to a SQL
>> Server in your environment.
>>
>> SQL Server does not have "files" (like Excel). It is more similar to MS
>> Access in that it has tables within one container (a database).
>>
>> --
>> Keith Kratochvil
>>
>> "Eddie" <Eddie@.discussions.microsoft.com> wrote in message
>> news:389AA3B8-1996-487C-A65D-29F4E5404246@.microsoft.com...
>> >I currently have a database through a company that has been upkeeping
>> >SQL
>> >and
>> > storing data. I am cancelling subscription and want to retrieve data.
>> > They
>> > are sending me a backup DVD with all the files. How easy is it to use
>> > Microsoft SQL and can the files be transfered to another program such
>> > as
>> > Access or Excel'
>> >
>> > Thanks
>>|||Eddie,
You can certainly use Access as the front-end program for
your users, even if you maintain the data in SQL Server.
SQL Server in fact has no front-end, really - just a set of
management tools, so you have many options for your
users.
Since no one has mentioned it, don't overlook the Workgroup
Edition of SQL Server, which might be good for your needs.
http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
Steve Kass
Drew University
Eddie wrote:
>The company is not using the system to it's fullest potential. The users are
>not computer savvy and would prefer a more simple system ie: Access. They
>don't want to be paying the fees associated to a program they don't use.
>They have 7 years of data they need to access and I have to figure out how.
>Once we access the data and can retrieve the reports and pictures we won't be
>using the system. We want to transfer it to Access.
>Thanks
>"Norman Yuan" wrote:
>
>>Are you saying that you have data stored in a SQL Server database, which is
>>hosted by other company, and you now want to sotre your data by yourself? If
>>so, the best way to do is to set up your own SQL Server, have you data on
>>the other SQL Server backed up and restore it on your own SQL Server. (or
>>use detach/attach database from/to SQL Server).
>>There are different edition of SQL Server, from very expensive Enterprise
>>Edition, to free version of SQL Server Express/MSDE. If your data value is
>>not so big (2G or 4G), you can use MSDE or SQL Server Express for free. Of
>>course, some basic knowledge on SQL Server is required (I mean a bit more
>>and complicated that required by using Access).
>>If you want those data being transferred to Access DB, then you can ask your
>>SQL Server host to export it to a *.mdb file. However, in general, only data
>>can be transferred, server object used in that database (view, sp... they
>>may contain critical logics for using the database) would not be
>>transferred. So, you would need to build a sort of front end app inorder to
>>use your data. Unless the only thing you need is the data.
>>"Eddie" <Eddie@.discussions.microsoft.com> wrote in message
>>news:389AA3B8-1996-487C-A65D-29F4E5404246@.microsoft.com...
>>
>>I currently have a database through a company that has been upkeeping SQL
>>and
>>storing data. I am cancelling subscription and want to retrieve data.
>>They
>>are sending me a backup DVD with all the files. How easy is it to use
>>Microsoft SQL and can the files be transfered to another program such as
>>Access or Excel'
>>Thanks
>>
>>|||So, summarize what you want:
1. You have data stored in database of someone's SQL Server;
2. You need to get all data out in this database and svae them in some
formats you choose;
3. You do not need application related server objects/logics in the database
(views, stored procedures, UDFs) once you retrieved the data;
4. The SQL Server host would not do the data retrieving for you. They can
only give you the backup or data file of the database.
Since you want to transfer data from SQL Server to Access (more accurately,
Jet database), there may be some degree of data or data type or data
accuracy loss because of the differences of the two data engine.
Here is what I would do:
1. Get full backup or detached database file (latter would be better);
2. Install a MSDE, which is free version of SQL Server, if the data file of
the database (*.mdf) is less than 2GB.
3. Restore/attach the databse to MSDE.
4. Use Access as tool to connect to MSDE and start retrieve data. You can
create an Access ADP project to use as MSDE front end UI. Then you can open
each table to export, or write some macro/VBA code to retireve specific data
and output to specific format.
5. Since I have the database on my onw, no-cost MSDE engine, I can try and
try until satisfied totally (of course do it on a copy of the data).
6, Do the final retrieving and then uninstall the MSDE.
"Eddie" <Eddie@.discussions.microsoft.com> wrote in message
news:E426270B-5851-456C-B190-1D747BAFA433@.microsoft.com...
> The company is not using the system to it's fullest potential. The users
> are
> not computer savvy and would prefer a more simple system ie: Access. They
> don't want to be paying the fees associated to a program they don't use.
> They have 7 years of data they need to access and I have to figure out
> how.
> Once we access the data and can retrieve the reports and pictures we won't
> be
> using the system. We want to transfer it to Access.
> Thanks
> "Norman Yuan" wrote:
>> Are you saying that you have data stored in a SQL Server database, which
>> is
>> hosted by other company, and you now want to sotre your data by yourself?
>> If
>> so, the best way to do is to set up your own SQL Server, have you data on
>> the other SQL Server backed up and restore it on your own SQL Server. (or
>> use detach/attach database from/to SQL Server).
>> There are different edition of SQL Server, from very expensive Enterprise
>> Edition, to free version of SQL Server Express/MSDE. If your data value
>> is
>> not so big (2G or 4G), you can use MSDE or SQL Server Express for free.
>> Of
>> course, some basic knowledge on SQL Server is required (I mean a bit more
>> and complicated that required by using Access).
>> If you want those data being transferred to Access DB, then you can ask
>> your
>> SQL Server host to export it to a *.mdb file. However, in general, only
>> data
>> can be transferred, server object used in that database (view, sp... they
>> may contain critical logics for using the database) would not be
>> transferred. So, you would need to build a sort of front end app inorder
>> to
>> use your data. Unless the only thing you need is the data.
>> "Eddie" <Eddie@.discussions.microsoft.com> wrote in message
>> news:389AA3B8-1996-487C-A65D-29F4E5404246@.microsoft.com...
>> >I currently have a database through a company that has been upkeeping
>> >SQL
>> >and
>> > storing data. I am cancelling subscription and want to retrieve data.
>> > They
>> > are sending me a backup DVD with all the files. How easy is it to use
>> > Microsoft SQL and can the files be transfered to another program such
>> > as
>> > Access or Excel'
>> >
>> > Thanks
>>
storing data. I am cancelling subscription and want to retrieve data. They
are sending me a backup DVD with all the files. How easy is it to use
Microsoft SQL and can the files be transfered to another program such as
Access or Excel'
ThanksAre you saying that you have data stored in a SQL Server database, which is
hosted by other company, and you now want to sotre your data by yourself? If
so, the best way to do is to set up your own SQL Server, have you data on
the other SQL Server backed up and restore it on your own SQL Server. (or
use detach/attach database from/to SQL Server).
There are different edition of SQL Server, from very expensive Enterprise
Edition, to free version of SQL Server Express/MSDE. If your data value is
not so big (2G or 4G), you can use MSDE or SQL Server Express for free. Of
course, some basic knowledge on SQL Server is required (I mean a bit more
and complicated that required by using Access).
If you want those data being transferred to Access DB, then you can ask your
SQL Server host to export it to a *.mdb file. However, in general, only data
can be transferred, server object used in that database (view, sp... they
may contain critical logics for using the database) would not be
transferred. So, you would need to build a sort of front end app inorder to
use your data. Unless the only thing you need is the data.
"Eddie" <Eddie@.discussions.microsoft.com> wrote in message
news:389AA3B8-1996-487C-A65D-29F4E5404246@.microsoft.com...
>I currently have a database through a company that has been upkeeping SQL
>and
> storing data. I am cancelling subscription and want to retrieve data.
> They
> are sending me a backup DVD with all the files. How easy is it to use
> Microsoft SQL and can the files be transfered to another program such as
> Access or Excel'
> Thanks|||You are taking a big step backward going from SQL Server to MS Access and an
even bigger step if you go to Excel.
Microsoft SQL Server is an enterprise level relational database management
system.
Microsoft Access is a database engine but it is not quite enterprise level.
MS Excel is can be used to display information to management types and
create pretty reports, but it is by no means an enterprise storage
technology.
IF your host sends you the correct information -- backup files created via
the T-SQL BACKUP command OR database and log files after they ran
sp_detach_db -- you should be able to restore the database(s) to a SQL
Server in your environment.
SQL Server does not have "files" (like Excel). It is more similar to MS
Access in that it has tables within one container (a database).
Keith Kratochvil
"Eddie" <Eddie@.discussions.microsoft.com> wrote in message
news:389AA3B8-1996-487C-A65D-29F4E5404246@.microsoft.com...
>I currently have a database through a company that has been upkeeping SQL
>and
> storing data. I am cancelling subscription and want to retrieve data.
> They
> are sending me a backup DVD with all the files. How easy is it to use
> Microsoft SQL and can the files be transfered to another program such as
> Access or Excel'
> Thanks|||The company is not using the system to it's fullest potential. The users are
not computer savvy and would prefer a more simple system ie: Access. They
don't want to be paying the fees associated to a program they don't use.
They have 7 years of data they need to access and I have to figure out how.
Once we access the data and can retrieve the reports and pictures we won't be
using the system. We want to transfer it to Access.
Thanks
"Norman Yuan" wrote:
> Are you saying that you have data stored in a SQL Server database, which is
> hosted by other company, and you now want to sotre your data by yourself? If
> so, the best way to do is to set up your own SQL Server, have you data on
> the other SQL Server backed up and restore it on your own SQL Server. (or
> use detach/attach database from/to SQL Server).
> There are different edition of SQL Server, from very expensive Enterprise
> Edition, to free version of SQL Server Express/MSDE. If your data value is
> not so big (2G or 4G), you can use MSDE or SQL Server Express for free. Of
> course, some basic knowledge on SQL Server is required (I mean a bit more
> and complicated that required by using Access).
> If you want those data being transferred to Access DB, then you can ask your
> SQL Server host to export it to a *.mdb file. However, in general, only data
> can be transferred, server object used in that database (view, sp... they
> may contain critical logics for using the database) would not be
> transferred. So, you would need to build a sort of front end app inorder to
> use your data. Unless the only thing you need is the data.
> "Eddie" <Eddie@.discussions.microsoft.com> wrote in message
> news:389AA3B8-1996-487C-A65D-29F4E5404246@.microsoft.com...
> >I currently have a database through a company that has been upkeeping SQL
> >and
> > storing data. I am cancelling subscription and want to retrieve data.
> > They
> > are sending me a backup DVD with all the files. How easy is it to use
> > Microsoft SQL and can the files be transfered to another program such as
> > Access or Excel'
> >
> > Thanks
>
>|||The company is not using the system to it's fullest potential. The users are
not computer savvy and would prefer a more simple system ie: Access. They
don't want to be paying the fees associated to a program they don't use.
They have 7 years of data they need to access and I have to figure out how.
Once we access the data and can retrieve the reports and pictures we won't be
using the system. We want to transfer it to Access.
Thanks
"Keith Kratochvil" wrote:
> You are taking a big step backward going from SQL Server to MS Access and an
> even bigger step if you go to Excel.
> Microsoft SQL Server is an enterprise level relational database management
> system.
> Microsoft Access is a database engine but it is not quite enterprise level.
> MS Excel is can be used to display information to management types and
> create pretty reports, but it is by no means an enterprise storage
> technology.
>
> IF your host sends you the correct information -- backup files created via
> the T-SQL BACKUP command OR database and log files after they ran
> sp_detach_db -- you should be able to restore the database(s) to a SQL
> Server in your environment.
>
> SQL Server does not have "files" (like Excel). It is more similar to MS
> Access in that it has tables within one container (a database).
>
> --
> Keith Kratochvil
>
> "Eddie" <Eddie@.discussions.microsoft.com> wrote in message
> news:389AA3B8-1996-487C-A65D-29F4E5404246@.microsoft.com...
> >I currently have a database through a company that has been upkeeping SQL
> >and
> > storing data. I am cancelling subscription and want to retrieve data.
> > They
> > are sending me a backup DVD with all the files. How easy is it to use
> > Microsoft SQL and can the files be transfered to another program such as
> > Access or Excel'
> >
> > Thanks
>
>|||Maybe SQL Server Express combined with MS Access front-ends is the answer.
SS Express is free.
Randall Arnold
"Eddie" <Eddie@.discussions.microsoft.com> wrote in message
news:49490C53-3ADC-418F-8E8F-A7468A98DEE4@.microsoft.com...
> The company is not using the system to it's fullest potential. The users
> are
> not computer savvy and would prefer a more simple system ie: Access. They
> don't want to be paying the fees associated to a program they don't use.
> They have 7 years of data they need to access and I have to figure out
> how.
> Once we access the data and can retrieve the reports and pictures we won't
> be
> using the system. We want to transfer it to Access.
> Thanks
> "Keith Kratochvil" wrote:
>> You are taking a big step backward going from SQL Server to MS Access and
>> an
>> even bigger step if you go to Excel.
>> Microsoft SQL Server is an enterprise level relational database
>> management
>> system.
>> Microsoft Access is a database engine but it is not quite enterprise
>> level.
>> MS Excel is can be used to display information to management types and
>> create pretty reports, but it is by no means an enterprise storage
>> technology.
>>
>> IF your host sends you the correct information -- backup files created
>> via
>> the T-SQL BACKUP command OR database and log files after they ran
>> sp_detach_db -- you should be able to restore the database(s) to a SQL
>> Server in your environment.
>>
>> SQL Server does not have "files" (like Excel). It is more similar to MS
>> Access in that it has tables within one container (a database).
>>
>> --
>> Keith Kratochvil
>>
>> "Eddie" <Eddie@.discussions.microsoft.com> wrote in message
>> news:389AA3B8-1996-487C-A65D-29F4E5404246@.microsoft.com...
>> >I currently have a database through a company that has been upkeeping
>> >SQL
>> >and
>> > storing data. I am cancelling subscription and want to retrieve data.
>> > They
>> > are sending me a backup DVD with all the files. How easy is it to use
>> > Microsoft SQL and can the files be transfered to another program such
>> > as
>> > Access or Excel'
>> >
>> > Thanks
>>|||Eddie,
You can certainly use Access as the front-end program for
your users, even if you maintain the data in SQL Server.
SQL Server in fact has no front-end, really - just a set of
management tools, so you have many options for your
users.
Since no one has mentioned it, don't overlook the Workgroup
Edition of SQL Server, which might be good for your needs.
http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
Steve Kass
Drew University
Eddie wrote:
>The company is not using the system to it's fullest potential. The users are
>not computer savvy and would prefer a more simple system ie: Access. They
>don't want to be paying the fees associated to a program they don't use.
>They have 7 years of data they need to access and I have to figure out how.
>Once we access the data and can retrieve the reports and pictures we won't be
>using the system. We want to transfer it to Access.
>Thanks
>"Norman Yuan" wrote:
>
>>Are you saying that you have data stored in a SQL Server database, which is
>>hosted by other company, and you now want to sotre your data by yourself? If
>>so, the best way to do is to set up your own SQL Server, have you data on
>>the other SQL Server backed up and restore it on your own SQL Server. (or
>>use detach/attach database from/to SQL Server).
>>There are different edition of SQL Server, from very expensive Enterprise
>>Edition, to free version of SQL Server Express/MSDE. If your data value is
>>not so big (2G or 4G), you can use MSDE or SQL Server Express for free. Of
>>course, some basic knowledge on SQL Server is required (I mean a bit more
>>and complicated that required by using Access).
>>If you want those data being transferred to Access DB, then you can ask your
>>SQL Server host to export it to a *.mdb file. However, in general, only data
>>can be transferred, server object used in that database (view, sp... they
>>may contain critical logics for using the database) would not be
>>transferred. So, you would need to build a sort of front end app inorder to
>>use your data. Unless the only thing you need is the data.
>>"Eddie" <Eddie@.discussions.microsoft.com> wrote in message
>>news:389AA3B8-1996-487C-A65D-29F4E5404246@.microsoft.com...
>>
>>I currently have a database through a company that has been upkeeping SQL
>>and
>>storing data. I am cancelling subscription and want to retrieve data.
>>They
>>are sending me a backup DVD with all the files. How easy is it to use
>>Microsoft SQL and can the files be transfered to another program such as
>>Access or Excel'
>>Thanks
>>
>>|||So, summarize what you want:
1. You have data stored in database of someone's SQL Server;
2. You need to get all data out in this database and svae them in some
formats you choose;
3. You do not need application related server objects/logics in the database
(views, stored procedures, UDFs) once you retrieved the data;
4. The SQL Server host would not do the data retrieving for you. They can
only give you the backup or data file of the database.
Since you want to transfer data from SQL Server to Access (more accurately,
Jet database), there may be some degree of data or data type or data
accuracy loss because of the differences of the two data engine.
Here is what I would do:
1. Get full backup or detached database file (latter would be better);
2. Install a MSDE, which is free version of SQL Server, if the data file of
the database (*.mdf) is less than 2GB.
3. Restore/attach the databse to MSDE.
4. Use Access as tool to connect to MSDE and start retrieve data. You can
create an Access ADP project to use as MSDE front end UI. Then you can open
each table to export, or write some macro/VBA code to retireve specific data
and output to specific format.
5. Since I have the database on my onw, no-cost MSDE engine, I can try and
try until satisfied totally (of course do it on a copy of the data).
6, Do the final retrieving and then uninstall the MSDE.
"Eddie" <Eddie@.discussions.microsoft.com> wrote in message
news:E426270B-5851-456C-B190-1D747BAFA433@.microsoft.com...
> The company is not using the system to it's fullest potential. The users
> are
> not computer savvy and would prefer a more simple system ie: Access. They
> don't want to be paying the fees associated to a program they don't use.
> They have 7 years of data they need to access and I have to figure out
> how.
> Once we access the data and can retrieve the reports and pictures we won't
> be
> using the system. We want to transfer it to Access.
> Thanks
> "Norman Yuan" wrote:
>> Are you saying that you have data stored in a SQL Server database, which
>> is
>> hosted by other company, and you now want to sotre your data by yourself?
>> If
>> so, the best way to do is to set up your own SQL Server, have you data on
>> the other SQL Server backed up and restore it on your own SQL Server. (or
>> use detach/attach database from/to SQL Server).
>> There are different edition of SQL Server, from very expensive Enterprise
>> Edition, to free version of SQL Server Express/MSDE. If your data value
>> is
>> not so big (2G or 4G), you can use MSDE or SQL Server Express for free.
>> Of
>> course, some basic knowledge on SQL Server is required (I mean a bit more
>> and complicated that required by using Access).
>> If you want those data being transferred to Access DB, then you can ask
>> your
>> SQL Server host to export it to a *.mdb file. However, in general, only
>> data
>> can be transferred, server object used in that database (view, sp... they
>> may contain critical logics for using the database) would not be
>> transferred. So, you would need to build a sort of front end app inorder
>> to
>> use your data. Unless the only thing you need is the data.
>> "Eddie" <Eddie@.discussions.microsoft.com> wrote in message
>> news:389AA3B8-1996-487C-A65D-29F4E5404246@.microsoft.com...
>> >I currently have a database through a company that has been upkeeping
>> >SQL
>> >and
>> > storing data. I am cancelling subscription and want to retrieve data.
>> > They
>> > are sending me a backup DVD with all the files. How easy is it to use
>> > Microsoft SQL and can the files be transfered to another program such
>> > as
>> > Access or Excel'
>> >
>> > Thanks
>>
Tuesday, March 20, 2012
Pulling data from two different servers
Hi,
I have some data I'd like to retrieve and run a report from, but the data is
one 2 different servers. I don't know how to set this up in report manager
or create a query that says something like
select server.database.field, differentserver.database.field etc.
Any help would be greatly appreciated.
Thanks
JillJill,
The solution to your question lies solely within your SQL query and
Reporting Services will not affect how you solve this problem.
The easiest way to perform this task is to use a four-part query that
specifies the tables you want to pull from your remote server.
The format for the reference to the table is
"Servername.databasename.owner.table"
For example
select *
from myserver1.pubs.dbo.authors as A
join myserver2.pubs.dbo.titleauthor as B
on A.au_id = b.au_id
To perform this type of query you'll need to create a linked server. For
instance, if your connection is "myserver1" you need to create a linked
server on the myserver1 server to myserver2. If you Google "Linked Server
SQL Security" you will find articles instructing you how to do so. While
explaining the query is fairly straight forward, explaining Linked Server
security and permissions is best left up to people who have already written
about it.
Hope this helps.
"Jill" <Jill@.discussions.microsoft.com> wrote in message
news:B37A4B57-F8A9-44DC-B43F-63F63F1902F3@.microsoft.com...
> Hi,
> I have some data I'd like to retrieve and run a report from, but the data
> is
> one 2 different servers. I don't know how to set this up in report
> manager
> or create a query that says something like
> select server.database.field, differentserver.database.field etc.
> Any help would be greatly appreciated.
> Thanks
> Jill|||If you tables are large you need to be very careful with how you use the 4
part naming method of using linked servers. You can end up where an
incredible amount of data is brought locally to do the join. You are better
off to do this in two parts. First use openquery instead of 4 part naming.
Have two temp tables that two separate queries put their results into. Then
join the two temp tables together. The performance will be much much better.
If your tables are of any size I strongly recommend this approach (speaking
from painful learning experience).
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Joel Rumerman" <JRumerman@.prometheuslabs.com> wrote in message
news:%23hmoatSFFHA.3732@.TK2MSFTNGP14.phx.gbl...
> Jill,
> The solution to your question lies solely within your SQL query and
> Reporting Services will not affect how you solve this problem.
> The easiest way to perform this task is to use a four-part query that
> specifies the tables you want to pull from your remote server.
> The format for the reference to the table is
> "Servername.databasename.owner.table"
> For example
> select *
> from myserver1.pubs.dbo.authors as A
> join myserver2.pubs.dbo.titleauthor as B
> on A.au_id = b.au_id
> To perform this type of query you'll need to create a linked server. For
> instance, if your connection is "myserver1" you need to create a linked
> server on the myserver1 server to myserver2. If you Google "Linked Server
> SQL Security" you will find articles instructing you how to do so. While
> explaining the query is fairly straight forward, explaining Linked Server
> security and permissions is best left up to people who have already
written
> about it.
> Hope this helps.
>
> "Jill" <Jill@.discussions.microsoft.com> wrote in message
> news:B37A4B57-F8A9-44DC-B43F-63F63F1902F3@.microsoft.com...
> > Hi,
> > I have some data I'd like to retrieve and run a report from, but the
data
> > is
> > one 2 different servers. I don't know how to set this up in report
> > manager
> > or create a query that says something like
> > select server.database.field, differentserver.database.field etc.
> > Any help would be greatly appreciated.
> >
> > Thanks
> > Jill
>|||While not as robust as the other solutions, you could also use a subreport
to accomplish the same thing.
Mike G.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:uZezRATFFHA.3504@.TK2MSFTNGP12.phx.gbl...
> If you tables are large you need to be very careful with how you use the 4
> part naming method of using linked servers. You can end up where an
> incredible amount of data is brought locally to do the join. You are
> better
> off to do this in two parts. First use openquery instead of 4 part naming.
> Have two temp tables that two separate queries put their results into.
> Then
> join the two temp tables together. The performance will be much much
> better.
> If your tables are of any size I strongly recommend this approach
> (speaking
> from painful learning experience).
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Joel Rumerman" <JRumerman@.prometheuslabs.com> wrote in message
> news:%23hmoatSFFHA.3732@.TK2MSFTNGP14.phx.gbl...
>> Jill,
>> The solution to your question lies solely within your SQL query and
>> Reporting Services will not affect how you solve this problem.
>> The easiest way to perform this task is to use a four-part query that
>> specifies the tables you want to pull from your remote server.
>> The format for the reference to the table is
>> "Servername.databasename.owner.table"
>> For example
>> select *
>> from myserver1.pubs.dbo.authors as A
>> join myserver2.pubs.dbo.titleauthor as B
>> on A.au_id = b.au_id
>> To perform this type of query you'll need to create a linked server. For
>> instance, if your connection is "myserver1" you need to create a linked
>> server on the myserver1 server to myserver2. If you Google "Linked Server
>> SQL Security" you will find articles instructing you how to do so. While
>> explaining the query is fairly straight forward, explaining Linked Server
>> security and permissions is best left up to people who have already
> written
>> about it.
>> Hope this helps.
>>
>> "Jill" <Jill@.discussions.microsoft.com> wrote in message
>> news:B37A4B57-F8A9-44DC-B43F-63F63F1902F3@.microsoft.com...
>> > Hi,
>> > I have some data I'd like to retrieve and run a report from, but the
> data
>> > is
>> > one 2 different servers. I don't know how to set this up in report
>> > manager
>> > or create a query that says something like
>> > select server.database.field, differentserver.database.field etc.
>> > Any help would be greatly appreciated.
>> >
>> > Thanks
>> > Jill
>>
>|||True. I should have mentioned this. If it is master/detail type of
relationship the subreports are the way to go.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Mike G." <theNOSPAMjunkbox@.comcast.net> wrote in message
news:OMxYhDTFFHA.1968@.tk2msftngp13.phx.gbl...
> While not as robust as the other solutions, you could also use a subreport
> to accomplish the same thing.
> Mike G.
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:uZezRATFFHA.3504@.TK2MSFTNGP12.phx.gbl...
> > If you tables are large you need to be very careful with how you use the
4
> > part naming method of using linked servers. You can end up where an
> > incredible amount of data is brought locally to do the join. You are
> > better
> > off to do this in two parts. First use openquery instead of 4 part
naming.
> > Have two temp tables that two separate queries put their results into.
> > Then
> > join the two temp tables together. The performance will be much much
> > better.
> > If your tables are of any size I strongly recommend this approach
> > (speaking
> > from painful learning experience).
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> >
> > "Joel Rumerman" <JRumerman@.prometheuslabs.com> wrote in message
> > news:%23hmoatSFFHA.3732@.TK2MSFTNGP14.phx.gbl...
> >> Jill,
> >>
> >> The solution to your question lies solely within your SQL query and
> >> Reporting Services will not affect how you solve this problem.
> >>
> >> The easiest way to perform this task is to use a four-part query that
> >> specifies the tables you want to pull from your remote server.
> >>
> >> The format for the reference to the table is
> >> "Servername.databasename.owner.table"
> >>
> >> For example
> >>
> >> select *
> >> from myserver1.pubs.dbo.authors as A
> >> join myserver2.pubs.dbo.titleauthor as B
> >> on A.au_id = b.au_id
> >>
> >> To perform this type of query you'll need to create a linked server.
For
> >> instance, if your connection is "myserver1" you need to create a linked
> >> server on the myserver1 server to myserver2. If you Google "Linked
Server
> >> SQL Security" you will find articles instructing you how to do so.
While
> >> explaining the query is fairly straight forward, explaining Linked
Server
> >> security and permissions is best left up to people who have already
> > written
> >> about it.
> >>
> >> Hope this helps.
> >>
> >>
> >> "Jill" <Jill@.discussions.microsoft.com> wrote in message
> >> news:B37A4B57-F8A9-44DC-B43F-63F63F1902F3@.microsoft.com...
> >> > Hi,
> >> > I have some data I'd like to retrieve and run a report from, but the
> > data
> >> > is
> >> > one 2 different servers. I don't know how to set this up in report
> >> > manager
> >> > or create a query that says something like
> >> > select server.database.field, differentserver.database.field etc.
> >> > Any help would be greatly appreciated.
> >> >
> >> > Thanks
> >> > Jill
> >>
> >>
> >
> >
>
I have some data I'd like to retrieve and run a report from, but the data is
one 2 different servers. I don't know how to set this up in report manager
or create a query that says something like
select server.database.field, differentserver.database.field etc.
Any help would be greatly appreciated.
Thanks
JillJill,
The solution to your question lies solely within your SQL query and
Reporting Services will not affect how you solve this problem.
The easiest way to perform this task is to use a four-part query that
specifies the tables you want to pull from your remote server.
The format for the reference to the table is
"Servername.databasename.owner.table"
For example
select *
from myserver1.pubs.dbo.authors as A
join myserver2.pubs.dbo.titleauthor as B
on A.au_id = b.au_id
To perform this type of query you'll need to create a linked server. For
instance, if your connection is "myserver1" you need to create a linked
server on the myserver1 server to myserver2. If you Google "Linked Server
SQL Security" you will find articles instructing you how to do so. While
explaining the query is fairly straight forward, explaining Linked Server
security and permissions is best left up to people who have already written
about it.
Hope this helps.
"Jill" <Jill@.discussions.microsoft.com> wrote in message
news:B37A4B57-F8A9-44DC-B43F-63F63F1902F3@.microsoft.com...
> Hi,
> I have some data I'd like to retrieve and run a report from, but the data
> is
> one 2 different servers. I don't know how to set this up in report
> manager
> or create a query that says something like
> select server.database.field, differentserver.database.field etc.
> Any help would be greatly appreciated.
> Thanks
> Jill|||If you tables are large you need to be very careful with how you use the 4
part naming method of using linked servers. You can end up where an
incredible amount of data is brought locally to do the join. You are better
off to do this in two parts. First use openquery instead of 4 part naming.
Have two temp tables that two separate queries put their results into. Then
join the two temp tables together. The performance will be much much better.
If your tables are of any size I strongly recommend this approach (speaking
from painful learning experience).
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Joel Rumerman" <JRumerman@.prometheuslabs.com> wrote in message
news:%23hmoatSFFHA.3732@.TK2MSFTNGP14.phx.gbl...
> Jill,
> The solution to your question lies solely within your SQL query and
> Reporting Services will not affect how you solve this problem.
> The easiest way to perform this task is to use a four-part query that
> specifies the tables you want to pull from your remote server.
> The format for the reference to the table is
> "Servername.databasename.owner.table"
> For example
> select *
> from myserver1.pubs.dbo.authors as A
> join myserver2.pubs.dbo.titleauthor as B
> on A.au_id = b.au_id
> To perform this type of query you'll need to create a linked server. For
> instance, if your connection is "myserver1" you need to create a linked
> server on the myserver1 server to myserver2. If you Google "Linked Server
> SQL Security" you will find articles instructing you how to do so. While
> explaining the query is fairly straight forward, explaining Linked Server
> security and permissions is best left up to people who have already
written
> about it.
> Hope this helps.
>
> "Jill" <Jill@.discussions.microsoft.com> wrote in message
> news:B37A4B57-F8A9-44DC-B43F-63F63F1902F3@.microsoft.com...
> > Hi,
> > I have some data I'd like to retrieve and run a report from, but the
data
> > is
> > one 2 different servers. I don't know how to set this up in report
> > manager
> > or create a query that says something like
> > select server.database.field, differentserver.database.field etc.
> > Any help would be greatly appreciated.
> >
> > Thanks
> > Jill
>|||While not as robust as the other solutions, you could also use a subreport
to accomplish the same thing.
Mike G.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:uZezRATFFHA.3504@.TK2MSFTNGP12.phx.gbl...
> If you tables are large you need to be very careful with how you use the 4
> part naming method of using linked servers. You can end up where an
> incredible amount of data is brought locally to do the join. You are
> better
> off to do this in two parts. First use openquery instead of 4 part naming.
> Have two temp tables that two separate queries put their results into.
> Then
> join the two temp tables together. The performance will be much much
> better.
> If your tables are of any size I strongly recommend this approach
> (speaking
> from painful learning experience).
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Joel Rumerman" <JRumerman@.prometheuslabs.com> wrote in message
> news:%23hmoatSFFHA.3732@.TK2MSFTNGP14.phx.gbl...
>> Jill,
>> The solution to your question lies solely within your SQL query and
>> Reporting Services will not affect how you solve this problem.
>> The easiest way to perform this task is to use a four-part query that
>> specifies the tables you want to pull from your remote server.
>> The format for the reference to the table is
>> "Servername.databasename.owner.table"
>> For example
>> select *
>> from myserver1.pubs.dbo.authors as A
>> join myserver2.pubs.dbo.titleauthor as B
>> on A.au_id = b.au_id
>> To perform this type of query you'll need to create a linked server. For
>> instance, if your connection is "myserver1" you need to create a linked
>> server on the myserver1 server to myserver2. If you Google "Linked Server
>> SQL Security" you will find articles instructing you how to do so. While
>> explaining the query is fairly straight forward, explaining Linked Server
>> security and permissions is best left up to people who have already
> written
>> about it.
>> Hope this helps.
>>
>> "Jill" <Jill@.discussions.microsoft.com> wrote in message
>> news:B37A4B57-F8A9-44DC-B43F-63F63F1902F3@.microsoft.com...
>> > Hi,
>> > I have some data I'd like to retrieve and run a report from, but the
> data
>> > is
>> > one 2 different servers. I don't know how to set this up in report
>> > manager
>> > or create a query that says something like
>> > select server.database.field, differentserver.database.field etc.
>> > Any help would be greatly appreciated.
>> >
>> > Thanks
>> > Jill
>>
>|||True. I should have mentioned this. If it is master/detail type of
relationship the subreports are the way to go.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Mike G." <theNOSPAMjunkbox@.comcast.net> wrote in message
news:OMxYhDTFFHA.1968@.tk2msftngp13.phx.gbl...
> While not as robust as the other solutions, you could also use a subreport
> to accomplish the same thing.
> Mike G.
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:uZezRATFFHA.3504@.TK2MSFTNGP12.phx.gbl...
> > If you tables are large you need to be very careful with how you use the
4
> > part naming method of using linked servers. You can end up where an
> > incredible amount of data is brought locally to do the join. You are
> > better
> > off to do this in two parts. First use openquery instead of 4 part
naming.
> > Have two temp tables that two separate queries put their results into.
> > Then
> > join the two temp tables together. The performance will be much much
> > better.
> > If your tables are of any size I strongly recommend this approach
> > (speaking
> > from painful learning experience).
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> >
> > "Joel Rumerman" <JRumerman@.prometheuslabs.com> wrote in message
> > news:%23hmoatSFFHA.3732@.TK2MSFTNGP14.phx.gbl...
> >> Jill,
> >>
> >> The solution to your question lies solely within your SQL query and
> >> Reporting Services will not affect how you solve this problem.
> >>
> >> The easiest way to perform this task is to use a four-part query that
> >> specifies the tables you want to pull from your remote server.
> >>
> >> The format for the reference to the table is
> >> "Servername.databasename.owner.table"
> >>
> >> For example
> >>
> >> select *
> >> from myserver1.pubs.dbo.authors as A
> >> join myserver2.pubs.dbo.titleauthor as B
> >> on A.au_id = b.au_id
> >>
> >> To perform this type of query you'll need to create a linked server.
For
> >> instance, if your connection is "myserver1" you need to create a linked
> >> server on the myserver1 server to myserver2. If you Google "Linked
Server
> >> SQL Security" you will find articles instructing you how to do so.
While
> >> explaining the query is fairly straight forward, explaining Linked
Server
> >> security and permissions is best left up to people who have already
> > written
> >> about it.
> >>
> >> Hope this helps.
> >>
> >>
> >> "Jill" <Jill@.discussions.microsoft.com> wrote in message
> >> news:B37A4B57-F8A9-44DC-B43F-63F63F1902F3@.microsoft.com...
> >> > Hi,
> >> > I have some data I'd like to retrieve and run a report from, but the
> > data
> >> > is
> >> > one 2 different servers. I don't know how to set this up in report
> >> > manager
> >> > or create a query that says something like
> >> > select server.database.field, differentserver.database.field etc.
> >> > Any help would be greatly appreciated.
> >> >
> >> > Thanks
> >> > Jill
> >>
> >>
> >
> >
>
Subscribe to:
Posts (Atom)