Appropriate Data Type

I want to save some serialized data in a SQL Server 2005 table. What is the appropriate field type for this? I won't be associating a schema with the data. The data could vary in length--even up to several MB. I'm not sure whether to use text, NVARChar(MAX) or XML.

Brian

Brian,

A couple of SQL Server 2005 MSDN aritcles talk about when to use XML and some of the best practices. Some relevant ones are "XML Best Practices for Microsoft SQL Server 2005"; "XML Options in Microsoft SQL Server 2005"; "Performance Optimizations for the XML Data Type in SQL Server 2005".

In short XML is very useful 1) when you want schema validation (may not apply to your case); 2) when you want to query into the XML data or update granular parts of it. If your data is in XML format but your application merely uses the database to store and retrieve the data, an (n)varchar (max) column might suffice.

As a side note: Use varchar and nvarchar datatypes instead of text and ntext as the latter are in deprecation path (Deprecated Database Engine Features in SQL Server 2005)

Thanks

Babu

Apply XSLT to an xml field in SQL Server 2005

Hi all,
Is it possible to apply an xslt template to an xml field in SQL Server 2005
by means of T-SQL? Or should I create a .NET assembly to perform this task?
Thanks in advance,
Alberto.Hello Alberto,

> Is it possible to apply an xslt template to an xml field in SQL Server
> 2005 by means of T-SQL? Or should I create a .NET assembly to perform
> this task?
Here you go: http://www.sqljunkies.com/WebLog/kt...l
t.aspx
Thanks,
Kent Tegels, DevelopMentor
http://staff.develop.com/ktegels/|||Thanks Kent.
Not only you answer my question, but also you give a good example.

apply unique constraint in MS

I want to apply a unique constraint to an existing field in the database.
The constraints dialog box asks for a "constraint expression". I have went
through bol and google without finding a solution. I know how to create a
column with a unique constraint but no where can I find how to write the
"expression" to make an existing column unique in Management Studio.
How?
Thanks,
TCREATE TABLE Banana (Peal char(5) NOT NULL);
ALTER TABLE Banana
ADD Constraint Demonstrate UNIQUE(Peal);
INSERT Banana values('a')
INSERT Banana values('b')
INSERT Banana values('b')
(1 row(s) affected)
(1 row(s) affected)
Server: Msg 2627, Level 14, State 1, Line 3
Violation of UNIQUE KEY constraint 'Demonstrate'. Cannot insert
duplicate key in object 'dbo.Banana'.
The statement has been terminated.
Roy Harvey
Beacon Falls, CT
On Tue, 13 Nov 2007 17:06:59 -0700, "Tina"
<TinaMSeaburn@.nospamexcite.com> wrote:

>I want to apply a unique constraint to an existing field in the database.
>The constraints dialog box asks for a "constraint expression". I have went
>through bol and google without finding a solution. I know how to create a
>column with a unique constraint but no where can I find how to write the
>"expression" to make an existing column unique in Management Studio.
>How?
>Thanks,
>T
>

apply unique constraint in MS

I want to apply a unique constraint to an existing field in the database.
The constraints dialog box asks for a "constraint expression". I have went
through bol and google without finding a solution. I know how to create a
column with a unique constraint but no where can I find how to write the
"expression" to make an existing column unique in Management Studio.
How?
Thanks,
T
CREATE TABLE Banana (Peal char(5) NOT NULL);
ALTER TABLE Banana
ADD Constraint Demonstrate UNIQUE(Peal);
INSERT Banana values('a')
INSERT Banana values('b')
INSERT Banana values('b')
(1 row(s) affected)
(1 row(s) affected)
Server: Msg 2627, Level 14, State 1, Line 3
Violation of UNIQUE KEY constraint 'Demonstrate'. Cannot insert
duplicate key in object 'dbo.Banana'.
The statement has been terminated.
Roy Harvey
Beacon Falls, CT
On Tue, 13 Nov 2007 17:06:59 -0700, "Tina"
<TinaMSeaburn@.nospamexcite.com> wrote:

>I want to apply a unique constraint to an existing field in the database.
>The constraints dialog box asks for a "constraint expression". I have went
>through bol and google without finding a solution. I know how to create a
>column with a unique constraint but no where can I find how to write the
>"expression" to make an existing column unique in Management Studio.
>How?
>Thanks,
>T
>

apply unique constraint in MS

I want to apply a unique constraint to an existing field in the database.
The constraints dialog box asks for a "constraint expression". I have went
through bol and google without finding a solution. I know how to create a
column with a unique constraint but no where can I find how to write the
"expression" to make an existing column unique in Management Studio.
How?
Thanks,
TCREATE TABLE Banana (Peal char(5) NOT NULL);
ALTER TABLE Banana
ADD Constraint Demonstrate UNIQUE(Peal);
INSERT Banana values('a')
INSERT Banana values('b')
INSERT Banana values('b')
(1 row(s) affected)
(1 row(s) affected)
Server: Msg 2627, Level 14, State 1, Line 3
Violation of UNIQUE KEY constraint 'Demonstrate'. Cannot insert
duplicate key in object 'dbo.Banana'.
The statement has been terminated.
Roy Harvey
Beacon Falls, CT
On Tue, 13 Nov 2007 17:06:59 -0700, "Tina"
<TinaMSeaburn@.nospamexcite.com> wrote:
>I want to apply a unique constraint to an existing field in the database.
>The constraints dialog box asks for a "constraint expression". I have went
>through bol and google without finding a solution. I know how to create a
>column with a unique constraint but no where can I find how to write the
>"expression" to make an existing column unique in Management Studio.
>How?
>Thanks,
>T
>

Appending to a text field using UPDATE

I would like to update a field that already has data in it and I dont' want to overwrite the existing text. Here is my existing statement

UPDATE wr SET cf_notes = " + tmp_array(24) + " WHERE wr_id = " + data_temp(0)

I would like to add cf_notes + tmp_array(24) to cf_notes. Is this possible in SQL? If so, what is the correct syntax. I have tried 6 different statements and I get a compile error on every statement.

Thanks,

SBRcreate procedure spU_update_table_with_text (
@.key_field_value int,
@.text text = null)
as
declare @.txtptr binary(16), @.insert_offset int
select @.txtptr = textptr(text_field),
@.insert_offset = datalength(text_field) + 1 --or 2 if a space is needed
from your_table
where key_field = @.key_field_value
updatetext your_table.text_field @.insert_offset 0 @.text
return

Appending to a Text field

Hi Guys
I am trying to append to a text (text data type) field in my database.
Simply put, the Customer table has a field called Notes. I want to append
some extra data to this field via TSQL. So for a Customer who has an ID of
1234, how do I append some extra text to his Notes field? The help in Books
on Line has me completely baffled.
Thank youWhich helo are you referring to? Have you looked at WRITETEXT/UPDATETEXT?
ML
http://milambda.blogspot.com/

Appending to a record

I have created a Table that contains a WorksheetID field and a SalesNotes field. I can successfully populate that table but I am uncertain as to how I can append to the SalesNotes record.

I want to keep the orginal notes but populate addtional notes.

Would I do this by using the UPDATE call?

Many Thanks

T

Hi,

you explanation is a bit unclear, but if you want to fill the table with additional data which can′t be filled during the initial load, you can sure use an update statement to modify the exisiting records.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Appending to a record

I have created a Table that contains a WorksheetID field and a SalesNotes field. I can successfully populate that table but I am uncertain as to how I can append to the SalesNotes record.

I want to keep the orginal notes but populate addtional notes.

Would I do this by using the UPDATE call?

Many Thanks

T

Hi,

you explanation is a bit unclear, but if you want to fill the table with additional data which can′t be filled during the initial load, you can sure use an update statement to modify the exisiting records.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Appending Text to a SQL Text Data Type

I am trying to use a cursor to create a mass Text field with the results fro
m
the selections from a series of VarChar(8000) values. I know I need to use
UpdateText, but it only seems to store the 1st one it reads. Can anyone hel
p?
Here's my text:
Declare @.TriggerText nVarChar(4000)
Declare @.ptrval Binary(16)
Declare @.Offset Int
-- Create temporary table to hold Text field
Create Table #tempTrigger
(TextField Text NULL)
Insert Into #tempTrigger Select ''
-- Get Trigger "basis"
Declare curTriggerBasis Insensitive Cursor For
Select c.Text
From sysObjects o (nolock)
Inner Join sysComments c (nolock)
On o.ID = c.ID
Where o.Name = 'cttx_Customer'
Order By ColID
For Read Only
Open curTriggerBasis
Fetch Next From curTriggerBasis Into @.TriggerText
While @.@.Fetch_Status = 0
Begin
Select @.ptrval = TEXTPTR(TextField),
@.Offset = DataLength(TextField)
From #tempTrigger (nolock)
UpdateText #tempTrigger.TextField @.ptrval @.Offset 0 @.TriggerText
Fetch Next From curTriggerBasis Into @.TriggerText
End
Close curTriggerBasis
Deallocate curTriggerBasis
Select * From #tempTrigger (nolock)> but it only seems to store the 1st one it reads
How are you determining this? What does 'SELECT DATALENGTH(TextField) FROM
#tempTrigger' return?
Happy Holidays
Dan Guzman
SQL Server MVP
"bobnunny" <u17151@.uwe> wrote in message news:59ac1c8e96b9c@.uwe...
>I am trying to use a cursor to create a mass Text field with the results
>from
> the selections from a series of VarChar(8000) values. I know I need to
> use
> UpdateText, but it only seems to store the 1st one it reads. Can anyone
> help?
> Here's my text:
> Declare @.TriggerText nVarChar(4000)
> Declare @.ptrval Binary(16)
> Declare @.Offset Int
> -- Create temporary table to hold Text field
> Create Table #tempTrigger
> (TextField Text NULL)
> Insert Into #tempTrigger Select ''
> -- Get Trigger "basis"
> Declare curTriggerBasis Insensitive Cursor For
> Select c.Text
> From sysObjects o (nolock)
> Inner Join sysComments c (nolock)
> On o.ID = c.ID
> Where o.Name = 'cttx_Customer'
> Order By ColID
> For Read Only
> Open curTriggerBasis
> Fetch Next From curTriggerBasis Into @.TriggerText
> While @.@.Fetch_Status = 0
> Begin
> Select @.ptrval = TEXTPTR(TextField),
> @.Offset = DataLength(TextField)
> From #tempTrigger (nolock)
> UpdateText #tempTrigger.TextField @.ptrval @.Offset 0 @.TriggerText
> Fetch Next From curTriggerBasis Into @.TriggerText
> End
> Close curTriggerBasis
> Deallocate curTriggerBasis
> Select * From #tempTrigger (nolock)|||I've put Print statements in there to check this out. It shows Datalength a
s
4000 everytime except the last one. BUT, like an idiot I was checking the
loop so hard, but the Select statement at the end will only return the first
4000. Once I changed that to DataLength, it showed it had it all.
Thanx!
Dan Guzman wrote:
>How are you determining this? What does 'SELECT DATALENGTH(TextField) FRO
M
>#tempTrigger' return?
>
>[quoted text clipped - 36 lines]

Appending a Field

I know this must be simple, but I am stumpted, please help!

I am writing a stored procedure in SQL 2000 where an incomming variable is a string of characters (a couple of sentences) and I want to add that to the existing string of characters in a table field called "Comments".

I do not know how to append the text in a field. How is that best done?

The basic function of the procedure is to take whatever string is passed to it and append it to the current contents of the field "Comments". As the procedure is ran over and over again, the field is constantly appended with the incomming text.

What is the best way to do this? Can anyone give me an example?This one should be quite easy:

UPDATE table SET Filed = Field + @.Value WHERE ID = @.ID|||Thank you very much!

It is working now.

Appending a Field

I know this must be simple, but I am stumpped, please help!

I am writing a stored procedure in SQL 2000 where an incomming variable is a string of characters (a couple of sentences) and I want to add that to the existing string of characters in a table field called "Comments".

I do not know how to append the text in a field. How is that best done?

The basic function of the procedure is to take whatever string is passed to it and append it to the current contents of the field "Comments". As the procedure is ran over and over again, the field is constantly appended with the incomming text.

What is the best way to do this? Can anyone give me an example?update tablename set fieldname = fieldname + @.incomingtext where condition ...|||Also, if you haven't already, you may want to look at the data type of the "comments" field and make sure when you are appending the next text the maximum length for that data type is not being exceeded.|||Originally posted by Donner
Also, if you haven't already, you may want to look at the data type of the "comments" field and make sure when you are appending the next text the maximum length for that data type is not being exceeded.

create table test(id int identity,code varchar(50))
go
insert test(code) values('a')
go
update test set code=code+'b' where datalength(code+'b')<51
go
select * from test

AppendChunk uses a lot of memory

I try to upload a big file to a binary field. Because it is very big, I use
AppendChunk.
But it just takes as much memory as before.
_variant_t bigarray;
// set bigarray to 5 Mega bytes
while(true){
read next part of file into bigarrary;
Recordset->Fields->Item["ImageField"].AppendChunk(bigarrary);
}
Recordset->updata();
If the file is 200 Mega bytes, after call the update, the memory usage of
the process will go up to more than 600Mega bytes.
And then after some time, the update failed with time-out.
Can anyone help? Or advise me another way to do this?
Thanks
Eason
eason@.hotmail.comHi
At a guess you are continually looping.
Also check out:
http://support.microsoft.com/defaul...kb;en-us;153238
John
"Eason" wrote:

> I try to upload a big file to a binary field. Because it is very big, I us
e
> AppendChunk.
> But it just takes as much memory as before.
> _variant_t bigarray;
> // set bigarray to 5 Mega bytes
> while(true){
> read next part of file into bigarrary;
> Recordset->Fields->Item["ImageField"].AppendChunk(bigarrary);
> }
> Recordset->updata();
> If the file is 200 Mega bytes, after call the update, the memory usage of
> the process will go up to more than 600Mega bytes.
> And then after some time, the update failed with time-out.
> Can anyone help? Or advise me another way to do this?
> Thanks
> Eason
> eason@.hotmail.com
>|||Thanks for your response. But the loop is fine, not a dead loop.
The real code just generates some data and exits the loop after some
AppendChunk.
I tried a middle size file (10MByte), it works fine.
Then I tried a big file (100MByte), it ran a long time using more than
600Mbyte virtual memory after finishing all AppendChunk. Then called update,
the memory went higher and higher and gave an error time out.
Here is my code:
======================================
// This is the main project file for VC++ application project
// generated using an Application Wizard.
#include "stdafx.h"
#import "D:\Program Files\Common Files\System\ADO\mo15.dll" \
no_namespace rename("EOF", "EndOfFile")
#define ChunkSize 1024*1024
#include <ole2.h>
#include <stdio.h>
#include "conio.h"
#include "malloc.h"
_ConnectionPtr pConnection;
///////////////////////////////////////////////////////////
// //
// PrintProviderError Function //
// //
///////////////////////////////////////////////////////////
VOID PrintProviderError(_ConnectionPtr pConnection)
{
// Print Provider Errors from Connection object.
// pErr is a record object in the Connection's Error collection.
ErrorPtr pErr = NULL;
long nCount = 0;
long i = 0;
if( (pConnection->Errors->Count) > 0)
{
nCount = pConnection->Errors->Count;
// Collection ranges from 0 to nCount -1.
for(i = 0; i < nCount; i++)
{
pErr = pConnection->Errors->GetItem(i);
printf("\t Error number: %x\t%s", pErr->Number,(LPCSTR)
pErr->Description);
}
}
}
///////////////////////////////////////////////////////////
// //
// AppendChunkX Function //
// //
///////////////////////////////////////////////////////////
int AppendChunkX(VOID)
{
// Define ADO object pointers.
// Initialize pointers on define.
// These are in the ADODB:: namespace.
_RecordsetPtr pRstPubInfo = NULL;
_ConnectionPtr pConnection = NULL;
HRESULT hr = S_OK;
_bstr_t strCnn("Provider='sqloledb';Data Source='crybaby';"
"Initial Catalog='MyTest';Integrated Security='SSPI';");
SAFEARRAY FAR *psa;
SAFEARRAYBOUND rgsabound[1];
rgsabound[0].lLbound = 0;
rgsabound[0].cElements = ChunkSize;
psa = SafeArrayCreate(VT_UI1, 1, rgsabound);
_variant_t varChunk;
_RecordsetPtr RsVersions(__uuidof(Recordset));
int len,k,i;
char* databuf=(char*)psa->pvData;
{
char buf[256]="
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aa\n";
len=strlen(buf);
k=0;
memset(databuf,' ',ChunkSize);
for(i=0;i<(ChunkSize/len);i++){
strncpy(databuf+k,buf,len);
k+=len;
}
}
try
{
//Open a Connection.
hr=pConnection.CreateInstance(__uuidof(Connection));
hr = pConnection->Open(strCnn,"","",adConnectUnspecified);
_bstr_t wQueryString="Select content from Doc where (id=1)";
try {
hr=RsVersions->Open(
variant_t(wQueryString),
_variant_t((IDispatch *)pConnection, true),
adOpenUnspecified, //adOpenForwardOnly, // adOpenUnspecified,
adLockOptimistic, // adLockUnspecified,
-1);
if (!FAILED(hr) && RsVersions->BOF){
printf("error -1\n");
RsVersions->Close();
return -1;
}
}
catch(_com_error e) {
printf("error -2-\n");
// dump_com_error(e,LOG_DEBUG);
return -1;
}
int num=1;
char sbuf[10];
// for(i=0;i<10;i++){
for(i=0;i<300;i++){
sprintf(sbuf,"%d",i);
strncpy((char*)psa->pvData,sbuf,strlen(sbuf));
//Assign the Safe array to a variant.
varChunk.vt = VT_ARRAY|VT_UI1;
varChunk.parray = psa;
RsVersions->Fields->Item["content"]->AppendChunk(varChunk);
}
RsVersions->Update();
printf("Write %d Mega bytes\n",num);
}
catch(_com_error &e)
{
// Notify the user of errors if any.
_bstr_t bstrSource(e.Source());
_bstr_t bstrDescription(e.Description());
PrintProviderError(pConnection);
printf("Source : %s \n Description : %s\n",(LPCSTR)bstrSource,
(LPCSTR)bstrDescription);
}
// Clean up objects before exit.
if (RsVersions)
if (RsVersions->State == adStateOpen)
RsVersions->Close();
if (pConnection)
if (pConnection->State == adStateOpen)
pConnection->Close();
}
int main()
{
HRESULT hr = S_OK;
if(FAILED(::CoInitialize(NULL)))
return 1;
AppendChunkX();
//Wait here for the user to see the output
printf("\n\nPress any key to continue..");
getch();
::CoUninitialize();
return 0;
}
========================================
"John Bell" wrote:
> Hi
> At a guess you are continually looping.
> Also check out:
> http://support.microsoft.com/defaul...kb;en-us;153238
> John
> "Eason" wrote:
>|||Hi
Is this happening
http://support.microsoft.com/defaul...kb;en-us;182423
You may want to look at:
http://support.microsoft.com/defaul...kb;en-us;189415
I also seem to remember that returning a second (non text) column was
the solution for some error, but can't remember or find the article
that was talking about it.
John
Eason wrote:
> Thanks for your response. But the loop is fine, not a dead loop.
> The real code just generates some data and exits the loop after some
> AppendChunk.
> I tried a middle size file (10MByte), it works fine.
> Then I tried a big file (100MByte), it ran a long time using more
than
> 600Mbyte virtual memory after finishing all AppendChunk. Then called
update,
> the memory went higher and higher and gave an error time out.
> Here is my code:
> ======================================
> // This is the main project file for VC++ application project
> // generated using an Application Wizard.
> #include "stdafx.h"
> #import "D:\Program Files\Common Files\System\ADO\mo15.dll" \
> no_namespace rename("EOF", "EndOfFile")
> #define ChunkSize 1024*1024
> #include <ole2.h>
> #include <stdio.h>
> #include "conio.h"
> #include "malloc.h"
> _ConnectionPtr pConnection;
> ///////////////////////////////////////////////////////////
> // //
> // PrintProviderError Function //
> // //
> ///////////////////////////////////////////////////////////
> VOID PrintProviderError(_ConnectionPtr pConnection)
> {
> // Print Provider Errors from Connection object.
> // pErr is a record object in the Connection's Error collection.
> ErrorPtr pErr = NULL;
> long nCount = 0;
> long i = 0;
> if( (pConnection->Errors->Count) > 0)
> {
> nCount = pConnection->Errors->Count;
> // Collection ranges from 0 to nCount -1.
> for(i = 0; i < nCount; i++)
> {
> pErr = pConnection->Errors->GetItem(i);
> printf("\t Error number: %x\t%s", pErr->Number,(LPCSTR)
> pErr->Description);
> }
> }
> }
> ///////////////////////////////////////////////////////////
> // //
> // AppendChunkX Function //
> // //
> ///////////////////////////////////////////////////////////
> int AppendChunkX(VOID)
> {
> // Define ADO object pointers.
> // Initialize pointers on define.
> // These are in the ADODB:: namespace.
> _RecordsetPtr pRstPubInfo = NULL;
> _ConnectionPtr pConnection = NULL;
> HRESULT hr = S_OK;
> _bstr_t strCnn("Provider='sqloledb';Data Source='crybaby';"
> "Initial Catalog='MyTest';Integrated Security='SSPI';");
> SAFEARRAY FAR *psa;
> SAFEARRAYBOUND rgsabound[1];
> rgsabound[0].lLbound = 0;
> rgsabound[0].cElements = ChunkSize;
> psa = SafeArrayCreate(VT_UI1, 1, rgsabound);
> _variant_t varChunk;
> _RecordsetPtr RsVersions(__uuidof(Recordset));
> int len,k,i;
> char* databuf=(char*)psa->pvData;
> {
> char buf[256]="
>
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaa\n";
> len=strlen(buf);
> k=0;
> memset(databuf,' ',ChunkSize);
> for(i=0;i<(ChunkSize/len);i++){
> strncpy(databuf+k,buf,len);
> k+=len;
> }
> }
> try
> {
> //Open a Connection.
> hr=pConnection.CreateInstance(__uuidof(Connection));
> hr = pConnection->Open(strCnn,"","",adConnectUnspecified);
> _bstr_t wQueryString="Select content from Doc where (id=1)";
> try {
> hr=RsVersions->Open(
> variant_t(wQueryString),
> _variant_t((IDispatch *)pConnection, true),
> adOpenUnspecified, //adOpenForwardOnly, // adOpenUnspecified,
> adLockOptimistic, // adLockUnspecified,
> -1);
> if (!FAILED(hr) && RsVersions->BOF){
> printf("error -1\n");
> RsVersions->Close();
> return -1;
> }
> }
> catch(_com_error e) {
> printf("error -2-\n");
> // dump_com_error(e,LOG_DEBUG);
> return -1;
> }
>
> int num=1;
> char sbuf[10];
> // for(i=0;i<10;i++){
> for(i=0;i<300;i++){
> sprintf(sbuf,"%d",i);
> strncpy((char*)psa->pvData,sbuf,strlen(sbuf));
> //Assign the Safe array to a variant.
> varChunk.vt = VT_ARRAY|VT_UI1;
> varChunk.parray = psa;
> RsVersions->Fields->Item["content"]->AppendChunk(varChunk);
> }
> RsVersions->Update();
> printf("Write %d Mega bytes\n",num);
> }
> catch(_com_error &e)
> {
> // Notify the user of errors if any.
> _bstr_t bstrSource(e.Source());
> _bstr_t bstrDescription(e.Description());
> PrintProviderError(pConnection);
> printf("Source : %s \n Description :
%s\n",(LPCSTR)bstrSource,
> (LPCSTR)bstrDescription);
> }
> // Clean up objects before exit.
> if (RsVersions)
> if (RsVersions->State == adStateOpen)
> RsVersions->Close();
> if (pConnection)
> if (pConnection->State == adStateOpen)
> pConnection->Close();
> }
> int main()
> {
> HRESULT hr = S_OK;
> if(FAILED(::CoInitialize(NULL)))
> return 1;
> AppendChunkX();
> //Wait here for the user to see the output
> printf("\n\nPress any key to continue..");
> getch();
> ::CoUninitialize();
> return 0;
> }
> ========================================
>
> "John Bell" wrote:
>
big, I use
usage of|||Thanks for the sample.
The sample code only calls AppendChunk once, so it cannot be very big and it
does not save any memory.
GetChunk is correct. It can get the part of data each time and it does not
load all data to the memory first.
If all the data will save in the memory during multiple AppendChunk call, I
do not see any reason we need use it.
I can allocate the memory for the whole data if AppendChunk will also
allocate same amount of memory.
It looks like AppendChunk API is just a joke.
"John Bell" wrote:

> Hi
> Is this happening
> http://support.microsoft.com/defaul...kb;en-us;182423
> You may want to look at:
> http://support.microsoft.com/defaul...kb;en-us;189415
> I also seem to remember that returning a second (non text) column was
> the solution for some error, but can't remember or find the article
> that was talking about it.
> John
> Eason wrote:
> than
> update,
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaa\n";
> %s\n",(LPCSTR)bstrSource,
> big, I use
> usage of
>|||Hi
Storing large blobs in a relational database is not what they are
really designed for, when you start looking at using text/image
datatypes you will see that there are significant restrictions in what
you can do.
John
Eason wrote:
> Thanks for the sample.
> The sample code only calls AppendChunk once, so it cannot be very big
and it
> does not save any memory.
> GetChunk is correct. It can get the part of data each time and it
does not
> load all data to the memory first.
> If all the data will save in the memory during multiple AppendChunk
call, I
> do not see any reason we need use it.
> I can allocate the memory for the whole data if AppendChunk will also
> allocate same amount of memory.
> It looks like AppendChunk API is just a joke.
>
> "John Bell" wrote:
>
was
some
called
collection.
pErr->Number,(LPCSTR)
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaa\n";
pConnection->Open(strCnn,"","",adConnectUnspecified);
very
Recordset->Fields->Item["ImageField"].AppendChunk(bigarrary);
memory|||It is true that there is a limitation on the size of blob data.
But it is not true in my case.
I try to move data from sharepoint database to another sql database(not on
the sharepoint).
The sharepoint database (SQL2000) has a blob data 200Mega bytes. I read it
out using GetChunk without any error.
But when I try to put it into our SQL2000 database, I cannot put it back. I
use ADO.
Do you know what database API that sharepoint uses to put blob data into SQL
database?
Your help is greatly appreciated.
Thanks
Eason
"John Bell" wrote:

> Hi
> Storing large blobs in a relational database is not what they are
> really designed for, when you start looking at using text/image
> datatypes you will see that there are significant restrictions in what
> you can do.
> John
> Eason wrote:
> and it
> does not
> call, I
>
> was
> some
> called
> collection.
> pErr->Number,(LPCSTR)
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaa\n";
> pConnection->Open(strCnn,"","",adConnectUnspecified);
> very
> Recordset->Fields->Item["ImageField"].AppendChunk(bigarrary);
> memory
>|||Hi
I am not sure why you aren't using DTS, or a linked server, BCP or
replication to do this?
JOhn
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!

Append to text type field in an update statement.

I have a table with one text type column. I am trying to append this
coulmn with whatever it has with another string. This field may have
more than 8000 characters already
here is What I am trying to do.
Update X
Set textTypeColumn = textTypeColumn + ' XXXXXXXXXXXXXXXXXXXXXXX'
WHERE
id = id
-- some criteria with different joins to different tables.
' XXXXXXXXXXXXXXXXXXXXXXX' will be a fixed string
TextTypeColumn may already have more than 8000 charaters in it and now
I want to append the XXXXXXX string to that and store the new value in
the texttypecolumn. This update statement is inside a stored proc. When
I try to compile it, I am getting below error.
Server: Msg 403, Level 16, State 1, Procedure
stp_scr_postmass_denyopclaims, Line 426
Invalid operator for data type. Operator equals add, type equals text.
I want to avoid cursor and looping for each id and then use Updatetext
to update it.
I would appreciate if you could let me know if it can be done in a
query for all the ids in one go.
Thanks & regards,
MandarText datatype won't support UPDATEs. Check BOL
--
Thanks & Rate the Postings.
-Ravi-
"reachmandar@.gmail.com" wrote:

> I have a table with one text type column. I am trying to append this
> coulmn with whatever it has with another string. This field may have
> more than 8000 characters already
> here is What I am trying to do.
> Update X
> Set textTypeColumn = textTypeColumn + ' XXXXXXXXXXXXXXXXXXXXXXX'
> WHERE
> id = id
> -- some criteria with different joins to different tables.
> ' XXXXXXXXXXXXXXXXXXXXXXX' will be a fixed string
> TextTypeColumn may already have more than 8000 charaters in it and now
> I want to append the XXXXXXX string to that and store the new value in
> the texttypecolumn. This update statement is inside a stored proc. When
> I try to compile it, I am getting below error.
> Server: Msg 403, Level 16, State 1, Procedure
> stp_scr_postmass_denyopclaims, Line 426
> Invalid operator for data type. Operator equals add, type equals text.
> I want to avoid cursor and looping for each id and then use Updatetext
> to update it.
> I would appreciate if you could let me know if it can be done in a
> query for all the ids in one go.
> Thanks & regards,
> Mandar
>

Append to text type field in an update statement.

I have a table with one text type column. I am trying to append this
coulmn with whatever it has with another string. This field may have
more than 8000 characters already
here is What I am trying to do.
Update X
Set textTypeColumn = textTypeColumn + ' XXXXXXXXXXXXXXXXXXXXXXX'
WHERE
id = id
-- some criteria with different joins to different tables.
' XXXXXXXXXXXXXXXXXXXXXXX' will be a fixed string
TextTypeColumn may already have more than 8000 charaters in it and now
I want to append the XXXXXXX string to that and store the new value in
the texttypecolumn. This update statement is inside a stored proc. When
I try to compile it, I am getting below error.
Server: Msg 403, Level 16, State 1, Procedure
stp_scr_postmass_denyopclaims, Line 426
Invalid operator for data type. Operator equals add, type equals text.
I want to avoid cursor and looping for each id and then use Updatetext
to update it.
I would appreciate if you could let me know if it can be done in a
query for all the ids in one go.
Thanks & regards,
Mandar
Text datatype won't support UPDATEs. Check BOL
Thanks & Rate the Postings.
-Ravi-
"reachmandar@.gmail.com" wrote:

> I have a table with one text type column. I am trying to append this
> coulmn with whatever it has with another string. This field may have
> more than 8000 characters already
> here is What I am trying to do.
> Update X
> Set textTypeColumn = textTypeColumn + ' XXXXXXXXXXXXXXXXXXXXXXX'
> WHERE
> id = id
> -- some criteria with different joins to different tables.
> ' XXXXXXXXXXXXXXXXXXXXXXX' will be a fixed string
> TextTypeColumn may already have more than 8000 charaters in it and now
> I want to append the XXXXXXX string to that and store the new value in
> the texttypecolumn. This update statement is inside a stored proc. When
> I try to compile it, I am getting below error.
> Server: Msg 403, Level 16, State 1, Procedure
> stp_scr_postmass_denyopclaims, Line 426
> Invalid operator for data type. Operator equals add, type equals text.
> I want to avoid cursor and looping for each id and then use Updatetext
> to update it.
> I would appreciate if you could let me know if it can be done in a
> query for all the ids in one go.
> Thanks & regards,
> Mandar
>

Append to text type field in an update statement.

I have a table with one text type column. I am trying to append this
coulmn with whatever it has with another string. This field may have
more than 8000 characters already
here is What I am trying to do.
Update X
Set textTypeColumn = textTypeColumn + ' XXXXXXXXXXXXXXXXXXXXXXX'
WHERE
id = id
-- some criteria with different joins to different tables.
' XXXXXXXXXXXXXXXXXXXXXXX' will be a fixed string
TextTypeColumn may already have more than 8000 charaters in it and now
I want to append the XXXXXXX string to that and store the new value in
the texttypecolumn. This update statement is inside a stored proc. When
I try to compile it, I am getting below error.
Server: Msg 403, Level 16, State 1, Procedure
stp_scr_postmass_denyopclaims, Line 426
Invalid operator for data type. Operator equals add, type equals text.
I want to avoid cursor and looping for each id and then use Updatetext
to update it.
I would appreciate if you could let me know if it can be done in a
query for all the ids in one go.
Thanks & regards,
MandarText datatype won't support UPDATEs. Check BOL
--
Thanks & Rate the Postings.
-Ravi-
"reachmandar@.gmail.com" wrote:
> I have a table with one text type column. I am trying to append this
> coulmn with whatever it has with another string. This field may have
> more than 8000 characters already
> here is What I am trying to do.
> Update X
> Set textTypeColumn = textTypeColumn + ' XXXXXXXXXXXXXXXXXXXXXXX'
> WHERE
> id = id
> -- some criteria with different joins to different tables.
> ' XXXXXXXXXXXXXXXXXXXXXXX' will be a fixed string
> TextTypeColumn may already have more than 8000 charaters in it and now
> I want to append the XXXXXXX string to that and store the new value in
> the texttypecolumn. This update statement is inside a stored proc. When
> I try to compile it, I am getting below error.
> Server: Msg 403, Level 16, State 1, Procedure
> stp_scr_postmass_denyopclaims, Line 426
> Invalid operator for data type. Operator equals add, type equals text.
> I want to avoid cursor and looping for each id and then use Updatetext
> to update it.
> I would appreciate if you could let me know if it can be done in a
> query for all the ids in one go.
> Thanks & regards,
> Mandar
>

Append to a field in a database

Hey guys,
I am working on some ASP stuff, and I have an html table which displays records from a SQL table.There is one field for adding notes which i want to append to. Basically I have a textbox, in which if a user enters information, it should be appended to a field (rNotes) in my database table. Right now all i can get it to do is overwrite the current information as i'm using an UPDATE query. Any ideas?In update statement you should use:

Update table_name set
column_name = column_name + new_data
where ....|||

Quote:

Originally Posted by nikhil86

Hey guys,
I am working on some ASP stuff, and I have an html table which displays records from a SQL table.There is one field for adding notes which i want to append to. Basically I have a textbox, in which if a user enters information, it should be appended to a field (rNotes) in my database table. Right now all i can get it to do is overwrite the current information as i'm using an UPDATE query. Any ideas?

Personally, I would create a notes table, and add records. This way, you can also store things like date/time, user id, etc. You can then just display them in sequential order. I do this with ASP all the time. Just did it yesterday, as a matter of fact. The client loved it.

Good luck,

Michael C. Gates

Append string to field value in select list

How can I append a string to the field value in the select list
SELECT Code + '-20' FROM tb....
I want to the above to return 2000-20 for example.
How can I do this?
Mike BJust like you did if Code is a character-based datatype. If not, - then CAST(Code as varchar(10)) + '-20'

Append Counter variable to field name

Hi all,

I have a table with fields name Days1, Days2, Days3 - I am trying to use a loop in conjunction with a counter to identify each of these fields - I can't quite get the correct syntax and it is driving me crazy!!!

Here's the proc:

WHILE @.Counter < 4
BEGIN

SELECT @.AppointmentsCount = COUNT(tbl_SurgerySlot.SurgerySlotKey)
FROM tbl_SurgerySlot INNER JOIN
tbl_SurgerySlotDescription ON tbl_SurgerySlot.PracticeCode = tbl_SurgerySlotDescription.PracticeCode AND
tbl_SurgerySlot.Label = tbl_SurgerySlotDescription.Label LEFT OUTER JOIN
tbl_Appointment ON tbl_SurgerySlot.SurgerySlotKey = tbl_Appointment.SurgerySlotKey AND
tbl_SurgerySlot.ExtractDate = tbl_Appointment.ExtractDate
WHERE (tbl_SurgerySlot.ExtractDate = @.ExtractDate) AND (tbl_Appointment.AppointmentKey IS NULL) AND
(tbl_SurgerySlot.StartTime > @.DateFrom) AND (tbl_SurgerySlot.StartTime < @.DateTo) AND (tbl_SurgerySlotDescription.IsBookable = 1)

SET @.FieldName = 'Days' + CONVERT(VARCHAR(20),@.Counter)

INSERT INTO tmp_Availability (@.FieldName)
VALUES (@.AppointmentsCount)

SET @.DateTo = DATEADD(Day,1,@.DateTo)

--Increment the loop counter
SET @.Counter = @.Counter + 1

When I run the above the follwoing message is displayed:

Server: Msg 208, Level 16, State 3, Line 36
Invalid object name 'tmp_Availability'.

The object IS valid so I'm lost...Try to use object owner (object_owner.table_name)|||Instead of :

INSERT INTO tmp_Availability (@.FieldName)
VALUES (@.AppointmentsCount)

you could use exec:

exec('insert into ...'+ @.FieldName+') ...'|||OK, thanks, I'll give that a go...|||You can turn the whole thing in to a set based solution..

Also, are you sure that the query will return 1 row...|||Hi Bret,

Not sure what u mean by a Set based solution. I've just realised that the way I'm doing this won't work anyway cos' every time I use the INSERT statement it will obviously insert a new row, which I don't want it to do. I want the code to fill up the row with firgures for each day e.g.

Row1 5, 25,6

At the mo' it will do:

Row1 5,
Row2 ,25,
Row3 , , 6

Thought I could maybe store the data in an Array before committing it to the DB but have found T-SQL doesn't support this! Any ideas...|||The are no arrays in sql server...

I guess you could call a table like an array...

If you have sql server 2000 you can use table varialbles...

And I'm kinda of lost (so what else in new) with your example

Can you tell us, in business terms, what you're trying to do?|||I work for the Health Service so business doesn't really come into it - just loads of shitty data!!

I'll look into table variables to see if they might help, I realise it's difficult trying to figure out what I'm doing - come to think of it I need to try and firgure out what I'm supposed to be doing :-)|||I work for the Health Service so business doesn't really come into it - just loads of shitty data!!

I'll look into table variables to see if they might help, I realise it's difficult trying to figure out what I'm doing - come to think of it I need to try and firgure out what I'm supposed to be doing :-)

Thanks for the chuckle...

Lots of time sql server will through an erroneous error...

BUT...your process needs to be changed...

If you ever figure out what's suppose to happen, tell use and post the ddl of the tables, some sample data with dml statements and expected results..

good luck...

Append a field

Hello,

How do I append data on an update?
I have a table with a field that is nVarchar(1000) and the initial insert is a few sentences. If I wanted to add to that row using an update statement and without starting at the end of the sentences, how would I write that?

Update table set fieldname = 'more data' where value = @.variable

instead of

Update table set fieldname = 'initial data more data' where value = @.variable

and the 'more data' appends to the initial data... hmmm

help please.I'm not exactly following you. If you want to do both an INSERT and an UPDATE you need 2 different commands.

INSERT INTO table (ID, fieldname) VALUES(@.MyID, 'initial data')
UPDATE table SET fieldname = fieldname + ' more data' WHERE ID = @.MyID

Maybe you could explain further?

Terri|||OK, lets see...

An existing field containing data:

'A small red dog crapped on my lawn.'

In a textbox append to that same field:

'Then the owner cleaned it up.'

So now the field will contain:
'A small red dog crapped on my lawn. Then the owner cleaned it up.'

If i perform the statement like this:
Inset Into table (fieldname) Values ('Then the owner cleaned it up.') where value = @.value

Won't the 'A small red dog crapped on my lawn.' part be overwritten?|||No, you'd get an error ;-) You can't put a WHERE on an INSERT. What you need is an UPDATE:

UPDATE table SET fieldname = RTRIM(fieldname) + 'Then the owner cleaned it up.' WHERE value = @.value

(Note that you'd only need the RTRIM if the datatype of that field is char. Varchar data will not have extra spaces padding it out to the field length.

Terri