<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
SQL Server Newbies
»
Alternative to views
23 posts, Page 1 of 3
1
2
3
»
»»
Alternative to views
Rate Topic
Display Mode
Topic Options
Author
Message
wanox
wanox
Posted Thursday, November 15, 2012 8:12 PM
SSC Rookie
Group: General Forum Members
Last Login: Friday, May 10, 2013 9:01 AM
Points: 25,
Visits: 188
Databases in our servers communicate with each other using views. For instance, dbA has views that gets data from db2 through its views. Is there another way for a database to use data from another database other than views.
Post #1385434
dwain.c
dwain.c
Posted Thursday, November 15, 2012 8:53 PM
SSCrazy
Group: General Forum Members
Last Login: Today @ 8:01 PM
Points: 2,340,
Visits: 3,163
Using linked servers (
http://msdn.microsoft.com/en-us/library/ms188279.aspx
) you should be able to access tables directly given appropriate authorizations.
No loops! No CURSORs! No RBAR! Hoo-uh!
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Are you too recursively challenged?
Splitting strings based on patterns can be fast!
Post #1385440
opc.three
opc.three
Posted Thursday, November 15, 2012 10:10 PM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 5:26 PM
Points: 6,696,
Visits: 11,715
Linked Servers are useful for server-to-server communication but if you're just needing to communicate from one database to another on the same instance then Linked Servers would not be a great option.
With the proper permissions any SQL statement executed within a database can use 3-part naming to access data in any other database on the instance, e.g. This
SELECT column_a
FROM dbA.dbo.table_a;
can be run in dbB to retrieve data.
SQL statements that use 3-part naming can belong to views, triggers, stored procedures, user-defined functions...pretty much any SQL object can contain a query with a 3-part name.
__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Believe you can and you're halfway there.
--Theodore Roosevelt
Everything Should Be Made as Simple as Possible, But Not Simpler
--Albert Einstein
The significant problems we face cannot be solved at the same level of thinking we were at when we created them.
--Albert Einstein
1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples.
--Giordy
Post #1385463
dwain.c
dwain.c
Posted Thursday, November 15, 2012 10:16 PM
SSCrazy
Group: General Forum Members
Last Login: Today @ 8:01 PM
Points: 2,340,
Visits: 3,163
opc.three (11/15/2012)
Linked Servers are useful for server-to-server communication but if you're just needing to communicate from one database to another on the same instance then Linked Servers would not be a great option.
With the proper permissions any SQL statement executed within a database can use 3-part naming to access data in any other database on the instance, e.g. This
SELECT column_a
FROM dbA.dbo.table_a;
can be run in dbB to retrieve data.
SQL statements that use 3-part naming can belong to views, triggers, stored procedures, user-defined functions...pretty much any SQL object can contain a query with a 3-part name.
dbA can be a DB2 database? I didn't know that.
No loops! No CURSORs! No RBAR! Hoo-uh!
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Are you too recursively challenged?
Splitting strings based on patterns can be fast!
Post #1385469
opc.three
opc.three
Posted Friday, November 16, 2012 7:42 AM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 5:26 PM
Points: 6,696,
Visits: 11,715
dwain.c (11/15/2012)
opc.three (11/15/2012)
Linked Servers are useful for server-to-server communication but if you're just needing to communicate from one database to another on the same instance then Linked Servers would not be a great option.
With the proper permissions any SQL statement executed within a database can use 3-part naming to access data in any other database on the instance, e.g. This
SELECT column_a
FROM dbA.dbo.table_a;
can be run in dbB to retrieve data.
SQL statements that use 3-part naming can belong to views, triggers, stored procedures, user-defined functions...pretty much any SQL object can contain a query with a 3-part name.
dbA can be a DB2 database? I didn't know that.
Not straightaway but we could always define a SYNONYM named dbA.dbo.table that resolved to a remote table referenced by its 4-part name, however that's a different bunny trail.
There is an ambiguity in the original post, and I read "db2" as the name of a SQL Server database on the same instance as dbA. If "db2" actually means an instance of an IBM DB2 database to the original poster then Linked Servers are pretty much the only option if the requirement was to do everything in T-SQL. if that is the case then I would edit to replace "3-part naming" in my initial post with "4-part naming."
__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Believe you can and you're halfway there.
--Theodore Roosevelt
Everything Should Be Made as Simple as Possible, But Not Simpler
--Albert Einstein
The significant problems we face cannot be solved at the same level of thinking we were at when we created them.
--Albert Einstein
1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples.
--Giordy
Post #1385711
wanox
wanox
Posted Saturday, November 17, 2012 6:31 AM
SSC Rookie
Group: General Forum Members
Last Login: Friday, May 10, 2013 9:01 AM
Points: 25,
Visits: 188
We are trying to stay away from linked servers. Alos we have a policy to to directly access the tables in the other databases and that is why we use views. Synonyms might not be a bad idea. I'd look into that, otherwise does anyone have an other tricks for databases to communicate.
Post #1385937
Jeff Moden
Jeff Moden
Posted Saturday, November 17, 2012 2:43 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 9:57 PM
Points: 32,893,
Visits: 26,771
wanox (11/15/2012)
Databases in our servers communicate with each other using views. For instance, dbA has views that gets data from db2 through its views. Is there another way for a database to use data from another database other than views.
As a couple of folks have pointed out, Synonyms are probably the best way to go here and can be pointed at either a local (same instance) database or a linked server database. There are two great things about synonyms... you don't need to remember to regen (SELECT *... a very bad thing) or rebuild (discreet named columns) views if columns are added to or deleted from the underlying tables and, as with views, you can avoid making your code dependent on another database by sticking to a 2 part naming convention.
I said "probably" because the bad part about Synonyms is that they can't hide columns or rows (if you need to) using just the Synonym like you can with a View.
--Jeff Moden
"
RBAR
is pronounced "ree-bar" and is a "Modenism" for "
R
ow-
B
y-
A
gonizing-
R
ow".
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1386009
wanox
wanox
Posted Sunday, November 18, 2012 3:25 PM
SSC Rookie
Group: General Forum Members
Last Login: Friday, May 10, 2013 9:01 AM
Points: 25,
Visits: 188
Thanks Jeff. I ahve a presentation ready to sell Syninyms to the rest fo my team.
Thank you all for your responses!!
Post #1386107
Jeff Moden
Jeff Moden
Posted Monday, November 19, 2012 6:03 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 9:57 PM
Points: 32,893,
Visits: 26,771
wanox (11/18/2012)
Thanks Jeff. I ahve a presentation ready to sell Syninyms to the rest fo my team.
Thank you all for your responses!!
Be prepared. Like I said, the
ONLY
advantage that synonyms have over "pass through" views is that they never need to be regenerated if the underlying object (table, in this case) changes. Views have a lot more advantages (which I also covered) over Synonyms and if you need the type of functionality they offer, a Synonym just isn't going to hack it.
--Jeff Moden
"
RBAR
is pronounced "ree-bar" and is a "Modenism" for "
R
ow-
B
y-
A
gonizing-
R
ow".
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1386335
EdVassie
EdVassie
Posted Monday, November 19, 2012 6:37 AM
SSCrazy
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 8:57 AM
Points: 2,619,
Visits: 2,749
Why do you not want to use a view?
Author:
SQL Server FineBuild
1-click install and best practice configuration of SQL Server 2012, 2008 R2, 2008 and 2005.
25 March 2013
: now over 23,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #1386358
« Prev Topic
|
Next Topic »
23 posts, Page 1 of 3
1
2
3
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
