SQL Server Procedure Execution Status
Feb 18, 2008
I am running a SQL Server procedure from ADO in visual C++, sometimes I get return -6 as return code from procedure.
From SQL Server documentation:
"SQL Server currently uses return values 0 through -14 to indicate the execution status of stored procedures. Values from -15 through -99 are reserved for future use."
I cannot find a description for the returns codes from SQL Server for error conditions (from -1 to -14), does anyone here knows where can I find this information?
Best regards,
Pedro Chau
View 1 Replies
ADVERTISEMENT
Dec 2, 2005
Hi. When SqlServer executes a procedure (any type: select, update, insert) after it´s executed can I get a default status for this executed procedure, like a return bool value from SqlServer as true for successfull and false for failed to execute?
»»» Ken.A
View 6 Replies
View Related
Oct 30, 2007
Hi,
In command line how do i get status of Package Execution?
Thanks
View 1 Replies
View Related
Nov 23, 2005
Hi,Say, you have two inter-dependent packages of pA and pB. With thesuccessful execution of pA you would proceed to execute pB. And sincethey would perform some repeatitive tasks on regular basis they arescheduled as a job. But you don't want to the job to make attempt torun pB if you know pA has failed and you want to nofiy sys adm of it.And let's say pA has log option checked (so, there's a log file forit). Now, it would seem to me quite clumsy to check this potentiallyhuge file to see if a particular package (pA here) is successful beforerunning pB. Any other viable and clean solution?Yes, one could possibly combine pA and pB into one big package (pBIG),however, for the sake of a clean and clear business process this is notdesirable since pA and pB handlestotally different business processes.TIA.
View 8 Replies
View Related
Aug 14, 2006
All,
Is there a system variable to indicate a package run successfully or failed?
I have a task in Event Handlers to log the process into a table, but I still need extra information like execution status, more likely,
if package successfully executed, I want to get a specific values from SQL server (like in DTS, execution_status = 4 when success), Any suggestions are welcome.
Thanks in Advance
View 1 Replies
View Related
Aug 3, 2007
Hello :
How to execute a procedure stored during execution of the report, that is before the poster the data.
Thnak you.
View 4 Replies
View Related
Jun 17, 2015
Is it possible to check query execution plan of a store procedure from create script (before creating it)?
Basically the developers want to know how a newly developed procedure will perform in production environment. Now, I don't want to create it in production for just checking the execution plan. However they've provided SQL script for the procedure. Now wondering is there any way to look at the execution plan for this procedure from the script provided?
View 8 Replies
View Related
Jul 29, 2015
I am replicating a stored procedure execution, which builds and executes the following dynamic SQL command:
IF EXISTS (select * from MyDB..sysfiles sf (nolock) where name = 'MyDB_201201')
ALTER DATABASE [MyDB] REMOVE FILE [MyDB_201201]
IF EXISTS (select * from MyDB..sysfilegroups sfg (nolock)
where groupname = 'MyDB_201201' and sfg.groupname not in(
SELECT distinct fg.name file_group_name
[Code] ....
I can run this SP with no errors on both the publisher and the subscriber. However, when I try to replicate the execution of this SP, I get an error in replication monitor:
ALTER DATABASE statement not allowed within multi-statement transaction. (Source: MSSQLServer, Error number: 226)
How can I change my code to workaround this? Perhaps some explicit transactions?
View 6 Replies
View Related
Oct 9, 2006
I have the following execution of a global temporary stored procedure on a remote SQL 2000 server:
insert into targetTable
exec remoteServer.master.dbo.sp_MSforeachdb ' ', @precommand = 'exec ##up_fetchQuery'
This is an ugly duck query but it seems to work fine. when I try to directly execute the remote stored procedure such as with
insert into query_log exec remoteServer.master.dbo.##up_fetchQuery
I get execution error
Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure '##up_xportQueryLog'.
Database name 'master' ignored, referencing object in tempdb.
When I try
insert into query_log exec remoteServer.tempdb.dbo.##up_fetchQuery
I get
Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure '##up_xportQueryLog'.
Database name 'tempdb' ignored, referencing object in tempdb.
with
insert into query_log exec remoteServer..dbo.##up_fetchQuery
or
insert into query_log exec remoteServer...##up_fetchQuery
I get
Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure '##up_xportQueryLog'.
I guess the remote server has trouble resolving the name of the global temp stored procedure when its reference comes in as a remote stored procedure calls. Is there any way to directly call a global temp stored procedure from a remote server or do I need to stick with this goofy-looking work-around?
Dave
View 3 Replies
View Related
Oct 16, 2007
Hi ,
I ma using sql server 2005.I have a bunch of statements of sql and i have created a stored procedure for those. When i execute i found that there is lot's of difference between execution time of stored procedure and direct sql in query windows.
can anyone help me to optimize the execution time for stored prcedure even stored prcedure is very simple.
I have used sql server 2000 and i am new in sql server 2005.
View 1 Replies
View Related
Oct 15, 2014
I have created stored procedure to find out first word of the keyword. I am getting error below on execution:
"Must declare the scalar variable "@SubjectBeginning"."
View 9 Replies
View Related
May 23, 2007
Hello,
A call to a stored procedure via the SQLCommand.ExecuteReader() is made. When the stored procedure goes into suspend state the ExecuteReader() call returns. The stored procedure is not yet finished, after the suspend state it goes into the runnable state and performs some calculations.
Is it possible to set a configuration that the ExecuteReader doesn't return when the stored procedure goes into suspend, or must an other method be used?
Tx
View 2 Replies
View Related
Dec 18, 2003
How to check status(valid/invalid) for all stored procedure in my DB...
View 14 Replies
View Related
Mar 24, 2008
I have a stored procedure in SQL 2005 that purges data, and may take a few minutes to run. I'd like to report back to the client with status messages as the sp executes, using PRINT statements or something similar. I imagine something similar to BACKUP DATABASE, where it reports on percentage complete as the backup is executing.
I can't seem to find any information on how to do this. All posts on this subject state that it's not possible; that PRINT data is returned after the procedure executes. However it would seem possible since BACKUP DATABASE, for example, does this.
Is there any way to send status type messages to the client as the sp is executing??
Thanks.
View 6 Replies
View Related
Jan 8, 2007
Hi guys , may I know is there any examples of store procedure/scripts for monitoring replication status and performance ? I just know about sp_replmonitorhelppublisher. Thx for the assistance.
From,
Hans
View 1 Replies
View Related
Jun 11, 2015
Is there a way to keep track in real time on how long a stored procedure is running for? So what I want to do is fire off a trace in a stored procedure if that stored procedure is running for over like 5 minutes.
View 5 Replies
View Related
Nov 18, 2007
hi
how can i execute the stored procedure statements in asp with c#.net ?
View 2 Replies
View Related
May 9, 2006
Hi,
what are possiblities of tracing/ loginng execution steps inside of procedure WITHOUT modifiing code.
Example
Develper created procedure that run for 3 hrs , getting data
from different sources using openquery(db2 , sql servers, xml files), inside procedure 25-30 different statements
If we want to use profiler, what steps and filters to use in order to capture this procedure AND all steps inside procedure ?
View 1 Replies
View Related
Mar 31, 2006
Is there a way to automatically execute a procedure? For example to make one automatically execute every night at midnight or every hour.
View 4 Replies
View Related
Apr 24, 2008
Hi,
I am new to service broker but I heard that maybe can be useful for my needs.
I have to install my DB to many different machines with SQL Server 2005 express edition and I need a scheduled execution of a procedure (each night ay 4 AM). Due to the "express" I don't have the server agent and due to the kind of the application I can't use task scheduler in order to execute a command by sqlcmd.
So, service broker (included inside the DB instance, correct?!?) can helps me?
Thank you..
Luca
View 5 Replies
View Related
Jun 29, 2000
Is there a way that a stored procedure (or a SQL script) can be forced to execute
when a user logs on?
View 2 Replies
View Related
Jun 6, 2000
1) Is it possible to run stored procedures at specified intervals without
using the job system (through T-SQL)? I want the schedule to be
independent of the MSDB database in case of temporary failures, etc.
2) Would extended stored procedures be helpful in this scenario?
Thanks
ziggy
View 1 Replies
View Related
Jun 1, 2005
i have a stored procedure that builds a dynamic insert statement & inserts data into a table. Now when I execute the
sp manually with a 'exec sptest parm1,parm2', it runs fine & inserts the data in the table. But when this sp is called from within a .net application,it prepares the insert statement but does not actually insert the record in the table. It comes back with a RPC: Completed so it seems like it completed but it does not insert the record in the table. Also just after the RPC:Completed, it throws an ATTENTION with nothing in the text data. I am confused on whats going on here. The definition of ATtention
in the event class implies that the query has been cancelled or it timed out. But we have no timeout on the sql server side. The application developer says there is no timeout on the application side (i dont totally believe that). so what else could
cause that Attention? There is nothing in the error log as well. Also why does the trace come back with a RPC:Completed when the stored procedure did NOT insert any data? Does the RPC:Complete only mean that the RPC completed - irrespective of success or failure? If the sp failed or had an error will it still come back with a rpc:completed?
Any thoughts are appreciated...
View 2 Replies
View Related
Aug 10, 2007
hi
i want to know the execution status of a stored procedure . That is i want to know whether the stored procedure was executed succesfully or not.If not i want to get the error message
View 3 Replies
View Related
Mar 26, 2004
I am looking for a way to count the number of times a stored procedure on the database has been executed over let's say over a period of time(month, years, etc).
Is there a system stored procedure or a system table that stores that information.
I am struggling to find some information about this topic
Thanks for the help
View 1 Replies
View Related
Jul 23, 2005
Hi,There is a stored procedure which runs through job.It is calling to other stored procedure and other stored procedures arecalling to another .. so on (approx 12-15 sp in batch)Problem:Sometime it does not execute properly. (approx very rare... once in 500execution or sometime on new site/database)I want to know the reason for it.If anybody have faced the similar problem.Please tell the possible causes and possible solutions.Thanks in Adv.T.S.Negi(MIND)
View 1 Replies
View Related
Nov 15, 2006
hi guys
I am having problems running a stored procedure where i am using two input parameters
my stored procedure is as follows
ALTER procedure [dbo].[enterdhbnameDhbService]
(
@dhb_service char, @dhbname char
)
as
SELECT dbo.DHBMappingTable.[DHB Name], dbo.Agency.DHB_service, dbo.PurchaseUnitMappingTable.PU,
SUM(dbo.[NMDS Data for IDF Report].[Number of caseweighted discharges]) AS Expr1, dbo.AdmissionMappingTable.Admission
FROM dbo.DomicileCodes INNER JOIN
dbo.[NMDS Data for IDF Report] ON dbo.DomicileCodes.[Domicile code] = dbo.[NMDS Data for IDF Report].[Domicile Code] INNER JOIN
dbo.PurchaseUnitMappingTable ON dbo.[NMDS Data for IDF Report].[Purchase Unit] = dbo.PurchaseUnitMappingTable.PU INNER JOIN
dbo.AdmissionMappingTable ON
dbo.[NMDS Data for IDF Report].[Admission Type Description] = dbo.AdmissionMappingTable.[Admission Type Description] INNER JOIN
dbo.Agency ON dbo.[NMDS Data for IDF Report].[Agency Name] = dbo.Agency.Agengy INNER JOIN
dbo.DHBMappingTable ON dbo.DomicileCodes.[DHB area] = dbo.DHBMappingTable.[DHB Code]
WHERE (dbo.[NMDS Data for IDF Report].[Financial Year] = '20062007')
GROUP BY dbo.DHBMappingTable.[DHB Name], dbo.Agency.DHB_service, dbo.PurchaseUnitMappingTable.PU, dbo.AdmissionMappingTable.Admission
HAVING (dbo.Agency.DHB_service = @dhb_service) and
AND (dbo.DHBMappingTable.[DHB Name] = @dhbname )
The values of " @dhb_service" and "@dhbname" need to be entered when the stored procedure is executed. Now when I execute the stored procedure through the following statement:
exec enterdhbnameDhbService
@dhb_service = 'canterbury' ,@dhbname = 'south canterbury'
SQL does not give me any results, only empty table gets displayed. I have checked the combination.. This combination does exist in my table
pls help guys
View 3 Replies
View Related
Aug 17, 2007
Hi,
I created the an assembly and stored procedure using the following steps:
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
alter database TEST set trustworthy on
CREATE ASSEMBLY ClrWebServices
FROM 'D:Dataclr_4.dll'
WITH PERMISSION_SET = UNSAFE;
GO
CREATE ASSEMBLY [ClrWebServices.XmlSerializers]
FROM 'D:Dataclr_4.XmlSerializers.dll'
WITH PERMISSION_SET = SAFE;
GO
CREATE PROCEDURE InsertLocation(@city nvarchar(200),
@state nvarchar(200), @country nvarchar(200))
AS
EXTERNAL NAME ClrWebServices.StoredProcedures.clr_4
GO
After this when i am trying to execute the procedure InsertLocation
EXEC InsertLocation 'Sarasota','Florida','USA'
I am getting the following exception..
Msg 6522, Level 16, State 1, Procedure InsertLocation, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "InsertLocation":
System.Net.WebException: Unable to connect to the remote server ---> System.Net.Sockets.SocketException: No connection could be made because the target machine actively refused it
System.Net.Sockets.SocketException:
at System.Net.Sockets.Socket.DoConnect(EndPoint endPointSnapshot, SocketAddress socketAddress)
at System.Net.Sockets.Socket.InternalConnect(EndPoint remoteEP)
at System.Net.ServicePoint.ConnectSocketInternal(Boolean connectFailure, Socket s4, Socket s6, Socket& socket, IPAddress& address, ConnectSocketState state, IAsyncResult asyncResult, Int32 timeout, Exception& exception)
System.Net.WebException:
at System.Net.HttpWebRequest.GetRequestStream()
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
at ClrWebServices.Test.TerraService.GetPlaceFacts(Place place)
at StoredProcedures.GetLocationImage(SqlString city, SqlString state, SqlString country)
at StoredProcedures.clr_4(SqlString city, SqlString state, SqlString country)
I searched in the google and found one answer in http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=76222&SiteID=1 suggesting use
ALTER ASSEMBLY ClrWebServices WITH PERMISSION_SET=EXTERNAL_ACCESS
when i executed i got the exception
Msg 6213, Level 16, State 1, Line 1
ALTER ASSEMBLY failed because method "add_ConvertLonLatPtToNearestPlaceCompleted" on type "ClrWebServices.Test.TerraService" in external_access assembly "clr_4" has a synchronized attribute. Explicit synchronization is not allowed in external_access assemblies.
Can any body help on this?
Thanks in advance....
View 9 Replies
View Related
Jun 29, 2007
Hello,
I have a big problem with slow execution of stored procedure in SQL Server 2005 but I really don't understand the reason. I have a database with large table (about 400 million rows) and simple stored procedure to get data from that table (one select statement to select time and value columns).
Strange thing is that if I call that stored procedure from .net application (native SqlDataProvider) it takes about 6 seconds to execute but if I call the same procedure with the same parameters from within SQL Server Management Studio it takes only 25 milliseconds to execute!
I've noticed that from .net, procedure is called with binary data and in Management Studio sql script is executed so I've copied/pasted the script from Management Studio to .net code and again the same thing happens (6 seconds from .net and 25ms from Management Studio). I traced executions with SQL Profiler and everything seems to be identical for both applications except it takes much longer time for .net application.
Both SQL Server Management Studio and .net application are on the same machine and SQL Server is on another.
This is the query that when executed in Management Studio takes 25ms:
EXEC [dbo].[GetRawData] @pcu = N'DV_ZERJ_HEV1',@tag = N'MJERENO',@from = N'20070629 07:00:00',@to = N'20070629 08:00:00'
This is the same query in .net application code that takes 6 seconds to execute:
sqlCommand = new SqlCommand("EXEC [dbo].[GetRawData] @pcu = N'DV_ZERJ_HEV1',@tag = N'MJERENO',@from = N'20070629 07:00:00',@to = N'20070629 08:00:00'",sqlConnection);
sqlReader = sqlCommand.ExecuteReader();
At first I thought that Management Studio somehow caches results but if I change parameters of stored procedure it always takes less than 30ms to execute.
I really don't understand this. Please, help!
View 7 Replies
View Related
Jul 27, 2015
I want to simulate 100 users simultaneously executing a procedure in sql server (best example would be 100 users using one report with different parameters in ssrs at the same time ). can i do that ?
View 3 Replies
View Related
Dec 13, 2007
hi
i am using a query with procedure by passing sqlcondition and some parameters to the procedure .its giving full text operation failed or escape seqence not allowed,if i am using the same query individual its giving the result
ex:
Individual--its giving the correct result --as it contains special charecters
Select Top 17 DCNewsID,StoryTitle,Author,Source,Location,PubDate,PageNumber,PageName,
ArticleName,substring(StoryBody,1,100) as StoryBody,PageTitle from DCNews_Live where Contains(*,' "æ˜Ÿæ´²æ—¥å ±" ') And ( (convert(char(14),PubDate,112) >='20061109' and convert(char(14),PubDate,112) <='20061109')) and PublicationId ='632' order by DCNewsID
--from procedure execution ----the same query
exec ceps_advTop100 'Contains(*,' "æ˜Ÿæ´²æ—¥å ±" ') And ( (convert(char(14),PubDate,112) >="20061109" and convert(char(14),PubDate,112) <="20061109"))','0','0','0','632'
its giving error---Line 1: Incorrect syntax near ''.
i tried by removing the escape sequence also
exec ceps_advTop100 'Contains(*,"æ˜Ÿæ´²æ—¥å ±") And ( (convert(char(14),PubDate,112) >="20061109" and convert(char(14),PubDate,112) <="20061109"))','0','0','0','632'
its giving the error like ---Execution of a full-text operation failed. A clause of the query contained only ignored words.
please any one know the solution for thos let me know
Thanks and regards
Ravishanker maduri
ravi_maduri_mca@hotmail.com
View 1 Replies
View Related
Mar 7, 2008
Hi all,I have a problem with a stored procedure.This stored procedure inserts around bout 500,000 records but when it is executed it takes about 15-16 hours to do so.The stored procedure is using a temporary table to do this and is also calling a function.Please let me know if there is a way to reduce the execution time.will a cursor help?
Thanks,
Anne.
View 19 Replies
View Related
