January 22, 2015 at 8:33 am
Hi
First , sorry if I word this incorrectly....
We have setup a report server where a db's tables are refreshed from a backup daily.
All SP and views are created in a different DB but look at the daily refresh tables
Can I setup the DB that creates the view to point at the DB with the tables so when I say new view it looks at the tables in that DB
Thanks
Joe
January 26, 2015 at 8:34 pm
jbalbo (1/22/2015)
HiFirst , sorry if I word this incorrectly....
We have setup a report server where a db's tables are refreshed from a backup daily.
All SP and views are created in a different DB but look at the daily refresh tables
Can I setup the DB that creates the view to point at the DB with the tables so when I say new view it looks at the tables in that DB
Thanks
Joe
So, you want a database to host a view of tables that exist in a different database? I did some testing and I was able to create a linked server from one database I have to another and then create a view. InstanceA holds the Linked Server to Instance B. On Instance A I created a view to a table on Instance B like as follows:
CREATE VIEW TEST VIEW AS
SELECT SomeColmnNames FROM LnkedServername.SourceDatabaseName.dbo.SourceTableName
This worked fine. Is this what you are asking about doing?
January 27, 2015 at 9:43 am
Thanks for the info, definitely helps...
I was also wondering if I was using the view Gui interface where I can pick tables or other views if I can point it to another database so the choices that come up are from that database. So lets say I am writing views on "reporting" database but I am getting my data from "data" databse. Whne I start my view and choose the gui the list of tables comes from "Data" but I am writing it in" reporting" databse Its not a big deal just wondering if I could set it to look at the other databse
January 27, 2015 at 4:49 pm
I don't know of a way to do that. The GUI is going to automatically point to the tables that are in the database from which you start the New View wizard. I don't know how you would redirect it to look across the Linked Server to another SQL instance.
January 28, 2015 at 7:23 am
jbalbo (1/27/2015)
Thanks for the info, definitely helps...I was also wondering if I was using the view Gui interface where I can pick tables or other views if I can point it to another database so the choices that come up are from that database. So lets say I am writing views on "reporting" database but I am getting my data from "data" databse. Whne I start my view and choose the gui the list of tables comes from "Data" but I am writing it in" reporting" databse Its not a big deal just wondering if I could set it to look at the other databse
No. And honestly you shouldn't be using the gui to generate views. You should write them in code, you have a lot more control. It may be a little bit slower at first but pretty soon you will be able to write queries much faster than the gui wizards can.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply