Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







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 :
 
CREATE PROC TEST
@A VARCHAR(8000) = '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,...,5000'
 
AS
BEGIN

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


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Data Type Of Parameter Passing To A Stored Procedure
Hi,
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 ??
Hi
 
I have Try to Create Stored Procedure in C# with the following structure
 
[Microsoft.SqlServer.Server.SqlProcedure]
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 ??
Or
How Can I Change the data type of the parameter for the Deployed Stored Procedure ??
Or
How Can I defined the new Data type ??
 
Or
 
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
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [DatabaseAndImages].[StoredProcedures].[sp_AddImage]
GO
 
And thanks with my best regarding
Fraas

View Replies !   View Related
Stored Procedure Input Parameter (asp.net 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:
Code:
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"

cmd.Insert()
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)

AS
INSERT tblCustomer
(firstName,lastName,address1,address2,city,state,zipCode,telephone,email,ageGroup,birthday,emailNotification,magazine,question)

Values(@firstName,@lastName,@address1,@address2,@city,@state,@zipcode,@telephone,@email,@ageGroup,@birthday,@emailNotification,@magazine,@question)
 
ERROR:
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:
<object>
<property @propID="14" @propType="4" @propValue="Blah blah text" />
<property @propID="217" @propType="2" @propValue="Some other text" />
</object>

I have a table like this in my database:
CREATE TABLE SCENE_PROPERTY_LINK (ID INT, OBJ_ID INT, PROPERTY_ID INT, PROPERTY_VALUE NTEXT)
and I want a stored procedure that will accept XML and update this table.
Here is what I am trying:
CREATE PROCEDURE sp_UpdateObject
@inValues XML
AS
BEGIN
--create a temporary table
DECLARE @props TABLE(PROPID INT, PROPTYPE INT, PROPVALUE NTEXT)

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

INSERT INTO @props(PROPID, PROPTYPE, PROPVALUE)
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


END

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","EmployeeID","Varchar"
   "1","FirstName","varchar"
   "1","LastName","varchar"
   "1", "EmployedDate","date"
 
It has the following records.
   "2","AddressID","int"
   "2","ApartNo","varchar"
   "2", "Address","varchar"
 
 
Then I have table named "Employee" has columns "employeeID","FirstName","LastName","EmployedDate" which have the following data,
   "001","Susan","Daka","1999-09-09",
   "002","Lisa","Marzs","1999-08-08",
   "003","David","Smith","2000-01-01",
 
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
hi,
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?
thanks
Rozina

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
Hi,

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?

thanks

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
STORED PROCEDURE
CREATE PROCEDURE USP_Machcusidsearch
@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
VB.NET CODE
Private Sub LSBmachcusid_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LSBmachcusid.SelectedIndexChanged
SqlDataAdapter2.Fill(DsMachcusidsearch1)
SqlSelectCommand2.Parameters("@Machcusid").Value = LSBmachcusid.SelectedItem.Value
DGstatussearch.DataBind()
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!

Thanks,
Mike123

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]
    @NewValue TIMESTAMP
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE ServerSettings
    SET [Value] = @NewValue
    WHERE [Key] = 'SynchTimestamp'
END
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
Hi,

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


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


where:
@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!

cheers,
g11DB

View Replies !   View Related
Input Parameter To Store Procedure
Hi,
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?


Regards!
Jonas

View Replies !   View Related
Table Name As An Input Parameter To A Procedure
Hi
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".

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[GetNextAction]

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

)
AS
BEGIN

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


END
GO
Can someone correct me here
Thanks

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

DECLARE @STARTDATE DATETIME
DECLARE @ENDDATE DATETIME

SELECT @STARTDATE = CAST(@STDATE AS DATETIME)
SELECT @ENDDATE = CAST(@EDATE AS DATETIME)

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)

regards,

Anas

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
Hello,
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:

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE PROCEDURE [schema].[spCreateReportTest]
 @Name nvarchar(20)= ''

AS
BEGIN

declare @slqSelectQuery nvarchar(MAX);

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

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:
A1 AA
A2 BB
B1 AAA
B2 BBB

and puts it another table in this manner:
A1 AA BB
A2 AA BB
B1 AAA BBB
B2 AAA BBB

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?

Thanks!

ParulV

View Replies !   View Related
How To Deal With Image Data Type Within Stored Procedure When Using SQL2000?
Hi,
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
SQL Server 'text' Data Type. Why Is Length = 16??
i need to store quite along description in the database, which in anyother database i would choose the data type 'text', however, can someone tell me why the length is set to just 16 in sql server..... i have seen a text field with far greater than 16 chars, set at length 16, so what does this length of 16 mean?????
also what is the max length of the text field?

thanks

View Replies !   View Related
Total Length Of Data Type In A Table
Folks:

I would like to know the total length of data type in a table. I ran the following query. Will this give me the correct information? I also ran sp_columns <table name> and it too give the length. But There is a difference in the numbers. Am I doing something wrong and which is the correct the query or sp_column.

select sum(length) from syscolumns
where id in (select id from sysobjects
where name = 'XYZABC')


sp_columns XYZABC



Thanks !

View Replies !   View Related
The Value You Entered Is Not Consistent With The Data Type Or Length Of The Column
I keep getting this error:

'The value you entered is not consistent with the data type or length of the column'

when trying to enter data into a feild, the feild type is char and the length
is 100 i'm entering text 3 words long but no where near a 100 characters long
any one know why this is happening?

View Replies !   View Related
Using A Declare Variable To Represent A Data Type Length
I'm trying to use a variable set by DECLARE to represent a data type length with CAST. Don't ask why. Here is what I'm got:

declare @n int
set @n = (select max(len(col_1)) from tblOne)
select cast(Col_2 as varchar(@n))
from tblOne

This should make Col_2 the length of the longest entry in Col_1. It does not work. I must use a set number for the length after the data type like this:

declare @n int
set @n = (select max(len(col_1)) from tblOne)
select cast(Col_2 as varchar(28)) -- (28 is longest current entry in Col_1)
from tblOne

Is there a way around this? I'm new to SQL.

View Replies !   View Related
Check Data Type And Maximum Length Of A Field
Hi All,

Is there a way to check the data type and the maximum length of a field?

View Replies !   View Related
How To Pass A XML Data Parameter To An SQL 2005 Stored Procedure
How to pass a XML data parameter to an SQL 2005 Stored Procedure
I hope to insert a xml data into an typed xml column in SQL 2005.
1. I can run the Code 1 correctly.
2. I hope that I can pass a XML data parameter to an SQL 2005 Stored Procedure, So create the Code 2. but I get the error below:XQuery [cw_bookmark.Bookmark.modify()]: Only non-document nodes can be inserted. Found "xs:string ?".
3. I create the Code 3, but I get the error below:XQuery [cw_bookmark.Bookmark.modify()]: ',' or ')' expected 
4. I create the Code 4, but I get the error below:XQuery: SQL type 'xml' is not supported in XQuery.
 
//--------------------------Code 1-------------------------------------create procedure Hellocw_InsertBookmark40@userId varchar(80)='61809B69-4AD5-40E4-B456-D957C78DD99E',@Id varchar(80)='a6dce8fe-749c-4e38-ab2f-3d03d9711b3d',asupdate cw_bookmark set Bookmark.modify('declare namespace x="http://www.hellocw.com/onlinebookmark";insert <x:Bookmark Id="ghdce3ak-456c-4e38-ab2f-5h02d9711b67" Title="cw" Url="kk" Description="Thte" InputDate="2004-08-12" IsPrivate="false"></x:Bookmark>as first into (//x:*[@Id=sql:variable("@Id")])[1]')where userId=@userId//--------------------------Code 1-------------------------------------
//--------------------------Code 2-------------------------------------create procedure Hellocw_InsertBookmark41@userId varchar(80)='61809B69-4AD5-40E4-B456-D957C78DD99E',@Id varchar(80)='a6dce8fe-749c-4e38-ab2f-3d03d9711b3d',@Insertxml varchar(80)='<x:Bookmark Id="ghdce3ak-456c-4e38-ab2f-5h02d9711b67" Title="cw" Url="kk" Description="Thte" InputDate="2004-08-12" IsPrivate="false"></x:Bookmark>'asupdate cw_bookmark set Bookmark.modify('declare namespace x="http://www.hellocw.com/onlinebookmark";insert sql:variable("@Insertxml")as first into (//x:*[@Id=sql:variable("@Id")])[1]')where userId=@userId//--------------------------Code 2-------------------------------------
//--------------------------Code 3-------------------------------------create procedure Hellocw_InsertBookmark41@userId varchar(80)='61809B69-4AD5-40E4-B456-D957C78DD99E',@Id varchar(80)='a6dce8fe-749c-4e38-ab2f-3d03d9711b3d',@Insertxml varchar(80)='<x:Bookmark Id="ghdce3ak-456c-4e38-ab2f-5h02d9711b67" Title="cw" Url="kk" Description="Thte" InputDate="2004-08-12" IsPrivate="false"></x:Bookmark>'asupdate cw_bookmark set Bookmark.modify('declare namespace x="http://www.hellocw.com/onlinebookmark";insert cast(sql:variable("@Insertxml") as xml)as first into (//x:*[@Id=sql:variable("@Id")])[1]')where userId=@userId//--------------------------Code 3-------------------------------------
//--------------------------Code 4-------------------------------------create procedure Hellocw_InsertBookmark41@userId varchar(80)='61809B69-4AD5-40E4-B456-D957C78DD99E',@Id varchar(80)='a6dce8fe-749c-4e38-ab2f-3d03d9711b3d',@Insertxml xmlasupdate cw_bookmark set Bookmark.modify('declare namespace x="http://www.hellocw.com/onlinebookmark";insert sql:variable("@Insertxml")as first into (//x:*[@Id=sql:variable("@Id")])[1]')where userId=@userId
//--------------------------Code 4-------------------------------------

View Replies !   View Related
Stored Proc Input Parameter Limited?
Hi all,

    I am trying to create a stored proc that will take
in a long string, but the stored proc does not allow me to take in more
than 50 characters at a time. Is there a way to take away the limit?
Please help me out, thanks in advance.



Daren

View Replies !   View Related
In SQL 2000 In TEXT Data Type How Much Long Is The Limit?
Hai Every one
      i am facing a werid problem it is related to storing a long text data in SQL 2000 the text data is some thing like the following
"dshjfsjlksdjakdjlksadjfeidkadflkdsajfieawirfjalkdfjsakdfjaiekdvnmckaumnmmmmmmmmmmmmmmmoadifdjsakdjfauiereoweiiiiiiiiiiiiiiiiiiiiiiiiiiidalfkjdsa,mlfdsdflvmsaldifsdjfskladfakdfjakladkalfkfadkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkadlkfjaidfajfkamcmmmmmmmmmmmmmmmmmmmmmmmmmmmmmiadlmfalierfmaerjeaiaelelllllllllllllllllllll..."
in short it is really long so i opted to go with TEXT data type as it states that it can store more than 8 kb but when i try to insert this data it gives me error stating that Text data type cannot be of length more than 128...........?
What  am i doing wrong........if Text is not the proper datatype to store such a data then can anyone suggest some thing better...............
Thanks in advance
Austin

View Replies !   View Related
How To Format The Date Data Type Input In DD-MM-YY
hi all , does SQL server able to input the date that is originally from 2/6/2004 11:07:46 AM to DD-MM-YY 06-02-04 and store in the DB. For example , this is the SQL insert query

INSERT INTO ADMIN ( ID , NAME , DATE ) VALUES ( 'A001' , 'karen' ,'2/6/2004 11:07:46 AM')

the date will automatically converted to 06-02-04 and store in DB .

Does it need to use SQL Rule or user-defined data type.

View Replies !   View Related
Creating A Stored Procedure That Will Summarize Data In A Table Into A Table Reflecting Period Data Using An Array Type Field
I am attempting to create a stored procedure that will launch at report runtime to summarize data in a table into a table that will reflect period data using an array type field.  I know how to execute one line but I am not sure how to run the script so that it not only summarizes the data below but also creates and drops the table.
 
Any help would be greatly appreciated.
 
Current Table
 
Project   |  Task      | Category |  Fiscal Year | Fiscal Month | Total Hours
---------------------------------------------------------------------------------------------------------
Proj 1     |  Task 1   | Cat 1      |  2007           |  01                |   40
Proj 1     |  Task 1   | Cat 2      |  2007           |  02                |   20
Proj 1     |  Task 1   | Cat 3      |  2007           |  03                |   35
Proj 1     |  Task 1   | Cat 1      |  2008           |  01                |   40
Proj 1     |  Task 1   | Cat 2      |  2008           |  02                |   40
Proj 1     |  Task 1   | Cat 3      |  2008           |  03                |   40
 
Proposed Table
 
Project   | Task      | Category   | Fiscal Month 01   | Fiscal Month 02   | Fiscal Month 03 | Fiscal Year
---------------------------------------------------------------------------------------------------------------------------------------------------
Proj 1     |  Task 1  | Cat 1        |          40              |           0               |         0              |    2007
Proj 1     |  Task 1  | Cat 2        |           0               |         20               |         0              |    2007Proj 1     |  Task 1  | Cat 3        |           0               |           0               |        35             |    2007
Proj 1     |  Task 1  | Cat 1        |         40               |           0               |          0             |    2008

Proj 1     |  Task 1  | Cat 2        |           0               |         40               |          0             |    2008
Proj 1     |  Task 1  | Cat 3        |           0               |           0               |        40             |    2008
 
Thanks,
Mike Misera

View Replies !   View Related
Limit The Number Of Digits After Decimal Point In Flaot Data Type
Hi..
I have a column in the data base with the type Float,
I want to limit the number of digits after decimal point to 2 when I display the value in ASP.NET but I don't know how!?
the number that appear after calculation llike "93.333333"
I use decimal(2,2) as data type but an error accour and this is the message
"- Unable to modify table.  Arithmetic overflow error converting float to data type numeric.The statement has been terminated."
 Can you help me..
thanks
 

View Replies !   View Related
Limit On Blob/image Data Type In SQL Compact 3.5 SP 1 BETA For ADO Entity Framework?
Hi! I tried to save some image data, but it get truncated at 8000 (the table column is defined as Image). I then wrote a converter to try ntext-datatype instead, but it gets truncated at 4000.


Error message:
System.Data.SqlServerCe: @3 : String truncation: max=4000, len=4168


The code uses only ADO entity framework for database access. Is there a way to store binary data larger than 8000 bytes? I am running SQL Compact 3.5 sp 1 BETA.


Henning



 

View Replies !   View Related
Flat File Name As SSIS Data Source Input Parameter
Each day I receive a file with a different name. For example, the name is filename_mmddyyyy.txt where filename_ stays constant and mmddyyyy is the date of the file. The file is always in the same format.
 
I want to build an SSIS where I pass it this file name. I can write a script to generate the correct file name. How do I build the SSIS so it can accept the input parameter and find the correct file to process?
 
Thanks

View Replies !   View Related
Stored Procedure That Uses Input From Another Table
Hi,    I need to be able to create a Stored Procedure that gets its information based on dates stored in another table.Does anyone have an idea on how I can acheive this??Regards..Peter.

View Replies !   View Related
Parameterised Input Stored Procedure
I need to create a stored procedure with a parameterised input. An input parameter is declared as a variable, this will hold the units that a user wants to insert from TableA into TableB. Using a CASE clause, within a
stored procedure, it will first need to compare the input parameter against the avaiable units in TableA, let's say
in this case, an input of 3 units of Item_No (1235), is required to be inserted into TableB's 4 columns (refer below for field names). The stored procedure should be able to assign the units required base on the earliest Item_ID (1st Primary key comes first, on a first-in-first-out basis) from TableA, by first filtering a number of sequential criteria. First it would search for the Item_No (1235) in TableA, next its available units,
if both condition are met, next, insert a row of record into TableB's 4 columns, If units required is insufficient,
it would loop for the next available units, then inserting the next row of records into TableB. An update procedure is also required to be created to update the units being used for the assignment in TableA (refer table below
for updated records for Item_No (1235).

The following are the fieldnames in TableA running on SQL Server 2000:

Dates as datetime
Item_ID as int (Primary key)
Item_No as int
Qty as int
Unit_Price as int

TableA consist of the following info :
======================================
Dates | Item_ID | Item_No | Qty | Unit_Price
======================================
30/10/2007 | IT1000 | 1234 | 2 | 4
======================================
30/10/2007 | IT1001 | 1235 | 2 | 6
======================================
28/09/2007 | IT1002 | 1236 | 4 | 8
======================================
01/11/2007 | IT1003 | 1235 | 2 | 2

INSERT Result in TableB after executing the stored procedure :
======================================
Dates | Item_ID | Item_No | Qty | Unit_Price
======================================
30/10/2007 | IT1001 | 1235 | 2 | 6
======================================
01/11/2007 | IT1003 | 1235 | 1 | 2

UPDATE Result in TableA after executing the stored procedure :
======================================
Dates | Item_ID | Item_No | Qty | Unit_Price
======================================
30/10/2007 | IT1000 | 1234 | 2 | 4
======================================
30/10/2007 | IT1001 | 1235 | 0 | 6
======================================
28/09/2007 | IT1002 | 1236 | 4 | 8
======================================
01/11/2007 | IT1003 | 1235 | 1 | 2

The following stored procedure was created:

Create procedure dbo.up_parmins_item
@Date_DT VARCHAR(22),
@Item_No INT,
@Qty_IT INT,
@Unit_Price_IT INT as

Declare @Item_ID INT

Select @Item_ID = Item_ID
From TableA
Where Item_No = @Item_No and Qty <> 0
-- If item not exist, then insert into TableA
If @Item_ID IS NULL
BEGIN
Insert into TableA
( Dates,Item_No,Qty,Unit_Price)
Values(Getdate(),@Item_No,@Qty_IT,@Unit_Price_IT)
Set @Item_ID = @@IDENTITY
END
-- If item exist, and Qty is not 0, then insert into TableB and Update TableA
If @Item_ID IS NOT NULL and Qty <> 0
BEGIN
Set @Item_ID = Min(Item_ID),
Insert into TableB
( Dates ,Item_ID,Qty,Unit_Price)
Values(Getdate(),@Item_ID,@Qty_IT,@Unit_Price_IT)
-- update quantity balance for Item_No (1235) in TableA
Update TableA
Set Qty = (@Qty_IT - Qty)
Where Item_No = @Item_No
END

If @@Error > 0
BEGIN
RAISERROR ('Update and Insert Items failed',16,1)
RETURN 99
END

RETURN 0

My issue here is, how do I create a CASE clause or if-else statement to verify the quantity (Qty) available in TableA is sufficient for the input parameter (@Qty_IT) to be inserted into TableB? The procedure should be able to perform a loop in the CASE or If-else statement to allocate the quantity requested base on the earliest Item_ID existing in TableA, and the next available units in the subsequent Item_ID (e.g. (1st) IT1001 - 2 units, (2nd) IT1003 - balance of 1 unit) If the units requested (input parameter) for an item, does not exist in TableA, a new record is then inserted into TableA for this new item. The update statement should be able to update the
quantity for Item_No (1235) to 0 unit under Item_ID IT1001, and 1 unit under Item_ID IT1003.

Any suggestion to the above stored procedure would be helpful, Thanks guys!

View Replies !   View Related
How To Input A DOS Variable Into A Stored Procedure
I am trying to Execute a Stored Procedure using Call OSQL from a .bat file, and passing a DOS variable to the Stored Procedure.

The DOS variable is established in the DOS command...

set SERVERJOB=JOBNAME

I have tried...

EXEC sp_procedure %SERVERJOB%

With this, I get an error...

sg 170, Level 15, State 1, Server ABCDEFGH, Line 20
Line 20: Incorrect syntax near '%'.

If I put the variable name in quotes on the EXEC statement above, the value used is the variable name, %SERVERJOB% itself rather than the value the variable was set to, which would be JOBNAME in the above example.

Any ideas??? Thanks!

View Replies !   View Related
Stored Procedure Input Variables
Hi,

I want to convert a SQL query as shown below into a stored procedure:


select name
from namelist
where town in ('A','B','D')


If I want to make the town as the input variable into the stored procedure, how should I declare the stored procedure? As far as I know, stored procedure could only handle individual values, and not a range of values.

Thanks.

View Replies !   View Related
Problem With Xml As Input To Stored Procedure
I am facing a problem while i pass xml as an intput to stored procedure.
The problem is that there are ceratin special characters which when used as a part of xml give error.Like the input which i give to my sp is :

Declare @XMLString  XML
Set @XMLString = N'<Company CompanyName = "Hilary Group & Sons"     Code = "HGS"    >
    </Company>'
Exec sproc_Insert_Company @XMLString

The error which i get on execution is: Msg 9421, Level 16, State 1, Line 2
XML parsing: line 2, character 34, illegal name character..

Its being generated because of the '&' being used in CompanyName.
 
In my sp i m using it as : .
 

Insert Into Company(

CompanyName,

Code,

 )

Output Inserted.CompanyId Into @tbl

-- TurnOver,

-- NetIncome,

-- YrOfIncorporation,

SELECT

CompanyName = ParamValues.Item.value( '@CompanyName' , 'varchar(101)'),

Code = ParamValues.Item.value( '@Code' , 'varchar(6)'),



FROM @XMLString.nodes('Company') AS ParamValues(Item)
 
Its not only this but there are other special characters which create problem like '@' and many more...

How to resolve it??
plzz do help at the earliest...
 

View Replies !   View Related
SQL Statement Length Limit
Does anyone know the SQL statement length limit?

Is there one or can I write a book of SQL and send it?

Tim Henning

View Replies !   View Related

Copyright © 2005-08 www.BigResource.com, All rights reserved