Hello,
I've proposed to the head of IT at my organisation to head an database development export group to:
- Provide assistance in improving the performance of existing relational databases
- Provide assistance for the development of new database, e.g. correct construction of indexes; data contention, etc.
- Creation of database development standards
- Enforce the above standards for new databases so that the database is not deployed to production servers until it passes the standards.
I'd really like this to be a success as it should improve our rather crappy databases, basically because the people currently creating the databases don't know much about relational design.
Can anyone please post their experiences of setting something like this up, or working on such an expert group where they work? I'd really like to hear the good and bad experiences and what and (what not) to avoid.
Write the following queries in SQL: 1. Find the pname of parts for which there is some supplier. 2. Find the snames of suppliers who supply every part. 3. Find the snames of suppliers who supply every red part. 4. Find the pnames of parts supplied by Acme Widget Suppliers and no one else. 5. Find the sids of suppliers who charge more for some part than the average cost of that part. 6. For each part, find the sname of the supplier who charges the most for that part. 7. Find the sids of suppliers who supply only red parts. 8. Find the sids of suppliers who supply a red part and a green part. 9. Find the sids of suppliers who supply a red part or a green part. 10. For every supplier that only supplies green parts print the name of the supplier and the total number of parts that she supplies. 11. For every supplier that supplies a green part and a red part, print the name and price of the most expensive part that she supplies.
Beside its an opensource and supported by community, what's the fundamentaldifferences between PostgreSQL and those high-price commercial database (andsome are bloated such as Oracle) from software giant such as Microsoft SQLServer, Oracle, and Sybase?Is PostgreSQL reliable enough to be used for high-end commercialapplication? Thanks
Obviously Excel is the tool of choice for most people but with it's limited ability to leverage RAM (32 bit) and it's limitation with rows at just over 1 million what other choices do we have for viewing data?
My bosses boss created several OLAP universes and they seems to fly a lot fast than regular relational database. This still doesn't work with the fact the data can't be worked with unless you have a strong front end that can handle processing all those rows.
We're looking at using SSRS for all our new reporting requirements. However, we use SQLServer(2000 and 2005), Ingres and Postgres DBMSs. Ideally, we'd like to use SSRS against databases within all 3 DBMS. Ingres can be accessed via ODBC, JDBC, and has a .NET data provider, but no oledb data provider. Postgres can be accessed via ODBC. Reading some of the threads on this forum, there appears to be some limitations on the features of SSRS that can be used when accessing a database via ODBC compared to OLEDB. I'd like to understand these limitations if they exist so that we can work round them. Could anyone point me in the direction of a document that covers this issue, or outline what the issues are.
I am using SQL server 2000. While using query analyzer I am facing problem. If a query has group by clause and if that query is not fetching any record (i.e. query is returning nothing), then in this situation, I want zero to be displayed where datatype of field is integer and "-" if datatype of field is varchar.
Please give me solution as soon as possible, a kind request.
Facing problem for the below mentioned query:-
select IsD.ItemCode, case when sum(IsD.IssuedQty) is null then 0 else sum(IsD.IssuedQty) end as IssuedToday from Inv_IssueMaster IsM, Inv_IssueDetail IsD where IsM.IssueNo=IsD.IssueNo group by IsD.ItemCode
In the above mentioned query, datatype of IssuedQty is int and for ItemCode it's varchar.
Hi all,I'm trying to create a layout of our website for Marketing to review,and though I know how I want it presented, I'm not sure how to writethe SQL code to make it work. Here's a sample of the View I've writtenwith all our content:Level Title ID ParentID1 Clinics 11 Services 22 Surgery 1 22 ER 2 22 Radiology 3 22 Clinic 1 4 12 Clinic 2 5 12 Clinic 3 6 13 Heart 1 13 Lung 2 13 Physicians 3 43 Physicians 4 53 Physicians 5 6And the output would basically be this:Clinics- Clinic 1-- Physicians- Clinic 2-- Physicians- Clinic 3-- PhysiciansServices- Surgery-- Heart-- Lung- ER- RadilogyIs there anyway to do this in a SQL statement, or maybe Crystal orAccess? I even tried a Pivot table in Excel, but it wants to sumstuff, and I'm not working with numbers (sums, counts, etc). Alsosince I'm not using any aggrate functions MS SQL is complaining when iuse Group By.Thanks for any suggestions. Also though the layout is similar, theitems listed above are pulled outta the air. Hopefully I typed upsomething that makes since :)Alex
I have a table that I want to summarize the amount column and have it show the sub totals for the employee for each pay period. For example:
EMPLOYEE PerEndDate EarnCode Amt A 8/1/2007 Hourly 100 A 8/1/2007 Peices 250 B 8/1/2007 Hourly 75 B 8/1/2007 Pieces 300 A 9/1/2007 Hourly 50 A 9/1/2007 Pieces 200 B 9/1/2007 Hourly 100 B 9/1/2007 Pieces 200
What I want to show is
Employee PerEndDate Amt A 8/1/2007 350 A 9/1/2007 250 B 8/1/2007 375 B 9/1/2007 300
I'm using:
SELECT PayPeriodNo, EMPLOYEE AS PayEmpNo, MIN(PerEndDate) AS PerEndDate, SUM(AMT) AS Amt FROM dbo._vPayroll GROUP BY PayPeriodNo, EMPLOYEE ORDER BY PayPeriodNo, PayEmpNo
And the quantity is showind grand totals for each instance
Employee PerEndDate Amt A 8/1/2007 600 A 9/1/2007 600 B 8/1/2007 675 B 9/1/2007 675
The requirement is to customize database admin activities by creating new user group.
Need to create a group of user / dbauser1 which will have restriction in seeing the data but they should be able to alter database - add / remove the data file , increase or decrease the data file space when required.
This requirement came we wanted to create a new dba group they should not be able to any user data / any table but increase / decrease / add / modify space etc.
Thanks for looking at this for me. Here is the SQL Script as it stands so far. UPDATE TableImportHistorySET ImportStopTime = CONVERT(numeric, GETDATE(), 108), Elapsetime = CONVERT(numeric,GETDATE(), 108) - CAST((SELECT TOP 1 ImportStartTime FROM TableImportHistory WHERE TableName = 'ARCHRG'ORDER BY DataImportID DESC) AS numeric), Successful = 1WHERE (DataImportID = (SELECT TOP 1 DataImportID AS LastID FROM TableImportHistory WHERE TableName = 'ARCHRG') ORDER BY DataImportID DESC)) The Elapetime update portion of this statement is where I'm getting stuck (if I take it out the statement works) I get the error "ERROR converting Data type NVARCHAR to Numeric" I've changed the data type in the Table from numric to varchar, nvarchar, decemial nothing works and not sure what I should set the Starttime and StopTime datatypes in the table to (any suggestions please) Elapsetime = CONVERT(numeric,GETDATE(), 108) - CAST((SELECT TOP 1 ImportStartTime FROM TableImportHistory WHERE TableName = 'ARCHRG'ORDER BY DataImportID DESC) AS numeric) Are there better ways to do this statement?
...we coded round most of the issues and deadlocks and things seemed to improve for quite a while but we have recently run into performance problems again of late.
The CPUs on our SQL box often thrash away at 100% and our ColdFusion website has begun running painfully slow.
Our developers/hosts have suggested we might need to look for a 3rd party SQL guru who could look at the SQL box, do some tracing etc. and perhaps make some recomendations around optimising the DB architecture or changing the way we run certain queries. At the moment we have only gut instinct on which bits of the DB are likely to be being hit most hard.
Our website has grown from being very small to being really quite busy and it's possible we are running into shortcomings with the design of the DB that need to be tackled before we can expand further.
I'm not sure what the protocol is (I see there is a Jobs part of the site) but I wondered about the possibility of getting one of you guys in for a short while to review our server and database, for a fee of course. I'm not sure how long it would take to review that kind of data and get a feel for the usage?
We are based in the UK and whilst I guess it could be done remotely it might be easiest if the person was UK based too.
I'm as much interested in feedback about the idea (it might be not workable/a good idea for example) as I am to people offering their services.
Don't know if this is the appropriate forum. I am looking for an experienced SS consultant to review our setup, hardware architecture, recovery plan, and to provide high-level advice moving forward. My company is a CRM hosted software provider with a dynamic, metadata-based product built in Visual Studio 2005. Currently we run on SS 2000, but plan to migrate to SS 2005 or 2008. We anticipate quite a bit of growth and want to make sure that we are on the right path. Let me know if you are interested or know someone who is.
This is my 1st attempt at designing a database, and I have not finished it completely, but I would like somebody to review and critique for me. I really don't want to make any mistakes with this and I would appreciate any expertise out there to direct, recommend, suggest improvements and/or changes, PLEASE.
Thank you for considering this,,,if you provide me with your e-mail, I can send you a script.
First of all, this is my initial thread here on dbforums. I come from the land of Broadband Reports and would like to say, Hello fellow DB enthusiasts. :)
I'm not a novice to relational databases (Access MDBs), but new to implementing a db via SQL SERVER (2000 in this case) and using Access Data Projects.
Sample Data would be 1 | CRYS | CRYSTAL STAIRS 2 | MAOF | MEXICAN AMERICAN FOUNDATION 3 | PATH | PATHWAYS 4 | CCRC | CHILD CARE RESOURCE CENTER 5 | CHSC | CHILDREN'S HOME SOCIETY OF CALIFORNIA ==========================================
THE REMAINING "FKs" FROM SCHOOL ARE SIMILAR, as is other tables and their relationships. The design of the foreign keys were made using sql and the keyword "REFERENCES" and "FOREIGN KEY."
My questions are: :confused: (1) Is the use of FK as a Constraint any different than using an INDEX and how? (2) Should I Alter the Tables to include CASCADING Up/Down? (3) Are the use of CHARs Ok for the Keys? (4) Have I over/under-normalized any of the relationships?
is there a way using sqlexpress to review the most recent set of sql commandssubmitted to a database?I remember something about setting up logging in sql server 2000, but don'tsee much about this on google or the news group for 2005.thanksJeff Kish
I have an automation project to review the software. The software is using MSVS2012 and uses DBs from MSSQL2012. Is it possible to restore the DB files on my local laptop to review the code completed thus far? If so what other software would I need besides MSQLserver 2012 on my local laptop. I'm able to use MSSQL server 2012 on my local laptop, but the DBs are on servers.
Here is a script I wrote that analyzes datasets and returns all the minimal composite and unary keys that uniquely identify records. I wrote it because I frequently have to analyze client spreadsheets and non-normalized data tables.
On my desktop server it took about two minutes to analyze 2000 permutations of a table with 50 columns and 5000 records.
Please try it out for me and let me know if it chokes on anything, or if you see any ways it could be improved!
My name is W. Curtis Preston, and I'm the author of "Backup & Recovery"(formerly "Unix Backup & Recovery") from O'Reilly & Associates.(http://www.oreilly.com/catalog/unixbr/ )We're updating the book for 2006 and adding a chapter on SQL Serverbackup and recovery. Someone has already written what appears to be astrong chapter. The problem is that I'm not qualified to review it,since I'm not that familiar with SQL ServerCan anyone in this group step up to the plate and review the SQL Serverchapter? You would be mentioned in the acknowledgments of the book andreceive an autographed copy of the book.We're looking for two tech reviewers.
I'm having 2 issues on my first "real" SSRS report. 1. How to do a print preview via the preview tab. For print rendering, I have no print preview button on the preview toolbar. Is this normal? MS documention said that there is a Print Preview button. (BOL May 2007 - Designing & Creating reports -> working with report designer -> debugging and Publishing reports (Print Preview)) This report will always be rendered via the printer and it is a "pain" to have to always print it to see changes. The print layout option comes up all black with nothing viewable.
2. How do I reset page numbers back to 1 for report groups? I see no option for this. My report group requires a new page and I want page numbering to start at 1 when the grouping changes.
Note: I'm running VS2005 via Vista (with VS2005 Vista fix installed). SQL2005 SP2 also installed.
Any advice on this will be appreciated. I've spent too much time already where this should be obvious.
I have a company table and a address table. I have created a linked table with two foreign keys from the company table and the address table respectively.
This set up allows me to apply more than one address per company which is fine. My problem is that I need a way to prevent a company from entering exactly the same address twice.
In the address table i have the following fields:
AddressKey - PK CompanyKey - FK Address 1 - 3 Town County Country PostCode
In the company table I have the following fields.
CompanyKey - PK FirstName Secondname
And finally in the link table I have the following:
CompanyKey - FK AddressKey - FK.
Now, if i entered the following into the address table Assuming that company id of 1 was already entered into the company table.
Dear All,How to store a separated company-calendar for each client-company (about500).Company-calendar: workingdays, non-workingdays, meetings, etc.Thanks,Filips
We deal with multiple vendors who provide us information via text/xml files. Vendor A may provide financial data, vendor b provides litigation data, vendor c provides ratings data. Our current structure has databases for each vendor with its own company table which basically makes all this data disconnected. Of course each vendor has its own proprietary company id to make records unique.
All of the data is based on companies so the grain of data would be at a company level. I would like to be able to link this information together by creating a dimensional model that has a single company table (DimCompany) and has facts populated based on the type of data we receive. Would this be the right sequence of events?
1. My initial load (historical) would have to look at all these data sources and create one company record in my DimCompany table. This table would then link to all other fact tables to provide a single view of company info. I would imagine this would have to be a fuzzy lookup since one company will be in all sources.
2. On subsequent loads (incremental) I would probably have to do a lookup of companies in the dimension via the proprietary code and add if the company wasn't there.
Any advice on tackling this issue would be greatly appreciated especially if SSIS was used in the process.
Not seeing the Review Data Type Mapping Screen in SQL Server Import and Export Wizard?
Is there only a certain version where that screen shows up?
I am trying to import data from an MS Access application to SQL Server and all of the connections are good, but some of the data isn't and if I let it migrate using this tool it crashes on the bad data and there is no data that migrates. The Review Data Type Mapping screen will allow me to bypass the records in error and load the rest. however, I can;t do that if I cannot see the screen.
I have a database that allows a client to have multiple addresses, could be a home, business, vacation, etc... I'm trying to deside if I should put the Company Name and Title fileds in the tblClientAddresses table or keep it with the tblClient table. We do mailings to clients and if the mail is going to a home address the company and title should be excluded. If I put them in the tblClients table I really have no way of knowing if we are mailing to a home or a company. If i put the company and title with the address it makes sence that this would solve the issue.
Are there any forseeable problems with only moving one of my company's departments SQL database server to sp2 and leaving the others on sp1? From an admin tools perspective? Our internet groupd would like to move on to sp3 and the others would like to wait?
My Name is Nick Soutter, I am the owner of a small game company, Aepox Games(We're in the middle of a name change from "Lamar Games"),www.lamargames.net.Our first commercial game, Andromeda Online (www.andromedaonline.net) isgoing into beta soon. It runs on an evaluation edition of SQL Server 2000(our intention is, when it launches, we earn the money to buy a copy beforethe evaluation expires).We have been testing Andromeda Online, and found that saves to the databasetake about 10 seconds (we were anticipating less than 1). We felt we needsomebody experienced in optimizing sql databases to help us optimize thedatabase, and get it running in the best method for our particularapplication.Our program accesses the database in Java, and people with understanding inhow to optimize java connections would be a tremendous help.My company is small, and we honestly cant afford much. Everybody on thisproject, from the sound guys to the graphic artist, has worked for 1/10 to1/100 of the value of the job. We're simply a starting company looking fordedicated people who are willing to work more for credit than money.We can offer credit on our website(http://www.andromedaonline.net/credits.html) to anybody who helps us, butlittle more (maybe $100, but we're very over budget, and in desperate needof help). Because of how we intend the game to run (with maybe 100-200concurrent games running online), a 10 second save time is simplyunacceptable.Anybody who would be willing to help us, please send a resume toJoin Bytes!. Experience would be nice, but not a requirement.We're looking for someone who can talk with our programmer about the typesof calls made to our SQL database, and then can log into the DB and optimizeit to run as fast as possible considering our specific needs.Thank you for your time.Nick SoutterAepox (Lamar) Games
I plan to use Visual Web Developer 2005 Express Edition create web application which access SQL database(Express Edition). Is there any license require for this 2 software if I use its in company? Please provide information to me. Thank you!
