How Do I Capture The Error Message From A Stored Procedure?

May 30, 2007

Greetings,



I am creating a package that has many SQL tasks. Each task executes a stored procedure. I need to capture any error messages returned by the stored procedures. Eventually, the error messages will be logged so that we can audit the package and know if individual tasks succeeded or failed.



I'm not sure where or how I can access a stored procedure message. What is the best way?



Thanks,

BCB

View 7 Replies


ADVERTISEMENT

How To Capture The Error Message?

Apr 26, 2004

Is possible to capture the message of error generated in the execution
of a command SQL?

Thanks.

View 2 Replies View Related

Capture Error Message And Include It In The Email

Feb 15, 2008

Hello,

I have a SQL Task that executes some stored procedure. When a condition is met the stored procedure raises an error by calling RAISERROR (@ErrorMessage,16,1). Following the SQL Task I have Send Mail Task that sends an error email.

I would like to know how to include the @ErrorMessage in the error email.

Thank you,
-Oleg

View 3 Replies View Related

How Do I Capture An Error Message Into An Expression Or A Variable???

Nov 16, 2006

Hi

We have set up an SSIS package which goes to an FTP site and downloads files.

Everything is fine... EXCEPT (lol) when there are no files to download. This then fails the task.

However, I want the package to continue to run.

Is there away of assigning the error message given to an expression and then using the expression in the precedence contraint?



thanking you in advance

David

View 1 Replies View Related

Stored Procedure Error Message...

Mar 6, 2007

Hi

I have trouble to get this stored procedure running and I tried to figure out where I did a mistake but I'm just lost, so if somebody could help me... Thanks!

The Error Message is 102, Level 15, State 1



SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE InsertNewDocumentTypeAndAddSystemFields



@varDocumentTypeName nvarchar(254),

@varNEWDocumentTypeID integer = 0,

@varTempID integer

AS

BEGIN

SET NOCOUNT ON;

INSERT INTO dbo.Document_Type_LookUP

VALUES(@varDocumentTypeName);



@varNEWDocumentTypeID = IDENT_CURRENT('dbo.Document_Type_LookUP')





-- Name or Title

INSERT INTO dbo.Document_Field

SELECT 'Name', SQLDataTypeID FROM SQLDataType WHERE SQLDataTypeName = 'string'



@varTempID = IDENT_CURRENT('dbo.Document_Field')

INSERT INTO dbo.Document_Type_Field_Link

VALUES(@varNEWDocumentTypeID,@varTempID)



-- Filename



INSERT INTO dbo.Document_Field

SELECT 'Filename', SQLDataTypeID FROM SQLDataType WHERE SQLDataTypeName = 'string'





@varTempID = IDENT_CURRENT('dbo.Document_Field')

INSERT INTO dbo.Document_Type_Field_Link

VALUES(@varNEWDocumentTypeID,@varTempID)

END

GO





Thanks in advance!

View 1 Replies View Related

Error Message And Stored Procedure

Mar 24, 2008



I have read many error message articles on the web but still cannot get this to work. I need to return the description of the error that is produced to a output variable (@ErrMsg).


In my stored procedure, I assign @SQLCode to @@Error. @SQLCode is also an output variable. I got the @SQLCode to return no problem, just the description is wrong.




Code SnippetIF @SQLCode <> 0
BEGIN
SELECT description = @ErrMsg
FROM master.dbo.sysmessages
WHERE error = @SQLCode
END






Can anyone shed some light on this? I have heard I have to assign the error information to another table and then pull the info from that table, but I don't know how to do that either. Help is greatly appreciated.

Thanks

View 6 Replies View Related

Error Message While Creating Stored Procedure

Jan 10, 2008



I'm creating a simple stored procedure:


create procedure spzipcode
(
@TableName nvarchar(10)
)
as
select distinct customer_code
from @TableName
GO

When running the code above, I get the following error message:


Server: Msg 137, Level 15, State 2, Procedure spzipcode, Line 8
Must declare the variable '@TableName'.

I can't figure out what I'm doing wrong.

Does anyone have an answer:

View 6 Replies View Related

About An Error Message Could Not Find Stored Procedure 'dbo.U_Login .

Jan 19, 2007

Hi All,I am having an error message when running the program below, the message says " Could not find stored procedure 'dbo.U_Login ". I have tried a inline Sql statement to match if user exists and then redirects to another page but when using a Stored Procedure what I get is the above mentioned error. I am logged onto my PC as Administrator but I don't know why this error appaears. Can anyone help me with this ? I am using SQL Server Express with Visual Studio. here the application I am trying to run:1 using System;
2 using System.Data.SqlClient;
3 using System.Data;
4 using System.Configuration;
5 using System.Web;
6 using System.Web.Security;
7 using System.Web.UI;
8 using System.Web.UI.WebControls;
9 using System.Web.UI.WebControls.WebParts;
10 using System.Web.UI.HtmlControls;
11
12
13 public partial class _Default : System.Web.UI.Page
14 {
15 protected void Page_Load(object sender, EventArgs e)
16 {
17
18 }
19 protected void btnSubmit_Click(object sender, EventArgs e)
20 {
21
22 //SqlDataSource LoginDataSource = new SqlDataSource();
23 //LoginDataSource.ConnectionString =
24 // ConfigurationManager.ConnectionStrings["LoginConnectionString"].ConnectionString;
25 ////ConfigurationManager.ConnectionStrings["LoginConnectionString"].ToString();
26 ////// if (LoginDataSource != null)
27 //// // Response.Redirect("DebugPage.aspx");
28
29 //Sql connection = LoginDataSource.ConnectionString;
30
31 string UserName = txtUserName.Text;
32 string Password = txtPassword.Text;
33
34 // string query = "SELECT * FROM Users WHERE userName = @UserName " + "AND Password = @Password";
35 //SqlCommand Command = new SqlCommand(query, new SqlConnection(GetConnectionString()));
36
37 SqlCommand Command = new SqlCommand("dbo.U_Login", new SqlConnection(GetConnectionString()));
38
39
40 //SqlConnection SqlConnection1 = new SqlConnection(GetConnectionString()); //added
41 //SqlCommand Command = new SqlCommand(); //added
42 //Command.Connection = SqlConnection1; //added
43 // Command.Connection = new SqlConnection(GetConnectionString());
44 //SqlConnection_1.Open();//added
45
46 Command.CommandType = CommandType.StoredProcedure; //added
47 //Command.CommandText = "dbo.U_Login"; //added
48
49
50
51
52 Command.Parameters.AddWithValue("@UserName", UserName);
53
54 Command.Parameters.AddWithValue("@Password", Password);
55
56 Command.Connection.Open();
57
58 SqlDataReader reader;
59
60
61 //reader = Command.ExecuteReader(CommandBehavior.CloseConnection);11
62
63 reader = Command.ExecuteReader();
64
65
66 if (reader.Read())
67
68 Response.Redirect("DebugPage.aspx");
69 else
70 Response.Write("user doesn't exist");
71
72
73 //SqlConnection_1.Close();//added
74
75
76 }
77 private static string GetConnectionString()
78 {
79
80 return ConfigurationManager.ConnectionStrings["LoginConnectionString"].ConnectionString;
81
82 }
83
84
85 }
86
87
88
89

 Thanks in advance

View 1 Replies View Related

Error Message Given Using Access 2003 Adp File To Change A Stored Procedure

Sep 17, 2007

Client/Server machine: Windows Xp Pro (SP2) (latest patches)
Office Software: Access 2003 (latest patches)
Database S/W: SQL Server 2005 (latest patches)

The following error message is displayed when trying to modify a stored procedure.

This version of Microsoft Access doesn't support design changes to the
version of Microsoft SQL Server your project is connected to. See the
Microsoft Office Update Web site for the latest information and downloads
(on the Help menu, click Office on the Web). Your design changes will not be
saved.

However, if you save, close and re-open the stored procedure having made the required changes, the changes have been saved.

Is there any way to suppress the error message / hotfix available from microsoft since the error message appears to be completely erroneous ?

Have I provided enough detail as this is my first post ?


Philip


View 1 Replies View Related

Capture Stored Procedure Along With Parameters

Jan 28, 2008

Is there a way to capture Stored Procedure Name along with the Parameter passed in SQL 2005 or SQL2000 when a stored procedure is executed?


Thanks !

View 6 Replies View Related

How Do I Capture @@ROWCOUNT From A Stored Procedure?

Dec 5, 2006

Greetings,

I have a stored procedure that does updates. The sproc is called from an €œExec SQL Task€?. The sproc returns the number of rows that were updated using this technique:

RETURN @@ROWCOUNT

How do I capture the sproc€™s return value (number of rows) in a package-level variable? I eventually want to log this value. The sproc is not returning a result set. I€™m new to SSIS so any general guidance would be appreciated.

Thanks,
BCB

View 6 Replies View Related

Is It Possible To Capture An OUT Type Parameter From A PL/SQL Stored Procedure?

Dec 19, 2007

When a stored PL/SQL procedure in my Oracle database is called from ASP.NET, is it possible to retrieve the OUT parameter from the PL/SQL procedure?  For example, if I have a simple procedure as below to insert a row into the database.  Ideally I would like it to return back the parameter named NewId to my ASP.NET server.  I'd like to capture this in the VB.NET code. 1 create or replace procedure WriteName(FirstName in varchar2, LastName in varchar2, NewId out pls_integer) is2 3 NameId pls_integer;4 5 begin6 7 select name_seq.nextval into NameId from dual;8 9 insert into all_names(id, first_name, last_name)10 values(NameId, FirstName, LastName);11 12 NewId := NameId;13 14 end WriteName;  1 <asp:SqlDataSource 2 ID="SqlDataSaveName" 3 runat="server" 4 ConnectionString="<%$ ConnectionStrings:ConnectionString %>"5 ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" 6 SelectCommand="WRITENAME"7 SelectCommandType="StoredProcedure">8 <SelectParameters>9 <asp:ControlParameter ControlID="TextBoxFirstName" Name="FIRSTNAME" PropertyName="Text" Type="String" />10 <asp:ControlParameter ControlID="TextBoxLastName" Name="LASTNAME" PropertyName="text" Type="String" />11 </SelectParameters>12 </asp:SqlDataSource>This is then called in the VB.NET code as below. It is in this section that I would like to capture the PL/SQL OUT parameter NewId returned from Oracle. 1 SqlDataSaveName.Select(DataSourceSelectArguments.Empty) If anybody can help me with the code I need to add to the VB.NET section to capture and then use the returned OUT parameter then I'd be very grateful.

View 2 Replies View Related

Capture Return Value From Stored Procedure, Use Same In Code Behind Page

Apr 18, 2007

My stored procedure works and codes is working except I need to capture the return value from the stored procedure and use that value in my code behind page to indicate that a duplicate record entry was attempted.  In my code behind file (VB) how would I capture the value "@myERROR" then display in the label I have that a duplicate entry was attempted.
Stored ProcedureCREATE PROCEDURE dbo.usp_InsertNew @IDNumber         nvarchar(25), @ID  nvarchar(50), @LName  varchar(50), @FName  varchar(50)
AS
 DECLARE @myERROR int    -- local @@ERROR      , @myRowCount int  --local @@rowcountBEGIN  -- See if a contact with the same name and zip code exists IF EXISTS (Select * FROM Info   WHERE ID = @ID)   BEGIN    RETURN 1END ELSEBEGIN TRAN    INSERT INTO Info(IDNumber, ID, LName,             FName) VALUES (@IDNumber, @ID, @LName,             @FName)             SELECT @myERROR = @@ERROR, @myRowCount = @@ROWCOUNT If @myERROR !=0 GOTO HANDLE_ERROR
                               COMMIT TRAN RETURN 0 HANDLE_ERROR:  ROLLBACK TRAN  RETURN @myERROR      ENDGO
asp.net page<asp:SqlDataSource ID="ContactDetailDS" runat="server" ConnectionString="<%$ ConnectionStrings:EssPerLisCS %>"            SelectCommand="SELECT * FROM TABLE_One"                        UpdateCommand="UPDATE TABLE_One WHERE ID = @ID"                         InsertCommand="usp_InsertNew" InsertCommandType="StoredProcedure">                        <SelectParameters>                <asp:ControlParameter ControlID="GridView1" Name="ID" PropertyName="SelectedValue" />            </SelectParameters>             </asp:SqlDataSource>
 

View 2 Replies View Related

How To Capture The ResultSet Of EXEC Command In Stored Procedure

Oct 1, 2007



Hi All,

I have created a dynamic SQL STATEMENT , but the result of the execution of that statement matters to me. within stored procedure.

Note: If run the statement using EXEC() command, the result gets displayed on the SQL Editor.
But I DO NOT KNOW HOW to Capture that value.

Any idea how to capture as I would like capture the result and stored it in a table.

Thank you.
--Israr

View 4 Replies View Related

Is There A Way To Capture All Bulk Insert Errors From Within A Stored Procedure?

Sep 28, 2007

Hi all!!

I have a stored procedure that dynamically bulk loads several tables from several text files. If I encounter an error bulk loading a table in the stored procedure, all I get is the last error code produced, but if I run the actual bulk load commands through SQL Management Studio, it gives much more usable errors, which can include the column that failed to load. We have tables that exceed 150 columns (don't ask), and having this information cuts troubleshooting load errors from hours down to minutes. Onto my question..., is there any way to capture all of the errors produced by the bulk load from within a stored procedure (see examples below)?


Running this...


BULK INSERT Customers

FROM 'c: estcustomers.txt'

WITH (TabLock, MaxErrors = 0, ErrorFile = 'c: estcustomers.txt.err')


Produces this (notice column name at the end of the first error)...


Msg 4864, Level 16, State 1, Line 1

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (CustId).

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.

Msg 7330, Level 16, State 2, Line 1

Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".




Running this (similar to code in my stored procedure)...


BEGIN TRY

BULK INSERT Customers

FROM 'c: estcustomers.txt'

WITH (TabLock, MaxErrors = 0, ErrorFile = 'c: estcustomers.txt.err')

END TRY

BEGIN CATCH

SELECT

ERROR_NUMBER() AS ErrorNumber,

ERROR_SEVERITY() AS ErrorSeverity,

ERROR_STATE() AS ErrorState,

ERROR_PROCEDURE() AS ErrorProcedure,

ERROR_LINE() AS ErrorLine,

ERROR_MESSAGE() AS ErrorMessage;

END CATCH



Produces something similar to this (which is useless)...
...Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

View 3 Replies View Related

SQL Server 2012 :: How To Capture CPU And Memory Usage For A Stored Procedure

Jan 19, 2015

I have around 100 packages (all [packages run at same time) each package calls a stored Procedure once the Stored proc Execution is completed the package will write the log information into a log table, here how can i capture the CPU and Memory usage for execution of each stored proc.

View 2 Replies View Related

Return Only One Message From Stored Procedure...

May 7, 2008



I have a stored procedure which checks to see if a user's email address exists before it inserts a new record. If it does it should return a message that notifies the user they are already subscribed. If they are not a different message should be returned stating that a new subscription has been created. This procedure works and is shown below.

The problem I am having is with the first SELECT statement. How can I get the procedure to show just one message and not the results from the first SELECT statement too?



CREATE PROCEDURE sp_InsertSubscription
@SubscriberFirstName VARCHAR(50),
@SubscriberLastName VARCHAR(50),
@SubscriberEmailAddress VARCHAR(50),
@SubscriberZipCode INT,
@IsActive BIT,
@SubscriberOptIn BIT,
@AdditionalOffersOptIn BIT,
@Msg VARCHAR(50) OUTPUT

AS
BEGIN
SET NOCOUNT ON;

-- Check to see if email address exists first
SELECT @@ROWCOUNT FROM Subscriber WHERE SubscriberEmailAddress = @SubscriberEmailAddress

IF @@ROWCOUNT > 0
BEGIN
SET @Msg = 'This email address is already subscribed'
SELECT @Msg AS 'User'
RETURN
END

-- Insert statements for procedure here
INSERT INTO Subscriber (SubscriberFirstName,
SubscriberLastName,
SubscriberEmailAddress,
SubscriberZipCode,
IsActive,
SubscriberOptIn,
AdditionalOffersOptIn,
SubscriberSignUpDate)

VALUES (@SubscriberFirstName,
@SubscriberLastName,
@SubscriberEmailAddress,
@SubscriberZipCode,
@IsActive,
@SubscriberOptIn,
@AdditionalOffersOptIn,
GETDATE())


IF @@ROWCOUNT > 0
BEGIN
SET @Msg = 'New user subscription created'
END

SELECT @Msg AS 'User'
END
GO




If the user's email address does not exist I get

(No column name)

User
New user subscription created

If the user's email address does exist I get

(No column name)
0

User
This email address is already subscribed

I would like for the (No column name) to go away - I know this is coming from the first SELECT statement. How do I suppress that statement from being output, yet still get the @@ROWCOUNT variable set?

View 3 Replies View Related

No Exception Message From CLR Stored Procedure

Aug 11, 2006

Hello everybody,



I've encountered a strange thing using a CLR Stored procedure:

The procedure throws an exception with no message inside... value = {" "}

Basically the procedure has a string as argument which contains a SQL statement that changes according to the users selections...

The results of the query are saved into a dataset for further processing.

Those resultsets can sometimes be very big (ex: 15000 records...). (For the record the procedure works fine for smaller datasets ex 6000 records and running the same query on the application server returns the expected resultset )

By Debugging the procedure I could determine that the failing point was when the dataset is filled...

Anyone having any idea??

The only information I have from this exception is the stacktrace:



at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

at System.Data.SqlClient.SqlDataReader.CloseInternal(Boolean closeReader)

at System.Data.SqlClient.SqlDataReader.Close()

at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)

at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)

at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)

at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)

at System.Data.SqlClient.SqlCommand.ExecuteScalar()

at DataAccess.runScalar(String strSQL, Boolean isStoredProcedure) in c:InetpubwwwrootStatistixApp_CodeDataAccess.cs:line 114

Thanks in advance

Alaindlk

View 5 Replies View Related

Call Stored Procedure From MSMQ Message

Nov 28, 2007

Does anyone have any ideas or sample code for firing a stored procedure using an MSMQ message?

I have to assume XML is in there somewhere.
(ie. create XML formatted message with proc call as element?)

Just getting started on project, any help appreciated.


www.beyonder422.com

View 4 Replies View Related

Error Message: Error 0x800706BE While Loading Package File D:PackagesToradSales.dtsx. The Remote Procedure Call Failed.

Dec 20, 2006

Hello,

I have a bundling package that runs about 20 other packages. It has been working fine for a while but a couple of days ago it fail with the following message,

Error 0x800706BE while loading package file "D:PackagesToradSales.dtsx". The remote procedure call failed.

I´m running the SSIS packages in an 64-bit environment.

Thankful for help with this!

//Patrick

View 3 Replies View Related

Soap Message Exchange Between .NET Webservice And Stored Procedure

Aug 16, 2007

Hello,

is message exchange between a .NET Webservice and a SQL stored procedure possible?
And if, could you please explain me how? Or give me a tip where i can get more informations
and maybe samples?
thanks for your help

regards
pamelia

View 30 Replies View Related

Error Message Of Store Procedure

Jul 28, 2006

create procedure Pr_addsupportor(
@supportornumber varchar(10),
@company varchar(100),
@title char(10),
@firstname char(30),
@surname char(30),
@addressline1 varchar(1000),
@addressline2 varchar(1000),
@addressline3 varchar(1000),
@postcode varchar(20),
@town char(100),
@county char(100),
@country varchar(100),
@phonenumber varchar(15),
@faxnumber varchar(15),
@email varchar(100),
@paymenttitle varchar(100),
@barcode varchar(2000),
@collector varchar(3),
@status varchar(10),
@registerdate datetime)
INSERT into
supportor(
[supportor number],
company,
title,
[first name],
surname,
[address line1],
[address line2],
[addess line3],
[post code],
town,
county,
country,
[phone number],
[fax number],
[e-mail],
[payment title],
barcode,
collector,
status,
[register date])
values
(
@supportornumber,
@company,
@title,
@firstname,
@surname,
@addressline1,
@addressline2,
@addressline3,
@postcode,
@town,
@county,
@country,
@phonenumber,
@faxnumber,
@email,
@paymenttitle,
@barcode,
@collector,
@status,
@registerdate);

Server: Msg 156, Level 15, State 1, Procedure Pr_addsupportor, Line 22
Incorrect syntax near the keyword 'INSERT'.
plz help me to find the error

View 4 Replies View Related

Integration Services :: Call Stored Procedure Result In Message Box In SSIS Script Task

Sep 4, 2015

I had the SP, I want to call in Script Task , had the Result set data value then I need pop up message box. So how can I call stored procedure result in message box in ssis script task using C#.

and I want  to use SSIS -OLEDB connection.
 
ConnectionManager cm;
System.Data.SqlClient.SqlConnection sqlConn;
System.Data.SqlClient.SqlCommand sqlComm;
cm = Dts.Connections["OLE_TEST_"];
sqlConn = (System.Data.SqlClient.SqlConnection)cm.AcquireConnection(Dts.Transaction);
sqlComm = new System.Data.SqlClient.SqlCommand("Exec dbo.sOp_xx_XXXe_VXX 280", sqlConn);
sqlComm.ExecuteNonQuery();

above code , no message box.

View 2 Replies View Related

Gridview / SqlDataSource Error - Procedure Or Function &<stored Procedure Name&> Has Too Many Arguments Specified.

Jan 19, 2007

Can someone help me with this issue? I am trying to update a record using a sp. The db table has an identity column. I seem to have set up everything correctly for Gridview and SqlDataSource but have no clue where my additional, phanton arguments are being generated. If I specify a custom statement rather than the stored procedure  in the Data Source configuration wizard I have no problem. But if I use a stored procedure I keep getting the error "Procedure or function <sp name> has too many arguments specified." But thing is, I didn't specify too many parameters, I specified exactly the number of parameters there are. I read through some posts and saw that the gridview datakey fields are automatically passed as parameters, but when I eliminate the ID parameter from the sp, from the SqlDataSource parameters list, or from both (ID is the datakey field for the gridview) and pray that .net somehow knows which record to update -- I still get the error. I'd like a simple solution, please, as I'm really new to this. What is wrong with this picture? Thank you very much for any light you can shed on this.

View 9 Replies View Related

Help With TSQL Stored Procedure - Error-Exec Point-Procedure Code

Nov 6, 2007

I am building a stored procedure that changes based on the data that is available to the query. See below.
The query fails on line 24, I have the line highlighted like this.
Can anyone point out any problems with the sql?

------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
This is the error...


Msg 8114, Level 16, State 5, Procedure sp_SearchCandidatesAdvanced, Line 24

Error converting data type varchar to numeric.

------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
This is the exec point...


EXEC [dbo].[sp_SearchCandidatesAdvanced]

@LicenseType = 4,

@PositionType = 4,

@BeginAvailableDate = '10/10/2006',

@EndAvailableDate = '10/31/2007',

@EmployerLatitude = 29.346675,

@EmployerLongitude = -89.42251,

@Radius = 50

GO

------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
This is the STORED PROCEDURE...


set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go



ALTER PROCEDURE [dbo].[sp_SearchCandidatesAdvanced]

@LicenseType int = 0,

@PositionType int = 0,

@BeginAvailableDate DATETIME = NULL,

@EndAvailableDate DATETIME = NULL,

@EmployerLatitude DECIMAL(10, 6),

@EmployerLongitude DECIMAL(10, 6),

@Radius INT


AS


SET NOCOUNT ON


DECLARE @v_SQL NVARCHAR(2000)

DECLARE @v_RadiusMath NVARCHAR(1000)

DECLARE @earthRadius DECIMAL(10, 6)


SET @earthRadius = 3963.191


-- SET @EmployerLatitude = 29.346675

-- SET @EmployerLongitude = -89.42251

-- SET @radius = 50


SET @v_RadiusMath = 'ACOS((SIN(PI() * ' + @EmployerLatitude + ' / 180 ) * SIN(PI() * p.CurrentLatitude / 180)) + (COS(PI() * ' + @EmployerLatitude + ' / 180) * COS(PI() * p.CurrentLatitude / 180) * COS(PI()* p.CurrentLongitude / 180 - PI() * ' + @EmployerLongitude + ' / 180))) * ' + @earthRadius




SELECT @v_SQL = 'SELECT p.*, p.CurrentLatitude, p.CurrentLongitude, ' +

'Round(' + @v_RadiusMath + ', 0) AS Distance ' +

'FROM ProfileTable_1 p INNER JOIN CandidateSchedule c on p.UserId = c.UserId ' +

'WHERE ' + @v_RadiusMath + ' <= ' + @Radius


IF @LicenseType <> 0

BEGIN

SELECT @v_SQL = @v_SQL + ' AND LicenseTypeId = ' + @LicenseType

END


IF @PositionType <> 0

BEGIN

SELECT @v_SQL = @v_SQL + ' AND Position = ' + @PositionType

END


IF LEN(@BeginAvailableDate) > 0

BEGIN

SELECT @v_SQL = @v_SQL + ' AND Date BETWEEN ' + @BeginAvailableDate + ' AND ' + @EndAvailableDate

END


--SELECT @v_SQL = @v_SQL + 'ORDER BY CandidateSubscriptionEmployerId DESC, CandidateFavoritesEmployerId DESC, Distance'


PRINT(@v_SQL)

EXEC(@v_SQL)


-----------------------------------------------------------------------------------------------------------------

View 4 Replies View Related

Capture Messages From Extended Procedure

Nov 18, 2005

Hi all,

I am using an extended stored procedure that sends email using SMTP. The procedure can be downloaded here (http://www.sqldev.net/xp/xpsmtp.htm). If I execute the procedure in QA and it fails for some reason, the error message is printed in the Messages tab. Is there any way to capture this message/messages when I use the XP in my stored procedures?

Your help is much appreciated.

View 3 Replies View Related

ERROR:Syntax Error Converting Datetime From Character String. With Stored Procedure

Jul 12, 2007

Hi All,





i have migrated a DTS package wherein it consists of SQL task.

this has been migrated succesfully. but when i execute the package, i am getting the error with Excute SQL task which consists of Store Procedure excution.



But the SP can executed in the client server. can any body help in this regard.





Thanks in advance,

Anand

View 4 Replies View Related

If Exists Capture Value Returned From Stored Proc

Feb 26, 2006

I have a stored proc with a query which checks whether an identical
value is already in the database table. If so, it returns a value of 1.
How do I caputure this value in an asp.net page in order to display a
message accordingly? (using ASP.NET 1.1)

Currently my stored proc looks something like this (snippet only):
If Exists(
SELECT mydoc WHERE....
)
Return 1
Else
...INSERT INTO.... code here.

View 2 Replies View Related

Capture Stored Proc Output To File

Jan 8, 2001

I am running a stored proc that does many updates. I want to capture the results of the stored proc in a text file. Results returned such as, 18 rows affected (the stuff that shows in results pane of query analyzer). Do I have to run the stored proc in batch mode to do this and capture via the output file in OSQL? I also want to capture error messages in case the stored proc aborts in the middle.

I am running SQL Server 7.0 SP2 on Win2K. I am running the stored proc in a DTS package.

Thanks.

Tim

View 3 Replies View Related

Capture A Fresh Copy Of The Stored Proc

Sep 18, 2007

How do I go about if I want to capture a fresh copy of the stored procedure?

Nishi

View 5 Replies View Related

How Does The .net App Capture Raiserror Values From A Stored Proc

Oct 28, 2006

I have been using return values to check the status of my stored procedures called from an application. But how does one read the new raiserror values that are passed with sql server 2005 and using ado.net 2.0. Are they returned as parameters or as a dataset or what?

Thanks

smHaig

View 2 Replies View Related

Capture Error

May 2, 2002

Hi all,
Is there anyway to capture the SQL Server Error and act accordingly?
I donot want sql server to raise an error when a Primary key violation has occured. Instead i want to capture that error(number,description etc) and act
accordingly.
Whats happening is, from the application we are trapping this sql error
and raising it. Instead, if somebody inserts a record which already exists, then we want to trap that error from the sqlprocedure itself and then do an update to that record.

thanks for the help

View 1 Replies View Related

Capture Error Msg Into Another Table

Nov 15, 2013

One of my co-worker told me I can do this to capture errors and insert into error table but when I test it, it doesn't work. Here is what I try to accomplish. SQL 2012. In reality, I have more complicate queries than below.

1. Insert data FROM SourceEmployee INTO Employee table and capture emp_id and error msg insert into dbo.##temperror table
2. Continue on the process until no more record. Basically, skip the error records and do a while loop until end of record.

--DROP TABLE dbo.Employee;
CREATE TABLE [dbo].[Employee]
(
[emp_id] [int] NOT NULL,
[last_name] [varchar](20) NULL,
[first_name] [varchar](15) NOT NULL,

[code]....

View 2 Replies View Related







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