Is it possible to create relationships and enforce constrains thereof between two fields with the same data type from 2 different databases ( i.e. 2 different .mdf files ) altogether, attached to an instance of SQL Server 2005 Express ?
This would help overcome the limitation of the 4GB database size it imposes, by splitting the info required into different databases, rather than just different tables. All the relationships could be then created and a program could be created using Visual Basic or C# or C++ to access all the data meaningfully.
I also say this, because in a scenario like where some common piece of data, like contact info, has to be shared for different purposes, which really require two different databases, and the 2 databases cannot be integrated into one, but have to stay separate, would lead to duplication of data, where the same contact info would have to be entered and stored twice. Now, instead, if we create three databases in all, with the third one being the contact info database, we can have the 2 original databases drawing all their required info from the contact database, there need not be any duplication of data. Actually, this is the very purpose of the RDBMS model, isn't it, to reduce unneccesary replicaion ?
So, is there any way to do it, because I tried creating relationships between two tables from 2 different databases in SQL Server Management Studio Express, and I was unable to find any option that would allow me to do the same. I just use the GUI tools in it, not T-SQL.
Mind you, you are speaking to a rookie here, who has absolutely no programming knowlwdge. I have just migrated here from MS Access, and I currently am using Managemnt Studio Express and Visual Basic 2005 Express and I just know how to use the GUI tools in all these programs. I do not know any T-SQL or where to issue T-SQL commands from.
? No, it is not possible. If your only goal is to get around the 4gb limit, then my suggestion is that you upgrade to Workgroup Edition, which has no such limitation. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Rishi Khetan@.discussions.microsoft.com> wrote in message news:d2141cdb-833b-49ba-8bc4-98feb1cd8a83@.discussions.microsoft.com... Is it possible to create relationships and enforce constrains thereof between two fields with the same data type from 2 different databases ( i.e. 2 different .mdf files ) altogether, attached to an instance of SQL Server 2005 Express ?This would help overcome the limitation of the 4GB database size it imposes, by splitting the info required into different databases, rather than just different tables. All the relationships could be then created and a program could be created using Visual Basic or C# or C++ to access all the data meaningfully. I also say this, because in a scenario like where some common piece of data, like contact info, has to be shared for different purposes, which really require two different databases, and the 2 databases cannot be integrated into one, but have to stay separate, would lead to duplication of data, where the same contact info would have to be entered and stored twice. Now, instead, if we create three databases in all, with the third one being the contact info database, we can have the 2 original databases drawing all their required info from the contact database, there need not be any duplication of data. Actually, this is the very purpose of the RDBMS model, isn't it, to reduce unneccesary replicaion ?So, is there any way to do it, because I tried creating relationships between two tables from 2 different databases in SQL Server Management Studio Express, and I was unable to find any option that would allow me to do the same. I just use the GUI tools in it, not T-SQL. Mind you, you are speaking to a rookie here, who has absolutely no programming knowlwdge. I have just migrated here from MS Access, and I currently am using Managemnt Studio Express and Visual Basic 2005 Express and I just know how to use the GUI tools in all these programs. I do not know any T-SQL or where to issue T-SQL commands from.|||No NNTP User,
my only goal is not to get around the 4GB limit. As I mentioned in the later part of my post, I also want to 'borrow' the data from one completely different database in another database. I want to eliminate duplication of data in the 2 separate databases by borrowing common data from one db. That's the main reason I wanted to do that.
Can you think of any workaround ?
|||? You can enforce cross-database relational integrity using DML triggers. But putting everything in the same database and using schemas to seperate logical boundaries is a much better solution. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Rishi Khetan@.discussions.microsoft.com> wrote in message news:7915ff66-3526-4b90-aea2-8762ed87d8dc@.discussions.microsoft.com... No NNTP User, my only goal is not to get around the 4GB limit. As I mentioned in the later part of my post, I also want to 'borrow' the data from one completely different database in another database. I want to eliminate duplication of data in the 2 separate databases by borrowing common data from one db. That's the main reason I wanted to do that. Can you think of any workaround ?|||To add on to NNTP User, you can also wrap them in distributed transactions. Just make sure the design of one table in one database is appropriate for "virtually" creating a relationship between the other table on the other database.|||You can enforce cross-database relational integrity using DML triggers. But putting everything in the same database and using schemas to seperate logical boundaries is a much better solution.
I'm afraid I've lost you guys, I think I really need to go and learn some T-SQL first, as somebody else suggested to me a while back. Meanwhile, could you please give me the above directions again using the GUI tools of Management Studio Express and VB Express ? Also, what are 'distributed transactions', bass_player ? And how do I use DML triggers, NNTP user ? Isn't DML short for Data Markup Language or something ?
|||? Actually, you only need distributed transactions if you're going across servers or instances. Cross-database work in the same instance will not need to make use of a distributed transaction. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <bass_player@.discussions.microsoft.com> wrote in message news:793732c8-c600-43b9-8e1a-294100137ac6@.discussions.microsoft.com...To add on to NNTP User, you can also wrap them in distributed transactions. Just make sure the design of one table in one database is appropriate for "virtually" creating a relationship between the other table on the other database.|||? Unfortunately, you can't do most of this stuff via the UI. DML is Data Manipulation Language; DML triggers fire on DML events such as inserting, updating, or deleting data. I recommend that you pick up a good basic SQL book to start with. I'm not sure if there are any out for SQL Server 2005 yet, but you might just start with a SQL Server 2000 one. I've heard good things about "Murach's SQL For SQL Server" by Bryan Syverson. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Rishi Khetan@.discussions.microsoft.com> wrote in message news:b6185a4a-9770-456b-86e3-feacd72df877@.discussions.microsoft.com... you can also wrap them in distributed transactions. You can enforce cross-database relational integrity using DML triggers. But putting everything in the same database and using schemas to seperate logical boundaries is a much better solution. I'm afraid I've lost you guys, I think I really need to go and learn some T-SQL first, as somebody else suggested to me a while back. Meanwhile, could you please give me the above directions again using the GUI tools of Management Studio Express and VB Express ? Also, what are 'distributed transactions', bass_player ? And how do I use DML triggers, NNTP user ? Isn't DML short for Data Markup Language or something ?|||
Rishi, it is not possible to enforce foreign key relationship across database. NNTP is right, you can use triggers. Triggers are programs which runs when there is a new rows created in a table. Or when there is a row deleted from a table. Or when a row is updated. That's DML trigger, there is something else called DDL trigger, which is a new feature in 2005, but let's not talk about this right now.
Compared to foreign key (FK), this DML trigger has a performance issue, it will make the insert (or delete or update) slower than usual. But it has advantages: it is more flexible than FK. A short answer to your question is that yes you can span across several databases of 4 GB each to create a larger system.
Vincent
A short answer to your question is that yes you can span across several databases of 4 GB each to create a larger system.
But for that, I would have to write code, wouldn't I ? I mean, I cannot do all this stuff using the GUI ?
Anyway, I wouldn't want to implement a design which slows down the performance of the database much. Thanks for all the help guys.
Rishi.
|||Say, is there anyway I can create the relationship in the front end application using VB Express ?
Because I really want to put all the info into one application. Its like having everything under one roof. A complete ERP solution ( for small proprietory concerns with no employees, not counting peons ). I have seen many organisations use different software for different purposes and I did not like the idea. For instance, they use different software for keeping track of their inventory, employee details (which covers employee attendance, performance, incentives etc.), financial records etc. Now for example, I have the inventory software, which has a separate table which stores employee names, and this is used so that the sales reports show the employee wise sales, but all the main employee information is stored in the database of another application altogether. Now, suppose I am the manager, and I am going through the sales by emp report, and I suddenly feel I want to know more details about the emp, so in this scenario, I would have to quit the inventory program, fire up the employee program, and then look up the employee's name, and then pull up the emp's info. Now wouldn't it be nice if I had both the databases linked in the front end application, so I could directly pull up the employee's info ? Why I don't want to put both these separate pieces of info in the same database is again performance, and I need to clarify this point - If I have both the inventory and the employee databases from the above example consolidated into a single database, would it slow down the performance of the database as it grows larger in size ? Or would it offer performance benefits if they are kept in separate dbs and are linked through the front end.
I know the idea given to me above about using DML triggers would considerably slow down the database, as the program would have to scan every record every time a record is created or updated or deleted. So I have scrapped the plan of using DML triggers ( after learning DML of course )
Any ideas about using the VB Express created front end application though ?
|||>>I've heard good things about "Murach's SQL For SQL Server" by Bryan Syverson.
Anyone know if there's going to be a SQL Server 2005 version of this book? I just "discovered" it last night at B&N; it looks like a great book. However, there's nothing on Amazon.com nor the publisher's site about an upcoming revised edition. 
No comments:
Post a Comment