SQL White Space! HELP!
Oct 4, 2006
Ok, here is the problem.
Client created a database with Allocated Space of 5GB. THEY WILL ONLY EVER HOLD A MAXIMUM OF 1GB IN DATA!!! Don't ask.
In any case, they currently have a database filled with 600MB of Data and 4.4GB of White Space. From what I can see, the database is backing up that entire database nightly, based on 5GB of allocated space. It doesnt care that only 600MB is being used, it only knows that someone put aside 5GB and its gonna darn well back it up.
I need to get rid of that space. Their Backups are taking hours on end and Batch Files are timing out. Quite honestly, they dont need to back up 4.5GB of white space.
QUESTION:
How can I eliminate that white space? I have tried to run several SQL scripts to Truncate the DB and eliminate unused space, unfortunately it is not working.
Any help would be GREATLY appreciated.
Matt.
View 14 Replies
ADVERTISEMENT
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
Oct 1, 2006
I think this may be a dumb question, but here goes.If there is a lot of "white space" in a field in SQL Server, does it take upserver space, or is it just ignored?Example:Name of product<br>Manufacturer's Name<br>Manufacture's Phone number<br>Instead of this:Name of product<br>Manufacturer's Name<br>Manufacture's Phonenumber<br>I think it would, but maybe not?Thanks, J~
View 1 Replies
View Related
Aug 10, 2007
Good day to you, community friend!
Just a quick one - I swear I've seen a function before that was like Len() but it counted the white spaces too. I have a feeling it was something along the lines of "data_length" but I can't for the life of me find it!
Many thanks,
George
View 7 Replies
View Related
Aug 2, 2007
I am hoping there is either a quick fix within SQL reporting services or a 3rd party tool for SQL reporting services that will fix my error. If not, we will be forced to go back to Crystal Reports.
I have a large report, that per each record prints between 3 and 5 pages of information. It is laid out like an MS Word document (as that is what is needed to send off to the client), so it is in standard letter style with the text growing down vertically.
I have about 10 large text (SQL Server Text field types) fields on this report. The report itself was built using the SQL Reporting Services table tool, so I could correclty group the information into the same record and also to keep the needed specific report formatting.
Everything looks GREAT, except for large white space in the report. The cause is that each row on the SQL Reporting Services table contains a field of data. Well, in some instances there is too much text to fit on the current page of the report (in some cases as much as 3/4 a page of paper), so instead of starting to print on the page and then continuing to print on to the next page, the data will skip the page and start at the top of the next page.
This will leave large white spaces in my report. In one instance I have a Text field that with the correct formatting is larger than a page. So the report prints on 1/4 of page 2, is blank for remander 3/4 of a page 2, and then starts printing the large text field on the following page (page 3). So 3/4 of a page in my report is left empty.
Is there a work around for this, or a 3rd party tool that can be purchased to allow for this? I have not found a setting or a way to keep the table rows together as there is in Crystal Reports.
Thank you for your help!
T.J.
View 3 Replies
View Related
Dec 24, 2007
White space
In SSRS the controls(chart and table) are hidden .Some white space occured for each full page. How to avoid such white space (blank page) for every page.
In our report there is five Controls and some of the controls are to be hidden. And that some pages having only white space. How to avoid such blank spaces.
Regards
Mugesh
View 3 Replies
View Related
Jul 20, 2015
I am trying to print only the grand total raw. The grand total is based on the information in the groups and therefore I cannot hide the groups.
Instead I am trying to minimize the size of the group raw by sizing font, shrink, etc but with no success. I still get a lot of unwanted space. How can resolve this?
View 2 Replies
View Related
Mar 27, 2008
Hello everyone,
In reports ,My customer requirement is to display column based on selected criteria in UI .
The columns which are not selected by him will hide.
for that we kept an expression in Visibility --> Hide
Code Snippet
= NOT Parameters!Parameters.Value.ToString().Contains("Name")
then coming to HTML Report
It is working fine,but white space coming at end of the Table.
can't we supress the white space?
The white space width is exactly the width of the column which is hidden.
My designing in layout is wrong?
Else is that Problem with the SSRS?
Experts Please let me Know!!!!
Give me Solution!!!
Customer is strictly focusing on that requirement.
***Note: white Space is Some what Acceptable.But My Reports are very big like 45 columns around.When he selects 10 out of 45 then you can assume how much space is coming????????****
View 4 Replies
View Related
Jul 27, 2007
I have a main report and a subreport. If the subreport doesn't have any data then I hide it, but the main report still shows the white space of where the subreport would show up if it had data. So how can I get rid of the extra white space in the main report?
Thanks,
Han
View 3 Replies
View Related
May 16, 2006
Hello
I have created a report that works how I want it in the visual studio 2003 designer. I have deployed it to our test box (sql server 2k, reporting services with sp2) and when we preview the report, there is a giant white space between the graph in the report and the matrix object below it. This white space was not in the designer. It only shows when previewing the report. If you print from the report manager, it does not print the white space.
Is there a way to remove it from the report manager when the report is previewed? I am sure users will complain when they see this.
Thanks.
Ben
View 2 Replies
View Related
Sep 28, 2015
I build a tablix A in SSRS, which contains a tablix B and a textbox C. When the report is exported to word,  I found that there is white space between the tablix B and textbox C if the row of tablix B is spilled over to next page.
If I export the report to PDF, the white space issue is gone.
View 2 Replies
View Related
May 15, 2007
I have a report that (for purposes of this example) displays States and the Cities in a state.
Now the State is the first group which I want to drill down into by expanding and then show the Cities as the detail line.
What I would expect to happen (and what does happen if I use the wizard instead of creating the report manually) is that I would click on a State and then the page would expand to make room for all the cities listed below.
(That is to say if I am showing only states, not expanded, then all 50 states fit on a single page. Then if I expand California all the states below that shift onto other pages to make room for the 400+ cities in California)
However, what happens is that when I preview the report instead of being 1 page long it is many pages in length with empty white space between the States (white space that would be occupied by the cities if I were to expand the state).
Can someone tell me what setting I need to adjust to get the report to dynamically resize when I expand/collapse groups?
Thank you.
View 1 Replies
View Related
Feb 12, 2015
I have some sql below..
SELECT
t.Doctor, t.LedgerAmount, t.TransactionDate,
ISNULL(lg.LedgerGrpDesc, 'No Sales Group') AS LedgerGroup
FROM
Transactions t
LEFT OUTER JOIN LedgerGroups lg ON t.LedgerDescription = lg.dbLedgerDesc
[Code] .....
My problem is that the data in t.LedgerDescription sometimes now has either leading/trailing white space or more likely special chars so the join against lg.dbLedgerDesc doesn't always work.
I can't change the source of the data to strip out special chars/white space so am stuck on how to deal with it.
I tried using LTRIM & RTRIM in the where clause but this doesn't seem to have had any effect...
LEFT OUTER JOIN LedgerGroups lg ON LTRIM(RTRIM(t.LedgerDescription)) = lg.dbLedgerDesc
View 7 Replies
View Related
May 26, 2008
Hi Team,
When i view the Report from SSRS Report preview Tab it's working fine, But when i deploy that and try to view in the IE
I am seeing the Body background color in between the image and page border of the page footer how to solve that?
View 1 Replies
View Related
Mar 22, 2007
When I go to preview mode and select the print preview. The white background is black and the black is white. Any one know how to set this back to the original way it worked?
View 3 Replies
View Related
Jan 8, 2002
Creating a text file using DTS, is there a function/way to take out white
spaces from columns. Example:
'1234 ','567 ' would come in text as
'1234','567'
Thanks in advance.
View 2 Replies
View Related
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 €œ €? 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
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
Jan 10, 2007
Dear Forum Members,
We've been working on a white paper targeting SSIS connectivity which we hope will help answer some of the key questions in the following areas :
What are the SSIS components and their support level for ADO.NET, ODBC, and OleDB?
How to deal with 64-bit connectors? what is supported, what is not?
Special sections on popular data sources such as SAP, Oracle, DB2, Flat File, XML.
A comprehensive list of data sources and available connectors from Microsoft and other 3rd parties.
You'll also find answers to why some of the things are the way they are today.
Note that this white paper is currently under official editing and publishing in Microsoft. It'll be a while before it goes public officially, but I wanted to share it with you, as the rich content it offers can't really wait. You'll find the paper in my blog, which is really a wiki site about SSIS connectivity fully open to public, so feel free to add/update content in there as you feel proper, and help the SSIS community with your wisdom!
A lot of feedback went into this white paper not only from Microsoft, but also from some of our partners and MVPs. I'd like to extend special thanks to Bob Beauschemin for authoring this challenging white paper.
Enjoy!
Deniz Erkan
Program Manager - SSIS
View 7 Replies
View Related
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
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
Jan 31, 2006
Hi,
Whenever I insert a record into my table it adds trailing white spaces up to the amount of char's that the field is set to allow. Obviously I don't want it to do this.
Among other problems then when I get the data back out it has a ton of white spaces, which normally wouldn't be a problem. i could just use the .Trim() function, but for some reason when I bind the data to a drop down list and use the .Trim() function it doesn't trim the white spaces.
Anyways any ideas on how to make it so the white spaces don't get put in in the first place?? Or any other thoughts on this?? Thanks!
View 3 Replies
View Related
May 30, 2008
What is the Select statement to remove white spaces between words?
View 4 Replies
View Related
Jan 23, 2008
When I exported report to PDF, I am getting a white page alternately between my report pdf pages.
What is the problem behind that? Please suggest a solution.
Thank you very much.
Praveen Kumar. M
View 9 Replies
View Related
Nov 14, 2006
Hi All,
I have a column which has some white spaces that I suspect is tab delimeted one. So when I use a rtrim(col1) it would not trim those. So i used a scrip component and wrote this line,
Row.trimDetail = RTrim(Row.detail)
here trimdetail is an o/p column and detail is the input col with the trailing spaces.
but still I don know why the column has that spaces. Can someone help me to figure out what is the problem ?
Thanks in advance,
View 11 Replies
View Related
Apr 24, 2006
Hi, I'm inserting a few columns into my db (they all have a nvarchar(50) ).. but i noticed when i retrieve them out of the db, the length of the string always have some trailing white spaces behind them and such when I try to do stuff like dropdownlist.items.findbyvalue(), it normally fails.I did trace and before the string get into the db, they were teh right length. so I'm not sure where did I do things wrong? thanks
View 1 Replies
View Related
Sep 15, 2005
I have a table . It has a nullable column called AccountNumber, whichis of varchar type. The AccountNumber is alpha-numeric. I want to takedata from this table and process it for my application. Before doingthat I would like to filter out duplicate AccountNumbers. I get most ofthe duplicates filtered out by using this query:select * from customerswhere AccountNumber NOT IN (select AccountNumber from customers whereAccountNumber <> '' group by AccountNumber having count(AccountNumber)[color=blue]> 1)[/color]But there are few duplicate entries where the actual AccountNumber issame, but there is a trailing space in first one, and hence thisduplicate records are not getting filtered out. e.g"abc123<white-space>" and "abc123" are considered two different entriesby above query.I ran a query like :update customers set AccountNumber = LTRIM(RTRIM(AccountNumber)But even after this query, the trailing space remains, and I am notable to filter out those entries.Am I missing anything here? Can somebody help me in making sure Ifilter out all duplicate entries ?Thanks,Rad
View 3 Replies
View Related
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
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
May 3, 2007
Hey guys, i finally connected to the db fine however for some reason i cannot pull the data. I was hoping somebody here might be able to help me. The code is below
Function MyQueryMethod() As System.Data.DataSet Dim connectionString As String = "server='xxxx'; user id='xxxx'; password='xxxxxx'; database='wel"& _ "sh_indiv'" Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "SELECT [test12].[name], [test12].[grade] FROM [test12]" Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand dbCommand.CommandText = queryString dbCommand.Connection = dbConnection
Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter dataAdapter.SelectCommand = dbCommand Dim dataSet As System.Data.DataSet = New System.Data.DataSet dataAdapter.Fill(dataSet)
Return dataSet End Function
im kinda new to programming with vb for asp.net pages so please bare with me.
View 2 Replies
View Related
Feb 14, 2007
Hello All!
Many of you have seen the draft version, but we finally have it out the door. You can download the new version directly from Microsoft's official site.
The momentum in our connecitivty wiki is growing! so check back and see if you can get useful information on 64bit, Office 2007 connecitivity, and samples.
Some of the recent activity in our connectivity portal:
***************
The new connectivity white paper is now officially available, click here (http://download.microsoft.com/download/2/7/c/27cd7357-2649-4035-84af-e9c47df4329c/ConnectivitySSIS.doc) to download.
***************
There's a new article from Deniz Erkan on Office 2007 Connectivity (Data Sources/Microsoft Office (2007)).
***************
Bob Beauschemin provided us a sample package to connect to Excel 2007.
***************
Microsoft's Partner ETI has a new separate page for connectivity offerings for SSIS (Data Sources/ETI High Performance Data Integration).
***************
Microsoft's Partner Persistent (Data Sources/Persistent Systems Products for SSIS) has put together a list of options for SSIS connectivity.
********************************************************************
View 4 Replies
View Related
Dec 27, 2007
I spent huge amount of time figuring out how to preserve lading and trailing white spaces on report display without success. Can anyone help me here?
My problem is I have data with leading and or trailing white spaces and I need to show it as is. In designer preview it shows correct values. As soon as report is published and accessed on web, it truncates the whitespaces . I had a look at source, it shows values are correctly fetched(with spaces) but are ignored while rendering. I also tried replacing blank space with , however it reads this as &nbsp;.
I am using asp.net 2.0 and SQL serer 2005 reporting services.
View 4 Replies
View Related
Dec 3, 2007
I have a co-worker who applied build 3161 to SQL Server 2005. The original problem was that the print preview was showing up as black.This is a documented issue with Microsoft here:
http://support.microsoft.com/kb/935436
Now, when she goes into expression builder, the foreground AND background color for what you type in there appears to be white... so everything shows up as invisible. You can highlight the text you type and it shows up but otherwise it is obviously invisible.
Anyone know how to fix this?
View 1 Replies
View Related
