Hi, everyone, I want to know is there a way for me to set varchar to store more than 8000 characters? (I did checked from sql server books online and i know that the maximum storage for varchar, but i just want to know is there any exceptional way for me to store more than that).
Thanks for any reply.
aex
Newbie question:
Why bother specifing the length in varchar()?
Why not just specify the max and not worry about truncation?
Thanks,
Martin
Hi all!
I'm using this store proc to get a pivot table in SQL:
USE [m0851System]
GO
/****** Object: StoredProcedure [dbo].[sp_CrossTabIntoTable] Script Date: 01/25/2008 09:59:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_CrossTabIntoTable]
@select varchar(8000),
@sumfunc varchar(100),
@pivot varchar(100),
@table varchar(100)
-- AJOUTÉ PAR JULIEN BONNIER 17 juillet 2007
,@tbl_result varchar(100),
@fld_sufx varchar(10)
-- FIN JULIEN BONNIER
AS
--DECLARE @sql varchar(8000), @delim varchar(1)
DECLARE @sql varchar(8000), @delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
-- AJOUTÉ PAR JULIEN BONNIER 17 juillet 2007
-- MODIFIÉ PAR JULIEN BONNIER 25 janvier 2008 (ajout de la clause case)
IF LEFT(@tbl_result,1)='#'
BEGIN
IF EXISTS(SELECT name FROM tempdb.dbo.sysobjects WHERE type='U' AND name='' + @tbl_result + '')
EXEC ('DROP TABLE ' + @tbl_result + '')
END
ELSE
BEGIN
IF EXISTS(SELECT name FROM sysobjects WHERE type='U' AND name='' + @tbl_result + '')
EXEC ('DROP TABLE ' + @tbl_result + '')
END
-- FIN JULIEN BONNIER
EXEC ('SELECT ' + @pivot + ' AS pvt INTO ##pivot FROM ' + @table + ' WHERE 1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE '
+ @pivot + ' Is Not Null')
SELECT @sql='', @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )
SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )
WHEN 0 THEN '' ELSE '''' END
FROM tempdb.information_schema.columns
WHERE table_name='##pivot' AND column_name='pvt'
-- MODIFIÉ PAR JULIEN BONNIER 18 juillet 2007
--SELECT @sql=@sql + '''' + convert(varchar(100), pvt) + ''' = ' +
SELECT @sql=@sql + '''' + convert(varchar(100), pvt) + '' + @fld_sufx + ''' = ' +
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN '
+ @delim + convert(varchar(100), pvt) + @delim + ' THEN ' ) + ', ' FROM ##pivot
-- FIN JULIEN BONNIER
-- AJOUTÉ PAR JULIEN BONNIER 15 octobre 2007
ORDER BY pvt
-- FIN JULIEN BONNIER
DROP TABLE ##pivot
SELECT @sql=left(@sql, len(@sql)-1)
PRINT(LEN(@sql))
SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')
-- AJOUTÉ PAR JULIEN BONNIER 17 juillet 2007
SELECT @select=stuff(@select, charindex(' FROM ', @select), 6, ' INTO ' + @tbl_result + ' FROM ')
-- FIN JULIEN BONNIER
--EXEC (@select)
PRINT('YOYO'+@select)
RETURN
SET ANSI_WARNINGS ON
But now my @sql and @select that are varchar(8000) get bigger than 8000 for one of my reports... So my query fails ever times.
What can I do to fix it ?
Thanks in advance.
Or Tho
Hi,
I had a VARCHAR(MAX) parameter declared in my stored procedure and trying to concatenat single column from a table which has~500 rows into a string and keep in this variable, if i am not mistaken, i read that the VARCHAR(MAX) actually can hold up to 2GB of data, so it make me confuse why the variable which i declared as MAX size, can only hold up 8000 characters, any idea?
Regards,
Derek
Does anyone know why this statement would fail? I have created my own assembly which is on the server and when i run the mdx query call myassembly.mystoredproc() it returns data, but now when I use call myassembly.mystoredproc() it returns an error.
using an mdx query this works fine
call AsmTest.Asm.Analysis.TestClass.NameMe()
When using openquery i get an error
select * from openquery(NV, 'call AsmTest.Asm.Analysis.TestClass.NameMe()' )
OLE DB provider "MSOLAP.3" for linked server "NV" returned message "Prepare is not safe during execution of the NameMe stored procedure.".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "call AsmTest.Asm.Analysis.TestClass.NameMe()" for execution against OLE DB provider "MSOLAP.3" for linked server "NV".
linked server definition
USE master
GO
/* Add new linked server */
EXEC sp_addlinkedserver
@server='NV', -- local SQL name given to the linked server
@srvproduct='', -- not used
@provider='MSOLAP.3', -- OLE DB provider (the .2 means the SQL2K version)
@datasrc='nvsifwfp', -- analysis server name (machine name)
@catalog='ARTSDW' -- default catalog/database
I am trying to change a text field into a varchar 8000.
I get his error message when trying to convert.
Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot create a row of size 8317 which is greater than the allowable maximum of 8060.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.
Anyone know of a way to either truncate the text field or
to select only those that are over the 8000 character mark?
Please advise
Thanks
Susan
I was trying to build a string based on one field from one table in Stored Procedure. I know if I declare A VARCHAR variable for the string, it won’t allow to exceed 8000. If does, it will truncate the reminding parts of the fields. What might be the simple solution for that? Even though I know I might need to use several substrings but how?
Thanks!
J827
Declare @sql varchar 8000
Select @sql =@sql + convert(varchar(50),fieldName) from tableA where conditions …
hi all, I'm after a way to produce a single-lined output of a char/varchar string replicated over 8000 times.
basically I've been given a task to create a stored procedure which can accept any integer to replicate a string X times.
From what I've read the replicate() fn will covert to varchar of max 8000 bytes.
I've thought about creating a UDF to accept the varchar and int and run a loop to keep outputting but 'print' will pass an endline to the buffer which is no good for my loop.
Any help would be great on this. Thanks.
I have two string variables each has Varchar(8000)
Declare @VariableA varchar(8000)
Declare @VariableB varchar(8000)
How can I concatenate these two variables without converting them to varchar(max) ?
If try select @VariableA + @VariableB , I only got 7999 characters…
Hello,
We have migrated a sql server 2000 database to sql server 2005, this was done through scripts (table, stored procedures and data).
To access this database we are using an ASP.Net 2.0 application which uses the sqlhelper Aplication Block to connect to the database.
Everything works fine except one Stored Procedure which has an OUT varchar(8000) parameter.
We use the following .Net Code to execute the stored procedure this stored procedure:
aParams[2] = sSerDatos.GetParameter("@DominiosMenu", DbType.String, 8000);
aParams[2].Direction = ParameterDirection.Output;
sSerDatos.ExecuteNonQuery("VM_SDominiosMenu", aParams)
When we invoque the sqlcommand we get this sqlexception:
The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 3 ("@DominiosMenu"): Data type 0xE7 has an invalid data length or metadata length.
If we change the DbType.String Size to 4000 in the .Net code everything works, this same procedure work correctly in SQL Server 2000 with the same .Net code.
Any help would be appreciated.
Thanks,
Sam
I have this sql statement in a stored procedure
SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' + stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN ' + @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ', ' FROM ##pivot
in the statement, where @sql is defined as DECLARE @sql varchar(Max). the problem is that this statement produces results that are in excess of 8000 characters and the results are truncated. Is there anyway to avoid this? I know that it's not possible to user ntext/text as a local variable, and if i try to return the result as an ouput paramater, only the first result is returned.
my code is based off of this article http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables
Thanks for any suggestions.
Hi
I need to pass the where clause of my statement as an input parameter for a stored procedure. As this is built dynamically within the program (Borland C++), it could sometimes exceed the maximum 8000 bytes of a varchar. I thought of sending in two input paramenters and concatenating them into a memo in the sql, but cannot seem to be able to execute the memo variable (i.e. exec(memo) doesn't work). Is there any other options for passing in such a huge parameter.
Also, I do not think there is any other option than passing in the where clause: passing in seperate parameters and building the clause in sql would require over 300 parameters to be passed, and as the possible number of combination is indefinate, I cannot have a different Stored Procedure for each potential case.
Thank you
I have a table that contains a lot of demographic information. The data is usually small (<20 chars) but ocassionally needs to handle large values (250 chars). Right now its set up for varchar(max) and I don't think I want to do this.
How does varchar(max) store info differently from varchar(250)? Either way doesn't it have to hold the container information? So the word "Crackers" have 8 characters to it and information sayings its 8 characters long in both cases. This meaning its taking up same amount of space?
Also my concern will be running queries off of it, does a varchar(max) choke up queries because the fields cannot be properly analyzed? Is varchar(250) any better?
Should I just go with char(250) and watch my db size explode?
Usually the data that is 250 characters contain a lot of blank space that is removed using a SPROC so its not usually 250 characters for long.
Any insight to this would be appreciated.
I need to handle this conversion in SSIS and not on oracle.
The following expression is executed on a datatype of dt_str with a length of 8000.
SUBSTRING((DT_STR,8000,1252)Column_name,1,8000)
Records longer then 4000 bytes take an error path
The next expression with 4000 bytes works but there is truncation.
SUBSTRING((DT_STR,4000,1252)Column_name,1,4000)
Basically I need to know how to cast a text or ntext into a varchar or nvarchar using ssis but I need to capture the first 8000 byes without truncation.
is this possible?
Using SSIS Reading From oracle I can convert to a text or ntext field but I am having a hard time going directly to a varchar.
Does anyone know of a way to execute sql code from a dynamically builttext field?Before beginning, let me state that I know this db architecture isbuilt solely for frustration and I hope to make it better soon.Unfortunately, there is never a non-crucial time in which we can do anupgrade, so we are stuck for now.Point 1:There are multiple tables: students, courses, cross-referencestudent/courses, teachers, cross-reference teacher/courses, andothers.Point 2:Yearly archiving is done by appending a 2 digit year suffix to thetable and rebuilding the table structure for the current year. Thatis, each of the tables named above has an archive table for 1999,2000, 2001, etc. This leads to many tables and a big mess whenunioning them.Point 3:A history report is run by building a big query that unions each unionof tables (students, courses, etc) by year. This query has grown toobig for a varchar(8000) field. Actually, it's too big for 2 of them.Point 4:I don't want to write code to maintain any more varchar(8000) fieldsfor this query. It should be much more easily handled with atemporary table holding each bit of yearly archive data. I have builtthis and it works fine. I have also figured out how to pull the rowsfrom that table, concatenate them, and insert the resulting lump intoa text field in another table.Point 5:I haven't figured out how to grab the 5 or so records from that tableand execute them on their own. I figured I could grab them, put theminto a text field that's big enough to hold the whole query and thenselect and execute that text field. I've had no luck with that and Ihaven't had any luck finding any references that might help me withthis problem. I keep thinking of nesting execute() calls, but thatdoesn't work.I'm open to questions, potential solutions, hints about different wayto approach the problem, anything.Many thanks in advance,Rick Caborn
View 7 Replies View RelatedHi,
If I want to make a field of characters to be unlimited length(or maybe 2k for example), what datatype should I use?
Char, varchar and text have a max. limit of 255...
Will appreciate any suggestions.
Thanks,
Nishi
Hello all, I'm using SQL Server 2000 and have about 250 stored procedures that use an EMPLID parameter or variable of type varchar with a length of 4. I need to change the length to 10 instead and would like to do so without having to open every sp for editing. Is there a way to do this through SQL Server 2000? Does anyone have a script to do this? Any help would be appreciated.
View 1 Replies View RelatedHello All,
Can someone tell me how (in SQL) to convert an integer to a fixed length character filled with leading zeros. For example, I have an integer value of '125'. My user wants to see it displayed as '00000125'. How do I get the zeroes to fill in to a char(8) field when the length of the value differs, ie. '1', '125', '3452', etc.
Thanks in advance,
Terry
I'm wondering which way is the best way to store your numeric values.
It probably doesnt matter, b/c you can always convert back and forth...but i'm just wondering what the best practice is i guess...
thx
Hi,
Does any body know of any performance implications of using 'varchar' data type against 'char'?
I have some columns that are using 'char' data type, but the data in them is not fixed length. So, to gain some disk space I am planning to change the data type to 'varchar'. But, I am concerned if there will be any performance de-gradation or any other implications of doing this.
Regards
Chakri
Hi,
Is that true that using CHAR datatype improves the performance comparing to VARCHAR
thanks
indeed.
Why would you want to use char when you have varchar? Is there any performance hit using a varchar and the size you make that varchar?
Debate going at work.
Phil
I have recently inherited a database where all of the tables use varchar instead of chars for fields. Very , very few of these fields are involved in keys of even indices, but performance is an issue. I thought that I had read that varchars are worse for performance than chars when page splits may occur. Is this related to updates only, or does it matter?
Any help appreciated.
In relation to the code in this thread
http://forums.databasejournal.com/showthread.php?t=42622
My customer code field is Char (8) but accept an argument of Varchar (8) to my stored procedure. Don't ask me why!?!!
The customer code could be anything from 'A' to 'ZZZZZZZZ'.
Will this have any effect especially relating to overhead and retrieving incorrect data?
Hi,
This question may sound silly,but please comment.
Please tell me a situation where char should be used and not varchar.
Let us assume that we are dealing with non unicode characters.
Well, I find varchar is always smarter than char, so why char?
Thanks!!
Rudra
i would like to know if there is an overhead in using VARCHAR when you use to store it...
a colleague of mine claims that if the field is defined to be VARCHAR the system creates and additional column DOUBLE/DECIMAL with storage size of 17/18bytes.
such that if the size of the varchar field is less than 30 it is better to be defined as CHAR instead.
please help me out here... i think there's something wrong with his statement, but i need concrete proof to it... a link to page or pdf file would be very much appreciated.
Greetings,I have a question. I work on some SQL2k/ASP.NET apps at work. Mypredacessor, who created the databases/tables seemed to have liked touse 'char' for all text fields. Is there a reason why he would havedone this over using varchar? It's a minor annoyance to always have toRTRIM data and it makes directly making changes to the database moreannoying (with all the pointless trailing spaces)?I usually use char for fixed string lengths, like state abbreviationsor something, and varchar for strings of unknown length.Is it a performance issue? Our database doesn't do much traffic, forthe most part.
View 5 Replies View Relatedi have problem regarding the row length and varchar.
my problem is on every new row i have +6 more character on one of my field then the last record. and BOL says i can only have 8060 character per row.
What i can not use the full lenght of varchar(8000) on field.
Can anybody help?????????
What is the difference between the above data types in SQL? Which datatype should I use if I wanted both numbers and characters?
View 1 Replies View Relatedi changed my state table from char 20 to varchar 20 and it still fills it with empty space at the end of the statename
I tried entering it with the trim command still no good
i looked a the sql command made in vs and it says that the variables are varchar
is there something i am missing with varchar
does it only work properly if it is over 50 or does it not change completely when you change from char to varchar
Does using varchar or varchar(max) affect the system performance?
Should I use fixed size coloumns to increase speed?
I have one question to all SQL Guru's
I know the basic difference between CHAR and VARCHAR of CHAR taking all the space it is declared with and VARCHAR taking only amount of space used.
I want to know or link to any doucment which gives the difference between CHAR and VARCHAR more than what is mentioned above.
Please this is a urgent requirment.
Thanks in advance.
what'z the difference between char & varchar, like i am doing a cast function, which one should be used..
View 11 Replies View Related