Prev: Compatibility issue???
Next: Sql 2005 64 bit , error creating Maintenance Plan
From: LAM on 8 Jul 2010 11:13
Hi!
On my own sqlDB, I need to link tables form other sqlDB in the same machine
and then, create views that combine fields of my own tables and linked
tables. Please give some suggestions.

Thanks

LAM


From: Erland Sommarskog on 8 Jul 2010 17:02
LAM (luism0@(arroba)yahoo.es) writes:
> On my own sqlDB, I need to link tables form other sqlDB in the same
> machine and then, create views that combine fields of my own tables and
> linked tables. Please give some suggestions.

CREATE VIEW myview AS
SELECT ...
FROM localtable
JOIN otherdb.dbo.othertable ON

That is, you refer to the tables in the other database with three-part
notation on the form database.schema.tablename.

If you want to avoid to hardcode the database name again and again you
can create a synonym:

CREATE SYNONYM othertable FOR otherdb.dbo.othertable

And then just use othertable in the view definition.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: LAM on 9 Jul 2010 14:44
Great ! thanks for your hints. I've never used synonym.

Now I find a new question: In an easy way, how to change the source or
external BD ("otherbd.dbo.othertable") for all created views ?

Thanks again.

LAM


"Erland Sommarskog" <esquel(a)sommarskog.se> escribi� en el mensaje
news:Xns9DAFEA52A4D26Yazorman(a)127.0.0.1...
> LAM (luism0@(arroba)yahoo.es) writes:
>> On my own sqlDB, I need to link tables form other sqlDB in the same
>> machine and then, create views that combine fields of my own tables and
>> linked tables. Please give some suggestions.
>
> CREATE VIEW myview AS
> SELECT ...
> FROM localtable
> JOIN otherdb.dbo.othertable ON
>
> That is, you refer to the tables in the other database with three-part
> notation on the form database.schema.tablename.
>
> If you want to avoid to hardcode the database name again and again you
> can create a synonym:
>
> CREATE SYNONYM othertable FOR otherdb.dbo.othertable
>
> And then just use othertable in the view definition.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000:
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>


From: Erland Sommarskog on 9 Jul 2010 15:51
LAM (luism0@(arroba)yahoo.es) writes:
> Now I find a new question: In an easy way, how to change the source or
> external BD ("otherbd.dbo.othertable") for all created views ?

If you have the code under version control, check it out and run an
edit/replace tool over them. www.download.com should have a couple to
offer.

If you don't have them under version control, but only in the database,
script them to files, and run the edit/replace dance.

I also seem to recall that Red Gate have something called SQL Refactor in
their product suite.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: Compatibility issue???
Next: Sql 2005 64 bit , error creating Maintenance Plan