Database Discussion Boards

Re: Table Variables as Stored Procedure Parameters

Morten Abrahamsen22-Oct-02 2:14

22-Oct-02 2:14

As you have correctly pointed out. SQL Server 2000 doesn't allow table variables as input parameters in stored procedures.

What you could use instead is the "politically correct" xml document. Pass an xml document (array... ) to the SQL Server, and use the OPENXML statement to process it.

Smile | :)

Morty

SQL equivalent of Access Memo feild

Ray Cassick15-Oct-02 2:19

Ray Cassick

15-Oct-02 2:19

.. anyone know what it is?

I thought it was nvarchar but I cant get it to go higher than 4000.

Re: SQL equivalent of Access Memo feild

Paul Watson15-Oct-02 3:17

Paul Watson

15-Oct-02 3:17

Ray Cassick wrote:
.. anyone know what it is?

I thought it was nvarchar but I cant get it to go higher than 4000.

Text.

But be warned the Text data type can be an utter pain in the neck. My recommendation is to put it as the last field in a table and then sacrifice a virgin to the SQL god on the slopes of Mount Kiloquery.

Also it is a pain to edit a Text field within QA or Enterprise Manager. Generally Text fields suck IMO.

Paul Watson
Bluegrass
Cape Town, South Africa

Re: SQL equivalent of Access Memo feild

Jon Hulatt15-Oct-02 3:59

Jon Hulatt

15-Oct-02 3:59

Paul Watson wrote:
Also it is a pain to edit a Text field within QA or Enterprise Manager. Generally Text fields suck IMO

How so? other than the fact that it potentially contains a lot of data?

Signature space for rent. Apply by email to....

Re: SQL equivalent of Access Memo feild

Paul Watson15-Oct-02 4:51

Paul Watson

15-Oct-02 4:51

Jon Hulatt wrote:
How so? other than the fact that it potentially contains a lot of data?

Well hopefully you can prove me wrong but when in Enterprise Manager it cuts off the value after a certain length in the display (the data is still there naturally, just that it does not show it all. Copy and paste also does not work.) Same with QA, it returns a delimited set of that field.

The only way I can ever edit those text fields and retain a lot of data is by using some ASP and a recordset to input and output. Is there a better way?

Paul Watson
Bluegrass
Cape Town, South Africa

Re: SQL equivalent of Access Memo feild

Jon Hulatt15-Oct-02 5:07

Jon Hulatt

15-Oct-02 5:07

Tools -> Options -> Results -> Maximum Characters per column. I was chuffed when i found that too.

Signature space for rent. Apply by email to....

Re: SQL equivalent of Access Memo feild

Ray Cassick15-Oct-02 4:35

Ray Cassick

15-Oct-02 4:35

I had looked at the text type, but was confused because 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.

I am looking at writing an ASP.NET message area using SQL and need a data type for the messages. I know that 8000 would probably be ok, but.....

Re: SQL equivalent of Access Memo feild

Paul Watson15-Oct-02 4:57

Paul Watson

15-Oct-02 4:57

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 Smile | :)

Paul Watson
Bluegrass
Cape Town, South Africa

Re: SQL equivalent of Access Memo feild

Jon Hulatt15-Oct-02 5:12

Jon Hulatt

15-Oct-02 5:12

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....

Re: SQL equivalent of Access Memo feild

William Forney15-Oct-02 6:25

William Forney

15-Oct-02 6:25

So what exactly is the difference between text and ntext then? Smile | :)

Re: SQL equivalent of Access Memo feild

Jon Hulatt15-Oct-02 6:35

Jon Hulatt

15-Oct-02 6:35

ntext is a wide character text.

Signature space for rent. Apply by email to....

Case Sensitive Comparison Method

Kerry Chou14-Oct-02 16:07

Kerry Chou

14-Oct-02 16:07

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)

Re: Case Sensitive Comparison Method

Jon Hulatt14-Oct-02 22:03

Jon Hulatt

14-Oct-02 22:03

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....

Re: Case Sensitive Comparison Method

Rein Hillmann14-Oct-02 22:37

Rein Hillmann

14-Oct-02 22:37

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 />

Fast Insert ?

ssirisha14-Oct-02 9:00

ssirisha

14-Oct-02 9:00

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

Re: Fast Insert ?

Daniel Turini15-Oct-02 1:53

Daniel Turini

15-Oct-02 1:53

ssirisha wrote:

This insertion takes about 5 secs on a 400Mhz computer and I need to cut the time down to 1 sec or smaller.

Use SQL Server and BCP utility.
Sorry, I don't know other way...

My latest articles:
Desktop Bob - Instant CP notifications
XOR tricks for RAID data protection

Re: Fast Insert ?

Morten Abrahamsen22-Oct-02 2:19

Morten Abrahamsen

22-Oct-02 2:19

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

Re: Fast Insert ?

Pavel Klocek16-Oct-02 8:21

Pavel Klocek

16-Oct-02 8:21

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

Access Setting Options Programactically

Hyien14-Oct-02 5:16

Hyien

14-Oct-02 5:16

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?

How to Convert object that selected from SQL Sever to a DataTime?

14-Oct-02 2:25

As the title. Thanks ahead. Confused | :confused:

I have some code like that:
SqlDataReader rd = ...

...

DataTime dt = ... rd["CreatedTime"] // How to CONVERT it to a DataTime?

Re: How to Convert object that selected from SQL Sever to a DataTime?

Nick Parker14-Oct-02 2:31

Nick Parker

14-Oct-02 2:31

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

SqlDataReader : I need to assign retrieved value to C++ variable

Doug13-Oct-02 13:27

Doug

13-Oct-02 13:27

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

Re: SqlDataReader : I need to assign retrieved value to C++ variable

Nick Parker14-Oct-02 2:37

Nick Parker

14-Oct-02 2:37

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

Re: SqlDataReader : I need to assign retrieved value to C++ variable

Doug15-Oct-02 12:15

Doug

15-Oct-02 12:15

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

Re: SqlDataReader : I need to assign retrieved value to C++ variable

Nick Parker15-Oct-02 16:38

Nick Parker

15-Oct-02 16:38

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

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.