Extra Space For Column???

Dec 27, 2007

Hi,
I am declaring a table with 2 column Id, and Name.
Id is a integer column but Name is a Varchar.
I am declaring Name as VARCHAR(100) you can say that varchar(100) is more than enougf for a name column. But lets suppose ifn futuer user will enter data only till 100 characters but lets suppose i put or declare it as NAME VARCHAR(4000), That means a column requires only 100 memory space but i am giving 4000 to that column
So in case of SQL any drwbacks are there for this approach. Meaning my 3900 space will not be used so will it a wrong thing or we can declare it as ,,, it will put no effect???
or what are the drawback??

View 3 Replies

Extra Space At Bottom Of Page

Jan 18, 2007

I keep getting extra space at the bottom of each page on my report when it is deployed to the web server but I don't get this extra space on the report server. Does anyone know if this is in the report layout or if the problem is in the web page itself?

Thanks,

View 1 Replies View Related

Extra Column In Destination

Feb 27, 2007

I have worked with straight-forward simplistic SSIS up to now, so I apologize if this is a simple question. I have a SQL table destination that accepts daily data from an AS400 table. We now have a need to datestamp the incoming data so I added a new column to the destination table where I want today's date. Is there a data flow transformation that I need to use to default the destination column to today's date? Time is not a consideration and not needed, just the date.

Thanks for the information.

View 4 Replies View Related

Extra Column In Matrix

Sep 20, 2006

Hello,

I'm trying to make a report with the following layout:

Car Sales 2004 2005 Var %
Total 10 20 50%
Green 7 14 50%
Red 3 6 50%

I'm using a matrix and the data is coming from a cube. One dimension called 'Years' is used to fill the matrix columns and the data corresponding to the car sales is filled by a measure called 'Sales'.
I've built part of the example but I can't add the final column ( the VAR% column).
If I try to add a static column in the end it apears only one year.
The behavior that I would like to have is simillar to the situation when we add the Subtotal column. Although, instead the sum() made by subtotal I would like to calculate the variation percentage between years.
Is there any way to add a final column into a matrix avoiding the problem that I'm having or maybe change the behavior of Subtotal column?

Thanks and best regards.

vjn

View 5 Replies View Related

Add Extra Column In DB Table Through SSIS

Aug 3, 2007

Hi all

I need help ..

I want to create one SSIS...my requirement is

I have text file having three column
like
300;Dev Fot;30097
400;Kit Mol;79684

now i have to insert this in DB with SSIS but at the same time I
want another column insert in DB table with ID
like
1;300;Dev Fot;30097
2;400;Kit Mol;79684

my table structure is
Table_name (ID not null,EmpID not null;desc Not null;FinID Null)

my input to package is my text file path and ID

How shud i process??

T.I.A

View 1 Replies View Related

Aggregate Functions - Adding Extra Column With Another Field

Jun 13, 2014

I have a simple query like:

select max(PTR_DATE)
from MPR_portfolio_transactions
group by PTR_SYMBOL

and this is working fine, but if I add an extra column with another field like:

select max(PTR_DATE) , PTR_SHAREBALANCE
from MPR_portfolio_transactions
group by PTR_SYMBOL

Then I get an error message like:

Column 'MPR_portfolio_transactions.PTR_SHAREBALANCE' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

How can I get round to this?

View 6 Replies View Related

Show/Hide Extra Column Without Running Report Again

Mar 9, 2007

Hi all,

Is it possible to show or hide columns in a report on the fly, i.e. without having to run the report again?

I have a report in with a parameter to let the user choose between the data to show. However the customer doesn't like it that he has to rerun the report. Any help would be appreciated!

Regards,

Henk

View 3 Replies View Related

Displaying Extra Characters With COLUMN-defined Number Format?

Jul 20, 2005

Here's a tricky SQL question that has definitely driven me to the end ofmy rope. I'm using Oracle 9i and I need to perform some simplemultiplication on a field and then display it with a percent sign usingthe COLUMN command. Here's the code thus far:COLUMN price format 9,999.99 HEADING 'Charged%'SELECT pricecharged * .231 as priceFROM VT_examdetailThe output from this reads:Charged%---------23.1034.6534.65....The kicker here is that I need to add a percent sign to the right of theoutput, so that it reads:Charged%---------23.10%34.65%34.65%....I thought I could do this by just adding "|| ('%')" into the SELECTstatement, but when I do this the decimal position defined in the COLUMNcommand is lost. Does anyone know another way around this?Thanks,Alex

View 3 Replies View Related

Reporting Services :: Getting Extra Column While Exporting SSRS Report Into Excel

Nov 15, 2012

I am getting extra column while exporting SSRS report into excel but those columns not in my report. How to avoid these columns please find the attached screen shot with red circle.

View 8 Replies View Related

Matrix Page Break, Extra Space Displayed Before Page Break

Dec 6, 2007

I have a report with several matrix objects. The data contained in each matrix is simple. One matrix has one column of labels and a column of data. The other has 3 columns of data. Both matrix's only show about 25 rows of data. A variable amount of data is displayed above the matrix's so that sometime the render across a page boundary. Whenever this happens the matrix doesn't render rows down to the bottom of the page. A considerable amount of empty space is left on the page, the completed matrix is displayed on the next page. I'm wondering how to get the matrix to render in this empty space.

This problem only occurs when I view the report on screen, in the Visual Studio "Preview" window, or from the Report Server web site. Tiff, pdf and printed output doesn't contain the extra space.

I've tried adding my matrix to a List and a Rectangle to see of this would fix the problem but it didn't help.

I've check the dimensions and margins or the page and I don't think I have any sizing problems, everything should fit on the page.

--Mark

View 1 Replies View Related

Column Space Utilization

Aug 3, 2007

Hello

I created a table with column name "description" as varchar(8000). My doubt is if I am not storing 8000 characters in this column, will SQL Server use memory space needed for 8000 characters ? or will it use only the space that needs for my text. ?

Thanking You
Navaneeth

View 8 Replies View Related

Column Delimiter As A SPACE?

Jul 25, 2007

I'm in a flat file connection manager editor and I have a flat file where the columns are separated by a space. Does anyone know how to specify a space in the column delimiter option? I've tried {' '} and {s} but these don't work. Not sure what the syntax is for indicating a space. Thanks in advance

View 1 Replies View Related

Reclain Space After DROP COLUMN

Jul 23, 2005

I have a huge table in it own tablespace (250 GB). I just deleted more thanhalf of the columns in that table, but I didn't see any change in allocatedspace. I did DBCC CLEANTABLE and DBCC SHRINKFILE, but nothing. MS also saysthat CLEANTABLE only reclaims space for columns with text, char datatypes.How can I get space back for integer, decimal, number datatypes? I'm justall out of ideas.

View 7 Replies View Related

Please Help!! How To Find Space At End Of Data In A Column

May 14, 2008

I need a query that checks each row in a column (hardness) to see if the last character is a "space". The column holds numbers (73.45) but is a varchar 50 type. It will always be the 5th character but it would be nice to not make this assumption. Please help!!
Thanks.

View 5 Replies View Related

Does A Column That Takes Null Value Use Up Disk Space?

Apr 7, 2008

I have a table in which there is a column called 'Forecast' of type 'int', that is null for about 25% of the rows in this table.
Will null value for this column consume storage bytes?

View 2 Replies View Related

Release Table Space After Column Is Dropped

Apr 12, 2008

I hv dropped the useless columns for a table but the table size is remain unchanged by executing sp_spaceused. pls advise how to release table spaces.

thx

View 6 Replies View Related

Transact SQL :: Table With SPARSE Column Need More Space?

Jul 24, 2015

As I understood, if SPARSE is used on a column, which have many NULL marks, then the storage could be efficently used (we need less spaces to save NULL marks, hence a table which has many NULL marks with SPARSE property needs less storage than the same table, but without SPARSE. I created two table as follow:

/******* Table with Sparse ******/CREATE TABLE Sprstb(
unsprsid INT IDENTITY(1,1) NOT NULL,
Firstname varchar(20) NOT NULL,
Lastname varchar(20) NOT NULL,
Tel int NOT NULL,
adress nvarchar(60) SPARSE NULL)/***** Table without Sparse*******/CREATE TABLE Unsprstb(unsprsid INT IDENTITY(1,1) NOT NULL,Firstname varchar(20) NOT NULL,
Lastname varchar(20) NOT NULL,
Tel int NOT NULL,
address nvarchar(60) NULL)

I have populated the Sprstb with 5 Milion records. It needs 509,961 MB storage. Then I have copied this table into Unsprstb

SET IDENTITY_INSERT [dbo].[Unsprstb] ON
Insert [dbo].[Unsprstb](unsprsid,Firstname,Lastname,Tel,adress)
SELECT unsprsid,Firstname,Lastname,Tel, adress FROM [dbo].[Sprstb]
SET IDENTITY_INSERT [dbo].[Unsprstb] OFF
The Unssprstb need only 466,031MB !

That means the Table with SPARSE column need more storage, Why?

By the way, in table Sprstb column address has 1666198 Null mark (from 5000000)

View 5 Replies View Related

How To Set Empty A Space From A Derived Column Expression?

May 30, 2006

hi,

This field comes from a flat file and sometimes own zero and sometimes own a empty position (after 'EUR'):

2006053000499236000005307700108287457080200408287452006052953990000000010000EUR
2006053000499236004414989200101423426004400501423422006052953990000000010000EUR0

[Column 12] == "0" ? [Column 12] : ?????????

TIA

View 7 Replies View Related

Transact SQL :: Alter Table To Rename Column Name With No Space

Oct 27, 2015

I want to alter my all tables to change the name of all columns.

I need this because all column names were created with space and I want to remove the space for future work .

For example – In Table Customer there is a column name [Cust_Id ] and I want to change it with [Cust_Id]

View 11 Replies View Related

How To Calculate The Space (size In Bytes) Used By A BLOB Column In A Row

Dec 3, 2007

What is the easiest way to calculate the space (size in bytes) used by a BLOB column that is already stored in a particular row?

Thanks.

View 3 Replies View Related

Question About Converting Column Datatypes In Order To Save Space

Nov 6, 2007

All,

I've converted datatypes in the past to something more appropriate in order to save space, and speed up the database, to great success. I'm in the middle of another such change, this one table in particular I am converting from [text] columns to varchar(max)'s, and varchar(1)'s to char(1)'s. I've made a duplicate of the table in question (which has about 25 text columns, and 20 varchar(1) columns), ported over the data, and ran 'sp_spaceused' and got the following results:

-- new table
name | rows | reserved | data | index_size | unused
Response | 39920 | 15384 KB | 15168 KB | 208 KB | 8 KB

-- old table
name | rows | reserved | data | index_size | unused
Response | 39920 | 124128 KB | 123696 KB | 200 KB | 232 KB

I didn't expect results quite this dramatic. Now to my question: Did I miss something measuring the difference? Is there something else, another method I should employ to determine the size differentials?

If this is right, it's outstanding, just unexpected. I would have been happy losing 10 or 20 MB.

I appreciate any insight anyone might be able to provide.

Best,
B.

Just fyi - here are the table schemas:

-- old table
CREATE TABLE [dbo].[Response](
[ResponseID] [varchar](30) NOT NULL,
[Term] [varchar](5) NOT NULL,
[Subject] [varchar](4) NOT NULL,
[Course] [varchar](4) NOT NULL,
[Sect] [varchar](3) NOT NULL,
[MidEndFlag] [varchar](3) NOT NULL,
[SID] [varchar](9) NOT NULL,
[TemplateID] [varchar](30) NOT NULL,
[LastModified] [datetime] NULL,
[College] [varchar](30) NULL,
[Classification] [varchar](10) NULL,
[CourseRequired] [varchar](3) NULL,
[ExpectedGrade] [varchar](10) NULL,
[Sex] [varchar](6) NULL,
[ItemAnswer1] [varchar](1) NULL,
[ItemComments1] [text] NULL,
[ItemAnswer2] [varchar](1) NULL,
[ItemComments2] [text] NULL,
[ItemAnswer3] [varchar](1) NULL,
[ItemComments3] [text] NULL,
[ItemAnswer4] [varchar](1) NULL,
[ItemComments4] [text] NULL,
[ItemAnswer5] [varchar](1) NULL,
[ItemComments5] [text] NULL,
[ItemAnswer6] [varchar](1) NULL,
[ItemComments6] [text] NULL,
[ItemAnswer7] [varchar](1) NULL,
[ItemComments7] [text] NULL,
[ItemAnswer8] [varchar](1) NULL,
[ItemComments8] [text] NULL,
[ItemAnswer9] [varchar](1) NULL,
[ItemComments9] [text] NULL,
[ItemAnswer10] [varchar](1) NULL,
[ItemComments10] [text] NULL,
[ItemAnswer11] [varchar](1) NULL,
[ItemComments11] [text] NULL,
[ItemAnswer12] [varchar](1) NULL,
[ItemComments12] [text] NULL,
[ItemAnswer13] [varchar](1) NULL,
[ItemComments13] [text] NULL,
[ItemAnswer14] [varchar](1) NULL,
[ItemComments14] [text] NULL,
[ItemAnswer15] [varchar](1) NULL,
[ItemComments15] [text] NULL,
[ItemAnswer16] [varchar](1) NULL,
[ItemComments16] [text] NULL,
[ItemAnswer17] [varchar](1) NULL,
[ItemComments17] [text] NULL,
[ItemAnswer18] [varchar](1) NULL,
[ItemComments18] [text] NULL,
[ItemAnswer19] [varchar](1) NULL,
[ItemComments19] [text] NULL,
[ItemAnswer20] [varchar](1) NULL,
[ItemComments20] [text] NULL,
[EssayQuestionAnswer1] [text] NULL,
[EssayQuestionAnswer2] [text] NULL,
[EssayQuestionAnswer3] [text] NULL,
[EssayQuestionAnswer4] [text] NULL,
[EssayQuestionAnswer5] [text] NULL,
CONSTRAINT [PK_Response] PRIMARY KEY CLUSTERED
(
[SID] ASC,
[TemplateID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

-- new table
CREATE TABLE [Test].[Response](
[ResponseID] [varchar](30) NOT NULL,
[Term] [varchar](5) NOT NULL,
[Subject] [varchar](4) NOT NULL,
[Course] [varchar](4) NOT NULL,
[Sect] [varchar](3) NOT NULL,
[MidEndFlag] [varchar](3) NOT NULL,
[SID] [varchar](9) NOT NULL,
[TemplateID] [varchar](30) NOT NULL,
[LastModified] [datetime] NULL,
[College] [varchar](30) NULL,
[Classification] [varchar](10) NULL,
[CourseRequired] [varchar](3) NULL,
[ExpectedGrade] [varchar](10) NULL,
[Sex] [varchar](6) NULL,
[ItemAnswer1] [char](1) NULL,
[ItemComments1] [varchar](max) NULL,
[ItemAnswer2] [char](1) NULL,
[ItemComments2] [varchar](max) NULL,
[ItemAnswer3] [char](1) NULL,
[ItemComments3] [varchar](max) NULL,
[ItemAnswer4] [char](1) NULL,
[ItemComments4] [varchar](max) NULL,
[ItemAnswer5] [char](1) NULL,
[ItemComments5] [varchar](max) NULL,
[ItemAnswer6] [char](1) NULL,
[ItemComments6] [varchar](max) NULL,
[ItemAnswer7] [char](1) NULL,
[ItemComments7] [varchar](max) NULL,
[ItemAnswer8] [char](1) NULL,
[ItemComments8] [varchar](max) NULL,
[ItemAnswer9] [char](1) NULL,
[ItemComments9] [varchar](max) NULL,
[ItemAnswer10] [char](1) NULL,
[ItemComments10] [varchar](max) NULL,
[ItemAnswer11] [char](1) NULL,
[ItemComments11] [varchar](max) NULL,
[ItemAnswer12] [char](1) NULL,
[ItemComments12] [varchar](max) NULL,
[ItemAnswer13] [char](1) NULL,
[ItemComments13] [varchar](max) NULL,
[ItemAnswer14] [char](1) NULL,
[ItemComments14] [varchar](max) NULL,
[ItemAnswer15] [char](1) NULL,
[ItemComments15] [varchar](max) NULL,
[ItemAnswer16] [char](1) NULL,
[ItemComments16] [varchar](max) NULL,
[ItemAnswer17] [char](1) NULL,
[ItemComments17] [varchar](max) NULL,
[ItemAnswer18] [char](1) NULL,
[ItemComments18] [varchar](max) NULL,
[ItemAnswer19] [char](1) NULL,
[ItemComments19] [varchar](max) NULL,
[ItemAnswer20] [char](1) NULL,
[ItemComments20] [varchar](max) NULL,
[EssayQuestionAnswer1] [varchar](max) NULL,
[EssayQuestionAnswer2] [varchar](max) NULL,
[EssayQuestionAnswer3] [varchar](max) NULL,
[EssayQuestionAnswer4] [varchar](max) NULL,
[EssayQuestionAnswer5] [varchar](max) NULL,
CONSTRAINT [PK_Test_Response] PRIMARY KEY CLUSTERED
(
[SID] ASC,
[TemplateID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

View 5 Replies View Related

How To Import Text File(no Space, No Symbol, No Column) Into Mssql

Dec 24, 2005

the text file format use the length of character to define the field

for example,
0001130130HAUT BAGES AVEROUS 03

9 chars <0001130130> is a field
1 char <H> is a field
20 chars <AUT BAGES AVEROUS 03> is a field

one record by one record store in db
no space, no symbol, no line break between each record
----------------------------------------------------------

I try bcp method, but some error happens. Please give me suggestions. thx

I run the following code in query analyzer.

BULK INSERT Chain.dbo.POLL59
FROM 'D:POLL59.DWN'
WITH (FORMATFILE = 'D:cp.fmt')

?????
it shows the error

Server: Msg 4839, Level 16, State 1, Line 1
Cannot perform bulk insert. Invalid collation name for source column 4 in format file 'D:cp.fmt'.

***
For your information
data file
000000011301220051222000192000000000011301320051222000030000000000019067420051222000000001<there are many space >

bcp.fmt file <I use tab to separate and use ascii>
8.0
4
1 SQLCHAR 0 4 "" 1 PLUEVT ""
2 SQLCHAR 0 9 "" 2 PLUSKU ""
3 SQLCHAR 0 8 "" 3 PLUFRD ""
4 SQLCHAR 0 9 "" 4 PLUPRC ""

!!!!
I try to edit collation name in Chinese_Taiwan_Stroke_CI_AS or others, but the error also happens.

View 6 Replies View Related

T-SQL (SS2K8) :: Blank Space In Integer Data Type Column View

Apr 4, 2014

I am dealing with what I believe is Oracle that is the source of a SQL View.

I am seeing a data type of Integer in the View, but I am not able to see what makes up that View. When I query the View, I can see that an Integer data type column is storing a blank space. I use ISNUMERIC(ColumnName) = 0 and there are a lot of rows that show as a zero length blank space, or text, or something. I just know that it is not an Integer.

I have attempted to CAST and Convert this value, but it will not. I have changed the data type on the table that is being inserted in too, and it still fails with a Conversion error. I have tried REPLACE(), but still the same conversion error.

View 1 Replies View Related

Reporting Services :: Decrease Space Between Columns In Stacked Column Chart

Sep 9, 2015

I have another query regarding the graph/chart that i made in report builder. May i know on how to lessen the space between a column in my stacked bart chart.

View 5 Replies View Related

How To Replace Empty Space Or White Space In A String In A Stored Procedure

Nov 14, 2007

Hi,
I am trying to do this:
UPDATE Users SET uniqueurl = replaceAllEmptySpacesInUniqueURL('uniqueurl')
What would be the syntax.
Any help appreciated.
Thanks

View 1 Replies View Related

Mutilple Space Gets Converted To Single Space In Report Viewer Control

Feb 23, 2007

I am generating a Report from Sql Data Source in Sql Server 2005 and viewing the Report in Report viewer control of Visual Studio 2005.
The data in the Data Source contains string with multiple spaces (for example �� Test String ��) but when they get rendered in Report viewer control, multiple spaces gets converted to single space �? Test String ��.

I tried following solutions
1) Replacing spaces with �� �?
2) Inserting <pre> tag before the string and </pre> tag after the string (Also tried <Pre> instead of <pre>)

But in all the cases result is same. The Report Viewer control is showing ��&nbsp�? instead of space and ��<Pre>�? tag instead of preserving spaces.

Please provide me a solution so that spaces can be preserved in Report Viewer.

View 1 Replies View Related

Transact SQL :: How To Find Space Available Or Send Space Alerts In Percentage

Nov 26, 2015

I am using the below script to get space alerts and now i am interested in sending alerts if for any drive space available is Less than 10% or 15%.. how to convert beelow code to find in %

Declare @Drives Varchar(20)
DECLARE @Spaces Varchar(50)
DECLARE @availableSpace FLOAT
DECLARE @alertMessage Varchar(4000)
DECLARE @RecipientsList VARCHAR(4000);
CREATE TABLE #tbldiskSpace

[Code] ....

View 3 Replies View Related

Trans Log-&>space Allocated 27GB, Space Used 100MB

Mar 2, 2005

Hi.. I was doing a good maintenance on my DB and my trans log LDF keep growing until 30GB but my DB data file MDF is only 2GB. I found the two following method to reduce my log size.

Method 1: I used veritas to backup log file with truncate
Method 2: I used the shrink database option in Enterprises manager to shrink it (file chosen=log , use default option)

After doing that, I found my LDF log file is still about the same size=27GB but when I see clearly, from the shrink database windows, the log spaced used reduced to only 100MB, the allocation log space is still 27GB. Why? How to make the LDF smaller to be the around the same size as the space used 100MB?

View 1 Replies View Related

Double Space Replaced With Single Space By Dbms ??!

Jul 20, 2005

This is driving me bananas. Can't find any info on this anywhere....SQL 2000 seems to replace double space with a single space when I seta varchar field to " " (2spaces), it only stores " " (1space). Whyon earth would microsoft do this? If I save 2 spaces - I WANT TO SEE2 SPACES!!!!Can anyone help? Is this a database setting? Is this due to usingvarchar?Any help appreciated.Colin Hale

View 2 Replies View Related

Problem With Space Allocated For Transaction Log Space

Dec 5, 2001

Hello,

Somebody know how to reduce the space allocated for the transaction log space for my SQL_DB ?

3700 MB allocated but only 100 MB used and 3600 MB are free !

Transaction log properties :
Automatically grow file are filled
file growth by percent = 5%
maximum file size - restrict filegrowth = 3700 MB (we can't reduce it !)

Thank you for your precious help !
Khaix from Brussel.

View 1 Replies View Related

Suppress Multiple Space To Single Space..

Nov 14, 2006

How do we suppress multiple spaces to a single space in T-SQL

E.G.

Field: FullName

e.g.

WOMENS HEALTH RIVER VALLEY
JOHN FAMILY MED GROUP
HERSH STWEART P.
PARK HEIGHTS MEDICAL CENTER
KOPP WHITEFIELD E

The o/p wanted is

HERSH STWEART P.

Thank you.

View 3 Replies View Related

Extra Row

Jan 18, 2007

hi all,

i wonder if i could break one row to multiple row base on this case :-

status=A OriginQty=10 HoldQty=3

i want to print the status=H for the remaining originqty-holdqty
isit possible to digest this data in my SP to:-

status=A BalQty=7
status=H BalQty=3

~~~Focus on problem, not solution~~~

View 2 Replies View Related

Extra MDF File ??

Jan 17, 2008

HI
When I'v click in Visual Web Developer 2008 to build web site it has made a new MDB file name ASPNETDB. MDF
So now I have 2 mdb file (MyFileName.mdf and ASPNETDB. MDF)
What is that extra (ASPNETDB. MDF) file and do I need it and why?
Thanks assaf

View 1 Replies View Related