Boot.ini file: field

Showing posts with label field. Show all posts

Thursday, March 22, 2012

Bug? Hide duplicates works differently for different renderers.

I have found a bug in when using hide duplicates. It only affects certain renderers.
I have field with hide duplicates set on it.
In Preview or Deployed view, The value is shown in the first row correctly and not in subsequent rows until it changes as you would expect.
When in Print Preview in VS or Exported to PDF, it isn't shown in the first row.
Example;
--
Sales 123 456
789 123
432 123
Costs 111 222
333 444
When exported to PDF
--
123 456
789 123
432 123
Costs 111 222
333 444
Regards
Chris McGuiganThank you for your input.
I will investigate this issue.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chris McGuigan" <ChrisMcGuigan@.discussions.microsoft.com> wrote in message
news:E613FB90-D604-48E1-AFD9-8B35EED99A96@.microsoft.com...
> To narrow this down further, the first line in the problem section was
> conditionally hidden.
> If I made the first line of the second section hidden, the problem moves
> to that section.
> Regards
> Chris McGuigan
> "Chris McGuigan" wrote:
>> I have found a bug in when using hide duplicates. It only affects certain
>> renderers.
>> I have field with hide duplicates set on it.
>> In Preview or Deployed view, The value is shown in the first row
>> correctly and not in subsequent rows until it changes as you would
>> expect.
>> When in Print Preview in VS or Exported to PDF, it isn't shown in the
>> first row.
>> Example;
>> --
>> Sales 123 456
>> 789 123
>> 432 123
>> Costs 111 222
>> 333 444
>> When exported to PDF
>> --
>> 123 456
>> 789 123
>> 432 123
>> Costs 111 222
>> 333 444
>> Regards
>> Chris McGuigan|||Could you email me your RDL, because I'm unable to reproduce this problem.
Thank you,
Lev
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chris McGuigan" <ChrisMcGuigan@.discussions.microsoft.com> wrote in message
news:E613FB90-D604-48E1-AFD9-8B35EED99A96@.microsoft.com...
> To narrow this down further, the first line in the problem section was
> conditionally hidden.
> If I made the first line of the second section hidden, the problem moves
> to that section.
> Regards
> Chris McGuigan
> "Chris McGuigan" wrote:
>> I have found a bug in when using hide duplicates. It only affects certain
>> renderers.
>> I have field with hide duplicates set on it.
>> In Preview or Deployed view, The value is shown in the first row
>> correctly and not in subsequent rows until it changes as you would
>> expect.
>> When in Print Preview in VS or Exported to PDF, it isn't shown in the
>> first row.
>> Example;
>> --
>> Sales 123 456
>> 789 123
>> 432 123
>> Costs 111 222
>> 333 444
>> When exported to PDF
>> --
>> 123 456
>> 789 123
>> 432 123
>> Costs 111 222
>> 333 444
>> Regards
>> Chris McGuigan

Monday, March 19, 2012

Bug with GetFloat?

I have field defined as float (price).

when I do the following command I get invalid cast.

reader.GetFloat(reader.GetOrdinal("price"));

I have to pull it with GetDouble.

reader.GetDouble (reader.getOrdinal("price"));

which works.

since it's defined as a float would that not make this an error.. or am I missing something?

GetFloat and GetDouble don't do any conversions. GetFloat expects a single precision and GetDouble a double precision value. Since the SQL Float is double precision you'll get an casting error with GetFloat. You could use GetFloat with a real field.

See "SQL Server Data Types and Their .NET Framework Equivalents" in books online for more info.

|||
When dealing with currency, why not create price as a sql type 'money'?

reader.GetDecimal (reader.getOrdinal("price")) would then work for you without throwing any casting exceptions.

Bug with GetFloat?

I have field defined as float (price).

when I do the following command I get invalid cast.

reader.GetFloat(reader.GetOrdinal("price"));

I have to pull it with GetDouble.

reader.GetDouble (reader.getOrdinal("price"));

which works.

since it's defined as a float would that not make this an error.. or am I missing something?

See "SQL Server Data Types and Their .NET Framework Equivalents" in books online for more info.

|||
When dealing with currency, why not create price as a sql type 'money'?

reader.GetDecimal (reader.getOrdinal("price")) would then work for you without throwing any casting exceptions.

BUG Multi-value field in SP2

Hi,
There seems to be a bug with the multi-valed fields when only one value is
selected and that value contains only spaces. I get the message 'Please
select a value for the parameter...'. It works when I test it in Visual
Studio but it doesn't work on the Web. I'm using SQL Server 2005 with SP2.
Any ideas ? Suggestions ?
Regards
AlainOn Apr 6, 12:05 pm, "Alain Magnan" <AMag...@.Hatch.caGARBAGE> wrote:
> Hi,
> There seems to be a bug with the multi-valed fields when only one value is
> selected and that value contains only spaces. I get the message 'Please
> select a value for the parameter...'. It works when I test it in Visual
> Studio but it doesn't work on the Web. I'm using SQL Server 2005 with SP2.
> Any ideas ? Suggestions ?
> Regards
> Alain
Are you using 2 columns in the dataset that sources the multi-select
parameter? If the value field is something like 0, it might not error
out. Something like this might work (in the dataset; where Name is the
displayed item and NameID is the parameter value):
Name NameID
" " 0
"Bob" 1
"John" 2
"Bill" 3
Hope this is helpful.
Regards,
Enrique Martinez
Sr. Software Consultant

Wednesday, March 7, 2012

Bug

I have a client that I wrote a script for them to change the clustered index
from the PK field (which is an identity) to another field. The new
clustering field happens to contain some nulls (which is not supposed to
happen but..). Anyway, they ran the script and the new table with the new
clustered index was created without apparent errors. Then they ran a query
that looks like this:
SELECT *
FROM myTable
WHERE clusterField is not null and someOtherField is null
The return set returned records where the clusterField is null. I tried to
duplicate this on one of my servers and didn't have the problem. So I went
to their office and ran it and the problem persisted. After a couple of
hours of testing I found that the problem only existed above a certain
threshold of records in the table (about 1.1 million). After not being able
to find a solution I went home to do some more troubleshooting on some of my
servers and found that on one server it had the problem but on two others it
didn't have the problem. the one server that had the problem was using
service pack 3 while the two that worked were on SP3a. I thought I found
the problem and told my client to make sure that their servers were on SP3a.
They had on that was on SP3 and one on 3a. They tested the script again on
the server with 3a and still had the problem. I don't know where to go
next. I'm going to post the exact script that I used to exhibit the problem
in hopes that someone can find a solution or verify that this is a bug and
under what circumstances it occurs.
CREATE TABLE [HIST_NEW] (
[HISTOID] [bigint] IDENTITY(1,1) NOT NULL ,
[LOANOID] [bigint] NULL ,
[USERFILEOID] [bigint] NULL ,
[LOANPURGEOID] [bigint] NULL ,
[SystemDate] [varchar] (8) DEFAULT (''),
[SystemDateSQL] [datetime] default getdate(),
[SystemTime] [varchar] (6) DEFAULT (''),
[SystemTImeSQL] [datetime] default getdate(),
[TransactionID] [varchar] (5) DEFAULT (''),
[ActionCode] [varchar] (1) DEFAULT (''),
[BatchDate] [varchar] (8) DEFAULT (''),
[BatchDateSQL] [datetime] default getdate(),
[CompanyAgent] [varchar] (20) DEFAULT (''),
[DocumentID] [varchar] (20) DEFAULT (''),
[EffDate] [varchar] (8) DEFAULT (''),
[EffDateSQL] [datetime] default getdate(),
[Entry] [int] NULL ,
[ExpDate] [varchar] (8) DEFAULT (''),
[ExpDateSQL] [datetime] default getdate(),
[HistoryNumber] [int] NULL ,
[LNUM] [varchar] (18) DEFAULT (''),
[MailDate] [varchar] (8) DEFAULT (''),
[MailDateSQL] [datetime] default getdate(),
[ModCount] [smallint] NULL ,
[Policy] [varchar] (15) DEFAULT (''),
[UserID] [varchar] (10) DEFAULT (''),
[lenderNumber] [char] (4) DEFAULT (''),
CONSTRAINT [PK__HIST_] PRIMARY KEY NONCLUSTERED (HISTOID)
) ON [PRIMARY]
GO
--create a new clustered index on the LOANOID column
create clustered index cix_hist on hist_new (LOANOID) with fillfactor = 90
declare @.counter
set @.counter = 1
while @.counter < 3500000
BEGIN
INSERT HIST_NEW (LOANOID, LNUM)
SELECT case when @.counter %6 = 0 then null else @.counter end,
case when @.counter %500 = 0 then null else @.counter end,
SET @.counter = @.counter + 1
END
--This query should not return nulls in the LOANOID field but is does in
some cases.
select lnum, LOANOID
from HIST_new
where LOANOID is not null
and lnum is null
I appreciate any insight that can be provided on this issue. Also, I ahve
been running this on SQL Server Standard Edition on Win 2K with SP4 in all
cases and on various types of hardware.Is this on a multi-processor computer? If so, perhaps this is the bug
documented in 814509:
http://support.microsoft.com/default.aspx?scid=kb;en-us;814509&Product=sql
A workaround not mentioned in the article is to specify a MAXDOP 1 hint or
set the max degree of parallelism to 1.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Buddy Ackerman" <buddy_nospam@.buddyackerman.com> wrote in message
news:eTqRkj7JEHA.1892@.TK2MSFTNGP09.phx.gbl...
> I have a client that I wrote a script for them to change the clustered
index
> from the PK field (which is an identity) to another field. The new
> clustering field happens to contain some nulls (which is not supposed to
> happen but..). Anyway, they ran the script and the new table with the new
> clustered index was created without apparent errors. Then they ran a
query
> that looks like this:
> SELECT *
> FROM myTable
> WHERE clusterField is not null and someOtherField is null
> The return set returned records where the clusterField is null. I tried
to
> duplicate this on one of my servers and didn't have the problem. So I
went
> to their office and ran it and the problem persisted. After a couple of
> hours of testing I found that the problem only existed above a certain
> threshold of records in the table (about 1.1 million). After not being
able
> to find a solution I went home to do some more troubleshooting on some of
my
> servers and found that on one server it had the problem but on two others
it
> didn't have the problem. the one server that had the problem was using
> service pack 3 while the two that worked were on SP3a. I thought I found
> the problem and told my client to make sure that their servers were on
SP3a.
> They had on that was on SP3 and one on 3a. They tested the script again
on
> the server with 3a and still had the problem. I don't know where to go
> next. I'm going to post the exact script that I used to exhibit the
problem
> in hopes that someone can find a solution or verify that this is a bug and
> under what circumstances it occurs.
>
> CREATE TABLE [HIST_NEW] (
> [HISTOID] [bigint] IDENTITY(1,1) NOT NULL ,
> [LOANOID] [bigint] NULL ,
> [USERFILEOID] [bigint] NULL ,
> [LOANPURGEOID] [bigint] NULL ,
> [SystemDate] [varchar] (8) DEFAULT (''),
> [SystemDateSQL] [datetime] default getdate(),
> [SystemTime] [varchar] (6) DEFAULT (''),
> [SystemTImeSQL] [datetime] default getdate(),
> [TransactionID] [varchar] (5) DEFAULT (''),
> [ActionCode] [varchar] (1) DEFAULT (''),
> [BatchDate] [varchar] (8) DEFAULT (''),
> [BatchDateSQL] [datetime] default getdate(),
> [CompanyAgent] [varchar] (20) DEFAULT (''),
> [DocumentID] [varchar] (20) DEFAULT (''),
> [EffDate] [varchar] (8) DEFAULT (''),
> [EffDateSQL] [datetime] default getdate(),
> [Entry] [int] NULL ,
> [ExpDate] [varchar] (8) DEFAULT (''),
> [ExpDateSQL] [datetime] default getdate(),
> [HistoryNumber] [int] NULL ,
> [LNUM] [varchar] (18) DEFAULT (''),
> [MailDate] [varchar] (8) DEFAULT (''),
> [MailDateSQL] [datetime] default getdate(),
> [ModCount] [smallint] NULL ,
> [Policy] [varchar] (15) DEFAULT (''),
> [UserID] [varchar] (10) DEFAULT (''),
> [lenderNumber] [char] (4) DEFAULT (''),
> CONSTRAINT [PK__HIST_] PRIMARY KEY NONCLUSTERED (HISTOID)
> ) ON [PRIMARY]
> GO
> --create a new clustered index on the LOANOID column
> create clustered index cix_hist on hist_new (LOANOID) with fillfactor = 90
>
> declare @.counter
> set @.counter = 1
> while @.counter < 3500000
> BEGIN
> INSERT HIST_NEW (LOANOID, LNUM)
> SELECT case when @.counter %6 = 0 then null else @.counter end,
> case when @.counter %500 = 0 then null else @.counter end,
> SET @.counter = @.counter + 1
> END
>
> --This query should not return nulls in the LOANOID field but is does in
> some cases.
> select lnum, LOANOID
> from HIST_new
> where LOANOID is not null
> and lnum is null
>
> I appreciate any insight that can be provided on this issue. Also, I ahve
> been running this on SQL Server Standard Edition on Win 2K with SP4 in all
> cases and on various types of hardware.
>|||Thanks Dan, that appears to be the problem. I've advised my client to turn
off parallelism until a service pack with that fix becomes available.
--Buddy
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:%23%237dZTBKEHA.2556@.TK2MSFTNGP11.phx.gbl...
> Is this on a multi-processor computer? If so, perhaps this is the bug
> documented in 814509:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;814509&Product=sql
> A workaround not mentioned in the article is to specify a MAXDOP 1 hint or
> set the max degree of parallelism to 1.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Buddy Ackerman" <buddy_nospam@.buddyackerman.com> wrote in message
> news:eTqRkj7JEHA.1892@.TK2MSFTNGP09.phx.gbl...
> >
> > I have a client that I wrote a script for them to change the clustered
> index
> > from the PK field (which is an identity) to another field. The new
> > clustering field happens to contain some nulls (which is not supposed to
> > happen but..). Anyway, they ran the script and the new table with the
new
> > clustered index was created without apparent errors. Then they ran a
> query
> > that looks like this:
> >
> > SELECT *
> > FROM myTable
> > WHERE clusterField is not null and someOtherField is null
> >
> > The return set returned records where the clusterField is null. I tried
> to
> > duplicate this on one of my servers and didn't have the problem. So I
> went
> > to their office and ran it and the problem persisted. After a couple of
> > hours of testing I found that the problem only existed above a certain
> > threshold of records in the table (about 1.1 million). After not being
> able
> > to find a solution I went home to do some more troubleshooting on some
of
> my
> > servers and found that on one server it had the problem but on two
others
> it
> > didn't have the problem. the one server that had the problem was using
> > service pack 3 while the two that worked were on SP3a. I thought I
found
> > the problem and told my client to make sure that their servers were on
> SP3a.
> > They had on that was on SP3 and one on 3a. They tested the script again
> on
> > the server with 3a and still had the problem. I don't know where to go
> > next. I'm going to post the exact script that I used to exhibit the
> problem
> > in hopes that someone can find a solution or verify that this is a bug
and
> > under what circumstances it occurs.
> >
> >
> >
> > CREATE TABLE [HIST_NEW] (
> > [HISTOID] [bigint] IDENTITY(1,1) NOT NULL ,
> > [LOANOID] [bigint] NULL ,
> > [USERFILEOID] [bigint] NULL ,
> > [LOANPURGEOID] [bigint] NULL ,
> > [SystemDate] [varchar] (8) DEFAULT (''),
> > [SystemDateSQL] [datetime] default getdate(),
> > [SystemTime] [varchar] (6) DEFAULT (''),
> > [SystemTImeSQL] [datetime] default getdate(),
> > [TransactionID] [varchar] (5) DEFAULT (''),
> > [ActionCode] [varchar] (1) DEFAULT (''),
> > [BatchDate] [varchar] (8) DEFAULT (''),
> > [BatchDateSQL] [datetime] default getdate(),
> > [CompanyAgent] [varchar] (20) DEFAULT (''),
> > [DocumentID] [varchar] (20) DEFAULT (''),
> > [EffDate] [varchar] (8) DEFAULT (''),
> > [EffDateSQL] [datetime] default getdate(),
> > [Entry] [int] NULL ,
> > [ExpDate] [varchar] (8) DEFAULT (''),
> > [ExpDateSQL] [datetime] default getdate(),
> > [HistoryNumber] [int] NULL ,
> > [LNUM] [varchar] (18) DEFAULT (''),
> > [MailDate] [varchar] (8) DEFAULT (''),
> > [MailDateSQL] [datetime] default getdate(),
> > [ModCount] [smallint] NULL ,
> > [Policy] [varchar] (15) DEFAULT (''),
> > [UserID] [varchar] (10) DEFAULT (''),
> > [lenderNumber] [char] (4) DEFAULT (''),
> > CONSTRAINT [PK__HIST_] PRIMARY KEY NONCLUSTERED (HISTOID)
> > ) ON [PRIMARY]
> > GO
> >
> > --create a new clustered index on the LOANOID column
> > create clustered index cix_hist on hist_new (LOANOID) with fillfactor =90
> >
> >
> > declare @.counter
> > set @.counter = 1
> >
> > while @.counter < 3500000
> > BEGIN
> > INSERT HIST_NEW (LOANOID, LNUM)
> > SELECT case when @.counter %6 = 0 then null else @.counter end,
> > case when @.counter %500 = 0 then null else @.counter end,
> >
> > SET @.counter = @.counter + 1
> > END
> >
> >
> > --This query should not return nulls in the LOANOID field but is does in
> > some cases.
> > select lnum, LOANOID
> > from HIST_new
> > where LOANOID is not null
> > and lnum is null
> >
> >
> >
> > I appreciate any insight that can be provided on this issue. Also, I
ahve
> > been running this on SQL Server Standard Edition on Win 2K with SP4 in
all
> > cases and on various types of hardware.
> >
> >
>

Friday, February 24, 2012

Brute-Force Substring Extraction

In my product description field, I need to extract the data that looks like
19-3-19 or 0-0-7. Sometimes that pattern is in the data, sometimes not.
I only want those that contain the pattern, of course, but I have no other method of determining whether or not the pattern will be in the description.
I thought I'd found a method that worked, until I encountered descriptions
that had a '-' in it prior to the pattern.

Here's what I have so far, it works for all but the last one.

CREATE TABLE #TMPPROD (
PROD_DESC VARCHAR(60)
)
INSERT INTO #TMPPROD
SELECT '22-4-12 15%SCU'
UNION ALL
SELECT '14-4-8 W/F'
UNION ALL
SELECT 'UFLEXX 17-3-17 2%FE'
UNION ALL
SELECT 'FERT-PEST 19-19-19'

SELECT PROD_DESC, SUBSTRING(PROD_DESC,CHARINDEX('-', PROD_DESC)-CASE WHEN PATINDEX('[0-9]', SUBSTRING(PROD_DESC, CHARINDEX('-', PROD_DESC) - 2,1))<>0 THEN 2
WHEN PATINDEX('[0-9]', SUBSTRING(PROD_DESC, CHARINDEX('-', PROD_DESC) - 1,1))<>0 THEN 1
ELSE 0 END, CHARINDEX('-', SUBSTRING(PROD_DESC,
CHARINDEX('-',PROD_DESC)+1,2))+CASE WHEN PATINDEX('[0-9]', SUBSTRING(PROD_DESC, CHARINDEX('-', PROD_DESC) - 2,1))<>0 THEN 2
WHEN PATINDEX('[0-9]', SUBSTRING(PROD_DESC, CHARINDEX('-', PROD_DESC) - 1,1))<>0 THEN 1
ELSE 0 END+2+CASE WHEN PATINDEX('[0-9]', SUBSTRING(PROD_DESC, CHARINDEX('-', PROD_DESC) +1,2))<>0 THEN 2
WHEN PATINDEX('[0-9]', SUBSTRING(PROD_DESC, CHARINDEX('-', PROD_DESC) - 1,1))<>0 THEN 1
ELSE 0 END) AS 'ANALYSIS'
FROM #TMPPROD
WHERE PROD_DESC LIKE '%-[0-9]%-[0-9]%'

DROP TABLE #TMPPROD

Any pointers on what to try next, or have I severely over-complicated the process - as I am wont to do.

Thanks
MarkSorry fella - I could not be bothered figuring out the DML.
When you say:
19-3-19 or 0-0-7
do you mean literals (i.e. these are the ONLY two values the field might contain) or these are examples? I suspect the latter since the former is trivial but to be sure...|||Yes, it's the latter. The numbers themselves can be anywhere between 0 and 62|||Just in case it helps, the statement above returns:

Prod_Desc...............Analysis
22-4-12 15%SCU........22-4-12
14-4-8 W/F.............14-4-8
UFLEXX 17-3-17 2%FE...17-3-17
FERT-PEST 19-19-19.....-P

That last one aint what I'm looking for|||Okey doo - ta :)

So - I don't think this is a problem:
I thought I'd found a method that worked, until I encountered descriptions
that had a '-' in it prior to the pattern.Unless the pattern can also be followed by digits and/ or "-".

Am I being a thicky pants?|||Unless the pattern can also be followed by digits and/ or "-".

Not sure I follow, but... Anything that follows the pattern is immaterial.
I tried some test data with an extra '-' farther down the line, and it still worked as expected.|||DECLARE @.TMPPROD TABLE (PROD_DESC VARCHAR(60))

INSERT @.TMPPROD
SELECT '22-4-12 15%SCU' UNION ALL
SELECT '14-4-8 W/F' UNION ALL
SELECT 'UFLEXX 17-3-17 2%FE' UNION ALL
SELECT 'No-valid-data 17-17 x' UNION ALL
SELECT 'FERT-PEST 19-19-19'

SELECT LEFT(d.q, CHARINDEX(' ', d.q + ' '))
FROM (
SELECT SUBSTRING(PROD_DESC, PATINDEX('%[0-9]%-%[0-9]-%[0-9]%', PROD_DESC), 60) AS q
FROM @.TMPPROD
) AS d
WHERE d.q LIKE '[0-9]%-%[0-9]-%[0-9]%'|||Ok, I'm going to preface this with the assertion that the code is uglier than a mud fence because it is doing a "brute force" dance around what I see as pattern matching limitations in the product. I also take a few liberties in accepting any one or two digits, not just 1 through 62, although you could also work around that easily enough if it was important to you.-- 20070828 ptp Find/return a CNO formatted substring inside an argument string

CREATE FUNCTION dbo.fCNO(@.pArg VARCHAR(8000))
RETURNS VARCHAR(9) AS
BEGIN
DECLARE
@.cResult VARCHAR(9)
, @.i INT

SET @.cResult = NULL

SET @.i = PatIndex('%[0-9]%-[0-9]%-[0-9]%', @.pArg)
WHILE @.cResult IS NULL AND 0 < @.i
BEGIN
SET @.pArg = SubString(@.pArg, @.i, 8000)
SET @.cResult =
CASE
WHEN @.pArg LIKE '[0-9][0-9]-[0-9][0-9]-[0-9][0-9]%' THEN SubString(@.pArg, 1, 8)
WHEN @.pArg LIKE '[0-9][0-9]-[0-9][0-9]-[0-9]%' THEN SubString(@.pArg, 1, 7)
WHEN @.pArg LIKE '[0-9][0-9]-[0-9]-[0-9][0-9]%' THEN SubString(@.pArg, 1, 7)
WHEN @.pArg LIKE '[0-9]-[0-9][0-9]-[0-9][0-9]%' THEN SubString(@.pArg, 1, 7)
WHEN @.pArg LIKE '[0-9][0-9]-[0-9]-[0-9]%' THEN SubString(@.pArg, 1, 6)
WHEN @.pArg LIKE '[0-9]-[0-9][0-9]-[0-9]%' THEN SubString(@.pArg, 1, 6)
WHEN @.pArg LIKE '[0-9]-[0-9]-[0-9]%' THEN SubString(@.pArg, 1, 5)
ELSE NULL
END

SET @.i = PatIndex('%[0-9]%-[0-9]%-[0-9]%', @.pArg)
END

RETURN @.cResult
END
GO

CREATE TABLE #TMPPROD (
PROD_DESC VARCHAR(60)
)
INSERT INTO #TMPPROD
SELECT '22-4-12 15%SCU'
UNION ALL
SELECT '14-4-8 W/F'
UNION ALL
SELECT 'UFLEXX 17-3-17 2%FE'
UNION ALL
SELECT 'FERT-PEST 19-19-19'

SELECT dbo.fCNO(PROD_DESC), PROD_DESC
FROM #TMPPROD
WHERE dbo.fCNO(PROD_DESC) IS NOT NULL

DROP TABLE #TMPPROD-PatP|||WOOHOO!
Two working solutions. :)
Thanks guys!|||Just be careful because Peso's solution runs quicker than mine does, but it also returns SSNs like 123-45-6789 and United States phone numbers like 312-555-1212. I read about a guy having that problem in this book once!

-PatP|||Yes, I also found it returns a few oddball items like "19-19-19F" or
if there is a series of 5 or more numbers prior to the pattern. That is
helpful in finding descriptions that need some maintenance, but ultimately
I don't want that showing up in my reports.|||-- Create and stage a permanent valid patterns table
CREATE TABLE #Patterns
(
Pattern VARCHAR(12) PRIMARY KEY CLUSTERED
)

INSERT #Patterns
(
Pattern
)
SELECT '% ' + CONVERT(VARCHAR(2), v1.Number) + '-' + CONVERT(VARCHAR(2), v2.Number) + '-' + CONVERT(VARCHAR(2), v3.Number) + ' %' AS x
FROM master..spt_values AS v1
CROSS JOIN master..spt_values AS v2
CROSS JOIN master..spt_values AS v3
WHERE v1.Type = 'p'
AND v1.Number BETWEEN 0 AND 62
AND v2.Type = 'p'
AND v2.Number BETWEEN 0 AND 62
AND v3.Type = 'p'
AND v3.Number BETWEEN 0 AND 62

-- Prepare sample data
DECLARE @.TMPPROD TABLE (PROD_DESC VARCHAR(60))

INSERT @.TMPPROD
SELECT '22-4-12 15%SCU' UNION ALL
SELECT '14-4-8 W/F' UNION ALL
SELECT 'UFLEXX 17-3-17 2%FE' UNION ALL
SELECT 'No-valid-data 17-17 x' UNION ALL
SELECT 'FERT-PEST 19-19-19'

-- Show the expected output
SELECT p.PROD_DESC,
PATINDEX(pat.Pattern, ' ' + p.PROD_DESC + ' ') AS Position
FROM #Patterns AS pat
INNER JOIN @.TMPPROD AS p ON ' ' + p.PROD_DESC + ' ' LIKE pat.Pattern|||That approach is better than the one that I posted with two caveats... First of all, it misses leading zeros which tend to appear relatively often in live CNO data although there aren't any in RedNeckGeeks sample data. As a side issue, I think that slows the selection process down quite a bit, so I think that will run a good bit slower.

-PatP|||Add this you your approach and you are more set!
2 combinations on three places gives 8 variations in total.

Here is the eight

WHEN @.pArg LIKE '[0-9]-[0-9][0-9]-[0-9]%' THEN SubString(@.pArg, 1, 6)|||As a side issue, I think that slows the selection process down quite a bit, so I think that will run a good bit slower.Yes, it is slower.

BUT.. It handles the 0-62 only condition...

Browsing SSAS (2005) Dimensions from Excel

Hi all,

I am having difficulty understanding why my dimension names aren't showing up in a pivot table field list when I browse SSAS 2005 cubes from Excel 2003 (using the 90 PTS OLAP DLL).

Has anybody else experienced this problem where the dimension names aren't being made visible? Unfortunately all I see are my attribute names and these, by themselves, are extremely ambiguous.

Just wondering if there are any quick and easy fixes or work-arounds that anybody could recommend.

Thanks in advance,
DB
Since the hierarchies within dimensions (including the visible default attribute hierarchies) are probably what you're seeing in Excel, one work-around would be to create user-defined hierarchies with more meaningful names within the dimensions, even if they merely mimic an attribute hierarchy with an ambiguous name. The corresponding attribute hierarchies could then be hidden, to avoid confusion.

http://msdn2.microsoft.com/en-us/library/ms175631(en-US,SQL.90).aspx
>>

Configuring Multilevel Hierarchies

The Cube Wizard and the Dimension Wizard in Business Intelligence Development Studio in Microsoft SQL Server 2005 Analysis Services (SSAS) create multilevel hierarchies that are based on natural (one-to-many) relationships between columns in tables of a database. You can also create and edit hierarchies by using the Dimension Structure tab of Dimension Designer.

The Hierarchies and Levels pane of the Dimension Structure tab shows the hierarchies in the dimension. The title bar for a hierarchy shows the name of the hierarchy, which you can change by setting the Name property of the hierarchy. The levels of a hierarchy are marked by dots—the root level is marked by a single dot, and each level below the root is marked with an additional dot.

Click a hierarchy to display its properties in the Properties window. Click a level in the hierarchy to display its properties.

Creating Multilevel Hierarchies

Create multilevel hierarchies by dragging attributes from the Attributes pane of the Dimension Structure tab to a blank area on the Hierarchies and Levels pane. You can also drag columns from the Data Source View pane to the Hierarchies and Levels pane. Dragging a column creates the corresponding attribute, if the attribute does not already exist. To add an attribute or a column as a level in an existing hierarchy, drag the attribute or column to the existing hierarchy. As you drag an attribute or a column over a hierarchy, a bar indicates where the new level will be created when you drop the attribute or the column on the hierarchy.
>>

http://msdn2.microsoft.com/en-us/library/ms174939(en-US,SQL.90).aspx
>>
Showing or Hiding an Attribute Hierarchy

If you want to add an attribute as a level to multilevel hierarchies, but you do not want users to see the attribute hierarchy in client applications, set AttributeHierarchyEnabled to True and set AttributeHierarchyVisible to False. These settings prevent users from browsing an attribute hierarchy without disabling it. The AttributeHierarchyVisible setting is ignored if AttributeHierarchyEnabled is set to False.
>>

|||Your work-around makes sense. But it is a great deal of effort to do this for all of our dimension attributes in all of our dimensions.

I am still somewhat amazed that Excel 2003 isn't automatically displaying dimension names. Call me crazy but it seems to me that the name of the dimension is significant enough to display in some fashion.

Thanks,
db

Thursday, February 16, 2012

Bring File In As Binary Field

I want to have an SSIS package that processes a file in the normal insert, update style. But at the end I want to store the file as a binary field to another table for archive purposes. I am having trouble finding a good way to do this. Any samples, ideas, or articles would be appreciated.I haven't tried this, but my first thought would be reading the file into a variable through a script task, and then using that variable to write the blob to the destination table.|||That is the only method I could come up with as well.|||

Import Column transform is supposed to do this. Could you give it a try? It is not easy to configure as it deos not have a custom UI, but see if the BOL has some info.

Thanks.

|||It sounds like this might do what I need but books on line (like many things) is worthless for explanation of how/what to setup and use this component. Are you aware of other resources for this component?|||A quick Google search turned this up. See if it helps:

http://mgopinath.blogspot.com/search/label/Integration%20Services|||Thanks Phil... my google skills were poor as I was unable to find an article like that.|||

1Dave wrote:
Thanks Phil... my google skills were poor as I was unable to find an article like that.

ssis "import column"

Bring back excluded item

Hi all,

I have been wrestling with this problem all morning with no success so
far where I have a need to bring back an excluded field.

Basically I have a list of order numbers. Each order number can have
many order types attached one of which is a P type. Most order types
have an account number attached in its own field however when a P type
is selected the account number is not brought back.

Is there someway I can get this brought back for each P type or do I
have to do some fancy insert in a data warehouse to get this done (i.e.
insert account numbers into all P types)?

Many thanks

Sam

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Please post DDL (CREATE TABLE statements, including the relevant columns,
keys and constraints) for these tables, post a few rows of sample data as
INSERT statements and show your required result. That way it's much easier
for others to understand and solve your problem.

--
David Portas
----
Please reply only to the newsgroup
--

Tuesday, February 14, 2012

Breaking out data from a text field type

In my database there is a text field type that is used to enter street
address. This address could be a few lines long, each line with a
carriage return at the end.
Is there a way to search for these carriage returns and break out what
is in each line seperately?

Thanks.
Mike[posted and mailed, please reply in news]

Mike (mrea@.ohiotravelbag.com) writes:
> In my database there is a text field type that is used to enter street
> address. This address could be a few lines long, each line with a
> carriage return at the end.
> Is there a way to search for these carriage returns and break out what
> is in each line seperately?

Is that really the datatype text? That seems a bit over kill for a street
address. They would very rarely be over 8000 bytes. Or even 4000 if you
are using varchar.

The functions to use are substring and charindex. And char(13) for the
CRs. Or char(13) + char(10) if it's actually CR + LF. charindex does not
handle text beyond the varchar limit, but I don't think this would be
an issue.

You could also do:

SELECT @.adr = adr FROM tbl WHERE ..
SELECT str
FROM iter_charlist_to_table(@.adr, char(13))
ORDER BY listpos

You find this function on
http://www.sommarskog.se/arrays-in-...list-of-strings

Note that if you need to use char(13) + char(10) as delimiter, you
will have to change the function. (And not only the length of delimiter.)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Thursday, March 22, 2012

Bug? Hide duplicates works differently for different renderers.

Monday, March 19, 2012

Bug with GetFloat?

Bug with GetFloat?

BUG Multi-value field in SP2

Wednesday, March 7, 2012

Bug

Bug

Bug

Friday, February 24, 2012

Brute-Force Substring Extraction

Browsing SSAS (2005) Dimensions from Excel

Thursday, February 16, 2012

Bring File In As Binary Field

Bring back excluded item

Tuesday, February 14, 2012

Breaking out data from a text field type

Boot.ini file

Blog Archive

About Me