Solved

DTS Export to Flat File

Posted on 2006-06-28
10
394 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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
 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Can I skip a node in XML? 9 35
sql server cross db update 2 23
install sql server management studio 2008 express error 3 34
Neglected Questions 3 13
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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 combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

730 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