• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 408
  • Last Modified:

DTS Export to Flat File

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
cbeaudry1
Asked:
cbeaudry1
  • 4
  • 2
  • 2
  • +2
2 Solutions
 
Jay ToopsCommented:
remove tvfaddress from the "FROM" line
its just a sql syntax error
jay
0
 
RickBeebeCommented:
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
 
Jay ToopsCommented:
You tried this and it failed?

what database provider are you using to specifiy your database connection?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
cbeaudry1Author Commented:
I guess we're getting closer. I ran the statement and now get a "duplicate addressID" error.
0
 
Jay ToopsCommented:
it shouldn't care ...

can u give me more info?
0
 
dstanley9Commented:
You've got two columns named AddressID.  Take one of them out since you're joining on that column.
0
 
dstanley9Commented:
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
 
Jay ToopsCommented:
duplicate addressid error should only occur when inserting into a table with a "Unique primary key" constraint
0
 
cbeaudry1Author Commented:
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
 
Anthony PerkinsCommented:
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now