T-SQL (SS2K8) :: Varchar (8000) - Concatenate Two Variables

Apr 16, 2015

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…

View 3 Replies


How To Concatenate Stored Procedure Varchar Variables

Oct 11, 2007

Hi , I am trying to write a stored procedure (i have given it below).i am basically trying to join 4 strings into one based on some if conditions.But the result gives only the intially assaigned string and rest are not getting concatenated.i have provided teh stored procedure below along with the inputs and result i got.Can anyone Please help me to acheive this set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER PROCEDURE [dbo].[TestSearch] @distributorId int, @locationId int, @orderTypeId int, @fromDate Datetime = NULL, @toDate Datetime = NULL, @OrderStatus varchar(500) = NULL, @TaxAuthority varchar(500) = NULL, @TaxStampType varchar(500) = NULLASBEGINDeclare @SQL varchar(8000)Set @SQL ='select * from Orders AS a INNER JOINOrderLines AS b ON a.Id = b.FkOrder INNER JOINTaxStampTypes AS c ON b.FkTaxStampType = c.Id where ''' +CONVERT(VARCHAR(8),@fromDate ,1) + ''' <= CONVERT(VARCHAR(8),a.OrderDate ,1) and ''' +CONVERT(VARCHAR(8),@toDate ,1) + '''>= CONVERT(VARCHAR(8), a.OrderDate,1)and a.fkordertype = '+ convert(varchar(1),@orderTypeId) +'anda.FkDistributor = ('+ convert(varchar(50), @distributorId)+',a.FkDistributor)anda.FkLocation in ('+convert(varchar(10),@locationId)+',a.FkLocation)and'IF(@OrderStatus != null)Beginset @SQL= @SQL + 'a.FkOrderState in ('+ @OrderStatus +') and' EndIF(@TaxAuthority!= null)Beginset @SQL = @SQL +'a.FkTaxAuthority in ('+@TaxAuthority+') and'EndIF(@TaxStampType!= null)Beginset @SQL = @SQL + 'c.id in ('+ @TaxStampType+ ')and'End--Execute (@SQL1)select (@SQL);ENDHere is the Input Given to stored Procedure for executing:DECLARE @return_value intEXEC @return_value = [dbo].[TestSearch] @distributorId = 1002, @locationId = 3, @orderTypeId = 1, @fromDate = N'06/10/07', @toDate = N'10/10/07', @OrderStatus = N'2', @TaxAuthority = N'1000', @TaxStampType = N'1000'SELECT 'Return Value' = @return_valueHere Is The Output i get when I execute the stored procedure: select * from Orders AS a INNER JOIN OrderLines AS b ON a.Id = b.FkOrder INNER JOIN TaxStampTypes AS c ON b.FkTaxStampType = c.Id where '06/10/07' <= CONVERT(VARCHAR(8),a.OrderDate ,1) and '10/10/07'>= CONVERT(VARCHAR(8), a.OrderDate,1) and a.fkordertype = 1 and a.FkDistributor = (1002,a.FkDistributor) and a.FkLocation in (3,a.FkLocation) and--Ajay

View 9 Replies View Related

T-SQL (SS2K8) :: Concatenate Image With Varchar?

Feb 24, 2015

I want to concatenate image column with varchar column in stuff function.

View 8 Replies View Related

T-SQL (SS2K8) :: Group Sum LEN On VARCHAR Column And Concatenate

Jul 3, 2015

I've got a fairly standard query that does a group by a type column, and then sums the lengths of a VARCHAR column. I'd like to add into that a concatenated version of the string always concatenating in primary key order. Is that possible?

View 1 Replies View Related

Varchar(8000) Is Not Enough... What Can I Do?

Jan 25, 2008

Hi all!

I'm using this store proc to get a pivot table in SQL:

USE [m0851System]
/****** Object: StoredProcedure [dbo].[sp_CrossTabIntoTable] Script Date: 01/25/2008 09:59:37 ******/
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)

--DECLARE @sql varchar(8000), @delim varchar(1)
DECLARE @sql varchar(8000), @delim varchar(1)

-- AJOUTÉ PAR JULIEN BONNIER 17 juillet 2007
-- MODIFIÉ PAR JULIEN BONNIER 25 janvier 2008 (ajout de la clause case)
IF LEFT(@tbl_result,1)='#'
IF EXISTS(SELECT name FROM tempdb.dbo.sysobjects WHERE type='U' AND name='' + @tbl_result + '')
EXEC ('DROP TABLE ' + @tbl_result + '')
IF EXISTS(SELECT name FROM sysobjects WHERE type='U' AND name='' + @tbl_result + '')
EXEC ('DROP TABLE ' + @tbl_result + '')

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) )
FROM tempdb.information_schema.columns
WHERE table_name='##pivot' AND column_name='pvt'

--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
-- AJOUTÉ PAR JULIEN BONNIER 15 octobre 2007

DROP TABLE ##pivot

SELECT @sql=left(@sql, len(@sql)-1)
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 ')

--EXEC (@select)

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

View 14 Replies View Related

VARCHAR(MAX) Only Contains 8000 Characters?

Nov 25, 2007


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?


View 10 Replies View Related

Can&#39;t Fit Text Field Into Varchar 8000

Aug 16, 2000

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

View 2 Replies View Related

Varchar Exceed 8000 Issue

Jun 14, 2004

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?


Declare @sql varchar 8000

Select @sql =@sql + convert(varchar(50),fieldName) from tableA where conditions …

View 8 Replies View Related

Can Varchar Length Be Set To More Than 8000 Char?

May 22, 2006

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.


View 4 Replies View Related

Varchar Output Greater Than 8000 Chars

May 20, 2007

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.

View 1 Replies View Related

Error In SQL-Server 2005 With Varchar 8000

Jun 5, 2006


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.

View 10 Replies View Related

Stored Procedure Varchar (8000) Limitation.

Mar 12, 2008

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.

View 4 Replies View Related

Input Parameter Greater Than Varchar(8000)

May 30, 2006


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

View 3 Replies View Related

How To You Convert An Oracle Long Field To Varchar 8000

Jan 17, 2007

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.


Records longer then 4000 bytes take an error path

The next expression with 4000 bytes works but there is truncation.


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.

View 1 Replies View Related

Executing Sql Code From Text Field Or From Multiple Varchar(8000) Rows In A Table

Jul 20, 2005

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 Related

Concatenate Integer And Varchar

Feb 29, 2008

I have a table like this:

ProjID | Description
1 | First
2 | Second
3 | Third

I want to write a SELECT statement that will output in this format:
1 - First
2 - Second

I've tried these two statements:

SELECT (ProjID + ' - ' + Description) AS Projects
FROM tbl_Projects
//Error: Conversion failed when converting the varchar value 'First' to data type int.


SELECT CONCAT(ProjID, CONCAT(' - ', Description)) AS Projects
FROM tbl_Projects
//Error: The data types int and varchar are incompatible in the concat operator.

Is there any way to concatenate an int value with a varchar value?

View 3 Replies View Related

Concatenate Text And Varchar Fields

May 29, 2008

I am trying to add a carriage return to the end of a text field through a script. This is what I'm trying:

UPDATE Table_Name SET Column_TEXT = Column_TEXT) + '

' WHERE Column_TEXT = 'Some text'

I also tried

UPDATE Table_Name SET Column_TEXT = Column_TEXT) + '<cr>' WHERE Column_TEXT = 'Some text'

But I keep getting the error

The data types text and varchar are incompatible in the equal to operator.


Thanks in advance

View 3 Replies View Related

T-SQL (SS2K8) :: Concatenate With Leading Zeros

Sep 9, 2013

I am creating a view which involved concatenation of 2 int columns.

The data in the columns look like

Column 1 Column 2
1234 1
12345 11

I am trying to get the following output


So the first column should have zeros padded to the front to make 6 numbers, the second column should be 3 numbers long with zeros in front. So when added together it is 9 numbers long.

View 9 Replies View Related

T-SQL (SS2K8) :: Using For XML Path To Concatenate In A Sub-Query

Feb 17, 2015

how to properly use the "For XML Path" code to concatenate multiple values into one record. What I have is a procedure I've written for a SSRS report that summarizes drive information: date, account, recruiter, times.But each drive can have multiple shifts, and each shift will have it's own vehicle assigned. So a drive may have 1 vehicle assigned to it or it may have 5. If there are 5 shifts/5 vehicles, I don't want to the report to display 5 rows of data for the one drive. So I'm trying to get all of the vehicles to be placed into one field for the report.My initial sub-query will not work because it is possible that it will contain more than one item (vehicle):

DM.DriveID [DriveID],
DM.FromDateTime [FromDateTime],
DSD.ShiftID [ShiftID],
Case When DM.OpenToPublic = 1 Then 'Yes' Else 'No' End As [OpenToPublic],
Case When DM.OwnerType=0 Then 'Mobile' Else 'Fixed' End As [OwnerType],
Case When DM.OwnerType = 0 Then Acct.Name Else CD.DescLong End As [OwnerName],


SQL Server Newbie or here either. I'm a newbie as well.

View 8 Replies View Related

T-SQL (SS2K8) :: Concatenate Specific Values From A Column

May 20, 2014

Give this data set;

declare @BO table (Col1 varchar(50), Col2 varchar(50))
insert into @BO values

[Code] ....

select * from @BO

The 2 digit number that appears on line 1, 7 and 13 (only in this example) i need to have added to the begin of each value below it until the next 2 digit number is encountered. The desired result set would look like:

declare @BOD table (Col1 varchar(50), Col2 varchar(50), col3 varchar(50))
insert into @BOD values

[Code] ....

View 7 Replies View Related

T-SQL (SS2K8) :: How To Concatenate / Convert Date And Time Into Datetime

Aug 3, 2015

We have 2 columns:

StartDate - data type Date
StartTime - data type Time

I need to combine them into a DateTime data type. For now, I convert each of them into varchar, insert space in between, and convert to DateTime, like this:

convert(datetime, convert(varchar(10), EndDate , 101) + ' ' + CONVERT(varchar(20), EndTime,24))

Is there a better, more elegant way to do this?

View 9 Replies View Related

Varchar Variables

Sep 5, 1999


Declare @ii varcahr(2)
Select @ii = 2

when I execute the above SQl statement, it returns error in SQL 6.5.
Doesn't seem to be same in SQL7.0

Someone, Pls help


View 1 Replies View Related

How To Concatenate Variables For RAW Output Filename I.e.@path + @filename?

Sep 7, 2007

Doesn't appear you can do this.

Am I wrong?

Please tell me I am.

View 11 Replies View Related

T-SQL (SS2K8) :: Error When Attempting To Set Variables Value

May 28, 2014

I am getting the error 'Incorrect syntax near the keyword set' with the following code snippet -

declare @dteTo Date
set @dteTo = CONVERT(date, GETDATE())

My intention is to set the variable @dteTo to the current date (no time component). If I run a SELECT CONVERT(date, GETDATE()) command I do get what I want, it is just assigning the value to the variable that isn't working as intended.

View 8 Replies View Related

T-SQL (SS2K8) :: Changing Varchar To Datetime?

Oct 2, 2014

I can't get a varchar to datetime conversion to work. The varchar data is in the form yyyymmddhhmi

I tried convert(datetime, '200508310926')

also tried cast('200508310926' as datetime) both have error "Conversion failed when converting date and/or time from character string"

Is there a format code that needs to be there? If so, I can't figure out what it should be.

I eventually need to convert these to dates and compare them to getdate, example:

...where convert(datetime, dtfield) >= getdate()-1

View 7 Replies View Related

T-SQL (SS2K8) :: Compare Two Varchar For Similarities?

Oct 28, 2014

I am asked to compare the address fields (three columns of nvarchar(100) ) of a customer database (around 10,000 records) and find any duplicates. If it is a character by character match, I could have just GROUPed and get the result.

But, I am expected to produce a list with similar addresses which the guys who entered may have use slightly different spelling or more or less characters, or a "." here and there.

is there any way to create a query for this?

View 5 Replies View Related

T-SQL (SS2K8) :: How To Return Max From A Varchar Column

Jan 21, 2015

I need to return the max value from a fieldwhich contains a three part numeric, stored as a varchar. For example


These represent processes, and sub tasks. So I want to return the highest process and its highest task and sub task.

View 9 Replies View Related

T-SQL (SS2K8) :: Convert Varchar To Float

Feb 11, 2015

I have the following CASE statement:

SELECT CASE WHEN [TBL_whole_rock_geochem].au = 0 THEN '<' + [TBL_ActLab_codes].[Au] ELSE [TBL_whole_rock_geochem].[Au] END AS [Au det]

It errors on the highlighted code.

Error converting data type nvarchar to float

the code should return :


I have tried '<' + ISNUMERIC([TBL_ActLab_codes].[Au]) but received this error

Conversion failed when converting the varchar value '<' to data type int.

How I can concatenate the < with a float value?

View 5 Replies View Related

T-SQL (SS2K8) :: Distinct On Varchar Column

May 21, 2015

The below TSQL query takes about 19 minutes to run

SELECT count(distinct SessionID) ) FROM SessionTracker

Sessionid is a varchar(138)

Through SQL profiler - I notice implicit conversion taking place when running distinct option.

Select count(sessionid) from sessiontracker

- runs in milliseconds.

View 1 Replies View Related

T-SQL (SS2K8) :: Conversion Failed When Converting Varchar To Int

Mar 6, 2015

I have a statement that I'm having trouble converting:

select * from emails join InternalContacts On emails.EmailAddress = InternalContacts.EmailID
When I run this I get -

Conversion failed when converting the varchar value 'credentialing@floridaeyeclinic.com' to data type int.

What is the correct syntax to convert?

View 2 Replies View Related

T-SQL (SS2K8) :: Copy Varchar (max) Fields Slow

Mar 13, 2015

I'm archiving some data. In a 2 step process.

1. Copy old data from each table in LiveDB to same table in ArchiveDB.
2. Delete the data from each table in LiveDB which is in ArchiveDB

Both DBs SIMPLE recovery mode.

Each table has a clustered PK on a single int value. In both DBs

The tables with varchar(max) columns are taking a v.long time to copy over.

IS there anything I can change in the ArchiveDB to make it run faster.

It is the insert that is taking the time. I've tried dropping the clustered PKs in ArchiveDB tables and then rebuilding afterwards but it has not made any difference. After all I am adding data to the ArchiveDB in clustered index order, so wouldn't have expected it to.

How I can change the Archive DB but cannot touch the schema/settings of Live DB.

View 9 Replies View Related

T-SQL (SS2K8) :: Conversion From Varchar To Strong Data Types

Mar 21, 2013

There are a few databases I work with that have been designed where varchar columns are used to store what actually displays on the front end as Ints, Decimals, Varchars, Datetimes, checkboxes.

I often have to write integrations with these databases bringing data in and prefer to validate the data whilst loading from the staging tables.

I have seen allsorts of values being passed into the staging tables that will load into the target database because the columns are all varchars but the values don't display on the front end because the app actively filters bad values out.

What I would like to do is for my validation scripts to warn up front of potentially invalid datatypes. My problem is that forexample the ISNUMERIC() function return 1 for the value ',1234' but a CONVERT(NUMERIC, ',1234') or CAST(',1234' AS NUMERIC) will fail with a "Error converting data type varchar to numeric).

I've been trying to locate a set of reliable datatype testing functions that will reliably determine if a varchar can be converted to a given data type or not.

View 6 Replies View Related

T-SQL (SS2K8) :: Using Not Like To Filter Out Non-numeric Values From Varchar Field

Mar 26, 2014

I am putting a SELECT statement together where I need to evaluate a results field, to determine how the color indicator will show on a SSRS report. I am running into a problem when I try to filter out any non-numeric values from a varchar field, using a nested CASE statement.

For example, this results field may contain values of '<1', '>=1', '1', '100', '500', '5000', etc. For one type of test, I need a value of 500 or less to be shown as a green indicator in a report, and any value over that would be flagged as a red. Another test might only allow a value of 10 or less before being flagged with a red.

This is why I setup a CASE statement for an IndicatorValue that will pass over to the report to determine the indicator color. Using CASE statements for this is easier to work with, and less taxing on the report server, if done in SQL Server instead of nested SSRS expressions, especially since a variety of tests have different result values that would be flagged as green or red.

I have a separate nested CASE statement that will handle any of the values that contain ">" or "<", so I am using the following to filter those out, and then convert it to an int value, to determine what the indicator value should be. Here is the line of the script that is erring out"

case when (RESULT not like '%<%') or (RESULT not like '%>%') then
CASE WHEN (CONVERT(int, RESULT) between 0 and 500) THEN '2'
ELSE '0'

The message I am getting is: Conversion failed when converting the varchar value '<1' to data type int.

I thought a "not like" statement would not include those values for converting to an int, but that does not seem to be working correctly. I did also try moving the not to show as "not RESULT like", and that did not change the message.

How I can filter out non-numeric values before converting the rest of the varchar field (RESULT) to int, so that it is only converting actual numbers?

View 6 Replies View Related

Copyrights 2005-15 www.BigResource.com, All rights reserved