The problem scenario is this:
I have an application which uses few tables.
user can post data and can submit the form multiple times.
each posting is saved as each version.
Now that I have all data, I need to find the difference between 2
versions of the saved data
and report that he has modified these fields in the current submission
to the older.
what is the best way to compare all the fields(except for Primary key)
of 2 rows in a table.
I am thinking of doing this -
select
case when oldversion.Field1 <> newversion.Field1 then 'changed' as
Field1 end,
case when oldversion.Field2 <> newversion.Field2 then 'changed' as
Field2 end
from
(select * from Table1 where TablePKField = oldPKID ) oldversion
inner join
(select * from Table1 where TablePKField = NewPKID ) newversion
where newversion.commonField = newversion.CommonField
Please suggest me the best way of doing this without much performance
loss...
Thanks for your time.
G.Gees
if (select checksum_agg(checksum(*)) from t1)
<> (select checksum_agg(checksum(*)) from t2)
print 'different'
else
print 'probably the same'
"Gees" <gayathri.s@.gmail.com> wrote in message
news:1141032918.046365.85170@.j33g2000cwa.googlegroups.com...
> The problem scenario is this:
> I have an application which uses few tables.
> user can post data and can submit the form multiple times.
> each posting is saved as each version.
> Now that I have all data, I need to find the difference between 2
> versions of the saved data
> and report that he has modified these fields in the current submission
> to the older.
> what is the best way to compare all the fields(except for Primary key)
> of 2 rows in a table.
> I am thinking of doing this -
> select
> case when oldversion.Field1 <> newversion.Field1 then 'changed' as
> Field1 end,
> case when oldversion.Field2 <> newversion.Field2 then 'changed' as
> Field2 end
> from
> (select * from Table1 where TablePKField = oldPKID ) oldversion
> inner join
> (select * from Table1 where TablePKField = NewPKID ) newversion
> where newversion.commonField = newversion.CommonField
> Please suggest me the best way of doing this without much performance
> loss...
> Thanks for your time.
> G.
>|||Thanks Uri Dimant.
My problem also includes, quering those fields where the data is
changed and show only the changes.
something like In Table1 ,
Field1- Field 2 - Field3
Row1 A - B - C
Row2 A - X - Y
I need to show that,
>From Row1 to Row2
Values of Field2 , B to X
and Values of Field3 C to Y
are the changes.
any help ?
Thanks again!
G
Uri Dimant wrote:
> Gees
> if (select checksum_agg(checksum(*)) from t1)
> <> (select checksum_agg(checksum(*)) from t2)
> print 'different'
> else
> print 'probably the same'
>
>
> "Gees" <gayathri.s@.gmail.com> wrote in message
> news:1141032918.046365.85170@.j33g2000cwa.googlegroups.com...|||Gees
CREATE TABLE [dbo].Audit (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Col1] [varchar] (50) NOT NULL ,
[Col2] [int] NOT NULL ,
[Col3] [varchar] (255) NOT NULL ,
[Col4] [int] NOT NULL
) ON [PRIMARY]
And it has the following records:
INSERT INTO Audit VALUES ('Andy', 3, 'Oxford', 21)
INSERT INTO Audit VALUES ('Andy', 4, 'Oxford', 21)
INSERT INTO Audit VALUES ('Andy', 4, 'Cambridge', 21)
INSERT INTO Audit VALUES ('Andy', 6, 'Cambridge', 29)
INSERT INTO Audit VALUES ('Andy', 4, 'Manchester', 21)
ID ChangedColumn NewVal
2 Col2 4
3 Col3 Cambridge
4 Col2 6
4 Col4 29
5 Col3 Manchester
select a2.id,'col2' as colchng, cast(a2.col2 as varchar(255)) as newvalue
from audit a1 join audit a2 on a1.id=a2.id-1
where a1.col2<>a2.col2
union all
select a2.id,'col3', a2.col3
from audit a1 join audit a2 on a1.id=a2.id-1
where a1.col3<>a2.col3
union all
select a2.id,'col4', cast(a2.col4 as varchar(255))
from audit a1 join audit a2 on a1.id=a2.id-1
where a1.col4<>a2.col4
order by 1,2
"Gees" <gayathri.s@.gmail.com> wrote in message
news:1141042195.525248.161020@.i39g2000cwa.googlegroups.com...
> Thanks Uri Dimant.
> My problem also includes, quering those fields where the data is
> changed and show only the changes.
> something like In Table1 ,
> Field1- Field 2 - Field3
> Row1 A - B - C
> Row2 A - X - Y
> I need to show that,
> Values of Field2 , B to X
> and Values of Field3 C to Y
> are the changes.
> any help ?
> Thanks again!
> G
> Uri Dimant wrote:
>
>|||Hi Uri Dimant,
Thanks for the quick and nice reply.
Thats very useful.
Thanks a ton!
Best,
G
Showing posts with label scenario. Show all posts
Showing posts with label scenario. Show all posts
Tuesday, March 20, 2012
Monday, March 19, 2012
Compare two queries to see if their results are identical
Scenario - I would like to compare two queries to find out if their results
are identical. The first query returns all the values stored in an invoice
table for a given invoice no. The second query returns all the values
stored in a table holding booking information.
E.g.
SELECT Quantity, [Description], UnitPrice, TaxPercentage, Tax, Price FROM
InvoiceDetails WHERE InvoiceNo = 12345
SELECT Quantity, [Description], UnitPrice, TaxPercentage, Tax, Price FROM
BookingDetails WHERE BookingNo = 20
I need to know whether the results from the two queries are identical. One
way I can think of doing this is to perform two EXISTS queries, the first
one finding a row that does not exist in InvoiceDetails that does exist in
BookingDetails and a second query that finds a row that does not exist in
BookingDetails that does in InvoiceDetails. Then if one or the other tests
finds a row the answer is they are different otherwise they are the same.
But is there a better way to perform this test - can be be done is one test?
Thanks.One way is to do a full join and then look for NULLs on either side.
Another method is to UNION the two queries and in the result GROUP BY
<all columns> HAVING COUNT(*)=1 to show any mismatches. This second
method has the advantage that you can compare on the basis of NULL=NULL
if you wish.
David Portas
SQL Server MVP
--|||The first way is to create a cursor with your first query and within to
compare it with your second result, as you specify it with IF EXISTS.
You should also do the opposite operation with the second resultset.
But you should consider that cursor are not efiiciency for large table. So
you could also create tempory table from you resulstet and compare them
trough join operation,
Laurent
"Chris" <cw@.community.nospam> a crit dans le message de news:
eli7vkbyFHA.3720@.TK2MSFTNGP14.phx.gbl...
> Scenario - I would like to compare two queries to find out if their
> results are identical. The first query returns all the values stored in
> an invoice table for a given invoice no. The second query returns all the
> values stored in a table holding booking information.
> E.g.
> SELECT Quantity, [Description], UnitPrice, TaxPercentage, Tax, Price FROM
> InvoiceDetails WHERE InvoiceNo = 12345
> SELECT Quantity, [Description], UnitPrice, TaxPercentage, Tax, Price FROM
> BookingDetails WHERE BookingNo = 20
> I need to know whether the results from the two queries are identical.
> One way I can think of doing this is to perform two EXISTS queries, the
> first one finding a row that does not exist in InvoiceDetails that does
> exist in BookingDetails and a second query that finds a row that does not
> exist in BookingDetails that does in InvoiceDetails. Then if one or the
> other tests finds a row the answer is they are different otherwise they
> are the same.
> But is there a better way to perform this test - can be be done is one
> test?
> Thanks.
>|||Try:
if exists (
select *
from InvoiceDetails as a inner join BookingDetails as b
on a.InvoiceNo = 12345 and b.BookingNo = 20
and a.Quantity = b.Quantity
and a.[Description] = b.[Description]
and a.UnitPrice = b.UnitPrice
and a.TaxPercentage = b.TaxPercentage
and a.Tax = b.Tax
and a.Price = b.Price
)
print 'identical'
else
print 'not identical'
go
AMB
"Chris" wrote:
> Scenario - I would like to compare two queries to find out if their result
s
> are identical. The first query returns all the values stored in an invoic
e
> table for a given invoice no. The second query returns all the values
> stored in a table holding booking information.
> E.g.
> SELECT Quantity, [Description], UnitPrice, TaxPercentage, Tax, Price FROM
> InvoiceDetails WHERE InvoiceNo = 12345
> SELECT Quantity, [Description], UnitPrice, TaxPercentage, Tax, Price FROM
> BookingDetails WHERE BookingNo = 20
> I need to know whether the results from the two queries are identical. On
e
> way I can think of doing this is to perform two EXISTS queries, the first
> one finding a row that does not exist in InvoiceDetails that does exist in
> BookingDetails and a second query that finds a row that does not exist in
> BookingDetails that does in InvoiceDetails. Then if one or the other test
s
> finds a row the answer is they are different otherwise they are the same.
> But is there a better way to perform this test - can be be done is one tes
t?
> Thanks.
>
>|||Can't you do a FULL OUTER JOIN and see if either side is NULL?
"Chris" <cw@.community.nospam> wrote in message
news:eli7vkbyFHA.3720@.TK2MSFTNGP14.phx.gbl...
> Scenario - I would like to compare two queries to find out if their
> results are identical. The first query returns all the values stored in
> an invoice table for a given invoice no. The second query returns all the
> values stored in a table holding booking information.
> E.g.
> SELECT Quantity, [Description], UnitPrice, TaxPercentage, Tax, Price FROM
> InvoiceDetails WHERE InvoiceNo = 12345
> SELECT Quantity, [Description], UnitPrice, TaxPercentage, Tax, Price FROM
> BookingDetails WHERE BookingNo = 20
> I need to know whether the results from the two queries are identical.
> One way I can think of doing this is to perform two EXISTS queries, the
> first one finding a row that does not exist in InvoiceDetails that does
> exist in BookingDetails and a second query that finds a row that does not
> exist in BookingDetails that does in InvoiceDetails. Then if one or the
> other tests finds a row the answer is they are different otherwise they
> are the same.
> But is there a better way to perform this test - can be be done is one
> test?
> Thanks.
>|||CREATE TABLE #T1(i int, j int)
CREATE TABLE #T2(i int, j int)
insert into #t1 values(1,2)
insert into #t2 values(1,2)
select count(*) from(
select * from #t1
union
select * from #t2
) t
-- identical
--
1
update #t2 set j=3
select count(*) from(
select * from #t1
union
select * from #t2
) t
-- different
--
2
drop table #t1
drop table #t2|||USE LRUKUVL
IF OBJECT_ID('tempdb..#Table1') IS NOT NULL DROP TABLE #Table1
IF OBJECT_ID('tempdb..#Table2') IS NOT NULL DROP TABLE #Table2
CREATE TABLE
#Table1
(
ID INT PRIMARY KEY CLUSTERED
, Data1 TINYINT NULL
, Data2 CHAR(1) NULL
)
CREATE TABLE
#Table2
(
ID INT PRIMARY KEY CLUSTERED
, Data1 TINYINT NULL
, Data2 CHAR(1) NULL
)
INSERT INTO #Table1
SELECT 1 , 4 , 'A'
UNION ALL SELECT 2 , 3 , 'B'
UNION ALL SELECT 3 , NULL , 'C'
UNION ALL SELECT 4 , 1 , 'D'
UNION ALL SELECT 5 , 1 , 'D'
UNION ALL SELECT 7 , 5 , NULL
INSERT INTO #Table2
SELECT 2 , 3 , 'B'
UNION ALL SELECT 3 , 2 , 'C'
UNION ALL SELECT 4 , 1 , NULL
UNION ALL SELECT 5 , 1 , 'E'
UNION ALL SELECT 6 , 1 , 'F'
UNION ALL SELECT 7 , 5 , NULL
/* return records that are different */
/* only id=2 and id=7 are equal - others are returned */
/* write a UDF to compaire a.data = b.data checking for equality, and is
nulls differences */
SELECT
*
FROM
#Table1 a
FULL OUTER JOIN
#Table2 b
ON
a.id = b.id
WHERE
a.id IS NULL
OR
b.id IS NULL
OR
( a.data1 IS NULL AND b.data1 IS NOT NULL )
OR
( a.data1 IS NOT NULL AND b.data1 IS NULL )
OR
( a.data1 != b.data1 )
OR
( a.data2 IS NULL AND b.data2 IS NOT NULL )
OR
( a.data2 IS NOT NULL AND b.data2 IS NULL )
OR
( a.data2 != b.data2 )
"Chris" <cw@.community.nospam> wrote in message
news:eli7vkbyFHA.3720@.TK2MSFTNGP14.phx.gbl...
> Scenario - I would like to compare two queries to find out if their
results
> are identical. The first query returns all the values stored in an
invoice
> table for a given invoice no. The second query returns all the values
> stored in a table holding booking information.
> E.g.
> SELECT Quantity, [Description], UnitPrice, TaxPercentage, Tax, Price FROM
> InvoiceDetails WHERE InvoiceNo = 12345
> SELECT Quantity, [Description], UnitPrice, TaxPercentage, Tax, Price FROM
> BookingDetails WHERE BookingNo = 20
> I need to know whether the results from the two queries are identical.
One
> way I can think of doing this is to perform two EXISTS queries, the first
> one finding a row that does not exist in InvoiceDetails that does exist in
> BookingDetails and a second query that finds a row that does not exist in
> BookingDetails that does in InvoiceDetails. Then if one or the other
tests
> finds a row the answer is they are different otherwise they are the same.|||Thanks to everyone for the replies
Chris
"Chris" <cw@.community.nospam> wrote in message
news:eli7vkbyFHA.3720@.TK2MSFTNGP14.phx.gbl...
> Scenario - I would like to compare two queries to find out if their
> results are identical. The first query returns all the values stored in
> an invoice table for a given invoice no. The second query returns all the
> values stored in a table holding booking information.
> E.g.
> SELECT Quantity, [Description], UnitPrice, TaxPercentage, Tax, Price FROM
> InvoiceDetails WHERE InvoiceNo = 12345
> SELECT Quantity, [Description], UnitPrice, TaxPercentage, Tax, Price FROM
> BookingDetails WHERE BookingNo = 20
> I need to know whether the results from the two queries are identical.
> One way I can think of doing this is to perform two EXISTS queries, the
> first one finding a row that does not exist in InvoiceDetails that does
> exist in BookingDetails and a second query that finds a row that does not
> exist in BookingDetails that does in InvoiceDetails. Then if one or the
> other tests finds a row the answer is they are different otherwise they
> are the same.
> But is there a better way to perform this test - can be be done is one
> test?
> Thanks.
>
are identical. The first query returns all the values stored in an invoice
table for a given invoice no. The second query returns all the values
stored in a table holding booking information.
E.g.
SELECT Quantity, [Description], UnitPrice, TaxPercentage, Tax, Price FROM
InvoiceDetails WHERE InvoiceNo = 12345
SELECT Quantity, [Description], UnitPrice, TaxPercentage, Tax, Price FROM
BookingDetails WHERE BookingNo = 20
I need to know whether the results from the two queries are identical. One
way I can think of doing this is to perform two EXISTS queries, the first
one finding a row that does not exist in InvoiceDetails that does exist in
BookingDetails and a second query that finds a row that does not exist in
BookingDetails that does in InvoiceDetails. Then if one or the other tests
finds a row the answer is they are different otherwise they are the same.
But is there a better way to perform this test - can be be done is one test?
Thanks.One way is to do a full join and then look for NULLs on either side.
Another method is to UNION the two queries and in the result GROUP BY
<all columns> HAVING COUNT(*)=1 to show any mismatches. This second
method has the advantage that you can compare on the basis of NULL=NULL
if you wish.
David Portas
SQL Server MVP
--|||The first way is to create a cursor with your first query and within to
compare it with your second result, as you specify it with IF EXISTS.
You should also do the opposite operation with the second resultset.
But you should consider that cursor are not efiiciency for large table. So
you could also create tempory table from you resulstet and compare them
trough join operation,
Laurent
"Chris" <cw@.community.nospam> a crit dans le message de news:
eli7vkbyFHA.3720@.TK2MSFTNGP14.phx.gbl...
> Scenario - I would like to compare two queries to find out if their
> results are identical. The first query returns all the values stored in
> an invoice table for a given invoice no. The second query returns all the
> values stored in a table holding booking information.
> E.g.
> SELECT Quantity, [Description], UnitPrice, TaxPercentage, Tax, Price FROM
> InvoiceDetails WHERE InvoiceNo = 12345
> SELECT Quantity, [Description], UnitPrice, TaxPercentage, Tax, Price FROM
> BookingDetails WHERE BookingNo = 20
> I need to know whether the results from the two queries are identical.
> One way I can think of doing this is to perform two EXISTS queries, the
> first one finding a row that does not exist in InvoiceDetails that does
> exist in BookingDetails and a second query that finds a row that does not
> exist in BookingDetails that does in InvoiceDetails. Then if one or the
> other tests finds a row the answer is they are different otherwise they
> are the same.
> But is there a better way to perform this test - can be be done is one
> test?
> Thanks.
>|||Try:
if exists (
select *
from InvoiceDetails as a inner join BookingDetails as b
on a.InvoiceNo = 12345 and b.BookingNo = 20
and a.Quantity = b.Quantity
and a.[Description] = b.[Description]
and a.UnitPrice = b.UnitPrice
and a.TaxPercentage = b.TaxPercentage
and a.Tax = b.Tax
and a.Price = b.Price
)
print 'identical'
else
print 'not identical'
go
AMB
"Chris" wrote:
> Scenario - I would like to compare two queries to find out if their result
s
> are identical. The first query returns all the values stored in an invoic
e
> table for a given invoice no. The second query returns all the values
> stored in a table holding booking information.
> E.g.
> SELECT Quantity, [Description], UnitPrice, TaxPercentage, Tax, Price FROM
> InvoiceDetails WHERE InvoiceNo = 12345
> SELECT Quantity, [Description], UnitPrice, TaxPercentage, Tax, Price FROM
> BookingDetails WHERE BookingNo = 20
> I need to know whether the results from the two queries are identical. On
e
> way I can think of doing this is to perform two EXISTS queries, the first
> one finding a row that does not exist in InvoiceDetails that does exist in
> BookingDetails and a second query that finds a row that does not exist in
> BookingDetails that does in InvoiceDetails. Then if one or the other test
s
> finds a row the answer is they are different otherwise they are the same.
> But is there a better way to perform this test - can be be done is one tes
t?
> Thanks.
>
>|||Can't you do a FULL OUTER JOIN and see if either side is NULL?
"Chris" <cw@.community.nospam> wrote in message
news:eli7vkbyFHA.3720@.TK2MSFTNGP14.phx.gbl...
> Scenario - I would like to compare two queries to find out if their
> results are identical. The first query returns all the values stored in
> an invoice table for a given invoice no. The second query returns all the
> values stored in a table holding booking information.
> E.g.
> SELECT Quantity, [Description], UnitPrice, TaxPercentage, Tax, Price FROM
> InvoiceDetails WHERE InvoiceNo = 12345
> SELECT Quantity, [Description], UnitPrice, TaxPercentage, Tax, Price FROM
> BookingDetails WHERE BookingNo = 20
> I need to know whether the results from the two queries are identical.
> One way I can think of doing this is to perform two EXISTS queries, the
> first one finding a row that does not exist in InvoiceDetails that does
> exist in BookingDetails and a second query that finds a row that does not
> exist in BookingDetails that does in InvoiceDetails. Then if one or the
> other tests finds a row the answer is they are different otherwise they
> are the same.
> But is there a better way to perform this test - can be be done is one
> test?
> Thanks.
>|||CREATE TABLE #T1(i int, j int)
CREATE TABLE #T2(i int, j int)
insert into #t1 values(1,2)
insert into #t2 values(1,2)
select count(*) from(
select * from #t1
union
select * from #t2
) t
-- identical
--
1
update #t2 set j=3
select count(*) from(
select * from #t1
union
select * from #t2
) t
-- different
--
2
drop table #t1
drop table #t2|||USE LRUKUVL
IF OBJECT_ID('tempdb..#Table1') IS NOT NULL DROP TABLE #Table1
IF OBJECT_ID('tempdb..#Table2') IS NOT NULL DROP TABLE #Table2
CREATE TABLE
#Table1
(
ID INT PRIMARY KEY CLUSTERED
, Data1 TINYINT NULL
, Data2 CHAR(1) NULL
)
CREATE TABLE
#Table2
(
ID INT PRIMARY KEY CLUSTERED
, Data1 TINYINT NULL
, Data2 CHAR(1) NULL
)
INSERT INTO #Table1
SELECT 1 , 4 , 'A'
UNION ALL SELECT 2 , 3 , 'B'
UNION ALL SELECT 3 , NULL , 'C'
UNION ALL SELECT 4 , 1 , 'D'
UNION ALL SELECT 5 , 1 , 'D'
UNION ALL SELECT 7 , 5 , NULL
INSERT INTO #Table2
SELECT 2 , 3 , 'B'
UNION ALL SELECT 3 , 2 , 'C'
UNION ALL SELECT 4 , 1 , NULL
UNION ALL SELECT 5 , 1 , 'E'
UNION ALL SELECT 6 , 1 , 'F'
UNION ALL SELECT 7 , 5 , NULL
/* return records that are different */
/* only id=2 and id=7 are equal - others are returned */
/* write a UDF to compaire a.data = b.data checking for equality, and is
nulls differences */
SELECT
*
FROM
#Table1 a
FULL OUTER JOIN
#Table2 b
ON
a.id = b.id
WHERE
a.id IS NULL
OR
b.id IS NULL
OR
( a.data1 IS NULL AND b.data1 IS NOT NULL )
OR
( a.data1 IS NOT NULL AND b.data1 IS NULL )
OR
( a.data1 != b.data1 )
OR
( a.data2 IS NULL AND b.data2 IS NOT NULL )
OR
( a.data2 IS NOT NULL AND b.data2 IS NULL )
OR
( a.data2 != b.data2 )
"Chris" <cw@.community.nospam> wrote in message
news:eli7vkbyFHA.3720@.TK2MSFTNGP14.phx.gbl...
> Scenario - I would like to compare two queries to find out if their
results
> are identical. The first query returns all the values stored in an
invoice
> table for a given invoice no. The second query returns all the values
> stored in a table holding booking information.
> E.g.
> SELECT Quantity, [Description], UnitPrice, TaxPercentage, Tax, Price FROM
> InvoiceDetails WHERE InvoiceNo = 12345
> SELECT Quantity, [Description], UnitPrice, TaxPercentage, Tax, Price FROM
> BookingDetails WHERE BookingNo = 20
> I need to know whether the results from the two queries are identical.
One
> way I can think of doing this is to perform two EXISTS queries, the first
> one finding a row that does not exist in InvoiceDetails that does exist in
> BookingDetails and a second query that finds a row that does not exist in
> BookingDetails that does in InvoiceDetails. Then if one or the other
tests
> finds a row the answer is they are different otherwise they are the same.|||Thanks to everyone for the replies
Chris
"Chris" <cw@.community.nospam> wrote in message
news:eli7vkbyFHA.3720@.TK2MSFTNGP14.phx.gbl...
> Scenario - I would like to compare two queries to find out if their
> results are identical. The first query returns all the values stored in
> an invoice table for a given invoice no. The second query returns all the
> values stored in a table holding booking information.
> E.g.
> SELECT Quantity, [Description], UnitPrice, TaxPercentage, Tax, Price FROM
> InvoiceDetails WHERE InvoiceNo = 12345
> SELECT Quantity, [Description], UnitPrice, TaxPercentage, Tax, Price FROM
> BookingDetails WHERE BookingNo = 20
> I need to know whether the results from the two queries are identical.
> One way I can think of doing this is to perform two EXISTS queries, the
> first one finding a row that does not exist in InvoiceDetails that does
> exist in BookingDetails and a second query that finds a row that does not
> exist in BookingDetails that does in InvoiceDetails. Then if one or the
> other tests finds a row the answer is they are different otherwise they
> are the same.
> But is there a better way to perform this test - can be be done is one
> test?
> Thanks.
>
Sunday, March 11, 2012
compare mdf for success restore of data and file storage
Gurus,
I have a scenario where in if i restore a database file(mdf) onto
say some AppManager,i want to make sure its restored properly comparing with
the mdf that it came from(i take this as base).i hope iam making sense here.Ram,
Restore of a backup (.bak) or an attach of a data file (.mdf)? If the
latter do you have the log file as well? If so the database will resolve to
the same schema and data as the database was when it was detached.
HTH
Jerry
"Ram" <Ram@.discussions.microsoft.com> wrote in message
news:4D3E6347-DCCE-49C4-A1DD-B14D0596AB82@.microsoft.com...
> Gurus,
> I have a scenario where in if i restore a database file(mdf)
> onto
> say some AppManager,i want to make sure its restored properly comparing
> with
> the mdf that it came from(i take this as base).i hope iam making sense
> here.|||Thanks Jerry,
Actually backup file contains the folders database(mdf and logfile) and the
file store.
Ram
"Jerry Spivey" wrote:
> Ram,
> Restore of a backup (.bak) or an attach of a data file (.mdf)? If the
> latter do you have the log file as well? If so the database will resolve
to
> the same schema and data as the database was when it was detached.
> HTH
> Jerry
> "Ram" <Ram@.discussions.microsoft.com> wrote in message
> news:4D3E6347-DCCE-49C4-A1DD-B14D0596AB82@.microsoft.com...
>
>|||Jerry,
Back up file has an extension of _dbbak which is compressed and contains
both mdf and ldf files.
"Jerry Spivey" wrote:
> Ram,
> Restore of a backup (.bak) or an attach of a data file (.mdf)? If the
> latter do you have the log file as well? If so the database will resolve
to
> the same schema and data as the database was when it was detached.
> HTH
> Jerry
> "Ram" <Ram@.discussions.microsoft.com> wrote in message
> news:4D3E6347-DCCE-49C4-A1DD-B14D0596AB82@.microsoft.com...
>
>|||Ram,
Compressed? Try decompressing the .mdf and .ldf files then copy them to the
location(s) where you store your .mdf and .ldf files. Then use sp_attach_db
to associate the database with SQL Server.
HTH
Jerry
"Ram" <Ram@.discussions.microsoft.com> wrote in message
news:A4A34557-DEAA-4E69-81B6-5085E78C0830@.microsoft.com...
> Jerry,
> Back up file has an extension of _dbbak which is compressed and contains
> both mdf and ldf files.
> "Jerry Spivey" wrote:
>
I have a scenario where in if i restore a database file(mdf) onto
say some AppManager,i want to make sure its restored properly comparing with
the mdf that it came from(i take this as base).i hope iam making sense here.Ram,
Restore of a backup (.bak) or an attach of a data file (.mdf)? If the
latter do you have the log file as well? If so the database will resolve to
the same schema and data as the database was when it was detached.
HTH
Jerry
"Ram" <Ram@.discussions.microsoft.com> wrote in message
news:4D3E6347-DCCE-49C4-A1DD-B14D0596AB82@.microsoft.com...
> Gurus,
> I have a scenario where in if i restore a database file(mdf)
> onto
> say some AppManager,i want to make sure its restored properly comparing
> with
> the mdf that it came from(i take this as base).i hope iam making sense
> here.|||Thanks Jerry,
Actually backup file contains the folders database(mdf and logfile) and the
file store.
Ram
"Jerry Spivey" wrote:
> Ram,
> Restore of a backup (.bak) or an attach of a data file (.mdf)? If the
> latter do you have the log file as well? If so the database will resolve
to
> the same schema and data as the database was when it was detached.
> HTH
> Jerry
> "Ram" <Ram@.discussions.microsoft.com> wrote in message
> news:4D3E6347-DCCE-49C4-A1DD-B14D0596AB82@.microsoft.com...
>
>|||Jerry,
Back up file has an extension of _dbbak which is compressed and contains
both mdf and ldf files.
"Jerry Spivey" wrote:
> Ram,
> Restore of a backup (.bak) or an attach of a data file (.mdf)? If the
> latter do you have the log file as well? If so the database will resolve
to
> the same schema and data as the database was when it was detached.
> HTH
> Jerry
> "Ram" <Ram@.discussions.microsoft.com> wrote in message
> news:4D3E6347-DCCE-49C4-A1DD-B14D0596AB82@.microsoft.com...
>
>|||Ram,
Compressed? Try decompressing the .mdf and .ldf files then copy them to the
location(s) where you store your .mdf and .ldf files. Then use sp_attach_db
to associate the database with SQL Server.
HTH
Jerry
"Ram" <Ram@.discussions.microsoft.com> wrote in message
news:A4A34557-DEAA-4E69-81B6-5085E78C0830@.microsoft.com...
> Jerry,
> Back up file has an extension of _dbbak which is compressed and contains
> both mdf and ldf files.
> "Jerry Spivey" wrote:
>
Thursday, February 16, 2012
Comments on configuring partitions
Hello,
I'm currently building partitions into my cube. My scenario is thus:
I have 74 measure groups (yeah I know - it sounds ridiculous but trust me, it IS valid)
Each measure group is to be partitioned by year
We are (currently) going to partition them over 12 years
In other words I have to build 74*12 = 888 partitions
Quite apart from the fact that its going to take most of my working week (and it'll be the most boring working week of my life) there is huge potential for human error here.
As stated above I am applying the same partitioning strategy for each of my 74 measure groups so I am repeating the same basic steps 74 times. It occurs to me that there MUST be a better way and if there isn't - there should be!
Does anyone know of a better way? I'd like to automate it somehow but I don't see how that is possible seeing as I have to edit a SQL statement for each one.
Anyone got any thoughts? I'm happy to provide feedback to MSFT about how this could be improved - contact me offline if interested - jamie.thomson[@.]nospam.conchango.com
ta
Jamie
I've done something similar using an SSIS package - I just scripted out the XML/A necessary to build a partition to use as a template, then created a FOR loop inside my package, used some script to generate the XMLA (and the SQL inside it) and then executed it. It worked pretty well, and I'll send you the code even though I know you're more than capable of doing this yourself. I agree it would be nice to have this functionality built into BIDS.
One thing that caught me here though was the problem with setting the slices for partitions that came up at the BI evening we were at, and which is discussed on Mark Hill's blog:
http://markhill.org/blog/?p=11
I created around 600 partitions on my cube and some of them turned out to have very little or no data in; this meant that no slicers got set automatically and my query performance suffered as a result.
Chris
|||[Chris sent me this stuff offline]
Cheers Chris. That's good stuff and works well.
HOWEVER, this only works once the cube has been deployed. I want the partitions to appear in my BIDS solution (I should have said that originally - sorry about that). Is there a way to reverse-engineer them back in?
-Jamie
|||
Yes, you can reverse engineer a project from a deployed database very easily. All you need to do is open BIDS, then File/New/Project and select "Import Analysis Services 9.0 Database".
Chris
|||After looking at Chris' package its just occurred to me....hod useful would it be to have an AMO Enumerator in the SSIS ForEach loop?
That would be brilliant!! Microsoft Connect here I come!!!
-Jamie
|||
Jamie Thomson wrote:
After looking at Chris' package its just occurred to me....hod useful would it be to have an AMO Enumerator in the SSIS ForEach loop?
That would be brilliant!! Microsoft Connect here I come!!!
-Jamie
And here it is: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=163202
Click through and vote would you please!!! And leave a comment. Much more likely to get it that way!
|||Hi Jamie:
You could probably have relational one-to-one for your partitions. That way - you could iterate that in SSIS and conditionally create the partitions if required - and then process them.
Hope that helps.
Suranjan
Subscribe to:
Posts (Atom)