|
|
Hi all,
I have a sql statement that occur error when I run in sql server 2005....
"The query uses non-ANSI outer join operators ("*=" or "=*"). To run this query without modification, please set the compatibility level for current database to 80 or lower, using stored procedure sp_dbcmptlevel. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes."
update
TMP_TABLE_1
set
CLASS_NO = coalesce(B.CLASS_NO , B.STUDENT_NO)
from TMP_TABLE_1 A,
TMP_TABLE_2 B
where
A.CLASS_NO *= B.STUDENT_NO
and A.REQUEST_ID = @requestID
But How can I change the sql to Right outer join or left outer join.
Please kindly help.
Thanks
|
|
|
|
|
update
TMP_TABLE_1
set
CLASS_NO = coalesce(B.CLASS_NO , B.STUDENT_NO)
from TMP_TABLE_1 A LEFT JOIN
TMP_TABLE_2 B
ON A.CLASS_NO = B.STUDENT_NO
where
<s>A.CLASS_NO *= B.STUDENT_NO
and</s> A.REQUEST_ID = @requestID
|
|
|
|
|
|
Hi,
Every time i retrieve information from a large scale database from asp source, i get the information in 4 Sec. and with a lot of dataTable exception in the debugger mode.
If i use the query directly in the sql mode i get it all after 10 Sec.
I tried to change the connection string timeout but it made no difference.
can someone help me please?
|
|
|
|
|
What do you mean by "debugger mode" versus "sql mode"?
Lior S wrote: can someone help me please?
Show us your code, it will help us determine what exactly it is that you are doing.
|
|
|
|
|
Lior S wrote: lot of dataTable exception
are u using DataReader.Read or DataAdapter.Fill ?
ThaScorpion
|
|
|
|
|
Hello,
I was wondering if anyone is having this problem and knows the how and why of it. I have a Microsoft Access table, where one column is in the Date/Time format (format set to general). When I try to update this table (i.e. adding a new row) using C#, I get a datatype mismatch error. Is there anyway to directly pass this information to Access? I am using oledb.
I've found a work around by converting my date/time in c# to a string and storing it in my database as a string. But if possible, I'd like to keep it as a date/time format in my database.
Thanks in advance!
 over-worked, under-paid, glorified secretary 
|
|
|
|
|
It would help if you could post a sample of the code that is not working, along with a description of the table structure (is the field a date/time type, or a text datatype with display format set to general date?).
|
|
|
|
|
Hehe, oops, here is some of the trouble code:
userDS.UserSessionInfo.AddUserSessionInfoRow(CurrentUser,myDate,false,false,"",false,"");
when myDate is:
DateType myDate = DateTime.Now;
and the table structure for UserSessionInfo is:
UserSessionPkey --> int (autoincrement row, primary key)
UserPkey --> int (foreign key)
SessionDate --> dateType
ErrorsGen --> boolean
Training --> boolean
TrainingType --> string
Testing --> boolean
TestingType --> string
The above doesn't work and generates a datetype mismatch type error. But when I change it to the following, it works fine:
string myDate = DateTime.Now.ToString();
and the table structure for UserSessionInfo is:
UserSessionPkey --> int (autoincrement row, primary key)
UserPkey --> int (foreign key)
SessionDate --> string
ErrorsGen --> boolean
Training --> boolean
TrainingType --> string
Testing --> boolean
TestingType --> string
In my MS Access database, the column for SessionDate is of type Date/Time, with no formatting specified. When I change myDate to be a string, it works fine, even though I did not change the type in the Access database.
I know I've got a work around, by using strings, but I'm curious if anyone knows why this is happening.
TIA!
-- modified at 17:17 Sunday 19th November, 2006
~~~~~~~~~~~~
Over-worked, under-paid, glorified "secretary"
|
|
|
|
|
Okay...and now its doing the same thing with a boolean type. If I change my .xsd column from boolean to string, it works.
What's going on?? I found this website, and I thought I was doing what it lists...argh!
http://support.microsoft.com/default.aspx?scid=kb;en-us;320435
~~~~~~~~~~~~
Over-worked, under-paid, glorified "secretary"
|
|
|
|
|
I believe Access stores dates as real numbers representing the number of days since December 20, 1899 (or something close to that), so you probably need a conversion function. In VB, we could use CDate(X) to convert a date represented as a string to a value with date/time format. See if C# has a similar conversion function.
Scott
|
|
|
|
|
Ohhhh...now why don't they just say that instead of crazy errors? I don't suppose you have a resource (website, book, etc.) I can look into in case I run into this problem again for other types (i.e. stupid boolean type in another post). I will give this a try, it seems to make crazy enough sense to be right. Thanks so much! ![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)
~~~~~~~~~~~~
Over-worked, under-paid, glorified "secretary"
|
|
|
|
|
I want to import a txt CSV file into a view in SQL 2000.
that can be done easily using VBA but the problem is that I want to check the validity of data e.g checking the serial no if its numeric , check the date if its in correct format...and I want to use a sql trigger for that so that each time theres an insert , before that it should chk the validity of data , etc . .
plz hlp.
imran
|
|
|
|
|
Dear all;
I have a datetime field that stores data like "15/11/2006 10:12:23 AM"
how can I extract the date part only without concatenating month and day and year inside the Sql statement as i have LOTs of data to be extracted. I tried to cast to char(10) and then convert to date but it was too slow.
Thanx
Scorpion
|
|
|
|
|
ThaScorpion wrote: how can I extract the date part only
Use the following in stored procedure,
convert(varchar,checkindate,101) as checkindate
This will extract only 15/11/2006 only.
For ur reference:
checkindate --> Field in the table.
Hope u will understand it.
Janani
|
|
|
|
|
I mean extracting as date not just a string !
As converting to varchar ordering as date will fail !!
If I will say "Order By convert(varchar,checkindate,101) as checkindate"
the sort order will not be correct.
Is there anything in SQL server that solves that problem ??
hope u understand it.
Thank u
Scorpion
|
|
|
|
|
use dateadd(day, datediff(day, 0, checkindate), 0) as checkindate to truncate the time value
|
|
|
|
|
Thank you indianet for your reply
but i think i will face the same problem of speed.
i didnt try it yet, but do u think it will not be slow ?
There is a function in Access that do the solution in one step like :
"Order by DateValue(CheckInDate)" and thats it !
there is nothing like that in Sql Server ?!
thanx
Scorpion
|
|
|
|
|
select convert(varchar,Reminder_date,101) as Reminder, Reminder_From, Reminder_Until, Reminder_AlarmTime, Create_dt, Update_dt from dbo.TBL_Reminder order by Reminder
It was possible to order by date.
Janani
|
|
|
|
|
Thanx alot till i Try it
ThaScorpion
|
|
|
|
|
Have U order it by date?
Janani
|
|
|
|
|
Is there a way to run a query where i delete all rows containing a row ID in a text file? ie. I have a table "Items" with a column "ItemID". Id like to delete every ItemID row contained in c:\test.txt
Any help would be appreciated.
|
|
|
|
|
Hello everybody,
How to build a regular expression to check that all the given words are present in a sentence in any order but all words should be present.
for example: Given words:- aaa bbb ccc
sentence:-1) rock aaa bed bbb sky ccc
2) rock bbb bed aaa sky ccc
3) rock aaa bed bbb
Matchs:- 1 and 2 should get matched in the sentence
3 is not a match
Mitesh... best of luck..
|
|
|
|
|
select sentence
from ( select sentence
from ( select sentence
from book
where sentence like '% aaa %'
)
where sentence like '% bbb %'
)
where sentence like '% ccc %'
;It's not a regular expression, but this is the SQL forum after all. 
Chris Meech
I am Canadian. [heard in a local bar]
I agree with you that my argument is useless. [Red Stateler]
Hey, I am part of a special bread, we are called smart people [Captain See Sharp]
The zen of the soapbox is hard to attain...[Jörgen Sigvardsson]
I wish I could remember what it was like to only have a short term memory.[David Kentley]
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
