Char and Varchar: piece

Showing posts with label piece. Show all posts

Tuesday, March 20, 2012

Check if a file exists using sql

Is there is a piece for code ot sample code that can let me check if a file exists? Has any one done this before?
ThanksTry this idea.

create table #tmp(result varchar(100))
go
insert #tmp
exec master..xp_cmdshell 'dir filename.txt'
select * from #tmp where result like '%filename.txt%'|||Originally posted by snail
Try this idea.

create table #tmp(result varchar(100))
go
insert #tmp
exec master..xp_cmdshell 'dir filename.txt'
select * from #tmp where result like '%filename.txt%'

I found another method to do this. I used the xp_fileexist command and it works well. The idea above works as well

Thanks for the input.sql

Sunday, February 19, 2012

Chart Colors

Hi,
I need to change the colors of my pie chart. The pie chart consists of five
pieces, which represent the severity of the piece (i.e, 30% of the users have
more than 5 problmes) so I need this piece to be red (no problems should be
green, for this matter). I can't seem to find a way to configure the colors
myself.
Any suggestions?You will need RS 2000 with SP1 or later. In that case, you can control the
color of data points based on an expression. Please check the "Chart Styles"
section under 4.1.3 in the SP1 Readme:
http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm#_chart_enhancements
An example is copied to the bottom of this posting. The example will
highlight all pie segments which have absolute values > 15 in red color.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Eyal S" <Eyal S@.discussions.microsoft.com> wrote in message
news:EB3EFF84-2A41-47F0-9AA2-FB291D9FB22C@.microsoft.com...
> Hi,
> I need to change the colors of my pie chart. The pie chart consists of
> five
> pieces, which represent the severity of the piece (i.e, 30% of the users
> have
> more than 5 problmes) so I need this piece to be red (no problems should
> be
> green, for this matter). I can't seem to find a way to configure the
> colors
> myself.
> Any suggestions?
============================================
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Chart Name="chart1">
<ThreeDProperties>
<Rotation>30</Rotation>
<Inclination>30</Inclination>
<Shading>Simple</Shading>
<WallThickness>50</WallThickness>
</ThreeDProperties>
<Style>
<BackgroundColor>White</BackgroundColor>
</Style>
<Legend>
<Visible>true</Visible>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Position>BottomCenter</Position>
<Layout>Table</Layout>
</Legend>
<Palette>Default</Palette>
<ChartData>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Sum(Fields!UnitsInStock.Value)</Value>
</DataValue>
</DataValues>
<DataLabel>
<Style />
<Visible>true</Visible>
</DataLabel>
<Style>
<BackgroundGradientEndColor>Black</BackgroundGradientEndColor>
<BackgroundColor>=iif(Sum(Fields!UnitsInStock.Value) >
15, "Red", Nothing)</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Marker>
<Size>6pt</Size>
</Marker>
</DataPoint>
</DataPoints>
</ChartSeries>
</ChartData>
<CategoryAxis>
<Axis>
<Title />
<MajorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<Margin>true</Margin>
<Visible>true</Visible>
</Axis>
</CategoryAxis>
<DataSetName>Northwind</DataSetName>
<PointWidth>0</PointWidth>
<Type>Pie</Type>
<Top>0.125in</Top>
<Title />
<CategoryGroupings>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="chart1_CategoryGroup1">
<GroupExpressions>
<GroupExpression>=Fields!ProductName.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Label>=Fields!ProductName.Value</Label>
</DynamicCategories>
</CategoryGrouping>
</CategoryGroupings>
<Subtype>Plain</Subtype>
<PlotArea>
<Style>
<BackgroundColor>LightGrey</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</PlotArea>
<Left>0.25in</Left>
<ValueAxis>
<Axis>
<Title />
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<Visible>true</Visible>
<Scalar>true</Scalar>
</Axis>
</ValueAxis>
</Chart>
</ReportItems>
<Style />
<Height>5in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>f029975b-69ee-431e-b75d-ece991d33884</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=(local);initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>6.5in</Width>
<DataSets>
<DataSet Name="Northwind">
<Fields>
<Field Name="ProductID">
<DataField>ProductID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="ProductName">
<DataField>ProductName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="SupplierID">
<DataField>SupplierID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="CategoryID">
<DataField>CategoryID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="QuantityPerUnit">
<DataField>QuantityPerUnit</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="UnitPrice">
<DataField>UnitPrice</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
<Field Name="UnitsInStock">
<DataField>UnitsInStock</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
<Field Name="UnitsOnOrder">
<DataField>UnitsOnOrder</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
<Field Name="ReorderLevel">
<DataField>ReorderLevel</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
<Field Name="Discontinued">
<DataField>Discontinued</DataField>
<rd:TypeName>System.Boolean</rd:TypeName>
</Field>
<Field Name="CategoryName">
<DataField>CategoryName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="NullUnits">
<DataField>NullUnits</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT TOP 7 *, NULL AS NullUnits
FROM [Alphabetical list of products]
WHERE (UnitsOnOrder > 0)</CommandText>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>d0cefd8d-3b82-4f54-b1af-a9af24a270a5</rd:ReportID>
<BottomMargin>1in</BottomMargin>
</Report>

Thursday, February 16, 2012

CHARINDEX

SQL Server 2000

Ya know, it is always the simplest stuff that gets ya !!

I am having the hardest time getting a simple piece of code working.
Must be brain dead today.

Goal: Get the users full name from a string

Here is sample data:

"LDAP://blahblahblah/CN=Kevin Jones,OU=DevEng,DC=nobody,DC=priv,DC=com"

Code:
IF LEN(@.strReturnValue) > 0 BEGIN
SELECT @.strReturnValue = SUBSTRING(@.strReturnValue,
(CHARINDEX('CN=',@.strReturnValue)+3),
(CHARINDEX(',',@.strReturnValue)-1))
END

It will extract:
"Kevin Jones,OU=DevEng,DC=nobody,DC=priv,DC=com"

I want it to extract:
Kevin Jones

Thanks.On 23 Jun 2005 08:51:27 -0700, csomberg@.dwr.com wrote:

> "LDAP://blahblahblah/CN=Kevin Jones,OU=DevEng,DC=nobody,DC=priv,DC=com"
> Code:
> IF LEN(@.strReturnValue) > 0 BEGIN
> SELECT @.strReturnValue = SUBSTRING(@.strReturnValue,
> (CHARINDEX('CN=',@.strReturnValue)+3),
> (CHARINDEX(',',@.strReturnValue)-1))

declare @.test varchar(255)
declare @.pos int

select @.test = 'LDAP://blahblahblah/CN=Kevin
Jones,OU=DevEng,DC=nobody,DC=priv,DC=com'

select @.pos = CHARINDEX('CN=',@.test)+3

SELECT SUBSTRING(@.test, @.pos ,(CHARINDEX(',',@.test)) - @.pos)

Tony
--
http://www.dotnet-hosting.com
Free web hosting with ASP.NET & SQL Server
No ads - No trials - Innovative features|||...oops i should have written:

You're thinking the substring syntax is
substring( string, start, end )

when the correct syntax is
substring( string, start, length )

where length is end_position - start position

Sorry for the confusion.

hth,

victor dileo

csomberg@.dwr.com wrote:
> SQL Server 2000
> Ya know, it is always the simplest stuff that gets ya !!
> I am having the hardest time getting a simple piece of code working.
> Must be brain dead today.
> Goal: Get the users full name from a string
> Here is sample data:
> "LDAP://blahblahblah/CN=Kevin Jones,OU=DevEng,DC=nobody,DC=priv,DC=com"
> Code:
> IF LEN(@.strReturnValue) > 0 BEGIN
> SELECT @.strReturnValue = SUBSTRING(@.strReturnValue,
> (CHARINDEX('CN=',@.strReturnValue)+3),
> (CHARINDEX(',',@.strReturnValue)-1))
> END
> It will extract:
> "Kevin Jones,OU=DevEng,DC=nobody,DC=priv,DC=com"
> I want it to extract:
> Kevin Jones
> Thanks.|||Note the charindex syntax:
charindex ( string, start, length )

You're mistakenly thinking the syntax is:
charindex ( string, start, end )

The "end" parameter should actually be length from the "start"
position, and be calculated as something like:
length = end - start

Once you fix that, I think your code will work just fine.