automatically running sql query every week

I have a simple query (a select statement which retrieves results from 2 tables via join). I run this query every week and just copy the results from Management Studio (Ctrl-A) and then open Microsoft Excel and just paste it there.

Is there a way I can automatically run this every week and generate the Excel from the results? If so what steps I have to do?

I use SQL Server 2005 Express Edition.

You can use a DTS in SQL Server to schedule this operation to run every week. Have a look at this article:

http://support.microsoft.com/kb/319951

|||

Does SQL 2005 also have DTS? I didnt see it, may be its hidden somewhere else? Because I am using SQL Server 2005 express edition (the free one)

|||

Nope, Express edition does not have DTS.

|||

You can code a windows script (vb.net or c#) using ADO to grab the data and the Excel object model to write out the spreadsheet.

Then schedule the script with the Windows scheduler.

Company I worked for do all their reporting that way. The script can also email the spreadsheet to a suitable distribution list etc.

|||

Hidotnet001 ,

bullpit:

You can use a DTS in SQL Server to schedule this operation to run every week. Have a look at this article:

http://support.microsoft.com/kb/319951

bullpit is right. You can use DTS if you are using sql2000, or SSIS if you are using sql2005. Try to generate a SSIS package and put it under intergration service, after which you will be able to run it automatically.

But since you are using sql express, i would suggset you adopting the approachSalmonTraining suggested above. Use ADO.NET programming to write some programms and schedule it through windows schedule (you will have to handle pretty lots of more things in this case than in the first approach).

Hope my suggestion can help

sql

Automatically Export reports in SQL Reporting services 2000

Is it possible to have a report automatically export the results to Excel, when the report is generated? ...rather than having to select the format and click Export? .Pl let me know-Thanks.

I found this article some days ago!

http://ryanfarley.com/blog/archive/2006/01/27/15689.aspx

sql

Automatically Export reports in SQL Reporting services 2000

Is it possible to have a report automatically export the results to Excel, when the report is generated? ...rather than having to select the format and click Export? .Pl let me know-Thanks.

I found this article some days ago!

http://ryanfarley.com/blog/archive/2006/01/27/15689.aspx

Automatic RTRIM?

Could anyone explain why this happens:

-- All outputs works but only the first should

select 'works' where '1' = '1'

select 'works' where '1' = '1 '

select 'works' where '1 ' = '1'

Seems to me like trailing blanks are automatically trimmed, why?

SQL Server follows the ANSI/ISO SQL-92 specification (Section 8.2, <Comparison Predicate>, General rules #3) on how to compare strings with spaces. The ANSI standard requires padding for the character strings used in comparisons so that their lengths match before comparing them. The padding directly affects the semantics of WHERE and HAVING clause predicates and other Transact-SQL string comparisons. For example, Transact-SQL considers the strings 'abc' and 'abc ' to be equivalent for most comparison operations.

The only exception to this rule is the LIKE predicate. When the right side of a LIKE predicate expression features a value with a trailing space, SQL Server does not pad the two values to the same length before the comparison occurs. Because the purpose of the LIKE predicate, by definition, is to facilitate pattern searches rather than simple string equality tests, this does not violate the section of the ANSI SQL-92 specification mentioned earlier. (Microsoft, 2003).

Check this out at http://support.microsoft.com/default.aspx?scid=kb;en-us;316626

You can dig up lots of interesting ones, such as:

select 'works' where char(32) = ''

select 'works' where '1' + char (32) = '1'

In BOL, read about some of the string functions, such as Len:

LEN

Returns the number of characters, rather than the number of bytes, of the given string expression, excluding trailing blanks.

Hope this helps.

Lee Everest

MCP MCDBA

Adjunct SQL Instructor

North Lake College
www.texastoo.com/sqlblog

|||An interesting follow-up:

'1' = '1 ' is true
'1' like ' 1 ' is false

LIKE treats the trailing spaces as significant while = does not.
|||

Is there supposed to be a second "1" in your "like" example?

Currently it reads: '1' like ' ' is false

I'm thinking you meant: '1' like '1 ' is false

Or maybe I'm missing something?

Dan

|||You are correct. Typo fixed.
|||;-)

Automatic PDF output

Normally if I want a report in PDF I have to first view the report in the web browser, then select export as PDF and save the PDF file.

Is there a way to avoid the first step (web browser viewing) and have the report immediatly in PDF as soon as I click the "View Report" button? (without using email/file subscription)

Thank you,

Roberto

use this link

http://www.codeproject.com/sqlrs/PDFUsingSQLRepServices.asp

Automated script generation

I often create scripts from SQL Server 2000 Enterprise Mgr the same way: I select all tables, check Indexes, check Constraints, check Windows text, then I go. Is it possible to automate this task further, for example running the scripting from a Stored Proc?RE: I often create scripts from SQL Server 2000 Enterprise Mgr the same way: I select all tables, check Indexes, check Constraints, check Windows text, then I go. Is it possible to automate this task further, for example running the scripting from a Stored Proc?

Q1 Is it possible to automate this task further, for example running the scripting from a Stored Proc?

A2 Yes, though for some tasks (particularly with previous versions) you may have to use cursors.|||I could, fairly quickly, write a SP that reads some system tables and do the scripting I need itself.

But, is there some way to make Enterprise Mgr to do it, without really having to write a program?|||Originally posted by Coolberg
I could, fairly quickly, write a SP that reads some system tables and do the scripting I need itself.

Well, not quickly I realize, because I need to put the ALTER TABLE ... DROP CONSTRAINT statements in the proper order.
Or could I do a ALTER TABLE ... NOCHECK ALL on all tables...?

Automated DTS package will not run

I suspect this is a user-rights/security issue:
I have a DTS package that needs to run weekly. This DTS package runs fine
when you open it and select "execute". I have done the following to set
this up as an automated task:
1)Go to DTS/Local Packages and right click the package.
2)Select "Schedule" from the pop-up menu.
3)Change settings to "weekly" and set the time to the current time + 5
minutes
4)Go to Management/SQL Server Agent/Jobs and refresh
5)Find the job that you just created, right-click and choose 'properties'
6)Confirm the settings (Owner: <my login name chosen from list> ) and let it
run.
I set up the "Notifications" tab to send me an email on completion which it
does:
========================================
================
JOB RUN: 'PhoneImportFromHR' was run on 2/12/2004 at 2:30:00 PM
DURATION: 0 hours, 0 minutes, 11 seconds
STATUS: Failed
MESSAGES: The job failed. The Job was invoked by Schedule 32
(PhoneImportFromHR). The last step to run was step 1 (PhoneImportFromHR).
========================================
================
Due to the fact that the DTS package runs fine when I run it, but won't run
automatically, I am thinking that this is a security issue (ie. The
automated process does not think I have rights to run this package, but
recognizes me when I try to run it manually). I have tried other login IDs
in the "Owner" box (see step #6 above), but I always get the same result.
Can anyone tell me what I can do to get DTS jobs to run automatically?
Schoo
PS: Windows 2000 Server, running SQL 2000.Hi Scott,
Thank you for using the newsgroup and it is my pleasure to help you with
your issue.
From the information you provided. since you got the email notification,
the job runs and you the notification is working fine. For the job to run
automatically, could you check the job schedule. You could right-click the
job, then choose 'properties'; In the 'Schedule' tab, you will notice the
schedule of how you job runs. If you want to change the schedule, you could
press the 'Edit' button. You could arrange a new schedule of the job, Then
the job will run automatically. You could confirm by right-click the job
and choose the 'View job history' and check the record of how the job runs.
For the 'Notification' button, you could check the 'Email operator', choose
one operator and select when to send the email notification.
Hope this helps. If you still have questions, please feel free to post your
message here and I am ready to help!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Thank you for your response... perhaps I was not clear enough on what is
happening. All of the items you mention in regards to changing a schedule,
I am aware of. I am saying that the actual job starts, fails and I get an
email notifying me that the job fails (see original email in this string).
The issue is not how to run the job automatically or how to get it to email
me. The issue is that the DTS package will run fine if I open it up and
execute it, but not if I create a job and try to run it automatically. My
questions is: how can I get the DTS package (that runs manually without
errors) to run without errors automatically.
I hope this is more clear and also that you might shine some light on this
issue for us.
Scott
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:S0TW5ug8DHA.1992@.cpmsftngxa07.phx.gbl...
> Hi Scott,
> Thank you for using the newsgroup and it is my pleasure to help you with
> your issue.
> From the information you provided. since you got the email notification,
> the job runs and you the notification is working fine. For the job to run
> automatically, could you check the job schedule. You could right-click the
> job, then choose 'properties'; In the 'Schedule' tab, you will notice the
> schedule of how you job runs. If you want to change the schedule, you
could
> press the 'Edit' button. You could arrange a new schedule of the job, Then
> the job will run automatically. You could confirm by right-click the job
> and choose the 'View job history' and check the record of how the job
runs.
> For the 'Notification' button, you could check the 'Email operator',
choose
> one operator and select when to send the email notification.
> Hope this helps. If you still have questions, please feel free to post
your
> message here and I am ready to help!
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>|||questions are
1) who is the owner of the job
if owner of job is member of sysadmins fixed serverrole then job will run
dts in job owner's context
if owner of the job is not member of sysadmins then job will attempt to run
in sqlproxyaccount's context(if sql proxy account is configured) if not you
will get a failure
Olu Adedeji
"Schoo" <scott.schuman@.nospam.ma-hc.com> wrote in message
news:OEcdtEm8DHA.4044@.tk2msftngp13.phx.gbl...
> Thank you for your response... perhaps I was not clear enough on what is
> happening. All of the items you mention in regards to changing a
schedule,
> I am aware of. I am saying that the actual job starts, fails and I get an
> email notifying me that the job fails (see original email in this string).
> The issue is not how to run the job automatically or how to get it to
email
> me. The issue is that the DTS package will run fine if I open it up and
> execute it, but not if I create a job and try to run it automatically. My
> questions is: how can I get the DTS package (that runs manually without
> errors) to run without errors automatically.
> I hope this is more clear and also that you might shine some light on this
> issue for us.
> Scott
> "Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
> news:S0TW5ug8DHA.1992@.cpmsftngxa07.phx.gbl...
run
the
the
> could
Then
> runs.
> choose
> your
rights.
>|||Hi Scott,
Thanks for your update and Olu's reply.
Besides Olu's question, could you run the DTS package in the design windows
of the package? Is it successful or any information? When the mail is send
to you, what is the job history? You could also run the following code in
you Query Analyzer and collect the information of this job.
select * from msdb..sysjobs
select * from msdb..sysjobsteps
select * from msdb..sysjobhistory
select * from msdb..sysnotifications
select * from msdb..sysjobservers
select * from msdb..sysjobschedules
I am waiting on your reply. Thanks
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Hi Scott:
How about everything going? Now I would add some more information on this
issue:
There is one good article for your reference:
269074 INF: How to Run a DTS Package as a Scheduled Job
http://support.microsoft.com/?id=269074
Also, you could add the error file to capture the detailed error happened
when run the DTS package:
In the DTS package design window, choose from menu, 'Package'->'Properties'
and set the options in the 'Logging Tab'. You could refer to the DTS
Package Properties (Logging Tab) in the SQL Server Books Online.
Hope this helps. If you still have questions, please feel free to post
message here and I am ready to help.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

autocontained selects

Dear gurus,
select 'insert into cargafrecuencias([id],tipo,horas) values(' +
convert(char(2),[id]) + ',
''' + rtrim(ltrim(tipo)) + ''',
' + convert(char(5), horas) + ')'
from cargafrecuencias
That query returns fine INSERTS and ready for be executed:
insert into cargafrecuencias([id],tipo,horas) values(1 , 'Semanal',
168 )
insert into cargafrecuencias([id],tipo,horas) values(2 , 'Mensual',
720 )
insert into cargafrecuencias([id],tipo,horas) values(3 , 'Diaria',
24 )
insert into cargafrecuencias([id],tipo,horas) values(4 , 'Cuando llegue
el fichero', 8640 )
It's easy and useful without a doubt. But can you imagine do the same with a
table with 80 columns?
I would like do something like that (it would be gorgeous):
select 'insert into table(f1,f2,f3... fn) values (sp_help table)'
from table
On the other hand is very annonying.
Does anyone have any ideas or thoughts?
Thanks in advance and regards,CREATE PROCEDURE SPInserttest
(
@.Test varchar(50)
)
AS
BEGIN
PRINT 'Do something with the test varchar(50)'
Select 'Jens'
UNION
SELECT 'BOB'
END
CREATE TABLE #Tabname
(
Names varchar(50)
)
INSERT INTo #Tabname
EXEC('SPInserttest ''Test''')
Select * from #Tabname
DROP Procedure SPInserttest
DROP Table #Tabname
--
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Enric" wrote:

> Dear gurus,
> select 'insert into cargafrecuencias([id],tipo,horas) values(' +
> convert(char(2),[id]) + ',
> ''' + rtrim(ltrim(tipo)) + ''',
> ' + convert(char(5), horas) + ')'
> from cargafrecuencias
>
> That query returns fine INSERTS and ready for be executed:
> insert into cargafrecuencias([id],tipo,horas) values(1 , 'Semanal',
> 168 )
> insert into cargafrecuencias([id],tipo,horas) values(2 , 'Mensual',
> 720 )
> insert into cargafrecuencias([id],tipo,horas) values(3 , 'Diaria',
> 24 )
> insert into cargafrecuencias([id],tipo,horas) values(4 , 'Cuando llegue
> el fichero', 8640 )
>
> It's easy and useful without a doubt. But can you imagine do the same with
a
> table with 80 columns?
> I would like do something like that (it would be gorgeous):
> select 'insert into table(f1,f2,f3... fn) values (sp_help table)'
> from table
> On the other hand is very annonying.
> Does anyone have any ideas or thoughts?
> Thanks in advance and regards,
>|||Try this one.
http://vyaskn.tripod.com/code/generate_inserts.txt
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:1BD06558-758F-490C-B228-7090641DA685@.microsoft.com...
> Dear gurus,
> select 'insert into cargafrecuencias([id],tipo,horas) values(' +
> convert(char(2),[id]) + ',
> ''' + rtrim(ltrim(tipo)) + ''',
> ' + convert(char(5), horas) + ')'
> from cargafrecuencias
>
> That query returns fine INSERTS and ready for be executed:
> insert into cargafrecuencias([id],tipo,horas) values(1 , 'Semanal',
> 168 )
> insert into cargafrecuencias([id],tipo,horas) values(2 , 'Mensual',
> 720 )
> insert into cargafrecuencias([id],tipo,horas) values(3 , 'Diaria',
> 24 )
> insert into cargafrecuencias([id],tipo,horas) values(4 , 'Cuando
> llegue
> el fichero', 8640 )
>
> It's easy and useful without a doubt. But can you imagine do the same with
> a
> table with 80 columns?
> I would like do something like that (it would be gorgeous):
> select 'insert into table(f1,f2,f3... fn) values (sp_help table)'
> from table
> On the other hand is very annonying.
> Does anyone have any ideas or thoughts?
> Thanks in advance and regards,
>|||Great, thanks a lot, it works very well
"Roji. P. Thomas" wrote:

> Try this one.
> http://vyaskn.tripod.com/code/generate_inserts.txt
>
> --
> Roji. P. Thomas
> Net Asset Management
> http://toponewithties.blogspot.com
>
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:1BD06558-758F-490C-B228-7090641DA685@.microsoft.com...
>
>|||XXXXing life. That doesn't works with my table, it is too large in terms of
columns.
"Roji. P. Thomas" wrote:

> Try this one.
> http://vyaskn.tripod.com/code/generate_inserts.txt
>
> --
> Roji. P. Thomas
> Net Asset Management
> http://toponewithties.blogspot.com
>
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:1BD06558-758F-490C-B228-7090641DA685@.microsoft.com...
>
>

auto_increment id question

hi all,
I was wondering if there is an easier way to find the (auto_increment)
id of the last row you entered than doing a select statement? At the
moment I use this line:
SELECT DISTINCT `sale_number` FROM `sales` ORDER BY `sale_number` DESC
LIMIT 1
to get the sale_number so that I can link other tables to the sale in
the same operation. it seems like a waste of database processing
though.
any advice would be greatly appreciated.Look up @.@.IDENTITY and it's use in BOL.
Nik Marshall-Blank MCSD/MCDBA
Linz, Austria
"Ben" <b.eppel@.gmail.com> wrote in message
news:1128512865.557869.305910@.z14g2000cwz.googlegroups.com...
> hi all,
> I was wondering if there is an easier way to find the (auto_increment)
> id of the last row you entered than doing a select statement? At the
> moment I use this line:
> SELECT DISTINCT `sale_number` FROM `sales` ORDER BY `sale_number` DESC
> LIMIT 1
> to get the sale_number so that I can link other tables to the sale in
> the same operation. it seems like a waste of database processing
> though.
> any advice would be greatly appreciated.
>|||Assuming you are using SQL Server 2000 you should use the
SCOPE_IDENTITY() function. Your suggested method wouldn't be reliable
in a multi-user system and LIMIT isn't a valid SQL Server keyword
anyway.
David Portas
SQL Server MVP
--|||I'm actually using mysql, I know that's not what this forum is for but
the people here are so clever.|||Clever we may be but we're not Psychic.
Nik Marshall-Blank MCSD/MCDBA
Linz, Austria
"Ben" <b.eppel@.gmail.com> wrote in message
news:1128514094.204595.138820@.f14g2000cwb.googlegroups.com...
> I'm actually using mysql, I know that's not what this forum is for but
> the people here are so clever.
>|||mysql_insert_id()
http://dev.mysql.com/doc/mysql/en/mysql-insert-id.html
I found this using Google. Very clever of me :-)
--
Nik Marshall-Blank MCSD/MCDBA
Linz, Austria
"Ben" <b.eppel@.gmail.com> wrote in message
news:1128514094.204595.138820@.f14g2000cwb.googlegroups.com...
> I'm actually using mysql, I know that's not what this forum is for but
> the people here are so clever.
>

auto_increment id question

hi all,
I was wondering if there is an easier way to find the (auto_increment)
id of the last row you entered than doing a select statement? At the
moment I use this line:
SELECT DISTINCT `sale_number` FROM `sales` ORDER BY `sale_number` DESC
LIMIT 1
to get the sale_number so that I can link other tables to the sale in
the same operation. it seems like a waste of database processing
though.
any advice would be greatly appreciated.
Look up @.@.IDENTITY and it's use in BOL.
Nik Marshall-Blank MCSD/MCDBA
Linz, Austria
"Ben" <b.eppel@.gmail.com> wrote in message
news:1128512865.557869.305910@.z14g2000cwz.googlegr oups.com...
> hi all,
> I was wondering if there is an easier way to find the (auto_increment)
> id of the last row you entered than doing a select statement? At the
> moment I use this line:
> SELECT DISTINCT `sale_number` FROM `sales` ORDER BY `sale_number` DESC
> LIMIT 1
> to get the sale_number so that I can link other tables to the sale in
> the same operation. it seems like a waste of database processing
> though.
> any advice would be greatly appreciated.
>
|||Assuming you are using SQL Server 2000 you should use the
SCOPE_IDENTITY() function. Your suggested method wouldn't be reliable
in a multi-user system and LIMIT isn't a valid SQL Server keyword
anyway.
David Portas
SQL Server MVP
|||I'm actually using mysql, I know that's not what this forum is for but
the people here are so clever.
|||Clever we may be but we're not Psychic.
Nik Marshall-Blank MCSD/MCDBA
Linz, Austria
"Ben" <b.eppel@.gmail.com> wrote in message
news:1128514094.204595.138820@.f14g2000cwb.googlegr oups.com...
> I'm actually using mysql, I know that's not what this forum is for but
> the people here are so clever.
>
|||mysql_insert_id()
http://dev.mysql.com/doc/mysql/en/mysql-insert-id.html
I found this using Google. Very clever of me :-)
Nik Marshall-Blank MCSD/MCDBA
Linz, Austria
"Ben" <b.eppel@.gmail.com> wrote in message
news:1128514094.204595.138820@.f14g2000cwb.googlegr oups.com...
> I'm actually using mysql, I know that's not what this forum is for but
> the people here are so clever.
>

auto_increment id question

hi all,
I was wondering if there is an easier way to find the (auto_increment)
id of the last row you entered than doing a select statement? At the
moment I use this line:
SELECT DISTINCT `sale_number` FROM `sales` ORDER BY `sale_number` DESC
LIMIT 1
to get the sale_number so that I can link other tables to the sale in
the same operation. it seems like a waste of database processing
though.
any advice would be greatly appreciated.Look up @.@.IDENTITY and it's use in BOL.
--
Nik Marshall-Blank MCSD/MCDBA
Linz, Austria
"Ben" <b.eppel@.gmail.com> wrote in message
news:1128512865.557869.305910@.z14g2000cwz.googlegroups.com...
> hi all,
> I was wondering if there is an easier way to find the (auto_increment)
> id of the last row you entered than doing a select statement? At the
> moment I use this line:
> SELECT DISTINCT `sale_number` FROM `sales` ORDER BY `sale_number` DESC
> LIMIT 1
> to get the sale_number so that I can link other tables to the sale in
> the same operation. it seems like a waste of database processing
> though.
> any advice would be greatly appreciated.
>|||Assuming you are using SQL Server 2000 you should use the
SCOPE_IDENTITY() function. Your suggested method wouldn't be reliable
in a multi-user system and LIMIT isn't a valid SQL Server keyword
anyway.
--
David Portas
SQL Server MVP
--|||I'm actually using mysql, I know that's not what this forum is for but
the people here are so clever.|||Clever we may be but we're not Psychic.
--
Nik Marshall-Blank MCSD/MCDBA
Linz, Austria
"Ben" <b.eppel@.gmail.com> wrote in message
news:1128514094.204595.138820@.f14g2000cwb.googlegroups.com...
> I'm actually using mysql, I know that's not what this forum is for but
> the people here are so clever.
>|||mysql_insert_id()
http://dev.mysql.com/doc/mysql/en/mysql-insert-id.html
I found this using Google. Very clever of me :-)
--
Nik Marshall-Blank MCSD/MCDBA
Linz, Austria
"Ben" <b.eppel@.gmail.com> wrote in message
news:1128514094.204595.138820@.f14g2000cwb.googlegroups.com...
> I'm actually using mysql, I know that's not what this forum is for but
> the people here are so clever.
>

Auto update Statistics Option

The developer of a finance application suggests us not to select the "Auto
Update Statistics" Option of a SQL Server 2000 database (In Full Recovery
Model). The reason he mentions is that it will affect the database
performance. However, from a number of articles, they suggest us to turn it
ON and it is the default setting as well.
The size of the database file is around 10GB and there are around 20
concurrent users (mainly retrieving information).
I would like to know does the Execution Plan is fixed for Stored Procedure
OR it changes from time to time (Just like running query according to the
data structure) ?
Your advice is sought.
The Execution Plan for Stored Procedure may change from time to time. For
example, after 'Update Statistics' of related table(s), the SQL Server will
generate a new Execution Plan for the SP. Below is more information from BOL
(Execution Plan Caching and Reuse):
Recompiling Execution Plans
Certain changes in a database can cause an execution plan to be either
inefficient or invalid, given the new state of the database. SQL Server
detects the changes that invalidate an execution plan, and marks the plan as
invalid. A new plan must then be recompiled for the next connection that
executes the query. The conditions that cause a plan to be invalidated
include:
Any structural changes made to a table or view referenced by the query
(ALTER TABLE and ALTER VIEW).
New distribution statistics generated either explicitly from a statement
such as UPDATE STATISTICS or automatically.
Dropping an index used by the execution plan.
An explicit call to sp_recompile.
Large numbers of changes to keys (generated by INSERT or DELETE statements
from other users that modify a table referenced by the query).
For tables with triggers, if the number of rows in the inserted or deleted
tables grows significantly.
"Jason" wrote:

> The developer of a finance application suggests us not to select the "Auto
> Update Statistics" Option of a SQL Server 2000 database (In Full Recovery
> Model). The reason he mentions is that it will affect the database
> performance. However, from a number of articles, they suggest us to turn it
> ON and it is the default setting as well.
> The size of the database file is around 10GB and there are around 20
> concurrent users (mainly retrieving information).
> I would like to know does the Execution Plan is fixed for Stored Procedure
> OR it changes from time to time (Just like running query according to the
> data structure) ?
> Your advice is sought.

Auto update Statistics Option

The developer of a finance application suggests us not to select the "Auto
Update Statistics" Option of a SQL Server 2000 database (In Full Recovery
Model). The reason he mentions is that it will affect the database
performance. However, from a number of articles, they suggest us to turn it
ON and it is the default setting as well.
The size of the database file is around 10GB and there are around 20
concurrent users (mainly retrieving information).
I would like to know does the Execution Plan is fixed for Stored Procedure
OR it changes from time to time (Just like running query according to the
data structure) ?
Your advice is sought.The Execution Plan for Stored Procedure may change from time to time. For
example, after 'Update Statistics' of related table(s), the SQL Server will
generate a new Execution Plan for the SP. Below is more information from BOL
(Execution Plan Caching and Reuse):
Recompiling Execution Plans
Certain changes in a database can cause an execution plan to be either
inefficient or invalid, given the new state of the database. SQL Server
detects the changes that invalidate an execution plan, and marks the plan as
invalid. A new plan must then be recompiled for the next connection that
executes the query. The conditions that cause a plan to be invalidated
include:
Any structural changes made to a table or view referenced by the query
(ALTER TABLE and ALTER VIEW).
New distribution statistics generated either explicitly from a statement
such as UPDATE STATISTICS or automatically.
Dropping an index used by the execution plan.
An explicit call to sp_recompile.
Large numbers of changes to keys (generated by INSERT or DELETE statements
from other users that modify a table referenced by the query).
For tables with triggers, if the number of rows in the inserted or deleted
tables grows significantly.
"Jason" wrote:
> The developer of a finance application suggests us not to select the "Auto
> Update Statistics" Option of a SQL Server 2000 database (In Full Recovery
> Model). The reason he mentions is that it will affect the database
> performance. However, from a number of articles, they suggest us to turn it
> ON and it is the default setting as well.
> The size of the database file is around 10GB and there are around 20
> concurrent users (mainly retrieving information).
> I would like to know does the Execution Plan is fixed for Stored Procedure
> OR it changes from time to time (Just like running query according to the
> data structure) ?
> Your advice is sought.

Auto update Statistics Option

The developer of a finance application suggests us not to select the "Auto
Update Statistics" Option of a SQL Server 2000 database (In Full Recovery
Model). The reason he mentions is that it will affect the database
performance. However, from a number of articles, they suggest us to turn it
ON and it is the default setting as well.
The size of the database file is around 10GB and there are around 20
concurrent users (mainly retrieving information).
I would like to know does the Execution Plan is fixed for Stored Procedure
OR it changes from time to time (Just like running query according to the
data structure) ?
Your advice is sought.The Execution Plan for Stored Procedure may change from time to time. For
example, after 'Update Statistics' of related table(s), the SQL Server will
generate a new Execution Plan for the SP. Below is more information from BOL
(Execution Plan Caching and Reuse):
Recompiling Execution Plans
Certain changes in a database can cause an execution plan to be either
inefficient or invalid, given the new state of the database. SQL Server
detects the changes that invalidate an execution plan, and marks the plan as
invalid. A new plan must then be recompiled for the next connection that
executes the query. The conditions that cause a plan to be invalidated
include:
Any structural changes made to a table or view referenced by the query
(ALTER TABLE and ALTER VIEW).
New distribution statistics generated either explicitly from a statement
such as UPDATE STATISTICS or automatically.
Dropping an index used by the execution plan.
An explicit call to sp_recompile.
Large numbers of changes to keys (generated by INSERT or DELETE statements
from other users that modify a table referenced by the query).
For tables with triggers, if the number of rows in the inserted or deleted
tables grows significantly.
"Jason" wrote:

> The developer of a finance application suggests us not to select the "Auto
> Update Statistics" Option of a SQL Server 2000 database (In Full Recovery
> Model). The reason he mentions is that it will affect the database
> performance. However, from a number of articles, they suggest us to turn
it
> ON and it is the default setting as well.
> The size of the database file is around 10GB and there are around 20
> concurrent users (mainly retrieving information).
> I would like to know does the Execution Plan is fixed for Stored Procedure
> OR it changes from time to time (Just like running query according to the
> data structure) ?
> Your advice is sought.

auto save query result to a text file

Is there an easy way to automate the following steps
from SQL server:
1. run a sql-query script (like "select * from ..." )
2. save the query result to a .txt or .rpt file in a
designated folder.
-i know i can write some c++ application to do this
but is there an easy way (like some simple script to be
run as scheduled task)
to do it ?
THANKS!The osql utility will do this for you. It can take a query as an input
parameter, and send its output to a file which is also specified as a
parameter.
The osql command can be called from a command file (.bat) so it can be
automated fully.
See the full osql syntax in Books Online.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"AMY" <anonymous@.discussions.microsoft.com> wrote in message
news:2bb7601c39331$37fc0650$a601280a@.phx.gbl...
> Is there an easy way to automate the following steps
> from SQL server:
> 1. run a sql-query script (like "select * from ..." )
> 2. save the query result to a .txt or .rpt file in a
> designated folder.
> -i know i can write some c++ application to do this
> but is there an easy way (like some simple script to be
> run as scheduled task)
> to do it ?
> THANKS!
>|||thank you Kalen, it's big help !
-amy
>--Original Message--
>The osql utility will do this for you. It can take a
query as an input
>parameter, and send its output to a file which is also
specified as a
>parameter.
>The osql command can be called from a command file (.bat)
so it can be
>automated fully.
>See the full osql syntax in Books Online.
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
>"AMY" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2bb7601c39331$37fc0650$a601280a@.phx.gbl...
>> Is there an easy way to automate the following steps
>> from SQL server:
>> 1. run a sql-query script (like "select * from ..." )
>> 2. save the query result to a .txt or .rpt file in a
>> designated folder.
>> -i know i can write some c++ application to do this
>> but is there an easy way (like some simple script to be
>> run as scheduled task)
>> to do it ?
>> THANKS!
>>
>
>.
>

auto number via a query

Hello,
I am wondering if somone could provide me with a sample SELECT for an
auto number query. For example I have a table called People with two columns
first_name, and last_name. There isn't a unique id to correspond with the
table but would like to dynamically make one during the return of the query.
So if there was 5 rows in the table it would return
1 John Alpha
2 John Beta
3 John Cat
4 John Delta
5 John Echo
Where the query was an order by last_name. Thanks in advance.
Jake"Jake Smythe" <someone@.microsoft.com> wrote in message
news:O5zxrnZmGHA.4100@.TK2MSFTNGP05.phx.gbl...
> Hello,
> I am wondering if somone could provide me with a sample SELECT for an
> auto number query. For example I have a table called People with two
> columns first_name, and last_name. There isn't a unique id to correspond
> with the table but would like to dynamically make one during the return of
> the query. So if there was 5 rows in the table it would return
> 1 John Alpha
> 2 John Beta
> 3 John Cat
> 4 John Delta
> 5 John Echo
> Where the query was an order by last_name. Thanks in advance.
Something like this will work, you'll need to add the first name to the
comparison also.
CREATE TABLE People (FirstName VARCHAR(100), LastName VARCHAR(100))
INSERT INTO People VALUES('John','Alpha')
INSERT INTO People VALUES('John','Beta')
INSERT INTO People VALUES('John','Cat')
INSERT INTO People VALUES('John','Delta')
INSERT INTO People VALUES('John','Echo')
SELECT *, (SELECT COUNT(*) FROM People AS P1 WHERE P1.LastName <=
People.LastName) FROM People
ORDER BY LastName, FirstName
DROP TABLE People

> Jake
>|||While this kludge may work for the immediate need, you must be warned that t
here is no certainly that the order will be static. Each time the query exec
utes, the order may be different. And if will have problems with perfectly d
uplicate names. Try adding duplicate names and watch what happens...
SELECT
( SELECT sum(1)
FROM People p
WHERE ( p.LastName + p.FirstName ) <= ( p.LastName + p.FirstName )
) AS rownum
, p2.LastName
, p2.FirstName
FROM People p2
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Michael C" <nospam@.nospam.com> wrote in message news:%23j%23u8yZmGHA.4052@.TK2MSFTNGP05.phx
.gbl...
> "Jake Smythe" <someone@.microsoft.com> wrote in message
> news:O5zxrnZmGHA.4100@.TK2MSFTNGP05.phx.gbl...
>
> Something like this will work, you'll need to add the first name to the
> comparison also.
>
> CREATE TABLE People (FirstName VARCHAR(100), LastName VARCHAR(100))
> INSERT INTO People VALUES('John','Alpha')
> INSERT INTO People VALUES('John','Beta')
> INSERT INTO People VALUES('John','Cat')
> INSERT INTO People VALUES('John','Delta')
> INSERT INTO People VALUES('John','Echo')
> SELECT *, (SELECT COUNT(*) FROM People AS P1 WHERE P1.LastName <=
> People.LastName) FROM People
> ORDER BY LastName, FirstName
> DROP TABLE People
>
>
>
>|||"Arnie Rowland" <arnie@.1568.com> wrote in message
news:eQ0TcDamGHA.4076@.TK2MSFTNGP05.phx.gbl...
While this kludge may work for the immediate need, you must be warned that
there is no certainly that the order will be static. Each time the query
executes, the order may be different. And if will have problems with
perfectly duplicate names. Try adding duplicate names and watch what
happens...
I was going to add a warning that this method wasn't perfect but I had to
race off so just hit send. Of course the value will change if the order
changes :-) As for duplicates you'd just need to use the fields that make up
the primary key, if rows are duplicated then maybe they should have the same
values anyway. Maybe performance of this method might be a problem?
Michael|||Guys thanks for the responses. It's fine if the order changes each time I am
just looking for a identifier at run time.
"Michael C" <nospam@.nospam.com> wrote in message
news:e0x5fZbmGHA.4064@.TK2MSFTNGP02.phx.gbl...
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:eQ0TcDamGHA.4076@.TK2MSFTNGP05.phx.gbl...
> While this kludge may work for the immediate need, you must be warned that
> there is no certainly that the order will be static. Each time the query
> executes, the order may be different. And if will have problems with
> perfectly duplicate names. Try adding duplicate names and watch what
> happens...
> I was going to add a warning that this method wasn't perfect but I had to
> race off so just hit send. Of course the value will change if the order
> changes :-) As for duplicates you'd just need to use the fields that make
> up the primary key, if rows are duplicated then maybe they should have the
> same values anyway. Maybe performance of this method might be a problem?
> Michael
>

Auto increment after select?

I have two processes will continue to get the number from a table. After eac
h
get, the number should increment by 1, and the two processes need to be
getting a unique number. Is it possible to avoid the two processes get the
same number?Use locking mechansims to ensure that only one process gets to update the
count at any one time. For example, you can do an UPDLOCK on the table.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"BntConan" <BntConan@.discussions.microsoft.com> wrote in message
news:0CB33A5A-3B3A-4DC6-BCCA-BED54594F42C@.microsoft.com...
>I have two processes will continue to get the number from a table. After
>each
> get, the number should increment by 1, and the two processes need to be
> getting a unique number. Is it possible to avoid the two processes get the
> same number?
>|||try this
declare @.Curr_Num int
Update Tbl set @.Curr_Num = CurrValue, CurrValue = Currvalue + 1
where < Condition >
select @.Curr_Num
tbl is the table from which u have to select the value.
and CurrValue holds the current value to be returned.
Tx

Auto generated CRUD in Sql 2005 issue

Here is our problem. If you right click on a table in Management studio it gives you the option of creating The Delete, insert, select and Update stored procedures for any table. The problem is that the auto generation script includes the database name in the stored procedures. So if we have a database called DB_DEV and we move the stored procedures over to database DB_QA these stored procedures are now trying to access the wrong database. Is there a way to make sure that the database name is not included?

If I am reproducing your steps correctly, I see that SSMS will 'auto-magically' write a query for one of the CRUD actions -but it's not a stored procedure.

You may wish to combine that action with using a Stored Procedure template -but as far as I can determine, you'll have to manually remove the dbname.

|||

Could you please post the script you see, and the version of SQL Server you are using?

You can try it on a simple table and not necessary your primary one.

When I try to reproduce your problem, the CRUD script created has a "use [<dbname>]" at the beginning of it. If this is the case for you, you can simply remove this line from the script and it will be applicable to any database.

|||Why not use Edit / Find and Replace after you generate the script?