Hi,
I have a matrix object in a report that sometimes runs off the side of the page based on the underlying data. Essentially if there are more than 11 columns it stretches out my page.
How can I fix this? Ideally, I would like to show only the Top 11 results but cannot seem to figure out how (or where, or on what data element) to properly set a filter.
Thanks.
Brad
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?
I 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 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.
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'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.
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, 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 got the following code to add a column in a matrix with a variance:
IIF(IsNothing(Previous(Sum(Fields!Amount.Value))) or Fields!year.Value=First(Fields!year.Value,"Category") or Previous(Sum(Fields!Amount.Value))=0,nothing,
(
(Fields!Amount.Value)
/Previous(sum(Fields!Amount.Value))
)
)
This code works fine, except that the first row of the matrix shows an #error
This happens with each matrix where I use this expression. A warning emerges:
rsruntimeerrorinexpression the value expression for the textrun Textbox43.Paragraphs[0].TextRuns[0]' contains an error.
Attempted to divide by zero.
The strange thing is that the part
Fields!year.Value=First(Fields!year.Value,"Category")
should prevent an error and I expect it to show 'nothing'
An screenshot of the table. (each color is a different category. Each row stands for 2013, 2014, 2015)
As you can see, all other 2013 rows show a blank cell, except the first row.
I have a Matrix table that expands to the right when choosing an amount of months to be shown. Under this matrix I have to Charts. The two charts are situated together, that is no space between them, and to the left of the report.
Now, if I choose a lot of months, say three years the matrix diagram will be huge to the right. The problem I have is that the second diagram, the one on the right, moves to the right depending on how big the report gets, and this is not good at all. The two charts are supposed to be all the way to the left.
How? Why does the right chart move?
Thanks in advanced
Kind Regards
I'm trying to decide whether MS SQL will allow me to accomplish the following objectives at no cost, or whether I'd eventually have to pay for an MS SQL upgrade to accompish my objectives.
I have big, unrealistic dreams. I want to create a humorous newsblog into which I would post more than a dozen times a day. Most of the posts would have large photographs. Presumably, I'd archive the posts by subject and ranking and 'most viewed," etc., using a database.
1) Will the space limitations of the MS SQL Express edition be an issue after a while?
2) Could I hire a web developer to help me from a remote location, once the website is large enough to warrant expansion? Or does the MS SQL Express edition allow only one user? I read something I didn't understand about CPU restrictions.
3) I'm confused because web hosts advertise the availability of MS SQL databases on their server...so does that mean I wouldn't have to buy an upgrade if it became neccessary? (I know, I'm shockingly uneducated.)
4) I'm going to buy Office 2007. Is it important to purchase a package that includes Microsoft Access, given my goals?
5) Any other thoughts in plain english on how the MS SQL express edition imposes limitations....basically, I don't understand how MS SQL Express might limit me down the road if the site were actually a success. What would have to happen before I would be forced to spend a lot of money on an upgrade later?
I'm almost completely new to computing. I've read a bunch of criticisms of MS SQL Express on internet forums that I didn't understand, but that really made me worried about my decision to go with Microsoft Products and Asp.net web hosts. (I understand some people have an irrational dislike of Microsoft, but there was A LOT of bashing.)
Hi,
please do help. Have been wrestling with this for about 3 hours and read a buncha forums + searched the tutorial lists.. AARRGH!
Anyhow,
I have to paginate a datalist (and I really can't use a datagrid because of the layout, blame the bluddy graphic designer)
I want to return the top 8 rows, the next 8 top rows and so on.
This is the sql string I have:
'retrieve pagination in order to construct the rest of the sql string
Dim startrec As Integer
If pageno = 1 Then
startrec = 0
Else
startrec = (pageno - 1) * pagesize
End If
' this builds the sql string, getting ONLY the records we need for this. Page size is a constant expressed on the base page
' startrec is the record where I want to start in the db.
strsql = "select top " & pagesize & " * " & strsqlbasic & " and itemID>" & startrec & " order by itemnotes1 asc"
noresults.text = strsql & " <br> " & searchwhat & searchfor
strsqlbasic is constructed elsewhere and is just the 'from X where y = var
Of course, this returns all records where the value of itemID is greater than the value of startrec. How would I change this so it returns the next 4 rows starting from the row number given by the value of startrec?
thanks for any help.
I was wondering if there is a way to select ranges of rows, for instance:
I want rows 5-10 of a database, how would i select that?
I know in MySQL you do a "limit #,#", no clue about SQL Server.
Orcale uses a psuedo colum ROWNUM to limit the number of rows returned by a query. Does MSSQL server 6.5 provide some similar feature (or any indirect way of doing that).
View 1 Replies View RelatedHi
I have a problem on microsoft sql server 2000.
I need to limit the amount of resources(eg cpu, mem..) a query uses
Is there a way to do this in SQL server 2000
Thks in advance
:eek:
I'm trying to write a query where if the value of one of the paramters is equal to "-1" it will not include one of the search criteria. For example I have a param called "Gender". 0 = Male and 1 = Female. If the value of the "Gender" param is "-1" I'd like it to find either Males or Females.
Code (please not this doesn't compile):
SELECT
[UserID],[Gender]
FROM
[table_user]
WHERE
Country = @Country
AND IsOnline = @IsOnline
IF (@Gender != -1)
AND Gender = @Gender
Obviously this doesn't work, but I think it conveys the gist of what I'm trying to do. Does the secret lie in default parameters? Or is my IF syntax just flat out wrong?
Thanks!
I am trying to convert some mySql code to sqlserver and was amazed to see that sqlserver has no functionality to specify the rows of a select statement. In mySql its e.g. "SELECT * FROM table LIMIT 20, 10" this returns ten rows starting from row 21. Using TOP in sqlserver only returns the top n rows but what if I want row 20 to 30? Is there an easy way that I have just missed or does one realy have to do something like "SELECT TOP 60 * FROM myTable WHERE id NOT IN (SELECT TOP 40 id FROM myTable);"?
Hope you can reply with good news...
Ed
Hello everyone.. I'm working here on a little problem which's driving me nuts : I have two tables :EQUIPMENT :
-EQID
-EQName
-EQblabla QUALITYCHECK :
-QCID
-EQID <---- Is connected with EQUIPMENT
-QCDATE
-QCACTION
-QCnextdate They're needed to manage Qualityactions on some machines we own. Every year, we do quality actions on every machine, after then, we Inserta new QUALITYCHECK-entry into our database and automatically theQCnextdate is generated with QCdate+365days. So far, so easy. So now, how will i do a query against the DB with the NEWEST QUALITYCHECK-entrys for every machine ? The result has to besmth. like that : EQID | EQName | QCDATE | QCNextdate
1 | machine 1 | 21.12.2006 | 21.12.2007
2 | machine 2 | 21.06.2006 | 21.06.2007
3 | machine 3 | 21.11.2006 | 21.11.2007
4 | machine 4 | 21.12.2005 | 21.12.2006
5 | machine 5 | 23.10.2006 | 23.10.2007
AND NOT
EQID | EQName | QCDATE | QCNextdate
1 | machine 1 | 21.12.2006 | 21.12.2007
1 | machine 1 | 21.12.2005 | 21.12.2006
1 | machine 1 | 21.12.2004 | 21.12.2005
1 | machine 1 | 21.12.2003 | 21.12.2004
2 | machine 2 | 21.06.2006 | 21.06.2007
2 | machine 2 | 21.06.2005 | 21.06.2006
2 | machine 2 | 21.06.2004 | 21.06.2005
2 | machine 2 | 21.06.2003 | 21.06.2004etc..
I tried several things with top 1, subquerys, distinct etc..But unfortunatly it seems that im not capable to solve the problem..I would be thankful for any suggestions !
Hello.
I am trying to limit the number of records to start in a position and show me "n" records. Here is an example query:
SELECT TOP 60 * FROM Entries WHERE Id NOT IN ( SELECT TOP 45 Id FROM Entries ORDER BY Id DESC ) AND Static = False AND Authorized = False ORDER BY Id DESC
It's not working correctlly. Any ideas?
Thanks in advance.
Hello,
We are migrating from VB6/MySQL to VB6/MS SQL.
The application retrieves limited records for paginated results.
This way we can access large databases, but only return rows for one screen.
For example:
Select row 1 - 10, then 11 - 20 then 21 to 30 etc.
With MySQL we can use LIMIT and OFFSET and it works great.
MySQL
SELECT <columns> FROM <tables> [WHERE <condition>] [ORDER BY <columns>]
[LIMIT [offset,]howmany>]
Does anybody know how we can do something similar with MS SQL?
Any feedback is appreciated!
Thank you,
Edi
I know how to do this in MySQL; but I was hoping there was a way to do it in MS SQL.
I want to be able to limit the number of rows an UPDATE or DELETE will effect regardless of the WHERE clause. I want to do this as a stop gap in the event that there is a logical error in the WHERE clause that would make it effect more rows than is humanly intended.
If I write a complex query that I know should drill down to only affecting one row, I just want to lock it in before I run it on database and take the risk of damaging some data.
In MySQL you would just do something like this:
Code:
DELETE FROM table_name WHERE ... LIMIT 1;
The only thing close to LIMIT I could come up with for MS SQL was TOP but it seems to only work for SELECT.
Any ideas would be greatly appreciated.
I ask this because if you screw up; manually saving 1, 2 or even 5 rows is a lot easier than having to rescue a whole table of data (even if it on a development server).
I have a table that looks like
CP | P | ST | Date
where each letter represents a column. I need a SELECT statement that results one row if there is an entry with a date within the last 6 mo for each CP,P,ST combination.
example:
cp1, p1, st1, 1/1/6
cp1, p1, st1, 4/1/6
cp1, p2, st1, 3/1/6
would return just the first and third row...
thanks.
any ideas?
Let me start off with my table layouts.
CREATE TABLE [dbo].[Competition] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[Name] NVARCHAR (150) NOT NULL,
[Remarks] NVARCHAR (350) NULL,
[Start] DATETIME NOT NULL,
[IsActive] BIT NULL,
[End] DATETIME NOT NULL,
[Code] ....
Then here is my query with the problem below. The problem is that multiple EntryImages can relate to a single Entry. My goal is to only select one Row for each Entry (using @EntrySelection) but when I join to EntryImages I always get back multiple rows when an Entry has multiple EntryImages.
Declare@EntrySelection int= 10
select top (@EntrySelection)
[dbo].Entries.Id,
[dbo].Entries.CatchDate,
[dbo].Entries.CompetitionId,
[dbo].Competition.Name,
[Code] ....
We currently have a routine that "forks" out (to use the unix term)TSQLcommands to run asynchronously via SQL Agent jobs. Each TSQL commandgets its own Job, and the job starts immediately after creation.Sometimes we can have too many of these jobs running at the same, andthe box crawls to a slow speed until the jobs finish up.Is there a way we can limit the number of active jobs running under theSQL Agent at one time? Or is there away to limit the number of active(runnable) processes on SQL Server, in general?
View 2 Replies View RelatedHello,i am in a problem that i am having a table with 100 rows while i ampresenting in report i want to limit rows to be 10 in a page in thereport like that i want to get 10 pages .Please say me the procedurefor this.Thanks,Baba.
View 1 Replies View RelatedSQL Server 2000 SP4. I built a large DTS package that grabs a numberof tables from an Oracle DB, does some scrubbing and date verificationand loads to a SQL Server DB. Most of the tables are full refresh anda few are incremental.Main DW: DwSQLStaging Area: DwLoadAreaSQLThe DW is about 60 Gigs. The Staging Area is about 80 Gigs. This isall good.However, the log file for the staging area is 50 Gigs and I'm tryingto find ways to not require such a large log file. I tried adding afew "BACKUP LOG DwLoadAreaSQL WITH TRUNCATE_ONLY" statements in theDTS package but figured out that because it's 1 DTS package it's all 1transaction. I've thought about breaking it up into multiple DTSpackages and truncating the log between running them but was hoping toavoid this. To be clear, I know how to shrink DB's and LogFiles...that's not the issue.Any Ideas? Thanks.
View 2 Replies View RelatedThanks everyone
I have another question. If I use FOr Each Loop Container (For each file Enumerator), it will select all the files in that folder. What if I want to select just 100 files (assuming 500 files in the folder)
How do i do this??
Thanks
Is there any way to limit the content that is placed into the Progress Tab during execution of a package in debug mode?
My problem is that I have a complex package that puts lots of info in there. This package is called in a loop from another package. Each time the package is called, the new info is added to the existing info in the Progress Tab. Eventually the instance of Dev Studio hangs when it gets too much content in that screen.
The only solution would be if I can limit the output content of that screen or turn it off.
All suggestions appreciated.
hi,
im doing network monitoring app where basically i run a checks on servers every few minutes and log the data to a table. Naturally the table can get big, quite quickly. What I want is to be able to overwrite the table data at the start of each new day. Alternatively, rollup the data into a daily or weekly packets and then overwrite table data. How do i do this?
Hello all, I have a table called jobs that has a total column which contains integers between 0 and 25. I have a deal worked out with a client that caps the total per job at 9. How do I sum every entry to get the months total while capping any day whose total is over 9. I have tried looping it in php which I can't get to work, seems like it should be part of the query structure anyway.
Just to clarify: If I had 4 days last month whose totals were 4, 6, 10 and 17. I would need 10 and 17 to cap out at 9 and the total = 28 not 37.
Thanks for your time.
Tim
Ideally, I'd like to move away from using SQL-based logins for our internal applications and take advantage of integrated security instead.
Defining AD groups and their permissions in SQL is simple and getting the application to work with that is not an issue.
Where I'm having difficulty, though, is in isolating the accessibility in integrated security. Because the SQL-based login was isolated from the windows user, they could only get access to the sql server via our app -- their normal windows accounts had no access.
If we switch to use only windows authentication, the user would be able connect fine from our application and have rights to various tables. The issue is that they could also connect via Enterprise Manager, Excel, or any other tool. Is there any way to limit the exposure so that we can take use of AD for our access but further limit to allow connections based upon the application? I realize that this could be impersonated, but it's still better than nothing...
--Kevin Fairchild
Hi,
I have a small requirement in SSIS Error Logging Mechanism.
Presently in my SSIS package i am using a File Connection Manager for creating a Log file.
I have a problem on this regard. Every time when i am executing my DTS package, the error log messages are getting appended to my error log at OS level (say D:error_messg.log). And for this reason whenever my DTS package is getting executed the size of the file is keep on increasing and there by killing my disk space.
I have a requirement for this error logging mechanism. At any time my log file should not exceed more than 20MB.
Or can we remove the log events a week ago or say more than 2 days or say. Just ensuring the log file do not fill up the disk space eventually.
How can we do this?
Any suggestions are greatly appreciated.
Thanks & Regards
Hello,I am running SQL Server 2000. I would like to know whetherMicrosoft Transact-SQL has a method for limiting the resultset from a query in a way analogous to MySQL's LIMIT keyword,so that, for instance, if the result set contains 10,000 rows,then only the first 10 rows from the record set are output.Thank you,Best Regards,Neil
View 2 Replies View Related