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