A growing Sql Server 2005 database performs several hours of updates each
night. This particular region of code has run fine for over a year. Now we
are getting the folllowing message every few nights causing our processing to
abort:
Msg 8630, Level 17, State 52, Procedure sp_dts_post_activity, Line 150
Internal Query Processor Error: The query processor encountered an unexpected
error during execution.
This is a simplified version of the query from line 150:
select
p.PeriodStartDate,
p.PeriodType,
p.ActivityUserNumber,
p.AppNumber,
max( case when ActivityType = 'APP_SUBMITTED' then 1 else 0 end ),
max( case when ActivityType = 'DOCS_REVIEWED' then 1 else 0 end ),
max( case when ActivityType = 'DOCS_RECEIVED' then 1 else 0 end ),
max( case when ActivityType = 'DOCS_COMPLETED' then 1 else 0 end ),
max( case when ActivityType = 'BOOKED' then 1 else 0 end ),
max( case when ActivityType = 'FUNDED' then 1 else 0 end )
from Activity a, xxxPeriod p
where a.ActivityDate >= p.PeriodStartDate
and a.ActivityDate < p.PeriodEndDate
and a.ActivityUserNumber = p.ActivityUserNumber
and a.AppNumber = p.AppNumber
and ActivityType in (select code from Lookup where SetName =
'ACTIVITY_TYPE' and ParentCode = 'ACCOUNT')
group by p.PeriodStartDate, p.PeriodType, p.ActivityUserNumber, p.AppNumber
When this simplified query is run from the management studio, it fails about
10-20% of the time.
Some Observations:
- Sometimes we get a few records in the result set prior to the failure.
- This query works on our smaller development database.
- If we change the query in any of the following ways, the query works
(e.g., 15 attempts w/o an error):
- Removing the group by and max()
- Removing one or more max statements
- Hard-code the list of activity types in place of the sub-select from
Lookup table
Version:
Sql Server 2005, 9.00.2047.00,SP1, Standard Edition
Help!
Mike
Mike wrote:
> A growing Sql Server 2005 database performs several hours of updates each
> night. This particular region of code has run fine for over a year. Now we
> are getting the folllowing message every few nights causing our processing to
> abort:
> Msg 8630, Level 17, State 52, Procedure sp_dts_post_activity, Line 150
> Internal Query Processor Error: The query processor encountered an unexpected
> error during execution.
> This is a simplified version of the query from line 150:
> select
> p.PeriodStartDate,
> p.PeriodType,
> p.ActivityUserNumber,
> p.AppNumber,
> max( case when ActivityType = 'APP_SUBMITTED' then 1 else 0 end ),
> max( case when ActivityType = 'DOCS_REVIEWED' then 1 else 0 end ),
> max( case when ActivityType = 'DOCS_RECEIVED' then 1 else 0 end ),
> max( case when ActivityType = 'DOCS_COMPLETED' then 1 else 0 end ),
> max( case when ActivityType = 'BOOKED' then 1 else 0 end ),
> max( case when ActivityType = 'FUNDED' then 1 else 0 end )
> from Activity a, xxxPeriod p
> where a.ActivityDate >= p.PeriodStartDate
> and a.ActivityDate < p.PeriodEndDate
> and a.ActivityUserNumber = p.ActivityUserNumber
> and a.AppNumber = p.AppNumber
> and ActivityType in (select code from Lookup where SetName =
> 'ACTIVITY_TYPE' and ParentCode = 'ACCOUNT')
> group by p.PeriodStartDate, p.PeriodType, p.ActivityUserNumber, p.AppNumber
> When this simplified query is run from the management studio, it fails about
> 10-20% of the time.
> Some Observations:
> - Sometimes we get a few records in the result set prior to the failure.
> - This query works on our smaller development database.
> - If we change the query in any of the following ways, the query works
> (e.g., 15 attempts w/o an error):
> - Removing the group by and max()
> - Removing one or more max statements
> - Hard-code the list of activity types in place of the sub-select from
> Lookup table
> Version:
> Sql Server 2005, 9.00.2047.00,SP1, Standard Edition
> Help!
> Mike
>
>
>
>
I would suspect a TEMPDB problem. Lack of space? Autogrow timeout?
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Mike, please contact Microsoft product support.
Thanks,
Leo
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:168FB0C7-AF8D-4722-B8EF-FC5A8D309F47@.microsoft.com...
>A growing Sql Server 2005 database performs several hours of updates each
> night. This particular region of code has run fine for over a year. Now
> we
> are getting the folllowing message every few nights causing our processing
> to
> abort:
> Msg 8630, Level 17, State 52, Procedure sp_dts_post_activity, Line 150
> Internal Query Processor Error: The query processor encountered an
> unexpected
> error during execution.
> This is a simplified version of the query from line 150:
> select
> p.PeriodStartDate,
> p.PeriodType,
> p.ActivityUserNumber,
> p.AppNumber,
> max( case when ActivityType = 'APP_SUBMITTED' then 1 else 0 end ),
> max( case when ActivityType = 'DOCS_REVIEWED' then 1 else 0 end ),
> max( case when ActivityType = 'DOCS_RECEIVED' then 1 else 0 end ),
> max( case when ActivityType = 'DOCS_COMPLETED' then 1 else 0 end ),
> max( case when ActivityType = 'BOOKED' then 1 else 0 end ),
> max( case when ActivityType = 'FUNDED' then 1 else 0 end )
> from Activity a, xxxPeriod p
> where a.ActivityDate >= p.PeriodStartDate
> and a.ActivityDate < p.PeriodEndDate
> and a.ActivityUserNumber = p.ActivityUserNumber
> and a.AppNumber = p.AppNumber
> and ActivityType in (select code from Lookup where SetName =
> 'ACTIVITY_TYPE' and ParentCode = 'ACCOUNT')
> group by p.PeriodStartDate, p.PeriodType, p.ActivityUserNumber,
> p.AppNumber
> When this simplified query is run from the management studio, it fails
> about
> 10-20% of the time.
> Some Observations:
> - Sometimes we get a few records in the result set prior to the failure.
> - This query works on our smaller development database.
> - If we change the query in any of the following ways, the query works
> (e.g., 15 attempts w/o an error):
> - Removing the group by and max()
> - Removing one or more max statements
> - Hard-code the list of activity types in place of the sub-select from
> Lookup table
> Version:
> Sql Server 2005, 9.00.2047.00,SP1, Standard Edition
> Help!
> Mike
>
>
>
>
Showing posts with label region. Show all posts
Showing posts with label region. Show all posts
Tuesday, March 6, 2012
A Real Sql Server 2005 Bug! A Real Sql Server Bug!
A growing Sql Server 2005 database performs several hours of updates each
night. This particular region of code has run fine for over a year. Now we
are getting the folllowing message every few nights causing our processing t
o
abort:
Msg 8630, Level 17, State 52, Procedure sp_dts_post_activity, Line 150
Internal Query Processor Error: The query processor encountered an unexpecte
d
error during execution.
This is a simplified version of the query from line 150:
select
p.PeriodStartDate,
p.PeriodType,
p.ActivityUserNumber,
p.AppNumber,
max( case when ActivityType = 'APP_SUBMITTED' then 1 else 0 end ),
max( case when ActivityType = 'DOCS_REVIEWED' then 1 else 0 end ),
max( case when ActivityType = 'DOCS_RECEIVED' then 1 else 0 end ),
max( case when ActivityType = 'DOCS_COMPLETED' then 1 else 0 end ),
max( case when ActivityType = 'BOOKED' then 1 else 0 end ),
max( case when ActivityType = 'FUNDED' then 1 else 0 end )
from Activity a, xxxPeriod p
where a.ActivityDate >= p.PeriodStartDate
and a.ActivityDate < p.PeriodEndDate
and a.ActivityUserNumber = p.ActivityUserNumber
and a.AppNumber = p.AppNumber
and ActivityType in (select code from Lookup where SetName =
'ACTIVITY_TYPE' and ParentCode = 'ACCOUNT')
group by p.PeriodStartDate, p.PeriodType, p.ActivityUserNumber, p.AppNumber
When this simplified query is run from the management studio, it fails about
10-20% of the time.
Some Observations:
- Sometimes we get a few records in the result set prior to the failure.
- This query works on our smaller development database.
- If we change the query in any of the following ways, the query works
(e.g., 15 attempts w/o an error):
- Removing the group by and max()
- Removing one or more max statements
- Hard-code the list of activity types in place of the sub-select from
Lookup table
Version:
Sql Server 2005, 9.00.2047.00,SP1, Standard Edition
Help!
MikeMike wrote:
> A growing Sql Server 2005 database performs several hours of updates each
> night. This particular region of code has run fine for over a year. Now
we
> are getting the folllowing message every few nights causing our processing
to
> abort:
> Msg 8630, Level 17, State 52, Procedure sp_dts_post_activity, Line 150
> Internal Query Processor Error: The query processor encountered an unexpec
ted
> error during execution.
> This is a simplified version of the query from line 150:
> select
> p.PeriodStartDate,
> p.PeriodType,
> p.ActivityUserNumber,
> p.AppNumber,
> max( case when ActivityType = 'APP_SUBMITTED' then 1 else 0 end ),
> max( case when ActivityType = 'DOCS_REVIEWED' then 1 else 0 end ),
> max( case when ActivityType = 'DOCS_RECEIVED' then 1 else 0 end ),
> max( case when ActivityType = 'DOCS_COMPLETED' then 1 else 0 end ),
> max( case when ActivityType = 'BOOKED' then 1 else 0 end ),
> max( case when ActivityType = 'FUNDED' then 1 else 0 end )
> from Activity a, xxxPeriod p
> where a.ActivityDate >= p.PeriodStartDate
> and a.ActivityDate < p.PeriodEndDate
> and a.ActivityUserNumber = p.ActivityUserNumber
> and a.AppNumber = p.AppNumber
> and ActivityType in (select code from Lookup where SetName =
> 'ACTIVITY_TYPE' and ParentCode = 'ACCOUNT')
> group by p.PeriodStartDate, p.PeriodType, p.ActivityUserNumber, p.AppNumb
er
> When this simplified query is run from the management studio, it fails abo
ut
> 10-20% of the time.
> Some Observations:
> - Sometimes we get a few records in the result set prior to the failure.
> - This query works on our smaller development database.
> - If we change the query in any of the following ways, the query works
> (e.g., 15 attempts w/o an error):
> - Removing the group by and max()
> - Removing one or more max statements
> - Hard-code the list of activity types in place of the sub-select from
> Lookup table
> Version:
> Sql Server 2005, 9.00.2047.00,SP1, Standard Edition
> Help!
> Mike
>
>
>
>
I would suspect a TEMPDB problem. Lack of space? Autogrow timeout?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Mike, please contact Microsoft product support.
Thanks,
Leo
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:168FB0C7-AF8D-4722-B8EF-FC5A8D309F47@.microsoft.com...
>A growing Sql Server 2005 database performs several hours of updates each
> night. This particular region of code has run fine for over a year. Now
> we
> are getting the folllowing message every few nights causing our processing
> to
> abort:
> Msg 8630, Level 17, State 52, Procedure sp_dts_post_activity, Line 150
> Internal Query Processor Error: The query processor encountered an
> unexpected
> error during execution.
> This is a simplified version of the query from line 150:
> select
> p.PeriodStartDate,
> p.PeriodType,
> p.ActivityUserNumber,
> p.AppNumber,
> max( case when ActivityType = 'APP_SUBMITTED' then 1 else 0 end ),
> max( case when ActivityType = 'DOCS_REVIEWED' then 1 else 0 end ),
> max( case when ActivityType = 'DOCS_RECEIVED' then 1 else 0 end ),
> max( case when ActivityType = 'DOCS_COMPLETED' then 1 else 0 end ),
> max( case when ActivityType = 'BOOKED' then 1 else 0 end ),
> max( case when ActivityType = 'FUNDED' then 1 else 0 end )
> from Activity a, xxxPeriod p
> where a.ActivityDate >= p.PeriodStartDate
> and a.ActivityDate < p.PeriodEndDate
> and a.ActivityUserNumber = p.ActivityUserNumber
> and a.AppNumber = p.AppNumber
> and ActivityType in (select code from Lookup where SetName =
> 'ACTIVITY_TYPE' and ParentCode = 'ACCOUNT')
> group by p.PeriodStartDate, p.PeriodType, p.ActivityUserNumber,
> p.AppNumber
> When this simplified query is run from the management studio, it fails
> about
> 10-20% of the time.
> Some Observations:
> - Sometimes we get a few records in the result set prior to the failure.
> - This query works on our smaller development database.
> - If we change the query in any of the following ways, the query works
> (e.g., 15 attempts w/o an error):
> - Removing the group by and max()
> - Removing one or more max statements
> - Hard-code the list of activity types in place of the sub-select from
> Lookup table
> Version:
> Sql Server 2005, 9.00.2047.00,SP1, Standard Edition
> Help!
> Mike
>
>
>
>
night. This particular region of code has run fine for over a year. Now we
are getting the folllowing message every few nights causing our processing t
o
abort:
Msg 8630, Level 17, State 52, Procedure sp_dts_post_activity, Line 150
Internal Query Processor Error: The query processor encountered an unexpecte
d
error during execution.
This is a simplified version of the query from line 150:
select
p.PeriodStartDate,
p.PeriodType,
p.ActivityUserNumber,
p.AppNumber,
max( case when ActivityType = 'APP_SUBMITTED' then 1 else 0 end ),
max( case when ActivityType = 'DOCS_REVIEWED' then 1 else 0 end ),
max( case when ActivityType = 'DOCS_RECEIVED' then 1 else 0 end ),
max( case when ActivityType = 'DOCS_COMPLETED' then 1 else 0 end ),
max( case when ActivityType = 'BOOKED' then 1 else 0 end ),
max( case when ActivityType = 'FUNDED' then 1 else 0 end )
from Activity a, xxxPeriod p
where a.ActivityDate >= p.PeriodStartDate
and a.ActivityDate < p.PeriodEndDate
and a.ActivityUserNumber = p.ActivityUserNumber
and a.AppNumber = p.AppNumber
and ActivityType in (select code from Lookup where SetName =
'ACTIVITY_TYPE' and ParentCode = 'ACCOUNT')
group by p.PeriodStartDate, p.PeriodType, p.ActivityUserNumber, p.AppNumber
When this simplified query is run from the management studio, it fails about
10-20% of the time.
Some Observations:
- Sometimes we get a few records in the result set prior to the failure.
- This query works on our smaller development database.
- If we change the query in any of the following ways, the query works
(e.g., 15 attempts w/o an error):
- Removing the group by and max()
- Removing one or more max statements
- Hard-code the list of activity types in place of the sub-select from
Lookup table
Version:
Sql Server 2005, 9.00.2047.00,SP1, Standard Edition
Help!
MikeMike wrote:
> A growing Sql Server 2005 database performs several hours of updates each
> night. This particular region of code has run fine for over a year. Now
we
> are getting the folllowing message every few nights causing our processing
to
> abort:
> Msg 8630, Level 17, State 52, Procedure sp_dts_post_activity, Line 150
> Internal Query Processor Error: The query processor encountered an unexpec
ted
> error during execution.
> This is a simplified version of the query from line 150:
> select
> p.PeriodStartDate,
> p.PeriodType,
> p.ActivityUserNumber,
> p.AppNumber,
> max( case when ActivityType = 'APP_SUBMITTED' then 1 else 0 end ),
> max( case when ActivityType = 'DOCS_REVIEWED' then 1 else 0 end ),
> max( case when ActivityType = 'DOCS_RECEIVED' then 1 else 0 end ),
> max( case when ActivityType = 'DOCS_COMPLETED' then 1 else 0 end ),
> max( case when ActivityType = 'BOOKED' then 1 else 0 end ),
> max( case when ActivityType = 'FUNDED' then 1 else 0 end )
> from Activity a, xxxPeriod p
> where a.ActivityDate >= p.PeriodStartDate
> and a.ActivityDate < p.PeriodEndDate
> and a.ActivityUserNumber = p.ActivityUserNumber
> and a.AppNumber = p.AppNumber
> and ActivityType in (select code from Lookup where SetName =
> 'ACTIVITY_TYPE' and ParentCode = 'ACCOUNT')
> group by p.PeriodStartDate, p.PeriodType, p.ActivityUserNumber, p.AppNumb
er
> When this simplified query is run from the management studio, it fails abo
ut
> 10-20% of the time.
> Some Observations:
> - Sometimes we get a few records in the result set prior to the failure.
> - This query works on our smaller development database.
> - If we change the query in any of the following ways, the query works
> (e.g., 15 attempts w/o an error):
> - Removing the group by and max()
> - Removing one or more max statements
> - Hard-code the list of activity types in place of the sub-select from
> Lookup table
> Version:
> Sql Server 2005, 9.00.2047.00,SP1, Standard Edition
> Help!
> Mike
>
>
>
>
I would suspect a TEMPDB problem. Lack of space? Autogrow timeout?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Mike, please contact Microsoft product support.
Thanks,
Leo
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:168FB0C7-AF8D-4722-B8EF-FC5A8D309F47@.microsoft.com...
>A growing Sql Server 2005 database performs several hours of updates each
> night. This particular region of code has run fine for over a year. Now
> we
> are getting the folllowing message every few nights causing our processing
> to
> abort:
> Msg 8630, Level 17, State 52, Procedure sp_dts_post_activity, Line 150
> Internal Query Processor Error: The query processor encountered an
> unexpected
> error during execution.
> This is a simplified version of the query from line 150:
> select
> p.PeriodStartDate,
> p.PeriodType,
> p.ActivityUserNumber,
> p.AppNumber,
> max( case when ActivityType = 'APP_SUBMITTED' then 1 else 0 end ),
> max( case when ActivityType = 'DOCS_REVIEWED' then 1 else 0 end ),
> max( case when ActivityType = 'DOCS_RECEIVED' then 1 else 0 end ),
> max( case when ActivityType = 'DOCS_COMPLETED' then 1 else 0 end ),
> max( case when ActivityType = 'BOOKED' then 1 else 0 end ),
> max( case when ActivityType = 'FUNDED' then 1 else 0 end )
> from Activity a, xxxPeriod p
> where a.ActivityDate >= p.PeriodStartDate
> and a.ActivityDate < p.PeriodEndDate
> and a.ActivityUserNumber = p.ActivityUserNumber
> and a.AppNumber = p.AppNumber
> and ActivityType in (select code from Lookup where SetName =
> 'ACTIVITY_TYPE' and ParentCode = 'ACCOUNT')
> group by p.PeriodStartDate, p.PeriodType, p.ActivityUserNumber,
> p.AppNumber
> When this simplified query is run from the management studio, it fails
> about
> 10-20% of the time.
> Some Observations:
> - Sometimes we get a few records in the result set prior to the failure.
> - This query works on our smaller development database.
> - If we change the query in any of the following ways, the query works
> (e.g., 15 attempts w/o an error):
> - Removing the group by and max()
> - Removing one or more max statements
> - Hard-code the list of activity types in place of the sub-select from
> Lookup table
> Version:
> Sql Server 2005, 9.00.2047.00,SP1, Standard Edition
> Help!
> Mike
>
>
>
>
Sunday, February 19, 2012
A problem with DTS Import/Export wizard....
Hello,
I am trying to replace data in the "region" column in table 2 with data in
the "region" column in table 1...in other words 1 is the source and 2 is the
destination. I go thru the wizard and then use a query to specify the data
to transfer. I go into the query builder and select the column that I want
transfered. I don't specify a sort order or criteria. The query statement
is thus:
select [Customers].[Region]
from [Customers]
easy enough...
I then select the source table and click transform and I do not see an
option to replace the data only append, create destination table, and delete
rows in destination table.
Is this all the functionality of the wizard gives me?
Do I need to create my own script?
Thanks
Ken S.I am sure that someone can help, but we will need some additional =information...
Do the tables (table1 and table2) share a common column that you can =JOIN on in order to perform an update?
What other data exists within Table2? Does all the data come from =Table1? Can you simply delete all the rows within Table2 and insert =from Table1?
-- Keith
"SMAN" <ksanti@.nycap.rr.com> wrote in message =news:eJUmwnjxDHA.2456@.TK2MSFTNGP12.phx.gbl...
> Hello,
> > I am trying to replace data in the "region" column in table 2 with =data in
> the "region" column in table 1...in other words 1 is the source and 2 =is the
> destination. I go thru the wizard and then use a query to specify the =data
> to transfer. I go into the query builder and select the column that I =want
> transfered. I don't specify a sort order or criteria. The query =statement
> is thus:
> > select [Customers].[Region]
> from [Customers]
> > easy enough...
> > I then select the source table and click transform and I do not see an
> option to replace the data only append, create destination table, and =delete
> rows in destination table.
> > Is this all the functionality of the wizard gives me?
> > Do I need to create my own script?
> > Thanks
> > Ken S.
> >|||Thanks Keith...
both tables are identical tables with the same structure and row count. The
region column in table 1 is the data I want in table 2's region column.
Perhaps your solution of deleting all the rows in table 2 and replacing with
table 1 rows is the way to go.
Thanks
Ken
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:ejEZiujxDHA.2328@.TK2MSFTNGP10.phx.gbl...
I am sure that someone can help, but we will need some additional
information...
Do the tables (table1 and table2) share a common column that you can JOIN on
in order to perform an update?
What other data exists within Table2? Does all the data come from Table1?
Can you simply delete all the rows within Table2 and insert from Table1?
--
Keith
"SMAN" <ksanti@.nycap.rr.com> wrote in message
news:eJUmwnjxDHA.2456@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I am trying to replace data in the "region" column in table 2 with data in
> the "region" column in table 1...in other words 1 is the source and 2 is
the
> destination. I go thru the wizard and then use a query to specify the
data
> to transfer. I go into the query builder and select the column that I
want
> transfered. I don't specify a sort order or criteria. The query
statement
> is thus:
> select [Customers].[Region]
> from [Customers]
> easy enough...
> I then select the source table and click transform and I do not see an
> option to replace the data only append, create destination table, and
delete
> rows in destination table.
> Is this all the functionality of the wizard gives me?
> Do I need to create my own script?
> Thanks
> Ken S.
>|||Since the tables are identical you could also update the data with an =update statement:
UPDATE table2 SET region =3D B.region
FROM table2 A JOIN table1 B ON A.ThePrimaryKeyColumn =3D =B.ThePrimaryKeyColumn
SELECT @.@.rowcount
-- Keith
"SMAN" <ksanti@.nycap.rr.com> wrote in message =news:uKByO2jxDHA.3116@.tk2msftngp13.phx.gbl...
> Thanks Keith...
> > both tables are identical tables with the same structure and row =count. The
> region column in table 1 is the data I want in table 2's region =column.
> Perhaps your solution of deleting all the rows in table 2 and =replacing with
> table 1 rows is the way to go.
> > Thanks
> > Ken
> > > "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:ejEZiujxDHA.2328@.TK2MSFTNGP10.phx.gbl...
> I am sure that someone can help, but we will need some additional
> information...
> > Do the tables (table1 and table2) share a common column that you can =JOIN on
> in order to perform an update?
> > What other data exists within Table2? Does all the data come from =Table1?
> Can you simply delete all the rows within Table2 and insert from =Table1?
> > --
> Keith
> > > "SMAN" <ksanti@.nycap.rr.com> wrote in message
> news:eJUmwnjxDHA.2456@.TK2MSFTNGP12.phx.gbl...
> > Hello,
> >
> > I am trying to replace data in the "region" column in table 2 with =data in
> > the "region" column in table 1...in other words 1 is the source and =2 is
> the
> > destination. I go thru the wizard and then use a query to specify =the
> data
> > to transfer. I go into the query builder and select the column that =I
> want
> > transfered. I don't specify a sort order or criteria. The query
> statement
> > is thus:
> >
> > select [Customers].[Region]
> > from [Customers]
> >
> > easy enough...
> >
> > I then select the source table and click transform and I do not see =an
> > option to replace the data only append, create destination table, =and
> delete
> > rows in destination table.
> >
> > Is this all the functionality of the wizard gives me?
> >
> > Do I need to create my own script?
> >
> > Thanks
> >
> > Ken S.
> >
> >
> >
I am trying to replace data in the "region" column in table 2 with data in
the "region" column in table 1...in other words 1 is the source and 2 is the
destination. I go thru the wizard and then use a query to specify the data
to transfer. I go into the query builder and select the column that I want
transfered. I don't specify a sort order or criteria. The query statement
is thus:
select [Customers].[Region]
from [Customers]
easy enough...
I then select the source table and click transform and I do not see an
option to replace the data only append, create destination table, and delete
rows in destination table.
Is this all the functionality of the wizard gives me?
Do I need to create my own script?
Thanks
Ken S.I am sure that someone can help, but we will need some additional =information...
Do the tables (table1 and table2) share a common column that you can =JOIN on in order to perform an update?
What other data exists within Table2? Does all the data come from =Table1? Can you simply delete all the rows within Table2 and insert =from Table1?
-- Keith
"SMAN" <ksanti@.nycap.rr.com> wrote in message =news:eJUmwnjxDHA.2456@.TK2MSFTNGP12.phx.gbl...
> Hello,
> > I am trying to replace data in the "region" column in table 2 with =data in
> the "region" column in table 1...in other words 1 is the source and 2 =is the
> destination. I go thru the wizard and then use a query to specify the =data
> to transfer. I go into the query builder and select the column that I =want
> transfered. I don't specify a sort order or criteria. The query =statement
> is thus:
> > select [Customers].[Region]
> from [Customers]
> > easy enough...
> > I then select the source table and click transform and I do not see an
> option to replace the data only append, create destination table, and =delete
> rows in destination table.
> > Is this all the functionality of the wizard gives me?
> > Do I need to create my own script?
> > Thanks
> > Ken S.
> >|||Thanks Keith...
both tables are identical tables with the same structure and row count. The
region column in table 1 is the data I want in table 2's region column.
Perhaps your solution of deleting all the rows in table 2 and replacing with
table 1 rows is the way to go.
Thanks
Ken
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:ejEZiujxDHA.2328@.TK2MSFTNGP10.phx.gbl...
I am sure that someone can help, but we will need some additional
information...
Do the tables (table1 and table2) share a common column that you can JOIN on
in order to perform an update?
What other data exists within Table2? Does all the data come from Table1?
Can you simply delete all the rows within Table2 and insert from Table1?
--
Keith
"SMAN" <ksanti@.nycap.rr.com> wrote in message
news:eJUmwnjxDHA.2456@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I am trying to replace data in the "region" column in table 2 with data in
> the "region" column in table 1...in other words 1 is the source and 2 is
the
> destination. I go thru the wizard and then use a query to specify the
data
> to transfer. I go into the query builder and select the column that I
want
> transfered. I don't specify a sort order or criteria. The query
statement
> is thus:
> select [Customers].[Region]
> from [Customers]
> easy enough...
> I then select the source table and click transform and I do not see an
> option to replace the data only append, create destination table, and
delete
> rows in destination table.
> Is this all the functionality of the wizard gives me?
> Do I need to create my own script?
> Thanks
> Ken S.
>|||Since the tables are identical you could also update the data with an =update statement:
UPDATE table2 SET region =3D B.region
FROM table2 A JOIN table1 B ON A.ThePrimaryKeyColumn =3D =B.ThePrimaryKeyColumn
SELECT @.@.rowcount
-- Keith
"SMAN" <ksanti@.nycap.rr.com> wrote in message =news:uKByO2jxDHA.3116@.tk2msftngp13.phx.gbl...
> Thanks Keith...
> > both tables are identical tables with the same structure and row =count. The
> region column in table 1 is the data I want in table 2's region =column.
> Perhaps your solution of deleting all the rows in table 2 and =replacing with
> table 1 rows is the way to go.
> > Thanks
> > Ken
> > > "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:ejEZiujxDHA.2328@.TK2MSFTNGP10.phx.gbl...
> I am sure that someone can help, but we will need some additional
> information...
> > Do the tables (table1 and table2) share a common column that you can =JOIN on
> in order to perform an update?
> > What other data exists within Table2? Does all the data come from =Table1?
> Can you simply delete all the rows within Table2 and insert from =Table1?
> > --
> Keith
> > > "SMAN" <ksanti@.nycap.rr.com> wrote in message
> news:eJUmwnjxDHA.2456@.TK2MSFTNGP12.phx.gbl...
> > Hello,
> >
> > I am trying to replace data in the "region" column in table 2 with =data in
> > the "region" column in table 1...in other words 1 is the source and =2 is
> the
> > destination. I go thru the wizard and then use a query to specify =the
> data
> > to transfer. I go into the query builder and select the column that =I
> want
> > transfered. I don't specify a sort order or criteria. The query
> statement
> > is thus:
> >
> > select [Customers].[Region]
> > from [Customers]
> >
> > easy enough...
> >
> > I then select the source table and click transform and I do not see =an
> > option to replace the data only append, create destination table, =and
> delete
> > rows in destination table.
> >
> > Is this all the functionality of the wizard gives me?
> >
> > Do I need to create my own script?
> >
> > Thanks
> >
> > Ken S.
> >
> >
> >
Monday, February 13, 2012
A little MDX problem
Hi,
I need some support for a MDX problem.
We have a cube with a measure, are region (with hierachy, but this doesn't matter at all) and two time dimensions (with the usual hierachies). The business problem is now:
You select a date (on any level) from Time1. If you would place the region on rows and Time2 on colums (the days) with nonempty enabled, you would get a number of members back (about 1000 numbers in Time2 per day in Time1). I don't what to see that 1000 numbers, I need a measure which is calulated like this:
- Sort these numbers by value
- Find the number which is as position 99%, so i.e. if you have 1000 numbers (but this number can change), you need the 990th member. So you have to count the members, multiply by .99 and you have the ordinal of the member you need
So: how to do this in MDX? I'm quite struggeling around with counts and sorts, the 1000 are returned quite quickly but when I start to sort them it's getting veeeerrrrryyyy slow...
I would be very happy if someone can help me with that...
Hi Thomas,
There are some problem parameters that I'm not sure of, so I'll assume that:
- Ordering is along a pre-determined level of a hierarchy (not based on query axes)
- Ordering is by a pre-determined measure (again, not dynamically determined)
- Desired position is 99th percentile ascending (ie. 1st percentile descending)
Based on these assumptions, here is an Adventure Works query which returns the [Date] name, value and ordinal of the 99th percentile [Order Count], by Product Category on rows:
>>
With
Member [Measures].[Orders99thMember] as
MemberToStr(Tail(TopCount(NonEmpty([Date].[Calendar].[Date].Members,
{[Measures].[Order Quantity]}) as DS,
Int(DS.Count/100)+1, [Measures].[Order Quantity])).Item(0).Item(0))
Member [Measures].[Orders99thName] as
StrToMember([Measures].[Orders99thMember]).Name
Member [Measures].[Orders99thValue] as
(StrToMember([Measures].[Orders99thMember]),
[Measures].[Order Quantity])
Member [Measures].[Orders99thOrdinal] as
CInt(99 * (NonEmpty([Date].[Calendar].[Date].Members,
{[Measures].[Order Quantity]}).Count)/ 100)
select {[Measures].[Order Quantity],
[Measures].[Orders99thName], [Measures].[Orders99thValue],
[Measures].[Orders99thOrdinal]} on 0,
NonEmpty([Product].[Product Categories].[Category].Members,
{[Measures].[Order Quantity]}) on 1
from [Adventure Works]
Order Qty Orders99thName Orders99thValue Orders99thOrdinal
Accessories 61,931 November 1, 2003 1663 417
Bikes 90,220 February 1, 2003 2654 1082
Clothing 73,598 July 1, 2003 3200 417
Components 49,027 September 1, 2003 4365 38
>>
|||Deepak,
thanks for you excellent support... But one question: Where do you do the "sort" by order quantity? I do only see that you return the 99th procentile of the set... I don't think your assuptions will help since you can't define that a set is always sorted by a measure... I understand your second assumption that you mean that it's always the order quatity and not sometimes the quantity and sometimes the amount...
Thanks,
|||Sorry, I guess the topcount does the job... Thanks, it's not very fast but it does the job...
Subscribe to:
Posts (Atom)