Database Discussion Boards

SQL Server 2000 "length"

Steve McLenithan10-Mar-03 7:42

10-Mar-03 7:42

What exactly is the meaning of the integers in the length column when designing a table. I notice that int is 4 and can't be changed. nvarchar is default to 50 and can be changed. What is the meaning of the numbers? Number of characters?, bits?

Confused | :confused:

Any website or explanation would be great.

$TeVe McLeNiThAn

Re: SQL Server 2000 "length"

Bruce Duncan10-Mar-03 7:47

Bruce Duncan

10-Mar-03 7:47

I think it is the maximum byte size of the field.

Bruce Duncan, CP#9088, CPUA 0xA1EE, Sonork 100.10030
Blackadder: Baldrick, have you no idea what irony is?
Baldrick: Yeah, it's like goldy and bronzy only it's made of iron.

Re: SQL Server 2000 "length"

Steve McLenithan10-Mar-03 7:50

Steve McLenithan

10-Mar-03 7:50

Bruce Duncan wrote:
maximum byte size

Ok cool. I thought that's what it might be.

How would I determine the maximum amount of characters that can be stored in an nvarchar field, say of length 64?

Thanks Smile | :)

$TeVe McLeNiThAn

Re: SQL Server 2000 "length"

Bruce Duncan10-Mar-03 8:00

Bruce Duncan

10-Mar-03 8:00

A thought crossed my mind just as I posted my previous answer...

In Enterprise Manager, when you have an nvarchar type, with a length of 50, it means it can store 50 unicode characters, and therefore take up a maximum of 100 bytes. Hence, my previous answer is not entirely correct.

To answer your new question, an nvarchar field of length 64 can hold up to 64 unicode character, with a byte size of up to 128 (+ minimal overhead I guess)

Bruce Duncan, CP#9088, CPUA 0xA1EE, Sonork 100.10030
Blackadder: Baldrick, have you no idea what irony is?
Baldrick: Yeah, it's like goldy and bronzy only it's made of iron.

Re: SQL Server 2000 "length"

Steve McLenithan10-Mar-03 8:02

Steve McLenithan

10-Mar-03 8:02

Thanks. That helps.... I didn't want to make fields too short nor too big;)

$TeVe McLeNiThAn

Re: SQL Server 2000 "length"

Bruce Duncan10-Mar-03 8:12

Bruce Duncan

10-Mar-03 8:12

Cheers, glad to help Smile | :)

Bruce Duncan, CP#9088, CPUA 0xA1EE, Sonork 100.10030
Blackadder: Baldrick, have you no idea what irony is?
Baldrick: Yeah, it's like goldy and bronzy only it's made of iron.

Build dynamic query within a stored procedure?

Duncan Edwards Jones10-Mar-03 6:45

Duncan Edwards Jones

10-Mar-03 6:45

I have a situation whereby I need to modify a base query with an unknown number of additional restraints which are themselves held in a reference table. How would I go about this?

For example the reference table might hold:

"user_name <> 'default'"
"policy_no > 20000"

etc.

and my stored proc needs to add these conditions to its select operation. Ideas?

'--8<------------------------
Ex. Datis:
Duncan Jones
http://www.merrioncomputing.com

Re: Build dynamic query within a stored procedure?

Bruce Duncan10-Mar-03 7:43

Bruce Duncan

10-Mar-03 7:43

I'm assuming you're using SQL Server.

Look up the sp_executesql system stored proc or the EXECUTE ( ) statement, both will let you execute a statement contained within a varchar variable.

Bruce Duncan, CP#9088, CPUA 0xA1EE, Sonork 100.10030
Blackadder: Baldrick, have you no idea what irony is?
Baldrick: Yeah, it's like goldy and bronzy only it's made of iron.

Re: Build dynamic query within a stored procedure?

Duncan Edwards Jones10-Mar-03 8:27

Duncan Edwards Jones

10-Mar-03 8:27

I'm using Sybase XI - but I'm sure there's an equivalent somewhere - but that'd mean I'd have to assemple the entire query as a varchar().

'--8<------------------------
Ex. Datis:
Duncan Jones
http://www.merrioncomputing.com

Re: Build dynamic query within a stored procedure?

Bruce Duncan10-Mar-03 8:42

Bruce Duncan

10-Mar-03 8:42

Sorry, don't know anything about Sybase, but I would imagine it has something similar.

Maybe this is what you're looking for ?

How can I execute dynamic SQL with ASE?[^]

Bruce Duncan, CP#9088, CPUA 0xA1EE, Sonork 100.10030
Blackadder: Baldrick, have you no idea what irony is?
Baldrick: Yeah, it's like goldy and bronzy only it's made of iron.

that dang datagrid...

stephen.hazel8-Mar-03 11:43

stephen.hazel

8-Mar-03 11:43

Okay... c# winforms ado.net

Can i size up columns using the resource editor thing?
Ahead of time before the app starts and forces the user
to resize all the columns each time?

And how do I hide a column?

Thanks.

...Steve

SQL server call back our application

Shah Shehpori7-Mar-03 22:22

Shah Shehpori

7-Mar-03 22:22

Dear friends,

I have an application developed in VC++ and it makes an active connection to SQL server using ADO. My application issues queries and in return SQL server sends me results.

Is it possible that SQL server 'talk to my application' i.e what i want is that whenever any row of a table is changed in SQL server by some other entity, SQL server inform my application of this change. Is it possible??

Re: SQL server call back our application

Hesham Amin8-Mar-03 11:03

Hesham Amin

8-Mar-03 11:03

Hi..
Seems you need to create a trigger in SQL Server that call an Extended Stored Procedure (made in vc++ for example)
this Stored Procedure can store a shared variable that your Application checks for it..

You can create a worker thread that is blocked until this variable changes and unlocked by the Extended Stored Procedure...

Anyway .. I love the idea of making SQL server talks to your Application..

tell us about any progress Smile | :)

this freakin adapter dealy???

stephen.hazel7-Mar-03 7:08

stephen.hazel

7-Mar-03 7:08

I'm new to c#, winforms and ado.net, so bear with me.

I've got a dumb lil checkbook table. I don't WANT to have an identity
column for a primary key, but i do to help out ado.net...

So I get SqlConnection, DataSet, and SqlDataAdapter's Fill/Update.
I haven't really messed much with the DataGrid, but got somethin goin on.
And I need help with the properties of this frassin brassin adapter.

I've been thru the adapter wizard, but hate the mess it leaves.

I know about the commandbuilder a bit, but don't know when it's needed
and when not.

1) c# to hide the dang identity column in my datagrid is?

2) can I lose the identity column? implications are?

3) What's the deal with the SqlDataAdapter properties???
I guess tackin on a CommandBuilder is easiest, but you gotta have
a single primary key, right?
Otherwise, what do I do to fill in the sel/ins/upd/del command props?

Thanks VERY much for ANY help Rose | [Rose]

...Steve

Determining SQL Datatypes

AustinTom7-Mar-03 3:52

AustinTom

7-Mar-03 3:52

Hi folks,

From a dataset (generated by Data.SqlClient rather than Data.OleDb) can I determine the original SQL Datatype? I am trying to determine if a field is Currency vs. a straight decimal. It seems that the "datatype" property of the datacolumn object return decimal for both SQl currency and SQL decimal.

Thanks

Tom K.

Help: Save data back to MS Access DB

DionChen6-Mar-03 10:55

DionChen

6-Mar-03 10:55

Hi, all,

I am very close to finish my first .NET application( C#, Winform Datagrid, and MS Access database). I can modify all the data in my DataSet on my winform datagrid. NowI am trying for the first time to save the data back to my Access DB. When I call daMyDataAdaptor.Update( for a single table), I got this attached error. It says the UPDATE statement has syntax error, but the Statement was created by the wizard when I configured DataAdaptor, how can it be wrong? I know this must be something I missed, Please help.

Due to size limitation of the attachment, I can not attach the small test program. If you can help me, I can sent you the zip test program.

Code on Save button as follows:
------------------------------------

if ( connMain.State != ConnectionState.Open )
{
connMain.Open();
}

System.Data.OleDb.OleDbTransaction trans = connMain.BeginTransaction();

daTaskList.UpdateCommand.Transaction = trans;

try
{
daTaskList.Update(dsMain1, "LMTaskList");

//System.Exception MyErr = new System.Exception("Error Occurred");
//throw(MyErr);

trans.Commit();
}

catch(System.Exception err)
{
trans.Rollback();
MessageBox.Show("Failed to save Available Tasks!\n" + "Details: " + err.ToString() );
}

finally
{
connMain.Close();
}
-----------------------------------------------------

I wonder what is wrong with the code I have.

Thanks again.

Dion

Re: Help: Save data back to MS Access DB

andyharman6-Mar-03 19:57

andyharman

6-Mar-03 19:57

What was the error message that you got?

What SQL Insert/Update/Delete statements were you using?

Andy Harman

Re: Help: Save data back to MS Access DB

DionChen7-Mar-03 6:38

DionChen

7-Mar-03 6:38

After about a day, I finally figured out what is wrong. In my DB table, I have a column called "Value". It caused the crash.

Thanks anyway.

Dion

Text Changed in WinForm Datagrid cell

DionChen6-Mar-03 9:49

DionChen

6-Mar-03 9:49

Hi,

I am developing a simple WinForm app with datagrid on the form. I want to know when any cell text changed so that I can enable my Save button. What event should I handle? Any example will be even better. Thanks.

Dion

Is it possible to set a adBoolean Column to AdColNullable ?

BrunoDelille6-Mar-03 3:40

BrunoDelille

6-Mar-03 3:40

I'm creating a simple Access Database with one table and one boolean field via ADOX and Jet Oledb Provider.

The following code gives me an IDispatch Error 3105 when appending the Table. Note that with adInteger or adVarWChar the code is working. Any idea ? Thx in advance.

pThisAdoCatalogPtr.CreateInstance(__uuidof(Catalog));
pThisAdoTablePtr.CreateInstance(__uuidof(Table));
pThisAdoColumnPtr.CreateInstance(__uuidof(Column));

pThisAdoCatalogPtr->Create("PROVIDER=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Engine Type=4;Jet OLEDB:System Database=c:\\MsOffice\\Access\\System.mdw;Data Source=mybase.mdb;User Id=admin;Jet OLEDB:Database Password=mypass;");

pThisAdoTablePtr->Name="MyTable";

pThisAdoColumnPtr->Name="BooleanColumn";
pThisAdoColumnPtr->Type=adBoolean;
pThisAdoColumnPtr->Attributes=adColNullable;

pThisAdoTablePtr->Columns->Append(_variant_t((IDispatch *)pThisAdoColumnPtr), adBoolean, 0);

pThisAdoCatalogPtr->Tables->Append(_variant_t((IDispatch *)pThisAdoTablePtr)); <--- Here is the exception thrown.

Bruno.

How to display full column length in SQL

IrishSonic5-Mar-03 23:54

IrishSonic

5-Mar-03 23:54

Hiya I have an MFC app that writes data to my SQL database. The columns lenghts are set to 30. But when I open my SQL database to view the contents, I can only see the first 15 chars of each column..

How do I get the SQL database to display the full columns lenghts when it is opened??

Thanks,
grahamoj.

Re: How to display full column length in SQL

Nick Parker6-Mar-03 3:09

Nick Parker

6-Mar-03 3:09

Are you passing spaces at the end of the values at all, is it 30 characters of data, could you be trimming the values?

-Nick Parker

Re: How to display full column length in SQL

Jon Hulatt6-Mar-03 3:38

Jon Hulatt

6-Mar-03 3:38

in query analyser, tools menu, options, results tab, maximum output characters per column.

jon

#include <beer.h>

Re: How to display full column length in SQL

IrishSonic6-Mar-03 11:13

IrishSonic

6-Mar-03 11:13

Hiya thanks, have checked that but it is already set to 256 chars.
What I do is write the info to the database and then using Enterprise Manager, go in to the table to view it. All of the string is in the column. I can see it when I expand the column.

What I want to be able to do is when I open the table in Enterprise Manager, I want the column already expanded to the full lenght of the string...

Any ideas,
grahamoj

Introduction of parameter problem!!

Andrea Ferraro5-Mar-03 23:34

Andrea Ferraro

5-Mar-03 23:34

Hy guys,
I have a question relative to passing parameters of Class Accessor:
Below an extract of my code:

string sqlcmd;
sqlcmd = "UPDATE FileLocation \
SET LastAccessTime = CONVERT(DateTime,???????,121), \
LastUpdateTime = CONVERT(DateTime,??????,121) \
WHERE ([Index] = 1)";

// if(FAILED(ThedboSPSelectAccessor.Open(DBSession, _T("{ ? = CALL dbo.SPdboTWOModifyTime;1 (?,?) }"))))
if(FAILED(ThedboSPSelectAccessor.Open(DBSession, sqlcmd.c_str() ) ) )

I tried to using the calling of store procedure and all it's OK,after I tried to use a sql command but I don't know like to pass the two parameters (indicated as ?????), I tried using ? or (?) or '?' but noone is correct... Confused | :confused:

Can someone help me???
Thanks in advance.

Andrea Smile | :)

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