First post here so hope somebody can be of some help.
I have a table of product information from manufacturers, which has a number of child tables etc. It's basically a collection of tables, related, that contain product information for around 100,000 products.
We also have a table with a list of products that are for sale. Of course, every row isn't going to match so I can't create a PK->FK relationship.
Does anybody have any ideas on a design solution? I'm pretty sure the solution is very simple and I've just overlooked it.
Ok, I'm doing a football database for fixtures and stuff. The problem I am having is that in a fixture, there is both a home, and an away team. The tables as a result are something like this:
It's not exactly like that, but you get the point. The question is, can I do a fixture query which results in one record per fixture, showing both teams details. The first in a hometeam field and the second in an away team field.
Fixture contains the details about the fixture like date and fixture id and has it been played
Team contains team info like team id, name, associated graphic
TeamFixture is the table which links the fixture to it's home and away team.
TeamFixture exists to prevent a many to many type relationship.
Make sense? Sorry if this turns out to be really easy, just can't get my head around it at the mo!
I would like to create a table called product. My objective is to get list of packages available for each product in data grid view column while selecting each product. Each product may have different packages type (eg:- Nos, CTN, OTR etc). Some product may have two packages and some for 3 packages etc. Quantity in each packages also may be differ ( for eg:- for some CTN may contain 12 nos or in other case 8 nos etc). Prices for each packages also will be different that also need to show. How to design the table..
Product name : Nestle milk | Rainbow milk packages : CTN,OTR, NOs |
CTN, NOs Price: 50,20,5 | 40,6
(Remarks for your reference):CTN=10nos, OTR=4 nos | CTN=8 Nos
Hi there, I foolishly did not allow enough characters for a char field in SQL Server 2000. Had to change it - now I can't get the table design to update when I insert or update a record. Recreated DataAdapter, detached table and re-attached, deleted connection... I'm running out of ideas - any suggestions. Thanks.
I'm designing an application that will be deployed to 500 users. It is very database intensive (selects, inserts, updates, deletes). One select statement could possibly have up to 20,000 records returned. My question is, where should I put the database? Is this a bad candidate to have the database on one remote server? Or should I install sql server express on each users desktop? Any design issues I should consider?
HiI am facing a problem in designing a database for my project.Please help I have hotel Information.The hotel allocates rooms for my company.This is done on weekly basis. Now suppose in first week of a year the number of rooms allocated to the company is 3,2ndweek =5,3rdWeek=5 and so on... So when i search based on a week i should have one result setIf i search based on MOnth i should have one result set.. in this way. So what fields i need to take in a database table so that when i search based on week /month/quarter/year i get different resultsets.
Hi,Still in the design process of a Windows 2003 web server with a SQL backend.Expecting to have about 2000 visitors a day accessing lists and searchqueries on a 200-300 MB db. This server will be collocated in a datacenter.I have a few scenarios that I would appreciate in getting somecomments/criticism on:Scenario 1:Box 1, SBS 2003 Premium (with SQL 2000), 2GB RAM, Raid 5 HDBased on the SBS faq, as long as visitors are not Windows authenticated, anadditional SQL Internet license is not required.My concern with this scenario is (correct me if I'm wrong) lack ofscalability and fail-over (in the future).Scenario 3:Box 1, Windows 2003 Standard Edition, SQL 2000 Standard Edition, 2GB RAM,Raid 5 HDDo I need a processor based SQL license since it's exposed to the Internetor is this the same case as in the SBS where an additional license is notrequired?Scenario 3:Box 1, Windows 2003 Web Edition, 1GB RAM, Raid 1 HDBox 2, Windows 2003 Standard Edition, SQL 2000 Standard Edition, 1GB RAM,Raid 5 HDWeb Edition faq states that I can not deploy SQL on it. I'm guessing I canstill connect to another server's SQL.Is this setup more secure, in the sense that the SQL is on a separate boxfrom the web server?All comments are very appreciated. If you have other suggestions too, pleaselet me know.Thank youGreg
I have developed an reporting solution for a customer with reporting services and using report builder. When he connect to http://localhost/reports , he see the page like
I am very new to SQL Server 2005. I have a database of criminal records consisting of a master table of names where the primary key is a case number and 3 related minor tables: violations, charges, and appeals. They are linked by the case number with a one-to-many relationship. Not all of these tables have a record for every master record.
I am designing a simple lookup program in VB 2005 in which info from all tables will be on one screen. A search will be done on either name or case number.
What is the best way so set up views and/or stored procs for such a program? Do I need a stored proc for each minor table or is there a way to set up one proc to pull all of the info? Did I mention I was new at this? I have worked a lot with Access and recognize just enough things in SQL Server to be really confused.
I want to be able to implement infinite levels of Hierarchies in SQL Server (2012), in addition to being able to address issues like same child having more than one parent (eg. An Employee could end up having 2 different managers - eg. Project Manager, Delivery Manager).
One way is to have self referencing table (where each row has a parent id , referencing to a parent record - but this would not work in cases where a child has more than 1 parent).
Are there other more efficient ways? What is the best way to implement this?
We are migrating sql server Stored Procedures from 2005 to 2012.We are facing some conflicts(Keyword changes) while migrating into 2012. We are executing each and every stored procedure and finding some keyword changes.We have nearly 2000 stored procedures. It is a time consuming process.
What are the best way to find out the changes in 2012. Anyscript(Sql server) to find out/what are the changes need to be change in 2012.
I am new to using SQL Server but will be having to get to know it at work and have downloaded SQL Server 2005 Express to tinker with at home.
What are the easiest methods to set up a relational database?
Are there any tools you can download to give SQL Server or the express version functionality similar to Access?
Can you get a tool to allow you to not only design tables in design view but then go on to create relationships and a database model?
Please excuse my ignorance if this is possible already, can anyone provide any links to getting started links?
There seems to be plenty out there about writing queries and stored procedures etc. but I haven't found as much about setting up a database from scratch.
it is used in an web application that seraches for all the rest of the information using an orderid and displays the rest of the details the size of the table currently is about 123000 records that is increasing by 20000 every week.
i have an archive of the same data that has 7,666,000 records that are also going to be placed into the same database.
my question is is that a good idea of doing this? are there any performance issues that i need to be aware of as currently the application runs quite quick?
Hello gurus:Hopefully someone can shed some light on some questions I have. I amtasked to build an application that will schedule and track tasks. Ifirmly believe in not reinventing the wheel however also feel thatcustomizing is certainly not out of the question.I am looking to build something in ASP (or .NET) that can allow anindividual to work "disconnected" from the network while stillaccessing a database (MSDE?) that will sync with SQL 2000 oncere-connected. I have built multiple db apps that are strictly webbased and consider myself somewhat knowledgeable (read.. dangereous)SQL/ASP dude. I understand that for a stand-alone client app I mayhave to use .NET and load the framewsork on the client in order to runthe dynamic pages for database updates. Here are my questions:1) Is there a way for SQL server to "replicate" its tables into aschema/data recognized by MSDE (i.e. generate the "mother ship" tablesand schema first, then use SQL Server to automatically create (export)the client MSDE tables)2) are there tools to manage MSDE so one can see what the heck is inthere? I understand Enterprise manager will not work in this regard.3) Are there any resources (documents, tutorials etc..) for datareplication (i.e. dbsync from MSDE up to MSSQL and vice-versa)4) Am I wasting my time doing this from scratch because there isalready something out there that does all this that is moderatelypriced and customizable?Thanks for your attention and consideration.Eric B
I'm trying to do a simple alteration to the table design of one of ourSQL 2k tables, simply changing an identity row so that its not 'notfor replication', and its taking absolutely ages to do so, and stopsthe sql server from working.Whilst it's attempting the update, no one can access the database, thesqlservr.exe memory usage shoots up and enterprise manager reports anot responding status. Eventually after about 10 minutes, it bombs outreporting,Unable to modify tableCould not allocate space for object 'Tmp_TableName' in database'DBNAME' because the 'PRIMARY' filegroup is full.The table i'm attempting to change has only about 4000 records sothere's not a huge amount of data.Any ideas what's causing this and how i can get around it?A similar thing happens when i attempt to change the length of avarchar too.Thanks in advance for any suggestionsDan Williams.
Well, I just spent 2 days tediously debugging an issue with SQL Server 2005 and I have my answer, but I am disappointed... Here's the story. I have a simple Full-Text query integrated into my .NET app. Since app launch I have been getting intermittent complaints that the program does not work, and yet I was never able to reproduce the behavior. I finally got the stack trace from a user and it was a time out exception. On the weekend, I decided to work a bit and the FT (Full-Text) query was the first I tried. Boom - Timeout! Rerunning the query though, does not timeout! Exactly what my users are seeing. Subsequent queries return in the usual 0.5 seconds. So after running many traces over 2 days, I determined that Full-Text queries time out on the first FT query run after 45 seconds and the timeout will happen again if no FT queries are run for 30 minutes. So basically, if we run text queries less frequently than every 30 minutes, we get a timeout every time!!! So with this info, I finally found the fix...
http://support.microsoft.com/kb/915850
So this fix is spot on and the Status says: This behavior is by design. My problem is resolved with this command - sp_fulltext_service 'verify_signature', 0. But, the fix supposedly opens a security hole? So here are my questions:
1. How worried about this security hole should I be? 2. My server has internet access! So why am I getting the timeout? Internet Explorer works fine. 3. So since the query times-out with the verify_signature = 1, why are the subsequent queries allowed to execute? Shouldn't the subsequent queries fail as well since the word breaker is un-verified? Seems to me that if you're gonna let subsequent queries run without signature verification, why make the first one wait 45 seconds and timeout? 4. This timeout event should have AT LEAST shown some event in the trace that the timeout happened... Trace only showed that FT portion of query took almost exactly 45 seconds. Even with all the FT Event Classes turned on I saw nothing indicating a signature verification timeout.
Note: considering the fact that I have Internet access and still see this problem, it may be more widespread of a problem than MS believes. Also, others may be experiencing this and not realize what is happening because of the lack of any Trace events and the inability to reproduce! People may think (as I did) that the SQL Server is flakey or the network is flakey. I'd really like to see a hot-fix to address this.
Is it possible to create a database which design or data is not needed to update and i want to set to it as a READ ONLY. what are the steps to create such type of database?
I am studying for the above exam and have got a new laptop with the basic version of Vista pre-installed on it. I've got an evaluation version of SQL Server 2000 I got from a Microsoft event but the basic version of Vista I have, will not let me install it.
Can anyone advise me whether SQL Server Express will be useful in studying for the SQL Server 2000 Design 70-229 Exam?
If not, I see my options as fork out for an older OS licence - XP pro to put on the laptop which would allow me to install the evaluation edition. Not my first choice but it would do...
I want to control the size of ldf files and mdf files of several databases on SQL Server 2008 in my organization (manual increase), but i have a question:
What would be the best practices (best methods) for provisioning a ldf file and mdf file? Exists any generic formula?
With this i want to avoid the shrink operation and the autogrow of sql server databases...
Hello, we have some tables and views that are not assigned to the standard schema of dbo.Whenever I try to use a design time control and/or SQLDataSource connection control, it does not like objects other than those using the dbo. schema.Any ideas on how to resolve this issue?For example, her are a couple of views:dbo.vwCustomersdbo.vwCustomerDetailsord.vwOrdersord.vwOrderDetailsthe views with dbo.* I have no problem with.the views with ord. it tells me the view(s) do not exist.Thanks in advance!
I'm in the process of building messaging functionality in to my application where by users can contact one another, look at it as a dating site, you click on someones profile, view their profile and then send that user a message.
I started to build the table which looked like this:
Id (PK) (Increments by 1)
ToUserId (FK) -- User who they're getting in contact with
FromUserId (FK) -- User who sent the message
Content (nvarchar(3000)) -- Message being send
Status (int) -- read / new / deleted / sent
EmailDate (datetime)
EmailDeleted (datetime)
But the problem with this setup is both user's maybe sending / replying to each other so I would have multiple entries / statuses in one table which may become a nightmare to manage / control.
I have a problem setting the default value of a column. I am trying to set it to
(CONVERT([float],getdate()+(2),0))
However, SQL Server automatically sets it to
(CONVERT([float],getdate()+(2),(0)))
While it functionally does not change anything, we have a tool which compares the database schema against a pre-existing schema and shows this as an error.I have tried setting the value directly and through scripts but it does not work either way.
In our application we are copying data between 2 sqlserver databases using linked server. Say, sqlserver1 is source and sqlserver2 is destination, then, our application will be on sqlserver2 box and will copy data from sqlserver1 to sqlserver2.
User also need to choose from which database of sqlserver1, the data need to be copied. This data is our application data.. Nothing related to sqlserver database.
We are using the following query, to get the database names of the source sqlserver..
SELECT * FROM <linked server to dest db>.master.sys.databases
My questions is : is this query generic enough that works on all sqlserver versions?
Is the master database name of sqlserver configurable or its alwasys fixed as "master"?
while failover from one node to another node in cluster environment. cluster node is moving to another node but my SQL server services are not started.in event viewer i am getting bellow errorr event id 19019
I ran into an interesting situation. I'm working on contract and was looking at creating an ERD for an existing database when I ran into a problem. I found FK's that are referencing columns that do not have a unique constraint or a unique index.
I don't know the history of the database but was there a time in SQL Server history where this would have been possible? I scripted out the tables and created it in a test database. When I run the script to create the FK I get the following message.
I double checked the original tables and this FK does exist in table1 and there is no unique anything in the referenced table, table2. Currently the database is running on SQL Server 2008 Ent.
ALTER TABLE [dbo].[table1] WITH CHECK ADD CONSTRAINT [FK_table1] FOREIGN KEY([Col1]) REFERENCES [dbo].[table2] ([col2])
There are no primary or candidate keys in the referenced table 'dbo.table2' that match the referencing column list in the foreign key 'FK_table1'.
I want to do something with error checking in my company. For this we have a selection of different tables and the data needs to meet various validation rules else it is classed as an error.
To deal with this I'm currently thinking of this approach:
1. Create a view pulling all of the various data together from the multiple tables. 2. Create an empty 'errors' data table. 3. Create an Excel file with a button to call a Check for Errors Script
Then in the the script:
1. Clear the 'errors' data table 2. Call multiple scripts, each of which uses the new view, applies the checks for that specific error and writes any erroring data into the 'errors' data table (along with a text string with the unique error code for filtering / sorting purposes). 3. After calling all the scripts, the table can be refreshed in excel when when used with a pivot table can show the various errors, and let us drill down into all the data so we can fix them.
Also.. Ideally, I'd like some way to write comments in an excel column for each entry and error code and be able to write that back into a comment table.
select distinct case when LastStatusMessageIDName = 'Program completed with success' then 'Office 2013 SP1 Installed Successfully' when LastExecutionResult = '2013' then 'Machine Does not have Office 2013' when LastExecutionResult = '17023' then 'User cancelled installation' when LastExecutionResult = '17302' then 'Application failed due to low disk space.'
[Code] .....
The below is the output for the given query,here i want to see only one comment value in my list and the count is also sum of all where comment should be Application will be installed once machine is online(Bold columns o/p)
Comment Machine Name Application will be Installed once machine is Online 4 Application will be Installed once machine is Online 12 Application will be Installed once machine is Online 42 Application will be Installed once machine is Online 120 Machine Does not have Office 2013 25 User cancelled installation 32 Application failed due to low disk space 41 Office 2013 SP1 already Exist 60
I need o/p like below:in single line
Application will be Installed once machine is Online 178 Machine Does not have Office 2013 25 User cancelled installation 32 Application failed due to low disk space 41 Office 2013 SP1 already Exist 60
