|
Hmm.. I wish I could. I have dealt with dBase/FoxPro before, but it was (fortunately) a long time ago.
Anyway, if you install MDAC (Microsoft Data Access Components), you get an ODBC-driver for FoxPro. You can use that driver, just like you would use Access. The SQL syntax is fairly similar, so simple CREATE's, SELECT's, etc should work.
I am not sure how ODBC works with .NET, but I would assume it works (haven't fiddled much with anything other than MSSQL in .NET)
--
The moment of terror is the beginning of life.
|
|
|
|
|
Thanks for you time/input, Jorgen.
I guess that I will have to trial-and-error as we engineers say!
Xiao!
|
|
|
|
|
|
Arjan,
Thank You for this information resource!
Don.
|
|
|
|
|
I have a table and everything works out okay if I log in as sa, naturally.
If I run:
SELECT TOP 1000 * FROM [site] WHERE [date]>='2000-10-01' And [date]<='2000-10-07 23:59:00'
select count(*) from site
select * from site
I get back everything I expect.
However if I log is as another user, the first SQL results in zero rows (as sa it was ~700), the second statement reports there are ~5000 in the table, and the third returns all ~5000 rows. Weird!
I have a feeling I am missing something totally obvious, but I can't figure it out, especially as the statment without the WHERE clause works fine.
Any Ideas?
--Colin Mackay--
"In the confrontation between the stream and the rock, the stream always wins - not through strength but perseverance." (H. Jackson Brown)
|
|
|
|
|
It was a date thing. At some point a particular user got set to "British English" rather than just "English"???? And it flipped the month and days around.
Now... (perhaps I should go off to the soapbox at this point) WHY THE $&*% is American localisation formats called "English" and British localisation formats called "British English". If someone said this date is in "English" format I'd expect the format to be the one used in England. Makes Sense? Doesn't it?! But oooohhh! noooooo! in SQL Server 2000 "English" format dates mean the format of dates used in the United States of America! (which is about 3000 miles from England).
Aaaarrrrrrggghhhh!
--Colin Mackay--
"In the confrontation between the stream and the rock, the stream always wins - not through strength but perseverance." (H. Jackson Brown)
|
|
|
|
|
What country is Microsoft located in? US. Thus English is US English.
Actually, if I saw "date format: English" I'd immediately be suspicious because I know that different English-speaking countries use different date formats.
--Mike--
Ericahist [updated Oct 26] | CP SearchBar v2.0.2 | Homepage | RightClick-Encrypt | 1ClickPicGrabber
#include "witty-quote.h"
|
|
|
|
|
I colleague of mine is having similar problems to what you're describing.
<rant>What's amazing to me is why the hell localization is done on this level. Why not stick with ISO dates and let applications worry about localization and presentation. Same thing goes for decimal points. The DBMS vendors have gotten it all wrong if you ask me. A database is seldom accessed by end users with a console anyway. A DBA should be able to handle ISO-dates.</rant>
--
The moment of terror is the beginning of life.
|
|
|
|
|
I totally agree - The RDMS should not be handling presentation layer issues like date formats.
However some localisation issues may still be important - For example, collation order - It is much more efficient if SQL Server handles localised collation sequences. For example, take the values:
Lladró
London
is correct if you are dealing with the English language. But if you select Traditional Spanish as the locale in SQL Server 2000 you get the order:
London
Lladró
because in Traditional Spanish "LL" is considered a letter in its own right that is placed after "L" in the alphabet.
And if the database is localised to non-latin character sets how would the ORDER BY work without locale specific collation rules.
--Colin Mackay--
"In the confrontation between the stream and the rock, the stream always wins - not through strength but perseverance." (H. Jackson Brown)
|
|
|
|
|
Ah yes, but the sort order of things falls outside the scope of abstract datatypes. A date is an abstract datatype, while order is just.. well order! 
A chosen few should sit down and rethink SQL...
--
The moment of terror is the beginning of life.
|
|
|
|
|
Its interesting what you say about it being a localisation issue, as thats an ISO format, so it should not matter. Access will identify it as ISO always yyyy-mm-dd, but when you have 01-10-2000 it will then use the local version.
Iteresting that SQL Server tries to read it as yyyy-mm-dd or yyyy-dd-mm depending on where it thinks it is.
"Je pense, donc je mange." - Rene Descartes 1689 - Just before his mother put his tea on the table.
Shameless Plug - Distributed Database Transactions in .NET using COM+
|
|
|
|
|
Yes, that's what I thought - All the code uses ISO date strings except on the UI (where normal british dd/mm/yyyy is accepted also). That's why it took so long to discover the fault. And because it only happened to one user I was sure it was some kind of strange permissions thing.
--Colin Mackay--
"In the confrontation between the stream and the rock, the stream always wins - not through strength but perseverance." (H. Jackson Brown)
|
|
|
|
|
I have 8 fields in my table:
century1,year1,month1,day1,century2,year2,month2,day2
20 01 12 01 Null Null Null Null
19 98 01 01 Null Null Null Null
I need to set century2,century2,year2,month2,day2 to the next day
after century1,year1,month1,day1, so my date will look like this:
century1,year1,month1,day1,century2,year2,month2,day2
20 01 12 01 20 01 12 02
19 98 12 31 19 99 01 01
How do I do this?
|
|
|
|
|
Why don't you use a datetime field or smalldatetime field? Then you can use the DATEADD() SQL function. to Add one day.
--Colin Mackay--
"In the confrontation between the stream and the rock, the stream always wins - not through strength but perseverance." (H. Jackson Brown)
|
|
|
|
|
I can do this, but What should I use do get after to the 4 field again?
|
|
|
|
|
CREATE TABLE [dbo].[my_date_table] (
[date1] [smalldatetime],
[date2] [smalldatetime] NULL,
[day1] AS (datepart(day, date1),
[month1] AS (datepart(month, date1),
[year1] AS (datepart(year, date1)/100),
[century1] AS (datepart(year, date1)%100)
[day2] AS (datepart(day, date2),
[month2] AS (datepart(month, date2),
[year2] AS (datepart(year, date2)/100),
[century2] AS (datepart(year, date2)%100)) ON [PRIMARY]
The SQL Server documentation has a lot of good information about manipulating dates. You can find more information there.
--Colin Mackay--
"In the confrontation between the stream and the rock, the stream always wins - not through strength but perseverance." (H. Jackson Brown)
|
|
|
|
|
I have a table in SQL Server 2000 as such:
categoryId
categoryParentId
categoryName
I want the categories in hierarchical structure thats why I have the parentId. All top level categories have categoryParentId equal to 0. All other subcategories have a categoryParentId that is equal to the categoryId of their parent category.
I have the following statement:
<br />
SELECT categoryId, categoryParentId, categoryName<br />
FROM categories<br />
WHERE categoryParentId=" & parentId & " OR categoryId=" & parentId<br />
The OR categoryId= will also return the parent category along with its children.
The problem that I am facing is:
I need to modify the statement in such way that it will also provide me with a TRUE or FALSE value for each category that will let me know if the category has children. I cannot change the table at all on the database. I need to do this from within the SQL statement.
Any ideas?
Thank you
theJazzyBrain
Wise is he who asks good questions, not he who gives good answers
|
|
|
|
|
I'm not more than slightly familiar with SQL Server yet, but isn't there a recordcount property you can read? Assuming that each query will return at least one result (the categoryID = parentID case), any value greater than one would indicate that the recordset contains child records.
"Your village called -
They're missing their idiot."
|
|
|
|
|
I made this and it works but I dont know if there is a better way to do it.
<br />
SELECT a.categoryId, a.categoryParentId, a.categoryName, MAX(ISNULL(b.categoryId,0)) AS categoryHasChildren<br />
FROM categories a LEFT JOIN categories b ON a.categoryId = b.categoryParentId<br />
WHERE a.categoryParentID=2 OR a.categoryID=2<br />
GROUP BY a.categoryId, a.categoryParentId, a.categoryName<br />
theJazzyBrain
Wise is he who asks good questions, not he who gives good answers
|
|
|
|
|
What about using EXISTS with a CASE statement? One of the nice things about EXISTS is that it only walks the index-- it doesn't have to retrieve a result set. In this case, it sounds like you are just wondering whether a child record exists or not.
SELECT
a.categoryId,
a.categoryParentId,
a.categoryName,
(
CASE
WHEN EXISTS(SELECT * FROM categories b WHERE b.categoryParentID=a.categoryID)
THEN 1
ELSE 0
END
) AS categoryHasChildren
FROM categories a
WHERE
a.categoryParentID=2
OR
a.categoryID=2
Especially if you are often-reading and seldom-updating the number of children, you may want to consider adding an extra column to each record containing the number of children. Then you could use triggers to update that column on the parent record when you add or remove a child.
Thank you.
Jeff Varszegi
|
|
|
|
|
This seems to be a good and fast solution.
Thanks for your feedback.
theJazzyBrain
Wise is he who asks good questions, not he who gives good answers
|
|
|
|
|
You're welcome!
Jeff Varszegi
|
|
|
|
|
I might be wrong but my SQL statement does only one SELECT but yours does two SELECT.
I have no idea how SQL server handles that, but is there a posibility that my version is faster than yours?
I am not sure about this...
theJazzyBrain
Wise is he who asks good questions, not he who gives good answers
|
|
|
|
|
Like I said, an EXISTS statement is special. It's not really a SELECT because it doesn't return a result set.
In general, you need to worry about table accesses (which may lock a table), use of indexes, doing extra work etc. when you worry about query performance. In this case, you cannot escape the fact that you have to do a subquery of some type in order to look for child records! My way actually is faster because not only does it not return a result set, but it will stop looking after the first record; your original solution (I'm not slamming it, but just explaining) looks up all the child records by joining this potentially-large table to itself and doing an expensive GROUP operation.
I wasn't sure whether or not to leave in the extra WHERE clause to retrieve the children or not, so I left it in.
I recommend pulling up the query plan for each query so you can see what's going on; this should show you a pretty good breakdown of the work done by the server. After you get done with that, you should realize that the proof is in the pudding; if you're curious about the real-world difference, do a microbenchmark!
This is as simple as making a Transact-SQL script that calls GETDATE(), loops through a certain amount of times calling a script, calls GETDATE() again, and does math to figure out the approximate time for each operation. Test a suitably large number of operations to give decent precision, and also put more than one call in each loop to minimize looping overhead. Lastly, select the results into variables, instead of returning a result set to the browser (due to inefficiencies in Query Analyzer). I usually make a stored procedure to perform a microbenchmark if I'm that curious.
Now that I think of it, one way to really speed up your performance (and provide a nice layer of abstraction from the database in the bargain) is to wrap your query in a stored procedure. This greatly reduces the parse/compile burden on the server.
It's great that you don't just take my word for it! I'm curious to hear of your results.
Regards,
Jeff Varszegi
|
|
|
|
|
Thanks for taking the time and explaining all this to me. I shall experiment next week or over the weekend at home as I will be leaving in a while. Although I have been programming in ASP for a long time, I never really thought about queries. Now we are developing large projects at work and we have to make sure that our queries are fast.
Thanks again.
theJazzyBrain
Wise is he who asks good questions, not he who gives good answers
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
