Execute Stored Proc And Then Return A Value

Jul 13, 2004

ok 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?

View 3 Replies


ADVERTISEMENT

Execute Stored Procedure Y Asynchronously From Stored Proc X Using SQL Server 2000

Oct 14, 2007

I 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 Related

Can't Get Stored Proc To Return A Value

Feb 12, 2008

Hi,I'm having trouble getting a stored procedure to return a single
integer value.  Here's a short
version:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~set ANSI_NULLS ONset
QUOTED_IDENTIFIER ONGOALTER PROCEDURE
[dbo].[Perm_Import_CJ]AS/* bunch of stuff removed */DECLARE
@NoCategory intSELECT @NoCategory = COUNT(*) FROM table WHERE CategoryID IS
NULL/* print @NoCategory */RETURN
@NoCategory~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~If I uncomment "print
@NoCategory" it prints exactly the number it's supposed to, so there is no
problem with any of the queries in the stored procedure.  Then, in the code,
this is what I'm doing:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Dim dbConn
As New
SqlConnection(WebConfigurationManager.ConnectionStrings("ConnectionName").ConnectionString)Dim
cmd As New SqlCommand("StoredProc", dbConn)cmd.CommandType =
CommandType.StoredProceduredbConn.Open()Dim intNoCategory As Integer =
CInt(cmd.ExecuteScalar())dbConn.Close()~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~But,
and here's the problem ---> Even though @NoCateogry prints as the correct number, by the time it gets to intNoCategory in the code, it is ALWAYS zero.I have no idea what I am doing wrong. 
Thanks in advance for any help!Casey 

View 4 Replies View Related

Return Value From Stored Proc?

Jun 8, 2005

I need a stored procedure to return a value to my .NET app (ascx). The value will tell the app if the stored procedure returned any values or not. For example, if the Select SQL statement in the stored procedure returns no rows, the stored procedure could return a zero to the .NET app, otherwise it could return the number of rows or just a one to the .NET app.Anyone know how to do this?Thanks!

View 1 Replies View Related

PLEASE PLEASE HELP - How Can I Get A Return Value From A SQL Stored Proc Is ASP.NET?

Nov 30, 2006

Hi. I'm sorry to bother all of you, but I have spent two days lookingat code samples all over the internet, and I can not get a single oneof them to work for me. I am simply trying to get a value returned tothe ASP from a stored procedure. The error I am getting is: Item cannot be found in the collection corresponding to the requested name orordinal.Here is my Stored Procedure code.set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGoALTER PROCEDURE [dbo].[sprocRetUPC]@sUPC varchar(50),@sRetUPC varchar(50) OUTPUTASBEGINSET NOCOUNT ON;SET @sRetUPC = (SELECT bcdDVD_Title FROM tblBarcodes WHERE bcdUPC =@sUPC)RETURN @sRetUPCENDHere is my ASP.NET code.Protected Sub Page_Load(ByVal sender As Object, ByVal e AsSystem.EventArgs) Handles Me.LoadDim oConnSQL As ADODB.ConnectionoConnSQL = New ADODB.ConnectionoConnSQL.ConnectionString = "DSN=BarcodeSQL"oConnSQL.Open()Dim oSproc As ADODB.CommandoSproc = New ADODB.CommandoSproc.ActiveConnection = oConnSQLoSproc.CommandType = ADODB.CommandTypeEnum.adCmdStoredProcoSproc.CommandText = "sprocRetUPC"Dim oParam1Dim oParam2oParam1 = oSproc.CreateParameter("sRetUPC",ADODB.DataTypeEnum.adVarChar,ADODB.ParameterDirectionEnum.adParamOutput, 50)oParam2 = oSproc.CreateParameter("sUPC", ADODB.DataTypeEnum.adVarChar,ADODB.ParameterDirectionEnum.adParamInput, 50, "043396005396")Dim resres = oSproc("sRetUPC")Response.Write(res.ToString())End SubIf I put the line -oSproc.Execute()above the "Dim res" line, I end up with the following error:Procedure or function 'sprocRetUPC' expects parameter '@sUPC', whichwas not supplied. I thought that oParam2 was the parameter. I was alsounder the assumption that the return parameter has to be declaredfirst. What am I doing wrong here?

View 8 Replies View Related

Get A Return Value From An Insert Without Using A Stored Proc.

Oct 31, 2006

hi all, lets say i have this insert command being executed from C# to a SQL Db. //store transaction log
SqlCommand cmdStoreTrans = new SqlCommand("INSERT into Transactions(ImportID,ProfileID,RowID) values (@ImportID,@ProfileID,@RowID);",conn);
cmdStoreTrans.Parameters.Add("@ImportID",importId);
cmdStoreTrans.Parameters.Add("@ProfileID",profileId);
cmdStoreTrans.Parameters.Add("@RowID",i);
try
{
conn.Open();
cmdStoreTrans.ExecuteNonQuery();
conn.Close();
}
catch(SqlException ex)
{
throw(ex);
}I need the new Identity number of that record added.  how can i get that within THIS Sqlcommand.  Currently im closing the connection, creating a new command with 'SELECT MAX(id) from transactions" and getting the value that way, im sure there is a easier way keeping it all within one command object? someone mentioned using something liek @@Identity any help appreciatedTIA, mcm

View 2 Replies View Related

Return @@rowcount From Stored Proc

Sep 24, 2007

 HiI'm using an sqldatasource control in my aspx page, and then executing it from my code behind page (SqlDataSource1.Insert()), how do i retrieve the number of rows (@@rowcount) which have been inserted into the database and display it in my aspx page.  I am using a stored procedure. thanks 

View 1 Replies View Related

Stored Proc How To Return A Single Value

Oct 9, 2007

How do I return a value in a stored procedure? I want to return a value for TheQuarterId below but under all test conditions am only getting back a negative one. Please help! create PROCEDURE [dbo].[GetQuarterIdBasedOnDescription]
(
@QuarterString nvarchar(10),
@TheQuarterId int output
)
AS

BEGIN
SELECT @TheQuarterId = QuarterId from Quarter WHERE Description=@QuarterString
END

 

View 1 Replies View Related

Stored Proc Won't Return Correct Value.

Oct 18, 2007

I am using VS 2006, asp.net and C# to call a stored procedure. I want to return a value from the stored procedure into a variable in my C# code. Currently this is not working for me, and I can not figure out whatthe problem is? Can someone please help me out?
I really don't think the problem is in my stored procedure. I can right click on the stored proc and run it withsuccess. If I trace into the C# code though only a negative one (-1) is returned.
On line 5 I have tried the alternate lines of code but this has not worked for me.
     mySqlCommand.Parameters["@TotalRecords"].Direction = ParameterDirection.Output;     mySqlCommand.Parameters["@TotalRecords"].Direction = ParameterDirection.ReturnValue;
Can someone please help me out. I have spent to much time trying to figure this one out.
// C# code to call stored proc.1  try2   {3     SqlCommand mySqlCommand = new SqlCommand("[GetRecordsAssociatedWithRealtor]", mySqlConnection);4     mySqlCommand.Parameters.Add("@RealtorId", SqlDbType.Decimal, 10).Value = RealtorId;5     mySqlCommand.Parameters["@TotalRecords"].Direction = ParameterDirection.InputOutput;6     mySqlCommand.CommandType = CommandType.StoredProcedure;7     RecordsAssociatedWithRealtor = mySqlCommand.ExecuteNonQuery();8   }
// Stored procedure below.USE [REALTOR]GO/****** Object:  StoredProcedure [dbo].[GetRecordAssociatedWithRealtor]    Script Date: 10/18/2007 13:15:18 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[GetRecordAssociatedWithRealtor]( @RealtorId int, @TotalRecords int output)AS BEGIN DECLARE @HouseDetailRecords int DECLARE @RealtorRecords int SELECT  @HouseDetailRecords= RealtorId from Realtor where RealtorId=@RealtorId SELECT  @RealtorRecords = RealtorId from ConstructionDetail where RealtorId=@RealtorId SET     @TotalRecords=SUM(@HouseDetailRecords+@RealtorRecords) RETURN  @TotalRecordsEND

View 5 Replies View Related

Newbie: Cannot Get Return Value From Stored Proc

Feb 20, 2007

Good morning, all,

OK, I have read a ton of posting on this issue, but either they don't give enough information or they are for packages which use the Execute SQL command, whereas I am using the OLE DB Command Data Flow Transformation.

I have an Excel spreadsheet that we are receiving from agencies with rows of client data which I have to load into an application that is ready to go live. I also have a stored procedure spClientsInsertRcd, which was written for the application. In the normal flow of the application, the stored procedure is called from a Coldfusion page, which does some processing prior to calling it. So, I have written a 'wrapper' stored procedure, spImportAgencyData, which does the processing and then calls the spClientInsertRcd.

My dataflow has the following components:

An Excel Source, containing my test data, consisting of just one row of data,

which points to a

Derived Column Transformation, which reformats the SSN and adds a user variable, named returnValue with an Expression value of @[User::returnvariable] set to a four-byte signed integer, which (i think) I need to get the value out of the stored procedure.

which points to a

Data Conversion Transformation, which takes care of all the datatype conversions

which points to a

OLE DB Command, which contains the following as the SQL Command:

exec ?= spImportAgencyData ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?

In the OLE DB Command, I have mapped returnValue, my user variable to @RETURN_VALUE.

Right now, I am in initial testing. The dataflow shows that it is succeeding, but my one data record for testing is not getting inserted. I need to get the value of returnValue to figure out what is happening.

How do I get the value of the returnValue? I have tried putting a recordset destination after the OLE DB command, but that just gives me the data that went into the OLE DB Command.

Thanks,
Kathryn

View 2 Replies View Related

Invalid Return Value From Stored Proc From VC++ 6.0

Oct 27, 2006

I have a stored procedure that takes a computer name (nvarchar) and either updates a time stamp in a matching row or adds a new row when no match is found based on the computer name (replicates a set of rows in another table as well in the case of not found).

When the row is unmatched, an output param (int) is set to 1 indicating it is new. When found, a zero is placed into the output parameter.

This stored procedure worked fine until we recently upgraded to SQL Server Express (2005).

C++ code:

_bstr_t conn_str = CONN_STR;
conn_str += (LPCTSTR)_server_name;

try
{
_ConnectionPtr epi_conn;
_CommandPtr cmd("ADODB.Command");
_ParameterPtr param;
long addfg;
_variant_t var_addfg;

//
// connect to database
//
epi_conn.CreateInstance(__uuidof(Connection));
epi_conn->Open(conn_str,"","",adConnectUnspecified);

cmd->ActiveConnection = epi_conn;
cmd->CommandText = "qry_DBUpdWorkstation";
cmd->CommandType = adCmdStoredProc;

//
// setup stored procedure call
//
param = cmd->CreateParameter (_bstr_t("@s_computer_name"), adVarChar, adParamInput, 31);
cmd->Parameters->Append(param);
param->Value = client_name;

param = cmd->CreateParameter (_bstr_t("@l_addfg"), adInteger, adParamOutput, 4);
cmd->Parameters->Append(param);

//
// debug output
//
if (trace_fp)
{
COleDateTime cur_dt = COleDateTime::GetCurrentTime();
fwprintf(trace_fp, L"%s - clsSrvrCtrlr::UpdWorkstation 1 client=%s",
cur_dt.Format(), client_name);
fflush(trace_fp);
}

cmd->Execute(NULL, NULL, adCmdStoredProc);

var_addfg = cmd->Parameters->Item[_variant_t("@l_addfg")]->Value;
addfg = var_addfg.iVal;

//
// done with stored proc
//
epi_conn->Close();

if (trace_fp)
{
COleDateTime cur_dt = COleDateTime::GetCurrentTime();
fwprintf(trace_fp, L"%s - clsSrvrCtrlr::UpdWorkstation 2 var_addfg.vt=%d, var_addfg.iVal=%d",
cur_dt.Format(), var_addfg.vt, var_addfg.iVal);
fflush(trace_fp);
}



and the stored proc:

set ANSI_NULLS OFF

set QUOTED_IDENTIFIER OFF

GO

-- Database Version 5.5.0

ALTER PROCEDURE [dbo].[qry_DBUpdWorkstation]

@s_computer_name nvarchar(31),

@l_addfg int OUTPUT

AS

SET NOCOUNT ON

DECLARE @d_update_dt datetime

DECLARE @l_msg_cd int

DECLARE @b_view_local bit

DECLARE @b_view_global bit

DECLARE @l_alert_type smallint

DECLARE @s_sound_file nvarchar(255)

DECLARE @l_wscd int

SELECT @d_update_dt=UpdateDT

FROM dbo.tblWorkstations

WHERE ComputerNameTxt=@s_computer_name

SET @l_addfg = 0

IF @d_update_dt IS NULL

BEGIN

INSERT tblWorkstations (ComputerNameTxt, UpdateDT, OnlineFg)

VALUES (@s_computer_name, GETDATE(), 0)

SET @l_wscd = SCOPE_IDENTITY()

DECLARE msg_cursor SCROLL CURSOR FOR

SELECT SysMsgCd, ViewLocalFg, ViewGlobalFg, AlertTypeVal, SoundFileTxt

FROM tblMsgAlerts

WHERE WorkstationCd = -1

OPEN msg_cursor

FETCH FIRST FROM msg_cursor INTO

@l_msg_cd, @b_view_local, @b_view_global, @l_alert_type, @s_sound_file

WHILE @@fetch_status = 0

BEGIN

INSERT tblMsgAlerts (WorkstationCd, SysMsgCd, ViewLocalFg, ViewGlobalFg, AlertTypeVal, SoundFileTxt)

VALUES (@l_wscd, @l_msg_cd, @b_view_local, @b_view_global, @l_alert_type, @s_sound_file)

FETCH NEXT FROM msg_cursor INTO

@l_msg_cd, @b_view_local, @b_view_global, @l_alert_type, @s_sound_file

END

CLOSE msg_cursor

DEALLOCATE msg_cursor

SET @l_addfg = 1

END

ELSE

BEGIN

UPDATE tblWorkstations

SET UpdateDT = GETDATE()

WHERE ComputerNameTxt=@s_computer_name

END



The stored proc ALWAYS returns 0 but will execute the code to insert the new row when not found and replicate the rows in the second table. Any ideas, suggestions?

Thanks

View 5 Replies View Related

Is It Possible To Execute Package Through Stored Proc

Jul 7, 2006

is 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

View 10 Replies View Related

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 ??!!!!??

View 4 Replies View Related

Stored Proc Failing To Return Results

Aug 9, 2000

I have a search stored proc which fails to return results when called by more than one user.

I have put selects in the various SPs to trace results and if I call from 3 query windows (executnig each as quickly as I can work the mouse) I get the following:
1st query returns the trace values (including correct count of temp table recs) but no result set
2nd query erturns nothing just "The command(s) completed successfully."
3rd query returns full results.

This seems to be consistent.

We are running SQL Server 7 SP1.
Just upgrading to SP2 to see if that helps.

The main SP calls other SPs to build result sets.
These use quite a few temp tables passed between SPs, parse CSV lists, join different other tables, create a SQL string to exec to do the search and get record IDs to return (no cursors).
The result set is built by a called SP using the temp table with IDs to return.

Anyone know of any problems or can suggest anything to try?

View 3 Replies View Related

Stored Proc Return File Size

Jan 21, 2004

Hi everyone,

Does anyone know of a SQL stored proc that when given a operating system filename (i.e. a text file), returns the size of the file in bytes.

Thanks in advance,

Jim

View 4 Replies View Related

Return Formatted Date From Stored Proc?

Mar 8, 2004

What is the recommended method of returning a formatted date from a stored procedure?


The date is held in a date time field. I wish to return the date formatted as:

dd/mm/yyyy hh:mm

for display in a bound text box on a win form. JUst selecting the date and binding it to the text box shows:

dd/mm/yyyy hh:mm:ss

I do not want the :ss to show. A textbox does not have a format property (that I can see). I suppose I could create my own textbox inheriting from the standard and apply a display format property. I thought it may be easier to select as required in an sp. The textbox is read only on the form.

I was looking at:

select jobHeaders.DateTimeJobTaken AS [Job Taken],
CAST(datepart(dd,jobHeaders.DateTimeJobTaken) as char(2)) + '/' +
CAST(datepart(mm,jobHeaders.DateTimeJobTaken) as char(2)) + '/' +
CAST(datepart(yyyy,jobHeaders.DateTimeJobTaken) as char(4))

from jobHeaders

but this gives :
8 /3 /2004 with spaces.

Before looking further I thought one of you guys may have the answer.

Thanks in advance

View 14 Replies View Related

Execute Multiple SQL Statements In Stored Proc

Nov 1, 2005

Hi, 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

View 2 Replies View Related

Execute Stored Proc On A Named Instance

Jun 2, 2006

I 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?

View 3 Replies View Related

HOW TO EXECUTE THE PACKAGE IN STORED PROC PLZZZZZZZZZZZZZZZZZZZZZZZZZ

Aug 6, 2007

My 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


View 1 Replies View Related

How Do I Access The Value Of A Stored Proc Return Param In C# Using ExecuteNonQuery?

Jun 8, 2007

I've got a stored proc to insert a record and return the id of the record inserted in an output param.How do I access this value in my code after the proc is executed?
param = comm.CreateParameter();param.ParameterName = "@MemberID";param.Direction = ParameterDirection.Output;param.DbType = DbType.Int32;comm.Parameters.Add(param);
try{     rowsAffected = GenericDataAccess.ExecuteNonQuery(comm);}catch {     rowsAffected = -1;}
 

View 1 Replies View Related

SQL Server Express Stored Proc Does Not Return Any Data

Jan 28, 2008

Hi!I have this table:Units  -id uniqueidentified (PK),  -groupName NVARCHAR(50) NOT NULL,  -name NVARCHAR(50) NOT NULL,  -ratio float NULL and the stored proc that simply returns all rows:ALTER PROCEDURE dbo.ilgSP_GetUnitsAS    SELECT [id], [groupName], [name], [ratio] FROM [Units] ORDER BY [groupName], [name]If I select 'Show Table Data' in Visual Studio 2005 I see all rows from the table. If I 'Execute' my stored from VS 2005 I get this:Running [dbo].[ilgSP_GetUnits].id                                     groupName                                          name                                               ratio                     -------------------------------------- -------------------------------------------------- -------------------------------------------------- ------------------------- No rows affected.(1 row(s) returned)@RETURN_VALUE = 0Finished running [dbo].[ilgSP_GetUnits].And I don't get any data in my ASP.NET application. WHY?Thanks! 

View 1 Replies View Related

Looping A Package Based On A Return Value From A Stored Proc?

Oct 24, 2007

Hi,

I have a package that I need to loop (possibly multiple times) based on a return value from a stored procedure.

I know that in DTS you could use the return value of "execution status" to do this.

Is there a way in SSIS to loop based on a return value?

Thanks much

View 5 Replies View Related

Execute Stored Proc Using A Proxy Account In 2005

Sep 18, 2007

Hi 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

View 1 Replies View Related

Can't Pass In GUID When Using Execute Stored Proc In VS 2008

Apr 8, 2008

I 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

View 3 Replies View Related

How To Call AS400 Stored Proc And Evaluate The Return Code?

May 30, 2007

I am trying to use SSIS to update an AS400 DB2 database by calling a stored procedure on the AS400 using an OLE DB command object. I have a select statement running against the SQL Server 2005 that brings back 20 values, all of which are character strings, and the output of this select is piped into the OLE DB command object. The call from SSIS works just fine to pass parameters into the AS400 as long as the stored procedure being called does not have an output parameter defined in its signature. There is no way that I can find to tell the OLE DB command object that one of the parameters is an output (or even an input / output) parameter. As soon as one of the parameters is changed to an output type, I get an error like this:






Code Snippet


Error: 0xC0202009 at SendDataToAs400 1, OLE DB Command [2362]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x8000FFFF.

Error: 0xC0047022 at SendDataToAs400 1, DTS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "OLE DB Command" (2362) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

Error: 0xC0047021 at SendDataToAs400 1, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0202009. There may be error messages posted before this with more information on why the thread has exited.

Information: 0x40043008 at SendDataToAs400 1, DTS.Pipeline: Post Execute phase is beginning.

Information: 0x40043009 at SendDataToAs400 1, DTS.Pipeline: Cleanup phase is beginning.

Task failed: SendDataToAs400 1

Warning: 0x80019002 at RetrieveDataForSchoolInitiatedLoans: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

Warning: 0x80019002 at Load_ELEP: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "Load_ELEP.dtsx" finished: Failure.





I really need to know if the call to the AS400 stored procedure succeeded or not, so I need a way to obtain and evaluate the output parameter. Is there a better way to accomplish what I am trying to do? Any help is appreciated.

View 3 Replies View Related

SQL Server 2012 :: Run DBCC Inside Stored Proc Using EXECUTE AS

Mar 18, 2014

I 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...

View 5 Replies View Related

Execute Dynamic SQL Stored Proc Without Specifying Table Level Permissions

Sep 17, 2007

I 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?

View 1 Replies View Related

How To Return A Numeric Value Based Upon If A Record Is Returned From My Query/Stored Proc.

Oct 2, 2007

 
I need to call the stored procedure below. Basically what I need to know is if the query returns a record?
Note I would be happy if I could just return the number Zero if no records are returned. Can someone please help me out here?Here is my query so far in SQL Server. I just don't know how to return a value based upon the result of the records returned from the query.
GOCREATE PROCEDURE [dbo].[GetNameStatus]( @CountryId decimal, @NameId decimal, @DescriptionId decimal)AS SELECT     Name.Active FROM       Name INNER JOIN               NameDescription ON Name.NameId = NameDescription.NameId WHERE Name.CountryId=@CountryId AND               Name.NameId=@NameId AND NameDescription.DescriptionId=@DescriptionId AND Name.Active='Y'
 

View 3 Replies View Related

Exec SQL Task: Capture Return Code Of Stored Proc Not Working

May 19, 2006

I am just trying to capture the return code from a stored proc as follows and if I get a 1 I want the SQL Task to follow a failure(red) constrainst workflow and send a SMTP mail task warning the customer. How do I achieve the Exec SQL Task portion of this, i get a strange error message [Execute SQL Task] Error: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_SingleRow".



Using OLEDB connection, I utilize SQL: EXEC ? = dbo.CheckCatLog

EXEC SQL Task Editer settings:
RESULTSET: Single Row
PARAMETER MAPPING: User::giBatchID
DIRECTION: OUTPUT
DATATYPE: LONG
PARAMETER NAME: 0

PS-Not sure if I need my variable giBatchID which is an INT32 but I thought it is a good idea to feed the output into here just in case there is no way that the EXEC SQL TASK can chose the failure constrainst workflow if I get a 1 returned or success constraint workflow if I get a 0 returned from stored proceedure





CREATE PROCEDURE CheckCatLog
@OutSuccess INT
AS

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
DECLARE @RowCountCAT INT
DECLARE @RowCountLOG INT

---these totals should match
SELECT @RowCountCAT = (SELECT Count(*) FROM mydb_Staging.dbo.S_CAT)
SELECT @RowCountLOG = (SELECT Count(*) FROM mydb_Staging.dbo.S_LOG)
--PRINT @RowCountCAT
--PRINT @RowCountLOG
BEGIN
IF @RowCountCAT <> @RowCountLOG
--PRINT 'Volume of jobs from the CAT file does not match volume of jobs from the LOG file'
--RETURN 1
SET @OutSuccess = 1
END
GO

Thanks in advance

Dave

View 6 Replies View Related

Not Able To Create Hash Table Inside Stored Proc Through Execute Sp_executesql @strQuery

Aug 21, 2007

Hello,
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.

View 4 Replies View Related

SSIS Hard Time Getting Back XML Return Data From Stored Procedure Call Executed By Execute SQL Task

Aug 9, 2006

I'm having a hard time to getting back an xml data back from a stored procedure executed by an Execute SQL task.

I'm passing in an XML data as a parameter and getting back resulting XML data as a parameter. The Execute SQL task is using ADO connection to do this job. The two parameters(in/out) are type of "string" and mapped as string.

When I execute the task, I get the following error message.

[Execute SQL Task] Error: Executing the query "dbo.PromissorPLEDataUpload" failed with the following error: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 2 ("@LogXML"): Data type 0xE7 has an invalid data length or metadata length.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I also tried mapping the parameter as XML type, but that didn't work either.

If anyone knows what's going on or how to fix this problem please let me know. All I want to do is save returning XML data in the parameter to a local package variable.

Thanks

View 10 Replies View Related

Can You Trace Into A Stored Proc? Also Does RAISERROR Terminate The Stored Proc Execution.

Feb 13, 2008

I am working with a large application and am trying to track down a bug. I believe an error that occurs in the stored procedure isbubbling back up to the application and is causing the application not to run. Don't ask why, but we do not have some of the sourcecode that was used to build the application, so I am not able to trace into the code.
So basically I want to examine the stored procedure. If I run the stored procedure through Query Analyzer, I get the following error message:
Msg 2758, Level 16, State 1, Procedure GetPortalSettings, Line 74RAISERROR could not locate entry for error 60002 in sysmessages.
(1 row(s) affected)
(1 row(s) affected)
I don't know if the error message is sufficient enough to cause the application from not running? Does anyone know? If the RAISERROR occursmdiway through the stored procedure, does the stored procedure terminate execution?
Also, Is there a way to trace into a stored procedure through Query Analyzer?
-------------------------------------------As a side note, below is a small portion of my stored proc where the error is being raised:
SELECT  @PortalPermissionValue = isnull(max(PermissionValue),0)FROM Permission, PermissionType, #GroupsWHERE Permission.ResourceId = @PortalIdAND  Permission.PartyId = #Groups.PartyIdAND Permission.PermissionTypeId = PermissionType.PermissionTypeId
IF @PortalPermissionValue = 0BEGIN RAISERROR (60002, 16, 1) return -3END 
 

View 3 Replies View Related

Conditional Execute By Execute SQL Task Return Value?

Jun 25, 2007

I have a SSIS package contains an "Execute SQL Task". The SQL will raise error or succeed. However, it sounds the package won't pick up the raised error?

Or is it possible to conditional run other control flow items according the the status of SQL task execution?

View 1 Replies View Related







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