When using the Kimball templates for designing Dimension tables, there are 3
columns I have questions about.
Isn't the RowStartDate and RowEndDate the way to manage historical values?
If so; why is the need for RowIsCurrent?
In my example dimension below, it is capturing slow changing phone numbers.
Is the RowIsCurrent just a better faster way to find the most current recor
ds instead of querying the most recent dates?
Client Name: Phone Number: RowStartDate: RowEndDate; RowIsCu
rrent
Joe Momma (360) 533-3232 2/1/2005 2/18/2005 N
Greg Olson (360) 822-2323 3/4/2005 12/31/9999 Y
Joe Momma (360) 331-8800 2/18/2005 12/31/9999 YOn Apr 12, 7:42 pm, "Joe" <hortoris...@.gmail.dot.com> wrote:
> When using the Kimball templates for designing Dimension tables, there are
3 columns I have questions about.
> Isn't the RowStartDate and RowEndDate the way to manage historical values?
If so; why is the need for RowIsCurrent?
> In my example dimension below, it is capturing slow changing phone numbers
. Is the RowIsCurrent just a better faster way to find the most current rec
ords instead of querying the most recent dates?
> Client Name: Phone Number: RowStartDate: RowEndDate; RowIs
Current
> Joe Momma (360) 533-3232 2/1/2005 2/18/2005
N
> Greg Olson (360) 822-2323 3/4/2005 12/31/9999
Y
> Joe Momma (360) 331-8800 2/18/2005 12/31/9999
Y
Joe,
if you think about the sql require to get 'the most recent record'
using a date versus using a flag you will see why we have the
flags....if you don't want to just accept that this is how it is
done...write the sql and check it out.
Best Regards
Peter
www.peternolan.com
Showing posts with label columns. Show all posts
Showing posts with label columns. Show all posts
Wednesday, March 28, 2012
Kimball Templates
When using the Kimball templates for designing Dimension tables, there are 3 columns I have questions about.
Isn't the RowStartDate and RowEndDate the way to manage historical values? If so; why is the need for RowIsCurrent?
In my example dimension below, it is capturing slow changing phone numbers. Is the RowIsCurrent just a better faster way to find the most current records instead of querying the most recent dates?
Client Name: Phone Number: RowStartDate: RowEndDate; RowIsCurrent
Joe Momma (360) 533-3232 2/1/2005 2/18/2005 N
Greg Olson (360) 822-2323 3/4/2005 12/31/9999 Y
Joe Momma (360) 331-8800 2/18/2005 12/31/9999 Y
On Apr 12, 7:42 pm, "Joe" <hortoris...@.gmail.dot.com> wrote:
> When using the Kimball templates for designing Dimension tables, there are 3 columns I have questions about.
> Isn't the RowStartDate and RowEndDate the way to manage historical values? If so; why is the need for RowIsCurrent?
> In my example dimension below, it is capturing slow changing phone numbers. Is the RowIsCurrent just a better faster way to find the most current records instead of querying the most recent dates?
> Client Name: Phone Number: RowStartDate: RowEndDate; RowIsCurrent
> Joe Momma (360) 533-3232 2/1/2005 2/18/2005 N
> Greg Olson (360) 822-2323 3/4/2005 12/31/9999 Y
> Joe Momma (360) 331-8800 2/18/2005 12/31/9999 Y
Joe,
if you think about the sql require to get 'the most recent record'
using a date versus using a flag you will see why we have the
flags....if you don't want to just accept that this is how it is
done...write the sql and check it out.
Best Regards
Peter
www.peternolan.com
Isn't the RowStartDate and RowEndDate the way to manage historical values? If so; why is the need for RowIsCurrent?
In my example dimension below, it is capturing slow changing phone numbers. Is the RowIsCurrent just a better faster way to find the most current records instead of querying the most recent dates?
Client Name: Phone Number: RowStartDate: RowEndDate; RowIsCurrent
Joe Momma (360) 533-3232 2/1/2005 2/18/2005 N
Greg Olson (360) 822-2323 3/4/2005 12/31/9999 Y
Joe Momma (360) 331-8800 2/18/2005 12/31/9999 Y
On Apr 12, 7:42 pm, "Joe" <hortoris...@.gmail.dot.com> wrote:
> When using the Kimball templates for designing Dimension tables, there are 3 columns I have questions about.
> Isn't the RowStartDate and RowEndDate the way to manage historical values? If so; why is the need for RowIsCurrent?
> In my example dimension below, it is capturing slow changing phone numbers. Is the RowIsCurrent just a better faster way to find the most current records instead of querying the most recent dates?
> Client Name: Phone Number: RowStartDate: RowEndDate; RowIsCurrent
> Joe Momma (360) 533-3232 2/1/2005 2/18/2005 N
> Greg Olson (360) 822-2323 3/4/2005 12/31/9999 Y
> Joe Momma (360) 331-8800 2/18/2005 12/31/9999 Y
Joe,
if you think about the sql require to get 'the most recent record'
using a date versus using a flag you will see why we have the
flags....if you don't want to just accept that this is how it is
done...write the sql and check it out.
Best Regards
Peter
www.peternolan.com
Monday, March 19, 2012
Keyword Function in SQL Server 8.0 but not in 7.0
Hi,
SQL server 8.0 has "function" as keyword but version 7.0
doesn't. I have a table that has two columns labeled
Module, and function. I have a store procedure that calls
this two columns but since I swicht from SQL Server 7.0 to
8.0 the function its a keyword in 8.0...and I can't store
my records when I swicth to SQL version 8.0
How I can force the SP to take the name of a column as a
field instead of a keyword? I tried to place brakets but
still I can't run my store procedure...in other words I
can't store new records in my table whose field's name is
a keyword...
I define my table like this.
...
[Module]
[Function]
...using brackets...but nothing...any ideas?
Thanks,
Patty
*******************SP*******************
***********
ALTER PROCEDURE sp_LogErrors
@.ID int, @.Number int, @.Description varchar(255),
@.Application varchar(30), @.Version varchar(30), @.Source
varchar(30), @.Module varchar(30),
@.Function varchar(30), @.Occurred DateTime, @.SBCID
varchar(30), @.Machine varchar(30)
AS
INSERT INTO Error (ID, Number, Description, Application,
Version, Source, [Module], [Function], Occurred,
Machine )
values (@.ID, @.Number, @.Description, @.Application,
@.Version, @.Source, @.Module, @.Function, @.Occurred,
@.Machine)What error message do you get if you execute that procedure from Query
Analyzer?
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Patty" <anonymous@.discussions.microsoft.com> wrote in message
news:b58901c3ecd3$aa67cd90$a301280a@.phx.gbl...
> Hi,
> SQL server 8.0 has "function" as keyword but version 7.0
> doesn't. I have a table that has two columns labeled
> Module, and function. I have a store procedure that calls
> this two columns but since I swicht from SQL Server 7.0 to
> 8.0 the function its a keyword in 8.0...and I can't store
> my records when I swicth to SQL version 8.0
> How I can force the SP to take the name of a column as a
> field instead of a keyword? I tried to place brakets but
> still I can't run my store procedure...in other words I
> can't store new records in my table whose field's name is
> a keyword...
> I define my table like this.
> ...
> [Module]
> [Function]
> ...using brackets...but nothing...any ideas?
> Thanks,
> Patty
> *******************SP*******************
***********
> ALTER PROCEDURE sp_LogErrors
> @.ID int, @.Number int, @.Description varchar(255),
> @.Application varchar(30), @.Version varchar(30), @.Source
> varchar(30), @.Module varchar(30),
> @.Function varchar(30), @.Occurred DateTime, @.SBCID
> varchar(30), @.Machine varchar(30)
> AS
> INSERT INTO Error (ID, Number, Description, Application,
> Version, Source, [Module], [Function], Occurred,
> Machine )
> values (@.ID, @.Number, @.Description, @.Application,
> @.Version, @.Source, @.Module, @.Function, @.Occurred,
> @.Machine)
>
>
SQL server 8.0 has "function" as keyword but version 7.0
doesn't. I have a table that has two columns labeled
Module, and function. I have a store procedure that calls
this two columns but since I swicht from SQL Server 7.0 to
8.0 the function its a keyword in 8.0...and I can't store
my records when I swicth to SQL version 8.0
How I can force the SP to take the name of a column as a
field instead of a keyword? I tried to place brakets but
still I can't run my store procedure...in other words I
can't store new records in my table whose field's name is
a keyword...
I define my table like this.
...
[Module]
[Function]
...using brackets...but nothing...any ideas?
Thanks,
Patty
*******************SP*******************
***********
ALTER PROCEDURE sp_LogErrors
@.ID int, @.Number int, @.Description varchar(255),
@.Application varchar(30), @.Version varchar(30), @.Source
varchar(30), @.Module varchar(30),
@.Function varchar(30), @.Occurred DateTime, @.SBCID
varchar(30), @.Machine varchar(30)
AS
INSERT INTO Error (ID, Number, Description, Application,
Version, Source, [Module], [Function], Occurred,
Machine )
values (@.ID, @.Number, @.Description, @.Application,
@.Version, @.Source, @.Module, @.Function, @.Occurred,
@.Machine)What error message do you get if you execute that procedure from Query
Analyzer?
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Patty" <anonymous@.discussions.microsoft.com> wrote in message
news:b58901c3ecd3$aa67cd90$a301280a@.phx.gbl...
> Hi,
> SQL server 8.0 has "function" as keyword but version 7.0
> doesn't. I have a table that has two columns labeled
> Module, and function. I have a store procedure that calls
> this two columns but since I swicht from SQL Server 7.0 to
> 8.0 the function its a keyword in 8.0...and I can't store
> my records when I swicth to SQL version 8.0
> How I can force the SP to take the name of a column as a
> field instead of a keyword? I tried to place brakets but
> still I can't run my store procedure...in other words I
> can't store new records in my table whose field's name is
> a keyword...
> I define my table like this.
> ...
> [Module]
> [Function]
> ...using brackets...but nothing...any ideas?
> Thanks,
> Patty
> *******************SP*******************
***********
> ALTER PROCEDURE sp_LogErrors
> @.ID int, @.Number int, @.Description varchar(255),
> @.Application varchar(30), @.Version varchar(30), @.Source
> varchar(30), @.Module varchar(30),
> @.Function varchar(30), @.Occurred DateTime, @.SBCID
> varchar(30), @.Machine varchar(30)
> AS
> INSERT INTO Error (ID, Number, Description, Application,
> Version, Source, [Module], [Function], Occurred,
> Machine )
> values (@.ID, @.Number, @.Description, @.Application,
> @.Version, @.Source, @.Module, @.Function, @.Occurred,
> @.Machine)
>
>
Keyword Function in SQL Server 8.0 but not in 7.0
Hi,
SQL server 8.0 has "function" as keyword but version 7.0
doesn't. I have a table that has two columns labeled
Module, and function. I have a store procedure that calls
this two columns but since I swicht from SQL Server 7.0 to
8.0 the function its a keyword in 8.0...and I can't store
my records when I swicth to SQL version 8.0
How I can force the SP to take the name of a column as a
field instead of a keyword? I tried to place brakets but
still I can't run my store procedure...in other words I
can't store new records in my table whose field's name is
a keyword...
I define my table like this.
...
[Module]
[Function]
...using brackets...but nothing...any ideas?
Thanks,
Patty
*******************SP******************************
ALTER PROCEDURE sp_LogErrors
@.ID int, @.Number int, @.Description varchar(255),
@.Application varchar(30), @.Version varchar(30), @.Source
varchar(30), @.Module varchar(30),
@.Function varchar(30), @.Occurred DateTime, @.SBCID
varchar(30), @.Machine varchar(30)
AS
INSERT INTO Error (ID, Number, Description, Application,
Version, Source, [Module], [Function], Occurred,
Machine )
values (@.ID, @.Number, @.Description, @.Application,
@.Version, @.Source, @.Module, @.Function, @.Occurred,
@.Machine)What error message do you get if you execute that procedure from Query
Analyzer?
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Patty" <anonymous@.discussions.microsoft.com> wrote in message
news:b58901c3ecd3$aa67cd90$a301280a@.phx.gbl...
> Hi,
> SQL server 8.0 has "function" as keyword but version 7.0
> doesn't. I have a table that has two columns labeled
> Module, and function. I have a store procedure that calls
> this two columns but since I swicht from SQL Server 7.0 to
> 8.0 the function its a keyword in 8.0...and I can't store
> my records when I swicth to SQL version 8.0
> How I can force the SP to take the name of a column as a
> field instead of a keyword? I tried to place brakets but
> still I can't run my store procedure...in other words I
> can't store new records in my table whose field's name is
> a keyword...
> I define my table like this.
> ...
> [Module]
> [Function]
> ...using brackets...but nothing...any ideas?
> Thanks,
> Patty
> *******************SP******************************
> ALTER PROCEDURE sp_LogErrors
> @.ID int, @.Number int, @.Description varchar(255),
> @.Application varchar(30), @.Version varchar(30), @.Source
> varchar(30), @.Module varchar(30),
> @.Function varchar(30), @.Occurred DateTime, @.SBCID
> varchar(30), @.Machine varchar(30)
> AS
> INSERT INTO Error (ID, Number, Description, Application,
> Version, Source, [Module], [Function], Occurred,
> Machine )
> values (@.ID, @.Number, @.Description, @.Application,
> @.Version, @.Source, @.Module, @.Function, @.Occurred,
> @.Machine)
>
>
SQL server 8.0 has "function" as keyword but version 7.0
doesn't. I have a table that has two columns labeled
Module, and function. I have a store procedure that calls
this two columns but since I swicht from SQL Server 7.0 to
8.0 the function its a keyword in 8.0...and I can't store
my records when I swicth to SQL version 8.0
How I can force the SP to take the name of a column as a
field instead of a keyword? I tried to place brakets but
still I can't run my store procedure...in other words I
can't store new records in my table whose field's name is
a keyword...
I define my table like this.
...
[Module]
[Function]
...using brackets...but nothing...any ideas?
Thanks,
Patty
*******************SP******************************
ALTER PROCEDURE sp_LogErrors
@.ID int, @.Number int, @.Description varchar(255),
@.Application varchar(30), @.Version varchar(30), @.Source
varchar(30), @.Module varchar(30),
@.Function varchar(30), @.Occurred DateTime, @.SBCID
varchar(30), @.Machine varchar(30)
AS
INSERT INTO Error (ID, Number, Description, Application,
Version, Source, [Module], [Function], Occurred,
Machine )
values (@.ID, @.Number, @.Description, @.Application,
@.Version, @.Source, @.Module, @.Function, @.Occurred,
@.Machine)What error message do you get if you execute that procedure from Query
Analyzer?
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Patty" <anonymous@.discussions.microsoft.com> wrote in message
news:b58901c3ecd3$aa67cd90$a301280a@.phx.gbl...
> Hi,
> SQL server 8.0 has "function" as keyword but version 7.0
> doesn't. I have a table that has two columns labeled
> Module, and function. I have a store procedure that calls
> this two columns but since I swicht from SQL Server 7.0 to
> 8.0 the function its a keyword in 8.0...and I can't store
> my records when I swicth to SQL version 8.0
> How I can force the SP to take the name of a column as a
> field instead of a keyword? I tried to place brakets but
> still I can't run my store procedure...in other words I
> can't store new records in my table whose field's name is
> a keyword...
> I define my table like this.
> ...
> [Module]
> [Function]
> ...using brackets...but nothing...any ideas?
> Thanks,
> Patty
> *******************SP******************************
> ALTER PROCEDURE sp_LogErrors
> @.ID int, @.Number int, @.Description varchar(255),
> @.Application varchar(30), @.Version varchar(30), @.Source
> varchar(30), @.Module varchar(30),
> @.Function varchar(30), @.Occurred DateTime, @.SBCID
> varchar(30), @.Machine varchar(30)
> AS
> INSERT INTO Error (ID, Number, Description, Application,
> Version, Source, [Module], [Function], Occurred,
> Machine )
> values (@.ID, @.Number, @.Description, @.Application,
> @.Version, @.Source, @.Module, @.Function, @.Occurred,
> @.Machine)
>
>
KEY_COLUMN_USAGE bug
Is it on purpose or is it a bug that the view
INFORMATION_SCHEMA.KEY_COLUMN_USAGE doesn't return PK columns outside the
"dbo" schema?
I've checked the view code and came accross this selection criteria
...
WHERE
...
col.name = index_col(t_obj.name, i.indid, v.number) AND
...
and I would guess it should be
col.name = index_col(user_name(c_obj.uid) + '.' + t_obj.name, i.indid,
v.number) AND
instead
is this bug on purpose or am I wrong?
it's kind of hard to get pk schema information when the schema isn't "dbo" -
what would the sql server experts solution be in this case?
regards
Chris
...
WHERE
...
SELECT
...
UNION
SELECT
db_name() AS CONSTRAINT_CATALOG,
user_name(c_obj.uid) AS CONSTRAINT_SCHEMA,
i.name AS CONSTRAINT_NAME,
db_name() AS TABLE_CATALOG,
user_name(t_obj.uid) AS TABLE_SCHEMA,
t_obj.name AS TABLE_NAME,
col.name AS COLUMN_NAME,
v.number AS ORDINAL_POSITION
FROM
sysobjects c_obj, sysobjects t_obj, syscolumns col,
master.dbo.spt_values v, sysindexes i
WHERE
permissions(t_obj.id) != 0 AND
c_obj.xtype IN ('UQ', 'PK') AND
t_obj.id = c_obj.parent_obj AND
t_obj.xtype = 'U' AND
t_obj.id = col.id AND
--**
col.name = index_col(t_obj.name, i.indid, v.number) AND
--**
t_obj.id = i.id AND
c_obj.name = i.name AND
v.number > 0 AND
v.number <= i.keycnt AND
v.type = 'P'
It's a bug, and it was discussed earlier in the following thread:
http://groups.google.co.uk/group/mic...407b1b00d42b32
I haven't checked if it has actually been fixed in SP4.
Jacco Schalkwijk
SQL Server MVP
"christian kuendig" <xxxx> wrote in message
news:OVHcnzSWFHA.3188@.TK2MSFTNGP09.phx.gbl...
> Is it on purpose or is it a bug that the view
> INFORMATION_SCHEMA.KEY_COLUMN_USAGE doesn't return PK columns outside the
> "dbo" schema?
> I've checked the view code and came accross this selection criteria
> ...
> WHERE
> ...
> col.name = index_col(t_obj.name, i.indid, v.number) AND
> ...
> and I would guess it should be
> col.name = index_col(user_name(c_obj.uid) + '.' + t_obj.name, i.indid,
> v.number) AND
> instead
> is this bug on purpose or am I wrong?
> it's kind of hard to get pk schema information when the schema isn't
> "dbo" - what would the sql server experts solution be in this case?
> regards
> Chris
> ...
> WHERE
> ...
> SELECT
> ...
> UNION
> SELECT
> db_name() AS CONSTRAINT_CATALOG,
> user_name(c_obj.uid) AS CONSTRAINT_SCHEMA,
> i.name AS CONSTRAINT_NAME,
> db_name() AS TABLE_CATALOG,
> user_name(t_obj.uid) AS TABLE_SCHEMA,
> t_obj.name AS TABLE_NAME,
> col.name AS COLUMN_NAME,
> v.number AS ORDINAL_POSITION
> FROM
> sysobjects c_obj, sysobjects t_obj, syscolumns col,
> master.dbo.spt_values v, sysindexes i
> WHERE
> permissions(t_obj.id) != 0 AND
> c_obj.xtype IN ('UQ', 'PK') AND
> t_obj.id = c_obj.parent_obj AND
> t_obj.xtype = 'U' AND
> t_obj.id = col.id AND
> --**
> col.name = index_col(t_obj.name, i.indid, v.number) AND
> --**
> t_obj.id = i.id AND
> c_obj.name = i.name AND
> v.number > 0 AND
> v.number <= i.keycnt AND
> v.type = 'P'
>
|||Hi Jacco
It seems to be the same in SP4.
John
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:Ooj3SEUWFHA.2984@.tk2msftngp13.phx.gbl...
> It's a bug, and it was discussed earlier in the following thread:
> http://groups.google.co.uk/group/mic...407b1b00d42b32
> I haven't checked if it has actually been fixed in SP4.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "christian kuendig" <xxxx> wrote in message
> news:OVHcnzSWFHA.3188@.TK2MSFTNGP09.phx.gbl...
>
INFORMATION_SCHEMA.KEY_COLUMN_USAGE doesn't return PK columns outside the
"dbo" schema?
I've checked the view code and came accross this selection criteria
...
WHERE
...
col.name = index_col(t_obj.name, i.indid, v.number) AND
...
and I would guess it should be
col.name = index_col(user_name(c_obj.uid) + '.' + t_obj.name, i.indid,
v.number) AND
instead
is this bug on purpose or am I wrong?
it's kind of hard to get pk schema information when the schema isn't "dbo" -
what would the sql server experts solution be in this case?
regards
Chris
...
WHERE
...
SELECT
...
UNION
SELECT
db_name() AS CONSTRAINT_CATALOG,
user_name(c_obj.uid) AS CONSTRAINT_SCHEMA,
i.name AS CONSTRAINT_NAME,
db_name() AS TABLE_CATALOG,
user_name(t_obj.uid) AS TABLE_SCHEMA,
t_obj.name AS TABLE_NAME,
col.name AS COLUMN_NAME,
v.number AS ORDINAL_POSITION
FROM
sysobjects c_obj, sysobjects t_obj, syscolumns col,
master.dbo.spt_values v, sysindexes i
WHERE
permissions(t_obj.id) != 0 AND
c_obj.xtype IN ('UQ', 'PK') AND
t_obj.id = c_obj.parent_obj AND
t_obj.xtype = 'U' AND
t_obj.id = col.id AND
--**
col.name = index_col(t_obj.name, i.indid, v.number) AND
--**
t_obj.id = i.id AND
c_obj.name = i.name AND
v.number > 0 AND
v.number <= i.keycnt AND
v.type = 'P'
It's a bug, and it was discussed earlier in the following thread:
http://groups.google.co.uk/group/mic...407b1b00d42b32
I haven't checked if it has actually been fixed in SP4.
Jacco Schalkwijk
SQL Server MVP
"christian kuendig" <xxxx> wrote in message
news:OVHcnzSWFHA.3188@.TK2MSFTNGP09.phx.gbl...
> Is it on purpose or is it a bug that the view
> INFORMATION_SCHEMA.KEY_COLUMN_USAGE doesn't return PK columns outside the
> "dbo" schema?
> I've checked the view code and came accross this selection criteria
> ...
> WHERE
> ...
> col.name = index_col(t_obj.name, i.indid, v.number) AND
> ...
> and I would guess it should be
> col.name = index_col(user_name(c_obj.uid) + '.' + t_obj.name, i.indid,
> v.number) AND
> instead
> is this bug on purpose or am I wrong?
> it's kind of hard to get pk schema information when the schema isn't
> "dbo" - what would the sql server experts solution be in this case?
> regards
> Chris
> ...
> WHERE
> ...
> SELECT
> ...
> UNION
> SELECT
> db_name() AS CONSTRAINT_CATALOG,
> user_name(c_obj.uid) AS CONSTRAINT_SCHEMA,
> i.name AS CONSTRAINT_NAME,
> db_name() AS TABLE_CATALOG,
> user_name(t_obj.uid) AS TABLE_SCHEMA,
> t_obj.name AS TABLE_NAME,
> col.name AS COLUMN_NAME,
> v.number AS ORDINAL_POSITION
> FROM
> sysobjects c_obj, sysobjects t_obj, syscolumns col,
> master.dbo.spt_values v, sysindexes i
> WHERE
> permissions(t_obj.id) != 0 AND
> c_obj.xtype IN ('UQ', 'PK') AND
> t_obj.id = c_obj.parent_obj AND
> t_obj.xtype = 'U' AND
> t_obj.id = col.id AND
> --**
> col.name = index_col(t_obj.name, i.indid, v.number) AND
> --**
> t_obj.id = i.id AND
> c_obj.name = i.name AND
> v.number > 0 AND
> v.number <= i.keycnt AND
> v.type = 'P'
>
|||Hi Jacco
It seems to be the same in SP4.
John
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:Ooj3SEUWFHA.2984@.tk2msftngp13.phx.gbl...
> It's a bug, and it was discussed earlier in the following thread:
> http://groups.google.co.uk/group/mic...407b1b00d42b32
> I haven't checked if it has actually been fixed in SP4.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "christian kuendig" <xxxx> wrote in message
> news:OVHcnzSWFHA.3188@.TK2MSFTNGP09.phx.gbl...
>
KEY_COLUMN_USAGE bug
Is it on purpose or is it a bug that the view
INFORMATION_SCHEMA.KEY_COLUMN_USAGE doesn't return PK columns outside the
"dbo" schema?
I've checked the view code and came accross this selection criteria
...
WHERE
...
col.name = index_col(t_obj.name, i.indid, v.number) AND
...
and I would guess it should be
col.name = index_col(user_name(c_obj.uid) + '.' + t_obj.name, i.indid,
v.number) AND
instead
is this bug on purpose or am I wrong?
it's kind of hard to get pk schema information when the schema isn't "dbo" -
what would the sql server experts solution be in this case?
regards
Chris
...
WHERE
...
SELECT
..
UNION
SELECT
db_name() AS CONSTRAINT_CATALOG,
user_name(c_obj.uid) AS CONSTRAINT_SCHEMA,
i.name AS CONSTRAINT_NAME,
db_name() AS TABLE_CATALOG,
user_name(t_obj.uid) AS TABLE_SCHEMA,
t_obj.name AS TABLE_NAME,
col.name AS COLUMN_NAME,
v.number AS ORDINAL_POSITION
FROM
sysobjects c_obj, sysobjects t_obj, syscolumns col,
master.dbo.spt_values v, sysindexes i
WHERE
permissions(t_obj.id) != 0 AND
c_obj.xtype IN ('UQ', 'PK') AND
t_obj.id = c_obj.parent_obj AND
t_obj.xtype = 'U' AND
t_obj.id = col.id AND
--**
col.name = index_col(t_obj.name, i.indid, v.number) AND
--**
t_obj.id = i.id AND
c_obj.name = i.name AND
v.number > 0 AND
v.number <= i.keycnt AND
v.type = 'P'It's a bug, and it was discussed earlier in the following thread:
a0407b1b00d42b32" target="_blank">http://groups.google.co.uk/group/mi...0407b1b00d42b32
I haven't checked if it has actually been fixed in SP4.
Jacco Schalkwijk
SQL Server MVP
"christian kuendig" <xxxx> wrote in message
news:OVHcnzSWFHA.3188@.TK2MSFTNGP09.phx.gbl...
> Is it on purpose or is it a bug that the view
> INFORMATION_SCHEMA.KEY_COLUMN_USAGE doesn't return PK columns outside the
> "dbo" schema?
> I've checked the view code and came accross this selection criteria
> ...
> WHERE
> ...
> col.name = index_col(t_obj.name, i.indid, v.number) AND
> ...
> and I would guess it should be
> col.name = index_col(user_name(c_obj.uid) + '.' + t_obj.name, i.indid,
> v.number) AND
> instead
> is this bug on purpose or am I wrong?
> it's kind of hard to get pk schema information when the schema isn't
> "dbo" - what would the sql server experts solution be in this case?
> regards
> Chris
> ...
> WHERE
> ...
> SELECT
> ...
> UNION
> SELECT
> db_name() AS CONSTRAINT_CATALOG,
> user_name(c_obj.uid) AS CONSTRAINT_SCHEMA,
> i.name AS CONSTRAINT_NAME,
> db_name() AS TABLE_CATALOG,
> user_name(t_obj.uid) AS TABLE_SCHEMA,
> t_obj.name AS TABLE_NAME,
> col.name AS COLUMN_NAME,
> v.number AS ORDINAL_POSITION
> FROM
> sysobjects c_obj, sysobjects t_obj, syscolumns col,
> master.dbo.spt_values v, sysindexes i
> WHERE
> permissions(t_obj.id) != 0 AND
> c_obj.xtype IN ('UQ', 'PK') AND
> t_obj.id = c_obj.parent_obj AND
> t_obj.xtype = 'U' AND
> t_obj.id = col.id AND
> --**
> col.name = index_col(t_obj.name, i.indid, v.number) AND
> --**
> t_obj.id = i.id AND
> c_obj.name = i.name AND
> v.number > 0 AND
> v.number <= i.keycnt AND
> v.type = 'P'
>|||Hi Jacco
It seems to be the same in SP4.
John
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:Ooj3SEUWFHA.2984@.tk2msftngp13.phx.gbl...
> It's a bug, and it was discussed earlier in the following thread:
> http://groups.google.co.uk/group/mi...0407b1b00d42b32
> I haven't checked if it has actually been fixed in SP4.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "christian kuendig" <xxxx> wrote in message
> news:OVHcnzSWFHA.3188@.TK2MSFTNGP09.phx.gbl...
>
INFORMATION_SCHEMA.KEY_COLUMN_USAGE doesn't return PK columns outside the
"dbo" schema?
I've checked the view code and came accross this selection criteria
...
WHERE
...
col.name = index_col(t_obj.name, i.indid, v.number) AND
...
and I would guess it should be
col.name = index_col(user_name(c_obj.uid) + '.' + t_obj.name, i.indid,
v.number) AND
instead
is this bug on purpose or am I wrong?
it's kind of hard to get pk schema information when the schema isn't "dbo" -
what would the sql server experts solution be in this case?
regards
Chris
...
WHERE
...
SELECT
..
UNION
SELECT
db_name() AS CONSTRAINT_CATALOG,
user_name(c_obj.uid) AS CONSTRAINT_SCHEMA,
i.name AS CONSTRAINT_NAME,
db_name() AS TABLE_CATALOG,
user_name(t_obj.uid) AS TABLE_SCHEMA,
t_obj.name AS TABLE_NAME,
col.name AS COLUMN_NAME,
v.number AS ORDINAL_POSITION
FROM
sysobjects c_obj, sysobjects t_obj, syscolumns col,
master.dbo.spt_values v, sysindexes i
WHERE
permissions(t_obj.id) != 0 AND
c_obj.xtype IN ('UQ', 'PK') AND
t_obj.id = c_obj.parent_obj AND
t_obj.xtype = 'U' AND
t_obj.id = col.id AND
--**
col.name = index_col(t_obj.name, i.indid, v.number) AND
--**
t_obj.id = i.id AND
c_obj.name = i.name AND
v.number > 0 AND
v.number <= i.keycnt AND
v.type = 'P'It's a bug, and it was discussed earlier in the following thread:
a0407b1b00d42b32" target="_blank">http://groups.google.co.uk/group/mi...0407b1b00d42b32
I haven't checked if it has actually been fixed in SP4.
Jacco Schalkwijk
SQL Server MVP
"christian kuendig" <xxxx> wrote in message
news:OVHcnzSWFHA.3188@.TK2MSFTNGP09.phx.gbl...
> Is it on purpose or is it a bug that the view
> INFORMATION_SCHEMA.KEY_COLUMN_USAGE doesn't return PK columns outside the
> "dbo" schema?
> I've checked the view code and came accross this selection criteria
> ...
> WHERE
> ...
> col.name = index_col(t_obj.name, i.indid, v.number) AND
> ...
> and I would guess it should be
> col.name = index_col(user_name(c_obj.uid) + '.' + t_obj.name, i.indid,
> v.number) AND
> instead
> is this bug on purpose or am I wrong?
> it's kind of hard to get pk schema information when the schema isn't
> "dbo" - what would the sql server experts solution be in this case?
> regards
> Chris
> ...
> WHERE
> ...
> SELECT
> ...
> UNION
> SELECT
> db_name() AS CONSTRAINT_CATALOG,
> user_name(c_obj.uid) AS CONSTRAINT_SCHEMA,
> i.name AS CONSTRAINT_NAME,
> db_name() AS TABLE_CATALOG,
> user_name(t_obj.uid) AS TABLE_SCHEMA,
> t_obj.name AS TABLE_NAME,
> col.name AS COLUMN_NAME,
> v.number AS ORDINAL_POSITION
> FROM
> sysobjects c_obj, sysobjects t_obj, syscolumns col,
> master.dbo.spt_values v, sysindexes i
> WHERE
> permissions(t_obj.id) != 0 AND
> c_obj.xtype IN ('UQ', 'PK') AND
> t_obj.id = c_obj.parent_obj AND
> t_obj.xtype = 'U' AND
> t_obj.id = col.id AND
> --**
> col.name = index_col(t_obj.name, i.indid, v.number) AND
> --**
> t_obj.id = i.id AND
> c_obj.name = i.name AND
> v.number > 0 AND
> v.number <= i.keycnt AND
> v.type = 'P'
>|||Hi Jacco
It seems to be the same in SP4.
John
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:Ooj3SEUWFHA.2984@.tk2msftngp13.phx.gbl...
> It's a bug, and it was discussed earlier in the following thread:
> http://groups.google.co.uk/group/mi...0407b1b00d42b32
> I haven't checked if it has actually been fixed in SP4.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "christian kuendig" <xxxx> wrote in message
> news:OVHcnzSWFHA.3188@.TK2MSFTNGP09.phx.gbl...
>
KEY_COLUMN_USAGE bug
Is it on purpose or is it a bug that the view
INFORMATION_SCHEMA.KEY_COLUMN_USAGE doesn't return PK columns outside the
"dbo" schema?
I've checked the view code and came accross this selection criteria
...
WHERE
...
col.name = index_col(t_obj.name, i.indid, v.number) AND
...
and I would guess it should be
col.name = index_col(user_name(c_obj.uid) + '.' + t_obj.name, i.indid,
v.number) AND
instead
is this bug on purpose or am I wrong?
it's kind of hard to get pk schema information when the schema isn't "dbo" -
what would the sql server experts solution be in this case?
regards
Chris
...
WHERE
...
SELECT
...
UNION
SELECT
db_name() AS CONSTRAINT_CATALOG,
user_name(c_obj.uid) AS CONSTRAINT_SCHEMA,
i.name AS CONSTRAINT_NAME,
db_name() AS TABLE_CATALOG,
user_name(t_obj.uid) AS TABLE_SCHEMA,
t_obj.name AS TABLE_NAME,
col.name AS COLUMN_NAME,
v.number AS ORDINAL_POSITION
FROM
sysobjects c_obj, sysobjects t_obj, syscolumns col,
master.dbo.spt_values v, sysindexes i
WHERE
permissions(t_obj.id) != 0 AND
c_obj.xtype IN ('UQ', 'PK') AND
t_obj.id = c_obj.parent_obj AND
t_obj.xtype = 'U' AND
t_obj.id = col.id AND
--**
col.name = index_col(t_obj.name, i.indid, v.number) AND
--**
t_obj.id = i.id AND
c_obj.name = i.name AND
v.number > 0 AND
v.number <= i.keycnt AND
v.type = 'P'It's a bug, and it was discussed earlier in the following thread:
http://groups.google.co.uk/group/microsoft.public.sqlserver.programming/browse_thread/thread/77f82a458be4b9d9/a0407b1b00d42b32?q=schalkwijk+key_column_usage&rnum=1#a0407b1b00d42b32
I haven't checked if it has actually been fixed in SP4.
--
Jacco Schalkwijk
SQL Server MVP
"christian kuendig" <xxxx> wrote in message
news:OVHcnzSWFHA.3188@.TK2MSFTNGP09.phx.gbl...
> Is it on purpose or is it a bug that the view
> INFORMATION_SCHEMA.KEY_COLUMN_USAGE doesn't return PK columns outside the
> "dbo" schema?
> I've checked the view code and came accross this selection criteria
> ...
> WHERE
> ...
> col.name = index_col(t_obj.name, i.indid, v.number) AND
> ...
> and I would guess it should be
> col.name = index_col(user_name(c_obj.uid) + '.' + t_obj.name, i.indid,
> v.number) AND
> instead
> is this bug on purpose or am I wrong?
> it's kind of hard to get pk schema information when the schema isn't
> "dbo" - what would the sql server experts solution be in this case?
> regards
> Chris
> ...
> WHERE
> ...
> SELECT
> ...
> UNION
> SELECT
> db_name() AS CONSTRAINT_CATALOG,
> user_name(c_obj.uid) AS CONSTRAINT_SCHEMA,
> i.name AS CONSTRAINT_NAME,
> db_name() AS TABLE_CATALOG,
> user_name(t_obj.uid) AS TABLE_SCHEMA,
> t_obj.name AS TABLE_NAME,
> col.name AS COLUMN_NAME,
> v.number AS ORDINAL_POSITION
> FROM
> sysobjects c_obj, sysobjects t_obj, syscolumns col,
> master.dbo.spt_values v, sysindexes i
> WHERE
> permissions(t_obj.id) != 0 AND
> c_obj.xtype IN ('UQ', 'PK') AND
> t_obj.id = c_obj.parent_obj AND
> t_obj.xtype = 'U' AND
> t_obj.id = col.id AND
> --**
> col.name = index_col(t_obj.name, i.indid, v.number) AND
> --**
> t_obj.id = i.id AND
> c_obj.name = i.name AND
> v.number > 0 AND
> v.number <= i.keycnt AND
> v.type = 'P'
>|||Hi Jacco
It seems to be the same in SP4.
John
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:Ooj3SEUWFHA.2984@.tk2msftngp13.phx.gbl...
> It's a bug, and it was discussed earlier in the following thread:
> http://groups.google.co.uk/group/microsoft.public.sqlserver.programming/browse_thread/thread/77f82a458be4b9d9/a0407b1b00d42b32?q=schalkwijk+key_column_usage&rnum=1#a0407b1b00d42b32
> I haven't checked if it has actually been fixed in SP4.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "christian kuendig" <xxxx> wrote in message
> news:OVHcnzSWFHA.3188@.TK2MSFTNGP09.phx.gbl...
>> Is it on purpose or is it a bug that the view
>> INFORMATION_SCHEMA.KEY_COLUMN_USAGE doesn't return PK columns outside the
>> "dbo" schema?
>> I've checked the view code and came accross this selection criteria
>> ...
>> WHERE
>> ...
>> col.name = index_col(t_obj.name, i.indid, v.number) AND
>> ...
>> and I would guess it should be
>> col.name = index_col(user_name(c_obj.uid) + '.' + t_obj.name, i.indid,
>> v.number) AND
>> instead
>> is this bug on purpose or am I wrong?
>> it's kind of hard to get pk schema information when the schema isn't
>> "dbo" - what would the sql server experts solution be in this case?
>> regards
>> Chris
>> ...
>> WHERE
>> ...
>> SELECT
>> ...
>> UNION
>> SELECT
>> db_name() AS CONSTRAINT_CATALOG,
>> user_name(c_obj.uid) AS CONSTRAINT_SCHEMA,
>> i.name AS CONSTRAINT_NAME,
>> db_name() AS TABLE_CATALOG,
>> user_name(t_obj.uid) AS TABLE_SCHEMA,
>> t_obj.name AS TABLE_NAME,
>> col.name AS COLUMN_NAME,
>> v.number AS ORDINAL_POSITION
>> FROM
>> sysobjects c_obj, sysobjects t_obj, syscolumns col,
>> master.dbo.spt_values v, sysindexes i
>> WHERE
>> permissions(t_obj.id) != 0 AND
>> c_obj.xtype IN ('UQ', 'PK') AND
>> t_obj.id = c_obj.parent_obj AND
>> t_obj.xtype = 'U' AND
>> t_obj.id = col.id AND
>> --**
>> col.name = index_col(t_obj.name, i.indid, v.number) AND
>> --**
>> t_obj.id = i.id AND
>> c_obj.name = i.name AND
>> v.number > 0 AND
>> v.number <= i.keycnt AND
>> v.type = 'P'
>>
>
INFORMATION_SCHEMA.KEY_COLUMN_USAGE doesn't return PK columns outside the
"dbo" schema?
I've checked the view code and came accross this selection criteria
...
WHERE
...
col.name = index_col(t_obj.name, i.indid, v.number) AND
...
and I would guess it should be
col.name = index_col(user_name(c_obj.uid) + '.' + t_obj.name, i.indid,
v.number) AND
instead
is this bug on purpose or am I wrong?
it's kind of hard to get pk schema information when the schema isn't "dbo" -
what would the sql server experts solution be in this case?
regards
Chris
...
WHERE
...
SELECT
...
UNION
SELECT
db_name() AS CONSTRAINT_CATALOG,
user_name(c_obj.uid) AS CONSTRAINT_SCHEMA,
i.name AS CONSTRAINT_NAME,
db_name() AS TABLE_CATALOG,
user_name(t_obj.uid) AS TABLE_SCHEMA,
t_obj.name AS TABLE_NAME,
col.name AS COLUMN_NAME,
v.number AS ORDINAL_POSITION
FROM
sysobjects c_obj, sysobjects t_obj, syscolumns col,
master.dbo.spt_values v, sysindexes i
WHERE
permissions(t_obj.id) != 0 AND
c_obj.xtype IN ('UQ', 'PK') AND
t_obj.id = c_obj.parent_obj AND
t_obj.xtype = 'U' AND
t_obj.id = col.id AND
--**
col.name = index_col(t_obj.name, i.indid, v.number) AND
--**
t_obj.id = i.id AND
c_obj.name = i.name AND
v.number > 0 AND
v.number <= i.keycnt AND
v.type = 'P'It's a bug, and it was discussed earlier in the following thread:
http://groups.google.co.uk/group/microsoft.public.sqlserver.programming/browse_thread/thread/77f82a458be4b9d9/a0407b1b00d42b32?q=schalkwijk+key_column_usage&rnum=1#a0407b1b00d42b32
I haven't checked if it has actually been fixed in SP4.
--
Jacco Schalkwijk
SQL Server MVP
"christian kuendig" <xxxx> wrote in message
news:OVHcnzSWFHA.3188@.TK2MSFTNGP09.phx.gbl...
> Is it on purpose or is it a bug that the view
> INFORMATION_SCHEMA.KEY_COLUMN_USAGE doesn't return PK columns outside the
> "dbo" schema?
> I've checked the view code and came accross this selection criteria
> ...
> WHERE
> ...
> col.name = index_col(t_obj.name, i.indid, v.number) AND
> ...
> and I would guess it should be
> col.name = index_col(user_name(c_obj.uid) + '.' + t_obj.name, i.indid,
> v.number) AND
> instead
> is this bug on purpose or am I wrong?
> it's kind of hard to get pk schema information when the schema isn't
> "dbo" - what would the sql server experts solution be in this case?
> regards
> Chris
> ...
> WHERE
> ...
> SELECT
> ...
> UNION
> SELECT
> db_name() AS CONSTRAINT_CATALOG,
> user_name(c_obj.uid) AS CONSTRAINT_SCHEMA,
> i.name AS CONSTRAINT_NAME,
> db_name() AS TABLE_CATALOG,
> user_name(t_obj.uid) AS TABLE_SCHEMA,
> t_obj.name AS TABLE_NAME,
> col.name AS COLUMN_NAME,
> v.number AS ORDINAL_POSITION
> FROM
> sysobjects c_obj, sysobjects t_obj, syscolumns col,
> master.dbo.spt_values v, sysindexes i
> WHERE
> permissions(t_obj.id) != 0 AND
> c_obj.xtype IN ('UQ', 'PK') AND
> t_obj.id = c_obj.parent_obj AND
> t_obj.xtype = 'U' AND
> t_obj.id = col.id AND
> --**
> col.name = index_col(t_obj.name, i.indid, v.number) AND
> --**
> t_obj.id = i.id AND
> c_obj.name = i.name AND
> v.number > 0 AND
> v.number <= i.keycnt AND
> v.type = 'P'
>|||Hi Jacco
It seems to be the same in SP4.
John
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:Ooj3SEUWFHA.2984@.tk2msftngp13.phx.gbl...
> It's a bug, and it was discussed earlier in the following thread:
> http://groups.google.co.uk/group/microsoft.public.sqlserver.programming/browse_thread/thread/77f82a458be4b9d9/a0407b1b00d42b32?q=schalkwijk+key_column_usage&rnum=1#a0407b1b00d42b32
> I haven't checked if it has actually been fixed in SP4.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "christian kuendig" <xxxx> wrote in message
> news:OVHcnzSWFHA.3188@.TK2MSFTNGP09.phx.gbl...
>> Is it on purpose or is it a bug that the view
>> INFORMATION_SCHEMA.KEY_COLUMN_USAGE doesn't return PK columns outside the
>> "dbo" schema?
>> I've checked the view code and came accross this selection criteria
>> ...
>> WHERE
>> ...
>> col.name = index_col(t_obj.name, i.indid, v.number) AND
>> ...
>> and I would guess it should be
>> col.name = index_col(user_name(c_obj.uid) + '.' + t_obj.name, i.indid,
>> v.number) AND
>> instead
>> is this bug on purpose or am I wrong?
>> it's kind of hard to get pk schema information when the schema isn't
>> "dbo" - what would the sql server experts solution be in this case?
>> regards
>> Chris
>> ...
>> WHERE
>> ...
>> SELECT
>> ...
>> UNION
>> SELECT
>> db_name() AS CONSTRAINT_CATALOG,
>> user_name(c_obj.uid) AS CONSTRAINT_SCHEMA,
>> i.name AS CONSTRAINT_NAME,
>> db_name() AS TABLE_CATALOG,
>> user_name(t_obj.uid) AS TABLE_SCHEMA,
>> t_obj.name AS TABLE_NAME,
>> col.name AS COLUMN_NAME,
>> v.number AS ORDINAL_POSITION
>> FROM
>> sysobjects c_obj, sysobjects t_obj, syscolumns col,
>> master.dbo.spt_values v, sysindexes i
>> WHERE
>> permissions(t_obj.id) != 0 AND
>> c_obj.xtype IN ('UQ', 'PK') AND
>> t_obj.id = c_obj.parent_obj AND
>> t_obj.xtype = 'U' AND
>> t_obj.id = col.id AND
>> --**
>> col.name = index_col(t_obj.name, i.indid, v.number) AND
>> --**
>> t_obj.id = i.id AND
>> c_obj.name = i.name AND
>> v.number > 0 AND
>> v.number <= i.keycnt AND
>> v.type = 'P'
>>
>
Monday, March 12, 2012
Key columns in SQL Server Everywhere Edition
DBPROPSET_ROWSET property set. Does it mean that I have to use the KEY_COLUMN_USAGE rowset to get list of key columns in table?
key columns heeeeeeelp
hi i need some help,, i have a dimension like this:
dimgeo
fiDWHgeoid as my primary key
and these attributes
ficanalid
fidivisionid
figciaid
with thier description fields:
fcdesccanal
fcdescdivision
fcdescgcia
the problem: how can i make a unique key that includes the three id`s i listed in only one and unique key so when i browse my cube it will display the correct match.. i'm really really new in this, i saw a property which is key columns should i use it and it so.. how? please i need some help.
You can try and modify the KeyColumns property for your "fiDWHgeoid " attribute to include more than a single column into this.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
I did what you said but at the moment f processing it it comes this error:
Error 1 Cube 'Aforecubo' > Measure Group 'Fact Comite' > Dimension 'Dim Geo Afore' > Measure Group Attribute 'Fc Desc Division Afore' : Same count of key columns as in dimension attribute is required. This measure group attribute has 1 key columns, the dimension attribute has 2.
|||This error means that you have 2 columns your dimension key based upon. But when looking at relationships to the measure group, you still have a single attribute mentioned there.
Now, you've modified the dimension key, you need to make sure you can connect your dimension to the measure group.
I will have to take back my suggestion to modify key columns. You probably need to go back to relational database and solve the problem of unique keys there.
You can make intermediate attribute keys unique by adding more columns into the Key columns collection, but for the key attribute of the dimension you need pay attention to the way data in measure group is referenced correctly.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
1.-when you create the dimension select the 3 keys columns for example A,B,C
2.-Save your dimension
3.-Edit your dimension
4.-From de data source view drag the same 3 columns that you have selected before to the hierarchy pane to create a hierarchy
5.- See in Attributes pane that now you have a 3 key atributte key and this 3 atributtes individually
This is all
key columns
I have noticed that Microsoft SQL Server 2005 Everywhere Edition OLE DB Provider doesn't support DBPROP_UNIQUEROWS property from
DBPROPSET_ROWSET property set. Does it mean that I have to use the KEY_COLUMN_USAGE rowset to get list of key columns in table?Yes, you should use DBSCHEMA_KEY_COLUMN_USAGE in this case. or you can get the same information with the following query: "select * from information_schema.key_column_usage;"
DBPROPSET_ROWSET property set. Does it mean that I have to use the KEY_COLUMN_USAGE rowset to get list of key columns in table?Yes, you should use DBSCHEMA_KEY_COLUMN_USAGE in this case. or you can get the same information with the following query: "select * from information_schema.key_column_usage;"
key columns
I have noticed that Microsoft SQL Server 2005 Everywhere Edition OLE DB Provider doesn't support DBPROP_UNIQUEROWS property from
DBPROPSET_ROWSET property set. Does it mean that I have to use the KEY_COLUMN_USAGE rowset to get list of key columns in table?Yes, you should use DBSCHEMA_KEY_COLUMN_USAGE in this case. or you can get the same information with the following query: "select * from information_schema.key_column_usage;"
DBPROPSET_ROWSET property set. Does it mean that I have to use the KEY_COLUMN_USAGE rowset to get list of key columns in table?Yes, you should use DBSCHEMA_KEY_COLUMN_USAGE in this case. or you can get the same information with the following query: "select * from information_schema.key_column_usage;"
Wednesday, March 7, 2012
Keeping DB schemas synched
Hi - what's the best (preferably open source or inexpensive) tool for
identifying differences (tables, columns, types, etc) between a development
database and a production one, and applying dev changes to production
automatically?
Thanks,
ChrisI don't think it is ever a good idea to make changes to a production system
"automatically". But the compare tool from www.red-gate.com is inexpensive
and should do what you are after.
Andrew J. Kelly SQL MVP
"querylous" <querylous@.discussions.microsoft.com> wrote in message
news:9E38D2DB-B107-45D1-A5D4-2E0970067AE8@.microsoft.com...
> Hi - what's the best (preferably open source or inexpensive) tool for
> identifying differences (tables, columns, types, etc) between a
> development
> database and a production one, and applying dev changes to production
> automatically?
> Thanks,
> Chris|||2nd the vote for red gate...I've used SQL COmpare and Data Compare for
several years to keep my schems and lookup data intact
Free 14 day trial, less than $300 per tool. VERY hard to beat
Kevin Hill
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.expertsrt.com - not your average tech Q&A site
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uxykvJONGHA.2472@.TK2MSFTNGP11.phx.gbl...
>I don't think it is ever a good idea to make changes to a production system
>"automatically". But the compare tool from www.red-gate.com is inexpensive
>and should do what you are after.
> --
> Andrew J. Kelly SQL MVP
>
> "querylous" <querylous@.discussions.microsoft.com> wrote in message
> news:9E38D2DB-B107-45D1-A5D4-2E0970067AE8@.microsoft.com...
>|||Thanks Andrew- I fear I do a lot of things that aren't such good ideas when
developing! With luck, the right tool will have options to select the change
s
I'd like to apply, and then apply them.
Thanks for the rec,
Chris|||Developing is one thing, production is quite another. Yes this tool will
generate scripts for the changes but no matter how good the tool all scripts
should be tested before being applied to production.
Andrew J. Kelly SQL MVP
"querylous" <querylous@.discussions.microsoft.com> wrote in message
news:A653C08A-D917-4E85-84DE-D8B4B6A2E015@.microsoft.com...
> Thanks Andrew- I fear I do a lot of things that aren't such good ideas
> when
> developing! With luck, the right tool will have options to select the
> changes
> I'd like to apply, and then apply them.
> Thanks for the rec,
> Chris|||You should be checking your development and production scripts into some
type of source version control system. For example, Visual Source Safe has
an option to compare two projects and list files that are different, and it
has a feature for comparing two versions of a script side by side with
differences highlighted.
Also, you can script the databases to seperate folders and use a tool like
WinMerge to perform the comparisons:
http://groups.google.com/group/micr...br />
46abfa76
"querylous" <querylous@.discussions.microsoft.com> wrote in message
news:9E38D2DB-B107-45D1-A5D4-2E0970067AE8@.microsoft.com...
> Hi - what's the best (preferably open source or inexpensive) tool for
> identifying differences (tables, columns, types, etc) between a
> development
> database and a production one, and applying dev changes to production
> automatically?
> Thanks,
> Chris|||Thanks JT- unfortunately, I currently do all admin via the admin console, no
t
with scripts. Bad practice?
Chris
"JT" wrote:
> You should be checking your development and production scripts into some
> type of source version control system. For example, Visual Source Safe has
> an option to compare two projects and list files that are different, and i
t
> has a feature for comparing two versions of a script side by side with
> differences highlighted.
> Also, you can script the databases to seperate folders and use a tool like
> WinMerge to perform the comparisons:
> http://groups.google.com/group/micr... />
0c46abfa76
> "querylous" <querylous@.discussions.microsoft.com> wrote in message
> news:9E38D2DB-B107-45D1-A5D4-2E0970067AE8@.microsoft.com...
>
>|||In most SQL Server environments, changes to the database schema are
implemented as scripts; which are first tested against a DEV or QA server
and then promoted against the production server. These scripts are archived
using a source control system just like C# or Visual Basic projects.
"querylous" <querylous@.discussions.microsoft.com> wrote in message
news:86627853-B428-44F0-B94E-282A69894DF7@.microsoft.com...
> Thanks JT- unfortunately, I currently do all admin via the admin console,
> not
> with scripts. Bad practice?
> Chris
> "JT" wrote:
>
identifying differences (tables, columns, types, etc) between a development
database and a production one, and applying dev changes to production
automatically?
Thanks,
ChrisI don't think it is ever a good idea to make changes to a production system
"automatically". But the compare tool from www.red-gate.com is inexpensive
and should do what you are after.
Andrew J. Kelly SQL MVP
"querylous" <querylous@.discussions.microsoft.com> wrote in message
news:9E38D2DB-B107-45D1-A5D4-2E0970067AE8@.microsoft.com...
> Hi - what's the best (preferably open source or inexpensive) tool for
> identifying differences (tables, columns, types, etc) between a
> development
> database and a production one, and applying dev changes to production
> automatically?
> Thanks,
> Chris|||2nd the vote for red gate...I've used SQL COmpare and Data Compare for
several years to keep my schems and lookup data intact
Free 14 day trial, less than $300 per tool. VERY hard to beat
Kevin Hill
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.expertsrt.com - not your average tech Q&A site
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uxykvJONGHA.2472@.TK2MSFTNGP11.phx.gbl...
>I don't think it is ever a good idea to make changes to a production system
>"automatically". But the compare tool from www.red-gate.com is inexpensive
>and should do what you are after.
> --
> Andrew J. Kelly SQL MVP
>
> "querylous" <querylous@.discussions.microsoft.com> wrote in message
> news:9E38D2DB-B107-45D1-A5D4-2E0970067AE8@.microsoft.com...
>|||Thanks Andrew- I fear I do a lot of things that aren't such good ideas when
developing! With luck, the right tool will have options to select the change
s
I'd like to apply, and then apply them.
Thanks for the rec,
Chris|||Developing is one thing, production is quite another. Yes this tool will
generate scripts for the changes but no matter how good the tool all scripts
should be tested before being applied to production.
Andrew J. Kelly SQL MVP
"querylous" <querylous@.discussions.microsoft.com> wrote in message
news:A653C08A-D917-4E85-84DE-D8B4B6A2E015@.microsoft.com...
> Thanks Andrew- I fear I do a lot of things that aren't such good ideas
> when
> developing! With luck, the right tool will have options to select the
> changes
> I'd like to apply, and then apply them.
> Thanks for the rec,
> Chris|||You should be checking your development and production scripts into some
type of source version control system. For example, Visual Source Safe has
an option to compare two projects and list files that are different, and it
has a feature for comparing two versions of a script side by side with
differences highlighted.
Also, you can script the databases to seperate folders and use a tool like
WinMerge to perform the comparisons:
http://groups.google.com/group/micr...br />
46abfa76
"querylous" <querylous@.discussions.microsoft.com> wrote in message
news:9E38D2DB-B107-45D1-A5D4-2E0970067AE8@.microsoft.com...
> Hi - what's the best (preferably open source or inexpensive) tool for
> identifying differences (tables, columns, types, etc) between a
> development
> database and a production one, and applying dev changes to production
> automatically?
> Thanks,
> Chris|||Thanks JT- unfortunately, I currently do all admin via the admin console, no
t
with scripts. Bad practice?
Chris
"JT" wrote:
> You should be checking your development and production scripts into some
> type of source version control system. For example, Visual Source Safe has
> an option to compare two projects and list files that are different, and i
t
> has a feature for comparing two versions of a script side by side with
> differences highlighted.
> Also, you can script the databases to seperate folders and use a tool like
> WinMerge to perform the comparisons:
> http://groups.google.com/group/micr... />
0c46abfa76
> "querylous" <querylous@.discussions.microsoft.com> wrote in message
> news:9E38D2DB-B107-45D1-A5D4-2E0970067AE8@.microsoft.com...
>
>|||In most SQL Server environments, changes to the database schema are
implemented as scripts; which are first tested against a DEV or QA server
and then promoted against the production server. These scripts are archived
using a source control system just like C# or Visual Basic projects.
"querylous" <querylous@.discussions.microsoft.com> wrote in message
news:86627853-B428-44F0-B94E-282A69894DF7@.microsoft.com...
> Thanks JT- unfortunately, I currently do all admin via the admin console,
> not
> with scripts. Bad practice?
> Chris
> "JT" wrote:
>
Friday, February 24, 2012
Keep fields together in table control
I have a table that display large amounts of text in some fields. The table only has two columns (Field Name and Field Data). If the Field data is too large to fit on the same page as the field above it, it pushes to the next page, and then starts printing at the top of that page (the next page down).
I tried setting the "Keep Together" property of the table = True, but this was of no use.
Has anyone found a way to work around this, and if so could you let me know what you had to do. It may just be a SQL Server default setting that cannot be changed. I just want to research all possibilities before reporting back to the users.
Thank you,
T.J.
I wish I could share a screen shot of one of my reports.
The first record contains a lot of data. So on the first page all the prints is the Page Header and the Column heads.
Then the second page prints the data. At this rate, SQL Reporting Services is not even a useful tool for even simple reports, if there is not a work around for this (which I have not found playing with the reports).
Very disappointing.
|||TJ,I am having the same problem. The problem lies with the fact that pagination occurs at the end of the report creation process, long after the data is grouped together.
This is not pretty, but I found the following work-around:
http://blogs.msdn.com/ChrisHays/
HTH,
TQ|||I'm having a little trouble find the reference to the proposed solution at the link provided. Could you help me locate your proposed solution for implementing a keep together feature for the table control? thanx b|||
The only solution to this problem is to repeat the headers on each page and I hope it is in the wish list of next release.
Shyam
|||I understand your frustration TJ, I have the same problem but have not been able to find a workable solution... it seems like such a basic thing.Bumping this in hopes of a better answer.
Keep fields together in table control
I have a table that display large amounts of text in some fields. The table only has two columns (Field Name and Field Data). If the Field data is too large to fit on the same page as the field above it, it pushes to the next page, and then starts printing at the top of that page (the next page down).
I tried setting the "Keep Together" property of the table = True, but this was of no use.
Has anyone found a way to work around this, and if so could you let me know what you had to do. It may just be a SQL Server default setting that cannot be changed. I just want to research all possibilities before reporting back to the users.
Thank you,
T.J.
I wish I could share a screen shot of one of my reports.
The first record contains a lot of data. So on the first page all the prints is the Page Header and the Column heads.
Then the second page prints the data. At this rate, SQL Reporting Services is not even a useful tool for even simple reports, if there is not a work around for this (which I have not found playing with the reports).
Very disappointing.
|||TJ,I am having the same problem. The problem lies with the fact that pagination occurs at the end of the report creation process, long after the data is grouped together.
This is not pretty, but I found the following work-around:
http://blogs.msdn.com/ChrisHays/
HTH,
TQ
|||I'm having a little trouble find the reference to the proposed solution at the link provided. Could you help me locate your proposed solution for implementing a keep together feature for the table control? thanx b|||
The only solution to this problem is to repeat the headers on each page and I hope it is in the wish list of next release.
Shyam
|||I understand your frustration TJ, I have the same problem but have not been able to find a workable solution... it seems like such a basic thing.Bumping this in hopes of a better answer.
Keep fields together in table control
I have a table that display large amounts of text in some fields. The table only has two columns (Field Name and Field Data). If the Field data is too large to fit on the same page as the field above it, it pushes to the next page, and then starts printing at the top of that page (the next page down).
I tried setting the "Keep Together" property of the table = True, but this was of no use.
Has anyone found a way to work around this, and if so could you let me know what you had to do. It may just be a SQL Server default setting that cannot be changed. I just want to research all possibilities before reporting back to the users.
Thank you,
T.J.
I wish I could share a screen shot of one of my reports.
The first record contains a lot of data. So on the first page all the prints is the Page Header and the Column heads.
Then the second page prints the data. At this rate, SQL Reporting Services is not even a useful tool for even simple reports, if there is not a work around for this (which I have not found playing with the reports).
Very disappointing.
|||TJ,I am having the same problem. The problem lies with the fact that pagination occurs at the end of the report creation process, long after the data is grouped together.
This is not pretty, but I found the following work-around:
http://blogs.msdn.com/ChrisHays/
HTH,
TQ|||I'm having a little trouble find the reference to the proposed solution at the link provided. Could you help me locate your proposed solution for implementing a keep together feature for the table control? thanx b|||
The only solution to this problem is to repeat the headers on each page and I hope it is in the wish list of next release.
Shyam
|||I understand your frustration TJ, I have the same problem but have not been able to find a workable solution... it seems like such a basic thing.Bumping this in hopes of a better answer.
Subscribe to:
Posts (Atom)