Stoping Sql Server Agent Using Store Procedures

Jul 10, 2007

Hi
all i need little help regarding stored procedure.
i have some job say ".one." that runs daily on sql server agent But i have some holiday schedule as well in database
suppose if a holiday come and i want to stop that job on sql server agent on that particular holiday.
can anybody help me writing it ..
how to do it by calling SQL server agent to stop and start with date in stored procedure.
Regards

View 8 Replies


ADVERTISEMENT

SQL Server Agent Stoping Automatically...

Mar 26, 2008



Hi all

I have a mainteenance Server,SQL Server 2000 Installed on that server,every day some time in mid-night Agent it self stoping

What may be the reason...where I have to find out reason for stop.


Any advice and Help is appreciated.


View 11 Replies View Related

Storing SQL Server Agent Logs In A Relational Store

May 29, 2006

Hi,I want to store the SQL Server Agent logs into a table. I was able toread SQL server errorlogs using sp_readerrorlog and store the data intoa table successfully.I want to do the same for SQL Agent logs. options?Thanks in advanceThyagu.

View 1 Replies View Related

Stoping And Starting SQL Server

Aug 12, 1998

Does anyone use PeopleSoft and SQL?
I am doing tape backups with Seagate.
Seagate hangs sometimes.
I am planning on writing a command to shut off SQL server then restart it.
i am having trouble with writing the .BAT File
so far:
--start of bat
NET STOP MSSQLServer
NET STOP SQLExecutive
<<call to backup software>> -- (my problem)
net start server
net start executive
-- end of bat
any help would be appreciated
thanks
marlene

View 1 Replies View Related

Stoping SQL Server 2000 Database

May 2, 2006

We need to Reboot the Windows Server (Windows 2003 Ent.). Let me know how to smoothly stop the SQL Server 2000 Database. What all services needs to be stoped.

Plz let me know the steps..

Thanks in advance,

View 1 Replies View Related

Calling DB2 Store Procedures From SQL Server 2000

Feb 5, 2004

I am trying to call DB2 stroe procedure from within SQL server 2000 using DTS. I have the IBM odbc driver installed on the server. I have created an ACtiveX script to run in DTS and it fails staing it could not findor load the DB2 store procedure.

Has anyone come across doing this and how they did it?

THanks for the help....

View 4 Replies View Related

SQL Server 2008 :: List Store Procedures Contents?

Jul 27, 2015

who i can to list the content of all store procedures and funtions in text file ?

View 3 Replies View Related

Using Sql Server Agent Stored Procedures To Execute A Package

Aug 15, 2006

hi everyone,

awhile back someone (i think it was darren) submitted a post which had a code example of using sql server agent stored procedures to execute a package.  i searched for the post, but i didn't find it.

anyway, can someone please re-post that example or provide a similar one?

thanks.

View 7 Replies View Related

Script To Export Tables,store Procedures And Data In SQL Server Management Studio Expres

Nov 23, 2006

how i can do a script to export tables,store procedures and data in SQL Server Management Studio Expres

I just arrive to do a script for tables and stores procedures



help please

View 3 Replies View Related

Store Procedures

Jan 27, 2004

hi everyone :)

I've got a store procedure(sp) on the database side ( MS SQL 2000 )

and want to access the sp via ASP.net

so simply I have got few textboxs, which I am getting the values from and I need to pass this data to my sp and get an output of a string from my sp.

I've tested the SP and it does work fine

can you just give me a template of how to access the sp from ASP.net


mant thanks indeed

M

View 2 Replies View Related

Store Procedures For DBA

Mar 16, 2008

Can anyone plz tell me what kind of store procedures DBA's needs to write or DBA needs to know?

View 2 Replies View Related

Store Procedures

Jan 11, 2006

Hi:Does anyone know if IBM (or any other IT Software company) offers storeprocedure classes anywhere in the country?ThanksMarc

View 2 Replies View Related

Store Procedures Return

Apr 28, 2000

Hi guys, I want to know if it is possible to retrieve the fieldnames that a
Stored Procedure return, without executing this StoreProc.

Thank you

View 1 Replies View Related

TSQL And ASP - Store Procedures Help!!!!!!

Dec 1, 2000

All,
I have beating my head over this and have asked questions to clear this up in small bits.

In trying to get all of my business processes in stored procedures versus in the ASP pages. The ASP page is as follows:


--Normal ASP header
<%
myquery = "Exec MainSysLogQuery '6ED178C0-0202-4F8D-9C04-4C7B83A14190'

'Set Conn = Server.CreateObject("ADODB.Connection")'Set Connection Variable
Set SysMainQuery=Server.CreateObject("ADODB.Recordset" )
SysMainQuery.open "Select * from tbl_Date", strDSNPath

howmanyfields=SysMainQuery.fields.count -1

response.write "<table border='1'><tr>"

'Put Headings On The Table of Field Names
FOR i=0 to howmanyfields
response.write "<td NOWRAP><Font Size=2><b>" & SysMainQuery(i).name & "</b></td>"
NEXT
response.write "</tr>"

' Now loop through the data
DO WHILE NOT SysMainQuery.eof
response.write "<tr>"
FOR i = 0 to howmanyfields
fieldvalue=SysMainQuery(i)
If isnull(fieldvalue) THEN
fieldvalue="n/a"
END IF
If trim(fieldvalue)="" THEN
fieldvalue="&nbsp;"
END IF
response.write "<td valign='top' NOWRAP><Font Size=2>"
response.write fieldvalue
response.write "</td>"
next
response.write "</tr>"
SysMainQuery.movenext
'howmanyrecs=howmanyrecs+1
LOOP
response.write "</table></font><p>"

' close, destroy
SysMainQuery.close
set SysMainQuery=nothing
%>
</body></html>


The stored procedure takes the input an returns the sql statement:

Alter Procedure MainSysLogQuery
@myDates nvarchar(200) = '',
As
declare @oSql nvarchar(4000)
declare @viewtmp nvarchar(3000)
declare @querytmp nvarchar(3000)
declare @wheretmp nvarchar(3000)
declare @ordertmp nvarchar(3000)
declare @tmplen int

Set @wheretmp = ''
Set @tmplen = 0

if @myDates != ''
Set @wheretmp = @wheretmp + 'tbl_All_SysDATA.dateid = ''' + @myDates +''''

If len(@wheretmp)>0
Set @wheretmp = ' WHERE ' + @wheretmp

print @wheretmp
Set @viewtmp = 'SELECT *'
Set @querytmp = ' FROM tbl_All_SysData'
Set @ordertmp = ' ORDER BY LongDate DESC'
Set @oSQL = @viewtmp + @querytmp + @wheretmp + @ordertmp
PRINT @oSQL
Exec(@oSQL)
Go


The problem is I keep getting:
Error Type:
ADODB.Recordset (0x800A0E78)
Operation is not allowed when the object is closed.

It returns the recordset when I call it from SQL Query Tool but not in the ADO object. If I use a regular SQL statement it works get but not with a Stored Procedure....
The Errors are on on the recordset.eof, recordset.movenext, etc....

Has anyone ever run into this?
Thanks
~Lee

View 2 Replies View Related

SQL 6.5 Security For Store Procedures

Sep 30, 1999

How would you set up a group of developers-application programers in SQL 6.5 to let them have authority so that they all can store, update, delete, & execute each others stored procedures, within a particular database.
They are not permitted to modify the table structures within a data base, but I can not seem to let them have authority so that
they can work on and execute any of their sp's unless the DBO actually does the sp modifications? They do not want to modify any code by putting the sp owners name in front of the sp name (I don't blame them), otherwise Error 2812 results.

TIA for all you responses

View 1 Replies View Related

Store Procedures &&amp; Functions

Feb 10, 2006

hi,

what is the different between store procedure and function?

when it is right to use sp and when functions?

thanks in advanced

View 3 Replies View Related

Create Another Tempdb Without Stoping SQL

Oct 22, 2007

Hi to all

In sql server there is a query that is making tempdb full.
What I want is resoling this problem but without stoping sql server?

Thanks in advance,

kristinaRR

View 6 Replies View Related

Search And Replace Within Store Procedures

Nov 29, 2004

I am developing a complex database-driven application with SQL Server 2000. My database has dozens of stored procedures, and whenever I want to rename a database field, I have to go through my stored procedures, finding where that field is used. This is a laborious and error-prone process, even when I look up depenencies.

Is there an easier way to work stored procedure code – some tool to search/replace the text perhaps?

View 1 Replies View Related

Speeding Up Store Procedures Using EXEC?

May 26, 2004

Hello, can anyone offer any advice on this problem related to store procedures.

The following 2 chunks of SQL illustrate the problem

--1
declare @lsFilt varchar(16)
select @lsFilt = 'fil%ter'
select * from sysobjects where name like @lsFilt

--2
declare @lsQuery varchar(128)
select @lsQuery = 'select * from sysobjects where name like ''fil%ter'''
exec (@lsQuery)

When I view the execution plan the cost % breakdown is approx 82%, 18%. The second query does a bookmark lookup and an index seek while the first slow query does a clustered index seek and takes approx 5 times longer to do.


Now my real question is suppose I have an store procedure to run a similar query. Should be writing my SPs along the lines of

create proc SP2Style
@psFilter varchar(16)
AS
declare @lsQuery varchar(128)
select @lsQuery = 'select * from sysobjects where name like ''' @psFilter + ''''
exec (@lsQuery)
GO

instead of

create proc SP1Style
@psFilter varchar(16)
AS
select * from sysobjects where name like @psFilter
GO

Is there another way to write similar store procedures without using dynamic query building or the exec but keep the faster execution speed?

thanks

Paul

View 2 Replies View Related

How To Give The Grant On Store Procedures

Dec 19, 2007

hello i am new to sql server 2000.

i am one of the user of database and in that user i created store procedures.

now i want to create another user and give that user to permission of excute all my proceudres and i also give him privillages such a way that user can also modify my procedures...

so can u assist me on that...

thanks in advance...

View 5 Replies View Related

Can't Import Views And Store Procedures

Aug 6, 2007

I recently set up a new sql 2005 standard edition and planning to mirgrate our production sql 2000 data. but only the tables were migrated from the copy data or import / export task. I cannot find a way to recreate the store procedures and views on the new server without create one view and one store procedure at a time unless all hundreds of views and procedures were rescripted. can anyone help

Thank

Andy Wong
awong@virginiadare.com

View 3 Replies View Related

Temp Table In Store Procedures

Jan 25, 2008

Can any one please tell me where i am going wrong..




Code Snippet
create proc SP_PercentageRMU
as
SELECT cast (sum([Usage Qty]) as [decimal] (28,8))as 'TUsageQty'
,RAW_MATERIAL
into #TZMelt_Pound
FROM [LatrobeOCT].[dbo].[ZMelt_Pound]

group by RAW_MATERIAL
GO
select [Usage Qty],(case when r.raw_material = z.raw_material
then cast ((r.[usage QTY] / z.TUsageQty) as decimal (28,8))
else 0
end) as '%UsageQty'
,r.[PRODL]

,r.RAW_MATERIAL
,r.[GRADECODE]
into #PZMelt_Pound
FROM [LatrobeOCT].[dbo].[ZMelt_Pound]r
inner join #TZMelt_Pound z on r.raw_material = z.raw_material

drop table #TZMelt_Pound
go






error

Msg 208, Level 16, State 0, Line 2

Invalid object name '#TZMelt_Pound'.

View 6 Replies View Related

SQL 2012 :: Find Out Which Store Procedures Fired

Apr 16, 2014

Our company purchased a app. Is there any way to find out which store procedures were fired once a button was clicked?

View 3 Replies View Related

SQL 2012 :: How To Purge Old Store Procedures From Clients

Jul 11, 2014

I do have very old versions of duplicate store procedures on my databases. I know there is no "safe" way to do this using DMVs, so I am planning to combine that with a trace. But I would like to get others opinions about that.

Here's the DMV I am planning to use:

SELECT
CASE WHEN database_id = 32767 then 'Resource' ELSE DB_NAME(database_id)END AS DBName
,OBJECT_SCHEMA_NAME(object_id,database_id) AS [SCHEMA_NAME]
,OBJECT_NAME(object_id,database_id)AS [OBJECT_NAME]
,cached_time
,last_execution_time
,execution_count

[Code] ....

I will save that on a local table and run it every 5 min maybe? Or at an interval equal or lower than PLE?

View 5 Replies View Related

Create Multiple Store Procedures In 1 SQL Statement

Nov 17, 2006

Hi guys , may I know is that possible to create multiple store procedures in 1 time using 1 SQL statement? Thx for the assistance.

Best Regards,

Hans

View 5 Replies View Related

Link 2 Store Procedures In 1 Crystal Report

May 20, 2015

In a report called ICD_PrivateHospital, I have designed to show output to two table from two store procedures. 1st store proc: usp_RPT_Private and 2nd store proc: usp_RPT_Private2.

I have created both the store procs and it is executing successfully. I also have designed the Crystal Report in Visual Studio 2008.

I have added both the store procs in the crystal report with no error. I only wants to show top 20 records. It is running successfully when I add the fields and parameters from the first store procedure. But when I add the 2nd store procedures fields into the report, duplication occurs for both the results in store proc 1 and store proc 2. How to solve this issue?

View 5 Replies View Related

Folder Underneath Programmability ==&&> Store Procedures

Aug 15, 2007

Hello there



I have several developers working on different systems but same database; these developers make Store Procedures and all utilize these SP€™s; at the moment our SP list grows and keeps about 200 + SP's, whereas I expect a lot more to come.



Now I want to organize the SP''s under certain folders; example by the System and underneath by User name so that traceability of the work can be easy as well as organization of the SP's will be achieved.



I cannot find any way how to customize the Store Procedure node; and create folder underneath.



I know the filter option helps for searching the SP's but we also have remote users who are working somewhere away from us; so I want to get their work only by filtering the folder of these user name and also sometimes I need to know who made these SP's so that that developer can fix or alter the procedures.



We may do this using security instead of dbo, the developer who is creating the procedure will carry his name; but this needs tight security and we are in the phase of testing and lot of changes occurs on daily basis which requires little loose security till we pass this phase.



Any Idea or suggestions to make it viewable and accessible and copy the work of the specific users right away; rather than searching his Sp's, with the coordination of the user or with the maintained documentation.

View 5 Replies View Related

Store Procedures: Pass Array Of Numbers For IN Clause?

May 24, 2006

How does one pass into a Stored Procedure an array of numbers to be used in an IN clause? If I pass "1,2" in a VARCHAR, the stored procedure sees only the first number (1 in this case).
I'm using VB and ADO.NET, but I don't know how to set up the stored procedure for an array. Is there a parsing function to do this?
CREATE PROCEDURE TestInClause( @TeamList VARCHAR)ASSELECT Name FROM Teams WHERE TeamID IN (@TeamList); /* sees only 1st number */GO

View 2 Replies View Related

Script To Store Stored Procedures In Seperate Files

Jul 20, 2005

How does MicroSoft store the stored procedures in seperate files.What tools are used.Cause that's what I like to do too.Arno de Jong,The Netherlands.(SCPTFXR does not have the option to store the stored procedures indifferent filesI think)

View 2 Replies View Related

Merge Agent System Stored Procedures And Sp_Recompile

Jul 13, 2006

Hello,

We are trying to be proactive and stop a potential performance issue by reducing the number of recompiles in our SQL 2000 database application. This database is replicated. After viewing output from Profiler and PerfMon it seems that over 90% of the recompiles are due to system stored procedures generated by replication merge agents. Can anything be done about this?

Thanks

View 3 Replies View Related

Use Of User Data Types For Creating Local Temporally Tables In Store Procedures

Aug 24, 2006


I am able to use user data types for creating local temporally tables in store procedures, but I receive an error at run-time about the user data type.


Msg 2715, Level 16, State 7, Procedure SP_SAMPLE_TEST, Line 4
Column, parameter, or variable #1: Cannot find data type D_ORDER_NUMBER.


The same user data type is used as parameters in several other store procedures, and they work perfect. It is also used in several table definitions.


What am I doing wrong?


Thanks in advance to any one who can help me with this problem.


Diego Sierra

View 4 Replies View Related

Execute Sql Server Agent Job Task - Job Immediately Returns Success... However Agent Job Is Still Running???

Nov 30, 2006

when I run a package from a command window using dtexec, the job immediately says success.
DTExec: The package execution returned DTSER_SUCCESS (0).
Started: 3:37:41 PM
Finished: 3:37:43 PM
Elapsed: 2.719 seconds



However the Job is still in th agent and the status is executing. The implications of this are not good. Is this how the sql server agent job task is supposed to work by design.



Thanks,

Larry

View 1 Replies View Related

SQL Server Agent Could Not Access Replication Agent

Feb 19, 2007

We just moved source server to newer, bigger box ... Windows 2003 and Active Directory ... Snapshot agent worked but distribution failed ... Same login as on older machine, login is sysadm, used DCOMCNFG to allow ability to launch process ... What are we missing?

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved