Changing Table Column Size
Mar 2, 2004
I'm new to mssql.
I need to change the size of a column of a table from char(255) to char(500). I used the line:
alter table table_name alter column column_name char(500)
When I run that command, I get a message that it was sucessful. However, when I try to enter data into the changed column, the number of characters I can enter is still 255. I check the information schema for the column and the 'character_maximum_length' field is 500.
What is the problem here? Is the maximum allowable length for char 255? How can I get a column to have 500 characters?
View 6 Replies
ADVERTISEMENT
Jul 20, 2005
I must increase column (filed) size in existing datebase but without usingEnterprise manager....(Becouse we use MSDE on our clients PCs)The Filed is part of primary and foreign key constraints....And every constraint has diferent index number in each database...for example (PK_something_9e382hjl8), and I don't know how to pick thisvalue before "drop constraint" command.....Thank you very much....
View 1 Replies
View Related
Aug 7, 2015
Impact on performance of changing Text column size.
However, many of them were regarding older releases of SQL Server.
Does the architecture in 2012, 2014 releases make this less of an issue ?
In other words, why bother going from Varchar(1000) to Varchar(50) ?
I'm just thinking maybe there is column compression automatically now.
View 4 Replies
View Related
Jul 20, 2005
Hi All,I am trying to change column name on an existing table. I am using SQLServer 7.As the table is quite big, it is taking quite long time to do it.By the way I could change the column name only through the EnterprizeManager.Is it possible to change the column name using SQL script?Why the change of column name will depend on the size of the table?Thanks for your answer.-Mokles
View 4 Replies
View Related
Jul 10, 2007
Dear All,I'm trying to alter the name of several columns' in a table which gets created in a stored procedure.trying to use:exec sp_rename '#tblBd.week1', '2007_18', 'COLUMN'I get:Server: Msg 15248, Level 11, State 1, Procedure sp_rename, Line 163Either the parameter @objname is ambiguous or the claimed @objtype (COLUMN) is wrong.There is no mistype, the table name and column name are correct.Can temp table's column names not be altered?If yes, how?Thanks in advance!
View 7 Replies
View Related
Jul 20, 2005
This subject has been posted several times, but I haven't seen a goodanswer.Problem:I want to change the order of the columns in a table using T-SQL only.Explanation:After running your code, I want to see the following table...CREATE TABLE [dbo].[TableName] ([First_Column] [int] NULL ,[Second_Column] [varchar] (20) NULL) ON [PRIMARY]look like this...CREATE TABLE [dbo].[TableName] ([Second_Column] [varchar] (20) NULL ,[First_Column] [int] NULL) ON [PRIMARY]Limitations:Don't post if your post would fall in the following categories:1. If you don't think it can be done2. If you think Enterprise Manager is the only way to do this3. If you think I should just change the order of my Selectstatements4. If you want to state that order column doesn't matter in arelational database5. If you want to ask me why I want to do thisWish:Hopefully the answer WON'T involve creating a brand new table, movingthe data from old to new, dropping the old table, then renaming thenew table to the old name. Yes, I can do that. The table I'm workingwith is extremely huge -- I don't want to do the data juggling.Thanks in advance!
View 2 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 7, 2006
Hello,i have a table and if a record is inserted i will test a numeric valuein this table. If the this value is greather as 1 million, than anstatus column should be changed from 'A' to 'B'.Yes and sorry, this is a newbie question.On Oracle this work simple:create trigger myTrigger on tableXasbeginif :old.x 100000 then:new.y:='B'end if;end;ThanksMaik
View 2 Replies
View Related
May 7, 2008
Is there any way to get size of the individual column in a table?
I know we can use sp_spaceused to get the size of the table. But my question is diiferent. I have a table with 50 columns and approx 2 million rows in it. I wanted to know which column is taking most of the space.
Thanks
View 5 Replies
View Related
Oct 25, 2006
I'm trying to write an SSIS package that exports a table that has changing column names to an excel file. The column names change due to the fact that the table is created by a pivot daily. the only thing I'm missing is the ability to dynamically map the tables' columns to the excel destination. Is this possible?
I read in another thread that
"It is not possible to create packages or new objects within packages using SSIS."
I also read in the books online that "The input and the input columns of the Excel destination have no custom
properties." To me this means that I cannot programmatically create or remove columns in the excel destination. Please tell me I'm wrong.
So, to summarize my research so far. In writing an SSIS package, I cannot programmatically create a new excel destination object and I can't manipulate an existing one. I hope I'm wrong. Can anyone help me? (and please correct any wrong assumptions I may have stated)
View 2 Replies
View Related
Jul 8, 2015
Does null column take storage in table ? I created the following table:
drop table [dbo].[TBL_orig]
CREATE TABLE [dbo].[TBL_orig](
[1] [bigint] NOT NULL,
[2] [bigint] NULL,
[3] [bigint] NULL,
[4] [bigint] NULL,
[5] [float] NULL,
[Code] ....
Does null value will take a place even no data insert to this columns and if yes this answer is 4 mb is logical gap ?
View 4 Replies
View Related
Feb 22, 2006
I have added a Slowly Changing Dimension transformation to an SSIS package and have launched the Wizard to edit it. After selecting the source (a SQL Server 2005 instance), if I select a very large table (9+ million rows), I'm encountering two strange behaviors:
1. The wizard hangs for several minutes before displaying the columns from that table.
2. The wizard does not display the primary key column. This, of course, is the column I want to designate as the "business key", but can't because it's not displayed.
I know this is more like a fact table than a dimension, but this is not a data warehouse. This is just a very large table, and I need to update a field in certain records based on the contents of a source text file. Is there another transformation I should use to perform updates?
View 2 Replies
View Related
Apr 16, 2003
What causes SQL Server 2000 to create tables with default column sizes of 8000 for a varchar datatype??
I would assume this can cause significant performance problems.
(see attached)
View 2 Replies
View Related
May 13, 2007
I am able to get reports going with tables sized properly. They look fine on the ReportServer website and I adjust the column widths so that the headings and data look nice. When I set up a subscription to be delivered by "Report Server E-Mail," though, the table formattings get completely distorted.
In particular, I have two tables, with some column headers being two short words (e.g. Max Height). When rendering on the site, I adjust the columns so the full column header is visible on one line. When I receive the email and read it in Outlook, the header row is now about twice as tall and everything is scrunched together. Both the headings and the data in the fields do not format the same as on the website.
The two tables tend to actually have the exact same width in the email version, although occasionally they are a little different (in the web version one is about half as wide as the other). I have tried just making the columns bigger and that has not worked. I've tried making the font sizes smaller, which didn't work. If I do that, leaving the columns the same width, the email version just gets scrunched into a smaller area with the same text-wrapping problems.
If I open the email in a browser (in a web mail interface) the report renders perfectly as on the site.
I have almost all the default settings, and haven't been messing around with page sizes and things like this (except after, to see if that would fix the problem).
Any ideas, similar experiences, or suggestions? If there is a book I should read or any reference you could point me to in order to figure this out would be helpful. I haven't been able to understand this either using web searches or the two SQL reporting services books I have.
View 4 Replies
View Related
Dec 14, 2001
Greetings,
I want to increase the size of a transaction log file. Can I do this while the database is live? If not, what do I need to do?
Thanks!
Rob
View 2 Replies
View Related
Jan 17, 2001
We currently have a hard-drive size of 3.89MB and 3.3MB is being used by tempdb. I have tried shrinking the database truncateonly but this is not working. The problem is that the tempdb file is as large as my C: drive size. In addition can this be moved to another directory. For example can I move the tempdb.mdf and ldf from C: to E:. Any help would be greatly appreciated.
Thanks
View 1 Replies
View Related
Dec 5, 2007
I would like to increase the initial size of a SQL 2005 DB from 150 to 250 GB to prevent automatic autogrowth; would this have any impact in production if you do it on the fly?
Thanks,
Carlos
View 3 Replies
View Related
Jan 27, 2007
Folks:
Is there a way to decrease the initial size of a database/log file? I've noticed you can increase it, but if you decrease it, after you confirm the change and go checking again, you will see nothing happened.
Is there a way? Am I missing anything?!
View 4 Replies
View Related
Dec 5, 2007
I would like to increase the initial size of a SQL 2005 DB from 150 to 250 GB to prevent automatic autogrowth; would this have any impact in production if you do it on the fly?
Thanks,
Carlos
View 1 Replies
View Related
Jun 10, 2014
It is possible to find table size and in that table each row size.
View 4 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
Feb 11, 2004
Is it possible to change the command buffer size??
I need to export data on demand to an excel spreadsheet via a stored procedure. The only way I know how to do this is through a bulk copy command; but my query is much to big for the buffer....
Thanks!!!
View 9 Replies
View Related
Apr 29, 2008
Dear all,
I am having a problem with the footer in my SQL Reporting Services project. I am trying to print a disclaimer in a text box on the last page in the footer. I can get the text box in the footer just fine but as the disclaimer is quite a lengthly multi line text string the footer has to be enlarged in order to fit the whole text box in.
This causes a problem on all the other pages where even though the footer is not displayed on pages before the last page it still takes up space stopping other data from being displayed where the footer would normally be.
Is there a way for me to set the footer size to a default value all all pages except the last page? I just want to enlarge the footer to about 1.5" on the last page to print my disclaimer without effecting the other pages layout.
Many thanks in advance,
Trevor Keast
View 14 Replies
View Related
Jan 17, 2002
Hi,
In my production box is running on SQL7.0 with Merge replication and i want add one more table and i want add one more column existing replication table. Any body guide me how to add .This is very urgent
Regards
Don
View 1 Replies
View Related
Sep 10, 2015
I am loading from SQL Server 2008 to Access 2010 using SSIS. One of the columns in the table I am loading into is a Number datatype and Fieldsize is long integer. The values are being truncated, so I want to change the Fieldsize to DOUBLE. However, when I do that I receive the error below. What should I do? I would like not to change my Windows registry.
This error can be caused by one of the following:
The maximum number of columns allowed in a table or the maximum number of locks for a single file is exceeded.
The indexed property of a field is changed from Yes (Duplicates OK) to Yes (No Duplicates) when duplicate data exists in the table.
An expression is not specified in the Expression property of a calculated field.
If the maximum number of locks per file was exceeded, you can increase the number by editing a registry entry. However, this is not a recommended option.
If you use Registry Editor incorrectly, you could cause serious problems that require you to reinstall the operating system. Microsoft cannot guarantee that you can solve problems that result from using Registry Editor incorrectly. Use Registry Editor at your own risk.
Make a backup of the registry. Find the MaxLocksPerFile registry value by using the Windows Registry Editor, and then increase the value. The MaxLocksPerFile value is saved as part of the following key:
HKEY_LOCAL_MACHINESOFTWAREMicrosoftOffice14.0Access Connectivity EngineEnginesACE
If the Indexed property of a field and duplicate data is located in the table, reset the Indexed property to the previous setting, or remove duplicate records from the table.
While I was loading to the same table a few days ago, I received a warning and the task took approx 9 hours. I am attaching the screen shot.
View 8 Replies
View Related
Jun 4, 2015
How to change column size dynamically depends on content of the column.
View 2 Replies
View Related
Mar 2, 2008
Hi,
i use this script that show me the size of each table and do the sum of all the table size.
SELECT
X.[name],
REPLACE(CONVERT(varchar, CONVERT(money, X.[rows]), 1), '.00', '') AS [rows],
REPLACE(CONVERT(varchar, CONVERT(money, X.[reserved]), 1), '.00', '') AS [reserved],
REPLACE(CONVERT(varchar, CONVERT(money, X.[data]), 1), '.00', '') AS [data],
REPLACE(CONVERT(varchar, CONVERT(money, X.[index_size]), 1), '.00', '') AS [index_size],
REPLACE(CONVERT(varchar, CONVERT(money, X.[unused]), 1), '.00', '') AS [unused]
FROM
(SELECT
CAST(object_name(id) AS varchar(50)) AS [name],
SUM(CASE WHEN indid < 2 THEN CONVERT(bigint, [rows]) END) AS [rows],
SUM(CONVERT(bigint, reserved)) * 8 AS reserved,
SUM(CONVERT(bigint, dpages)) * 8 AS data,
SUM(CONVERT(bigint, used) - CONVERT(bigint, dpages)) * 8 AS index_size,
SUM(CONVERT(bigint, reserved) - CONVERT(bigint, used)) * 8 AS unused
FROM sysindexes WITH (NOLOCK)
WHERE sysindexes.indid IN (0, 1, 255)
AND sysindexes.id > 100
AND object_name(sysindexes.id) <> 'dtproperties'
GROUP BY sysindexes.id WITH ROLLUP) AS X
ORDER BY X.[name]
the problem is that the sum of all tables is not the same size when i make a full database backup.
example of this is when i run this query against my database i see a sum of 111,899 KB that they are 111MB,but when
i do full backup to that database the size of this full backup is 1.5GB,why is that and where this size come from?
THX
View 5 Replies
View Related
Aug 23, 2006
Hi, I have a problem importing data from SQL Server 2000 'text' columns to SQL Server 2005 nvarchar(max) columns. I get the following error when encountering a transfer of any column that matches the above.
The error is copied below,
Any help on this greatly appreciated...
ERROR : errorCode=-1071636471 description=An OLE DB error has occurred. Error code: 0x80004005.An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unicode data is odd byte size for column 3. Should be even byte size.". helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC} (Microsoft.SqlServer.DtsTransferProvider)
Many thanks
View 5 Replies
View Related
Nov 9, 2004
Hi all,
I have a table that has integrity constraints in place and it is populated with the data. I need to rename one of the columns in the table. I am hesitant to use sp_rename procedure because when I used it before I get a warning message that says "changing any part of an object name could break scriptd and stored procedure." how big of a problem is it? Is there any other way to do it without hearting anything? Thanks.
View 1 Replies
View Related
Nov 30, 2005
How can you change a Column name for MS SQL 2000 ?
something like :
ALTER TABLE [dbo].[MyTable]
OldName RENAME NewName
GO
thank you
View 6 Replies
View Related
Jul 20, 2005
ie if i havex 1 2 3 4 5------------1| a b c d e2| f g h i j3| k l m n o4| p q r s t5| u v w x yand i want to change it tox 1 2 3 4 5------------1| a b c d e2| a b c d e3| a b c d e4| a b c d e5| a b c d eany ideas on how to do that?
View 2 Replies
View Related
Sep 20, 2005
I am trying to change the row and column heading to make it more meaningful in MDX.
For example instead of showing the_actual_cost, i want to show cost. This is done easily in SQL like
SELECT the_actual_cost AS cost......
Any help will be greatly appreciated.
Thanks
Mahesh
View 1 Replies
View Related
Dec 2, 2006
Hello I am having a table
table1
col1 (bit)
and i want to changethe col1 type for smallint
col1 (smallint)
true will be = 1
and false = 0
how can i do it ??
thank you
View 3 Replies
View Related
