Dynamically Changing The Length Of A Varchar(n) Field

Dec 26, 2006

Hi Everyone,

I have a question about dynamically changing the length of a varchar(n)
field, in case the value I'm trying to insert is too big and will give
a "truncated" error, but before the error is given! i.e. Is there some
kind of a way to "test" the length of the field while Inserting the
value into it, and to have it automatically increase its length to the
length of the value being inserted, in case the value is too big?

I've been able to do this in a "primitive" way, simply by identifying
the specific error number in case the value is being truncated, and
then increasing the length of the varchar(n) field by using the ALTER
command, and then duplicating the insert statement, but is there a
standard (shorter) way of doing this?

Here is my code (I'm working in an ASP environment):

<%
var_txt = "abcdefghijklmnopqrstuvwxyz12345678789"

sql = "Insert Into Table1 (text) Values ('" & var_txt & "')"
On Error Resume Next
conn.Execute sql

If err = -2147217833 Then
Response.Write "Error Recognized Successfully!<br /><br />"

sql = "ALTER TABLE Table1 ALTER COLUMN text VARCHAR(" &
Len(var_txt) &
") NOT NULL"
On Error Resume Next
conn.Execute sql

If err<>0 Then
Response.Write "Error while trying to alter Column:<br
/>" & err & "
= " & err.description & "<br />"
Else
Response.Write "Column altered successfully to: " &
Len(var_txt) &
"<br />"

sql = "Insert Into Table1 (text) Values ('" & var_txt &
"')"
On Error Resume Next
conn.Execute sql

If err<>0 Then
Response.Write "<br />Error number 2:<br />" &
err.description &
"<br />"
Else
Response.Write "Now it was added successfully!
HaHa!<br />"
End If

End If

Else
Response.Write "Success."
End If
%>

Thanks in advance!

View 2 Replies


ADVERTISEMENT

Database Growth Excessive When Changing Varchar Length From 50 To 100.

Aug 9, 2007



Hi all,

I'm trying to get an understanding of a serious problem I have with a large DB in production. This is going to be obvious to someone (everyone probably) <bg>

I have a table which consists of numerous varchars and ints but also a Text type field. This table resides in a SQL 2000 Database. This DB currently has a data file size of 16Gb and a Transaction Log size of 17Gb. When I edit the table and increase the size of a Varchar field from 50 to 100 these files grow to more than double their size!

Why is this happening and how can I prevent this?

TIA

NozFx

View 1 Replies View Related

VARCHAR Max Field Length

Nov 21, 2000

What is the max field length in SQL Server 7.0 that a varchar field can be?
I think 8000. Please advise
Thanks

View 2 Replies View Related

Max Length For Varchar And Text Field

Feb 2, 2005

Hi, all
I am seting up a table with email message, I am wondering what is the max length for varchar field. I am so reluctant to use text field, since when
I run query for the descriptiona in sql analyzer, text field cannot be fully display in column. Any tricks to share?
Thanks
Betty

View 5 Replies View Related

Changing Field Length Whilst Replicating

Jan 6, 2004

Hi

I have two databases that are merged using replication, and I want to change the length of one of the fields. Can anyone think of a way of doing it that doesn't require dropping the whole publication and rebuilding it? Thanks Ed

View 2 Replies View Related

Changing The Size Of A Varchar Field

Jul 7, 2006

We have a small table of about 13 million rows that needs altered. A column in the table needs to be changed from a varchar(20) to a varchar(500). When we ran the alter table script, 3 hrs later and it wasn't done running. Any suggestions on what we can do to speed up the process?


Thanks ahead of time
DMW


Edit:
We are running SQL Server 2000 and the db at the time was running in simple mood

View 1 Replies View Related

Varchar Length

Jul 12, 2004

i have problem regarding the row length and varchar.
my problem is on every new row i have +6 more character on one of my field then the last record. and BOL says i can only have 8060 character per row.
What i can not use the full lenght of varchar(8000) on field.

Can anybody help?????????

View 5 Replies View Related

Transact SQL :: NVARCHAR Field Getting Truncated After Increasing Field Length?

Aug 28, 2015

I have a very strange situation. I've increased the size of an NVARCHAR field from 8 to 9 in a database table. The format of the data that I enter will either be an 8 character field (123456-8) or a 9 character field (1234567-9). The '-' is critical.

It used to only accept the 8 character version, but after increasing the field size, if I try to insert the 9 character field version, it gets truncated after the '-', as though it's still only allowing 8 characters. But that only occurs when I include the '-' or other such characters like '#'. If I try to insert 1234567a9, it works. The following explains the outcomes:

Inserted Value -> Result in table

123456-8 -> 123456-8      *Correct
1234567-9 -> 1234567-     *Wrong
123456789 -> 123456789    *Correct
1234567#9 -> 1234567#     *Wrong
1234567a9 -> 1234567a9    *Correct

Why is it that characters such as '-' and '#' are truncating the value, but only if the string is 9 chars long?

I'm currently using a direct t-sql insert statement in SQLExpress. The field is a simple NVARCHAR(9) field.

View 3 Replies View Related

Why Specify The Char Length In Varchar?

Feb 6, 2002

Newbie question:

Why bother specifing the length in varchar()?
Why not just specify the max and not worry about truncation?

Thanks,

Martin

View 2 Replies View Related

Length Of Varchar In SQL7.0

May 2, 2000

Hi,
I am trying to insert text into a varchar(5000) column from a JAVA program. I am using MSDE database (which I believe is a strip down version of SQL7.0), with a variation of ms SQL Server 3.70.06.90 ODBC driver and tds jdbc driver from inet software. With the ODBC driver, I don't get any error but the data saved is only 255 characters. With the latter driver, I get a data truncation error and nothing is saved.

Any help is appreciated.

Thanks

- Sharma

View 1 Replies View Related

Varchar/nvarchar Length

Mar 9, 2007

Hi,I have a pretty straightforward question to do with variable length fields I hope someone can help me with:When using varchar (or nvarchar), is there any point in specifying a smaller length than the maximum? Does it save space or improve performance at all?ThanksRedit: I suppose the max rowsize is an issue. any others?

View 2 Replies View Related

Varchar Default Length

Aug 21, 2007



Hi all,

a simple question to ask for varchar type. I want to update a varchar type parameter to a stored procedure to update a table. My question is , if I don't specify the length. and the varchar type will be trimmed to only 1 character. I thought it's supposed to be 50 by default? Besides if I need to compare varchar values, do I need to specify length as well? Cheers.

View 9 Replies View Related

Changing Datatype Length

Jul 20, 2005

Hi all,I need to change a varchar from 35 to 50. In the SQL Server books online it says that SQL Server actually creates a new table when youchange the length. I ran a test in a test database and it appears theonly thing that changes is the length. All the data remains in tact.The table with the column I want to modify is very critical. Is thereany chance I would loose data if I change the length to a larger size? Iam making a back up of the table just in case. Thanks,Kelly

View 2 Replies View Related

Can Varchar Length Be Set To More Than 8000 Char?

May 22, 2006

Hi, everyone, I want to know is there a way for me to set varchar to store more than 8000 characters? (I did checked from sql server books online and i know that the maximum storage for varchar, but i just want to know is there any exceptional way for me to store more than that).

Thanks for any reply.

aex

View 4 Replies View Related

Varchar Minimum Length Check

Oct 7, 2007

Hi,

I have a column set to varchar(12) so I can ensure that the length of the string entered will never be more than 12 characters but I want to limit the string to a minimum of 8 characters, so I end up with a string that is from 8-12 characters long. How do I ensure the minimum length of 8 characters? I'm using SQL Server 2005 if that helps. I tried adding a check constraint like so:

DATALENGTH(UserName) >= 8

But I keep getting an error when I save the table, so any help would be very much appreciated.

Thanks

View 3 Replies View Related

Query By Length Of Varchar Column

Sep 14, 2007

Hi mates,

I have a Table:Test with column text:varchar(255). I want get rows where text length to be longer than 100. Is it possible?

Thx in advance,

View 3 Replies View Related

Transact SQL :: Changing Length Of Datatype?

May 20, 2015

I have a field in a table

FormID nvarchar(6)

i want to change the length of the datatype to nvarchar(8).

what is the best way to do with out dropping the table?

View 5 Replies View Related

Reporting Services :: How To Adjust Column Length Dynamically In SSRS Report

Nov 11, 2015

I developed a SSRS report, the problem is i dont have data in DEV server. So i dont know how to adjust the column lengths in ssrs report. is there any property so that the column length can be adjusted dynamically based on the data length whenever data is available in production.

View 3 Replies View Related

Varchar Absolute Max Length Of 1024 Characters. Why?

Oct 1, 2000

Hi all,
I have a strange situation. I have a field in the database that has to be a string type field of around 4000 characters.

So naturally I setup the field as
type: varchar
length: 4000

However when I try to put any text in this field I find that I can put no more than 1023 characters of ascii text in there.

To check if this was a max record length prob I setup a test table with only 2 fields:
ID: int, PK, Identity
longVarchar: varchar, 4000

and tried to put some ascii text into the field called longVarchar. Again the most I could put in was 1023 characters!

Thinking that it could just be that SQL svr box that was wacky, I tried it on another one with the same result.

I have tried using other field types (nvarchar, char) and have found that they all could only hold 1023 characters max, no matter what how high I defined the size of the field.

Try it out yourselves and see if you get the same result. Any useful suggestions would really be appreciated.

View 1 Replies View Related

How Do I Calculate The Average Variable Length For A Varchar?

Feb 26, 2008

im trying to learn how to calculate table size.
i understand some of it, but im stuck at calculating the varchars

Ex. i have 2 varchar columns
- varchar(50)
- varchar(100)

i'm suppose to find the average length for them?

i'm suppose to use that to add up to my ROW SIZE

and also after i got the average, do i add 2 for variable columns overhead and another 2 for Row pointer in row offset array

please help me asap before 2morrow night.
Thanks!
i have a test

View 2 Replies View Related

Changing Char Length In Stored Procedures

Aug 11, 2004

Hello all, I'm using SQL Server 2000 and have about 250 stored procedures that use an EMPLID parameter or variable of type varchar with a length of 4. I need to change the length to 10 instead and would like to do so without having to open every sp for editing. Is there a way to do this through SQL Server 2000? Does anyone have a script to do this? Any help would be appreciated.

View 1 Replies View Related

Changing Column Length Of A Replicated Table

Jul 31, 2006



Can I increase the length of a varchar column of table involved in transactional replication without dropping and recreating publication/subscription?

Any help/short-cuts/undocumented features greatly appreciated.

Regards

Opal

View 6 Replies View Related

Error When Changing The Length On DataReader Source

Nov 3, 2006

Hi,
I am trying to import data from Oracle RDB into SQL Server 2005 using SSIS. Created a ODBC data source to connect to Oracle and used DataReader Source component and ADO.net to connect to the ODBC data source.

Under the Component properties tab, the SQL Command looks something like this.

Select ID, ADDRESS, REVISED from ADDRESS

The data type for the source columns are Integer, Varchar(30) and DATE VMS.

Now when I look at the Input and Output properties window,

The External columns has the following data types.

ID - four-byte signed integer [DT_I4]
ADDRESS - Unicode string [DT_WSTR], length = 0
REVISED - database timestamp [DT_DBTIMESTAMP]

The Output columns has the following data types

ID - four-byte signed integer [DT_I4]
ADDRESS - Unicode string [DT_WSTR], length = 0
REVISED - database timestamp [DT_DBTIMESTAMP]

When I tried to change the length of the ADDRESS on the output column, I get the following error.

Error at Data Flow Task [DataReader Source [1]]: The data type of output columns on the component "DataReader Source" (1) cannot be changed.

Error at Data Flow Task [DataReader Source [1]]: System.Runtime.InteropServices.COMException (0xC020837D)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.SetOutputColumnDataTypeProperties(Int32 iOutputID, Int32 iOutputColumnID, DataType eDataType, Int32 iLength, Int32 iPrecision, Int32 iScale, Int32 iCodePage)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostSetOutputColumnDataTypeProperties(IDTSManagedComponentWrapper90 wrapper, Int32 iOutputID, Int32 iOutputColumnID, DataType eDataType, Int32 iLength, Int32 iPrecision, Int32 iScale, Int32 iCodePage)

Is this the default length for the Unicode string type. I was not able to load the ADDRESS column as it gets truncated before I load it into destination. Even if I use Derived or Data Conversion transformation, the ADDRESS is getting truncated before it reaches this transformation.

Any thoughts.

Thanks,
SK

View 8 Replies View Related

Changing PageHeight Dynamically

Aug 16, 2007

My requirement is to have my report display on one 11 by 8.5 page. Is there a way to programmatically change the PageHeight report property based on the number of rows returned in a table? i.e., if the rowcount for table x >= 10, change PageHeight to 9.5.

Any ideas?

Thanks.

Brad

View 3 Replies View Related

Changing Dynamically The Destination Name In Ssis

Apr 15, 2008

i am transfering the table from one database to csv file format..i did it.. again i want to shift that csv files to another databse as tables. how to do this task.. pls help me.. its very urgent..out TL had given me the dead line.. send reply soon....

View 5 Replies View Related

Dynamically Changing Column Headings

Feb 5, 2008

We have a query in which we have data in fields called TS1Min, TS1Max, TS1Avg, TS2Min, TS2Max, TS2Avg etc.

We have a different table in which we define that vale of TS1, TS2, as an example TS1 might equal RED, TS2 might equal BLUE.

We have written a query that puts TS1Min, TS1Max, TS1Avg, TS2Min, TS2Max, TS2Avg in a temp table #TEMPA

and we also put the values of RED and BLUE in another temp table #TEMPB

now we want to select * from #TEMPA but rename the headings TS1Min to display RED-TS1Min, TS1Max as RED-TS1Max, TS1Avg as RED-TS1Avg etc...

any ideas on how to do this

View 4 Replies View Related

Dynamically Changing Default Parameter??????

Jun 4, 2007

I am creating SSRS reports on top of SSAS cubes. I want the default value of parameter to change dynamically based on the current year or it should select the last of the parameter values.

Can this be done?

View 4 Replies View Related

Changing The Dataset Of A Table Dynamically

May 1, 2007

Hi all,

Is there a way to change the dataset being used by a table dynamically ?

Regards,
Neil

View 2 Replies View Related

Dynamically Changing Position Of Textbox

Sep 10, 2007

Hi,

In my report, I've to print the address of the customer in a form which is in a pre-defined format. So while printing, the line number and position should be specified by the user. Is there any ways in SSRS to change the position of a textbox/any control dynamically by inputing the x and y coordinates? If there is a way please let me know that.

Thanks in Advance,
Leks

View 9 Replies View Related

Dynamically Changing The Connection Properties

Apr 18, 2007

I want to transfer data from one server to another by using SSIS. i want the connection string to be dynamic and also according to the some other variable, the transforming data is changing.

Could you provide me the solution thet how i am able to change my connetction string dynamically and the other variable too.

i am using VS 2003 as front end and SQL server 2005 as a backhand.

Due to VS.NET 2003 i am able to create DTS packages but i have to migrate it and then anly i am able to use it in JOB in SQL server agent of SQL server 2005.

is that any code or any stored procedure from which i am able to migrate DTS packages to SSIS packages.

Thank you

View 4 Replies View Related

Advice Needed : Nasty Problem PHP/MS SQL Server And Varchar Fields &> 255 In Length

Jul 20, 2005

I am currently working on a PHP based website that needs to be able to drawfrom Oracle, MS SQL Server, MySQL and given time and demand other RDBMS. Itook a lot of time and care creating a flexible and solid wrapper and amdeep into coding. The only problem is a noticed VARCHAR fields being drawnfrom SQL Server 2000 are being truncated to 255 characters.I searched around php.net and found the following :Note to Win32 Users: Due to a limitation in the underlying API used by PHP(MS DbLib C API), the length of VARCHAR fields is limited to 255. If youneed to store more data, use a TEXT field instead.(http://www.php.net/manual/en/functi...ield-length.php)The only problem with this advice is Text fields seem to be limited to 16characters in length, and I am having similar results in terms of truncationwith other character based fields that can store more than 255 characters.I am using PHP 4.3.3 running on IIS using the php_mssql.dll extensions andthe functions referenced here http://www.php.net/manual/en/ref.mssql.php.What are my options here? Has anybody worked around this or am I missingsomething obvious?James

View 4 Replies View Related

Can Varbinary And Varchar Lengths Be Declared Dynamically?

Mar 25, 2008

I just learned that bit masking a varbinary column can increase it's length in bytes unnecessarily. For example, I ran the following...

declare @v1 varbinary(max)

set @v1 = 0x0100AB

select len(@v1)

set @v1 = @v1 | (len(@v1) - 2) * 256

select master.dbo.fn_varbintohexstr(@v1)

select len(@v1)
...and get
3
0x00000000000101ab
8

This messes up a plan I had for varbinary column use.

So I quickly tried the following to look for strategies to deal with this unwanted growth...thought being that while I'm passed a varbinary(max), operating on a varbinary that matches its passed length would avoid the unwanted growth after bit masking....


declare @i int

set @i = 5

declare @v2 varbinary(@i)

...but got errors.

So I suppose I can use a combo of the len, substring etc functions to correct the situation after bit masking but would like to know if the more elegant approach of dynamically sizing a varbinary is possible in t-sql, or if perhaps there is a way to prevent the unwanted growth during bit masking.

View 6 Replies View Related

SSIS Changing Excel Tabs Dynamically

Mar 25, 2008



In my SSIS package I have a loop container that I am running the same code against 4 servers.
I have the package export the SQL data to an Excel spreadsheet that has multiple tabs.

Is there a way I can change the tab on the fly or do I need to create a Connection for the same spreadsheet 4 times
Each Connection pointing to a different tab?

I tried to set up a expression for the Excel Connection Manager to use the InitialCatalog for the tab and change it
based on the script in the loop however this causes the following error:


An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
Cannot create an OLE DB accessor. Verify that the column metadata is valid.

Thanks in advance

View 22 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved