I want to add some spaces on the starting feild value. Please see the below example
" " & Fields!Name.Value
This is working on my BIDS. But the spaces are removed automatically when I deployed the report to the report manager. What should be the problem.
Hi,
I am trying to do this:
UPDATE Users SET uniqueurl = replaceAllEmptySpacesInUniqueURL('uniqueurl')
What would be the syntax.
Any help appreciated.
Thanks
Hello
I am creating the following table
CREATE TABLE [dbo].[myT] (
[name] [varchar] (100) NULL
)
if I do
INSERT INTO myT (name) VALUES ('A')
i get in the table a column with
A ...
the 100 char place is full even if there is a data with only 1 char
how is it possible to avoid it ?
I want 100 char maximum but not full with nothing
thank you for helping
Hi
I have datacloum called 'code'. When I use @imglink+''+code+''+'.jpg' as [main-image-url],'in my stored procedure. I should get the folowing
http://www.batterymasters.co.uk/ImageResizeCache/productImages/Pack/ACCFUFN076360A.jpg
But I am getting like the one below
http://www.batterymasters.co.uk/ImageResizeCache/productImages/Pack/AABFUFN076360A. jpg
So, some of them are fine but most of them seperated by blank space. I thing the datacolum 'code' has value follwed by empty string like below
'ABFUFN076360A '
SO HOW DO I REMOVE EMPTY STRING AND GET ONLY 'ABFUFN076360A' instead of 'ABFUFN076360A ' in my STORED PROCEDURE.
Please help
Hello,
After the "DAYS" column, some fields are coming null. We don't want it to come null. Beside this, i.e; in the "MONDAY"column, the same "NAMESURNAME" is coming more then one. How can I do the grouping by "NAMESURNAME" ?
MachType DayPeriod Monday Tuesday Wednesday Thursday Friday Saturday
Negative- || NIGHT || NULL || CAN AK || NULL || FÄ°RDEVS ATEÅž || NULL || NULL
Negative- || NIGHT || NULL || NULL || CEM TEK || NULL || NULL || NULL
Negative- || NIGHT || NULL || NULL || NULL || FÄ°RDEVS ATEÅž || MÃœK HAK || NULL
Negative- || NIGHT || NULL || CAN AK || CEM TEK || FÄ°RDEVS ATEÅž || NULL || NULL
Negative- || NIGHT || NULL || NULL || NULL || NULL || ÖMER CA || NULL
select MachineType,DAYS,
CASE WHEN DIALYSISDAY= 'MONDAY'THEN NAMESURNAME END AS MONDAY,
CASE WHEN DIALYSISDAY= 'TUESDAY' THEN NAMESURNAME END AS TUESDAY,
CASE WHEN DIALYSISDAY= 'WEDNESDAY' THEN NAMESURNAME END AS WEDNESDAY,
CASE WHEN DIALYSISDAY= 'THURSDAY' THEN NAMESURNAME END AS THURSDAY,
CASE WHEN DIALYSISDAY= 'FRIDAY' THEN NAMESURNAME END AS FRIDAY,
CASE WHEN DIALYSISDAY= 'SATURDAY' THEN NAMESURNAME END AS SATURDAY
from
(SELECT SICK.MACHINETYPE,SICK.NAMESURNAME ,
CASE
WHEN SUBSTRING(DAYPERIOD, DATEPART(dw, SEANCE.SEANCEDATE), 1)= 'N' THEN 'NIGHT' ELSE '' END AS DAYS,
CASE
WHEN SICK.MACHINETYPE= 1 THEN 'Negative-'
WHEN SICK.MACHINETYPE= 2 THEN 'B(+)'
WHEN SICK.MACHINETYPE= 3 THEN 'C(+)B(+)'
WHEN SICK.MACHINETYPE= 4 THEN 'C(+)' END AS MachineType,
CASE
WHEN (DATEPART(dw, SEANCE.SEANCEDATE)) = 1 THEN 'MONDAY'
WHEN (DATEPART(dw, SEANCE.SEANCEDATE)) = 2 THEN 'TUESDAY'
WHEN (DATEPART(dw, SEANCE.SEANCEDATE)) = 3 THEN 'WEDNESDAY'
WHEN (DATEPART(dw, SEANCE.SEANCEDATE)) = 4 THEN 'THURSDAY'
WHEN (DATEPART(dw, SEANCE.SEANCEDATE)) = 5 THEN 'FRIDAY'
WHEN (DATEPART(dw, SEANCE.SEANCEDATE)) = 6 THEN 'SATURDAY' END AS DIALYSISDAY
FROM SEANCE,SICK WHERE SUBSTRING(DAYPERIOD, DATEPART(dw, SEANCE.SEANCEDATE), 1)= 'N' and
SUBSTRING(SICK.EUCLID,1,5)= '18016' AND SEANCEDATEBETWEEN '20080301' AND '20080307'
AND SICK.EUCLID=SEANCE.EUCLID )AS T ORDER BY MachineType
Hi all, I have some columns in my database which allows null. I want to know if leaving the field to be NULL or storing an empty string into the field, which will take up more space?? if the field type is varchar(100)
View 5 Replies View RelatedHow do I define a field to have the default value = ''. Not NULL but not a space either in SQL Server 2005?
View 10 Replies View RelatedI'm doing a bcp out of a table to a file. Some of the fields in arecord may have an empty string.When I bcp out to the file and examine it, the fields that have anempty string in the database now show up in the file as having oneblank character.Why is bcp doing this? I don't want the blank character in my output.Thanks,Eric
View 4 Replies View Relatedhi,
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
In sql server 2000 - our QA pointed out that his testing for empty strings returned 200 + rows but that when he clicked in the field there were obviously a space there. This issue came up because of the script I created to replace and earlier one that queried on empty strings instead of datalength and the earlier script always reported that it had updated x number of rows regardless of how many times it was run on the same database.
QA query based on the earlier script:
Select * from StringTable
WHERE (LongString = '' OR LongString IS NULL)
My script:
The fields are nvarchars in the newer database but older version of the database had varchars. I had created a script to replace empty strings as follows:
-- if LongString column is varchar - run varchar update else nvarchar update
If exists (Select * from sysobjects o
inner join syscolumns c on c.id = o.id
where c.name = 'LongString' and o.name = 'StringTable' and c.xtype = 167) begin
-- update varchar LongString
UPDATE StringTable
SET LongString = char(32)
-- Select * from StringTable
WHERE ((DATALENGTH(LongString ) < 1) OR LongString IS NULL)
END
Else Begin
-- update nvarchar LongString
UPDATE StringTable
SET LongString = char(32)
-- Select * from StringTable
WHERE ((DATALENGTH(LongString ) < 2) OR LongString IS NULL)
END
If exists (Select * from sysobjects o
inner join syscolumns c on c.id = o.id
where c.name = 'ShortString' and o.name = 'StringTable' and c.xtype = 167) begin
UPDATE StringTable
SET ShortString= char(32)
-- Select * from StringTable
WHERE ((DATALENGTH(ShortString) < 1) OR ShortString IS NULL)
END
Else Begin
-- update nvarchar ShortString
UPDATE StringTable
SET ShortString= char(32)
-- Select * from StringTable
WHERE ((DATALENGTH(ShortString) < 2) OR ShortString IS NULL)
END
My method for checking for datalength appears to work correctly why doesn't the QA script? I thought it might have to do with the nvarchar used in the table but I changed the column to a varchar and still has the same issue.
Thanks
I'm performing an insert, and I want to insert specified text, if the field is empty. I thought it should be a simple matter of a case expression, which I have below, but it's not working:
CASE PropertyBuilding when '' then [PropertyStreetAddress] else [PropertyBuilding]end
Hello, I attempt to export a CSV formatted file from SSRS and if the field is not containing data, a space is added to the field.
output:
4, ,1, , , ,
desired output:
4,,1,,,,
I know it is just a property setting. If someone can instruct me on the correct setting to adjust, I would greatly appreciate your help!
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
I have two grids in two different pages. In that one grid have more number of columns compared another grid. In this case report takes width of the first grid which has more no of columns. So empty sapce in right side of the report is there for second grid. Based on the grid size reprt width should be there. How to avoid empty space in this?
View 8 Replies View Related
Hi all,
hope anyone can help me on this one.
I have a parameterized report based on a MDX query (so no drag and drop of filters and fields).
Now i have to ad a parameter. So i open the query parameter dialog box and i ad my parameter.
when i click ok , my dataset becomes empty , resulting in an error on my report saying that the fields on the report cannot by found and should be in the scope of the dataset.
Has anyone experienced this problem before, or am i forgetting something here?
thanks in advance
Steven J
Hi,
I'm trying to add space to tempdb. I created a new device for 100megs and then I double clicked on tempdb db and then clicked on 'Expand'. I selected this new device in the data device - pull down and then did a 'Expand now'. After it successfully completed I went back and did a recalculate to find that the log space on tempdb has been increased and not the data space. I tried this twice with the same results.
Could anyone please tell me what's happening and how I can increase the data space on tempdb.
Thanks in advance,
Haranath
I've production sql server 7 sp3 on windows NT. I had a 8GB data file ofwhich 5GB were used and 3GB were unused. I wanted to take back the unused3GB.So I did the following with EM GUI:1. I tried to "truncate fre space from end of the file". Didn't truncatethe file. I believe there was no empty space at the end of the file.2. Next I chose the option to "shrink file to 5GB". And to my horror thedata file instead of taking just 5GB took the empty spaces also and the sizeof the used data file went to 8GB.Any idea what's going on?TIA,SP
View 2 Replies View RelatedI 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.
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] ....
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?
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 RelatedHello,
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.
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.
Is there any way or option to get the all columns of dataset added to table when we add a table in data region. It will take lot of time to add one by one and also there are chances to add one column ore than once.
View 7 Replies View RelatedI made some copy of table and I have this error but on my hard disk i have 4 gig of empty space.
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not allocate space for object 'Backup_Date_11_24_00_Time_9_08_34_AM' in database 'LogActiviteIntramedia' because the 'PRIMARY' filegroup is full.
/Intranet_API/Forms/videTableLog.asp, line 16
My question is how can I increase the space of primary filegroup?
Thanks and have a good friday
What is the syntax for adding a column where you are adding a year to a date in a date format? For example adding a column displaying a year after the participation date in date format?
View 1 Replies View RelatedSQL Server 2000 SP4. Hoping to find a logical explanation for a certainbehavior.Consider this script:USE pubsGOIF 1 = ALL (SELECT contract FROM dbo.authors WHERE state = 'CA')PRINT 'TRUE'ELSEPRINT 'FALSE'This, as expected, prints FALSE, since not all authors in CA are undercontract. Now, if the script is changed as follows:USE pubsGOIF 1 = ALL (SELECT contract FROM dbo.authors WHERE state = '')PRINT 'TRUE'ELSEPRINT 'FALSE'then the result is TRUE. In other words, the expression evaluates to TRUEwhen the select statement produces an empty set, which doesn't make senseto me. Even more interesting, the expressionNULL = ALL (SELECT contract FROM dbo.authors WHERE state = '')still evaluates to TRUE (ANSI_NULLS is ON).Can anyone explain these results? Is this the expected behavior in the SQLstandard, or something that is specific to SQL Server? Thanks.--remove a 9 to reply by email
View 5 Replies View Related@Names is a query string passed in, I need to count the number of records as a result of the below query/
Dim test As String
Dim sqlConnection3 As New SqlConnection("data Source=EQ-520-WEBSQLEXPRESS;Initial Catalog=CRDB.MDF;Integrated Security=True")
Dim cmd As New SqlCommand
Dim returnValue As Object
cmd.CommandText = "SELECT COUNT(ReqID) AS Expr1, LineManager FROM TblReqMain GROUP BY LineManager HAVING (LineManager = @Names)"
cmd.CommandType = Data.CommandType.Text
cmd.Connection = sqlConnection3
cmd.Parameters.Add("@Names", Data.SqlDbType.NVarChar)
sqlConnection3.Open()
cmd.Parameters("@Names").Value = test
If test = "" Then
Response.Write("An error occured")
Exit Sub
Else
returnValue = cmd.ExecuteScalar()
sqlConnection3.Close()
Label6.Text = "Number " & returnValue
I am fetching records from a table and putting in a datatable using the "sqlda.fill(datatable)"
when i see the datable in "data visualizer" i find no rows.
It is confused eventhough it does not throw any error
Hi, is there an equivalent to the mysql's " INSERT INTO table () VALUES ();"? The fields don't allow null values, and there is a primary key with auto increment. Any ideas?Thanks a lot.
View 7 Replies View RelatedHi i would like to know how to empty the transaction log programmaticly.
Someone got a clue? :)
Hi, I have a SQL Server 6.50 - 6.50.415 (Intel X86) and one database has 8Gb of data and 2Gb of log, the problem is that
the log space used is never below 44%, even if I back it up this way:
DUMP TRANSACTION DBName TO DBNAME_log_930 VOLUME = 'SS0003' WITH NOUNLOAD , STATS = 10, INIT , NOSKIP
This should clean the log, right?
I check for open transactions via: dbcc opentran (GMS), but there aren't.
The way I check for the log space used is: dbcc sqlperf(logspace)
The recover interval is 5.
Any Ideas?
Thank You
Gaspar Gonzalez
i have a sql sever2000 , and few disk.
when i am doing many opertaion , the log file is full. so how can i empty it?
sorry for my bad english