automating a query

Need help again.

I've a query that searches for a specific record. I need to make this query available for some collegues at work who don't understand SQL, so I wouldn't like to have them editing the query to insert the new number for every search they have to do.

Is there something like a popup text box that calls for the new number and then substitutes it in the query sintaxe?

this would be very helpfull. Thanks

Unfortuantely, there is not.

And if your collegues could endanger your query, think about what they could do to your database...

Put the query in a stored procedure, and show them how to execute the stored procedure.

Automatic trigger on a specific time

Hi!

I use SQL Server Express 2005 and would like that a trigger is fired automatically at a specific time. I have been developing a game which has a lot of periods with a start date and an end date. Every time a periode is finished the trigger should add new records for the next periode. The dates of the periodes are already setted at the beginning of the game. For example:

periode startdate enddate
1 23.08.2006, 15:00 24.08.2006, 17:00
2 24.08 .2006, 17:00 25.08.2006, 08:00
and so on

Can anyone help me how to do this? Thanks!

You can create a maintenance job that runs every few hours or every few days and call a stored proc and put your logic in the stored proc.

|||

Hi ndinakar,

thx for your reply!

I know that jobs are an alternative but there are two problems: firstly it seems, that jobs are not available in SQL Server Express Version and secondly the trigger should actually fire on a specific time that could be every minute. So I would have to fire a job every minute but I think, that might not be a good idea.

|||I havent used Express editions. The other alternative is to create a Windows Service and schedule it to run every 1 minute. You could call a stored proc using OSQL. Activate the OSQL from your windows service. Might sound complicated but I cant think of any other option.|||Sounds like what you really want is a windows service like ndinakar mentioned.|||Thx ndinakar and Motley. I`ll try to use a windows service. That might be the best solution.sql

Automatic SQL Queries to dump data to folder

Hey everyone, trying to set up a SQL Query to run on a pre-determined
batch cycle that will automatically dump out specific table data to a
..csv file so that users can load the file into Excel right away, with
no manipulation or modification.

We are running SQL Server 2000. I am a novice SQL user. Any
suggestions?kosta.triantafillou@.gmail.com (kosta.triantafillou@.gmail.com) writes:
> Hey everyone, trying to set up a SQL Query to run on a pre-determined
> batch cycle that will automatically dump out specific table data to a
> .csv file so that users can load the file into Excel right away, with
> no manipulation or modification.
> We are running SQL Server 2000. I am a novice SQL user. Any
> suggestions?

Best is probably to set up a job in Agent that uses BCP to bulk out
the data. Do you want the entire table, or only part of it?

You can also try the Export/Import wizard and see what it gives you.
(Never have used it myself, though.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||DTS is probably something you want to look at also. Like Erland's
suggestion, you package would be scheduled via SQL Agent.

Automatic query killing...

All,

Is there a way to have SQL server automatically kill a spid when its result set gets to be larger than a specific threshold( eg 3 gigs) ?

Any help is greatly appreciated: any sample stored procedures or code library for doing something like this?

Thanks,

IsaacThat's a scary post...

I'd invest more time determining IF a process would be one that resembled a run away train...

Imagine the rollback...

Automated testing | How to "force" GETDATE() function to return specific value?

Hello,

Our QA team have running a lot of test scripts (for automated regression
testing), they run them on the different databases (Oracle/MS SQL).
Several of those tests are dependent on the current date/time. In order to
be
able to use them efficiently, we changed the current date/time on the QA
database server to a specific date/time before starting the scripts, so we
are sure the test scripts always run in the same environment.

Resetting the date/time of the database server gives us more and more
problems (OS problems, backup/ virusscan, ...).

It is possible to fix the problem with SYSDATE function on Oracle by setting
FIXED_DATE init parameter.

Is it possible to 'change' the current date/time on 'database' level,
instead of on OS level for MSSQL2000?
Do you know other means to do such things?

Thanks in advance,

Konstantin"Konstantin Zakharenko" <kzakharenko@.infopulse.com.ua> wrote in message
news:bpt1s9$ctp$1@.snoopy.infopulse.com.ua...
> Hello,
> Our QA team have running a lot of test scripts (for automated regression
> testing), they run them on the different databases (Oracle/MS SQL).
> Several of those tests are dependent on the current date/time. In order to
> be
> able to use them efficiently, we changed the current date/time on the QA
> database server to a specific date/time before starting the scripts, so we
> are sure the test scripts always run in the same environment.
> Resetting the date/time of the database server gives us more and more
> problems (OS problems, backup/ virusscan, ...).
> It is possible to fix the problem with SYSDATE function on Oracle by
setting
> FIXED_DATE init parameter.
>
> Is it possible to 'change' the current date/time on 'database' level,
> instead of on OS level for MSSQL2000?
> Do you know other means to do such things?
> Thanks in advance,
> Konstantin

I don't know what FIXED_DATE does in Oracle - does it force SYSDATE to
return the same value every time it's called? If so, I don't believe there's
any way to do this in SQL Server. You could create your own function called
dbo.getdate(), and use that, but then it wouldn't be a valid test of your
'real' code.

In any case, it's not clear why you would want to always test with the same
datetime value - you wouldn't prove anything except that your code works
with one particular datetime, and that probably isn't desirable. It might be
better to put some work into establishing what the test results should be
for a given datetime input value, and validate your code that way.

If I've misunderstood, or if this doesn't help, perhaps you can clarify
exactly what you want getdate() to return, and how you want to use it.

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:3fc25218$1_3@.news.bluewin.ch...
> "Konstantin Zakharenko" <kzakharenko@.infopulse.com.ua> wrote in message
> news:bpt1s9$ctp$1@.snoopy.infopulse.com.ua...
> > Hello,
> > Our QA team have running a lot of test scripts (for automated regression
> > testing), they run them on the different databases (Oracle/MS SQL).
> > Several of those tests are dependent on the current date/time. In order
to
> > be
> > able to use them efficiently, we changed the current date/time on the QA
> > database server to a specific date/time before starting the scripts, so
we
> > are sure the test scripts always run in the same environment.
> > Resetting the date/time of the database server gives us more and more
> > problems (OS problems, backup/ virusscan, ...).
> > It is possible to fix the problem with SYSDATE function on Oracle by
> setting
> > FIXED_DATE init parameter.
> > Is it possible to 'change' the current date/time on 'database' level,
> > instead of on OS level for MSSQL2000?
> > Do you know other means to do such things?
> > Thanks in advance,
> > Konstantin
> I don't know what FIXED_DATE does in Oracle - does it force SYSDATE to
> return the same value every time it's called? If so, I don't believe
there's
> any way to do this in SQL Server. You could create your own function
called
> dbo.getdate(), and use that, but then it wouldn't be a valid test of your
> 'real' code.
> In any case, it's not clear why you would want to always test with the
same
> datetime value - you wouldn't prove anything except that your code works
> with one particular datetime, and that probably isn't desirable. It might
be
> better to put some work into establishing what the test results should be
> for a given datetime input value, and validate your code that way.
> If I've misunderstood, or if this doesn't help, perhaps you can clarify
> exactly what you want getdate() to return, and how you want to use it.
> Simon

Thank you for replay.

Yes. You are right. The FIXED_DATE lets you set a constant date that SYSDATE
will always return instead of the current date.

The main problem is that if you take a fixed test database, and run for
example a report, the output of that report can be (very) different if you
run this report today vs. the run you will do tomorrow vs. ... Other typical
example: our application (financial one) reacts differently if you want to
create some entities with dates in the past (different execution flow).

We are using Rational Robot in the automated testing. It validates that all
system reaction and outcome is identical to the reference run (verification
points). If the 'current date/time' is changed since the 'reference' run,
you get a lot of (unnecessary) errors/warnings and failed verification
points. That's why we need to run test scripts with specific datetime value.

Of course, automated tests are not intended to replace all other tests,
their purpose to supplement another tests. Automated test scripts are mainly
used for regression testing. Since it is very difficult and time-consuming
to make Robot test scripts time independent it is acceptable for us to
execute tests with fixed datetime.

Unfortunately, calling of new dbo.getdate() function will cause modification
of application. It is time-consuming and task. That's why our main goal is
to solve the problem by means of Oracle/MSSQL parameters. Moreover, your
solution with dbo.getdate() will not work in a case when we need correct
datetime (not fixed).

CREATE FUNCTION dbo.getdate()
RETURNS datetime AS
BEGIN
-- return cast ('2003-01-01' as datetime) -- It is OK.
return getdate() -- It is NOT OK.
END

The GETDATE() is a nondeterministic function and it is not allowed to use it
in user-defined functions.

If we will not solve the problem by configuring MSSQL database (I think it
is most likely) our solution will be to create the following view:

CREATE VIEW v_nondeterministic AS SELECT getdate() AS getdate;

And to use SELECT getdate FROM v_nondeterministic where required.

Best regards,

Konstantin