long running queries quit - audit logout

I have a web based application that launches sql queries using ADO from
ASP/VBScript. Typically 2 - 10 queries are executed in a loop, and the
result sets are sent off to Crystal Reports at the end of the run with the
report being displayed in a browser window. In general this all works well
is has been very reliable.
Lately I've been having trouble with some of these queries on databases that
have gotten quite large (25 - 40G) but only a couple of the tables have
rowcounts in the tens of millions, most are relatively small. The behavior
is that the first query runs, sometimes for up to 90 minutes, seems to
complete, and then the pid is gone. Looking at the profile trace, I can see
that the query executed nearly 13 M reads with a duration of over 90
minutes. The line following the SQL:BatchCompleted event is Audit Logout,
and thats the last trace of the process. Under "normal" operation, the next
query would start and they would all run until the loop is complete. I've
seen this now with 2 completely different sets of queries, on 2 different
dbs, yet the symptoms are the same. It completes a very simple query that
returns a single datetime value. Then the first "real" query runs, anywhere
from 60 - 90 minutes or more, and then the process goes away.
It seems as if I've reached some threshold or internal timeout. I have the
connection time and query timeouts set to very large values (1000 minutes),
so it's not that.The client process is of course IIS. This is running SQL
Server 2000 SP3, NT 4.0, IIS 5.5.
I have had sets of queries that have taken as long as 3 hours to complete,
but each individual query probably never exceeded 45 minutes in duration.
Can anyone think of what is causing this to happen -- why does the pid go
away after the first query completes? Is there some other internal timeout
somewhere that I'm running into?
Any advice is appreciated,
-GaryYou might be experiencing lock_timeout. See if it helps:
http://msdn.microsoft.com/library/en-us/tsqlref/ts_set-set_1yr8.asp
"Gary" <gld@.hotmail.com> wrote in message
news:bisSc.243431$IQ4.52808@.attbi_s02...
> I have a web based application that launches sql queries using ADO from
> ASP/VBScript. Typically 2 - 10 queries are executed in a loop, and the
> result sets are sent off to Crystal Reports at the end of the run with the
> report being displayed in a browser window. In general this all works well
> is has been very reliable.
> Lately I've been having trouble with some of these queries on databases
that
> have gotten quite large (25 - 40G) but only a couple of the tables have
> rowcounts in the tens of millions, most are relatively small. The behavior
> is that the first query runs, sometimes for up to 90 minutes, seems to
> complete, and then the pid is gone. Looking at the profile trace, I can
see
> that the query executed nearly 13 M reads with a duration of over 90
> minutes. The line following the SQL:BatchCompleted event is Audit Logout,
> and thats the last trace of the process. Under "normal" operation, the
next
> query would start and they would all run until the loop is complete. I've
> seen this now with 2 completely different sets of queries, on 2 different
> dbs, yet the symptoms are the same. It completes a very simple query that
> returns a single datetime value. Then the first "real" query runs,
anywhere
> from 60 - 90 minutes or more, and then the process goes away.
> It seems as if I've reached some threshold or internal timeout. I have the
> connection time and query timeouts set to very large values (1000
minutes),
> so it's not that.The client process is of course IIS. This is running SQL
> Server 2000 SP3, NT 4.0, IIS 5.5.
> I have had sets of queries that have taken as long as 3 hours to complete,
> but each individual query probably never exceeded 45 minutes in duration.
> Can anyone think of what is causing this to happen -- why does the pid go
> away after the first query completes? Is there some other internal timeout
> somewhere that I'm running into?
> Any advice is appreciated,
> -Gary
>
>|||Thanks for the tip. I checked a server and it's returning -1 (infinite) for
this. I'm thinking that the problem is the client connection to the database
dropping sometime during the processing of a query, and when the statement
complete event occurs, SQL detects that the connection is gone and therefore
the audit logoff event.
"oj" <nospam_ojngo@.home.com> wrote in message
news:O7lkZQ$fEHA.3632@.TK2MSFTNGP09.phx.gbl...
> You might be experiencing lock_timeout. See if it helps:
> http://msdn.microsoft.com/library/en-us/tsqlref/ts_set-set_1yr8.asp
>

login with security on records based on data (not tables)

Hi,
Is it possible to define a login on an SQL SERVER that has only access to
certain records in a table based on the data in the records?
For example login LoginA can should only be able to
update/read/insert/delete records with the Field Company = 'A'. LoginB only
those for Company = 'B'.
Is there some way for doing this?
I should be able to do something like this with views (one view for each
login), and define in the views the records they can use. But I would really
need something directly to the table, or if not with only one view (one view
for all the login's).
Is there some way, soem trick or I don't knwo what the produce such a
behaviour?
Any help or hint would be really aprpeciated!
Thanks a lot in advance,
Pieter
See if this helps:
http://vyaskn.tripod.com/row_level_s..._databases.htm
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:ugcklHoiEHA.2356@.TK2MSFTNGP10.phx.gbl...
Hi,
Is it possible to define a login on an SQL SERVER that has only access to
certain records in a table based on the data in the records?
For example login LoginA can should only be able to
update/read/insert/delete records with the Field Company = 'A'. LoginB only
those for Company = 'B'.
Is there some way for doing this?
I should be able to do something like this with views (one view for each
login), and define in the views the records they can use. But I would really
need something directly to the table, or if not with only one view (one view
for all the login's).
Is there some way, soem trick or I don't knwo what the produce such a
behaviour?
Any help or hint would be really aprpeciated!
Thanks a lot in advance,
Pieter
|||Thanks man! You did a nice job there!! This wil be very helpfull!!!
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:esiJ5RoiEHA.212@.TK2MSFTNGP10.phx.gbl...
> See if this helps:
> http://vyaskn.tripod.com/row_level_s..._databases.htm
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:ugcklHoiEHA.2356@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Is it possible to define a login on an SQL SERVER that has only access to
> certain records in a table based on the data in the records?
> For example login LoginA can should only be able to
> update/read/insert/delete records with the Field Company = 'A'. LoginB
only
> those for Company = 'B'.
> Is there some way for doing this?
> I should be able to do something like this with views (one view for each
> login), and define in the views the records they can use. But I would
really
> need something directly to the table, or if not with only one view (one
view
> for all the login's).
> Is there some way, soem trick or I don't knwo what the produce such a
> behaviour?
> Any help or hint would be really aprpeciated!
> Thanks a lot in advance,
> Pieter
>
>

login with security on records based on data (not tables)

Hi,
Is it possible to define a login on an SQL SERVER that has only access to
certain records in a table based on the data in the records?
For example login LoginA can should only be able to
update/read/insert/delete records with the Field Company = 'A'. LoginB only
those for Company = 'B'.
Is there some way for doing this?
I should be able to do something like this with views (one view for each
login), and define in the views the records they can use. But I would really
need something directly to the table, or if not with only one view (one view
for all the login's).
Is there some way, soem trick or I don't knwo what the produce such a
behaviour?
Any help or hint would be really aprpeciated!
Thanks a lot in advance,
PieterSee if this helps:
es.htm" target="_blank">http://vyaskn.tripod.com/ row_level...as
es.htm
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:ugcklHoiEHA.2356@.TK2MSFTNGP10.phx.gbl...
Hi,
Is it possible to define a login on an SQL SERVER that has only access to
certain records in a table based on the data in the records?
For example login LoginA can should only be able to
update/read/insert/delete records with the Field Company = 'A'. LoginB only
those for Company = 'B'.
Is there some way for doing this?
I should be able to do something like this with views (one view for each
login), and define in the views the records they can use. But I would really
need something directly to the table, or if not with only one view (one view
for all the login's).
Is there some way, soem trick or I don't knwo what the produce such a
behaviour?
Any help or hint would be really aprpeciated!
Thanks a lot in advance,
Pieter|||Thanks man! You did a nice job there!! This wil be very helpfull!!!
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:esiJ5RoiEHA.212@.TK2MSFTNGP10.phx.gbl...
> See if this helps:
> es.htm" target="_blank">http://vyaskn.tripod.com/ row_level...as
es.htm
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:ugcklHoiEHA.2356@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Is it possible to define a login on an SQL SERVER that has only access to
> certain records in a table based on the data in the records?
> For example login LoginA can should only be able to
> update/read/insert/delete records with the Field Company = 'A'. LoginB
only
> those for Company = 'B'.
> Is there some way for doing this?
> I should be able to do something like this with views (one view for each
> login), and define in the views the records they can use. But I would
really
> need something directly to the table, or if not with only one view (one
view
> for all the login's).
> Is there some way, soem trick or I don't knwo what the produce such a
> behaviour?
> Any help or hint would be really aprpeciated!
> Thanks a lot in advance,
> Pieter
>
>

login with security on records based on data (not tables)

Hi,
Is it possible to define a login on an SQL SERVER that has only access to
certain records in a table based on the data in the records?
For example login LoginA can should only be able to
update/read/insert/delete records with the Field Company = 'A'. LoginB only
those for Company = 'B'.
Is there some way for doing this?
I should be able to do something like this with views (one view for each
login), and define in the views the records they can use. But I would really
need something directly to the table, or if not with only one view (one view
for all the login's).
Is there some way, soem trick or I don't knwo what the produce such a
behaviour?
Any help or hint would be really aprpeciated!
Thanks a lot in advance,
PieterSee if this helps:
http://vyaskn.tripod.com/row_level_security_in_sql_server_databases.htm
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:ugcklHoiEHA.2356@.TK2MSFTNGP10.phx.gbl...
Hi,
Is it possible to define a login on an SQL SERVER that has only access to
certain records in a table based on the data in the records?
For example login LoginA can should only be able to
update/read/insert/delete records with the Field Company = 'A'. LoginB only
those for Company = 'B'.
Is there some way for doing this?
I should be able to do something like this with views (one view for each
login), and define in the views the records they can use. But I would really
need something directly to the table, or if not with only one view (one view
for all the login's).
Is there some way, soem trick or I don't knwo what the produce such a
behaviour?
Any help or hint would be really aprpeciated!
Thanks a lot in advance,
Pieter|||Thanks man! You did a nice job there!! This wil be very helpfull!!!
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:esiJ5RoiEHA.212@.TK2MSFTNGP10.phx.gbl...
> See if this helps:
> http://vyaskn.tripod.com/row_level_security_in_sql_server_databases.htm
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:ugcklHoiEHA.2356@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Is it possible to define a login on an SQL SERVER that has only access to
> certain records in a table based on the data in the records?
> For example login LoginA can should only be able to
> update/read/insert/delete records with the Field Company = 'A'. LoginB
only
> those for Company = 'B'.
> Is there some way for doing this?
> I should be able to do something like this with views (one view for each
> login), and define in the views the records they can use. But I would
really
> need something directly to the table, or if not with only one view (one
view
> for all the login's).
> Is there some way, soem trick or I don't knwo what the produce such a
> behaviour?
> Any help or hint would be really aprpeciated!
> Thanks a lot in advance,
> Pieter
>
>

Login Times

Helo,
Is there any way I could grant or deny a user login based on the time of
day?
I'm running mad with this issue, since not everybody is a full time
employee, and for security reasons I would want to block their logins when
they are supposed to be out of the office.
Thanks in advance.Juan wrote:
> Helo,
> Is there any way I could grant or deny a user login based on the time
> of day?
There is no possibility in SQL Server, but if you use integrated security
you can use Active Directory Users and Computer to limit the logon hour of
each account

> Thanks in advance.
Bye
Luca Bianchi
Microsoft MVP - SQL Server
http://mvp.support.microsoft.com
http://italy.mvps.org

login page problem with reportviewer for winforms

Hi,
My reportserver has forms authentication (for web based users).
When I now try to open a report in the winform report viewer I get an error
on the viewer about the login page. How can I pass the login credentials via
code to the reportserver?:
System.Net.WebException: The request failed with the error message:
--
<html><head><title>Object moved</title></head><body>
<h2>Object moved to <a
href='/ReportServer/pages/UIlogon.aspx?ReturnUrl=%2fReportserver%2fReportExecution2005.asmx'>here</a>.</h2>
</body></html>Hello bartVDA,
Thank you for posting.
Regarding on the windows form's reportViewer control, it is
programmatically requesting the report content from reportServer
application rather than interactively like the webbrowser control. Also,
since the form based authentication require the client-side always use
webbrowser to interactively request the report, this will break the winform
reportviewer(also webform reportviewer). BTW, for wniform application, you
can also consider directly lauch webbrowser to display the report through
the actual URL or use a webbrowser control in it though they're not as good
as the well encapsulated reportviewer.
Regards,
Steven Cheng
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may
learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.