application retrieve same records, sometimes fast, sometimes extreme slow

Hi,
Sometimes, in my application, when i browse a
record, it takes very fast 1/2 seconds but sometimes it
seems like hang there and take a very long time around
10 minutes to finish,could it be deadlock ? Any other
suggestion?
How to check and prevent from deadlock?
thanks.
regards,
florence
Could it be possible you are blocked due to your choice of isolation levels?
You could run sp_who and see if anyone is blocking you the next time your
app slows down. You could set to READ UNCOMMITTED if its acceptable by your
app. Then again, a commit by another app should not take that long, so you
might want to look at fixing the other app instead.
Another possibility is that the data was in the buffer cache when the app
was fast.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:158001c4ac52$2735ceb0$a401280a@.phx.gbl...
> Hi,
> Sometimes, in my application, when i browse a
> record, it takes very fast 1/2 seconds but sometimes it
> seems like hang there and take a very long time around
> 10 minutes to finish,could it be deadlock ? Any other
> suggestion?
> How to check and prevent from deadlock?
> thanks.
> regards,
> florence
>
|||Hi,
Thanks for your reply. I didn't set any isolation
level in my application, so i think by default it should
be read uncommitted right? How to detect whether the data
is in the buffer cache but the application run too fast?
Thanks

>--Original Message--
>Could it be possible you are blocked due to your choice
of isolation levels?
>You could run sp_who and see if anyone is blocking you
the next time your
>app slows down. You could set to READ UNCOMMITTED if
its acceptable by your
>app. Then again, a commit by another app should not
take that long, so you
>might want to look at fixing the other app instead.
>Another possibility is that the data was in the buffer
cache when the app
>was fast.
>--
>Peter Yeoh
>http://www.yohz.com
>Need smaller SQL2K backup files? Use MiniSQLBackup
Lite, free!
>
>"florencelee" <florencelee@.visualsolutions.com.my> wrote
in message[vbcol=seagreen]
>news:158001c4ac52$2735ceb0$a401280a@.phx.gbl...
it
>
>.
>
|||DBCC MEMUSAGE(names, 50) to show the top 50 objects in the buffer cache by
number of pages (each page is 8k).
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:3db501c4ac5a$f93f5d10$a501280a@.phx.gbl...[vbcol=seagreen]
> Hi,
> Thanks for your reply. I didn't set any isolation
> level in my application, so i think by default it should
> be read uncommitted right? How to detect whether the data
> is in the buffer cache but the application run too fast?
> Thanks
>
> of isolation levels?
> the next time your
> its acceptable by your
> take that long, so you
> cache when the app
> Lite, free!
> in message
> it

application retrieve same records, sometimes fast, sometimes extreme slow

Hi,
Sometimes, in my application, when i browse a
record, it takes very fast 1/2 seconds but sometimes it
seems like hang there and take a very long time around
10 minutes to finish,could it be deadlock ? Any other
suggestion?
How to check and prevent from deadlock?
thanks.
regards,
florenceCould it be possible you are blocked due to your choice of isolation levels?
You could run sp_who and see if anyone is blocking you the next time your
app slows down. You could set to READ UNCOMMITTED if its acceptable by your
app. Then again, a commit by another app should not take that long, so you
might want to look at fixing the other app instead.
Another possibility is that the data was in the buffer cache when the app
was fast.
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:158001c4ac52$2735ceb0$a401280a@.phx.gbl...
> Hi,
> Sometimes, in my application, when i browse a
> record, it takes very fast 1/2 seconds but sometimes it
> seems like hang there and take a very long time around
> 10 minutes to finish,could it be deadlock ? Any other
> suggestion?
> How to check and prevent from deadlock?
> thanks.
> regards,
> florence
>|||Hi,
Thanks for your reply. I didn't set any isolation
level in my application, so i think by default it should
be read uncommitted right? How to detect whether the data
is in the buffer cache but the application run too fast?
Thanks
>--Original Message--
>Could it be possible you are blocked due to your choice
of isolation levels?
>You could run sp_who and see if anyone is blocking you
the next time your
>app slows down. You could set to READ UNCOMMITTED if
its acceptable by your
>app. Then again, a commit by another app should not
take that long, so you
>might want to look at fixing the other app instead.
>Another possibility is that the data was in the buffer
cache when the app
>was fast.
>--
>Peter Yeoh
>http://www.yohz.com
>Need smaller SQL2K backup files? Use MiniSQLBackup
Lite, free!
>
>"florencelee" <florencelee@.visualsolutions.com.my> wrote
in message
>news:158001c4ac52$2735ceb0$a401280a@.phx.gbl...
>> Hi,
>> Sometimes, in my application, when i browse a
>> record, it takes very fast 1/2 seconds but sometimes
it
>> seems like hang there and take a very long time around
>> 10 minutes to finish,could it be deadlock ? Any other
>> suggestion?
>> How to check and prevent from deadlock?
>> thanks.
>> regards,
>> florence
>
>.
>|||DBCC MEMUSAGE(names, 50) to show the top 50 objects in the buffer cache by
number of pages (each page is 8k).
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:3db501c4ac5a$f93f5d10$a501280a@.phx.gbl...
> Hi,
> Thanks for your reply. I didn't set any isolation
> level in my application, so i think by default it should
> be read uncommitted right? How to detect whether the data
> is in the buffer cache but the application run too fast?
> Thanks
>
> >--Original Message--
> >Could it be possible you are blocked due to your choice
> of isolation levels?
> >You could run sp_who and see if anyone is blocking you
> the next time your
> >app slows down. You could set to READ UNCOMMITTED if
> its acceptable by your
> >app. Then again, a commit by another app should not
> take that long, so you
> >might want to look at fixing the other app instead.
> >
> >Another possibility is that the data was in the buffer
> cache when the app
> >was fast.
> >
> >--
> >Peter Yeoh
> >http://www.yohz.com
> >Need smaller SQL2K backup files? Use MiniSQLBackup
> Lite, free!
> >
> >
> >"florencelee" <florencelee@.visualsolutions.com.my> wrote
> in message
> >news:158001c4ac52$2735ceb0$a401280a@.phx.gbl...
> >> Hi,
> >> Sometimes, in my application, when i browse a
> >> record, it takes very fast 1/2 seconds but sometimes
> it
> >> seems like hang there and take a very long time around
> >> 10 minutes to finish,could it be deadlock ? Any other
> >> suggestion?
> >>
> >> How to check and prevent from deadlock?
> >>
> >> thanks.
> >>
> >> regards,
> >>
> >> florence
> >>
> >
> >
> >.
> >

Apostrophes in field

If I am using a variable to retrieve a field value and it has an apostrophe in the field, I am getting an error

DECLARE @.C_CenterName nvarchar(255)

...

FETCH NEXT FROM curJCD INTO @.C_CenterName,@.Address,@.City,@.State,@.Zip

...

SET @.SQL='INSERT INTO dbo.Newsletter(ID,[Center Name],[Address 1],City,State,Zip,Active,[Child Care Centers]) VALUES(' + CAST(@.NextClientID AS NVARCHAR(10)) + ',' + @.C_CenterName + ',' + @.Address + ',' + @.City + ',' + @.State + ',' + @.Zip + ',1,1);'

@.C_CenterName sometimes has apostrophe

ERROR BELOW:

Msg 105, Level 15, State 1, Line 1

Unclosed quotation mark before the character string

How do I get around that if the fields that have the apostrophe are random?

Jason

Hi Jason

You can use the QUOTENAME function, like so:

SET @.SQL='INSERT INTO dbo.Newsletter(ID,[Center Name],[Address 1],City,State,Zip,Active,[Child Care Centers]) VALUES(' + CAST(@.NextClientID AS NVARCHAR(10)) + ',' + QUOTENAME(@.C_CenterName, '''') + ',' + QUOTENAME(@.Address, '''') + ',' + QUOTENAME(@.City, '''') + ',' + QUOTENAME(@.State, '''') + ',' + QUOTENAME(@.Zip, '''') + ',1,1);'

This example assumes that your variables are not already delimited with single quotes. I should have spotted this when you posted your previous question.

Again, here's a link that might help:

http://msdn2.microsoft.com/en-us/library/ms176114.aspx

Chris

|||

Just taking a step back for a moment, I'm not sure why you're using dynamic SQL at all in this code.

You could, in fact, should, just use:

INSERT INTO dbo.Newsletter(ID,[Center Name],[Address 1],City,State,Zip,Active,[Child Care Centers])

VALUES(@.NextClientID, @.C_CenterName, @.Address, @.City, @.State, @.Zip,1,1)

Chris

|||

THX. I think i got side tracked by my other problem but this worked great.

Jason

API Information

Can someone direct me to the APi information that would allow me to retrieve
the "Last Run date" of a report as well as the schedule information that
belongs to that report?>From Reporting Services BOL 2005:
Report.ExecutionTime
Gets the date and time that the report was generated.
Namespace: Microsoft.ReportingServices.ReportRendering
Assembly: Microsoft.ReportingServices.Processing (in
microsoft.reportingservices.processing.dll)
C#
public DateTime ExecutionTime { get; }
Regards,
Thiago Silva