Problem with GETDATE()

Hello All,

I have a problem as follows

if i execute SELECT GETDATE() statement multiple times in a single run it returns me the same datetime without any difference in even milliseconds.

I am unable to figure out what is wrong. I am assuming that whenever executed in a transaction it will give the same result.

could anybody let me know what is correct. Thanks for your help in advance.

SELECT GETDATE()

SELECT GETDATE()

SELECT GETDATE()

SELECT GETDATE()

SELECT GETDATE()

SELECT GETDATE()

SELECT GETDATE()

even then i get the same date.

What are you trying to achieve? The amount of time it takes to run multiple Select GetDate() is very minor. We would be able to help you better if we knew what your goal was.

|||

Hi, mate

I just executed:

SELECTGETDATE()SELECT *FROM Table1SELECTGETDATE()

and the the two dates was different. (Table1 has 120 000 rows)

This means that the query is executing too fast (in less than a millisecond) and that is why you receive the same results.

|||

yeah... if u execute query select getdate() several times one after another u cant understand the difference of milliseconds. don't worry...

|||

Hi Diamsorn,

Thanks for the reply. but all i am trying to do was i have a history table and i have included modified date as a part of primary key and when i am trying to update my main table i am inserting a record into history table. eventhough i am doing it in different time system says it is a violation of primary key.

For eg. Table1 is having below columns

Column1 Column2 Column3 and Suppose Primary key is composite key of column1 and column2

I have HistoryTable having columns

Column1 Column2 modifieddate and Suppose Primary key is composite key of Column1,Column2 and Modifieddate. but when i am trying to update the table1, and though trigger i am capturing getdate() to fill modifieddate, then as it is not different it is giving error.

how to overcome this problem?

Gneralproblem

|||

Which table is giving the primary key violation error? Table1 or HistoryTable.

What is your purpose of having a composite primary key in your history table of column1, column2, and modified date?

I would move away from using a trigger to insert into your history table, and do your update/insert inside of a transaction in a stored procedure. Triggers are a maintenance nightmare and I avoid them personally at all costs.

|||

Hi Diamsorn,

History table is giving me error. As i have to update the same record in Table1 and track the changes in HistoryTable. As my operation is so fast and as it is caputring same date it is giving primary key violation.

I would appreciate if any way to handle this problem using Triggers.

Thanks,

GeneralProblem

problem with exports in pdf

Hi
I am trying to export the report in pdf file with version 5.0. In pdf file
, it leaves the blank pages in between. At the same time returns half of the
information on one page and continues half on another page. For some data,
it gives acsii characters in the pdf file and when in the designer view ,
the same report looks correct.
This happens intermittently. For some data , the same RDL works fine whereas
for some, it doesnt give the proper formatting.
Please provide the solution and suggestions if any.
Thanks & Cheers,
TruptiTo keep the PDF export from leaving blank pages or having columns on one
page and others continued on the next, make sure the BODY size of the report
is not even 1 pixel more than the PAGE SIZE - (the MARGINS).
--
Adrian M.
MCP
"Trupti" <gettrupti@.hotmail.com> wrote in message
news:Oa9qP57IFHA.3332@.TK2MSFTNGP15.phx.gbl...
> Hi
> I am trying to export the report in pdf file with version 5.0. In pdf
> file , it leaves the blank pages in between. At the same time returns half
> of the information on one page and continues half on another page. For
> some data, it gives acsii characters in the pdf file and when in the
> designer view , the same report looks correct.
> This happens intermittently. For some data , the same RDL works fine
> whereas for some, it doesnt give the proper formatting.
> Please provide the solution and suggestions if any.
> Thanks & Cheers,
> Trupti
>|||Dear Trupti
I am experiencing the same problems - and at least in my case it does not
have anything to do with the definition of the report width.
I have checked my report's and body's width over and over again - everything
is as it should be. And according to the piece of advice Microsoft is dishing
out setting the correct report and body width should do the trick - but it
does not. And it seems as if nobody is really working on solving this problem
because there are no better answers to be found in this newsgroup - and the
problem is known for a long time now!
According to my own experiences, reports with tables do just fine. It is
reports that contain a matrix that have this pdf-problem. We have even
istalled and tested the beta version of SP2, but with no better results. I am
at the end of my rope. I really wish somebody would come up with an
explanation and solution soon!
Greetings to all poor souls out there suffering from the same phenomenon...
"Trupti" wrote:
> Hi
> I am trying to export the report in pdf file with version 5.0. In pdf file
> , it leaves the blank pages in between. At the same time returns half of the
> information on one page and continues half on another page. For some data,
> it gives acsii characters in the pdf file and when in the designer view ,
> the same report looks correct.
> This happens intermittently. For some data , the same RDL works fine whereas
> for some, it doesnt give the proper formatting.
> Please provide the solution and suggestions if any.
> Thanks & Cheers,
> Trupti
>
>|||Send a description of the problem/issue to:
mswish@.microsoft.com
http://www.microsoft.com/mswish
That way you don't just wait for a 'solution' - you can have an active share
in encouraging Microsoft to fix the problem.
--
Adrian M.
MCP
"j schuetz" <jschuetz@.discussions.microsoft.com> wrote in message
news:B16D99CB-D4A7-48A3-9CA0-627B73C94904@.microsoft.com...
> Dear Trupti
> I am experiencing the same problems - and at least in my case it does not
> have anything to do with the definition of the report width.
> I have checked my report's and body's width over and over again -
> everything
> is as it should be. And according to the piece of advice Microsoft is
> dishing
> out setting the correct report and body width should do the trick - but it
> does not. And it seems as if nobody is really working on solving this
> problem
> because there are no better answers to be found in this newsgroup - and
> the
> problem is known for a long time now!
> According to my own experiences, reports with tables do just fine. It is
> reports that contain a matrix that have this pdf-problem. We have even
> istalled and tested the beta version of SP2, but with no better results. I
> am
> at the end of my rope. I really wish somebody would come up with an
> explanation and solution soon!
> Greetings to all poor souls out there suffering from the same
> phenomenon...
>
> "Trupti" wrote:
>> Hi
>> I am trying to export the report in pdf file with version 5.0. In pdf
>> file
>> , it leaves the blank pages in between. At the same time returns half of
>> the
>> information on one page and continues half on another page. For some
>> data,
>> it gives acsii characters in the pdf file and when in the designer view
>> ,
>> the same report looks correct.
>> This happens intermittently. For some data , the same RDL works fine
>> whereas
>> for some, it doesnt give the proper formatting.
>> Please provide the solution and suggestions if any.
>> Thanks & Cheers,
>> Trupti
>>|||I have deposited a detailed documentation with the Microsoft support of my
home country - but up to now nothing has resulted of that either.

Problem with ExecuteNonQuery

I have created a stored procedure that takes several parameters and ultimately does an INSERT on two tables. The sp returns with an integer indicating which is positive if one or more rows were added.

If I execute the SP by hand using the SQL Server Management Studio Express I get the proper results, the records are added to both tables and the return values are proper. One is an output parameter indicating the Identity value of the main record, the return value simply >0 if OK.

However, when I use C#, build my connection, command and its associated parameters making sure they match the SP then I get a malfunction.

The problem is that when I call ExecuteNonQuery the integer value it returns is -1 even though calling it from Mgmt. Studio gives a >0 result. Even though it returns -1 I can confirm that the records were added to BOTH tables and that the output parameter (The identity) given to me is also correct. However the return value is always -1.

I have no idea what is going wrong, Since I have SQL Express 2005 I do cannot do profiling :(. I really don't see why this goes wrong and I think using ExecuteScalar is not the best choice for this type of action.

ExecuteScalar is used for database calls that return one and only one value. This sounds like what you're doing. Why don't you think that ExecuteScalar is appropriate for this?

|||

The return Value from ExecuteNonQuery retuns number of rows effected. It is better to use ExecuteScalar but not must. For execute nonquery commnd, 0th [zero] command parameter is your return value. try to access that. If you dont get then create another parameter to ur sp @.newID OUTPUT, and set that in u r sp @.newID = @.@.IDENTITY. and access that in your CommandObject after executing ExecuteNonQuery();

|||

Are you checking the return value from the ExecuteNonQuery function, or the parameter with type ReturnValue?

result=cmd.ExecuteNonQuery()

or

cmd.Parameters.Add("@.RETURN_VALUE",sqldbtype.Int).ParameterDirection=ReturnValue

cmd.executeNonQuery()

result=cmd.Parameters("@.RETURN_VALUE").Value

?

Problem with displaying image on the report

I have a webservice which creates an image returns that image in the form of Byte array. I want to consume this webservice and display that image on my report. When I see my report in the Preview tab of the report designer, it displays that image but when I deploy my report on the report server, image doesn't show up. I have copied app_code.dll (dll for the webservice) to the following locations:

C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies
C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PublicAssemblies
C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\bin

Here is the code for the webservice:

Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.Drawing
Imports System.Diagnostics
Imports System.IO

<WebService(Namespace:="http://tempuri.org/")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _

Public Class Service
Inherits System.Web.Services.WebService

<WebMethod()> _

Public Function HelloWorld() As Byte()

Dim marker As New Bitmap("C:\Inetpub\wwwroot\help.gif")
Dim imageStream As New System.IO.MemoryStream
marker.Save(imageStream, System.Drawing.Imaging.ImageFormat.Jpeg)
Trace.Write("Before Write")
Return imageStream.ToArray()

End Function

End Class

I have referenced this dll saved in public assemblies folder from my report project and wrote a function to call the above mentioned webservice function HelloWorld() and called the function from value property of an image control on the report.

Does anybody has any idea what am I missing?

Thanks

Its probably an authentication problem. Try configuring an Unattended Execution Account for SRS to an account which has permission to access the web service on the remote machine. Also check the IIS logs on the machine serving the image for failed login attempts.

Problem with DateTime and strings in stored procedures

I'm keeping in the database a log of all the sessions for my
application. I'm trying to write a stored procedure that returns all
the sessions that; the login contains a certain string, loggedin after
a certain datetime and loggedout before another datetime. Any
combination of these parameters can be used and, if none, returns all
the log.

Below is the code I came up with but I'm having a "Syntax error
converting datetime from character string" exception. When not using
DateTime parameters everything works fine. Can you tell me how can I
avoid this exception? Thanks in advance...

ALTER PROCEDURE dbo.RetrieveAllSessionHistoryItemsContaining
(
@.Pattern Varchar(255),
@.From DateTime,
@.To DateTime
)
AS
DECLARE @.Query VARCHAR(500)
SET @.Query = 'SELECT * FROM SessionHistoryItems, Sessions WHERE
SessionHistoryItems.SessionId = Sessions.SessionId'

DECLARE @.conditions nvarchar(257)
SET @.conditions = '';

IF LEN(@.Pattern) > 0 BEGIN
SET @.conditions = @.conditions + ' Sessions.Login LIKE ''%' + @.Pattern
+ '%'''
END

IF @.From IS NOT NULL BEGIN
IF LEN(@.conditions) > 0 BEGIN
SET @.conditions = @.conditions + ' AND '
END
SET @.conditions = @.conditions + ' SessionHistoryItems.LoggedOutAt >=
' + @.From
END

IF @.To IS NOT NULL BEGIN
IF LEN(@.conditions) > 0 BEGIN
SET @.conditions = @.conditions + ' AND '
END
SET @.conditions = @.conditions + ' SessionHistoryItems.LoggedInAt <= '
+ @.To
END

IF LEN(@.conditions) > 0 BEGIN
EXEC(@.Query + ' AND ' + @.conditions)
END
ELSE BEGIN
EXEC(@.Query)
END
RETURN<antao@.iilab.com> wrote in message
news:1117467828.906603.299460@.g49g2000cwa.googlegr oups.com...
> I'm keeping in the database a log of all the sessions for my
> application. I'm trying to write a stored procedure that returns all
> the sessions that; the login contains a certain string, loggedin after
> a certain datetime and loggedout before another datetime. Any
> combination of these parameters can be used and, if none, returns all
> the log.
> Below is the code I came up with but I'm having a "Syntax error
> converting datetime from character string" exception. When not using
> DateTime parameters everything works fine. Can you tell me how can I
> avoid this exception? Thanks in advance...
> ALTER PROCEDURE dbo.RetrieveAllSessionHistoryItemsContaining
> (
> @.Pattern Varchar(255),
> @.From DateTime,
> @.To DateTime
> )
> AS
> DECLARE @.Query VARCHAR(500)
> SET @.Query = 'SELECT * FROM SessionHistoryItems, Sessions WHERE
> SessionHistoryItems.SessionId = Sessions.SessionId'
> DECLARE @.conditions nvarchar(257)
> SET @.conditions = '';
> IF LEN(@.Pattern) > 0 BEGIN
> SET @.conditions = @.conditions + ' Sessions.Login LIKE ''%' + @.Pattern
> + '%'''
> END
> IF @.From IS NOT NULL BEGIN
> IF LEN(@.conditions) > 0 BEGIN
> SET @.conditions = @.conditions + ' AND '
> END
> SET @.conditions = @.conditions + ' SessionHistoryItems.LoggedOutAt >=
> ' + @.From
> END
> IF @.To IS NOT NULL BEGIN
> IF LEN(@.conditions) > 0 BEGIN
> SET @.conditions = @.conditions + ' AND '
> END
> SET @.conditions = @.conditions + ' SessionHistoryItems.LoggedInAt <= '
> + @.To
> END
> IF LEN(@.conditions) > 0 BEGIN
> EXEC(@.Query + ' AND ' + @.conditions)
> END
> ELSE BEGIN
> EXEC(@.Query)
> END
> RETURN

It looks like you need to CAST or CONVERT the datetime to a string (and add
quotes) in order to build up the @.conditions string:

declare @.dt datetime
set @.dt = getdate()

select 'x' + @.dt -- fails
select 'x''' + cast(@.dt as varchar(20)) + '''' -- succeeds

But in this case, using sp_executesql would probably be a better approach
anyway:

exec sp_executesql
N'select col1, col2 from dbo.MyTable where datecol >= @.From and datecol <=
@.To',
N'@.From datetime, @.To datetime',
@.From, @.To

See sp_executesql in Books Online, and also these articles for more
information/ideas:

http://www.sommarskog.se/dynamic_sql.html
http://www.sommarskog.se/dyn-search.html

Simon

Problem with Date Function ?

I insert date into table using getdate() function and that works fine.

Now from my query when I try to compare with it and returns nothing.

Thats how the date is stored in the table : 6/4/2007 1:46:57 PM

Now If I do

SELECT * FROM Sometable

WHERE date = getdate() ///Nothng returned

SELECT * FROM sometable

WHERE date = '6-4-2007' // Nothing is returned

How to Fix this issue ?

This has to do with the way that datetime data is stored. Try this:

Code Snippet

select * from someTable

where date >= convert(datetime(convert(varchar(10), getdate(), 101)))

and date < convert(datetime(convert(varchar(10), getdate()+1, 101)))

or

Code Snippet

select * from someTable

where date >= '6-4-2007'

and date < convert(datetime, '6-4-2007')+1

The problem with using the BETWEEN operator is that it includes events that occur exactly on midnight which is technically part of the next day. There are a number of other ways of doing this in addition to these two.

Something that you ought to avoid is loading either GETDATE() or your date constant '6-4-2007' into a variable. Loading this data into a variable eliminates some optimization options that might be exploited by the SQL optimizer.

|||

Hello Kent,

That will return me all dates greater than that specific date. I want the exact date, is there any way to change how the date is stored ? As i'm creating my SQL programatically, it is hard to have everything done at that level.

|||

Sorry my bad, I made a mistake in dates. That works !!!

Is there any way to fix this at Database Level....

|||

Harsimrat,

Datatime data type can include also time part, so if you need to select rows you will have to specify the time part you want.

select *

from sometable

where date >= '20070604' and date < '20070605'

-- or

select *

from sometable

where date between '2007-06-04T10:00:00' and '2007-06-04T18:59:59.997'

If you used function getdate() to insert the rows, then remember that this function returns current date and time and it will not be useful to match rows inserted in the past unless both operations are executed in the same time.

-- this could work

declare @.t table (c1 datetime)

insert into @.t values(getdate())

select * from @.t where c1 = getdate()

-- but not this

declare @.t table (c1 datetime)

insert into @.t values(getdate())

waitfor delay '00:00:00.003'

select * from @.t where c1 = getdate()

AMB

|||

Adding to Kent's explanition, when you store a datetime value as you have, then you will not get a match unless the criteria has the EXACT same datetime value -up to the milliseconds.

So to find all values for a particular date, you need to look for values since midnight, and up to the next midnight. Kent provided one set of criteria that accomplishes that goal, here is another.

--For today

Code Snippet

WHERE ( [Date] >= dateadd( day, datediff( day, 0, getdate() ), 0 )
AND [Date] < dateadd( day, datediff( day, 0, getdate() + 1 ), 0)

)

p.s., You really shouldn't name your columns and tables with 'RESERVED WORDS'. [Date] is a reserved word that has special meaning in SQL Server. Refer to Books Online, Topic: 'Reserved Words'

|||

When I run this query:

Code Snippet

declare @.someTable table
( date datetime
)
insert into @.someTable
select '6/3/7 15:00' union all
select '6/3/7 23:59:59.997' union all
select '6/4/7' union all
select '6/4/7 8:00' union all
select '6/4/7 23:59:59.997' union all
select '6/5/7'
--select * from @.someTable

select * from @.someTable
where date >= convert(datetime, (convert(varchar(10), getdate(), 101)))
and date < convert(datetime, (convert(varchar(10), getdate()+1, 101)))

I get this result:

Code Snippet

date
--
2007-06-04 00:00:00.000
2007-06-04 08:00:00.000
2007-06-04 23:59:59.997

Is that result incorrect?