Showing posts with label property. Show all posts
Showing posts with label property. Show all posts

Thursday, March 29, 2012

Alternating BackColor

Hi
I wand to use an Alternating Backcolor in a Report for every Row. In don't
find this property.
How can i do it?
Many thanks.
AndyHi,
There is no property to do this
Here is the post replied by Bruce Johnson [MSFT]
Hope this helps
At the end of this posting are two reports that demonstrate how to alternate
row colors on a table and a matrix.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Christian Larsen" <ChristianLarsen@.discussions.microsoft.com> wrote in
message news:608EB056-430E-44A2-AD02-1621CA41FC3F@.microsoft.com...
> Hi all.
> How do i get a different color on odd rows in a table or matrix?
> /Chrsitian
TableGreenBar.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">
<Height>1in</Height>
<Style />
<Header>
<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>11</ZIndex>
<rd:DefaultName>textbox4</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Country</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>10</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Company 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>9</ZIndex>
<rd:DefaultName>textbox3</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="CompanyName">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>=iif(RowNumber(Nothing) Mod 2,
"PaleGreen", "White")</BackgroundColor>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>CompanyName</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!CompanyName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>textbox6</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Details>
<DataSetName>Northwind</DataSetName>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="Country">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>=iif(RunningValue(Fields!Country.Value,CountDistinct,Nothing)
Mod 2, "Cornsilk", "White")</BackgroundColor>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>8</ZIndex>
<rd:DefaultName>Country</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Country.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>7</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>6</ZIndex>
<rd:DefaultName>textbox12</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Header>
<Grouping Name="CountryGroup">
<GroupExpressions>
<GroupExpression>=Fields!Country.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</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>5</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>4</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>3</ZIndex>
<rd:DefaultName>textbox9</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Footer>
<TableColumns>
<TableColumn>
<Width>1.66667in</Width>
</TableColumn>
<TableColumn>
<Width>1.66667in</Width>
</TableColumn>
<TableColumn>
<Width>1.66667in</Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Style />
<Height>1.875in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>32d95cbf-5e5b-4fb3-a37a-39b9506b8c80</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=localhost;initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>5in</Width>
<DataSets>
<DataSet Name="Northwind">
<Fields>
<Field Name="CustomerID">
<DataField>CustomerID</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="CompanyName">
<DataField>CompanyName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ContactName">
<DataField>ContactName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ContactTitle">
<DataField>ContactTitle</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Address">
<DataField>Address</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="City">
<DataField>City</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Region">
<DataField>Region</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="PostalCode">
<DataField>PostalCode</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Country">
<DataField>Country</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Phone">
<DataField>Phone</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Fax">
<DataField>Fax</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT *
FROM Customers</CommandText>
<Timeout>30</Timeout>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>4792d607-5639-4c89-ac36-2794e9e78a74</rd:ReportID>
<BottomMargin>1in</BottomMargin>
</Report>
MatrixGreenBar.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>
<Matrix Name="matrix1">
<Corner>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</Corner>
<Height>0.5in</Height>
<Style />
<MatrixRows>
<MatrixRow>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="Qty">
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>=ReportItems!Color.Value</BackgroundColor>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>Qty</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!Qty.Value)</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
<Height>0.25in</Height>
</MatrixRow>
</MatrixRows>
<MatrixColumns>
<MatrixColumn>
<Width>0.875in</Width>
</MatrixColumn>
</MatrixColumns>
<DataSetName>DataSet1</DataSetName>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<Grouping Name="Category">
<GroupExpressions>
<GroupExpression>=Fields!CategoryName.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="CategoryName">
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>CategoryName</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!CategoryName.Value</Value>
</Textbox>
</ReportItems>
</DynamicColumns>
<Height>0.25in</Height>
</ColumnGrouping>
</ColumnGroupings>
<Width>2in</Width>
<Top>0.125in</Top>
<Left>0.125in</Left>
<RowGroupings>
<RowGrouping>
<DynamicRows>
<Grouping Name="Country">
<GroupExpressions>
<GroupExpression>=Fields!Country.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="Country">
<Style>
<BorderStyle>
<Default>Solid</Default>
<Right>None</Right>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>=iif(RunningValue(Fields!Country.Value,CountDistinct,Nothing)
Mod 2, "AliceBlue", "White")</BackgroundColor>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>Country</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Country.Value & " " &
RunningValue(Fields!Country.Value,CountDistinct,Nothing)</Value>
</Textbox>
</ReportItems>
</DynamicRows>
<Width>1in</Width>
</RowGrouping>
<RowGrouping>
<DynamicRows>
<Grouping Name="Count">
<GroupExpressions>
<GroupExpression>=1</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="Color">
<Style>
<BorderStyle>
<Default>Solid</Default>
<Left>None</Left>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>=Value</BackgroundColor>
<FontSize>1pt</FontSize>
<Color>=Value</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<CanGrow>true</CanGrow>
<Value>=iif(RunningValue(Fields!Country.Value,CountDistinct,Nothing)
Mod 2, "AliceBlue", "White")</Value>
</Textbox>
</ReportItems>
</DynamicRows>
<Width>0.125in</Width>
</RowGrouping>
</RowGroupings>
</Matrix>
</ReportItems>
<Style />
<Height>3.25in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>26f1bf87-1fa6-4e77-8d1a-81b0cd940403</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=.;initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Code />
<Width>6.875in</Width>
<DataSets>
<DataSet Name="DataSet1">
<Fields>
<Field Name="Country">
<DataField>Country</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Qty">
<DataField>Qty</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="CategoryName">
<DataField>CategoryName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT Customers.Country, SUM([Order
Details].Quantity) AS Qty, Categories.CategoryName
FROM Customers INNER JOIN
Orders ON Customers.CustomerID = Orders.CustomerID
INNER JOIN
[Order Details] ON Orders.OrderID = [Order
Details].OrderID INNER JOIN
Products ON [Order Details].ProductID =Products.ProductID INNER JOIN
Categories ON Products.CategoryID =Categories.CategoryID
GROUP BY Customers.Country, Categories.CategoryName</CommandText>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<Description />
<rd:ReportID>ab2c120b-3169-427d-8ad6-b8716f8c5101</rd:ReportID>
<BottomMargin>1in</BottomMargin>
</Report>
"Andreas Szabo" <Andreas.Szabo_PLEASE_INSERT_ADD_complementa.ch> wrote in
message news:O%23rS43yuEHA.2196@.TK2MSFTNGP14.phx.gbl...
> Hi
> I wand to use an Alternating Backcolor in a Report for every Row. In don't
> find this property.
> How can i do it?
> Many thanks.
> Andy
>
Posted by Maisie at 6:03 PM 0 comments
Labels: , , , , , , , , , , ,

Sunday, March 25, 2012

altering a primary key property

I need to change a primary key from clustered to nonclustered. Is there any
way to do this without completely dropping and re-adding the primary key?
There are several foreign keys throughout the database referencing this
primary key and I was hoping to make this change without having to drop all
those foreign keys and recreate them afterward.
-Joel
I'm pretty sure you have to drop it and re-add it
Greg Jackson
PDX, Oregon
|||Only way is to drop and recreate the Primary key constrain mentioning
NONCLUESTERED.
Thanks
Hari
SQL SERVER MVP
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:e2zJtLWXFHA.632@.TK2MSFTNGP14.phx.gbl...
> I'm pretty sure you have to drop it and re-add it
>
> Greg Jackson
> PDX, Oregon
>
Posted by Maisie at 7:50 PM 0 comments
Labels: , , , , , , , , , , , , ,

altering a primary key property

I need to change a primary key from clustered to nonclustered. Is there any
way to do this without completely dropping and re-adding the primary key?
There are several foreign keys throughout the database referencing this
primary key and I was hoping to make this change without having to drop all
those foreign keys and recreate them afterward.
-JoelI'm pretty sure you have to drop it and re-add it
Greg Jackson
PDX, Oregon|||Only way is to drop and recreate the Primary key constrain mentioning
NONCLUESTERED.
Thanks
Hari
SQL SERVER MVP
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:e2zJtLWXFHA.632@.TK2MSFTNGP14.phx.gbl...
> I'm pretty sure you have to drop it and re-add it
>
> Greg Jackson
> PDX, Oregon
>
Posted by Maisie at 7:50 PM 0 comments
Labels: , , , , , , , , , , , , ,

altering a primary key property

I need to change a primary key from clustered to nonclustered. Is there any
way to do this without completely dropping and re-adding the primary key?
There are several foreign keys throughout the database referencing this
primary key and I was hoping to make this change without having to drop all
those foreign keys and recreate them afterward.
-JoelI'm pretty sure you have to drop it and re-add it
Greg Jackson
PDX, Oregon|||Only way is to drop and recreate the Primary key constrain mentioning
NONCLUESTERED.
Thanks
Hari
SQL SERVER MVP
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:e2zJtLWXFHA.632@.TK2MSFTNGP14.phx.gbl...
> I'm pretty sure you have to drop it and re-add it
>
> Greg Jackson
> PDX, Oregon
>
Posted by Maisie at 7:49 PM 0 comments
Labels: , , , , , , , , , , , , ,

Monday, March 19, 2012

Alter table : identity col

I would like to add Indetntiy property to exitinng column
and would like to start it from 100. Could anybody please
give me the syntaxHi James
You have to drop the table & re-create it afaik.
Here's an example:
set nocount on
go
-- do it in a tran for safety
begin transaction
go
-- set up demo table
create table t1 (
c1 int not null primary key
, c2 char(1) not null
)
go
-- insert a demo row
insert into t1 (c1, c2) values (1, 'a')
go
-- set up a temp table with identity on the column
create table t1_temp (
c1 int not null identity (99, 1) primary key
, c2 char(1) not null
)
go
-- populate the temp table
set identity_insert t1_temp on
insert into t1_temp (c1, c2) select c1, c2 from t1
set identity_insert t1_temp off
go
-- destroy the original table
drop table t1
go
-- rename the temp table to t1
exec sp_rename 't1_temp', 't1'
go
-- insert another row to test
insert into t1 (c2) values ('b')
go
-- check results
select * from t1
go
-- clean up
rollback
go
Things might be a little more complicated if you're using schema binding for
stored procs / views & you might want to flush your proc cache too if you've
got stored procs using the table.
HTH
Regards,
Greg Linwood
SQL Server MVP
"james" <anonymous@.discussions.microsoft.com> wrote in message
news:1413101c3f7e7$c92ea810$a601280a@.phx.gbl...
> I would like to add Indetntiy property to exitinng column
> and would like to start it from 100. Could anybody please
> give me the syntax|||Greg
I think we can use the same table to add identity property
create table t
(
col int not null primary key,
col2 char(1) not null
)
go
insert into t values (1,'a')
insert into t values (2,'b')
go
alter table t add col1 int identity(1,1)
go
alter table t drop constraint PK__t__41D98783
go
alter table t drop column col
go
EXEC sp_rename 't.col1', 'col', 'COLUMN'
go
select * from t
go
drop table t
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:eGs#FA$9DHA.3488@.tk2msftngp13.phx.gbl...
> Hi James
> You have to drop the table & re-create it afaik.
> Here's an example:
> set nocount on
> go
> -- do it in a tran for safety
> begin transaction
> go
> -- set up demo table
> create table t1 (
> c1 int not null primary key
> , c2 char(1) not null
> )
> go
> -- insert a demo row
> insert into t1 (c1, c2) values (1, 'a')
> go
> -- set up a temp table with identity on the column
> create table t1_temp (
> c1 int not null identity (99, 1) primary key
> , c2 char(1) not null
> )
> go
> -- populate the temp table
> set identity_insert t1_temp on
> insert into t1_temp (c1, c2) select c1, c2 from t1
> set identity_insert t1_temp off
> go
> -- destroy the original table
> drop table t1
> go
> -- rename the temp table to t1
> exec sp_rename 't1_temp', 't1'
> go
> -- insert another row to test
> insert into t1 (c2) values ('b')
> go
> -- check results
> select * from t1
> go
> -- clean up
> rollback
> go
> Things might be a little more complicated if you're using schema binding
for
> stored procs / views & you might want to flush your proc cache too if
you've
> got stored procs using the table.
> HTH
> Regards,
> Greg Linwood
> SQL Server MVP
> "james" <anonymous@.discussions.microsoft.com> wrote in message
> news:1413101c3f7e7$c92ea810$a601280a@.phx.gbl...
> > I would like to add Indetntiy property to exitinng column
> > and would like to start it from 100. Could anybody please
> > give me the syntax
>|||Greg
Sorry, did not read OP to the end. My example doesnot resolve his problem
because he wants to start from 100.
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:eGs#FA$9DHA.3488@.tk2msftngp13.phx.gbl...
> Hi James
> You have to drop the table & re-create it afaik.
> Here's an example:
> set nocount on
> go
> -- do it in a tran for safety
> begin transaction
> go
> -- set up demo table
> create table t1 (
> c1 int not null primary key
> , c2 char(1) not null
> )
> go
> -- insert a demo row
> insert into t1 (c1, c2) values (1, 'a')
> go
> -- set up a temp table with identity on the column
> create table t1_temp (
> c1 int not null identity (99, 1) primary key
> , c2 char(1) not null
> )
> go
> -- populate the temp table
> set identity_insert t1_temp on
> insert into t1_temp (c1, c2) select c1, c2 from t1
> set identity_insert t1_temp off
> go
> -- destroy the original table
> drop table t1
> go
> -- rename the temp table to t1
> exec sp_rename 't1_temp', 't1'
> go
> -- insert another row to test
> insert into t1 (c2) values ('b')
> go
> -- check results
> select * from t1
> go
> -- clean up
> rollback
> go
> Things might be a little more complicated if you're using schema binding
for
> stored procs / views & you might want to flush your proc cache too if
you've
> got stored procs using the table.
> HTH
> Regards,
> Greg Linwood
> SQL Server MVP
> "james" <anonymous@.discussions.microsoft.com> wrote in message
> news:1413101c3f7e7$c92ea810$a601280a@.phx.gbl...
> > I would like to add Indetntiy property to exitinng column
> > and would like to start it from 100. Could anybody please
> > give me the syntax
>
Posted by Maisie at 2:48 AM 0 comments
Labels: , , , , , , , , , , , , ,

Alter table : identity col

I would like to add Indetntiy property to exitinng column
and would like to start it from 100. Could anybody please
give me the syntaxHi James
You have to drop the table & re-create it afaik.
Here's an example:
set nocount on
go
-- do it in a tran for safety
begin transaction
go
-- set up demo table
create table t1 (
c1 int not null primary key
, c2 char(1) not null
)
go
-- insert a demo row
insert into t1 (c1, c2) values (1, 'a')
go
-- set up a temp table with identity on the column
create table t1_temp (
c1 int not null identity (99, 1) primary key
, c2 char(1) not null
)
go
-- populate the temp table
set identity_insert t1_temp on
insert into t1_temp (c1, c2) select c1, c2 from t1
set identity_insert t1_temp off
go
-- destroy the original table
drop table t1
go
-- rename the temp table to t1
exec sp_rename 't1_temp', 't1'
go
-- insert another row to test
insert into t1 (c2) values ('b')
go
-- check results
select * from t1
go
-- clean up
rollback
go
Things might be a little more complicated if you're using schema binding for
stored procs / views & you might want to flush your proc cache too if you've
got stored procs using the table.
HTH
Regards,
Greg Linwood
SQL Server MVP
"james" <anonymous@.discussions.microsoft.com> wrote in message
news:1413101c3f7e7$c92ea810$a601280a@.phx
.gbl...
> I would like to add Indetntiy property to exitinng column
> and would like to start it from 100. Could anybody please
> give me the syntax|||Greg
I think we can use the same table to add identity property
create table t
(
col int not null primary key,
col2 char(1) not null
)
go
insert into t values (1,'a')
insert into t values (2,'b')
go
alter table t add col1 int identity(1,1)
go
alter table t drop constraint PK__t__41D98783
go
alter table t drop column col
go
EXEC sp_rename 't.col1', 'col', 'COLUMN'
go
select * from t
go
drop table t
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:eGs#FA$9DHA.3488@.tk2msftngp13.phx.gbl...
> Hi James
> You have to drop the table & re-create it afaik.
> Here's an example:
> set nocount on
> go
> -- do it in a tran for safety
> begin transaction
> go
> -- set up demo table
> create table t1 (
> c1 int not null primary key
> , c2 char(1) not null
> )
> go
> -- insert a demo row
> insert into t1 (c1, c2) values (1, 'a')
> go
> -- set up a temp table with identity on the column
> create table t1_temp (
> c1 int not null identity (99, 1) primary key
> , c2 char(1) not null
> )
> go
> -- populate the temp table
> set identity_insert t1_temp on
> insert into t1_temp (c1, c2) select c1, c2 from t1
> set identity_insert t1_temp off
> go
> -- destroy the original table
> drop table t1
> go
> -- rename the temp table to t1
> exec sp_rename 't1_temp', 't1'
> go
> -- insert another row to test
> insert into t1 (c2) values ('b')
> go
> -- check results
> select * from t1
> go
> -- clean up
> rollback
> go
> Things might be a little more complicated if you're using schema binding
for
> stored procs / views & you might want to flush your proc cache too if
you've
> got stored procs using the table.
> HTH
> Regards,
> Greg Linwood
> SQL Server MVP
> "james" <anonymous@.discussions.microsoft.com> wrote in message
> news:1413101c3f7e7$c92ea810$a601280a@.phx
.gbl...
>|||Greg
Sorry, did not read OP to the end. My example doesnot resolve his problem
because he wants to start from 100.
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:eGs#FA$9DHA.3488@.tk2msftngp13.phx.gbl...
> Hi James
> You have to drop the table & re-create it afaik.
> Here's an example:
> set nocount on
> go
> -- do it in a tran for safety
> begin transaction
> go
> -- set up demo table
> create table t1 (
> c1 int not null primary key
> , c2 char(1) not null
> )
> go
> -- insert a demo row
> insert into t1 (c1, c2) values (1, 'a')
> go
> -- set up a temp table with identity on the column
> create table t1_temp (
> c1 int not null identity (99, 1) primary key
> , c2 char(1) not null
> )
> go
> -- populate the temp table
> set identity_insert t1_temp on
> insert into t1_temp (c1, c2) select c1, c2 from t1
> set identity_insert t1_temp off
> go
> -- destroy the original table
> drop table t1
> go
> -- rename the temp table to t1
> exec sp_rename 't1_temp', 't1'
> go
> -- insert another row to test
> insert into t1 (c2) values ('b')
> go
> -- check results
> select * from t1
> go
> -- clean up
> rollback
> go
> Things might be a little more complicated if you're using schema binding
for
> stored procs / views & you might want to flush your proc cache too if
you've
> got stored procs using the table.
> HTH
> Regards,
> Greg Linwood
> SQL Server MVP
> "james" <anonymous@.discussions.microsoft.com> wrote in message
> news:1413101c3f7e7$c92ea810$a601280a@.phx
.gbl...
>
Posted by Maisie at 2:48 AM 0 comments
Labels: , , , , , , , , , , , , , ,

Thursday, March 8, 2012

alter identity property of a column to NOT FOR REPLICATION

i need to alter all foreign keys in my database and uncheck the
"Enforce relationship for replication" check box. Using the EM, I
extracted the code snippet below. unfortunately, when i run this test
from query analyzer, then go back into the EM, the box is still
checked.

can anyone tell me what i am missing? any advice on unsetting this
attribute globally would be appreciated!

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.CustomerCustomerDemo
DROP CONSTRAINT FK_CustomerCustomerDemo_Customers
GO
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.CustomerCustomerDemo WITH NOCHECK ADD CONSTRAINT
FK_CustomerCustomerDemo_Customers FOREIGN KEY
(
CustomerID
) REFERENCES dbo.Customers
(
CustomerID
) NOT FOR REPLICATION

GO
COMMIT

thanks!!dayong (reedmb89@.yahoo.com) writes:
> i need to alter all foreign keys in my database and uncheck the
> "Enforce relationship for replication" check box. Using the EM, I
> extracted the code snippet below. unfortunately, when i run this test
> from query analyzer, then go back into the EM, the box is still
> checked.

It's not simply a refresh issue? I was not able to reproduce this, of
the simple reason that I was not able find where you poke with FKs in
Enterprise Manager. I prefer to work exclusively with SQL statements
for DDL statements.

You can use "sp_helpconstraint" in Query Analyzer to verify the status
of the constraint.

> can anyone tell me what i am missing? any advice on unsetting this
> attribute globally would be appreciated!

As long as you know which the foreign keys are, going like the code you
included should not be a problem.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||
first, obviously, i'm new to sql server. thanks for your advice so far.
you are correct, it was a refresh issue. unfortunately, i cannot find a
simple way to find the foreign keys that are set for replication. i
looked at the stored procedure you advised (sp_helpconstraint). it
appears to create a temp table and then query and join info and
eventually has a boolean value where if true is_for_replication and
false not_for_replication.

this code is greek to me in my early stages of sql server
administration. is there a simpler way to locate the keys and columns
that are set is_for_replication?

thanks in advance for any advice!!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Michael Reed (anonymous@.anonymous.com) writes:
> first, obviously, i'm new to sql server.

If you find out how get the commands that EM runs, and run them
in Query Analyzer, you have come a long way compared to many other
SQL Server newbies!

> this code is greek to me in my early stages of sql server
> administration. is there a simpler way to locate the keys and columns
> that are set is_for_replication?

This SELECT lists all foreign key constraints that are set for replication,
and the parent table:

select tbl = object_name(parent_obj), fk_name = name
from sysobjects
where xtype = 'F' and objectproperty(id, 'CnstIsNotRepl') = 0
order by tbl, fk_name

I don't know how many constraints you have. If you have only a handful,
you might be able to the rest manually. If you have hundreds of table,
you probably want a list of the columns in each FK. Since I'm lazy, and
I don't have a query ready for that right now, I don't include one. :-)

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

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

please re-post your last response. i can only see the summary. when i
click on the link, your post is nowhere to be found.

please re-post.

thanks!!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Posted by Maisie at 7:35 PM 0 comments
Labels: , , , , , , , , , , , , , , , , , ,

Sunday, February 19, 2012

Allow zero length

We ran into a problem loading access where tables where if we did not
select the property allow zero length we got an error message when
loading data with some empty values.

We are now ramping up SQL server and the question came up will SQL
server have the same problem with empty data values.

Is there an SQL server equivalent to allow zero length?SQL-Server will always allow zero length values, unless you explicitely
add a constraint to disallow this.

So, by default, zero length values are allowed. If you do not want that,
you could run

ALTER TABLE mytable
ADD CONSTRAINT constraintname
CHECK (mycol <> '')

for each relevant column.

Hope this helps,
Gert-Jan

William Kossack wrote:
> We ran into a problem loading access where tables where if we did not
> select the property allow zero length we got an error message when
> loading data with some empty values.
> We are now ramping up SQL server and the question came up will SQL
> server have the same problem with empty data values.
> Is there an SQL server equivalent to allow zero length?

--
(Please reply only to the newsgroup)|||thanks

Gert-Jan Strik wrote:

> SQL-Server will always allow zero length values, unless you explicitely
> add a constraint to disallow this.
> So, by default, zero length values are allowed. If you do not want that,
> you could run
> ALTER TABLE mytable
> ADD CONSTRAINT constraintname
> CHECK (mycol <> '')
> for each relevant column.
> Hope this helps,
> Gert-Jan
> William Kossack wrote:
> > We ran into a problem loading access where tables where if we did not
> > select the property allow zero length we got an error message when
> > loading data with some empty values.
> > We are now ramping up SQL server and the question came up will SQL
> > server have the same problem with empty data values.
> > Is there an SQL server equivalent to allow zero length?
> --
> (Please reply only to the newsgroup)

Posted by Maisie at 1:00 AM 0 comments
Labels: , , , , , , , , , , , , , , ,

Thursday, February 9, 2012

All Member Formula AS 2005

Hello,

In AS 2000, we used the All Member Formula property for a dimension to force the dimension to use a particular level when the dimension was at the All level. Is there similar functionality in AS 2005?

Thanks.

In AS2005 the same functionality can be achieved by adding the following line to your MDX Script:

Dimension.Hierarchy.[All Member] = <all member formula>;

Posted by Maisie at 11:16 PM 0 comments
Labels: , , , , , , , , , , , ,

alignment when value from custom code

I have a textbox in my heading that gets its value from custom code. The
horizontal alignment property is set to RIGHT and I notice that the value is
getting truncated in my report. There is another textbox that has a similar
problem. These text boxes are somewhat free standing in the report (not
within tables etc). Can anyone tell me if this is expected behavior and if
so ... how can I fix the alignment so it doesnt truncate my client name?
Thanks
the expression in the textbox in the heading is nothing unusual ...
=code.GetClient(ReportItems!tbClientName.value)Have you tried adjusting the padding of the control? Usually, when padding
is set to 0pt I'll see this behavior.
-Tim
"MJT" <MJT@.discussions.microsoft.com> wrote in message
news:BF351039-B112-473E-8DE0-B38F1643F25C@.microsoft.com...
>I have a textbox in my heading that gets its value from custom code. The
> horizontal alignment property is set to RIGHT and I notice that the value
> is
> getting truncated in my report. There is another textbox that has a
> similar
> problem. These text boxes are somewhat free standing in the report (not
> within tables etc). Can anyone tell me if this is expected behavior and
> if
> so ... how can I fix the alignment so it doesnt truncate my client name?
> Thanks
> the expression in the textbox in the heading is nothing unusual ...
> =code.GetClient(ReportItems!tbClientName.value)
>|||Havent messed with the padding ... it is 2pt all the way around.
"Tim Dot NoSpam" wrote:
> Have you tried adjusting the padding of the control? Usually, when padding
> is set to 0pt I'll see this behavior.
> -Tim
> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> news:BF351039-B112-473E-8DE0-B38F1643F25C@.microsoft.com...
> >I have a textbox in my heading that gets its value from custom code. The
> > horizontal alignment property is set to RIGHT and I notice that the value
> > is
> > getting truncated in my report. There is another textbox that has a
> > similar
> > problem. These text boxes are somewhat free standing in the report (not
> > within tables etc). Can anyone tell me if this is expected behavior and
> > if
> > so ... how can I fix the alignment so it doesnt truncate my client name?
> > Thanks
> >
> > the expression in the textbox in the heading is nothing unusual ...
> > =code.GetClient(ReportItems!tbClientName.value)
> >
>
>
Posted by Maisie at 11:13 PM 0 comments
Labels: , , , , , , , , , , , , ,
Subscribe to: Posts (Atom)