Execute Proc On Stopping Mssql Server
Jul 23, 2005How execute extended proc on stopping ms sql server (it mean before
stoping).
Is it possible to handle this?
Or is in system proc a'la sp_on_stop?
Thx
ADVERTISEMENT
SQL 2012 :: Use SSIS Or Stored Proc Or Combination To Handle Stopping A Process On Failure
Apr 7, 2015A project I'm working on consists of a Main stored procedure which then runs about 30 nested procedures. The client wants to know when a certain nested SP fails, but wihtout rollbacks, as they may want to fix a data item manually (such as a missing Patient ID, that they have to call someone about). At this point, we don't want to roll back anything but halt the rest of the nested SP's and send out an email to someone that they have to check out a missing PatientID.
I'm wondering if an SSIS package would handle this better than just using a Stored Procedure. When that SP runs, it will also update a "Process tracking" table in the backend, that would update [Lastprocessran] with a number. I'm thinking that if they run the main SP again, after making a manual correction, that they could re-run the main SP, and it would bypass any step that already ran successfully based upon the [Lastprocessrun] number.
SQL Server 2012 :: Run DBCC Inside Stored Proc Using EXECUTE AS
Mar 18, 2014I am a SysAdmin on a test/dev SQL instance. Other non-sysadmin users (developers) need the ability to execute DBCC commands like the following:
DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL
OR
DBCC FREEPROCCACHE
I tried creating a store proc in a user database and granting those non-sysadmin users EXECUTE permission on it as so:
CREATE PROC spFreeSystemCache
WITH EXECUTE AS 'sa'
AS
DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL
GO
When I try to create this proc, I get the following error:
Msg 102, Level 15, State 1, Procedure spFreeSystemCache, Line 2
Incorrect syntax near 'sa'.
Ok, so I can't EXECUTE AS sa...
Execute Stored Procedure Y Asynchronously From Stored Proc X Using SQL Server 2000
Oct 14, 2007I am calling a stored procedure (say X) and from that stored procedure (i mean X) i want to call another stored procedure (say Y)asynchoronoulsy. Once stored procedure X is completed then i want to return execution to main program. In background, Stored procedure Y will contiue his work. Please let me know how to do that using SQL Server 2000 and ASP.NET 2.
View 3 Replies View RelatedDebug Stored Proc's In MSSQL 2005
Mar 15, 2007In SQL Server 2000's Query Analyzer, you can debug a stored procedure by right-clicking a SP and select "Debug".
You can then step through the SP one line at a time.I don't see this in SQL Server 2005 and have searched bu cannot find any documentation on what happened to this feature.What did Microsoft do with this?
Debug Stored Proc's In MSSQL 2005
Mar 15, 2007In SQL Server 2000's Query Analyzer, you can debug a stored procedure by right-clicking a SP and select "Debug".
You can then step through the SP one line at a time.
I don't see this in SQL Server 2005 and have searched but cannot find any documentation on what happened to this feature.
What did Microsoft do with this?
Execute MDX From SQL Proc
Sep 5, 2004--------------------------------------------------------------------------------
Can someone shed some light??? I have searched hi and low for an answer to my dilemma. I have an mdx query that works fine in my olap environment but when I try to run the same query from sql 2000, one of my calculations comes back as a long int and includes (E-02) at the end. Can you please review my store procedure and provide some insight?
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE GetWeeklyTrendData
@Level int,
@year int,
@week int,
@PrintMDX int = 0
AS
SET NOCOUNT ON
DECLARE @MDX varchar(4000),
@CubeQuery varchar(4500), @Leaf int, @Tier int
BEGIN
SET @MDX = '''
WITH
MEMBER [Measures].[OrgName] As ''''[GL].CurrentMember.Name''''
MEMBER [Measures].[LevelSKey] As ''''[GL].CurrentMember.Properties("Key")''''
MEMBER [Measures].[OrgType] As ''''Iif(IsLeaf([GL].CurrentMember), 1, 0)''''
MEMBER Measures.Lookp AS '''' LookupCube("cubeName", "(Measures.Customers, " + [GL].CurrentMember.UniqueName + "," + [Week].CurrentMember.UniqueName + ")" )''''
MEMBER Measures.Trend AS '''' Measures.Customers / LookupCube("cubeName", "(Measures.Customers, " + [GL].CurrentMember.UniqueName + "," + [Week].CurrentMember.UniqueName + ")" )''''
, SOLVE_ORDER = 1, FORMAT_STRING = ''''Percent''''
SELECT
{
CrossJoin
(
{ [Week].[' + CAST(@year AS VARCHAR) + '].[Week ' + CAST(@week AS VARCHAR)+ '].Lag(12) : [Week].[' + CAST(@year AS VARCHAR) + '].[Week ' + CAST(@week AS VARCHAR) + '] },
{ Measures.OrgName, Measures.LevelSKey, Measures.OrgType, Measures.Customers, Measures.Lookp , Measures.Trend
}
) } ON COLUMNS,
NON EMPTY
{ [GL].&[' + CAST (@Level AS VARCHAR) + '].Children } ON Rows
FROM [cubeName]
WHERE
( [Monthly Income].[All Monthly Income].[Y],[Contact Events].[All Contact Events].[Y],
[Other Financial].[All Other Financial].[Y],[Bankers Notes].[All Bankers Notes].[Y],
[Employer Name].[All Employer Name].[Y] )
'''
END
SELECT @CubeQuery =
'
SELECT *
FROM OPENQUERY(ReportDBMart, ' + @MDX + ')'
IF @PrintMDX = 1
PRINT 'MDX: ' + @MDX
EXEC(@CubeQuery)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
The following is the value that I get back.
9.4993517502742597E-2 and is derived from Measures.Trend.
My guess is that it doesn’t recognize FORMAT_STRING = ''''Percent'''' or its trying to return a numeric value inside a varchar. Any thoughts???
Trigger To Execute A Proc
Jul 20, 2015I wanted to execute a proc whenever a new login or a login is droped is created on the server. I was trying for CREATE_LOGIN in the following code but not able to capture the loginname and pass it to the proc.
Server object Trigger:-
USE [master]
GO
/****** Object: DdlTrigger [LoginCreateTrigger] Script Date: 7/20/2015 3:41:06 AM ******/
SET ANSI_NULLS ON
GO
[code]...
[mssql][ant] Failed To Execute: GO
Jul 23, 2005Hi,I am trying to execute a simple sql file thru ant task. The file is:use [centivaTest]GOHowever I get the errror message:[sql] Failed to execute: use [centivaTest] GOSo I changed the file to:use [centivaTest] ;GOas ant treats ';' as a delimiter. However another erro messageappears:[mssql][ant] Failed to execute: GODo you know how to execute my simple sql statements thru ant (orgenerally what wrong am I doing)?Best regards,Maciek Zywno
View 1 Replies View RelatedExecute Stored Proc And Then Return A Value
Jul 13, 2004ok I have a stored procedure in my MS-SQL Server database.
It looks something like this.....
CREATE PROCEDURE updatePCPartsList
(
@Descriptionvarchar(255),
@ManCodevarchar(255),
@ProdCodevarchar(255),
@Pricedecimal(6,2),
@Commentsvarchar(255)
)
AS
declare @IDFound bigint
declare @LastChangedDate datetime
select @LastChangedDate = GetDate()
select @IDFound = PK_ID from PCPartsList where ProdCode = @ProdCode
if @IDFound > 0
begin
update PCPartsList set Description = @Description, ManCode = @ManCode, ProdCode = @ProdCode, Price = @Price, Comments = @Comments, LastChanged = @LastChangedDate where PK_ID = @IDFound
end
else
insert into PCPartsList (Description, ManCode, ProdCode, Price, Comments, LastChanged) values(@Description, @ManCode, @ProdCode, @Price, @Comments, @LastChangedDate)
GO
It executes fine so I know i've done that much right....
But what i'd like to know is how I can then return a value - specifically @LastDateChanged variable
I think this is a case of i've done the hard part but i'm stuck on the simple part - but i'm very slowly dragging my way through learning SQL.
Someone help?
Need Help Executing A Proc In Execute SQL Task
Jan 12, 2008Hello,
I am trying to use an Execute SQL Task to execute a stored procedure with parameters in SSIS but it keeps giving me errors, for example I type in 'exec someproc ?, ?, ?' in SQL Task it wouldnt parse correctly and when I set the bypassPrepare to 'true' the package runs but gives me error at the destination saying couldnt find stored procedure 'someProc'.
Does anybody know how I can solve this problem?
Also how do I pass the results of the queries in the Execute SQL Task into the Data Flow Task? The proc is supposed to retrieve data and transform them into XML format to be placed at a XML file. I am not sure if I should used an OLEDB source or an XML source since the data has been transformed into XML. Any help will be greatly appreciated.
Please note proc runs fine when it does not have any parameters.
Is It Possible To Execute Package Through Stored Proc
Jul 7, 2006is there a way to execute SSIS Package through stored proceedure.
Or any other method of executing the SSIS Package command line in stored proceedure
Thanks,
jas
Execute Stored Proc From OLE DB Destination
Apr 19, 2007
is is posible to execute a stored proc (with parameters) from an OLE DB Destination ??
reason we are trying this is cos
our current setup is an OLE DB Command doing the first database update and then passing over to an OLE DB Destination that does the second update. There is error handling coming off the OLE DB Command to a Script component that passes to an OLE DB Destination.
we are having a problem getting the error reporting working from the OLE DB Command - the updates work fine - but not getting any updates of the error database when there is an error.
have got the error reporting fine on the OLE DB Destination.
if we can execute the stored proc from the OLE DB Destination, we will then do both updates via one stored procedure executed by the OLE DB Destination.
thx
m
n.b. think the updating of the error logs from the OLE DB Command used to work - but cant get it to work now ??!!!!??
Execute Multiple SQL Statements In Stored Proc
Nov 1, 2005Hi, I have a table containing SQl statements. I need to extract the statements and execute them through stored procedure(have any better ideas?)
Table Test
Id Description
1Insert into test(Id,Name) Values (1,'Ron')
2Update Test Set Name = 'Robert' where Id = 1
3Delete from Test where Id = 1
In my stored procedure, i want to execute the above statements in the order they were inserted into the table. Can Someone shed some light on how to execute multiple sql statements in a stored procedure. Thanks
Reo
Execute Stored Proc On A Named Instance
Jun 2, 2006I think I'm being a bit thick, but I just cannot figure out the proper syntax to call a stored proc on a SQL named instance I have. I've tried many variations, but here is the basic format of what I'm trying:
EXEC ServerInstance.DB.dbo.usp_fm_proc 1
It seems it doesn't like the as I get an error "Incorrect syntax near 'Instance'.
What am I missing here?
HOW TO EXECUTE THE PACKAGE IN STORED PROC PLZZZZZZZZZZZZZZZZZZZZZZZZZ
Aug 6, 2007My Package Exist in
C:WROXWROXinexcel_database package exist
OR sql server agent ->job-> excel_database package exist
now i want to excute the from master database how please send the
stored procedure code
Regards
koti
Invalid Character Value For Cast Specification From VC++ When I Execute A Proc
Apr 21, 2006Hi Everybody,
I have a question regarding the running of a procedure from VC++ in SQL server.
well this is the scenario.
i have a procedure in SQL server and i am running that thru my VC++ code.
:eek:
i have a recordset class and i create an object of it and then do a obj.Open(). the procedure is fetching 24 records correctly in the query analyser and in my while(!(obj.IsEOF())) loop it gets the first record correctly but the moment it encounters obj.MoveNext(); it bombs and gives me the following error..
"Invalid Character value for cast specification ".
i don't know why this is happening
Thanks for you help.
Execute Stored Proc Using A Proxy Account In 2005
Sep 18, 2007Hi all,
I have a problem while i create a proxy account in SQL Sever 2005.The situation is like this...There is a user who has an login in to the server.He has a stored procedure which calls some on the SSIS packages and XP_cmdshell...so this stored procedure basically load some data in to the tables .So for the login in order to execute the stored procedure as he is not a Sys admin I have created a proxy account in my account as Iam an SA and then in the proxies and in principals I selected his login name and this way I have created a credential and a proxy account.
Now the problem is if he logins with his id and password and try to execute the stored procedure it gives an error message
Server: Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1
The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information.
....so this mean the login is not able to see the proxy account.So what I did is I created a job and then in the job owner tab I have selected his login and then created a step with the type operating system (CmdExec) as I need to just execute the stored procedure and used the proxy account that I have created.
so I gave the command -- exec <stored procedure> --.
But this job fails and gives the error message as
[298] SQLServer Error: 536, Invalid length parameter passed to the SUBSTRING function. [SQLSTATE 42000]....
So now ....first My question is am I doing in a right way....if its right then why Iam not able to execute the stored procedure.
If there is any other way through which I can execute the stored procedure using a proxy account for the logins who are not sys admins....please do let me know.....
Thanks
Raja.V
Can't Pass In GUID When Using Execute Stored Proc In VS 2008
Apr 8, 2008I am trying to test a stored proc and I can't execute the stored proc when I use a Guid. Has anyboyd had the same problem. I right click in VS 2008 on the Stored proc click Execute and fill in the variables one of them is a Guid and no records are returned.
Here is my guid, I have tried '{0a5c25fa-3aac-4abc-8d07-6d76ab46c9b2}', {0a5c25fa-3aac-4abc-8d07-6d76ab46c9b2} and "0a5c25fa-3aac-4abc-8d07-6d76ab46c9b2" no success. This stored proc might not work but I have had the same issue with other stored procs when I wanted to test them by using execute and using a Guid.
Testing with an int works fine when I try to Select the records by UserID which is a Guid no cigar. I have notice this behavier before is there a way around this problem, thanks newbie
Execute Dynamic SQL Stored Proc Without Specifying Table Level Permissions
Sep 17, 2007I am writing a SQL 2000 stored procedure which uses an €˜EXEC @sqlString€™ statement. The @sqlString is generated at runtime. I want to give as few permissions as possible and currently allow users to access the database tables using only the stored procedures provided. However, with €˜Exec€™ I discover that I need to grant permissions on the actual tables to the users or groups. I would like to avoid this. I would also prefer not having to maintain a separate user with table level permissions and hardcoding the stored procedure with these details.
Is there anyway for me to dynamically generate the required SQL statement within my stored procedure and let SQL know that this stored procedure is allowed to select whatever tables it wants to without having to define permissions on the tables?
Not Able To Create Hash Table Inside Stored Proc Through Execute Sp_executesql @strQuery
Aug 21, 2007Hello,
i need to create temporary table inside SP.
i having one string variable @strQuery which contain dynamic query inside SP.
i am executing that trhough execute sp_executesql @strQuery once query build.
now instead of select query , i want to creat hash table.
so i wrote :
set @strQuery = "Select * into #tmp_tbl from table_name..."
when i tried to execute it through
execute sp_executesql @strQuery , its giving error 'Invalid object name '#tmp_tbl'
If i removed Hash then it works fine. even for double Hash also its work fine.
but i want hash table only as i want that table local to that user.
Even direct execution of select statement without @strQuery works fine. but i want to execute @strQuery through execute sp_executesql @strQuery only as query is dynamic .
please guide me how to do this?
its very urgent for me.
thanks in advance.
Stopping SQL Server Services
Dec 13, 2001Anyone got any "gotchas" or "nightmares" they want to share out of experience where the services have been shutdown without the proper forethought?
I'm trying to put a case in for a SQL DBA to be paged and to be in charge of shutting down the services and/or put together a checklist of things to verify first.
While I realize shutting down the services is supposed to be a graceful shutdown process I'm willing to bet there are a host of considerations.
Appreciate any Input.
Regards
Server Stopping In Sudden
Oct 31, 2000hi,
i am having sql7 with sp2.
Recently our server got bounced back. No body stopped the server, there are no work load at that time. Server suddenly stopped and started.
It is weired.
Can anyone have any clues , how it happened and why it happened.
Thanks!
__kavira
SQL Server Agent Stopping
May 7, 2007I have a SQL Server agent that keeps stopping for no reason. It happens about every other day. I have to go in on the server and manually start it. What would cause this to happen? We have about 50+ computers that need to sync with the server and do so successfully without having to manually start the agent every other day. How do we fix it?
View 17 Replies View RelatedDetach/Attach DB VS Stopping SQL Server
Mar 21, 2002What problems could be invoked by stopping SQL Server and copying a user databases data and log files and pasting these files over existing files on another machine.
Ex:
MyDB on Server1
MyDB on Server2
Stop SQL Server Server1 and Server2
Copy Server1.MyDB files
Over write Server2.MyDB files
Start SQL Server Server1 and Server2
Make sense. This scenerio seems to work but wonder what side affects could result.
Any comments would be helpful!!
Stopping And Starting SQL Server Nightly
Feb 9, 1999Does anyone see any negative consequences to stopping and starting SQL Server nightly?
View 2 Replies View RelatedCopy Mdf Files Without Stopping Sql Server
Jan 29, 2006hi.
how can I copy any mdf file to another folder without stopping sql server?
Server Configuration For MSSQL 2000 And MSSQL 2005
Sep 6, 2006Does enabling/disabling Data Execution Prevention have a performanceimpact on SQL 2000 or SQL 2005?For SQL best performance - how should I configure for:Processor Scheduling:Programs or Background servicesMemory Usage:Programs or System Cache
View 9 Replies View RelatedStarting && Stopping SQL Server From Command Line
Nov 10, 2005Hello All...
I am new to this forum (and new to SQL for that matter), so I hope this isn't a stupid question. I tried searching for the answer but couldn't find it here.
I am trying to start & stop SQL server through the command line so I can start & stop it as a scheduled task. Are there any switches for sqlmangr.exe I can use? Maybe another way?
Thanks....Dave S. <majordis@weco-group.com>
Report Server Service Stopping Unexpectedly
Jul 12, 2007we run ssrs 2005 sp2 hotfix applied - how do we troubleshoot a problem where the report server service is stopping unexpectedly several times a day. When this happens please tell me what happens to the reports that are currently running. I believe that
1) a current subscription that is running will fail - not sure if it will try to restart itself 3 times or not
2) connection to the report server db cannot be made when that occurs
this is a huge problem for us and we dont know what is causing it or how to fix it. Any suggestions would help. I can tell you that our customers flood the server with subscriptions
SQL Server Stopping While Configuring OLEDB Connection
Jan 23, 2007When I configure and test and OLEDB connection SQL Server 2005 keeps stopping with an error message as shown below. The problem seems to happen when I open the connection for ediing in Data Flow Window. There is a consistent pattern of errors (in there Event Log) relating to the Evnet Log prior to this error occurring.
Any idea what the problem is?
I have to say that my experience with SSIS has not been very encouraging so far.
Event Type: Error
Event Source: MSSQL$SQL2005
Event Category: (2)
Event ID: 17311
Date: 23/01/2007
Time: 2:06:41 PM
Description:
SQL Server is terminating because of fatal exception c0000005. This error may be caused by an unhandled Win32 or C++ exception, or by an access violation encountered during exception handling. Check the SQL error log for any related stack dumps or messages. This exception forces SQL Server to shutdown. To recover from this error, restart the server (unless SQLAgent is configured to auto restart).
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 9f 43 00 00 10 00 00 00 ŸC......
0008: 14 00 00 00 53 00 59 00 ....S.Y.
0010: 44 00 2d 00 4c 00 2d 00 D.-.L.-.
0018: 30 00 31 00 33 00 32 00 0.1.3.2.
0020: 36 00 5c 00 53 00 51 00 6..S.Q.
0028: 4c 00 32 00 30 00 30 00 L.2.0.0.
0030: 35 00 00 00 11 00 00 00 5.......
0038: 45 00 78 00 61 00 6d 00 E.x.a.m.
0040: 70 00 6c 00 65 00 48 00 p.l.e.H.
0048: 69 00 73 00 74 00 6f 00 i.s.t.o.
0050: 72 00 69 00 61 00 6e 00 r.i.a.n.
0058: 00 00 ..
SQL Server Services Stopping After System Reboot (URGENT)
Jun 3, 2002Hello,
SQL Server 2000 services getting stoped automatically when system get reboot. When system reboot they started and after 5 minutes they were getting stoped.
I went through the event log file and found that an error is occuring in sql server with number 17148.
And I also observed that there is a file called "C : P r o g r a m F i l e s V E R I T A S B a c k u p E x e c N T b e n g i n e . e x e" is executing during the maintenance process,which is stopping the sql server services(agent and olap)
This problem is generating error in sql server with above mentioned number.
regards,
vnk.
Run Mssql 6.5 Client And Mssql 7.0 Server On Same Nt Workstaton?
Oct 26, 1999Hello:
I am currently work on mssql 6.5. On my workstation, I have mssql 6.5
cient software.
However, I would like to install mssql 7.0 server on my nt workstation
and work with it to become familiar with 7.0. Can I install mssql 7.0
server on my nt workstation? Can mssql 6.5 client coexist with mssql 7.0
on the same machine if they are in different directories?
Thanks.
David Spaisman