Output Column Width not refected in the Flat File that is created using a Flat File Destination?

I am transferring data from an OLEDB source to a Flat File Destination and I want the column width for all of the output columns to 30 (max width amongst the columns selected), but that is not refected in the Fixed Width Flat File that got created. The outputcolumnwidth seems to be the same as the inputcolumnwidth. Is there any other setting that I am possibly missing or is this a possible defect?

Any inputs will be appreciated.

M.Shah

InputColumnWidth represents the width in the file and OutputColumnWidth is the width in the data flow.

This may sound confusing for the case of Flat File destination connection, but the same connection manager object is used for sources and destination. There is also the description of these properties in the property grid of the Flat File Connection Manager UI.

So, to conclude the InputColumnWidth is the one controlling the size of the columns in the destination file.

Thanks.

|||

Thanks for the information. That was definitely helpful to figure out why the OutputColumnWidth was not reflected in the flat file.

Output Column Width not refected in the Flat File that is created using a Flat File Destination?

I am transferring data from an OLEDB source to a Flat File Destination and I want the column width for all of the output columns to 30 (max width amongst the columns selected), but that is not refected in the Fixed Width Flat File that got created. The outputcolumnwidth seems to be the same as the inputcolumnwidth. Is there any other setting that I am possibly missing or is this a possible defect?

Any inputs will be appreciated.

M.Shah

InputColumnWidth represents the width in the file and OutputColumnWidth is the width in the data flow.

This may sound confusing for the case of Flat File destination connection, but the same connection manager object is used for sources and destination. There is also the description of these properties in the property grid of the Flat File Connection Manager UI.

So, to conclude the InputColumnWidth is the one controlling the size of the columns in the destination file.

Thanks.

|||

Thanks for the information. That was definitely helpful to figure out why the OutputColumnWidth was not reflected in the flat file.

sql

OUTER JOIN with multiple tables and a plus sign?

I am trying to select specific columns from multiple tables based on a
common identifier found in each table.

For example, the three tables:

PUBACC_AC
PUBACC_AM
PUBACC_AN

each have a common column:

PUBACC_AC.unique_system_identifier
PUBACC_AM.unique_system_identifier
PUBACC_AN.unique_system_identifier

What I am trying to select, for example:

PUBACC_AC.name
PUBACC_AM.phone_number
PUBACC_AN.zip

where the TABLE.unique_system_identifier is common.

For example:

--------------
PUBACC_AC
=========
unique_system_identifier name
1234 JONES

--------------
PUBACC_AM
=========
unique_system_identifier phone_number
1234 555-1212

--------------
PUBACC_AN
=========
unique_system_identifier zip
1234 90210

When I run my query, I would like to see the following returned as one
blob, rather than the separate tables:

--------------------
unique_system_identifier name phone_number zip
1234 JONES 555-1212 90210
--------------------

I think this is an OUTER JOIN? I see examples on the net using a plus
sign, with mention of Oracle. I'm not running Oracle...I am using
Microsoft SQL Server 2000.

Help, please?

P. S. Will this work with several tables? I actually have about 15
tables in this mess, but I tried to keep it simple (!??!) for the above
example.

Thanks in advance for your help!

NOTE: TO REPLY VIA E-MAIL, PLEASE REMOVE THE "DELETE_THIS" FROM MY E-MAIL
ADDRESS.

Who actually BUYS the cr@.p that the spammers advertise, anyhow?!!!
(Rhetorical question only.)You do the following:

SELECT PUBACC_AC.Name + PUBACC_AM.Phone_number + PUBACC_AN.zip
FROM PUBACC_AC
INNER JOIN PUBACC_AM
ON PUBACC_AM.unique_col = PUBACC_AC.unique_col
INNER JOIN PUBACC_AN
ON PUBACC_AN.unique_col = PUBACC_AC.unique_col

That's it - you do inner join, if you want only those records, for which
unique_col value exists in all 3 tables.
Or, if you replace "INNER JOIN" with "FULL JOIN", which is the same as OUTER
JOIN in Oracle, you will get
as meny records as number of unique_col values.

Thats it!

Hope it helped,
Andrey aka Muzzy

"TeleTech1212" <tele_tech1212DELETE_THIS@.yahoo.com> wrote in message
news:Xns9556E99BEE9B4teletech1212DELETETH@.207.115. 63.158...
> I am trying to select specific columns from multiple tables based on a
> common identifier found in each table.
> For example, the three tables:
> PUBACC_AC
> PUBACC_AM
> PUBACC_AN
> each have a common column:
> PUBACC_AC.unique_system_identifier
> PUBACC_AM.unique_system_identifier
> PUBACC_AN.unique_system_identifier
>
> What I am trying to select, for example:
> PUBACC_AC.name
> PUBACC_AM.phone_number
> PUBACC_AN.zip
> where the TABLE.unique_system_identifier is common.
>
> For example:
> --------------
> PUBACC_AC
> =========
> unique_system_identifier name
> 1234 JONES
> --------------
> PUBACC_AM
> =========
> unique_system_identifier phone_number
> 1234 555-1212
> --------------
> PUBACC_AN
> =========
> unique_system_identifier zip
> 1234 90210
>
> When I run my query, I would like to see the following returned as one
> blob, rather than the separate tables:
> --------------------
> unique_system_identifier name phone_number zip
> 1234 JONES 555-1212 90210
> --------------------
>
> I think this is an OUTER JOIN? I see examples on the net using a plus
> sign, with mention of Oracle. I'm not running Oracle...I am using
> Microsoft SQL Server 2000.
> Help, please?
> P. S. Will this work with several tables? I actually have about 15
> tables in this mess, but I tried to keep it simple (!??!) for the above
> example.
> Thanks in advance for your help!
> NOTE: TO REPLY VIA E-MAIL, PLEASE REMOVE THE "DELETE_THIS" FROM MY E-MAIL
> ADDRESS.
> Who actually BUYS the cr@.p that the spammers advertise, anyhow?!!!
> (Rhetorical question only.)

Out Join Syntax

Hello,

I need to write and Ansi Outer Join that has 2 columns from the same table.

I keep getting

Server: Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "dim_person.person_key" could not be bound.

here is what the code looks like. any help is appreciated.

select ...

from dim_day_of_year
, dim_company
, dim_currency
, dim_account
, dim_person
, ods_conference
left outer join fact_usage_alloc fua1 on ods_conference.conf_key=fua1.conf_key
left outer join fact_usage_alloc fua2
on dim_person.person_key=fua2.requestor_person_key

where...from ...
, ods_conference
left outer
join fact_usage_alloc fua
on fua.conf_key = ods_conference.conf_key
and fua.requestor_person_key = dim_person.person_key|||That certainly makes sense to me but produces a syntax error :mad:

left outer join fact_usage_alloc fua
on fua.conf_key=ods_conference.conf_key
and on fua.requestor_person_key=dim_person.person_key
where fua.company_key = dim_company.company_key...........

Thanks for the reply|||you're welcome

you can't say "and on"|||hmmm. Still getting the bind error. I even cut and pasted your example in and I get the same thing. I didn't realize mssql was so particular :S|||okay, here's the deal: re-write your other comma-delimited table joins as INNER JOINs

i bet the error goes away ;)|||Mixing non ansi and ansi forms is just a bad idea

Plus, unless I missing it, your outter join tables don't seem to be joing to anything

Plus, you really should get into good formatting, it will make your like so much easier

SELECT ColumnList
FROM dim_day_of_year a
INNER JOIN dim_company b
ON b.col = ?.col
INNER JOIN dim_currency c
ON c.col = ?.col
INNER JOIN dim_account d
ON d.col =?.col
INNER JOIN dim_person e
ON e.col = ?.col
INNER JOIN ods_conference f
ON f.col = ?.col
LEFT JOIN fact_usage_alloc g
ON e.conf_key = f.conf_key -- This one makes no sense
LEFT JOIN fact_usage_alloc h
ON d.person_ke = g.requestor_person_key -- Nor does this one|||okay, here's the deal: re-write your other comma-delimited table joins as INNER JOINs

i bet the error goes away ;)

You funny

The message you have enetered is too short|||You funnyno, i was serious -- i bet the error goes away because of how the FROM clause is parsed

the explicit JOINS are probably being interpreted first, before being combined with the comma-delimited tables

sort of like * and / are done before + and -

thus, in this local context:
ods_conference
left outer
join fact_usage_alloc fua
on fua.conf_key = ods_conference.conf_key
and fua.requestor_person_key = dim_person.person_keythis explains the error message -- "dim_person.person_key" could not be bound

dim_person hasn't been parsed as a table yet|||Thanks for the effort guys. This was a quick test to see how easy it would be to convert a legacy system to MSSQL. I think we got a portion of the answer.

Thanks Again.sql

Other Best Practices

As near as I can tell, the SQL Best Practices Analyzer does not have rules
for the following:
row size greater than 8 K
# of columns in a table greater than x
row size greater than x
FK Constraints that are disabled
Other Constraints that are disabled
Triggers that are disabled
Anyone know of a way to analyze this?
Thanks!
Michelle
Thanks Michelle for your feedback. Will definitely consider some of these
for the next release of BPA.
- Christian
___________________________
Christian Kleinerman
Program Manager, SQL Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"michelle" <michelle@.nospam.com> wrote in message
news:um%23%23Q9WhEHA.4064@.TK2MSFTNGP12.phx.gbl...
> As near as I can tell, the SQL Best Practices Analyzer does not have rules
> for the following:
> row size greater than 8 K
> # of columns in a table greater than x
> row size greater than x
> FK Constraints that are disabled
> Other Constraints that are disabled
> Triggers that are disabled
> Anyone know of a way to analyze this?
> Thanks!
> Michelle
>

OT: Database Documentation Strategy

I'm creating a new SQL Server 2005 database with about 110 tables, some of
which have dozens of columns. I would like to document this database. What I
mean by that is I want a document that contains at least the following:
1. Each table name, and each column name per table.
2. For each column: data type, length, default value (if any), constraints
3. For each table: primary key column(s), indexes, foreign key constraints.
4. Brief text description of the data stored in the table, and business use
of the data (where not obvious).
Before I go and create an Excel spreadsheet that contains all of the above I
was wanting to know what some of you do. Are you all using Excel for this
sort of thing? Is there something more efficient? If so, what is it or what
do you do or recommend? Can I automate some of this?
FWIW: I'm already documenting the data model (tables and relationships etc)
in a Visio diagram. Now I need to document the table detail.
Thanks!Mark (A@.B.COM) writes:
> I'm creating a new SQL Server 2005 database with about 110 tables, some
> of which have dozens of columns. I would like to document this database.
> What I mean by that is I want a document that contains at least the
> following:
> 1. Each table name, and each column name per table.
> 2. For each column: data type, length, default value (if any), constraints
> 3. For each table: primary key column(s), indexes, foreign key
> constraints.
> 4. Brief text description of the data stored in the table, and business
> use of the data (where not obvious).
> Before I go and create an Excel spreadsheet that contains all of the
> above I was wanting to know what some of you do. Are you all using Excel
> for this sort of thing? Is there something more efficient? If so, what
> is it or what do you do or recommend? Can I automate some of this?
In our shop we use PowerDesigner from Sybase. This is a full-blown
data-modelling tool. The way I use it is that I first define the tables
in PowerDesigner and then generate a script from PowerDesigner that I
then post-process with my own Perl tools to get files formatted and
structuted to my own liking. PowerDesigner permits you to enter comments
for tables and columns. I can also generate reports from the data model,
either in RTF or HTML. What we actually use is the HTML reports, and
to make them a little nicer, I have a post-processing tool here as well.
PowerDesigner permits you to configure the reports, so that you can decide
what goes into it and what does not.
Since you already have the database in place, you may want to avoid to
enter all data manually - stay , you can do reverse engineering. Once
you have the data model in shape, you should start working in the other
direction.
The main competitors to PowerDesigner are ERwin (from Computer Associates)
and Embrocadero. I have not looked very closely on them, but they should
have the same capabilities as PowerDesigner.
All these tools comes with quite a price tag, I should hasten to add.
But a single-user license of PowerDesigner with only Physical Atchitect
is certainly worth the money.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx