SQL Task - Output Parameter Fails Using Strings.
Oct 3, 2005Scenario:
View 14 Replies
ADVERTISEMENT
Execute SQL Task – Output Parameter On Stored Procedure Causes Task To Fail.
Dec 2, 2005I have a SQL Task that calls a stored procedure and returns an output parameter. The task fails with error "Value does not fall within the expected range." The Stored Procedure is defined as follows: Create Procedure [dbo].[TestOutputParms] @InParm INT , @OutParm INT OUTPUT as Set @OutParm = @InParm + 5 The task uses an OLEDB connection and has a source type of Direct Input. The SQL Statement is Exec TestOutputParms 7, ? output The parameter mapping is: Variable Name Direction Data Type Parameter Name User::OutParm Output LONG @OutParm
View 7 Replies View RelatedExecute SQL Task Fails When Passing A Parameter Using OLE DB
Apr 26, 2008I have a SSIS Execute SQL Task that calls a stored procedure with a date parameter. The text of the stored procedure is an "INSERT INTO .. SELECT ..." statement. When I run the text in Query Analyzer, it completes successfully. When I call the Stored Procedure, it executes but does not insert the data. Setting ByPass Prepare to True does not affect the outcome. I also used the query directly in the SQL task itself to no avail. Executing the query in Query Analyzer works. Any assistance would be greatly appreciated.
Dan
Output Assign To Parameter....from Execute Sql Task.
Feb 28, 2008Hi all,
I'm trying to capture the OUTPUT from Execute Sql task...However when I run, the parameter didn't seem to capture the OUTPUT.
In my Sql Task, the parameter mapping:
Variable name: user::variable,
direction: OUTPUT,
Data Type: Varchar,
parameter name: 0,
parameter size: -1
connectiontype: OleDB
sourcetype: direct input
statement ELECT columnx FROM table1 WHERE (columnID=
(SELECT MAX(columnID) ASID
FROM table1 A)
I could be misunderstood on how Execute sql task work on Output.
thanks
Execute Sql Task To Set Output Parameter Of Type Integer
Jun 1, 2006I'm having a heckuva time with creating output parameters based on a query.
Here's what I'm doing. Every day, we copy rows from our mysql table to our sql server table. We only want to copy those rows that are new, so as to not have to recopy the entire table.
So I have a DataReader Source set to Ado.net/odbc provider that connects to the mysql db.
The destination is an OLE connection to the local sql server.
I create an Execute SQL Task.
The connection is set to the OLE connection
The type is direct input
The SQL Statement is "select max(id) from copy_table"
In Parameter Mapping, I create a user variable called maxId that is an int64. That variable is now used as the Variable Name. The Direction is Output. The Parameter Name is 0.
Whatever data type I use for the mapping does not work with the variable type. If the parameter was set to ULARGE_INTEGER, here's the error
[Execute SQL Task] Error: Executing the query "SELECT MAX(stats_id) AS max_id FROM copy_table" failed with the following error: "Type name is invalid.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
If parameter is set to LONG:
[Execute SQL Task] Error: An error occurred while assigning a value to variable "maxId": "The type of the value being assigned to variable "User::maxId" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. ".
I found that if variable and parameter were dates to use datetime for the variable and DBTIMESTAMP for the parameter.
There are an awful lot of combinations between all the possible variable types, and the possible parameter types. Does anyone know the secret combination for your typical integer?
Thanks,
Lori
Trailing Spaces On Output Parameter Of SQL Task - Different Behavior On Different Machines
Dec 20, 2007
Hello Everyone,
I've seen many entries about trailing spaces but have not found one like this.
In the Control Flow I am using an "Execute SQL Task" to populate some SSIS local variables (type string) by: (1) executing a SQL stored proc with output variables (type varchar(100)) to (2) be mapped to the local variable name (the parameter mapping Data Type is VARCHAR).
One of these mapped outputs is used as a path for subsequent operation in the Control Flow. At execution the sproc fires, populating the local variable with the path but with trailing spaces out to 255. Later in the "Script Task" when that path is used I receive an error telling me that the path is too long, and something about 260 or 246 characters.
Here's the oddity. I have two desktop environments running XP and a server environment (server 2003). This package runs just fine on the server - no trailing space issue, no need to trim. But on both my desktops I get the errors. By adding trim statements I can get back the correct path, but varchars should not be including trailing spaces, and the sproc return variable is a varchar (100).
I know this soulds like numerous other posts which indicate the solution is to trim, but I think the question I am asking is why does it work on the server but not the desktop? Is the SSIS variable type string experiencing a bug on different OS's?
Not to further complicate the issue but it used to work on my laptop, but through a horrible sequence of events I had to reload the studio in which case the error started to happen on that too.
Parse Strings To Get Selected Output
Nov 26, 2009I have a field with following value :
USE TESTTABLE
GO
CREATE TABLE Example (
Description varchar(500))
GO
INSERT INTO [TESTTABLE].[dbo].[Example] ([Description])
VALUES ('hello This is a test query.
[Code] ....
I want to extract three things from the above data stored ina single field
1. LAST NAME (All characters before COMMA till a special character is met)
2. FIRST NAME (All characters AFTER COMMA till a special character is met)
2. ID (All numbers after the word ID. Ignore all special characters before and after the numbers)
The Output will be :
FIRST NAME LAST NAME ID
John Doe 123456
John Doe 123456
John Doe 123456
How can I use substrings to extract this data in SQL 2005
Integration Services :: Stored Procedure In Execute Task Fails But Task Does Not Fail
Jul 1, 2015I'm using SSIS in Visual Studio 2012. My Execute SQL Task calls a Stored Procedure where I have a TRY-CATCH. Last week there was a problem and the CATCH was executed and logged an error to my error table, but for some reason the Execute SQL Task didn't fail. Is there a setting to make the Execute SQL Task fail when an SP encounters a failure?
View 3 Replies View RelatedThe Formal Parameter @ReportingId Was Not Declared As An OUTPUT Parameter, But...what Is This?
Apr 17, 2008After running my ssis pkg for some time with no problems I saw the following error come up, probably during execution of a stored procedure ...
An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The formal parameter "@ReportingId" was not declared as an OUTPUT parameter, but the actual parameter passed in requested output.".
I see some references to this on the web (even this one) but so far they seem like deadends. Doe anybody know what this means? Below is a summary of how the variable is used in the stored proc.
The sp is declared with 21 input params only, none of them is @ReportingId. It declares @ReportingId as a bigint variable and uses it in the beginning of the proc as follows...
Code Snippet
select @ReportingId = max(ReportingId)
from [dbo].[GuidToReportingid]
where Guid = @UniqueIdentifier and
EffectiveEndDate is null
if @ReportingId is null
BEGIN
insert into [dbo].[GuidToReportingId]
select @UniqueIdentifier,getdate(),null,getdate()
set @ReportingId = scope_identity()
END
ELSE
BEGIN
select @rowcount = count(*) from [dbo].[someTable]
where ReportingId = @ReportingId and...lots of other conditions
...later as part of an else it does the following...
Code Snippet
if @rowcount > 0 and @joinsMatch = 1
begin
set @insertFlag = 0
end
else
begin
update [dbo].[GuidToReportingId]
set EffectiveEndDate = getdate()
where ReportingId = @ReportingId
insert into [dbo].[GuidToReportingId]
select @UniqueIdentifier,getdate(),null,getdate()
set @ReportingId = scope_identity()
end
...and before the return it's value is inserted to different tables.
Data Flow Task SQL Strings
Jan 11, 2006Hi,
I just wanna ask:
I'm creating an SSIS package, a Data Flow Task. I have used OLEDB Source connected to a SQL Server Destination. Now in my OLEDB Source, I have this SQL statement
SELECT FirstName, LastName, Age FROM Employees WHERE (Age > 10) AND (Age < 95)
But what I want is to have the last name and first name concatenated and in proper case(capitalize first letter of the firstname and surname). I also want to TRIM or remove the blank spaces of the field in my SQL statement. How I be able to do this?
I tried using proper(), trim() and ucase() like in MSAccess but no success.
Please help. Thanks in advance.
List Of Strings Passed Into A Parameter
Oct 18, 2006I am trying to pass multiple values as parameters into my update command:UPDATE tblUserDetails SET DeploymentNameID = 102 WHERE ((EmployeeNumber IN (@selectedusersparam)));I develop my parameter (@selectedusersparam) using the following subroutine: Private Sub btnAddUsersToDeployment_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddUsersToDeployment.ClickDim iVal As Integer = 0Dim SelectedCollection As StringSelectedCollection = ""If (lsbUsersAvail.Items).Count > 1 ThenFor iVal = 0 To lsbUsersAvail.Items.Count - 1If lsbUsersAvail.Items(iVal).Selected = True ThenSelectedCollection = SelectedCollection & "," & lsbUsersAvail.Items(iVal).ValueEnd IfNextSelectedCollection = Mid(SelectedCollection, 2, Len(SelectedCollection))Session.Item("SelectedCollectionSession") = SelectedCollectionSqlDataSource4.Update()ltlUsersMessage.Text = String.Empty 'UPDATE tblUserDetails SET DeploymentNameID = @DeploymentNameIDparam WHERE (EmployeeNumber IN (@selectedusersparam))'SqlDataSource4.UpdateCommand = "UPDATE tblUserDetails SET DeploymentNameID = @DeploymentNameIDparam WHERE (EmployeeNumber IN (" + SelectedCollection + ")"ElseltlUsersMessage.Text = "Select users before adding to deployment. Hold Control for multiselect"End IfEnd SubFor some reason the query does not pass the parameters which are "21077679,22648722,22652940,21080617" into the queryI don't understand why.
View 4 Replies View RelatedMy Output Parameter Is Being Treated As An Input Parameter...why
Sep 25, 2006I 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.
Execute SQL Tasks With One Input And 2 Output Variables Fails
Mar 21, 2007I 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
SQL Transformation Task Fails
Oct 9, 2007Hi, I was wondering if anybody knows the solution to this problem: I have a DTS package (SQL Server 2000) which is scheduled to pick up and process a TEXT file into the SQL table. The file contains a lot of fields, among them are DUE_DATE and DISC_DATE in such format "MM/DD/YYY" but they are string type in the file. During Transformation Task both fields are being imported into SQL table as DateTime. It happened that today the task failed because the file contained 00 00 0000 and it couldn't convert integer to DateTime. Unfortunatly there is no way to check on correctness of data when file is supplied. Is it possible to do something during transformation task to either remove the record and send email that that record is not being processed or convert bad data to blank string so it can be processed. Not sure if converting is a best practice because i don't know what kind of junk could be entered :)I noticed that actually the problem occurs quite often due to invalid data format supplied in the file. It worries me that the whole process fails when the Transformation fails for the specific record.Please help,Thank you in advance!
Tatyana Hughes
DataFlow Task Fails?
Jan 18, 2007Hi:
I am getting the following error when I start debugging my Package, I am not sure what this is related to, but basically, input (datatype is a int, and its mapped to a column which is also int), so I am not sure whats happening here. The input column is actually a derived column, and its set as a 4 byte un-signed int, please advice on where should I start looking to troubleshoot this issue. This loanapplicationid is actually a user variable that is utilized by other tasks in my control flow as well:
Error: 0xC020901C at InsertApplicationCL5, OLE DB Destination [16]: There was an error with input column "LoanApplicationID" (1161) on input "OLE DB Destination Input" (29). The column status returned was: "The value violated the integrity constraints for the column.".
FTP Task Fails When Run As A Scheduled Job
Jun 10, 2005I've created an SSIS package that retrieves a file from an FTP
View 13 Replies View RelatedFTP Task Fails To Execute
Aug 30, 2007Hi everyone:
I have a very simple FTP Task that is supposed to grab two files from an ftp server and save them locally on my machine. The problem is that when I test the connection everything works okay (I know that this connection "hides" the password when seen on the Connection Manager Editor, so I re-enter it everytime I want to test the connection). I can see in the Task property that the username and passwords are set, but regardless I always get this error message:
Error: 0xC002918F at Cost Files Transfer, FTP Task: Unable to connect to FTP server using "FTP Bread".
Any idea what could be worng?
Thanks
Idelso
ExecuteSQL Task Fails And I Think It Should Not
Jun 19, 2005I setup my ExecuteSQL task to have a "Single Row" resultset. The query returns no rows. It fails. I don't think it should but then maybe this is consistent with the lookup transform piping rows down the error output if there is not a lookup value returned.
View 9 Replies View RelatedXML Task - Validation Fails
Oct 31, 2006Now is this a limitation in SSIS or am I missing something. I have an XML file that needs validation. I am using the 'XML Task' control for that. The validation runs fine so long as the file is below a certain size. I am not sure what the cutoff size is but the validation runs fine if the XML file size is 60Mb. I include a few more records in my XML file to make the file size 65Mb and run ETL again and validation fails!
There is not much detail in the error message other than 'Task XML Validation failed'. I dont think there should be any kind of timeout issue. The whole process takes less than a minute before failing.
The issue is definately not with the new records that get added. I have verified the new file with xmlspy and it validates just fine. I have also validated the newly added records (5 Mb) through the same ETL process and that runs fine too.
Please suggest if I should spend time finding what the issue is or simply move on assuming its a known bug? Has any one experienced this?
Thanks,
Sumeet
Getting Value From Output Parameter
Aug 2, 2006I have an SQL INSERT statement with an output parameter called @CusRef. I have been trying to store this in a session variable, however all i am able to store is the reference to the parameter object. (The returned value stored in the parameter is an Integer)Session("CustomerReference") = DataConn.InsertParameters.Item("CusRef")I cant seem to find a value field or a method to get the value from a parameter. I have also tried using CType(DataConn.InsertParameters.Item("CusRef"), Integer) but it cant convert the Parameter type to an Integer.Please help,ThanksGareth
View 1 Replies View RelatedOutput Parameter?
Aug 25, 2006A SQL Server 2005 DB table named "Users" has the following columns:
ID - int (IDENTITY)FirstName - varchar(50)LastName - varchar(50)UserID - varchar(20)Password - varchar(20)
Before inserting a new record in the DB table, ASP.NET first checks whether the UserID supplied by the new record already exists or not. If it exists, the new record shouldn't be inserted in the DB table & the user should be shown a message saying UserID already exists. To do this, ASP.NET uses a stored procedure. If the value returned by the stored procedure is 1, it means that the UserID already exists. If the value returned by the stored procedure is 0, then the new record should be inserted in the DB table. This is how I have framed the stored procedure:
CREATE PROCEDURE RegUsers @FName varchar(50), @LName varchar(50), @UserID varchar(50), @Password varchar(50), @return_value int OUTPUTAS IF EXISTS(SELECT UserID FROM Users WHERE UserID=@UserID) BEGIN SET @return_value=1 END ELSE BEGIN SET @return_value=0 INSERT INTO Users VALUES (@FName,@LName,@UserID,@Password) END
& this is how I am invoking the stored procedure from the ASPX page:
<script runat="server"> Sub btnSubmit(ByVal obj As Object, ByVal ea As EventArgs) Dim sqlCmd As SqlCommand Dim sqlConn As SqlConnection
sqlConn = New SqlConnection("Data Source=MyDBSQLEXPRESS;Initial Catalog=DB;Integrated Security=True") sqlCmd = New SqlCommand("RegUsers", sqlConn) sqlCmd.CommandType = CommandType.StoredProcedure
With sqlCmd Parameters.Add("@return_value", SqlDbType.Int, 4).Direction = ParameterDirection.ReturnValue Parameters.AddWithValue("@FName", txtFName.Text) Parameters.AddWithValue("@LName", txtLName.Text) Parameters.AddWithValue("@UserID", txtUserID.Text) Parameters.AddWithValue("@Password", txtPassword.Text) End With
sqlConn.Open() sqlCmd.ExecuteNonQuery()
If (sqlCmd.Parameters(0).Value = 1) Then lblMessage.Text = "UserID Already Exists!" ElseIf (sqlCmd.Parameters(0).Value = 0) Then lblMessage.Text = "Thanks For Registering!" End If
sqlConn.Close() End Sub</script><form runat="server"><%-- the 4 TextBoxes come here --></form>
When I execute the above ASPX code, if the UserID I entered already exists in the DB table, then ASPX generates the following error:
Procedure or Function 'RegisterUsers' expects parameter '@return_value', which was not supplied.
pointing to the line
sqlCmd.ExecuteNonQuery()
Can someone please point out where am I going wrong?
How Can I Get And Use An OUTPUT Parameter
Jan 31, 2007Here is what I have so far, I can get a number added to the table running my sproc from management studio. But how do I get it out as it is being intserted and then use it in my code?ALTER PROCEDURE [dbo].[NumberCounter]
-- Add the parameters for the stored procedure here
@InsertDate datetime
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
INSERT INTO tblNumberCounter (InsertDate) Values (@InsertDate);Select IDENT_CURRENT('tblNumberCounter')
END
Public Sub SubmitAdd_Click(ByVal Sender As System.Object, ByVal E As System.EventArgs)
Dim Con As SqlConnection
Dim StrInsert As String
Dim cmdInsert As SqlCommand
Dim myNewReceiptNumber As Integer
Dim ConnectStr As String = _
ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
'Add row to receipt table, then get it for ReceiptNumberText field.
cmdInsert = New SqlCommand
cmdInsert.CommandText = "NumberCounter"
cmdInsert.CommandType = CommandType.StoredProcedure
cmdInsert.Connection = New SqlConnection(ConnectStr)
cmdInsert.Parameters.AddWithValue("@InsertDate", System.DateTime.Today.ToShortDateString())
Try
Con.Open()
myNewReceiptNumber = cmdInsert.ExecuteScalar()
'Response.Write(myNewReceiptNumber)
Catch objException As SqlException
Dim objError As SqlError
For Each objError In objException.Errors
Response.Write(objError.Message)
Next
Finally
Con.Close()
End Try
End Sub
Why Am I Not Getting My OUTPUT Parameter
Jan 7, 2008Can anyone see in this stored procedure code and my post sub why the stored procedure is not getting the @ReceiptNumber?
@ReceiptNumber int OUTPUTASBEGININSERT INTO tblNumberCounter (InsertDate)Values (GETDATE())SET @ReceiptNumber=SCOPE_IDENTITY()INSERT INTO tblReceipts (pl_ID,client_ID,PaymentDate,Fund_ID,TenderTypeID,AmountPaid,ReceiptNumber,DateEntered,EnteredBy) SELECT PL.Pl_ID, RS.client_id, PL.pmtDate, RS.rec_fund_id, RS.rec_tendertypeid, RS.rec_amount, @ReceiptNumber, RS.DateEntered, RS.EnteredByFROM tblRec_setup AS RS INNER JOIN tblPayments AS PL ON RS.rec_id = PL.rec_id Sub Post() Dim cmdInsert1 As SqlCommand Dim tr As SqlTransaction = Nothing Dim ConnectStr As String = _ ConfigurationManager.ConnectionStrings("2ConnectionString").ConnectionString Dim conn As New SqlConnection(ConnectStr) cmdInsert1 = New SqlCommand cmdInsert1.CommandType = CommandType.StoredProcedure cmdInsert1.CommandText = "BatchMonthly" 'Get a new receipt number and add it. Dim InsertedIntegerRN As New SqlParameter("@ReceiptNumber", SqlDbType.Int) InsertedIntegerRN.Direction = ParameterDirection.Output cmdInsert1.Parameters.Add(InsertedIntegerRN) 'Try conn.Open() tr = conn.BeginTransaction() cmdInsert1.Transaction = tr cmdInsert1.Connection = conn cmdInsert1.ExecuteScalar() tr.Commit() 'Catch objException As SqlException tr.Rollback() 'Dim ex As SqlError 'Response.Write(ex.Message) 'Finally conn.Close() 'End Try End Sub
Can This Be Done With An Output Parameter?
Nov 4, 2003Hi I want to make a Function in my User class that adds new members into the db. What I want to do is add the users details in using a stored procedure and input parameters and then return a parameter indicating their userid value and set it to a variable.
My userid column in the my db is auto incrementing with a seed of 1 and a step value of 1.
How could I create an output parameter that would return their new user id and then how would i set it to an integer variable.
Thanks
Output Parameter
Jun 21, 2004I keep getting an error stating my stored proc expects "@ret_value" and it's not declared. Even once it is declared, I'm not sure if my proc is going to return the value properly...been working on this half a day.
params put on new line for readability...
Private Sub update_DB()
Dim intResult, ret_value As Integer
ret_value = 0
intResult = SqlHelper.ExecuteNonQuery(ConfigurationSettings.AppSettings("connString"), CommandType.StoredProcedure, "Media_Tracking_add_MarketingID",
New System.Data.SqlClient.SqlParameter("@strMarketingID", txtMarketingID.Text),
New System.Data.SqlClient.SqlParameter("@strAdCampaignName", txtAdCampaignName.Text),
New System.Data.SqlClient.SqlParameter("@strCompany", ddlCompany.SelectedItem.Value),
New System.Data.SqlClient.SqlParameter("@strCampaignType", ddlCampaignType.SelectedItem.Value),
New System.Data.SqlClient.SqlParameter("@ret_value", ret_value, ParameterDirection.Output))
lblSuccess.Text = "RETURN = " & ret_value
End Sub
S Proc
CREATE PROCEDURE [dbo].[media_tracking_add_MarketingID]
@strMarketingID nvarchar(50),
@strAdCampaignName nvarchar(50),
@strCompany nvarchar(3),
@strCampaignType nvarchar(50),
@ret_value int OUTPUT
AS
INSERT INTO media_tracking_Marketing_IDs(MarketingID,AdCampaignName,company,status,CampaignType)
values (@strMarketingID,@strAdCampaignName,@strCompany,'1',@strCampaignType);
set @ret_value = scope_identity();
return @ret_value;
GO
Output Parameter
Nov 17, 2005Hello!Can anybody tell me how can I retreive the value of an output parameter from an stored procedure using an SqlDataSource control?Thank you
View 1 Replies View RelatedParameter Output
Apr 28, 2008Can any body help me
CREATE PROCEDURE getRowCount
@where NVARCHAR(500)
,@totalRows INT OUTPUT
AS
BEGIN
DECLARE @SQL NVARCHAR(2000)
SET @SQL = ' SELECT ' + cast(@totalRows as NVARCHAR) + ' = COUNT(*)
FROM Employee E'
IF LEN(@where ) > 0
BEGIN
SET @SQL= @SQL + @Where
END
EXEC (@SQL)
END
It doesn't return a totalRows value.
Thanks in advance.
How Do I Run This SP To Get Output Parameter?
Dec 7, 2007I've tried using:
Declare @answer bit
Execute procUserVer username answer output
Create PROCEDURE procUserVer
@User char(15),
@Validate bit output
AS
Declare @UserFound int
Select @UserFound = count(*)
From usernames
where networklogin = @user
select @userFound
if @userFound >0
set @validate = 1
else
set @validate = 0
Output Of Scheduled Task
Nov 1, 2000I think I have a basic question. Can someone tell me
the command I can use to have a stored procedure run
and generate output? This is for SQL 6.5.
I would like to schedule the sp_help_revdatabase
stored procedure to run periodically and output it
to a file. I think this is possible, but I haven't
been able to come up with the syntax...
Thanks for any suggestions!
Toni
XML Task And Variable Output?
Feb 20, 2007Hello,
What I'm trying to do is get an XML query from a database and use the XML to render an excel document. Can this be done using SSIS? Can I actually read a column from a database and store it in a variable and then create an excel spreadsheet with that variable in SSIS? Would this be done in the control flow using the XML task editor?
Thanks,
Phil
Execute SQL Task W/ XML Output
Apr 19, 2007I have a stored procedure that returns XML and have defined my own "Root" element w/ "ROOT('urlset')" in the stored procedure. When I put this into my Control Flow, I have a subsequent task that takes this XML stream and writes it to a file. The issue I am running into is that SSIS is adding it's own "Root" element before my output. Is there any way of avoiding this?
View 4 Replies View RelatedUsing XML Output From UDF In Execute SQL Task
Feb 1, 2007Hi,
I am trying to do the following without success;
I have a UDF which returns a string of XML data (of the data type XML)
I want to use this UDF within an Execute SQL Task to set a variable to the output of the UDF. I then want to use the variable to pass the XML string to a XML Task to apply an XSLT to the XML.
I have set the following properties in the Execute SQL Task:
ResultSet: XML
SQLSourceType: Direct Input
SQLStatement: SELECT [dbo].[fn_GENERATE_XML](123) as rawxml
ResultName: 0
Variable Name:user::xml (where the variable is of type string)
My problem is something to do with the UDF call as if I substitute the UDF for a simple SELECT statement with a FOR XML Clause it works fine.
Any ideas where I am going wrong?
Thanks
FTP Task Fails For No Good Reason
Jan 11, 2008Hi,
I have a package that has several FTP tasks in sequence:
[ftp task 1]->[ftp task 2]->[ftp task 3]->[ftp task 4]
[Ftp task 4] fails about 20% of the time, for no apparent reason. This is the error message I receive:
[FTP Task] Error: Unable to receive files using "FTP Connection Manager".
The problem is, it works most of the time (80%), but every 4 or 5 tries, it fails. This makes NO sense to me. Is this a bug perhaps?
Please help... should we use an ftp script instead, as the ftp task seems rather unreliable.
Thanks