We 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';
HiI 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.
Dear 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.
I 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?
I 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?
I 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
I 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.
I 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?
We 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]
Hi, I'm writing an intranet application - a web survey engine.
I'd like to create a new database for each user that creates a survey form... versus creating new tables for each survey.
Memory and disk space is not exactly the problem... just user permissions. If I dump all the results onto one database, doesn't it mean that this user can have access to all other tables(including the results of other surveys??)
Question is, does SQL Server reserve a bunch of memory for each database that I create, thereby hogging up a lot of space if I create more than one database.
Credit for this script really goes to ToddV (see http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=13737)
The following script issues a DBREINDEX command against every table in every database on your server. It can be modified to issue other commands, but this one in particular is helpful as we are migrating about 30 databases from SQL 7 to SQL 2000 (new server) and re-indexing is recommended. Here's the script:
DECLARE @SQL NVarchar(4000) SET @SQL = ''
SELECT @SQL = @SQL + 'EXEC ' + NAME + '..sp_MSforeachtable @command1=''DBCC DBREINDEX (''''*'''')'', @replacechar=''*''' + Char(13) FROM MASTER..Sysdatabases WHERE dbid > 6 -- skip the 6 built-in databases. Remove to process ALL
PRINT @SQL -- Only if you want to see the code you're about to execute. EXEC (@SQL)
Notes: There is a limit (nvarchar 4000) to how big your command can be, so too many databases will halt this.
hi, i have one database ASPNETDB.MDF created by default when adding a user to my site, and MyData.mds - my database...i want to join the aspnet_Users table with another table created by me (in myData.mds), how can i do that? is hard if i should re-write all the data from myData into the ASPNETDB,i even writed both connectionStrings in the web.config but still with no succes... is there any trick in the SQL statment? please help me thank you
Hello, Let's say I created some tables in SQL server using the designer. Tables -> Right click -> New Table ->... How would I send someone else the script for creating that table? Or how would I send somebody else the tables? Thanks.
I am using Vista Business with Visual Web Developer and Sql Server Express 2005. I want to be able to access the data from two tables in a query. Both tables are attached to the application. To refer to the two databases I have used the following DATABASE.OWNER.TABLE (specifically TaskMgr.dbo.TaskTable) For both databases I get the error "Invalid object name" What am I missing? Thanks
I would like to know if it is possible to link two databases together (in my case ASPNETDB, and another mdf database) so that I can run queries on those shared tables. For example, I would like to use the uniqueidentifiers from the ASPNETDB tables in my own tables.
Thank you!
(I do use the latest version of Visual Web Developer).
I am using SQL server7.0. I am having two databases ,say database A and database B.Database A is the main database which is used in two /three projects.While database B is created by me for my work.From the database A, I am using the 4/5 tables, which i have copied in to the database B. So, i want to update these tables in the database B as soon as any change (insert,update or delete ) occures on the tables in the database A. That's why I am interested in doing these work.I have tried, but it doesn't work.So, I have placed these into these forum. Is any body is having the trigger, procedure ready for doing these job. then plz,mail it to me
Hey everyone. I am somewhat of a newbie to the database world. I have been given the task of connecting two different SQL databases. Both are Microsoft SQL.
I want some of the tables of one of the databases to be linked to the tables of the other.
I have two databases that each contain the same tables, but different data in the tables. For example, each data contains a table with the same name, arcus, that holds data on our customers. Although the data is different in each table, there is some overlap, particularly in the area of customer number since that is assigned automatically when a customer is entered and serves as the primary key for that table.
To consolidate, I need to merge the two databases. How can I import the data from one table in second database into a table in the first database and append a number to the customer number so that all data will be brought across.
To better illustrate:
database one has an arcus file with a field cusno and contains cusno 1-50 database two has an arcus file with a field cusno and contains cusno 27-58
The overlapping cusno's are not the same customer.
How can I get all the cusno from the arcus file in database two to the arcus file in database one?
I am trying to relate two tables but both are from different databases. I have a database 'Current' which has a table 'case' and another database 'Org' which has a table 'employee'. I want to establish a one-to-many relationship between employee and case. I created a coulmn 'employee_key' in the case table & entered values matching the pkey of the employee table. Then I wrote a query using joins to access the table records.
select Org..employee.pkey, Current..case.assignedengineer, Current..case.pkey from Current..case join Org..employee on Current..request.employee_key=Org..employee.pkey
Although the above query works fine, there is no relation between the two tables of the two databases, I wanted to know if this is the right way to achieve what i want to or is there a way in which i can actually create a relationship between tables of two different datbases. Can anyone suggest/help??? Also, I wanted to know if i can relate 1 table of SQL Server db with another table of Oracle DB. Please help ........... Thanks
My boss has asked me to look into this and I haven't been able to find any information on the web. I hope someone can answer this for me. We currently have a single database that is storing all the user information and transactions. Within the same database we are also logging different types of user activity. If both these tables are heavily used, would it make sense to separate it into different database, one for data and one for logging? Is there any pro or cons of having more than one database? Any opinion or suggestion would be greatly appreciated. I'm the closest thing they have to DBA and I'm really new to this. Thanks.
i have 2 db's that are totally different except that they both have a table users and I want to make it that anytime the user table is updated or added to in one db - that it is copied over to the other db.
HiI'm working on an ASP project where the clients want to be able toeffectively perform SELECT queries joining tables from two differentdatabases (located on the same SQL-Server).Does this involve creating virtual tables that link to another database, oram I completely on the wrong track?Any hints as to where I might find more information (buzz-words, etc.) wouldbe most appreciated.Thanks
In a database, I am creating a new db. From there, I am setting up thetables, so that I can eventually create a front end (usually access,but I may attempt to be brave and lose the shell.) Anyway, I want touse a table, read-only for a lookup. It exists in another database onour system. Is there a way for me to link it into this database that Iam working on? I would think of it like in access when you go to do anew table and you choose to link the table from somewhere else. Thatis what I want to do. Can anyone possibly step me through this?I know basics of sql server and like to learn.Thanks!
(SQL 2005)I'm looking to create a stored procedure to first "select name fromsys.databases where name like '%site'" then pass each name to thefollowing using some kind of loop "USE @name select name fromsys.tables where type = 'U'"I tried a while statement, but the master sys.databases recordsetdoesn't change..It's the loop I can't get to work.Any help is greatly appreciated!
Hello,I am quite new to ms-sql and I have a problem : I want to create an SQLrequest which would copy serveral records from a table in a given databaseto another table (with exactly the same structure) in another database(theses two tables and databases already exist).Could you please tell me how to do this ? I dont know how to access twodifferent databases in a single SQL request.Thank you for you help.
Hi.I'm currently working on a project which involves the creation of aweb page which reports selected data to customers from two back-endsystems. I use ASP script language on IIS, the server is MS SQL 2000.Now I'm struggling with combining two tables from the differentdatabases. I'm sure it's simple enough, but I'm a little short on theSQL expertise.I've got two databases, db1 and db2, and then two tables, db1.t1 anddb2.t2. I need to combine these two tables (both tables have amatching key field) to make a list of all items from db1.t1, and thosewho correspond from db2.t2.I can list all items from db1.t1, but I can't seem to get the db2.t2joined in.Can anybody help me with the syntax for this, please ? Help !Answers, hints & tips greatly appreciated.Thanks in advance !Kenneth
