|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
varchar(8000) data being truncated to 4096I have a stored procedure that has an input parameter, @chvComments defined as text. The comments are getting in with the proper length, as shown by printing out len(@chvComments) in the body of the stored procedure. Prior to being inserted into a table, the comments are CAST to varchar(8000) and then run through a function to strip out various problematic characters. This function is defined to return a varchar(8000) However, when the text is inserted into a table column defined as varchar(8000), only 4096 characters are inserted, as shown by using a select len(chvComments) statement on the table. Due to the length value being 4096, I am thinking that this truncation must be the result of a setting somewhere, but for the life of me, i can't think of where. Any references, resources, war stories would be greatly appreciated. Thanks, Kathryn Can you reproduce this? Which version of SQL Server? Can you give us some
scripts that demonstrate this problem? -- Show quoteHTH, Vyas, MVP (SQL Server) SQL Server Articles and Code Samples @ http://vyaskn.tripod.com/ <kbutte***@yahoo.com> wrote in message news:1153423256.727428.258150@i42g2000cwa.googlegroups.com... > Good afternoon, all! > > I have a stored procedure that has an input parameter, @chvComments > defined as text. > > The comments are getting in with the proper length, as shown by > printing out len(@chvComments) in the body of the stored procedure. > > Prior to being inserted into a table, the comments are CAST to > varchar(8000) and then run through a function to strip out various > problematic characters. This function is defined to return a > varchar(8000) > > However, when the text is inserted into a table column defined as > varchar(8000), only 4096 characters are inserted, as shown by using a > select len(chvComments) statement on the table. > > Due to the length value being 4096, I am thinking that this truncation > must be the result of a setting somewhere, but for the life of me, i > can't think of where. > > Any references, resources, war stories would be greatly appreciated. > > Thanks, > Kathryn > It would help us better assist you if you could include table DDL, query strategy used so far, sample data in the form of INSERT statements, and an illustration of the desired results. (For help with that refer to: http://www.aspfaq.com/5006 )
The less 'set up' work we have to do, the more likely you are going to have folks tackle your problem and help you. Without this effort from you, we are just playing guessing games. What you are describing is NOT the expected behavior and there are no settings that are specifically limiting the length. -- Show quoteArnie Rowland Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous <kbutte***@yahoo.com> wrote in message news:1153423256.727428.258150@i42g2000cwa.googlegroups.com... > Good afternoon, all! > > I have a stored procedure that has an input parameter, @chvComments > defined as text. > > The comments are getting in with the proper length, as shown by > printing out len(@chvComments) in the body of the stored procedure. > > Prior to being inserted into a table, the comments are CAST to > varchar(8000) and then run through a function to strip out various > problematic characters. This function is defined to return a > varchar(8000) > > However, when the text is inserted into a table column defined as > varchar(8000), only 4096 characters are inserted, as shown by using a > select len(chvComments) statement on the table. > > Due to the length value being 4096, I am thinking that this truncation > must be the result of a setting somewhere, but for the life of me, i > can't think of where. > > Any references, resources, war stories would be greatly appreciated. > > Thanks, > Kathryn > Arnie and Vyas,
Thanks so much for the replies. I apologize, i missed a statement in a function that limited the size of the return value to 4096. I reread that function at least three times and somehow missed it every time. Sorry to have bothered you. Thanks again, Kathryn Arnie Rowland wrote: Show quote > It would help us better assist you if you could include table DDL, query strategy used so far, sample data in the form of INSERT statements, and an illustration of the desired results. (For help with that refer to: http://www.aspfaq.com/5006 ) > > > The less 'set up' work we have to do, the more likely you are going to have folks tackle your problem and help you. Without this effort from you, we are just playing guessing games. > > What you are describing is NOT the expected behavior and there are no settings that are specifically limiting the length. > > -- > Arnie Rowland > Most good judgment comes from experience. > Most experience comes from bad judgment. > - Anonymous > > > <kbutte***@yahoo.com> wrote in message news:1153423256.727428.258150@i42g2000cwa.googlegroups.com... > > Good afternoon, all! > > > > I have a stored procedure that has an input parameter, @chvComments > > defined as text. > > > > The comments are getting in with the proper length, as shown by > > printing out len(@chvComments) in the body of the stored procedure. > > > > Prior to being inserted into a table, the comments are CAST to > > varchar(8000) and then run through a function to strip out various > > problematic characters. This function is defined to return a > > varchar(8000) > > > > However, when the text is inserted into a table column defined as > > varchar(8000), only 4096 characters are inserted, as shown by using a > > select len(chvComments) statement on the table. > > > > Due to the length value being 4096, I am thinking that this truncation > > must be the result of a setting somewhere, but for the life of me, i > > can't think of where. > > > > Any references, resources, war stories would be greatly appreciated. > > > > Thanks, > > Kathryn > > > ------=_NextPart_000_0B7A_01C6AC12.7A128260 > Content-Type: text/html; charset=iso-8859-1 > Content-Transfer-Encoding: quoted-printable > X-Google-AttachSize: 3172 > > <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> > <HTML><HEAD> > <META http-equiv=Content-Type content="text/html; charset=iso-8859-1"> > <META content="MSHTML 6.00.5296.0" name=GENERATOR> > <STYLE></STYLE> > </HEAD> > <BODY> > <DIV> > <P align=left><FONT face=Arial size=2>It would help us better assist you if you > could include table DDL, query strategy used so far, sample data in the form of > INSERT statements, and an illustration of the desired results. (For help with > that refer to: </FONT><A href="http://www.aspfaq.com/5006"><FONT face=Arial > size=2>http://www.aspfaq.com/5006</FONT></A><FONT face=Arial size=2> ) > </FONT></P> > <P align=left><FONT face=Arial size=2></FONT></P> > <P align=left><FONT face=Arial size=2>The less 'set up' work we have to do, the > more likely you are going to have folks tackle your problem and help you. > Without this effort from you, we are just playing guessing games. > </FONT></P></DIV> > <DIV><FONT face=Arial size=2>What you are describing is NOT the expected > behavior and there are no settings that are specifically limiting the > length.</FONT></DIV> > <DIV><FONT face=Arial size=2></FONT> </DIV> > <DIV><FONT face=Arial size=2>-- <BR>Arnie Rowland<BR>Most good judgment comes > from experience. <BR>Most experience comes from bad judgment. <BR>- > Anonymous</FONT></DIV> > <DIV><FONT face=Arial size=2></FONT> </DIV> > <DIV><FONT face=Arial size=2></FONT> </DIV> > <DIV><FONT face=Arial size=2><</FONT><A > href="mailto:kbutte***@yahoo.com"><FONT face=Arial > size=2>kbutte***@yahoo.com</FONT></A><FONT face=Arial size=2>> wrote in > message </FONT><A > href="news:1153423256.727428.258***@i42g2000cwa.googlegroups.com"><FONT > face=Arial > size=2>news:1153423256.727428.258150@i42g2000cwa.googlegroups.com</FONT></A><FONT > face=Arial size=2>...</FONT></DIV><FONT face=Arial size=2>> Good afternoon, > all!<BR>> <BR>> I have a stored procedure that has an input parameter, > @chvComments<BR>> defined as text.<BR>> <BR>> The comments are getting > in with the proper length, as shown by<BR>> printing out len(@chvComments) in > the body of the stored procedure.<BR>> <BR>> Prior to being inserted into > a table, the comments are CAST to<BR>> varchar(8000) and then run > through a function to strip out various<BR>> problematic characters. > This function is defined to return a<BR>> varchar(8000)<BR>> <BR>> > However, when the text is inserted into a table column defined as<BR>> > varchar(8000), only 4096 characters are inserted, as shown by using a<BR>> > select len(chvComments) statement on the table.<BR>> <BR>> Due to the > length value being 4096, I am thinking that this truncation<BR>> must be the > result of a setting somewhere, but for the life of me, i<BR>> can't think of > where.<BR>> <BR>> Any references, resources, war stories would be greatly > appreciated.<BR>> <BR>> Thanks,<BR>> > Kathryn<BR>></FONT></BODY></HTML> > > ------=_NextPart_000_0B7A_01C6AC12.7A128260-- I hope that you don't go away thinking that your quest for information and
help was a 'bother' -for me, it was not. I'm glad that you resolved the issue. Don't hesitate to use this resource anytime you are stuck and this seems appropriate. -- Show quoteArnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous <kbutte***@yahoo.com> wrote in message news:1153833822.730165.163260@p79g2000cwp.googlegroups.com... > Arnie and Vyas, > > Thanks so much for the replies. I apologize, i missed a statement in a > function that limited the size of the return value to 4096. I reread > that function at least three times and somehow missed it every time. > Sorry to have bothered you. > > Thanks again, > Kathryn > > > Arnie Rowland wrote: >> It would help us better assist you if you could include table DDL, query >> strategy used so far, sample data in the form of INSERT statements, and >> an illustration of the desired results. (For help with that refer to: >> http://www.aspfaq.com/5006 ) >> >> >> The less 'set up' work we have to do, the more likely you are going to >> have folks tackle your problem and help you. Without this effort from >> you, we are just playing guessing games. >> >> What you are describing is NOT the expected behavior and there are no >> settings that are specifically limiting the length. >> >> -- >> Arnie Rowland >> Most good judgment comes from experience. >> Most experience comes from bad judgment. >> - Anonymous >> >> >> <kbutte***@yahoo.com> wrote in message >> news:1153423256.727428.258150@i42g2000cwa.googlegroups.com... >> > Good afternoon, all! >> > >> > I have a stored procedure that has an input parameter, @chvComments >> > defined as text. >> > >> > The comments are getting in with the proper length, as shown by >> > printing out len(@chvComments) in the body of the stored procedure. >> > >> > Prior to being inserted into a table, the comments are CAST to >> > varchar(8000) and then run through a function to strip out various >> > problematic characters. This function is defined to return a >> > varchar(8000) >> > >> > However, when the text is inserted into a table column defined as >> > varchar(8000), only 4096 characters are inserted, as shown by using a >> > select len(chvComments) statement on the table. >> > >> > Due to the length value being 4096, I am thinking that this truncation >> > must be the result of a setting somewhere, but for the life of me, i >> > can't think of where. >> > >> > Any references, resources, war stories would be greatly appreciated. >> > >> > Thanks, >> > Kathryn >> > >> ------=_NextPart_000_0B7A_01C6AC12.7A128260 >> Content-Type: text/html; charset=iso-8859-1 >> Content-Transfer-Encoding: quoted-printable >> X-Google-AttachSize: 3172 >> >> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> >> <HTML><HEAD> >> <META http-equiv=Content-Type content="text/html; charset=iso-8859-1"> >> <META content="MSHTML 6.00.5296.0" name=GENERATOR> >> <STYLE></STYLE> >> </HEAD> >> <BODY> >> <DIV> >> <P align=left><FONT face=Arial size=2>It would help us better assist you >> if you >> could include table DDL, query strategy used so far, sample data in the >> form of >> INSERT statements, and an illustration of the desired results. (For help >> with >> that refer to: </FONT><A href="http://www.aspfaq.com/5006"><FONT >> face=Arial >> size=2>http://www.aspfaq.com/5006</FONT></A><FONT face=Arial size=2> ) >> </FONT></P> >> <P align=left><FONT face=Arial size=2></FONT></P> >> <P align=left><FONT face=Arial size=2>The less 'set up' work we have to >> do, the >> more likely you are going to have folks tackle your problem and help you. >> Without this effort from you, we are just playing guessing games. >> </FONT></P></DIV> >> <DIV><FONT face=Arial size=2>What you are describing is NOT the expected >> behavior and there are no settings that are specifically limiting the >> length.</FONT></DIV> >> <DIV><FONT face=Arial size=2></FONT> </DIV> >> <DIV><FONT face=Arial size=2>-- <BR>Arnie Rowland<BR>Most good judgment >> comes >> from experience. <BR>Most experience comes from bad judgment. <BR>- >> Anonymous</FONT></DIV> >> <DIV><FONT face=Arial size=2></FONT> </DIV> >> <DIV><FONT face=Arial size=2></FONT> </DIV> >> <DIV><FONT face=Arial size=2><</FONT><A >> href="mailto:kbutte***@yahoo.com"><FONT face=Arial >> size=2>kbutte***@yahoo.com</FONT></A><FONT face=Arial size=2>> wrote >> in >> message </FONT><A >> href="news:1153423256.727428.258***@i42g2000cwa.googlegroups.com"><FONT >> face=Arial >> size=2>news:1153423256.727428.258150@i42g2000cwa.googlegroups.com</FONT></A><FONT >> face=Arial size=2>...</FONT></DIV><FONT face=Arial size=2>> Good >> afternoon, >> all!<BR>> <BR>> I have a stored procedure that has an input >> parameter, >> @chvComments<BR>> defined as text.<BR>> <BR>> The comments are >> getting >> in with the proper length, as shown by<BR>> printing out >> len(@chvComments) in >> the body of the stored procedure.<BR>> <BR>> Prior to being >> inserted into >> a table, the comments are CAST to<BR>> varchar(8000) and then >> run >> through a function to strip out various<BR>> problematic >> characters. >> This function is defined to return a<BR>> varchar(8000)<BR>> >> <BR>> >> However, when the text is inserted into a table column defined as<BR>> >> varchar(8000), only 4096 characters are inserted, as shown by using >> a<BR>> >> select len(chvComments) statement on the table.<BR>> <BR>> Due to >> the >> length value being 4096, I am thinking that this truncation<BR>> must >> be the >> result of a setting somewhere, but for the life of me, i<BR>> can't >> think of >> where.<BR>> <BR>> Any references, resources, war stories would be >> greatly >> appreciated.<BR>> <BR>> Thanks,<BR>> >> Kathryn<BR>></FONT></BODY></HTML> >> >> ------=_NextPart_000_0B7A_01C6AC12.7A128260-- >  |
|||||||||||||||||||||||
Other interesting topics