Showing posts with label database2. Show all posts
Showing posts with label database2. Show all posts

Monday, March 19, 2012

Joining one database table with other database table in stroed procedures.

Hi All,
I need to perform a join on table1 of database1 with table2 or database2, in a stored procedure and return to my web application.. For this I'm providing execute permission on stored procedure, in database1 and "SELECT" permission on database2 table 2, to my database webuser.
Is there any way ( Another stored procedure in database2), i can get join on two tables without SELECT permission right on table2 ( or table1).
Purely using stored procedures. If so how?Normally you only need to grant execute permission on a stored procedure not select permission on the underlying tables.

Past that I would setup a sp to on database1 to return the data and make sure the webuser has an a security path from database1 to database2. How do yo uhave security setup for this user on databse1 and database2?|||Hi Paul,
Thanks, What is meant by security path? I didnot get ur point.
User have only public permission on both databases. Is it not enough? I do have execute permission on sp1 (Stored Procedure) in database1.
but in sp1 I have a select statement which is a join on database2 table-'table2'. When I tried to execute sp1 from web application only with execute permission on sp1-
I got an error as Webuser does not have 'SELECT' permission on table2 of database2. So I provided it and sp1 worked fine. But I don't want to use a SELECT permission- What is alternate, for the join.
sp1 has simple
SELECT *
FROM database1.dbo.table1 t1
INNER JOIN database2.dbo.table2 t2
ON (t1.col1 = t2.col1)

Originally posted by Paul Young
Normally you only need to grant execute permission on a stored procedure not select permission on the underlying tables.

Past that I would setup a sp to on database1 to return the data and make sure the webuser has an a security path from database1 to database2. How do yo uhave security setup for this user on databse1 and database2?|||by security path I was refering to how a user gets authenticated on server2 when making a connection from server1.

You might try using OPENQUERY to call a stored procedure on server2 and use the results to join to a table on server1. I haven't had the need for this in the past so I am working on theory here. Check BOL for usage on OPENQUERY, they have some good examples.|||When calling a stored proc from database 1, you need execute privs on the proc. You do not need to give the user privs on the table itself as long as it is in database 1, too. If database 2 is owned by the same user that is the owner of database 1, you do not need explicit permissions on the object in table 2 accessed by the proc. However, if the database owners are different for the two databases, the user calling the proc must have explicit permissions in the second database.

If need be, you can change the database owner by:

EXEC sp_changedbowner 'username'

Execute this in the database you want to change.|||Well Both databases are on same server. Is OPENQUERY solve in this case too?|||Originally posted by soumyag
Well Both databases are on same server.

It doesn't matter what server they are on, the owner of the databases is what matters. You can have multiple databases on the same server with different owners. In query analyzer, run:

sp_helpdb

It will list the owner for each of the databases. If the owner is different, run sp_changedbowner to set them the same.|||Hi,
Both are on same server, but owned by different users. And I don't have any right to change the dbowners But how it will help in writing a join on tables. What is other alternative way to solve this problem.
Thanks.

Originally posted by bglass

It doesn't matter what server they are on, the owner of the databases is what matters. You can have multiple databases on the same server with different owners. In query analyzer, run:

sp_helpdb

It will list the owner for each of the databases. If the owner is different, run sp_changedbowner to set them the same.

Monday, March 12, 2012

Joing tables from different databases - performance issues

Hello,
What is performance difference between joining tables
1. from the same database
2. from different databases located on the same instance of MS SQL Server
3. from different databases located on different instances of MS SQL
Server (linked servers) when these instances are located on the same
physical machine
4. from different databases located on different instances of MS SQL
Server (linked servers) when these instances are located on different
physical machines and these machines contact each other through LAN
I guess, performance or variant 1 is the best and for 4 is the worst,
but if (and how big) are there differences between: 1 and 2, 2 and 3.
Thanks a lot.
MerlinIt is really opene-end question , because only you do know about your tables
structure, indexes and amount of data.
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:u5qDn7WAGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Hello,
>
> What is performance difference between joining tables
> 1. from the same database
> 2. from different databases located on the same instance of MS SQL Server
> 3. from different databases located on different instances of MS SQL
> Server (linked servers) when these instances are located on the same
> physical machine
> 4. from different databases located on different instances of MS SQL
> Server (linked servers) when these instances are located on different
> physical machines and these machines contact each other through LAN
> I guess, performance or variant 1 is the best and for 4 is the worst, but
> if (and how big) are there differences between: 1 and 2, 2 and 3.
>
> Thanks a lot.
> Merlin|||1 is same as 2. The optimizer has all the information and can process the qu
ery the same whether the
tables are in the same database or different database. For 3 and 4, the quer
y is optimized locally
and parts of the query is passed onto the linked server. This limits the fle
xibility that the
optimizer otherwise has. 4 is obviously worse than 3. For quantification, yo
u need to test with your
data, schema, queries etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:u5qDn7WAGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Hello,
>
> What is performance difference between joining tables
> 1. from the same database
> 2. from different databases located on the same instance of MS SQL Server
> 3. from different databases located on different instances of MS SQL Serve
r (linked servers) when
> these instances are located on the same physical machine
> 4. from different databases located on different instances of MS SQL Serve
r (linked servers) when
> these instances are located on different physical machines and these machi
nes contact each other
> through LAN
> I guess, performance or variant 1 is the best and for 4 is the worst, but
if (and how big) are
> there differences between: 1 and 2, 2 and 3.
>
> Thanks a lot.
> Merlin|||
> It is really opene-end question , because only you do know about your tabl
es
> structure, indexes and amount of data.
Structure of these tables is the same in all variants.
I don't expect exact answers, because it is impossible without exact
info, but I think it is possible to point at mainspriongs which affect
performance.
Difference between variants 1 and 4 is obvious.
What about difference between 1 and 2, 2 and 3 it's not obvious for me.
Merlin|||U?ytkownik Tibor Karaszi napisa?:

> 1 is same as 2. The optimizer has all the information and can process
> the query the same whether the tables are in the same database or
> different database. For 3 and 4, the query is optimized locally and
> parts of the query is passed onto the linked server. This limits the
> flexibility that the optimizer otherwise has. 4 is obviously worse than
> 3. For quantification, you need to test with your data, schema, queries
> etc.
Thanks, this is the info what I've expcected.
Do you have feeling what difference can be between variants 2 and 3
(small, medium, big)
Merlin|||> Do you have feeling what difference can be between variants 2 and 3 (small
, medium, big)
I'd say medium to big. But you can always find exceptions.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:43A160FA.6060206@.NOSPAM_poczta.onet.pl...
> U?ytkownik Tibor Karaszi napisa?:
>
> Thanks, this is the info what I've expcected.
> Do you have feeling what difference can be between variants 2 and 3 (small
, medium, big)
> Merlin
>

Joing tables from different databases - performance issues

Hello,
What is performance difference between joining tables
1. from the same database
2. from different databases located on the same instance of MS SQL Server
3. from different databases located on different instances of MS SQL
Server (linked servers) when these instances are located on the same
physical machine
4. from different databases located on different instances of MS SQL
Server (linked servers) when these instances are located on different
physical machines and these machines contact each other through LAN
I guess, performance or variant 1 is the best and for 4 is the worst,
but if (and how big) are there differences between: 1 and 2, 2 and 3.
Thanks a lot.
Merlin
That really depends on a lot of things such as the queries
themselves and on your network configuration - issues such
as are the servers on the same switch. There is no black and
white percentage to give you. If you are concerned about
performance across servers, you may want to check the
performance tuning tips in the following article:
http://www.sql-server-performance.com/linked_server.asp
-Sue
On Thu, 15 Dec 2005 13:00:15 +0100, MerlinXP
<MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote:

>Hello,
>
>What is performance difference between joining tables
>1. from the same database
>2. from different databases located on the same instance of MS SQL Server
>3. from different databases located on different instances of MS SQL
>Server (linked servers) when these instances are located on the same
>physical machine
>4. from different databases located on different instances of MS SQL
>Server (linked servers) when these instances are located on different
>physical machines and these machines contact each other through LAN
>I guess, performance or variant 1 is the best and for 4 is the worst,
>but if (and how big) are there differences between: 1 and 2, 2 and 3.
>
>Thanks a lot.
>Merlin

Joing tables from different databases - performance issues

Hello,
What is performance difference between joining tables
1. from the same database
2. from different databases located on the same instance of MS SQL Server
3. from different databases located on different instances of MS SQL
Server (linked servers) when these instances are located on the same
physical machine
4. from different databases located on different instances of MS SQL
Server (linked servers) when these instances are located on different
physical machines and these machines contact each other through LAN
I guess, performance or variant 1 is the best and for 4 is the worst,
but if (and how big) are there differences between: 1 and 2, 2 and 3.
Thanks a lot.
Merlin
It is really opene-end question , because only you do know about your tables
structure, indexes and amount of data.
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:u5qDn7WAGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Hello,
>
> What is performance difference between joining tables
> 1. from the same database
> 2. from different databases located on the same instance of MS SQL Server
> 3. from different databases located on different instances of MS SQL
> Server (linked servers) when these instances are located on the same
> physical machine
> 4. from different databases located on different instances of MS SQL
> Server (linked servers) when these instances are located on different
> physical machines and these machines contact each other through LAN
> I guess, performance or variant 1 is the best and for 4 is the worst, but
> if (and how big) are there differences between: 1 and 2, 2 and 3.
>
> Thanks a lot.
> Merlin
|||1 is same as 2. The optimizer has all the information and can process the query the same whether the
tables are in the same database or different database. For 3 and 4, the query is optimized locally
and parts of the query is passed onto the linked server. This limits the flexibility that the
optimizer otherwise has. 4 is obviously worse than 3. For quantification, you need to test with your
data, schema, queries etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:u5qDn7WAGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Hello,
>
> What is performance difference between joining tables
> 1. from the same database
> 2. from different databases located on the same instance of MS SQL Server
> 3. from different databases located on different instances of MS SQL Server (linked servers) when
> these instances are located on the same physical machine
> 4. from different databases located on different instances of MS SQL Server (linked servers) when
> these instances are located on different physical machines and these machines contact each other
> through LAN
> I guess, performance or variant 1 is the best and for 4 is the worst, but if (and how big) are
> there differences between: 1 and 2, 2 and 3.
>
> Thanks a lot.
> Merlin
|||
> It is really opene-end question , because only you do know about your tables
> structure, indexes and amount of data.
Structure of these tables is the same in all variants.
I don't expect exact answers, because it is impossible without exact
info, but I think it is possible to point at mainspriongs which affect
performance.
Difference between variants 1 and 4 is obvious.
What about difference between 1 and 2, 2 and 3 it's not obvious for me.
Merlin
|||U?ytkownik Tibor Karaszi napisa?:

> 1 is same as 2. The optimizer has all the information and can process
> the query the same whether the tables are in the same database or
> different database. For 3 and 4, the query is optimized locally and
> parts of the query is passed onto the linked server. This limits the
> flexibility that the optimizer otherwise has. 4 is obviously worse than
> 3. For quantification, you need to test with your data, schema, queries
> etc.
Thanks, this is the info what I've expcected.
Do you have feeling what difference can be between variants 2 and 3
(small, medium, big)
Merlin
|||> Do you have feeling what difference can be between variants 2 and 3 (small, medium, big)
I'd say medium to big. But you can always find exceptions.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:43A160FA.6060206@.NOSPAM_poczta.onet.pl...
> U?ytkownik Tibor Karaszi napisa?:
>
> Thanks, this is the info what I've expcected.
> Do you have feeling what difference can be between variants 2 and 3 (small, medium, big)
> Merlin
>

Friday, March 9, 2012

Joing tables from different databases - performance issues

Hello,
What is performance difference between joining tables
1. from the same database
2. from different databases located on the same instance of MS SQL Server
3. from different databases located on different instances of MS SQL
Server (linked servers) when these instances are located on the same
physical machine
4. from different databases located on different instances of MS SQL
Server (linked servers) when these instances are located on different
physical machines and these machines contact each other through LAN
I guess, performance or variant 1 is the best and for 4 is the worst,
but if (and how big) are there differences between: 1 and 2, 2 and 3.
Thanks a lot.
MerlinThat really depends on a lot of things such as the queries
themselves and on your network configuration - issues such
as are the servers on the same switch. There is no black and
white percentage to give you. If you are concerned about
performance across servers, you may want to check the
performance tuning tips in the following article:
http://www.sql-server-performance.com/linked_server.asp
-Sue
On Thu, 15 Dec 2005 13:00:15 +0100, MerlinXP
<MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote:

>Hello,
>
>What is performance difference between joining tables
>1. from the same database
>2. from different databases located on the same instance of MS SQL Server
>3. from different databases located on different instances of MS SQL
>Server (linked servers) when these instances are located on the same
>physical machine
>4. from different databases located on different instances of MS SQL
>Server (linked servers) when these instances are located on different
>physical machines and these machines contact each other through LAN
>I guess, performance or variant 1 is the best and for 4 is the worst,
>but if (and how big) are there differences between: 1 and 2, 2 and 3.
>
>Thanks a lot.
>Merlin

Wednesday, March 7, 2012

Join tables from two databases

Say I have two database files, database1.sdf and database2.sdf, how can I make a select that joins tables from both?

I'm using C#.

Thanks!

There is no implicit support for querying across multiple databases with the SQL Server CE engine itself. However you could achieve a similar result by loading data from the two databases into an in-memory dataset from which you could then query the data.