I am trying to eliminate the extra space in columns that have been returned from a DB query...the tables have character lengths of 40, I would like to return just the necessary characters.
Thanks in advance.
Mark.
Hi there,
May be a kind of dumb question, but I have a multi-value parameter that I would like to give users the ability to select from in a SSRS 2005 Report and I was wondering if there was a way to adjust the width of the drop down so the user wouldn't have to scroll horizontally to see a full parameter item. It looks like it cuts off after about 30 characters.
Any help here would be appreciated.
Thanks!
Rich
Hi All,
We are having a table report which has set of columns where the visibility is made false based on report parameters. It works fine and the table automatically shrinks. But, the page width remains as early and has lot of blank space in it. It also causes printer to print many blank pages.
Is there a way to control pagewidth dynamically? Thought of adding a custom assembly to control page width - but how to access the page that is getting displayed. "this" will refer to the the assembly class instead of report as such.
Any help is highly appreciated.
Regards, kart
Is there a maximum width for fixed width column?
I'm trying to read in a flat file (which, admittedly, has one very wide column), and it keeps breaking because of truncation when it tries to read in the file.
Any ideas?
Jim Work
Hi,
I am trying to create a program that transfers tables to flat files.
At this point in time, I have suceeded in created one that creates delimited files.
However, I am now trying to create fixed-width files as you can do with the SSIS designer, but programatically.
Is there a way to programatically determine the width of a column from the source table? I can not seem to find any kind of function or member that stores this information or allows me to retrieve it.
I know what I need to change in order to set a width for a column, but I just don't know how to find the width without just asking the user to provide one.
Hi Every1, I'm getting this error.
'Cannot specify a column width on data type smalldatetime.'
I'm using SQL Server & I've check the data type in the db & it's same as smalldatetime.
Any suggestions would be highly appreciated.
Thanks
<Error>
Column or parameter #8: Cannot specify a column width on data type smalldatetime.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Column or parameter #8: Cannot specify a column width on data type smalldatetime.
Source Error:
Line 344: Try
Line 345: myConnection.Open()
Line 346: dgdSearch.DataSource = myCommand.ExecuteReader()
Line 347: dgdSearch.DataBind()
Line 348: Finally
Source File: c:inetpubwwwrootDemo Equip LogSearch Equipment.aspx.vb Line: 346
Stack Trace:
[SqlException: Column or parameter #8: Cannot specify a column width on data type smalldatetime.]
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +742
System.Data.SqlClient.SqlCommand.ExecuteReader() +41
Demo_Equip_Log.Search_Equipment.BindGrid() in c:inetpubwwwrootDemo Equip LogSearch Equipment.aspx.vb:346
Demo_Equip_Log.Search_Equipment.Page_Load(Object sender, EventArgs e) in c:inetpubwwwrootDemo Equip LogSearch Equipment.aspx.vb:195
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +750
Hi
When i create a temp table with a field as blank string as in following example and then try to insert a blank string int it following error is coing.
compatibility level of database is 70
following is the example
Select CtpId = convert(int, a.CtpId), a.OrgId, a.Sname, a.Intls, a.Prfx, a.CallName, a.Sex, a.RspblEmp,
City = convert(char(40), ''), OrgDescr = convert(char(46), ''),
a.ActvtyStatus, a.Sales, a.SaleCntct, a.ContractCntct, a.ContractSigner,
a.CRW, a.WkndCntct, a.UseOrgAddr, CtpTimeStamp = convert(int, a.TimeStamp), FrequentFlyer = convert(bit,0) ,
a.PrevCmpl, b.St, a.DtIns
into #Ctp
from tbCtp a, tbAddr b
where 0 = 1
Select a.CtpId, a.OrgId, a.Sname, a.Intls, a.Prfx, a.CallName, a.Sex, a.RspblEmp,
City = '', OrgDescr = '',
a.ActvtyStatus, a.Sales, a.SaleCntct, a.ContractCntct, a.ContractSigner,
a.CRW, a.WkndCntct, a.UseOrgAddr, CtpTimeStamp = convert(int, a.TimeStamp),FrequentFlyer = 0, a.PrevCmpl,
St = "", a.DtIns
into #CTP
from tbctp a
where a.Sname like 'as%'
and CallName = '%'
ERROR MESSAGE:: Server: Msg 2731, Level 16, State 1, Line 1
"The width of column 'City' is 0. This width is not valid."
Please tell why this error is coming.
Thanks in advance
Rajesh
Is there a way to change the width of the bars in a bar chart?
I know that there is an option to 'Plot the data as line', but for some reason this is greyed out.
Any ideas on both?
I have been looking over the forums looking for answers to the question that nails us all: how do you dynamically resize column widths? MS says: you don't. However, I have heard many people say they have found ways around it. I wonder if anyone might share those with me (and everyone else). Share the wisdom you have gained with those less experienced!
View 1 Replies View RelatedI have like 100 columns and most of them work fine, but some will not work. I need to select the columns with correct width for fixed width flat file.
Here is typical SQL statment that works for most of them.
left(RTRIM(A.City) + Replicate(' ', 25) ,25) as [City]
The above one is not working, but many other works fine like the address. Why would it work for some, but not others?
It works fine if I use any char, but not space. i.e.
left(RTRIM(A.City) + Replicate('*', 25) ,25) as [City]
I know we could change the data type or width of a table column in SQl 7 database, but I am not certain if this column is a primary key. Can we change a primary key column width? If we can, how about this column as a foreign key in another table? Will it be automatically updated to the same width? Thanks in advance for your input.
View 1 Replies View RelatedI use bcp command to output to excel, it works. But I want to format the excel, some column width are too small,user need adjust the column width, otherwise it shows ######.
How can I set columns width when I use bcp output to excel.
Also, can bcp command output to multiple excel sheets and add report title in each excel sheet?
Hello,
We are generating RDL file on the fly using a XSLT. We are trying to implement an auto width to all the columns dislpayed in the Report. Is Automatic horizontal width for column supported in RDL? If so how? If not is there is any workaround for it?
We tried using CanGrow and CanShrink tags, but those are for only vertical.
Thanks in advance.
I have a subreport on my main report, and the subreport contains a matrix. When I run the report, the subreport seems to expand beyond the width of the main report. The matrix itself does not expand beyond the width of the body of the main report, but for some reason it seems that the subreport does. The subreport does not contain any headers that might be messing up the width. If I cut and paste the matrix from the subreport directly onto the main report, and remove the subreport, it prints fine. But as soon as I include the subreport on the main report it prints with blank pages because the subreport expands beyond the width of the body of the main report. I have checked the width and margins on the subreport and compared to the width of the main report and all looks good. Can anyone help?
Thanks.
Hi All,
Is there a way by which we can modify the width of a column of a table which is being replicated without touching the ongoing transactional replication? This is for MSSQL2000 Transactional Replication.
I know (and successfully tried) that we can add a column to a table and that gets propaged to the replicate database and indeed the added column gets reflected there. How to add a column? sp_repaddcolumn or Right Click on the Publication-Properties and it shows a button to Add a Column.
This is what I have tried for modifying the width of a column of a table participating in Transactional Replication from varchar(10) to varchar(100)
MH (source) -> MH1 (Replicate)
The column “col1” had width of varchar(10) and this was altered to varchar(100).
insert into MH..test_mh values(4,'abcdeabcdefff')
select * from MH1..test_mh
exec sp_dropsubscription @publication = N'MH', @article = N'test_mh', @subscriber = N'UKPBDRMTST2', @destination_db = N'MH1'
go
exec sp_droparticle @publication = N'MH', @article = N'test_mh'
go
alter table test_mh alter column col2 varchar(100) null OR
MH1..sp_help test_mh
exec sp_addarticle @publication = N'MH', @article = N'test_mh', @source_table = N'test_mh'
go
exec sp_addsubscription @publication = N'MH', @article = N'test_mh', @subscriber = N'UKPBDRMTST2' , @destination_db = N'MH1'
go
Needless to say, help would be apreciated -:)
~Mihir
However, adding a column was possible.
I have a report that has a certain number of columns that are hidden and when hidden they leave a lot of white space. Is there a way to detect when they are hidden and set the width property to 0 and then reverse the value when they are visible?
View 1 Replies View Related
Dear all hi,
I would like to create a function that will return to me the width of the column of the table without giving as a parameter the table name. I need it, as I want to exceed my data in this column to the column length. I want to fill the column data with trailing blanks until I reach the column width. The function len returns the length of the data in the column.
I know that If I have the table name I can find the column width through the system tables. I don€™t want this.
Sincerely,
Hellen
Hi,
I am facing issue with the auto fit width. When i am creating a report which includes table. The table column length should get adjusted to the text size displayed in it instead of displaying the text in 2 lines. But i dont find any way to set that option. Could anyone let me know how to set the column length as per the text displayed in the column in table.
Thanks
Will the width property of a column be able to be set at run-time (i.e. ability to set the width of a column to an expression) in SQL Reporting Services 2008? One problem I have seen is the inability to set the width of columns in a report when you you have several columns whose order is determined based on parameters.
Â
Lets say I have 2 columns in a report and two fields from the database which will populate those columns: 1 field that is varchar(5) and one which is varchar(50). If I do not know which column each field will go to until runtime how am I supposed to adjust the column width? Letting the column width be set by an expression would solve everything as I could pass the width in to the report. As it stands now, I would have to set each column's width to accomodate a field of type varchar(50) in order to avoid wrapping of data.
Â
You could say just create 2 reports, one for each case, but what if I had 15 fields? 20?how to dynamically set the width of a column? Will the width of a column be able to be set at run-time in SQL Server Reporting Services 2008 via expression or some other means?
FOA, I am using the webservices of the RS2005.
I created a report with same parameters. Two of them a based on a query. The first query contains a simple select * from ... the result will be stored in the parameter @param1. The second parameter contains select * from ... where ID = @param1. So far...
If a pass the reportparameters of the report i will get a valuelist for the first query, but nothing for the second one.
How can I manage that the second parameters also gets a valuelist?
regs
Hi,
I've a report projects where each report has a number of columns and the spec is to have all the columns print-out on the same page. Is there a setting that will auto-scale these columns to fit to the page or will I have to edit the font size and widths manually on each report to fit to the page?
Thanks for any comments.
I keep getting requests to increase the width of a varchar colum every now and then.
I want to ask if its perfectly ok when you have active users connecting to the application to do this?
Hi All,
Is there a way by which we can modify the width of a column of a table which is being replicated without touching the ongoing transactional replication? This is for MSSQL2000 Transactional Replication.
I know (and successfully tried) that we can add a column to a table and that gets propaged to the replicate database and indeed the added column gets reflected there. How to add a column? sp_repaddcolumn or Right Click on the Publication-Properties and it shows a button to Add a Column.
This is what I have tried for modifying the width of a column of a table participating in Transactional Replication from varchar(10) to varchar(100)
MH (source) -> MH1 (Replicate)
The column €ścol1€? had width of varchar(10) and this was altered to varchar(100).
insert into MH..test_mh values(4,'abcdeabcdefff')
select * from MH1..test_mh
exec sp_dropsubscription @publication = N'MH', @article = N'test_mh', @subscriber = N'UKPBDRMTST2', @destination_db = N'MH1'
go
exec sp_droparticle @publication = N'MH', @article = N'test_mh'
go
alter table test_mh alter column col2 varchar(100) null OR
MH1..sp_help test_mh
exec sp_addarticle @publication = N'MH', @article = N'test_mh', @source_table = N'test_mh'
go
exec sp_addsubscription @publication = N'MH', @article = N'test_mh', @subscriber = N'UKPBDRMTST2' , @destination_db = N'MH1'
go
Needless to say, help would be apreciated -
~Mihir
This one has set me back many, many hours on this project; it's about got me ready to dump SSIS & just roll a custom .NET solution in C#.
I need to create import packages for quite a few very wide flat files (130 - 180+ columns, not my design). Many of these columns have data > 50 characters long.
I change column widths on the data flow source using the Advanced Editor, via Input and Output Properties ==> Flat File Source Output ==> External Columns.
About 50% of the time, the changes vanish after clicking OK to dismiss the Advanced Editor. There is no warning message or output announcing that the editor failed to persist its changes, or that it set some columns' properties back to the defaults, or why. The column's widths just silently revert back to 50.
If the cause and resolution aren't known, does anyone know of a way to accomplish any of the following workarounds?
Create a data source connection by importing an external text schema defining the flat file's column names, data types and sizes, or
Change a property on multiple source columns en-masse, or...
Get at a text version of the file containing the Data Flow Component's definition, so we can edit wide import schemas without racking up thousands of mouse-clicks?Thanks! A virtual cheeseburger to anyone with answers.
How do i import a Varying Column Width Flat file into a Table using SSIS?
I have a flat file that has 4 columns with varying width
Like I should read the file as
Col 1 - (1 to 10 Characters)
Col 2 - (12 to 21 Characters)
Col 3 - (22 to 35 Characters)
Col 4 - (36 to 38 Characters)
At the end of the record is a "LF"
I think "Fixed Width" Columns allow me to define a standard column length for all the columns.. Right?
Any thoughts on how to?
I need to create the following table in reporting services
PRODUCT April March Feb
2008 2007 2008 2007 2008 2007
chair 8 9 7 4 4 4
table 3 4 5 6 4 6
My problem is the month names are a column in the dataset, but I dont know how to get it to fill as column headers???
Thanks in advance!!!
Can you point me in the correct direction in SQL2005 Server to adjust the SQL Server database collation case-insensitive, accent-sensitive, Kana-sensitive, and width-sensitive. We are trying to configure it to run with SharePoint Services.
Thanks in advance for any assistance that you might be able to provide.
-Austin
I have 2 higher level column groupings of month name and year above my actual date groups. It looks a little weird aligning them left but there is no guarantee that centering them will even allow them to show until I've scrolled right to the middle of the cell width that they occupy.
Is there a feature that comes with, or a well known trick for making them center in the area that is being viewed instead of the potentially very wide cell that they occupy?
Hi,
I have this simple SQL query which SELECTs the fields according to the criteria in WHERE clause. SELECT callingPartyNumber, originalCalledPartyNumber, finalCalledPartyNumber,
dateadd(ss, (dateTimeConnect + (60 * 60 * -5))+3600 , '01-01-1970 00:00:00') AS dateTimeConnect,
dateadd(ss, (dateTimeDisconnect + (60 * 60 * -5))+3600, '01-01-1970 00:00:00') AS dateTimeDisconnect,
CONVERT(char(8), DATEADD(second, duration, '0:00:00'), 108) AS duration
FROM Calls
WHERE
(callingPartyNumber = ISNULL(@callingPartyNumber, callingPartyNumber)) AND
(originalCalledPartyNumber = ISNULL(@originalCalledPartyNumber, originalCalledPartyNumber)) AND
(finalCalledPartyNumber = ISNULL(@finalCalledPartyNumber, finalCalledPartyNumber)) AND
(duration >= @theDuration) AND
((datetimeConnect - 14400) >= ISNULL(convert(bigint,
datediff(ss, '01-01-1970 00:00:00', @dateTimeConnect)), datetimeConnect)) AND
((dateTimeDisconnect - 14400) <= ISNULL(convert(bigint,
datediff(ss, '01-01-1970 00:00:00', @dateTimeDisconnect)), dateTimeDisconnect))
If you notice, in the SELECT, I add 3600 (1 hour) to adjust the time according to the current DayLight saving setting. Similarly, while comparing in WHERE clause, I subtract 14400 seconds (4 hours) to adjust the comparison according to EST (-5 GMT, but doing -4 GMT since daylight saving) and daylight saving.
Also, dateTimeConnect and dataTimeDisconnect fields save time in UTC seconds.
Now this application may be used by clients in different timezones. The application may be hosted on their server which would mean a different time zone. What would be the best way to make this time zone and daylight adjustment more dynamic instead of hardcoding the values.
We have a database that doesn't seem to be growing on its own correctly (or seems like it isn't). This database has been running really slow for the last week and we think this may be whu
Current size is listed at 963mb while allocated size is 959mb. Trans log size is set to auto-grow and is allocated 4mb
Can we change that number on the fly, and also change the the growth settings without affecting the system?
Being a live system we obviously do not want any downtime, but believe this will help with our slowdown.
Any other ideas would also be appreciated
Thanks!
Hi,
I need information on adjusting the priority of the replication thread(s) within SQL Server 2000. All I've been able to find is how-to's on adjusting the priority of the SQL Server process itself, as well as information on how to adjust the relative priority of changes made to the database by subscribers.
Here's the situation, from time to time our replication system goes down, it starts itself up after waiting 5 minutes but this causes a problem. When its starting up it seems to be taking up far too many CPU cycles. As such the server is unable to process SQL queries, which forces its subscribers to time out. This is a bad thing as the subscribers are control systems on an assembly line. What I want to do is lower the priority of replication so that the server can still process queries while the replication agent is restarting itself. Is this possible? If so how can it be done?
Thanks,
Geoff Dupuis
Hi, everyone
There is a table t1 with two fields, such as,
ID NAME
１ Ｔｏｋｙｏ
2 Xian
3 America
For there are full-width and half-width strings in the values of the two fields, I can not select and get the right records. So I want to transform the two fields ID and NAME, I fail to find the function in the SQL Server 2005. Please give me some advice.
Thank you very much!
I have had to add a new column to a mobile 5 database in VS2005. I obviously have added it in the wrong way. I have two applications that use the same db. The .net application accesses the table with the new column fine., this is where I added it first. When I try to add the column to the Compact framework (Pocket Pc) app I get this error when I try to run the application. In both applications the Server explorer shows the new column.
The column name is not valid. [ Node name (if any) = ,Column name = GUID ]
As long as I remove the GUID column from the tableadapter.fill sql the program runs the same as before.( even though the sql executes in the query designer with the GUID column in the sql statement.)
How do I add the column so that the Dataset.designer.cs gets updated and can load the new column?
Thanks I hope this make sense.
Jon Stroh