Excludes 'Z' while giving LIKE '[A-Z]%' but works fine for all other combinations

Hai Friends!

I have a peculiar problem while using LIKE pattern match with SQL Server 2005. My requirement is to list names starting from a range of Characters (anything from a to z). If I want to list Names starting from A to Z I am giving the LIKE pattern as LIKE '[A-Z]%' but the result set contains names starting with 'A' alone. If I give '[B-Z]%' the 'B' Alone is included in the result set. For all remaining combinations (ex: '[A-Y]%') it gives correct results.

If I give '%' or 'Z%' it gives all starting with Z correctly. If I include the Z with any other alphabet it takes only the starting alphabet and excludes all remaining letters.

This problem seems to be too strange and couldn't understand why it gives wrong results!!

Any solutions? Thanks in advance

Visualcpp

Please list your specific syntax; the following works fine:

select aName
from ( select 'Alphabet' as aName
union all
select 'Zymurgy'
) a
where aName like '[A-Z]%'

-- aName
-- --
-- Alphabet
-- Zymurgy

exclude names that have numbers

Eg:
Create table test
(col1 char(20))
insert test values ('test')
insert test values ('test1')
insert test values ('te1st')
insert test values ('tes')
Basically I want the output to return those values that do not contain a
number in the entire value..
So the output from above should only include
test
tes
How can I do so ?
ThanksTry
select * from test
where col1 not like '%[0-9]%'
Ben Nevarez, MCDBA, OCP
Database Administrator
"Hassan" wrote:

> Eg:
> Create table test
> (col1 char(20))
> insert test values ('test')
> insert test values ('test1')
> insert test values ('te1st')
> insert test values ('tes')
> Basically I want the output to return those values that do not contain a
> number in the entire value..
> So the output from above should only include
> test
> tes
> How can I do so ?
> Thanks
>
>

Excel with ADO problem

Hello,

I connect to an Excel file with ADO and I get the sheet names and after that I read the table. if the column is all integers or strings there's no problem but if the column is composed of integers and strings then only the strings are returned,others are returned empty.

What's the reason?

Thanks

Hi,

did you try this here:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q194124

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Excel renderer in RS2005-Sheet names

The Reporting group in our company uses SRS 2005 to generate most reports for
the Company. Many of these reports are distributed automatically to our
clients. An increasing number of these reports are distributed as Excel
spreadsheets. These Excel reports generally contain multiple worksheets.
For customer convenience, these worksheets should be named. The portion of
Report Services that writes the Excel files, is apparently not capable of
naming these worksheets with user specified names when there are multiple
worksheets. However when only one worksheet is present, it is named with the
title of the report. Is there a way to name the worksheets dynamically as the
report is generated? If not, is it possible to write a custom renderer to do
so, or to extend the excel renderer? I appreciate any code examples.
Thanks in advance.You might want to look into Software Artisan's Excel writer for Reporting
Services. It gives you more control over the Excel rendering.
http://officewriter.softartisans.com/officewriter-250.aspx
--
Andy Potter
blog : http://sqlreportingservices.spaces.live.com
info@.(NOSPAM)lakeclaireenterprises.com
"Saritha" <Saritha@.discussions.microsoft.com> wrote in message
news:1BD099C8-6EA3-4D9B-999D-5574F9A151E6@.microsoft.com...
> The Reporting group in our company uses SRS 2005 to generate most reports
> for
> the Company. Many of these reports are distributed automatically to our
> clients. An increasing number of these reports are distributed as Excel
> spreadsheets. These Excel reports generally contain multiple worksheets.
> For customer convenience, these worksheets should be named. The portion of
> Report Services that writes the Excel files, is apparently not capable of
> naming these worksheets with user specified names when there are multiple
> worksheets. However when only one worksheet is present, it is named with
> the
> title of the report. Is there a way to name the worksheets dynamically as
> the
> report is generated? If not, is it possible to write a custom renderer to
> do
> so, or to extend the excel renderer? I appreciate any code examples.
> Thanks in advance.
>

Excel import and german umlaute

I'm still having some issues with report names using "german umlaute".
Attached two small dataless sample reports, originally named "Ärger.rdl" and
"Übersicht.rdl". Both consists of a textbox linking to each other. Tis works
fine and is displayed correctly in HTML. Exporting one report to Excel and
trying to follow the link fails.
Should I avoid german umlaute in report names? I would consider to do so if
we would have 1982. But not today...:)
r.
Ärger.rdl
--
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<rd:GridSpacing>0.25cm</rd:GridSpacing>
<RightMargin>2.5cm</RightMargin>
<Body>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextDecoration>Underline</TextDecoration>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>textbox1</rd:DefaultName>
<Width>6.78968cm</Width>
<Action>
<Drillthrough>
<ReportName>Ãobersicht</ReportName>
</Drillthrough>
</Action>
<CanGrow>true</CanGrow>
<Value>Here is Ã"rger.rdl. Back to Ãobersicht.rdl</Value>
</Textbox>
</ReportItems>
<Style />
<Height>0.63492cm</Height>
<ColumnSpacing>1cm</ColumnSpacing>
</Body>
<TopMargin>2.5cm</TopMargin>
<Width>16cm</Width>
<LeftMargin>2.5cm</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<PageHeight>29.7cm</PageHeight>
<rd:DrawGrid>true</rd:DrawGrid>
<PageWidth>21cm</PageWidth>
<rd:ReportID>52737cce-c0a6-4c5e-b23b-eaeca22bd186</rd:ReportID>
<BottomMargin>2.5cm</BottomMargin>
<Language>de-DE</Language>
</Report>
Übersicht.rdl
--
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<rd:GridSpacing>0.25cm</rd:GridSpacing>
<RightMargin>2.5cm</RightMargin>
<Body>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextDecoration>Underline</TextDecoration>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>textbox1</rd:DefaultName>
<Height>0.63492cm</Height>
<Width>7.75cm</Width>
<Action>
<Drillthrough>
<ReportName>Ã"rger</ReportName>
</Drillthrough>
</Action>
<CanGrow>true</CanGrow>
<Value>This way to Ã"rger.rdl</Value>
</Textbox>
</ReportItems>
<Style />
<Height>0.75cm</Height>
<ColumnSpacing>1cm</ColumnSpacing>
</Body>
<TopMargin>2.5cm</TopMargin>
<Width>8cm</Width>
<LeftMargin>2.5cm</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<PageHeight>29.7cm</PageHeight>
<rd:DrawGrid>true</rd:DrawGrid>
<PageWidth>21cm</PageWidth>
<rd:ReportID>b4667abb-f9e5-490e-b3ff-8cca1050a2d1</rd:ReportID>
<BottomMargin>2.5cm</BottomMargin>
<Language>de-DE</Language>
</Report>Yes, we can reproduce the drillthrough issue and we will look into fixing
it. Unfortunately, it's too late for SP1.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Roland" <nomail@.spamprotect.com> wrote in message
news:OP%23skQWWEHA.2816@.TK2MSFTNGP11.phx.gbl...
> I'm still having some issues with report names using "german umlaute".
> Attached two small dataless sample reports, originally named "Ärger.rdl"
and
> "Übersicht.rdl". Both consists of a textbox linking to each other. Tis
works
> fine and is displayed correctly in HTML. Exporting one report to Excel and
> trying to follow the link fails.
> Should I avoid german umlaute in report names? I would consider to do so
if
> we would have 1982. But not today...:)
> r.
>
> Ärger.rdl
> --
> <?xml version="1.0" encoding="utf-8"?>
> <Report
>
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
> tion"
>
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> <rd:GridSpacing>0.25cm</rd:GridSpacing>
> <RightMargin>2.5cm</RightMargin>
> <Body>
> <ReportItems>
> <Textbox Name="textbox1">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <TextDecoration>Underline</TextDecoration>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <rd:DefaultName>textbox1</rd:DefaultName>
> <Width>6.78968cm</Width>
> <Action>
> <Drillthrough>
> <ReportName>Ãobersicht</ReportName>
> </Drillthrough>
> </Action>
> <CanGrow>true</CanGrow>
> <Value>Here is Ã"rger.rdl. Back to Ãobersicht.rdl</Value>
> </Textbox>
> </ReportItems>
> <Style />
> <Height>0.63492cm</Height>
> <ColumnSpacing>1cm</ColumnSpacing>
> </Body>
> <TopMargin>2.5cm</TopMargin>
> <Width>16cm</Width>
> <LeftMargin>2.5cm</LeftMargin>
> <rd:SnapToGrid>true</rd:SnapToGrid>
> <PageHeight>29.7cm</PageHeight>
> <rd:DrawGrid>true</rd:DrawGrid>
> <PageWidth>21cm</PageWidth>
> <rd:ReportID>52737cce-c0a6-4c5e-b23b-eaeca22bd186</rd:ReportID>
> <BottomMargin>2.5cm</BottomMargin>
> <Language>de-DE</Language>
> </Report>
>
> Übersicht.rdl
> --
> <?xml version="1.0" encoding="utf-8"?>
> <Report
>
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
> tion"
>
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> <rd:GridSpacing>0.25cm</rd:GridSpacing>
> <RightMargin>2.5cm</RightMargin>
> <Body>
> <ReportItems>
> <Textbox Name="textbox1">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <TextDecoration>Underline</TextDecoration>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <rd:DefaultName>textbox1</rd:DefaultName>
> <Height>0.63492cm</Height>
> <Width>7.75cm</Width>
> <Action>
> <Drillthrough>
> <ReportName>Ã"rger</ReportName>
> </Drillthrough>
> </Action>
> <CanGrow>true</CanGrow>
> <Value>This way to Ã"rger.rdl</Value>
> </Textbox>
> </ReportItems>
> <Style />
> <Height>0.75cm</Height>
> <ColumnSpacing>1cm</ColumnSpacing>
> </Body>
> <TopMargin>2.5cm</TopMargin>
> <Width>8cm</Width>
> <LeftMargin>2.5cm</LeftMargin>
> <rd:SnapToGrid>true</rd:SnapToGrid>
> <PageHeight>29.7cm</PageHeight>
> <rd:DrawGrid>true</rd:DrawGrid>
> <PageWidth>21cm</PageWidth>
> <rd:ReportID>b4667abb-f9e5-490e-b3ff-8cca1050a2d1</rd:ReportID>
> <BottomMargin>2.5cm</BottomMargin>
> <Language>de-DE</Language>
> </Report>
>
>|||Great, Robert. I'll be patient. Meanwhile I'll rename my reports.
Thanks.
r.