Validation Of Returned Order Of Fields From A Stored Proc
Jul 17, 2006
Hi,
Can we validate the returned order of fields from a stored procedure? Infact, i am taking a query as user input and extracts the results based on the query but for that order of fields specified in a query is important.
Can i check the order after the query is run i.e if this is entered "select field1,field2,field3 from table" then i need to check the order of the resultset generated. I can't check the query before the resultset is generated because a user can enter bunch of queries.
Any way will work, tsql or .net app.
Thanks,
View 3 Replies
ADVERTISEMENT
Apr 16, 2007
Hi,
Summary: When my stored procedure uses temporary tables then the TableAdapter won't be able to work out the field names and so won't work. I get an error in the TableAdapter configure wizard saying: Invalid object name '#TempTable'.
I'm not doing anything unusual so this must be a common problem. Let me explain:
I'm using Visual Studio 2005 and SQL Server 2000.
Detail: I've written a new stored procedure (SP) that uses a temporary table in calculating the resulting results set (several fields with several rows). I recon the temporary table bit is significant.
I've created a new DataSet in VS2005 and dragged the stored proc onto the DataSet design surface.
I right click on the TableAdapter and enter the 'configure'. The problem is that the wizard doesn't think any fields are being returned by the SP.
If I try and do it another way I get the same problem: Right click on DataSet and add new TableAdapter (same thing happens, it won't recognise that there are fields being returned from the SP).
FYI: If I do it for an SP that doesn't use any temporary tables it all works like a dream (problem is that I need to use temporary tables as its complex ).
Thanks for any advise
View 7 Replies
View Related
Feb 26, 2006
I have a stored proc with a query which checks whether an identical
value is already in the database table. If so, it returns a value of 1.
How do I caputure this value in an asp.net page in order to display a
message accordingly? (using ASP.NET 1.1)
Currently my stored proc looks something like this (snippet only):
If Exists(
SELECT mydoc WHERE....
)
Return 1
Else
...INSERT INTO.... code here.
View 2 Replies
View Related
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
Feb 5, 2008
I have a stored procedure that returns a resultset AND an output parameter, pseudocode:myspGetPoll@pollID int,@totalvoters int outputselect questionID,question from [myPoll] where pollID=@pollID @totalvoters=(select count(usercode) from [myPoll] where pollID=@pollID)1. In my code behind I'd like to read both the rows (questionID and question) as well as total results (totalvoters) How could I do so?2. what would be the signature of my function so that I can retreive BOTH a resultset AND a single value?e.g.: private function getPollResults(byval pollID as integer, byref totalvoters as integer) as datasetwhile reader.read dataset.addrow <read from result>end whiletotalvoters=<read from result>end functionThanks!
View 2 Replies
View Related
Apr 6, 2000
Can you put an ORDER BY clause in a stored procedure? What I'd like to do is have a stored procedure where the proc could be called, with an ORDER BY clause passed on as a variable,
as in:
CREATE PROCEDURE dbo.select_all_from_users
@order_by varchar(100)
AS
SELECT * from USERS
ORDER BY @order_by;
This doesn't work, I get the following nastygram thrown in my face "Error 1008: The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression itentifying a column position. Variables are only allowed when ordering by an expression refrencing a column name."
That's where I'm stuck. The variable @order_by WILL be refrencing a column name, at least it will in my opinion, but the SQL Server doesn't think so...
Any ideas or workarounds?
Alan McCollough
View 1 Replies
View Related
May 9, 2008
I have a stored procedure that is going to be called from my ssis job. I create a package and it as an 'Execute SQL Task' within it. My stored procedure will return a 1 or 0. If 1 is returned then the package needs to continue processing. If
0 is returned then the job stops running.
How can I get the value that is being returned from the stored procedure and either continue or stop the job depending on what is returned?
View 19 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
Dec 21, 1999
Upon entering data into the database, my fields fill the entire length specified.
For Example a name field with char type and allocated for 50 spaces when entered with SAM returns SAM and 47 spaces. When I have to modify the record, I need to first delete the 47 spaces. Any ideas as to why this is happening or what I can do to alleviate the problem? This happens with all types and lengths.
Thanks.
View 1 Replies
View Related
Apr 3, 2008
In C#, when I'm using an SqlDataReader to run a simple select query and use the values returned I have a question about referencing the field values.
There seem to be a large number of "Get" type methods (i.e reader.GetValue(int i), etc.) All of them (that I have found so far, require the use of the index of the field (GetValue(0) is the first field and so on).
Is there a way to use the actual name of the field? (i.e. GetValue("myField"))? This helps code readability but the big reason is that changes to the underlying field list can completely hose up the application.
I know there are such functions in VB, but I just can't seem to find the equivelants in C#.
View 4 Replies
View Related
Oct 19, 2006
Hello--
For extracting the link structure of a dependency network with a large number of nodes (for problems having a large number of variables), we have been using the stored procedure:
System.Microsoft.AnalysisServices.System.DataMining.DecisionTreesDepNet.DTGetNodeGraph('{model-name}', value)
The stored procedure returns a resultset with columns: [Node_type], [Node_unique_name_1], [Node_unique_name_2], and [MSOLAP_NODE_SCORE]
Are there any pointers, references or descriptions of the values of [MSOLAP_NODE_SCORE]?
Thanks,
- Paul
View 5 Replies
View Related
Sep 10, 2007
I have the follow table.
/****** Object: Table [dbo].[deletethisTempOut] Script Date: 09/10/2007 09:20:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[deletethisTempOut](
[ThemeName] [varchar](60) NULL,
[intLocationCount] [int] NULL,
[dblRepValueA] [float] NULL,
[dblRepValueB] [float] NULL,
[dblRepValueC] [float] NULL,
[dblRepValueD] [float] NULL,
[dblTotalRepValue] [float] NULL,
[dblLimit1] [float] NULL,
[dblLimit2] [float] NULL,
[dblLimit3] [float] NULL,
[dblLimit4] [float] NULL,
[dblTotalLimit] [float] NULL,
[fltEmployeecount] [float] NULL,
[intAreaLevel1] [tinyint] NOT NULL,
[strFullName] [varchar](13) NOT NULL,
[strAreaLevel2] [varchar](20) NOT NULL,
[strAreaLevel3] [varchar](20) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
If I use the following SQL:
SELECT ThemeName, intLocationCount, dblRepValueA, dblRepValueB, dblRepValueC, dblRepValueD, dblTotalRepValue, dblLimit1, dblLimit2, dblLimit3,
dblLimit4, dblTotalLimit, fltEmployeecount, intAreaLevel1, strFullName, strAreaLevel2, strAreaLevel3
FROM deletethisTempOut
ORDER BY strAreaLevel2, strAreaLevel3
GET Following correct results:
Adair
284
899989594
0
574857716
190479902
1665327212
0
0
0
0
1665327212
0
1
United States
1
1
IF I use the following SQL I get the wrong results:
SELECT ThemeName, intLocationCount, dblRepValueA, dblRepValueB, dblRepValueC, dblRepValueD, dblTotalRepValue, dblLimit1, dblLimit2, dblLimit3,
dblLimit4, dblTotalLimit, fltEmployeecount, intAreaLevel1, strFullName, strAreaLevel2, strAreaLevel3
FROM deletethisTempOut
ORDER BY ThemeName
WRONG results:
Adair
74
81733110
0
49616018
24671651
156020779
50510500
0
0
0
203870779
0
1
United States
50
1
Adair
437
1468698657
0
495479839
353202768
2317381264
12984266
0
0
0
2315676030
0
1
United States
25
1
Adair
1813
20309722045
0
6597005374
4253819645
31160547064
43636703
0
0
0
31135010742
0
1
United States
11
1
Adair
606
439581417
0
331746662
132240332
903568411
0
0
0
0
903568411
0
1
United States
45
1
Adair
236
350256381
0
524269553
504973831
1379499765
4080368
0
0
0
1380473415
0
1
United States
23
1etc.....
View 6 Replies
View Related
Apr 16, 2007
Dear Friends,
I have an OLE DB Command inside an ETL. I want to execute a stored procedure that returns 2 fields, for example:
CREATE PROCEDURE sp_Name
AS
SELECT Field1, Field2 FROM Table
How can I continue the dataflow? How can I return the values from the query? Returns only a row...
View 10 Replies
View Related
Jan 12, 2005
i have a table and a column called req_id, i have it set as the primary key.. so if i just do SELECT * FROM table, shouldnt the rows returned be sorted by the order that the rows were inserted?
this database was improted from an access database.. when i did that in access it would return the rows in sorted order by the order the row was inserted.. but now in MS SQL, its not sorted in that order.. i can't really tell what type of order it's in
View 6 Replies
View Related
Jun 19, 2007
Hi,
I'm reading values from a named range within an Excel spreadsheet using the Excel ODBC driver. If I ask for all columns within a range, using e.g. select * from 'named range', does the driver ensure that the returned rowset has the same column and row ordering as in the spreadsheet? In other words, if a named range on a spreadsheet is the block of cells:
name age
richard 54
jemima 27
I want to make sure that my returned rowset is not going to be:
age name
jemima 27
richard 54
I know that proper databases do not guarantee the order of returned values (unless you specify it) but since Excel is a fixed view of data I was hoping that a returned rowset of values would retain their spreadsheet ordering.
Thanks,
aionaut
View 1 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 4, 2008
Hey guys, i need to find out how can i add order items under a Purchase Order number.
My table relationship is PurchaseOrder ->PurchaseOrderItem.
below is a Stored Procedure that i have wrote in creating a PO:
CREATE PROC spCreatePO (@SupplierID SmallInt, @date datetime, @POno SmallInt OUTPUT)
AS
BEGIN
INSERT INTO PurchaseOrder (PurchaseOrderDate, SupplierID) VALUES(@date, @SupplierID)
END
SET @POno = @@IDENTITY
RETURN
However, how do i make it that it will automatically adds item under the POno being gernerated? can i use a trigger so that whenever a Insert for PO is success, it automaticallys proceed to adding the items into the table PurcahseOrderItem?
CREATE TRIGGER trgInsertPOItem
ON PurchaseOrderItem
FOR INSERT
AS
BEGIN
'What do i entered???'
END
RETURN
help is needed asap! thanks!
View 14 Replies
View Related
Mar 22, 2000
I have a varchar field which holds IDs like (1, 3, 5, 19, 23) when I order it, i get it ordered in ASCII order like (1, 19, 23, 3, 5) rather than (1, 3, 5, 19, 23) Even if I convert it to int, I won't be able to order it.
is there any way I can order a varchar field numerically?
Angel
View 1 Replies
View Related
Aug 6, 2004
I have a problem with ordering and I am hoping that someone is able to help.
In my table I have two fields, "requestdate" and "sentdate", and when I display the records I would like to sort by BOTH fields. I want to do this so that the full query is in order by date.
I tried:
ORDER BY requestdate, sentdate DESC
But obviously all that does is order by requestdate (which is NULL or a date) and than it will order by sentdate.
Can somebody tell me how to order by both as if they were the same field?
Thanks!
View 2 Replies
View Related
May 20, 2014
There are about 300 fields in a views.
How to reorder all fields by alphabet?
For example, from script of views like below:
select name, date, amount from order
re-order fields' name like below:
select amount, date, name from order
For about 300 fields view, how to code to re-order fields' name?
View 6 Replies
View Related
Apr 15, 2004
the table is like :
ID F1 F2 F3
--- --- --- ---
1 A 1 VR
2 B 2 VR
3 A 3 VF
4 A 2 VF
5 B 1 VF
......
the rules is:
if there are rows with same values of F3 field,then choose the top 1 row order by F1,F2 in the group,and delete other rows.
how can I delete rows with ID in (2 , 3 , 5)?
View 7 Replies
View Related
Apr 29, 2015
I have a test query that i have ordered by the expected result order.
select t from (
select cast('1' as nvarchar(20)) as t
union all select '1---qa'
union all select '1q'
union all select '1q1'
union all select '1qz'
[Code] ....
and the question is... why is minus character (at 3rd line in '1---qa' string) ignored by "order by" clause? That row is put after '1q1' and not after '1'.
When you replace '-' with '+' or any other special char it works as expected but not with '-'. It is just ignored no matter if there is one or more of them.
Don't mind local chars like čž, they are for testing purposes with collate (thus commented) and with bin collation that is not doing proper sorting but minus order is ok in that case.
Tested on SQL 2008 R2 and SQL express 2012, database collation is 'Croatian_CI_AS'.
View 4 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
Apr 17, 2007
Dear Friends,
I need to execute a SQL query, inside a dataflow (not in controlFlow) and need the records returned to continue the dataflow... In my case I cant use lookup and OLE DB COmmand and nothing else...
I need to execute a query and need the records for dataflow... with OLE DB command I cant see the fields returned... :-(
How can I do it? Using a script? Can I use a Script Component? That receive 2 parameters for input and give me the fields returned from query as output?
Thanks!!
View 38 Replies
View Related
Aug 22, 2006
Hi All,
I am working on a project in which an Integration Services package should be executed 24 times a day. Everytime it is executed, it validates for tables, views etc. I am able to validate for tables, views, partition functions etc. But when I am trying to write a code as
If not exists (select name from sys.objects where type='U' and name='SP')
Create Procedure SP
Begin
End
I am trying to put the above as a task in Integration Services.
When the task is executed, it is throw an error 'Create Procedure should be the first line' etc.
How to solve the above problem? Can any help me immediately?
Thanks
View 3 Replies
View Related
Feb 19, 2014
How do I properly write a CASE statement for the following business logic:
I want three of the following fields to be true in order to return 'Y'.
For example:
If field name 'Accepted' = 1 AND field 'StepNo' = '1' and field 'Visit' = 'V1'
Otherwise, I want it to return 'N'.
I have tried the following code below and it is not working.
, CASE WHEN Accepted = '1' AND StepNo ='1' AND Visit ='V1'
THEN 'Y' ELSE 'N' END AS 'StatusQ (Col N)'
View 2 Replies
View Related
Dec 10, 2007
I have a package that hangs in the designer after I change the sql statement in a DataReader Source from a 'select' to a 'call stored procedure'. The stored procedure takes 2 date parameters. I use an expression to build the 'call stored proc' statement and the 2 date strings. The data reader source uses an ADO.Net connection manager. The ADO.Net connection manager uses the provider for MySQL (Connector/.Net 5.1) which I installed from MySQL.com (http://dev.mysql.com/downloads/connector/net/5.1.html). Before creating the stored procedure I had been using an expression to build a 'select' statement with two date variables as follows:
select ...
where ads.last_seen >= "" + (DT_STR,10,1252) Year(@[User:: StartDate] ) + "-" + (DT_STR,10,1252) Month(@[User:: StartDate] ) + "-" + (DT_STR,10,1252) Day(@[User:: StartDate] )
+ "" and ads.first_seen <= "" + (DT_STR,10,1252) Year(@[User::EndDate] ) + "-" + (DT_STR,10,1252) Month(@[User::EndDate] ) + "-" + (DT_STR,10,1252) Day(@[User::EndDate] )+ "" group by sm.service_provider_id,lm.location_id,lm.web_sublocation_id;"
The sql for the data reader source is set via the sql command property of the data flow component.
After testing the sql, I created a stored proc from this sql and then changed the expression (using the sql command property of the the data flow component) to build the 'call stored proc' statement, like this.
"call usp_SEL_Rollup ("" + (DT_STR,10,1252) Year(@[User:: StartDate] ) + "-" + (DT_STR,10,1252) Month(@[User:: StartDate] ) + "-" + (DT_STR,10,1252) Day(@[User:: StartDate] ) + "","" +(DT_STR,10,1252) Year(@[User::EndDate] ) + "-" + (DT_STR,10,1252) Month(@[User::EndDate] ) + "-" + (DT_STR,10,1252) Day(@[User::EndDate] ) +"");"
then when I tried to switch to the data flow tab, the editor froze, with the status bar saying "validating datareader source". The data flow tab says "Loading...". I don't know how to troubleshoot this. Each time I have tried I have had to kill the application. Any ideas/suggestions?
Thanks,
Al
View 6 Replies
View Related
Sep 26, 2007
Hi,
I use a strong-typed DataSet to build the data access tier of my application, and the IDE generates most of code for me. However I can't find a way to get the value returned by a stored procedure(The stored procedure seems like 'RETURN 0').I noticed that a @RETURN_VALUE parameter is added automatically for each query method, but I don't know how to read the value.
Anybody could help me with the problem? Thanks.
View 2 Replies
View Related
