[SQL Server Data Warehousing] Replace function ineffective with the string more than 90 chars

Hi Deva,

You are trying to replace  comma(,) with ',' 

I guess , you are trying like this

where geography in ('India','USA','Pakistan')

REPLACE function - http://msdn.microsoft.com/en-us/library/ms186862.aspx

"If string_expression is not of type varchar(max) or nvarchar(max),REPLACE truncates the return value at 8,000 bytes. To return values greater than 8,000 bytes, string_expression must be explicitly cast to a large-value data type "

So what happens if passed more than 90 characters truncation error or not getting replaced ??

I guess the problem will be because of single quotes not properly enclosed in your dynamic query

make sure you are adding quotes properly in  the begining and end of the values of @Geography

sathya --------- Please Mark as answered if my post solved your problem and Vote as helpful if my post was useful.

.

social.technet.microsoft.com/Forums

[SQL Server Data Warehousing] Replace function ineffective with the string more than 90 chars

Hi Deva,

You are trying to replace  comma(,) with ',' 

I guess , you are trying like this

where geography in ('India','USA','Pakistan')

REPLACE function - http://msdn.microsoft.com/en-us/library/ms186862.aspx

"If string_expression is not of type varchar(max) or nvarchar(max),REPLACE truncates the return value at 8,000 bytes. To return values greater than 8,000 bytes, string_expression must be explicitly cast to a large-value data type "

So what happens if passed more than 90 characters truncation error or not getting replaced ??

I guess the problem will be because of single quotes not properly enclosed in your dynamic query

make sure you are adding quotes properly in  the begining and end of the values of @Geography

sathya --------- Please Mark as answered if my post solved your problem and Vote as helpful if my post was useful.

.

social.technet.microsoft.com/Forums

[SQL Server Data Warehousing]Replace function ineffective with the string more than 90 chars

Hi,

I am working on T-SQL stored procedure. In my code i use REPLACE function. It works fine until i pass the string of length more than 90 characters.

where geography in (''' + REPLACE(@Geography,',',''',''') + '''' + ')'

The geography value is passed from SSRS report, where i send the parameter with the JOIN function using comma seperator. In this stored procedure i frame the multivalued parameter using the replace function. I have tried changing the parameter resolution to nvarchar(500) no changes notified

The problem is that it is working ABSOLUTELY fine until i pass  @Geography with more than 90 characters.

---

Bunch of thanks ~ Deva ~ 

.

social.technet.microsoft.com/Forums

Replace function ineffective with the string more than 90 chars

Hi,

I am working on T-SQL stored procedure. In my code i use REPLACE function. It works fine until i pass the string of length more than 90 characters.

where geography in (''' + REPLACE(@Geography,',',''',''') + '''' + ')'

The geography value is passed from SSRS report, where i send the parameter with the JOIN function using comma seperator. In this stored procedure i frame the multivalued parameter using the replace function. I have tried changing the parameter resolution to nvarchar(500) no changes notified

The problem is that it is working ABSOLUTELY fine until i pass  @Geography with more than 90 characters.

---

Bunch of thanks ~ Deva ~ 

.

social.technet.microsoft.com/Forums