Using Multiple Databases In A View
Nov 11, 2007How can I include tables and views from database A when building a view in database B, if possible?
Same for stored procedures.
ADVERTISEMENT
View Of Multiple Databases With Identical Schemas
Aug 24, 2006I have several distributed databases with identical schemas that I have added to my SQL server as a set of linked servers. For the sake of simplicity consider a schema with single table with one column called Info. I would like to present a view with columns DatabaseName and Info, that presents all the rows from all the databases in a single view.
How can this be achieved this using a SQL Server View?
If not is not possible using Views, what approach you recommend?
Linking Tables From Different Databases Or Querying From Multiple Databases
Dec 10, 2007Dear Readers,Is it possible, like in Access, to link to tables in other SQL databases that are on the same server? I have a query that I originally had in Access that queered from multiply databases. It did this by having those other tables in the other databases linked to the database that had the query.
Creating Index On A View To Prevent Multiple Not Null Values - Indexed View?
Jul 23, 2005I am looking to create a constraint on a table that allows multiplenulls but all non-nulls must be unique.I found the following scripthttp://www.windowsitpro.com/Files/0.../Listing_01.txtthat works fine, but the following lineCREATE UNIQUE CLUSTERED INDEX idx1 ON v_multinulls(a)appears to use indexed views. I have run this on a version of SQLStandard edition and this line works fine. I was of the understandingthat you could only create indexed views on SQL Enterprise Edition?
View 3 Replies View RelatedTransact SQL :: Create Email Report Which Gives Result Of Multiple Results From Multiple Databases In Table Format
Jul 24, 2015I'm trying to create an email report which gives a result of multiple results from multiple databases in a table format bt I'm trying to find out if there is a simple format I can use.Here is what I've done so far but I'm having troble getting into html and also with the database column:
EXEC msdb.dbo.sp_send_dbmail
@subject
= 'Job Summary',
@profile_name =
'SQL SMTP',
[code]....
Creating A Database From Multiple Databases Accross Multiple Servers
Sep 13, 2007Hi,
I'm trying to create a database that takes specific information from a number of databases on different servers to make some reporting that we have much easier.
I'm pretty new to SQL so I'm not sure of the best way to proceed. I read an article that suggested I use the OPENROWSET command. The problem is, the version of SQL that came with one of the programmes we use is limited and will not allow you to turn on the allow "Ad Hoc distributed Queries" so the SLQ statement will not execute.
I'm confused why it won't let me to connect through ODBC as I've created a web page that selects data from this database with no problems!
Here is the SQL statement that I've written to make sure it is the correct one (on the msdn library page it said that this was the ODBC connection):
SELECT a.*
FROM OPENROWSET('MSDASQL','DRIVER=(SQL Server);SERVER=APPOLOACT7;UID=sa;PWD=***************',
'SELECT * FROM MDCTestAndDev.dbo.TBL_CONTACT') AS a
I've also created the ODBC connection using the tool on Administration Tools>Data Sources ODBC
Any help would be greatly appreciated (also any ways of selecting from one database and inserting it into another will be helpfull)
Thanks
Merging Data From Multiple Databases With Multiple Tables (all With The Same Structure)
Nov 15, 2006Hi!
I have 7 source databases and one target database, all using the same structure. The structure is made of 10 tables, with foreign key constraints.
I need to merge the source databases into the target (which won't have any data before that process, but will already have the correct schema), and to keep the relationships between the records.
I know how to iterate over the source databases (with SMO foreach), but I'd like to know if someone can advise the best copy method for that context in SSIS ? (I don't want to keep the primary keys, but I need to keep the relationships...)
Any pointer most welcome!
best regards and thanks
Thibaut
SQL Tools :: How To Restore Multiple Databases From Multiple BAK Files At Once
Aug 14, 2012I am trying to restore multiple .bak backup SQL database files onto a new server. However, I have found that it will not allow me to restore multiple databases at once. Is there a way to do this so that I do not have to manually upload one at a time? I tried adding all the .bak files at once to the backup device window but it only did the first one listed. It would be so much easier to restore them all at once so that I do not have to continue this manual process. I am restoring them via device.
View 13 Replies View RelatedJOIN Multiple Tables From Multiple Databases
May 23, 2008Hello,
I am in the progress of designing a new section of my database and was thinking of creating a hole new database instead of just creating tables inside the database. My question is can you JOIN multiple tables in an SQL Statement from multiple databases. Ie, In the Management program I have a database called 'Convention' and another one called 'Services', inside the two databases there are many tables. Can I link say tblRegister from Convention to tblUser in Services?
Thanks
View Over Different Databases
Feb 22, 2006Hello,
How can I write a view that gets data from different databases in the same Database server?
View Between Tables In Different Databases
Jun 25, 2007We have created a view that selects from tables in two databases. it looks like something like this:
CREATE VIEW myview AS
SELECT col FROM db1.mytab
UNION SELECT col FROM db2.mytab;
Both tables have col as primary key.
When we executes SELECT col FROM myview WHERE col='SOMEVALUE' we receives the correct result but SQL server does not use any indexes. It performs a index scan on both tables. Wh and what can we do about it? The tables contains millions of rows so the performance is terrible.
The following works fine:
SELECT col FROM db1.mytab WHERE col ='SOMEVALUE'
UNION SELECT col FROM db2.mytab WHERE col ='SOMEVALUE';
Qe are running SQL Server 2000 and TRANSACT SQL
Best regards
/Ingvar
SQL 2012 :: Partitioned View Over Two Databases
Sep 8, 2014I have database with a large table (30 Billion rows) because it is so big I separated the data in quarterly tables and created a partitioned view (with hints for the date column) about 1 billions a quarter. (all in separated filegroups). The tables themselfes are partitioned by date again, so you slice out one day
However the full-backup of grows and grows and the mainpart of it is "old" but needed data.
So I was thinking to put the older data in a separate database (with separated backup) and then point to the table in my view.
While this is technical possible (leaving out the WITH SCHEMABINDING) I wonder what negative consequences it will have.
I already had to lose "with schemabing".
I have to use separate partioning functions - for each database its own - (partition schemas where already separated due to separated filegroups)
What about query optimization, does the optimizer care that there are two databases?
Mirroring Databases Connected Through A View
Oct 12, 2006I have two databases db_A_primary and db_B_primary, both databases are on one Primary server.
db_B_primary has a View into db_A_primary.
Scenario: db_A_primary goes down and failsover to db_A_mirror on the Mirror server.
In this scenario when the View in db_B_primary is accessed will it automatically be redirected to look at the db_A_mirror database on the Mirror server?
Barry.
Alternative To Creating View With Union In Two Databases?
Jul 20, 2005I attempted to create a view in SQL Server 2000 that Unions twoqueries. The first part of the query gets data from the local server,the second part gets info from a linked server. (The query works finein Query Analyzer.)I received this error when I tried to save the query:ODBC error: [Microsoft][ODBC SQL Server Driver] The operation couldnot be performed because the OLE DB provider 'SQLOLEDB' was unable tobegin a distributed transaction.[Microsft][ODBC SQL Server Driver][SQL Server][OLE/DB providerreturned message: New transaction cannot enlist in the specifiedtransaction coordinator.]After a little reading I discovered the "Database limitation":"A view can be created on a table only in the database the viewcreator is accessing".That's my problem... is there a simple solution or alternative tocreating a view?Thanks,Matt
View 2 Replies View RelatedPermissions With Windows Groups And View To Other Databases
Aug 24, 2007I am having a problem with permissions using Windows groups. I have a database (database1) that has permissions granted via Windows groups. Two groups (group1 and group2) are members of the db_datareader role in database1, and this work fine. Do to the number of tables that get created during our work, using db_datareader is the easiest way to keep up with permissions without creating a maintenance problem. Now I have a table that I want to add to this database, but I only want group2 to have select permission on this one table which is a problem because group1 has the db_datareader role. So I thought I could create a view in this database to the restricted table that I put in database2. Then in database2 I only added group2 as a user with the permission to select from this table. Unfortunately the group membership does not seem to get interpretted correctly in database2 and no one can successfult select from the view in database1.
In other words, user1 who belongs to group1 connects to database1 and cannot select from the restricted view -- this is what I would expect. However, when user2 who belongs to group2 connects to database1 they also cannot select from the restricted view -- not the behvior I would expect. Now, if I make user2 a user in database2 with select on the restricted table then user2 can connect to database1 and successfuly get data from the restricted view. So it looks like the fact that user2 belongs to group2 is never passed to database2 via the select from the view on database1. Is this indeed the way that Windows group security is working or is meant to work in SQL Server?
I realize I could solve this simplified version of the problem by creating my own role in database1 for group1 etc., but I am trying to solve a bigger problem in our environment that has hundreds of databases across numerous servers.
Thanks
Rob
SQL Server 2008 :: Create View For Each Table In The Databases?
May 27, 2015We have two databases with same schema and tables (same table names, basically main DB and a copy of the main DB). following is example of table names from 2 DBs.
CREATE TABLE #SourceDatabase (SourceColumn1 VARCHAR(50))
INSERT INTO #SourceDatabase VALUES('TABLE1') , ('TABLE2'),('TABLE3') , ('TABLE4'),('TABLE5') , ('TABLE6')
SELECT * FROM #SourceDatabase
DROP TABLE #SourceDatabase
CREATE TABLE #ArchiveDatabase (SourceColumn2 VARCHAR(50))
INSERT INTO #ArchiveDatabase VALUES('TABLE1') , ('TABLE2'),('TABLE3') , ('TABLE4'),('TABLE5') , ('TABLE6')
SELECT * FROM #ArchiveDatabase
DROP TABLE #ArchiveDatabase
We need a T_SQL statement that can create one view for each table from both the databases(assuming both databases have same number of tables and same table names). so that we can run the T_SQL on a thrid database and the third DB has all the views (one view for each table from the 2 DBs). and the name of the view should be same as the tables name. and all 3 DBs are on the same server.
the 2 temp tables are just examples, DBs have around 1700 tables each. so we ned something like following for each table.
CREATE VIEW DBO.TABLE1 AS SELECT * FROM [SourceDatabase].[dbo].[TABLE1] UNION ALL SELECT * FROM [ArchiveDatabase].[dbo].[TABLE1]
CREATE VIEW DBO.TABLE2 AS SELECT * FROM [SourceDatabase].[dbo].[TABLE2] UNION ALL SELECT * FROM [ArchiveDatabase].[dbo].[TABLE2]
CREATE VIEW DBO.TABLE3 AS SELECT * FROM [SourceDatabase].[dbo].[TABLE3] UNION ALL SELECT * FROM [ArchiveDatabase].[dbo].[TABLE3]
CREATE VIEW DBO.TABLE4 AS SELECT * FROM [SourceDatabase].[dbo].[TABLE4] UNION ALL SELECT * FROM [ArchiveDatabase].[dbo].[TABLE4]
CREATE VIEW DBO.TABLE5 AS SELECT * FROM [SourceDatabase].[dbo].[TABLE5] UNION ALL SELECT * FROM [ArchiveDatabase].[dbo].[TABLE5]
CREATE VIEW DBO.TABLE6 AS SELECT * FROM [SourceDatabase].[dbo].[TABLE6] UNION ALL SELECT * FROM [ArchiveDatabase].[dbo].[TABLE6]
[SQL2000] Permissions To Use View Based On Tables From Many Databases
Feb 21, 2006HiI have two databases: Customers and Operations. In Customers database I havemade a view based on a few tables from both Customers and Operations (leftjoin - customers without any operations). In the same database (Customers) Ihave created a stored procedure based on the view. Finally I'd like to giveto some users permission only to exec the stored procedure.Have I to add the users to Customers? If yes, please describe me how tolimit the users privileges only to execution the stored procedure (no rightsto open tables or view from Customers).Regards,GrzegorzPs. I had sent the post on microsoft.public.sqlserver.security, but I had noanswer.
View 5 Replies View RelatedMultiple Databases?
Jul 8, 2004Is it possible to retrieve Data from SQL Database #1 and insert it into SQL Database #2 using a Stored Procedure? Thanks. If so Can you show example. Thanks
View 12 Replies View RelatedMultiple Databases
May 18, 2007Hi All
I have a question about having one or multiple databases for a large project. What are the main differences between designing my database as one or breaking in to multiple databases. I should mention that there are many relationships between tables in different DBs in the multiple DBs state?
regards
sasan
Querying Multiple Databases
Feb 7, 2007Hey there!
I'm not sure how to explain this but here goes...
I'm a bit stuck, a new project that's come about "requires" me to query multiple databases as if they were tables.
1 Oracle database which stores information on our staff (this database is a part of some MIS software and can't be changed in any way)
1 SQL Server 2005 database which stores information on staff sickness
Basically say theres 1 table in each database
Oracle DB
People
Person_Code
Forename
Surname
SQL DB
Sickness
Person_Code
Daydate
Lets say the query I want to perform is to select all the records from sickness where person code is 22334 and also get their name from the other db, so the output may look like
22334 Dann Rees 01/01/2007
22334 Dann Rees 03/01/2007
22334 Dann Rees 10/02/2007
Now I realise I can write a quick function to pull the information but this is just a basic example. Effectivley what I "need" is to be able to query sickness while sub querying people to get the names, or some kind of pass through query?
Please remember this is just a very simple example and the "actual" queries will be far more complicated, for instance finding all the employees of a certain department who is male and was sick in January. All the data for that example is stored in people (oracle) except for the dates which is stores in sick (SQL 2005). Now these are easy enough if they were tables in 1 database....but their not, their tables in 2 databases, and theres nothing I can do to change that :(
All help appreciated as this is becoming very urgent.
Many thanks
Dann
(I couldnt post this in the General data access forum for some reason)
Searching Multiple Databases
Jul 16, 2007Hi all:I am trying to create a website which will search and return results that are from multiple tables in different databases. I have the code to search a singular database right now, and I tried to make a connection to a second one, which worked, but I don't know how to send the proper sql commands to the second database or how to link them in the code. Does anyone have any pointers on creating a site which will search different tables in different databases with a known connection between databases? Any help would be really really really really appreciated.-Shrey
View 3 Replies View RelatedHow To Write An Sql Across Multiple Databases
Dec 23, 2005so, I am trying to write an sql that requires information across two
different database. It's under the same sql server. However, the
location of the tables are from different database.
Any links I can read about that can show me how to write the queries?
Thanks in advance, and merry X'mas.
Reflecting Changes To Multiple Databases
Jun 14, 2001We have a number of databases(on sqlserver 7.0) which has the same structure but with data for different users. We would like to change the structure of the databases with minimum effort and with minimum down time.
Is there any method to automate the change so that the change is reflected in all the databases when a single database structure is changed?
Any suggestions in this regard are welcome and urgent
Indexes In Multiple Databases
Nov 1, 2000Okay this is a test...actually I am still learning SQL and need some help. Does anyone have any information on being able to move indexes from one database to another. My scenario is I have 3 databases, Development, QA and Production. I want to move/copy indexes I created in Development to the QA database. I have many indexes so I do not want to have to recreated them if I can avoid it. Any suggestions?
Thanks!
Multiple Databases Synchronizing
Apr 5, 2004Hi,
Currently developping a c# database based application, which will be used in different establishments worldwide. I'm currently a bit confused by the ms sql features offered. Because the huge data amount which will be fetched during application use from the database, the best option is probably to put a ms sql server in each establishment to realise quick data fething. Problem is that the servers, which are all running on the same database design have to be synchronised real-time. I read about datareplication with the merge option which I thought was a nice solution. The subscribers can only have read-only rights on these subscriber databases, because the merging doesn't work appropriate i heard, so this is no good solution. Then I read about distributing transaction coordination. Seems a good solution as well, but what happens if a server system crashes for one day and then comes back online? It won't be up to date anymore... Right now I think about a combination of these 2 features. DTC in combination with merge replication, but there must be a better solution. Fact is that a lot of users will be editing data worldwide non stop, and everyone has to be up to date. Because of huge data fetching I don't think it's a good solution to let everyone work worldwide with one single database. Is there a possibility to realise this with ms sql server? Because I'm getting more and more confused... Thx in advance.
Best Tool For Changes To Multiple Databases?
Feb 5, 2002I need some guidance! We are running SQL Server 2000. We have 4 or 5 databases with the same schema, and I need to make sure that their structures all stay synched. We will have more soon. I also need certain tables to have the same data across all the databases.
I need a way to manage changes on the multiple databases! Right now I generate a script in Enterprise Manager, then run it in Query Analyzer on each database, manually. There's too much room for error here.
Any suggestions are appreciated! I am currently evaluating Embarcadero's products, but they are complicated and confusing - I'm not sure yet which of their tools is meant for this purpose.
Thanks,
Avi
Updating Multiple Databases
Jun 25, 2002I have a problem like this. I have an application which shud be internet hosted. All transactions should be updated to the central server of the net as well as an intranet DB server. (I'll have a DB server - SQL Server 2000 in each intranet). Is this possible.. i'm planning to use ASP to develop the net based application.
Thank you in advance..
Geetha R
Duplicates Multiple Databases
Nov 9, 2007Ok, so right now we have a bunch of queries similar to the following:
Code:
use db001 select co#=(001), fieldvalue from tablename where fieldid = '1'
use db002 select co#=(002), fieldvalue from tablename where fieldid = '1'
what i need to find is if there are duplicates of fieldvalue and have it display all in one.
Basically right now we run all these there are 25 databases and we export everything to a file and then open it in excel. we then format it it so its just co# and fieldvalue and then look for duplicates.
i would like to avoid the whole excel part and just run a query that will display any duplicates of fieldvalue across all 25 databases in "tablename"
hope that makes sense. - Thanks
Copy Multiple Databases
Aug 14, 2007Could anyone possibly help me out with an issue I am having...
I need to copy all my SQL2005 databases from Server1 to Server2. How can I, using SSIS, copy all the databases and not just 1 specific one (database transfer task)???
My actual task is much deeper than this, but this is the main problem I seem to be encountering and I cannot find this anywhere on the web...
Over and above the what I have mentioned, is there a way to also specify a name of the target DB the copy will go through to?
e.g. Server 1 has 3 instances, each one with a different copy of the same DB.
I would like to copy all 3 copies to Server 2 running 1 SQL instance, but to target DB's like DB_dev, DB_prod and DB_test.
The latter section is not so crucial at the moment, but the first part is really an issues I am struggling with at the moment...
Any help would be greatly appreciated.
Thanking You in advance!!!!!
Best Way To Organize Multiple Databases?
Jan 21, 2008I'm very new to SQL, so you'll have to excuse the nubishness of this question. I've got my trusty O'Reilly book here!
We're using Microsoft SQL Server 2005 on our web server. I'm currently working on a project to get a few MS Access Databases available on the web page.
The web part is actually working fine, with the DBs exported into SQL for performance reasons. (From what I've read pulling data from the .mdb file could cause me problems in the future, so I'm tossing it all onto the SQL server from the start.)
Before I get into the project full-tilt, though, I'm curious about how best to organize all the data?
I'm using the Upsizing Wizard in Access for the conversions, and every new DB gets added under Databases. If this gets approved I may be looking at well over a hundred DBs which will eventually be converted and made web accessible.
I can keep everything neat and tidy on the web server by just tossing things in a few directories, but the menu on the SQL Server Management Studio is going to become a mess really quickly if I let it just keep adding to +Databases.
Is there a way to sort everything into a series of folder/groups, like how System Databases has it?
Thanks in advance,
-Lamune
One Database Vs. Multiple Databases
Jun 20, 2006I need to design a system which represents multiple "projects" in SQLServer. Each project has the same data model, but is independent of allothers. My inclination is to use one database to store all projects.Looking at the numbers involved, however, I wonder if I would getbetter performance by storing each project in its own database.Suppose I have 50 projects, each with two users and 10,000 rows; itseems to me I'd rather have 50 x 2 users working in a table with 10,000rows than 1 x 100 users working in a table with 500,000 rows.On the other hand, the single database approach seems more elegant froma design perspective. I wouldn't be creating multiple copies of anidentical data model, and I wouldn't be creating new databases as abusiness procedure, every time a new project is required.Here are my questions:1. For the scenario described above, am I correct to assume I will getbetter performance by using multiple databases, or does SQL Server havesome clever way of achieving the same performance in a single database?2. Is the multiple database approach common? If anyone has tried it,please tell me about how it works in practice.-TC
View 9 Replies View RelatedLog Shipping For Multiple Databases
Sep 21, 2007
Hello
we have SQL instance which contains databases (A, B, C...). When ever i configured LS with Standby option, and i want complete restore of database A (Primary) to stand by A (Secondary), then logs will always going to restore...
but SQL server is taking a copy of the Backup like "GJ_Temp_data, GJ_Temp_log" as the default file names, for restore the DB Backups.
if i want to set up for the database B, it is taking "GJ_Temp_data, GJ_Temp_log" again for the database B, and giving error as "the files are already in use".
If i script the Log shipping setup while enabling for Database A, and make sure that i will enabling it for remaining databases B, C, D..
Please advice, and give us best experienceS for enabling LS FOR Multiple databases on the single SQL Instance.
Thanks in advance.
Querying Multiple Databases
Sep 21, 2006Hello,
This is probably a simple solution but I'm a bit confused about which flows to use.
I have two databases on different servers that contain the same information but for the appropriate server. I want to retrieve data from each of the servers using a "select" statement.
Would I use a ForLoop and change the connection in a Script Task and then execute the SQL statement? Or can a ForEach Loop be used, but I'm not sure which type of enumerator.
Thanks for any help you can give.