Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
eyau1981
Starting Member
2 Posts |
 Posted - 2009-10-20 : 15:11:36
|
| Hello all,I have an interesting issue for you to ponder.I am developing a small-scale document management system, and I'm trying to get round the following issue:I have a 'Document' table with an ON CREATE trigger that creates tables in various other databases with the following code:IF OBJECT_ID ('dbo.'+@docID, 'U') IS NULLBEGINCREATE TABLE [dbo].[newDocumentType]( [ID] [int] IDENTITY(1,1) NOT NULL, [Pattern] [nvarchar](100) NOT NULL, [Replacement] [nvarchar](100) NOT NULL, [UseWildcards] [bit] NULL DEFAULT ((0)), [CaseSensitive] [bit] NULL DEFAULT ((1)), [Style] [nvarchar](20) NULL) ON [PRIMARY]EXEC sp_rename [newDocumentType], @docIDENDThis works fine, although I get a warning "Caution: Changing any part of an object name could break scripts and stored procedures", which is normal.The front end of the system, for legacy reasons, is in MS Access.Everything is fine when I create a 'document' through Access.But I figure if I am creating lots of these I want a batch-import function that runs an StoredProc with an INSERT on the 'Document' table. This StoredProc runs fine from Management Studio. The problem I have is that Access seems to be stopping after it gets this warning message. I see 2 ways around this.1) I find an alternative way of creating the table2) I find a way of preventing this message from propagatingFrom what I have read, this is a level 10 error, so SQL Server treats it as a non-fatal error, hence why it works in Management Studio.I would be grateful for any helpful insights into how to get this working, as I am really stuck!Many thanks, Ed |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-10-20 : 15:15:03
|
| try setting ANSI WARNINGS to OFF |
 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-10-20 : 15:34:13
|
| The model of a new table for each document type seems fishy to me. Is there a reason you can't use one table that includes a DocumentTypeID column? Generally speaking a physical model that changes dynamically as data changes is not good. Also, tying object creation to a user transaction can make for a frustrating user experience.Be One with the OptimizerTG |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2009-10-20 : 16:28:27
|
| i agree with TG. also what happens with concurrent entry by multiple users. sounds fishily unscalable. you might need to look into one main document table, and if you need to keep track of multiple data regarding the document (metadata) I would go with either multiple fields per one master document table or XML it into an XML field<><><><><><><><><><><><><><><><><><><><><><><><><>If you don't have the passion to help people, you have no passion |
|
|
|
eyau1981
Starting Member
2 Posts |
Posted - 2009-10-21 : 08:29:20
|
| Hello all,Thank you for the pointers and feedback!I can tell you are all far more experienced than me when it comes to DB design so I welcome all comments.I agree that changing the model for each new document sounds rather silly. It got me thinking a bit about why it's been designed this way.The Document table is actually for 'Document Types' this stores the info needed for invoicing purposes. There is only one of these. There isn't actually a table that records where the individual documents are.When work is processed, it goes through a workflow engine that runs various transform rules. These rules are different for each document type. So I have a number of other databases that store these rules, there is a separate database for each stage (e.g. pretransform, posttransform), with a table that is named according to an ID from the Documents Table (so there is a 1-1 mapping). Perhaps overkill to split it so much, but I think the main reason I did this was so that the rules would be easy to manage.I suppose with a bit more hindsight, I might have had just the one database for the transforms, with different tables for each stage, with the rules all in one table, but with an extra column for the ID that we could use to separate them.That I suppose would solve this problem I'm having!Thanks, Ed |
|
|
|
|
|
|
|
|
Hiding error message