|
Ray Cassick wrote:
when I added it, the maximum I could set the size for was 16. I asume that it ends up being a type of record pointer so it only needs that size, but it looks really confusing.
Honestly I do not know the max of the Text field but we have stored a helluva lot in it before with no problem (way, way, way more than 8000chars.)
So it should be fine for your use, just be careful
|
|
|
|
|
FYI, text is indeed a reference field. It is 16 bytes in size, because the data is stored outside of a row.
The net result of this is that:- conditions involving text fields are slower than a varchar would be, and many of the T-SQL functions cannot operate on text.
SQL Server supports max row size of 8192 (IIRC) bytes, and after some internal overhead this leaves 8060 (IIRC) bytes for user columns. Varchars max size is 8000 bytes. So you could not have two varchar(8000) columns in one table.
Incidentally, in your original post, you mention nvarchar. nvarchar is simply a wide character version of varchar, and as such takes twice the storage space of a varchar, thus explaining the max size of 4000 characters.
hope that clears some stuff up.
Signature space for rent. Apply by email to....
|
|
|
|
|
So what exactly is the difference between text and ntext then?
|
|
|
|
|
ntext is a wide character text.
Signature space for rent. Apply by email to....
|
|
|
|
|
Hello everyone,
When I install the MS SQLServer, I can choose the comparison method of the default instance whether is case sensitive. Can I change the character comparison method of a existed instance.
Thanks.
Kerry Chou (STU)
|
|
|
|
|
I don't know if you can change the entire DB to be case sensitive, but you can apply case sesitivity to a specific column or table.
Read up on collations in sql server documentation. But for now, have a look at this example:-
CREATE TABLE [CaseSense] (
[ColA] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
[ColB] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CS_AS NULL
) ON [PRIMARY]
insert into CaseSense values ('aaaa','aaaa')
insert into CaseSense values ('bbbb','BBBB')
--then
select * from casesense where cola=colb
-- will return the first record
select * from casesense where not cola=colb
-- will return the other one.
Signature space for rent. Apply by email to....
|
|
|
|
|
In SQL Server 2000 you can collate when selecting objects.
Example of case sensitive compare on case-insensitive database:
<br />
SELECT table1.name COLLATE SQL_Latin1_General_CP1_CS_AS <br />
FROM table1<br />
WHERE table1.name NOT IN<br />
(SELECT table2.name COLLATE SQL_Latin1_General_CP1_CS_AS FROM table2)<br />
|
|
|
|
|
ACCESS2000, ADO with VC++
I need to insert 500 records into a table. I am executing the following query for each record in a loop..
/////////////
PARAMETERS vID Short, vMRad IEEESingle, vQual Byte, vType Byte, vCRad IEEESingle, vWID Long;
INSERT INTO PostODData ( OID, MRad, Qual, Type, CRad, WID )
VALUES (vID, vMRad, vQual, vType, vCRad, vWID);
//////////////////
This insertion takes about 5 secs on a 400Mhz computer and I need to cut the time down to 1 sec or smaller.
Any suggestions how to improve. Is it possible to send the parameters for all 500 records in an array . Does it help ?
Thanks
|
|
|
|
|
|
You could use the XML version IXmlBulkLoad (?) found in one of the SqlXml releases for SQL Server 2000. It would give you a more "programmable" approach.
You could also use the native OLEDB interface (used by BCP ? ) IRowsetFastLoad. (look it up in the SQLOLEDB specs.)
Morty
|
|
|
|
|
I don't know about ADO but in DAO it was significantly faster to use a recordset's AddNew/Update methods to insert records than INSERT statement.
Pavel
Sonork 100.15206
|
|
|
|
|
1. How do I turn off the confirmation warnings when executing action queries
and deleting records from VBA code in MS Access 2000?
2. How do I call a VBA routine at startup of the MDB?
|
|
|
|
|
As the title. Thanks ahead.
I have some code like that:
SqlDataReader rd = ...
...
DataTime dt = ... rd["CreatedTime"] // How to CONVERT it to a DataTime?
|
|
|
|
|
KCII HKB wrote:
DataTime dt = ... rd["CreatedTime"] // How to CONVERT it to a DataTime?
It looks as if you are doing this in C#, however whatever language you are using you should look into the Convert class. You could do something like this.
DataTime dt = Convert.ToDateTime(rd["CreatedTime"]);
Nick Parker
The greatest lesson in life is to know that even fools are right sometimes. - Winston Churchill
|
|
|
|
|
ADO.NET in C++
SqlDataReader
I can get the following to work, the result displaying on the screen:
Console::WriteLine(myReader->GetSqlValue(3));
What I really need to do is to assign the item retrieved to a C++ variable eg.
int halfhrSQL = myReader->GetSqlValue(3);
This gives the C2440 compile error: cannot convert from 'System::Object __gc *' to 'int'.
No matter what cast I use, I cannot get around the error.
Please help if you can.
Thanks, Doug (New Zealand)
Doug
|
|
|
|
|
Doug wrote:
This gives the C2440 compile error: cannot convert from 'System::Object __gc *' to 'int'.
Have you tried to convert the object instead of casting it? You might look into the Convert.ToInt16 method as it will accept an object as a paramerter.
HTH
Nick Parker
The greatest lesson in life is to know that even fools are right sometimes. - Winston Churchill
|
|
|
|
|
Thanks Nick,
I have definitely got this working where the native C++ type is int and I am confident that I can now handle any numeric type.
What I am battling with is where the .NET value retrieved is a String (the SQL field is text).
I have managed to get the following to work:
String* busNumberSQL __gc[] = new String* __gc[97];
short halfhoursSQL = 0;
.
.
.
Console::WriteLine(myReader->GetString(0));
halfhoursSQL++;
busNumberSQL[halfhoursSQL] = Convert::ToString(myReader->GetValue(0));
However busNumberSQL is a managed array of type String*.
Now I need assign the value of busNumberSQL[halfhoursSQL] to a CString or native C++ string type. All I seem to end up with is the decimal representation of the hexidecimal memory address.
I have also tried:
Convert::ToChar(myReader->GetValue(0)) to assign one character
(Convert::ToString(myReader->GetValue(0)))->ToCharArray()
As I continue to try different things, do you have any idea how this may be done? I will let you know if I crack it.
(With the old ADO I was able to do this by doing a double cast: ---> _bstr_t --> LPCTSTR and then doing a simple assignment to a CString)
Thanks and regards
Doug
Doug
|
|
|
|
|
Doug wrote:
busNumberSQL[halfhoursSQL] = Convert::ToString(myReader->GetValue(0));
Can you do something like this:
busNumberSQL[halfhoursSQL] = myReader->get_Item("<code>field_name_here</code>")->ToString();
Nick Parker
The greatest lesson in life is to know that even fools are right sometimes. - Winston Churchill
|
|
|
|
|
Hi Nick
Re: ADO.NET : SqlDataReader : I need to assign retrieved value to C++ variable
I have found the source of the problem expressed in the thread "ADO.NET : SqlDataReader : I need to assign retrieved value to C++ variable":
http://www.codeproject.com/script/comments/forums.asp?forumid=3785#xx309754xx
but do not yet know why it occurs or how I can fix it.
The problem arises with the /clr compiler setting.
Here is the code to illustrate this:
/////////////////////////////////////////////////////////
#include <stdafx.h>
#include <cstdlib>
#include "try_ADONET.h"
//#using <mscorlib.dll>
//#using <system.dll>
//#using <system.data.dll> // This is required for the ADO.NET Provider
using namespace std;
//using namespace System;
int Main()
{
CString busNumberSQL[2];
CString bus = "Hello";
busNumberSQL[1] = bus;
return 0;
}
////////////////////////////////////////////////////////
Without the /clr setting the array of CString busNumberSQL[2] is correctly contructed as is CString bus and the line
busNumberSQL[1] = bus;
works as expected. This can be seen by putting a breakpoint in at
return 0;
and seeing the variables in a Watch. Copy the expanded Watch details (Name, Value & Type) to an EXCEL sheet.
..................
Next, add the compiler switch /clr and rerun the code.
CString bus is constructed correctly BUT something weird happens in the construction of CString busNumberSQL[2].
Now,
busNumberSQL[1] = bus;
results in the address of bus as an integer being assigned to busNumberSQL[1], and look at the Watch details!!
Copy the expanded Watch details (Name, Value & Type) to an EXCEL sheet
Name: busNumberSQL; Value: {Length=2}; Type: ATL::CStringT<char,strtraitmfc<char,atl::chtraitscrt<char> > >[] ...(Note no dimension at end compared to the "no /clr" case.
Next Watch line: [0] 2083454756 __int32
Next Watch line: [1] 3103464 __int32
My conclusion is that busNumberSQL[2] is not an array of CString.
Of course, I need the /clr in order to use ADO.NET!
It seems as though a CString works, but a CString array for some (unknown) cannot even be defined.
Do you have any light for me? (I have not got into your previous post yet on Marshall)
Best regards
Doug.
(In case you are wondering I am very much a beginner with C++ and .NET)
Doug
|
|
|
|
|
what's wrong, tell me plz.
f:\projects center\task history\mission 8_13\hydrosrv\debug\msado15.tlh(530) : error C2011: 'ParameterDirectionEnum' : 'enum' type redefinition
Extreme programming. Do the No.1
|
|
|
|
|
It looks like you are trying to #import the ADO stuff twice?
What does your #import line look like and where do you use it in your codd?
Michael
"I've died for a living in the movies and tv.
But the hardest thing I'll ever do is watch my leading ladies,
Kiss some other guy while I'm bandaging my knee."
-- The Unknown Stuntman
|
|
|
|
|
Hi !
I'm totally new to Transact SQL. I've just been trying to familiarise myself with the below problem and struggling with it for last 6 hours without break.
Here is the schema
HOTEL (Hotel_No, Hotel_Name, Address)
GUEST (Guest_No, Guest_Name, Address)
ROOM (Room_No, Hotel_No, Type, Price)
BOOKING (Hotel_No, Guest_No, Date_From, Date_To, Room_No)
Where it asks what is the total income, for the month of July 2001, from occupied rooms at all the Sheridan Hotels.
How can i implement this in SQL? Or does any one have idea or hints.
Here is my attempmpt at it
<br />
DECLARE @NumberOfDay int<br />
<br />
IF (SELECT Hotel_Name FROM HOTEL WHERE Hotel_Name = 'Sheridan Hotel' and Address = 'Melbourne') = 'Sheridan Hotel'<br />
BEGIN<br />
PRINT 'Sheridan Melbourne'<br />
SELECT * FROM HOTEL h1, BOOKING b1, ROOM r1<br />
WHERE h1.Hotel_Name = 'Sheridan Hotel' <br />
and Address = 'Melbourne'<br />
and b1.Date_From < '2001-Jul-01'<br />
and b1.Date_To in (Select DatePart(07,01)= true<br />
<br />
END<br />
ELSE<br />
PRINT 'Sheridan Sydney'<br />
<br />
I'm trying to program it in SQL in SQL Analyzer, but i have never experienced programming with this transact SQL before. In the first instance, it is like Pascal to me, (IF ..END IF, BEGIN)
|
|
|
|
|
This should help:
--Store the first and last day of the month (July 2001)
DECLARE @StartMonth smalldatetime, @EndMonth smalldatetime
SELECT
--First of the month
@StartMonth = CAST('1 July 2001' As smalldatetime),
--Day before the first of the next month
@EndMonth = DATEADD(day, -1, DATEADD(month, 1, @StartMonth))
SELECT
--Hotel details
HOTEL.Hotel_No,
HOTEL.Hotel_Name,
HOTEL.Address,
--Total income per hotel
SUM(Bookings.Income) As Income
FROM
HOTEL INNER JOIN
(
--Calculate the income as Room price (per night) * # nights
SELECT
CASE
--Booking started before the month
WHEN Date_From < @StartMonth THEN
DATEDIFF(day, @StartMonth, Date_To) * ROOM.Price
--Booking finished after the month
WHEN Date_To > @EndMonth THEN
DATEDIFF(day, Date_From, @EndMonth) * ROOM.Price
--Booking was entirely within the month
ELSE
DATEDIFF(day, Date_From, Date_To) * ROOM.Price
END As Income
FROM
BOOKING INNER JOIN ROOM
ON ROOM.Room_No = BOOKING.Room_No
WHERE
--Only want bookings that intersect the month
Date_From < @EndMonth
AND
Date_To > @StartMonth
)
As Bookings
WHERE
--Only want bookings for the Sheridan hotels
Hotel_Name = 'Sheridan Hotel'
GROUP BY
HOTEL.Hotel_No,
HOTEL.Hotel_Name,
HOTEL.Address
|
|
|
|
|
Hi!
Your answer has resolved and swept away alot of mysterious things that were in my head! I highly appreciate your it.
Thank you very much
Regard,
Ps: it gives me error say near the "WHERE" keyword, but i am still attempting to fix it!
Server: Msg 156, Level 15, State 1, Line 48
Incorrect syntax near the keyword 'WHERE'.
|
|
|
|
|
Whoops!
--Store the first and last day of the month (July 2001)
DECLARE @StartMonth smalldatetime, @EndMonth smalldatetime
SELECT
--First of the month
@StartMonth = CAST('1 July 2001' As smalldatetime),
--Day before the first of the next month
@EndMonth = DATEADD(day, -1, DATEADD(month, 1, @StartMonth))
SELECT
--Hotel details
HOTEL.Hotel_No,
HOTEL.Hotel_Name,
HOTEL.Address,
--Total income per hotel
SUM(Bookings.Income) As Income
FROM
HOTEL INNER JOIN
(
--Calculate the income as Room price (per night) * # nights
SELECT
ROOM.Hotel_No,
CASE
--Booking started before the month
WHEN Date_From < @StartMonth THEN
DATEDIFF(day, @StartMonth, Date_To) * ROOM.Price
--Booking finished after the month
WHEN Date_To > @EndMonth THEN
DATEDIFF(day, Date_From, @EndMonth) * ROOM.Price
--Booking was entirely within the month
ELSE
DATEDIFF(day, Date_From, Date_To) * ROOM.Price
END As Income
FROM
BOOKING INNER JOIN ROOM
ON ROOM.Room_No = BOOKING.Room_No
WHERE
--Only want bookings that intersect the month
Date_From < @EndMonth
AND
Date_To > @StartMonth
)
As Bookings
ON Bookings.Hotel_No = HOTEL.Hotel_No
WHERE
--Only want bookings for the Sheridan hotels
Hotel_Name = 'Sheridan Hotel'
GROUP BY
HOTEL.Hotel_No,
HOTEL.Hotel_Name,
HOTEL.Address
|
|
|
|
|