Sql Auto grow: automatically

Showing posts with label automatically. Show all posts

Thursday, March 29, 2012

Automating SQL Profiler

The powers at be have decided that they would like to automatically run

a trace on one of our analysis servers when it processes a cube in the

early hours of the morning. Now I have no problem creating a SQL

Profile to run and store the results in a database table for them, but

I have no idea how to automate it so that it runs everyday, any help

would be greatly appreciated. My apologises for posting this in forums

let's search in the internet with your subject.

you will find a lot of articles. this is one of them.
http://www.lazydba.com/sql/1__18318.html

Automating SQL Profiler

The powers at be have decided that they would like to automatically run

a trace on one of our analysis servers when it processes a cube in the

early hours of the morning. Now I have no problem creating a SQL

Profile to run and store the results in a database table for them, but

I have no idea how to automate it so that it runs everyday, any help

would be greatly appreciated. My apologises for posting this in forums

Option B - Not sure what information you want to capture from trace but if you are processing your cube using SSIS package you can capture log in database table at package level and cube level both. In cube level log there are many options you can select like sql, start, end time and lot more. This way when your SSIS package will run your database table will also get populated.

-Ashok

|||

SP2 is going to come out with little sample application ASTrace. This tiny sample installs as a service and knows how to subsribe to Analysis Services trace and output this trace into SQL Server table.

Drop me a line if you are in urgent need to solve this problem and cant wait for SP2 (which is pretty close).

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Tuesday, March 27, 2012

Automatics restore script

I am setting up a job to automatically restore the standby database.
This is to test our tape backups. I need to passon the latest full
backup and log file names to the tape system to extract the files. How
do I get the file names? I would like to store these file names in temp
table and pass on to tape system one by one to extract it to local
disk. Do I need to query the msdb of primary or backup directory?
Please suggest sql to achieve this.
Thanks
Tram
If you do SQL Backups then you shouldn't need to know the file names because
the backup is referenced by database name. Are you doing file system
backups? That's not recommended unless you detach the database first.
Query the sysfiles system table for the physical file names.
David Portas
SQL Server MVP
|||A I need to restore from third party tape subsyste, the enterprise
manager doen't recognize the restore fle anmes. I have to develop the
script to retiriev the latest backup and logs therafter.
|||A I need to restore from third party tape subsystem, the enterprise
manager doen't recognize the restore fle anmes. I have to develop the
script to retiriev the latest backup and logs therafter.
|||A I need to restore from third party tape subsystem, the enterprise
manager doen't recognize the restore fle anmes. I have to develop the
script to retiriev the latest backup and logs therafter.
|||Hi
If it is 3rd party, ask the 3rd party vendor how you can script a restore
using their command line tools.
If SQL Server did not back the data up itself (to disk or a backup device
that it can control) it can't get the data back for you by itself.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"tram" <tram_e@.hotmail.com> wrote in message
news:1117887893.908844.130200@.f14g2000cwb.googlegr oups.com...
>A I need to restore from third party tape subsystem, the enterprise
> manager doen't recognize the restore fle anmes. I have to develop the
> script to retiriev the latest backup and logs therafter.
>
|||Thirty party vendor has command to resore the files. I have to passon
the filename as parameter, that is latest bak file and corresponding
trn files.
|||Hi
Have you looked at the backupfile and backupset tables in msdb to see if it
has what you need?
John
"tram" <tram_e@.hotmail.com> wrote in message
news:1117889003.683361.325430@.g43g2000cwa.googlegr oups.com...
> Thirty party vendor has command to resore the files. I have to passon
> the filename as parameter, that is latest bak file and corresponding
> trn files.
>

Automatics restore script

I am setting up a job to automatically restore the standby database.
This is to test our tape backups. I need to passon the latest full
backup and log file names to the tape system to extract the files. How
do I get the file names? I would like to store these file names in temp
table and pass on to tape system one by one to extract it to local
disk. Do I need to query the msdb of primary or backup directory?
Please suggest sql to achieve this.
Thanks
TramIf you do SQL Backups then you shouldn't need to know the file names because
the backup is referenced by database name. Are you doing file system
backups? That's not recommended unless you detach the database first.
Query the sysfiles system table for the physical file names.
--
David Portas
SQL Server MVP
--|||A I need to restore from third party tape subsyste, the enterprise
manager doen't recognize the restore fle anmes. I have to develop the
script to retiriev the latest backup and logs therafter.|||A I need to restore from third party tape subsystem, the enterprise
manager doen't recognize the restore fle anmes. I have to develop the
script to retiriev the latest backup and logs therafter.|||A I need to restore from third party tape subsystem, the enterprise
manager doen't recognize the restore fle anmes. I have to develop the
script to retiriev the latest backup and logs therafter.|||Hi
If it is 3rd party, ask the 3rd party vendor how you can script a restore
using their command line tools.
If SQL Server did not back the data up itself (to disk or a backup device
that it can control) it can't get the data back for you by itself.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"tram" <tram_e@.hotmail.com> wrote in message
news:1117887893.908844.130200@.f14g2000cwb.googlegroups.com...
>A I need to restore from third party tape subsystem, the enterprise
> manager doen't recognize the restore fle anmes. I have to develop the
> script to retiriev the latest backup and logs therafter.
>|||Thirty party vendor has command to resore the files. I have to passon
the filename as parameter, that is latest bak file and corresponding
trn files.|||Hi
Have you looked at the backupfile and backupset tables in msdb to see if it
has what you need?
John
"tram" <tram_e@.hotmail.com> wrote in message
news:1117889003.683361.325430@.g43g2000cwa.googlegroups.com...
> Thirty party vendor has command to resore the files. I have to passon
> the filename as parameter, that is latest bak file and corresponding
> trn files.
>sql

Automatics restore script

I am setting up a job to automatically restore the standby database.
This is to test our tape backups. I need to passon the latest full
backup and log file names to the tape system to extract the files. How
do I get the file names? I would like to store these file names in temp
table and pass on to tape system one by one to extract it to local
disk. Do I need to query the msdb of primary or backup directory?
Please suggest sql to achieve this.
Thanks
TramIf you do SQL Backups then you shouldn't need to know the file names because
the backup is referenced by database name. Are you doing file system
backups? That's not recommended unless you detach the database first.
Query the sysfiles system table for the physical file names.
David Portas
SQL Server MVP
--|||A I need to restore from third party tape subsyste, the enterprise
manager doen't recognize the restore fle anmes. I have to develop the
script to retiriev the latest backup and logs therafter.|||A I need to restore from third party tape subsystem, the enterprise
manager doen't recognize the restore fle anmes. I have to develop the
script to retiriev the latest backup and logs therafter.|||A I need to restore from third party tape subsystem, the enterprise
manager doen't recognize the restore fle anmes. I have to develop the
script to retiriev the latest backup and logs therafter.|||Hi
If it is 3rd party, ask the 3rd party vendor how you can script a restore
using their command line tools.
If SQL Server did not back the data up itself (to disk or a backup device
that it can control) it can't get the data back for you by itself.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"tram" <tram_e@.hotmail.com> wrote in message
news:1117887893.908844.130200@.f14g2000cwb.googlegroups.com...
>A I need to restore from third party tape subsystem, the enterprise
> manager doen't recognize the restore fle anmes. I have to develop the
> script to retiriev the latest backup and logs therafter.
>|||Thirty party vendor has command to resore the files. I have to passon
the filename as parameter, that is latest bak file and corresponding
trn files.|||Hi
Have you looked at the backupfile and backupset tables in msdb to see if it
has what you need?
John
"tram" <tram_e@.hotmail.com> wrote in message
news:1117889003.683361.325430@.g43g2000cwa.googlegroups.com...
> Thirty party vendor has command to resore the files. I have to passon
> the filename as parameter, that is latest bak file and corresponding
> trn files.
>

Automatically update datetime field in a database table.

hi e'body:

I have some database tables, and each one of them have a creation_date and modified_date in them. I was trying to figure out a way where when a row in one of these tables is changed using Enterprise Manager (Database -> Tables -> select table -> right click -> select all rows -> change a field in a row inside a table), is there a way apart from triggers, such that the "modified_date" column for that row get changed to 'getdate()' (rather picks up the current datetime).

thanks in advance.Here is the generic trigger I use to record who modified a record and when:
CREATE TRIGGER TR_[TABLENAME]_U ON dbo.[TABLENAME]
FOR UPDATE
AS
set nocount on
update [TABLENAME]
set Modified = getdate(),
Modifier = isnull(inserted.Modifier, (convert(nvarchar(50),suser_sname())))
from [TABLENAME]
inner join Inserted on TABLENAME.PKey = Inserted.PKey
set nocount offNote that this trigger is for update only. For inserts, you should have default values defined on the table.

automatically update another field based on other parts of the record

Sql is not a strong point with me so I'm just going to throw this out there. I have a stored procedure that updates the quantity in my 'CartItems' table. Is there a way to have something else happen within the stored procedure that will update another field based on other parts of the record? There is a 'lineTotal' field that I need to equal the 'pounds * itemSell' fields which are both fields within this record.

CREATE PROCEDURE UpdateCartItem
(
@.cartItemID Int,
@.newQuantity numeric(9)
)
AS
UPDATE CartItems Set quantity = @.newQuantity
WHERE cartItemID = @.cartItemID
GO

sure. you can update as many fields as you want within the UPDATE statement.

CREATE PROCEDURE UpdateCartItem
(
@.cartItemID Int,
@.newQuantity numeric(9)
)
AS

SET NOCOUNT ON

UPDATE
CartItems
Set
quantity = @.newQuantity
,lineTotal =pounds * itemSell
WHERE
cartItemID = @.cartItemID

SET NOCOUNT OFF

|||Or make the lineTotal a computed column. Just modify the table and tell it the lineTotal column is equal to pounds*itemSell.sql

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 stop/restart sql service

I would like to be able to automatically stop a sql service running on sql
2005 express, use a script to transfer files over to another server and
restart the service automatically.
Any ideas of how I can do this?You can use a batch file that does a net stop, copy and net start. Schedule
it with task scheduler.
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
"pete0085" <pete0085@.discussions.microsoft.com> wrote in message
news:63C59DBE-8BBF-4967-BFB4-EDEF3DA0B84C@.microsoft.com...
>I would like to be able to automatically stop a sql service running on sql
> 2005 express, use a script to transfer files over to another server and
> restart the service automatically.
> Any ideas of how I can do this?|||When it does a copy and it's only copying one file, not the entire directory.
The directory has 4 folders and 1 readme file and it's copying the readme
file.
Any ideas?
"Jason Massie" wrote:
> You can use a batch file that does a net stop, copy and net start. Schedule
> it with task scheduler.
>
> --
> Jason Massie
> Web: http://statisticsio.com
> RSS: http://feeds.feedburner.com/statisticsio
> "pete0085" <pete0085@.discussions.microsoft.com> wrote in message
> news:63C59DBE-8BBF-4967-BFB4-EDEF3DA0B84C@.microsoft.com...
> >I would like to be able to automatically stop a sql service running on sql
> > 2005 express, use a script to transfer files over to another server and
> > restart the service automatically.
> >
> > Any ideas of how I can do this?
>
>|||xcopy /S
Documentation for the other switches is available by running xcopy /?
--
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
"pete0085" <pete0085@.discussions.microsoft.com> wrote in message
news:752C5BDF-A76C-48E3-8D9F-013739AE87AE@.microsoft.com...
> When it does a copy and it's only copying one file, not the entire
> directory.
> The directory has 4 folders and 1 readme file and it's copying the readme
> file.
> Any ideas?
> "Jason Massie" wrote:
>> You can use a batch file that does a net stop, copy and net start.
>> Schedule
>> it with task scheduler.
>>
>> --
>> Jason Massie
>> Web: http://statisticsio.com
>> RSS: http://feeds.feedburner.com/statisticsio
>> "pete0085" <pete0085@.discussions.microsoft.com> wrote in message
>> news:63C59DBE-8BBF-4967-BFB4-EDEF3DA0B84C@.microsoft.com...
>> >I would like to be able to automatically stop a sql service running on
>> >sql
>> > 2005 express, use a script to transfer files over to another server and
>> > restart the service automatically.
>> >
>> > Any ideas of how I can do this?
>>

Automatically Sort Table when Table is Opened in Project

Hi all,

We recently upsized two Microsoft Access Databases to SQL. We're
using an ADP (2002) as the front end.

All the conversion issues have been resolved, except for one:
Whenever we insert a record into a table, the table isn't sorted by
primary key like I would expect. Instead, the record can be found at
the end of the table. This makes finding a particular record
(especially as time goes on) very difficult.

I've tried eliminating all indexes except for the primary key, and
also writing AFTER INSERT triggers, but the table still does not sort
correctly.

Any suggestions would be greatly appreciated!

MattThis is a client-side problem since a table in SQL has no inherent logical
order. I don't think Access will resort the table unless you requery it. To
get around this you will probably need to create your own form for data
entry and display - a good idea anyway if you are doing a lot of data entry.

--
David Portas
SQL Server MVP
--|||Yes adding to David's post, you can define the sort order for the adp form
(you can make the form look virtually identical to a datasheet/table if
you'd like). It will be a string like an Order by clause. I believe you
need the sort string and you need to set the OrderByOn property to true. You
can add code to react to a new record or whatever. Good luck.
~ck

"Matt" <mhen001@.yahoo.com> wrote in message
news:ea450280.0406211322.7b5e60e2@.posting.google.c om...
> Hi all,
> We recently upsized two Microsoft Access Databases to SQL. We're
> using an ADP (2002) as the front end.
> All the conversion issues have been resolved, except for one:
> Whenever we insert a record into a table, the table isn't sorted by
> primary key like I would expect. Instead, the record can be found at
> the end of the table. This makes finding a particular record
> (especially as time goes on) very difficult.
> I've tried eliminating all indexes except for the primary key, and
> also writing AFTER INSERT triggers, but the table still does not sort
> correctly.
> Any suggestions would be greatly appreciated!
> Matt|||oops in a project you use these properties.
ServerFilter Property

You can use the ServerFilter property to specify a subset of records to be
displayed when a server filter is applied to a form or report within a
Microsoft Access project (.adp) or a data access page in a Microsoft Access
project (.adp) or database (.mdb).

Setting

The ServerFilter property is a string expression consisting of a WHERE
clause without the WHERE keyword. For example, the following Visual Basic
code defines and applies a filter to show only customers from the USA:

Me.ServerFilter = "Country = 'USA'"
Me.ServerFilterByForm = TrueThe easiest way to set this property is by using
a form or report's property sheet. You can also set this property on a form
or report by using Visual Basic.

"Matt" <mhen001@.yahoo.com> wrote in message
news:ea450280.0406211322.7b5e60e2@.posting.google.c om...
> Hi all,
> We recently upsized two Microsoft Access Databases to SQL. We're
> using an ADP (2002) as the front end.
> All the conversion issues have been resolved, except for one:
> Whenever we insert a record into a table, the table isn't sorted by
> primary key like I would expect. Instead, the record can be found at
> the end of the table. This makes finding a particular record
> (especially as time goes on) very difficult.
> I've tried eliminating all indexes except for the primary key, and
> also writing AFTER INSERT triggers, but the table still does not sort
> correctly.
> Any suggestions would be greatly appreciated!
> Matt|||Thanks for the replies. It looks like I'll have to go the form route.

The strange thing is: if you open up the properties of the table and
click the "Data" tab, you can specify the "ORDER BY" property. Help
defines this property as:

"Shows client-side sorting criteria sorting criteria applied after
the result set is returned from the database. This property is a
string expression that is the name of the field or fields on which you
want to sort records. When you use more than one field name, separate
the names with a comma (,). If you want to sort records in descending
order, type DESC at the end of the string expression."

Why would Microsoft create this property yet in practice not allow you
to sort tables on the client-side?

Matt

"ck" <c_kettenbach@.hotmail.com> wrote in message news:<5CMBc.3267$6F6.1885@.newssvr25.news.prodigy.com>...
> oops in a project you use these properties.
> ServerFilter Property
>
> You can use the ServerFilter property to specify a subset of records to be
> displayed when a server filter is applied to a form or report within a
> Microsoft Access project (.adp) or a data access page in a Microsoft Access
> project (.adp) or database (.mdb).
> Setting
> The ServerFilter property is a string expression consisting of a WHERE
> clause without the WHERE keyword. For example, the following Visual Basic
> code defines and applies a filter to show only customers from the USA:
> Me.ServerFilter = "Country = 'USA'"
> Me.ServerFilterByForm = TrueThe easiest way to set this property is by using
> a form or report's property sheet. You can also set this property on a form
> or report by using Visual Basic.
> CK
> "Matt" <mhen001@.yahoo.com> wrote in message
> news:ea450280.0406211322.7b5e60e2@.posting.google.c om...
> > Hi all,
> > We recently upsized two Microsoft Access Databases to SQL. We're
> > using an ADP (2002) as the front end.
> > All the conversion issues have been resolved, except for one:
> > Whenever we insert a record into a table, the table isn't sorted by
> > primary key like I would expect. Instead, the record can be found at
> > the end of the table. This makes finding a particular record
> > (especially as time goes on) very difficult.
> > I've tried eliminating all indexes except for the primary key, and
> > also writing AFTER INSERT triggers, but the table still does not sort
> > correctly.
> > Any suggestions would be greatly appreciated!
> > Matt

Automatically setup table from dataset

I need to add tables to an existing "report" from datasets that have 50+
columns. Each table is a different dataset. Is there anyway to replicte the
behiour of the wizard on an existing report? Adding columns one at a time is
a pain, it'd be easier for me to add them all and then remove the few I don't
need.
And yes I know that db tables that wide and reports with this much info are
somewhat useless but I'm forced to swim in the pond I'm in.Can't you just copy and paste a previously created table? It will maintain
all the properties of the table.
>I need to add tables to an existing "report" from datasets that have 50+
> columns. Each table is a different dataset. Is there anyway to replicte
> the
> behiour of the wizard on an existing report? Adding columns one at a time
> is
> a pain, it'd be easier for me to add them all and then remove the few I
> don't
> need.
> And yes I know that db tables that wide and reports with this much info
> are
> somewhat useless but I'm forced to swim in the pond I'm in.

automatically set processor affinity mask not checked

I take that by default for multi CPU machines, the "Automatically set
processor affinity mask for all processors" is checked in the properties of
the server in SQL 2005
My question is, if its not checked, what does that mean and what are the
pros and cons of it ?Check out sp_configure and the "affinity mask" option. Checking the checkbox means 0 (all processors
can be used). Un.checking this box is setting it to some other bitmask, where you specify which
processors can be used.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Hassan" <hassan@.test.com> wrote in message news:OPwvm$7VIHA.2304@.TK2MSFTNGP06.phx.gbl...
>I take that by default for multi CPU machines, the "Automatically set processor affinity mask for
>all processors" is checked in the properties of the server in SQL 2005
> My question is, if its not checked, what does that mean and what are the pros and cons of it ?
>
>|||So how can I check which processors are being used ?
If I look at the processors, all the processors are checked ?
So in essence, is it the same thing as if this setting was checked ?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:A1C1FE69-965D-4A38-B1B8-D61E5CB38CD0@.microsoft.com...
> Check out sp_configure and the "affinity mask" option. Checking the
> checkbox means 0 (all processors can be used). Un.checking this box is
> setting it to some other bitmask, where you specify which processors can
> be used.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Hassan" <hassan@.test.com> wrote in message
> news:OPwvm$7VIHA.2304@.TK2MSFTNGP06.phx.gbl...
>>I take that by default for multi CPU machines, the "Automatically set
>>processor affinity mask for all processors" is checked in the properties
>>of the server in SQL 2005
>> My question is, if its not checked, what does that mean and what are the
>> pros and cons of it ?
>>
>|||> So how can I check which processors are being used ?
Configured to be used, or actually doing some work at a particular point in time? For the later, you
can for instance use Task Manager. For the former, check your sp_configure setting.
> If I look at the processors, all the processors are checked ?
> So in essence, is it the same thing as if this setting was checked ?
There is a slight difference between having all checked and the checkbox not checked (the later is
value 0). SQL Server has as many schedulers as processors. With value 0, a scheduler isn't tied to a
particular processor. With anything but 0, each scheduler is tied to a particular processor. If you
want to dig more into schedulers, threads, workers etc, check out Kalen's Inside SQL Server 2005
book.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Hassan" <hassan@.test.com> wrote in message news:eMOEut8VIHA.5132@.TK2MSFTNGP02.phx.gbl...
> So how can I check which processors are being used ?
> If I look at the processors, all the processors are checked ?
> So in essence, is it the same thing as if this setting was checked ?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:A1C1FE69-965D-4A38-B1B8-D61E5CB38CD0@.microsoft.com...
>> Check out sp_configure and the "affinity mask" option. Checking the checkbox means 0 (all
>> processors can be used). Un.checking this box is setting it to some other bitmask, where you
>> specify which processors can be used.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Hassan" <hassan@.test.com> wrote in message news:OPwvm$7VIHA.2304@.TK2MSFTNGP06.phx.gbl...
>>I take that by default for multi CPU machines, the "Automatically set processor affinity mask for
>>all processors" is checked in the properties of the server in SQL 2005
>> My question is, if its not checked, what does that mean and what are the pros and cons of it ?
>>
>>
>sql

automatically set database to simple recovery upon creation

Is there a way to set the recovery model of any new databases created on SQL
Server 2005 to Simple instead of Full? I would like the recovery model to
default to Simple for any new databases created on the server.
Thanks for your help!
Recovery model is inherited from the model database. So you can set model to simple.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"mp3nomad" <mp3nomad@.discussions.microsoft.com> wrote in message
news:4B63FB22-E1A0-4E8F-895E-75E25FD089AA@.microsoft.com...
> Is there a way to set the recovery model of any new databases created on SQL
> Server 2005 to Simple instead of Full? I would like the recovery model to
> default to Simple for any new databases created on the server.
> Thanks for your help!
|||awesome! Thanks!
"Tibor Karaszi" wrote:

> Recovery model is inherited from the model database. So you can set model to simple.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "mp3nomad" <mp3nomad@.discussions.microsoft.com> wrote in message
> news:4B63FB22-E1A0-4E8F-895E-75E25FD089AA@.microsoft.com...
>

automatically set database to simple recovery upon creation

Is there a way to set the recovery model of any new databases created on SQL
Server 2005 to Simple instead of Full? I would like the recovery model to
default to Simple for any new databases created on the server.
Thanks for your help!Recovery model is inherited from the model database. So you can set model to simple.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"mp3nomad" <mp3nomad@.discussions.microsoft.com> wrote in message
news:4B63FB22-E1A0-4E8F-895E-75E25FD089AA@.microsoft.com...
> Is there a way to set the recovery model of any new databases created on SQL
> Server 2005 to Simple instead of Full? I would like the recovery model to
> default to Simple for any new databases created on the server.
> Thanks for your help!|||awesome! Thanks!
"Tibor Karaszi" wrote:
> Recovery model is inherited from the model database. So you can set model to simple.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "mp3nomad" <mp3nomad@.discussions.microsoft.com> wrote in message
> news:4B63FB22-E1A0-4E8F-895E-75E25FD089AA@.microsoft.com...
> > Is there a way to set the recovery model of any new databases created on SQL
> > Server 2005 to Simple instead of Full? I would like the recovery model to
> > default to Simple for any new databases created on the server.
> >
> > Thanks for your help!
>

automatically set database to simple recovery upon creation

Is there a way to set the recovery model of any new databases created on SQL
Server 2005 to Simple instead of Full? I would like the recovery model to
default to Simple for any new databases created on the server.
Thanks for your help!Recovery model is inherited from the model database. So you can set model to
simple.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"mp3nomad" <mp3nomad@.discussions.microsoft.com> wrote in message
news:4B63FB22-E1A0-4E8F-895E-75E25FD089AA@.microsoft.com...
> Is there a way to set the recovery model of any new databases created on S
QL
> Server 2005 to Simple instead of Full? I would like the recovery model to
> default to Simple for any new databases created on the server.
> Thanks for your help!|||awesome! Thanks!
"Tibor Karaszi" wrote:

> Recovery model is inherited from the model database. So you can set model
to simple.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "mp3nomad" <mp3nomad@.discussions.microsoft.com> wrote in message
> news:4B63FB22-E1A0-4E8F-895E-75E25FD089AA@.microsoft.com...
>

automatically send a notice after 6 months (was "Date Question")

Hello,

I'm trying to write a query that will do the following... I'll give a quick background:

When a machine is installed, I record an installation date. We would like to automatically send a notice to our customers 6 months past this install date regarding scheduled maintenance.

Right now all I have is the notice, which is a report in Access with a query attached that pulls all machine sales that have install dates.

I'm not sure how to reference this situation... or how to start addressing the problem.

Basically:

Customer(CustomerID, CustomerName, CustomerEmail)
MachineDetail(DetailID, CustomerID, ModelID)
MachineModel(ModelID,ModelName)
InstallInfo(InstallID, DetailID, InstallComplete)

Please help :( Thanks,
CherishTry looking here (http://www.1keydata.com/sql/sql.html) or here (http://www.geocities.com/SiliconValley/Vista/2207/sql1.html) or here (http://www.w3schools.com/sql/default.asp).

;)|||use the DATEADD function
select ...
from ...
where InstallComplete
between dateadd("m",-6,date())
and dateadd("m",-5,date()) this will pull all installs between 5 and 6 months old

that way you can prepare the notices manually

sorry, no idea how to set up access to do this automatically

perhaps ask in the access forum|||Create a stored procedure starting with the code posted by r937, that selects the customers you want to send a notice to and either send an e-mail or write the notices to a file.

Create a batch script to execute this procedure ONCE per month, depending on your OS use:
1) Dos cmd and Win2K(XP) task scheduler or 2) Unix KSH and crontab or 3) Oracle jobs.

There have been many posts in this and other forums on how to send e-mail from either Oracle or MS SQL.
:rolleyes:

PS: Ooops, just noticed you mentioned Access...
I beleive you can e-mail the notices by having Access 'write' to Outlook.
Good Luck.|||LKBrwn_DBA, Access doesn't support stored procs|||LKBrwn_DBA, Access doesn't support stored procs
Yeah, I just noticed it was Access, but he can create a VB 'Procedure' or something like that. :(|||PS: Check these links:
MS Email FAQ (http://www.granite.ab.ca/access/email.htm) and MS ACCESS VB script (http://www.febooti.com/products/command-line-email/online-help/send-email-ms-access.html)
;)|||She, and thanks for all your help, I'll try out the suggestions today :)

Thursday, March 29, 2012

Automating SQL Profiler

Automating SQL Profiler

Tuesday, March 27, 2012

Automatics restore script

Automatics restore script

Automatics restore script

Automatically update datetime field in a database table.

automatically update another field based on other parts of the record

Automatically Transfer logins/users to a script file

Automatically Transfer logins/users to a script file

Automatically Transfer logins/users to a script file

automatically stop/restart sql service

automatically stop/restart sql service

Automatically Sort Table when Table is Opened in Project

Automatically setup table from dataset

automatically set processor affinity mask not checked

automatically set processor affinity mask not checked

automatically set database to simple recovery upon creation

automatically set database to simple recovery upon creation

automatically set database to simple recovery upon creation

automatically send a notice after 6 months (was "Date Question")

Sql Auto grow

Blog Archive

About Me