Layout incorrect when using ReportViewer

Please can someone help me with this.

I have created a very simple SQL report which has a title, Image, 3 subtitles and some data fields.

When I view it is my VS designer and preview it looks perfect, however when I deploy and view the the report through my ReportViewer control all the labels get moved around and the entire thing is a mess.

If I then export it to PDF it looks perfect again... I am sure I am missing something obvious but I can't find it.

I have done absolutely no expression formatting or coding at all. It's a plain SQL query and plain result textboxes.

Could be that something is overlapping in your report. Make sure that none of the components or fields are overlapping even slightly with each other. Overlapping items usually do not get rendered correctly when viewed on HTML.

-Aayush

|||

Aayush Goyal wrote:

Could be that something is overlapping in your report. Make sure that none of the components or fields are overlapping even slightly with each other. Overlapping items usually do not get rendered correctly when viewed on HTML.

-Aayush

It actually turned out to be a combination of two things, I had some overlapping but it still failed after I fixed that. I then checked it in IE and it worked this time.

Now I need to find out why it is not working in Firefox?

Latest quarterly default

In Query Analyzer, I would like to design a view that would that would return
data automatically for the most recent completed quarter based on a calendar
year.
For example(from the SOP30200 table in Great Plains Dynamics 7.0):
sopnumbe soptype docdate subtotal
-- -- -- --
I would like all data from July 1 through September 30, 2006 since today is
December 27 and this current calendar quarter is still in progress.
Thank you.
If you are still interested this should work (but it's a bit convoluted).
You can replace the @.Today WITH GETDATE() is you want to use the machine date.
DECLARE @.Today DATETIME
SELECT @.Today = '12/27/2006'
SELECT
@.Today AS TodaysDate,
DATEPART(year, @.Today) - CASE WHEN DATEPART(quarter, @.Today) = 1 THEN 1
ELSE 0 END AS [Year],
CASE WHEN DATEPART(quarter, @.Today) = 1 THEN 4 ELSE DATEPART(quarter,
@.Today) - 1 END AS [Quarter],
CAST(
CASE
WHEN DATEPART(quarter, @.Today) = 1 THEN '12/31/'
WHEN DATEPART(quarter, @.Today) = 2 THEN '03/31/'
WHEN DATEPART(quarter, @.Today) = 3 THEN '06/30/'
ELSE '09/30/'
END +
CAST(DATEPART(year, @.Today) - CASE WHEN DATEPART(quarter, @.Today) = 1 THEN
1 ELSE 0 END AS VARCHAR)
AS DATETIME) AS LastQuarterEnd
Results:
2006-12-27 00:00:00.000200632006-09-30 00:00:00.000
Regards,
JayAchTee
"chas2006" wrote:

> In Query Analyzer, I would like to design a view that would that would return
> data automatically for the most recent completed quarter based on a calendar
> year.
> For example(from the SOP30200 table in Great Plains Dynamics 7.0):
> sopnumbe soptype docdate subtotal
> -- -- -- --
> I would like all data from July 1 through September 30, 2006 since today is
> December 27 and this current calendar quarter is still in progress.
> Thank you.

Last time publication/subscription replicated data

Is there somewhere that I can tell when the last time either the publication or better yet a subscrition replicated data (in a system table or view maybe)?

I want to set up monitoring to make sure I am aware if something for some reason does not replicate

OH and it is on SQL 2005 enterprise... an

If synchronization is processed by SQL Server Agent Job, the best choice is to view job state.

Here is example how to do it, if there is Merge publication with Merge Push Subscriptions:

1. Call sp_MSEnum_Merge_subscriptions in Distribution database to determine job ID's of the Push subscriptions:

Get PushSubscriptions Dataset

exec distribution..sp_MSEnum_Merge_subscriptions 'publisher_server','pub_database','publication_name'

2. The returning dataset contains a column named [job_id], that contains Agent Job ID in Binary format. You must convert it to hexadecimal string format (for example, 0x8392E005C4941A4C9A279A68EFC9F3D8)

3. Execute sp_help_job in MSDB database to retrieve current job state:

Get Job State

exec msdb..sp_help_job @.job_id = 0x8392E005C4941A4C9A279A68EFC9F3D8 , @.job_aspect ='job'

The SP returns dataset, containing number of information on the job: date_created, last_run_date, last_run_time, next_run_date, next_run_time, last_run_outcome etc...

I think, this will be helpful!

Regards,

|||

Also check out MSMerge_history in the distribution database. Fill history of the job in there.

Martin

Last time publication/subscription replicated data

Is there somewhere that I can tell when the last time either the publication or better yet a subscrition replicated data (in a system table or view maybe)?

I want to set up monitoring to make sure I am aware if something for some reason does not replicate

OH and it is on SQL 2005 enterprise... an

If synchronization is processed by SQL Server Agent Job, the best choice is to view job state.

Here is example how to do it, if there is Merge publication with Merge Push Subscriptions:

1. Call sp_MSEnum_Merge_subscriptions in Distribution database to determine job ID's of the Push subscriptions:

Get PushSubscriptions Dataset

exec distribution..sp_MSEnum_Merge_subscriptions 'publisher_server', 'pub_database', 'publication_name'

2. The returning dataset contains a column named [job_id], that contains Agent Job ID in Binary format. You must convert it to hexadecimal string format (for example, 0x8392E005C4941A4C9A279A68EFC9F3D8)

3. Execute sp_help_job in MSDB database to retrieve current job state:

Get Job State

exec msdb..sp_help_job @.job_id = 0x8392E005C4941A4C9A279A68EFC9F3D8 , @.job_aspect = 'job'

The SP returns dataset, containing number of information on the job: date_created, last_run_date, last_run_time, next_run_date, next_run_time, last_run_outcome etc...

I think, this will be helpful!

Regards,

|||

Also check out MSMerge_history in the distribution database. Fill history of the job in there.

Martin

sql

Last recompilation date of the procedure / view

Hi,

Kindly let me know How will we find the last recompilation date of the procedure / view in sql server.

Regards,

S.Balavenkatesh

perhaps what you seek is in:

select refdate, crdate from sysobjects

or

select create_date, modify_date from sys.system_objects -- 2005

?

Dave

Last modified date of a view

How to get the date a view was last modified? (As opposed to created)Trev@.Work (bouncer@.localhost) writes:
> How to get the date a view was last modified? (As opposed to created)

Alas this information is not available in SQL Server. There is a column
schema_ver in sysobjects which appears to be incremented by 16 each time you
alter the view, but that is as close as you get.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns94464E4FE2E5Yazorman@.127.0.0.1...
> Trev@.Work (bouncer@.localhost) writes:
> > How to get the date a view was last modified? (As opposed to created)
> Alas this information is not available in SQL Server. There is a column
> schema_ver in sysobjects which appears to be incremented by 16 each time
you
> alter the view, but that is as close as you get.

That may help, thanks.

Last login for SA Account

How can I view when waa the last login for the SA account?The only way to track this is to either set Audit Level to All (not a great
idea as it tends to fill up the SQL errorlog and Application Event Log) or
have a permanent server side login trace.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Marcos Marrero" <mmarero@.netzero.net> wrote in message
news:59a96a25.0410130904.88a6485@.posting.google.com...
> How can I view when waa the last login for the SA account?

last is not a recognised function name

Hi all,
ive tried to run a query in a view and am getting this error message:
'last' is not a recognised function name
i was sure that last was used in sql, how can i get the same result if it wont accept 'last'?
greguse MAX

LAST is a microsoft access function|||oh yeah!! forgot the syntax changed-

cheers

greg

Large views vs multiple small views

Which is more efficient? One large view that joins >=10 tables, or a few smaller views that join only the tables needed for individual pages?

It depends on how often you use the views and how much of data you need from all the 10 tables. If majority of your usage is only couple of columns from couple of tables then smaller view with only required tables will be more efficient as opposed to one giant view that is called every time even if most of the info the view returns is not required.

Large Views need index

I have couple of large views to data for reporting. That will be wonderful i
f
there is a way I can put indexs on a view. I could'n find a way to do so.
Any ideas?
Thanks a lot.[url]http://www.novicksoftware.com/Articles/Indexed-Views-Basics-in-SQL-Server.htm[/url
]
(But remember that Index Views are only available in SQL2kEE)
HTH, Jens Suessmeyer.
"Catelin Wang" <CatelinWang@.discussions.microsoft.com> schrieb im
Newsbeitrag news:6C62F500-8C4C-4BF8-A8D4-A554D71A60C9@.microsoft.com...
>I have couple of large views to data for reporting. That will be wonderful
>if
> there is a way I can put indexs on a view. I could'n find a way to do so.
> Any ideas?
> Thanks a lot.
>|||Very good, I was thinking about the indexed view, but I was sure the
overheads it brings. We have a one-day old history database for reporting
only, may be indexed view is the best way . Thanks a lot.
"Jens Sü?meyer" wrote:

> [url]http://www.novicksoftware.com/Articles/Indexed-Views-Basics-in-SQL-Server.htm[/u
rl]
> (But remember that Index Views are only available in SQL2kEE)
> HTH, Jens Suessmeyer.
>
> "Catelin Wang" <CatelinWang@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:6C62F500-8C4C-4BF8-A8D4-A554D71A60C9@.microsoft.com...
>
>|||Jens,
FYI, We can create Indexed views on all editions of SQL Server 2000.
Info from books online
Note Indexed views can be created in any edition of SQL Server 2000. In SQL
Server 2000 Enterprise Edition, the query optimizer will automatically
consider the indexed view. To use an indexed view in all other editions, the
NOEXPAND hint must be used.
Thanks
Hari
SQL Server MVP
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:uMj89WnfFHA.1416@.TK2MSFTNGP09.phx.gbl...
> [url]http://www.novicksoftware.com/Articles/Indexed-Views-Basics-in-SQL-Server.htm[/u
rl]
> (But remember that Index Views are only available in SQL2kEE)
> HTH, Jens Suessmeyer.
>
> "Catelin Wang" <CatelinWang@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:6C62F500-8C4C-4BF8-A8D4-A554D71A60C9@.microsoft.com...
>|||>I have couple of large views to data for reporting. That will be wonderful
>if
> there is a way I can put indexs on a view.
In most cases, if you correctly index the base tables, you shouldn't need an
index on the view...|||I used union to combine 2 tables in a view, may not be indexed on the view..
.
"Catelin Wang" wrote:

> I have couple of large views to data for reporting. That will be wonderful
if
> there is a way I can put indexs on a view. I could'n find a way to do so.
> Any ideas?
> Thanks a lot.
>|||Thanks, I knew. Since I am not a fan of using Optimizer Hints, this is not
an option for me and I wouldnt suggest that to anybody.
Jens.
"Hari Prasad" <hari_prasad_k@.hotmail.com> schrieb im Newsbeitrag
news:uFnBFpnfFHA.1412@.TK2MSFTNGP09.phx.gbl...
> Jens,
> FYI, We can create Indexed views on all editions of SQL Server 2000.
> Info from books online
> Note Indexed views can be created in any edition of SQL Server 2000. In
> SQL Server 2000 Enterprise Edition, the query optimizer will automatically
> consider the indexed view. To use an indexed view in all other editions,
> the NOEXPAND hint must be used.
> Thanks
> Hari
> SQL Server MVP
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:uMj89WnfFHA.1416@.TK2MSFTNGP09.phx.gbl...
>|||"Catelin Wang" <CatelinWang@.discussions.microsoft.com> wrote in message
news:09C9D493-57C5-464C-A198-1D799FFE0B3E@.microsoft.com...[vbcol=seagreen]
>I used union to combine 2 tables in a view, may not be indexed on the
>view...
> "Catelin Wang" wrote:
>
Perhaps you can create two indexed views and union those. But perhaps there
is a more direct way to improve your performance.
If you post the table DDL, the view text and a description of how many rows
are in each base table, and how many different values each of the important
columns has, and how many rows you will get back from the query, someone may
suggest an optimization either to your base table indexes or your SQL
design.
David

Large Views need index

I have couple of large views to data for reporting. That will be wonderful if
there is a way I can put indexs on a view. I could'n find a way to do so.
Any ideas?
Thanks a lot.
http://www.novicksoftware.com/Articl...SQL-Server.htm
(But remember that Index Views are only available in SQL2kEE)
HTH, Jens Suessmeyer.
"Catelin Wang" <CatelinWang@.discussions.microsoft.com> schrieb im
Newsbeitrag news:6C62F500-8C4C-4BF8-A8D4-A554D71A60C9@.microsoft.com...
>I have couple of large views to data for reporting. That will be wonderful
>if
> there is a way I can put indexs on a view. I could'n find a way to do so.
> Any ideas?
> Thanks a lot.
>
|||Very good, I was thinking about the indexed view, but I was sure the
overheads it brings. We have a one-day old history database for reporting
only, may be indexed view is the best way . Thanks a lot.
"Jens Sü?meyer" wrote:

> http://www.novicksoftware.com/Articl...SQL-Server.htm
> (But remember that Index Views are only available in SQL2kEE)
> HTH, Jens Suessmeyer.
>
> "Catelin Wang" <CatelinWang@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:6C62F500-8C4C-4BF8-A8D4-A554D71A60C9@.microsoft.com...
>
>
|||Jens,
FYI, We can create Indexed views on all editions of SQL Server 2000.
Info from books online
Note Indexed views can be created in any edition of SQL Server 2000. In SQL
Server 2000 Enterprise Edition, the query optimizer will automatically
consider the indexed view. To use an indexed view in all other editions, the
NOEXPAND hint must be used.
Thanks
Hari
SQL Server MVP
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:uMj89WnfFHA.1416@.TK2MSFTNGP09.phx.gbl...
> http://www.novicksoftware.com/Articl...SQL-Server.htm
> (But remember that Index Views are only available in SQL2kEE)
> HTH, Jens Suessmeyer.
>
> "Catelin Wang" <CatelinWang@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:6C62F500-8C4C-4BF8-A8D4-A554D71A60C9@.microsoft.com...
>
|||>I have couple of large views to data for reporting. That will be wonderful
>if
> there is a way I can put indexs on a view.
In most cases, if you correctly index the base tables, you shouldn't need an
index on the view...
|||I used union to combine 2 tables in a view, may not be indexed on the view...
"Catelin Wang" wrote:

> I have couple of large views to data for reporting. That will be wonderful if
> there is a way I can put indexs on a view. I could'n find a way to do so.
> Any ideas?
> Thanks a lot.
>
|||Thanks, I knew. Since I am not a fan of using Optimizer Hints, this is not
an option for me and I wouldnt suggest that to anybody.
Jens.
"Hari Prasad" <hari_prasad_k@.hotmail.com> schrieb im Newsbeitrag
news:uFnBFpnfFHA.1412@.TK2MSFTNGP09.phx.gbl...
> Jens,
> FYI, We can create Indexed views on all editions of SQL Server 2000.
> Info from books online
> Note Indexed views can be created in any edition of SQL Server 2000. In
> SQL Server 2000 Enterprise Edition, the query optimizer will automatically
> consider the indexed view. To use an indexed view in all other editions,
> the NOEXPAND hint must be used.
> Thanks
> Hari
> SQL Server MVP
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:uMj89WnfFHA.1416@.TK2MSFTNGP09.phx.gbl...
>
|||"Catelin Wang" <CatelinWang@.discussions.microsoft.com> wrote in message
news:09C9D493-57C5-464C-A198-1D799FFE0B3E@.microsoft.com...[vbcol=seagreen]
>I used union to combine 2 tables in a view, may not be indexed on the
>view...
> "Catelin Wang" wrote:
Perhaps you can create two indexed views and union those. But perhaps there
is a more direct way to improve your performance.
If you post the table DDL, the view text and a description of how many rows
are in each base table, and how many different values each of the important
columns has, and how many rows you will get back from the query, someone may
suggest an optimization either to your base table indexes or your SQL
design.
David

Large Views need index

I have couple of large views to data for reporting. That will be wonderful if
there is a way I can put indexs on a view. I could'n find a way to do so.
Any ideas?
Thanks a lot.http://www.novicksoftware.com/Articles/Indexed-Views-Basics-in-SQL-Server.htm
(But remember that Index Views are only available in SQL2kEE)
HTH, Jens Suessmeyer.
"Catelin Wang" <CatelinWang@.discussions.microsoft.com> schrieb im
Newsbeitrag news:6C62F500-8C4C-4BF8-A8D4-A554D71A60C9@.microsoft.com...
>I have couple of large views to data for reporting. That will be wonderful
>if
> there is a way I can put indexs on a view. I could'n find a way to do so.
> Any ideas?
> Thanks a lot.
>|||Very good, I was thinking about the indexed view, but I was sure the
overheads it brings. We have a one-day old history database for reporting
only, may be indexed view is the best way . Thanks a lot.
"Jens Sü�meyer" wrote:
> http://www.novicksoftware.com/Articles/Indexed-Views-Basics-in-SQL-Server.htm
> (But remember that Index Views are only available in SQL2kEE)
> HTH, Jens Suessmeyer.
>
> "Catelin Wang" <CatelinWang@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:6C62F500-8C4C-4BF8-A8D4-A554D71A60C9@.microsoft.com...
> >I have couple of large views to data for reporting. That will be wonderful
> >if
> > there is a way I can put indexs on a view. I could'n find a way to do so.
> > Any ideas?
> >
> > Thanks a lot.
> >
> >
>
>|||Jens,
FYI, We can create Indexed views on all editions of SQL Server 2000.
Info from books online:)
Note Indexed views can be created in any edition of SQL Server 2000. In SQL
Server 2000 Enterprise Edition, the query optimizer will automatically
consider the indexed view. To use an indexed view in all other editions, the
NOEXPAND hint must be used.
Thanks
Hari
SQL Server MVP
"Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:uMj89WnfFHA.1416@.TK2MSFTNGP09.phx.gbl...
> http://www.novicksoftware.com/Articles/Indexed-Views-Basics-in-SQL-Server.htm
> (But remember that Index Views are only available in SQL2kEE)
> HTH, Jens Suessmeyer.
>
> "Catelin Wang" <CatelinWang@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:6C62F500-8C4C-4BF8-A8D4-A554D71A60C9@.microsoft.com...
>>I have couple of large views to data for reporting. That will be wonderful
>>if
>> there is a way I can put indexs on a view. I could'n find a way to do
>> so.
>> Any ideas?
>> Thanks a lot.
>>
>|||>I have couple of large views to data for reporting. That will be wonderful
>if
> there is a way I can put indexs on a view.
In most cases, if you correctly index the base tables, you shouldn't need an
index on the view...|||I used union to combine 2 tables in a view, may not be indexed on the view...
"Catelin Wang" wrote:
> I have couple of large views to data for reporting. That will be wonderful if
> there is a way I can put indexs on a view. I could'n find a way to do so.
> Any ideas?
> Thanks a lot.
>|||Thanks, I knew. Since I am not a fan of using Optimizer Hints, this is not
an option for me and I wouldn´t suggest that to anybody.
Jens.
"Hari Prasad" <hari_prasad_k@.hotmail.com> schrieb im Newsbeitrag
news:uFnBFpnfFHA.1412@.TK2MSFTNGP09.phx.gbl...
> Jens,
> FYI, We can create Indexed views on all editions of SQL Server 2000.
> Info from books online:)
> Note Indexed views can be created in any edition of SQL Server 2000. In
> SQL Server 2000 Enterprise Edition, the query optimizer will automatically
> consider the indexed view. To use an indexed view in all other editions,
> the NOEXPAND hint must be used.
> Thanks
> Hari
> SQL Server MVP
> "Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:uMj89WnfFHA.1416@.TK2MSFTNGP09.phx.gbl...
>> http://www.novicksoftware.com/Articles/Indexed-Views-Basics-in-SQL-Server.htm
>> (But remember that Index Views are only available in SQL2kEE)
>> HTH, Jens Suessmeyer.
>>
>> "Catelin Wang" <CatelinWang@.discussions.microsoft.com> schrieb im
>> Newsbeitrag news:6C62F500-8C4C-4BF8-A8D4-A554D71A60C9@.microsoft.com...
>>I have couple of large views to data for reporting. That will be
>>wonderful if
>> there is a way I can put indexs on a view. I could'n find a way to do
>> so.
>> Any ideas?
>> Thanks a lot.
>>
>>
>|||"Catelin Wang" <CatelinWang@.discussions.microsoft.com> wrote in message
news:09C9D493-57C5-464C-A198-1D799FFE0B3E@.microsoft.com...
>I used union to combine 2 tables in a view, may not be indexed on the
>view...
> "Catelin Wang" wrote:
>> I have couple of large views to data for reporting. That will be
>> wonderful if
>> there is a way I can put indexs on a view. I could'n find a way to do
>> so.
>> Any ideas?
Perhaps you can create two indexed views and union those. But perhaps there
is a more direct way to improve your performance.
If you post the table DDL, the view text and a description of how many rows
are in each base table, and how many different values each of the important
columns has, and how many rows you will get back from the query, someone may
suggest an optimization either to your base table indexes or your SQL
design.
David

Large view problem

I have a considerably large view that is pulling data from tables and other view, using user defined functions, and using case statements. This view is taking a lot of time to load.

I was under the impression that SQL server keeps the views uptodate, so selecting data from them is as fast as selecting from a table. It now seems like SQL server rebuilds the view evertime something accesses it.

Can someone please tell me more on this. I am now having to re-write everything :-(

Thanks Jag

Shameless bump|||Do you have indexes on the columns being referenced in the view? Are you filtering the view with a WHERE clause and do they also have indexes and how up to date are they?|||

I have never looked into indexes, so I am presuming that the columns do not have any indexes.

The view is being filtered by a where clause. However, the problem is actually building the view. When I use the query analyzer to select all the rows, it still takes 30-40 seconds to return just 252 rows !!!!

What was happening was that I was using a number of view to pull out the last data being entered into different areas. These view where then combined into this large view. I also used user defined functions to pull out other data.

I have had to change this to use triggers and store the data elsewhere. This has cut the time down considerable, but I an still wondering why the problem existed in the first place.

|||

jagdipa:

I have never looked into indexes, so I am presuming that the columns do not have any indexes.

Perhaps its time to look into it now..There are some very good articles @.http://www.sql-server-performance.com/articles.asp