|
Check the link in my reply to the post below. Hope it helps.
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...!!
|
|
|
|
|
Dear friends,
I need to export records from sql to excel. I know how to export the whole record to excel. But my requirement is, i have to insert 10 records in sheet1 and another 10 records in sheet2 and so on.... a help or some links would be highly appriciated..... (i dont want any packages like DTS)
|
|
|
|
|
The only direct way I know how to do that with separate sheets is with ADO and COM.
|
|
|
|
|
Check this[^]. I think this should help you.
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...!!
|
|
|
|
|
|
A seven field composite primary key  - sounds like a disaster has found somewhere to happen. This is a BAD idea, I have no knowledge of the performance issues with such a mess but the sheer cumberomeness of forever doing a 7 key join would drive me to drink.
I recommend that you insert a primary key into the central table and populate it, then turn autonumber on.
Now add the foreign key field to the other table(s) and use the 7 key horror to populate the field.
Make your relationship using these fields. You must make sure you support the change within your CRUD code.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
What are some options to replace sql dependency using sql server with multiple users? I like how sqlDependency works but without some changes to my database I can't determine what was added, modified , or deleted. So when I receive a notification I'm forced to reload the dataset and refresh. While this is not a major problem with a small dataset, as the dataset grows the time required to do this will be come unacceptable. I know one option is to continue using sqldependency and only load the data displayed for the current day on the calendar. So is there any other ways to keep data sync'd in a multiple user environment?
Thanks,
Paul
Some more information:
Application is a scheduling program
Use sqldependency for notification only
Use linq to sql for actual data retrieval
|
|
|
|
|
I'll be interested in any responses to this one, I have never worked with SQLDependancy. I insist on minimum recordset size for the UI, any more than about 500 records gets looked at severely and usually the design is changed to meet the requirement.
Where I have to load a large volume I often allow the user to make a number of changes and then reload the dataset.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi,
We have got hundreds of tables and SPs but no diagram. Basically we just keep on adding new tables as per requirement or delete table and there is no info.
What i want to implement now is - a whole database diagram - So what would be the best way keeping in mind that database is big if not huge.
Thank You
Andyyy
|
|
|
|
|
Which database?
With SQL, it comes as a built in feature. Is that what you are looking for?
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...!!
|
|
|
|
|
Yeh it's SQL - sorry i mean Diagram.
Should i just use visio and find my way out but that means going through hundreds of tables and sps and understanding them..Looks like i will never get time to sort this out unless there is quicker way which is also good.
|
|
|
|
|
If you expand the [DatabaseName] node, the first thing you will see is diagram. Although AFAIK, it will not help with stored procedures.
You can also check this[^] link. Might be of some help.
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...!!
|
|
|
|
|
You need to be careful with SQL diagram designer you could end up applying foreign key constraints while building a diagram. SSMS will not let you save the diagram if the data does not match the constraint, so save OFTEN, this is a good thing but you are going to be VERY frustrated chasing down the data errors.
I would use visio unless you actually want to clean up the data. Personally I would opt to clean up the database there are some enormous benefits to be gained from a well structured database.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: I would opt to clean up the database
Although it would be a bit of pain while cleaning the mess but the in the long run, it is going to be very helpful.
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...!!
|
|
|
|
|
Hi all, I am trying to get the largest number from a column.
where it happend to be the same number exist multiple times.
But I am only interested only one number that is largest.
do not care how many are there.
thanks
FYI database sqlite3.
Second question: How come variable type datetime does not work in sqlite.
I am using now as string data type but I need to use as datetime where I can query as datetime.
thanks again.
|
|
|
|
|
select max([your field name]) from [your table];
That returns the max number of that column.
There is no DateTime type in SQLite. You can use text type for it. SQLite has a few date/time functions you can use, though.
Use ANSI format for the datetime field. It will sort the field correctly.
|
|
|
|
|
Thanks for your reply. I will let you know if were able to solve my problems.
thanks again.
|
|
|
|
|
Loyal ginger, thanks for your help it works.
Just to let you know that I am very new to database.
thanks
|
|
|
|
|
So why not vote the reply as good answer.
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...!!
|
|
|
|
|
loyal ginger wrote: There is no DateTime type in SQLite
I find that incredible considering all the problems that arise from storing date values as strings.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi,
I have a situation where I wanted to have the weekno for the given date.
If year is changing then weekno should add with the previous weekno.
Below is the table contains two columns, Bug date and corresponding weeknumber... till 12/31/2011 I am getting correctly but after that am getting wrong result. for 01/01/2012 the weeknumber should be 158 but am getting 157, can any one help me solve this problem..Pls see my logic below... am using sql server 2008.
Bug Date weeknumber
10/25/2009 44
11/01/2009 45
12/30/2009 53
12/31/2009 53
01/01/2010 53
01/03/2010 54
12/30/2010 105
12/31/2010 105
01/01/2011 105
01/02/2011 106
01/04/2011 106
12/30/2011 157
12/31/2011 157
01/01/2012 158
01/08/2012 158
DECLARE @FirstDate char(20)
DECLARE @baseyear char(20)
SET @FirstDate =(SELECT Min(Bugdate) FROM BugsDB_DefectVolume)
SET @baseyear=(SELECT Datepart(year,@FirstDate)) //am considering the first date as base year.
UPDATE BugsDB_DefectVolume
SET weeknumber= Datepart(wk,@BugDate) + ((Datepart(year,@BugDate) - @baseyear) *52),
StartDayofWeek=DATEadd(day, (1- DATEPART(dw,@BugDate)), @BugDate)
WHERE BugDate=@BugDate and [Product ID]=@ProductID
|
|
|
|
|
Does this work:
DECLARE @FirstDate char(20)
SET @FirstDate =(SELECT Min(Bugdate) FROM BugsDB_DefectVolume)
UPDATE BugsDB_DefectVolume
SET weeknumber= DateDiff(wk, @FirstDate, @BugDate) +1,
StartDayofWeek=DATEadd(day, (1- DATEPART(dw,@BugDate)), @BugDate)
WHERE BugDate=@BugDate and [Product ID]=@ProductID
Wout Louwers
|
|
|
|
|
Yes its working fine but only the problem is when date is 01/01/2012 its giving the weeknumber as 157. this is wrong. it should be 158. To achieve this where can I do the modification in my existing query???
|
|
|
|
|
What I was trying to tell you is that you should DateDiff to calculate the number of weeks. Not every year has 52 weeks!
Wout Louwers
|
|
|
|
|
It's because contrary to popular belief, there are not 52 weeks in a year. There are 52 weeks and one spare day left over. So, if you just assume 52 weeks every year, eventually your calculation gets out of line. That's why financial years occasionally have a week 53 to stop things from drifting off.
Basically about 1 year in every 5 or 6 has 53 weeks. It's not a straight 1 in 7 because leap years throw it out (every fourth year has 52 weeks and 2 days left over which makes the calculation trickier). There is an ISO standard which defines it. 2004 had 53 weeks, 2009 has 53 weeks, 2010 has 52, the next 53 week year will be 2015.
Basically, if 1st Jan is a Thursday (or if it is a leap year either a Wednesday or a Thursday) then there are 53 weeks in the year.
For what you want, you might be able to calculate the number of days from a base date and then divide by 7. It might be easier than trying to work out weeks per year. Does that give the right answer?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
