daily refresh of data

Hi
I have around 7 tables that get new data or updates in the production envt
and I would like to insert/update these tables on a daily basis in the
testing environment. Can you suggest a script to do this.
Thanks
Bob
=?Utf-8?B?Qm9i?= <Bob@.discussions.microsoft.com> wrote in
news:08545F94-6089-4A25-B925-51F321E0D4B2@.microsoft.com:

> I have around 7 tables that get new data or updates in the production
> envt and I would like to insert/update these tables on a daily basis
> in the testing environment. Can you suggest a script to do this.
To me, this looks like a task for SSIS, eh.. sorry, it's called DTS in the
"old" SQL Server 2000
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging
|||Bob
--New data
INSERT INTO TableA (<column lists>) SELECT <column lists> FROM TableB WHERE
NOT EXISTS (SELECT * FROM TableA WHERE TableA.PK=TableB.PK)
--Updated data
UPDATE TableA SET col=(SELECT col FROM TableB WHERE TableA.PK=TableB.PK AND
TableA.col<>Table.B.col) WHERE EXISTS (SELECT * FROM TableB WHERE
TableA.PK=TableB.PK AND TableA.col<>Table.B.col)
"Bob" <Bob@.discussions.microsoft.com> wrote in message
news:08545F94-6089-4A25-B925-51F321E0D4B2@.microsoft.com...
> Hi
> I have around 7 tables that get new data or updates in the production envt
> and I would like to insert/update these tables on a daily basis in the
> testing environment. Can you suggest a script to do this.
> Thanks
> Bob
|||Thank you for the reply, but the problem with the inserts and updates is that
each attribute in my table has referential integrity constraints and there
are 7-8 such constraints on the table. What strategy should I use to solve
this problem.
"Ole Kristian Bang?s" wrote:

> =?Utf-8?B?Qm9i?= <Bob@.discussions.microsoft.com> wrote in
> news:08545F94-6089-4A25-B925-51F321E0D4B2@.microsoft.com:
>
> To me, this looks like a task for SSIS, eh.. sorry, it's called DTS in the
> "old" SQL Server 2000
> --
> Ole Kristian Bang?s
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging
>

daily refresh of data

Hi
I have around 7 tables that get new data or updates in the production envt
and I would like to insert/update these tables on a daily basis in the
testing environment. Can you suggest a script to do this.
Thanks
Bob=?Utf-8?B?Qm9i?= <Bob@.discussions.microsoft.com> wrote in
news:08545F94-6089-4A25-B925-51F321E0D4B2@.microsoft.com:
> I have around 7 tables that get new data or updates in the production
> envt and I would like to insert/update these tables on a daily basis
> in the testing environment. Can you suggest a script to do this.
To me, this looks like a task for SSIS, eh.. sorry, it's called DTS in the
"old" SQL Server 2000 :)
--
Ole Kristian Bangås
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||Bob
--New data
INSERT INTO TableA (<column lists>) SELECT <column lists> FROM TableB WHERE
NOT EXISTS (SELECT * FROM TableA WHERE TableA.PK=TableB.PK)
--Updated data
UPDATE TableA SET col=(SELECT col FROM TableB WHERE TableA.PK=TableB.PK AND
TableA.col<>Table.B.col) WHERE EXISTS (SELECT * FROM TableB WHERE
TableA.PK=TableB.PK AND TableA.col<>Table.B.col)
"Bob" <Bob@.discussions.microsoft.com> wrote in message
news:08545F94-6089-4A25-B925-51F321E0D4B2@.microsoft.com...
> Hi
> I have around 7 tables that get new data or updates in the production envt
> and I would like to insert/update these tables on a daily basis in the
> testing environment. Can you suggest a script to do this.
> Thanks
> Bob|||Thank you for the reply, but the problem with the inserts and updates is that
each attribute in my table has referential integrity constraints and there
are 7-8 such constraints on the table. What strategy should I use to solve
this problem.
"Ole Kristian Bangås" wrote:
> =?Utf-8?B?Qm9i?= <Bob@.discussions.microsoft.com> wrote in
> news:08545F94-6089-4A25-B925-51F321E0D4B2@.microsoft.com:
> > I have around 7 tables that get new data or updates in the production
> > envt and I would like to insert/update these tables on a daily basis
> > in the testing environment. Can you suggest a script to do this.
> To me, this looks like a task for SSIS, eh.. sorry, it's called DTS in the
> "old" SQL Server 2000 :)
> --
> Ole Kristian Bangås
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging
>

daily refresh of data

Hi
I have around 7 tables that get new data or updates in the production envt
and I would like to insert/update these tables on a daily basis in the
testing environment. Can you suggest a script to do this.
Thanks
Bobexamnotes <Bob@.discussions.microsoft.com> wrote in
news:08545F94-6089-4A25-B925-51F321E0D4B2@.microsoft.com:

> I have around 7 tables that get new data or updates in the production
> envt and I would like to insert/update these tables on a daily basis
> in the testing environment. Can you suggest a script to do this.
To me, this looks like a task for SSIS, eh.. sorry, it's called DTS in the
"old" SQL Server 2000
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||Bob
--New data
INSERT INTO TableA (<column lists> ) SELECT <column lists> FROM TableB WHERE
NOT EXISTS (SELECT * FROM TableA WHERE TableA.PK=TableB.PK)
--Updated data
UPDATE TableA SET col=(SELECT col FROM TableB WHERE TableA.PK=TableB.PK AND
TableA.col<>Table.B.col) WHERE EXISTS (SELECT * FROM TableB WHERE
TableA.PK=TableB.PK AND TableA.col<>Table.B.col)
"Bob" <Bob@.discussions.microsoft.com> wrote in message
news:08545F94-6089-4A25-B925-51F321E0D4B2@.microsoft.com...
> Hi
> I have around 7 tables that get new data or updates in the production envt
> and I would like to insert/update these tables on a daily basis in the
> testing environment. Can you suggest a script to do this.
> Thanks
> Bob|||Thank you for the reply, but the problem with the inserts and updates is tha
t
each attribute in my table has referential integrity constraints and there
are 7-8 such constraints on the table. What strategy should I use to solve
this problem.
"Ole Kristian Bang?s" wrote:

> examnotes <Bob@.discussions.microsoft.com> wrote in
> news:08545F94-6089-4A25-B925-51F321E0D4B2@.microsoft.com:
>
> To me, this looks like a task for SSIS, eh.. sorry, it's called DTS in the
> "old" SQL Server 2000
> --
> Ole Kristian Bang?s
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging
>

Daily Maintenanec Plan

Good Day,
I am having a problem with an SQL server 2005 maintenance plan that updates
Statistics.
I keep receiving the following error:-
Executing the query "UPDATE STATISTICS [dbo].[STATSDATA8_27_2007]
WITH FULLSCAN
" failed with the following error: "Table 'STATSDATA8_27_2007' does not
exist.". Possible failure reasons: Problems with the query, "ResultSet"
property not set correctly, parameters not set correctly, or connection not
established correctly.
I am not sure how to resolve this and it seems to stop statistical updating.
Could anyone offer any help?
Kind Regards,
Paul.It seems that the table you want to automatically update its statistics is
not exist. You or someone else may be deleted it?
Be sure it still exists first.
--
Ekrem Önsoy
"Paul Johnson" <paul.johnson@.gaapweb.com> wrote in message
news:ufUwvys7HHA.1188@.TK2MSFTNGP04.phx.gbl...
> Good Day,
> I am having a problem with an SQL server 2005 maintenance plan that
> updates Statistics.
> I keep receiving the following error:-
> Executing the query "UPDATE STATISTICS [dbo].[STATSDATA8_27_2007]
> WITH FULLSCAN
> " failed with the following error: "Table 'STATSDATA8_27_2007' does not
> exist.". Possible failure reasons: Problems with the query, "ResultSet"
> property not set correctly, parameters not set correctly, or connection
> not established correctly.
> I am not sure how to resolve this and it seems to stop statistical
> updating. Could anyone offer any help?
> Kind Regards,
> Paul.
>|||Thank you for your reply.
I should have mentioned that the plan is updating statistics for all user
databases and so there is no database or table specifically specified. If
this table is not present, why would it attempt to update the statistics?
Thanks for any advice.
Regards,
Paul.
"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:9BE37C2E-55DB-4EA3-ACAC-702118FF60B8@.microsoft.com...
> It seems that the table you want to automatically update its statistics is
> not exist. You or someone else may be deleted it?
> Be sure it still exists first.
> --
> Ekrem Önsoy
>
> "Paul Johnson" <paul.johnson@.gaapweb.com> wrote in message
> news:ufUwvys7HHA.1188@.TK2MSFTNGP04.phx.gbl...
>> Good Day,
>> I am having a problem with an SQL server 2005 maintenance plan that
>> updates Statistics.
>> I keep receiving the following error:-
>> Executing the query "UPDATE STATISTICS [dbo].[STATSDATA8_27_2007]
>> WITH FULLSCAN
>> " failed with the following error: "Table 'STATSDATA8_27_2007' does not
>> exist.". Possible failure reasons: Problems with the query, "ResultSet"
>> property not set correctly, parameters not set correctly, or connection
>> not established correctly.
>> I am not sure how to resolve this and it seems to stop statistical
>> updating. Could anyone offer any help?
>> Kind Regards,
>> Paul.
>>
>|||Well, I did not understand exactly what you meant by saying "plan updates
statistics for all user databases and there is no specipic tables nor
databases".
However, when you create a maintenance plan to update statistics
automatically, you select databases and tables to be updated. So, there you
have already selected those databases\tables... And then, it appears that
you drop one and the job arises an error because it can not find the object
which you chose to be updated.
--
Ekrem Önsoy
"Paul Johnson" <paul.johnson@.gaapweb.com> wrote in message
news:O1D49cv7HHA.980@.TK2MSFTNGP06.phx.gbl...
> Thank you for your reply.
> I should have mentioned that the plan is updating statistics for all user
> databases and so there is no database or table specifically specified. If
> this table is not present, why would it attempt to update the statistics?
> Thanks for any advice.
> Regards,
> Paul.
>
> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
> news:9BE37C2E-55DB-4EA3-ACAC-702118FF60B8@.microsoft.com...
>> It seems that the table you want to automatically update its statistics
>> is not exist. You or someone else may be deleted it?
>> Be sure it still exists first.
>> --
>> Ekrem Önsoy
>>
>> "Paul Johnson" <paul.johnson@.gaapweb.com> wrote in message
>> news:ufUwvys7HHA.1188@.TK2MSFTNGP04.phx.gbl...
>> Good Day,
>> I am having a problem with an SQL server 2005 maintenance plan that
>> updates Statistics.
>> I keep receiving the following error:-
>> Executing the query "UPDATE STATISTICS [dbo].[STATSDATA8_27_2007]
>> WITH FULLSCAN
>> " failed with the following error: "Table 'STATSDATA8_27_2007' does not
>> exist.". Possible failure reasons: Problems with the query, "ResultSet"
>> property not set correctly, parameters not set correctly, or connection
>> not established correctly.
>> I am not sure how to resolve this and it seems to stop statistical
>> updating. Could anyone offer any help?
>> Kind Regards,
>> Paul.
>>
>|||Thanks for your reply.
I am assuming that if I do not actually specify the database by selecting
"All databases" it would update the stats for all that are available. So if
I add a new database, I do not need to edit the plan, it would just update
stats for all databases.
Regards,
Paul.
"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:4BFEB99B-50C0-4247-9E10-52BD5A6A4458@.microsoft.com...
> Well, I did not understand exactly what you meant by saying "plan updates
> statistics for all user databases and there is no specipic tables nor
> databases".
> However, when you create a maintenance plan to update statistics
> automatically, you select databases and tables to be updated. So, there
> you have already selected those databases\tables... And then, it appears
> that you drop one and the job arises an error because it can not find the
> object which you chose to be updated.
> --
> Ekrem Önsoy
>
> "Paul Johnson" <paul.johnson@.gaapweb.com> wrote in message
> news:O1D49cv7HHA.980@.TK2MSFTNGP06.phx.gbl...
>> Thank you for your reply.
>> I should have mentioned that the plan is updating statistics for all user
>> databases and so there is no database or table specifically specified. If
>> this table is not present, why would it attempt to update the statistics?
>> Thanks for any advice.
>> Regards,
>> Paul.
>>
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:9BE37C2E-55DB-4EA3-ACAC-702118FF60B8@.microsoft.com...
>> It seems that the table you want to automatically update its statistics
>> is not exist. You or someone else may be deleted it?
>> Be sure it still exists first.
>> --
>> Ekrem Önsoy
>>
>> "Paul Johnson" <paul.johnson@.gaapweb.com> wrote in message
>> news:ufUwvys7HHA.1188@.TK2MSFTNGP04.phx.gbl...
>> Good Day,
>> I am having a problem with an SQL server 2005 maintenance plan that
>> updates Statistics.
>> I keep receiving the following error:-
>> Executing the query "UPDATE STATISTICS [dbo].[STATSDATA8_27_2007]
>> WITH FULLSCAN
>> " failed with the following error: "Table 'STATSDATA8_27_2007' does not
>> exist.". Possible failure reasons: Problems with the query, "ResultSet"
>> property not set correctly, parameters not set correctly, or connection
>> not established correctly.
>> I am not sure how to resolve this and it seems to stop statistical
>> updating. Could anyone offer any help?
>> Kind Regards,
>> Paul.
>>
>>
>|||Can you try deleting the old one and creating a new one?
--
Ekrem Önsoy
"Paul Johnson" <paul.johnson@.gaapweb.com> wrote in message
news:OBh8V0w7HHA.1188@.TK2MSFTNGP04.phx.gbl...
> Thanks for your reply.
> I am assuming that if I do not actually specify the database by selecting
> "All databases" it would update the stats for all that are available. So
> if I add a new database, I do not need to edit the plan, it would just
> update stats for all databases.
> Regards,
> Paul.
> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
> news:4BFEB99B-50C0-4247-9E10-52BD5A6A4458@.microsoft.com...
>> Well, I did not understand exactly what you meant by saying "plan updates
>> statistics for all user databases and there is no specipic tables nor
>> databases".
>> However, when you create a maintenance plan to update statistics
>> automatically, you select databases and tables to be updated. So, there
>> you have already selected those databases\tables... And then, it appears
>> that you drop one and the job arises an error because it can not find the
>> object which you chose to be updated.
>> --
>> Ekrem Önsoy
>>
>> "Paul Johnson" <paul.johnson@.gaapweb.com> wrote in message
>> news:O1D49cv7HHA.980@.TK2MSFTNGP06.phx.gbl...
>> Thank you for your reply.
>> I should have mentioned that the plan is updating statistics for all
>> user databases and so there is no database or table specifically
>> specified. If this table is not present, why would it attempt to update
>> the statistics?
>> Thanks for any advice.
>> Regards,
>> Paul.
>>
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:9BE37C2E-55DB-4EA3-ACAC-702118FF60B8@.microsoft.com...
>> It seems that the table you want to automatically update its statistics
>> is not exist. You or someone else may be deleted it?
>> Be sure it still exists first.
>> --
>> Ekrem Önsoy
>>
>> "Paul Johnson" <paul.johnson@.gaapweb.com> wrote in message
>> news:ufUwvys7HHA.1188@.TK2MSFTNGP04.phx.gbl...
>> Good Day,
>> I am having a problem with an SQL server 2005 maintenance plan that
>> updates Statistics.
>> I keep receiving the following error:-
>> Executing the query "UPDATE STATISTICS [dbo].[STATSDATA8_27_2007]
>> WITH FULLSCAN
>> " failed with the following error: "Table 'STATSDATA8_27_2007' does
>> not exist.". Possible failure reasons: Problems with the query,
>> "ResultSet" property not set correctly, parameters not set correctly,
>> or connection not established correctly.
>> I am not sure how to resolve this and it seems to stop statistical
>> updating. Could anyone offer any help?
>> Kind Regards,
>> Paul.
>>
>>
>>
>|||Good Day,
I have recreated the maintenance plan but I still get a similar error:-
Failed:(-1073548784) Executing the query "UPDATE STATISTICS
[dbo].[STATSDATA8_30_2007] WITH FULLSCAN " failed with the following error:
"Table 'STATSDATA8_30_2007' does not exist.". Possible failure reasons:
Problems with the query, "ResultSet" property not set correctly, parameters
not set correctly, or connection not established correctly.
I am really not sure what is going on. I have not picked databases
individually, I just chose the all setting.
Paul
"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:19F9B743-6704-4A02-91CA-B5C2EE1ABBAF@.microsoft.com...
> Can you try deleting the old one and creating a new one?
> --
> Ekrem Önsoy
>
> "Paul Johnson" <paul.johnson@.gaapweb.com> wrote in message
> news:OBh8V0w7HHA.1188@.TK2MSFTNGP04.phx.gbl...
>> Thanks for your reply.
>> I am assuming that if I do not actually specify the database by selecting
>> "All databases" it would update the stats for all that are available. So
>> if I add a new database, I do not need to edit the plan, it would just
>> update stats for all databases.
>> Regards,
>> Paul.
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:4BFEB99B-50C0-4247-9E10-52BD5A6A4458@.microsoft.com...
>> Well, I did not understand exactly what you meant by saying "plan
>> updates statistics for all user databases and there is no specipic
>> tables nor databases".
>> However, when you create a maintenance plan to update statistics
>> automatically, you select databases and tables to be updated. So, there
>> you have already selected those databases\tables... And then, it appears
>> that you drop one and the job arises an error because it can not find
>> the object which you chose to be updated.
>> --
>> Ekrem Önsoy
>>
>> "Paul Johnson" <paul.johnson@.gaapweb.com> wrote in message
>> news:O1D49cv7HHA.980@.TK2MSFTNGP06.phx.gbl...
>> Thank you for your reply.
>> I should have mentioned that the plan is updating statistics for all
>> user databases and so there is no database or table specifically
>> specified. If this table is not present, why would it attempt to update
>> the statistics?
>> Thanks for any advice.
>> Regards,
>> Paul.
>>
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:9BE37C2E-55DB-4EA3-ACAC-702118FF60B8@.microsoft.com...
>> It seems that the table you want to automatically update its
>> statistics is not exist. You or someone else may be deleted it?
>> Be sure it still exists first.
>> --
>> Ekrem Önsoy
>>
>> "Paul Johnson" <paul.johnson@.gaapweb.com> wrote in message
>> news:ufUwvys7HHA.1188@.TK2MSFTNGP04.phx.gbl...
>> Good Day,
>> I am having a problem with an SQL server 2005 maintenance plan that
>> updates Statistics.
>> I keep receiving the following error:-
>> Executing the query "UPDATE STATISTICS [dbo].[STATSDATA8_27_2007]
>> WITH FULLSCAN
>> " failed with the following error: "Table 'STATSDATA8_27_2007' does
>> not exist.". Possible failure reasons: Problems with the query,
>> "ResultSet" property not set correctly, parameters not set correctly,
>> or connection not established correctly.
>> I am not sure how to resolve this and it seems to stop statistical
>> updating. Could anyone offer any help?
>> Kind Regards,
>> Paul.
>>
>>
>>
>>
>