Showing posts with label character. Show all posts
Showing posts with label character. Show all posts

Wednesday, March 28, 2012

HELP! Strange behavior using t-sql REPLACE function

I am seeing very odd behavior with the t-sql REPLACE funtion.

I am trying to wipe out the 'T' character (unicode value 222) with a space character (' ' unicode value 32) and it appears to be replacing occurrences of 'th' together as well. Here are some selects to show this behavior:

select nchar(unicode('T'))
select nchar(unicode(' '))
select unicode('T')
select unicode(' ')
select replace('Urban & Ethnic', nchar(unicode('T')), nchar(unicode(' ')))
select replace('Urban T Ethnic', nchar(unicode('T')), nchar(unicode(' ')))
select replace('Urban & Ethnic', nchar(222), nchar(32))
select replace('Urban T Ethnic', nchar(222), nchar(32))
select replace('Urban & Ethnic', 'T', ' ')
select replace('Urban T Ethnic', 'T', ' ')

NOTICE that the last two statements:

select replace('Urban & Ethnic', 'T', ' ') - RESULT 'Urban & Ethnic'
select replace('Urban T Ethnic', 'T', ' ') - RESULT 'Urban Ethnic'

...work properly, while the other ones dont:

select replace('Urban & Ethnic', nchar(unicode('T')), nchar(unicode(' '))) - RESULT 'Urban & E nic'
select replace('Urban T Ethnic', nchar(unicode('T')), nchar(unicode(' '))) - RESULT 'Urban E nic'
select replace('Urban & Ethnic', nchar(222), nchar(32)) - RESULT 'Urban & E nic'
select replace('Urban T Ethnic', nchar(222), nchar(32)) - RESULT 'Urban E nic'

This function ONLY seems to work with the character representation, any attempt to convert an integer value to a character representation fails miserably...

Please advise, and thanks for your help in advance.

It seems that the collation the active database uses treats "th" and "T" as the same character. To fix this, change the database collation to something more appropriate, or use tricks like the following in your queries:

select replace(N'Urban T Ethnic' COLLATE Latin1_General_BIN, N'T', N' ')

By forcing the collation to BIN (binary), a binary comparison is done, which doesn't do fancy character-equivilancy stuff.

-Ryan

|||

Thanks!

sp_helpsort reveals the default collation = Latin1-General, blah, blah, blah, etc...

I even tried to convert EVERYTHING to binary var types within the query and it STILL came up with the wrong result.

Thanks again for your help. I didn't even consider changing the COLLATION type in the query itself...

sql

Wednesday, March 7, 2012

HELP! - Hitting 260 character limit on parameters in URL - how to avoid?

Hi,
Got some reports with a lot of parameters to deal with, a custom web front
end and using the ReportViewer component to render the reports. A typical
URL generated by the app is:
http://localhost/reportserver?/Reports/My%20Report&rs:Command=Render&rs:Format=HTML4.0&rc:Parameters=false&DateFrom=12/08/2004%2013:59:44&DateTo=12/08/2004%2013:59:44&ClientRefFrom=_All_&ClientRefTo=_All_&SurnameFrom=_All_&SurnameTo=_All_&AreaFrom=_All_&AreaTo=_All_&RegionFrom=_All_&RegionTo=_All_
Problem is i'm getting the following error message displayed:
Reporting Services Error
----
--
a.. The path of the item '/Reports/My Report,' is not valid. The full path
must be less than 260 characters long, must start with slash; other
restrictions apply. Check the documentation for complete set of
restrictions. (rsInvalidItemPath) Get Online Help
----
--
How do I get around this? I understand I need to convert the params in some
way & pass them into the report, cannot find out howto atm.
Any help appreciated
SiDo you have a Folder called Reports in the home directory? Is the 'My
Report' report located in that folder? If you navigate to
//localhost/reportserver you can then click on the folder and the report and
see what the url should look like.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Si" <no@.spam.thanks> wrote in message
news:uhscKCHgEHA.636@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Got some reports with a lot of parameters to deal with, a custom web front
> end and using the ReportViewer component to render the reports. A typical
> URL generated by the app is:
>
http://localhost/reportserver?/Reports/My%20Report&rs:Command=Render&rs:Format=HTML4.0&rc:Parameters=false&DateFrom=12/08/2004%2013:59:44&DateTo=12/08/2004%2013:59:44&ClientRefFrom=_All_&ClientRefTo=_All_&SurnameFrom=_All_&SurnameTo=_All_&AreaFrom=_All_&AreaTo=_All_&RegionFrom=_All_&RegionTo=_All_
> Problem is i'm getting the following error message displayed:
> Reporting Services Error
> ----
--
> --
> a.. The path of the item '/Reports/My Report,' is not valid. The full
path
> must be less than 260 characters long, must start with slash; other
> restrictions apply. Check the documentation for complete set of
> restrictions. (rsInvalidItemPath) Get Online Help
> ----
--
> --
>
> How do I get around this? I understand I need to convert the params in
some
> way & pass them into the report, cannot find out howto atm.
> Any help appreciated
> Si
>|||Hi Daniel,
Yup the reports work fine if I have less params to play with i.e. if I
just use DateFrom & DateTo it's ok, just when I use all the parameters
in the report I get this message.
Just seems to be the length of the params that causes the problem,
seems like I need to use SetReportParameters to programatically pass
the params into the report but as i'm using the ReportViewer component
(programmatic access), there is no Render method for me to use to pass
these params into!
Si
On Thu, 12 Aug 2004 10:14:55 -0700, "Daniel Reib [MSFT]"
<danreib@.online.microsoft.com> wrote:
>Do you have a Folder called Reports in the home directory? Is the 'My
>Report' report located in that folder? If you navigate to
>//localhost/reportserver you can then click on the folder and the report and
>see what the url should look like.
>--
>-Daniel
>This posting is provided "AS IS" with no warranties, and confers no rights.
>
>"Si" <no@.spam.thanks> wrote in message
>news:uhscKCHgEHA.636@.TK2MSFTNGP12.phx.gbl...
>> Hi,
>> Got some reports with a lot of parameters to deal with, a custom web front
>> end and using the ReportViewer component to render the reports. A typical
>> URL generated by the app is:
>>
>http://localhost/reportserver?/Reports/My%20Report&rs:Command=Render&rs:Format=HTML4.0&rc:Parameters=false&DateFrom=12/08/2004%2013:59:44&DateTo=12/08/2004%2013:59:44&ClientRefFrom=_All_&ClientRefTo=_All_&SurnameFrom=_All_&SurnameTo=_All_&AreaFrom=_All_&AreaTo=_All_&RegionFrom=_All_&RegionTo=_All_
>> Problem is i'm getting the following error message displayed:
>> Reporting Services Error
>> ----
>--
>> --
>> a.. The path of the item '/Reports/My Report,' is not valid. The full
>path
>> must be less than 260 characters long, must start with slash; other
>> restrictions apply. Check the documentation for complete set of
>> restrictions. (rsInvalidItemPath) Get Online Help
>> ----|||Found the problem,
You were correct in the URL was suspect but it was with the parameters
rather than the path to the report. I noticed that there was an extra
ampersand at the end of the paramstring and also the date format
included a timestamp. Removing those two entries seems to have fixed
the problem
Thanks for the heads up. At least I now know i'm not going mad!
Si
On Fri, 13 Aug 2004 08:15:04 +0100, Si <no@.spam.thanks> wrote:
>Hi Daniel,
>Yup the reports work fine if I have less params to play with i.e. if I
>just use DateFrom & DateTo it's ok, just when I use all the parameters
>in the report I get this message.
>Just seems to be the length of the params that causes the problem,
>seems like I need to use SetReportParameters to programatically pass
>the params into the report but as i'm using the ReportViewer component
>(programmatic access), there is no Render method for me to use to pass
>these params into!
>Si
>On Thu, 12 Aug 2004 10:14:55 -0700, "Daniel Reib [MSFT]"
><danreib@.online.microsoft.com> wrote:
>>Do you have a Folder called Reports in the home directory? Is the 'My
>>Report' report located in that folder? If you navigate to
>>//localhost/reportserver you can then click on the folder and the report and
>>see what the url should look like.
>>--
>>-Daniel
>>This posting is provided "AS IS" with no warranties, and confers no rights.
>>
>>"Si" <no@.spam.thanks> wrote in message
>>news:uhscKCHgEHA.636@.TK2MSFTNGP12.phx.gbl...
>> Hi,
>> Got some reports with a lot of parameters to deal with, a custom web front
>> end and using the ReportViewer component to render the reports. A typical
>> URL generated by the app is:
>>
>>http://localhost/reportserver?/Reports/My%20Report&rs:Command=Render&rs:Format=HTML4.0&rc:Parameters=false&DateFrom=12/08/2004%2013:59:44&DateTo=12/08/2004%2013:59:44&ClientRefFrom=_All_&ClientRefTo=_All_&SurnameFrom=_All_&SurnameTo=_All_&AreaFrom=_All_&AreaTo=_All_&RegionFrom=_All_&RegionTo=_All_
>> Problem is i'm getting the following error message displayed:
>> Reporting Services Error
>> ----
>>--
>> --
>> a.. The path of the item '/Reports/My Report,' is not valid. The full
>>path
>> must be less than 260 characters long, must start with slash; other
>> restrictions apply. Check the documentation for complete set of
>> restrictions. (rsInvalidItemPath) Get Online Help
>> ----