Input Parameter Exceeds The Limit Of The Data Type's Length In Stored Procedure

Hi guys, is there any way to solve my problem as title ? Assuming my stored proc is written as below :

@A VARCHAR(8000) = '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,...,5000'


DECLARE @B nvarchar(MAX);
SET @B = 'SELECT * FROM C WHERE ID IN ( ' + @A + ')'

EXECUTE sp_executesql @B

View Replies


Data Type Of Parameter Passing To A Stored Procedure

I pass a paramter of text data type in sql server (which crosspnds Memo data type n Access) to a stored procedure but the problem is that I do not know the crossponding DataTypeEnum to Text data type in SQL Server.

The exact error message that occurs is:

ADODB.Parameters (0x800A0E7C)
Parameter object is improperly defined. Inconsistent or incomplete information was provided.

The error occurs in the following code line:
.parameters.Append cmd.CreateParameter ("@EMedical", advarwchar, adParamInput)

I need to know what to write instead of advarwchar?
Thanks in advance

View Replies View Related

Output Parameter With Text Data Type In Stored Procedure

How can I make a stored procedure which has a output parameter withtext data type? My procedure is:CREATE PROCEDURE try@outPrm text OutputASselect @outPrm =(select field1 from databaseName Wherefield2='12345')GOHere field1 is text data type.Thanks

View Replies View Related

How Can I Change The Data Type Of The Parameter For The Deployed Stored Procedure ??

I have Try to Create Stored Procedure in C# with the following structure
public static void sp_AddImage(Guid ImageID, string ImageFileName, byte[] Image)
But  when I try to deploy that SP to SQL Server Express  , The SP Parameters become in the following Stature
@ImageID uniqueidentifier
@ImageFileName nvarchar(4000)
@Image varbinary(8000)
But I don€™t want that Data types .. I want it to be in the following format
@ImageID uniqueidentifier
@ImageFileName nText
@Image Image
How Can I Control the data type for each parameter ??
How Can I Change the data type of the parameter for the Deployed Stored Procedure ??
How Can I defined the new Data type ??
What's the solution to this problem ??
Note : I get Error when I try to use Alert Statement to change the parameter Data type for the SP
ALTER PROCEDURE [dbo].[sp_AddImage]
      @ImageID [uniqueidentifier],
      @ImageFileName nText,
      @Image Image
EXTERNAL NAME [DatabaseAndImages].[StoredProcedures].[sp_AddImage]
And thanks with my best regarding

View Replies View Related

Stored Procedure Input Parameter ( 2.0)

This should be relatively easy but for some reason it isn't. I'm trying to simply add parameters to a stored procedure that performs a simple input and I can't do it... I keep getting an error that the parameters are not found when I am explicitly stating them. I could do this with VB ASP.NET 1.x but with all these radical changes with 2.0, I'm pulling my hair out.... I can get to work if I declare a sqlStatement in the code but don't want to go that route (but will if there is no other choice) Any help would be great:
Dim cmd As New SqlDataSource
cmd.InsertCommandType = SqlDataSourceCommandType.StoredProcedure
cmd.InsertParameters.Add("@firstName", txtFirstName.Text)
cmd.InsertParameters.Add("@lastName", txtLastName.Text)
cmd.InsertParameters.Add("@address1", txtAddress1.Text)
cmd.InsertParameters.Add("@address2", txtaddress2.Text)
cmd.InsertParameters.Add("@city", txtCity.Text)
cmd.InsertParameters.Add("@state", ddlState.SelectedItem.Value)
cmd.InsertParameters.Add("@zipCode", txtZipCode.Text)
cmd.InsertParameters.Add("@telephone", txtTelephone.Text)
cmd.InsertParameters.Add("@email", txtEmail.Text)
cmd.InsertParameters.Add("@agegroup", ddlAgeGroup.SelectedItem.Value)
cmd.InsertParameters.Add("@birthday", txtBirthday.Text)
cmd.InsertParameters.Add("@emailnotification", rbEmail.SelectedItem.Value)
cmd.InsertParameters.Add("@magazine", rbEmail.SelectedItem.Value)
cmd.InsertParameters.Add("@question", txtquestion.Text)
cmd.ConnectionString = "Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True"
cmd.InsertCommand = "sp_insertCustomer"

Stored Procedure:
CREATE PROCEDURE dbo.sp_insertCustomer @firstName nchar(30),@lastName nchar(30),@address1 nchar(50),@address2 nchar(50),@city nchar(30),@state nchar(2),@zipcode nchar(10),@telephone nchar(10),@email nchar(50),@ageGroup int,@birthday dateTime,@emailNotification int,@magazine int,@question varchar(1000)

INSERT tblCustomer

Procedure or Function 'sp_insertCustomer' expects parameter '@firstName', which was not supplied.
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_insertCustomer' expects parameter '@firstName', which was not supplied.
Source Error:
Line 24: cmd.InsertCommand = "sp_insertCustomer"Line 25: Line 26: cmd.Insert()Line 27: Line 28:

View Replies View Related

Help With XML Input Parameter For Stored Procedure

I am trying to send XML as an input parameter for a stored procedure. I have seen many articles that do a good job of describing different variations but all the examples show the stored procedure only pulling one value (field) per record from the XML input. I need to pull 3 fields for each record.

Here is an example of the XML being passed:
<property @propID="14" @propType="4" @propValue="Blah blah text" />
<property @propID="217" @propType="2" @propValue="Some other text" />

I have a table like this in my database:
and I want a stored procedure that will accept XML and update this table.
Here is what I am trying:
@inValues XML
--create a temporary table

--And then insert the values from the @inValues XML argument into the temporary table
--I am sure the SELECT statement is VERY wrong

SELECT @inValues('@propID', INT), @inValues('@propType', INT), @inValues('@propValue', NTEXT)
FROM @inValues.nodes('/object/property')

--...and then I will use the temp table to update the DB table (SCENE_PROPERTY_LINK) for each record where SCENE_PROPERTY_LINK.PROPERTY_ID = @props.PROPID
AND @props.PROPTYPE != 6


I am sure it would be more efficient to update the DB table directly from the XML argument, without using the temporary table. But, I will settle for this solution using the temp table. I have done some work creating XML output from several stored procedures but, this is the first time I have been faced with consuming XML input in SQL.

I apologize for the long post.
Thanks in advance for any help you can provide.

View Replies View Related

Help For Stored Procedure Input Parameter

Hi All,

I have a project which will be a tools to edit different tables.

Now I need a stored_procedure to select data from different table.

For example I have a table name "TableFields" which have "tableID","FieldName", "DataType"and so on columns.
It has the following records.
"1", "EmployedDate","date"

It has the following records.
"2", "Address","varchar"

Then I have table named "Employee" has columns "employeeID","FirstName","LastName","EmployedDate" which have the following data,

I also have address table has columns "AddressID","ApartNo","Address" and has the following data
"1","1101","1208 Mornelle Crt, Toronto",
"2","1209","1940 Garden Drive, Toronto"

I need to create a stored procedure to select data from table "employee " or table "address" or even other tables according to information from "TableFields."
So the table's name can be know as a input parameter, but the fields name will be a list of values and it all depends on tables.

I want to use fields name as a long string separated by",", like I have input "EmployeeID, FirstName,LastName" as an input parameter. But I don't know how to split the string.

Second, I need to create a stored procedure to insert or update data into these dynamically table.

Can anyone help me?

Thanks in advance.

View Replies View Related

Help: 900 Byte Limit On Stored Procedure Parameter?

Hi,I have a .NET application that I want to save the Config.EXE contentsto my SQL database for remote review/testing. This config file is3700+ bytes long. I created a field in one of my tables with a VARCHAR4800 and then created a stored procedure that receives a parameter(also VARCHAR(4800).However it fails to write anything if the length of the value that Ipass is anything greater than 900. If I pass exactly 900 characters orless - the data is written to the field. If I pass 901 characters Iget nothing.I'm suspicious since it is exactly 900. I seriously doubt it's somelimitation of MS-SQL so I need a nudge in the right direction.Thanks

View Replies View Related

Length Of Input Parameter For SP

quick question. The cold fusion application calling the stored proc needs to pass a very long string to the stored proc.
If i declare the input variable of type varchar(8000) , does it just allow up to 8000 characters, if so are there other options that would allow a longer string?
Any alternate ways of doing this?

View Replies View Related

UpdateText Through A Stored Procedure On A Input Parameter

Hi Fellows,I am trying to write a User Defined Function in SQL 2000 to accept aParameter of type Text Field and then Do a replace function tomanipulate the some strings inside that text data. (Scenario: I havean email body and i have to replace some tags like <^ContactEmail^>and others to get the actual Data from the DB, I can do this) but myproblem I cannot do a replace function a text data type and the SQLBOL says I have to use UPDATETEXT but as I see the example I have afeeling that Updatetext can only be done on a field inside a table, Iam not sure how to get a stored procedure to accept a text data typeinput parameter and do a Updatetext on that input parameter.Your help and guide is higly appreciated.Thanks in advance.Dev

View Replies View Related

Vector As Input Parameter For Stored Procedure


i wanna know if i can use a vector (or an array) for input parameter to a Stored Procedure? Can anyone give me example or links?


View Replies View Related

Stored Procedure Input Parameter Get Truncated

I have a stored procedure that takes an input string parameter defined as @name nvarchar(24). The stored procedure takes this string and insert it into a table with the column also defined as nvarchar(24). When I execute this stored procedure with a string of more than 24 characters, the input string somehow get truncated and inserted successfully into the table without giving an error. Why is this the case?

If I simply execute an insert statement with a string longer than 24 characters, it will give me an error message. I tried enclosing the insert statement with a try-catch block in the stored procedure but I still can't trap any error.

Any suggestions?

View Replies View Related

Using Datagrid Selected Value To Input To Parameter In Stored Procedure

could someone please let me know if i am declaring the parameter wrong or have the wrong VB CODE.  I receive my 3 column headers in my datagrid but the parameter isn't doing anything
@Machcusid nvarchar  OUTPUTAS
SELECT     dbo.Machine.machcustomID, dbo.Problem.ProblemDesc, dbo.Request.ReqDateFROM         dbo.Machine INNER JOIN                      dbo.Request ON dbo.Machine.machID = dbo.Request.MachID INNER JOIN                      dbo.Problem ON dbo.Request.ProblemID = dbo.Problem.ProblemIDwhere machcustomID = @MachcusidGO
Private Sub LSBmachcusid_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LSBmachcusid.SelectedIndexChanged
SqlSelectCommand2.Parameters("@Machcusid").Value = LSBmachcusid.SelectedItem.Value
End Sub
End Class

View Replies View Related

Bit Type Parameter For Stored Procedure

I am trying to supply a bit type parameter to a stored procedure. This is used to update a Bit type field in a table. The field is called PDI

The syntax I am trying to use is:

MyStoredProcedure.Parameters.Add(New SqlParameter("@Pdi",SqlDbtype.bit))
MyStoredProcedure.Parameters("@pdi").value = -1

When I do my ExecuteNonQuery I get error 8114

What am I doing wrong?

View Replies View Related

Stored Procedure Parameter Without Type

Hi All !

How i can create a stored procedure like ISNULL ?

I need to a procedure that accepts Int type parameters and Float type ect.

Thanks. DBT.

View Replies View Related

Is It Possible To Capture An OUT Type Parameter From A PL/SQL Stored Procedure?

When a stored PL/SQL procedure in my Oracle database is called from ASP.NET, is it possible to retrieve the OUT parameter from the PL/SQL procedure?  For example, if I have a simple procedure as below to insert a row into the database.  Ideally I would like it to return back the parameter named NewId to my ASP.NET server.  I'd like to capture this in the VB.NET code. 1 create or replace procedure WriteName(FirstName in varchar2, LastName in varchar2, NewId out pls_integer) is2 3 NameId pls_integer;4 5 begin6 7 select name_seq.nextval into NameId from dual;8 9 insert into all_names(id, first_name, last_name)10 values(NameId, FirstName, LastName);11 12 NewId := NameId;13 14 end WriteName;  1 <asp:SqlDataSource 2 ID="SqlDataSaveName" 3 runat="server" 4 ConnectionString="<%$ ConnectionStrings:ConnectionString %>"5 ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" 6 SelectCommand="WRITENAME"7 SelectCommandType="StoredProcedure">8 <SelectParameters>9 <asp:ControlParameter ControlID="TextBoxFirstName" Name="FIRSTNAME" PropertyName="Text" Type="String" />10 <asp:ControlParameter ControlID="TextBoxLastName" Name="LASTNAME" PropertyName="text" Type="String" />11 </SelectParameters>12 </asp:SqlDataSource>This is then called in the VB.NET code as below. It is in this section that I would like to capture the PL/SQL OUT parameter NewId returned from Oracle. 1 SqlDataSaveName.Select(DataSourceSelectArguments.Empty) If anybody can help me with the code I need to add to the VB.NET section to capture and then use the returned OUT parameter then I'd be very grateful.

View Replies View Related

Row Length Exceeds 8060 Bytes

Hi All,I have created a table in sql server 2000 where at the time of creatingit, the row size excced 8K. I understand why I get the warning below:The table 'tbl_detail' has been created but its maximum row size(12367) exceeds the maximum number of bytes per row (8060). INSERT orUPDATE of a row in this table will fail if the resulting row lengthexceeds 8060 bytes.However, when I call a stored procedure from my ASP Code, which returnsme this warning, my ASP page displays the warning and does not move tothe next line.What can I do not to get this warning? How do I turn off warningmessages? I tried to wrap my stored procedure call code within SETNOCOUNT ON and SET NOCOUNT OFF but that didn't help.Any help would be really appreciated,Thanks,Boris

View Replies View Related

Index Entry Exceeds The Maximum Length

I'm seeing this error in my application log. Not quite sure how it started happening all of a sudden. I'm not quite sure where to start on this one.

Any suggestions greatly appreciated!


Exception information:
Exception type: SqlException
Exception message: Operation failed. The index entry of length 1007 bytes for the index 'tblMessage25' exceeds the maximum length of 900 bytes.

View Replies View Related

Syntax To Pass A Parameter Of Type Nvarchar(max) To Stored Procedure

I have a stored procedure that contains a paramteter of type nvarchar(max). What is the syntax to pass this parameter to the sp from a VB.Net application. Specifically, I need to know what to put in the 3rd parameter below:

cmd.Parameters.Add("@Name", SqlDbType.NVarChar, , Name)

View Replies View Related

Niewbie: Accept The Input Parameter Of Astring Type

Very simple problem but I still can not sovle, could you help me?
I have a following store procedure:

create procedure countauthors @state as
declare @countauthors int
select @countauthors=count(*) from authors
where state=@state
return @countauthors

declare @result int
exec @result=countauthors'ca'

But it don't work for me. Is something wrong here?
many thanks

View Replies View Related

Execute SQL Task With An INPUT Parameter Of Type DBTIMESTAMP

Hi Everyone,

I'm trying to do something that should be fairly straightforward, but SSIS seems to be getting confused. I have a stored procedure which takes a timestamp as an input parameter. (NOTE: It's not a DateTime that's being stored as a DBTIMESTAMP, it really is a timestamp in the SQL sense.)

The command should be something like this:

Code Block

EXEC dbo.UpdateSynchTimestamp ?
I tried to use my variable to pass the value through Parameter Mapping, but I got an unusual error:

[Execute SQL Task] Error: Executing the query "EXEC dbo.UpdateSynchTimestamp ?" failed with the following error: "An error occurred while extracting the result into a variable of type (DBTYPE_DBTIMESTAMP)". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

This is strange for a number of reasons:

1) The DBTIMESTAMP parameter has the Direction set to "Input", so it should not be interpreted as an Output or ReturnValue.
2) The Execute SQL Task has Result Set = "None", so it should not be trying to return anything.

If I change the code to include a value hard-coded it works:

Code Block

EXEC dbo.UpdateSynchTimestamp 0x00000000000013BD
It is only when a variable is involved that it breaks.

Finally, here's the Stored Procedure itself:

Code Block

CREATE PROCEDURE [dbo].[UpdateSynchTimestamp]

UPDATE ServerSettings
SET [Value] = @NewValue
WHERE [Key] = 'SynchTimestamp'
Doe anyone have any suggestions as to why this isn't working for me? For the time being, I have a Script Task which constructs the command text and stores it in a variable. I can't even use an Expression because the DBTIMESTAMP is not supported.

Thanks for reading this!

View Replies View Related

How To Declare A Procedure Parameter Type To Match A Referenced Table Colum Type

I like to define my procedure parameter type to match a referenced table colum type,
similar to PL/SQL "table.column%type" notation.
That way, when the table column is changes, I would not have to change my stored proc.
Any suggestion?

View Replies View Related

Data Type Conversion In Stored Procedure


I have a stored procedure a portion of which looks like this:

IF (CAST (@intMyDate AS datetime)) IN (
FROM PayPerView PP
WHERE PP_Pay_Indicator = 'N' )
--ToDo Here

@intMyDate is of type int and is of the form 19991013
SHOW_END_DATE is of type datetime and is of the form 13/10/1999

however when I run the procedure in sql query analyzer as:

EXEC sp_mystoredproc param1, param2

i get the error:

Server: Msg 242, Level 16, State 3, Procedure usp_Summary_Incap, Line 106
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

what is the proper way of doing the conversion from int to datetime in stored procedure?

thank you!


View Replies View Related

Input Parameter To Store Procedure

I am using a ListBox where a user can choose multiple lines.
The index of the selected items are then used in a stored procedure.

I wan´t to use the ID´s in this statement:
SELECT * FROM MyTable WHERE MyID IN (1,2,4,9)

But how can I do this?
If I pass them as a string, then I can´t use them as above.
Can I separate the string '1,2,4,9' so I can use them in the statement above?
Or can I send the values as a array to the stored procedure?


View Replies View Related

Table Name As An Input Parameter To A Procedure

I have this procedure it is creating the proc but when I execute it gives error
Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@ID".
Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@nextCode".


CREATE PROCEDURE [dbo].[GetNextAction]

@Code char(10),
@Track varchar(30)


Declare @ID int;
SET @SQL1='Select @ID = Sequence from'+' '+ @Track+ ' where Code=@Code'
Declare @nextCode varchar;
SET @SQL2 ='Select @nextCode= Code from '+' '+ @Track+ ' where sequence =(@ID+1)'
Declare @NextAction varchar(30);
Select @NextAction= nextAction from [dbo].[CaseStage] where Code=@nextCode;
Select @NextAction;

Can someone correct me here

View Replies View Related

Converting Date Data Type In Stored Procedure

HI Experts...

I am using SQL SERVER 2005 standard edition

I have encountered a problem regarding converting date data type in stored procedure

As i was having problem taking date as input parameter in my stored procedure, so, then  I changed to varchar (16) i.e.

CREATE PROCEDURE sp_CalendarCreate
                                      @StDate VARCHAR(16) ,
                                      @EDate VARCHAR(16),

then I am converting varchar to date with following code



When I try to execute the procedure with following code

execute sp_CalendarCreate @stdate='12-1-06',@edate='20-1-06'

but it gives me following error

Msg 242, Level 16, State 3, Procedure sp_CalendarCreate, Line 45
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Can any one tell me the  solution of that problem (at ur earliest)



View Replies View Related

RS 2005: Stored Procedure With Parameter It Runs In The &&"Data&&" Tab But The Report Parameter Is Not Passed To It

since a couple of days I'm fighting with RS 2005 and the Stored Procedure.

I have to display the result of a parameterized query and I created a SP that based in the parameter does something:

CREATE PROCEDURE [schema].[spCreateReportTest]
@Name nvarchar(20)= ''


declare @slqSelectQuery nvarchar(MAX);

set @slqSelectQuery = N'SELECT field1,field2,field3 from table'
if (@Name <> '')
set @slqSelectQuery = @slqSelectQuery + ' where field2=''' + @Name + ''''
EXEC sp_executesql @slqSelectQuery

Inside my business Intelligence Project I created:
-the shared data source with the connection String
- a data set :
CommandType = Stored Procedure
Query String = schema.spCreateReportTest
When I run the Query by mean of the "!" icon, the parameter is Prompted and based on the value I provide the proper result set is displayed.

Now I move to "Layout" and my undertanding is that I have to create a report Paramater which values is passed to the SP's parameter...
So inside"Layout" tab, I added the parameter: Name
allow blank value is checked and is non-queried

the problem is that when I move to Preview -> I set the value into the parameter field automatically created but when I click on "View Report" nothing has been generated!!

What is wrong? What I forgot??

Thankx for any help!
Marina B.

View Replies View Related

Data Type And Length In SQL Server

Hello folks!

I have written a stored proc that selects data from this table:

and puts it another table in this manner:

In short, I wanted to concatenate data in the second column.

My question is for some reason, SQL Server is limiting the length of the string to only 256 characters, even though I have defined the local variables in the storeds proc as varchar(1000) and the table has that field defined as text.

Any ideas how I can get around this problem?



View Replies View Related

How To Deal With Image Data Type Within Stored Procedure When Using SQL2000?

Does anyone know how to deal with image data type within stored procedure when using SQL2000?
I have a table and there is an image data type column. In this table I need to make a copy of one row within a table through a SP /it means to retrieve the whole row within SP,  change another columns data (but, that the image is not modified) / and save the modified row back to the same table.
Problem is, that within SP is not alowed to use local varaibles of image data type. Does anyone know a solution for this? Please.
Thank you.

View Replies View Related

Convert Data File Type From Unix To Pc Using Stored Procedure?

Hi, I have a script written in ASP to load data file (.csv) to ms sql. In the script, I have a portion of script looks like taht :
Do While NOT oInFile.AtEndOfStream  oOutFile.WriteLine Replace(oInFile.Readline, chr(13), vbcrlf)Loop
After that, I will use a BULK INSERT to input data to ms sql.
I am wondering how do I convert each row (data) to vbcrlf in Stored Procedure? Coz' I did not compose the convertion part, and I got no error when running BULK INSERT, but no rows are inserted :( HELP!!!!
I guess it's because the file is not being converted into a correct format??

View Replies View Related

User Defined Data Type Used In Stored Procedure Parameters

I have several stored procedures with parameters that are defined withuser defined data types. The time it takes to run the procedures cantake 10 - 50 seconds depending on the procedure.If I change the parameter data types to the actual data type such asvarchar(10), etc., the stored procedure takes less that a second toreturn records. The user defined types are mostly varchar, but someothers such as int. They are all input type parameters.Any ideas on why the stored procedure would run much faster if notusing user defined types?Using SQL Server 2000.Thanks,DW

View Replies View Related

Copyrights 2005-15, All rights reserved