Returning Recordset From Stored Proc
Mar 7, 2008
I need to return a rowset from a stored procedure. The returned rows will have ten columns and need to be bound to a gridview. Here is a code snippet.
'Create a DataAdapter, and then provide the name of the stored procedure.MyDataAdapter = New SqlDataAdapter("TMPTABLE_QUERY", MyConnection)
'Set the command type as StoredProcedure.
MyDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure
'Create and add a parameter to Parameters collection for the stored procedure.MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@condition_cl", _
SqlDbType.VarChar, 100))
'Assign the search value to the parameter.MyDataAdapter.SelectCommand.Parameters("@condition_cl").Value = sqlwhere & orderby
'ASSIGN THE OUTPUT PARAMETERS. HERE IS WHERE I NEED HELP (?????????????????)
DS = New DataSet() 'Create a new DataSet to hold the records.
MyDataAdapter.Fill(DS, "TMPTABLE_QUERY") 'Fill the DataSet with the rows returned.
'Set the data source for the DataGrid as the DataSet that holds the rows.GridView1.DataSource = DS.Tables("TMPTABLE_QUERY").DefaultView
GridView1.DataBind()
MyDataAdapter.Dispose() 'Dispose of the DataAdapter.
MyConnection.Close()
In my little research, I have seen examples of how to return a single value, but not multiple rows. I essentially have two problems. I'm not sure how my output parameters are to be defined and added. Do I need a separate 'Parameters.Add' statement for each column field value returned or can I do a single 'Parameters.Add' statement to define the whole row as an output parameter? Also, upon returning from the call to the SP, will I need a looping mechanism to populate the recordset for each individual record returned, or will the 'MyDataAdapter.Fill(DS, "TMPTABLE_QUERY") suffice, as included in my code above?
Thanks in advance.
View 3 Replies
ADVERTISEMENT
Jul 12, 2000
HELP HELP HELP!!
I have two questions.
I'm using VB 6.0 with ADO to SQL 7.0. My stored procedure works fine with Query Analyzer. I pass one parameter.
sp_test "1234"
And I get a recordset returned.
The stored procedure looks like this:
CREATE PROCEDURE sp_test
@facility_key varchar(255) = null
AS
/*DECLARE @sql1 varchar(255)*/
SELECT * FROM v_reimbursement_report
WHERE facility_key = @facility_key
/*IF @facility_key <> null
PRINT "0"
BEGIN
SELECT * FROM v_reimbursement_report
WHERE facility_key = @facility_key
END*/
When I uncomment the commented lines, I can still get a recordset returned using the query analyzer. And I get the same recordset returned when I use VB ADO and leave the stored proc lines commented out. However, when I call the procedure using the same VB code with the stored proc lines uncommented, I get -1 returned for rs.recordcount:
Private Sub Main_Click()
On Error GoTo Err_Main
Dim lsFacility As String
Dim lsReportName As String
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Dim gconn As ADODB.Connection
Dim param_facility_key As ADODB.Parameter
Dim gConnString As String
gConnString = "Trusted_Connection=Yes;UID=sa;PWD=yoyo;DATABASE=y ada;SERVER=ya;Network=dbnmpntw;Address=ya;DRIVER={ SQL Server};DSN=''"
Set gconn = New ADODB.Connection
Set rs = New ADODB.Recordset
Set cmd = New ADODB.Command
gconn.Open gConnString
cmd.Parameters.Refresh
lsFacility = "1234"
Set param_facility_key = cmd.CreateParameter("facility_key", adVarChar, adParamInput, 255)
cmd.Parameters.Append param_facility_key
param_facility_key.Value = lsFacility
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "sp_reimbursement_report_test"
cmd.Name = "sp_reimbursement_report_test"
Set cmd.ActiveConnection = gconn
rs.Open cmd, , adOpenKeyset, adLockOptimistic
MsgBox rs.RecordCount
Exit_Main:
Screen.MousePointer = vbNormal
gconn.Close
rs.Close
Exit Sub
Err_Main:
Screen.MousePointer = vbNormal
MsgBox "Error " & Err.Number & " has occurred: " & Err.Description
End Sub
Here are my questions:
1. Why don't I get a recordset returned to VB if I have anything (the commented lines) except a simple SELECT statement in the stored proc.
2. Why must I do a Print "0" (or anything) command in the stored procedure within the IF statement to see a recordset return?
TIA for any help you can give....this one has been keeping me up....and my company down.
JWB
View 3 Replies
View Related
Apr 2, 2000
Dear all,
I have a big problem in using SQL Server stored procedures:
When I have two select statement in the same procedure, the first one will use for returning specific information for the second one to select appropiate result to the client, but the stored procedure just return the first select statement recordset! What can I do? (I use VB Data Environment to access the stored procedures)
View 2 Replies
View Related
Jun 12, 2008
The stored procedure I created returns all records from the table if no parameters are passed, as expected. When I pass in the only parameter, I get 0 records returned when there should be one or more returned. I'm sure it's something simple in my syntax, but I don't see it.
This call returns all records:
exec webservices_BENEFICIAL_USES_DM_SELECT
This call returns 0 records, when it should return 1:
exec webservices_BENEFICIAL_USES_DM_SELECT @DISPOSAL_AREA_NAME='Cell 8'
Here is the stored procedure:
ALTER PROCEDURE [dbo].[webservices_BENEFICIAL_USES_DM_SELECT]
-- Add the parameters for the stored procedure here
@DISPOSAL_AREA_NAME DISPOSAL_AREA_NAME_TYPE = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
IF @DISPOSAL_AREA_NAME IS NULL
BEGIN
SELECT*
FROMBENEFICIAL_USES_DM
END
ELSE
BEGIN
SELECT*
FROMBENEFICIAL_USES_DM
WHEREDISPOSAL_AREA_NAME = '@DISPOSAL_AREA_NAME'
END
END
View 4 Replies
View Related
Jul 23, 2005
Hi All. My question is this. I have a complex stored procedure in SQLServer which works fine when I run it in Query Analyzer. However, whenI call it within my ASP script, it returns nothing, and sometimes locksup. If I change my script to call other existing stored procedures itworks fine. It's just with this particular stored proc. I have triedvarious DB calls in ASP, such as opening the recordset through an ADOconnection and through the Command object but to no avail. Here is mySQL:SET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS OFFGOALTER PROCEDURE dbo.sp_getProfessionalsByTypeID@typeID intASdeclare @catID intdeclare @userID intdeclare @strUserName varchar(100)declare @strFirstName varchar(100)declare @strLastName varchar(100)declare @strTypeName varchar(100)declare @strCategoryName varchar(100)declare @strPictureURL varchar(100)declare @sql varchar(1000)declare @a varchar(100)declare @b varchar(100)declare @c varchar(100)declare @d varchar(100)beginset @a=''set @b=''set @c=''set @d=''--Create Temp TableCREATE TABLE #QueryResult (nCatID int, nTypeID int, nUserID int,strUserName varchar(100), strFirstName varchar(100), strLastNamevarchar(100), strTypeName varchar(100), strCategoryNamevarchar(100),strPictureURL varchar(100))--Search QuerybeginINSERT #QueryResultSELECTdbo.tbl_musician_type.nCatID, dbo.tbl_musician_type.nTypeID,dbo.tbl_users.nUserID, dbo.tbl_users.strUserName,dbo.tbl_users.strLastName,dbo.tbl_users.strFirstName,dbo.tbl_musician_type.strTypeName, dbo.tbl_category.strCategoryName,dbo.tbl_professionals.strPictureURLFROMdbo.tbl_musician_type INNER JOINdbo.tbl_category ON dbo.tbl_musician_type.nCatID= dbo.tbl_category.nCategoryID INNER JOINdbo.tbl_profile ONdbo.tbl_musician_type.nTypeID = dbo.tbl_profile.nTypeID INNER JOINdbo.tbl_users ON dbo.tbl_profile.nUserID =dbo.tbl_users.nUserID LEFT OUTER JOINdbo.tbl_professionals ON dbo.tbl_users.nUserID= dbo.tbl_professionals.nUserIDWHEREdbo.tbl_musician_type.nTypeID = @typeIDend--Create Temp TableCREATE TABLE #QueryResult2 (ID int IDENTITY,nCatID int, nTypeID int,nUserID int, strUserName varchar(100), strFirstName varchar(100),strLastName varchar(100), strTypeName varchar(100), strCategoryNamevarchar(100),strPictureURL varchar(100), strArtist varchar(100),strAlbumTitle varchar(100), strRecordLabel varchar(100), strYearvarchar(100))--Now Declare the Cursor for Speakersdeclare cur_musicians CURSOR FOR--Combined Results Groupedselect distinct nCatID, nTypeID, nUserID, strUserName, strLastName,strFirstName, strTypeName, strCategoryName, strPictureURLFrom #QueryResultopen cur_musiciansfetch next from cur_musicians INTO @catID, @typeID, @userID,@strUserName, @strLastName, @strFirstName, @strTypeName,@strCategoryName, @strPictureURL--Loop Through Cursorwhile @@FETCH_STATUS = 0beginSELECT TOP 1 @a = strArtist, @b=strAlbumTitle,@c=strRecordLabel, @d=strYearFROM dbo.tbl_profile_discogwhere nTypeID = @typeID AND nCategoryID = @catID AND nUserID =@userIDinsert #QueryResult2select @catID as nCatID, @typeID as nTypeID, @userID as nUserID,@strUserName as strUserName, @strLastName as strLastName, @strFirstNamestrFirstName, @strTypeName as strTypeName, @strCategoryName asstrCategoryName, @strPictureURL as strPictureURL, @a ashighlightArtist, @b as highlightAlbumTitle, @c as highlightRecordLabel,@d as highlightYearfetch next from cur_musicians INTO @catID, @typeID, @userID,@strUserName, @strLastName, @strFirstName, @strTypeName,@strCategoryName, @strPictureURLset @a = ''set @b=''set @c=''set @d=''endselect * from #QueryResult2 TI--Clean Upclose cur_musiciansdeallocate cur_musiciansdrop table #QueryResultdrop table #QueryResult2endGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO
View 5 Replies
View Related
Sep 24, 2003
I'm having trouble getting a recordset out of stored procedure in ADO. The SP executes without errors, but the recordset object I return into is always closed.Here is my code:
<%
......
Set cmm = Server.CreateObject("ADODB.Command")
Set cmm.ActiveConnection = Connect
cmm.CommandType = adCmdStoredProc
cmm.CommandText = "dbo.client_updates_proc"
cmm.Parameters.Refresh
cmm.Parameters(1) = client_id
Set logRS = cmm.Execute()
if not logRS.EOF then
......
%>
My SP has one parameter, which I set above, and it ends with a select statement. When I run the SP in Query Analyzer, it outputs the table of results as is should, but I always get an error on 'if logRS.EOF then', saying that the object is closed.
View 3 Replies
View Related
Mar 3, 2004
i have a stored proc
CREATE PROCEDURE pop_notes ( @contnum as bigint,@cusnotes varchar(1000) OUTPUT) as
begin
declare @noteid as int
select @noteid=max(noteid) from cusaddnotes where contractnum=@contnum and rtrim(popup)='yes'
if @noteid > 0
begin
select @cusnotes=(notes + ' [' + convert(varchar(100),dateadded) + ']' ) from cusaddnotes where noteid =@noteid
end
else
begin
set @cusnotes='none'
end
print @cusnotes
end
GO
the srored proc is returning the @cusnotes with a size 0 and its throwing out errors in my asp.net page
'@cusnotes' of type: String, the property Size has an invalid size: 0
i tried to run the stored proc frm the QA
declare @note as varchar(1000)
exec @note=pop_notes 3430,'y'
print @note
it returned
none
0 <-- this seems to be causing the error
what could be the error/reason...
thanks in advance
View 3 Replies
View Related
Nov 15, 2005
I am trying to get the identity of an inserted record using this SP:
<code>
ALTER PROCEDURE acereal_Admin.AuctionInsertCommand
(
@AuctionID Int OUTPUT,
@StartDate char(255),
@StartTime char(255),
@Location char(255),
@Title char(255),
@Description char(255),
@Images char(255)
)
AS
INSERT INTO Auctions
(
StartDate,
StartTime,
Location,
Title,
Description,
Images
)
VALUES
(
@StartDate,
@StartTime,
@Location,
@Title,
@Description,
@Images
)
SELECT AuctionID AS ID
FROM Auctions
WHERE (AuctionID = @@IDENTITY)
</code>
Then, I am using this class for a file called dataaccess.cs to return the ID:
<code>
public string SaveImageName(string ImageName, string AuctionID)
{
SqlConnection
sqlConnection = new
SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
this.newSqlCommand = new
System.Data.SqlClient.SqlCommand("[AuctionImageSave]", sqlConnection);
this.newSqlCommand.CommandType =
System.Data.CommandType.StoredProcedure;
SqlParameter
paramImagePath = new System.Data.SqlClient.SqlParameter("@ImagePath",
System.Data.SqlDbType.Char, 255);
paramImagePath.Value = ImageName;
this.newSqlCommand.Parameters.Add(paramImagePath);
SqlParameter
paramAuctionID = new System.Data.SqlClient.SqlParameter("@AuctionID",
System.Data.SqlDbType.Int, 4);
paramAuctionID.Value = AuctionID;
this.newSqlCommand.Parameters.Add(paramAuctionID);
SqlParameter
paramImageID = new System.Data.SqlClient.SqlParameter("@ImageID",
System.Data.SqlDbType.Int, 4);
paramImageID.Direction = ParameterDirection.Output;
this.newSqlCommand.Parameters.Add(paramImageID);
//Return SqlDataReader Struct
this.newSqlCommand.Connection.Open();
this.newSqlCommand.ExecuteNonQuery();
this.newSqlCommand.Connection.Close();
int returnID = (int)paramImageID.Value;
return returnID.ToString();
}
</code>
The above code returns null.
Can anyone tell me what I am doing wrong?
THanks, Justin.
View 2 Replies
View Related
Feb 5, 2007
Hi,
Does sql server return an error code when a stored procedure fails, or an operation inside a stored procedure fails. How does one trap it?
We have an application that executes a number of stored procedures. I would like to verify that the procedures executed successfully, and the operations contained within the stored procedures (i.e. insert into a table) did not result in an error. If there was an error with the stored procedure of the operations inside, I would like to trap it, and return it to the calling program (non sql server).
I have seen the @@error global variable, but it only gives the return code of the last operation, which means I would have to check after every select, insert, delete, etc. I am hoping to avoid this.
Any thoughts?
Thanks in advance
View 2 Replies
View Related
Jul 18, 2014
In some of our business object reports we have to create variables to decode values to what we want. I am trying to replicate this in SQL Server and remember doing this in SQL server 2000 years ago back can't remember the exact way to do it. I remember running a query and calling stored proc within query which would return the value I wanted but not sure if I can still do this in SQL server 2008 and by that I mean doing it within query or have to do it another way.
Basically what I want is to have a procedure with all the variables replicated within that procedure so that when I run a query I can just call the appropriate bit of code by passing a specific name like
select job.dept, dbo.decodevariable('ShowJobDesc' job.jobtitle), job.salary
from job
so 'ShowJobDesc' and the job.jobtitle would be used to decode each job title to return job description.Just a bit unsure and can't remember if I am doing this the right way, is this possible?
View 2 Replies
View Related
Feb 26, 2007
Hi
I have coded the simple login page in vb .net which calls the stored proc to verify whether the user login details exists in the database. The stored procudure returns data back when I execute it in the SQL SERVER Management studio. But when I execute the stored proc in the 'Run stored Proc' wizard' , it is not retuning any data back. Connection string works fine as another SQL select command returns data in the same page.. I have included the VB code . Please help me to sort out this problem.Thank you.
If Not ((txtuser.Text = "") Or (txtpassword.Text = "")) Then
Dim conn As New SqlConnection()
conn.ConnectionString = Session("constr")
conn.Open()
Dim cmd As New SqlCommand("dbo.CheckLogin", conn)
cmd.CommandType = CommandType.StoredProcedure
' Create a SqlParameter for each parameter in the stored procedure.
Dim usernameParam As New SqlParameter("@userName", SqlDbType.VarChar, 10)
usernameParam.Value = Trim(txtuser.Text)
Dim pswdParam As New SqlParameter("@password", SqlDbType.NVarChar, 10)
pswdParam.Value = Trim(txtpassword.Text)
Dim useridParam As New SqlParameter("@userid", SqlDbType.NChar, 5)
Dim usercodeParam As New SqlParameter("@usercode", SqlDbType.VarChar, 10)
Dim levelParam As New SqlParameter("@levelname", SqlDbType.VarChar, 50)
'IMPORTANT - must set Direction as Output
useridParam.Direction = ParameterDirection.Output
usercodeParam.Direction = ParameterDirection.Output
levelParam.Direction = ParameterDirection.Output
'Finally, add the parameter to the Command's Parameters collection
cmd.Parameters.Add(usernameParam)
cmd.Parameters.Add(pswdParam)
cmd.Parameters.Add(useridParam)
cmd.Parameters.Add(usercodeParam)
cmd.Parameters.Add(levelParam)
Dim reader1 As SqlDataReader
Try
If conn.State = ConnectionState.Closed Then
conn.Open()
End If
Try
reader1 = cmd.ExecuteReader
Using reader1
If reader1.Read Then
Response.Write(CStr(reader1.Read))
Session("userid") = reader1.GetValue(0)
Session("usercode") = CStr(usercodeParam.Value)
Session("level") = CStr(levelParam.Value)
Server.Transfer("home.aspx")
Else
ErrorLbl.Text = "Inavlid Login. Please Try logging again" & Session("userid") & Session("usercode") & Session("level")
End If
End Using
Catch ex As InvalidOperationException
ErrorLbl.Text = ex.ToString()
End Try
Finally
If conn.State <> ConnectionState.Closed Then
conn.Close()
End If
End Try
Else
ErrorLbl.Text = "Please enter you username and password"
End If
View 5 Replies
View Related
May 14, 2007
Hey guys, could somebody pls provide me with an easy example as to how you would return multiple strings from a stored proc? Even a link to a decent tut would be great!
Muchos gracias!
View 12 Replies
View Related
Jun 6, 2007
I have a stored proc which should be returning a datatable. When I execute it manually it returns all requested results. However, when I call it via code (C#) it is returning a null table which leads me to believe the problem is in my code. I'm not getting any errors during runtime. Any help at all would be a BIG help!
private void PopulateControls() { DataTable table = CartAccess.getCart(); }
public static DataTable getCart() { DbCommand comm = GenericDataAccess.CreateCommand(); comm.CommandText = "sp_cartGetCart";
DbParameter param = comm.CreateParameter(); param.ParameterName = "@CartID"; param.Value = cartID; param.DbType = DbType.String; param.Size = 36; comm.Parameters.Add(param);
DataTable table = (GenericDataAccess.ExecuteSelectCommand(comm)); return table; }
public static DataTable ExecuteSelectCommand(DbCommand command) { // The DataTable to be returned DataTable table; // Execute the command making sure the connection gets closed in the end try { // Open the data connection command.Connection.Open(); // Execute the command and save the results in a DataTable DbDataReader reader = command.ExecuteReader(); table = new DataTable(); table.Load(reader); // Close the reader reader.Close(); } catch (Exception ex) { Utilities.SendErrorLogEmail(ex); throw ex; } finally { // Close the connection command.Connection.Close(); } return table; }
View 1 Replies
View Related
Jul 20, 2005
HiI'm not sure what the best approach for this is:I have a stored procedure which I would like to use to return severaloutput values instead of returning a recordset.CREATE PROCEDURE Test (@param1 int, @param2 int OUTPUT, @param3 intOUTPUT) ASSELECT field2, field3 FROM Table WHERE field1 = @param1I would like to return @param2 as field2 and @param3 as field3How do I do this without using SELECT multiple times?THanks in advanceSam
View 6 Replies
View Related
Nov 2, 2007
I am having a difficult time figuring this one out. I have a stored procedure that as part of an ASP.Net app connects to a csv file through a mapped network drive and imports the data into a table in SQL Server. Here it is.
------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[usp_ImportComp]
-- Add the parameters for the stored procedure here
@CSV as varchar(255)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Begin Try
Begin Tran
Declare @sql as nvarchar(max)
Set @SQL = 'Insert into dlrComp ( SN,
CM,
PM,
MC,
Comp_Total,
XU,
GM,
From_Date,
To_Date)
Select [Serial Number],
CM,
PM,
MC,
[Comp Total],
XU,
GM,
[From Date],
[To Date]
FROM OPENROWSET(' + char(39) + 'MICROSOFT.JET.OLEDB.4.0' + char(39) + ',' + char(39) + 'Text;Database=S:' + char(39) + ',' + char(39) + 'SELECT * FROM ' + @csv + char(39) + ')'
--print @sql
Exec sp_executesql @stmt=@sql
Commit Tran
End Try
Begin Catch
Rollback Tran
--Do some logging and stuff here
End Catch
END
------------------------------------------------------------------------------------
If I am connected to SQL through SQL Management Studio while logged in on the server that is running SQL as DomainUser1 and execute
exec usp_ImportComp @CSV='Comp.csv'
It completes successfully
However if I open SSMS (while logged into Windows on my PC as DomainUser2) using runas to run it as DomainUser1 while logged into my PC and connect to SQL Server using WIndows Auth and run the same I get the following error message.
OLE DB provider "MICROSOFT.JET.OLEDB.4.0" for linked server "(null)" returned message "'S:' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.".
If I add
With Execute as 'DomainUser1' and modify the stored procedure I get the same error message above.
If I log onto the Server that is running SQL as DomainUser2 I can successfully run
exec usp_ImportComp @CSV='Comp.csv'
Both User1 and User2 have the same permissions to the Share and csv as does the Domain user under whose context SQL Server is running.
What am I doing wrong?
View 4 Replies
View Related
Jul 7, 2004
Hi
I've created a sproc in SQL2000 that returns a dataset from a temp table & the number of records it's returning as an output parameter, although I can't seem to retrieve the value it's returning in asp.net although I get the dataset ok.
This is my sproc
create procedure return_data_and_value
@return int output
as
set nocount on
...
...
select * from #Table
select @return = count(*) from #Table
drop table #Table
go
This is asp.net code
Dim nRecords as Int32
Dim cmd As SqlCommand = New SqlCommand("return_data_and_value", conn)
cmd.CommandType = CommandType.StoredProcedure
Dim prm As SqlParameter = New SqlParameter("@return", SqlDbType.Int)
prm.Direction = ParameterDirection.Output
cmd.Parameters.Add(prm)
conn.Open()
dr = cmd.ExecuteReader
nRecords = convert.int32(cmd.parameters(@return).value)
conn.close
Thanks
Lbob
View 1 Replies
View Related
Sep 29, 2000
Is possible use DTS for return Data in recordset in the VB ?
I am asking this , why I have access the database INFORMIX, and several things do not work with Stored Procedure in the Informix
thank you in advance
View 1 Replies
View Related
Sep 13, 2004
Hi, I have a table that has the following field and results.
I want a where clause that returns records that start 0000........
I need to exclude those records that start 0001..........
Can anyone help
oseq
-------
0000
0000.0000
0000.0000.0000
0000.0000.0000.0000
0000.0000.0000.0000.0000
0000.0000.0000.0000.0001
0000.0000.0000.0001
0000.0000.0001
0001
0001.0000
0001.0001
0001.0002
0000.0000.0002
0000.0000.0003
0000.0001
0000.0001.0000
0000.0001.0000.0000
0000.0001.0000.0000.0000
0000.0001.0000.0000.0000.0000
0000.0026.0004.0009
0000.0026.0004.0010
View 3 Replies
View Related
Oct 19, 2005
Is there a server variable which will tell me how long the server took in returning a particular recordset. I've looked through MSDN SQL 2000 articles and could'nt / did'nt see anything. Or perhaps there is another way? Thanks!
View 1 Replies
View Related
Jul 20, 2005
Can anybody tell me why a) when running a stored proc from an asp page toreturn a recordset the command succeeds if the sp queries an existing tabledirectly, but b) if the stored proc populates results into a differenttable, temporary table, global temp table, or table variable, then queriesone of these, the asp page reports that the recordset object is closed. Ifusing a table, I have set grant, select, update, delete permissions for theasp page user account, so it doesn't appear to be a permissioning issue. Ifrun in Query Analyser the sp runs fine of course.Abridged asp code is as follows:StoredProc = Request.querystring("SP")oConn.ConnectionString = "Provider=SQLOLEDB etc"oConn.Openset oCmd = Server.CreateObject("ADODB.Command")oCmd.ActiveConnection = oConnoCmd.CommandText = StoredProcoCmd.CommandType = adCmdStoredProcoCmd.Parameters.Refresh'code here that populates the parameters of the oCmd object correctlySet oRs = Server.CreateObject("ADODB.Recordset")With oRS.CursorLocation = adUseClient.CursorType = adOpenStatic.LockType = adLockBatchOptimistic'execute the SP returning the result into a recordset.Open oCmdEnd With' Save data into IIS response objectResponse.ContentType = "text/xml"oRs.Save Response, adPersistXML'the line above fails with stored procs from example B below, reporting "notallowed when object is closed", but works with example ASP Example A - this one works fineCreate Proc spTestA ASSELECT ID FROM FileListGOSP Example B - this one doesn't work from ASP but runs fine in QACreate Proc spTestB ASDECLARE @Results Table (ID TinyInt)INSERT INTO @Results SELECT ID FROM FileListSELECT ID FROM @ResultsGOI can see the SP executing using profiler when the asp page is called forboth sp's above, so it doesn't appear to be a problem with the execution.It's something to do with returning the result set from the table variable.Thanks,Robin Hammond
View 1 Replies
View Related
Jul 20, 2005
Hi, not too swift with anything other than simple SQL statements, soI'm looking for some help.Using SQL Server 2000 with this stored proc:(@varCust varchar(50))ASSET NOCOUNT ONSELECT d.WorkOrder, d.Customer, d.SerialNo, d.Assy, d.Station,d.WIdoc,d.Start, d.StartUser, d.Finish, d.FinishUserFROM tblWorkOrder w, tblDocs dWHERE w.WorkOrder IS NULL AND w.WorkOrder = d.WorkOrder ANDd.Customer = @varCustGOI'm trying to get a complete dataset so I can simply apply it as thedatasource to a datagrid in asp.net. I need to include a 'TimeSpan'column that is the difference between d.Start and d.Finish. I alsoneed it to present in hh:mm:ss format in the datagrid column. (A) isit possible to do this within the stored proc, and (B) how would "I"do that?Thanks!Kathy
View 6 Replies
View Related
Oct 7, 2014
Following is the query that I'm running:
create table a (id int, name varchar(10));
create table b(id int, sal int);
insert into a values(1,'John'),(1,'ken'),(2,'paul');
insert into b values(1,400),(1,500);
select *
from a
cross apply( select max(sal) as sal from b where b.id = a.id)b;
Below is the result for the same:
idname sal
1John500
1ken500
2paulNULL
Now I'm not sure why the record with ID 2 is coming using CROSS APPLY, shouldn't it be avoided in case of CROSS APPLY and only displayed when using OUTER APPLY.
One thing that I noticed was that if you remove the Aggregate function MAX then the record with ID 2 is not shown in the output. I'm running this query on SQL Server 2012.
View 6 Replies
View Related
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
Nov 17, 2006
Following Proc is returning all records.I have passed any value to parameters but no luck.
CREATE PROCEDURE GetAllUsers(
@persontype varchar(100)="",
@name varchar(100)="",
@adddatetime datetime="",
@activeaccount int =-1,
@country varchar (20)=""
)
AS
declare @cond varchar(1000) ;
if @persontype<>""
begin
set @cond= @cond+"and persontype="+@persontype
end
if @name<>""
begin
set @cond= @cond+"and (charindex("+@name+",x_firstname)>0 or charindex("+@name+",x_lastname)>0 or charindex("+@name+",x_email)>0) "
end
if @activeaccount<>-1
begin
set @cond= @cond+'and activeaccount='+@activeaccount
end
if @adddatetime<>""
begin
set @cond= @cond+'and adddatetime='+@adddatetime
end
if @country<>""
begin
set @cond= @cond+'and x_country='+@country
end
print @cond
exec( " select * from users where 1=1 "+@cond)
GO
zx
View 2 Replies
View Related
Aug 24, 2006
I am having trouble executing a stored procedure on a remote server. On my
local server, I have a linked server setup as follows:
Server1.abcd.myserver.comSQLServer2005,1563
This works fine on my local server:
Select * From [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.dbo.TableName
This does not work (Attempting to execute a remote stored proc named 'Data_Add':
Exec [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.Data_Add 1,'Hello Moto'
When I attempt to run the above, I get the following error:
Could not locate entry in sysdatabases for database 'Server1.abcd.myserver.comSQLServer2005,1563'.
No entry found with that name. Make sure that the name is entered correctly.
Could anyone shed some light on what I need to do to get this to work?
Thanks - Amos.
View 3 Replies
View Related
Jun 15, 2006
Hi All,Quick question, I have always heard it best practice to check for exist, ifso, drop, then create the proc. I just wanted to know why that's a bestpractice. I am trying to put that theory in place at my work, but they areasking for a good reason to do this before actually implementing. All Icould think of was that so when you're creating a proc you won't get anerror if the procedure already exists, but doesn't it also have to do withCompilation and perhaps Execution. Does anyone have a good argument fordoing stored procs this way? All feedback is appreciated.TIA,~CK
View 3 Replies
View Related
Feb 23, 2007
I have an ASP that has been working fine for several months, but itsuddenly broke. I wonder if windows update has installed some securitypatch that is causing it.The problem is that I am calling a stored procedure via an ASP(classic, not .NET) , but nothing happens. The procedure doesn't work,and I don't get any error messages.I've tried dropping and re-creating the user and permissions, to noavail. If it was a permissions problem, there would be an errormessage. I trace the calls in Profiler, and it has no complaints. Thedatabase is getting the stored proc call.I finally got it to work again, but this is not a viable solution forour production environment:1. response.write the SQL call to the stored procedure from the ASPand copy the text to the clipboard.2. log in to QueryAnalyzer using the same user as used by the ASP.3. paste and run the SQL call to the stored proc in query analyzer.After I have done this, it not only works in Query Analyzer, but thenthe ASP works too. It continues to work, even after I reboot themachine. This is truly bizzare and has us stumped. My hunch is thatwindows update installed something that has created this issue, but Ihave not been able to track it down.
View 1 Replies
View Related
Jan 10, 2007
I hvae a stored procedure that has this at the end of it:
BEGIN
EXEC @ActionID = ActionInsert '', @PackageID, @AnotherID, 0, ''
END
SET NOCOUNT OFF
SELECT Something
FROM Something
Joins…..
Where Something = Something
now, ActionInsert returns a Value, and has a SELECT @ActionID at the end of the stored procedure.
What's happening, if that 2nd line that I pasted gets called, 2 result sets are being returned. How can I modify this SToredProcedure to stop returning the result set from ActionINsert?
View 2 Replies
View Related
Feb 20, 2003
I have seen this done by viewing code done by a SQL expert and would like to learn this myself. Does anyone have any examples that might help.
I guess I should state my question to the forum !
Is there a way to call a stored proc from within another stored proc?
Thanks In Advance.
Tony
View 1 Replies
View Related
Jan 13, 2006
Hi all,
I have a stored procedure "uspX" that calls another stored procedure "uspY" and I need to retrieve the return value from uspY and use it within uspX. Does anyone know the syntax for this?
Thanks for your help!
Cat
View 5 Replies
View Related
Jan 20, 2004
Hi all
I have about 5 stored procedures that, among other things, execute exactly the same SELECT statement
Instead of copying the SELECT statement 5 times, I'd like each stored proc to call a single stored proc that executes the SELECT statement and returns the resultset to the calling stored proc
The SELECT statement in question retrieves a single row from a table containing 10 columns.
Is there a way for a stored proc to call another stored proc and gain access to the resultset of the called stored proc?
I know about stored proc return values and about output parameters, but I think I am looking for something different.
Thanks
View 14 Replies
View Related
Aug 30, 2007
I would like to know if the following is possible/permissible:
myCLRstoredproc (or some C# stored proc)
{
//call some T SQL stored procedure spSQL and get the result set here to work with
INSERT INTO #tmpCLR EXECUTE spSQL
}
spSQL
(
INSERT INTO #tmpABC EXECUTE spSQL2
)
spSQL2
(
// some other t-sql stored proc
)
Can we do that? I know that doing this in SQL server would throw (nested EXECUTE not allowed). I dont want to go re-writing the spSQL in C# again, I just want to get whatever spSQL returns and then work with the result set to do row-level computations, thereby avoiding to use cursors in spSQL.
View 2 Replies
View Related
Nov 5, 2004
i am using .NET front end and back end SQL Server 2k.
for insert, update, delete and select queries what should i use stored procedure or direct sql through front end.
View 10 Replies
View Related
