Check Constraint ot Instead of trigger ?

Hi All.. I have a vendor supplied application that writes to a very large
table. I have identified a set of data that the vendors client software send
s
the database that we do not use, but adds 1 million rows of data each day.
I've added an "After" trigger to delete this data as it gets inserted, but
I'm searching for a way from keeping it from getting inserted in the first
place. I can create a check constraint or an instead of trigger. The docs
say use contraints when you can, but does anyone have a different opinion as
how to redirect data to the bit bucket and not insert it into the table ?
And, no the vendor won't help and I can't say who it is ? :>Is the Vendor insert coming from a Strored Procedure? If so, implement your
logic inside the stored procedure before even doing anything to the table
itself.
"JohnW" wrote:

> Hi All.. I have a vendor supplied application that writes to a very large
> table. I have identified a set of data that the vendors client software se
nds
> the database that we do not use, but adds 1 million rows of data each day.
> I've added an "After" trigger to delete this data as it gets inserted, but
> I'm searching for a way from keeping it from getting inserted in the first
> place. I can create a check constraint or an instead of trigger. The docs
> say use contraints when you can, but does anyone have a different opinion
as
> how to redirect data to the bit bucket and not insert it into the table ?
> And, no the vendor won't help and I can't say who it is ? :>|||On Wed, 26 Apr 2006 14:09:02 -0700, JohnW wrote:

>Hi All.. I have a vendor supplied application that writes to a very large
>table. I have identified a set of data that the vendors client software sen
ds
>the database that we do not use, but adds 1 million rows of data each day.
>I've added an "After" trigger to delete this data as it gets inserted, but
>I'm searching for a way from keeping it from getting inserted in the first
>place. I can create a check constraint or an instead of trigger. The docs
>say use contraints when you can, but does anyone have a different opinion a
s
>how to redirect data to the bit bucket and not insert it into the table ?
>And, no the vendor won't help and I can't say who it is ? :>
Hi John,
Adding a CHECK constraint will prevent the insertion of the data and
throw an error. Also, if 100 rows are inserted in a single INSERT
statement and 1 fails the CHECK constraint, they are all rejected. If
this is the behaviour you need, AND the thrid party application can
handle the error condition raised by the CHECK contraint, use it.
Otherwise, use an INSTEAD OF trigger. That can prevent insertion of all
rows, or of only the offending rows, without sputtering error messages
to the client.
Hugo Kornelis, SQL Server MVP|||Just another solution, though if u can do it in the SP thats the best way..
You can use a for insert trigger and
check if the insert should happen, if not rollback the transaction.
like this
(this not syntax.. just pseudo code) check this anyways
create trigger t1 "for insert" on tbl1
begin
if condition = false
rollback tran
end

Check Client IP

Hello,

How check Client IP address in stored procedure?

http://www.sqlserver2005.de/Articles/3/

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Chart: legend should not scale with the chart

The legend scales with the Chart. My client would the rectangles in the
legend to be perfect squares. Is this possible?
--
Callidus
Software DesignerYes, this is possible if you turn off the legend on the chart and use a
table instead to "draw" the legend for you. I will post a sample within the
next few days which shows how to do this.
Robert M. Bruckner
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Callidus" <Callidus@.discussions.microsoft.com> wrote in message
news:AE04FCEE-6125-4392-A9D0-FBDB89655532@.microsoft.com...
> The legend scales with the Chart. My client would the rectangles in the
> legend to be perfect squares. Is this possible?
> --
> Callidus
> Software Designer|||The sample RDL is shown at the bottom of this posting. It will require some
careful study to see how it works.
Robert M. Bruckner
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:e7Fdia%23VFHA.612@.TK2MSFTNGP12.phx.gbl...
> Yes, this is possible if you turn off the legend on the chart and use a
> table instead to "draw" the legend for you. I will post a sample within
> the next few days which shows how to do this.
>
> --
> Robert M. Bruckner
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Callidus" <Callidus@.discussions.microsoft.com> wrote in message
> news:AE04FCEE-6125-4392-A9D0-FBDB89655532@.microsoft.com...
>> The legend scales with the Chart. My client would the rectangles in the
>> legend to be perfect squares. Is this possible?
>> --
>> Callidus
>> Software Designer
=========================
<?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>
<Author>Robert M. Bruckner</Author>
<Body>
<ReportItems>
<Table Name="table1">
<Height>0.5in</Height>
<ZIndex>1</ZIndex>
<Style />
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Legend</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<DataSetName>DataSet1</DataSetName>
<Top>0.125in</Top>
<Width>2in</Width>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Rectangle Name="rectangle1">
<ZIndex>1</ZIndex>
<Style>
<BackgroundColor>=Code.GetColor(Fields!CategoryName.Value)</BackgroundColor>
</Style>
</Rectangle>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="CategoryName">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>CategoryName</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!CategoryName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_Group1">
<GroupExpressions>
<GroupExpression>=Fields!CategoryName.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!CategoryName.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
</TableGroup>
</TableGroups>
<Left>3.25in</Left>
<TableColumns>
<TableColumn>
<Width>0.25in</Width>
</TableColumn>
<TableColumn>
<Width>1.75in</Width>
</TableColumn>
</TableColumns>
</Table>
<Chart Name="chart1">
<ThreeDProperties>
<Rotation>30</Rotation>
<Inclination>30</Inclination>
<Shading>Simple</Shading>
<WallThickness>50</WallThickness>
</ThreeDProperties>
<Style>
<BackgroundColor>White</BackgroundColor>
</Style>
<Legend>
<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>
<BackgroundColor>=Code.GetColor(Fields!CategoryName.Value)</BackgroundColor>
</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>
<Min>0</Min>
</Axis>
</CategoryAxis>
<DataSetName>DataSet1</DataSetName>
<PointWidth>0</PointWidth>
<Type>Bar</Type>
<Top>0.25in</Top>
<Title />
<Width>3in</Width>
<Height>2.5in</Height>
<SeriesGroupings>
<SeriesGrouping>
<DynamicSeries>
<Grouping Name="chart1_SeriesGroup1">
<GroupExpressions>
<GroupExpression>=Fields!CategoryName.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!CategoryName.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Label>=Fields!CategoryName.Value</Label>
</DynamicSeries>
</SeriesGrouping>
</SeriesGroupings>
<Subtype>Plain</Subtype>
<PlotArea>
<Style>
<BackgroundColor>LightGrey</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</PlotArea>
<Left>0.125in</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>
<MajorTickMarks>Outside</MajorTickMarks>
<Min>0</Min>
<Margin>true</Margin>
<Visible>true</Visible>
<Scalar>true</Scalar>
</Axis>
</ValueAxis>
</Chart>
</ReportItems>
<Style />
<Height>3.25in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>f646058b-4784-494d-a09d-27fde13a5679</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=(local);initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Code> Private Shared colorPalette As String() = {"Green", "Blue",
"Red", "Orange", "Aqua", "Teal", "Gold", "RoyalBlue", "#A59D93", "#B8341B",
"#352F26", "#F1E7D6", "#E16C56", "#CFBA9B"}
Private Shared count As Integer = 0
Private Shared mapping As New System.Collections.Hashtable()
Public Function GetColor(ByVal groupingValue As String) As String
If mapping.ContainsKey(groupingValue) Then
Return mapping(groupingValue)
End If
Dim c As String = colorPalette(count Mod colorPalette.Length)
count = count + 1
mapping.Add(groupingValue, c)
Return c
End Function
</Code>
<Width>6.5in</Width>
<DataSets>
<DataSet Name="DataSet1">
<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="Country">
<DataField>Country</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT Products.*, Categories.CategoryName,
Suppliers.Country
FROM Products INNER JOIN
Categories ON Products.CategoryID =Categories.CategoryID INNER JOIN
Suppliers ON Products.SupplierID =Suppliers.SupplierID</CommandText>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>e5564b0f-4fab-4cf7-9cef-bda726589920</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>

character sets in SQL server

Hello!
My client has a need to be able to store Japanese characters in their
PeopleSoft database. So we need to change the character set from from
Latin1_General (1252) to Japanese character set (932) on SQL Server
2000 Enterprise. I have 2 questions:

1) I would like to know if in SQL Server, the character set is machine
specific or is it defined at the database instance level?
2) Can multiple installs of SQL Server co-exist on a single server with
different default character sets?

Thanks

VishalVishal (vverma2@.gmail.com) writes:
> My client has a need to be able to store Japanese characters in their
> PeopleSoft database. So we need to change the character set from from
> Latin1_General (1252) to Japanese character set (932) on SQL Server
> 2000 Enterprise. I have 2 questions:
> 1) I would like to know if in SQL Server, the character set is machine
> specific or is it defined at the database instance level?
> 2) Can multiple installs of SQL Server co-exist on a single server with
> different default character sets?

First, you did not say which version of SQL Server you are using. The
answer is not same for SQL 7 and SQL 2000.

On SQL 7, you can only have one single sortorder - and a sortorder implies
a character set - on the server.

On SQL 2000, you can have multiple collations - as the terminolgy is on
SQL 2000 - on the same same server. In fact, every column have its own
collation. Thus, in theory you could create the PeopleSoft database to
use a suitable collation. Problem is if Peoplesoft does make use of
temp tables, in which case you will get collation conflicts when you
join temp tables with regular tables. Use of temp tables is quite common...

So in practice you is likely to have to change the collation for tempdb,
and that means that you have to rebuild the master database. (And this is
about as close as reinstallation of SQL Server you can come.) If you are
setting up a new server for PeopleSoft, this is not much of an issue anyway.

If you have several instances of SQL Server running on the same box,
they can use completely different collations, they are entirely
independent of each other.

Now, whether PeopleSoft supports the Japanese stuff, I don't know, but
I assume you've sorted that out with PeopleSoft already.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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

character sets in SQL server

Hello!
My client has a need to be able to store Japanese characters in their
PeopleSoft database. So we need to change the character set from from
Latin1_General (1252) to Japanese character set (932) on SQL Server
2000 Enterprise. I have 2 questions:
1) I would like to know if in SQL Server, the character set is machine
specific or is it defined at the database instance level?
2) Can multiple installs of SQL Server co-exist on a single server with
different default character sets?
Thanks
Vishal
Vishal (vverma2@.gmail.com) writes:
> My client has a need to be able to store Japanese characters in their
> PeopleSoft database. So we need to change the character set from from
> Latin1_General (1252) to Japanese character set (932) on SQL Server
> 2000 Enterprise. I have 2 questions:
> 1) I would like to know if in SQL Server, the character set is machine
> specific or is it defined at the database instance level?
> 2) Can multiple installs of SQL Server co-exist on a single server with
> different default character sets?
First, you did not say which version of SQL Server you are using. The
answer is not same for SQL 7 and SQL 2000.
On SQL 7, you can only have one single sortorder - and a sortorder implies
a character set - on the server.
On SQL 2000, you can have multiple collations - as the terminolgy is on
SQL 2000 - on the same same server. In fact, every column have its own
collation. Thus, in theory you could create the PeopleSoft database to
use a suitable collation. Problem is if Peoplesoft does make use of
temp tables, in which case you will get collation conflicts when you
join temp tables with regular tables. Use of temp tables is quite common...
So in practice you is likely to have to change the collation for tempdb,
and that means that you have to rebuild the master database. (And this is
about as close as reinstallation of SQL Server you can come.) If you are
setting up a new server for PeopleSoft, this is not much of an issue anyway.
If you have several instances of SQL Server running on the same box,
they can use completely different collations, they are entirely
independent of each other.
Now, whether PeopleSoft supports the Japanese stuff, I don't know, but
I assume you've sorted that out with PeopleSoft already.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp