|
Jörgen Andersson wrote: Composite indices can on the other hand under certain circumstances considerably speed up a query
Ah but I was being derogetry about composite KEYS not indexes. Composite indexes are a basic tool for tuning a database, composite keys annoy me.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks you fellows for your guidance. I have corrected alot of mistakes I had made earlier. I have revised and resposted my work again. I am not sure of the Class and Enrollement tables relationship.
Isn't whenever the Foregin Key is involved it becomes 1 to many relationship. Please correct me. Also, shouldn't the CourseStartDate and CourseExpiryDate be in the Class table and student enrolledDate in Enrollement table.
STUDENT[StudentID(PK), StudentFName]
COURSE[CourseID(PK), CourseName]
INSTRUCTOR[InstructorID(PK), InstructorName]
CLASS[ClassID(PK),CourseID(FK), InstructorID, Location, CourseStartDate,CourseExpiryDate]
ENROLLEMENT[ClassID(PK), StudentID(PK),Grade, EnrolledDate]
Relationships are as follows:
Course and Class: 1 to many
Class and Instructor: 1 to many.
modified on Thursday, November 26, 2009 11:34 AM
|
|
|
|
|
Looking good, however.
It looks like your CourseStartDate and CourseExpiryDate should be on your course table. The classes probably have thier own dates within the course date range, unless they should be the ClassStartDate and ClassExpiryDate.
Personaly I would prefer to call it the EndDate rather than ExpiryDate. 'ExpiryDate' makes it sound like the class will begin to smell if you leave it in the cupboard to long. 
|
|
|
|
|
netJP12L wrote: Isn't whenever the Foregin Key is involved it becomes 1 to many relationship
You are correct! A many to many relationship requires a link table. Your structure loks good and I have yet to run across a smelly table no matter what the fields are named.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have the following code
SET @sql = 'SELECT *
FROM tbl_Employee
WHERE tbl_Employee.Department_ID = ' + @Department_ID + AND tbl_Employee.Surname LIKE %' + @Surname + '%'
If i remove the Like part it works fine but with it added it's casuing some problems. I think I'm concatenating the sql wrong.
If someone can help it would be much appreciated.
ASP all the way
|
|
|
|
|
Do this and check your SQL:
print @sql
|
|
|
|
|
The single quote before the AND keyword is missing, it should be:
SET @sql = 'SELECT * FROM tbl_Employee
WHERE tbl_Employee.Department_ID = ' + @Department_ID + ' AND tbl_Employee.Surname LIKE %' + @Surname + '%'
|
|
|
|
|
I presume there is a good reason why you are using dynamic SQL, the select statement does not require it!
This will work without dynamic SQL
Set @Surname = @Surname + '%'
SELECT *
FROM tbl_Employee
WHERE tbl_Employee.Department_ID = @Department_ID
AND tbl_Employee.Surname LIKE @Surname
As Shameel suggested print the @SQL, copy the result back and try to run it, then fix it so it runs and make the changes to your dynamic sql construct.
|
|
|
|
|
Thanks guys. The reason for dynamic sql is because this is part of a much larger section of code for custom paging. Again thanks
ASP all the way
|
|
|
|
|
Hi all,
I want to start to enter my records from specific number.
let's say I would like to start entry in database table from 1000 instead of 1.
means right now my records are entered in database from 1.
Instead of 1 I would like it to start from 1001. Is it possible? if yes then how?
Believe Yourself™
|
|
|
|
|
Try This :
DBCC CHECKIDENT (tableName, RESEED, 1000)
Abhijit Jana | Codeproject MVP
Web Site : abhijitjana.net
Don't forget to click "Good Answer" on the post(s) that helped you.
|
|
|
|
|
That worked!!
Thanks Abhijit 
Believe Yourself™
|
|
|
|
|
Great ! 
Abhijit Jana | Codeproject MVP
Web Site : abhijitjana.net
Don't forget to click "Good Answer" on the post(s) that helped you.
|
|
|
|
|
Make your question as clear as possible, so others can understand it properly. What you are referring to is probably start an identity column in a table.
When creating the column, you can specify the seed and the increment.
CREATE TABLE Tab1
(
Col1 int IDENTITY (1000, 1)
)
If you want to change the seed of an existing column that may already have data, use this:
DBCC CHECKIDENT ("Tab1", RESEED, 1000);
|
|
|
|
|
Both worked. Thanks. 
Actually I am going to start it from 300000.
Does this huge starting number could create any issue when the records increases??
If there would be any limitation for increasing the amount of the records in future then also please mention.
Believe Yourself™
|
|
|
|
|
There is no known problem with using IDENTITY columns. It works well. If you want to really have 'big' numbers, use bigint datatype instead of int. The syntax remains the same.
|
|
|
|
|
Hey All,
I find myself here asking a question around SSIS which is somewhat out my comfort zone of C# but I have been tasked with some work, and I have came across a problem which I can't seem to over come.
I have an SSIS Package which I am calling from the command line so its in the file system. Its encrypted with password etc. When I am logged in under my own domain user name the script executes perfectly. However; there is a network service user our support department has set up to run this job. When I run this package under this new user, the pacakge fails.
My package has several tasks, however; the task which appears not to run in the Script task. Again, it runs under my own logon, just no with the service user logon.
As a test, I added the service user to the administrator group on the computer the package is running, and the script task worked perfectly. (I think this might be important).
I'm guessing I have a permissions issue with my new user. My thoughts are that it cannot execute vb scripts which I believe are internal to SSIS?
If anyone has any thoughts, could you please help.
Thanks in advance
TF
|
|
|
|
|
You need a Configuration File to store the credentials to the Server. To make sure if what you are saying 9is what am assuming. can you please send us the error ?
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
|
Even though the structure might be similar, the order of columns is not the same. When you use *, SQL Server takes the column names in the order they were created, therefore causing a data type mismatch. When you specify the columns, SQL matches the column names exactly. This can even happen with a normal INSERT INTO ... SELECT statement, it has got nothing to do with Linked servers.
|
|
|
|
|
Please don't delete the post once someone has replied to it. It might help others.
50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!
|
|
|
|
|
Just throwing the question out there incase anyone has tried this. When you're using SQL Server Management Studio, you can right click on most objects and manually generate the create/drop/alter scripts.
Is it possible to generate the SQL scripts (or send the script text to a parameter) from within a stored procedure?
Thanks for any pointers!
"There's no such thing as a stupid question, only stupid people." - Mr. Garrison
|
|
|
|
|
I'm not aware of anything "built in" that would generate the create/alter/drop script for you, but the syntax of these commands is pretty straight forward.
For example:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FSACTIONSET]') AND type in (N'U'))
DROP TABLE [dbo].[FSACTIONSET]
So you could build your own SPROC to generate the script syntax.
What are you trying to do ? Maybe there is a different way to attack your problem.
david
|
|
|
|
|
Right, that would be from a drop script.
I need to dynamically rebuild a large set of tables. There could be changes to the table on a daily basis, so I was hoping there would be a way to programmatically get at the create scripts and exec it as dynamic sql.
"There's no such thing as a stupid question, only stupid people." - Mr. Garrison
|
|
|
|
|
Jon_Boy wrote: There could be changes to the table on a daily basis
This tells me your design sucks. If you are changing table structure daily then there must be something disastrously wrong with your data design. I shudder to consider working in such an environment, it sounds like a nightmare.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
