|
Hi i am using this sql statement on (SQL Server 2000) and tryin to sort the results on tl.Date but as i have already casted it to a nvarchar it sorts it like a char so im just wondering if anyone has any idea how to sort it by date correctly. thanks in advance Tim
select SUBSTRING(CAST(tl.Date as nvarchar), 0, 12) as 'Date',<br />
tft.BodyWeight from Person as p<br />
left join TrainingLog tl on tl.PersonId = p.Id<br />
left join TrainingFitnessTesting tft on tl.Id = tft.TrainingLogId <br />
where p.Id = 8 <br />
and tl.Date >= '01 January 2005' <br />
and tl.Date <= '03 January 2006' and tft.BodyWeight IS NOT NULL <br />
order by tl.Date asc
|
|
|
|
|
As per my knowledge this query will work fine and will display the result sorted based on tl.Date as Date. I may be mis-understanding your question but I think as you have converted Date into nvarchar in select clause not in Order By clause, hence it will have no effect on the sorting.
Akif
|
|
|
|
|
someone has actually solved the problem now but just in case u would like to know when i got a substring of the date i got dates like
Dec 12 2005
Sept 23 2005
sorted incorrectly as sept should be before dec but because they are nvarchar they are sorted as characters as in D comes before S in the alphabet hopes this clears it up
thanks anyway Tim
|
|
|
|
|
hi all,
In crystal report pull model i am getting following error.
Logon failed.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: CrystalDecisions.CrystalReports.Engine.LogOnException: Logon failed.
how can i rectify ths problem.
expecting immediate response.
thanks n advance
rs_net
|
|
|
|
|
Hallo All
Happy New Year.
I run into a big problem. I used for quite a time SQL Server Express Sql non-release 587. Now I would like to migrate the data to another server using sql Express release. Everything I tried did not work. Attaching the database, Backup restoring the database , nothing worked. Always I get an incompatible version error.
Is there a tool avaiable like imp/exp in Oracle or mysqldump?
Sincerely
Chris
Vietiane/Laos
P.S. I can't use graphical tools or sqlwebadmin. Non of them work with 587
P.P.S. I already contacted Microsoft for this. They say 587 is too old for migration. Only the full SQL Server could do this, not Express.
-- modified at 1:25 Tuesday 3rd January, 2006
|
|
|
|
|
sawat dii bi mai
Try an import/export format recognized by both DB engines.
For example comma separated values (.csv) or any other format both DB engines understand.
Export to a CSV file and then use it to import to the new DB.
I'm on-line therefore I am.
JimmyRopes
|
|
|
|
|
sawat dii Jimmy Ropes
Sawat di bpii mai duay
Chook dii mak
kop hkun krap khian jotmai hai pom
The problem is that the database is quite complicated. Tons of foreign keys for example. I need a way to extract the structure. Like create table statement e.t.c. I do not have a complete table creation script. Some parts of the application were done by different people. I really need something like mysqldump. I just now try to download Sql Server Eval to the new server in Europe. Microsoft SQL Team sugjested that!
Sincerely
Chris
Vietiane Laos (PDR)
P.S. Just now I am in Bangkok (oriental city) So greetings from Bangkok 
Are you in BKK or Thailand just now?
|
|
|
|
|
Sawat dii Chris,
The export to CSV may still work because when you export the CSV formatted file you can make it copy the Field Names in the first line as the column headers and the following lines are the data that were in those columns.
On the file menu select get external data and then import. When you import you can select your own primary key and you will have the same table structure as in the old data base. After all the tables are built you go back to the file menu and select get external data again but this time select Link Tables. You can make the table links from here one at a time.
In this way you can preserve the structure of the old data base.
If the old DB manager does not allow you to put the field headers in you can do it manually in notepad. If the new data base does not accept CVS format (I think they all do) you can import to a newer version of SQL Server or Access and then export to a format that the new data base engine knows about.
chok dii mak krap
I live up-country in Issan (North East Thailand). Changwat Buriram, which is in the southern end of Issan and is a border province with Siem Reib - Oddar Meanchey Province in Kampuchea. I do not go to Bangkok very often. I grew up in New York City and do not like big city life very much. A few days at a time are enough to get the things I need and then I am back on the train to Buriram. What part of Bangkok are you staying in?
I used to go to ViangChan for three month visas from the Thai embassy but when a border crossing opened up not too far from my house (near KapCheong) about two years ago I find it less expensive to go there every month than to have my wife drive me to NongKhai, stay in ViangChang for three days and then have her drive back to NongKhai to pick me up again. Now that petrol is so expensive the cost is even higher.
It costs around 2500 Baht (662,050 Kip) for transport and visa fees to go once a month to KapCheong. If I go to ViangChang it used to cost me around 10,000 Baht (2,648,200 Kip) and that was 2 years ago. The cost will be higher now. When I add up all the expenses by getting 3 one month visas I save around 2500 Baht.
In some ways I miss going to ViangChan because it is a small city and I do not mind going there. The last time I was there there was a very big road being built running in front of the Morning Market so it looks like ViangChan was starting to grow but it will probably be a few more years (hopefully many years) before you have traffic jams like Bangkok, Hanoi or HoChiMin City.
I do want to go to LouangPhrabang some time but the last time I was in ViangChan was not too long after the bus and truck were attacked (on Highway 13) 2 or 3 years ago. A few people died so was afraid to be going by road at the time. I haven’t heard of any more attacks so the next time I decide to take a break and go LPDR for a visa I think I will take 3 or 4 days and go north to see the interior.
I once crossed the Nam Mae Kong and visited a little village in the south. It was north of Pakse but that is as far as I have visited LPDR. When I do get to ViangChan again we will have to meet and have a BierLao.
I'm on-line therefore I am.
JimmyRopes
-- modified at 11:48 Tuesday 3rd January, 2006
|
|
|
|
|
Hallo,
I am not sure about CSV. My problem is most often the data is held in NTEXT fields. The database consists of articles which are often 100.000 Bytes and more and a lot of binary data like images. I guess it will be very difficult exporting with CSV. Maybe I have to write an own exporter. What a shame.
And I have hundreds of tables. 
You know already the most important fact of live. Beer Lao or also named Bia Lao is the best beer in the world.
Some information about myself. I permanetly travel between Vietiane, Chayaphum and Bangkok, so I have 3 places to stay. My Company has offices in Bangkok and Vietiane. I am married with a Laos Lady originally from Sam Nuea.
I just returned today from the Immigration Department in Sathorn Dai to extend my Thai Visa.
Maybe we should have a Beer in Buriram. Actually I will travel to Buriram (I have a customer there) within the next two weeks. I have to, the customer is already complaing. 
Sincerely
Chris
-- modified at 3:07 Wednesday 4th January, 2006
|
|
|
|
|
I think you are probably right that CSV format would not be able to handle BLOB data. I didn't know the nature of your data when I recommended CSV.
My suggestion was meant to alert you to the possibility that if you can not take the direct route sometimes there are other things you can try.
Before you ship your data off to Europe it may be worth taking a look at the other formats available from the old DB engine. Then again, by shipping the data off to a contractor you will not have to deal with the problems that come up whenever you try to do things that are not meant to be done.
============================================
In my heart of hearts I know what you are saying about Bia Lao is true but I can't be saying that around here. In Issan Beer Chang rules! Not the Carlsberg knockoff, Singha, but Beer Chang is the only beer sold in the village.
Of course, in Muang Buriram you can get Singha or even Carlsberg but here in BanLaBerg (you say you have never heard of BanLaBerg - it is also known as Nakhon Nowhere) we have a tradition to uphold. The local variety of Lao Cal, Sam Sung (Thai wiskey) and Beer Chang are the only alcoholic drinks you can buy here.
I even have to go to the next village, BanPrasat to buy Regency (Thai Brandy) but it is very close, maybe 1 Km from my house to the store in BanPrasat, so it is not all that bad.
I am surprised that you even know where Buriram is let alone that you actually come here. Let me know when you will be in town and we can meet and have a drink. I never thought I would ever meet anyone from the Code Project before. It is a small world.
I'm on-line therefore I am.
JimmyRopes
|
|
|
|
|
Here's my problem. I'm using Oracle 8i and I do not have the option of changing the design of the database. I have a master table with some fields and a unique key Code, something like this:
Code Field1 Field2
11111 1 2
11112 2 3
...
I also have another child table that contains some data that is related to my master table. It has two fields Key1 and Key2 which combined are unique:
Key1 Key2 Data1
1 1 1
1 2 5
2 1 12
2 2 3
...
Now, Key1 is linked to the master table through another "link" table:
Code Key1
11111 1
11112 2
...
Now what I want to end up with is this:
Code Field1 Field2 Key2_1data Key2_2data ...
11111 1 2 1 5
11112 2 3 12 3
In other words, the original table with fields added with the value of Data1 from the child table for each value of Key2. I hope that made sense. So I tried something like this:
SELECT master.code, master.Field1, master.Field2, (SELECT child.Data1 FROM child WHERE child.Key1 = link.Key1 AND child.Key2 = 1) AS Key2_1data, (SELECT child.Data1 FROM child WHERE child.Key1 = link.Key1 AND child.Key2 = 2) AS Key2_2data FROM master, link WHERE master.Code = link.Code (+)
This works and can be expanded for an arbitary number of values of Key2 by adding more of these nested select statements and there are actually only 10 values of Key2 for me to worry about, but that could change at some point. It just seems like it is a hugely ineffecient way to do things through. Is there a better way?
|
|
|
|
|
I think this is what you're looking for. I'm not sure if you need the outer join (plus sign) or not. Do you still want to show master data if there is no link/child data? If not, you don't; if so, you'll need one on the latter side of the three code comparisons.
SELECT
m.code,
m.field1,
m.field2,
c1.data1,
c2.data1
FROM master m, link l, child c1, child c2
WHERE m.code = l.code
AND l.code = c1.code
AND l.code = c2.code
AND c1.key1 = c2.key1
AND c1.key2 = 1
AND c2.key2 = 2
Michael Flanakin
Web Log
|
|
|
|
|
Thanks for your help. You code works but it should read l.key1 = c1.key1 and l.key1 = c2.key2 (the child table doesn't contain a code field).
Now I do want to display records from the master table that have no child entry or only have one child put I can't quite seem to figure out the right places to put the (+) signs.
|
|
|
|
|
Good catch. I thought it was odd that the code would be in the child table, too. Guess I got ahead of myself when writing the SQL. It's kinda hard to write that stuff when you don't have the tables to play with in front of you.
The plus sign represents an outer join. You typically do an outer join to a foreign key column that might be null. So, since the p.code column is the primary key, that comparison should be p.code = l.code(+). I don't think you need one on the key comparisons. If so, they probably go on the l.key1 column. I'd have to play with it to be sure.
Also, just to make things easier on people who read your SQL, I'd suggest you put the outer join columns on the right side of the operator. Not that it's a huge deal, but I personally find it a bit easier to read that way. Maybe that's just because it's the way I'm used to it, tho.
Michael Flanakin
Web Log
|
|
|
|
|
hi all,
I am new to this group.
i have to create new aplication trigger for the
table.
give some id or Url address.
thanks in advance
rs_net
|
|
|
|
|
Given the vagueness of your request you could trying reading this article: http://msdn.microsoft.com/msdnmag/issues/03/12/DataPoints/[^]
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
First of all: HAPPY NEW YEAR TO YOU ALL.
The following code executed ONCE and it works just fine. But it fails for subsequent calls, given the following error msg: “No Transaction in Active”.
Private Sub Update_Account()
Dim T_OwnRec, A_OwnRec, P_OwnRec As Boolean
T_OwnRec = Data.Get_TCount
A_OwnRec = Data.Get_ACount
P_OwnRec = Data.Get_PCount
Try
'****ERROR
Data.Con.BeginTrans()
'****ERROR
If Update_All(T_OwnRec, A_OwnRec, P_OwnRec) = True Then
Data.Con.CommitTrans()
Trigger_Thread2LoadTags()
Else
Data.Con.RollbackTrans()
MiniMe = False
MsgBox("The Account could not be updated!!!", MsgBoxStyle.Information, "Unexpected error at:" & t_Str)
MiniMe = True
End If
Catch
Data.Con.RollbackTrans()
Handle_Error(Err, "Update_Account")
End Try
End Sub
The error is generated at the "Data.Con.BeginTrans". CAN ANYBODY OFFER SOME HELP. THANKS A LOT GUYS/GIRLS
A VERY LOST NEW GUY
|
|
|
|
|
I'm confused. What technologies are you using?
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
I am "trying" to code using VB.Net and ADO.
|
|
|
|
|
alexvw wrote: I am "trying" to code using VB.Net and ADO.
That's what is confusing. ADO.NET has BeginTransaction, not BeginTrans. (I'm assuming SQL Server on the back end, because you didn't fully answer my question)
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
Hi Guys, first of all Thanks for offering help. I found out what was causing the error (still do not understand why). The three top variables are being loaded by running queries and each function is supposed to return TRUE/FALSE (they do). I understood that Local Variables (within subs or functions) were automatically disposed off, once the focus leaves the sub or function. But my Local Recordsets are not being disposed off, and for some reason that goes beyond my little knowledge is preventing me from starting a Transaction.
this is weird. Because I am not hardcoding the dispossal of any of my local variables at all, and this is the only piece of code that generates this error.
Once again. Thank you guys for offering your help. HAVE A VERY HAPPY NEW YEAR.
Alex. (STILL A VERY LOST NEW GUY)
|
|
|
|
|
Check the state of the connection. A transaction cannot be initiated with the state of the connection other than OPEN.
Happy new year to all of u
Akif
|
|
|
|
|
Unfortunately the Connection is OPEN, I am able to execute other commands through my app.
|
|
|
|
|
Hi Guys, first of all Thanks for offering help. I found out what was causing the error (still do not understand why). The three top variables are being loaded by running queries and each function is supposed to return TRUE/FALSE (they do). I understood that Local Variables (within subs or functions) were automatically disposed off, once the focus leaves the sub or function. But my Local Recordsets are not being disposed off, and for some reason that goes beyond my little knowledge is preventing me from starting a Transaction.
this is weird. Because I am not hardcoding the dispossal of any of my local variables at all, and this is the only piece of code that generates this error.
Once again. Thank you guys for offering your help. HAVE A VERY HAPPY NEW YEAR
Alex. (STILL A VERY LOST NEW GUY) 
|
|
|
|
|
alexvw wrote: Recordsets
ADO.NET does not have recordsets. Classic ADO does. You should be using ADO.NET. This may be an incompatibility issue.
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
