Showing posts with label row. Show all posts
Showing posts with label row. Show all posts

Friday, March 23, 2012

It may need to be a trigger

Hi,

I needed to perform an update of a database table row when another table gets an insert.

Would I use a trigger to do this?, If so, how?

I will need to pass two of the feilds just inserted to perform the update on the second table.

Yes, you can use a trigger to do that.
Posted by debrisuioh at 5:07 AM 0 comments
Labels: , , , , , , , , , , , ,

It is possible to hide/show a particular row or column?

Say, an original report displays
---
Supplier
Product 1
name
Price
...
Product 1
name
Price
...
---
Then our admin might want to hide price column so it will look like
---
Supplier
Product 1
name
...
Product 1
name
...
---
and then convert it into excel that doesn't show price.
or likewise the admin might not want name as well, so remove it from that
report.
Can it be done?
Thanks a lotHiddie,
hiding or displaying data can be controlled by the visibility property; at
the row or column level.
"Hiddie" wrote:
> Say, an original report displays
> ---
> Supplier
> Product 1
> name
> Price
> ...
> Product 1
> name
> Price
> ...
> ---
> Then our admin might want to hide price column so it will look like
> ---
> Supplier
> Product 1
> name
> ...
> Product 1
> name
> ...
> ---
> and then convert it into excel that doesn't show price.
> or likewise the admin might not want name as well, so remove it from that
> report.
> Can it be done?
> Thanks a lot
>
>|||The attached report demonstrates what you need to accomplish. Note that the
toggle is placed on the rows and not on the table cells.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hiddie" <Hiddie@.discussions.microsoft.com> wrote in message
news:B83ACC6E-C1CB-42FC-93C5-A85FEFC81051@.microsoft.com...
> Say, an original report displays
> ---
> Supplier
> Product 1
> name
> Price
> ...
> Product 1
> name
> Price
> ...
> ---
> Then our admin might want to hide price column so it will look like
> ---
> Supplier
> Product 1
> name
> ...
> Product 1
> name
> ...
> ---
> and then convert it into excel that doesn't show price.
> or likewise the admin might not want name as well, so remove it from that
> report.
> Can it be done?
> Thanks a lot
>
>
BasicShowHide.rdl
================================================================================
<?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>
<Table Name="table1">
<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>23</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Supplier Name</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>22</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Category Name</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>21</ZIndex>
<rd:DefaultName>textbox3</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Product Name</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>14</ZIndex>
<rd:DefaultName>textbox4</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>13</ZIndex>
<rd:DefaultName>textbox5</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="ProductName">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>12</ZIndex>
<rd:DefaultName>ProductName</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!ProductName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Visibility>
<ToggleItem>CategoryName</ToggleItem>
<Hidden>true</Hidden>
</Visibility>
</TableRow>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>textbox6</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox10">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>textbox10</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="UnitPrice_1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>UnitPrice_1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!UnitPrice.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Visibility>
<ToggleItem>CategoryName</ToggleItem>
<Hidden>true</Hidden>
</Visibility>
</TableRow>
</TableRows>
</Details>
<DataSetName>DataSet1</DataSetName>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="SupplierName">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>20</ZIndex>
<rd:DefaultName>SupplierName</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!SupplierName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox11">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>19</ZIndex>
<rd:DefaultName>textbox11</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox12">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>18</ZIndex>
<rd:DefaultName>textbox12</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Header>
<Grouping Name="table1_Group1">
<GroupExpressions>
<GroupExpression>=Fields!SupplierName.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Footer>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox13">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>8</ZIndex>
<rd:DefaultName>textbox13</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox14">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>7</ZIndex>
<rd:DefaultName>textbox14</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox15">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>6</ZIndex>
<rd:DefaultName>textbox15</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Footer>
</TableGroup>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox16">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>17</ZIndex>
<rd:DefaultName>textbox16</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="CategoryName">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>16</ZIndex>
<rd:DefaultName>CategoryName</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!CategoryName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox18">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>15</ZIndex>
<rd:DefaultName>textbox18</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Visibility>
<ToggleItem>SupplierName</ToggleItem>
<Hidden>true</Hidden>
</Visibility>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Header>
<Grouping Name="table1_Group2">
<GroupExpressions>
<GroupExpression>=Fields!CategoryName.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Footer>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox19">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>textbox19</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox20">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox20</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox17">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox17</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Footer>
</TableGroup>
</TableGroups>
<Footer>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>11</ZIndex>
<rd:DefaultName>textbox7</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox8">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>10</ZIndex>
<rd:DefaultName>textbox8</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox9">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>9</ZIndex>
<rd:DefaultName>textbox9</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Footer>
<TableColumns>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Style />
<Height>2in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>cdeea17b-1207-41bf-922f-56809605e672</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=localhost;initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>6.5in</Width>
<DataSets>
<DataSet Name="DataSet1">
<Fields>
<Field Name="SupplierName">
<DataField>SupplierName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="CategoryName">
<DataField>CategoryName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ProductName">
<DataField>ProductName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="UnitPrice">
<DataField>UnitPrice</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT Suppliers.CompanyName AS SupplierName,
Categories.CategoryName, Products.ProductName, Products.UnitPrice
FROM Products INNER JOIN
Suppliers ON Products.SupplierID =Suppliers.SupplierID INNER JOIN
Categories ON Products.CategoryID =Categories.CategoryID</CommandText>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>f63998f3-88b8-4c34-a16e-364595d1d7a8</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>|||If I export your sample report to excel, the toggling does not work properly.
On the lowest level the unit prices are not shown if this category level was
collapsed in web before export. Furthermore if the category level was elapsed
before export, the toggling is per product name and not per category and the
unit prices don't become hidden.
Is this a bug?
Thanks for any help!
M. Peyer
"Bruce Johnson [MSFT]" wrote:
> The attached report demonstrates what you need to accomplish. Note that the
> toggle is placed on the rows and not on the table cells.
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Hiddie" <Hiddie@.discussions.microsoft.com> wrote in message
> news:B83ACC6E-C1CB-42FC-93C5-A85FEFC81051@.microsoft.com...
> > Say, an original report displays
> >
> > ---
> > Supplier
> > Product 1
> > name
> > Price
> > ...
> > Product 1
> > name
> > Price
> > ...
> > ---
> >
> > Then our admin might want to hide price column so it will look like
> >
> > ---
> > Supplier
> > Product 1
> > name
> > ...
> > Product 1
> > name
> > ...
> > ---
> >
> > and then convert it into excel that doesn't show price.
> > or likewise the admin might not want name as well, so remove it from that
> > report.
> > Can it be done?
> >
> > Thanks a lot
> >
> >
> >
> >
>
> BasicShowHide.rdl
> ================================================================================> <?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>
> <Table Name="table1">
> <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>23</ZIndex>
> <rd:DefaultName>textbox1</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>Supplier Name</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox2">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>22</ZIndex>
> <rd:DefaultName>textbox2</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>Category Name</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox3">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>21</ZIndex>
> <rd:DefaultName>textbox3</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>Product Name</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> </Header>
> <Details>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox4">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>14</ZIndex>
> <rd:DefaultName>textbox4</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox5">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>13</ZIndex>
> <rd:DefaultName>textbox5</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="ProductName">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>12</ZIndex>
> <rd:DefaultName>ProductName</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!ProductName.Value</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> <Visibility>
> <ToggleItem>CategoryName</ToggleItem>
> <Hidden>true</Hidden>
> </Visibility>
> </TableRow>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox6">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>2</ZIndex>
> <rd:DefaultName>textbox6</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox10">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>1</ZIndex>
> <rd:DefaultName>textbox10</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="UnitPrice_1">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <TextAlign>Right</TextAlign>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <rd:DefaultName>UnitPrice_1</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!UnitPrice.Value</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> <Visibility>
> <ToggleItem>CategoryName</ToggleItem>
> <Hidden>true</Hidden>
> </Visibility>
> </TableRow>
> </TableRows>
> </Details>
> <DataSetName>DataSet1</DataSetName>
> <TableGroups>
> <TableGroup>
> <Header>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="SupplierName">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>20</ZIndex>
> <rd:DefaultName>SupplierName</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!SupplierName.Value</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox11">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>19</ZIndex>
> <rd:DefaultName>textbox11</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox12">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>18</ZIndex>
> <rd:DefaultName>textbox12</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> <RepeatOnNewPage>true</RepeatOnNewPage>
> </Header>
> <Grouping Name="table1_Group1">
> <GroupExpressions>
> <GroupExpression>=Fields!SupplierName.Value</GroupExpression>
> </GroupExpressions>
> </Grouping>
> <Footer>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox13">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>8</ZIndex>
> <rd:DefaultName>textbox13</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox14">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>7</ZIndex>
> <rd:DefaultName>textbox14</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox15">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>6</ZIndex>
> <rd:DefaultName>textbox15</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> </Footer>
> </TableGroup>
> <TableGroup>
> <Header>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox16">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>17</ZIndex>
> <rd:DefaultName>textbox16</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="CategoryName">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>16</ZIndex>
> <rd:DefaultName>CategoryName</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!CategoryName.Value</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox18">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>15</ZIndex>
> <rd:DefaultName>textbox18</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> <Visibility>
> <ToggleItem>SupplierName</ToggleItem>
> <Hidden>true</Hidden>
> </Visibility>
> </TableRow>
> </TableRows>
> <RepeatOnNewPage>true</RepeatOnNewPage>
> </Header>
> <Grouping Name="table1_Group2">
> <GroupExpressions>
> <GroupExpression>=Fields!CategoryName.Value</GroupExpression>
> </GroupExpressions>
> </Grouping>
> <Footer>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox19">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>5</ZIndex>
> <rd:DefaultName>textbox19</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox20">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>4</ZIndex>
> <rd:DefaultName>textbox20</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox17">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>3</ZIndex>
> <rd:DefaultName>textbox17</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> </Footer>
> </TableGroup>
> </TableGroups>
> <Footer>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox7">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>11</ZIndex>
> <rd:DefaultName>textbox7</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox8">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>10</ZIndex>
> <rd:DefaultName>textbox8</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox9">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>9</ZIndex>
> <rd:DefaultName>textbox9</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> </Footer>
> <TableColumns>
> <TableColumn>
> <Width>2.16667in</Width>
> </TableColumn>
> <TableColumn>
> <Width>2.16667in</Width>
> </TableColumn>
> <TableColumn>
> <Width>2.16667in</Width>
> </TableColumn>
> </TableColumns>
> </Table>
> </ReportItems>
> <Style />
> <Height>2in</Height>
> </Body>
> <TopMargin>1in</TopMargin>
> <DataSources>
> <DataSource Name="Northwind">
> <rd:DataSourceID>cdeea17b-1207-41bf-922f-56809605e672</rd:DataSourceID>
> <ConnectionProperties>
> <DataProvider>SQL</DataProvider>
> <ConnectString>data source=localhost;initial
> catalog=Northwind</ConnectString>
> <IntegratedSecurity>true</IntegratedSecurity>
> </ConnectionProperties>
> </DataSource>
> </DataSources>
> <Width>6.5in</Width>
> <DataSets>
> <DataSet Name="DataSet1">
> <Fields>
> <Field Name="SupplierName">
> <DataField>SupplierName</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="CategoryName">
> <DataField>CategoryName</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="ProductName">
> <DataField>ProductName</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="UnitPrice">
> <DataField>UnitPrice</DataField>
> <rd:TypeName>System.Decimal</rd:TypeName>
> </Field>
> </Fields>
> <Query>
> <DataSourceName>Northwind</DataSourceName>
> <CommandText>SELECT Suppliers.CompanyName AS SupplierName,
> Categories.CategoryName, Products.ProductName, Products.UnitPrice
> FROM Products INNER JOIN
> Suppliers ON Products.SupplierID => Suppliers.SupplierID INNER JOIN
> Categories ON Products.CategoryID => Categories.CategoryID</CommandText>
> </Query>
> </DataSet>
> </DataSets>
> <LeftMargin>1in</LeftMargin>
> <rd:SnapToGrid>true</rd:SnapToGrid>
> <rd:DrawGrid>true</rd:DrawGrid>
> <rd:ReportID>f63998f3-88b8-4c34-a16e-364595d1d7a8</rd:ReportID>
> <BottomMargin>1in</BottomMargin>
> <Language>en-US</Language>
> </Report>
>
>
Posted by debrisuioh at 5:03 AM 0 comments
Labels: , , , , , , , , , , , , , , , ,

Issuing multiple calls to SET IDENTITY_INSERT ON

My table's primary key is an identity column.
When I delete a row, I first copy it to another table and afterwards delete
it
from the original table.
When I want to restore the row, I use the SET IDENTITY_INSERT statement, in
order to avoid getting a new value for the identity column.
The only problem is when there are two clients trying to restore rows at the
same time - which causes an error, since the SET IDENTITY_INERT ON statement
can only be issued on one table at a time.
What can I do to fix this problem?Amir Shitrit wrote:
> My table's primary key is an identity column.
> When I delete a row, I first copy it to another table and afterwards
> delete it from the original table.
> When I want to restore the row, I use the SET IDENTITY_INSERT
> statement, in order to avoid getting a new value for the identity
> column.
> The only problem is when there are two clients trying to restore rows
> at the same time - which causes an error, since the SET
> IDENTITY_INERT ON statement can only be issued on one table at a time.
> What can I do to fix this problem?
SET IDENTITY_INSERT can be used by multiple sessions, even on the same
table, without a problem. Are you possibly running a web application
that is using pooled connections and both clients are getting the same
connection? If you're sure each client is using a different connection,
then there should be no problem.
If you are using the same connection, you may want to spawn temporary,
new connections when a restore operation occurs and close them once the
restore is complete.
David Gugick
Imceda Software
www.imceda.com|||Get rid of the IDENTITY Column and come up with a better way of assigning
keys to your rows.
"Amir Shitrit" <AmirShitrit@.discussions.microsoft.com> wrote in message
news:C400EE3C-E209-45F6-809F-CBB33DBC5FAE@.microsoft.com...
> My table's primary key is an identity column.
> When I delete a row, I first copy it to another table and afterwards
> delete it
> from the original table.
> When I want to restore the row, I use the SET IDENTITY_INSERT statement,
> in
> order to avoid getting a new value for the identity column.
> The only problem is when there are two clients trying to restore rows at
> the
> same time - which causes an error, since the SET IDENTITY_INERT ON
> statement
> can only be issued on one table at a time.
> What can I do to fix this problem?|||Hi Amir
Use Row level locks before inserting a row into the Table.
best regards
Chandra
"Amir Shitrit" <AmirShitrit@.discussions.microsoft.com> wrote in message
news:C400EE3C-E209-45F6-809F-CBB33DBC5FAE@.microsoft.com...
> My table's primary key is an identity column.
> When I delete a row, I first copy it to another table and afterwards
delete it
> from the original table.
> When I want to restore the row, I use the SET IDENTITY_INSERT statement,
in
> order to avoid getting a new value for the identity column.
> The only problem is when there are two clients trying to restore rows at
the
> same time - which causes an error, since the SET IDENTITY_INERT ON
statement
> can only be issued on one table at a time.
> What can I do to fix this problem?|||> When I delete a row, I first copy it to another table and afterwards
> delete it
> from the original table.
> When I want to restore the row, I use the SET IDENTITY_INSERT statement,
> in
> order to avoid getting a new value for the identity column.
This makes no sense to me. Why not just assign a new IDENTITY value, since
you apparently don't have dependent rows referencing the data in the
original table?
IDENTITY should be used only as an artificial key - if you care about what
the value is then don't use IDENTITY.
David Portas
SQL Server MVP
--|||"David Portas" wrote:

> This makes no sense to me. Why not just assign a new IDENTITY value, since
> you apparently don't have dependent rows referencing the data in the
> original table?
> IDENTITY should be used only as an artificial key - if you care about what
> the value is then don't use IDENTITY.
> --
> David Portas
> SQL Server MVP
> --
Well, I do have related records, thus I can't insert the row with a new
IDENTITY if I want to keep the relations.
It goes like this: I have a table full of Customers rows and another table
with CustomersReports rows (which is a child table of the Customers table).
When I delete a customer, I don't really delete it, but rather move it to an
archive table along with it's related CustomersReports child rows.
In another scenario, I might want to restore the Customer row to it's
original table, and restore it's related CustomersReports rows as well.
If I will restore the customer by assigning it a new ID, I will be compelled
to modify the foreign key in the child table as well.
I prefer to avoid it if possible.|||Add a CHAR(1) column called "Archive" and set it to 'Y' or 'N'. Adjust your
queries to include only Archive = 'Y'. The way you're doing it now, you're
leaving a lot of orphaned rows in related tables. From what you've
explained you don't even have Foreign Key constraints set up on these
tables, and won't be able to apply them at any point because of the manner
in which you've set this up.
"Amir Shitrit" <AmirShitrit@.discussions.microsoft.com> wrote in message
news:8578B5F3-08BF-4AB1-A950-8F5BE57AD138@.microsoft.com...
>
> "David Portas" wrote:
>
> Well, I do have related records, thus I can't insert the row with a new
> IDENTITY if I want to keep the relations.
> It goes like this: I have a table full of Customers rows and another table
> with CustomersReports rows (which is a child table of the Customers
> table).
> When I delete a customer, I don't really delete it, but rather move it to
> an
> archive table along with it's related CustomersReports child rows.
> In another scenario, I might want to restore the Customer row to it's
> original table, and restore it's related CustomersReports rows as well.
> If I will restore the customer by assigning it a new ID, I will be
> compelled
> to modify the foreign key in the child table as well.
> I prefer to avoid it if possible.|||"Michael C#" wrote:

> Add a CHAR(1) column called "Archive" and set it to 'Y' or 'N'. Adjust yo
ur
> queries to include only Archive = 'Y'. The way you're doing it now, you'r
e
> leaving a lot of orphaned rows in related tables. From what you've
> explained you don't even have Foreign Key constraints set up on these
> tables, and won't be able to apply them at any point because of the manner
> in which you've set this up.
> "Amir Shitrit" <AmirShitrit@.discussions.microsoft.com> wrote in message
> news:8578B5F3-08BF-4AB1-A950-8F5BE57AD138@.microsoft.com...
>
Hi.
I actually do have foreign key constrains, and when I'm moving a row to the
archive, I move all of it's related child rows as well (as I explaind before
).
Besides, Adding a column to the original table costs alot more than moving
rows to the archive - both in memory resources and performence.
Managing the table this way is also eazyer.
Thanks anyway.|||"Amir Shitrit" <AmirShitrit@.discussions.microsoft.com> wrote in message
news:2EC17FAD-43E6-41E8-9F6B-F5CACF4912FC@.microsoft.com...
> Hi.
> I actually do have foreign key constrains, and when I'm moving a row to
> the
> archive, I move all of it's related child rows as well (as I explaind
> before).
> Besides, Adding a column to the original table costs alot more than moving
> rows to the archive - both in memory resources and performence.
> Managing the table this way is also eazyer.
> Thanks anyway.
I missed your second post that explains how you're also keeping duplicates
of all your other tables as well to hold copies of your records.
I'm interested in learning more about how adding a CHAR(1) column to a
single table is much less efficient than maintaining and administering a
complete duplicate schema and writing additional code to move rows from one
schema to the other each time you want to eliminate them from your reports.
I'm a little surprised you find it "easier" to implement code that does
this:
INSERT INTO copy_of_schema_table1
SELECT * FROM real_schema_table1
WHERE MainID = 100
INSERT INTO copy_of_schema_table2
SELECT * FROM real_schema_table2
WHERE MainID = 100
--repeat for each table...
DELETE FROM real_schema_table2
WHERE MainID = 100
DELETE FROM real_schema_table1
WHERE MainID = 100
--repeat for each table...
All this to archive One set of related rows. Ahhh, probably better wrap all
of these INSERTs and DELETEs into a single transaction, so you don't end up
with out-of-sync schemas. Oh yeah, can't forget the IDENTITY_INSERT
statements. And it's a 'simple' matter of doing the reverse to "un-archive"
it. Yet something like this is 'inefficient'?
UPDATE schema_table1
SET Archive = 'Y'
WHERE MainID = 100
To "archive" a record, and
UPDATE schema_table1
SET Archive = 'N'
WHERE MainID = 100
To "un-archive" it.
Wow. As they say, to each his own, and whatever you find most clever.|||> Well, I do have related records, thus I can't insert the row with a new
> IDENTITY if I want to keep the relations.
In fact it should be easy to do this. See the example below. However, I
entirely agree with Michael. It's unnecessary and inefficient to move data
around in this way. Copying data from one table to another is a lot more
expensive than adding a one-byte column by any measure that I can think of.
CREATE TABLE Customers (cust_id INTEGER IDENTITY PRIMARY KEY, cust_name
VARCHAR(50) NOT NULL UNIQUE /* Note the alternate key */ )
CREATE TABLE CustomerReports (..., cust_id INTEGER REFERENCES Customrers
(cust_id), ...)
INSERT INTO Customers (cust_name, ...)
SELECT cust_name,
FROM CustomersArchive
WHERE ...
INSERT INTO CustomerReports (cust_id, ... /* other columns */)
SELECT C.cust_id, R. ... /* other columns */
FROM CustomerReportsArchive AS R
JOIN CustomersArchive AS A
ON R.cust_id = A.cust_id
JOIN Customers AS C
ON A.cust_name = C.cust_name
David Portas
SQL Server MVP
--
Posted by debrisuioh at 4:54 AM 0 comments
Labels: , , , , , , , , , , , , , , , , , , ,

Issuing multiple calls to SET IDENTITY_INSERT ON

My table's primary key is an identity column.
When I delete a row, I first copy it to another table and afterwards delete it
from the original table.
When I want to restore the row, I use the SET IDENTITY_INSERT statement, in
order to avoid getting a new value for the identity column.
The only problem is when there are two clients trying to restore rows at the
same time - which causes an error, since the SET IDENTITY_INERT ON statement
can only be issued on one table at a time.
What can I do to fix this problem?Amir Shitrit wrote:
> My table's primary key is an identity column.
> When I delete a row, I first copy it to another table and afterwards
> delete it from the original table.
> When I want to restore the row, I use the SET IDENTITY_INSERT
> statement, in order to avoid getting a new value for the identity
> column.
> The only problem is when there are two clients trying to restore rows
> at the same time - which causes an error, since the SET
> IDENTITY_INERT ON statement can only be issued on one table at a time.
> What can I do to fix this problem?
SET IDENTITY_INSERT can be used by multiple sessions, even on the same
table, without a problem. Are you possibly running a web application
that is using pooled connections and both clients are getting the same
connection? If you're sure each client is using a different connection,
then there should be no problem.
If you are using the same connection, you may want to spawn temporary,
new connections when a restore operation occurs and close them once the
restore is complete.
David Gugick
Imceda Software
www.imceda.com|||Get rid of the IDENTITY Column and come up with a better way of assigning
keys to your rows.
"Amir Shitrit" <AmirShitrit@.discussions.microsoft.com> wrote in message
news:C400EE3C-E209-45F6-809F-CBB33DBC5FAE@.microsoft.com...
> My table's primary key is an identity column.
> When I delete a row, I first copy it to another table and afterwards
> delete it
> from the original table.
> When I want to restore the row, I use the SET IDENTITY_INSERT statement,
> in
> order to avoid getting a new value for the identity column.
> The only problem is when there are two clients trying to restore rows at
> the
> same time - which causes an error, since the SET IDENTITY_INERT ON
> statement
> can only be issued on one table at a time.
> What can I do to fix this problem?|||> When I delete a row, I first copy it to another table and afterwards
> delete it
> from the original table.
> When I want to restore the row, I use the SET IDENTITY_INSERT statement,
> in
> order to avoid getting a new value for the identity column.
This makes no sense to me. Why not just assign a new IDENTITY value, since
you apparently don't have dependent rows referencing the data in the
original table?
IDENTITY should be used only as an artificial key - if you care about what
the value is then don't use IDENTITY.
--
David Portas
SQL Server MVP
--|||"David Portas" wrote:
> > When I delete a row, I first copy it to another table and afterwards
> > delete it
> > from the original table.
> > When I want to restore the row, I use the SET IDENTITY_INSERT statement,
> > in
> > order to avoid getting a new value for the identity column.
> This makes no sense to me. Why not just assign a new IDENTITY value, since
> you apparently don't have dependent rows referencing the data in the
> original table?
> IDENTITY should be used only as an artificial key - if you care about what
> the value is then don't use IDENTITY.
> --
> David Portas
> SQL Server MVP
> --
Well, I do have related records, thus I can't insert the row with a new
IDENTITY if I want to keep the relations.
It goes like this: I have a table full of Customers rows and another table
with CustomersReports rows (which is a child table of the Customers table).
When I delete a customer, I don't really delete it, but rather move it to an
archive table along with it's related CustomersReports child rows.
In another scenario, I might want to restore the Customer row to it's
original table, and restore it's related CustomersReports rows as well.
If I will restore the customer by assigning it a new ID, I will be compelled
to modify the foreign key in the child table as well.
I prefer to avoid it if possible.|||Add a CHAR(1) column called "Archive" and set it to 'Y' or 'N'. Adjust your
queries to include only Archive = 'Y'. The way you're doing it now, you're
leaving a lot of orphaned rows in related tables. From what you've
explained you don't even have Foreign Key constraints set up on these
tables, and won't be able to apply them at any point because of the manner
in which you've set this up.
"Amir Shitrit" <AmirShitrit@.discussions.microsoft.com> wrote in message
news:8578B5F3-08BF-4AB1-A950-8F5BE57AD138@.microsoft.com...
>
> "David Portas" wrote:
>> > When I delete a row, I first copy it to another table and afterwards
>> > delete it
>> > from the original table.
>> > When I want to restore the row, I use the SET IDENTITY_INSERT
>> > statement,
>> > in
>> > order to avoid getting a new value for the identity column.
>> This makes no sense to me. Why not just assign a new IDENTITY value,
>> since
>> you apparently don't have dependent rows referencing the data in the
> > original table?
>> IDENTITY should be used only as an artificial key - if you care about
>> what
>> the value is then don't use IDENTITY.
>> --
>> David Portas
>> SQL Server MVP
>> --
> Well, I do have related records, thus I can't insert the row with a new
> IDENTITY if I want to keep the relations.
> It goes like this: I have a table full of Customers rows and another table
> with CustomersReports rows (which is a child table of the Customers
> table).
> When I delete a customer, I don't really delete it, but rather move it to
> an
> archive table along with it's related CustomersReports child rows.
> In another scenario, I might want to restore the Customer row to it's
> original table, and restore it's related CustomersReports rows as well.
> If I will restore the customer by assigning it a new ID, I will be
> compelled
> to modify the foreign key in the child table as well.
> I prefer to avoid it if possible.|||"Michael C#" wrote:
> Add a CHAR(1) column called "Archive" and set it to 'Y' or 'N'. Adjust your
> queries to include only Archive = 'Y'. The way you're doing it now, you're
> leaving a lot of orphaned rows in related tables. From what you've
> explained you don't even have Foreign Key constraints set up on these
> tables, and won't be able to apply them at any point because of the manner
> in which you've set this up.
> "Amir Shitrit" <AmirShitrit@.discussions.microsoft.com> wrote in message
> news:8578B5F3-08BF-4AB1-A950-8F5BE57AD138@.microsoft.com...
> >
> >
> > "David Portas" wrote:
> >
> >> > When I delete a row, I first copy it to another table and afterwards
> >> > delete it
> >> > from the original table.
> >> > When I want to restore the row, I use the SET IDENTITY_INSERT
> >> > statement,
> >> > in
> >> > order to avoid getting a new value for the identity column.
> >>
> >> This makes no sense to me. Why not just assign a new IDENTITY value,
> >> since
> >> you apparently don't have dependent rows referencing the data in the
> > > original table?
> >>
> >> IDENTITY should be used only as an artificial key - if you care about
> >> what
> >> the value is then don't use IDENTITY.
> >>
> >> --
> >> David Portas
> >> SQL Server MVP
> >> --
> >
> > Well, I do have related records, thus I can't insert the row with a new
> > IDENTITY if I want to keep the relations.
> > It goes like this: I have a table full of Customers rows and another table
> > with CustomersReports rows (which is a child table of the Customers
> > table).
> > When I delete a customer, I don't really delete it, but rather move it to
> > an
> > archive table along with it's related CustomersReports child rows.
> > In another scenario, I might want to restore the Customer row to it's
> > original table, and restore it's related CustomersReports rows as well.
> > If I will restore the customer by assigning it a new ID, I will be
> > compelled
> > to modify the foreign key in the child table as well.
> > I prefer to avoid it if possible.
>
Hi.
I actually do have foreign key constrains, and when I'm moving a row to the
archive, I move all of it's related child rows as well (as I explaind before).
Besides, Adding a column to the original table costs alot more than moving
rows to the archive - both in memory resources and performence.
Managing the table this way is also eazyer.
Thanks anyway.|||"Amir Shitrit" <AmirShitrit@.discussions.microsoft.com> wrote in message
news:2EC17FAD-43E6-41E8-9F6B-F5CACF4912FC@.microsoft.com...
> Hi.
> I actually do have foreign key constrains, and when I'm moving a row to
> the
> archive, I move all of it's related child rows as well (as I explaind
> before).
> Besides, Adding a column to the original table costs alot more than moving
> rows to the archive - both in memory resources and performence.
> Managing the table this way is also eazyer.
> Thanks anyway.
I missed your second post that explains how you're also keeping duplicates
of all your other tables as well to hold copies of your records.
I'm interested in learning more about how adding a CHAR(1) column to a
single table is much less efficient than maintaining and administering a
complete duplicate schema and writing additional code to move rows from one
schema to the other each time you want to eliminate them from your reports.
I'm a little surprised you find it "easier" to implement code that does
this:
INSERT INTO copy_of_schema_table1
SELECT * FROM real_schema_table1
WHERE MainID = 100
INSERT INTO copy_of_schema_table2
SELECT * FROM real_schema_table2
WHERE MainID = 100
--repeat for each table...
DELETE FROM real_schema_table2
WHERE MainID = 100
DELETE FROM real_schema_table1
WHERE MainID = 100
--repeat for each table...
All this to archive One set of related rows. Ahhh, probably better wrap all
of these INSERTs and DELETEs into a single transaction, so you don't end up
with out-of-sync schemas. Oh yeah, can't forget the IDENTITY_INSERT
statements. And it's a 'simple' matter of doing the reverse to "un-archive"
it. Yet something like this is 'inefficient'?
UPDATE schema_table1
SET Archive = 'Y'
WHERE MainID = 100
To "archive" a record, and
UPDATE schema_table1
SET Archive = 'N'
WHERE MainID = 100
To "un-archive" it.
Wow. As they say, to each his own, and whatever you find most clever.|||> Well, I do have related records, thus I can't insert the row with a new
> IDENTITY if I want to keep the relations.
In fact it should be easy to do this. See the example below. However, I
entirely agree with Michael. It's unnecessary and inefficient to move data
around in this way. Copying data from one table to another is a lot more
expensive than adding a one-byte column by any measure that I can think of.
CREATE TABLE Customers (cust_id INTEGER IDENTITY PRIMARY KEY, cust_name
VARCHAR(50) NOT NULL UNIQUE /* Note the alternate key */ )
CREATE TABLE CustomerReports (..., cust_id INTEGER REFERENCES Customrers
(cust_id), ...)
INSERT INTO Customers (cust_name, ...)
SELECT cust_name,
FROM CustomersArchive
WHERE ...
INSERT INTO CustomerReports (cust_id, ... /* other columns */)
SELECT C.cust_id, R. ... /* other columns */
FROM CustomerReportsArchive AS R
JOIN CustomersArchive AS A
ON R.cust_id = A.cust_id
JOIN Customers AS C
ON A.cust_name = C.cust_name
--
David Portas
SQL Server MVP
--|||On Sun, 08 May 2005 11:25:15 -0700, Amir Shitrit wrote:
> Hi.
> I actually do have foreign key constrains, and when I'm moving a row to the
> archive, I move all of it's related child rows as well (as I explaind before).
> Besides, Adding a column to the original table costs alot more than moving
> rows to the archive - both in memory resources and performence.
> Managing the table this way is also eazyer.
> Thanks anyway.
Although I fully agree with Michael and David - this is just a bad idea,
as far as I can see, you could do it using application locks. Like this:
CREATE PROCEDURE NeverRunsInParallel
AS
DECLARE @.result int
EXEC @.result = sp_getapplock @.Resource = 'myLock', @.LockMode = 'Exclusive'
IF @.result => 0
BEGIN
-- Do your thing, secure in the fact that
-- no other connection will do it at the same
-- time.
END
EXEC sp_releaseapplock @.Resource = 'myLock1'
<<
HTH,
Andrés Taylor
Posted by debrisuioh at 4:53 AM 0 comments
Labels: , , , , , , , , , , , , , , , , , ,

Wednesday, March 21, 2012

Issues with Parameterised Report

Hi,

I want a matrix report which displays Sales Data or Discount Data (Column) Vs Year (Row). I want to have a parameter which displays a text as 'Sales', 'Discount'.

When I Select 'Sales', the report should display 'Sales Amount' Vs 'Year' and when I select 'Discount', the report should display 'Discount Data' Vs Year. I am getting all the data from OLAP Cube.

Any help is highly appreciated.

Thanks,

S Suresh

Create a string type parameter (say Parameter1) and have Available Values to be "Sales" and "Discount" (Non-queried)

In your matrix grouping (row or column), type the following expression:

IIf(Parameters!Parameter1.Value="Sales", Fields!Sales.Value, Fields!Discount.Value)

Shyam

Posted by debrisuioh at 6:33 AM 0 comments
Labels: , , , , , , , , , , , , , , ,

Monday, March 19, 2012

Issues PLEASE HELP

i am running the following and i keep getting process errors " can not read next data row for data set SERVER. transaction process id 838 was deadlocked on lock comminication buffer resources with another process and has been chosen as the deadlock victum rerun transactions."

SELECT transactions.practice_id, practice.practice_name, CONVERT(smalldatetime, transactions.closing_date) AS Date, transactions.type,

ISNULL(SUM(trans_detail.paid_amt), 0) AS Pmts, ISNULL(SUM(trans_detail.adj_amt), 0) AS Adjs, 0 AS NSF, transactions.tran_code_id, 0 AS Debits,

0 AS Credits, 0 AS Capitation, 0 AS UnAppl, trans_detail.system_charge_code,''

FROM provider_mstr RIGHT OUTER JOIN

charges ON provider_mstr.provider_id = charges.rendering_id LEFT OUTER JOIN

location_mstr ON charges.location_id = location_mstr.location_id RIGHT OUTER JOIN

transactions INNER JOIN

trans_detail ON transactions.trans_id = trans_detail.trans_id INNER JOIN

practice ON transactions.practice_id = practice.practice_id ON charges.charge_id = trans_detail.charge_id

where (CONVERT(smalldatetime, transactions.closing_date) BETWEEN @.begin_date AND @.end_date) and (transactions.practice_id = @.practice_id) and (convert(char(36), charges.rendering_id) like @.provider_id) and (convert(char(36), charges.location_id) like @.location_id)

GROUP BY transactions.practice_id, CONVERT(smalldatetime, transactions.closing_date), transactions.type, practice.practice_name, transactions.tran_code_id,

trans_detail.system_charge_code

HAVING (transactions.type = 'C' OR transactions.type = 'R')

AND transactions.tran_code_id NOT IN ('14759070-3370-4E4D-B752-01AE84A350EF', '23783F6F-8661-4785-922C-F55C154349F9', '36037DC4-4799-4E84-9AAB-78BE4F271CCD', '5F1665EE-08DC-47FF-8BC4-6F4BA6CC84C0',

'39137DD5-796F-4824-860B-EBD00D66CBB0') AND (trans_detail.system_charge_code is null)

UNION

SELECT tr.practice_id, pr.practice_name, CONVERT(smalldatetime, tr.closing_date) AS Date, tr.type, 0 AS Pmts, 0 AS Adjs, ISNULL(SUM(td.adj_amt), 0) AS NSF,

tr.tran_code_id, 0, 0, 0, 0, '',''

FROM provider_mstr pm1 RIGHT OUTER JOIN

charges c1 ON pm1.provider_id = c1.rendering_id LEFT OUTER JOIN

location_mstr lm1 ON c1.location_id = lm1.location_id RIGHT OUTER JOIN

transactions tr INNER JOIN

trans_detail td ON tr.trans_id = td.trans_id INNER JOIN

practice pr ON tr.practice_id = pr.practice_id ON c1.charge_id = td.charge_id

where (CONVERT(smalldatetime, tr.closing_date) BETWEEN @.begin_date AND @.end_date) AND (convert(char(36), c1.rendering_id) like @.provider_id) and (convert(char(36), c1.location_id) like @.location_id)

GROUP BY tr.practice_id, CONVERT(smalldatetime, tr.closing_date), tr.type, pr.practice_name, tr.tran_code_id

HAVING (tr.practice_id = @.practice_id) AND (tr.type = 'A') AND

(tr.tran_code_id = 'CEC50D49-3317-404D-A082-36610DAE6F0F')

UNION

SELECT tn.practice_id, prs.practice_name, CONVERT(smalldatetime, tn.closing_date) AS Date, tn.type, 0 AS Pmts, 0 AS Adjs, 0 AS NSF, tn.tran_code_id,

isnull(SUM(tds.paid_amt), 0) AS Debits, 0, 0, 0, '',''

FROM provider_mstr pm2 RIGHT OUTER JOIN

charges c2 ON pm2.provider_id = c2.rendering_id LEFT OUTER JOIN

location_mstr lm2 ON c2.location_id = lm2.location_id RIGHT OUTER JOIN

transactions tn INNER JOIN

trans_detail tds ON tn.trans_id = tds.trans_id INNER JOIN

practice prs ON tn.practice_id = prs.practice_id ON c2.charge_id = tds.charge_id

where (CONVERT(smalldatetime, tn.closing_date) BETWEEN @.begin_date AND @.end_date) AND (convert(char(36), c2.rendering_id) like @.provider_id) and (convert(char(36), c2.location_id) like @.location_id)

GROUP BY tn.practice_id, CONVERT(smalldatetime, tn.closing_date), tn.type, prs.practice_name, tn.tran_code_id

HAVING (tn.practice_id = @.practice_id) AND (tn.type = 'C') AND

(tn.tran_code_id = '14759070-3370-4E4D-B752-01AE84A350EF' OR

tn.tran_code_id = '23783F6F-8661-4785-922C-F55C154349F9' OR tn.tran_code_id='B6BA0D7D-BAC6-4653-9050-C5C5E8029DCB') AND isnull(SUM(tds.paid_amt), 0) > 0

UNION

SELECT trs.practice_id, ps.practice_name, CONVERT(smalldatetime, trs.closing_date) AS Date, trs.type, 0 AS Pmts, 0 AS Adjs, 0 AS NSF, trs.tran_code_id, 0,

isnull(SUM(ts .paid_amt), 0) AS Credits, 0, 0, '',''

FROM provider_mstr pm3 RIGHT OUTER JOIN

charges c3 ON pm3.provider_id = c3.rendering_id LEFT OUTER JOIN

location_mstr lm3 ON c3.location_id = lm3.location_id RIGHT OUTER JOIN

transactions trs INNER JOIN

trans_detail ts ON trs.trans_id = ts .trans_id INNER JOIN

practice ps ON trs.practice_id = ps.practice_id ON c3.charge_id = ts .charge_id

where (CONVERT(smalldatetime, trs.closing_date) BETWEEN @.begin_date AND @.end_date) AND (convert(char(36), c3.rendering_id) like @.provider_id) and (convert(char(36), c3.location_id) like @.location_id)

GROUP BY trs.practice_id, CONVERT(smalldatetime, trs.closing_date), trs.type, ps.practice_name, trs.tran_code_id

HAVING (trs.practice_id = @.practice_id) AND (trs.type = 'C') AND

(trs.tran_code_id = '14759070-3370-4E4D-B752-01AE84A350EF' OR

trs.tran_code_id = '23783F6F-8661-4785-922C-F55C154349F9' OR trs.tran_code_id='B6BA0D7D-BAC6-4653-9050-C5C5E8029DCB') AND isnull(SUM(ts .paid_amt), 0) < 0

UNION

SELECT tran1.practice_id, ps1.practice_name, CONVERT(smalldatetime, tran1.closing_date) AS Date, tran1.type, 0 AS Pmts, 0 AS Adjs, 0 AS NSF,

tran1.tran_code_id, 0, 0, isnull(SUM(ts1.paid_amt), 0), 0, '',''

FROM provider_mstr pm4 RIGHT OUTER JOIN

charges c4 ON pm4.provider_id = c4.rendering_id LEFT OUTER JOIN

location_mstr lm4 ON c4.location_id = lm4.location_id RIGHT OUTER JOIN

transactions tran1 INNER JOIN

trans_detail ts1 ON tran1.trans_id = ts1.trans_id INNER JOIN

practice ps1 ON tran1.practice_id = ps1.practice_id ON c4.charge_id = ts1.charge_id

where (CONVERT(smalldatetime, tran1.closing_date) BETWEEN @.begin_date AND @.end_date) AND (convert(char(36), c4.rendering_id) like @.provider_id) and (convert(char(36), c4.location_id) like @.location_id)

GROUP BY tran1.practice_id, CONVERT(smalldatetime, tran1.closing_date), tran1.type, ps1.practice_name, tran1.tran_code_id

HAVING (tran1.practice_id = @.practice_id) AND (tran1.type = 'C')

AND (tran1.tran_code_id IN ('36037DC4-4799-4E84-9AAB-78BE4F271CCD', '5F1665EE-08DC-47FF-8BC4-6F4BA6CC84C0', '39137DD5-796F-4824-860B-EBD00D66CBB0'))

UNION

SELECT tran2.practice_id, ps2.practice_name, CONVERT(smalldatetime, tran2.closing_date) AS Date, tran2.type, 0 AS Pmts, 0 AS Adjs, 0 AS NSF,

tran2.tran_code_id, 0, 0, 0, isnull(SUM(ts2.paid_amt), 0), ts2.system_charge_code,''

FROM provider_mstr pm5 RIGHT OUTER JOIN

charges c5 ON pm5.provider_id = c5.rendering_id LEFT OUTER JOIN

location_mstr lm5 ON c5.location_id = lm5.location_id RIGHT OUTER JOIN

transactions tran2 INNER JOIN

trans_detail ts2 ON tran2.trans_id = ts2.trans_id INNER JOIN

practice ps2 ON tran2.practice_id = ps2.practice_id ON c5.charge_id = ts2.charge_id

where (CONVERT(smalldatetime, tran2.closing_date) BETWEEN @.begin_date AND @.end_date) AND (isnull(convert(char(36), c5.rendering_id),'') like @.provider_id) and (isnull(convert(char(36), c5.location_id),'') like @.location_id)

GROUP BY tran2.practice_id, CONVERT(smalldatetime, tran2.closing_date), tran2.type, ps2.practice_name, tran2.tran_code_id, ts2.system_charge_code

HAVING (tran2.practice_id = @.practice_id) AND (tran2.type = 'C')

AND ts2.system_charge_code is not null

ORDER BY CONVERT(smalldatetime, transactions.closing_date)

Try moving all your "HAVING" clauses to WHERE clauses.

HAVING is for calculated fields. Your fields appear to be static, except for the "isnull(SUM(ts .paid_amt), 0) < 0" which will need to be in the HAVING clause.

I would suspect it is retrieving all records, multiple times, to check the having clauses, when it doesn't need too.

|||Also, try posting this to the TSQL forum, you will probably have better luck.

Posted by debrisuioh at 9:03 PM 0 comments
Labels: , , , , , , , , , , , ,
Subscribe to: Posts (Atom)