Hi,
I have two sql server enterprise servers running sp4 on windows server2003
sp1
I have a DTS package that does some various processing on one machine, then
at the end of the package. I copy tables to the other sql server box.
In one of the tables being copied, I have a bigint data type. The table
copies and shows that the bigint datatype is still part of the table
definition, but the values in the bigint column are negatives and positives.
Does anyone have any ideas as to what might be causing this?
thanks in advance,
Troy
Values in a bigint column can be negative as well as positive, but I assume
that you have only positive values on one side and end up with negative and
positive values on the other? If that's the case it looks to me like your
bigints are accidentally treated as ints somewhere along the way, at a
binary level.
Jacco Schalkwijk
SQL Server MVP
"Troy Sherrill" <tsherrill@.nc.rr.com> wrote in message
news:ekzUk8kfFHA.460@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have two sql server enterprise servers running sp4 on windows server2003
> sp1
> I have a DTS package that does some various processing on one machine,
> then
> at the end of the package. I copy tables to the other sql server box.
> In one of the tables being copied, I have a bigint data type. The table
> copies and shows that the bigint datatype is still part of the table
> definition, but the values in the bigint column are negatives and
> positives.
> Does anyone have any ideas as to what might be causing this?
> thanks in advance,
> Troy
>
|||What provider are you using? It may not understand a BigInt datatype.
Andrew J. Kelly SQL MVP
"Troy Sherrill" <tsherrill@.nc.rr.com> wrote in message
news:ekzUk8kfFHA.460@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have two sql server enterprise servers running sp4 on windows server2003
> sp1
> I have a DTS package that does some various processing on one machine,
> then
> at the end of the package. I copy tables to the other sql server box.
> In one of the tables being copied, I have a bigint data type. The table
> copies and shows that the bigint datatype is still part of the table
> definition, but the values in the bigint column are negatives and
> positives.
> Does anyone have any ideas as to what might be causing this?
> thanks in advance,
> Troy
>
Showing posts with label dts. Show all posts
Showing posts with label dts. Show all posts
Thursday, March 22, 2012
bigint problem
Hi,
I have two sql server enterprise servers running sp4 on windows server2003
sp1
I have a DTS package that does some various processing on one machine, then
at the end of the package. I copy tables to the other sql server box.
In one of the tables being copied, I have a bigint data type. The table
copies and shows that the bigint datatype is still part of the table
definition, but the values in the bigint column are negatives and positives.
Does anyone have any ideas as to what might be causing this?
thanks in advance,
TroyValues in a bigint column can be negative as well as positive, but I assume
that you have only positive values on one side and end up with negative and
positive values on the other? If that's the case it looks to me like your
bigints are accidentally treated as ints somewhere along the way, at a
binary level.
--
Jacco Schalkwijk
SQL Server MVP
"Troy Sherrill" <tsherrill@.nc.rr.com> wrote in message
news:ekzUk8kfFHA.460@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have two sql server enterprise servers running sp4 on windows server2003
> sp1
> I have a DTS package that does some various processing on one machine,
> then
> at the end of the package. I copy tables to the other sql server box.
> In one of the tables being copied, I have a bigint data type. The table
> copies and shows that the bigint datatype is still part of the table
> definition, but the values in the bigint column are negatives and
> positives.
> Does anyone have any ideas as to what might be causing this?
> thanks in advance,
> Troy
>|||What provider are you using? It may not understand a BigInt datatype.
--
Andrew J. Kelly SQL MVP
"Troy Sherrill" <tsherrill@.nc.rr.com> wrote in message
news:ekzUk8kfFHA.460@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have two sql server enterprise servers running sp4 on windows server2003
> sp1
> I have a DTS package that does some various processing on one machine,
> then
> at the end of the package. I copy tables to the other sql server box.
> In one of the tables being copied, I have a bigint data type. The table
> copies and shows that the bigint datatype is still part of the table
> definition, but the values in the bigint column are negatives and
> positives.
> Does anyone have any ideas as to what might be causing this?
> thanks in advance,
> Troy
>sql
I have two sql server enterprise servers running sp4 on windows server2003
sp1
I have a DTS package that does some various processing on one machine, then
at the end of the package. I copy tables to the other sql server box.
In one of the tables being copied, I have a bigint data type. The table
copies and shows that the bigint datatype is still part of the table
definition, but the values in the bigint column are negatives and positives.
Does anyone have any ideas as to what might be causing this?
thanks in advance,
TroyValues in a bigint column can be negative as well as positive, but I assume
that you have only positive values on one side and end up with negative and
positive values on the other? If that's the case it looks to me like your
bigints are accidentally treated as ints somewhere along the way, at a
binary level.
--
Jacco Schalkwijk
SQL Server MVP
"Troy Sherrill" <tsherrill@.nc.rr.com> wrote in message
news:ekzUk8kfFHA.460@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have two sql server enterprise servers running sp4 on windows server2003
> sp1
> I have a DTS package that does some various processing on one machine,
> then
> at the end of the package. I copy tables to the other sql server box.
> In one of the tables being copied, I have a bigint data type. The table
> copies and shows that the bigint datatype is still part of the table
> definition, but the values in the bigint column are negatives and
> positives.
> Does anyone have any ideas as to what might be causing this?
> thanks in advance,
> Troy
>|||What provider are you using? It may not understand a BigInt datatype.
--
Andrew J. Kelly SQL MVP
"Troy Sherrill" <tsherrill@.nc.rr.com> wrote in message
news:ekzUk8kfFHA.460@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have two sql server enterprise servers running sp4 on windows server2003
> sp1
> I have a DTS package that does some various processing on one machine,
> then
> at the end of the package. I copy tables to the other sql server box.
> In one of the tables being copied, I have a bigint data type. The table
> copies and shows that the bigint datatype is still part of the table
> definition, but the values in the bigint column are negatives and
> positives.
> Does anyone have any ideas as to what might be causing this?
> thanks in advance,
> Troy
>sql
bigint problem
Hi,
I have two sql server enterprise servers running sp4 on windows server2003
sp1
I have a DTS package that does some various processing on one machine, then
at the end of the package. I copy tables to the other sql server box.
In one of the tables being copied, I have a bigint data type. The table
copies and shows that the bigint datatype is still part of the table
definition, but the values in the bigint column are negatives and positives.
Does anyone have any ideas as to what might be causing this?
thanks in advance,
TroyValues in a bigint column can be negative as well as positive, but I assume
that you have only positive values on one side and end up with negative and
positive values on the other? If that's the case it looks to me like your
bigints are accidentally treated as ints somewhere along the way, at a
binary level.
Jacco Schalkwijk
SQL Server MVP
"Troy Sherrill" <tsherrill@.nc.rr.com> wrote in message
news:ekzUk8kfFHA.460@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have two sql server enterprise servers running sp4 on windows server2003
> sp1
> I have a DTS package that does some various processing on one machine,
> then
> at the end of the package. I copy tables to the other sql server box.
> In one of the tables being copied, I have a bigint data type. The table
> copies and shows that the bigint datatype is still part of the table
> definition, but the values in the bigint column are negatives and
> positives.
> Does anyone have any ideas as to what might be causing this?
> thanks in advance,
> Troy
>|||What provider are you using? It may not understand a BigInt datatype.
Andrew J. Kelly SQL MVP
"Troy Sherrill" <tsherrill@.nc.rr.com> wrote in message
news:ekzUk8kfFHA.460@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have two sql server enterprise servers running sp4 on windows server2003
> sp1
> I have a DTS package that does some various processing on one machine,
> then
> at the end of the package. I copy tables to the other sql server box.
> In one of the tables being copied, I have a bigint data type. The table
> copies and shows that the bigint datatype is still part of the table
> definition, but the values in the bigint column are negatives and
> positives.
> Does anyone have any ideas as to what might be causing this?
> thanks in advance,
> Troy
>
I have two sql server enterprise servers running sp4 on windows server2003
sp1
I have a DTS package that does some various processing on one machine, then
at the end of the package. I copy tables to the other sql server box.
In one of the tables being copied, I have a bigint data type. The table
copies and shows that the bigint datatype is still part of the table
definition, but the values in the bigint column are negatives and positives.
Does anyone have any ideas as to what might be causing this?
thanks in advance,
TroyValues in a bigint column can be negative as well as positive, but I assume
that you have only positive values on one side and end up with negative and
positive values on the other? If that's the case it looks to me like your
bigints are accidentally treated as ints somewhere along the way, at a
binary level.
Jacco Schalkwijk
SQL Server MVP
"Troy Sherrill" <tsherrill@.nc.rr.com> wrote in message
news:ekzUk8kfFHA.460@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have two sql server enterprise servers running sp4 on windows server2003
> sp1
> I have a DTS package that does some various processing on one machine,
> then
> at the end of the package. I copy tables to the other sql server box.
> In one of the tables being copied, I have a bigint data type. The table
> copies and shows that the bigint datatype is still part of the table
> definition, but the values in the bigint column are negatives and
> positives.
> Does anyone have any ideas as to what might be causing this?
> thanks in advance,
> Troy
>|||What provider are you using? It may not understand a BigInt datatype.
Andrew J. Kelly SQL MVP
"Troy Sherrill" <tsherrill@.nc.rr.com> wrote in message
news:ekzUk8kfFHA.460@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have two sql server enterprise servers running sp4 on windows server2003
> sp1
> I have a DTS package that does some various processing on one machine,
> then
> at the end of the package. I copy tables to the other sql server box.
> In one of the tables being copied, I have a bigint data type. The table
> copies and shows that the bigint datatype is still part of the table
> definition, but the values in the bigint column are negatives and
> positives.
> Does anyone have any ideas as to what might be causing this?
> thanks in advance,
> Troy
>
Thursday, March 8, 2012
BI Accelerator: problem with current day setting
Hi, All!
I'm experimenting with MSSABI and look this problem - after running
SetCurentDay.dts package every measure values mutiplied by 6.
Here is my steps:
1. Create Analytical, SM and staging databeses with Analytics builder
utility for SMA template (unmodified).
2. Manualy run Master Import DTS package for loading sample data in Satging
DB.
3. Manualy run Master Update DTS package for loading sample data in Subject
Matter DB (with gbManualProcessDim and gbManualProcessFact set to -1 for
automatic processing disable).
4. Manualy run processing of Sales cube in Ananlytical DB and browse data
after its finishing.
Measure Actual Invoice Count is 2843 for 2001 year (Time.Standard dim -
in rows), calculated members in Time.Standard dim - empty due to current
day is not set.
5. Manualy run SetCurrentDay.dts package with gsCurrentDay = '10.05.2001'
(October 5 2001)
6. Again manualy run processing of Sales cube in Ananlytical DB and browse
data after its finishing.
Measure Actual Invoice Count is 17058 for 2001 year (Time.Standard dim -
in rows), calculated members in
Time.Standard dim have some values.
This behaviour fist time was occured in my custom Analytical app.
constructed using MSSABI, the standard template (Sales and marketing)
behaviour is the same.
Where is the problem - is MSSABI or I'm doing something wrong?Hi
I have already seen this problem a while ago. Checkout
http://www.dbforums.com/showthread.php?t=895520
I don't really understand what the guy that replied means by "Setting the al
l tu current".
What I did at the time (and of course that is not the solution) was to remov
e the Weel.Standard dimension from the cube.
Did you found a solution.?
Checkout this:
http://support.microsoft.com/defaul...b;en-us;834285.
Data in cubes become correct after delete ALL date dims exept only one with
custom rollups.
"rsada" <rsada.1fzsd0@.mail.webservertalk.com> /
: news:rsada.1fzsd0@.mail.webservertalk.com...
> Hi
> I have already seen this problem a while ago. Checkout
> http://www.webservertalk.com/showthread.php?t=895520
> I don't really understand what the guy that replied means by "Setting
> the all tu current".
> What I did at the time (and of course that is not the solution) was to
> remove the Weel.Standard dimension from the cube.
> Did you found a solution.?
>
>
> Eugene Frolov wrote:
>
> --
> rsada
> ---
> Posted via http://www.webservertalk.com
> ---
> View this thread: http://www.webservertalk.com/message447789.html
>
I'm experimenting with MSSABI and look this problem - after running
SetCurentDay.dts package every measure values mutiplied by 6.
Here is my steps:
1. Create Analytical, SM and staging databeses with Analytics builder
utility for SMA template (unmodified).
2. Manualy run Master Import DTS package for loading sample data in Satging
DB.
3. Manualy run Master Update DTS package for loading sample data in Subject
Matter DB (with gbManualProcessDim and gbManualProcessFact set to -1 for
automatic processing disable).
4. Manualy run processing of Sales cube in Ananlytical DB and browse data
after its finishing.
Measure Actual Invoice Count is 2843 for 2001 year (Time.Standard dim -
in rows), calculated members in Time.Standard dim - empty due to current
day is not set.
5. Manualy run SetCurrentDay.dts package with gsCurrentDay = '10.05.2001'
(October 5 2001)
6. Again manualy run processing of Sales cube in Ananlytical DB and browse
data after its finishing.
Measure Actual Invoice Count is 17058 for 2001 year (Time.Standard dim -
in rows), calculated members in
Time.Standard dim have some values.
This behaviour fist time was occured in my custom Analytical app.
constructed using MSSABI, the standard template (Sales and marketing)
behaviour is the same.
Where is the problem - is MSSABI or I'm doing something wrong?Hi
I have already seen this problem a while ago. Checkout
http://www.dbforums.com/showthread.php?t=895520
I don't really understand what the guy that replied means by "Setting the al
l tu current".
What I did at the time (and of course that is not the solution) was to remov
e the Weel.Standard dimension from the cube.
Did you found a solution.?
quote:|||Hi!
Originally posted by Eugene Frolov
Hi, All!
I'm experimenting with MSSABI and look this problem - after running
SetCurentDay.dts package every measure values mutiplied by 6.
Here is my steps:
1. Create Analytical, SM and staging databeses with Analytics builder
utility for SMA template (unmodified).
2. Manualy run Master Import DTS package for loading sample data in Satging
DB.
3. Manualy run Master Update DTS package for loading sample data in Subject
Matter DB (with gbManualProcessDim and gbManualProcessFact set to -1 for
automatic processing disable).
4. Manualy run processing of Sales cube in Ananlytical DB and browse data
after its finishing.
Measure Actual Invoice Count is 2843 for 2001 year (Time.Standard dim -
in rows), calculated members in Time.Standard dim - empty due to current
day is not set.
5. Manualy run SetCurrentDay.dts package with gsCurrentDay = '10.05.2001'
(October 5 2001)
6. Again manualy run processing of Sales cube in Ananlytical DB and browse
data after its finishing.
Measure Actual Invoice Count is 17058 for 2001 year (Time.Standard dim -
in rows), calculated members in
Time.Standard dim have some values.
This behaviour fist time was occured in my custom Analytical app.
constructed using MSSABI, the standard template (Sales and marketing)
behaviour is the same.
Where is the problem - is MSSABI or I'm doing something wrong?
Checkout this:
http://support.microsoft.com/defaul...b;en-us;834285.
Data in cubes become correct after delete ALL date dims exept only one with
custom rollups.
"rsada" <rsada.1fzsd0@.mail.webservertalk.com> /
: news:rsada.1fzsd0@.mail.webservertalk.com...
> Hi
> I have already seen this problem a while ago. Checkout
> http://www.webservertalk.com/showthread.php?t=895520
> I don't really understand what the guy that replied means by "Setting
> the all tu current".
> What I did at the time (and of course that is not the solution) was to
> remove the Weel.Standard dimension from the cube.
> Did you found a solution.?
>
>
> Eugene Frolov wrote:
>
> --
> rsada
> ---
> Posted via http://www.webservertalk.com
> ---
> View this thread: http://www.webservertalk.com/message447789.html
>
BI Accelerator: problem with current day setting
Hi, All!
I'm experimenting with MSSABI and look this problem - after running
SetCurentDay.dts package every measure values mutiplied by 6.
Here is my steps:
1. Create Analytical, SM and staging databeses with Analytics builder
utility for SMA template (unmodified).
2. Manualy run Master Import DTS package for loading sample data in Satging
DB.
3. Manualy run Master Update DTS package for loading sample data in Subject
Matter DB (with gbManualProcessDim and gbManualProcessFact set to -1 for
automatic processing disable).
4. Manualy run processing of Sales cube in Ananlytical DB and browse data
after its finishing.
Measure Actual Invoice Count is 2843 for 2001 year (Time.Standard dim -
in rows), calculated members in Time.Standard dim - empty due to current
day is not set.
5. Manualy run SetCurrentDay.dts package with gsCurrentDay = '10.05.2001'
(October 5 2001)
6. Again manualy run processing of Sales cube in Ananlytical DB and browse
data after its finishing.
Measure Actual Invoice Count is 17058 for 2001 year (Time.Standard dim -
in rows), calculated members in
Time.Standard dim have some values.
This behaviour fist time was occured in my custom Analytical app.
constructed using MSSABI, the standard template (Sales and marketing)
behaviour is the same.
Where is the problem - is MSSABI or I'm doing something wrong?
Hi
I have already seen this problem a while ago. Checkout
http://www.webservertalk.com/showthread.php?t=895520
I don't really understand what the guy that replied means by "Setting
the all tu current".
What I did at the time (and of course that is not the solution) was to
remove the Weel.Standard dimension from the cube.
Did you found a solution.?
Eugene Frolov wrote:
> *Hi, All!
> I'm experimenting with MSSABI and look this problem - after running
> SetCurentDay.dts package every measure values mutiplied by 6.
> Here is my steps:
> 1. Create Analytical, SM and staging databeses with Analytics
> builder
> utility for SMA template (unmodified).
> 2. Manualy run Master Import DTS package for loading sample data in
> Satging
> DB.
> 3. Manualy run Master Update DTS package for loading sample data in
> Subject
> Matter DB (with gbManualProcessDim and gbManualProcessFact set to -1
> for
> automatic processing disable).
> 4. Manualy run processing of Sales cube in Ananlytical DB and browse
> data
> after its finishing.
> Measure Actual Invoice Count is 2843 for 2001 year (Time.Standard dim
> -
> in rows), calculated members in Time.Standard dim - empty due to
> current
> day is not set.
> 5. Manualy run SetCurrentDay.dts package with gsCurrentDay =
> '10.05.2001'
> (October 5 2001)
> 6. Again manualy run processing of Sales cube in Ananlytical DB and
> browse
> data after its finishing.
> Measure Actual Invoice Count is 17058 for 2001 year (Time.Standard
> dim -
> in rows), calculated members in
> Time.Standard dim have some values.
> This behaviour fist time was occured in my custom Analytical app.
> constructed using MSSABI, the standard template (Sales and
> marketing)
> behaviour is the same.
> Where is the problem - is MSSABI or I'm doing something wrong? *
rsada
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message447789.html
|||Hi!
Checkout this:
http://support.microsoft.com/default...;en-us;834285.
Data in cubes become correct after delete ALL date dims exept only one with
custom rollups.
"rsada" <rsada.1fzsd0@.mail.webservertalk.com> /
: news:rsada.1fzsd0@.mail.webservertalk.com...
> Hi
> I have already seen this problem a while ago. Checkout
> http://www.webservertalk.com/showthread.php?t=895520
> I don't really understand what the guy that replied means by "Setting
> the all tu current".
> What I did at the time (and of course that is not the solution) was to
> remove the Weel.Standard dimension from the cube.
> Did you found a solution.?
>
>
> Eugene Frolov wrote:
>
> --
> rsada
> Posted via http://www.webservertalk.com
> View this thread: http://www.webservertalk.com/message447789.html
>
I'm experimenting with MSSABI and look this problem - after running
SetCurentDay.dts package every measure values mutiplied by 6.
Here is my steps:
1. Create Analytical, SM and staging databeses with Analytics builder
utility for SMA template (unmodified).
2. Manualy run Master Import DTS package for loading sample data in Satging
DB.
3. Manualy run Master Update DTS package for loading sample data in Subject
Matter DB (with gbManualProcessDim and gbManualProcessFact set to -1 for
automatic processing disable).
4. Manualy run processing of Sales cube in Ananlytical DB and browse data
after its finishing.
Measure Actual Invoice Count is 2843 for 2001 year (Time.Standard dim -
in rows), calculated members in Time.Standard dim - empty due to current
day is not set.
5. Manualy run SetCurrentDay.dts package with gsCurrentDay = '10.05.2001'
(October 5 2001)
6. Again manualy run processing of Sales cube in Ananlytical DB and browse
data after its finishing.
Measure Actual Invoice Count is 17058 for 2001 year (Time.Standard dim -
in rows), calculated members in
Time.Standard dim have some values.
This behaviour fist time was occured in my custom Analytical app.
constructed using MSSABI, the standard template (Sales and marketing)
behaviour is the same.
Where is the problem - is MSSABI or I'm doing something wrong?
Hi
I have already seen this problem a while ago. Checkout
http://www.webservertalk.com/showthread.php?t=895520
I don't really understand what the guy that replied means by "Setting
the all tu current".
What I did at the time (and of course that is not the solution) was to
remove the Weel.Standard dimension from the cube.
Did you found a solution.?
Eugene Frolov wrote:
> *Hi, All!
> I'm experimenting with MSSABI and look this problem - after running
> SetCurentDay.dts package every measure values mutiplied by 6.
> Here is my steps:
> 1. Create Analytical, SM and staging databeses with Analytics
> builder
> utility for SMA template (unmodified).
> 2. Manualy run Master Import DTS package for loading sample data in
> Satging
> DB.
> 3. Manualy run Master Update DTS package for loading sample data in
> Subject
> Matter DB (with gbManualProcessDim and gbManualProcessFact set to -1
> for
> automatic processing disable).
> 4. Manualy run processing of Sales cube in Ananlytical DB and browse
> data
> after its finishing.
> Measure Actual Invoice Count is 2843 for 2001 year (Time.Standard dim
> -
> in rows), calculated members in Time.Standard dim - empty due to
> current
> day is not set.
> 5. Manualy run SetCurrentDay.dts package with gsCurrentDay =
> '10.05.2001'
> (October 5 2001)
> 6. Again manualy run processing of Sales cube in Ananlytical DB and
> browse
> data after its finishing.
> Measure Actual Invoice Count is 17058 for 2001 year (Time.Standard
> dim -
> in rows), calculated members in
> Time.Standard dim have some values.
> This behaviour fist time was occured in my custom Analytical app.
> constructed using MSSABI, the standard template (Sales and
> marketing)
> behaviour is the same.
> Where is the problem - is MSSABI or I'm doing something wrong? *
rsada
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message447789.html
|||Hi!
Checkout this:
http://support.microsoft.com/default...;en-us;834285.
Data in cubes become correct after delete ALL date dims exept only one with
custom rollups.
"rsada" <rsada.1fzsd0@.mail.webservertalk.com> /
: news:rsada.1fzsd0@.mail.webservertalk.com...
> Hi
> I have already seen this problem a while ago. Checkout
> http://www.webservertalk.com/showthread.php?t=895520
> I don't really understand what the guy that replied means by "Setting
> the all tu current".
> What I did at the time (and of course that is not the solution) was to
> remove the Weel.Standard dimension from the cube.
> Did you found a solution.?
>
>
> Eugene Frolov wrote:
>
> --
> rsada
> Posted via http://www.webservertalk.com
> View this thread: http://www.webservertalk.com/message447789.html
>
BI Accelerator SetCurrentDay Problem
I'm having a problem with the SetCurrentDay DTS package
of the BI Accelerator.
I'm loading a set of data using text files therefore I'm using
Master_Import.dts and Master_Update.dts unchanged. Then I run the VBScripts that are alsgo generated by Accelerator (VAMPanorama1_Load_VAMPanorama.vbs).
This loads the data from text files into the Staging Database and SM Database, and also runs the Set Current Day package with a fixed date of "2001-12-31".
The problem is data for the year of the Current Date is multiplied by a factor of six. Now, when I run SetCurrentDay Package Alone, to try to set a new date (say "2003-07-01") either opening the package in Enterprise Manager and changing the global variable, or using DTSRUN utility I get the same problem. The data es multiplied by a factor of 6 for the year 2003 and data for 2002 and 2001 shows up correctly at the year, qtr and month level.
I tried this on the Retail Analysis sample project that comes with Accelerator and had exactly the same problem.
Has anoyone seen something like this? am I doing something wrong here?
Any help is appreciated,I assume you've done something else by now, but I had the same problem. After verifying that the proper number of facts were loaded, and that a join wasn't creating additional records, I stumbled on the solution: There is no problem with the cube.
The problem is that the auto-generated base time dimensions each have an 'All' level and they should not. Setting all of them (for me they are Time.Standard and Week.Standard) to 'Current' will fix the problem and show the proper number of transactions. The reason they are multiplied is that 'All' aggregates across all time periods including the actual periods and the calculated ones like Current, Last Year, Change from Last, etc.
Originally posted by rsada
I'm having a problem with the SetCurrentDay DTS package
of the BI Accelerator.
I'm loading a set of data using text files therefore I'm using
Master_Import.dts and Master_Update.dts unchanged. Then I run the VBScripts that are alsgo generated by Accelerator (VAMPanorama1_Load_VAMPanorama.vbs).
This loads the data from text files into the Staging Database and SM Database, and also runs the Set Current Day package with a fixed date of "2001-12-31".
The problem is data for the year of the Current Date is multiplied by a factor of six. Now, when I run SetCurrentDay Package Alone, to try to set a new date (say "2003-07-01") either opening the package in Enterprise Manager and changing the global variable, or using DTSRUN utility I get the same problem. The data es multiplied by a factor of 6 for the year 2003 and data for 2002 and 2001 shows up correctly at the year, qtr and month level.
I tried this on the Retail Analysis sample project that comes with Accelerator and had exactly the same problem.
Has anoyone seen something like this? am I doing something wrong here?
Any help is appreciated,|||It's been some time since I last saw this thing. But could you refresh my memery
What do you mean by "Setting all of them (for me they are Time.Standard and Week.Standard) to 'Current'"? . Is this changing the name of the All Member or removing the All member ?
thanks|||It's actually constraining the dates to 'Current' but I found the real source of the problem.
The auto-generated date dimensions have custom members built into the dimension table, and one of them is called 'Current'. My original recommendation was that you could not leave the date dimensions set to 'All' (meaning that they are unconstrained) because that caused the totals to be much higher than they should be. Setting them to 'Current' or to some other specific value (a particular year, month, etc.) would fix the totals.
Here's the real problem: the unary operator property is not set by the spreadsheet generator. If you go into Analysis Manager and edit the Time.Standard dimension, you can fix it. For each level, look at the advanced properties and click the ellipse button beside the value for Unary Operators. A dialog comes up that lets you check "Enable Unary Operators" and tell it there is an existing field of <level name>_Oper (e.g. Year_Oper). This one change (setting it for all levels) fixes all of the auto-generated date dimensions.
What's happening is that without the unary operator, calculated dates like Current, Previous, Change, %Change, etc. are treated just like any other year value (like 2004) so the cube adds together all of the values causing data to be added both for the actual date that it occurs, and to the calculated dates (current, etc.). The unary operator lets the value in <level name>_Oper determine if the values will aggregate or not, and thankfully, the attribute has the right values.|||Thanks a lot Barclay I got it workin fine now..!! :)
of the BI Accelerator.
I'm loading a set of data using text files therefore I'm using
Master_Import.dts and Master_Update.dts unchanged. Then I run the VBScripts that are alsgo generated by Accelerator (VAMPanorama1_Load_VAMPanorama.vbs).
This loads the data from text files into the Staging Database and SM Database, and also runs the Set Current Day package with a fixed date of "2001-12-31".
The problem is data for the year of the Current Date is multiplied by a factor of six. Now, when I run SetCurrentDay Package Alone, to try to set a new date (say "2003-07-01") either opening the package in Enterprise Manager and changing the global variable, or using DTSRUN utility I get the same problem. The data es multiplied by a factor of 6 for the year 2003 and data for 2002 and 2001 shows up correctly at the year, qtr and month level.
I tried this on the Retail Analysis sample project that comes with Accelerator and had exactly the same problem.
Has anoyone seen something like this? am I doing something wrong here?
Any help is appreciated,I assume you've done something else by now, but I had the same problem. After verifying that the proper number of facts were loaded, and that a join wasn't creating additional records, I stumbled on the solution: There is no problem with the cube.
The problem is that the auto-generated base time dimensions each have an 'All' level and they should not. Setting all of them (for me they are Time.Standard and Week.Standard) to 'Current' will fix the problem and show the proper number of transactions. The reason they are multiplied is that 'All' aggregates across all time periods including the actual periods and the calculated ones like Current, Last Year, Change from Last, etc.
Originally posted by rsada
I'm having a problem with the SetCurrentDay DTS package
of the BI Accelerator.
I'm loading a set of data using text files therefore I'm using
Master_Import.dts and Master_Update.dts unchanged. Then I run the VBScripts that are alsgo generated by Accelerator (VAMPanorama1_Load_VAMPanorama.vbs).
This loads the data from text files into the Staging Database and SM Database, and also runs the Set Current Day package with a fixed date of "2001-12-31".
The problem is data for the year of the Current Date is multiplied by a factor of six. Now, when I run SetCurrentDay Package Alone, to try to set a new date (say "2003-07-01") either opening the package in Enterprise Manager and changing the global variable, or using DTSRUN utility I get the same problem. The data es multiplied by a factor of 6 for the year 2003 and data for 2002 and 2001 shows up correctly at the year, qtr and month level.
I tried this on the Retail Analysis sample project that comes with Accelerator and had exactly the same problem.
Has anoyone seen something like this? am I doing something wrong here?
Any help is appreciated,|||It's been some time since I last saw this thing. But could you refresh my memery
What do you mean by "Setting all of them (for me they are Time.Standard and Week.Standard) to 'Current'"? . Is this changing the name of the All Member or removing the All member ?
thanks|||It's actually constraining the dates to 'Current' but I found the real source of the problem.
The auto-generated date dimensions have custom members built into the dimension table, and one of them is called 'Current'. My original recommendation was that you could not leave the date dimensions set to 'All' (meaning that they are unconstrained) because that caused the totals to be much higher than they should be. Setting them to 'Current' or to some other specific value (a particular year, month, etc.) would fix the totals.
Here's the real problem: the unary operator property is not set by the spreadsheet generator. If you go into Analysis Manager and edit the Time.Standard dimension, you can fix it. For each level, look at the advanced properties and click the ellipse button beside the value for Unary Operators. A dialog comes up that lets you check "Enable Unary Operators" and tell it there is an existing field of <level name>_Oper (e.g. Year_Oper). This one change (setting it for all levels) fixes all of the auto-generated date dimensions.
What's happening is that without the unary operator, calculated dates like Current, Previous, Change, %Change, etc. are treated just like any other year value (like 2004) so the cube adds together all of the values causing data to be added both for the actual date that it occurs, and to the calculated dates (current, etc.). The unary operator lets the value in <level name>_Oper determine if the values will aggregate or not, and thankfully, the attribute has the right values.|||Thanks a lot Barclay I got it workin fine now..!! :)
Sunday, February 19, 2012
Best way to read a paradox file?
We used to use DTS to read Paradox files. This is something we do regularly. DTS has a native Paradox driver. This seems to have disappeared in SSIS.
Can anyone recommend the best way to read Paradox Files with SSIS?
Click New OLE DB connection. Choose "Native OLE DB\Microsoft Jet 4.0 OLE DB Provider". On the "All" tab find "Extended properties" and type "Paradox 5.x". For the "Data source" type the directory where your tables are.|||Great, that helps. Is there a way to add such a connection to the SSIS Wizard? (DTSWizard.exe)
|||thanks a million Bog !
Best way to promote DTS packages
To make the packages portable I have made connections as (local). Because once the packages go from say Development to Production, they would still work without re-designing them on a different box. The '(local)' as a the servername always looks for a the (local) server its on. But this seems too simple.
I have tired different methods such as using a INI file, Dynamic Protperties task etc., I have used global variables in the packages. Ultimately, I have used configuration tables to supply all the global variables, file names, etc.
Is there a flaw in this idea? Has anyone tired this before? If not how have you made DTS packages portable? Any ideas?
Thanks in advance.Your Idea sounds fine, but how does the package identify which record to pull to get the correct information. Do you query the server name, and use that in a where clause?
This is what I do, I put all my information in the registry of that machine. then have an active x script pull it out and assign it to global variables, then populate the properties of the tasks. How you populate the properties depends on the version of sql you are running.
hope this helps.|||Your Idea sounds fine, but how does the package identify which record to pull to get the correct information. Do you query the server name, and use that in a where clause?
This is what I do, I put all my information in the registry of that machine. then have an active x script pull it out and assign it to global variables, then populate the properties of the tasks. How you populate the properties depends on the version of sql you are running.
hope this helps.|||Originally posted by SHICKS
Your Idea sounds fine, but how does the package identify which record to pull to get the correct information. Do you query the server name, and use that in a where clause?
This is what I do, I put all my information in the registry of that machine. then have an active x script pull it out and assign it to global variables, then populate the properties of the tasks. How you populate the properties depends on the version of sql you are running.
hope this helps.
Yeah i run queries using the dynamic task properties to assign the global variables with the proper value from a conflict table. I have heard alot about putting the information in the registry of that machine. To do this wont you need to go on the computer you want to put the reg on? IF thats the case, it's hard for me to do that cuz the DEV,UAT,Production are spread all over the country.
But I would appreciate if I could find some more information about using registries.
Thanks is advance.|||Originally posted by vmlal
Yeah i run queries using the dynamic task properties to assign the global variables with the proper value from a conflict table. I have heard alot about putting the information in the registry of that machine. To do this wont you need to go on the computer you want to put the reg on? IF thats the case, it's hard for me to do that cuz the DEV,UAT,Production are spread all over the country.
But I would appreciate if I could find some more information about using registries.
Thanks is advance.
Updating the registry on remote machines is an administration issue I don't think I can anwser, but here is how I read the registy in an ActiveX script
Dim sh
Set sh = CreateObject("WScript.Shell")
path=sh.RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\FileLocations\Path")
server=sh.RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\Server\Server")
database=sh.RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\Server\Database")
username=sh.RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\Login\Username")
password=sh.RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\Login\Password")
I have tired different methods such as using a INI file, Dynamic Protperties task etc., I have used global variables in the packages. Ultimately, I have used configuration tables to supply all the global variables, file names, etc.
Is there a flaw in this idea? Has anyone tired this before? If not how have you made DTS packages portable? Any ideas?
Thanks in advance.Your Idea sounds fine, but how does the package identify which record to pull to get the correct information. Do you query the server name, and use that in a where clause?
This is what I do, I put all my information in the registry of that machine. then have an active x script pull it out and assign it to global variables, then populate the properties of the tasks. How you populate the properties depends on the version of sql you are running.
hope this helps.|||Your Idea sounds fine, but how does the package identify which record to pull to get the correct information. Do you query the server name, and use that in a where clause?
This is what I do, I put all my information in the registry of that machine. then have an active x script pull it out and assign it to global variables, then populate the properties of the tasks. How you populate the properties depends on the version of sql you are running.
hope this helps.|||Originally posted by SHICKS
Your Idea sounds fine, but how does the package identify which record to pull to get the correct information. Do you query the server name, and use that in a where clause?
This is what I do, I put all my information in the registry of that machine. then have an active x script pull it out and assign it to global variables, then populate the properties of the tasks. How you populate the properties depends on the version of sql you are running.
hope this helps.
Yeah i run queries using the dynamic task properties to assign the global variables with the proper value from a conflict table. I have heard alot about putting the information in the registry of that machine. To do this wont you need to go on the computer you want to put the reg on? IF thats the case, it's hard for me to do that cuz the DEV,UAT,Production are spread all over the country.
But I would appreciate if I could find some more information about using registries.
Thanks is advance.|||Originally posted by vmlal
Yeah i run queries using the dynamic task properties to assign the global variables with the proper value from a conflict table. I have heard alot about putting the information in the registry of that machine. To do this wont you need to go on the computer you want to put the reg on? IF thats the case, it's hard for me to do that cuz the DEV,UAT,Production are spread all over the country.
But I would appreciate if I could find some more information about using registries.
Thanks is advance.
Updating the registry on remote machines is an administration issue I don't think I can anwser, but here is how I read the registy in an ActiveX script
Dim sh
Set sh = CreateObject("WScript.Shell")
path=sh.RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\FileLocations\Path")
server=sh.RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\Server\Server")
database=sh.RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\Server\Database")
username=sh.RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\Login\Username")
password=sh.RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\Login\Password")
Thursday, February 16, 2012
Best way to move data to new server
I will shortly be moving some databases from an old server to a new one
(with a new name) and I am considering the best way to move the data.
DTS
Backup to disk and restore
Any ideas as to which is the best way?To add top Uri's post, if you need to move logins and passwords this page
will help
http://support.microsoft.com/default.aspx?scid=kb;en-us;246133
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OD8RtIOgDHA.3896@.tk2msftngp13.phx.gbl...
> mj
> http://support.microsoft.com/default.aspx?scid=kb;en-us;Q314546#9 --
Move
> Databases between computers running by sql server
> Q304692 INF: Moving SQL Server DBs to a New Location w/ BACKUP & RESTORE
> <http://support.microsoft.com/support/kb/articles/q304/6/92.asp>
>
> "MJ" <supportnospa_m@.belmont.co.uk> wrote in message
> news:vmtbjesm4bko81@.corp.supernews.com...
> > I will shortly be moving some databases from an old server to a new one
> > (with a new name) and I am considering the best way to move the data.
> >
> > DTS
> > Backup to disk and restore
> >
> > Any ideas as to which is the best way?
> >
> >
>
(with a new name) and I am considering the best way to move the data.
DTS
Backup to disk and restore
Any ideas as to which is the best way?To add top Uri's post, if you need to move logins and passwords this page
will help
http://support.microsoft.com/default.aspx?scid=kb;en-us;246133
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OD8RtIOgDHA.3896@.tk2msftngp13.phx.gbl...
> mj
> http://support.microsoft.com/default.aspx?scid=kb;en-us;Q314546#9 --
Move
> Databases between computers running by sql server
> Q304692 INF: Moving SQL Server DBs to a New Location w/ BACKUP & RESTORE
> <http://support.microsoft.com/support/kb/articles/q304/6/92.asp>
>
> "MJ" <supportnospa_m@.belmont.co.uk> wrote in message
> news:vmtbjesm4bko81@.corp.supernews.com...
> > I will shortly be moving some databases from an old server to a new one
> > (with a new name) and I am considering the best way to move the data.
> >
> > DTS
> > Backup to disk and restore
> >
> > Any ideas as to which is the best way?
> >
> >
>
best way to migrate dts pkg to other box?
Hi what is the best way to migrate DTS package to other box? Thanks.
The easiest way to move it is to bring it up in the DTS Designer and then
save it onto the other box. Keep in mind now you might also need to change
your connection information when you move it to a new box.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"flologic" <flo@.flo.net> wrote in message
news:eUSMw0hhEHA.4064@.TK2MSFTNGP12.phx.gbl...
> Hi what is the best way to migrate DTS package to other box? Thanks.
>
The easiest way to move it is to bring it up in the DTS Designer and then
save it onto the other box. Keep in mind now you might also need to change
your connection information when you move it to a new box.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"flologic" <flo@.flo.net> wrote in message
news:eUSMw0hhEHA.4064@.TK2MSFTNGP12.phx.gbl...
> Hi what is the best way to migrate DTS package to other box? Thanks.
>
best way to migrate dts pkg to other box?
Hi what is the best way to migrate DTS package to other box? Thanks.The easiest way to move it is to bring it up in the DTS Designer and then
save it onto the other box. Keep in mind now you might also need to change
your connection information when you move it to a new box.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"flologic" <flo@.flo.net> wrote in message
news:eUSMw0hhEHA.4064@.TK2MSFTNGP12.phx.gbl...
> Hi what is the best way to migrate DTS package to other box? Thanks.
>
save it onto the other box. Keep in mind now you might also need to change
your connection information when you move it to a new box.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"flologic" <flo@.flo.net> wrote in message
news:eUSMw0hhEHA.4064@.TK2MSFTNGP12.phx.gbl...
> Hi what is the best way to migrate DTS package to other box? Thanks.
>
best way to migrate dts pkg to other box?
Hi what is the best way to migrate DTS package to other box? Thanks.The easiest way to move it is to bring it up in the DTS Designer and then
save it onto the other box. Keep in mind now you might also need to change
your connection information when you move it to a new box.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"flologic" <flo@.flo.net> wrote in message
news:eUSMw0hhEHA.4064@.TK2MSFTNGP12.phx.gbl...
> Hi what is the best way to migrate DTS package to other box? Thanks.
>
save it onto the other box. Keep in mind now you might also need to change
your connection information when you move it to a new box.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"flologic" <flo@.flo.net> wrote in message
news:eUSMw0hhEHA.4064@.TK2MSFTNGP12.phx.gbl...
> Hi what is the best way to migrate DTS package to other box? Thanks.
>
Tuesday, February 14, 2012
Best way to keep track of SQL server changes...!
What would be the best practice to follow to keep track of MS SQL
server changes... Stroed procs, tables, views, triggers, indexes, DTS
and also jobs ect...
I am not quite sure how Source safe works with sql server. Any other
way to do this... Even if its manual work, its okey.. I would
appreciate if any of the DBA's let me know how they are facing this
issue...
Thanks in advance...Hi,
Have you thought about server side tracing?
If you do not wish to do the above and is ready to spend some money, then
there are few third-party tools available in the market, For ex: Red-gate.
--
Thanks
Yogish|||check out www.dbghost.com for SQL Server database change management with
source control intergration.
"SQLDBA" wrote:
> What would be the best practice to follow to keep track of MS SQL
> server changes... Stroed procs, tables, views, triggers, indexes, DTS
> and also jobs ect...
> I am not quite sure how Source safe works with sql server. Any other
> way to do this... Even if its manual work, its okey.. I would
> appreciate if any of the DBA's let me know how they are facing this
> issue...
> Thanks in advance...
>
server changes... Stroed procs, tables, views, triggers, indexes, DTS
and also jobs ect...
I am not quite sure how Source safe works with sql server. Any other
way to do this... Even if its manual work, its okey.. I would
appreciate if any of the DBA's let me know how they are facing this
issue...
Thanks in advance...Hi,
Have you thought about server side tracing?
If you do not wish to do the above and is ready to spend some money, then
there are few third-party tools available in the market, For ex: Red-gate.
--
Thanks
Yogish|||check out www.dbghost.com for SQL Server database change management with
source control intergration.
"SQLDBA" wrote:
> What would be the best practice to follow to keep track of MS SQL
> server changes... Stroed procs, tables, views, triggers, indexes, DTS
> and also jobs ect...
> I am not quite sure how Source safe works with sql server. Any other
> way to do this... Even if its manual work, its okey.. I would
> appreciate if any of the DBA's let me know how they are facing this
> issue...
> Thanks in advance...
>
Sunday, February 12, 2012
Best way to DTS
Does anyone know of a better way I could go about doing running a DTS
package where I basically delete all the data from the SQL server and
then move (re-populate) it with data from our Navision (ERP) database?
Right now I have connections which connect to each other through
connections. The first deletes the data from the tables, the next 40 or
so move the data from our ERP database back to the SQL server. The
reason we do so is because we cannot figure out a way how to move only
the data that has changed in the ERP database to to the SQL server, so
instead we just erase the SQL server every night and copy all the data
from the ERP database into it so we know we are getting current data
once a day. The main reason I need help is because this process is
taking 5+ hours now and has grown with the amount of data we have input
over time. I would really appreciate ANY input on my situation, it
would help tremendously.If you don't know how to identify changed data only, maybe SQL Server can
help you - do please check basic Replication topics in Books OnLine if this
is what you need. Also, as a quick solution - backup of the database on
production server and restore on standby server should be much faster than
deleting and inserting all of the data.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
<barhoc11@.yahoo.com> wrote in message
news:1124116978.660087.4840@.g47g2000cwa.googlegroups.com...
> Does anyone know of a better way I could go about doing running a DTS
> package where I basically delete all the data from the SQL server and
> then move (re-populate) it with data from our Navision (ERP) database?
> Right now I have connections which connect to each other through
> connections. The first deletes the data from the tables, the next 40 or
> so move the data from our ERP database back to the SQL server. The
> reason we do so is because we cannot figure out a way how to move only
> the data that has changed in the ERP database to to the SQL server, so
> instead we just erase the SQL server every night and copy all the data
> from the ERP database into it so we know we are getting current data
> once a day. The main reason I need help is because this process is
> taking 5+ hours now and has grown with the amount of data we have input
> over time. I would really appreciate ANY input on my situation, it
> would help tremendously.
>|||Thanks so much Dejan for the quick response I just have a couple
questions for you or anyone who has an answer, if you have the time to
answer I would greatful.
Is this possible to replicate a Navision(ERP) database to a SQL server?
Can you explain the quick solution you mentioned...- would this include
dumping the data to a temporary database then inserting it in the SQL
server database?|||> Is this possible to replicate a Navision(ERP) database to a SQL server?
Uh-uh...
AFAIK Navision has two possibilities: to use it's native db or to use SQL
Server as RDBMS. I guessed you have SQL Server version of Navision. This way
replication would be quite simple. But from your answer I infer you use
native Navision db. If this is true, then you would have to write custom
Publisher in order to use the replication, and I guess this is not an option
for you. Also, it is not possible to restore backup of Navision native db to
SQL Server. So I guess your best option is to find which rows have changed
and create more efficient DTS packages. Maybe you can ask in some Navision
group how to find only changed rows?
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
package where I basically delete all the data from the SQL server and
then move (re-populate) it with data from our Navision (ERP) database?
Right now I have connections which connect to each other through
connections. The first deletes the data from the tables, the next 40 or
so move the data from our ERP database back to the SQL server. The
reason we do so is because we cannot figure out a way how to move only
the data that has changed in the ERP database to to the SQL server, so
instead we just erase the SQL server every night and copy all the data
from the ERP database into it so we know we are getting current data
once a day. The main reason I need help is because this process is
taking 5+ hours now and has grown with the amount of data we have input
over time. I would really appreciate ANY input on my situation, it
would help tremendously.If you don't know how to identify changed data only, maybe SQL Server can
help you - do please check basic Replication topics in Books OnLine if this
is what you need. Also, as a quick solution - backup of the database on
production server and restore on standby server should be much faster than
deleting and inserting all of the data.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
<barhoc11@.yahoo.com> wrote in message
news:1124116978.660087.4840@.g47g2000cwa.googlegroups.com...
> Does anyone know of a better way I could go about doing running a DTS
> package where I basically delete all the data from the SQL server and
> then move (re-populate) it with data from our Navision (ERP) database?
> Right now I have connections which connect to each other through
> connections. The first deletes the data from the tables, the next 40 or
> so move the data from our ERP database back to the SQL server. The
> reason we do so is because we cannot figure out a way how to move only
> the data that has changed in the ERP database to to the SQL server, so
> instead we just erase the SQL server every night and copy all the data
> from the ERP database into it so we know we are getting current data
> once a day. The main reason I need help is because this process is
> taking 5+ hours now and has grown with the amount of data we have input
> over time. I would really appreciate ANY input on my situation, it
> would help tremendously.
>|||Thanks so much Dejan for the quick response I just have a couple
questions for you or anyone who has an answer, if you have the time to
answer I would greatful.
Is this possible to replicate a Navision(ERP) database to a SQL server?
Can you explain the quick solution you mentioned...- would this include
dumping the data to a temporary database then inserting it in the SQL
server database?|||> Is this possible to replicate a Navision(ERP) database to a SQL server?
Uh-uh...
AFAIK Navision has two possibilities: to use it's native db or to use SQL
Server as RDBMS. I guessed you have SQL Server version of Navision. This way
replication would be quite simple. But from your answer I infer you use
native Navision db. If this is true, then you would have to write custom
Publisher in order to use the replication, and I guess this is not an option
for you. Also, it is not possible to restore backup of Navision native db to
SQL Server. So I guess your best option is to find which rows have changed
and create more efficient DTS packages. Maybe you can ask in some Navision
group how to find only changed rows?
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
Best way to DTS
Does anyone know of a better way I could go about doing running a DTS
package where I basically delete all the data from the SQL server and
then move (re-populate) it with data from our Navision (ERP) database?
Right now I have connections which connect to each other through
connections. The first deletes the data from the tables, the next 40 or
so move the data from our ERP database back to the SQL server. The
reason we do so is because we cannot figure out a way how to move only
the data that has changed in the ERP database to to the SQL server, so
instead we just erase the SQL server every night and copy all the data
from the ERP database into it so we know we are getting current data
once a day. The main reason I need help is because this process is
taking 5+ hours now and has grown with the amount of data we have input
over time. I would really appreciate ANY input on my situation, it
would help tremendously.
If you don't know how to identify changed data only, maybe SQL Server can
help you - do please check basic Replication topics in Books OnLine if this
is what you need. Also, as a quick solution - backup of the database on
production server and restore on standby server should be much faster than
deleting and inserting all of the data.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
<barhoc11@.yahoo.com> wrote in message
news:1124116978.660087.4840@.g47g2000cwa.googlegrou ps.com...
> Does anyone know of a better way I could go about doing running a DTS
> package where I basically delete all the data from the SQL server and
> then move (re-populate) it with data from our Navision (ERP) database?
> Right now I have connections which connect to each other through
> connections. The first deletes the data from the tables, the next 40 or
> so move the data from our ERP database back to the SQL server. The
> reason we do so is because we cannot figure out a way how to move only
> the data that has changed in the ERP database to to the SQL server, so
> instead we just erase the SQL server every night and copy all the data
> from the ERP database into it so we know we are getting current data
> once a day. The main reason I need help is because this process is
> taking 5+ hours now and has grown with the amount of data we have input
> over time. I would really appreciate ANY input on my situation, it
> would help tremendously.
>
|||Thanks so much Dejan for the quick response I just have a couple
questions for you or anyone who has an answer, if you have the time to
answer I would greatful.
Is this possible to replicate a Navision(ERP) database to a SQL server?
Can you explain the quick solution you mentioned...- would this include
dumping the data to a temporary database then inserting it in the SQL
server database?
|||> Is this possible to replicate a Navision(ERP) database to a SQL server?
Uh-uh...
AFAIK Navision has two possibilities: to use it's native db or to use SQL
Server as RDBMS. I guessed you have SQL Server version of Navision. This way
replication would be quite simple. But from your answer I infer you use
native Navision db. If this is true, then you would have to write custom
Publisher in order to use the replication, and I guess this is not an option
for you. Also, it is not possible to restore backup of Navision native db to
SQL Server. So I guess your best option is to find which rows have changed
and create more efficient DTS packages. Maybe you can ask in some Navision
group how to find only changed rows?
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
package where I basically delete all the data from the SQL server and
then move (re-populate) it with data from our Navision (ERP) database?
Right now I have connections which connect to each other through
connections. The first deletes the data from the tables, the next 40 or
so move the data from our ERP database back to the SQL server. The
reason we do so is because we cannot figure out a way how to move only
the data that has changed in the ERP database to to the SQL server, so
instead we just erase the SQL server every night and copy all the data
from the ERP database into it so we know we are getting current data
once a day. The main reason I need help is because this process is
taking 5+ hours now and has grown with the amount of data we have input
over time. I would really appreciate ANY input on my situation, it
would help tremendously.
If you don't know how to identify changed data only, maybe SQL Server can
help you - do please check basic Replication topics in Books OnLine if this
is what you need. Also, as a quick solution - backup of the database on
production server and restore on standby server should be much faster than
deleting and inserting all of the data.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
<barhoc11@.yahoo.com> wrote in message
news:1124116978.660087.4840@.g47g2000cwa.googlegrou ps.com...
> Does anyone know of a better way I could go about doing running a DTS
> package where I basically delete all the data from the SQL server and
> then move (re-populate) it with data from our Navision (ERP) database?
> Right now I have connections which connect to each other through
> connections. The first deletes the data from the tables, the next 40 or
> so move the data from our ERP database back to the SQL server. The
> reason we do so is because we cannot figure out a way how to move only
> the data that has changed in the ERP database to to the SQL server, so
> instead we just erase the SQL server every night and copy all the data
> from the ERP database into it so we know we are getting current data
> once a day. The main reason I need help is because this process is
> taking 5+ hours now and has grown with the amount of data we have input
> over time. I would really appreciate ANY input on my situation, it
> would help tremendously.
>
|||Thanks so much Dejan for the quick response I just have a couple
questions for you or anyone who has an answer, if you have the time to
answer I would greatful.
Is this possible to replicate a Navision(ERP) database to a SQL server?
Can you explain the quick solution you mentioned...- would this include
dumping the data to a temporary database then inserting it in the SQL
server database?
|||> Is this possible to replicate a Navision(ERP) database to a SQL server?
Uh-uh...
AFAIK Navision has two possibilities: to use it's native db or to use SQL
Server as RDBMS. I guessed you have SQL Server version of Navision. This way
replication would be quite simple. But from your answer I infer you use
native Navision db. If this is true, then you would have to write custom
Publisher in order to use the replication, and I guess this is not an option
for you. Also, it is not possible to restore backup of Navision native db to
SQL Server. So I guess your best option is to find which rows have changed
and create more efficient DTS packages. Maybe you can ask in some Navision
group how to find only changed rows?
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
Best way to DTS
Does anyone know of a better way I could go about doing running a DTS
package where I basically delete all the data from the SQL server and
then move (re-populate) it with data from our Navision (ERP) database?
Right now I have connections which connect to each other through
connections. The first deletes the data from the tables, the next 40 or
so move the data from our ERP database back to the SQL server. The
reason we do so is because we cannot figure out a way how to move only
the data that has changed in the ERP database to to the SQL server, so
instead we just erase the SQL server every night and copy all the data
from the ERP database into it so we know we are getting current data
once a day. The main reason I need help is because this process is
taking 5+ hours now and has grown with the amount of data we have input
over time. I would really appreciate ANY input on my situation, it
would help tremendously.If you don't know how to identify changed data only, maybe SQL Server can
help you - do please check basic Replication topics in Books OnLine if this
is what you need. Also, as a quick solution - backup of the database on
production server and restore on standby server should be much faster than
deleting and inserting all of the data.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
<barhoc11@.yahoo.com> wrote in message
news:1124116978.660087.4840@.g47g2000cwa.googlegroups.com...
> Does anyone know of a better way I could go about doing running a DTS
> package where I basically delete all the data from the SQL server and
> then move (re-populate) it with data from our Navision (ERP) database?
> Right now I have connections which connect to each other through
> connections. The first deletes the data from the tables, the next 40 or
> so move the data from our ERP database back to the SQL server. The
> reason we do so is because we cannot figure out a way how to move only
> the data that has changed in the ERP database to to the SQL server, so
> instead we just erase the SQL server every night and copy all the data
> from the ERP database into it so we know we are getting current data
> once a day. The main reason I need help is because this process is
> taking 5+ hours now and has grown with the amount of data we have input
> over time. I would really appreciate ANY input on my situation, it
> would help tremendously.
>|||Thanks so much Dejan for the quick response I just have a couple
questions for you or anyone who has an answer, if you have the time to
answer I would greatful.
Is this possible to replicate a Navision(ERP) database to a SQL server?
Can you explain the quick solution you mentioned...- would this include
dumping the data to a temporary database then inserting it in the SQL
server database?|||> Is this possible to replicate a Navision(ERP) database to a SQL server?
Uh-uh...
AFAIK Navision has two possibilities: to use it's native db or to use SQL
Server as RDBMS. I guessed you have SQL Server version of Navision. This way
replication would be quite simple. But from your answer I infer you use
native Navision db. If this is true, then you would have to write custom
Publisher in order to use the replication, and I guess this is not an option
for you. Also, it is not possible to restore backup of Navision native db to
SQL Server. So I guess your best option is to find which rows have changed
and create more efficient DTS packages. Maybe you can ask in some Navision
group how to find only changed rows?
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
package where I basically delete all the data from the SQL server and
then move (re-populate) it with data from our Navision (ERP) database?
Right now I have connections which connect to each other through
connections. The first deletes the data from the tables, the next 40 or
so move the data from our ERP database back to the SQL server. The
reason we do so is because we cannot figure out a way how to move only
the data that has changed in the ERP database to to the SQL server, so
instead we just erase the SQL server every night and copy all the data
from the ERP database into it so we know we are getting current data
once a day. The main reason I need help is because this process is
taking 5+ hours now and has grown with the amount of data we have input
over time. I would really appreciate ANY input on my situation, it
would help tremendously.If you don't know how to identify changed data only, maybe SQL Server can
help you - do please check basic Replication topics in Books OnLine if this
is what you need. Also, as a quick solution - backup of the database on
production server and restore on standby server should be much faster than
deleting and inserting all of the data.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
<barhoc11@.yahoo.com> wrote in message
news:1124116978.660087.4840@.g47g2000cwa.googlegroups.com...
> Does anyone know of a better way I could go about doing running a DTS
> package where I basically delete all the data from the SQL server and
> then move (re-populate) it with data from our Navision (ERP) database?
> Right now I have connections which connect to each other through
> connections. The first deletes the data from the tables, the next 40 or
> so move the data from our ERP database back to the SQL server. The
> reason we do so is because we cannot figure out a way how to move only
> the data that has changed in the ERP database to to the SQL server, so
> instead we just erase the SQL server every night and copy all the data
> from the ERP database into it so we know we are getting current data
> once a day. The main reason I need help is because this process is
> taking 5+ hours now and has grown with the amount of data we have input
> over time. I would really appreciate ANY input on my situation, it
> would help tremendously.
>|||Thanks so much Dejan for the quick response I just have a couple
questions for you or anyone who has an answer, if you have the time to
answer I would greatful.
Is this possible to replicate a Navision(ERP) database to a SQL server?
Can you explain the quick solution you mentioned...- would this include
dumping the data to a temporary database then inserting it in the SQL
server database?|||> Is this possible to replicate a Navision(ERP) database to a SQL server?
Uh-uh...
AFAIK Navision has two possibilities: to use it's native db or to use SQL
Server as RDBMS. I guessed you have SQL Server version of Navision. This way
replication would be quite simple. But from your answer I infer you use
native Navision db. If this is true, then you would have to write custom
Publisher in order to use the replication, and I guess this is not an option
for you. Also, it is not possible to restore backup of Navision native db to
SQL Server. So I guess your best option is to find which rows have changed
and create more efficient DTS packages. Maybe you can ask in some Navision
group how to find only changed rows?
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
Friday, February 10, 2012
Best way to accomplish this - Move to new server
I just purchased a new server and I want to move all my
existing databases, DTS scripts, logins, etc to the new
server. What is the best/easiest/efficient way to
accomplish this?Easiest way is probably to restore a backup to the other server
or detach/attach
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/default.aspx?kbid=314546
INF: Moving SQL Server Databases to a New Location with Detach/Attach
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q224071
or use the Copy database wizard
INF: Understanding and Troubleshooting the Copy Database Wizard in SQL
Server 2000
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q274463
Also check out
INF: How To Transfer Logins and Passwords Between SQL Servers
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q246133
PRB: User Logon and/or Permission Errors After Restoring Dump
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q168001
INF: How to Resolve Permission Issues When a Database is Moved Between SQL
Servers
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q240872
PRB: "Troubleshooting Orphaned Users" Topic in Books Online is Incomplete
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q274188
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Mike Taylor" <mtaylor103@.hotmail.com> wrote in message
news:0b8c01c3d539$6a135590$a501280a@.phx.gbl...
> I just purchased a new server and I want to move all my
> existing databases, DTS scripts, logins, etc to the new
> server. What is the best/easiest/efficient way to
> accomplish this?|||Backup and Restore work well for me. Setup SQL Server on the new machine
with the same service pack as the existing one, and the same disk layout if
possible. Restore the Master and MSDB databases for your logins & DTS and
jobs etc. and any other DB's you have. If the DB's are large it may be
faster to de-attach/re-attach from one machine to the other.
It might be possible to just copy the OS files for the databases between the
machines if they have the same directory structure, but I've never tried
this.
Mike Kruchten
"Mike Taylor" <mtaylor103@.hotmail.com> wrote in message
news:0b8c01c3d539$6a135590$a501280a@.phx.gbl...
> I just purchased a new server and I want to move all my
> existing databases, DTS scripts, logins, etc to the new
> server. What is the best/easiest/efficient way to
> accomplish this?|||You may want to read this:
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://www.sqlmantra.com/314546
--
Rohtash Kapoor
http://www.sqlmantra.com
"Mike Taylor" <mtaylor103@.hotmail.com> wrote in message
news:0b8c01c3d539$6a135590$a501280a@.phx.gbl...
> I just purchased a new server and I want to move all my
> existing databases, DTS scripts, logins, etc to the new
> server. What is the best/easiest/efficient way to
> accomplish this?|||sorry wrong link. The correct one is:
support.microsoft.com?kbid=314546
"Rohtash Kapoor" <rohtash_nospam@.sqlmantra.com> wrote in message
news:Ov0ldsT1DHA.3216@.TK2MSFTNGP11.phx.gbl...
> You may want to read this:
> HOW TO: Move Databases Between Computers That Are Running SQL Server
> http://www.sqlmantra.com/314546
> --
> Rohtash Kapoor
> http://www.sqlmantra.com
>
> "Mike Taylor" <mtaylor103@.hotmail.com> wrote in message
> news:0b8c01c3d539$6a135590$a501280a@.phx.gbl...
> > I just purchased a new server and I want to move all my
> > existing databases, DTS scripts, logins, etc to the new
> > server. What is the best/easiest/efficient way to
> > accomplish this?
>
existing databases, DTS scripts, logins, etc to the new
server. What is the best/easiest/efficient way to
accomplish this?Easiest way is probably to restore a backup to the other server
or detach/attach
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/default.aspx?kbid=314546
INF: Moving SQL Server Databases to a New Location with Detach/Attach
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q224071
or use the Copy database wizard
INF: Understanding and Troubleshooting the Copy Database Wizard in SQL
Server 2000
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q274463
Also check out
INF: How To Transfer Logins and Passwords Between SQL Servers
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q246133
PRB: User Logon and/or Permission Errors After Restoring Dump
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q168001
INF: How to Resolve Permission Issues When a Database is Moved Between SQL
Servers
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q240872
PRB: "Troubleshooting Orphaned Users" Topic in Books Online is Incomplete
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q274188
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Mike Taylor" <mtaylor103@.hotmail.com> wrote in message
news:0b8c01c3d539$6a135590$a501280a@.phx.gbl...
> I just purchased a new server and I want to move all my
> existing databases, DTS scripts, logins, etc to the new
> server. What is the best/easiest/efficient way to
> accomplish this?|||Backup and Restore work well for me. Setup SQL Server on the new machine
with the same service pack as the existing one, and the same disk layout if
possible. Restore the Master and MSDB databases for your logins & DTS and
jobs etc. and any other DB's you have. If the DB's are large it may be
faster to de-attach/re-attach from one machine to the other.
It might be possible to just copy the OS files for the databases between the
machines if they have the same directory structure, but I've never tried
this.
Mike Kruchten
"Mike Taylor" <mtaylor103@.hotmail.com> wrote in message
news:0b8c01c3d539$6a135590$a501280a@.phx.gbl...
> I just purchased a new server and I want to move all my
> existing databases, DTS scripts, logins, etc to the new
> server. What is the best/easiest/efficient way to
> accomplish this?|||You may want to read this:
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://www.sqlmantra.com/314546
--
Rohtash Kapoor
http://www.sqlmantra.com
"Mike Taylor" <mtaylor103@.hotmail.com> wrote in message
news:0b8c01c3d539$6a135590$a501280a@.phx.gbl...
> I just purchased a new server and I want to move all my
> existing databases, DTS scripts, logins, etc to the new
> server. What is the best/easiest/efficient way to
> accomplish this?|||sorry wrong link. The correct one is:
support.microsoft.com?kbid=314546
"Rohtash Kapoor" <rohtash_nospam@.sqlmantra.com> wrote in message
news:Ov0ldsT1DHA.3216@.TK2MSFTNGP11.phx.gbl...
> You may want to read this:
> HOW TO: Move Databases Between Computers That Are Running SQL Server
> http://www.sqlmantra.com/314546
> --
> Rohtash Kapoor
> http://www.sqlmantra.com
>
> "Mike Taylor" <mtaylor103@.hotmail.com> wrote in message
> news:0b8c01c3d539$6a135590$a501280a@.phx.gbl...
> > I just purchased a new server and I want to move all my
> > existing databases, DTS scripts, logins, etc to the new
> > server. What is the best/easiest/efficient way to
> > accomplish this?
>
Subscribe to:
Posts (Atom)