I need to change the collation of a database (Including all the tables,
columns e.t.c) on a SQL Server 2005 SP2. Is there an easy way to do this
other than dropping indexes, creating a script for everything and running it ?
Thanks.
DXC
It is not an easy task. In my epxerience you will be better of re-creating
a database from scratch and import all the data.
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:8BBFC84E-6048-4636-A903-8EE7E6EA6FE5@.microsoft.com...
>I need to change the collation of a database (Including all the tables,
> columns e.t.c) on a SQL Server 2005 SP2. Is there an easy way to do this
> other than dropping indexes, creating a script for everything and running
> it ?
>
> Thanks.
|||That's what I thought...........Thanks.................
"Uri Dimant" wrote:
> DXC
> It is not an easy task. In my epxerience you will be better of re-creating
> a database from scratch and import all the data.
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:8BBFC84E-6048-4636-A903-8EE7E6EA6FE5@.microsoft.com...
>
>
|||I had cause to do this a while back and came across this .Net
application that generates a script to change the collation of all
objects in a database.
http://www.codeproject.com/KB/database/ChangeCollation.aspx
Be warned, you should backup your database first, take all reasonable
precautions. The databse script can take some time to run on large
databases. I'm not sure that CodeProject waranty the code or the
method in any way.
David.
|||Thanks David............This one says "SQL Server 2000 Collation Changer".
I don't know if it would work on SQL Server 2005 without any issues but I'll
take a look at it.
"David Halligan" wrote:
> I had cause to do this a while back and came across this .Net
> application that generates a script to change the collation of all
> objects in a database.
> http://www.codeproject.com/KB/database/ChangeCollation.aspx
> Be warned, you should backup your database first, take all reasonable
> precautions. The databse script can take some time to run on large
> databases. I'm not sure that CodeProject waranty the code or the
> method in any way.
> David.
>
Showing posts with label columns. Show all posts
Showing posts with label columns. Show all posts
Thursday, March 22, 2012
Collation Change
I need to change the collation of a database (Including all the tables,
columns e.t.c) on a SQL Server 2005 SP2. Is there an easy way to do this
other than dropping indexes, creating a script for everything and running it ?
Thanks.DXC
It is not an easy task. In my epxerience you will be better of re-creating
a database from scratch and import all the data.
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:8BBFC84E-6048-4636-A903-8EE7E6EA6FE5@.microsoft.com...
>I need to change the collation of a database (Including all the tables,
> columns e.t.c) on a SQL Server 2005 SP2. Is there an easy way to do this
> other than dropping indexes, creating a script for everything and running
> it ?
>
> Thanks.|||I had cause to do this a while back and came across this .Net
application that generates a script to change the collation of all
objects in a database.
http://www.codeproject.com/KB/database/ChangeCollation.aspx
Be warned, you should backup your database first, take all reasonable
precautions. The databse script can take some time to run on large
databases. I'm not sure that CodeProject waranty the code or the
method in any way.
David.|||That's what I thought...........Thanks.................
"Uri Dimant" wrote:
> DXC
> It is not an easy task. In my epxerience you will be better of re-creating
> a database from scratch and import all the data.
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:8BBFC84E-6048-4636-A903-8EE7E6EA6FE5@.microsoft.com...
> >I need to change the collation of a database (Including all the tables,
> > columns e.t.c) on a SQL Server 2005 SP2. Is there an easy way to do this
> > other than dropping indexes, creating a script for everything and running
> > it ?
> >
> >
> > Thanks.
>
>|||Thanks David............This one says "SQL Server 2000 Collation Changer".
I don't know if it would work on SQL Server 2005 without any issues but I'll
take a look at it.
"David Halligan" wrote:
> I had cause to do this a while back and came across this .Net
> application that generates a script to change the collation of all
> objects in a database.
> http://www.codeproject.com/KB/database/ChangeCollation.aspx
> Be warned, you should backup your database first, take all reasonable
> precautions. The databse script can take some time to run on large
> databases. I'm not sure that CodeProject waranty the code or the
> method in any way.
> David.
>
columns e.t.c) on a SQL Server 2005 SP2. Is there an easy way to do this
other than dropping indexes, creating a script for everything and running it ?
Thanks.DXC
It is not an easy task. In my epxerience you will be better of re-creating
a database from scratch and import all the data.
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:8BBFC84E-6048-4636-A903-8EE7E6EA6FE5@.microsoft.com...
>I need to change the collation of a database (Including all the tables,
> columns e.t.c) on a SQL Server 2005 SP2. Is there an easy way to do this
> other than dropping indexes, creating a script for everything and running
> it ?
>
> Thanks.|||I had cause to do this a while back and came across this .Net
application that generates a script to change the collation of all
objects in a database.
http://www.codeproject.com/KB/database/ChangeCollation.aspx
Be warned, you should backup your database first, take all reasonable
precautions. The databse script can take some time to run on large
databases. I'm not sure that CodeProject waranty the code or the
method in any way.
David.|||That's what I thought...........Thanks.................
"Uri Dimant" wrote:
> DXC
> It is not an easy task. In my epxerience you will be better of re-creating
> a database from scratch and import all the data.
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:8BBFC84E-6048-4636-A903-8EE7E6EA6FE5@.microsoft.com...
> >I need to change the collation of a database (Including all the tables,
> > columns e.t.c) on a SQL Server 2005 SP2. Is there an easy way to do this
> > other than dropping indexes, creating a script for everything and running
> > it ?
> >
> >
> > Thanks.
>
>|||Thanks David............This one says "SQL Server 2000 Collation Changer".
I don't know if it would work on SQL Server 2005 without any issues but I'll
take a look at it.
"David Halligan" wrote:
> I had cause to do this a while back and came across this .Net
> application that generates a script to change the collation of all
> objects in a database.
> http://www.codeproject.com/KB/database/ChangeCollation.aspx
> Be warned, you should backup your database first, take all reasonable
> precautions. The databse script can take some time to run on large
> databases. I'm not sure that CodeProject waranty the code or the
> method in any way.
> David.
>
Collation Change
I need to change the collation of a database (Including all the tables,
columns e.t.c) on a SQL Server 2005 SP2. Is there an easy way to do this
other than dropping indexes, creating a script for everything and running it
?
Thanks.DXC
It is not an easy task. In my epxerience you will be better of re-creating
a database from scratch and import all the data.
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:8BBFC84E-6048-4636-A903-8EE7E6EA6FE5@.microsoft.com...
>I need to change the collation of a database (Including all the tables,
> columns e.t.c) on a SQL Server 2005 SP2. Is there an easy way to do this
> other than dropping indexes, creating a script for everything and running
> it ?
>
> Thanks.|||That's what I thought...........Thanks.................
"Uri Dimant" wrote:
> DXC
> It is not an easy task. In my epxerience you will be better of re-creatin
g
> a database from scratch and import all the data.
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:8BBFC84E-6048-4636-A903-8EE7E6EA6FE5@.microsoft.com...
>
>|||I had cause to do this a while back and came across this .Net
application that generates a script to change the collation of all
objects in a database.
http://www.codeproject.com/KB/datab...eCollation.aspx
Be warned, you should backup your database first, take all reasonable
precautions. The databse script can take some time to run on large
databases. I'm not sure that CodeProject waranty the code or the
method in any way.
David.|||Thanks David............This one says "SQL Server 2000 Collation Changer"
.
I don't know if it would work on SQL Server 2005 without any issues but I'll
take a look at it.
"David Halligan" wrote:
> I had cause to do this a while back and came across this .Net
> application that generates a script to change the collation of all
> objects in a database.
> http://www.codeproject.com/KB/datab...eCollation.aspx
> Be warned, you should backup your database first, take all reasonable
> precautions. The databse script can take some time to run on large
> databases. I'm not sure that CodeProject waranty the code or the
> method in any way.
> David.
>
columns e.t.c) on a SQL Server 2005 SP2. Is there an easy way to do this
other than dropping indexes, creating a script for everything and running it
?
Thanks.DXC
It is not an easy task. In my epxerience you will be better of re-creating
a database from scratch and import all the data.
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:8BBFC84E-6048-4636-A903-8EE7E6EA6FE5@.microsoft.com...
>I need to change the collation of a database (Including all the tables,
> columns e.t.c) on a SQL Server 2005 SP2. Is there an easy way to do this
> other than dropping indexes, creating a script for everything and running
> it ?
>
> Thanks.|||That's what I thought...........Thanks.................
"Uri Dimant" wrote:
> DXC
> It is not an easy task. In my epxerience you will be better of re-creatin
g
> a database from scratch and import all the data.
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:8BBFC84E-6048-4636-A903-8EE7E6EA6FE5@.microsoft.com...
>
>|||I had cause to do this a while back and came across this .Net
application that generates a script to change the collation of all
objects in a database.
http://www.codeproject.com/KB/datab...eCollation.aspx
Be warned, you should backup your database first, take all reasonable
precautions. The databse script can take some time to run on large
databases. I'm not sure that CodeProject waranty the code or the
method in any way.
David.|||Thanks David............This one says "SQL Server 2000 Collation Changer"
.
I don't know if it would work on SQL Server 2005 without any issues but I'll
take a look at it.
"David Halligan" wrote:
> I had cause to do this a while back and came across this .Net
> application that generates a script to change the collation of all
> objects in a database.
> http://www.codeproject.com/KB/datab...eCollation.aspx
> Be warned, you should backup your database first, take all reasonable
> precautions. The databse script can take some time to run on large
> databases. I'm not sure that CodeProject waranty the code or the
> method in any way.
> David.
>
collation ansi padding and trailing blanks
Hi,
This might sound obvious, or a newbie question, but how are trailing blanks treated by SQL2005 on varchar columns?
I have a column where two rows only differ by a trailing blank. If write a select and a where clause on the column, anly trailing blanks seem to be trimmed. I tried the ansi padding setting but it doesn't change anything. Is it a question of collation? I have default collation on the server set to SQL_Latin1_General_CP1_CI_AS...
The problem also seems to arise when I try to create a unique index on the column, where both values are considered equivalent...
I give here a sample based on the BOL for set ansi_padding. I was expecting each of the select statements below to retrun only one row...
Cany somebody please explain why they all return two rows?
PRINT 'Testing with ANSI_PADDING ON'
SET ANSI_PADDING ON;
GO
CREATE TABLE t1 (
charcol CHAR(16) NULL,
varcharcol VARCHAR(16) NULL,
varbinarycol VARBINARY(8)
);
GO
INSERT INTO t1 VALUES ('No blanks', 'No blanks', 0x00ee);
INSERT INTO t1 VALUES ('Trailing blank ', 'Trailing blank ', 0x00ee00);
INSERT INTO t1 VALUES ('Trailing blank ', 'Trailing blank', 0x00ee00);
SELECT 'CHAR' = '>' + charcol + '<', 'VARCHAR'='>' + varcharcol + '<',
varbinarycol
FROM t1
where varcharcol='Trailing blank';
GO
SELECT 'CHAR' = '>' + charcol + '<', 'VARCHAR'='>' + varcharcol + '<',
varbinarycol
FROM t1
where varcharcol='Trailing blank ';
GO
PRINT 'Testing with ANSI_PADDING OFF';
SET ANSI_PADDING OFF;
GO
CREATE TABLE t2 (
charcol CHAR(16) NULL,
varcharcol VARCHAR(16) NULL,
varbinarycol VARBINARY(8)
);
GO
INSERT INTO t2 VALUES ('No blanks', 'No blanks', 0x00ee);
INSERT INTO t2 VALUES ('Trailing blank ', 'Trailing blank ', 0x00ee00);
INSERT INTO t2 VALUES ('Trailing blank ', 'Trailing blank', 0x00ee00);
SELECT 'CHAR' = '>' + charcol + '<', 'VARCHAR'='>' + varcharcol + '<',
varbinarycol
FROM t2
where varcharcol='Trailing blank';
GO
SELECT 'CHAR' = '>' + charcol + '<', 'VARCHAR'='>' + varcharcol + '<',
varbinarycol
FROM t2
where varcharcol='Trailing blank ';
GO
DROP TABLE t1
DROP TABLE t2
ANSI padding setting only affects the storage and how the trimming of blanks is performed for non-unicode data. It doesn't change the search semantics. SQL Server will always ignore trailing blanks / spaces for equality searches. If you perform the same using LIKE then trailing blanks will be considered. If you do the query below after inserting the data, you will see how the storage differs when ANSI_PADDING is ON and OFF.
select datalength(charcol), datalength(varcharcol)
from t1
select datalength(charcol), datalength(varcharcol)
from t2
Monday, March 19, 2012
Collapsing columns in table mode, part II
I figured out how to collapse a column, but the report generator is maintaining the space that the collapsed columns occupied even when the columns are hidden. For me, it's valuable for space optimization to have all remaining columns to the right "move over" to be flush against the other visible columns. Can this be done?Is this possible?|||
What you explain as your goal is the behavior I see in RS 2005. If I assign a Toggle Item to a table column, columns to the right of it move left when the column is hidden.
Are you sure you're assigning the Toggle Item to the Table Column instead of the individual table cells within the column?
-Chris
|||Hi Chris, thanks for hte response. I tried what you suggest, and that is select an entire column and assign a textbox to control the visibility, but whenever I did that, I get the following error:"The definition of the report '/ForecastReport' is invalid. The table 'table1' has 'textbox37' as a toggle item. Toggle items must be text boxes that share the same scope as the hidden item or are in a scope that contains the hidden item, and caonnot be contained within the current report item unless current grouping scope has a Parent"
One interesting thing about this error that I just noticed is, in the report designer, I have tripled checked to make sure I've got the desired COLUMN selected, not 'table1'.|||Perhaps more telling, when I select an entire column in the designer and then go to the "visibility" section, the "ToggleItem" drop down is completely empty of available choices.|||
Did you ever found a solution to this. I do have the same problem.
yours sincerely
Johan
Collapsing columns in table mode, part II
I figured out how to collapse a column, but the report generator is maintaining the space that the collapsed columns occupied even when the columns are hidden. For me, it's valuable for space optimization to have all remaining columns to the right "move over" to be flush against the other visible columns. Can this be done?Is this possible?|||
What you explain as your goal is the behavior I see in RS 2005. If I assign a Toggle Item to a table column, columns to the right of it move left when the column is hidden.
Are you sure you're assigning the Toggle Item to the Table Column instead of the individual table cells within the column?
-Chris
|||Hi Chris, thanks for hte response. I tried what you suggest, and that is select an entire column and assign a textbox to control the visibility, but whenever I did that, I get the following error:"The definition of the report '/ForecastReport' is invalid. The table 'table1' has 'textbox37' as a toggle item. Toggle items must be text boxes that share the same scope as the hidden item or are in a scope that contains the hidden item, and caonnot be contained within the current report item unless current grouping scope has a Parent"
One interesting thing about this error that I just noticed is, in the report designer, I have tripled checked to make sure I've got the desired COLUMN selected, not 'table1'.|||Perhaps more telling, when I select an entire column in the designer and then go to the "visibility" section, the "ToggleItem" drop down is completely empty of available choices.|||
Did you ever found a solution to this. I do have the same problem.
yours sincerely
Johan
Collapse cell if empty and
Whoops. Sent too quick.
=Iif(IsNothing(Fields!ADDRESS3.Value),True,False)
Part 2 of question. I've got city, state, and zip columns evenly spaced in
a table. How do I get the columns to collapse to the width of their data?
"Colin" <legendsfan@.nospam.nospam> wrote in message news:...
> I'm creating a Reporting Services form within VS 2003. I can't figure out
> how to collapse a cell if there is no data returned. As an example I've
> got three lines for an Address field but most only use two lines. How can
> I collapse the third cell on my form so city, state, and zip will be next
> to last line of address? I figure I need to use an "expression" but this
> doesn't seem to be working. I applied this under visibility.
>Use something like this expression =IIF( Fields!STREET2.Value =nothing,True,False) as the Hidden property value under Visibility for
the row or cell you want to conditionally hide.
As for your part 2, I don't know the answer to that. SRS allows for
increased/decreased height based on data but not width. If anybody has
a fix for this, I'd like to see that too.|||How do I put static data in a field with something dynamically generated? I
tried "Street Name:" & =IIF( Fields!STREET2.Value => nothing,True,False)
That didn't work.
This will hide the field but it doesn't collapse it or make it not show on
the page. I have four rows in a table for the address field but usually
only 3 are used. This is what happens when the third row is nothing. Under
Willow there is a blank space because this field rarely has data associated.
You can also see the spacing problem between city and state.
Target Stores
TARGET #1167
2241 WILLOW
GLENVIEW IL 60025
"toolman" <toolman_2000@.yahoo.com> wrote in message
news:1139939869.370244.104680@.o13g2000cwo.googlegroups.com...
> Use something like this expression =IIF( Fields!STREET2.Value => nothing,True,False) as the Hidden property value under Visibility for
> the row or cell you want to conditionally hide.
> As for your part 2, I don't know the answer to that. SRS allows for
> increased/decreased height based on data but not width. If anybody has
> a fix for this, I'd like to see that too.
>|||Hello,
To understand the issue better, will you provide a simple sample of the
report with backend data?
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
>From: "Colin" <legendsfan@.nospam.nospam>
>References: <eHxiKZOMGHA.2416@.TK2MSFTNGP15.phx.gbl>
<1139939869.370244.104680@.o13g2000cwo.googlegroups.com>
>Subject: Re: Collapse cell if empty and
>Date: Tue, 14 Feb 2006 14:37:02 -0800
>Lines: 29
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
>X-RFC2646: Format=Flowed; Original
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
>Message-ID: <#c0tfbbMGHA.2124@.TK2MSFTNGP14.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.reportingsvcs
>NNTP-Posting-Host: mail.topics-ent.com 64.122.1.82
>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP14.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:68725
>X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>How do I put static data in a field with something dynamically generated?
I
>tried "Street Name:" & =IIF( Fields!STREET2.Value =>> nothing,True,False)
>That didn't work.
>This will hide the field but it doesn't collapse it or make it not show on
>the page. I have four rows in a table for the address field but usually
>only 3 are used. This is what happens when the third row is nothing.
Under
>Willow there is a blank space because this field rarely has data
associated.
>You can also see the spacing problem between city and state.
> Target Stores
> TARGET #1167
> 2241 WILLOW
> GLENVIEW IL 60025
>"toolman" <toolman_2000@.yahoo.com> wrote in message
>news:1139939869.370244.104680@.o13g2000cwo.googlegroups.com...
>> Use something like this expression =IIF( Fields!STREET2.Value =>> nothing,True,False) as the Hidden property value under Visibility for
>> the row or cell you want to conditionally hide.
>> As for your part 2, I don't know the answer to that. SRS allows for
>> increased/decreased height based on data but not width. If anybody has
>> a fix for this, I'd like to see that too.
>
>|||Are you using the =IIF( Fields!STREET2.Value = > nothing,True,False)
expression in the property for the entire row or just a cell in the
row? If not, try that. It should work for you. I have a couple
reports using it and it always draws the city, state zip row up to the
last visible address row.
The expression for Street: Fields!Street.value would be
="Street: " & Field!Street.Value
If Fields!Street.Value is Nothing you'll get 'Street: ' otherwise
you'll get 'Street: 123 Street Value' (without the quotes)|||Thanks toolman. I was setting the hidden value in the cell instead of row.
"toolman" <toolman_2000@.yahoo.com> wrote in message
news:1140038132.121332.42660@.g47g2000cwa.googlegroups.com...
> Are you using the =IIF( Fields!STREET2.Value = > nothing,True,False)
> expression in the property for the entire row or just a cell in the
> row? If not, try that. It should work for you. I have a couple
> reports using it and it always draws the city, state zip row up to the
> last visible address row.
> The expression for Street: Fields!Street.value would be
> ="Street: " & Field!Street.Value
> If Fields!Street.Value is Nothing you'll get 'Street: ' otherwise
> you'll get 'Street: 123 Street Value' (without the quotes)
>
=Iif(IsNothing(Fields!ADDRESS3.Value),True,False)
Part 2 of question. I've got city, state, and zip columns evenly spaced in
a table. How do I get the columns to collapse to the width of their data?
"Colin" <legendsfan@.nospam.nospam> wrote in message news:...
> I'm creating a Reporting Services form within VS 2003. I can't figure out
> how to collapse a cell if there is no data returned. As an example I've
> got three lines for an Address field but most only use two lines. How can
> I collapse the third cell on my form so city, state, and zip will be next
> to last line of address? I figure I need to use an "expression" but this
> doesn't seem to be working. I applied this under visibility.
>Use something like this expression =IIF( Fields!STREET2.Value =nothing,True,False) as the Hidden property value under Visibility for
the row or cell you want to conditionally hide.
As for your part 2, I don't know the answer to that. SRS allows for
increased/decreased height based on data but not width. If anybody has
a fix for this, I'd like to see that too.|||How do I put static data in a field with something dynamically generated? I
tried "Street Name:" & =IIF( Fields!STREET2.Value => nothing,True,False)
That didn't work.
This will hide the field but it doesn't collapse it or make it not show on
the page. I have four rows in a table for the address field but usually
only 3 are used. This is what happens when the third row is nothing. Under
Willow there is a blank space because this field rarely has data associated.
You can also see the spacing problem between city and state.
Target Stores
TARGET #1167
2241 WILLOW
GLENVIEW IL 60025
"toolman" <toolman_2000@.yahoo.com> wrote in message
news:1139939869.370244.104680@.o13g2000cwo.googlegroups.com...
> Use something like this expression =IIF( Fields!STREET2.Value => nothing,True,False) as the Hidden property value under Visibility for
> the row or cell you want to conditionally hide.
> As for your part 2, I don't know the answer to that. SRS allows for
> increased/decreased height based on data but not width. If anybody has
> a fix for this, I'd like to see that too.
>|||Hello,
To understand the issue better, will you provide a simple sample of the
report with backend data?
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
>From: "Colin" <legendsfan@.nospam.nospam>
>References: <eHxiKZOMGHA.2416@.TK2MSFTNGP15.phx.gbl>
<1139939869.370244.104680@.o13g2000cwo.googlegroups.com>
>Subject: Re: Collapse cell if empty and
>Date: Tue, 14 Feb 2006 14:37:02 -0800
>Lines: 29
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
>X-RFC2646: Format=Flowed; Original
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
>Message-ID: <#c0tfbbMGHA.2124@.TK2MSFTNGP14.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.reportingsvcs
>NNTP-Posting-Host: mail.topics-ent.com 64.122.1.82
>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP14.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:68725
>X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>How do I put static data in a field with something dynamically generated?
I
>tried "Street Name:" & =IIF( Fields!STREET2.Value =>> nothing,True,False)
>That didn't work.
>This will hide the field but it doesn't collapse it or make it not show on
>the page. I have four rows in a table for the address field but usually
>only 3 are used. This is what happens when the third row is nothing.
Under
>Willow there is a blank space because this field rarely has data
associated.
>You can also see the spacing problem between city and state.
> Target Stores
> TARGET #1167
> 2241 WILLOW
> GLENVIEW IL 60025
>"toolman" <toolman_2000@.yahoo.com> wrote in message
>news:1139939869.370244.104680@.o13g2000cwo.googlegroups.com...
>> Use something like this expression =IIF( Fields!STREET2.Value =>> nothing,True,False) as the Hidden property value under Visibility for
>> the row or cell you want to conditionally hide.
>> As for your part 2, I don't know the answer to that. SRS allows for
>> increased/decreased height based on data but not width. If anybody has
>> a fix for this, I'd like to see that too.
>
>|||Are you using the =IIF( Fields!STREET2.Value = > nothing,True,False)
expression in the property for the entire row or just a cell in the
row? If not, try that. It should work for you. I have a couple
reports using it and it always draws the city, state zip row up to the
last visible address row.
The expression for Street: Fields!Street.value would be
="Street: " & Field!Street.Value
If Fields!Street.Value is Nothing you'll get 'Street: ' otherwise
you'll get 'Street: 123 Street Value' (without the quotes)|||Thanks toolman. I was setting the hidden value in the cell instead of row.
"toolman" <toolman_2000@.yahoo.com> wrote in message
news:1140038132.121332.42660@.g47g2000cwa.googlegroups.com...
> Are you using the =IIF( Fields!STREET2.Value = > nothing,True,False)
> expression in the property for the entire row or just a cell in the
> row? If not, try that. It should work for you. I have a couple
> reports using it and it always draws the city, state zip row up to the
> last visible address row.
> The expression for Street: Fields!Street.value would be
> ="Street: " & Field!Street.Value
> If Fields!Street.Value is Nothing you'll get 'Street: ' otherwise
> you'll get 'Street: 123 Street Value' (without the quotes)
>
Collapsable Columns not showing up in export to Excel
Dear Anyone,
I have created a report that has collapsable columns. We had used the visible toggle property in reporting services. While the colpsable column works well in report manager, it doesnt work anyone when we export the report to excel. Even of the report is expanded, the report in excel would always show up with just the columns that are the basis for the collapsable column.
Is this really the behavior when it comes to collapsable columns and exporting to excel?
Example:
Column A toggles Column B, Column C and Column D.
Thanks,
Joseph
I've solved this issue setting the hidden columns to be by defualt visible and then letting the user decide if to hide them or not.
In this way excel export it is correct.
HTH.
Alberto
|||Hello Alberto,
Would you mind sharing your findings?
Thanks,
Joseph
|||I will appreciate if you could post the code and explanation how you did that.Collapsable Columns not showing up in export to Excel
Dear Anyone,
I have created a report that has collapsable columns. We had used the visible toggle property in reporting services. While the colpsable column works well in report manager, it doesnt work anyone when we export the report to excel. Even of the report is expanded, the report in excel would always show up with just the columns that are the basis for the collapsable column.
Is this really the behavior when it comes to collapsable columns and exporting to excel?
Example:
Column A toggles Column B, Column C and Column D.
Thanks,
Joseph
I've solved this issue setting the hidden columns to be by defualt visible and then letting the user decide if to hide them or not.
In this way excel export it is correct.
HTH.
Alberto
|||Hello Alberto,
Would you mind sharing your findings?
Thanks,
Joseph
|||I will appreciate if you could post the code and explanation how you did that.Collapsable Columns not showing up in export to Excel
Dear Anyone,
I have created a report that has collapsable columns. We had used the visible toggle property in reporting services. While the colpsable column works well in report manager, it doesnt work anyone when we export the report to excel. Even of the report is expanded, the report in excel would always show up with just the columns that are the basis for the collapsable column.
Is this really the behavior when it comes to collapsable columns and exporting to excel?
Example:
Column A toggles Column B, Column C and Column D.
Thanks,
Joseph
I've solved this issue setting the hidden columns to be by defualt visible and then letting the user decide if to hide them or not.
In this way excel export it is correct.
HTH.
Alberto
|||Hello Alberto,
Would you mind sharing your findings?
Thanks,
Joseph
|||I will appreciate if you could post the code and explanation how you did that.Wednesday, March 7, 2012
Cocatinate 2 columns
Hi,
Please help.. I want a level in the dimension such that the concatenation of two columns in the dimension. Can I do that while creating the dimension or I have to do in the database level.
please answer urgent help..
Rajbest way to do it is at the relational level so it is ready for processing.
however, you can concatenate strings in MDX
this is an example of measure concatenation
LINK (http://www.databasejournal.com/features/mssql/article.php/10894_1550061_4)
Please help.. I want a level in the dimension such that the concatenation of two columns in the dimension. Can I do that while creating the dimension or I have to do in the database level.
please answer urgent help..
Rajbest way to do it is at the relational level so it is ready for processing.
however, you can concatenate strings in MDX
this is an example of measure concatenation
LINK (http://www.databasejournal.com/features/mssql/article.php/10894_1550061_4)
Tuesday, February 14, 2012
Clustered Indexed View or Custom Sync Object?
sql2k sp3
If you had more columns on Subscriber than Publisher,
would you be more likely to use Clustered Indexed Views or
Custom Sync Objects to implement 1 way, Transactional
Replication? Pros and Cons for each?
TIA, ChrisR
ChrisR,
I personally would prefer to use indexed views.
I suppose simplistically the downside is that there is increased overhead on
the publisher to maintain the materialized data. The upside is that there is
increased transparency so far as maintenance of replication is concerned.
Regards,
Paul Ibison
|||the problem is that for Chris Indexed Views won't support sp_repladdcolumn
and sp_repldropcolumn.
I am mystified as to why what works for me on my machine doesn't work on
ChrisRs. I am revisiting this script and I am hoping ChrisR and others can
test it. There may be a bug somewhere here.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OVm9MNphEHA.1356@.TK2MSFTNGP09.phx.gbl...
> ChrisR,
> I personally would prefer to use indexed views.
> I suppose simplistically the downside is that there is increased overhead
on
> the publisher to maintain the materialized data. The upside is that there
is
> increased transparency so far as maintenance of replication is concerned.
> Regards,
> Paul Ibison
>
|||I thought it couldnt be done. Thats what I got from the
replies to my post:
sp_repladdcolumn on Indexed View
08/04/04 4:24 pm.
Did I misunderstand?
>--Original Message--
>the problem is that for Chris Indexed Views won't support
sp_repladdcolumn
>and sp_repldropcolumn.
>I am mystified as to why what works for me on my machine
doesn't work on
>ChrisRs. I am revisiting this script and I am hoping
ChrisR and others can[vbcol=seagreen]
>test it. There may be a bug somewhere here.
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
>news:OVm9MNphEHA.1356@.TK2MSFTNGP09.phx.gbl...
increased overhead[vbcol=seagreen]
>on
upside is that there[vbcol=seagreen]
>is
replication is concerned.
>
>.
>
|||Sorry for the confusion.
You can't use sp_repladdcolumn or sp_repldropcolumn on an indexed view which
you are publishing for replication.
The Custom Sync object works for me.
Here is the script that works for me.
Here is my TransDTL.SQL script.
CREATE TABLE [dbo].[TransDtl] (
[TransDtlKey] [int] NOT NULL ,
[CustomerKey] [int] NULL ,
[SerialNbr] [char] (10),
[TranCode] [char] (4),
[TransDate] [smalldatetime] NULL ,
[TransDateShort] [char] (10),
[TransDateMonth] [tinyint] NULL ,
[TransDateYear] [smallint] NULL ,
[TransAmt] [money] NULL ,
[RefNbr] [char] (23),
[MerchName] [varchar] (25),
[City] [varchar] (15),
[State] [varchar] (3),
[RejectReason] [varchar] (15),
[PostDate] [datetime] NULL ,
[PostDateShort] [char] (10),
[PostDateMonth] [tinyint] NULL ,
[PostDateYear] [smallint] NULL ,
[CreateDate] [datetime] NULL ,
[MerchSIC] [char] (4)
) ON [PRIMARY]
GO
--this is my replication script
create database ChrisR
go
create database ChrisRSub
go
use ChrisR
go
CREATE TABLE TransDtl
(
TransDtlKey int IDENTITY(1, 1) NOT NULL PRIMARY KEY,
CustomerKey int NULL,
SerialNbr char(10),
TranCode char(4),
TransDate smalldatetime NOT NULL,
TransAmt money NOT NULL,
RefNbr char(23),
MerchName varchar(25),
City varchar(15),
State varchar(3),
RejectReason varchar(15),
PostDate datetime NOT NULL,
CreateDate datetime NOT NULL,
MerchSIC char(4)
)
GO
use chrisr
GO
Create View CustomSyncObject
as
select TransDtlKey=convert(int,TransDtlKey),
CustomerKey=convert(int,CustomerKey),
SerialNbr=convert(char(10),SerialNbr),
TranCode=convert(char(4),TranCode),
TransDate=convert(smalldatetime,TransDate),
TransDateShort = Convert(varchar(10),TransDate, 101),
TransDateMonth = convert(tinyint,Month(TransDate)),
TransDateYear = convert(smallint,Year(TransDate)),
TransAmt=convert(money, TransAmt),
RefNbr, MerchName, City, State, RejectReason, PostDate,
PostDateShort = Convert(varchar(10), PostDate, 101),
PostDateMonth = convert(tinyint, Month(PostDate)),
PostDateYear=convert(smallint, Year(PostDate)),
CreateDate,
MerchSIC from TransDtl
GO
sp_dboption 'ChrisR','published','true'
go
sp_addpublication 'ChrisR',@.status='active', @.sync_method = N'character'
go
sp_addpublication_snapshot 'ChrisR'
go
sp_addarticle @.publication = 'ChrisR',
@.article = 'TransDTL',
@.source_object = 'TransDTL',
@.destination_table = 'TransDTL',
@.type = 'logbased manualview',
@.sync_object='CustomSyncObject',
@.creation_script = 'c:\temp\TransDTL.sql',
@.pre_creation_cmd = 'delete',
@.schema_option = 0x0,
@.status = 8,
@.ins_cmd = 'CALL sp_MSins_TransDTL',
@.del_cmd = 'CALL sp_MSdel_TransDTL',
@.upd_cmd = 'MCALL sp_MSupd_TransDTL'
GO
use chrisRsub
go
if exists (select * from sysobjects where type = 'P' and name =
'sp_MSins_TransDTL') drop proc [sp_MSins_TransDTL]
go
create procedure [sp_MSins_TransDTL] @.c1 int,@.c2 int,@.c3 char(10),@.c4
char(4),@.c5 smalldatetime,@.c6 money,@.c7 char(23),@.c8 varchar(25),@.c9
varchar(15),@.c10 varchar(3),@.c11 varchar(15),@.c12 datetime,@.c13
datetime,@.c14 char(4)
AS
BEGIN
insert into [TransDTL](
[TransDtlKey], [CustomerKey], [SerialNbr], [TranCode],
[TransDate],[TransDateShort],
[TransDateMonth], [TransDateYear], [TransAmt], [RefNbr], [MerchName],
[City], [State],
[RejectReason], [PostDate],[PostDateShort],[PostDateMonth],[PostDateYear],
[CreateDate], [MerchSIC]
)
values (
@.c1, @.c2, @.c3, @.c4, @.c5, Convert(varchar(10), @.c5, 101), Month(@.c5),
Year(@.c5),
@.c6, @.c7, @.c8, @.c9, @.c10, @.c11, @.c12, Convert(varchar(10), @.c12, 101),
Month(@.c12), Year(@.c12), @.c13, @.c14
)
END
go
if exists (select * from sysobjects where type = 'P' and name =
'sp_MSupd_TransDTL') drop proc [sp_MSupd_TransDTL]
go
create procedure [sp_MSupd_TransDTL]
@.c1 int,@.c2 int,@.c3 char(10),@.c4 char(4),@.c5 smalldatetime,@.c6 money,@.c7
char(23),@.c8 varchar(25),@.c9 varchar(15),@.c10 varchar(3),@.c11
varchar(15),@.c12 datetime,@.c13 datetime,@.c14 char(4),@.pkc1 int
,@.bitmap binary(2)
as
if substring(@.bitmap,1,1) & 1 = 1
begin
update [TransDTL] set
[TransDtlKey] = case substring(@.bitmap,1,1) & 1 when 1 then @.c1 else
[TransDtlKey] end
,[CustomerKey] = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
[CustomerKey] end
,[SerialNbr] = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else
[SerialNbr] end
,[TranCode] = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
[TranCode] end
,[TransDate] = case substring(@.bitmap,1,1) & 16 when 16 then @.c5 else
[TransDate] end
,[TransDateShort]= case substring(@.bitmap,1,1) & 16 when 16 then
Convert(varchar(10), @.c5, 101) else [TransDateShort] end
,[TransDateMonth]= case substring(@.bitmap,1,1) & 16 when 16 then Month(@.c5)
else [TransDateMonth] end
,[TransDateYear]= case substring(@.bitmap,1,1) & 16 when 16 then Year(@.c5)
else [TransDateYear] end
,[TransAmt] = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
[TransAmt] end
,[RefNbr] = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else [RefNbr]
end
,[MerchName] = case substring(@.bitmap,1,1) & 128 when 128 then @.c8 else
[MerchName] end
,[City] = case substring(@.bitmap,2,1) & 1 when 1 then @.c9 else [City] end
,[State] = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else [State] end
,[RejectReason] = case substring(@.bitmap,2,1) & 4 when 4 then @.c11 else
[RejectReason] end
,[PostDate] = case substring(@.bitmap,2,1) & 8 when 8 then @.c12 else
[PostDate] end
,[PostDateShort] = case substring(@.bitmap,2,1) & 8 when 8 then
Convert(varchar(10), @.c12, 101) else [PostDateShort] end
,[PostDateMonth] = case substring(@.bitmap,2,1) & 8 when 8 then Month(@.c12)
else [PostDateMonth] end
,[PostDateYear] = case substring(@.bitmap,2,1) & 8 when 8 then Year(@.c12)
else [PostDateYear] end
,[CreateDate] = case substring(@.bitmap,2,1) & 16 when 16 then @.c13 else
[CreateDate] end
,[MerchSIC] = case substring(@.bitmap,2,1) & 32 when 32 then @.c14 else
[MerchSIC] end
where [TransDtlKey] = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
else
begin
update [TransDTL] set
[CustomerKey] = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
[CustomerKey] end
,[SerialNbr] = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else
[SerialNbr] end
,[TranCode] = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
[TranCode] end
,[TransDate] = case substring(@.bitmap,1,1) & 16 when 16 then @.c5 else
[TransDate] end
,[TransDateShort]= case substring(@.bitmap,1,1) & 16 when 16 then
Convert(varchar(10), @.c5, 101) else [TransDateShort] end
,[TransDateMonth]= case substring(@.bitmap,1,1) & 16 when 16 then Month(@.c5)
else [TransDateMonth] end
,[TransDateYear]= case substring(@.bitmap,1,1) & 16 when 16 then Year(@.c5)
else [TransDateYear] end
,[TransAmt] = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
[TransAmt] end
,[RefNbr] = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else [RefNbr]
end
,[MerchName] = case substring(@.bitmap,1,1) & 128 when 128 then @.c8 else
[MerchName] end
,[City] = case substring(@.bitmap,2,1) & 1 when 1 then @.c9 else [City] end
,[State] = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else [State] end
,[RejectReason] = case substring(@.bitmap,2,1) & 4 when 4 then @.c11 else
[RejectReason] end
,[PostDate] = case substring(@.bitmap,2,1) & 8 when 8 then @.c12 else
[PostDate] end
,[PostDateShort] = case substring(@.bitmap,2,1) & 8 when 8 then
Convert(varchar(10), @.c12, 101) else [PostDateShort] end
,[PostDateMonth] = case substring(@.bitmap,2,1) & 8 when 8 then Month(@.c12)
else [PostDateMonth] end
,[PostDateYear] = case substring(@.bitmap,2,1) & 8 when 8 then Year(@.c12)
else [PostDateYear] end
,[CreateDate] = case substring(@.bitmap,2,1) & 16 when 16 then @.c13 else
[CreateDate] end
,[MerchSIC] = case substring(@.bitmap,2,1) & 32 when 32 then @.c14 else
[MerchSIC] end
where [TransDtlKey] = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
go
if exists (select * from sysobjects where type = 'P' and name =
'sp_MSdel_TransDTL') drop proc [sp_MSdel_TransDTL]
go
create procedure [sp_MSdel_TransDTL] @.pkc1 int
as
delete [TransDTL]
where [TransDtlKey] = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
go
use ChrisR
go
declare @.counter int
set @.counter=1
while @.counter < 100
begin
insert into transdtl
(CustomerKey,SerialNbr,TranCode,TransDate,TransAmt ,RefNbr,MerchName,
City,State,RejectReason,PostDate,CreateDate,MerchS IC)
values
(@.counter,'t2','t3',getdate()-@.counter,@.counter+1,@.counter+2,'t4','t5','CA',
't6',getdate()-@.counter*10,getdate()-@.counter*100, 't7')
select @.counter=@.counter+1
end
go
--creating subscription
sp_addsubscription 'ChrisR','all', @.@.ServerName, 'ChrisRSub'
GO
--startup snapshot agent
DECLARE @.string varchar(200)
SET @.string = 'exec msdb.dbo.sp_start_job @.job_name =
'SELECT @.string = @.string + char(39) +
msdb.dbo.sysjobs.name + char(39) FROM
msdb.dbo.sysjobs,ChrisR.dbo.syspublications
WHERE ChrisR.dbo.syspublications.name = 'ChrisR'
AND ChrisR.dbo.syspublications.snapshot_jobid =
msdb.dbo.sysjobs.job_id
exec (@.string)
GO
use ChrisR
GO
update transdtl
set transdate=getdate()+365
where transdtlkey=1
go
select * from transdtl where transdtlkey=1
GO
use chrisRsub
GO
select * from transdtl where transdtlkey=1
GO
use ChrisR
GO
update transdtl
set transdate=getdate()+365
where transdtlkey=5
go
select * from transdtl where transdtlkey=5
GO
use chrisRsub
GO
select * from transdtl where transdtlkey=5
GO
use chrisR
GO
insert into
transdtl(TransDtlKey,CustomerKey,SerialNbr,TranCod e,TransDate,TransDateShort
,TransDateMonth,TransDateYear,TransAmt,RefNbr,Merc hName,City,State,RejectRea
son,PostDate,PostDateShort,PostDateMonth,PostDateY ear,CreateDate,MerchSIC)
select
TransDtlKey+100,CustomerKey,SerialNbr,TranCode,Tra nsDate,TransDateShort,Tran
sDateMonth,TransDateYear,TransAmt,RefNbr,MerchName ,City,State,RejectReason,P
ostDate,PostDateShort,PostDateMonth,PostDateYear,C reateDate,MerchSIC from
transdtl where transdtlkey=5
go
select * from transdtl where transdtlkey=105
use chrisRSUB
GO
select * from transdtl where transdtlkey=105
go
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:9c2001c486c6$432bff60$a501280a@.phx.gbl...[vbcol=seagreen]
> I thought it couldnt be done. Thats what I got from the
> replies to my post:
> sp_repladdcolumn on Indexed View
> 08/04/04 4:24 pm.
> Did I misunderstand?
>
>
> sp_repladdcolumn
> doesn't work on
> ChrisR and others can
> increased overhead
> upside is that there
> replication is concerned.
If you had more columns on Subscriber than Publisher,
would you be more likely to use Clustered Indexed Views or
Custom Sync Objects to implement 1 way, Transactional
Replication? Pros and Cons for each?
TIA, ChrisR
ChrisR,
I personally would prefer to use indexed views.
I suppose simplistically the downside is that there is increased overhead on
the publisher to maintain the materialized data. The upside is that there is
increased transparency so far as maintenance of replication is concerned.
Regards,
Paul Ibison
|||the problem is that for Chris Indexed Views won't support sp_repladdcolumn
and sp_repldropcolumn.
I am mystified as to why what works for me on my machine doesn't work on
ChrisRs. I am revisiting this script and I am hoping ChrisR and others can
test it. There may be a bug somewhere here.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OVm9MNphEHA.1356@.TK2MSFTNGP09.phx.gbl...
> ChrisR,
> I personally would prefer to use indexed views.
> I suppose simplistically the downside is that there is increased overhead
on
> the publisher to maintain the materialized data. The upside is that there
is
> increased transparency so far as maintenance of replication is concerned.
> Regards,
> Paul Ibison
>
|||I thought it couldnt be done. Thats what I got from the
replies to my post:
sp_repladdcolumn on Indexed View
08/04/04 4:24 pm.
Did I misunderstand?
>--Original Message--
>the problem is that for Chris Indexed Views won't support
sp_repladdcolumn
>and sp_repldropcolumn.
>I am mystified as to why what works for me on my machine
doesn't work on
>ChrisRs. I am revisiting this script and I am hoping
ChrisR and others can[vbcol=seagreen]
>test it. There may be a bug somewhere here.
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
>news:OVm9MNphEHA.1356@.TK2MSFTNGP09.phx.gbl...
increased overhead[vbcol=seagreen]
>on
upside is that there[vbcol=seagreen]
>is
replication is concerned.
>
>.
>
|||Sorry for the confusion.
You can't use sp_repladdcolumn or sp_repldropcolumn on an indexed view which
you are publishing for replication.
The Custom Sync object works for me.
Here is the script that works for me.
Here is my TransDTL.SQL script.
CREATE TABLE [dbo].[TransDtl] (
[TransDtlKey] [int] NOT NULL ,
[CustomerKey] [int] NULL ,
[SerialNbr] [char] (10),
[TranCode] [char] (4),
[TransDate] [smalldatetime] NULL ,
[TransDateShort] [char] (10),
[TransDateMonth] [tinyint] NULL ,
[TransDateYear] [smallint] NULL ,
[TransAmt] [money] NULL ,
[RefNbr] [char] (23),
[MerchName] [varchar] (25),
[City] [varchar] (15),
[State] [varchar] (3),
[RejectReason] [varchar] (15),
[PostDate] [datetime] NULL ,
[PostDateShort] [char] (10),
[PostDateMonth] [tinyint] NULL ,
[PostDateYear] [smallint] NULL ,
[CreateDate] [datetime] NULL ,
[MerchSIC] [char] (4)
) ON [PRIMARY]
GO
--this is my replication script
create database ChrisR
go
create database ChrisRSub
go
use ChrisR
go
CREATE TABLE TransDtl
(
TransDtlKey int IDENTITY(1, 1) NOT NULL PRIMARY KEY,
CustomerKey int NULL,
SerialNbr char(10),
TranCode char(4),
TransDate smalldatetime NOT NULL,
TransAmt money NOT NULL,
RefNbr char(23),
MerchName varchar(25),
City varchar(15),
State varchar(3),
RejectReason varchar(15),
PostDate datetime NOT NULL,
CreateDate datetime NOT NULL,
MerchSIC char(4)
)
GO
use chrisr
GO
Create View CustomSyncObject
as
select TransDtlKey=convert(int,TransDtlKey),
CustomerKey=convert(int,CustomerKey),
SerialNbr=convert(char(10),SerialNbr),
TranCode=convert(char(4),TranCode),
TransDate=convert(smalldatetime,TransDate),
TransDateShort = Convert(varchar(10),TransDate, 101),
TransDateMonth = convert(tinyint,Month(TransDate)),
TransDateYear = convert(smallint,Year(TransDate)),
TransAmt=convert(money, TransAmt),
RefNbr, MerchName, City, State, RejectReason, PostDate,
PostDateShort = Convert(varchar(10), PostDate, 101),
PostDateMonth = convert(tinyint, Month(PostDate)),
PostDateYear=convert(smallint, Year(PostDate)),
CreateDate,
MerchSIC from TransDtl
GO
sp_dboption 'ChrisR','published','true'
go
sp_addpublication 'ChrisR',@.status='active', @.sync_method = N'character'
go
sp_addpublication_snapshot 'ChrisR'
go
sp_addarticle @.publication = 'ChrisR',
@.article = 'TransDTL',
@.source_object = 'TransDTL',
@.destination_table = 'TransDTL',
@.type = 'logbased manualview',
@.sync_object='CustomSyncObject',
@.creation_script = 'c:\temp\TransDTL.sql',
@.pre_creation_cmd = 'delete',
@.schema_option = 0x0,
@.status = 8,
@.ins_cmd = 'CALL sp_MSins_TransDTL',
@.del_cmd = 'CALL sp_MSdel_TransDTL',
@.upd_cmd = 'MCALL sp_MSupd_TransDTL'
GO
use chrisRsub
go
if exists (select * from sysobjects where type = 'P' and name =
'sp_MSins_TransDTL') drop proc [sp_MSins_TransDTL]
go
create procedure [sp_MSins_TransDTL] @.c1 int,@.c2 int,@.c3 char(10),@.c4
char(4),@.c5 smalldatetime,@.c6 money,@.c7 char(23),@.c8 varchar(25),@.c9
varchar(15),@.c10 varchar(3),@.c11 varchar(15),@.c12 datetime,@.c13
datetime,@.c14 char(4)
AS
BEGIN
insert into [TransDTL](
[TransDtlKey], [CustomerKey], [SerialNbr], [TranCode],
[TransDate],[TransDateShort],
[TransDateMonth], [TransDateYear], [TransAmt], [RefNbr], [MerchName],
[City], [State],
[RejectReason], [PostDate],[PostDateShort],[PostDateMonth],[PostDateYear],
[CreateDate], [MerchSIC]
)
values (
@.c1, @.c2, @.c3, @.c4, @.c5, Convert(varchar(10), @.c5, 101), Month(@.c5),
Year(@.c5),
@.c6, @.c7, @.c8, @.c9, @.c10, @.c11, @.c12, Convert(varchar(10), @.c12, 101),
Month(@.c12), Year(@.c12), @.c13, @.c14
)
END
go
if exists (select * from sysobjects where type = 'P' and name =
'sp_MSupd_TransDTL') drop proc [sp_MSupd_TransDTL]
go
create procedure [sp_MSupd_TransDTL]
@.c1 int,@.c2 int,@.c3 char(10),@.c4 char(4),@.c5 smalldatetime,@.c6 money,@.c7
char(23),@.c8 varchar(25),@.c9 varchar(15),@.c10 varchar(3),@.c11
varchar(15),@.c12 datetime,@.c13 datetime,@.c14 char(4),@.pkc1 int
,@.bitmap binary(2)
as
if substring(@.bitmap,1,1) & 1 = 1
begin
update [TransDTL] set
[TransDtlKey] = case substring(@.bitmap,1,1) & 1 when 1 then @.c1 else
[TransDtlKey] end
,[CustomerKey] = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
[CustomerKey] end
,[SerialNbr] = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else
[SerialNbr] end
,[TranCode] = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
[TranCode] end
,[TransDate] = case substring(@.bitmap,1,1) & 16 when 16 then @.c5 else
[TransDate] end
,[TransDateShort]= case substring(@.bitmap,1,1) & 16 when 16 then
Convert(varchar(10), @.c5, 101) else [TransDateShort] end
,[TransDateMonth]= case substring(@.bitmap,1,1) & 16 when 16 then Month(@.c5)
else [TransDateMonth] end
,[TransDateYear]= case substring(@.bitmap,1,1) & 16 when 16 then Year(@.c5)
else [TransDateYear] end
,[TransAmt] = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
[TransAmt] end
,[RefNbr] = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else [RefNbr]
end
,[MerchName] = case substring(@.bitmap,1,1) & 128 when 128 then @.c8 else
[MerchName] end
,[City] = case substring(@.bitmap,2,1) & 1 when 1 then @.c9 else [City] end
,[State] = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else [State] end
,[RejectReason] = case substring(@.bitmap,2,1) & 4 when 4 then @.c11 else
[RejectReason] end
,[PostDate] = case substring(@.bitmap,2,1) & 8 when 8 then @.c12 else
[PostDate] end
,[PostDateShort] = case substring(@.bitmap,2,1) & 8 when 8 then
Convert(varchar(10), @.c12, 101) else [PostDateShort] end
,[PostDateMonth] = case substring(@.bitmap,2,1) & 8 when 8 then Month(@.c12)
else [PostDateMonth] end
,[PostDateYear] = case substring(@.bitmap,2,1) & 8 when 8 then Year(@.c12)
else [PostDateYear] end
,[CreateDate] = case substring(@.bitmap,2,1) & 16 when 16 then @.c13 else
[CreateDate] end
,[MerchSIC] = case substring(@.bitmap,2,1) & 32 when 32 then @.c14 else
[MerchSIC] end
where [TransDtlKey] = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
else
begin
update [TransDTL] set
[CustomerKey] = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
[CustomerKey] end
,[SerialNbr] = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else
[SerialNbr] end
,[TranCode] = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
[TranCode] end
,[TransDate] = case substring(@.bitmap,1,1) & 16 when 16 then @.c5 else
[TransDate] end
,[TransDateShort]= case substring(@.bitmap,1,1) & 16 when 16 then
Convert(varchar(10), @.c5, 101) else [TransDateShort] end
,[TransDateMonth]= case substring(@.bitmap,1,1) & 16 when 16 then Month(@.c5)
else [TransDateMonth] end
,[TransDateYear]= case substring(@.bitmap,1,1) & 16 when 16 then Year(@.c5)
else [TransDateYear] end
,[TransAmt] = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
[TransAmt] end
,[RefNbr] = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else [RefNbr]
end
,[MerchName] = case substring(@.bitmap,1,1) & 128 when 128 then @.c8 else
[MerchName] end
,[City] = case substring(@.bitmap,2,1) & 1 when 1 then @.c9 else [City] end
,[State] = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else [State] end
,[RejectReason] = case substring(@.bitmap,2,1) & 4 when 4 then @.c11 else
[RejectReason] end
,[PostDate] = case substring(@.bitmap,2,1) & 8 when 8 then @.c12 else
[PostDate] end
,[PostDateShort] = case substring(@.bitmap,2,1) & 8 when 8 then
Convert(varchar(10), @.c12, 101) else [PostDateShort] end
,[PostDateMonth] = case substring(@.bitmap,2,1) & 8 when 8 then Month(@.c12)
else [PostDateMonth] end
,[PostDateYear] = case substring(@.bitmap,2,1) & 8 when 8 then Year(@.c12)
else [PostDateYear] end
,[CreateDate] = case substring(@.bitmap,2,1) & 16 when 16 then @.c13 else
[CreateDate] end
,[MerchSIC] = case substring(@.bitmap,2,1) & 32 when 32 then @.c14 else
[MerchSIC] end
where [TransDtlKey] = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
go
if exists (select * from sysobjects where type = 'P' and name =
'sp_MSdel_TransDTL') drop proc [sp_MSdel_TransDTL]
go
create procedure [sp_MSdel_TransDTL] @.pkc1 int
as
delete [TransDTL]
where [TransDtlKey] = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
go
use ChrisR
go
declare @.counter int
set @.counter=1
while @.counter < 100
begin
insert into transdtl
(CustomerKey,SerialNbr,TranCode,TransDate,TransAmt ,RefNbr,MerchName,
City,State,RejectReason,PostDate,CreateDate,MerchS IC)
values
(@.counter,'t2','t3',getdate()-@.counter,@.counter+1,@.counter+2,'t4','t5','CA',
't6',getdate()-@.counter*10,getdate()-@.counter*100, 't7')
select @.counter=@.counter+1
end
go
--creating subscription
sp_addsubscription 'ChrisR','all', @.@.ServerName, 'ChrisRSub'
GO
--startup snapshot agent
DECLARE @.string varchar(200)
SET @.string = 'exec msdb.dbo.sp_start_job @.job_name =
'SELECT @.string = @.string + char(39) +
msdb.dbo.sysjobs.name + char(39) FROM
msdb.dbo.sysjobs,ChrisR.dbo.syspublications
WHERE ChrisR.dbo.syspublications.name = 'ChrisR'
AND ChrisR.dbo.syspublications.snapshot_jobid =
msdb.dbo.sysjobs.job_id
exec (@.string)
GO
use ChrisR
GO
update transdtl
set transdate=getdate()+365
where transdtlkey=1
go
select * from transdtl where transdtlkey=1
GO
use chrisRsub
GO
select * from transdtl where transdtlkey=1
GO
use ChrisR
GO
update transdtl
set transdate=getdate()+365
where transdtlkey=5
go
select * from transdtl where transdtlkey=5
GO
use chrisRsub
GO
select * from transdtl where transdtlkey=5
GO
use chrisR
GO
insert into
transdtl(TransDtlKey,CustomerKey,SerialNbr,TranCod e,TransDate,TransDateShort
,TransDateMonth,TransDateYear,TransAmt,RefNbr,Merc hName,City,State,RejectRea
son,PostDate,PostDateShort,PostDateMonth,PostDateY ear,CreateDate,MerchSIC)
select
TransDtlKey+100,CustomerKey,SerialNbr,TranCode,Tra nsDate,TransDateShort,Tran
sDateMonth,TransDateYear,TransAmt,RefNbr,MerchName ,City,State,RejectReason,P
ostDate,PostDateShort,PostDateMonth,PostDateYear,C reateDate,MerchSIC from
transdtl where transdtlkey=5
go
select * from transdtl where transdtlkey=105
use chrisRSUB
GO
select * from transdtl where transdtlkey=105
go
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:9c2001c486c6$432bff60$a501280a@.phx.gbl...[vbcol=seagreen]
> I thought it couldnt be done. Thats what I got from the
> replies to my post:
> sp_repladdcolumn on Indexed View
> 08/04/04 4:24 pm.
> Did I misunderstand?
>
>
> sp_repladdcolumn
> doesn't work on
> ChrisR and others can
> increased overhead
> upside is that there
> replication is concerned.
Sunday, February 12, 2012
Clustered Index quetion
I want to implement clustered indexes on some tables and have a couple of
questions. I read that a clustered index has its columns shared with
non-clustered indexes by default. Does that mean I can remove the columns
found in the clustered index from the non-clustered indexes?
NONCLUSTERED XKSomeTable1 ON SomeTable (InvoiceNumber)
NONCLUSTERED XKSomeTable2 ON SomeTable (InvoiceNumber, CustomerNumber)
NONCLUSTERED XKSomeTable2 ON SomeTable (InvoiceNumber, InvoiceDate)
If I switch #1 to a CLUSTERED can I switch 2 and 3 to this:
CLUSTERED XKSomeTable1 ON SomeTable (InvoiceNumber)
NONCLUSTERED XKSomeTable2 ON SomeTable (CustomerNumber)
NONCLUSTERED XKSomeTable2 ON SomeTable (InvoiceDate)
'
Thanks
RichardYes, the clustering key is autimatically a member of each non-clustered index. But you also need to
consider the column order. For instance: below two indexes are not the same:
NONCLUSTERED XKSomeTable2 ON SomeTable (InvoiceNumber, CustomerNumber)
NONCLUSTERED XKSomeTable2 ON SomeTable (CustomerNumber, InvoiceNumber)
So, to take one of your examples. If you have below clustered index:
> CLUSTERED XKSomeTable1 ON SomeTable (InvoiceNumber)
The below NC index:
> NONCLUSTERED XKSomeTable2 ON SomeTable (CustomerNumber)
Is the same as below:
NONCLUSTERED XKSomeTable2 ON SomeTable (CustomerNumber, InvoiceNumber)
Which is not the same as your original index:
> NONCLUSTERED XKSomeTable2 ON SomeTable (InvoiceNumber, CustomerNumber)
As an aside, I find it slightly odd to have below three indexes
> NONCLUSTERED XKSomeTable1 ON SomeTable (InvoiceNumber)
> NONCLUSTERED XKSomeTable2 ON SomeTable (InvoiceNumber, CustomerNumber)
> NONCLUSTERED XKSomeTable2 ON SomeTable (InvoiceNumber, InvoiceDate)
I would question the value of the first index. I.e., whether not the second and third are close to
as efffieient to use as the first one.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:OmVuXO8gIHA.3940@.TK2MSFTNGP05.phx.gbl...
>I want to implement clustered indexes on some tables and have a couple of questions. I read that a
>clustered index has its columns shared with non-clustered indexes by default. Does that mean I can
>remove the columns found in the clustered index from the non-clustered indexes?
> NONCLUSTERED XKSomeTable1 ON SomeTable (InvoiceNumber)
> NONCLUSTERED XKSomeTable2 ON SomeTable (InvoiceNumber, CustomerNumber)
> NONCLUSTERED XKSomeTable2 ON SomeTable (InvoiceNumber, InvoiceDate)
> If I switch #1 to a CLUSTERED can I switch 2 and 3 to this:
> CLUSTERED XKSomeTable1 ON SomeTable (InvoiceNumber)
> NONCLUSTERED XKSomeTable2 ON SomeTable (CustomerNumber)
> NONCLUSTERED XKSomeTable2 ON SomeTable (InvoiceDate)
> '
> Thanks
> Richard
>
questions. I read that a clustered index has its columns shared with
non-clustered indexes by default. Does that mean I can remove the columns
found in the clustered index from the non-clustered indexes?
NONCLUSTERED XKSomeTable1 ON SomeTable (InvoiceNumber)
NONCLUSTERED XKSomeTable2 ON SomeTable (InvoiceNumber, CustomerNumber)
NONCLUSTERED XKSomeTable2 ON SomeTable (InvoiceNumber, InvoiceDate)
If I switch #1 to a CLUSTERED can I switch 2 and 3 to this:
CLUSTERED XKSomeTable1 ON SomeTable (InvoiceNumber)
NONCLUSTERED XKSomeTable2 ON SomeTable (CustomerNumber)
NONCLUSTERED XKSomeTable2 ON SomeTable (InvoiceDate)
'
Thanks
RichardYes, the clustering key is autimatically a member of each non-clustered index. But you also need to
consider the column order. For instance: below two indexes are not the same:
NONCLUSTERED XKSomeTable2 ON SomeTable (InvoiceNumber, CustomerNumber)
NONCLUSTERED XKSomeTable2 ON SomeTable (CustomerNumber, InvoiceNumber)
So, to take one of your examples. If you have below clustered index:
> CLUSTERED XKSomeTable1 ON SomeTable (InvoiceNumber)
The below NC index:
> NONCLUSTERED XKSomeTable2 ON SomeTable (CustomerNumber)
Is the same as below:
NONCLUSTERED XKSomeTable2 ON SomeTable (CustomerNumber, InvoiceNumber)
Which is not the same as your original index:
> NONCLUSTERED XKSomeTable2 ON SomeTable (InvoiceNumber, CustomerNumber)
As an aside, I find it slightly odd to have below three indexes
> NONCLUSTERED XKSomeTable1 ON SomeTable (InvoiceNumber)
> NONCLUSTERED XKSomeTable2 ON SomeTable (InvoiceNumber, CustomerNumber)
> NONCLUSTERED XKSomeTable2 ON SomeTable (InvoiceNumber, InvoiceDate)
I would question the value of the first index. I.e., whether not the second and third are close to
as efffieient to use as the first one.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:OmVuXO8gIHA.3940@.TK2MSFTNGP05.phx.gbl...
>I want to implement clustered indexes on some tables and have a couple of questions. I read that a
>clustered index has its columns shared with non-clustered indexes by default. Does that mean I can
>remove the columns found in the clustered index from the non-clustered indexes?
> NONCLUSTERED XKSomeTable1 ON SomeTable (InvoiceNumber)
> NONCLUSTERED XKSomeTable2 ON SomeTable (InvoiceNumber, CustomerNumber)
> NONCLUSTERED XKSomeTable2 ON SomeTable (InvoiceNumber, InvoiceDate)
> If I switch #1 to a CLUSTERED can I switch 2 and 3 to this:
> CLUSTERED XKSomeTable1 ON SomeTable (InvoiceNumber)
> NONCLUSTERED XKSomeTable2 ON SomeTable (CustomerNumber)
> NONCLUSTERED XKSomeTable2 ON SomeTable (InvoiceDate)
> '
> Thanks
> Richard
>
Clustered index and varchar
One table I manage has a clustered index, and it includes some
varchar columns. When it is initially created, all the columns
in the clustered index are populated, and then some of the longer
varchars are populated through update queries. If the varchar
columns are stored outside the clustered structure, then it would
make sense to create the clustered index before populating the
varchar columns. Otherwise it would make sense to wait, because
populating the varchars might cause page splits. Are varchar
columns stored on the page along with the fixed-size columns, or
are they managed separately with the page containing pointers
to them?
varchar columns. When it is initially created, all the columns
in the clustered index are populated, and then some of the longer
varchars are populated through update queries. If the varchar
columns are stored outside the clustered structure, then it would
make sense to create the clustered index before populating the
varchar columns. Otherwise it would make sense to wait, because
populating the varchars might cause page splits. Are varchar
columns stored on the page along with the fixed-size columns, or
are they managed separately with the page containing pointers
to them?
Thanks,
Jim GeissmanOn 5 Aug 2005 15:09:48 -0700, jim_geissman@.countrywide.com wrote:
(snip)
>Are varchar
>columns stored on the page along with the fixed-size columns, or
>are they managed separately with the page containing pointers
>to them?
Hi Jim,
Varchar (and nvarchar, varbinary) columns are stored on the data page.
Text (and ntext, image) columns are stored on seperate pages, with only
a pointer on the data page. Unless the "text in row" option is set for
the table.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks, Hugo.
Jim|||(jim_geissman@.countrywide.com) writes:
> One table I manage has a clustered index, and it includes some
> varchar columns. When it is initially created, all the columns
> in the clustered index are populated, and then some of the longer
> varchars are populated through update queries. If the varchar
> columns are stored outside the clustered structure, then it would
> make sense to create the clustered index before populating the
> varchar columns. Otherwise it would make sense to wait, because
> populating the varchars might cause page splits. Are varchar
> columns stored on the page along with the fixed-size columns, or
> are they managed separately with the page containing pointers
> to them?
As Hugo said, the varchar data is stored within the page.
One should be careful with having to large clustered-index keys. In
non-clustered indexes, the value of clustered-index key is used as
row locator. Thus a wide clustered key, also affects the size of
the non-clustered index.
Of course, if you don't have any non-clustered indexes on the table,
this is not much of an issue.
--
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland,
Correct me if I'm mistaken but the values of the columns that make up the
clustered index are only used as the row locator if the index is set to
unique otherwise a uniqueidentifier is used. Either way the values are
still stored in all the nonclustered indexes so wide clustered indexes are
generally unpleasant. Small clustered indexes like a single int can still
make the nonclustered indexes large if when not set to unique.
At least this is always what appears to happen...
Danny
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96AA8DD847A5CYazorman@.127.0.0.1...
> (jim_geissman@.countrywide.com) writes:
>> One table I manage has a clustered index, and it includes some
>> varchar columns. When it is initially created, all the columns
>> in the clustered index are populated, and then some of the longer
>> varchars are populated through update queries. If the varchar
>> columns are stored outside the clustered structure, then it would
>> make sense to create the clustered index before populating the
>> varchar columns. Otherwise it would make sense to wait, because
>> populating the varchars might cause page splits. Are varchar
>> columns stored on the page along with the fixed-size columns, or
>> are they managed separately with the page containing pointers
>> to them?
> As Hugo said, the varchar data is stored within the page.
> One should be careful with having to large clustered-index keys. In
> non-clustered indexes, the value of clustered-index key is used as
> row locator. Thus a wide clustered key, also affects the size of
> the non-clustered index.
> Of course, if you don't have any non-clustered indexes on the table,
> this is not much of an issue.
>
> --
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Danny (someone@.nowhere.com) writes:
> Correct me if I'm mistaken but the values of the columns that make up the
> clustered index are only used as the row locator if the index is set to
> unique otherwise a uniqueidentifier is used. Either way the values are
> still stored in all the nonclustered indexes so wide clustered indexes are
> generally unpleasant. Small clustered indexes like a single int can still
> make the nonclustered indexes large if when not set to unique.
> At least this is always what appears to happen...
The clustered index is always used as a row locator.
When the clustered index is not unique, SQL Server adds a 32-bit
"uniquifier". That is, not a 128-bit uniqueidentifier.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Clustered Index and PK on GUID
Table has two columns
CODE varchar(10)
GUID uniqueidentifier,Rowguid (must be there for replication purposes)
When we update a row the CODE column is checked (CODE is unique)
What is better:
- to put primary key on GUID, clustered index and unique index on CODE
- to put primary key on CODE (with clustered index)
Thanx in advance.
½½½½
Martin Bajc> What is better:
That depends. What do you consider "better"?
--
http://www.aspfaq.com/
(Reverse address to reply.)|||"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:2FEF2DBA-3766-4FCC-86BA-034E90781AE5@.microsoft.com...
> CODE column, even better, make the CODE column CHAR(10). Fixed with
columns
> are better for indexes.
In what way?|||...better cosidering type of replication, number of rows in table... and
also developer's effort
(visio and EM puts automaticly clustered index on PK, so second choice seems
better to me uless I've missed something)
****
Martin Bajc
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eKcdWdKoEHA.132@.TK2MSFTNGP14.phx.gbl...
> > What is better:
> That depends. What do you consider "better"?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>|||Hi
CODE column, even better, make the CODE column CHAR(10). Fixed with columns
are better for indexes.
A GUID is not bad for an index, but not optimal.
"Martin Bajc" wrote:
> Table has two columns
> CODE varchar(10)
> GUID uniqueidentifier,Rowguid (must be there for replication purposes)
> When we update a row the CODE column is checked (CODE is unique)
>
> What is better:
> - to put primary key on GUID, clustered index and unique index on CODE
> - to put primary key on CODE (with clustered index)
> Thanx in advance.
> ½½½½
> Martin Bajc
>
>
>
>
>
CODE varchar(10)
GUID uniqueidentifier,Rowguid (must be there for replication purposes)
When we update a row the CODE column is checked (CODE is unique)
What is better:
- to put primary key on GUID, clustered index and unique index on CODE
- to put primary key on CODE (with clustered index)
Thanx in advance.
½½½½
Martin Bajc> What is better:
That depends. What do you consider "better"?
--
http://www.aspfaq.com/
(Reverse address to reply.)|||"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:2FEF2DBA-3766-4FCC-86BA-034E90781AE5@.microsoft.com...
> CODE column, even better, make the CODE column CHAR(10). Fixed with
columns
> are better for indexes.
In what way?|||...better cosidering type of replication, number of rows in table... and
also developer's effort
(visio and EM puts automaticly clustered index on PK, so second choice seems
better to me uless I've missed something)
****
Martin Bajc
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eKcdWdKoEHA.132@.TK2MSFTNGP14.phx.gbl...
> > What is better:
> That depends. What do you consider "better"?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>|||Hi
CODE column, even better, make the CODE column CHAR(10). Fixed with columns
are better for indexes.
A GUID is not bad for an index, but not optimal.
"Martin Bajc" wrote:
> Table has two columns
> CODE varchar(10)
> GUID uniqueidentifier,Rowguid (must be there for replication purposes)
> When we update a row the CODE column is checked (CODE is unique)
>
> What is better:
> - to put primary key on GUID, clustered index and unique index on CODE
> - to put primary key on CODE (with clustered index)
> Thanx in advance.
> ½½½½
> Martin Bajc
>
>
>
>
>
Friday, February 10, 2012
Clustered columns confusion
Hi,
I am having a primary key in my table as Clustered.
But when I fire an SQL Update (I am updating an Image type column!) Query th
rough my Java (JDBC) code,I get an exception.
If the same column was not clustered then the exception doesnt appear and up
date fires properly.
Any ideas why something like this should happen.
Thanks in Adv,
ChinmayHi
What kind of exeption did you get?
Do you have PRIMARY KEY on image type column?
"Chinmay" <anonymous@.discussions.microsoft.com> wrote in message
news:770F912B-465B-499E-B5BC-E21D67787B42@.microsoft.com...
> Hi,
> I am having a primary key in my table as Clustered.
> But when I fire an SQL Update (I am updating an Image type column!) Query
through my Java (JDBC) code,I get an exception.
> If the same column was not clustered then the exception doesnt appear and
update fires properly.
> Any ideas why something like this should happen.
> Thanks in Adv,
> Chinmay|||I got it some weeks back.
Yes and i dont have a Primary Key on Image column.
The Primary Key is on a normal varchar column.
The thing is now I dont get it.
Also the exception that I got was comething like "Could not prepare a Query
Plan for clustered....."
Also I found this artical on the Microsoft site...(but I m confused still)
"http://support.microsoft.com/default.aspx?scid=kb;%5BLN%5D;818729"
Thanks,
Chinmay
-- Uri Dimant wrote: --
Hi
What kind of exeption did you get?
Do you have PRIMARY KEY on image type column?
"Chinmay" <anonymous@.discussions.microsoft.com> wrote in message
news:770F912B-465B-499E-B5BC-E21D67787B42@.microsoft.com...
> Hi,
> I am having a primary key in my table as Clustered.
> But when I fire an SQL Update (I am updating an Image type column!) Query
through my Java (JDBC) code,I get an exception.
> If the same column was not clustered then the exception doesnt appear and
update fires properly.
> Any ideas why something like this should happen.
> Chinmay|||Hi
http://www.microsoft.com/technet/tr...chnet/prodtechn
ol/sql/reskit/sql7res/part9/sqc13.asp
"Chinmay" <anonymous@.discussions.microsoft.com> wrote in message
news:CB5EF10C-6B9E-4591-BDBD-05C9C739482E@.microsoft.com...
> I got it some weeks back.
> Yes and i dont have a Primary Key on Image column.
> The Primary Key is on a normal varchar column.
> The thing is now I dont get it.
> Also the exception that I got was comething like "Could not prepare a
Query Plan for clustered....."
> Also I found this artical on the Microsoft site...(but I m confused
still)
> "http://support.microsoft.com/default.aspx?scid=kb;%5BLN%5D;818729"
> Thanks,
> Chinmay
>
> -- Uri Dimant wrote: --
> Hi
> What kind of exeption did you get?
> Do you have PRIMARY KEY on image type column?
> "Chinmay" <anonymous@.discussions.microsoft.com> wrote in message
> news:770F912B-465B-499E-B5BC-E21D67787B42@.microsoft.com...
Query
> through my Java (JDBC) code,I get an exception.
appear and
> update fires properly.
>
>|||thanks Uri for the link...
But have u or anybdy have come across succha problem bfor.
I just realised that I have 2 Image columns being updated and the clustered
column is in the where clause.
The whole issue is that we are not able to regenerate it again.
thanks,
Chinmay
-- Uri Dimant wrote: --
Hi
http://www.microsoft.com/technet/tr...chnet/prodtechn
ol/sql/reskit/sql7res/part9/sqc13.asp
"Chinmay" <anonymous@.discussions.microsoft.com> wrote in message
news:CB5EF10C-6B9E-4591-BDBD-05C9C739482E@.microsoft.com...
> Yes and i dont have a Primary Key on Image column.
> The Primary Key is on a normal varchar column.
> The thing is now I dont get it.
> Also the exception that I got was comething like "Could not prepare a
Query Plan for clustered....."
still)
> Chinmay
> What kind of exeption did you get?
> Do you have PRIMARY KEY on image type column?
> news:770F912B-465B-499E-B5BC-E21D67787B42@.microsoft.com...
Query
> through my Java (JDBC) code,I get an exception.
appear and
> update fires properly.
I am having a primary key in my table as Clustered.
But when I fire an SQL Update (I am updating an Image type column!) Query th
rough my Java (JDBC) code,I get an exception.
If the same column was not clustered then the exception doesnt appear and up
date fires properly.
Any ideas why something like this should happen.
Thanks in Adv,
ChinmayHi
What kind of exeption did you get?
Do you have PRIMARY KEY on image type column?
"Chinmay" <anonymous@.discussions.microsoft.com> wrote in message
news:770F912B-465B-499E-B5BC-E21D67787B42@.microsoft.com...
> Hi,
> I am having a primary key in my table as Clustered.
> But when I fire an SQL Update (I am updating an Image type column!) Query
through my Java (JDBC) code,I get an exception.
> If the same column was not clustered then the exception doesnt appear and
update fires properly.
> Any ideas why something like this should happen.
> Thanks in Adv,
> Chinmay|||I got it some weeks back.
Yes and i dont have a Primary Key on Image column.
The Primary Key is on a normal varchar column.
The thing is now I dont get it.
Also the exception that I got was comething like "Could not prepare a Query
Plan for clustered....."
Also I found this artical on the Microsoft site...(but I m confused still)
"http://support.microsoft.com/default.aspx?scid=kb;%5BLN%5D;818729"
Thanks,
Chinmay
-- Uri Dimant wrote: --
Hi
What kind of exeption did you get?
Do you have PRIMARY KEY on image type column?
"Chinmay" <anonymous@.discussions.microsoft.com> wrote in message
news:770F912B-465B-499E-B5BC-E21D67787B42@.microsoft.com...
> Hi,
> I am having a primary key in my table as Clustered.
> But when I fire an SQL Update (I am updating an Image type column!) Query
through my Java (JDBC) code,I get an exception.
> If the same column was not clustered then the exception doesnt appear and
update fires properly.
> Any ideas why something like this should happen.
> Chinmay|||Hi
http://www.microsoft.com/technet/tr...chnet/prodtechn
ol/sql/reskit/sql7res/part9/sqc13.asp
"Chinmay" <anonymous@.discussions.microsoft.com> wrote in message
news:CB5EF10C-6B9E-4591-BDBD-05C9C739482E@.microsoft.com...
> I got it some weeks back.
> Yes and i dont have a Primary Key on Image column.
> The Primary Key is on a normal varchar column.
> The thing is now I dont get it.
> Also the exception that I got was comething like "Could not prepare a
Query Plan for clustered....."
> Also I found this artical on the Microsoft site...(but I m confused
still)
> "http://support.microsoft.com/default.aspx?scid=kb;%5BLN%5D;818729"
> Thanks,
> Chinmay
>
> -- Uri Dimant wrote: --
> Hi
> What kind of exeption did you get?
> Do you have PRIMARY KEY on image type column?
> "Chinmay" <anonymous@.discussions.microsoft.com> wrote in message
> news:770F912B-465B-499E-B5BC-E21D67787B42@.microsoft.com...
Query
> through my Java (JDBC) code,I get an exception.
appear and
> update fires properly.
>
>|||thanks Uri for the link...
But have u or anybdy have come across succha problem bfor.
I just realised that I have 2 Image columns being updated and the clustered
column is in the where clause.
The whole issue is that we are not able to regenerate it again.
thanks,
Chinmay
-- Uri Dimant wrote: --
Hi
http://www.microsoft.com/technet/tr...chnet/prodtechn
ol/sql/reskit/sql7res/part9/sqc13.asp
"Chinmay" <anonymous@.discussions.microsoft.com> wrote in message
news:CB5EF10C-6B9E-4591-BDBD-05C9C739482E@.microsoft.com...
> Yes and i dont have a Primary Key on Image column.
> The Primary Key is on a normal varchar column.
> The thing is now I dont get it.
> Also the exception that I got was comething like "Could not prepare a
Query Plan for clustered....."
still)
> Chinmay
> What kind of exeption did you get?
> Do you have PRIMARY KEY on image type column?
> news:770F912B-465B-499E-B5BC-E21D67787B42@.microsoft.com...
Query
> through my Java (JDBC) code,I get an exception.
appear and
> update fires properly.
Clustered columns confusion
Hi
I am having a primary key in my table as Clustered
But when I fire an SQL Update (I am updating an Image type column!) Query through my Java (JDBC) code,I get an exception
If the same column was not clustered then the exception doesnt appear and update fires properly
Any ideas why something like this should happen
Thanks in Adv
ChinmayHi
What kind of exeption did you get?
Do you have PRIMARY KEY on image type column?
"Chinmay" <anonymous@.discussions.microsoft.com> wrote in message
news:770F912B-465B-499E-B5BC-E21D67787B42@.microsoft.com...
> Hi,
> I am having a primary key in my table as Clustered.
> But when I fire an SQL Update (I am updating an Image type column!) Query
through my Java (JDBC) code,I get an exception.
> If the same column was not clustered then the exception doesnt appear and
update fires properly.
> Any ideas why something like this should happen.
> Thanks in Adv,
> Chinmay|||I got it some weeks back
Yes and i dont have a Primary Key on Image column
The Primary Key is on a normal varchar column.
The thing is now I dont get it
Also the exception that I got was comething like "Could not prepare a Query Plan for clustered.....
Also I found this artical on the Microsoft site...(but I m confused still
"http://support.microsoft.com/default.aspx?scid=kb;%5BLN%5D;818729
Thanks
Chinma
-- Uri Dimant wrote: --
H
What kind of exeption did you get
Do you have PRIMARY KEY on image type column
"Chinmay" <anonymous@.discussions.microsoft.com> wrote in messag
news:770F912B-465B-499E-B5BC-E21D67787B42@.microsoft.com..
> Hi
> I am having a primary key in my table as Clustered
> But when I fire an SQL Update (I am updating an Image type column!) Quer
through my Java (JDBC) code,I get an exception
> If the same column was not clustered then the exception doesnt appear an
update fires properly
> Any ideas why something like this should happen
>> Thanks in Adv
> Chinma|||Hi
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechn
ol/sql/reskit/sql7res/part9/sqc13.asp
"Chinmay" <anonymous@.discussions.microsoft.com> wrote in message
news:CB5EF10C-6B9E-4591-BDBD-05C9C739482E@.microsoft.com...
> I got it some weeks back.
> Yes and i dont have a Primary Key on Image column.
> The Primary Key is on a normal varchar column.
> The thing is now I dont get it.
> Also the exception that I got was comething like "Could not prepare a
Query Plan for clustered....."
> Also I found this artical on the Microsoft site...(but I m confused
still)
> "http://support.microsoft.com/default.aspx?scid=kb;%5BLN%5D;818729"
> Thanks,
> Chinmay
>
> -- Uri Dimant wrote: --
> Hi
> What kind of exeption did you get?
> Do you have PRIMARY KEY on image type column?
> "Chinmay" <anonymous@.discussions.microsoft.com> wrote in message
> news:770F912B-465B-499E-B5BC-E21D67787B42@.microsoft.com...
> > Hi,
> > I am having a primary key in my table as Clustered.
> > But when I fire an SQL Update (I am updating an Image type column!)
Query
> through my Java (JDBC) code,I get an exception.
> > If the same column was not clustered then the exception doesnt
appear and
> update fires properly.
> > Any ideas why something like this should happen.
> >> Thanks in Adv,
> > Chinmay
>
>|||thanks Uri for the link...
But have u or anybdy have come across succha problem bfor
I just realised that I have 2 Image columns being updated and the clustered column is in the where clause
The whole issue is that we are not able to regenerate it again
thanks
Chinma
-- Uri Dimant wrote: --
H
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtech
ol/sql/reskit/sql7res/part9/sqc13.as
"Chinmay" <anonymous@.discussions.microsoft.com> wrote in messag
news:CB5EF10C-6B9E-4591-BDBD-05C9C739482E@.microsoft.com..
>> I got it some weeks back
> Yes and i dont have a Primary Key on Image column
> The Primary Key is on a normal varchar column
> The thing is now I dont get it
> Also the exception that I got was comething like "Could not prepare
Query Plan for clustered.....
>> Also I found this artical on the Microsoft site...(but I m confuse
still
>> "http://support.microsoft.com/default.aspx?scid=kb;%5BLN%5D;818729
>> Thanks
> Chinma
>> -- Uri Dimant wrote: --
>> H
> What kind of exeption did you get
> Do you have PRIMARY KEY on image type column
>> "Chinmay" <anonymous@.discussions.microsoft.com> wrote in messag
> news:770F912B-465B-499E-B5BC-E21D67787B42@.microsoft.com..
>> Hi
>> I am having a primary key in my table as Clustered
>> But when I fire an SQL Update (I am updating an Image type column!
Quer
> through my Java (JDBC) code,I get an exception
>> If the same column was not clustered then the exception doesn
appear an
> update fires properly
>> Any ideas why something like this should happen
>> Thanks in Adv
>> Chinma
>>
I am having a primary key in my table as Clustered
But when I fire an SQL Update (I am updating an Image type column!) Query through my Java (JDBC) code,I get an exception
If the same column was not clustered then the exception doesnt appear and update fires properly
Any ideas why something like this should happen
Thanks in Adv
ChinmayHi
What kind of exeption did you get?
Do you have PRIMARY KEY on image type column?
"Chinmay" <anonymous@.discussions.microsoft.com> wrote in message
news:770F912B-465B-499E-B5BC-E21D67787B42@.microsoft.com...
> Hi,
> I am having a primary key in my table as Clustered.
> But when I fire an SQL Update (I am updating an Image type column!) Query
through my Java (JDBC) code,I get an exception.
> If the same column was not clustered then the exception doesnt appear and
update fires properly.
> Any ideas why something like this should happen.
> Thanks in Adv,
> Chinmay|||I got it some weeks back
Yes and i dont have a Primary Key on Image column
The Primary Key is on a normal varchar column.
The thing is now I dont get it
Also the exception that I got was comething like "Could not prepare a Query Plan for clustered.....
Also I found this artical on the Microsoft site...(but I m confused still
"http://support.microsoft.com/default.aspx?scid=kb;%5BLN%5D;818729
Thanks
Chinma
-- Uri Dimant wrote: --
H
What kind of exeption did you get
Do you have PRIMARY KEY on image type column
"Chinmay" <anonymous@.discussions.microsoft.com> wrote in messag
news:770F912B-465B-499E-B5BC-E21D67787B42@.microsoft.com..
> Hi
> I am having a primary key in my table as Clustered
> But when I fire an SQL Update (I am updating an Image type column!) Quer
through my Java (JDBC) code,I get an exception
> If the same column was not clustered then the exception doesnt appear an
update fires properly
> Any ideas why something like this should happen
>> Thanks in Adv
> Chinma|||Hi
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechn
ol/sql/reskit/sql7res/part9/sqc13.asp
"Chinmay" <anonymous@.discussions.microsoft.com> wrote in message
news:CB5EF10C-6B9E-4591-BDBD-05C9C739482E@.microsoft.com...
> I got it some weeks back.
> Yes and i dont have a Primary Key on Image column.
> The Primary Key is on a normal varchar column.
> The thing is now I dont get it.
> Also the exception that I got was comething like "Could not prepare a
Query Plan for clustered....."
> Also I found this artical on the Microsoft site...(but I m confused
still)
> "http://support.microsoft.com/default.aspx?scid=kb;%5BLN%5D;818729"
> Thanks,
> Chinmay
>
> -- Uri Dimant wrote: --
> Hi
> What kind of exeption did you get?
> Do you have PRIMARY KEY on image type column?
> "Chinmay" <anonymous@.discussions.microsoft.com> wrote in message
> news:770F912B-465B-499E-B5BC-E21D67787B42@.microsoft.com...
> > Hi,
> > I am having a primary key in my table as Clustered.
> > But when I fire an SQL Update (I am updating an Image type column!)
Query
> through my Java (JDBC) code,I get an exception.
> > If the same column was not clustered then the exception doesnt
appear and
> update fires properly.
> > Any ideas why something like this should happen.
> >> Thanks in Adv,
> > Chinmay
>
>|||thanks Uri for the link...
But have u or anybdy have come across succha problem bfor
I just realised that I have 2 Image columns being updated and the clustered column is in the where clause
The whole issue is that we are not able to regenerate it again
thanks
Chinma
-- Uri Dimant wrote: --
H
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtech
ol/sql/reskit/sql7res/part9/sqc13.as
"Chinmay" <anonymous@.discussions.microsoft.com> wrote in messag
news:CB5EF10C-6B9E-4591-BDBD-05C9C739482E@.microsoft.com..
>> I got it some weeks back
> Yes and i dont have a Primary Key on Image column
> The Primary Key is on a normal varchar column
> The thing is now I dont get it
> Also the exception that I got was comething like "Could not prepare
Query Plan for clustered.....
>> Also I found this artical on the Microsoft site...(but I m confuse
still
>> "http://support.microsoft.com/default.aspx?scid=kb;%5BLN%5D;818729
>> Thanks
> Chinma
>> -- Uri Dimant wrote: --
>> H
> What kind of exeption did you get
> Do you have PRIMARY KEY on image type column
>> "Chinmay" <anonymous@.discussions.microsoft.com> wrote in messag
> news:770F912B-465B-499E-B5BC-E21D67787B42@.microsoft.com..
>> Hi
>> I am having a primary key in my table as Clustered
>> But when I fire an SQL Update (I am updating an Image type column!
Quer
> through my Java (JDBC) code,I get an exception
>> If the same column was not clustered then the exception doesn
appear an
> update fires properly
>> Any ideas why something like this should happen
>> Thanks in Adv
>> Chinma
>>
Subscribe to:
Posts (Atom)