InstantForum.NET v4.1.4SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 17 May 2008 09:15:40 GMT20http://www.sqlservercentral.com/Forums/Topic502399-361-1.aspx"There's a fine line between stupid and clever." - David St. HubbinsA database logging scheme is implemented by including calls to a LogAuditEvent routine in stored procedures. LogAuditEvent adds records to this table:[code]CREATE TABLE [Security].[AuditLog]( [AuditLogId] [int] IDENTITY(1,1) NOT NULL, [DateTimeStamp] [datetime] NOT NULL, [AuditActionTypeId] [tinyint] NOT NULL, [SourceObjectId] [smallint] NULL CONSTRAINT [DF_AuditLog_SourceObjectId] DEFAULT ((0)), [TargetObjectId] [smallint] NULL CONSTRAINT [DF_AuditLog_TargetObjectId] DEFAULT ((0)), [TargetId] [int] NULL, [ModifiedBy] [Security].[userid] NULL, CONSTRAINT [PK_AuditLog] PRIMARY KEY CLUSTERED( [AuditLogId] ASC)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY][/code]Instead of the SourceObjectId and TargetObjectId fields in the table above, the original logging scheme had a variable of type sysname for the name of the stored procedure that was performing the action that was being logged (the source) and a second sysname variable for the table in which the change was being made (the target). In some cases a given stored procedure might modify more than one table so logging just the stored procedure name was not sufficient. I changed the LogAuditEvent routine and the Audit log table to use numeric identifiers instead of the actual database object names.MS SQL Server already has an internal table of database objects and numeric identifiers but those IDs are not meant for being referenced by standard database applications. A simple table could be used to map names of stored procedures and tables to unique identifiers but what about creating an extended property for each database object of interest with a unique ID and accessing that as part of the logging scheme? An administrative routine could add an AuditId property set to a unique number to all user-defined tables and stored procedures. A GetAuditId function could be used in the audit logging stored procedures to retrieve the integer value given the name of a database object:[code]CREATE FUNCTION [Security].[GetAuditId]( @schema sysname ,@objname sysname ,@objtype sysname = 'table' -- 'table' or 'procedure')RETURNS intASBEGIN DECLARE @RetVal int SET @RetVal = (SELECT CONVERT(int, value) FROM fn_listextendedproperty (NULL, 'schema', @schema, @objtype, default, NULL, NULL) WHERE objname = @objname And [name] = 'AuditId') IF @RetVal Is null RETURN 0 RETURN @RetValEND[/code]Advantages to this scheme:* IDs are part of the object definition.* Audit logging table can be more normalized by storing IDs instead of text names.Disadvantages* Extended properties don't enforce referential integrity such as preventing duplicate, bogus or missing AuditIds.* AuditIds are stored as character strings instead of actual integers and require conversion overhead.* After new tables and stored procedures are added to the database during project development, the administrative routine must be run to create extended properties on the new objects.Are there any other significant advantages or disadvantages? Is using extended properties to store database object IDs a reasonable way to go or would a user-defined mapping table be preferred? Is there a case to be made for logging the actual names instead of IDs representing the names?Part of the database design involves tables where each record can be linked to one of several other tables. For example, there is an Address table where an address can be linked to an entry in a Company, Supplier, Branch, Customer or Employee table. The linkage is handled via SourceTypeId and SourceId fields. Here is the Address table definition:[code]CREATE TABLE [Common].[Address]( [AddressId] [int] IDENTITY(1,1) NOT NULL, [SourceTypeId] [smallint] NULL, [SourceId] [int] NULL, [AddressTypeId] [tinyint] NULL, [AddressLine1] [varchar](64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [AddressLine2] [varchar](64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [City] [varchar](32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [State] [nchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [ZipCode] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Notes] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Created] [datetime] NULL CONSTRAINT [DF_Address_Created] DEFAULT (getdate()), [Modified] [datetime] NULL, [ModifiedBy] [Security].[userid] NULL, CONSTRAINT [PK_Address] PRIMARY KEY CLUSTERED( [AddressId] ASC)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]EXEC sys.sp_addextendedproperty @name=N'AuditId', @value=N'12' , @level0type=N'SCHEMA', @level0name=N'Common', @level1type=N'TABLE', @level1name=N'Address'ALTER TABLE [Common].[Address] WITH CHECK ADD CONSTRAINT [CK_Address] CHECK (([ZipCode] like '[0-9][0-9][0-9][0-9][0-9]' OR [ZipCode] like '[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'))ALTER TABLE [Common].[Address] CHECK CONSTRAINT [CK_Address][/code]There is another simple table called SourceType which assigns IDs to individual tables. [code]CREATE TABLE [Common].[SourceType]( [SourceTypeId] [smallint] NOT NULL, [SourceType] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NULL, CONSTRAINT [PK_SourceType] PRIMARY KEY CLUSTERED( [SourceTypeId] ASC)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]EXEC sys.sp_addextendedproperty @name=N'AuditId', @value=N'42' , @level0type=N'SCHEMA', @level0name=N'Common', @level1type=N'TABLE', @level1name=N'SourceType'[/code]As an example, the Company table has a primary key field called CompanyId. In the SourceType table, there might be a record with a SourceType value of "Company" and a SourceTypeId of 4. Within the Company table we might have a record with a CompanyId value of, say, 216. If we now want to link an address to this record in the Company table, we would create a new record in the Address table with a SourceTypeId of 4 to indicate that the record comes from the Company table and a SourceId of 216 to indicate the record within the Company table to which we are linking. If every database object has a unique ID and those IDs are stored in extended properties (assuming the auditing schema was implemented this way) does it then make sense to scrap having a SourceType table containing object IDs and instead grab the object ID from the extended property? In other words, is it clever or stupid to employ the same extended properties technique that was used for storing auditing IDs as the means of linking records in common tables such as the Address table to other tables? Is there an entirely different design that would be preferable?Fri, 16 May 2008 16:12:07 GMTbrucefhttp://www.sqlservercentral.com/Forums/Topic501859-361-1.aspxHi - I'm currently working on a project to build a data mart for a web site, the data mart is to measure traffic coming to the website. The website tracks page impressions, so we have a row of data for each page that a user views.The fact table (fctPageImpression) only requires a few dimensions, Date, Time, User, Page, Source (where the user came from) and Referrer. There are 2 measures Page Impressions and Users, PageImpressions will be calculated by summing the Amount col and Users will be calculated by performing a distinct count on the UserKey col. I estimate this fact table will have half a billion rows per year added to it.Please view attached PageImp_ERD_a.jpgWhile i have been getting more into the detail of the design i have realised that 90-95% of the queries are based on combinations of channel, date and source, measuring users and Page impressions. It is also true that 95% of these are measuring users rather than page impressions.Based on this i am considering an alternative design please see attached PageImp_ERD_b.jpgThe difference between a and b is that the fact table has been split in half, with an AggKey added to join them back together. PageKey and Time key have been moved into the detail fact, and ChannelKey has been added to the Agg Fact. I estimate that the Agg table will have 60% less rows than the detail table (per year this works out to 300,000,000 less) and will service 90% of the queries. BUT adding the detail table and relating via the AggKey means that no detail is actually lost because that join gives me back the full detail.Given the gains this does seem to be not a completely insane idea but its not something i've ever seen done before. Can i get some general thoughts please? I'd really appreciate some considered thought on this.ThankyouBob.Fri, 16 May 2008 03:37:06 GMTbob pearmanhttp://www.sqlservercentral.com/Forums/Topic500350-361-1.aspxI'm currently developing an ASP.NET site with SQL Server 2005 Standard and I'd like to ask a question about the future of the database. It needs to have continuity and performance. I'm thinking about doing replication or mirroring for continuity and table partitioning for performance. I admit I've never done any of those before and I'll learn about them but they're not needed at this time. The question is, I'm currently designing the database and do I have to anything for consideration for those things I'm thinking of implementing later? For example, I'm using Identity in my tables but I've heard about identity crisis using replication with identity columns, therefore I'm thinking of using Guid's but now I fear the Guid column index itself will be the slowdown factor in the first place.Any suggestions to consider? I'd appreciate any opinions.Wed, 14 May 2008 05:52:43 GMTdeastrhttp://www.sqlservercentral.com/Forums/Topic501588-361-1.aspxAny guidelines/recommendations on that? That's the question one of our clients is asking and I'm kind of lost in what they wantThanks!Thu, 15 May 2008 12:51:43 GMTBoris Tiokhinhttp://www.sqlservercentral.com/Forums/Topic500923-361-1.aspxI'm assisting another developer with database development. He described his problem, and basically described merge replication. The application has a master server, SQL Server 2005, and mobile client databases, which are all MS Access databases. When the client has a connection to the server (which it doesn't always have), the user can choose to sync the data. This should cause new rows on Access to be inserted into SQL Server and vice versa, and update both databases with whatever version of the row in either database is newest (based on timestamps). The amount of data is relatively small, but there are over 70 tables.This describes merge replication, except that the client database is MS Access! What kind of solution should I be investigating? Replication appears to require SQL Server instances on the mobile clients. Does SSIS provide this type of solution? I'm not familiar with SSIS but could read up on it if needed.Or, should this be custom-coded? In Access? In SQL Server? Is there a set-based way to accomplish this?I'm just looking for a direction to go.Wed, 14 May 2008 15:10:18 GMTStephanie Giovanninihttp://www.sqlservercentral.com/Forums/Topic500667-361-1.aspxI am managing the development of a database that could hold up about 18G of data. The project is evaluating the use of data persistence objects which I understand conceptually, but have not used in the development of a database system. If I understand this concept correctly the database tables become persistent data objects.This paradigm appears to me to represent an adhoc query mechanism to the database. What I am not clear on is how this methodology compares in terms of performance to using stored procedures which represent a compiled and optimized interface to the database. The data persistence paradigm would appear to be accessing data directly requiring each data access to be parsed, etc. which would incur considerable overhead.If anyone has experience with the use of data persistence and has information as to how this approach compares to the procedure approach in terms of performance I would appreciate some feedback. I am all for new technology but not if it will cost me in terms of performance.Wed, 14 May 2008 09:58:40 GMTmcginnhttp://www.sqlservercentral.com/Forums/Topic500662-361-1.aspxHi All I am currently having a database of 130 GB which contains tables for 3 years, splitted by months table. this is been accessed by an application.The data file is on a RAID 5 Lun, and its a 64 bit SQL 2005 std edition. i am planning to split the data into db's for each year and tables for each month, Will this improve performance while accessing data from 3 db's and 3 tables instead of 1 db and 36 tables.Is there any thing else i can do to improve performance of data retrival from this databaseCheersSujiWed, 14 May 2008 09:56:55 GMTSujihttp://www.sqlservercentral.com/Forums/Topic500653-361-1.aspxI'm trying to link AS400 server to SQL Svr from the SQL Server.I can see the AS400 in the servers list from SQL Server 2k5.However, when I tried to run a SELECT stmt, I got this error:OLE DB provider "IBMDA400" for linked server "as400" returned message "CWBCO1004 - Remote address could not be resolved ".Msg 7303, Level 16, State 1, Line 1Cannot initialize the data source object of OLE DB provider "IBMDA400" for linked server "as400".Desparately need helpWed, 14 May 2008 09:46:49 GMTPappohttp://www.sqlservercentral.com/Forums/Topic498944-361-1.aspxHi,I have a problem which appears simple on the surface and I am hoping there is a common design solution which I can apply.I won't initially go into great detail to avoid any confusion. For the sake of debate I have two tables, here are simplified versions:Transaction{ Id, Type, UserId, State, Description, Amount, EntryDate }(Again for the sake of discussion Transaction Types are Deposit and Withdrawal)Ledger{ Id, UserId, TransactionId, Amount, Balance, EntryDate }When I perform an Insert into the Transaction table I update the Ledger. For the most part this is a simple case of inserting the required data, the issue comes with the Balance column.I want the balance to be correct as it should be post-transaction. I can think of two approaches to this:a) Calculate the balance using a SUM on the Amount column of the Transaction table to determine the new aggregated value. My issue with this is performance and locking to avoid concurrency issues.b) Somehow perform this incrementally using some form of TOP ORDER BY combination. I prefer an incremental approach from a performance perspective but with respect to data integrity it makes me shudder an incorrect calculation for any reason would cause a ripple effect. Having said this it's obviously not acceptable for any solution to yield incorrect results :)I have implemented A and have found that I need to serialise the operation via transaction isolation levels / hints. This works however when more than a handful of concurrent transactions are running I cannot even read from the Transactions table without readuncommitted/nolock which is not something I want to start adding to the rest of the application unless I have to.I have played around with the various isolation levels although I will admit I am not entirely confident which I should be using just yet. Also I am currently calculating the balance in two phases: SET @DepositBalance = (SELECT SUM(Amount) FROM [Banking].[Transaction] --with (rowlock) WHERE UserId = @UserId AND Type = 2) SET @WithdrawalBalance = (SELECT SUM(Amount) FROM [Banking].[Transaction] --with (rowlock) WHERE UserId = @UserId AND Type = 3)I could compliment the stored transaction value with its negative counterpart if it's a withdrawal so this calculation could become a single SUM rather than the two. I believe I could experience many problems with the above if an insert operation occurs between my deposit and withdrawal calculations but then again the same could be said between the balance calculations and the Ledger insert operation.Any thoughts and ideas would be appreciated with regards to a safe solution to my problem, I hope the above makes sense it's been a long day ;)Regards,MarkMon, 12 May 2008 10:06:38 GMTquortexhttp://www.sqlservercentral.com/Forums/Topic498013-361-1.aspxThis is not a joke. A company I know of has actually decided to build a large production database where all of the data will be place in one large varchar column, of one huge table. There is another table that contains a "Label" and row type id that can be used to find the item in the other table. They say this will allow them to add new data items to the system without having to update the schema.Among the issues I see are:There is no way to index anything.Querying the data is a nightmare and requires joining 7-8 tables together to get one item that would be a column in a more traditional database.Because all data is stored in character form, doing any compares will first require that the entire data set you are looking at be converted to the correct type. (What happens when data that is supposed to be a date does not convert without an error?)Performance will be terrible.I have used this approach before, for certain limited cases. For example when storing the questions and answers to surveys, where the questions change from one year to the next.I am just wondering what industry professionals thing about this.Fri, 09 May 2008 09:40:34 GMTmichael.welcomehttp://www.sqlservercentral.com/Forums/Topic494118-361-1.aspxWe are migrating from an Access db because the volumes of data have got beyond its abilities.Everything we do centres around reports. These are always output in Excel, and may or may not contain pivots, charts and macros for onward processing by recipients. In most cases the reports need the base data rather than just the completed pivots.I've just spent 2 weeks writing a report I could do in a morning in Access, and it still won't work. Despite a good deal of help from this and other sites, I am stymied by 2 fields of greater than 255 characters. If I leave them as a unicode string they fail on output due to truncation. If I use a data conversion to make them into unicode text streams I get an error about not being able to create an OLE DB Accessor.If I get past this problem I know I'll get another and I've reached the end of the road with this, as I've got at least 50 more reports queuing up for the same treatment.So what I'm thinking now is to abandon SSIS, write views that extract the subsets of data needed for each report, and do the nitty-gritty processing in Access.Is this a bad idea, or does anyone think it has some genuine merit?Fri, 02 May 2008 04:47:27 GMTborn2bongohttp://www.sqlservercentral.com/Forums/Topic498919-361-1.aspxHi all,I have an Equipment table with the columns EquipID and EquipTypeID (amongst others). I then have other tables for different equipment types with more type specific detail in them. I want to search for Equipment based on information in the detail tables, i.e. I want to search using a keyword and pull back all equipment, regardless of type, that has data in columns that match the keyword.I have two ways in which I think I can do this at the moment and was looking for some advice as to which way to go.1) I can write a procedure that searches the equip table and inner joins to a detail table for a type, then union that onto the same again linking to each of the detail tables in turn where the detail.column(s) like '%keyword%'.2) I can add a keyword column to the equipment table and add a trigger to each of the detail tables updating the keywords when detail is changed. I can then search just on the equipment table.Any advice on the above, or alternatives would be greatly appreciated.Thanks in advance,Al.Mon, 12 May 2008 09:32:14 GMTAlan Kellhttp://www.sqlservercentral.com/Forums/Topic498508-361-1.aspxA quick technique I'm sure many of you have already used, but thought I'd share anyway:[code]SELECT TOP 0 * INTO ##tempTable FROM existingTable[/code]-- Update: Corrected thanks to Jeff Moden --If you have any other (better) ways, please share.Best,Sun, 11 May 2008 18:19:46 GMTTed Pinhttp://www.sqlservercentral.com/Forums/Topic492140-361-1.aspxI want to enable READ COMMITTED SNAPSHOT isolation on one of our busiest OLTP systems.I know this will require extra space for the tempdb files and possibly a faster disk subsystem, RAID 1 or RAID 10. I am also aware that extensive testing will be needed.Anything else to watch out for? Does anyone have any positive/negative experiences to share?Tue, 29 Apr 2008 08:09:56 GMTMarios Philippopouloshttp://www.sqlservercentral.com/Forums/Topic496043-361-1.aspxi've have these following tabletblTeamDetailsDepartment | Team_Name | Team_ID | Status---------------------------------------------------Computer | Spider-Man | 0002 | AvailableComputer | Hulk | 0003 | AvailableComputer | Iron Man | 0004 | AvailableComputer | Wonder Women | 0005 | AvailableSales | Robocop | 0006 | AvailableSales | Hancock | 0007 | AvailableSales | Superman | 0008 | AvailableSales | Flash | 0009 | AvailableSales | Storm | 0010 | AvailableAccount | Punisher | 0011 | AvailableAccount | She-Hulk | 0012 | AvailableAccount | He-Man | 0013 | AvailableAccount | Wolverine | 0014 | AvailableCorporate | Thor | 0015 | AvailableCorporate | Capt. America| 0016 | Available*Combination of Department and Team_ID generate a primary key.Is that the best design? Can anyone show me the best design?Tue, 06 May 2008 21:43:01 GMTSharul Nizamhttp://www.sqlservercentral.com/Forums/Topic497721-361-1.aspxI have to build a very strange query that has been bothering me for a few days now and it's starting to get the best of me.I know it might just have a simple solution, but it has eluded me for a long time and I just can't nail a solution that will return the corect subset on all situations. I'll try do decribe the problem as simple as I can:Using the standard Client, Order, Products metaphor, let's say I have a table that describes a many-to-many relationship, say ordered Products:ClientID | ProductID 1 | 30 1 | 35 2 | 30 3 | 30 3 | 31etc..Then I have this second table that acts like a "filter table"ProductID(int) | HasOrdered (bit NULL) 30 | 1 31 | 1 32 | 0 35 | 0 40 | NULLBased on the data in the second table, I need to get all the clients that have ordered products 30 and 31 but have not ordered products 32 and 35.However, I also need to select (in the same query) all of the clients that have ordered product 40 (where HasOrdered is NULL) regardless if they have ordered any of other products or not.The rule is : to select all (clients that have ordered ProductIDs where HasOrdered is null) OR ( clients that have ordered ALL products where HasOrdered =1 AND that have not ordered any of the products where HasOrdered = 0 )I could never find the corect way of joining all the tables to work in all situations: eg. if there are no rows with HasOrdered=1 or none with HasOrdered =0 or HasOrdered =NULL. Some situation will always mess up my joins and it will return a wrong subset of clients.Hope I managed to make sense in the post.Thanks for anyone dropping a hand to help me !Fri, 09 May 2008 04:37:34 GMTradu.poenaruhttp://www.sqlservercentral.com/Forums/Topic496151-361-1.aspxHi,I'm thinking on a reporting solution with SQL Server 2005.The requirements are that there must be a database to hold data from the last 2 months, and another database or mechanism to archive and compress the older data (due to the large amount of data).However, there is a requirement that the reports may retrieve data from the archive, when needed.The problem is that this archive database can have thousands of GB.What strategy do you recommend to archive the old data? Is that a way to have a compressed archive database and make a restore only of restricted time data? For example, if I want to retrieve a report with data of the last 2 months of 2007, is that a way for me to decompress or restore only data from that time window?Thanks.Wed, 07 May 2008 03:34:52 GMTheliomgghttp://www.sqlservercentral.com/Forums/Topic490723-361-1.aspxWhen archiving data, would you keep the data in the same database but in a new table or in a different database, altogether?We have a few tables which can contain millions of records.After a configurable point in time, our application will move the records older than x days to another location. What we have to decide is should that be a different table in the current database or a different database.Does anyone have any suggestions. I have been thinking about the cost of index updates, statistics maintenance, etc. The work still has to be done by SQL Server but will it occur all in one database or in two databases--depending on the chosen model.Fri, 25 Apr 2008 09:00:48 GMTholidasahttp://www.sqlservercentral.com/Forums/Topic494787-361-1.aspxHi All,I am developing a social networking site which is based on SQL 2005 servers and ran into some trouble with my design.When user logs on, or when he wants to visit another user's pages, the system uses data dependant routing to direct user to the appropriate server where the requested data is actually stored based on the username.The DB design is such that, there is one lookup table that maps usernames to data servers. There are then several "Data" servers that hold all data belonging to particular subset of users.The idea is that once member logs on, he will continue interacting only with their own server without the need to increase load the other servers.Now that was the initial design and idea. We have now however added the ability for other users to add comments and display photo & screen name next to that comment. So for example if User1 has all data on Server1 and then User2 (from Server2) leaves a comment, this comment will be stored in "Comments" table on Server1 (and currently the photo and screen name of User2 will be stored there too). Now when User1 opens their comments page, he will be able to see comments from different people (and it's all being read from this Comments table)This is all fine until the User2 changes their screen name or profile photo. I would like to have the screen names & photos update immediatelly on all related posts across the system when it changes.However don't seem to find the best solution.I figure there are 3 options:1) Keep the current design - and when the photo is changed, connect to all servers in the system and update the photo path on all comments on all servers. (don't like it much as this may cause trouble when many users keep changing their photos/screen names often)2) Change the design so that the Photo/screen name is basically read from one location only (the owners Users Table). Since this would be a live link, all comments and pages would read it from here. But if there was a comments page with comments from 50 users from 50 servers, I would have to make connection to 50 servers to display that one page ... not ideal either.3) Have a special table holding screen name/photo of ALL users - and then have it replicated to all Data servers. The name/photo would be read from the "local" replica on that server. This however requires linked servers, replication .. seems to add a lot more complexity...... So my question is, is there a better way? How do big sites like Flickr, Facebook, Cyworld update the profile image on all posts when this image changes? It seems to happen instantly, and I am sure those guys have tonts of servers running their systems.Any ideas?ThanksSun, 04 May 2008 23:38:06 GMTFin_777http://www.sqlservercentral.com/Forums/Topic495353-361-1.aspxHi,Hope this is the right forum for this. Just wanted to see if anyone had done this. has anyone used a witness server to witness multiple SQL 2005 mirrored instances? So I have different DBs mirrored on different mirrored instances but just the common witnes server.Any issues with performance? High Availability?Thanks in advance.ScottMon, 05 May 2008 17:28:25 GMTMossyhttp://www.sqlservercentral.com/Forums/Topic493525-361-1.aspxWe have been using the following SQL Encryption on one database table in SQLExpress2005. Originally our application would only use the installed db server to run our db. Now we're considering allowing users to install our database on thier own SQL servers if they don't want to download and use SQLExpress2005..Q. Will our 'MASTER KEY ENCRYPTION', CERTIFICATE or SYMMETRIC KEY corrupt the users existing databases or db server?Q. How will installing our db on their db server effect master user name and password?[Sample Encryption]CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'secretpassword;CREATE CERTIFICATE MyCert WITH SUBJECT = 'MyDatabaseAccess', EXPIRY_DATE = '12/31/9999' ;CREATE SYMMETRIC KEY MySymKey WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE MyCert ;Zone: MS SQL ServerThu, 01 May 2008 06:44:27 GMTprohttp://www.sqlservercentral.com/Forums/Topic493203-361-1.aspxSo I've read a little bit about the DB option READ_COMMITTED_SNAPSHOT and found reasons I should try it out, but nothing about negative consequences if I do impliment it. Is there any reason that I wouldn't want this option on (performance or otherwise)? ThanksWed, 30 Apr 2008 12:58:52 GMTBrent Burgesshttp://www.sqlservercentral.com/Forums/Topic491054-361-1.aspxWe have regular needs to develop small, simple , web-based data access forms for our intranet users. We don't have enough C# or Vb.Net development resource to meet these demands, so would like a quick means of deploying this sort of solution which doesn't require a high level of development expertise. Can anyone recommend a good solution?Sat, 26 Apr 2008 07:33:01 GMTsqlservercentralhttp://www.sqlservercentral.com/Forums/Topic490670-361-1.aspxI'm creating a database(s) that will store our employees information. From personal information, hardware they have checked out to surveys HR requires them to take. I would like to keep their personal information private and locked down, while surveys and hardware would be a little more public. My initial thought would be to have a database containing their personal private information. Then having a database for the other information that would link back to the private data to retrieve their name via their employee id assigned in the private database.Is this something practiced or do people usually just put it all into 1 database?Fri, 25 Apr 2008 07:52:19 GMTgrtn316http://www.sqlservercentral.com/Forums/Topic491602-361-1.aspxI use VBScript to manage files (such as backups, archives, etc) through SQL Server Jobs using the ActiveX option. I am wondering if anyone knows how I can pass T-SQL variables, or table data into the VBScript to access. I need this for file naming purposes.ThanksMon, 28 Apr 2008 10:58:26 GMTgpriesterhttp://www.sqlservercentral.com/Forums/Topic487646-361-1.aspxHi All I got a SQL server, that has been running normally, a few days back , i have made one job run 3 times more than it runs usual per minute, earlier the job runs once in every 3 minutes, and takes 30 secs to run, now it runs each minute taking the same time, but now the server seems to max its CPU and running out of resources, how do i check that how i can improve this server performance, what are the measures i have to take into account, how do i do it, i dont want to simply increase the server configuration, i want to do an analysis, how much the server can take, how can i do it?? please do drop a wordCheersSujiSun, 20 Apr 2008 11:01:41 GMTSujihttp://www.sqlservercentral.com/Forums/Topic483604-361-1.aspxIt looks like we might be facing a large project shifting over to using ORM methods through nHibernate. I'm trying to get a read from the database community on what exactly I should expect in terms of issues, challenges and headaches during the development process. I'm also interested in any long term maintenance issues, troubleshooting problems, etc. If your developers implemented ORM all the way down to storing object data on the database in a non-normalized/object oriented fashion, how did that affect you? Did it muck up reporting? What benefits did you realize on the database side of the house?I'm really looking for real-world, hands on information. Complaints or speculation about how stupid a lowest common denominator set of dynamic queries might be... well, I've got that complaint well in hand. I need as much hard data as I can collect so that I communicate enough information to my boss, his boss and his boss in order for them to make informed decisions about this and go into it with their eyes fully pinned open.Any and all help in this area would be appreciated.Fri, 11 Apr 2008 06:33:03 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic486011-361-1.aspxBackground: Using SQL Server 2005, SP2. Problem is occuring on a development database with no user traffic or concurrency related issues (including replication).I have successfully established an indexed view, each row of which will have a uniqueidentifier from a base table which will serve as the unique clustered index.Once the view is created, and in order to establish other indices, I am first trying to create the required unique clustered index.The problem is that this query is taking well over an hour (we end up cancelling the query) and it eventually consumes the larger part of a 60 GB drive. There are only 140,000 rows in the base table upon which the index is created ( no cross, full or outer joins present ). The database is reasonably small to begin with.Here is the DDL which we use to create the view successfully:================================================--Set the options to support indexed views.SET NUMERIC_ROUNDABORT OFF GO SET ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS ONGOif exists ( select * from information_schema.Views where table_name='ivTreatmentProcedure' ) drop view ivTreatmentProceduregocreate view [dbo].ivTreatmentProcedure with schemabindingas select tvp.txVisitProcID, tvp.txVisitProcDirMin, tvp.procCodeID, tv.txVisitID, t.txID, t.txDt, t.txLockInd, ds.facilityID, ds.discStayID, ds.disciplineMLID, 'disciplineShortDescTxt' = dml.masterLookupShortDescTxt, 'disciplineLongDescTxt' = dml.masterLookupDescTxt, 'disciplineSeqNum' = dml.masterLookupSeqNum, ds.discStayAdmitDt, ds.discStayDischargeDt from dbo.dwTreatment tvp join dbo.TreatmentVisit tv on tv.txVisitID = tv.txVisitID join dbo.Treatment t on t.txID = tv.txID join dbo.DisciplineStay ds on t.discStayID = ds.discStayID join dbo.MasterLookup dml on dml.masterLookupID = ds.disciplineMLIDgo-- Notes: xxxID columns are generally uniqueidentifiers in the above base tables. The from clause-- queries only base tables itself.-- This CREATE VIEW statement works very quickly. Here is the query which takes a long time and-- becomes a disk hog:create unique clustered index PK_ivTreatmentProcedure on ivTreatmentProcedure (txVisitProcID)go-- Note: txVisitProcID is a uniqueidentifier and is the primary key of the base table which should have the same cardinality as the view (140,000 rows)================================================Any help would be appreciated. What could be causing the slow performance and database bloat?This CREATE UNIQUE CLUSTERED INDEX runs for over an hour and I don't see why it should be so slow and consume so much space. We are really in an isolated DBA-access only development database here.Wed, 16 Apr 2008 16:05:05 GMTjsaskeyhttp://www.sqlservercentral.com/Forums/Topic488662-361-1.aspxScenarioTable 1: small tall table with an ID_field and state, with clustered index on ID_fieldTable 2: Large wide table with id_field and many columns, with index on ID_field (cannot be cluster indexes because of size).Each table has the same number of records and same set of ID_fields. Question:When I join the two tables via a SELECT INTO statement is it possible to have the records in the new table be sorted according to the clustered index on table 1?I wonder whether it matters when the join condition is:--> from Table1 a left join Table2 b on a.ID_field = b.id_field--> from Table2 b left join Table1 a on b.ID_field = a.id_fieldThanks!Tue, 22 Apr 2008 08:27:17 GMTPieterhttp://www.sqlservercentral.com/Forums/Topic488514-361-1.aspxI am generating a output for crystal report through procedure.The report is of varying length and should be printed in one or two pages.The one criteria is that the first page should contain only 8 lines any character after this should shift to next page.All this characters will be stored in a single variable.Depending upon the selected criteria in the front end the my first three lines will be printed.Then rest of the lines will be printed and this will be upto 8 lines in first page and rest in next page. I am unable to print the first 8 lines in the first page because of the first three lines. If the criteria is not selected then one or two lines will be notpresent in the report. How can this be done?Tue, 22 Apr 2008 05:50:12 GMTAshwin M Nhttp://www.sqlservercentral.com/Forums/Topic484329-361-1.aspxI have SQL2005ent on 2 node cluster.Both mdf and ldf files are on one LUN.Storage is working very slow, i need to change database location from one lun(raid 5) to another (raid 10).What can you suggest, with minimal downtime?Mon, 14 Apr 2008 06:10:33 GMTmisko37http://www.sqlservercentral.com/Forums/Topic485345-361-1.aspxHi all,I just started at this company and I've been tasked with monitoring and coming up with recommendations for a dedicated SQL Server 2005 machine, used by less than 50 users and not a lot of transactions. It's for a mission-critical financial application package from Microsoft. I'm thinking HP ProLiant DL380 G5.2 x Quad-Core Xeon 3.16 GHz, 1333 MHz FSB10GB RAM2x72GBx15K RPM - RAID 1 (OS)2x72GBx15K RPM - RAID 1 (Tempdb)2x146GBx10K RPM - RAID 1 (.MDF)2x146GBx10K RPM - RAID 1 (.LDF)Has anyone done something similar to this, and/or is there a different configuration recommendation for improved performance?Thanks in advance.Tue, 15 Apr 2008 16:48:17 GMTGary2004http://www.sqlservercentral.com/Forums/Topic486790-361-1.aspxwe are in testing phase with our new two node sql cluster and are trying to understand how it will re-act under different circumstances. our test web application, is attached to this cluster while having IIS hold the session state.one of the issues we have found is when we induce fail over from the cluster administrator, you are kicked out of the web application and are forced to re-login.we are trying to determine if this is a setting in the cluster or if the developers need to code the application differently to handle sql connections. any thoughts on this?thanks for all help in advance -ryanThu, 17 Apr 2008 14:36:17 GMTryan strawberryhttp://www.sqlservercentral.com/Forums/Topic486206-361-1.aspxDoes anyone have a template for what they consider is the perfect update stored proc? Say for a fictitious customer table?Would love to see how the experts do it.Thu, 17 Apr 2008 02:37:13 GMTmatthewhttp://www.sqlservercentral.com/Forums/Topic486269-361-1.aspxI'm trying to put together my first SSAS cube. I have successfully developed a Time Dimension for dates and I'm not sure where to go with the next dimension.We are doing a lot of reporting on a specific measure, which is held in minutes and fractions of minutes. The source column is defined as numeric (18,2). At the detail level the fractions should remain, but the categories that currently dimension the measure are 5 Weeks, or negative. These are the focus for my report, but there may be other categories.Based on my limited understanding of cube design, and an obsessive need to do things properly, it feels as if I should have a dimension with a row for every minute. The size of this dimension could then be practically infinite. At present, the minimum no of minutes is 0 and the maximum is 1,037,633.Would it be valid to build the fact table with the original count of minutes and a second column dividing it into hours? That way, the hour could be the key and I could maintain 2 years of hours in 17.5k rows, or 35k if I allow 2 years negative as well.Is this valid design? Does anyone have a better idea? Can anyone point me in the direction of good theory on cube design?TIAB2BThu, 17 Apr 2008 04:40:27 GMTborn2bongohttp://www.sqlservercentral.com/Forums/Topic484698-361-1.aspxI've been tasked with performing auditing of "sysadmin" activity. I'm wondering how many of you might be doing this kind of auditing. If so what kind of problems/hurdles might you have run into in deploying an auditing system, and what tools (home grown and/or 3rd party) might you be using to do your auditing? Here are my current audit requirements:Scope: The scope of these Audit Trail requirements applies to data that has been identified as confidential data that resides on production SQL Server machines that is access by any account that is a member of the sysadmin fixed server role. Physical Audit Trail:A physical electronic audit trail file or method to produce a physical file that can be queried and/or browsed when needed must be available for each SQL Server instance that contains confidential data. The electronic components (log files, programs, etc) necessary to produce an audit trail must be maintained and readily available at least 30 days and must available via and archive for at least 1 year. Audit Requirements: Each audit trail record will the contain WindowS LoginID (Windows account or SQL Server Login), the date and time of event, plus the type of action performed (SELECT, INSERT, UPDATE, DELETE, GRANT, REVOKE, EXEC, etc) and whether the action was successful, or unsuccessful. Auditing will happen continuously, while a SQL Server instance is up and running, because a complete set of audit trail records can not be collected if auditing is only done periodically. Audit only Login and Logoff attempts for logins that are a member of the sysadmin server role will be audited. Audit all SELECT, UPDATE, INSERT and DELETE commands against all tables that are identified to containing confidential data must be logged when the command is issued by a login that is a member of the sysadmin server role. Audit all SQL Server commands performed by any LoginID (Windows Account or SQL Server Login) that has sysadmin rights. Mon, 14 Apr 2008 16:45:26 GMTG. Larsenhttp://www.sqlservercentral.com/Forums/Topic483881-361-1.aspxi have a table like[b]table1[/b][b]aa bb cc dd[/b]1 2 3 45 6 7 8i need to convert columns in to rows...............( to use in reporting)table2[b]aa[/b] 1 5[b]bb[/b] 2 6[b]cc [/b] 3 7[b]dd [/b] 4 8and the number of columns/rows are not fixed..........as this [b]table1[/b] is present in cursor.......for each iteration the number of columns may change........................thanks in advanceFri, 11 Apr 2008 11:48:39 GMTchinnihttp://www.sqlservercentral.com/Forums/Topic483812-361-1.aspxHi,Basically, we have a 24/7 database on the MS SQL 2005 cluster. Last week, the cluster failed due to the NIC fault. The service was stopped for almost 4 hours. The penalty is heavy. My manager want to add another layer to ensure the availability of service, such as logging data mirror. I just want to check whether anyone here has adapted such technology and could share some experience.Fri, 11 Apr 2008 09:45:45 GMTL Xuhttp://www.sqlservercentral.com/Forums/Topic483173-361-1.aspxHello ,While designing the Data warehouse , i am importing data primarily from two transaction systems A and B.Now there are tables in A and B which have essentially the functionality, however because of the design there are records (which are unique) in tables of A and B which have the same id.Inserting all of this in the same table(for example one customer table) means that two separate customers will have the same id.I know i can create a surrogate key and this would help identify uniquely each customer. However to join across multiple tables(or the fact table) would make things slightly more complicated.Realistically there is a workaround, by creating columns that identify the system and so on, but would that be good DB design?Thu, 10 Apr 2008 10:44:32 GMTThe underdog!http://www.sqlservercentral.com/Forums/Topic483861-361-1.aspxI have been very happy with Database Mail in SQL Server 2005. It is SO much better than the old SQL Mail. However, I'm running into a problem with attachments that I'm hoping someone can help with. Our production environment has two servers: a DB server running SQL Server 2005 and an application server running Windows Server 2003/IIS 6. I want to use the Database Mail on the DB server to send emails from the application (which is in classic ASP). Everything works perfectly until I try to attach a file that sits on the app server, and I receive the following error:Msg 22051, Level 16, State 1, Line 0Attachment file [file name] is invalid.I've read everything I can find about this error, and I'm 100% sure the path is correct. According to my network guy, the proper users have the proper permissions. After talking to him, I think the problem may be the network configuration. The two servers are in the same subnet but not the same domain. Has anyone run into this? Is there a way to make DB mail work in this situation?Thanks in advance for any tips you can give me.JeannineFri, 11 Apr 2008 10:56:32 GMTjeschumm