Showing posts with label time. Show all posts
Showing posts with label time. Show all posts

Tuesday, March 27, 2012

Executing Oracle Stored Procedure via SQL Server 2000 Linked S

Thanks for the link. I have a stored procedure in Oracle that was created a
long time ago by someone else. Without manipulating the stored proc at all,
I wanted to feed it the required input parameters via SQL Server and let it
do its magic. From what I gather, this is not possible? I must create the
proc within a package in Oracle?
"oj" wrote:

> Here is an old post by Umachandar. See if it helps:
> http://tinyurl.com/7dxrr
> --
> -oj
>
> "marco" <marco@.discussions.microsoft.com> wrote in message
> news:792C4015-008A-420A-B307-EAFC6BAC49EE@.microsoft.com...
>
>Yes. Creating a package wrapper is your ticket to get to oracle proc.
-oj
"marco" <marco@.discussions.microsoft.com> wrote in message
news:4653000F-F545-4044-9876-E113CD14AFE6@.microsoft.com...
> Thanks for the link. I have a stored procedure in Oracle that was created
> a
> long time ago by someone else. Without manipulating the stored proc at
> all,
> I wanted to feed it the required input parameters via SQL Server and let
> it
> do its magic. From what I gather, this is not possible? I must create
> the
> proc within a package in Oracle?
> "oj" wrote:
>

executing multiple query at one time

hi,

i am making a n application which in between deletes the multiple tables from the sql database.

for that i have written the following code:

SqlCommand cmd = newSqlCommand();

cmd.CommandText = "delete from " + dbConstt.DBSchema + ".PicassoSelectivityLog where QTID=" + qtid;

cmd.ExecuteNonQuery();

cmd.CommandText = "delete from " + dbConstt.DBSchema + ".PicassoSelectivityLog where QTID=" + qtid;

cmd.ExecuteNonQuery();

in this way, many more tables are to be deleted.

is there any need to create the new SQLCommand object again an\d agin for each and every query. can iot be done like the given above or can there be some better method?

thanz in advance..

divya

What about creating a static method, passing in the connection and the things that can change in the query, composing query query within the method and executing this thing asynchronously ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
sql

Monday, March 26, 2012

Executing a Views from a Trigger

Hi,

I am new to using SQL. I want to be able to exucute a query that I
place in a view. I want this views to be executed every time a value
change in one of the table in a particular field. So my guess was to
use a trigger that will call the views every time the data change in
the selected table. Is this the proper way of doing thing? Should I
use other SQL tools to achive this. I search for exemple of trigger
executing views but did not found anything as of yet. Let's use this
dummy name for the exemple:

Database: DB1
Table: Tbl1
Special field in Tbl1: flag
Views name: views_01

Thank you.

PhilippeForgot to mention, I am using SQL 2000.

Thank you.|||Hi,

> I am new to using SQL. I want to be able to exucute a query that I
> place in a view.

You don't execute queries inside views. You just get to use the
view as if it were a table.

Why would you want to "execute the query"?

--
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

>I want this views to be executed every time a value
> change in one of the table in a particular field. So my guess was to
> use a trigger that will call the views every time the data change in
> the selected table. Is this the proper way of doing thing? Should I
> use other SQL tools to achive this. I search for exemple of trigger
> executing views but did not found anything as of yet. Let's use this
> dummy name for the exemple:
> Database: DB1
> Table: Tbl1
> Special field in Tbl1: flag
> Views name: views_01|||Hi

What exactly do you mean by "call the views every time the data change in
the selected table."?

When the data is added/updated/deleted from the underlying table the View is
automatically updated. (It is just a select statement).

Are you concerned about updating the user interface where the View is used
as a source for a datgrid or something. If so you have to find some way to
tell the Client program to requery the database, perhaps using the View.
when a database update occurs.

--
-Dick Christoph
<solidsna2@.gmail.com> wrote in message
news:1141226630.058373.30490@.e56g2000cwe.googlegro ups.com...
> Hi,
> I am new to using SQL. I want to be able to exucute a query that I
> place in a view. I want this views to be executed every time a value
> change in one of the table in a particular field. So my guess was to
> use a trigger that will call the views every time the data change in
> the selected table. Is this the proper way of doing thing? Should I
> use other SQL tools to achive this. I search for exemple of trigger
> executing views but did not found anything as of yet. Let's use this
> dummy name for the exemple:
> Database: DB1
> Table: Tbl1
> Special field in Tbl1: flag
> Views name: views_01
> Thank you.
> Philippe|||I am working with an application that use SQL to store it's data. What
I am trying to achieve is to execute the query below every hour.

UPDATE Tbl1
SET Tbl1.Flag = '0'
WHERE Tbl1.Flag <> '0'

UPDATE Tbl1
SET Flag = '1'
WHERE Tbl1.Key = (SELECT Tbl2.Key
FROM Tbl2
WHERE Tbl2.Key = Tbl1.Key
AND CURRENT_TIMESTAMP >= Tbl2.Expdate GROUP
BY Tbl2.Key)

DELETE Tbl2
WHERE CURRENT_TIMESTAMP >= Tbl2.Expdate

The Query does exactly what I want it to do. Now I want to launch this
query from the application I was talking about earlyer. So I thought
that if I use a trigger that will be iniated by a script in my
application by changing a value to 1 or 0 every hour. This will then
activate the SQL trigger that will execute the query above. I strore
the query in a views. So that is why I ask how to execute a views with
a trigger.

Maybe this is not the way to go, but I hope you can understand what I
am trying to do.

Thank you again.

Philippe|||Hi

If you want to execute this every hour, this would be better handled in a
scheduled job. Which can be scheduled to run a SQL Statement or stored
procedure every hour.

Although you could schedule the application to run an update query (without
a Trigger or View) every hour, what if the application isn't running all the
time? or if there are muttiple instances of it running?

One point of clarification you cannot execute and Update Query in a View.

-Dick Christoph

--
<solidsna2@.gmail.com> wrote in message
news:1141233438.780742.274780@.t39g2000cwt.googlegr oups.com...
>I am working with an application that use SQL to store it's data. What
> I am trying to achieve is to execute the query below every hour.
> UPDATE Tbl1
> SET Tbl1.Flag = '0'
> WHERE Tbl1.Flag <> '0'
> UPDATE Tbl1
> SET Flag = '1'
> WHERE Tbl1.Key = (SELECT Tbl2.Key
> FROM Tbl2
> WHERE Tbl2.Key = Tbl1.Key
> AND CURRENT_TIMESTAMP >= Tbl2.Expdate GROUP
> BY Tbl2.Key)
> DELETE Tbl2
> WHERE CURRENT_TIMESTAMP >= Tbl2.Expdate
> The Query does exactly what I want it to do. Now I want to launch this
> query from the application I was talking about earlyer. So I thought
> that if I use a trigger that will be iniated by a script in my
> application by changing a value to 1 or 0 every hour. This will then
> activate the SQL trigger that will execute the query above. I strore
> the query in a views. So that is why I ask how to execute a views with
> a trigger.
> Maybe this is not the way to go, but I hope you can understand what I
> am trying to do.
> Thank you again.
> Philippe|||How do I run a SQL statement or stored procedure every hour?

It can not run the the query all the time. I am doing other
manipulation withing my application. The 2 jobs needs to be
synchronise +- 5 min.

Thank you again for the help.

Philippe|||(solidsna2@.gmail.com) writes:
> How do I run a SQL statement or stored procedure every hour?

As Dick said, schedule a job to run from SQL Server Agent.

> It can not run the the query all the time. I am doing other
> manipulation withing my application. The 2 jobs needs to be
> synchronise +- 5 min.

In SQL Agent, a job can have several steps that executed in order. This
may be something for you.

It is difficult from your abstract definition to say whether you are
on the right track at all. If you can describe the underlying business
problem, it is more likely that you will get useful advice.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Wednesday, March 21, 2012

ExecuteScalar returns 0 (null) but INSERT is successful.

I have code that has worked just fine for some time, and now all of the sudden I am having an issue. I have a simple INSERT statement built and then make the following call:

RecordID = cmd.ExecuteScalar

I have never had a problem with this before. The RecordID of the newly inserted record is returned into the RecordID Integer varibale. All of the sudden, the varibale has a value of 0 (null I assume is being returned), but yet the INSERT worked just fine. I can check the table in SQL and it is populated with no issues.

No exception is thrown of any type or anything. Does anybody know what may be happening?

Can you give us a peek at the procedure?

|||

Need to check the SP, you should have the Select statement which returns the newly inserted value..

|||Yes, thanks for the quick response. I must of had a brain fart or something because I did not have the

SELECT @.@.IDENTITY

at the end of the SQL. I am not sure how it was working before, but I know I needed to add this at the end of the INSERT statement.

The only thing I can figure is that I copy and paste so much code to use as a template, that when I wrote this SQL from scratch I forgot to add it, and never really paid attention when I was copying the INSERT statements before.

Thank you!

|||

You may have a lingering problem; you probably should be using SCOPE_IDENTITY() function instead of @.@.IDENTITY.

You might want to give a look at a couple of previous posts related to SCOPE_IDENTITY() versus @.@.IDENTITY here and here.

|||

Its better to use the SCOPE_IDENTITY function..

Select Scope_Identity()

|||

Sounds good to me; I actually saw that as well in the MSDN example.

Could you tell me why it is better (performance, etc.)?

Thanks,

|||

@.@.IDENTITY hold the global value (across the scope)

Scope_Identity hold the current Scope value.

When there is a concurrency user try to insert the value on your table, the @.@.identity has the very latest data, which may not be inserted by your current scope, but the scope_identity always have the value whichever your current scope inserted.

ExecuteScalar returns 0 (null) but INSERT is successful.

I have code that has worked just fine for some time, and now all of the sudden I am having an issue. I have a simple INSERT statement built and then make the following call:

RecordID = cmd.ExecuteScalar

I have never had a problem with this before. The RecordID of the newly inserted record is returned into the RecordID Integer varibale. All of the sudden, the varibale has a value of 0 (null I assume is being returned), but yet the INSERT worked just fine. I can check the table in SQL and it is populated with no issues.

No exception is thrown of any type or anything. Does anybody know what may be happening?

Can you give us a peek at the procedure?

|||

Need to check the SP, you should have the Select statement which returns the newly inserted value..

|||Yes, thanks for the quick response. I must of had a brain fart or something because I did not have the

SELECT @.@.IDENTITY

at the end of the SQL. I am not sure how it was working before, but I know I needed to add this at the end of the INSERT statement.

The only thing I can figure is that I copy and paste so much code to use as a template, that when I wrote this SQL from scratch I forgot to add it, and never really paid attention when I was copying the INSERT statements before.

Thank you!

|||

You may have a lingering problem; you probably should be using SCOPE_IDENTITY() function instead of @.@.IDENTITY.

You might want to give a look at a couple of previous posts related to SCOPE_IDENTITY() versus @.@.IDENTITY here and here.

|||

Its better to use the SCOPE_IDENTITY function..

Select Scope_Identity()

|||

Sounds good to me; I actually saw that as well in the MSDN example.

Could you tell me why it is better (performance, etc.)?

Thanks,

|||

@.@.IDENTITY hold the global value (across the scope)

Scope_Identity hold the current Scope value.

When there is a concurrency user try to insert the value on your table, the @.@.identity has the very latest data, which may not be inserted by your current scope, but the scope_identity always have the value whichever your current scope inserted.

Monday, March 19, 2012

ExecuteQuery taking a long time

Hi:

I have a query which returns approximately 50000 records, I am using a linked server to connect to two databases and retrieve data. For some reason it is taking a liitle more than hour to execute the query, but on MS Sql Server query window it comes after few minutes but the query runs for a long time.

How can expediate my query execution process.

Environment details

Database: MS Sql Server 64bit 2005
MS Sql jar file: sqljdbc_1.2.jar
OS: Windows both server and client.

Connect String in java code:

jdbcTongue Tiedqlserver://sample_server:1433;databaseName=sample_db;user=admin_user;password=admin_pwd

and use PreparedStatement and ResultSet.

Regards
Arup
Try using a stored procedure if you can (dont know if you can do that in JDBC )|||

Hello Arup,

I would agree that the best way to solve long running query executions is to use a stored procedure which does all the processing of the data on the server and only return to the client the necessary amount of processed data via output parameters. You can use the CallableStatement API provided by the JDBC driver for doing this.

That being said, the Microsoft SQL Server 2005 v1.2 JDBC driver includes a critical 'feature' called "Adaptive Response Buffering" which allow developers to retrieve large amounts of data. The lengthy latency you are encountering is not because of the query execution, but because of the time it takes the driver to spool the data on the client machine before control is given back to the application.

You can ready more about "Adaptive Reponse Buffering" at http://blogs.msdn.com/jdbcteam/archive/2007/05/02/what-is-adaptive-response-buffering-and-why-should-i-use-it.aspx. This functionality if available in the V1.2 CTP1 driver which has the version 1.2.2323.101. You can download it at http://www.microsoft.com/downloads/details.aspx?familyid=f914793a-6fb4-475f-9537-b8fcb776befd&displaylang=en.

We appreciate your feedback using the newly added functionality of this driver.

Kind Regards,

Jaaved

Execute StoreProcedue in SQL2000 every time i received a replay me

Hi,
how can i execute a StoreProcedue in SQL2000 every time i received a replay
message with a specific subject, and pass some arguments like the subject.
thank in advanced,
Renato Ribeiro
Hi,
1. Create a Task to execute stored procedure
2. Create a message (SP_ADDMESSAGE) to create the subject
3. Using the message ID and Task name create a alert using SP_ADDALERT
4. Trigger the message using RAISERROR statement.
This will automatically fire the Task to execute the stored procedure
Thanks
Hari
SQL Server MVP
"Renato Ribeiro" <RenatoRibeiro@.discussions.microsoft.com> wrote in message
news:FA8C6A92-CB30-46B1-A9B8-187F43927B4D@.microsoft.com...
> Hi,
> how can i execute a StoreProcedue in SQL2000 every time i received a
> replay
> message with a specific subject, and pass some arguments like the subject.
>
> thank in advanced,
> Renato Ribeiro
>

Execute StoreProcedue in SQL2000 every time i received a replay me

Hi,
how can i execute a StoreProcedue in SQL2000 every time i received a replay
message with a specific subject, and pass some arguments like the subject.
thank in advanced,
Renato RibeiroRenato
How do you receive a message?
Have a look into JOBS , for more details please refer to BOL.
"Renato Ribeiro" <RenatoRibeiro@.discussions.microsoft.com> wrote in message
news:33C23D3F-60D2-4591-874F-FA9FA730DED6@.microsoft.com...
> Hi,
> how can i execute a StoreProcedue in SQL2000 every time i received a
> replay
> message with a specific subject, and pass some arguments like the subject.
>
> thank in advanced,
> Renato Ribeiro
>

Monday, March 12, 2012

Execute SQL task with update statement

I am running an update statement in an execute sql task, it will run one time but then fails after that. Whats going on?

Here is my query I'm running.

UPDATE encounter
SET mrn = r.mrn, resourcecode = r.resourcecode, resgroup = r.resgroup, apptdate = r.apptdate, appttime = r.appttime
FROM SCFDBWH.SigSched.dbo.EncounterNARaw AS r INNER JOIN
encounter ON encounter.encounter_number = r.Encounter

What I'm doing is pulling info from a flat file, inserting it into the encounter table, I then run this sql statement to pull additional info from another table on another server, and update the encounter table with the corresponding info. Pretty straight forward. But what is really getting me is that the package will run 1 time but if I wait ten minutes and try running it again it bombs. Any ideas?

Please provide any error messages. That usually helps us help you.|||Error: OLE DB provider "SQLNCLI" for linked server "SCFDBWH" returned message "Login timeout expired".

Error: OLE DB provider "SQLNCLI" for linked server "SCFDBWH" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".

[Execute SQL Task] Error: Executing the query "UPDATE encounter SET mrn = r.mrn, resourcecode = r.resourcecode, resgroup = r.resgroup, apptdate = r.apptdate, appttime = r.appttime FROM SCFDBWH.SigSched.dbo.EncounterNARaw AS r INNER JOIN encounter ON encounter.encounter_number = r.Encounter " failed with the following error: "Named Pipes Provider: Could not open a connection to SQL Server [5]. ". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

This is what the SSIS package says. I'm not sure how helpful it is but I can run the update statement in management studio all day long and it works great. It just bombs through the SSIS package.

|||

Seems as though the package is having problems talking to the server, "login timeout expired"

|||

I have all the timeouts settings set to 60 seconds, which should be plenty of time, one would think. When it does work it runs real fast-less than 4 seconds.

The main frustration is that it will work 1 time when I do something to the query but then it doesn't work again.

Wednesday, March 7, 2012

Execute SQL from File: How Can I process the record set?

I want to be able to pass the location of a file (contains SQL to be executed) to my package at run time. To do this I was going to override the connection string for the file.

I've created a 'Execute SQL Task' that opens the sql script and stores the full result set into an variable (system.object). I can execute this and it works fine i.e turns green :).

However I can't work out how to get the data back out of the variable. I have found a doc on SQLIS (The ExecuteSQL Task) that explained how to get the data in to a variable but didn't tell me how to process the data afterwards. There is another article on there that shows how to shred a recordset (Shredding a Recordset) but this example uses an OLE DB source and the 'Recordset Destination' object. This would work but but the only options are sql from a variable or the option to type in the command.

I really have two questions here.

1. Using the first method how can I pass the data stored in the variable into a data flow so that I can use it.

2. using the second method, how can I pass the SQL into a variable at runtime from a file?

Has anyone got examples of how they read SQL from a file and process the data without having to hard code the sql or sql file name in the package.

#1) If you have data in an object and want to use it in a data-flow then you're going to need to loop over the records in the object and add them to the pipeline. Its custom source adapter time!!!

Here's how you do it in a script task: http://blogs.conchango.com/jamiethomson/archive/2005/02/08/960.aspx I dare say you can take this code and adapt it to use it in a script component. I have to say, I haven't actually tried it.

#2) Again you may need a custom/script task to do this. I don't have time to look at this now but I'll try later. It shouldn't be too difficult, just use System.IO namespace.

-Jamie|||To configure the file connection at runtime. You need to use expressions. On the properties of the data flow task select expressions. In here you can set the connection string property of the flat file to the variable (containing a filename).

Execute report and go directly to a save file option

We have a report that is basically a data file. It can have 2 rows or it can
have 100,000 rows. We are running into response time issues, memory issues,
etc. I know that RS is not the preferred method to deliver these sorts of
large data files. We need to do anything possible to satify the user at this
point and the amount of time it's currently taking to run this report with a
large amount of data, even when the report runs successfully, is unacceptable
to them. But, until we have time to re-design, is there any way to have the
report always prompt to save the file instead of waiting for the default HTML
format and then exporting to CSV? Any other short-term solutions?
StephanieFrom your own web page it is easy. Using URL integration you can specify the
format. Even if you are using Report Manager you could have a report that
has the appropriate parameters, they open it up and all you have in the
report is a link that says Export Data. Use the Jump to URL action (I also
make the link blue and underlined).
One other thing, perhaps you have seen this already.If you do this you will
want to change the CSV to use ASCII instead of unicode. Excel puts unicode
into a single column. To have RS export CSV in ASCII format you have to make
a change to a config file.
You only need to change in one place, rsreportserver.config. Reboot after
the change. The below shows commenting out the existing entry and putting in
the needed change to have CSV export as ASCII
<!--
<Extension Name="CSV"
Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering"/>
-->
<Extension Name="CSV"
Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
<Configuration>
<DeviceInfo>
<Encoding>ASCII</Encoding>
</DeviceInfo>
</Configuration>
</Extension>
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
news:10819562-B6BC-4535-BE4A-4B5AA6BD2B8A@.microsoft.com...
> We have a report that is basically a data file. It can have 2 rows or it
> can
> have 100,000 rows. We are running into response time issues, memory
> issues,
> etc. I know that RS is not the preferred method to deliver these sorts of
> large data files. We need to do anything possible to satify the user at
> this
> point and the amount of time it's currently taking to run this report with
> a
> large amount of data, even when the report runs successfully, is
> unacceptable
> to them. But, until we have time to re-design, is there any way to have
> the
> report always prompt to save the file instead of waiting for the default
> HTML
> format and then exporting to CSV? Any other short-term solutions?
> Stephanie

execute query later

Hi NG
Im writing a web-interface that enables the user to update a DB. The
(non)queries takes heaps of time, and since my page "waits" for the query to
finish, wich is a pain. So basically I was wondering if there is kinda a
"update mytable set myrow=... LATER" kinda keyword or something, to make the
query execute whenever the sqlserver feels like it, but return "ok" right
away.
- Kasper"Kasper Birch Olsen" <kasper@.nospam.com> wrote in message
news:eYDSkU2ZFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Hi NG
> Im writing a web-interface that enables the user to update a DB. The
> (non)queries takes heaps of time, and since my page "waits" for the query
to
> finish, wich is a pain. So basically I was wondering if there is kinda a
> "update mytable set myrow=... LATER" kinda keyword or something, to make
the
> query execute whenever the sqlserver feels like it, but return "ok" right
> away.
> - Kasper
>
As far as I know, there is no direct way of doing this.
One thing comes to mind... Have a simple table where you can drop the
information quickly. A SQL Server job can come along and check the table
every few minutes and then perform the work.
There are some caveats however. What if the data is bad, or SQL Server has
an error attempting to process the data. You have already told the web-user
that everything is ok, but in reality it is not. How do you notify the
user that there were problems.
You may need to do some architecturual work to get this to work the way you
want it to.
Rick Sawtell
MCT, MCSD, MCDBA|||Implement the sql update as a stored procedure. If you are using an ADO /
ADO.NET connection, then look executing the SP asynchronously. The SP can
send an email notification back to the user when it completes.
"Kasper Birch Olsen" <kasper@.nospam.com> wrote in message
news:eYDSkU2ZFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Hi NG
> Im writing a web-interface that enables the user to update a DB. The
> (non)queries takes heaps of time, and since my page "waits" for the query
to
> finish, wich is a pain. So basically I was wondering if there is kinda a
> "update mytable set myrow=... LATER" kinda keyword or something, to make
the
> query execute whenever the sqlserver feels like it, but return "ok" right
> away.
> - Kasper
>|||Service Broker queue(s) come to mind. Need 2005 however or come up with
your own queue table.
William Stacey [MVP]
"Kasper Birch Olsen" <kasper@.nospam.com> wrote in message
news:eYDSkU2ZFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Hi NG
> Im writing a web-interface that enables the user to update a DB. The
> (non)queries takes heaps of time, and since my page "waits" for the query
> to finish, wich is a pain. So basically I was wondering if there is kinda
> a "update mytable set myrow=... LATER" kinda keyword or something, to make
> the query execute whenever the sqlserver feels like it, but return "ok"
> right away.
> - Kasper
>

Execute Program with Time Delay

Hi
I would like to execute a program from an SQL Server Proceedure.
But I need to Execute it with a 3 minute time delay, plus if there is
another instance of this delayed execute running then I need to not add
another.
Any help would be much appreciated
Thanks
BTry this
WAITFOR DELAY '00:03:00"
Madhivanan|||Synchronous or asynchronous?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Ben" <Ben@.NoSpam.com> wrote in message news:%23wVcch9HFHA.3608@.TK2MSFTNGP14.phx.gbl...[col
or=darkred]
> Hi
> I would like to execute a program from an SQL Server Proceedure.
> But I need to Execute it with a 3 minute time delay, plus if there is anot
her instance of this
> delayed execute running then I need to not add another.
> Any help would be much appreciated
> Thanks
> B
>[/color]|||Hi Tibor
Asynchronous, as I would like the user to continue working, in ignorance of
the delay/execution of the application.
Thanks
B
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OHzPo99HFHA.1172@.TK2MSFTNGP12.phx.gbl...
> Synchronous or asynchronous?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Ben" <Ben@.NoSpam.com> wrote in message
> news:%23wVcch9HFHA.3608@.TK2MSFTNGP14.phx.gbl...
>|||You can, from the stored procedure, create or just schedule a SQL Server Age
nt job. See Books Online
for things as sp_add_job etc. You need to put in some code for the checking
whether the job is
already "on queue". Agent will not execute a job is it is already executing
(will not have two
instances of the same job executing at the same time), so perhaps this is su
fficient for you?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Ben" <Ben@.NoSpam.com> wrote in message news:e2m4wF%23HFHA.3484@.TK2MSFTNGP12.phx.gbl...[col
or=darkred]
> Hi Tibor
> Asynchronous, as I would like the user to continue working, in ignorance o
f the delay/execution of
> the application.
> Thanks
> B
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:OHzPo99HFHA.1172@.TK2MSFTNGP12.phx.gbl...
>[/color]

Sunday, February 19, 2012

Execute Package Task

Hi,
I have a DTS package that runs numerous tasks simultaneously for time and
performance reasons, but I would like to add an "Execute Package Task" to
this package so that another package will run, but only after all tasks have
finished in this package. Is there a way to do this?
Thanks!Hi Patrice,
Try connect all your tasks to a dummy task using workflow properties.
Finally connect your final "Execute Package Task" to the dummy task.
Dennis.
"Patrice" wrote:

> Hi,
> I have a DTS package that runs numerous tasks simultaneously for time and
> performance reasons, but I would like to add an "Execute Package Task" to
> this package so that another package will run, but only after all tasks ha
ve
> finished in this package. Is there a way to do this?
> Thanks!

Execute Multiple Scripts from one directory

I got tired of having to execute multiple scripts one at a time from
my testing server to my production server. So I decided to create a
batch routine that utilizes the command line routine OSQL. All you
have to do to use it is place the batch file in a directory containing
your SQL scripts and execute the batch file. I have placed in the
command the argument to print a result file for each sql script. If
you need your SQL scripts run in a particular order simply rename them
accordingly. i.e. 001_updatecustomer.sql, 002_update_sp.sql
enjoy
REM ########################################
#####
REM Author: Joe Ocampo
REM Date: 04/17/2004
REM Version: 1.0.1
REM ########################################
#####
REM You must execute the script from the SQL server itself
REM and you must use the SA login or the DBO login of the
REM target database.
REM ########################################
#####
SET login=sa
SET password=password
SET server=local
SET database=pubs
for %%a in (*.sql) do osql -d %database% -U %login% -P %password% -i
%%a -o %%a_result.txtJoe wrote:
> I got tired of having to execute multiple scripts one at a time from
> my testing server to my production server. So I decided to create a
> batch routine that utilizes the command line routine OSQL. All you
> have to do to use it is place the batch file in a directory containing
> your SQL scripts and execute the batch file. I have placed in the
> command the argument to print a result file for each sql script. If
> you need your SQL scripts run in a particular order simply rename them
> accordingly. i.e. 001_updatecustomer.sql, 002_update_sp.sql
Exceedingly simple, but incredibly useful, and something that I was going to
have to write myself pretty soon - thanks.
John.|||I knew there had to be an easier way. I was scripting 35 scripts at a
time. Between opening and closing files and the occasional, "Didn't I
run that already?" I knew it was time to find a better way.
Glad to help,
Joe
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Execute Multiple Scripts from one directory

I got tired of having to execute multiple scripts one at a time from
my testing server to my production server. So I decided to create a
batch routine that utilizes the command line routine OSQL. All you
have to do to use it is place the batch file in a directory containing
your SQL scripts and execute the batch file. I have placed in the
command the argument to print a result file for each sql script. If
you need your SQL scripts run in a particular order simply rename them
accordingly. i.e. 001_updatecustomer.sql, 002_update_sp.sql
enjoy
REM #############################################
REM Author: Joe Ocampo
REM Date: 04/17/2004
REM Version: 1.0.1
REM #############################################
REM You must execute the script from the SQL server itself
REM and you must use the SA login or the DBO login of the
REM target database.
REM #############################################
SET login=sa
SET password=password
SET server=local
SET database=pubs
for %%a in (*.sql) do osql -d %database% -U %login% -P %password% -i
%%a -o %%a_result.txt
Joe wrote:
> I got tired of having to execute multiple scripts one at a time from
> my testing server to my production server. So I decided to create a
> batch routine that utilizes the command line routine OSQL. All you
> have to do to use it is place the batch file in a directory containing
> your SQL scripts and execute the batch file. I have placed in the
> command the argument to print a result file for each sql script. If
> you need your SQL scripts run in a particular order simply rename them
> accordingly. i.e. 001_updatecustomer.sql, 002_update_sp.sql
Exceedingly simple, but incredibly useful, and something that I was going to
have to write myself pretty soon - thanks.
John.
|||I knew there had to be an easier way. I was scripting 35 scripts at a
time. Between opening and closing files and the occasional, "Didn't I
run that already?" I knew it was time to find a better way.
Glad to help,
Joe
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Friday, February 17, 2012

Execute Multiple Scripts from one directory

I got tired of having to execute multiple scripts one at a time from
my testing server to my production server. So I decided to create a
batch routine that utilizes the command line routine OSQL. All you
have to do to use it is place the batch file in a directory containing
your SQL scripts and execute the batch file. I have placed in the
command the argument to print a result file for each sql script. If
you need your SQL scripts run in a particular order simply rename them
accordingly. i.e. 001_updatecustomer.sql, 002_update_sp.sql
enjoy
REM #############################################
REM Author: Joe Ocampo
REM Date: 04/17/2004
REM Version: 1.0.1
REM #############################################
REM You must execute the script from the SQL server itself
REM and you must use the SA login or the DBO login of the
REM target database.
REM #############################################
SET login=sa
SET password=password
SET server=local
SET database=pubs
for %%a in (*.sql) do osql -d %database% -U %login% -P %password% -i
%%a -o %%a_result.txtJoe wrote:
> I got tired of having to execute multiple scripts one at a time from
> my testing server to my production server. So I decided to create a
> batch routine that utilizes the command line routine OSQL. All you
> have to do to use it is place the batch file in a directory containing
> your SQL scripts and execute the batch file. I have placed in the
> command the argument to print a result file for each sql script. If
> you need your SQL scripts run in a particular order simply rename them
> accordingly. i.e. 001_updatecustomer.sql, 002_update_sp.sql
Exceedingly simple, but incredibly useful, and something that I was going to
have to write myself pretty soon - thanks.
John.

Execute msdb.dbo.sp_sqlagent_get_perf_counter

Hello:
Need some help. I do not recall setting up to run
msdb.dbo.sp_sqlagent_get_perf_counter
But it is running and taking up quite a bit of CPU time, about 60%, in the
server. Would it be ok if I kill it. Any benefit in running it?
I do not see it in the Jobs list, where is it set?
Thanks,
Q
If you haven't defined any alerts, it is from the Demo
alerts installed by default. You can delete them if you
aren't using them.
The stored procedure checks performance conditions for
alerts. It won't run if you don't have any alerts defined.
If you do have alerts, it runs at a default of every 20
seconds which is in the registry under
HKLM\Software\Microsoft\Microsoft SQL
Server\YourInstance\SQLServerAgent.
Under SQLServerAgent, the value for
PerformanceSamplingInterval is set in seconds. Setting it to
0 will disable this entirely if you aren't using any such
alerts.
-Sue
On Wed, 14 Dec 2005 17:46:02 -0800, "Q"
<Q@.discussions.microsoft.com> wrote:

>Hello:
>Need some help. I do not recall setting up to run
>msdb.dbo.sp_sqlagent_get_perf_counter
>But it is running and taking up quite a bit of CPU time, about 60%, in the
>server. Would it be ok if I kill it. Any benefit in running it?
>I do not see it in the Jobs list, where is it set?
>Thanks,
>Q

Execute msdb.dbo.sp_sqlagent_get_perf_counter

Hello:
Need some help. I do not recall setting up to run
msdb.dbo.sp_sqlagent_get_perf_counter
But it is running and taking up quite a bit of CPU time, about 60%, in the
server. Would it be ok if I kill it. Any benefit in running it?
I do not see it in the Jobs list, where is it set?
Thanks,
QIf you haven't defined any alerts, it is from the Demo
alerts installed by default. You can delete them if you
aren't using them.
The stored procedure checks performance conditions for
alerts. It won't run if you don't have any alerts defined.
If you do have alerts, it runs at a default of every 20
seconds which is in the registry under
HKLM\Software\Microsoft\Microsoft SQL
Server\YourInstance\SQLServerAgent.
Under SQLServerAgent, the value for
PerformanceSamplingInterval is set in seconds. Setting it to
0 will disable this entirely if you aren't using any such
alerts.
-Sue
On Wed, 14 Dec 2005 17:46:02 -0800, "Q"
<Q@.discussions.microsoft.com> wrote:

>Hello:
>Need some help. I do not recall setting up to run
>msdb.dbo.sp_sqlagent_get_perf_counter
>But it is running and taking up quite a bit of CPU time, about 60%, in the
>server. Would it be ok if I kill it. Any benefit in running it?
>I do not see it in the Jobs list, where is it set?
>Thanks,
>Q

Wednesday, February 15, 2012

Execute DTS 2000 Package Task. Mischievous task?

Hi everyone,

For first time I'm testing this task and surprisingly, when I try "Edit Package" option:

1)The DTS host failed to load or save the package properly

2)The selected package cannot be opened

3)Error HRESULT E_FAIL has been returned from a call to a COM component

But after these messages you can see all the tasks but they haven't name!!

It seem as if RCW mechanism has failed between managed and unmanaged coded-partially.

I don't dare to follow doing more stuff, I don't know if that package is well-loaded or not from there. ??

Any guidance or idea about this?

Jamie? Darren?|||Do you mean that you are trying to open the DTS package from Management Studio via the Management->Legacy folder?

If so, I had the same problem opening DTS packages even though I had the SQL2005_DTS.msi components installed.

When you open the package, you will get the 3 error messages and then the DTS Designer window will display the DTS package's tasks without names or correct icons. I've found that saving the opened package under a new DTS package : and then opening the new package will remove the error messages. You will be able to open the tasks and modify them, I still have to test if the new saved package works correctly. I will give you an update on this soon.

|||

Enric,

This is already answered here http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=124252&SiteID=1. We have to try this fix: http://support.microsoft.com/kb/917406/en-us

In my case, it's an x64 machine, the above fix doesnot work.. Anybody who has fixed this on x64 ?

Thanks

Subhash Subramanyam

|||

Hi Friends,

I got it working on the Method 2 posted here, but I wonder if it is a must to Install Enterprise Manager Client tools for an X64 to get rid of these errors.

Thanks

Subhash Subramanyam