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
ADVERTISEMENT
May 14, 2008
Hi All,
I have a stored proc which is executing successfully...but the results of that stored proc are displaying in the Messages Tab instaed of results Tab. And in the Results Tab the results shows as 0..So, Any clue friends..it is very urgent..I am trying to call this stored proc in my Report in SSRS as well but the stored proc is not displaying there also...Please help me ASAP..
Thanks
dotnetdev1
View 4 Replies
View Related
Apr 19, 2006
I have this stored proc:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[usp_CrimRecTest]
-- Add the parameters for the stored procedure here
@caseID [nvarchar]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT dbo.tblCASENOMASTER.CASENO, dbo.tblCASEMAST.LASTNAME, dbo.tblCASEMAST.FRSTNAME
FROM dbo.tblCASENOMASTER LEFT OUTER JOIN
dbo.tblCASEMAST ON dbo.tblCASENOMASTER.CASENO = dbo.tblCASEMAST.CASENO
WHERE (dbo.tblCASENOMASTER.CASENO = @caseID)
END
When I run this with an EXEC statement, the result pane shows no results but the message pane says it completed successfully and one row is affected. I know my input data is good. I also get nothing when I call this sproc from a VB front end. Any ideas?
Thanks.
View 4 Replies
View Related
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
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
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
Jun 10, 2008
Hi guys. I'm trying to compare the results from various stored procedures. Various stored procedures in our database got redone (refactored), and I want to see if they still pull back the same data.
Are there any programs out there that do this? Compare result sets from stored procedures? Any help is appreciated. Thanks!
View 1 Replies
View Related
Jul 23, 2005
Try hard to become familiar with T-SQL.Can anybodey tell me the best way to deal with set's provided by astored procedure. Til yesterday I thougt trapping set in temp tableusing INSERT EXEC is a way out of this, but then I struggeled withnested INSERT EXEC's.What are all the system proc's good for if the results cannot beevaluated? The approach of modular programming is to have code doingsimilar things in one place.If I try to make use of sp_helprolemember to get login names for moreroles, pack the logins in one table and return the result set in a SP,the procedure which calls that is unable to evaluate the set.On the other hand I read the advice, not to access system tablesdirectly.Is there a way out?
View 9 Replies
View Related
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
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
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
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
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
View Related
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
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
Jun 13, 2007
Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID, S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName', T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID FROM [Item].ItemDetails I INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID FROM [Item].ItemDetails IOr something like that... Any thoughts?
View 3 Replies
View Related
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
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
Jul 20, 2005
Hi,I am using data from multiple databases and/or queries. It would greatlysimplify and speed things up if I could use CONTAINS in processing theresults. However, "CONTAINS" requires the data to be indexed. Due to theamount of processing, I think it would be faster even if I had to re-indexevery time.For example, I would like to do something like this (simplified toillustrate the desired functionality... This should show all of the wordsfrom one table that are not contained in their current or inflectional formswithin another table):SELECT W1.ContentFROM(SELECT Word AS ContentFROM MyTable) W1LEFT OUTER JOIN(SELECT Phrase AS ContentFROM MyOtherTable) W2ON W2.Content CONTAINS(INFLECTIONAL, W1.Content)WHERE W2.Content IS NULLCan the results of a procedure be indexed? If not, can I drop the resultsinto a new table and trigger an automatic index of it, pausing the procedureuntil the indexing is done?Or, it there another way?Thanks!
View 2 Replies
View Related
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
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
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
Oct 8, 2004
Two questions - one slightly off topic:
1) How do I write a stored procedure in MS SQL so that it returns a results set?
2) How do I get this into ASP.NET so that I can put the values into controls?
Jags
View 1 Replies
View Related
Sep 30, 2006
I need to return the results of a stored procedure into a temporary table. Something like this:
Select * into #temp from exec (the stored procedure). It appears that I can not do this.
The following will not work for me cause I am not sure how many columns will be returned. I want this to work even if the calling stored procedure is changed (i.e add or take away columns)
insert into (...) exec (the stored procedure.
Does anyone have any ideas how I could do this.
View 4 Replies
View Related
Nov 18, 2006
Hi,I have written a stored procedure which includes a DATEPART command, i.e.DATEPART(weekday, <date>)The result when ran from SQL Query Analyser is as expected . i.e. Sundayreturns 1, Monday 2, etcWhen the same proc is called from within the Access 2000 project Sundayis returned as 7, Saturday as 6 instead of 1 and 7 respectively.Basically the same stored proc returns different data depending on fromwhere it has been called.This is causing some issues obviously as the resulting tables andreports are showing incorrect data when presented in Access 2000.Has anyone else experienced this before or have any idea what may cause it.Regards,PB
View 1 Replies
View Related
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
Aug 1, 2006
I thought I would impliment a new feature of my web page using stored procedures and the SqlDataSource object, for practice or whatever, since I don't normally use that stuff.
This is the stored procedure:set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author:Lance Colton
-- Create date: 7/31/06
-- Description:Find the contest winner
-- =============================================
ALTER PROCEDURE [dbo].[AppcheckContest]
-- Add the parameters for the stored procedure here
@BeginDate datetime = '1/1/2006',
@EndDate datetime = '12/31/2006',
@SectionID int = 10,
@WinnerID int = 0 OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT top 1 @WinnerID = P.UserID
FROM cs_Posts P
WHERE P.PostDate BETWEEN @BeginDate AND @EndDate
AND P.SectionID = @SectionID
AND P.UserID <> 2100 -- I don't want to win my own contest...
AND SettingsID = 1000 -- This number could be different if i had more than one CS installed?
AND IsApproved = 1
ORDER BY NEWID() -- yes this is slow, but it works...
RETURN @WinnerID
END
It's really simple - just needs to return the one randomly chosen integer userID. I've tested it in query designer or whatever it's called in Management Studio and it works fine there at least.
Thinking I was done the hard part, I created a new web form in visual studio, dropped a SqlDataSource on it, and used the 'configure data source' wizard from the smart tag to do all the work for me. I didn't have any trouble using the wizard to select my stored procedure, and i'm using the sa connection string to simplify my debugging. I tried using the FormParameter / FormField way of getting the output and setting the input parameters. I can't seem to get it working though. There's no errors or anything, just the output isn't coming through.
Here's the code from the aspx codebehind file:Partial Class Contest
Inherits System.Web.UI.Page
Protected Sub btnSelectWinner_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSelectWinner.Click
Dim stuff As New System.Web.UI.DataSourceSelectArguments
SqlDataSource1.Select(stuff)
SqlDataSource1.DataBind()
lblWinnerID.Text = SqlDataSource1.SelectParameters("WinnerID").ToString
End Sub
End Class
As you can see, I wasn't sure if you're supposed to call databind() or select() to actually get the stored procedure to execute, so I tried both. I was hoping the last line of code there would set the label to the value contained in the @WinnerID parameter, but instead it sets it to "WinnerID".
Here's the code from the .aspx file. Most of this was generated by the Wizard, but I messed around with it a bit. <%@ Page Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="false" CodeFile="Contest.aspx.vb" Inherits="Contest" title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="CPHMain" Runat="Server">
<asp:Button ID="btnSelectWinner" runat="server" Text="Find Winner" />
<asp:Calendar ID="Calendar_From" runat="server"></asp:Calendar>
<asp:Calendar ID="Calendar_To" runat="server"></asp:Calendar>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:appcheck-csConnectionString-sa %>"
SelectCommand="AppcheckContest" SelectCommandType="StoredProcedure" CancelSelectOnNullParameter="False">
<SelectParameters>
<asp:FormParameter FormField="Calendar_From" Name="BeginDate" Type="DateTime" />
<asp:FormParameter FormField="Calendar_To" Name="EndDate" Type="DateTime" />
<asp:Parameter DefaultValue="10" Name="SectionID" Type="Int32" />
<asp:formParameter FormField="lblWinnerID" defaultvalue="666" Direction="InputOutput" Name="WinnerID" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
<asp:Label ID="lblWinnerID" runat="server" Text="???"></asp:Label>
</asp:Content>
View 3 Replies
View Related
May 27, 2015
How can I return results from this SP?
Alter
Procedure sp_Blocking
as
SET NOCOUNT
ON
truncate
table blocked
[Code] ....
View 4 Replies
View Related
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
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
Jun 5, 2006
Can I use sqlDatareader with a stored procedure to return more than one reultset that I ll be accessing through MyDatareader.MoveNext
Any Little example on both the stored proc and the .Net side
Thanks
View 4 Replies
View Related
Dec 4, 2014
I have a stored procedure on a SQL Server 2008 database. The stored procedure is very simple, just a SELECT statement. When I run it, it returns 422 rows. However, when I run the SELECT statement from the stored procedure, it returns 467 rows. I've tried this by running both the stored procedure and the SELECT statement in the same SSMS window at the same time, and the behavior is the same. The stored procedure is:
USE [REMS]
GO
/****** Object: StoredProcedure [mobile].[GetAllMobileDeviceUsers] Script Date: 12/04/2014 */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[Code] ....
When I do this in the same SSMS window:
exec mobile.GetAllMobileDeviceUsers
SELECT
ee.EmployeeID,
EmployeeName = LastName + ', ' + FirstName
FROM EmployeeInvData ee
--UNION
[Code] ....
I get two result sets. The first is 422 rows; the second is 467 rows. Why?
View 4 Replies
View Related
Jul 16, 2006
This is driving me nuts, below is the C# for the proc as well as the runtime error upon calling EXEC on it. Any help would be appreciated. Using UNSAFE Permission Set.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Security;
using System.Security.Principal;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure()]
public static void uspExternalConnection()
{
WindowsIdentity newIdentity = null;
WindowsImpersonationContext newContext = null;
try
{
//impersonate the caller
newIdentity = SqlContext.WindowsIdentity;
newContext = newIdentity.Impersonate();
if(newContext != null)
{
using (SqlConnection oConn =
new SqlConnection("Server=.\sqlexpress;" +
"Integrated Security=true;"))
{
SqlCommand oCmd =
new SqlCommand("SELECT * FROM AdventureWorks.HumanResources.Employee", oConn);
oConn.Open();
SqlDataReader oRead =
oCmd.ExecuteReader(CommandBehavior.CloseConnection);
SqlContext.Pipe.Send(oRead);
}
}
else
{
throw new Exception("user impersonation has failed");
}
}
catch (Exception ex)
{
SqlContext.Pipe.Send(ex.Message.ToString());
}
finally
{
if (newContext != null)
{
newContext.Undo();
}
}
}
};
Msg 6522, Level 16, State 1, Procedure uspExternalConnection, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'uspExternalConnection':
System.InvalidOperationException: Data access is not allowed in this context. Either the context is a function or method not marked with DataAccessKind.Read or SystemDataAccessKind.Read, is a callback to obtain data from FillRow method of a Table Valued Function, or is a UDT validation method.
System.InvalidOperationException:
at System.Data.SqlServer.Internal.ClrLevelContext.CheckSqlAccessReturnCode(SqlAccessApiReturnCode eRc)
at System.Data.SqlServer.Internal.ClrLevelContext.GetCurrentContext(SmiEventSink sink, Boolean throwIfNotASqlClrThread, Boolean fAllowImpersonation)
at Microsoft.SqlServer.Server.InProcLink.GetCurrentContext(SmiEventSink eventSink)
at Microsoft.SqlServer.Server.SmiContextFactory.GetCurrentContext()
at Microsoft.SqlServer.Server.SqlContext.get_CurrentContext()
at Microsoft.SqlServer.Server.SqlContext.get_Pipe()
at StoredProcedures.uspExternalConnection()
View 1 Replies
View Related
