Showing posts with label ive. Show all posts
Showing posts with label ive. Show all posts

Friday, March 30, 2012

Layout problem

Layout problem -
I've created a report with the wizard and it looks fine.
It have a TITLE, a few SUB TITLES (all are textbox with values) and a textbox with JUMP TO link that use as a back button. beneath is a table.

Whan I run ut it look fine (In the first page there is the header and underneath there is the table) but I've noticed that whan the report have more then one page, all of my header (e.g. TITLE,SUBTITLE,BUTTON) is not showen in the second page and above and only the table is.

I've tried to move the title to the header part but it give me an error the the value of the textbox in the header refers to a field and field can't be use in a page header.

Can anybody help me with the title part to make it visible in all pages?

Thank alot in advance,
Roy.Are you using a true page header or just a couple of test fields at the top of the page. Click the Report menu and see if you have a Page Header selected.

Friday, March 23, 2012

Last Time Object Used

I have not been able to find anything remotely close to doing what I need.
I've been told that nothing like this exists in SQL Server, but I'm sure
there has to be something, somewhere, somehow to do this.
I need to find out when the last time an object was used.
The object could be a table, view, stored proc, etc.
I have a SQL Server than I am trying to maintain, and along with that I need
to get rid of all of the things from the past that having been used in some
time, and aren't part of the business model anymore.
If anyone could please help me, I would really appreciate it. I would
rather not have to go object by object and having to discuss it individually
to determine if it's been used recently or not.
Thank You.
- Anthony
You can setup SQL Trace to audit the use of the objects in a database. Let
the trace(s) run for a sufficient period of time, and then analyze the trace
file to see what objects have been used.
But personally, I would not rely on this method to determine what objects I
can get rid of. Rather, I'd do what you rather not have to do, that is, to
discuss object by object with the application folks, vendors, or whoever may
own the object, and get their approval before dropping anything. If I can't
find the owner, I'd rather leave the object there than drop a wrong object
and face the wrath of its user. You just never know. For instance, you may
have some objects that will only be used durign the critically important
year-end processes, and your trace/audit may not find them to be used even
if you audit your system for six months.
Linchi
"ALaRiva" <ALaRiva@.discussions.microsoft.com> wrote in message
news:EFA01E76-B5BF-4423-AEF1-E3359EDD57DC@.microsoft.com...
>I have not been able to find anything remotely close to doing what I need.
> I've been told that nothing like this exists in SQL Server, but I'm sure
> there has to be something, somewhere, somehow to do this.
> I need to find out when the last time an object was used.
> The object could be a table, view, stored proc, etc.
> I have a SQL Server than I am trying to maintain, and along with that I
> need
> to get rid of all of the things from the past that having been used in
> some
> time, and aren't part of the business model anymore.
> If anyone could please help me, I would really appreciate it. I would
> rather not have to go object by object and having to discuss it
> individually
> to determine if it's been used recently or not.
> Thank You.
> - Anthony
|||Create an event notification to queue to an audit table. F the SQL Profile
solution.
"ALaRiva" <ALaRiva@.discussions.microsoft.com> wrote in message
news:EFA01E76-B5BF-4423-AEF1-E3359EDD57DC@.microsoft.com...
>I have not been able to find anything remotely close to doing what I need.
> I've been told that nothing like this exists in SQL Server, but I'm sure
> there has to be something, somewhere, somehow to do this.
> I need to find out when the last time an object was used.
> The object could be a table, view, stored proc, etc.
> I have a SQL Server than I am trying to maintain, and along with that I
> need
> to get rid of all of the things from the past that having been used in
> some
> time, and aren't part of the business model anymore.
> If anyone could please help me, I would really appreciate it. I would
> rather not have to go object by object and having to discuss it
> individually
> to determine if it's been used recently or not.
> Thank You.
> - Anthony
|||Thank You Both!
Yes. It looks like my only option will be to go through each object, one by
one. We do know that there are alot of objects that haven't been used in
years, which are the ones that I was aiming for, however, a trace/audit won't
help because I can't wait 2 years to make sure it's not a year-end thing.
The cleanup and optimization is in preparation for possibly going to SQL
Server 2005. We are currently running SQL 7, and I'm the sole person
responsible for SQL and deciding on whether we should go to 2005 or not.
Looks like I have alot of calling to do. If/Once we go to 2005, I'm only
going to import object by object. We won't be getting rid of the current SQL
Server, so if something turns up to be required later in the future, we could
go back and get it.
Thank You everyone for the suggestions.
- Anthony

Monday, March 19, 2012

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

Monday, March 12, 2012

Last Date of previous Month

ive been trying very unsucessfully to try and get the last date of the
previous month,
ie if i run the query today i want the result to be 31/01/2006
declare @.d datetime
set @.d='20060201'
select DATEADD(day,-DATEPART(day,@.d),@.d)|||print dateadd( day, -1, cast( '1 ' + datename( month, getdate() ) + ' ' +
datename( year, getdate() ) as smalldatetime ) )
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:168DF5B1-BCE5-4601-802A-8FF10CAD3F2E@.microsoft.com...
> ive been trying very unsucessfully to try and get the last date of the
> previous month,
> ie if i run the query today i want the result to be 31/01/2006
>|||Have you looked up how to use a Calendar table? The table could
include a code for last (business?) day of each month, so you can pull
out a set of such dates for reports. I
Your other options are computational. There is the "COBOL method" and
the "UNIX method". The Cobol guys think of dates as character strngs,
and they will build a date with concatendation and extraction
functions. The bad news is that they will probably use a local date
format instead of ISO-8601 style.
The UNIX method uses extraction functions and temporal math -- go to
the first of this month and subtract 1 day. Since Sybase/SQL Server
use a floating point number for date time, it can be pretty fast for
one-at-time processing.|||SELECT DATEADD(d,- 1,CAST(CONVERT(char(6),CURRENT_TIMESTAMP
,112) + '01'
as smalldatetime))
Here are a few other little tricks too:
http://sqlnerd.blogspot.com/2005/06...sql-tricks.html
*mike hodgson*
http://sqlnerd.blogspot.com
Peter Newman wrote:

>ive been trying very unsucessfully to try and get the last date of the
>previous month,
>ie if i run the query today i want the result to be 31/01/2006
>
>|||Why go to all the effort and extra storage of building a calendar table when
you can simply use one of SQL Servers built in functions...
dateadd( day, -1, cast( '1 ' + datename( month, getdate() ) + ' ' +
datename( year, getdate() ) as smalldatetime ) )
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1138838149.462000.53340@.o13g2000cwo.googlegroups.com...
> Have you looked up how to use a Calendar table? The table could
> include a code for last (business?) day of each month, so you can pull
> out a set of such dates for reports. I
> Your other options are computational. There is the "COBOL method" and
> the "UNIX method". The Cobol guys think of dates as character strngs,
> and they will build a date with concatendation and extraction
> functions. The bad news is that they will probably use a local date
> format instead of ISO-8601 style.
> The UNIX method uses extraction functions and temporal math -- go to
> the first of this month and subtract 1 day. Since Sybase/SQL Server
> use a floating point number for date time, it can be pretty fast for
> one-at-time processing.
>|||>>Why go to all the effort and extra storage of building a calendar table wh
en you can simply use one of SQL Servers built in functions...<<
1) Portability. Temporal functions vary quite a bit from product. Did
you mean to avoid using the Standard CURRENT_TIMESTAMP with the
proprietary getdate()?
2) Standards conformance. Did you notice that you assumed the date
format is "dd-mm-yyyy" because you are British? An American would
assume "mm-dd-yyyy" instead. A SQL programmer would ** know** that
'yyyy-mm-dd" is the only format allowed in the ISO standards
3) Your code will have problems with the time part of a DATETIME. the
last day of a month should end at the last possible fraction of a
second to be sure to catch everything. The standard SQL version would
be:
CAST (EXTRACT (YEAR FROM CURRENT_TIMESTAMP)
|| EXTRACT (MONTH FROM CURRENT_TIMESTAMP)
|| '01 23:59:59.99999' AS TIMESTAMP) - INTERVAL '01' DAY
4) The Calendar table is a more general tool for other temporal
problems. For a small extra effort now, he can solve a lot of problems
in the future.
Unfortunately, I find that thanks to Agile / Extreme / Fast Cycle Time
/ <<insert cowboy coding fad here>>. the current batch programmers
think only in terms of the immediate problem. The results are bad.
It is not just duplicated code that cannot be found to be refactored.
It is a lot of code that does the same task in many different ways.
Arrrgh!|||I'm with Joe on this one. Solve the problem with data rather than
code. Then you can get the last day of last month, the last business
day of last month, the first day of this month, the first business
day... and so on.
Best
Payson
Peter Newman wrote:
> ive been trying very unsucessfully to try and get the last date of the
> previous month,
> ie if i run the query today i want the result to be 31/01/2006|||> 2) Standards conformance. Did you notice that you assumed the date
> format is "dd-mm-yyyy" because you are British? An American would
> assume "mm-dd-yyyy" instead. A SQL programmer would ** know** that
> 'yyyy-mm-dd" is the only format allowed in the ISO standards
Oh dear, not checked the product specs lately?
I use the format DD MMMMMMMM YYYY, example 1 February 2006; so there is NO
confusion at all. Also, I remove the time component entirely and reset back
to midnight on casting to smalldatetime
print '1 ' + datename( month, getdate() ) + ' ' + datename( year,
getdate() )
Even a beginner would **know** to look at the manual and not guess how
things work, how come you make such fundemental mistakes?

> 3) Your code will have problems with the time part of a DATETIME. the
> last day of a month should end at the last possible fraction of a
> second to be sure to catch everything. The standard SQL version would
> be:
> CAST (EXTRACT (YEAR FROM CURRENT_TIMESTAMP)
> || EXTRACT (MONTH FROM CURRENT_TIMESTAMP)
> || '01 23:59:59.99999' AS TIMESTAMP) - INTERVAL '01' DAY
That was not the posters requirement, they required a date without the time
component, which we don't have in SQL Server, so within SQL Server we use
00:00:00.000 for when no time component is required - again, even a beginner
would **know** that.
The syntax you give doesn't even work in SQL Server and as this is a
MICROSOFT SQL SERVER forum I would appreciate it if you at least point out
the differences when you post your unusable rubbish.

> 4) The Calendar table is a more general tool for other temporal
> problems. For a small extra effort now, he can solve a lot of problems
> in the future.
A very big overhead for a simple problem, again, stop bloating up the design
to make more money. Keep things Simple Sweet and the design will be more
supportable and less likely to be buggy.

> Unfortunately, I find that thanks to Agile / Extreme / Fast Cycle Time
> / <<insert cowboy coding fad here>>. the current batch programmers
> think only in terms of the immediate problem. The results are bad.
> It is not just duplicated code that cannot be found to be refactored.
> It is a lot of code that does the same task in many different ways.
> Arrrgh!
Seriously, you need to get some real programming experience because you
don't seem to have any, either that or just stick to logical design and live
the programming to people who have been professionally trained and not
picked their knowledge up from reading books.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1138908162.308682.73220@.f14g2000cwb.googlegroups.com...
> 1) Portability. Temporal functions vary quite a bit from product. Did
> you mean to avoid using the Standard CURRENT_TIMESTAMP with the
> proprietary getdate()?
> 2) Standards conformance. Did you notice that you assumed the date
> format is "dd-mm-yyyy" because you are British? An American would
> assume "mm-dd-yyyy" instead. A SQL programmer would ** know** that
> 'yyyy-mm-dd" is the only format allowed in the ISO standards
> 3) Your code will have problems with the time part of a DATETIME. the
> last day of a month should end at the last possible fraction of a
> second to be sure to catch everything. The standard SQL version would
> be:
> CAST (EXTRACT (YEAR FROM CURRENT_TIMESTAMP)
> || EXTRACT (MONTH FROM CURRENT_TIMESTAMP)
> || '01 23:59:59.99999' AS TIMESTAMP) - INTERVAL '01' DAY
> 4) The Calendar table is a more general tool for other temporal
> problems. For a small extra effort now, he can solve a lot of problems
> in the future.
> Unfortunately, I find that thanks to Agile / Extreme / Fast Cycle Time
> / <<insert cowboy coding fad here>>. the current batch programmers
> think only in terms of the immediate problem. The results are bad.
> It is not just duplicated code that cannot be found to be refactored.
> It is a lot of code that does the same task in many different ways.
> Arrrgh!
>|||I frequently use calendar table for calculations like '5 business days
later' or
'now many business daya are there between 15-jan-2006 and 25-jan-2006'.
Ignoring any theories, with calendar table my queries sometimes perform
better.
I don't take that for granted and like to re-check that I'm using
efficient queries from time to time.
In this particular case, I took 500K rows from a production table. I
ran this query:
set statistics time on
go
select min(dateadd( day, -1, cast( '1 ' + datename( month, order_dt ) +
' ' +
datename( year, order_dt ) as smalldatetime ) )) mmin, max(dateadd(
day, -1, cast( '1 ' + datename( month, order_dt ) + ' ' +
datename( year, order_dt ) as smalldatetime ) ) ) mmx
from orders
...
SQL Server Execution Times:
CPU time = 8922 ms, elapsed time = 17423 ms.
Then I built a calendar table:
create table month_calendar(num int, date_from datetime, date_to
datetime)
declare @.d1 datetime
declare @.d2 datetime
declare @.n int
set @.d1='19700101'
set @.d2='19700201'
set @.n=1
while @.d1 < '20060505'
begin
insert into month_calendar values(@.n, @.d1,@.d2)
set @.n=@.n+1
set @.d1 = @.d2
set @.d2 = dateadd(month, 1, @.d2)
end
go
select min(date_from), max(date_to) from month_calendar
select count(*) from month_calendar
create unique clustered index uci on month_calendar(date_from)
select * from month_calendar
then I ran another query:
select min(m.num), max(m.num)
--select count(*)
from month_calendar m join orders on date_from<=order_dt and
order_dt<date_to
SQL Server Execution Times:
CPU time = 563 ms, elapsed time = 1010 ms.
(both queries were rerun several times to account for warm/cold cache,
random fluctuations,
whatever else)
So for this particular scenario in this particular environment I'd
definitely go for a calendar table, if and only if the query is run
frequently.
I'm not saying calendar tables are the best choice in all the cases.
Any comments?

Last date of access on a DB

Hi!
I've a many test servers with sql server 2000. On these server i've a lot of
DB for test, development and staging. I want to delete some of these DB, but
i'm not sure what of them are really being accessed. Is there any option
active script or whatever to know when was the last time a BD was accessed on
the server?
Any help will be appreciated.
FRivas
Frivas wrote:
> Hi!
> I've a many test servers with sql server 2000. On these server i've a
> lot of DB for test, development and staging. I want to delete some of
> these DB, but i'm not sure what of them are really being accessed. Is
> there any option active script or whatever to know when was the last
> time a BD was accessed on the server?
> Any help will be appreciated.
> FRivas
You could try running a Profiler trace and capturing the Connect or
Disconnect events. The database ID is captured. Of course, this is only
the database that was originally connected to and may not be the
database where all the work took place.
Another option is to capture RPC:Completed and SQL:BatchCompleted
events. YOu don't have to bind the TextData to the trace if you want to
save space but you need to create a server-side trace to do this. This
trace captures all executed SQL on the server and you can easily see the
database id. If you were only concerned about specific databases you
could filter them out (once again I think you need to use a T-SQL
server-side trace for this type of filtering of multiple databases).
David Gugick
Imceda Software
www.imceda.com
|||Turn on dbo use only or single user mode..and wait to see who complains?
Jeff
"Frivas" <Frivas@.discussions.microsoft.com> wrote in message
news:228FA9DF-7284-48A6-B4E1-FB9F3B50F1DD@.microsoft.com...
> Hi!
> I've a many test servers with sql server 2000. On these server i've a lot
of
> DB for test, development and staging. I want to delete some of these DB,
but
> i'm not sure what of them are really being accessed. Is there any option
> active script or whatever to know when was the last time a BD was accessed
on
> the server?
> Any help will be appreciated.
> FRivas
>