Solved

DTS Export to Flat File

Posted on 2006-06-28
10
390 Views
Last Modified: 2013-11-30
I'm new to making statement in Enterprise Manager. I'm trying to run an export that joins addressid fields in two separate tables. Since DTS doesn't make joins through the wizard, I'm hitting a roadblock on the export. Here's what I got from the wizard:

select [TVFAddress].[AddressID], [TVFAddress].[City], [TVFUserGroup].[UserGroupID], [TVFUserGroup].[GroupName], [TVFUserGroup].[AddressID], [TVFUserGroup].[StoreType], [TVFUserGroup].[CustGroup]
from [TVFAddress],[TVFUserGroup]
order by [TVFAddress].[City], [TVFUserGroup].[GroupName], [TVFUserGroup].[UserGroupID], [TVFUserGroup].[StoreType], [TVFUserGroup].[CustGroup], [TVFUserGroup].[AddressID]

If I add

left join TVFAddress on TVFAddress.AddressID=TVFUserGroup.AddressID

after the "from" line, I get a syntax error with the following line at the bottom: "the provider does not support all the interfaces/methoids required by DTS."

help?
0
Comment
Question by:cbeaudry1
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 10

Accepted Solution

by:
Jay Toops earned 100 total points
ID: 17001528
remove tvfaddress from the "FROM" line
its just a sql syntax error
jay
0
 
LVL 3

Expert Comment

by:RickBeebe
ID: 17001557
This syntax fails?

SELECT [TVFAddress].[AddressID], [TVFAddress].[City], [TVFUserGroup].[UserGroupID], [TVFUserGroup].[GroupName], [TVFUserGroup].[AddressID], [TVFUserGroup].[StoreType], [TVFUserGroup].[CustGroup]
FROM [TVFAddress]
LEFT OUTER JOIN [TVFUserGroup]
ON TVFAddress.AddressID=TVFUserGroup.AddressID
ORDER BY [TVFAddress].[City], [TVFUserGroup].[GroupName], [TVFUserGroup].[UserGroupID], [TVFUserGroup].[StoreType], [TVFUserGroup].[CustGroup], [TVFUserGroup].[AddressID]
0
 
LVL 10

Expert Comment

by:Jay Toops
ID: 17001587
You tried this and it failed?

what database provider are you using to specifiy your database connection?
0
 

Author Comment

by:cbeaudry1
ID: 17001657
I guess we're getting closer. I ran the statement and now get a "duplicate addressID" error.
0
 
LVL 10

Expert Comment

by:Jay Toops
ID: 17001728
it shouldn't care ...

can u give me more info?
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 25

Expert Comment

by:dstanley9
ID: 17001778
You've got two columns named AddressID.  Take one of them out since you're joining on that column.
0
 
LVL 25

Assisted Solution

by:dstanley9
dstanley9 earned 25 total points
ID: 17001788
So it will be:

SELECT [TVFAddress].[AddressID], [TVFAddress].[City], [TVFUserGroup].[UserGroupID], [TVFUserGroup].[GroupName], [TVFUserGroup].[StoreType], [TVFUserGroup].[CustGroup]
FROM [TVFAddress]
LEFT OUTER JOIN [TVFUserGroup]
ON TVFAddress.AddressID=TVFUserGroup.AddressID
ORDER BY [TVFAddress].[City], [TVFUserGroup].[GroupName], [TVFUserGroup].[UserGroupID], [TVFUserGroup].[StoreType], [TVFUserGroup].[CustGroup], [TVFAddress].[AddressID]
0
 
LVL 10

Expert Comment

by:Jay Toops
ID: 17001804
duplicate addressid error should only occur when inserting into a table with a "Unique primary key" constraint
0
 

Author Comment

by:cbeaudry1
ID: 17001850
There were two addreddid in the select statement. I figured it out by running the following:

SELECT [TVFAddress].[AddressID] as add1, [TVFAddress].[City], [TVFUserGroup].[UserGroupID], [TVFUserGroup].[GroupName], [TVFUserGroup].[AddressID], [TVFUserGroup].[StoreType], [TVFUserGroup].[CustGroup]
FROM [TVFAddress]
LEFT OUTER JOIN [TVFUserGroup]
ON TVFAddress.AddressID=TVFUserGroup.AddressID
WHERE  [TVFUserGroup].[StoreType] <> ''
ORDER BY [TVFAddress].[City], [TVFUserGroup].[GroupName], [TVFUserGroup].[UserGroupID], [TVFUserGroup].[StoreType], [TVFUserGroup].[CustGroup], [TVFUserGroup].[AddressID]
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17002653
Correct.  All calculated columns need to be aliased and all column names (or aliases) have to be unique or DTS will fail.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now