AppDB - The main application database.
GeoDB - A somewhat static ZIP code / states / other geographic stuff database
I need to have some foreign key columns in tables in AppDB reference columns in the GeoDB database tables. Eventually other application database besides AppDB will be doing the same thing in our infrastructure. After googling and reading for days, here is what I think I know:
You cannot create foreign keys that reference tables in another database in SQL Server.
You cannot create foreign keys that reference columns in a view, and you definitely cannot make an index on a view that has base tables in another database.
You can create a trigger that references tables in another database, but this can be flaky? (nested/recursive trigger problem).
SQLServer2005 supports multiple schemas within the same database. Maybe I should logically separate my databases this way? Seems like it would be a tough solution to manage since I already have some databases live in production that will eventually use this 'static' GeoDB. Also, seems like itwouldn't be as portable as keeping the GeoDB info in its own database, but maybe I'm being too software engineer-ish here - afraid of low cohesion, high coupling.
I will greatly appreciate any advice I can get, any links to articles, or any more options I am missing.
Using SQL Server Express 2005, I have two databases. AppDB - The main application database.GeoDB - A somewhat static ZIP code / states / other geographic stuff databaseI need to have some foreign key columns in tables in AppDB reference columns in the GeoDB database tables. Eventually other application database besides AppDB will be doing the same thing in our infrastructure. After googling and reading for days, here is what I think I know:You cannot create foreign keys that reference tables in another database in SQL Server.You cannot create foreign keys that reference columns in a view, and you definitely cannot make an index on a view that has base tables in another database.You can create a trigger that references tables in another database, but this can be flaky? (nested/recursive trigger problem).SQLServer 2005 supports multiple schemas within the same database. Maybe I should logically separate my databases this way? Seems like it would be a tough solution to manage since I already have some databases live in production that will eventually use this 'static' GeoDB. Also, seems like it wouldn't be as portable as keeping the GeoDB info in its own database, but maybe I'm being too software engineer-ish here - afraid of low cohesion, high coupling.I will greatly appreciate any advice I can get, or any more options I am missing. Thanks,Adam Nofsingerucnmedia.com
create table TableMain ( RowID int primary key identity(1, 1) , ItemTypeID tinyint NOT NULL , Details varchar(200) NOT NULL ) go
create table TableDetails ( DetailRowID int primary key identity(1, 1) , RowID int NOT NULL , ItemTypeID tinyint NOT NULL , Details varchar(200) NOT NULL
) go
create table ItemTypes ( ItemTypeID tinyint primary key identity(1, 1) , Details varchar(50) NOT NULL ) go
create unique nonclustered index IX_TableMain__ItemTypeID__RowID on TableMain (ItemTypeID, RowID) go
alter table TableDetails add constraint FK_TableDetails_TableMain__RowID__ItemTypeID foreign key (ItemTypeID, RowID) references TableMain(ItemTypeID, RowID); go
alter table TableMain add constraint FK_TableMain_ItemTypes__ItemTypeID foreign key (ItemTypeID) references ItemTypes(ItemTypeID); go
/* drop table TableDetails drop table TableMain drop table ItemTypes */
As you can see TableDetails references TableMain by ItemTypeID and RowID. TableMain also has a foreign key on ItemTypeID.
In this example although there is no references between TableDetails and ItemTypes on ItemTypeID field, referential integrity is still maintain because of the foreign key on TableMain (ItemTypeID, RowID)
So here is my question. Although referential integrity is maintain with this structure, would you guys still create a foreign key on TableDetails (ItemTypeID), i.e.:
alter table TableDetails add constraint FK_TableDetails_ItemTypes__ItemTypeID foreign key (ItemTypeID) references ItemTypes(ItemTypeID); go
There might not be any right or wrong, it might be a personal preference sort of thing. To me it seems performance wise it's better to not create this extra contraints since it doesn't add any additional integrity, on the other hand when looking at a DB schema this extra constraint might help understanding what's going on. Also perhaps it helps SQL Server in picking the right execution plan but that I am not sure.
Perhaps the solution is to create the constraint with a NOCHECK on it... is it?
We are in search of beta testers for our new software tool for SQL Server : Remote Keys allows you to define and enforce cross databases integrity constraint. With Remote Keys, you can create a foreign key between to SQL tables located in distinct databases.
Beta version can be downloaded here : www.remote-keys.com. Thank you,
We are in search of beta testers for our new software tool for SQL Server : Remote Keys allows you to define and enforce cross databases integrity constraint. With Remote Keys, you can create a foreign key between to SQL tables located in distinct databases.
New version (beta 2) can be downloaded here : http://www.remote-keys.com
I allow the user to delete record1 from SQL Table1 and record2 from Table2. The only problem is, record1 and record2 refers to record3 in Table3 and I can´t allow the user to delete record1 if the is a field in record3 with record1 ref. code. I can´t set FK between them cuz there is more than one reference to the same field. Can someone point the best solution for my problem?
im trying to set up maintenance plan for the check database integrity...
In sql 2000 you get a nice little log in SQL Logs DBCC CHECKDB (WSS_Search_db3) executed by xxx found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 1 seconds.
But in SQL 2005 Im not getting a nice log of this but getting it against some system database and not the database i selected
Date1/22/2008 5:19:43 PM LogSQL Server (Current - 1/22/2008 5:19:00 PM) Sourcespid84 Message DBCC CHECKDB (mssqlsystemresource) WITH no_infomsgs executed by XXX found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 1 seconds.
Anyone got this to work? Trying to get the same message i got for SQL 2000 or at least so i can confirm it ran.
I have no way to test this. If let's say the database has logical integrity errors, will it throw back an error to the Check database integrity object such that, the error arrow (the red line which is the error handler stuff) will be triggered for notification purposes?
what does check database integrity in maintenance plan do.
i heard that the transactional log growth in sql server should be fixed to maximum available disk space and must be monitore the threshold through maintenance plan.how can it be done. please advice me through maintance plan instead of shrinking the log as it is unnecessary IO to the disk.
How often is the check database integrity scheduled. is it daily or weekly.
I have a database hosted that doesnt passes the integrity test. Here is the error it gives : Executing the query "USE [db_cs] failed with the following error: "Could not locate entry in sysdatabases for database 'db_cs'. No entry found with that name. Make sure that the name is entered correctly.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Any idea whats wrong and how I can let the integrity test pass for this db.
Since most use SQL server, I thought I would post the question here.Is it possible, or is there a product or DBMS that enforces referential integrity across multiple databases and database types? Such as SQL Server, Oracle, etc...Thanks,Zath
I have setup a full maintenance plan on SQL2005. When I run the job, I don't see any error but by loooking at the Log file viewer it appears that Check Database Integrity step failed because :
Alter failed for Server 'LUMONT001'.
Moreover, when I run DBCC CHECKDB from the console I have no error message. Any clue? Thanks, Paul
I searched the forum on this topic and saw the following explanation on Check Database Integrity Task, by DarrenSQLIS.
"The Check Database integrity task will fail if the DB has a problem. The task fails; the details are raised in the error event and dumped to the log etc. You can use on Failure precedence constraints or an OnError event handler to capture that failure and do something if you wish."
Here is the link to the quote above. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2290253&SiteID=1
I would like to continue this discussion on this new thread to help my understanding.
I understand that this command does dbcc checkdb( ) command on a specified server for the databases you want to check. And if a database has a problem the task will fail and raise an OnError event. DarrenSQLIS goes on to say that the error is dumped to a log. What log is this? Where do I specifiy the location of this log?
As per the advice given by somebody i set ldf initial log size as 29 gb and restricted the growth and set the autogrowth to 500 mb i did not run and shrink ldf command on daily basis. this is the advice given by another dba.
then he suggested to create a maintenance plan to check database integrity check. whick will check the disk space threshold.
i did not understand how will the maintenance plan check the disk space and give an alert. where should i check the alert for this maintenance plan. secondly how can i stop my database log file to deliver a message that my disk is full.
please suggest me a best method. where i am wrong , how should i handle the situation without the dba monitoring. and our system has lot of batches running for every minute.
I met a very strange problem recently. I set up a database integrity check maintenance plan. But this job failed every time. I looked into the logs, the error message was that Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped. I used the sp_helpdb to check the version of the databases included in my maintenance plan. The sp result shows that all the databases are above version 80....
Even more strange, i can successfully run the dbcc check query on each database.
Any comment and suggestion will be very appreciated.
I have built a package that firstly shrinks a database and then backs it up which is scheduled to run each day, but I would like to add a check (Check Database Integrity) in as the first task and if the Database checks out ok then continue on, if not send an email.
Now, I am unsure if the Check Database Integrity Task in SSIS actually returns the success or failure message back to the package that I am after. The SQL behind the task includes the NO_INFOMSGS option.
Can anyone advise if it is possible to have the status/integrity of the database returned to the SSIS task and to proceed based on the result?
Hi all, I am writing a portion of an app that is of intensely high online eCommerce usage. I have a question about identity columns and locking or not. What I am doing is, I have two tables (normalized), one is OrderDemographics(firstname,lastname,ccum,etc) the other is OrderItems. I have the primary key of OrderDemographics as a column called 'ID' (an Identity Integer that is incrementing). In the OrderItems table, the 'OrderID' column is a foreign key to the OrderDemographics Primary Key column 'ID'. What I have previously done is to insert the demographics into OrderDemographics, then do a 'select top 1 ID from OrderDemographics order by ID DESC' to get that last ID, since you can't tell what it is until you add another row.... The problem is, there's up to 20,000 users/sessions at once and there is a possiblity that in the fraction of a second it takes to select back that ID integer and use it for the initial OrderItems row, some other user might have clicked 'order' a fraction of a second after the first user and created another row in OrderDemographics, thus incrementing the ID column and throwing all the items that Customer #1 orders into Customer #2's order.... How do I lock a SQL table or lock the Application in .NET to handle this problem and keep it from occurring? Thanks, appreciate it.
In our production we have a database by name MyDb.The application team wanted another database by the name MyDbOld which contains the data of Mydb 1 month old. So I created a database MyDbOld in other instance(test) and restored MyDbOld from the one month backup of MyDb in production server. In test instance I backed MyDbOld as MyDbOld.bak...
In the production instance I created a new DB by the name MyDbOld and restored the MyDbOld in the production from the backup of MyDbold.bak in the test instance. Now I have 2 databases in the production instance ie MyDb and 2)MyDbOld. However I find the logical names of both the databases to be the same although the database name and the physical file name to be different.My questions are-:
1) Does it in any manner affect the integrity of the 2 databases if the 2 databases are in the same instance and have the same logical name? 2) Would the dml or data retrieval operations in the 2 databases have any conflict in any manner what so ever? 3) Having a unique database name, and unique file names for the physical files for any database in an instance what is the purpose and significance of additionally having a logical name for a database? 4) Which I could restore a database from the backup of another database in the same instance and at the same time change the logical and physical name of the files to correspond to the new database.
" I have two sql server2000 database named db1 and db2. i have a user named 'user1' who has permission in both database.I have used a 'Select * from tableOne'in db1 when i have this table 'tableOne'in db1. now this table was droped and created in db2.
what i need is i should log in to db1 and access the same select statement which is there in application used by my clients.
i have created a view in db1 with the same name as
'create view tableOne as select * from db2..tableOne'
now i can access. Is there some othere way with out creating view?
I've been running the Ola Hallengren maintenance script for the last five months without missing a beat. Today I find an error stating the UserDatabase Integrity check job failed last night. This is running on SQL Server 2014 BI edition w/64 Gigs.
I ran a DBCC CHECKDB on each database manually and all worked until I tried it on the biggest one that is about 18 gbs. It just keeps running and I eventually stopped it so I'm guessing it is memory, but doesn't make sense considering it has 64 gbs. I have it set to 64/4 max / min. Again, this was never an issue until last night.I've been looking up all morning, but not seeing much on this error "The operating system returned error 1453"?
I installed SQL Server 2005 Enterprise, then SP1 and then SP2, Maintenance Plan worked. But if I installed SQL Server 2005 Enterprise and then SP2 directly (skipping SP1), the Check Database Integrity in Maintenance Plan was not working. The error message is as follows:
Executed as user: DomainSqlServiceAccount. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:56:21 AM Could not load package "Maintenance PlansTest Plan" because of error 0xC0014062. Description: The LoadFromSQLServer method has encountered OLE DB error code 0x80004005 (Login failed for user ''. The user is not associated with a trusted SQL Server connection.). The SQL statement that was issued has failed. Source: Started: 10:56:21 AM Finished: 10:56:21 AM Elapsed: 0.047 seconds. The package could not be loaded. The step failed.
Other tasks like Rebuild Index seem to be fine. SP2 is supposed to be inclusive. Does anyone have any ideas on why this is happening.
Hi, I'm doing a web application that will get some information from an ERP. At this moment I have 2 databases: 1) The aspnetdb, where I have the tables for Merbership and Role 2) The ERP database I need to put my web application tables on one of these two DB's. This tables will reference the users from the membership and some products from the ERP DB. I will store products requests that will store both UserID (from aspnetdb) and ProductID (from ERP DB). I'm thinking to put these tables on the aspnetdb, so that all web application tables stick together. But, I will loose tha ability to make joins with the ERP database, right? Do you think this will work? Can someone make some comments about this situation, and give me some tips? Thank you!
I am interested in adding a new row to a table 'Table05' that exists in a SQL Server 2005 database whenever a table 'Table00' in another SQL Server 2000 database has a row added to it. Can someone tell me a way to implement the above solution?
How do you write a SQL SELECT statement for a cross-database query in ASP.NET (ADO.NET). I understand the server.database.owner.table structure, but the command runs under a connection. How do I run a query under two connections?
Hi, I'm trying to get data from several databses of different kinds (Sql Server and Oracle) and from different servers - is that possible ? If not - is there any way to copy some data from one database to another - like tables and their content (or partial content) ?
I tried to use "select name from <database name>..sysobjects where xtype = 'U'" but I get security error.
i know that db.owner.tablename works in the query analyzer, but what i really appreciate from anyone is how to apply this in vb6 code since the recordset is opened only from one db using the following syntax:
I recently found out that using ADO to connect to the SQL server without mentionning the data source; then, by sending a query with the following syntax: dbname.owner.tablename.columnname... an implicit connection to the data source (database) is performed automatically. This way of connecting allowed me to manipulate accross two or more databases thing that is necessary in my project. My question is:
1- Do anyone have any bad experience and/or negative consequence to such connectivity (memory consumption, unexpected disconnection, ...)? Please consider a very high frequency of manipulation since we are dealing here with a 24/24 hour operational site.
2- Are there any alternative solutions for cross database manipulation (select, insert, update, delete)?
