DTS problems - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

DTS problems

Author

Topic

tess
Starting Member

10 Posts

Posted - 2006-03-02 : 07:44:18

I need to get 2 colums of data from one citadel database to another Oracle database on a scheduled basis. This was very easy to setup, even for me:-) What I actually need is to add a column with a predefined value during the transfer to the Oracle database. This value always stays the same and its a number.

I have been fiddeling with the dts package for a couple of days using sql querys, but cant figure out how to do this. Im not a programmer, but are experienced in computing generally. I have tried to add the predefined valu as a global variable, but how do I then use it for this purpose? Some help on how to move forward in this case would be much appreciated.

Regards

Tess

kelcog
Starting Member

3 Posts

Posted - 2006-03-15 : 10:27:44

You have probably already finished this project by now, but in this case, can't you just alter the table on your Oracle database and include your column with a default value so that when the transfer is done, this field is populated automatically?

Kelli

tess
Starting Member

10 Posts

Posted - 2006-03-15 : 14:18:28

Kelli. Thx for replying. What you say is actually what i did eventually:-)

SELECT intdata1 as variable1, intdata2 as variable2, 'predefined value' as variable3
From mytable

It was actually very easy.....
But now I have another problem. The intdata1 and intdata2 columns have a number in them and I need to sum together these two numbers and put the results in another column. I tried the below statement,but with no luck. Any advice would be greatly appreciated.

SELECT intdata1 as variable1, intdata2 as variable2, (intdata 1 + intdata2)as Totalsum
From mytable

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-15 : 14:54:56

Hi tess,

Q1. intdata 1 or intdata1
Q2. r those numeric fields? if not convert to numeric using convert function
Q3. No Luck --> ? Not giving any result ? Not giving expected results ? Error Msg?

tess
Starting Member

10 Posts

Posted - 2006-03-16 : 05:42:28

q1: Intdata

q2: They are varchar(50) fields. I have to convert them as you say(Thx a lot!) but cant really get this to work.
q3: Here is how I have tried to do this:
First tried:

SELECT intdata1 as variable1, intdata2 as variable2, Convert(decimal(20),intdata + intdata2)as Totalsum
From mytable
But still the numbers are not correct. If Intdata1 has the number 1342 and intdata2 has the number 23242, the Totalsum geits to 134223242. Should I somehow convert the Totalsum variable to get the right output?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-03-16 : 08:39:37

[code]
SELECT intdata1 as variable1, intdata2 as variable2, (Convert(int,intdata) + Convert(int,intdata2))as Totalsum
From mytable
[/code]

As there is no precision, just use int's..

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-16 : 08:43:42

Hi tess,
Convert each field content to numeric
[Otherwise u convert something to numeric and by the + sign, u are concatenating the other]
Run the following & c

Print 25 + 34
Print 25 + '34'

or u can declare some variables as varchar and decimal and do the experiment similar to above and learn

Here u go

SELECT intdata1 as variable1, intdata2 as variable2, Convert(decimal(20,2),intdata) + Convert(decimal(20,2),intdata2) as Totalsum
From mytable

tess
Starting Member

10 Posts

Posted - 2006-03-16 : 10:08:27

What you specify seems very good, but now Im having trouble opening the databases "folder" in the enterprice manager. Enterprice Manager justs "hangs" forever and no packages run properly. Tried restarting the service/server but didnt help. Im stuck...

Any advice?

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-16 : 10:16:12

Restart the machine & C

tess
Starting Member

10 Posts

Posted - 2006-03-16 : 10:27:26

Did restart the machine but it hangs at once I try top open the databases "folder" in Enterprice Manager....

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-16 : 15:19:16

Ask this as a seperate Question in Administration or General ... Forum
describing the situation
Somebody else may help you

tess
Starting Member

10 Posts

Posted - 2006-03-17 : 07:16:46

Detatched the dataabse and reattached it. That solved the problem.

But anyway, now I have the solution based on you earlier help. Thx a lot to you all.

tess
Starting Member

10 Posts

Posted - 2006-03-20 : 11:04:12

This is crazy. Im almost done, but always there is something in the way. The problem is this:

*************************************************

SELECT table1.timeframe as timeamount, (Convert(int,table1.Intdata1) + Convert(int,TAble2.intdata2 ))as Totalamount , generalID' as Unit_id

FROM table1

CROSS
JOIN
table2
WHERE table1.timeframe=(SELECT MAX(table1.timeframe) From table1)

*************************************************
The intdata1 and intdata2 columns are located in separate tables.I tried using "cross Join" , but then the Totalsum amount was not correct.

Any advice?

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-20 : 11:17:29

Give the table structures, some sample data and expected results

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-03-20 : 11:58:47

To me, that looks like the same table, I can't see why you need a cross join?

tess
Starting Member

10 Posts

Posted - 2006-03-20 : 12:04:58

Convert(int,TAble2.intdata2 ))

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-03-21 : 07:25:52

I meant on your join, but why use a cross join? Why not just a left or inner?

tess
Starting Member

10 Posts

Posted - 2006-03-21 : 07:38:37

I believed Cross join to give the correct number, but it doesnt. Using the other joins requires a unicke ID column(doesnt it?), and there is not such a column in the tables.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-03-21 : 07:58:38

No, you don't need a uniqueid, you just need a way of matching records from table1 to table2, if this doesn't exist, then how will you ever know that you are summing the right values?

Can you post your table structures?

tess
Starting Member

10 Posts

Posted - 2006-03-21 : 08:21:47

Table1:

Timescale(datetime(8), Unit_ID(Varchar(50)), Intdata1(Varchar(50))

Table 2

Timescale(datetime(8), Unit_ID(Varchar(50)), Intdata2(Varchar(50))

Example table 1:
Timescale Intdata1 Unit_Id
24.02.2006 15:34 1776 55-10-105-07
24.02.2006 16:34 1776 55-10-105-07
24.02.2006 17:34 1776 55-10-105-07
24.02.2006 18:34 1776 55-10-105-07
24.02.2006 19:34 1776 55-10-105-07
24.02.2006 20:34 1776 55-10-105-07
24.02.2006 21:34 1776 55-10-105-07

Example table2:
Timescale Intdata2 Unit_Id
18.02.2006 11:52 785 55-10-105-08
22.02.2006 13:48 899 55-10-105-08
02.03.2006 14:03 1122 55-10-105-08
02.03.2006 14:06 1123 55-10-105-08
02.03.2006 14:13 1124 55-10-105-08
02.03.2006 14:15 1125 55-10-105-08
02.03.2006 14:18 1126 55-10-105-08

Please note that there can be identical values in the Timescale columns, so I can not use this for the index.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-03-21 : 08:27:36

So, what joins the two tables to do the calculation?

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources