Automatically Transfer logins/users to a script file

For DR purposes, I need to have a job that automatically scripts out the
creation of the logins and users for a database.
Looked at using sp_helprevlogin, but that is for different versions of SQL.
Looked at SCPTXFR to script out MASTER, but it is not including the logins,
only users.
Anyone have any ideas?
Hi
"Kristen" wrote:

> For DR purposes, I need to have a job that automatically scripts out the
> creation of the logins and users for a database.
> Looked at using sp_helprevlogin, but that is for different versions of SQL.
> Looked at SCPTXFR to script out MASTER, but it is not including the logins,
> only users.
> Anyone have any ideas?
Have you looked at DMO and the logins collection?
John
|||No....I will look into that. Thanks!
"John Bell" wrote:

> Hi
> "Kristen" wrote:
>
> Have you looked at DMO and the logins collection?
> John
|||Can you think of another way that does not entail alot of programming?
"John Bell" wrote:

> Hi
> "Kristen" wrote:
>
> Have you looked at DMO and the logins collection?
> John
|||Hi
"Kristen" wrote:

> Can you think of another way that does not entail alot of programming?
>
I would expect the DMO to take less then 12 lines of code!
I don't reallty see why you have an issue with sp_help_rev_login, it will
reside in the master database and have the same interface regardless of SQL
Server version!
Why not just backup the system databases?
John
|||> I would expect the DMO to take less then 12 lines of code!
I'm not certain how well DMO handles SID number and password, so make sure you verify this. Based on
for what purpose you want this script, it might be very important for the logins to have the same
SID and pwd as in the originating SQL Server, so make sure you check that DMO does it the right way.
This is, btw, the beauty of using sp_help_revlogin.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:8B6C82C3-6A1E-4EE0-B6FF-B5385176DA9F@.microsoft.com...
> Hi
> "Kristen" wrote:
>
> I would expect the DMO to take less then 12 lines of code!
> I don't reallty see why you have an issue with sp_help_rev_login, it will
> reside in the master database and have the same interface regardless of SQL
> Server version!
> Why not just backup the system databases?
> John
>

Automatically Transfer logins/users to a script file

For DR purposes, I need to have a job that automatically scripts out the
creation of the logins and users for a database.
Looked at using sp_helprevlogin, but that is for different versions of SQL.
Looked at SCPTXFR to script out MASTER, but it is not including the logins,
only users.
Anyone have any ideas?Hi
"Kristen" wrote:
> For DR purposes, I need to have a job that automatically scripts out the
> creation of the logins and users for a database.
> Looked at using sp_helprevlogin, but that is for different versions of SQL.
> Looked at SCPTXFR to script out MASTER, but it is not including the logins,
> only users.
> Anyone have any ideas?
Have you looked at DMO and the logins collection?
John|||No....I will look into that. Thanks!
"John Bell" wrote:
> Hi
> "Kristen" wrote:
> > For DR purposes, I need to have a job that automatically scripts out the
> > creation of the logins and users for a database.
> > Looked at using sp_helprevlogin, but that is for different versions of SQL.
> > Looked at SCPTXFR to script out MASTER, but it is not including the logins,
> > only users.
> > Anyone have any ideas?
> Have you looked at DMO and the logins collection?
> John|||Can you think of another way that does not entail alot of programming?
"John Bell" wrote:
> Hi
> "Kristen" wrote:
> > For DR purposes, I need to have a job that automatically scripts out the
> > creation of the logins and users for a database.
> > Looked at using sp_helprevlogin, but that is for different versions of SQL.
> > Looked at SCPTXFR to script out MASTER, but it is not including the logins,
> > only users.
> > Anyone have any ideas?
> Have you looked at DMO and the logins collection?
> John|||Hi
"Kristen" wrote:
> Can you think of another way that does not entail alot of programming?
>
I would expect the DMO to take less then 12 lines of code!
I don't reallty see why you have an issue with sp_help_rev_login, it will
reside in the master database and have the same interface regardless of SQL
Server version!
Why not just backup the system databases?
John|||> I would expect the DMO to take less then 12 lines of code!
I'm not certain how well DMO handles SID number and password, so make sure you verify this. Based on
for what purpose you want this script, it might be very important for the logins to have the same
SID and pwd as in the originating SQL Server, so make sure you check that DMO does it the right way.
This is, btw, the beauty of using sp_help_revlogin.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:8B6C82C3-6A1E-4EE0-B6FF-B5385176DA9F@.microsoft.com...
> Hi
> "Kristen" wrote:
>> Can you think of another way that does not entail alot of programming?
> I would expect the DMO to take less then 12 lines of code!
> I don't reallty see why you have an issue with sp_help_rev_login, it will
> reside in the master database and have the same interface regardless of SQL
> Server version!
> Why not just backup the system databases?
> John
>

Automatically Transfer logins/users to a script file

For DR purposes, I need to have a job that automatically scripts out the
creation of the logins and users for a database.
Looked at using sp_helprevlogin, but that is for different versions of SQL.
Looked at SCPTXFR to script out MASTER, but it is not including the logins,
only users.
Anyone have any ideas?Hi
"Kristen" wrote:

> For DR purposes, I need to have a job that automatically scripts out the
> creation of the logins and users for a database.
> Looked at using sp_helprevlogin, but that is for different versions of SQL
.
> Looked at SCPTXFR to script out MASTER, but it is not including the logins
,
> only users.
> Anyone have any ideas?
Have you looked at DMO and the logins collection?
John|||No....I will look into that. Thanks!
"John Bell" wrote:

> Hi
> "Kristen" wrote:
>
> Have you looked at DMO and the logins collection?
> John|||Can you think of another way that does not entail alot of programming?
"John Bell" wrote:

> Hi
> "Kristen" wrote:
>
> Have you looked at DMO and the logins collection?
> John|||Hi
"Kristen" wrote:

> Can you think of another way that does not entail alot of programming?
>
I would expect the DMO to take less then 12 lines of code!
I don't reallty see why you have an issue with sp_help_rev_login, it will
reside in the master database and have the same interface regardless of SQL
Server version!
Why not just backup the system databases?
John|||> I would expect the DMO to take less then 12 lines of code!
I'm not certain how well DMO handles SID number and password, so make sure y
ou verify this. Based on
for what purpose you want this script, it might be very important for the lo
gins to have the same
SID and pwd as in the originating SQL Server, so make sure you check that DM
O does it the right way.
This is, btw, the beauty of using sp_help_revlogin.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:8B6C82C3-6A1E-4EE0-B6FF-B5385176DA9F@.microsoft.com...
> Hi
> "Kristen" wrote:
>
> I would expect the DMO to take less then 12 lines of code!
> I don't reallty see why you have an issue with sp_help_rev_login, it will
> reside in the master database and have the same interface regardless of SQ
L
> Server version!
> Why not just backup the system databases?
> John
>|||If you contact me, I will share a script. It will script logins,
users, system and database role membership, and indiviudal grants with
passwords preserved in SQL Server 2005.
Terry

Automatically email query results

I'm very new to SQL but I have figured out how to do my first query. Now I would like to automate it to send the query as an attachment to users automatically (scheduler/cron?). How would you go about this, I need step by step hints. I'm using SQL Query Analyzer ver 8.00.2039 to generate the query from CDR records. Is this even possible?

Thank you for any guidance.

You have a query and you wish execute this query and send the results automatically. If this is what you want you can perform this by SQL jobs..........In the enterprise manager navigate to management and jobs refer,

http://doc.ddart.net/mssql/sql70/automaem_5.htm

http://doc.ddart.net/mssql/sql70/automaem_15.htm

in the 1st step of the job give your T-SQL code and also give the path of the o/p file in advanced options .......in the second step give the mail step and details of the recepients..........|||Maybe it would be also an option for you sending the information using Reporting Services which is also available for SQL Server 2000. It can handle recordset, format them properly and send them using various formats like Excel / Xml / Pdf etc.

Jens K. Suessmeyer

http://www.sqlserver2005.de

Automatically email query results

I'm very new to SQL but I have figured out how to do my first query. Now I would like to automate it to send the query as an attachment to users automatically (scheduler/cron?). How would you go about this, I need step by step hints. I'm using SQL Query Analyzer ver 8.00.2039 to generate the query from CDR records. Is this even possible?

Thank you for any guidance.

You have a query and you wish execute this query and send the results automatically. If this is what you want you can perform this by SQL jobs..........In the enterprise manager navigate to management and jobs refer,

http://doc.ddart.net/mssql/sql70/automaem_5.htm

http://doc.ddart.net/mssql/sql70/automaem_15.htm

in the 1st step of the job give your T-SQL code and also give the path of the o/p file in advanced options .......in the second step give the mail step and details of the recepients..........|||Maybe it would be also an option for you sending the information using Reporting Services which is also available for SQL Server 2000. It can handle recordset, format them properly and send them using various formats like Excel / Xml / Pdf etc.

Jens K. Suessmeyer

http://www.sqlserver2005.de

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
>

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.

automatic disconnect inactive users

How can I setup SQLServer 2000 to automatically disconnect clients that have
been inactive for 2 hours? There was an option to do this in Sybase
SQLAnywhere, but I haven't found it SQLServer.
Thanks.
Hi
Nothing in SQL. If you need to do this, you need to write code that call the
KILL function.
The system stored procedure sp_who2 is a good code base to use.
Regards
Mike
"Daryl A." wrote:

> How can I setup SQLServer 2000 to automatically disconnect clients that have
> been inactive for 2 hours? There was an option to do this in Sybase
> SQLAnywhere, but I haven't found it SQLServer.
> Thanks.

Autoincrement

Hello
I've a table of Users with an identity key
Some records are inserted by a replication system which sends records with
key like 2-4-6-8 ...
and put them into the table with a INSERT sql
Other records are inserted via web
I need that the records inserted via web takes a key like 1-3-5-7 ...
I've set the identity seed to 1 and identity increment to 2
I've made a test
1. Inserted some record by replication system
2. If I try to insert a new record manually (by enterprise manager) the new
key is a par number instead of an odd
What's wrong?
Can you help me?Why don't you instead of doing that create another field called Origin
make it a bit when it's from the web give it a value of 1 otherwise 0
Your identity will be Old Key + 2 (that's your increment)
http://sqlservercode.blogspot.com/
"Denis" wrote:

> Hello
> I've a table of Users with an identity key
> Some records are inserted by a replication system which sends records with
> key like 2-4-6-8 ...
> and put them into the table with a INSERT sql
> Other records are inserted via web
> I need that the records inserted via web takes a key like 1-3-5-7 ...
> I've set the identity seed to 1 and identity increment to 2
> I've made a test
> 1. Inserted some record by replication system
> 2. If I try to insert a new record manually (by enterprise manager) the ne
w
> key is a par number instead of an odd
> What's wrong?
> Can you help me?
>
>|||Denis,

> What's wrong?
Is the property "not for replication" set in this identity column?
When the values are inserted from the replication, sql server takes that
number as the last identity value inserted in the table, so if the las value
was 8 then when you insert from the web using "set identity_insert t1 off"
will increment that value with the identity increment 8+2 and this will be
the next value to be inserted.
Example:
create table t1(
c1 int not null identity(1, 2)
)
go
insert into t1 default values
insert into t1 default values
insert into t1 default values
go
select
ident_seed('t1'),
ident_incr('t1'),
ident_current('t1')
go
set identity_insert t1 on
go
insert into t1(c1) values(2)
insert into t1(c1) values(4)
insert into t1(c1) values(6)
insert into t1(c1) values(8)
go
select
ident_seed('t1'),
ident_incr('t1'),
ident_current('t1')
go
set identity_insert t1 off
go
insert into t1 default values
go
select * from t1 order by c1 asc
go
drop table t1
go
AMB
"Denis" wrote:

> Hello
> I've a table of Users with an identity key
> Some records are inserted by a replication system which sends records with
> key like 2-4-6-8 ...
> and put them into the table with a INSERT sql
> Other records are inserted via web
> I need that the records inserted via web takes a key like 1-3-5-7 ...
> I've set the identity seed to 1 and identity increment to 2
> I've made a test
> 1. Inserted some record by replication system
> 2. If I try to insert a new record manually (by enterprise manager) the ne
w
> key is a par number instead of an odd
> What's wrong?
> Can you help me?
>
>

Autogrow Timeouts

Hello - Can anyone direct me to good articles on Alter database and Autogrow?
We have a shared server where users in one database got timeouts. Looking
back at the logs, the database was trying to grow during that time frame, but
it timed out. Please see part of log:
2006-10-17 15:00:32.97 spid622 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 15922 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
2006-10-17 15:00:44.41 spid168 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 11390 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
2006-10-17 15:00:46.14 spid460 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 1672 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
2006-10-17 15:01:05.19 spid745 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 18968 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
2006-10-17 15:01:05.62 spid478 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 406 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
2006-10-17 15:01:10.03 spid213 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 4390 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
2006-10-17 15:02:09.95 spid175 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 59906 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
2006-10-17 15:02:23.48 spid478 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 13515 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
2006-10-17 15:02:47.55 spid69 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 24047 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
2006-10-17 15:02:49.81 spid875 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 2265 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
2006-10-17 15:03:12.64 spid168 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 22829 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
The database is about 2.7 gb, and the autogrow is set for 10%. So it was
trying to add maybe 300 mb, but it kept timing out. I could understand it
being an issue for a larger number, but 300 mb is not very much.
When the alter database add filespace happens, is the entire database
inaccessible during that time? Or is it just the space it is adding that is
inaccessible?
I am wondering if the autogrow timeouts and the user timeouts had the same
thing happening to make them timeout. Or if the autogrow attempts were
causing the user timeouts. Seems that cpu on the server were normal, and no
other databases had complaints of slows. The autogrow attempts kept
happening for about an hour and a half before it was succesful. Please help!
Below are two KB about the issue:
http://support.microsoft.com/kb/315512/
INF: Considerations for Autogrow and Autoshrink configuration in SQL
Server
http://support.microsoft.com/kb/305635/
PRB: A Timeout Occurs When a Database Is Automatically Expanding
Mitch wrote:
> Hello - Can anyone direct me to good articles on Alter database and Autogrow?
> We have a shared server where users in one database got timeouts. Looking
> back at the logs, the database was trying to grow during that time frame, but
> it timed out. Please see part of log:
> 2006-10-17 15:00:32.97 spid622 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 15922 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> 2006-10-17 15:00:44.41 spid168 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 11390 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> 2006-10-17 15:00:46.14 spid460 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 1672 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> 2006-10-17 15:01:05.19 spid745 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 18968 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> 2006-10-17 15:01:05.62 spid478 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 406 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> 2006-10-17 15:01:10.03 spid213 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 4390 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> 2006-10-17 15:02:09.95 spid175 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 59906 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> 2006-10-17 15:02:23.48 spid478 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 13515 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> 2006-10-17 15:02:47.55 spid69 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 24047 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> 2006-10-17 15:02:49.81 spid875 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 2265 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> 2006-10-17 15:03:12.64 spid168 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 22829 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> The database is about 2.7 gb, and the autogrow is set for 10%. So it was
> trying to add maybe 300 mb, but it kept timing out. I could understand it
> being an issue for a larger number, but 300 mb is not very much.
> When the alter database add filespace happens, is the entire database
> inaccessible during that time? Or is it just the space it is adding that is
> inaccessible?
> I am wondering if the autogrow timeouts and the user timeouts had the same
> thing happening to make them timeout. Or if the autogrow attempts were
> causing the user timeouts. Seems that cpu on the server were normal, and no
> other databases had complaints of slows. The autogrow attempts kept
> happening for about an hour and a half before it was succesful. Please help!

Autogrow Timeouts

Hello - Can anyone direct me to good articles on Alter database and Autogrow
?
We have a shared server where users in one database got timeouts. Looking
back at the logs, the database was trying to grow during that time frame, bu
t
it timed out. Please see part of log:
2006-10-17 15:00:32.97 spid622 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 15922 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
2006-10-17 15:00:44.41 spid168 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 11390 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
2006-10-17 15:00:46.14 spid460 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 1672 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
2006-10-17 15:01:05.19 spid745 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 18968 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
2006-10-17 15:01:05.62 spid478 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 406 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
2006-10-17 15:01:10.03 spid213 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 4390 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
2006-10-17 15:02:09.95 spid175 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 59906 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
2006-10-17 15:02:23.48 spid478 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 13515 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
2006-10-17 15:02:47.55 spid69 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 24047 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
2006-10-17 15:02:49.81 spid875 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 2265 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
2006-10-17 15:03:12.64 spid168 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 22829 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
The database is about 2.7 gb, and the autogrow is set for 10%. So it was
trying to add maybe 300 mb, but it kept timing out. I could understand it
being an issue for a larger number, but 300 mb is not very much.
When the alter database add filespace happens, is the entire database
inaccessible during that time? Or is it just the space it is adding that is
inaccessible?
I am wondering if the autogrow timeouts and the user timeouts had the same
thing happening to make them timeout. Or if the autogrow attempts were
causing the user timeouts. Seems that cpu on the server were normal, and no
other databases had complaints of slows. The autogrow attempts kept
happening for about an hour and a half before it was succesful. Please help
!Below are two KB about the issue:
http://support.microsoft.com/kb/315512/
INF: Considerations for Autogrow and Autoshrink configuration in SQL
Server
http://support.microsoft.com/kb/305635/
PRB: A Timeout Occurs When a Database Is Automatically Expanding
Mitch wrote:[vbcol=seagreen]
> Hello - Can anyone direct me to good articles on Alter database and Autogr
ow?
> We have a shared server where users in one database got timeouts. Lookin
g
> back at the logs, the database was trying to grow during that time frame,
but
> it timed out. Please see part of log:
> 2006-10-17 15:00:32.97 spid622 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 15922 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> 2006-10-17 15:00:44.41 spid168 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 11390 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> 2006-10-17 15:00:46.14 spid460 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 1672 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> 2006-10-17 15:01:05.19 spid745 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 18968 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> 2006-10-17 15:01:05.62 spid478 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 406 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> 2006-10-17 15:01:10.03 spid213 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 4390 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> 2006-10-17 15:02:09.95 spid175 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 59906 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> 2006-10-17 15:02:23.48 spid478 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 13515 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> 2006-10-17 15:02:47.55 spid69 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 24047 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> 2006-10-17 15:02:49.81 spid875 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 2265 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> 2006-10-17 15:03:12.64 spid168 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 22829 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> The database is about 2.7 gb, and the autogrow is set for 10%. So it was
> trying to add maybe 300 mb, but it kept timing out. I could understand it
> being an issue for a larger number, but 300 mb is not very much.
> When the alter database add filespace happens, is the entire database
> inaccessible during that time? Or is it just the space it is adding that
is
> inaccessible?
> I am wondering if the autogrow timeouts and the user timeouts had the same
> thing happening to make them timeout. Or if the autogrow attempts were
> causing the user timeouts. Seems that cpu on the server were normal, and
no
> other databases had complaints of slows. The autogrow attempts kept
> happening for about an hour and a half before it was succesful. Please help![/vbc
ol]

Autogrow Timeouts

Hello - Can anyone direct me to good articles on Alter database and Autogrow?
We have a shared server where users in one database got timeouts. Looking
back at the logs, the database was trying to grow during that time frame, but
it timed out. Please see part of log:
2006-10-17 15:00:32.97 spid622 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 15922 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
2006-10-17 15:00:44.41 spid168 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 11390 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
2006-10-17 15:00:46.14 spid460 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 1672 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
2006-10-17 15:01:05.19 spid745 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 18968 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
2006-10-17 15:01:05.62 spid478 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 406 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
2006-10-17 15:01:10.03 spid213 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 4390 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
2006-10-17 15:02:09.95 spid175 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 59906 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
2006-10-17 15:02:23.48 spid478 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 13515 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
2006-10-17 15:02:47.55 spid69 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 24047 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
2006-10-17 15:02:49.81 spid875 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 2265 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
2006-10-17 15:03:12.64 spid168 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 22829 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
The database is about 2.7 gb, and the autogrow is set for 10%. So it was
trying to add maybe 300 mb, but it kept timing out. I could understand it
being an issue for a larger number, but 300 mb is not very much.
When the alter database add filespace happens, is the entire database
inaccessible during that time? Or is it just the space it is adding that is
inaccessible?
I am wondering if the autogrow timeouts and the user timeouts had the same
thing happening to make them timeout. Or if the autogrow attempts were
causing the user timeouts. Seems that cpu on the server were normal, and no
other databases had complaints of slows. The autogrow attempts kept
happening for about an hour and a half before it was succesful. Please help!Below are two KB about the issue:
http://support.microsoft.com/kb/315512/
INF: Considerations for Autogrow and Autoshrink configuration in SQL
Server
http://support.microsoft.com/kb/305635/
PRB: A Timeout Occurs When a Database Is Automatically Expanding
Mitch wrote:
> Hello - Can anyone direct me to good articles on Alter database and Autogrow?
> We have a shared server where users in one database got timeouts. Looking
> back at the logs, the database was trying to grow during that time frame, but
> it timed out. Please see part of log:
> 2006-10-17 15:00:32.97 spid622 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 15922 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> 2006-10-17 15:00:44.41 spid168 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 11390 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> 2006-10-17 15:00:46.14 spid460 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 1672 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> 2006-10-17 15:01:05.19 spid745 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 18968 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> 2006-10-17 15:01:05.62 spid478 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 406 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> 2006-10-17 15:01:10.03 spid213 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 4390 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> 2006-10-17 15:02:09.95 spid175 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 59906 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> 2006-10-17 15:02:23.48 spid478 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 13515 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> 2006-10-17 15:02:47.55 spid69 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 24047 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> 2006-10-17 15:02:49.81 spid875 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 2265 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> 2006-10-17 15:03:12.64 spid168 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 22829 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> The database is about 2.7 gb, and the autogrow is set for 10%. So it was
> trying to add maybe 300 mb, but it kept timing out. I could understand it
> being an issue for a larger number, but 300 mb is not very much.
> When the alter database add filespace happens, is the entire database
> inaccessible during that time? Or is it just the space it is adding that is
> inaccessible?
> I am wondering if the autogrow timeouts and the user timeouts had the same
> thing happening to make them timeout. Or if the autogrow attempts were
> causing the user timeouts. Seems that cpu on the server were normal, and no
> other databases had complaints of slows. The autogrow attempts kept
> happening for about an hour and a half before it was succesful. Please help!

auto update/create statistics

For an OLTP application with 100+ users, is it better to disable the
following two options:
Auto create statistics
Auto update statistics
...and instead run
exec sp_updatestats daily/weekly?
TIA
Depends on your setup. If you have a reasonably sized maintenance window, I
would go for a daily sp_updatestats.
The problem with Auto update statistics IMO is that it is most likely to
kick in when your system is at its busiest.
Jacco Schalkwijk
SQL Server MVP
"Nimi" <Nimi@.discussions.microsoft.com> wrote in message
news:F6727E2C-8EFA-4291-B00A-E84E4143E57C@.microsoft.com...
> For an OLTP application with 100+ users, is it better to disable the
> following two options:
> Auto create statistics
> Auto update statistics
> ...and instead run
> exec sp_updatestats daily/weekly?
> TIA
|||you might want to disable AutoUpdate statistics IF you are seeing that it is
causing problems.
You likely do not want to disable AutoCreate Statistics.
Greg Jackson
PDX, Oregon

auto update/create statistics

For an OLTP application with 100+ users, is it better to disable the
following two options:
Auto create statistics
Auto update statistics
...and instead run
exec sp_updatestats daily/weekly?
TIADepends on your setup. If you have a reasonably sized maintenance window, I
would go for a daily sp_updatestats.
The problem with Auto update statistics IMO is that it is most likely to
kick in when your system is at its busiest.
--
Jacco Schalkwijk
SQL Server MVP
"Nimi" <Nimi@.discussions.microsoft.com> wrote in message
news:F6727E2C-8EFA-4291-B00A-E84E4143E57C@.microsoft.com...
> For an OLTP application with 100+ users, is it better to disable the
> following two options:
> Auto create statistics
> Auto update statistics
> ...and instead run
> exec sp_updatestats daily/weekly?
> TIA|||you might want to disable AutoUpdate statistics IF you are seeing that it is
causing problems.
You likely do not want to disable AutoCreate Statistics.
Greg Jackson
PDX, Oregon

auto update/create statistics

For an OLTP application with 100+ users, is it better to disable the
following two options:
Auto create statistics
Auto update statistics
...and instead run
exec sp_updatestats daily/weekly?
TIADepends on your setup. If you have a reasonably sized maintenance window, I
would go for a daily sp_updatestats.
The problem with Auto update statistics IMO is that it is most likely to
kick in when your system is at its busiest.
Jacco Schalkwijk
SQL Server MVP
"Nimi" <Nimi@.discussions.microsoft.com> wrote in message
news:F6727E2C-8EFA-4291-B00A-E84E4143E57C@.microsoft.com...
> For an OLTP application with 100+ users, is it better to disable the
> following two options:
> Auto create statistics
> Auto update statistics
> ...and instead run
> exec sp_updatestats daily/weekly?
> TIA|||you might want to disable AutoUpdate statistics IF you are seeing that it is
causing problems.
You likely do not want to disable AutoCreate Statistics.
Greg Jackson
PDX, Oregon

auto identity for each Type

Hello,

I am working on an accounting system using VB.NET and sql server 2005 as a database. the application should be used by multiple users.
i have a the following structure:
Voucher: ID (primary), Date,TypeID, ReferenceCode, ....
Type: ID, Code, Name. (the user can add new type anytime!)
(Ex: PV- payment voucher, JV - Journal Voucher ,...)

When adding a voucher the user will choose a type, according to this type (for each year) a counter will be increminted.
for example: PV1, PV2...PV233,... the other type will have its separate counter JV1, JV2 ,...JV4569,..
I am using the sqlTransaction cause i am doing other operations that should be transactional with the insertion of the Voucher.

The question is :
What is the best solution to generate a counter for each type?(With code sample)

Thanks.do you really need to have the 'PV' and 'JV' before each value? if you could use ints, then you could use identity columns. That's the standard way of doing this.

You can always tack on a JV or PV in the front end if that's the way your boss wants it to look in a report or something.

from BOL:

IDENTITY

Indicates that the new column is an identity column. When a new row is added to the table, Microsoft® SQL Server™ provides a unique, incremental value for the column. Identity columns are commonly used in conjunction with PRIMARY KEY constraints to serve as the unique row identifier for the table. The IDENTITY property can be assigned to tinyint, smallint, int, bigint, decimal(p,0), or numeric(p,0) columns. Only one identity column can be created per table. Bound defaults and DEFAULT constraints cannot be used with an identity column. You must specify both the seed and increment or neither. If neither is specified, the default is (1,1).|||if you were using mysql, this functionality (starting a new auto_increment within each type group) is built in

it's impossible to do this with an IDENTITY column

you will have to generate your own numbers, and i would recommend very strongly against it|||the counter in the question is the ReferenceCode in the Voucher table
Voucher: ID (primary), Date,TypeID, ReferenceCode.
so for each added voucher and according to the TypeID a the reference code will be generated. let say the last counter for the PV type is 230 so the referenceCode will be PV231. if the Type is JV and the last counter is 566 then the ReferenceCode will be JV567 and so on.
We don't have to forget that we are working in a multi user enviroment, and the Reference Code should be unique .|||put the JV or PV in another field and concatenate it in the front end. smart numbers are stupid and loved by the accounting types. this kind of things slow down joins and causes other kinds of pain. i have not seen smart numbers in a project for five years and that was a legacy foxpro app.

Auto generate report without clicking on the "View Report"

Is there a way to automatically generate the report when the users
changes the parameter selection - without clicking on the "View
Report" button?
ThxFor what it's worth, you can just press the enter key - and if all required
parameters are filled, the report will refresh.
Not quite the same thing - but you need to do something to indicate that you
have finished in a cell.
"Harsh" <creative@.mailcity.com> wrote in message
news:fa671a26.0407161434.4bb91320@.posting.google.com...
> Is there a way to automatically generate the report when the users
> changes the parameter selection - without clicking on the "View
> Report" button?
> Thx