How to Connect remote Access database to SQL server

Hai,
I want to Convert remote Ms access data to sqlserver data.
Here i am using FTP Task in DTS for downloading Ms Access database to local machine.
After that, I convert it to sql data.
But, most of time, With download the mdb file, the message 'Opreation sucessfully' display.
Please give solution for this.
With regards,
dharmaprakash.
**********************************************************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...Please do not post separately in different groups.
Whats the matter if its performed successfully.
Jens Süßmeyer.

how to compare 2 date by "month"?

I'm doing DTS, Here is one of my Sql Query in DTS

Select * From ZT_DailyRpt_Detail
Where
isNumeric(TenDayDate) = 1 And TenDayDate <
Convert(varchar(10),DateAdd(Month,-CAST
((SELECT keepmonth
FROM zt_databackup a, zt_biller b
WHERE a.companycode = b.companycode AND
zt_DailyRpt_Detail.biller_code = b.billercode) AS int),GetDate()),112)

the sub query 【SELECT keepmonth
FROM zt_databackup a, zt_biller b
WHERE a.companycode = b.companycode AND
zt_DailyRpt_Detail.biller_code = b.billercode) AS int】will return a value 6 or 12

for the reason, every month have differnet days, ( some have 31 days , some are 30 days ) I don't want to check the day of date , only to compare month

if sub query return "6" and I do DTS on 2007/07/29 , will select date whichTenDayDate< 2007/01 not TenDayDate<2007/01/29 ( don't want to check the day of date)

does my query correct? if not can you correct it for me? thank you very much

Not sure I understood your problem, but maybe what you're looking for isDATEDIFFsql function?

|||

sorry for not describe my question clearly

my problem is

if I want to compare 2 date for example one is 2007/01/22 another is 2007/07/30

2007/07/30 - 2007/01/22 = 2007/06/08 right? that is not what I need

I need the result is 2007/06

I want to do DTS

if user run DTS in 2007/07/28 , want to select the data < 2007/07/28- 6 months , so it would be where data < 2007/01/28

I am not select Data < 2007/01/28 , I want to select Data < 2007/01 ( need not to compare the Day )

thank you

how to combine text data rows into single row?

Hi!!!!

Need some quick help on SQL.....DTS packages.

I’ve loaded data from text files into a table which is in the following format.....

Seq_No

Row_No

Data

1

1

ABC

2

2

DEF

3

3

GHI

4

4

JKL

5

1

MNO

6

2

PQR

7

3

STU

8

4

VWX

Every four lines correspond to one single logical record. So, all the data in every 4 rows have to be combined into one big string which’ll then be loaded into one col of another temporary table .....structure below.....

Record_No

Data

1

ABCDEFGHIJKL

2

MNOPQRSTUVWX

I’m looking for a way to achieve this WITHOUT using Cursors (as it’ll slow down performance)......

Any suggestions will be highly appreciated.

what data type is the Data column?|||

One way is to use the CLR Aggregate function for string concatenation shown in the BOL

http://msdn2.microsoft.com/en-us/library/ms254508.aspx

and then run a query like this

select ((Seq_No-1) / 4)+1 as Record_No, dbo.Concatenate(Data) as Data
into dest
from source
group by ((Seq_No-1) / 4)+1
go

select * from dest
go

|||

hi pramy,

please refer to this post

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=586833&SiteID=1

regards

|||

hi Derek,

thanks for ur reply. the data type is of varchar.

|||

pramy, you still need help on this issue? if not please mark an answer.

thanks,

derek

|||

hi all,

thanks for ur response.

Derek, i've got the solution for my problem and the query is as given below.

select 'data' = convert(varchar(100),(a.data)+''+(b.data)+''+(c.data)+''+(d.data))

from test_data a,test_data b,test_data c,test_data d

where ((b.seqno=a.seqno+1 and b.rowno=a.rowno+1)and(c.seqno=b.seqno+1 and c.rowno=b.rowno+1)and(d.seqno=c.seqno+1 and d.rowno=c.rowno+1))

how to combine text data rows into single row?

Hi!!!!

Need some quick help on SQL.....DTS packages.

I’ve loaded data from text files into a table which is in the following format.....

Seq_No

Row_No

Data

1

1

ABC

2

2

DEF

3

3

GHI

4

4

JKL

5

1

MNO

6

2

PQR

7

3

STU

8

4

VWX

Every four lines correspond to one single logical record. So, all the data in every 4 rows have to be combined into one big string which’ll then be loaded into one col of another temporary table .....structure below.....

Record_No

Data

1

ABCDEFGHIJKL

2

MNOPQRSTUVWX

I’m looking for a way to achieve this WITHOUT using Cursors (as it’ll slow down performance)......

Any suggestions will be highly appreciated.

what data type is the Data column?|||

One way is to use the CLR Aggregate function for string concatenation shown in the BOL

http://msdn2.microsoft.com/en-us/library/ms254508.aspx

and then run a query like this

select ((Seq_No-1) / 4)+1 as Record_No, dbo.Concatenate(Data) as Data
into dest
from source
group by ((Seq_No-1) / 4)+1
go

select * from dest
go

|||

hi pramy,

please refer to this post

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=586833&SiteID=1

regards

|||

hi Derek,

thanks for ur reply. the data type is of varchar.

|||

pramy, you still need help on this issue? if not please mark an answer.

thanks,

derek

|||

hi all,

thanks for ur response.

Derek, i've got the solution for my problem and the query is as given below.

select 'data' = convert(varchar(100),(a.data)+''+(b.data)+''+(c.data)+''+(d.data))

from test_data a,test_data b,test_data c,test_data d

where ((b.seqno=a.seqno+1 and b.rowno=a.rowno+1)and(c.seqno=b.seqno+1 and c.rowno=b.rowno+1)and(d.seqno=c.seqno+1 and d.rowno=c.rowno+1))

How to Choose Method to Transfer Data from Production to Reporting database

My current project now is how to synchronize (data transfer) between
Production Database & Reporting Database using DTS, but I have no idea
how to do it and using which the best method. Need to know, that two
database have different structure tables - of course - and have a
thousand records inside, and our transaction working 24 hours, so
that's imposible to delete all records at Reporting Database and
replace with new data. The method that I've considered now is :
[vbcol=seagreen]
- 0 : haven't transfered at all
- 1 : the record have been modified
- 2 : have transfered to Reporting database
So I only need delete all records at Reporting Services which match
record at Production have flag 1, and then transfered all records that
have flag 0 and 1.
But the weakness of my method is how to synchronize for deleted
records? Should I save the primary key of deleted records, or is there
any other method?
Please give me suggestion/comment for my method.
Thanks
To determine what records have been deleted from MyTable in the Reporting
Database, you can left join MyTable from the Reporting Database (RD) back to
the Production Database (PD) on the primary key (pkey) and select
RD.MyTable.pkey where PD.Mytable.pkey is null.
Now, to delete these records from the Reporting version of MyTable:
delete from RD.MyTable where pkey in (the above subquery)
"Resant" <resant_v@.yahoo.com> wrote in message
news:1120701842.102486.317180@.f14g2000cwb.googlegr oups.com...
> My current project now is how to synchronize (data transfer) between
> Production Database & Reporting Database using DTS, but I have no idea
> how to do it and using which the best method. Need to know, that two
> database have different structure tables - of course - and have a
> thousand records inside, and our transaction working 24 hours, so
> that's imposible to delete all records at Reporting Database and
> replace with new data. The method that I've considered now is :
> - 0 : haven't transfered at all
> - 1 : the record have been modified
> - 2 : have transfered to Reporting database
> So I only need delete all records at Reporting Services which match
> record at Production have flag 1, and then transfered all records that
> have flag 0 and 1.
> But the weakness of my method is how to synchronize for deleted
> records? Should I save the primary key of deleted records, or is there
> any other method?
> Please give me suggestion/comment for my method.
> Thanks
>
|||I understand what you mean, but I've hundred records, so using that
query, I think is still have slow performance.
|||Actually, I would expect the query portion to run within a few seconds, if
the two tables are joined using an indexed column.
"Resant" <resant_v@.yahoo.com> wrote in message
news:1120784708.699397.48280@.f14g2000cwb.googlegro ups.com...
> I understand what you mean, but I've hundred records, so using that
> query, I think is still have slow performance.
>

How to Choose Method to Transfer Data from Production to Reporting database

My current project now is how to synchronize (data transfer) between
Production Database & Reporting Database using DTS, but I have no idea
how to do it and using which the best method. Need to know, that two
database have different structure tables - of course - and have a
thousand records inside, and our transaction working 24 hours, so
that's imposible to delete all records at Reporting Database and
replace with new data. The method that I've considered now is :
[vbcol=seagreen]
- 0 : haven't transfered at all
- 1 : the record have been modified
- 2 : have transfered to Reporting database
So I only need delete all records at Reporting Services which match
record at Production have flag 1, and then transfered all records that
have flag 0 and 1.
But the weakness of my method is how to synchronize for deleted
records? Should I save the primary key of deleted records, or is there
any other method?
Please give me suggestion/comment for my method.
ThanksTo determine what records have been deleted from MyTable in the Reporting
Database, you can left join MyTable from the Reporting Database (RD) back to
the Production Database (PD) on the primary key (pkey) and select
RD.MyTable.pkey where PD.Mytable.pkey is null.
Now, to delete these records from the Reporting version of MyTable:
delete from RD.MyTable where pkey in (the above subquery)
"Resant" <resant_v@.yahoo.com> wrote in message
news:1120701842.102486.317180@.f14g2000cwb.googlegroups.com...
> My current project now is how to synchronize (data transfer) between
> Production Database & Reporting Database using DTS, but I have no idea
> how to do it and using which the best method. Need to know, that two
> database have different structure tables - of course - and have a
> thousand records inside, and our transaction working 24 hours, so
> that's imposible to delete all records at Reporting Database and
> replace with new data. The method that I've considered now is :
>
> - 0 : haven't transfered at all
> - 1 : the record have been modified
> - 2 : have transfered to Reporting database
> So I only need delete all records at Reporting Services which match
> record at Production have flag 1, and then transfered all records that
> have flag 0 and 1.
> But the weakness of my method is how to synchronize for deleted
> records? Should I save the primary key of deleted records, or is there
> any other method?
> Please give me suggestion/comment for my method.
> Thanks
>|||I understand what you mean, but I've hundred records, so using that
query, I think is still have slow performance.|||Actually, I would expect the query portion to run within a few seconds, if
the two tables are joined using an indexed column.
"Resant" <resant_v@.yahoo.com> wrote in message
news:1120784708.699397.48280@.f14g2000cwb.googlegroups.com...
> I understand what you mean, but I've hundred records, so using that
> query, I think is still have slow performance.
>