Advice on working across databases?

I have two databases, one for DATA and another for GIS data. Each customer
has a table which stores a 3 letter code. This code is different for all
customers. For each customer the GIS db is labeled like so (<3 letter
code>_GIS). I would like to create views that are stored in the DATA db, but
actually retrieves data from a table in the GIS db. The issue lies with
accessing the code for each individual customers.
Sample statement:
create view vw_Space
as
select ID,Vector1,Vector2 from <code>_GIS.dbo.tabelname
But I need to access the customer code from a table and concatenate that
into my select statement in my view. This way the views are dynamic, so to
speak. How can I accomplish this? If you need a better explanation please
contact me. ThanksTim,
Add in fully qualified names for your databases i.e.,
yourdatabase.yourowner.yourtable and try:
CREATE TABLE DATA
(CODE CHAR(3) NOT NULL)
CREATE TABLE GIS
(CODE CHAR(7) NOT NULL,
VECTOR1 INT NOT NULL,
VECTOR2 INT NOT NULL)
INSERT DATA
VALUES ('ABC')
INSERT DATA
VALUES ('DEF')
INSERT GIS
VALUES ('ABC_GIS', 1,2)
INSERT GIS
VALUES ('DEF_GIS', 1,2)
INSERT GIS
VALUES ('DEF_GIS', 3,4)
SELECT D.CODE, VECTOR1, VECTOR2
FROM DATA D JOIN GIS G
ON D.CODE = LEFT(G.CODE,3)
HTH
Jerry
"Tim Gains" <kixman@.excite.com> wrote in message
news:u$iOxWQyFHA.788@.tk2msftngp13.phx.gbl...
>I have two databases, one for DATA and another for GIS data. Each customer
>has a table which stores a 3 letter code. This code is different for all
>customers. For each customer the GIS db is labeled like so (<3 letter
>code>_GIS). I would like to create views that are stored in the DATA db,
>but actually retrieves data from a table in the GIS db. The issue lies with
>accessing the code for each individual customers.
> Sample statement:
> create view vw_Space
> as
> select ID,Vector1,Vector2 from <code>_GIS.dbo.tabelname
> But I need to access the customer code from a table and concatenate that
> into my select statement in my view. This way the views are dynamic, so to
> speak. How can I accomplish this? If you need a better explanation please
> contact me. Thanks
>
>|||thanks for the reply, but what I need to do is extract the code from the
users table, conctenate that in my views select statement, again this would
make the view dynamic if this can be done. I'm not able to ad any tables to
any of the DB's.
The table is called tblInstalation, this table houses the code field w/
only a single row and code is a field in the table. The following is wrong,
but will get my point across better perhaps.
create view vMyViewas
select first *
from (select code from tblInstalation) +_GIS .dbo.tblGISData
How and could this be accomplished in such a manner?
Thanks.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:et3QjiQyFHA.2848@.TK2MSFTNGP15.phx.gbl...
> Tim,
> Add in fully qualified names for your databases i.e.,
> yourdatabase.yourowner.yourtable and try:
> CREATE TABLE DATA
> (CODE CHAR(3) NOT NULL)
> CREATE TABLE GIS
> (CODE CHAR(7) NOT NULL,
> VECTOR1 INT NOT NULL,
> VECTOR2 INT NOT NULL)
> INSERT DATA
> VALUES ('ABC')
> INSERT DATA
> VALUES ('DEF')
> INSERT GIS
> VALUES ('ABC_GIS', 1,2)
> INSERT GIS
> VALUES ('DEF_GIS', 1,2)
> INSERT GIS
> VALUES ('DEF_GIS', 3,4)
> SELECT D.CODE, VECTOR1, VECTOR2
> FROM DATA D JOIN GIS G
> ON D.CODE = LEFT(G.CODE,3)
> HTH
> Jerry
> "Tim Gains" <kixman@.excite.com> wrote in message
> news:u$iOxWQyFHA.788@.tk2msftngp13.phx.gbl...
>|||Ok...I'm . The sample before used CREATE TABLE just so you would
have an working example for the query enclosed. >first * (first record?
by what criteria?) also the FROM is trying to "build" a database name >
code + _GIS?
Please advise.
HTH
Jerry
"Tim Gains" <papow@.yahoo.com> wrote in message
news:%23u03e1QyFHA.720@.TK2MSFTNGP15.phx.gbl...
> thanks for the reply, but what I need to do is extract the code from the
> users table, conctenate that in my views select statement, again this
> would make the view dynamic if this can be done. I'm not able to ad any
> tables to any of the DB's.
> The table is called tblInstalation, this table houses the code field w/
> only a single row and code is a field in the table. The following is
> wrong, but will get my point across better perhaps.
> create view vMyViewas
> select first *
> from (select code from tblInstalation) +_GIS .dbo.tblGISData
> How and could this be accomplished in such a manner?
> Thanks.
>
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:et3QjiQyFHA.2848@.TK2MSFTNGP15.phx.gbl...
>|||Jerry,
Bottom line I need to create a view in DB1 that will return data from a
table in another database (GIS). Here is an example:
create view vSubscriber
as
select first
, last
, startdate
, enddate
, subscriberid
, email
from ABC_GIS.dbo.subscriber
My question is how do I go about concatenating in my view, a field from a
DB1 table in my view. This field would be concatenated in the fully
qualified name portion of the select statement in the view. Again, each
customer has a 3 letter code that resides in his database, this code would
make up a portion of the GIS database name, say for instance ABC_GIS. This
code (ABC) would need to be extracted and concatenated into the views
statement.
create view vSubscriber
as
select first
, last
, startdate
, enddate
, subscriberid
, email
from <DB1.Field>_GIS.dbo.subscriber
ultimately would yield:
create view vSubscriber
as
select first
, last
, startdate
, enddate
, subscriberid
, email
from DB2.dbo.subscriber
Does that make more sense, not sure if it can be done,perhaps with an inline
function returning a table, your thought please.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23c0lT6QyFHA.428@.TK2MSFTNGP10.phx.gbl...
> Ok...I'm . The sample before used CREATE TABLE just so you would
> have an working example for the query enclosed. >first * (first record?
> by what criteria?) also the FROM is trying to "build" a database name >
> code + _GIS?
> Please advise.
> HTH
> Jerry
> "Tim Gains" <papow@.yahoo.com> wrote in message
> news:%23u03e1QyFHA.720@.TK2MSFTNGP15.phx.gbl...
>|||Tim,
I put it in a proc so you wouldn't have to create so many views.
Try:
CREATE PROC USP_SUBSCRIBER
@.CODE CHAR(3)
AS
DECLARE @.SQL VARCHAR(500)
SET @.SQL = 'SELECT first,last,startdate,enddate,subscriberi
d,email from ' +
@.CODE +'_GIS.dbo.subscriber'
EXEC(@.SQL)
EXEC USP_SUBSCRIBER 'ABC'
HTH
Jerry
"Tim Gains" <papow@.yahoo.com> wrote in message
news:O%23sPLIRyFHA.1256@.TK2MSFTNGP09.phx.gbl...
> Jerry,
> Bottom line I need to create a view in DB1 that will return data from a
> table in another database (GIS). Here is an example:
> create view vSubscriber
> as
> select first
> , last
> , startdate
> , enddate
> , subscriberid
> , email
> from ABC_GIS.dbo.subscriber
> My question is how do I go about concatenating in my view, a field from a
> DB1 table in my view. This field would be concatenated in the fully
> qualified name portion of the select statement in the view. Again, each
> customer has a 3 letter code that resides in his database, this code would
> make up a portion of the GIS database name, say for instance ABC_GIS. This
> code (ABC) would need to be extracted and concatenated into the views
> statement.
>
> create view vSubscriber
> as
> select first
> , last
> , startdate
> , enddate
> , subscriberid
> , email
> from <DB1.Field>_GIS.dbo.subscriber
> ultimately would yield:
> create view vSubscriber
> as
> select first
> , last
> , startdate
> , enddate
> , subscriberid
> , email
> from DB2.dbo.subscriber
>
> Does that make more sense, not sure if it can be done,perhaps with an
> inline function returning a table, your thought please.
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23c0lT6QyFHA.428@.TK2MSFTNGP10.phx.gbl...
>|||Thanks, but I need these to be views, can this be done?
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:uaCbVRRyFHA.3000@.TK2MSFTNGP12.phx.gbl...
> Tim,
> I put it in a proc so you wouldn't have to create so many views.
> Try:
> CREATE PROC USP_SUBSCRIBER
> @.CODE CHAR(3)
> AS
> DECLARE @.SQL VARCHAR(500)
> SET @.SQL = 'SELECT first,last,startdate,enddate,subscriberi
d,email from '
> + @.CODE +'_GIS.dbo.subscriber'
> EXEC(@.SQL)
> EXEC USP_SUBSCRIBER 'ABC'
> HTH
> Jerry
> "Tim Gains" <papow@.yahoo.com> wrote in message
> news:O%23sPLIRyFHA.1256@.TK2MSFTNGP09.phx.gbl...
>|||Basically I want these act as tables.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:uaCbVRRyFHA.3000@.TK2MSFTNGP12.phx.gbl...
> Tim,
> I put it in a proc so you wouldn't have to create so many views.
> Try:
> CREATE PROC USP_SUBSCRIBER
> @.CODE CHAR(3)
> AS
> DECLARE @.SQL VARCHAR(500)
> SET @.SQL = 'SELECT first,last,startdate,enddate,subscriberi
d,email from '
> + @.CODE +'_GIS.dbo.subscriber'
> EXEC(@.SQL)
> EXEC USP_SUBSCRIBER 'ABC'
> HTH
> Jerry
> "Tim Gains" <papow@.yahoo.com> wrote in message
> news:O%23sPLIRyFHA.1256@.TK2MSFTNGP09.phx.gbl...
>|||and so that it could be parameterized.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:uaCbVRRyFHA.3000@.TK2MSFTNGP12.phx.gbl...
> Tim,
> I put it in a proc so you wouldn't have to create so many views.
> Try:
> CREATE PROC USP_SUBSCRIBER
> @.CODE CHAR(3)
> AS
> DECLARE @.SQL VARCHAR(500)
> SET @.SQL = 'SELECT first,last,startdate,enddate,subscriberi
d,email from '
> + @.CODE +'_GIS.dbo.subscriber'
> EXEC(@.SQL)
> EXEC USP_SUBSCRIBER 'ABC'
> HTH
> Jerry
> "Tim Gains" <papow@.yahoo.com> wrote in message
> news:O%23sPLIRyFHA.1256@.TK2MSFTNGP09.phx.gbl...
>|||How do I know what value (3 letter code) you want used? Are you trying to
supply it when you query the view? Or are you just trying to create a bunch
of views using the codes from the other table as a one time op?
"Tim Gains" <papow@.yahoo.com> wrote in message
news:%23LVC$TRyFHA.3772@.TK2MSFTNGP11.phx.gbl...
> Basically I want these act as tables.
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:uaCbVRRyFHA.3000@.TK2MSFTNGP12.phx.gbl...
>