Is there a way of automating SQL Server Reporting Services reports in my win
app?
I want to be able to pick up events when the user right-clicks on the report
etc.
I really want to provide a context menu with options relevant to what
they've selected in the report.
As I understood it, the generated report is basically html and theres no
easy way of doing it...
I basically want the report to be more dynamic than just being generated and
then printed.
If not is in coming in a future version of SQL Server?Are you using the ReportViewer control?
http://gotreportviewer.com/
If on the server you may be able to add via .css?
http://weblogs.asp.net/jgalloway/archive/2006/09/01/SQL-Reporting-Services-_2D00_-CSS-fix-for-Firefox.aspx
Steve MunLeeuw
"James" <James@.discussions.microsoft.com> wrote in message
news:E1244EC6-5372-4AB2-8BC1-9D41827F2D8E@.microsoft.com...
> Is there a way of automating SQL Server Reporting Services reports in my
> win
> app?
> I want to be able to pick up events when the user right-clicks on the
> report
> etc.
> I really want to provide a context menu with options relevant to what
> they've selected in the report.
> As I understood it, the generated report is basically html and theres no
> easy way of doing it...
> I basically want the report to be more dynamic than just being generated
> and
> then printed.
> If not is in coming in a future version of SQL Server?|||yes, I'm currently looking into doing it via hyperlinks as in the following
article:
http://www.devx.com/dotnet/Article/30424/0/page/6
"Steve MunLeeuw" wrote:
> Are you using the ReportViewer control?
> http://gotreportviewer.com/
> If on the server you may be able to add via .css?
> http://weblogs.asp.net/jgalloway/archive/2006/09/01/SQL-Reporting-Services-_2D00_-CSS-fix-for-Firefox.aspx
> Steve MunLeeuw
> "James" <James@.discussions.microsoft.com> wrote in message
> news:E1244EC6-5372-4AB2-8BC1-9D41827F2D8E@.microsoft.com...
> > Is there a way of automating SQL Server Reporting Services reports in my
> > win
> > app?
> > I want to be able to pick up events when the user right-clicks on the
> > report
> > etc.
> > I really want to provide a context menu with options relevant to what
> > they've selected in the report.
> > As I understood it, the generated report is basically html and theres no
> > easy way of doing it...
> > I basically want the report to be more dynamic than just being generated
> > and
> > then printed.
> >
> > If not is in coming in a future version of SQL Server?
>
>sql
Showing posts with label user. Show all posts
Showing posts with label user. Show all posts
Thursday, March 29, 2012
Sunday, March 25, 2012
Automatically Regenerate on Depending on parameter input
Hello,
Is there a way to get the reports to automatically regenerate when a user
changes a drop down or selects a radio button?
Thanks
DylanOn Feb 20, 11:45 am, Dylan <D...@.discussions.microsoft.com> wrote:
> Hello,
> Is there a way to get the reports to automatically regenerate when a user
> changes a drop down or selects a radio button?
> Thanks
> Dylan
As far as I know, there is not a way to do that; unless, you are
embedding the report in an application, whereas, you can call a
refresh of the report on a given event.
Regards,
Enrique Martinez
Sr. ASP.NET/SQL Server Developer|||By giving "default value" the report runs automatically (rather pretend to
run, infact it substitutes the values) Otherwise you have to click on view
report to see the report. other option is to write custom code..
Amarnath
"Dylan" wrote:
> Hello,
> Is there a way to get the reports to automatically regenerate when a user
> changes a drop down or selects a radio button?
> Thanks
> Dylansql
Is there a way to get the reports to automatically regenerate when a user
changes a drop down or selects a radio button?
Thanks
DylanOn Feb 20, 11:45 am, Dylan <D...@.discussions.microsoft.com> wrote:
> Hello,
> Is there a way to get the reports to automatically regenerate when a user
> changes a drop down or selects a radio button?
> Thanks
> Dylan
As far as I know, there is not a way to do that; unless, you are
embedding the report in an application, whereas, you can call a
refresh of the report on a given event.
Regards,
Enrique Martinez
Sr. ASP.NET/SQL Server Developer|||By giving "default value" the report runs automatically (rather pretend to
run, infact it substitutes the values) Otherwise you have to click on view
report to see the report. other option is to write custom code..
Amarnath
"Dylan" wrote:
> Hello,
> Is there a way to get the reports to automatically regenerate when a user
> changes a drop down or selects a radio button?
> Thanks
> Dylansql
Thursday, March 22, 2012
Automatically backup....?
//[Microsoft][ODBC SQL Server Driver][SQL Server]Repair
statement not processed. Database needs to be in single
user mode.
** Execution Time: 0 hrs, 0 mins, 1 secs **
End of maintenance plan 'Cybermurid maintenance' on
7/20/2004 5:55:00 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)//
How do I set automatically backup in SQL server?Niky wrote:
> //[Microsoft][ODBC SQL Server Driver][SQL Server]Repair
> statement not processed. Database needs to be in single
> user mode.
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> End of maintenance plan 'Cybermurid maintenance' on
> 7/20/2004 5:55:00 AM
> SQLMAINT.EXE Process Exit Code: 1 (Failed)//
> How do I set automatically backup in SQL server?
Hi Niky,
you can create a new job (under SQL Agent, in Enterprise Manager):
- the activity you must add is a T-SQL script, with the instruction
"BACKUP DATABASE MyDb TO [DISK|TAPE]...".
- schedule it
David|||Hello
I believe that you are running into the bug mentioned in the following
article :
290622 BUG: Database Maintenance Plan on System Databases Fails on Integrity
http://support.microsoft.com/?id=290622
The workaround is to disable the "Attempt to repair minor problems" option
in the Maintenance plan properties. This should stop generating the message
that you have posted.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
statement not processed. Database needs to be in single
user mode.
** Execution Time: 0 hrs, 0 mins, 1 secs **
End of maintenance plan 'Cybermurid maintenance' on
7/20/2004 5:55:00 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)//
How do I set automatically backup in SQL server?Niky wrote:
> //[Microsoft][ODBC SQL Server Driver][SQL Server]Repair
> statement not processed. Database needs to be in single
> user mode.
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> End of maintenance plan 'Cybermurid maintenance' on
> 7/20/2004 5:55:00 AM
> SQLMAINT.EXE Process Exit Code: 1 (Failed)//
> How do I set automatically backup in SQL server?
Hi Niky,
you can create a new job (under SQL Agent, in Enterprise Manager):
- the activity you must add is a T-SQL script, with the instruction
"BACKUP DATABASE MyDb TO [DISK|TAPE]...".
- schedule it
David|||Hello
I believe that you are running into the bug mentioned in the following
article :
290622 BUG: Database Maintenance Plan on System Databases Fails on Integrity
http://support.microsoft.com/?id=290622
The workaround is to disable the "Attempt to repair minor problems" option
in the Maintenance plan properties. This should stop generating the message
that you have posted.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
Automatically add permissions on items for users?
Can someone give me a couple of pointers on how to interact with the web
service (?) to automatically add policies for a user to view folders and
reports rather than adding them manually through Report Manager?
thanksThis is a multi-part message in MIME format.
--=_NextPart_000_00C1_01C4BA63.9F970620
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit
Rather than authorizing each user in Reporting Services it is
recommended that you create a Windows Group (e.g. Reporting Users),
associate the Group with a Reporting Services Role (e.g. Browser), and
then when you create a new Windows User you make the user a member of
the [Reporting Users] Group.
Garry
--=_NextPart_000_00C1_01C4BA63.9F970620
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
&
Re: Automatically add permissions on items for users?
Rather than authorizing each user in Reporting =Services it is recommended that you create a Windows Group (e.g. =Reporting Users), associate the Group with a Reporting Services Role =(e.g. Browser), and then when you create a new Windows User you make the =user a member of the [Reporting Users] Group.
Garry
--=_NextPart_000_00C1_01C4BA63.9F970620--|||the reason that I need to add each user is that I am using forms
authentication.
how can I call the web service to add policies for each user?
"Garry Lenz" wrote:
> Rather than authorizing each user in Reporting Services it is
> recommended that you create a Windows Group (e.g. Reporting Users),
> associate the Group with a Reporting Services Role (e.g. Browser), and
> then when you create a new Windows User you make the user a member of
> the [Reporting Users] Group.
> Garry
>
service (?) to automatically add policies for a user to view folders and
reports rather than adding them manually through Report Manager?
thanksThis is a multi-part message in MIME format.
--=_NextPart_000_00C1_01C4BA63.9F970620
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit
Rather than authorizing each user in Reporting Services it is
recommended that you create a Windows Group (e.g. Reporting Users),
associate the Group with a Reporting Services Role (e.g. Browser), and
then when you create a new Windows User you make the user a member of
the [Reporting Users] Group.
Garry
--=_NextPart_000_00C1_01C4BA63.9F970620
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
&
Re: Automatically add permissions on items for users?
Rather than authorizing each user in Reporting =Services it is recommended that you create a Windows Group (e.g. =Reporting Users), associate the Group with a Reporting Services Role =(e.g. Browser), and then when you create a new Windows User you make the =user a member of the [Reporting Users] Group.
Garry
--=_NextPart_000_00C1_01C4BA63.9F970620--|||the reason that I need to add each user is that I am using forms
authentication.
how can I call the web service to add policies for each user?
"Garry Lenz" wrote:
> Rather than authorizing each user in Reporting Services it is
> recommended that you create a Windows Group (e.g. Reporting Users),
> associate the Group with a Reporting Services Role (e.g. Browser), and
> then when you create a new Windows User you make the user a member of
> the [Reporting Users] Group.
> Garry
>
Tuesday, March 20, 2012
Automatic SQL db update at set time?
I would like to limit the number of pages a user can view on my website each day. The users logs in and I can count the number of pages viewd in a field but i want to know how i can set the page count field to reset to 0 at the end of the day (ie midnight). Is it possible to do this? and if so how? Thanks.You can set up a job to run a stored proc at that time every day using SQL Server Agent (find it in SQL Server Enterprise Manager)
A better way might be to log pageviews with a timestamp, and then you can allow X pages within any 24 hour period - simply count the pageviews in the log newer than getdate() - 1 and check it against the limit.
Does that help?|||The server will be a shared sql server and i don't have access to creating new jobs, so I think your second suggestion would be best but not sure how to implement it. Do you have an example or a link to where I can find an example? Thanks|||Normally that should not be a problem - I use a shared database server (one of those cheap .net hosters) and I can create jobs just fine.
Think about my other solution if you really can't create jobs - it's better (I believe) and it does not require a scheduled job.
Check BOL for examples of creating jobs.
Monday, March 19, 2012
Automatic Insert of data into a related table
I have two tables. When my user completes an insert of data in table (1), I would like the second "related" table (2) to be automatically populated with defaults. Is this possible?
My logical approach to this is:
1. Build a handler for the OnInsert event of the first table
2. In the handler, call the Insert Command on the SQLDataSource for the second table with the defaults specified in the DataSource.
What I'm not sure how to do is Step 2 or whats the best way. How do I call the Insertcommand programmatically for a DataSource? Or, is there a better way such as some kind of traditional hardwired SQL insert statement like in classical ASP? Or is there a way to programmatically call a stored procedure and if so is the 3rd approach the best way?
How exactly would someone do this best? It seems this would be a rather common thing someone might need to do.
Sub SqlDataSource1_Inserted(sender as object, e as system.eventargs) handles sqldatasource1.Inserted
dim conn as new sqlconnection("{Your connect string or pull from web.config"})
dim cmd as new sqlcommand("INSERT INTO Table2(col1,col2,col3) VALUES (@.col1,@.col2,@.col3",conn)
cmd.parameters.add("@.col1",sqldbtype.varchar).value={something}
cmd.parameters.add("@.col2",sqldbtype.varchar).value={something else}
cmd.parameters.add("@.col3",sqldbtype.varchar).value={something 3}
conn.open
cmd.executenonquery
conn.close
end sub
The above is easy, but it's not wrapped in a transaction. So if the 2nd insert fails, you'll have an inconsistant data model (No coresponding record in table2).
You can also build a trigger on table1 to do the insert.
You can also build a stored procedure, and use it to do both inserts via the sqldatasource.
|||
Motley:
You can also build a stored procedure, and use it to do both inserts via the sqldatasource.
I actually already have the first table insert occurring with a stored procedure so that I can get the identity. I guess I could use that stored procedure to also enter the default data for the second table but then I would need to know the Identity from the first table to plug it into the second table. Not really that good with stored procedures. Would something like this work?
CREATE PROCEDURE [InsertTable]
@.StoreID Int,
@.Weight real,
@.Length real,
@.Name nvarchar(25),
@.myID int OUTPUT
AS
INSERT INTO [FirstTable] ([StoreID], [Weight], [Length]) VALUES (@.StoreID, @.Weight, @.Length )
SELECT @.myID = @.@.IDENTITY
INSERT INTO [SecondTable] ([Table1ID], [Name]) VALUES (@.myID, @.Name)
|||Yes, that should work fine. As a minor change, don't use @.@.IDENTITY, use SCOPE_IDENTITY() instead.
SET @.myID=SCOPE_IDENTITY()
There is some minor differences between the two, and what you really want is SCOPE_IDENTITY().
|||I tried this and got errors for the other values so I gave up and went with the other approach which seems to be working.
The stored procedure is more elegant but it was just complaining there was no value for the very first column, and I had the value specified/harcoded as '0'. I was very
Thursday, March 8, 2012
Automated restore of a user database
I am trying to restore a user database in a scheduled Job I created that executes the following command "restore database sqleligibilitydatabase from disk= 'd:\dbfrom01\sqleligibilitydatabase_db_%.bak'". I get an error 3101 "unable to get exclusive use".
Absolutely no one is on this server when I do this
From which database are you executing the restore command? Make sure it is the master database. you can run an
sp_who just before and get the output from the jobstep to a file and examine that file to see who is using the
database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cbriscoejr" <cbriscoejr@.discussions.microsoft.com> wrote in message
news:C9D0496B-0E92-48B2-A3EE-860048654DE4@.microsoft.com...
> I am trying to restore a user database in a scheduled Job I created that executes the following command
"restore database sqleligibilitydatabase from disk= 'd:\dbfrom01\sqleligibilitydatabase_db_%.bak'". I get an
error 3101 "unable to get exclusive use". Absolutely no one is on this server when I do this
|||Hi,
Turn the database to single user before restore and make it multiuser after
restore:-
Change the script to do below :-
Alter database <dbname> set single_user with rollback immediate
go
restore database dbname from disk=...........
go
Alter database <dbname> set multi_user
Thanks
Hari
MCDBA
"cbriscoejr" <cbriscoejr@.discussions.microsoft.com> wrote in message
news:C9D0496B-0E92-48B2-A3EE-860048654DE4@.microsoft.com...
> I am trying to restore a user database in a scheduled Job I created that
executes the following command "restore database sqleligibilitydatabase from
disk= 'd:\dbfrom01\sqleligibilitydatabase_db_%.bak'". I get an error 3101
"unable to get exclusive use". Absolutely no one is on this server when I
do this
|||ALTER DATABASE sqleligibilitydatabase SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE sqleligibilitydatabase SET ONLINE
RESTORE DATABASE......
That will clear all users out of the database. Also, make sure your job
step does not execute in the context of the target database.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"cbriscoejr" <cbriscoejr@.discussions.microsoft.com> wrote in message
news:C9D0496B-0E92-48B2-A3EE-860048654DE4@.microsoft.com...
> I am trying to restore a user database in a scheduled Job I created that
executes the following command "restore database sqleligibilitydatabase from
disk= 'd:\dbfrom01\sqleligibilitydatabase_db_%.bak'". I get an error 3101
"unable to get exclusive use". Absolutely no one is on this server when I
do this
Absolutely no one is on this server when I do this
From which database are you executing the restore command? Make sure it is the master database. you can run an
sp_who just before and get the output from the jobstep to a file and examine that file to see who is using the
database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cbriscoejr" <cbriscoejr@.discussions.microsoft.com> wrote in message
news:C9D0496B-0E92-48B2-A3EE-860048654DE4@.microsoft.com...
> I am trying to restore a user database in a scheduled Job I created that executes the following command
"restore database sqleligibilitydatabase from disk= 'd:\dbfrom01\sqleligibilitydatabase_db_%.bak'". I get an
error 3101 "unable to get exclusive use". Absolutely no one is on this server when I do this
|||Hi,
Turn the database to single user before restore and make it multiuser after
restore:-
Change the script to do below :-
Alter database <dbname> set single_user with rollback immediate
go
restore database dbname from disk=...........
go
Alter database <dbname> set multi_user
Thanks
Hari
MCDBA
"cbriscoejr" <cbriscoejr@.discussions.microsoft.com> wrote in message
news:C9D0496B-0E92-48B2-A3EE-860048654DE4@.microsoft.com...
> I am trying to restore a user database in a scheduled Job I created that
executes the following command "restore database sqleligibilitydatabase from
disk= 'd:\dbfrom01\sqleligibilitydatabase_db_%.bak'". I get an error 3101
"unable to get exclusive use". Absolutely no one is on this server when I
do this
|||ALTER DATABASE sqleligibilitydatabase SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE sqleligibilitydatabase SET ONLINE
RESTORE DATABASE......
That will clear all users out of the database. Also, make sure your job
step does not execute in the context of the target database.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"cbriscoejr" <cbriscoejr@.discussions.microsoft.com> wrote in message
news:C9D0496B-0E92-48B2-A3EE-860048654DE4@.microsoft.com...
> I am trying to restore a user database in a scheduled Job I created that
executes the following command "restore database sqleligibilitydatabase from
disk= 'd:\dbfrom01\sqleligibilitydatabase_db_%.bak'". I get an error 3101
"unable to get exclusive use". Absolutely no one is on this server when I
do this
Automated restore of a user database
I am trying to restore a user database in a scheduled Job I created that exe
cutes the following command "restore database sqleligibilitydatabase from di
sk= 'd:\dbfrom01\sqleligibilitydatabase_db_%
.bak'". I get an error 3101 "un
able to get exclusive use".
Absolutely no one is on this server when I do thisFrom which database are you executing the restore command? Make sure it is t
he master database. you can run an
sp_who just before and get the output from the jobstep to a file and examine
that file to see who is using the
database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cbriscoejr" <cbriscoejr@.discussions.microsoft.com> wrote in message
news:C9D0496B-0E92-48B2-A3EE-860048654DE4@.microsoft.com...
> I am trying to restore a user database in a scheduled Job I created that executes
the following command
"restore database sqleligibilitydatabase from disk= 'd:\dbfrom01\sqleligibil
itydatabase_db_%.bak'". I get an
error 3101 "unable to get exclusive use". Absolutely no one is on this serv
er when I do this|||Hi,
Turn the database to single user before restore and make it multiuser after
restore:-
Change the script to do below :-
Alter database <dbname> set single_user with rollback immediate
go
restore database dbname from disk=...........
go
Alter database <dbname> set multi_user
Thanks
Hari
MCDBA
"cbriscoejr" <cbriscoejr@.discussions.microsoft.com> wrote in message
news:C9D0496B-0E92-48B2-A3EE-860048654DE4@.microsoft.com...
> I am trying to restore a user database in a scheduled Job I created that
executes the following command "restore database sqleligibilitydatabase from
disk= 'd:\dbfrom01\sqleligibilitydatabase_db_%
.bak'". I get an error 3101
"unable to get exclusive use". Absolutely no one is on this server when I
do this|||ALTER DATABASE sqleligibilitydatabase SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE sqleligibilitydatabase SET ONLINE
RESTORE DATABASE......
That will clear all users out of the database. Also, make sure your job
step does not execute in the context of the target database.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"cbriscoejr" <cbriscoejr@.discussions.microsoft.com> wrote in message
news:C9D0496B-0E92-48B2-A3EE-860048654DE4@.microsoft.com...
> I am trying to restore a user database in a scheduled Job I created that
executes the following command "restore database sqleligibilitydatabase from
disk= 'd:\dbfrom01\sqleligibilitydatabase_db_%
.bak'". I get an error 3101
"unable to get exclusive use". Absolutely no one is on this server when I
do this
cutes the following command "restore database sqleligibilitydatabase from di
sk= 'd:\dbfrom01\sqleligibilitydatabase_db_%
.bak'". I get an error 3101 "un
able to get exclusive use".
Absolutely no one is on this server when I do thisFrom which database are you executing the restore command? Make sure it is t
he master database. you can run an
sp_who just before and get the output from the jobstep to a file and examine
that file to see who is using the
database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cbriscoejr" <cbriscoejr@.discussions.microsoft.com> wrote in message
news:C9D0496B-0E92-48B2-A3EE-860048654DE4@.microsoft.com...
> I am trying to restore a user database in a scheduled Job I created that executes
the following command
"restore database sqleligibilitydatabase from disk= 'd:\dbfrom01\sqleligibil
itydatabase_db_%.bak'". I get an
error 3101 "unable to get exclusive use". Absolutely no one is on this serv
er when I do this|||Hi,
Turn the database to single user before restore and make it multiuser after
restore:-
Change the script to do below :-
Alter database <dbname> set single_user with rollback immediate
go
restore database dbname from disk=...........
go
Alter database <dbname> set multi_user
Thanks
Hari
MCDBA
"cbriscoejr" <cbriscoejr@.discussions.microsoft.com> wrote in message
news:C9D0496B-0E92-48B2-A3EE-860048654DE4@.microsoft.com...
> I am trying to restore a user database in a scheduled Job I created that
executes the following command "restore database sqleligibilitydatabase from
disk= 'd:\dbfrom01\sqleligibilitydatabase_db_%
.bak'". I get an error 3101
"unable to get exclusive use". Absolutely no one is on this server when I
do this|||ALTER DATABASE sqleligibilitydatabase SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE sqleligibilitydatabase SET ONLINE
RESTORE DATABASE......
That will clear all users out of the database. Also, make sure your job
step does not execute in the context of the target database.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"cbriscoejr" <cbriscoejr@.discussions.microsoft.com> wrote in message
news:C9D0496B-0E92-48B2-A3EE-860048654DE4@.microsoft.com...
> I am trying to restore a user database in a scheduled Job I created that
executes the following command "restore database sqleligibilitydatabase from
disk= 'd:\dbfrom01\sqleligibilitydatabase_db_%
.bak'". I get an error 3101
"unable to get exclusive use". Absolutely no one is on this server when I
do this
Automated birthday email
I have a SQL server 2005 database. I want to send a user an email when it's his birthday. His birthdate is stored in the "birthdate" column...
How can I automate this?
How can I automate this?
You can use xp_sendmail to send the birthday emails. Please follow these steps to configure:
1. Click 'Start'->'All Programs'->'Microsoft SQL Server 2005'->'Configuration Tools'->'SQL Server Surface Area Configuration'
2. Click 'Surface Configuration for Features'
3. Select the SQL2005 instance (MSSQLSERVER in my testing); explore 'Database Engine'->go to 'OLE Automation'-> select the 'Enable OLE Automation' checkbox; enable SQL Mail in the same way; Click 'OK' to save changes (seeServerConfig.jpg)
4. Click 'Start'->'All Programs'->'Microsoft SQL Server 2005'->'Configuration Tools'->'SQL Server Configuration Manager'
5. Go to 'SQL Server 2005 Services, double click on 'SQL Server (InstanceName)', in the 'Log On' panel, choose 'This account' to use a domain account to logon (fareast\t-leijie in my testing); Click 'OK' to save change (see ConfigLogonAccount.jpg)
6. Restart SQL2005 instance service.
7. Install a default mail client if you do not have yet (OutLook is good), make a mail profile ('iori' in my testing) using the domain account to send emails, make sure emails can be sent using successful the account.
8. Click 'Start'->'All Programs'->'Microsoft SQL Server 2005'->'SQL Server Management Studio'
9. Choose your instance, explore 'Management'->'Legacy'->'SQL Mail', double click to set Properties, choose the profile used for SQLMail; Click 'OK' to save change.
And you also need a continous running script to detect the "birthdate" column and send mail, such as:
DECLARE @.email varchar(100)
WHILE(1=1)
BEGIN
If exists(select email from tbl_Birthday where DATEDIFF(dd,birthdate,getdate())=0
BEGIN
select @.email=email from tbl_Birthday where DATEDIFF(dd,birthdate,getdate())=0
EXEC master.dbo.xp_sendmail
@.recipients = @.email,
@.subject = N'Happy Birthday!',
@.message = N'Happy Birthday my dear firend!';
END
END
You made such a detailed list and still I don't completely understand it...;)
A few more questions:
1. I want the script to run once every day at 05:00 AM (to keep db load to a minimum)
2. I want to use a format in HTML for my message, this must contain something like: dear <name>, we like to congratlate you with your birthday. Also this entire email must be made up nicely with tables and pictures (which can be just static). Should I just put my entire email in 1 string and replace the <name> part with the username or can I import a .html file and THEN replace the <name> tag with the username? I like the second solution better...but do you know how to achieve this?
Wow, long questions...:p...hope you can help me out!
Thanks!|||It's my pleasure to discuss with you:)
1. You can schedule a job at 05:00 AM every day to accompish this, just open Enterprise Manager, explorerMySQLInstance->'Management'->'SQL Server Agent'->right click 'Jobs'->'New Job', then go to 'Steps' pannel to new a step. Choose 'T-SQL' as step type, and copy your T-SQL script used for sending email to the 'Command' field (as you'll schedule the script to run once a day, let's remove the 'WHILE(1=1)' loop). Then go to 'Schedules' pannel to schedule your job step. You can press F1 for detailed help. Sorry I wrote a wrong script in my previous post, for the case that there may be more than 2 persons have same birhday:
declare @.email varchar(100), @.name varchar(100)
select email,name into #tbl
from tbl_Birthday
where month(birthdate)=month(getdate())
and day(birthdate)=day(getdate())
select @.eamil=email,@.name='Happy Birthday'+name+'!' from #tbl
WHILE (@.email is not null)
BEGIN
EXEC master.dbo.xp_sendmail
@.recipients = @.email,
@.subject = N'Happy Birthday!',
@.message = @.name,
@.attachments='c:\happyBirth.html'--you have use attachment if you want HTML feature
DELETE FROM #tbl whereemail=@.email
select @.eamil=email,@.name='Happy Birthday'+name+'!' from #tbl
END
DROP TABLE #tbl
2. See the script above. You can customize the email message by adding the name, but need attchments to add HTML feature.
Friday, February 24, 2012
Autogrow of log
I get this info message in my NT log:
Autogrow of file 'w2publish_Log' in database 'w2publish' was cancelled
by user or timed out after 546 milliseconds. Use ALTER DATABASE to
set a smaller FILEGROWTH value for this file or to explicitly set a
new file size.
This database is set to autogrow by 10%.
Searching the net, a few have suggested increasing the 10% to a larger
value. But it seems that the error is SQL Server failing to grow the
file in a timely manner. So moving from 10% to larger number will
only make matters worse. If I set it to a fixed size, what if that is
not enough?
What is the correct approach?
You don't want to set it larger you want to change it to a fixed size that
you know will grow in well under the timeout period. Once the files get
beyond 100MB or so you should change it to a fixed size of growth.
Andrew J. Kelly SQL MVP
"pbx" <pbeisel@.gmail.com> wrote in message
news:1183851948.581194.56800@.i38g2000prf.googlegro ups.com...
> I get this info message in my NT log:
> Autogrow of file 'w2publish_Log' in database 'w2publish' was cancelled
> by user or timed out after 546 milliseconds. Use ALTER DATABASE to
> set a smaller FILEGROWTH value for this file or to explicitly set a
> new file size.
> This database is set to autogrow by 10%.
> Searching the net, a few have suggested increasing the 10% to a larger
> value. But it seems that the error is SQL Server failing to grow the
> file in a timely manner. So moving from 10% to larger number will
> only make matters worse. If I set it to a fixed size, what if that is
> not enough?
> What is the correct approach?
>
|||Monitor how your database files grow. This will give you an idea on what
value to specify for autogrow increments. This is critical especially when
the files are very big (imagine 10% autogrow increments for 100GB database
files)
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%237Qq9gQwHHA.4640@.TK2MSFTNGP03.phx.gbl...
> You don't want to set it larger you want to change it to a fixed size that
> you know will grow in well under the timeout period. Once the files get
> beyond 100MB or so you should change it to a fixed size of growth.
> --
> Andrew J. Kelly SQL MVP
> "pbx" <pbeisel@.gmail.com> wrote in message
> news:1183851948.581194.56800@.i38g2000prf.googlegro ups.com...
>
Autogrow of file 'w2publish_Log' in database 'w2publish' was cancelled
by user or timed out after 546 milliseconds. Use ALTER DATABASE to
set a smaller FILEGROWTH value for this file or to explicitly set a
new file size.
This database is set to autogrow by 10%.
Searching the net, a few have suggested increasing the 10% to a larger
value. But it seems that the error is SQL Server failing to grow the
file in a timely manner. So moving from 10% to larger number will
only make matters worse. If I set it to a fixed size, what if that is
not enough?
What is the correct approach?
You don't want to set it larger you want to change it to a fixed size that
you know will grow in well under the timeout period. Once the files get
beyond 100MB or so you should change it to a fixed size of growth.
Andrew J. Kelly SQL MVP
"pbx" <pbeisel@.gmail.com> wrote in message
news:1183851948.581194.56800@.i38g2000prf.googlegro ups.com...
> I get this info message in my NT log:
> Autogrow of file 'w2publish_Log' in database 'w2publish' was cancelled
> by user or timed out after 546 milliseconds. Use ALTER DATABASE to
> set a smaller FILEGROWTH value for this file or to explicitly set a
> new file size.
> This database is set to autogrow by 10%.
> Searching the net, a few have suggested increasing the 10% to a larger
> value. But it seems that the error is SQL Server failing to grow the
> file in a timely manner. So moving from 10% to larger number will
> only make matters worse. If I set it to a fixed size, what if that is
> not enough?
> What is the correct approach?
>
|||Monitor how your database files grow. This will give you an idea on what
value to specify for autogrow increments. This is critical especially when
the files are very big (imagine 10% autogrow increments for 100GB database
files)
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%237Qq9gQwHHA.4640@.TK2MSFTNGP03.phx.gbl...
> You don't want to set it larger you want to change it to a fixed size that
> you know will grow in well under the timeout period. Once the files get
> beyond 100MB or so you should change it to a fixed size of growth.
> --
> Andrew J. Kelly SQL MVP
> "pbx" <pbeisel@.gmail.com> wrote in message
> news:1183851948.581194.56800@.i38g2000prf.googlegro ups.com...
>
Autogrow of log
I get this info message in my NT log:
Autogrow of file 'w2publish_Log' in database 'w2publish' was cancelled
by user or timed out after 546 milliseconds. Use ALTER DATABASE to
set a smaller FILEGROWTH value for this file or to explicitly set a
new file size.
This database is set to autogrow by 10%.
Searching the net, a few have suggested increasing the 10% to a larger
value. But it seems that the error is SQL Server failing to grow the
file in a timely manner. So moving from 10% to larger number will
only make matters worse. If I set it to a fixed size, what if that is
not enough?
What is the correct approach?You don't want to set it larger you want to change it to a fixed size that
you know will grow in well under the timeout period. Once the files get
beyond 100MB or so you should change it to a fixed size of growth.
Andrew J. Kelly SQL MVP
"pbx" <pbeisel@.gmail.com> wrote in message
news:1183851948.581194.56800@.i38g2000prf.googlegroups.com...
> I get this info message in my NT log:
> Autogrow of file 'w2publish_Log' in database 'w2publish' was cancelled
> by user or timed out after 546 milliseconds. Use ALTER DATABASE to
> set a smaller FILEGROWTH value for this file or to explicitly set a
> new file size.
> This database is set to autogrow by 10%.
> Searching the net, a few have suggested increasing the 10% to a larger
> value. But it seems that the error is SQL Server failing to grow the
> file in a timely manner. So moving from 10% to larger number will
> only make matters worse. If I set it to a fixed size, what if that is
> not enough?
> What is the correct approach?
>|||Monitor how your database files grow. This will give you an idea on what
value to specify for autogrow increments. This is critical especially when
the files are very big (imagine 10% autogrow increments for 100GB database
files)
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%237Qq9gQwHHA.4640@.TK2MSFTNGP03.phx.gbl...
> You don't want to set it larger you want to change it to a fixed size that
> you know will grow in well under the timeout period. Once the files get
> beyond 100MB or so you should change it to a fixed size of growth.
> --
> Andrew J. Kelly SQL MVP
> "pbx" <pbeisel@.gmail.com> wrote in message
> news:1183851948.581194.56800@.i38g2000prf.googlegroups.com...
>
Autogrow of file 'w2publish_Log' in database 'w2publish' was cancelled
by user or timed out after 546 milliseconds. Use ALTER DATABASE to
set a smaller FILEGROWTH value for this file or to explicitly set a
new file size.
This database is set to autogrow by 10%.
Searching the net, a few have suggested increasing the 10% to a larger
value. But it seems that the error is SQL Server failing to grow the
file in a timely manner. So moving from 10% to larger number will
only make matters worse. If I set it to a fixed size, what if that is
not enough?
What is the correct approach?You don't want to set it larger you want to change it to a fixed size that
you know will grow in well under the timeout period. Once the files get
beyond 100MB or so you should change it to a fixed size of growth.
Andrew J. Kelly SQL MVP
"pbx" <pbeisel@.gmail.com> wrote in message
news:1183851948.581194.56800@.i38g2000prf.googlegroups.com...
> I get this info message in my NT log:
> Autogrow of file 'w2publish_Log' in database 'w2publish' was cancelled
> by user or timed out after 546 milliseconds. Use ALTER DATABASE to
> set a smaller FILEGROWTH value for this file or to explicitly set a
> new file size.
> This database is set to autogrow by 10%.
> Searching the net, a few have suggested increasing the 10% to a larger
> value. But it seems that the error is SQL Server failing to grow the
> file in a timely manner. So moving from 10% to larger number will
> only make matters worse. If I set it to a fixed size, what if that is
> not enough?
> What is the correct approach?
>|||Monitor how your database files grow. This will give you an idea on what
value to specify for autogrow increments. This is critical especially when
the files are very big (imagine 10% autogrow increments for 100GB database
files)
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%237Qq9gQwHHA.4640@.TK2MSFTNGP03.phx.gbl...
> You don't want to set it larger you want to change it to a fixed size that
> you know will grow in well under the timeout period. Once the files get
> beyond 100MB or so you should change it to a fixed size of growth.
> --
> Andrew J. Kelly SQL MVP
> "pbx" <pbeisel@.gmail.com> wrote in message
> news:1183851948.581194.56800@.i38g2000prf.googlegroups.com...
>
Autogrow of log
I get this info message in my NT log:
Autogrow of file 'w2publish_Log' in database 'w2publish' was cancelled
by user or timed out after 546 milliseconds. Use ALTER DATABASE to
set a smaller FILEGROWTH value for this file or to explicitly set a
new file size.
This database is set to autogrow by 10%.
Searching the net, a few have suggested increasing the 10% to a larger
value. But it seems that the error is SQL Server failing to grow the
file in a timely manner. So moving from 10% to larger number will
only make matters worse. If I set it to a fixed size, what if that is
not enough?
What is the correct approach?You don't want to set it larger you want to change it to a fixed size that
you know will grow in well under the timeout period. Once the files get
beyond 100MB or so you should change it to a fixed size of growth.
--
Andrew J. Kelly SQL MVP
"pbx" <pbeisel@.gmail.com> wrote in message
news:1183851948.581194.56800@.i38g2000prf.googlegroups.com...
> I get this info message in my NT log:
> Autogrow of file 'w2publish_Log' in database 'w2publish' was cancelled
> by user or timed out after 546 milliseconds. Use ALTER DATABASE to
> set a smaller FILEGROWTH value for this file or to explicitly set a
> new file size.
> This database is set to autogrow by 10%.
> Searching the net, a few have suggested increasing the 10% to a larger
> value. But it seems that the error is SQL Server failing to grow the
> file in a timely manner. So moving from 10% to larger number will
> only make matters worse. If I set it to a fixed size, what if that is
> not enough?
> What is the correct approach?
>|||Monitor how your database files grow. This will give you an idea on what
value to specify for autogrow increments. This is critical especially when
the files are very big (imagine 10% autogrow increments for 100GB database
files)
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%237Qq9gQwHHA.4640@.TK2MSFTNGP03.phx.gbl...
> You don't want to set it larger you want to change it to a fixed size that
> you know will grow in well under the timeout period. Once the files get
> beyond 100MB or so you should change it to a fixed size of growth.
> --
> Andrew J. Kelly SQL MVP
> "pbx" <pbeisel@.gmail.com> wrote in message
> news:1183851948.581194.56800@.i38g2000prf.googlegroups.com...
>> I get this info message in my NT log:
>> Autogrow of file 'w2publish_Log' in database 'w2publish' was cancelled
>> by user or timed out after 546 milliseconds. Use ALTER DATABASE to
>> set a smaller FILEGROWTH value for this file or to explicitly set a
>> new file size.
>> This database is set to autogrow by 10%.
>> Searching the net, a few have suggested increasing the 10% to a larger
>> value. But it seems that the error is SQL Server failing to grow the
>> file in a timely manner. So moving from 10% to larger number will
>> only make matters worse. If I set it to a fixed size, what if that is
>> not enough?
>> What is the correct approach?
>
Autogrow of file 'w2publish_Log' in database 'w2publish' was cancelled
by user or timed out after 546 milliseconds. Use ALTER DATABASE to
set a smaller FILEGROWTH value for this file or to explicitly set a
new file size.
This database is set to autogrow by 10%.
Searching the net, a few have suggested increasing the 10% to a larger
value. But it seems that the error is SQL Server failing to grow the
file in a timely manner. So moving from 10% to larger number will
only make matters worse. If I set it to a fixed size, what if that is
not enough?
What is the correct approach?You don't want to set it larger you want to change it to a fixed size that
you know will grow in well under the timeout period. Once the files get
beyond 100MB or so you should change it to a fixed size of growth.
--
Andrew J. Kelly SQL MVP
"pbx" <pbeisel@.gmail.com> wrote in message
news:1183851948.581194.56800@.i38g2000prf.googlegroups.com...
> I get this info message in my NT log:
> Autogrow of file 'w2publish_Log' in database 'w2publish' was cancelled
> by user or timed out after 546 milliseconds. Use ALTER DATABASE to
> set a smaller FILEGROWTH value for this file or to explicitly set a
> new file size.
> This database is set to autogrow by 10%.
> Searching the net, a few have suggested increasing the 10% to a larger
> value. But it seems that the error is SQL Server failing to grow the
> file in a timely manner. So moving from 10% to larger number will
> only make matters worse. If I set it to a fixed size, what if that is
> not enough?
> What is the correct approach?
>|||Monitor how your database files grow. This will give you an idea on what
value to specify for autogrow increments. This is critical especially when
the files are very big (imagine 10% autogrow increments for 100GB database
files)
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%237Qq9gQwHHA.4640@.TK2MSFTNGP03.phx.gbl...
> You don't want to set it larger you want to change it to a fixed size that
> you know will grow in well under the timeout period. Once the files get
> beyond 100MB or so you should change it to a fixed size of growth.
> --
> Andrew J. Kelly SQL MVP
> "pbx" <pbeisel@.gmail.com> wrote in message
> news:1183851948.581194.56800@.i38g2000prf.googlegroups.com...
>> I get this info message in my NT log:
>> Autogrow of file 'w2publish_Log' in database 'w2publish' was cancelled
>> by user or timed out after 546 milliseconds. Use ALTER DATABASE to
>> set a smaller FILEGROWTH value for this file or to explicitly set a
>> new file size.
>> This database is set to autogrow by 10%.
>> Searching the net, a few have suggested increasing the 10% to a larger
>> value. But it seems that the error is SQL Server failing to grow the
>> file in a timely manner. So moving from 10% to larger number will
>> only make matters worse. If I set it to a fixed size, what if that is
>> not enough?
>> What is the correct approach?
>
AutoGenerate
Hi
I have set a field "MessageId" as primary in a Messages table. What I want is that whenever user inserts a message through my site, the MsSql should automatically generate MessageId for the new message inserted, but this is not happening. Any suggestions, advice are highly appreciated. Thank YouIs the column set as an IDENTITY column? If not, that explains the problem. In Enterprise Manager, go into Design mode for the table, and make sure in the properties window, Identity is True (or Yes, do not recall which is used).|||You've got to create a table in SQL with something like the below. As long as there is input in the column named "Message" then the MessageID will automatically increase.
I have set a field "MessageId" as primary in a Messages table. What I want is that whenever user inserts a message through my site, the MsSql should automatically generate MessageId for the new message inserted, but this is not happening. Any suggestions, advice are highly appreciated. Thank YouIs the column set as an IDENTITY column? If not, that explains the problem. In Enterprise Manager, go into Design mode for the table, and make sure in the properties window, Identity is True (or Yes, do not recall which is used).|||You've got to create a table in SQL with something like the below. As long as there is input in the column named "Message" then the MessageID will automatically increase.
CREATE TABLE Message
(
MessageID int IDENTITY(1,1) PRIMARY KEY,
Messagevarchar (2000)NOT NULL
)
Good luck!
-Gabian-|||Thanks to both of you gentleman.
One more thing, what enum of SqlDbtype should i keep for my actual Message(thats being recorded by the user) : "text" or "varChar" ?|||What are your needs:
Varchar will allow a maximum of 8000 bytes
Text will allow very large values ~ 2gb
Varchar will give you much more flexibility for searching and manipulating data though and if it is sufficient would be my recomendation.|||Depends on the size...
I usually use varchar (^_^)
Sunday, February 19, 2012
AutoClose
How do you set the AutoClose property of a SQLExpress db? Also, can the user instance timeout property be set at the database level too to make for easier application distribution.
|||Acutally I think you can′t. But you have another option from the MSDN archives:
Is there any downside to turning off AutoClose in order to speed up the initial startup times after a period of inactivity.
I think the AutoClose property is true by default in SQL Server Express. You can look it up and set it in Management Studio Express / Databases / Database / Properties / Options.
I have a question too:
If AutoClose is true: Can then the database files be copied at any time for backup? Even when users are working with the database?
Or must all connections be closed before the database files can be copied for backup or deployment? Or must the database even be detached to be sure?
How does connection pooling has effects?
What must a developer do to be sure that the database files are in a copy able state?
I asked this a few days before, but i did not get an answer.
|||
Hi Markus,
"If AutoClose is true: Can then the database files be copied at any time for backup? Even when users are working with the database?"
No the database *could* be not consistent in the time of the copy, cause you would also need the log with possible pending transactions.
"Or must all connections be closed before the database files can be copied for backup or deployment? Or must the database even be detached to be sure? "
The safest way would be to detach the database for cpoying (or stopping the server service). You can′be sure that in the meantime of copying another user could open the database again and change something in there (unless you change the state of the database to single_user mode). ANother option for a *hot* backup (which can be done along with open connections is to use backup instead of the *cold* method trhe detaching)
"How does connection pooling has effects? "
Same for connection pooling.
"What must a developer do to be sure that the database files are in a copy able state?"
You could query the server for the database state, but you could never be sure that the database in this state during the whole copy work. The status can be retrieved using the SMO enumeration DatabaseStatus on the Database object. (un?less you change the state of the database to single_user mode)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
|||Hello Jens,
thank you very much for your answer. Ok, safest way is detach or use backup.
> No the database *could* be not consistent in the time of the
> copy, cause you would also need the log with possible
> pending transactions.
Excuse me that i have another question, but i like to understand it. When a power outage occurs during pendig transactions this is handled from Sql Server at next startup.
When an attach attaches the database in the state as it was during detach, why is this a problem then?
What is the inconsistency and why is this a problem for Sql Server? Or do you mean that there can be changes in the files during the copy and only a part of these changes are in the copied files?
Regards,
Markus
Commonly spoken this is right.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
|||Acutally I think you can′t. But you have another option from the MSDN archives:
"A system administrator on the parent instance can set the duration of the time-out period for a user instance by using sp_configure to change the user instance timeout option. The default is 60 minutes."
http://msdn2.microsoft.com/en-us/ms254504.aspx
HTH; Jens Suessmeyer.
http://www.sqlserver2005.de
|||
> Commonly spoken this is right.
Sorry, may i ask you to explain it to me more exactly? So that even i can understand it?
It is still so that i don't really understand why copy of the .mdf and .ldf file is not safe. It's only a feeling that it may be so.
Auto URL Redirect
Hi,Does anyone know how to redirect the report to another URL automatically conditionally based on some checking, without user interaction with the report?I am trying to perform validity checking on user's input value and redirect user to a centralise error page if the input value is invalid.Thanks,J LimYou can't do this using the standard parameter toolbar. You will need to code your own textbox for accepting user input for the parameter value and pass that to the report if is valid and redirect to the error page if not valid.
Thursday, February 16, 2012
auto switching a login
Hello Group,
I have a situation where one of my intranet web
sites has this error: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'
What happens is that when you open the intranet site,
the user is normally logged in with their AD login.
This AD login is in a group which has permissions to the database.
The user will leave the web page open for say and hour or two. When they
come back to their desk and refresh their browser, they get the error.
is it possible the site logs in correctly but the sleeping process casues
the user to be force over to 'NT AUTHORITY\ANONYMOUS LOGON'? This user is n
ot
a valid user in our database. We do not user this user for any of our
permissions.
RichHere's a guess:
1. Your Intranet site stores the user's login information (i.e.
username) in a session variable
2. Your web server is configured to expire idle sessions after a
specific interval, in IIS the default is 20 minutes. When a session
expires, all variables associated with it are lost.
3. Your user leaves the site open for an hour without doing anything.
The session expires, losing their username from the session variables.
4. When your user refreshes their page, the page isn't checking for a
valid session, it blindly tries to connect to the database using the
username stored in the session variables, in this case nothing.
The proper fix for this is for the Intranet site to check for a valid
session, and throw up a "Session has expired" message, instead of
trying to connect to the database without a username.|||Hello Tracy,
thanks for the ideas. I forwarded them to my web guy but it seems he
already had thought of those ideas!
1. Your Intranet site stores the user's login information (i.e.
username) in a session variable
[me web guy said] I do use the session variable but my program has a
checking that will renew a session when it expires.
2. Your web server is configured to expire idle sessions after a
specific interval, in IIS the default is 20 minutes. When a session
expires, all variables associated with it are lost.
[me web guy said] I believed time is extended to be longer than 1 hour.
Not
sure how long but I think it’s more than an hour.
3. Your user leaves the site open for an hour without doing anything.
The session expires, losing their username from the session variables.
[me web guy said] Like I mentioned in #1, it’s automatically renewed.
4. When your user refreshes their page, the page isn't checking for a
valid session, it blindly tries to connect to the database using the
username stored in the session variables, in this case nothing.
[me web guy said] Same as #1
...nuts...
Rich
"Tracy McKibben" wrote:
> Here's a guess:
> 1. Your Intranet site stores the user's login information (i.e.
> username) in a session variable
> 2. Your web server is configured to expire idle sessions after a
> specific interval, in IIS the default is 20 minutes. When a session
> expires, all variables associated with it are lost.
> 3. Your user leaves the site open for an hour without doing anything.
> The session expires, losing their username from the session variables.
> 4. When your user refreshes their page, the page isn't checking for a
> valid session, it blindly tries to connect to the database using the
> username stored in the session variables, in this case nothing.
> The proper fix for this is for the Intranet site to check for a valid
> session, and throw up a "Session has expired" message, instead of
> trying to connect to the database without a username.
>
I have a situation where one of my intranet web
sites has this error: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'
What happens is that when you open the intranet site,
the user is normally logged in with their AD login.
This AD login is in a group which has permissions to the database.
The user will leave the web page open for say and hour or two. When they
come back to their desk and refresh their browser, they get the error.
is it possible the site logs in correctly but the sleeping process casues
the user to be force over to 'NT AUTHORITY\ANONYMOUS LOGON'? This user is n
ot
a valid user in our database. We do not user this user for any of our
permissions.
RichHere's a guess:
1. Your Intranet site stores the user's login information (i.e.
username) in a session variable
2. Your web server is configured to expire idle sessions after a
specific interval, in IIS the default is 20 minutes. When a session
expires, all variables associated with it are lost.
3. Your user leaves the site open for an hour without doing anything.
The session expires, losing their username from the session variables.
4. When your user refreshes their page, the page isn't checking for a
valid session, it blindly tries to connect to the database using the
username stored in the session variables, in this case nothing.
The proper fix for this is for the Intranet site to check for a valid
session, and throw up a "Session has expired" message, instead of
trying to connect to the database without a username.|||Hello Tracy,
thanks for the ideas. I forwarded them to my web guy but it seems he
already had thought of those ideas!
1. Your Intranet site stores the user's login information (i.e.
username) in a session variable
[me web guy said] I do use the session variable but my program has a
checking that will renew a session when it expires.
2. Your web server is configured to expire idle sessions after a
specific interval, in IIS the default is 20 minutes. When a session
expires, all variables associated with it are lost.
[me web guy said] I believed time is extended to be longer than 1 hour.
Not
sure how long but I think it’s more than an hour.
3. Your user leaves the site open for an hour without doing anything.
The session expires, losing their username from the session variables.
[me web guy said] Like I mentioned in #1, it’s automatically renewed.
4. When your user refreshes their page, the page isn't checking for a
valid session, it blindly tries to connect to the database using the
username stored in the session variables, in this case nothing.
[me web guy said] Same as #1
...nuts...
Rich
"Tracy McKibben" wrote:
> Here's a guess:
> 1. Your Intranet site stores the user's login information (i.e.
> username) in a session variable
> 2. Your web server is configured to expire idle sessions after a
> specific interval, in IIS the default is 20 minutes. When a session
> expires, all variables associated with it are lost.
> 3. Your user leaves the site open for an hour without doing anything.
> The session expires, losing their username from the session variables.
> 4. When your user refreshes their page, the page isn't checking for a
> valid session, it blindly tries to connect to the database using the
> username stored in the session variables, in this case nothing.
> The proper fix for this is for the Intranet site to check for a valid
> session, and throw up a "Session has expired" message, instead of
> trying to connect to the database without a username.
>
auto switching a login
Hello Group,
I have a situation where one of my intranet web
sites has this error: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'
What happens is that when you open the intranet site,
the user is normally logged in with their AD login.
This AD login is in a group which has permissions to the database.
The user will leave the web page open for say and hour or two. When they
come back to their desk and refresh their browser, they get the error.
is it possible the site logs in correctly but the sleeping process casues
the user to be force over to 'NT AUTHORITY\ANONYMOUS LOGON'? This user is not
a valid user in our database. We do not user this user for any of our
permissions.
RichHere's a guess:
1. Your Intranet site stores the user's login information (i.e.
username) in a session variable
2. Your web server is configured to expire idle sessions after a
specific interval, in IIS the default is 20 minutes. When a session
expires, all variables associated with it are lost.
3. Your user leaves the site open for an hour without doing anything.
The session expires, losing their username from the session variables.
4. When your user refreshes their page, the page isn't checking for a
valid session, it blindly tries to connect to the database using the
username stored in the session variables, in this case nothing.
The proper fix for this is for the Intranet site to check for a valid
session, and throw up a "Session has expired" message, instead of
trying to connect to the database without a username.|||Hello Tracy,
thanks for the ideas. I forwarded them to my web guy but it seems he
already had thought of those ideas!
1. Your Intranet site stores the user's login information (i.e.
username) in a session variable
[me web guy said] I do use the session variable but my program has a
checking that will renew a session when it expires.
2. Your web server is configured to expire idle sessions after a
specific interval, in IIS the default is 20 minutes. When a session
expires, all variables associated with it are lost.
[me web guy said] I believed time is extended to be longer than 1 hour. Not
sure how long but I think itâ's more than an hour.
3. Your user leaves the site open for an hour without doing anything.
The session expires, losing their username from the session variables.
[me web guy said] Like I mentioned in #1, itâ's automatically renewed.
4. When your user refreshes their page, the page isn't checking for a
valid session, it blindly tries to connect to the database using the
username stored in the session variables, in this case nothing.
[me web guy said] Same as #1
...nuts...
Rich
"Tracy McKibben" wrote:
> Here's a guess:
> 1. Your Intranet site stores the user's login information (i.e.
> username) in a session variable
> 2. Your web server is configured to expire idle sessions after a
> specific interval, in IIS the default is 20 minutes. When a session
> expires, all variables associated with it are lost.
> 3. Your user leaves the site open for an hour without doing anything.
> The session expires, losing their username from the session variables.
> 4. When your user refreshes their page, the page isn't checking for a
> valid session, it blindly tries to connect to the database using the
> username stored in the session variables, in this case nothing.
> The proper fix for this is for the Intranet site to check for a valid
> session, and throw up a "Session has expired" message, instead of
> trying to connect to the database without a username.
>
I have a situation where one of my intranet web
sites has this error: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'
What happens is that when you open the intranet site,
the user is normally logged in with their AD login.
This AD login is in a group which has permissions to the database.
The user will leave the web page open for say and hour or two. When they
come back to their desk and refresh their browser, they get the error.
is it possible the site logs in correctly but the sleeping process casues
the user to be force over to 'NT AUTHORITY\ANONYMOUS LOGON'? This user is not
a valid user in our database. We do not user this user for any of our
permissions.
RichHere's a guess:
1. Your Intranet site stores the user's login information (i.e.
username) in a session variable
2. Your web server is configured to expire idle sessions after a
specific interval, in IIS the default is 20 minutes. When a session
expires, all variables associated with it are lost.
3. Your user leaves the site open for an hour without doing anything.
The session expires, losing their username from the session variables.
4. When your user refreshes their page, the page isn't checking for a
valid session, it blindly tries to connect to the database using the
username stored in the session variables, in this case nothing.
The proper fix for this is for the Intranet site to check for a valid
session, and throw up a "Session has expired" message, instead of
trying to connect to the database without a username.|||Hello Tracy,
thanks for the ideas. I forwarded them to my web guy but it seems he
already had thought of those ideas!
1. Your Intranet site stores the user's login information (i.e.
username) in a session variable
[me web guy said] I do use the session variable but my program has a
checking that will renew a session when it expires.
2. Your web server is configured to expire idle sessions after a
specific interval, in IIS the default is 20 minutes. When a session
expires, all variables associated with it are lost.
[me web guy said] I believed time is extended to be longer than 1 hour. Not
sure how long but I think itâ's more than an hour.
3. Your user leaves the site open for an hour without doing anything.
The session expires, losing their username from the session variables.
[me web guy said] Like I mentioned in #1, itâ's automatically renewed.
4. When your user refreshes their page, the page isn't checking for a
valid session, it blindly tries to connect to the database using the
username stored in the session variables, in this case nothing.
[me web guy said] Same as #1
...nuts...
Rich
"Tracy McKibben" wrote:
> Here's a guess:
> 1. Your Intranet site stores the user's login information (i.e.
> username) in a session variable
> 2. Your web server is configured to expire idle sessions after a
> specific interval, in IIS the default is 20 minutes. When a session
> expires, all variables associated with it are lost.
> 3. Your user leaves the site open for an hour without doing anything.
> The session expires, losing their username from the session variables.
> 4. When your user refreshes their page, the page isn't checking for a
> valid session, it blindly tries to connect to the database using the
> username stored in the session variables, in this case nothing.
> The proper fix for this is for the Intranet site to check for a valid
> session, and throw up a "Session has expired" message, instead of
> trying to connect to the database without a username.
>
Monday, February 13, 2012
auto logout in sql server
Hi,
Is there a auto logout feature in SQL Server? If a user is logged on to the
server through an application and is not using the application, is there a
way to force a logoff after 15 or 30 minutes? Thanks.
Lookup KILL command in the BOL
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:8B561246-456E-427A-B57E-F887E329C4EB@.microsoft.com...
> Hi,
> Is there a auto logout feature in SQL Server? If a user is logged on to
> the
> server through an application and is not using the application, is there a
> way to force a logoff after 15 or 30 minutes? Thanks.
|||No built-in feature.
You could create a job that took the data from sp_who2 and compared the time
now to the LastBatch column. If they exceed your threshold disconnect them
with the KILL command.
Ensure your application can handle this and exit gracefully.
HTH. Ryan
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:8B561246-456E-427A-B57E-F887E329C4EB@.microsoft.com...
> Hi,
> Is there a auto logout feature in SQL Server? If a user is logged on to
> the
> server through an application and is not using the application, is there a
> way to force a logoff after 15 or 30 minutes? Thanks.
Is there a auto logout feature in SQL Server? If a user is logged on to the
server through an application and is not using the application, is there a
way to force a logoff after 15 or 30 minutes? Thanks.
Lookup KILL command in the BOL
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:8B561246-456E-427A-B57E-F887E329C4EB@.microsoft.com...
> Hi,
> Is there a auto logout feature in SQL Server? If a user is logged on to
> the
> server through an application and is not using the application, is there a
> way to force a logoff after 15 or 30 minutes? Thanks.
|||No built-in feature.
You could create a job that took the data from sp_who2 and compared the time
now to the LastBatch column. If they exceed your threshold disconnect them
with the KILL command.
Ensure your application can handle this and exit gracefully.
HTH. Ryan
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:8B561246-456E-427A-B57E-F887E329C4EB@.microsoft.com...
> Hi,
> Is there a auto logout feature in SQL Server? If a user is logged on to
> the
> server through an application and is not using the application, is there a
> way to force a logoff after 15 or 30 minutes? Thanks.
auto logout in sql server
Hi,
Is there a auto logout feature in SQL Server? If a user is logged on to the
server through an application and is not using the application, is there a
way to force a logoff after 15 or 30 minutes? Thanks.Lookup KILL command in the BOL
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:8B561246-456E-427A-B57E-F887E329C4EB@.microsoft.com...
> Hi,
> Is there a auto logout feature in SQL Server? If a user is logged on to
> the
> server through an application and is not using the application, is there a
> way to force a logoff after 15 or 30 minutes? Thanks.|||No built-in feature.
You could create a job that took the data from sp_who2 and compared the time
now to the LastBatch column. If they exceed your threshold disconnect them
with the KILL command.
Ensure your application can handle this and exit gracefully.
HTH. Ryan
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:8B561246-456E-427A-B57E-F887E329C4EB@.microsoft.com...
> Hi,
> Is there a auto logout feature in SQL Server? If a user is logged on to
> the
> server through an application and is not using the application, is there a
> way to force a logoff after 15 or 30 minutes? Thanks.
Is there a auto logout feature in SQL Server? If a user is logged on to the
server through an application and is not using the application, is there a
way to force a logoff after 15 or 30 minutes? Thanks.Lookup KILL command in the BOL
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:8B561246-456E-427A-B57E-F887E329C4EB@.microsoft.com...
> Hi,
> Is there a auto logout feature in SQL Server? If a user is logged on to
> the
> server through an application and is not using the application, is there a
> way to force a logoff after 15 or 30 minutes? Thanks.|||No built-in feature.
You could create a job that took the data from sp_who2 and compared the time
now to the LastBatch column. If they exceed your threshold disconnect them
with the KILL command.
Ensure your application can handle this and exit gracefully.
HTH. Ryan
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:8B561246-456E-427A-B57E-F887E329C4EB@.microsoft.com...
> Hi,
> Is there a auto logout feature in SQL Server? If a user is logged on to
> the
> server through an application and is not using the application, is there a
> way to force a logoff after 15 or 30 minutes? Thanks.
auto logout in sql server
Hi,
Is there a auto logout feature in SQL Server? If a user is logged on to the
server through an application and is not using the application, is there a
way to force a logoff after 15 or 30 minutes? Thanks.Lookup KILL command in the BOL
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:8B561246-456E-427A-B57E-F887E329C4EB@.microsoft.com...
> Hi,
> Is there a auto logout feature in SQL Server? If a user is logged on to
> the
> server through an application and is not using the application, is there a
> way to force a logoff after 15 or 30 minutes? Thanks.|||No built-in feature.
You could create a job that took the data from sp_who2 and compared the time
now to the LastBatch column. If they exceed your threshold disconnect them
with the KILL command.
Ensure your application can handle this and exit gracefully.
--
HTH. Ryan
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:8B561246-456E-427A-B57E-F887E329C4EB@.microsoft.com...
> Hi,
> Is there a auto logout feature in SQL Server? If a user is logged on to
> the
> server through an application and is not using the application, is there a
> way to force a logoff after 15 or 30 minutes? Thanks.
Is there a auto logout feature in SQL Server? If a user is logged on to the
server through an application and is not using the application, is there a
way to force a logoff after 15 or 30 minutes? Thanks.Lookup KILL command in the BOL
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:8B561246-456E-427A-B57E-F887E329C4EB@.microsoft.com...
> Hi,
> Is there a auto logout feature in SQL Server? If a user is logged on to
> the
> server through an application and is not using the application, is there a
> way to force a logoff after 15 or 30 minutes? Thanks.|||No built-in feature.
You could create a job that took the data from sp_who2 and compared the time
now to the LastBatch column. If they exceed your threshold disconnect them
with the KILL command.
Ensure your application can handle this and exit gracefully.
--
HTH. Ryan
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:8B561246-456E-427A-B57E-F887E329C4EB@.microsoft.com...
> Hi,
> Is there a auto logout feature in SQL Server? If a user is logged on to
> the
> server through an application and is not using the application, is there a
> way to force a logoff after 15 or 30 minutes? Thanks.
Subscribe to:
Posts (Atom)