SPROC Output Paramater Asking For Input?
Jan 3, 2008
I am trying to get a stored proceedure to return the autogenerated numerical primary key of the last row created to our appliciation. I have created what I thought was an output parameter to handle this however when the application runs I get a message that seems to indicate that it is ASKING for the parameter instead of returning it. Here is the code of the sproc:
Code Block
USE [chronicle]
GO
/****** Object: StoredProcedure [dbo].[CreateNewLicense] Script Date: 01/03/2008 06:35:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[CreateNewLicense]
@VendorId int,
@PoId int,
@LicenseTypeId int,
@LicenseUserId int,
@LocationId int,
@LicenseStartDate smalldatetime,
@DaysAllowed int,
@SerialNum varchar(50),
@ActivationKey varchar(50),
@MaxUsers int,
@Comments varchar(1000),
@LicenseId int OUTPUT
AS
BEGIN
INSERT INTO license
(vendor_id,
po_id,
license_type_id,
lic_user_id,
location_id,
lic_start_date,
days_allowed,
serial_num,
activation_key,
max_users,
comments
)
VALUES
( @VendorId,
@PoId,
@LicenseTypeId,
@LicenseUserId,
@LocationId,
@LicenseStartDate,
@DaysAllowed,
@SerialNum,
@ActivationKey,
@MaxUsers,
@Comments
)
SELECT @LicenseId = @@IDENTITY
END
View 5 Replies
ADVERTISEMENT
Nov 5, 2007
Hi all
In my application i have to upload a excel file.After that it will retrive tha data from database depending upon the data in excel sheet.So now the problem is that i am using a stored procedure for this and in that procedurei have taken output parameter to catch any type of error.For example suppose in my excel sheet i have 3 columns.let it Group Code,Employee No and Employee Code.So incase it will not find the employee number so using the output parameter i am throwing the error message.So here we are getting two return types.one is the selected data and another is the error message.So how to handle these two thing in asp.net.Usong a dataset i can only retrive the selected data.But how to get the errror message as well as the data.So please help me.
View 1 Replies
View Related
Sep 14, 2006
Can some one offer me some assistance? I'm using a SQLDataSource control to call a stored proc to insert a record into a control. The stored proc I'm calling has an output paramater that returns the new rows identity field to be used later. I'm having trouble getting to this return value via the SQLDataSource . Below is my code (C#): SqlDataSource1.InsertParameters["USR_AUTH_NAME"].DefaultValue = storeNumber;SqlDataSource1.InsertParameters["usr_auth_pwd"].DefaultValue = string.Empty;SqlDataSource1.InsertParameters["mod_usr_name"].DefaultValue = "SYSTEM";SqlDataSource1.InsertParameters["usr_auth_id"].Direction = ParameterDirection.ReturnValue;SqlDataSource1.Insert();int id = int.Parse(SqlDataSource1.InsertParameters["usr_auth_id"].DefaultValue); below is the error I'm getting: System.Data.SqlClient.SqlException: Procedure 'csi_USR_AUTH' expects parameter '@usr_auth_id', which was not supplied. Has anyone done this before and if so how did you do it?
View 1 Replies
View Related
Mar 16, 2007
Juste have fun with the following, if you can manage this out your my savior, been wasting far too much time on it, tried a couple of method and none ever worked. Conversion from type 'DBNull' to type 'String' is not valid.
Description: An
unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the
error and where it originated in the code. Exception Details: System.InvalidCastException: Conversion from type 'DBNull' to type 'String' is not valid.Source Error:
Line 105:Line 106: Protected Sub sdsProprActionAJO_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles sdsProprActionAJO.InsertedLine 107: Dim MessageSysteme As String = CType((CType(e.Command.Parameters("SQL_MSG"), IDbDataParameter)).Value, String)Line 108: End SubLine 109: With sdsProprActionAJO .ConnectionString = ConfigurationManager.AppSettings("DB_CS_MSSQL") .InsertCommandType = SqlDataSourceCommandType.StoredProcedure .InsertCommand = "dbo.sp_adm_cartListe" .InsertParameters.Clear() .InsertParameters.Add("Mode", TypeCode.String, "SEL") .InsertParameters.Add("LPR_Groupe", TypeCode.String, CBool(CType(Me.pnlProprActionAJO.FindControl("rblProprActionAJOGroupe"), RadioButtonList).SelectedValue)) .InsertParameters.Add("LPR_Etiquette", TypeCode.String, CType(Me.pnlProprActionAJO.FindControl("tbProprActionAJOEtiquette"), TextBox).Text) .Insert() End With End Sub Protected Sub sdsProprActionAJO_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles sdsProprActionAJO.Inserted Dim MessageSysteme As String = CType((CType(e.Command.Parameters("SQL_MSG"), IDbDataParameter)).Value, String) End Sub Protected Sub sdsProprActionAJO_Inserting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles sdsProprActionAJO.Inserting Dim Param As System.Data.SqlClient.SqlParameter = New System.Data.SqlClient.SqlParameter() param.DbType = DbType.String param.Direction = ParameterDirection.Output param.ParameterName = "SQL_MSG" e.Command.Parameters.Add(Param) End Sub CREATE PROCEDURE [dbo].[sp_Adm_Proprietes_AJO](@MODE VARCHAR(3),@LPR_Groupe BIT = NULL,@LPR_Etiquette NVARCHAR(32) = NULL,@RPP_LPR_IdParent [INT] = NULL,@RPP_LPR_IdEnfant [INT] = NULL,@SQL_MSG NVARCHAR(8) OUTPUT)AS--DECLARE @SQL_TEMP NVARCHAR(4000)DECLARE @INT_TEMP [INT]--IF @MODE = 'RRP'BEGIN INSERT INTO t__REL__PropParent_PropEnfant ( RPP_LPR_IdParent, RPP_LPR_IdEnfant ) VALUES ( @RPP_LPR_IdParent, @RPP_LPR_IdEnfant )END--IF @MODE = 'LPR'BEGIN IF NOT @LPR_Etiquette = NULL BEGIN SELECT * FROM t_Lib_Proprietes WHERE (LPR_Groupe = @LPR_Groupe ) AND (LPR_Etiquette = @LPR_Etiquette ) IF @@ROWCOUNT <= 0 BEGIN INSERT INTO t_Lib_Proprietes ( LPR_Groupe, LPR_Etiquette ) VALUES ( @LPR_Groupe, @LPR_Etiquette ) SET @SQL_MSG = 'CON_000A' END ELSE BEGIN SET @SQL_MSG = 'ERR_000A' END END ELSE BEGIN SET @SQL_MSG = 'ERR_003A' ENDENDRETURN @SQL_MSGGO
View 8 Replies
View Related
Jun 15, 2007
Wanted some candid feedback on this idea. Everyone knows that neural nets are a black box in terms of the weights and such it uses. The best you can do is to get an idea how "sensitive" the NN is to each input. I don't know of any example code that's out there to help you do this in an automated manner (correct me if I'm wrong) so I'm thinking about writing a sproc that would help with this task.
Basically, the sproc would take in the mining model's name and the key of one case. Let's say the case and its attributes are:
ZipCode=93901 (the case key)
MedianIncome=99,098
PopulationDensity=1234
AvgTemperature=74.3
Predict likelihood to respond to an offer
For that case, it would go through an iteration per input. First it would test the first input, MedianIncome. It would run 100 predictions using the input values listed above. All the inputs would remain the same throughout except for MedianIncome where it would try out the complete range of that input. Based upon how much the prediction changes, you would have an idea how sensitive the model is to MedianIncome. Then it would move on to the next input and do the same.
When it's done testing each input, it could spit out a dataset listing all the inputs in order of how sensitive the model is to them and a few other stats like min and max prediction.
Thoughts? Improvements? Alternatives?
View 6 Replies
View Related
Nov 28, 2007
I have a stored proc that I'd like to return an output param from. I'm using a SQLDataSource and invoking the Update method which calls the sproc.The proc looks like this currently:
ALTER proc [dbo].[k_sp_Load_IMIS_to_POP_x]@vcOutputMsg varchar(255) OUTPUT
AS
SET NOCOUNT ON ;
select @vcOutputMsg = 'asdf'
The code behind looks like this:protected void SqlDataSource1_Updated(object sender, SqlDataSourceStatusEventArgs e)
{
//handle error on return
string returnmessage = (string)e.Command.Parameters["@vcOutputMsg"].Value;
}
On the page source side, the params are defined declaratively:
<UpdateParameters>
<asp:Parameter Direction="ReturnValue" Name="RETURN_VALUE" Type="Int32" />
<asp:Parameter Direction="InputOutput" Name="vcOutputMsg" Type="String" />
</UpdateParameters>
When I run it, the code behind throws the following exception - "Unable to cast object of type 'System.DBNull' to type 'System.String'"
PLEASE HELP! What am I doing wrong? Is there a better way to get output from a stored proc?
View 3 Replies
View Related
Mar 6, 2015
We have a service written in c# that is processing packages of xml that contain up to 100 elements of goods consignment data.
In amongst that element is an identifier for each consignment. This is nvarchar(22) in our table. I have not observed any IDs that are different in length in the XML element.
The service picks up these packages from MSMQ, extracts the data using XPATH and passes the ID into the SPROC in question. This searches for the ID in one of our tables and returns a bool to the service indicating whether it was found or not. If found then we add a new row to another table. If not found then it ignores and continues processing.
The service seems to be dealing with a top end of around 10 messages a minute... so a max of about 1000 calls to the SPROC per minute. Multi-threading has been used to process these packages but as I am assured, sprocs are threadsafe.It is completing the calls without issue but intermittently it will return FALSE. For these IDs I am observing that they exist on the table mostly (there are the odd exceptions where they are legitimately missing).e.g Yesterday I was watching the logs and on seeing a message saying that an ID had not been found I checked the database and could see that the ID had been entered a day earlier according to an Entered Timestamp.
USE [xxxxxxxxxx]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
[code]....
So on occasions (about 0.33% of the time) it is failing to get a bit 1 setting in @bFound after the SELECT TOP(1).
change @pIdentifier nvarchar(25) to nvarchar(22)
Trim any potential blanks from either side of both parts of the identifier comparison
Change the SELECT TOP(1) to an EXISTS
The only other thought is the two way parameter direction in the C# for the result OUTPUT. I have been unable to replicate this using a test app and our test databases. Have observed selects failing to find even though the data is there, like this before?
View 1 Replies
View Related
Jun 27, 2013
I am uploading input sample data and desired output data, can get the desire output.
View 4 Replies
View Related
Feb 21, 2007
Hi All,
I want to know is it possible to have source as Flat File and destination as XML
Thanks in advance,
Shagun
View 1 Replies
View Related
Jul 23, 2005
I know that you can retrieve whether a parameter is for output buy wayof the "isoutparam" field, but is there anything that tells you whethera parameter is input/output?thanks
View 3 Replies
View Related
Oct 26, 2006
Hello
I'm trying to use the Merge component. When i attach a datasource to the the component, the Select Input/Output dialog box should popup.. It does, but VS.NET is hanging and i can only shutdown the procesess...
Any idea how i should solve this? how can i re-register this component?
ps. sql 2005 sp1 is installed.
Thanks
Marco
View 4 Replies
View Related
Feb 18, 2006
The foolowing code I cannot seem to get working right. There is an open connection c0 and a SqlCommand k0 persisting in class.The data in r0 is correct and gets the input arguments at r0=k0->ExecuteReader(), but nothing I do seems to get the output values. What am I missing about this?
System::Boolean rs::sp(System::String ^ ssp){
System::String ^ k0s0; bool bOK;
System::Data::SqlClient::SqlParameter ^ parami0;
System::Data::SqlClient::SqlParameter ^ parami1;
System::Data::SqlClient::SqlParameter ^ parami2;
System::Data::SqlClient::SqlParameter ^ paramz0;
System::Data::SqlClient::SqlParameter ^ paramz1;
System::Int32 pz0=0;System::Int32 pz1=0;
k0s = ssp;
k0->CommandType=System::Data::CommandType::StoredProcedure;
k0->CommandText=k0s;
paramz0=k0->Parameters->Add("@RETURN_VALUE", System::Data::SqlDbType::Int);
//paramz0=k0->Parameters->AddWithValue("@RETURN_VALUE",pz0);
//paramz0=k0->Parameters->AddWithValue("@RETURN_VALUE",pz0);
paramz0->Direction=System::Data::ParameterDirection::ReturnValue;
paramz0->DbType=System::Data::DbType::Int32;
parami0=k0->Parameters->AddWithValue("@DESCXV","chicken");
parami0->Direction=System::Data::ParameterDirection::Input;
parami1=k0->Parameters->AddWithValue("@SRCXV","UU");
parami1->Direction=System::Data::ParameterDirection::Input;
//paramz1=k0->Parameters->AddWithValue("@RCOUNT",pz1);
paramz1=k0->Parameters->Add("@RCOUNT",System::Data::SqlDbType::Int);
paramz1->Direction=System::Data::ParameterDirection::InputOutput;
paramz0->DbType=System::Data::DbType::Int32;
//k0->Parameters->GetParameter("@RCOUNT");
r0=k0->ExecuteReader();
//pz0=System::Convert::ToInt32(paramz0->SqlValue);
bOK=k0->Parameters->Contains("@RCOUNT");
//k0->Parameters->GetParameter("@RCOUNT");
pz0=System::Convert::ToInt32(paramz0->Value);
pz1=System::Convert::ToInt32(paramz1->Value);
ndx = -1;
while(r0->Read()){
if (ndx == -1){
ndx=0;
pai0ndx=0;
pad0ndx=0;
r0nf=r0->FieldCount::get();
for (iG1_20=0;iG1_20<r0nf;iG1_20++){
this->psf0[iG1_20]=this->r0->GetName(iG1_20);
this->psv0[iG1_20]=this->r0->GetDataTypeName(iG1_20);
this->psz0[iG1_20]=System::Convert::ToString(this->r0->GetValue(iG1_20));
this->pas0[ndx,iG1_20]=System::Convert::ToString(this->r0->GetValue(iG1_20));
if (psv0[iG1_20]=="int") {pai0[ndx,pai0ndx]=System::Convert::ToInt32(r0->GetValue(iG1_20));pai0ndx++;}
if (psv0[iG1_20]=="float") {pad0[ndx,pad0ndx]=System::Convert::ToDouble(r0->GetValue(iG1_20));pad0ndx++;}
}
}
else {
pai0ndx=0;
pad0ndx=0;
for (iG1_20=0;iG1_20<r0nf;iG1_20++)
{ this->pas0[ndx,iG1_20]=System::Convert::ToString(this->r0->GetValue(iG1_20));
if (psv0[iG1_20]=="int") {pai0[ndx,pai0ndx]=System::Convert::ToInt32(r0->GetValue(iG1_20));pai0ndx++;}
if (psv0[iG1_20]=="float") {pad0[ndx,pad0ndx]=System::Convert::ToDouble(r0->GetValue(iG1_20));pad0ndx++;}
}
}
ndx++;
}
r0nr=ndx;
r0->Close();
k0->Parameters->Remove(paramz0);
k0->Parameters->Remove(parami1);
k0->Parameters->Remove(parami0);
k0->Parameters->Remove(paramz1);
return true;
}
View 5 Replies
View Related
Mar 21, 2007
I am executing a stored proc with in the Execute SQL Task using OLEDB provider. I am passing the data as
ConnectionType: OLEDB
Connection : to my database
SQLSourceType: Direct
SQL Statment : Exec mysp 'table1',OUTPUT,OUTPUT
In the parmeter mappings:
variable1--direction Output, datatype Long, Parameter name: 0
variable2--direction Output, datatype date, Parameter name: 1
The variable 1 is created as int32 and variable 2 is created as dattime.
When i execute the SQLtask, I get error:
[Execute SQL Task] Error: Executing the query "Exec mysp 'table1',OUTPUT,OUTPUT" failed with the following error: "Error converting data type nvarchar to int.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
What am i missing. I tried changing the data types adding the input variable also as a variable in the mapping. Nothing seems to work. Any ideas please?
Anitha
View 2 Replies
View Related
May 25, 2007
Dear all,
I created a package that seems to work fine with a small amount of data. When I run the package however with more data (as in production) the merge join output is limites to 9963 rows, no matter if I change the number of input rows.
Situation as follows.
The package has 2 OLE DB Sources, in which SQL-statements have been defined in order to retrieve the data.
The flow of source 1 is: retrieving source data -> trimming (non-key) columns -> sorting on the key-columns.
The flow of source 2 is: retrieving source data -> deriving 2 new columns -> aggregating the data to the level of source 1 -> sorting on the key columns.
Then both flows are merged and other steps are performed.
If I test with just a couple of rows it works fine. But when I change the where-clause in the data source retrieval, so that the number of rows is for instance 15000 or 150000 the number of rows after the merge join is 9963.
When I run the package in debug-mode the step is colored green, nevertheless an error is displayed:
Error: 0xC0047022 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Merge Join" (4703) failed with error code 0xC0047020. 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.
To be honest, a few more errormessages appear, but they don't seem related to this issue. The package stops running after some 6000 rows have been written to the destination.
Any help will be greatly appreciated.
Kind regards,
Albert.
View 4 Replies
View Related
Aug 28, 2007
I'm trying to create a fairly simple custom transform component (because I've read that's the easiest one to create) which will take one column from a flat file source and based on the first row create the output columns.
I'm actually trying to write a component that will solve the now well known problem with parsing CSV files in SSIS. I have a lot of source files and all have many columns so a component that can read in the first line from the CSV file and create the output columns automatically will save me lots of time when migrating the old DTS packages.
I have the basic component set up but I'm stuck when trying to override the OnInputPathAttached method because I don't know how to use the inputID to get the first line from the input (the buffer).
Are there any good examples for creating output columns dynamically based on the input buffer?
Should I just give up on on the transform and create a custom source component instead?
View 5 Replies
View Related
Jan 2, 2007
Hi Everyone,
I haven't been able to successfully use the ADO.NET connection type to use both input and output parameters in an execute sql task containing just tsql statements (no stored procedure calls). I have successfully used input parameters on their own but when i combine it with output parameters it fails on the simplest of tasks.
I would really find it beneficial if you could use the flexibility of an ADO.NET connection type as the parameter marker and parameter name can be referenced anywhere throughout the sql statement in no particular order. The addition of an output parameter would really make it great!!
Thanks
View 11 Replies
View Related
Feb 23, 2008
Hi all,
In a Database "AP" of my SQL Server Management Studio Express (SSMSE), I have a stored procedure "spInvTotal3":
CREATE PROC [dbo].[spInvTotal3]
@InvTotal money OUTPUT,
@DateVar smalldatetime = NULL,
@VendorVar varchar(40) = '%'
This stored procedure "spInvTotal3" worked nicely and I got the Results: My Invoice Total = $2,211.01 in
my SSMSE by using either of 2 sets of the following EXEC code:
(1)
USE AP
GO
--Code that passes the parameters by position
DECLARE @MyInvTotal money
EXEC spInvTotal3 @MyInvTotal OUTPUT, '2006-06-01', 'P%'
PRINT 'My Invoice Total = $' + CONVERT(varchar,@MyInvTotal,1)
GO
(2)
USE AP
GO
DECLARE @InvTotal as money
EXEC spInvTotal3
@InvTotal = @InvTotal OUTPUT,
@DateVar = '2006-06-01',
@VendorVar = '%'
SELECT @InvTotal
GO
////////////////////////////////////////////////////////////////////////////////////////////
Now, I want to print out the result of @InvTotal OUTPUT in the Windows Application of my ADO.NET 2.0-VB 2005 Express programming. I have created a project "spInvTotal.vb" in my VB 2005 Express with the following code:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class Form1
Public Sub printMyInvTotal()
Dim connectionString As String = "Data Source=.SQLEXPRESS; Initial Catalog=AP; Integrated Security=SSPI;"
Dim conn As SqlConnection = New SqlConnection(connectionString)
Try
conn.Open()
Dim cmd As New SqlCommand
cmd.Connection = conn
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "[dbo].[spInvTotal3]"
Dim param As New SqlParameter("@InvTotal", SqlDbType.Money)
param.Direction = ParameterDirection.Output
cmd.Parameters.Add(param)
cmd.ExecuteNonQuery()
'Print out the InvTotal in TextBox1
TextBox1.Text = param.Value
Catch ex As Exception
MessageBox.Show(ex.Message)
Throw
Finally
conn.Close()
End Try
End Sub
End Class
/////////////////////////////////////////////////////////////////////
I executed the above code and I got no errors, no warnings and no output in the TextBox1 for the result of "InvTotal"!!??
I have 4 questions to ask for solving the problems in this project:
#1 Question: I do not know how to do the "DataBinding" for "Name" in the "Text.Box1".
How can I do it?
#2 Question: Did I set the CommandType property of the command object to
CommandType.StoredProcedure correctly?
#3 Question: How can I define the 1 output parameter (@InvTotal) and
2 input parameters (@DateVar and @VendorVar), add them to
the Parameters Collection of the command object, and set their values
before I execute the command?
#4 Question: If I miss anything in print out the result for this project, what do I miss?
Please help and advise.
Thanks in advance,
Scott Chang
View 7 Replies
View Related
Apr 10, 2008
Hello All,
I am migrating data from one database to another. I am using Multicast to seperate (legal street,legal mail and legal city) and (mail_street,mail_state,mail_zip,mail_city) also later after UNION of the above I am doing two lookups as I had to get contact ID and Customer ID from other two tables. In UNION i am matching (Mail street legal street) and so on.
I am getting double the data in the output. my input data is 1000000 and im gettin 2000000.
What could be the reasons. Please help me out.
Thank You
View 5 Replies
View Related
Sep 25, 2006
I have a stored procedure which takes an input parm and is supposed to return an output parameter named NewRetVal. I have tested the proc from Query Analyzer and it works fine, however when I run the ASP code and do a quickwatch I see that the parm is being switched to an input parm instead of the output parm I have it defined as...any ideas why this is happening? The update portion works fine, it is the Delete proc that I am having the problems... ASP Code...<asp:SqlDataSource ID="SqlDS_Form" runat="server" ConnectionString="<%$ ConnectionStrings:PTNConnectionString %>" SelectCommand="PTN_sp_getFormDD" SelectCommandType="StoredProcedure" OldValuesParameterFormatString="original_{0}" UpdateCommand="PTN_sp_Form_Update" UpdateCommandType="StoredProcedure" OnUpdated="SqlDS_Form_Updated" OnUpdating="SqlDS_Form_Updating" DeleteCommand="PTN_sp_Form_Del" DeleteCommandType="StoredProcedure" OnDeleting="SqlDS_Form_Updating" OnDeleted="SqlDS_Form_Deleted"><UpdateParameters><asp:ControlParameter ControlID="GridView1" Name="DescID" PropertyName="SelectedValue" Type="Int32" /><asp:ControlParameter ControlID="GridView1" Name="FormNum" PropertyName="SelectedValue" Type="String" /><asp:Parameter Name="original_FormNum" Type="String" /><asp:Parameter Direction="InputOutput" size="25" Name="RetVal" Type="String" /></UpdateParameters><DeleteParameters><asp:Parameter Name="original_FormNum" Type="String" /><asp:Parameter Direction="InputOutput" Size="1" Name="NewRetVal" Type="Int16" /></DeleteParameters></asp:SqlDataSource>Code Behind:protected void SqlDS_Form_Deleted(object sender, SqlDataSourceStatusEventArgs e){ if (e.Exception == null) { string strRetVal = (String)e.Command.Parameters["@NewRetVal"].Value.ToString(); ............................Stored Procedure:CREATE PROCEDURE [dbo].[PTN_sp_Form_Del] (
@original_FormNum nvarchar(20),
@NewRetVal INT OUTPUT )
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @stoptrans varchar(5), @AvailFound int, @AssignedFound int
Set @stoptrans = 'NO'
/* ---------------------- Search PART #1 ----------------------------------------------------- */
SET @AvailFound = ( SELECT COUNT(*) FROM dbo.AvailableNumber WHERE dbo.AvailableNumber.FormNum = @original_FormNum )
SET @AssignedFound = ( SELECT COUNT(*) FROM dbo.AssignedNumber WHERE dbo.AssignedNumber.FormNum=@original_FormNum )
IF @AvailFound > 0 OR @AssignedFound > 0 /* It is ok if no rows found on available table, continue on to Assigned table, otherwise stop the deletion.*/
-----This means the delete can't happen...........
BEGIN
IF @AssignedFound > 0 AND @AvailFound = 0
BEGIN
SET @NewRetVal = 1
END
IF @AssignedFound > 0 AND @AvailFound > 0
BEGIN
SET @NewRetVal = 2
END
IF @AssignedFound = 0 AND @AvailFound > 0
BEGIN
SET @NewRetVal = 3
END
END
ELSE
BEGIN
DELETE FROM dbo.Form
WHERE dbo.Form.FormNum=@original_FormNum
SET @NewRetVal = 0
---Successful deletion
END
GO
-------------------------------------------------------- When I go into the debug mode and do a quickwatch, the NewRetVal is showing as string input.
View 2 Replies
View Related
Jan 7, 2014
I need to be able to pass the output of a function to another function as input, where all functions involved are user-defined in-line table-valued functions. I already posted this on Stack Exchange, so here is a link to the relevant code: [URL] ...
I am fairly certain OUTER APPLY is the core answer here; there's *clearly* some way in which does *not* do what I need, or I would not get the null output you see in the link, but it seems clear that there should be a way to fool it into working.
View 5 Replies
View Related
Jul 13, 2006
I have a nasty situation in SQL Server 7.0. I have a table, in whichone column contains a string-delimited list of IDs pointing to anothertable, called "Ratings" (Ratings is small, containing less than tenvalues, but is subject to change.) For example:[ratingID/descr]1/Bronze2/Silver3/Gold4/PlatinumWhen I record rows in my table, they look something like this:[uniqueid/ratingIDs/etc...]1/2, 4/...2/null/...3/1, 2, 3/...My dilemma is that I can't efficiently read rows in my table, match thestring of ratingIDs with the values in the Ratings table, and returnthat in a reasonable fashion to my jsp. My current stored proceduredoes the following:1) Query my table with the specified criteria, returning ratingIDs as acolumn2) Split the tokens in ratingIDs into a table3) Join this small table with the Ratings table4) Use a CURSOR to iterate through the rows and append it to a string5) Return the string.My query then returns...1/"Silver, Platinum"2/""3/"Bronze, Silver, Gold"And is easy to output.This is super SLOW! Queries on ~100 rows that took <1 sec now take 12secs. Should I:a) Create a junction table to store the IDs initially (I didn't thinkthis would be necessary because the Ratings table has so few values)b) Create a stored procedure that does a "SELECT * FROM Ratings," putthe ratings in a hashtable/map, and match the values up in Java, sinceJava is better for string manipulation?c) Search for alternate SQL syntax, although I don't believe there isanything useful for this problem pre-SQL Server 2005.Thanks!Adam
View 2 Replies
View Related
Feb 13, 2007
I have attached the results of checking an Update sproc in the Sql database, within VSS, for a misbehaving SqlDataSource control in an asp.net web application, that keeps telling me that I have too many aurguments in my sproc compared to what's defined for parameters in my SQLdatasource control.....
No rows affected.
(0 row(s) returned)
No rows affected.
(0 row(s) returned)
Running [dbo].[sp_UPD_MESample_ACT_Formdata]
( @ME_Rev_Nbr = 570858
, @A1 = No
, @A2 = No
, @A5 = NA
, @A6 = NA
, @A7 = NA
, @SectionA_Comments = none
, @B1 = No
, @B2 = Yes
, @B3 = NA
, @B4 = NA
, @B5 = Yes
, @B6 = No
, @B7 = Yes
, @SectionB_Comments = none
, @EI_1 = N/A
, @EI_2 = N/A
, @UI_1 = N/A
, @UI_2 = N/A
, @HH_1 = N/A
, @HH_2 = N/A
, @SHEL_1 = 363-030
, @SHEL_2 = N/A
, @SUA_1 = N/A, @SUA_2 = N/A
, @Cert_Period = 10/1/06 - 12/31/06
, @CR_Rev_Completed = Y ).
No rows affected.
(0 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[sp_UPD_MESample_ACT_Formdata].
The program 'SQL Debugger: T-SQL' has exited with code 0 (0x0).
And yet every time I try to update the record in the formview online... I get
Procedure or function sp_UPD_MESample_ACT_Formdata has too many arguments specified.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Procedure or function sp_UPD_MESample_ACT_Formdata has too many arguments specified.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
I have gone through the page code with a fine tooth comb as well as the sproc itself. I have tried everything I can think of, including creating a new page and resetting the fields, in case something got broken that I can't see.
Does anyone have any tips or tricks or info that might help me?
Thanks,
SMA49
View 3 Replies
View Related
Oct 19, 2006
Hey everyone I'm having trouble finding a way to pass a particular paramater, my main goal of this is to create a custom paging and sorting control for the DataList, but we wont worry about all the un-nessesarry code, here is what I am dealing with... Sub Page_Load()
Dim Conn As SqlConnection
Dim Query As String
Dim SqlComm As SqlCommand
Dim myDataReader As SqlDataReader
' Define connection object
Conn = New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
' Define query to retrieve main category values
Query = "SELECT [productMainCategory] FROM [User_Products] WHERE [productMainCategory] = @productMainCategory"
' Define command object
SqlComm = New SqlCommand(Query, Conn)
' Open connection to database
Conn.Open()
' Create DataReader
myDataReader = SqlComm.ExecuteReader()
' Iterate through records and add to array list
While myDataReader.Read()
IDList.Add(myDataReader("productMainCategory"))
End While
' Close DataReader and connection objects
myDataReader.Close()
myDataReader = Nothing
Conn.Close()
Conn = Nothing
' If page has not been posted back, retrieve first page of records
If Not Page.IsPostBack Then
Paging()
End If
End Sub
The Entire Point to this bit of code is to open a connection to the database find the category productsMainCategory, count all the fields with the Name 3D Art --> and save that number into an Array List where I will be using it later on in my code......The big old problem here is that it counts every single field in the productsMainCategory, Columb.........I need to figure out how to pass it a parameter so that it only counts my 3D Art --> fields, I have tried using this bit of code but nothing SqlComm.Parameters("@productMainCategory").Value = "3D Art -->"Does anyone have any ideas of how I might go about donig this?Thank You..
View 4 Replies
View Related
Jun 26, 2007
I guess a pretty basic sql question here. I am using tableadapters. And some of the queries have multiple parameters. But often a parameter is at a default or null - wherein I'd like to be able to pass it in and have it not filter at all - ie., return everything.
Simple example:
Select * from ReportsWhere ReportID = @ReportID
If I have a dropdownlist of reports where the values are ReportIDs - but the topmost unselected value is say "", then the results will be generated by
Select * from ReportsWhere ReportID = ''
Whis not what I want. What I would want is simply
Select * from Reports
or
Select * from ReportsWhere ReportID = ReportID
So how can one setup say an ObjectDataSource or somehow handle this where I don't need to use dynamic sql to expand/contract my where clause?
Really need hlpe on this and hope I've made my question reasonably clear. Thanks.
View 3 Replies
View Related
May 24, 2004
is possible for a trigger to check a particular record by passing a parameter during an update event?
if yes, what is the syntax?
View 3 Replies
View Related
Apr 20, 2007
I need to parce a criteria in the data base
I have a field/colum in my tbl1 call invoice_num
I am left joining to to site_id on tbl2.
how can i talk tbl1 invoice_num and parce it to add in numbers.
IE tbl1.invoice _num is AF3456 and in order to match it to tbl2.site_id I need to add a 0 so that it would be AF03456 or AF034560.
Hope i did not loose you on this question.
View 2 Replies
View Related
Sep 23, 2005
I have a search page, containing 3 drop down lists. Theses are used to match data in 3 seperate collumns of a table.I can get the search to work when all boxes are completed, but I need to be able to leave some blank.A similar question was asked in http://forums.asp.net/626941/ShowPost.aspxAnd a very good solution was give: http://www.sqlteam.com/item.asp?ItemID=2077But I don't seem to be able to get any of these examples working!If I use the SET @SQL method, I get an error message saying that my input parameter has not been declared, when it has.When I use the COALESCE method, there is no data returned.(I am passing the data drop down list data fin the search page through the query string to a results page).Does anyone know of any other examples, or sample coding??Thanks...
View 2 Replies
View Related
Feb 6, 2006
Removing a parameter on a Reporting services report based on a 2005 AS cube results in an error:
[rsParameterReference] The Value expression for the query parameter €˜KalenderHierarchieJaarMaand€™ refers to a non-existing report parameter €˜KalenderHierarchieJaarMaand€™.
On the dataset, the parameter still exists. The error still occors after removing the parameter overhere too.
Steps to reproduce:
Build a raport based on a cube. Make a parameter in the dataset. check if the parameter works. Save the report and remove the parameter via the menu: Report> Report parameters > select the parameter and choose [Remove]. go to the "Preview" tab.
Has anyone found a work-around for this?
Thanks in advance,
Pieter
View 2 Replies
View Related
Apr 24, 2007
I get the ora-12645 error when doing an import from oracle to mssql. The error comes-up when setting up the datalink properties of the source database which is oracle. This error happens on a 2003 server machine using the import/export wizard. It works fine when using an xp machine.
any ideas?
View 19 Replies
View Related
May 22, 2000
make it so that I compare that parameter, that is a char(3), to something like "XXX" and then choose the correct SELECT statement to use depending on that parameter.... much like an if else.
Any help is GREATLY appreciated. Thanks.
Robert
View 1 Replies
View Related
Jul 23, 2005
Ok,This sounds dangerous (and yes I know it is)But its in a controlled enviroment and I really need to know how to dothis.How can I pass a Subquery for an Exist or In clause as a paramaterSomething like thisCREATE procedure dbo.mytry@funk varchar(1000)asSelect * from Customers where exists(@funk)GOSo I would execute something like soexec mytry @funk='Select ID From Customers where ID < 100'Any Ideas, I have tried LOTS of things but I can actually get it towork.I need to use it conjunction with a 3rd party product that can onlyselect from a Stored Procedure, and I can only pass paramaters to theSP.Any suggestions ?ThanksChris
View 6 Replies
View Related
Dec 26, 2007
I use "All" as default value in my parameters, my parameters are multivalue.
Now if a user chooses a value it will mark the value but the All will also remain marked. Isn't there a way that it deselects the all when one or more individual values are chosen ?
I thought that the reporting services just didn't have that functionality but someone told me that its possible but i haven't found it anywhere.
View 3 Replies
View Related
Jan 8, 2008
I have a parent and child package. i pass a parent package variable called @abc with a value of (1,2,3,4,5,6) to the child package. here in the oledb source i have a select statement like,
select *
from A
where id in (@abc)
It gives me an error. any way to make this work.
View 14 Replies
View Related