Hi all.
We are currently running SQL7 on an NT4 server (dual 800Mhz, 1GB RAM) and it is being pounded mercilessly 24/7!
We are currently in the market to upgrade, and I would like to get your opinions on this setup. Maybe some has experience with this box, or other issues in upgrading to a new OS and new version of SQL...
Box:
Compaq Proliant ML530
Processors:
2 Xeon 2.8GHz/512KB with 400Mhz System Bus
Hi, I have probably exhusted the topic of shapes etc... but I am still having a hard time determining the best solution for my problem:
I have several products, each with several specific properties:
Double Tee ----------------------------------------- Width | Height |Flange | Leg | Count
Column ------------------------ Width | Height
Round Column ----------------- Radius
Now originally I wanted to create a scalable table structure, so with the help of several people on this site (and SQL Team) I have developed the following : tbShape ------------------ ShapeID | Shape | XSectionFormula ------------------------------------------- 1 | Rect | Length X Width
From the above table structure I was able to select a product and by obtaining the formula from the tbShape table, using a cursor, replacing the Attribute names in the formula with the attribute values from the tbProductAttributeValues table, using dynamic SQL, I am able to determine the cross section of any selected product.
The Problem now is, what if I need to apply different functions to the data for any given product. This proves to be very difficult because the attributes for the product are not necessarily consistent.
For Example, lets say the above was a slab 10 feet by 1 foot giving a cross section of 10 square feet. Because it is simple to get the cross sectional area, I can easily figure out the cubic feet of concrete used by multiplying the cross section by a length. But lets say the user want to get the cost / square foot? How is the application sure what attribute is the width of the product?
I guess what I am getting at is why the structure below is not any better then the one above?
Now there would be a 1 - 1 relationship between the tbTemplates and tbDoubleTeeTemplates ON TemplateID - fkTemplateID. To add a new product, simple add the category, the new table, and then alter the Stored Procs which would use if() if else() statements based on the category to go to the appropriate template table.
Also, now I can write any customized functions for any product without the worry of user mispelling an attribute between the formula and attributes, etc...
Any opinions, thoughts on this would be appreciated!
I've always used the identity field in SQL server to maintain the unique id for a table. With the new DB design at work we brought in a dba and she made us move away from allowing SQL maintain the unique field and having us maintain the unique field in code. To do that we had to start a transaction, do a select max(id) + 1, insert into table, commit transaction. Doing it this way, I'm starting to see deadlocks due to the transactions locking the table.
Getting down to what I wanted to know, what is the pro's/con's you guys see in maintaining he unique ID this way and is there a better way of creating an unique id in T-SQL code?
I took a search through the archives for related topics (and got Des in trouble along the way :( ) but couldn't find a directly related thread. If I missed one, feel free to tell me where to go (hey...watch that...only if I MISSED one!)
I wrote what is, essentially, a data verification stored proc that goes out to each of FOUR servers we have - each one running a mirror database. In a nutshell, there is one table that contains a row with a column in it that, if everything has gone well in the daily processing in all 4 databases, will match identically between all 4 DBs.
So, that said, here is the output: Job 'Index - Verify PortfolioIndex Across Servers' : Step 1, 'PortfolioIndex Check across all servers and portfolios' : Began Executing 2004-11-09 15:30:00
------------------- BEGINNING PortfolioIndex VERIFICATION -------------------- [SQLSTATE 01000] WHOO-HOO!!! EVERYTHING MATCHES for porfolio number 2 on 11/09/2004! [SQLSTATE 01000] WHOO-HOO!!! EVERYTHING MATCHES for porfolio number 3 on 11/09/2004! [SQLSTATE 01000] WHOO-HOO!!! EVERYTHING MATCHES for porfolio number 11 on 11/09/2004! [SQLSTATE 01000] WHOO-HOO!!! EVERYTHING MATCHES for porfolio number 67 on 11/09/2004! [SQLSTATE 01000] WHOO-HOO!!! EVERYTHING MATCHES for porfolio number 72 on 11/09/2004! [SQLSTATE 01000] WHOO-HOO!!! EVERYTHING MATCHES for porfolio number 84 on 11/09/2004! [SQLSTATE 01000] WHOO-HOO!!! EVERYTHING MATCHES for porfolio number 90 on 11/09/2004! [SQLSTATE 01000] WHOO-HOO!!! EVERYTHING MATCHES for porfolio number 92 on 11/09/2004! [SQLSTATE 01000] WHOO-HOO!!! EVERYTHING MATCHES for porfolio number 100 on 11/09/2004! [SQLSTATE 01000] WHOO-HOO!!! EVERYTHING MATCHES for porfolio number 105 on 11/09/2004! [SQLSTATE 01000] WHOO-HOO!!! EVERYTHING MATCHES for porfolio number 110 on 11/09/2004! [SQLSTATE 01000] WHOO-HOO!!! EVERYTHING MATCHES for porfolio number 115 on 11/09/2004! [SQLSTATE 01000] WHOO-HOO!!! EVERYTHING MATCHES for porfolio number 120 on 11/09/2004! [SQLSTATE 01000] WHOO-HOO!!! EVERYTHING MATCHES for porfolio number 125 on 11/09/2004! [SQLSTATE 01000] WHOO-HOO!!! EVERYTHING MATCHES for porfolio number 130 on 11/09/2004! [SQLSTATE 01000] WHOO-HOO!!! EVERYTHING MATCHES for porfolio number 135 on 11/09/2004! [SQLSTATE 01000] WHOO-HOO!!! EVERYTHING MATCHES for porfolio number 140 on 11/09/2004! [SQLSTATE 01000] WHOO-HOO!!! EVERYTHING MATCHES for porfolio number 145 on 11/09/2004! [SQLSTATE 01000] WHOO-HOO!!! EVERYTHING MATCHES for porfolio number 150 on 11/09/2004! [SQLSTATE 01000] WHOO-HOO!!! EVERYTHING MATCHES for porfolio number 155 on 11/09/2004! [SQLSTATE 01000] UH-OH - TROUBLE!!! CloseIndex mismatch for porfolio number 160 on 11/09/2004! [SQLSTATE 01000] --> Server TA1 shows an index of 110.582 [SQLSTATE 01000] --> Server TRADEANALYSIS shows an index of 110.582 [SQLSTATE 01000] --> Server RECEIVE1 shows an index of NULL [SQLSTATE 01000] --> Server RECEIVE2 shows an index of NULL [SQLSTATE 01000] UH-OH - TROUBLE!!! CloseIndex mismatch for porfolio number 1000 on 11/09/2004! [SQLSTATE 01000] --> Server TA1 shows an index of 189.623 [SQLSTATE 01000] --> Server TRADEANALYSIS shows an index of 189.623 [SQLSTATE 01000] --> Server RECEIVE1 shows an index of NULL [SQLSTATE 01000] --> Server RECEIVE2 shows an index of NULL [SQLSTATE 01000] UH-OH - TROUBLE!!! CloseIndex mismatch for porfolio number 1001 on 11/09/2004! [SQLSTATE 01000] --> Server TA1 shows an index of 164.058 [SQLSTATE 01000] --> Server TRADEANALYSIS shows an index of 164.058 [SQLSTATE 01000] --> Server RECEIVE1 shows an index of NULL [SQLSTATE 01000] --> Server RECEIVE2 shows an index of NULL [SQLSTATE 01000] UH-OH - TROUBLE!!! CloseIndex mismatch for porfolio number 1002 on 11/09/2004! [SQLSTATE 01000] --> Server TA1 shows an index of 255.978 [SQLSTATE 01000] --> Server TRADEANALYSIS shows an index of 255.978 [SQLSTATE 01000] --> Server RECEIVE1 shows an index of NULL [SQLSTATE 01000] --> Server RECEIVE2 shows an index of NULL [SQLSTATE 01000] UH-OH - TROUBLE!!! CloseIndex mismatch for porfolio number 1003 on 11/09/2004! [SQLSTATE 01000] --> Server TA1 shows an index of 159.009 [SQLSTATE 01000] --> Server TRADEANALYSIS shows an index of 159.009 [SQLSTATE 01000] --> Server RECEIVE1 shows an index of NULL [SQLSTATE 01000] --> Server RECEIVE2 shows an index of NULL [SQLSTATE 01000] UH-OH - TROUBLE!!! CloseIndex mismatch for porfolio number 1004 on 11/09/2004! [SQLSTATE 01000] --> Server TA1 shows an index of 318.981 [SQLSTATE 01000] --> Server TRADEANALYSIS shows an index of 318.981 [SQLSTATE 01000] --> Server RECEIVE1 shows an index of NULL [SQLSTATE 01000] --> Server RECEIVE2 shows an index of NULL [SQLSTATE 01000] UH-OH - TROUBLE!!! CloseIndex mismatch for porfolio number 1005 on 11/09/2004! [SQLSTATE 01000] --> Server TA1 shows an index of 145.921 [SQLSTATE 01000] --> Server TRADEANALYSIS shows an index of 145.921 [SQLSTATE 01000] --> Server RECEIVE1 shows an index of NULL [SQLSTATE 01000] --> Server RECEIVE2 shows an index of NULL [SQLSTATE 01000] UH-OH - TROUBLE!!! CloseIndex mismatch for porfolio number 1006 on 11/09/2004! [SQLSTATE 01000] --> Server TA1 shows an index of 141.035 [SQLSTATE 01000] --> Server TRADEANALYSIS shows an index of 141.035 [SQLSTATE 01000] --> Server RECEIVE1 shows an index of NULL [SQLSTATE 01000] --> Server RECEIVE2 shows an index of NULL [SQLSTATE 01000] UH-OH - TROUBLE!!! CloseIndex mismatch for porfolio number 1007 on 11/09/2004! [SQLSTATE 01000] --> Server TA1 shows an index of NULL [SQLSTATE 01000] --> Server TRADEANALYSIS shows an index of NULL [SQLSTATE 01000] --> Server RECEIVE1 shows an index of NULL [SQLSTATE 01000] --> Server RECEIVE2 shows an index of NULL [SQLSTATE 01000] UH-OH - TROUBLE!!! CloseIndex mismatch for porfolio number 1008 on 11/09/2004! [SQLSTATE 01000] --> Server TA1 shows an index of 123.179 [SQLSTATE 01000] --> Server TRADEANALYSIS shows an index of 123.179 [SQLSTATE 01000] --> Server RECEIVE1 shows an index of NULL [SQLSTATE 01000] --> Server RECEIVE2 shows an index of NULL [SQLSTATE 01000] ------------------- COMPLETE -------------------- [SQLSTATE 01000]
This was cut-n-pasted here from a log file created by the actual SQL SERVER 2000 job created to run the afore-mentioned stored procedure.
After all that...my quandry is this:
What is the best way to send this info out in an email format to interested parties? Currently I have the job send out an email notification on completion, but that still requires my lazy buttocks to go look at the log file in the job (or, more accurately, on the server in the logfile directory).
I want to get the actual DATA as shown above into the email.
As I see it, my options are: (1) write the data out to a flat file during the run (or, as is done now, into a log file by the SQL Server scheduled job) and then attach that FILE to the email - this still requires my lazy buttocks to OPEN the attachment that comes with the email. ro (2) write the message out a line at a time to a table with an IDENTITY column (used to order them on the select) and a VARCHAR(128) column that each line in the log would be written to. This option allows me to just do a SELECT in the call to xp_sendmail to get the data into the actual email...but I just really hate the idea of creating a permanent table for this cheesy solution.
I tried it with a temp table within my stored proc, but of course, when I made the call to xp_Sendmail, it can't see my temp table in order to select from (mind you, it's not that I mind USING a cheesy table, just that I don't want it to have a lifespan longer than the time I need to use it and toss it aside)
I know the common denominator here is "My Lazy Buttocks", but I really can't understate the laziness of my buttocks, so this is a valid concern ;)
Any thoughts? How do people get status messages like this into an email without using an attachment or a cheesy middleman table?
Sorry, as always, about the miniseries...just trying to set the mood before popping the question ;)
I've a core component that is a Win32 DLL, this DLL implements some basic math calculations and conversions between several video systems.. PAL, NTSC and so on. Plus this DLL has a memory mapped file that stores a system value that is the current time of the day (House Clock), it is written by a service app and read by exernal apps that need this frame-accurate value.
I've the full control of the DLL and now I've to decide whether to use this DLL from SQLServer with P/Invoke or if it would be better to port this DLL to C#. Both solutions has pros and cons. (The DLL is written in delphi32 and cannot be easily ported)
- The most important pro is "code reuse"... sql implements the same math as applicstions and once the DLL is bug free, SQL math behaves the same as apps. No need to write code twice and so on.
- The most important con is about code security... not sure if an hard problem in the DLL may take down all the server, even if the situation is very unlikely to happen since the type of the DLL... but never to say...
I would like some opinions on how you would deal with the following scenario:
We probably have somewhere from 500 to 1000 reports (written in crystal). We also have about 120 clients; each client has their own database. One of the reasons for so many reports is because a lot of our customers want report A but with this or that extra column added so we end up with a lot of custom versions of one report for a particular client. My question is in converting over to Reporting Services, how would you setup the file structure?
Right now, we are thinking that every client is going to have their own folder which would contain all Reports, Models, and Datasources. What do you guys think?
Hearing complaints from users about speed on db server (I have almost no control on design) it just has to work. Ran profiler looking for all sql statements over 4000 millsec and in one hour returned over 715 tsql statements. Over 300 of these were over 10000 milliseconds. THis is on an 8 way Dell with 8 gig of RAM. Looking for opinions, how bad does this look compared to other servers you are taking care of? Cache hit ratio is at 99 % and system queue length still under 1, but this does not look good.
Hi all,I was wondering if I could get some experienced opinions on SQL hardware torun an ERP app on SQL 2000. The app does not yet support SQL 2005. The ERPapp has 25 users and likely won't exceed 30 users for several years. Alltraffic is on the LAN. The ERP clients basically submit SQL requests forreads and writes. The app makes heavy use of temp tables, temp views butnot many stored procedures. The current size of the db is 6GB and willlikely double in 4 years.Planned server:Windows Server 20034 GB RAMSQL 2000 Standard (ERP app does not yet support SQL 2005)RAID1 for OSRAID 10 for SQL dataRAID1 for SQL logsRAID1 for temp dbDual, teamed NICsI would try to get 15K SCSI drives. Any thoughts on SATA instead of SCSI?Could I expect much of an impovement by using SQL 2000 Enterprise since itcan use more RAM? I would rather wait for SQL 2005 to be supported.Does anyone have a Dell or HP server configured in an email-able cart thatthey would care to share?Thank you.
Anyone here tried ListCleaner by a company called WinPurehttp://www.winpure.co.uk/lists (a data deduping software) incomparison to other products that may be out there? Looks like somecompanies like Hewlett Packard use this.I am looking for a good (and inexpensive) datacleansing tool to dedupeand standardise lists, happy to extract the data from database andre-import it (which seems WinPure does) before incorporating it intoother BI tools. Tried MatchIt from help it systems but it is a bitcumbersome.Particularly interested in a tool with a good phonetic matchingengine, that handles multiple lists.Mostly work with oracle, sql and access.Recommendations appreciatated.dbdb
Looking for some insight from the professionals about how they handlerow inserts. Specifically single row inserts through a storedprocedure versus bulk inserts.One argument are people who say all inserts (and updates and deletionsI guess) should go through stored procedures. The reasoning is thatthe developers that code the client side have no reason to understandHOW the data is stored, just that it is. Another problem is an insertthat deals with multiple tables. It would be very easy for thedeveloper to forget a step. That last point also applies to businesslogic. In my case, adding a security to our SecurityMaster can touch 1to 4 tables depending on the type of security. Also, certain fieldsare required while others are set to null for depending on the type.Because a stored procedure cannot be passed datasets but only scalarvalues, when you need to deal with multiple (i.e. bulk) rows you arestuck using cursors. This post is NOT about the pros and cons ofcursors. There are plenty of those on the boards (some of themprobably started by me and showing my understanding (or morecorrectly, lack of) of the way to do things). Stored procedures alsogive you the ability to abort and/or log inserts that cannot happenbecause of contraints and/or business rule failures.Another approach is to write code (not accessible from outside thedatabase) that handles bulk inserts. You would need to write in rulesto "extract" or "exclude" rows that do not match constraints orbusiness rules otherwise ALL the inserts would fail because of one badrow. I guess you could put the "potential" rows into a temp table.Apply your rules to the temp table and delete / move rows that wouldfail. Any rows left can that be bulk inserted. (You could also use therows that were moved to another temp table for logging why theyfailed.)So that leaves use with two possible ways to get data into the system.A single row based approach for client apps and a bulk based forinternal use. But that leaves use with another problem. You now havebusiness logic in TWO separate areas. You have to remember to modifycode or fix bugs in multiple locations.For those that are still reading my post, my question is...How do you handle this? What is the approach you take?
I have a client who wants to be able to upload images to his website for his customers to access. It will probably max out at 100 images a month...so not a huge amount of data. I am using asp.net 2.0 and SQL Server 2005. Does anyone have thoughts or opinions on why I should or should not take this approach?
Before I started with this employer the server support staff had planned a backup strategy that included using database agents for Oracle and SQL Server. Nothing is really set up yet for SQL Server so I can still change this direction. Has anyone seen a definite benefit to using database backup agents? If so, what benefits have you seen? There doesn't seem to be much value added by paying for and using an additional product when the database's own utilities are so easy to use, and all the backup files can be backed up to tape with the basic backup software. I've not worked with it, though, so perhaps I am missing something. These are small databases so space is not an issue. Any opinions/comments are appreciated.
I'm wondering what other people do in regards to running hard drive defragmentation programs on SQL Server 2005 servers (assume 64-bit and Windows 2003). From what I can tell the most common opinions are:
1. Don't defragment because it doesn't help and it can cause problems. 2. Use Diskeeper 3. Use the built-in Windows defragmenter
Other respected defragmented programs are PerfectDisk, O&O Defrag, JkDefrag, and Contig.
Dear All, I am writing a procedure to import daily the customer excel file to SQL server 2000, I managed to do that where the excel file will be imported directly to the SQL server after creating the new data table, & then I need to read the created table & import it row by row to my original data table.The problem: I. The original excel file has the following:a. a protection passwordb. The contents has two merged headers (which effecting the import procedure)c. And last line is a totals line Before importing the file I have manually to remove (a – b & c)!! The Solution: II. I am trying to find a way to do the above points automatically inside the project. III. Also I thought of importing the excel file to a DataGrid first then:a. Let the user approve the file contents &b. Remove manually point (I.b.) above (I don’t now how yet, need to try it).c. Then import the DataGrid the the SQL server. I think I prefer solution (III), any suggestions are highly appreciated BR
Please help me decide what to do about my current hardware configuration. I have an ASP.NET app that uses SQL Server for the database. Currently both IIS and SQL are running on the same machine (see machine 1 below). I want to separate it so that IIS and SQL each have their own machine but I have a very limited hardware budget right now. I am trying to decide if it would be worth moving either IIS or SQL to another machine that we have, or if I would actually lose performance by doing so considering the extra machine I have is a bit outdated (see machine 2 below). Should I leave well-enough alone or try to split it to these 2 machines I have. (buying new machines aren't an option right now although that's what I'd like to do). I could probably afford a memory upgrade on one or both computers if necessary. Machine 1Dual XEON 1.8 Ghz w/ 1G RAM Machine 2P3 1.13 Ghz w/ 512K RAM Thanks
We have been told by the director over the DBAs that we may be standardizing ALL scheduled jobs and tasks (including SQL jobs) onto 1 tool called AutoMate (by NetworkAutomation), although I suspect the decision has already been made. I've argued that a standard for batch jobs is good but SQL has a job scheduler designed for SQL and integrated with SQL that works extremely well, but don't think I'm getting through. Has anyone used AutoMate as a replacement for SQLAgent? I am open to hearing both pros and cons please. Thank you.
I'm reviewing a data warehouse design schema for a client that is following Kimball's data warehousing principles. One of the first things I noticed was a table of dates with expanded columns giving such information as the year, month, month name, fiscal year, quarter, etc for each date, They also have a surrogate key (int) for the date value. The fact tables store the surrogate key rather than the date value itself. They were very surprised when I questioned the purpose of this table, assuring me that Kimball was very strong on the concept of having a date dimension for each table. I don't see the purpose of a table containing nothing by derived date formats. I think they will get a bigger performance hit from having to link through the surrogate key than they would suffer from having to convert datevalues stored in the fact tables. Has anybody else ever seen this before? Does Kimball really advise this?
Situation: SQL Server 2000. At my new employer they have a production database on one server and a copy of it that is set to read only on another server which is used for reporting.
#1 They have an SQL Server Agent job on the production server that: (2 times a day)
Backs up the production database Copies the backup file to a directory on the reporting server. (Its pretty big and can take time if there are problems with the LAN)
#2 They have an SQL Server Agent job on the Reporting server that: (scheduled to run 2 hours or so after the job on server 1 has run€¦they figured that it would be a safe bet that the backup and copy process of the first job would be done by then)
Breaks the user connections to the reporting database Performs a restore on the reporting database using the backup file that was copied to the holding directory by the production job. Sets some permissions for various users. Sets the reporting database to READ ONLY. What I would like to do is find a more efficient way to create this reporting database, I have started doing research into DTS methods but would like some opinions from more experienced users.
Need some opinions on how to query a set of tables designed like this:
Main table: Subscriber
Table has 80 columns, each column has a table which is written to via an update trigger everytime the column changes. Subscriber table is Type 1 dimension but the "Change" tables are Type 2 dimension. The "change" tables have 3 columns, SubscriberID, ChangeDate, NewValue.
The requirement is to push a Type 2'd version of the Subscriber table into a data warehouse for historical reporting. Meaning the users need to be able to see what a Subscriber looked like on an exact date.
Has anyone had any experience with this type of structure? If so, how did you join together the Subscriber table to all the change tables to extrapolate a "view" of the subscriber on each given day? You can assume that a record can only change once in a day, but not every column will change on the record.
I was thinking of something like this:
SELECT Subscriber.ID, (SELECT TOP 1 NewValue FROM Subscriber_FirstNameChanges b WHERE b.SubscriberID = Subscriber.ID AND b.ChangeDate <= @GivenDate ORDER BY b.ChangeDate DESC) FROM Subscriber
This would pull the most recent version of the subscribers FirstName based on a given date (I'd have to add in the 79 other change columns with almost identical sub queries). This would also take into consideration that there may not be change records for particular columns on particular dates because not all columns change at the same time/day and pull the most recent "version" of the subs FirstName since @GivenDate.
