Transact SQL :: Stored Procedure Not Returning Dataset (No Columns Are Coming)
Jun 3, 2015
We are facing an issue while executing a stored procedure which uses a table of current database with INNER JOIN a table of another database in same instance.
Per our requirement, we are inserting select statement output in table variable. Then applying business logic and finally showing the data from table variable.
This scenario is working exactly fine in Dev environment. But when we deployed the code in quality environment. Stored procedure does not returning OUTPUT/ (No column names) from table variable.
During initial investigation, we found that collation of these two databases are different but we added DATABASE_DEFAULT collation in the JOIN.
I have written a SQL Procedure to return all the results from a table and am writing a function to run the procedure (So that it is easier to use within my app). I have kinda got a bit confused. :oS
The SQL procedure Gallery_GetAllCetegoryPictures has one input variable (CategoryID) and returns a table procedure is something like SELECT * FROM Gallery WHERE CategoryID = @CategoryID.
The code im having trouble with is below:
Function GetAllCategoryPictures(ByVal CatID As Integer) As DataSet Dim MyConnection As SqlConnection Dim MyCommand As New SqlCommand Dim MyParameter As SqlParameter MyConnection = New SqlConnection(AppSettings("DSN")) MyConnection.Open()
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?
My task is to bind and show 3 different values coming from three different queries into three different columns of GridView. I had done this as mention in below. Program was successful. But I want to excute these three queries in same Stored Procedure. I can do that and stored in seperated variables. I need help how to call these three different values in data adapters and store each value in three different columns of grid view.
Simply I want to below statement in stored procedures and call from program. Can any one help me plz.
con = DataBaseConnection.GetConnection();
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter("select isnull(sum(PA_DAmt),0) from PA_Deposits where PA_UID = @PA_UID", con); da.SelectCommand.Parameters.Add("@PA_UID", SqlDbType.Int).Value = Convert.ToInt32(Session["PA_UID"]); da.Fill(ds,"Dep");
SqlDataAdapter da1 = new SqlDataAdapter("select isnull(sum(PA_EAmt),0) from PA_Expenses where PA_UID = @PA_UID", con); da1.SelectCommand.Parameters.Add("@PA_UID", SqlDbType.Int).Value = Convert.ToInt32(Session["PA_UID"]); da1.Fill(ds,"Exp");
SqlDataAdapter da2 = new SqlDataAdapter("select isnull(sum(PA_IAmt),0) from PA_Income where PA_UID = @PA_UID", con); da2.SelectCommand.Parameters.Add("@PA_UID", SqlDbType.Int).Value = Convert.ToInt32(Session["PA_UID"]); da2.Fill(ds,"Inc");
// This is another way I had tried. But failed. I am getting Index out of bound error. Can any solve this if possible to u. SqlCommand cmd = new SqlCommand("select isnull(sum(PA_DAmt),0) from PA_Deposits where PA_UID = @PA_UID", con); cmd.Parameters.Add("@PA_UID", SqlDbType.Int).Value = (int)Session["PA_UID"]; GridView1.Rows[0].Cells[1].Text = cmd.ExecuteScalar().ToString();
cmd.CommandText = "select isnull(sum(PA_EAmt),0) from PA_Expenses where PA_UID = @PA_UID"; GridView1.Rows[0].Cells[2].Text = cmd.ExecuteScalar().ToString();
cmd.CommandText = "select isnull(sum(PA_IAmt),0) from PA_Income where PA_UID = @PA_UID"; GridView1.Rows[0].Cells[3].Text = cmd.ExecuteScalar().ToString();
ASPX Code for Grid View <h2>Account Summary</h2><br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" emptydatatext="There are no data records to display." Width="238px" > <Columns> <asp:TemplateField >
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)
I am calling stored procedure called GetCommonItemCount within another stored procedure called CheckBoxAvailability, the first stored procedure should return a count to second stored procedure and based on that some logic will be executed.
I have 2 problems in that
1. The result is not coming from first stored so the variable called @Cnt is always 0 although it should be 18 2. At the end i need to see in the output the result from second stored procedure only while now i am seeing multiple results of record sets coming.
I have attached the scripts also, the line i described in step1 is
I'm having a very frustrating problem that no amount of formatting via properties and/or expression seems to be clearing up.
I have a column in the dataset that is data typed decimal (6,4) (from the SQL query). The numeric value is, for example, being returned to the data set as 0.6500.
The problem is the report wants to just show zero. Formatting efforts to date have only yielded, '0', '0.00', & '0.0000'.
I am trying to run an update stored procedure that will add 1 revision to the rev field and return the Value back to my Application. My number is incrementing by 2 and not 1. Here is my Stored Procedure CREATE PROCEDURE dbo.sp_Update_file @kbid big,@filename nvarchar(50),@rev big OUTPUT,@moddate datetime,@owner nvarchar(50),@author nvarchar(50)AsUPDATE KBFile SET rev = rev + 1,filename = @filename,moddate = @moddate,owner = @owner,author = @author,@rev = (Select rev from kbfile where kbid = @kbid)WHERE kbid = @kbIDGO
Ok, still a little bit novice on stored procedures, and can't see why this one doesn't return the value I need.In this case, lessonLocation.CREATE PROCEDURE dbo.getLocation @studentId VARCHAR(20), @lessonLocation VARCHAR(50) OUTPUTASBEGIN DECLARE @errCode INT SELECT lessonLocation FROM cmiDataModel WHERE studentId = @studentId SET @errCode = 0 RETURN @errCode RETURN @lessonLocation HANDLE_APPERR: SET @errCode = 1 RETURN @errCodeHANDLE_DBERR: SET @errCode = -1 RETURN @errCodeENDGOIn the query analyzer, it returns the value in the db, but always blank when run in the .Net app.Thanks all,Zath
Hi everyone! I am new to sql server 2005 and visual studio 2005.
I have the following simple stored procedure that checks if a user exists: ------------------------------------------------------------------------------------------------------- ALTER PROCEDURE [dbo].[sp_Users_AlreadyExists]
@UserName varchar(256) AS BEGIN SET NOCOUNT ON;
IF (EXISTS (SELECT UserName FROM dbo.Users WHERE LOWER(@UserName) = LoweredUserName )) RETURN(1) ELSE RETURN(0) END -------------------------------------------------------------------------------------------------------
I use the following code to execute the procedure on visual studio: ------------------------------------------------------------------------------------------------------- . . . cmdobj As SqlCommand cmdobj = New SqlCommand(sp_Users_AlreadyExists, connobj) cmdobj.CommandType = CommandType.StoredProcedure cmdobj.Parameters.AddWithValue("@UserName", "blablalala") cmdobj.ExecuteNonQuery() cmdobj.Dispose() connobj.Close() . . . -------------------------------------------------------------------------------------------------------
I expected that cmdobj.ExecuteNonQuery() would return 1 if the user blablab exists or 0 if the user doesnt, but it just return -1 (i think because no row was affected)
Does anyone knows how to retrieve the value that my stored procedure returns?
Trying to get a count on records that match search and all I'm getting is 0. I'm using the same basic sp and code elsewhere and it works fine. Anyone see anything wrong here? Stored Procedure:CREATE PROCEDURE GetResultsCount(@searchCatalog nVarchar(100))ASRETURN ( SELECT COUNT(*) FROM CatalogWHERE itemName LIKE @searchCatalog ORitemLongDesc LIKE @searchCatalog)GO Code: Dim connStr As SqlConnection Dim cmdResultsCount As SqlCommand Dim paramReturnCount As SqlParameter Dim intResultsCount As Integer connStr = New SqlConnection(ConfigurationSettings.AppSettings("sqlCon.ConnectionString")) cmdResultsCount = New SqlCommand("GetResultsCount", connStr) cmdResultsCount.CommandType = CommandType.StoredProcedure cmdResultsCount.Parameters.Add("@searchCatalog", Request.QueryString("search")) paramReturnCount = cmdResultsCount.Parameters.Add("ReturnValue", SqlDbType.Int) paramReturnCount.Direction = ParameterDirection.ReturnValue connStr.Open() cmdResultsCount.ExecuteNonQuery() intResultsCount = cmdResultsCount.Parameters("ReturnValue").Value connStr.Close() Me.lblResultsCount.Text = intResultsCount
Im creating an app in VB.NET that calls a stored procedure which inserts data into one of my tables. I need it to return the id it creates (autonumber) to the VB.NET Program
I keep raising an exception in VB.NET...I want to make sure my stored procedure is correct first...sadly this is my first procedure ive created.
If I pass in the value of 1, the stored procedure should return the value of (.5360268), else it should return 0 for any other value(null/blank/empty/etc)
The following stored procedure is returning the value 0 if I pass in the value of 1 which is wrong. And I would need to set to 0 for any other value. I am checking only for null or empty, but the if condition should check any value (except 1) and return 0 for it.
ALTER PROCEDURE [dbo].[Calculator] ( @ExtraCardiacArteriopathy bit ) AS BEGIN declare @zarterio decimal(14,10)
Hi Guys, I have a series of count statements in a Stored Procedure that return some values. What I want to do is take all of the values that have been returned by the Count statements, add them up and return the value to my .Net Code, but for some reason whenever I try I carry on getting a returned value of 0.
I have checked that the statements are in fact returning values other than 0, and I was wondering if there is something that I am doing wrong in the code below:
<code> CREATE PROCEDURE [dbo].[UCOutstanding] @userid int AS DECLARE @Num as Int DECLARE @Num2 as Int DECLARE @Num3 as Int DECLARE @Num4 as Int
SET @Num = (SELECT count(*) FROM images, albums, memorial WHERE (images.active=0 AND images.albumid=albums.id AND albums.memorialid=memorial.id AND memorial.userid=@userid)) SET @Num2 = (SELECT count(*) FROM downloads, memorial WHERE (downloads.active=0 AND downloads.memorialid=memorial.id AND memorial.userid=@userid)) SET @Num3 = (SELECT count(*) FROM images, comments, albums, memorial WHERE (comments.active=0 AND comments.imageid=images.id AND images.albumid=albums.id AND albums.memorialid=memorial.id AND memorial.userid=@userid)) SET @Num4 = (SELECT count(*) FROM memorial, story WHERE (story.memorialID=memorial.id and memorial.userid=@userid))
DECLARE @Total as Int SET @Total = @Num + @Num2 + @Num3 + @Num4
I have a Sproc shown below I want the procedure to return the New_ID value back to the calling program how can I do this. The current approach is not working. Please help. set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ................................. ALTER PROCEDURE [dbo].[SP] ( @Id int=NULL , @site_id int = null, @Date datetime = null, ) AS BEGIN Declare @New_ID varchar(10) EXEC [dbo].[SP_GENERATEID] @New_ID output INSERT INTO A_Table( CODE,INT_ID,SITE_INT_ID,DATE, ) Values ( @New_ID,@Id,@site_id,@Date ) return @New_ID END Thanks
Hi All.Maybe someone in here could help on this too....Uusally I can return a value from a stored procedure without any problem.Today I ran into something I cannot figure out.Basically....what I am doing is a couple of inserts or updates depending onwhat is being passed.So in the storedproc tag, I am passing the necessary values along with 1output param. I was using an INOUT param, but I figured I would play itsafe since it wasn't working.So...in the stored procedure, I do some conditions inside transactionstatements...I don't have the code with me right now as I am home, but I figure if I cangive you the general idea, you may know what the problem is.So I have something like this...BEGIN TRANIF team_ID is nullBEGINIF target_ID > 0BEGIN--- INSERT processingSET @OutPutVariable = Scope_Identity()ENDIF target_ID < 0BEGIN--- INSERT processingSET @OutPutVariable = Scope_Identity()ENDENDIF team_ID is not nullBEGIN-- UPDATE ProcessingENDCOMMIT TRANSELECT @OutPutVariableIf I run this procedure through enterprise, i get what I need....the valueof the last inserted record. When I do it through CF, I always get 0 ORnothing at all.If I do a SELECT 100, I get a return value of 100 of course, so it seemslike it's out of scope.Any ideas?
Hello and thank you for taking a moment to read this message. I am simply trying to use a stored procedure to set up a dataset. For some reason when I try to fill the dataset with the data adapter I get the following error: Compiler Error Message: BC30638: Array bounds cannot appear in type specifiers.Line 86: ' Create the Data AdapterLine 87: Dim objadapter As SQLDataAdapter(mycommand2, myconnection2) my code looks as follows for the dataset:<script runat="server">Sub ListSongs() ' Dimension Variables in order to get songs Dim myConnection2 as SQLConnection Dim myCommand2 as SQLCommand Dim intID4 As Integer 'retrieve albumn ID for track listings intID4 = Int32.Parse (Request.QueryString("id")) ' Create Instance of Connection myConnection2 = New SqlConnection( "Server=localhost;uid=jazz***;pwd=**secret**;database=Beatles" ) myConnection2.Open() 'Create Command object Dim mycommand2 AS New SQLCommand( "usp_Retrieve song_",objCon) mycommand2.CommandType = CommandType.StoredProcedure mycommand2.Parameters.Add("@ID", intID4) ' Create the Data Adapter (this is where my code fails, not really sure what to do) Dim objadapter As SQLDataAdapter(mycommand2, myconnection2) 'Use the Fill() method to create and populate a datatable object into a dataset. Table will be called dtsongs Dim objdataset As DataSet() objadapter.Fill(objdataset, "dtsongs") 'Bind the datatable object called dtsongs to our Datagrid: dgrdSongs.Datasource = objdataset.Tables("dtsongs") dgrdsongs.DataBind()</script><html><head> <title>Albumn Details</title></head><body style="FONT: 10pt verdana" bgcolor="#fce9ca"><center> <asp:DataGrid id="dgrdSongs" Runat="Server" ></ asp:DataGrid> </center></body></html> Any help or advice would be greatly appreciated. Thank You - Jason
I have a stored procedure below that returns a table of coaches. It worked before now it does not. It returns nothing, in vistual studio 2005 and on my asp.net webpage. But when I execute it in query analyzer with just SELECT * FROM Coaches it returns the rows. There is no error just will not return what I need. I recreated the database and stored procedure still doing the same thing. Any ideas? Would this be on my server hosting side? ALTER PROCEDURE [dbo].[GetCo]ASSELECT * FROM Coaches
Hi,I'm creating a stored procedure that pulls information from 4 tables based on 1 parameter. This should be very straightforward, but for some reason it doesn't work.Given below are the relevant tables: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[tbl_Project]( [ProjID] [varchar](300) NOT NULL, [ProjType] [varchar](20) NULL, [ProjectTitle] [varchar](max) NULL, [ProjectDetails] [varchar](max) NULL, [ProjectManagerID] [int] NULL, [RequestedBy] [varchar](max) NULL, [DateRequested] [datetime] NULL, [DueDate] [datetime] NULL, [ProjectStatusID] [int] NULL, CONSTRAINT [PK__tbl_Project__0B91BA14] PRIMARY KEY CLUSTERED ( [ProjID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[tbl_Project] WITH CHECK ADD CONSTRAINT [FK_tbl_Project_tbl_ProjectManager] FOREIGN KEY([ProjectManagerID]) REFERENCES [dbo].[tbl_ProjectManager] ([ProjectManagerID]) GO ALTER TABLE [dbo].[tbl_Project] CHECK CONSTRAINT [FK_tbl_Project_tbl_ProjectManager] GO ALTER TABLE [dbo].[tbl_Project] WITH CHECK ADD CONSTRAINT [FK_tbl_Project_tbl_ProjectStatus] FOREIGN KEY([ProjectStatusID]) REFERENCES [dbo].[tbl_ProjectStatus] ([ProjectStatusID]) GO ALTER TABLE [dbo].[tbl_Project] CHECK CONSTRAINT [FK_tbl_Project_tbl_ProjectStatus]
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[tbl_Report]( [ReportName] [varchar](50) NOT NULL, [ProjID] [varchar](300) NULL, [DeptCode] [varchar](50) NULL, [ProjType] [varchar](50) NULL, [ProjectTitle] [varchar](500) NULL, [ProjectDetails] [varchar](3000) NULL, [ProjectManagerID] [int] NULL, [RequestedBy] [varchar](50) NULL, [DateRequested] [datetime] NULL, [DueDate] [datetime] NULL, [ProjectStatusID] [int] NULL, CONSTRAINT [PK_tbl_Report] PRIMARY KEY CLUSTERED ( [ReportName] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[tbl_Report] WITH CHECK ADD CONSTRAINT [FK_tbl_Report_tbl_ProjectManager] FOREIGN KEY([ProjectManagerID]) REFERENCES [dbo].[tbl_ProjectManager] ([ProjectManagerID]) GO ALTER TABLE [dbo].[tbl_Report] CHECK CONSTRAINT [FK_tbl_Report_tbl_ProjectManager] GO ALTER TABLE [dbo].[tbl_Report] WITH CHECK ADD CONSTRAINT [FK_tbl_Report_tbl_ProjectStatus] FOREIGN KEY([ProjectStatusID]) REFERENCES [dbo].[tbl_ProjectStatus] ([ProjectStatusID]) GO ALTER TABLE [dbo].[tbl_Report] CHECK CONSTRAINT [FK_tbl_Report_tbl_ProjectStatus]
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[tbl_ProjectStatus]( [ProjectStatusID] [int] IDENTITY(1,1) NOT NULL, [ProjectStatus] [varchar](max) NULL, CONSTRAINT [PK__tbl_ProjectStatu__023D5A04] PRIMARY KEY CLUSTERED ( [ProjectStatusID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[tbl_ProjectManager]( [ProjectManagerID] [int] IDENTITY(1,1) NOT NULL, [FName] [varchar](50) NULL, [LName] [varchar](50) NULL, [Inactive] [int] NULL, CONSTRAINT [PK__tbl_ProjectManag__7D78A4E7] PRIMARY KEY CLUSTERED ( [ProjectManagerID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
GO SET ANSI_PADDING OFF
And here is the stored procedure that I wrote (doesn't return results): SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[GetReportQuery] ( @ReportName varchar(100) )
AS
BEGIN
SET NOCOUNT ON
IF @ReportName IS NULL BEGIN RETURN -1 END ELSE BEGIN
DECLARE @DeptCode varchar(50), @ProjID varchar(50) SELECT @DeptCode = DeptCode FROM tbl_Report WHERE ReportName = @ReportName
SET @ProjID = @DeptCode + '-' + '%'
SELECT P.ProjID, P.ProjType, P.ProjectTitle, P.ProjectDetails, M.FName, M.LName, P.DateRequested, P.DueDate, S.ProjectStatus FROM tbl_Project P, tbl_ProjectManager M, tbl_ProjectStatus S WHERE ((P.ProjID = (SELECT ProjID FROM tbl_Report WHERE ((ReportName = @ReportName)))) AND (P.ProjectDetails = (SELECT ProjectDetails FROM tbl_Report WHERE ReportName = @ReportName) OR P.ProjectDetails IS NULL) AND (M.FName = (SELECT FName FROM tbl_ProjectManager WHERE (ProjectManagerID = (SELECT ProjectManagerID FROM tbl_Report WHERE ReportName = @ReportName))) OR M.FName IS NULL) AND (M.LName = (SELECT LName FROM tbl_ProjectManager WHERE (ProjectManagerID = (SELECT ProjectManagerID FROM tbl_Report WHERE ReportName = @ReportName))) OR M.LName IS NULL) AND (P.DateRequested = (SELECT DateRequested FROM tbl_Report WHERE ReportName = @ReportName) OR P.DateRequested IS NULL) AND (P.DueDate = (SELECT DueDate FROM tbl_Report WHERE ReportName = @ReportName) OR P.DueDate IS NULL) AND (S.ProjectStatus = (SELECT ProjectStatusID FROM tbl_Report WHERE ReportName = @ReportName) OR S.ProjectStatus IS NULL) ) END
Hi, How to return values from stored procedures?? I have a value whose variable would be set thru this sp and it should return this value. How to do this?
Anyone know how to return a sql command from a sql server stored procedure using asp.net c# and sql server 2000?I'm trying to debug the stored proc and thought the easiest way would be to return the insert command and debug it in the query analyzer.Thanks.Doug.
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
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)
This is a strange issue I'm encountering. I have a websphere application that calls a basic Stored Procedure in SQL Server 2000. The SP works fine. Now, if I create a TEMP table in the SP, I no longer get a resultset. No results are returned by the websphere. Even if I do not use the temp table... that is, I just create the temp table like so: SELECT region_code, OperGroup_Code, country_name, MajorGroup_Name, RptgEntity_Name, shell_code, CRC, ' ' as right_type INTO #tmpTShell FROM TShell WHERE 1=0
But I then grab my records using the query that works (querying another table), I still get NULL for records returned. Only when I comment out the above TEMP table creation do I get the results back in websphere. Another strange thing is that this works fin in SQL analyzer. WRegardless of whether I have the temp table creation commented out or not, it always works in the Query Analyzer whe I call the SP. It just seems to effect WebSphere or my java code in that it returns null if I create the temp table.
Has anyone ever experienced anthying like this? Any help would be greatly appreciated!
I can't even think how I would go about beginning to solve this problem, so any tips/pointers would help immensely.
I'm running a query that builds a time line of a process using messages in a table called Event. The Event table is joined with a table called Charge_Info based on N_Charge, the charge number. It builds the time line by looking in the Message field for various text strings that signify a specific point has been reached in the process and builds a record for each charge number with data from N_Charge and timestamps from the events table.
The problem I have is that there aren't records for every event in the Event table, ie, for every charge that doesn't have a "Heating Complete" event, the returned recordset has no mention of that charge. The system recording these events is pretty much crap, but there's nothing to be done about that right now. What I want to happen is for the query to return every record in N_Charge and a null string or NULL in place of the non-existing events.
The stored procedure is pasted below. I pass the dates I want and select from the Event table into a temporary table and then do the join/text search on that table. It helped performance tremendously compared to the text search searching all 400,000+ records in the Event table and then doing the date select.
Thanks for any help in advance, TimC
SELECT EVENT.Time_Stamp, EVENT.N_CHARGE, EVENT.Message INTO #EVENT_FILTERED FROM EVENT WHERE (EVENT.TIME_STAMP >= @StartDate) AND (EVENT.TIME_STAMP < @EndDate)
SELECT CHARGE_INFO.TIME_STAMP, CHARGE_INFO.N_CHARGE, CHARGE_INFO.BASE, CHARGE_INFO.N_RECIPE, CHARGE_INFO.N_FCE, CHARGE_INFO.N_CH, CHARGE_INFO.HEIGHT, CHARGE_INFO.CREW_EOC, CHARGE_INFO.CREW_SOC, CHARGE_INFO.TIME_START, CHARGE_INFO.TIME_FCE_SET, CHARGE_INFO.TIME_FCE_IGNITED, CHARGE_INFO.TIME_FCE_REMOVED, CHARGE_INFO.TIME_CH_SET, CHARGE_INFO.TIME_CH_REMOVED, CHARGE_INFO.WEIGHT, CHARGE_INFO.TIME_POST_PRG_COMPLETE, EVENT.TIME_STAMP AS IC_Set, EVENT_1.TIME_STAMP AS Cycle_Started, EVENT_2.TIME_STAMP AS Leak_Test_Done, EVENT_3.TIME_STAMP AS End_N2_PrePurge, EVENT_4.TIME_STAMP AS Heating_Complete, EVENT_5.TIME_STAMP AS Split_Temp_Met, EVENT_6.TIME_STAMP AS End_N2_Final_Purge, EVENT_7.TIME_STAMP AS Inner_Cover_Removed, EVENT_8.TIME_STAMP AS Cycle_Complete, EVENT_9.TIME_STAMP AS Post_Purge_Time_Met
FROM dbo.CHARGE_INFO CHARGE_INFO LEFT OUTER JOIN #EVENT_FILTERED EVENT_9 ON CHARGE_INFO.N_CHARGE = EVENT_9.N_CHARGE LEFT OUTER JOIN #EVENT_FILTERED EVENT_7 ON CHARGE_INFO.N_CHARGE = EVENT_7.N_CHARGE LEFT OUTER JOIN #EVENT_FILTERED EVENT_6 ON CHARGE_INFO.N_CHARGE = EVENT_6.N_CHARGE LEFT OUTER JOIN #EVENT_FILTERED EVENT_8 ON CHARGE_INFO.N_CHARGE = EVENT_8.N_CHARGE LEFT OUTER JOIN #EVENT_FILTERED EVENT_5 ON CHARGE_INFO.N_CHARGE = EVENT_5.N_CHARGE LEFT OUTER JOIN #EVENT_FILTERED EVENT_3 ON CHARGE_INFO.N_CHARGE = EVENT_3.N_CHARGE LEFT OUTER JOIN #EVENT_FILTERED EVENT_1 ON CHARGE_INFO.N_CHARGE = EVENT_1.N_CHARGE LEFT OUTER JOIN #EVENT_FILTERED EVENT_4 ON CHARGE_INFO.N_CHARGE = EVENT_4.N_CHARGE LEFT OUTER JOIN #EVENT_FILTERED EVENT_2 ON CHARGE_INFO.N_CHARGE = EVENT_2.N_CHARGE LEFT OUTER JOIN #EVENT_FILTERED EVENT ON CHARGE_INFO.N_CHARGE = EVENT.N_CHARGE
WHERE (EVENT.MESSAGE LIKE '%Inner Cover Set%') AND (EVENT_1.MESSAGE LIKE '%Cycle Started%') AND (EVENT_2.MESSAGE LIKE '%Leak Test Done%') AND (EVENT_3.MESSAGE LIKE '%End of N2 PrePurge%') AND (EVENT_4.MESSAGE LIKE '%Heating Complete%') AND (EVENT_5.MESSAGE LIKE '%Split Temp Met%') AND (EVENT_6.MESSAGE LIKE '%End N2 Final%') AND (EVENT_7.MESSAGE LIKE '%Inner Cover Removed%') AND (EVENT_8.MESSAGE LIKE '%Cycle Complete%') AND (EVENT_9.MESSAGE LIKE '%Post Purge Time Met%') AND (CHARGE_INFO.TIME_STAMP >= @StartDate) AND (CHARGE_INFO.TIME_STAMP < @EndDate)
ORDER BY CHARGE_INFO.BASE, CHARGE_INFO.TIME_STAMP DESC
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
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
SELECT Facilities=@V_Facilities From UserLoginFacilities where LoginID=(Select LoginID From UserLogin where LoginName=@pStrUserName and Password=@pStrPassword)
If(@V_Facilities=null)
Set @pOutput = @V_Facilities
Return @pOutput;
Else
Set @pOutput = @V_Facilities
Return @pOutput;
GO
Anyone correct this query , I want return the output from this procedure
I am currently trying to write a number of processe's to keep track of what information is held in my SSIS package. The package I have created is rather large and it would prove a long labourious process to look through every task to see what stored procedure has been used.
What I wanted to do was write a stored procedure in SQL Server 2005 that pick's up each package name and checks for any stored procedures used and returns the names of these stored procedures and any other relevant information i.e required variables.
So far I have managed to create a stored procedure that picks up the name(s) of the packages but I am stuck after this.
