Description Metadata

When creating columns in MS-SQL there is a description field. How can I quer
y
this description field? I know how to return the Table Name, Column Name,
Data Type, Is_Nullable, etc. but I don’t know how to return the Descriptio
n.> When creating columns in MS-SQL there is a description field.
This is only ifyou create the columns in Enterprise Manager *and* choose to
use that property. Personally, I don't think you should do either, mostly
because your CREATE TABLE statements should be something that you can
script, modify, store in source control, etc.
In any case:
http://www.aspfaq.com/2244|||Aaron, good point! So where would you store the description of individual
columns? Would you create separate tables to store the descriptions of the
tables and the columns?|||> Aaron, good point! So where would you store the description of individual
> columns? Would you create separate tables to store the descriptions of the
> tables and the columns?
Why do you feel the description of your database schema needs to be stored
in the database?
We create separate human readable documentation. This allows us much more
freedom in how we are describing the schema, e.g. definitions > 64
characters, diagrams, tables, etc.
A|||I was thinking of creating a web app so that the descriptions, rules, etc.
can be documented, searched, and studied by several disparate groups. As of
now the database is completely undocumented. Then to complicate matters even
more, the knowledge of this database is scattered amongst many different
groups and individuals who won’t always work together. I keep running into
columns named TX5_RRT_SDescr and no one knows what the hell that column is
used for. The DBA is too busy to answer most questions and his answers are
too flipped to be of much use.
What form in your documentation in?|||Most of ours are in Word format. But you can make your job easier using an
extraction tool, for example Enterprise Architect.
Hey, if the DBA is too busy to answer questions that need to be answered,
why do you think he'd be willing to populate some table with the answers?
"Tome73" <Tome73@.discussions.microsoft.com> wrote in message
news:200AB1D7-AD01-426A-B580-7CE42716BBDE@.microsoft.com...
>I was thinking of creating a web app so that the descriptions, rules, etc.
> can be documented, searched, and studied by several disparate groups. As
> of
> now the database is completely undocumented. Then to complicate matters
> even
> more, the knowledge of this database is scattered amongst many different
> groups and individuals who won’t always work together. I keep running
> into
> columns named TX5_RRT_SDescr and no one knows what the hell that column is
> used for. The DBA is too busy to answer most questions and his answers are
> too flipped to be of much use.
> What form in your documentation in?|||Lol, he won’t! The rest of us peons will have to collaboratively piece thi
ngs
together. There are about 5 of us who have volunteered for this project and
I
just need to figure out how to best do it. I have a zero budget to work with
.
Ok then your suggestion is not to store the description in database
properties. Thanks.
The answer to my original post is:
Where Table name is ‘Admin_Users’ and the column name is ‘User_ID’
SELECT name, CONVERT(varchar(2000), [value]) AS Description
FROM ::fn_listextendedproperty(NULL, 'user', 'dbo', 'table',
'admin_Users', 'Column', 'User_ID')|||> The answer to my original post is:
> Where Table name is ‘Admin_Users’ and the column name is ‘User_ID’
> SELECT name, CONVERT(varchar(2000), [value]) AS Description
> FROM ::fn_listextendedproperty(NULL, 'user', 'dbo', 'table',
> 'admin_Users', 'Column', 'User_ID')
Yep, I posted a link to code samples earlier:
http://www.aspfaq.com/2244|||You could store your meta data in SQL Server tables and then develop your
own custom GUI and reports around it, but there are many 3rd party
applications would do a better job.
Tools like ERWin, Viseo (or even Enterprise Managers Diagramer) can be used
to create annotated diagrams of the database model
http://www.databaseanswers.com/modelling_tools.htm
Reporting Services 2005 has a Model Designer that provides end users with a
high level view of the database model for use with Report Builder.
http://www.devx.com/dbzone/Article/28047/1954?pf=true.
"Tome73" <Tome73@.discussions.microsoft.com> wrote in message
news:200AB1D7-AD01-426A-B580-7CE42716BBDE@.microsoft.com...
>I was thinking of creating a web app so that the descriptions, rules, etc.
> can be documented, searched, and studied by several disparate groups. As
> of
> now the database is completely undocumented. Then to complicate matters
> even
> more, the knowledge of this database is scattered amongst many different
> groups and individuals who won't always work together. I keep running into
> columns named TX5_RRT_SDescr and no one knows what the hell that column is
> used for. The DBA is too busy to answer most questions and his answers are
> too flipped to be of much use.
> What form in your documentation in?|||Aaron, yes you did and I missed it at first. I found
http://developer.com/db/article.php/3361751 which is basically the same.
Hey thanks Aaron for all your help and insight.sql

Description Column in Table Creation

Hello,
When you add a column you can put in a description. What
is the maximum size of this ?
Thanks
Peteruse extended properties, plenty of help in BOL
regards,
mark baekdal
www.dbghost.com
>--Original Message--
>Hello,
>When you add a column you can put in a description. What
>is the maximum size of this ?
>Thanks
>Peter
>.
>|||From BOL
[ @.value = ] { 'value' }
Is the value to be associated with the property. value is sql_variant, with
a default of NULL. The size of value may not be more than 7,500 bytes;
otherwise, SQL Server raises an error.
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:0c8801c3d5de$45f3d6d0$a101280a@.phx.gbl...
> Hello,
> When you add a column you can put in a description. What
> is the maximum size of this ?
> Thanks
> Peter|||Thanks Guys
>--Original Message--
>Hello,
>When you add a column you can put in a description. What
>is the maximum size of this ?
>Thanks
>Peter
>.
>

Desc as column name

Hi,
When I run the following sql statement
CREATE TABLE t1 (Desc varchar(50))
I get an error saying Incorrect syntax near keyword Desc.
However, I can run the same command on other database engines.
Where can I find situations like this where an sql statement will work
on other databases but not SqlServer?
The statement
CREATE TABLE t1 ([Desc] varchar(50))
works fine.
Do you suggest that I enclose column names in square brackets ([]) for
all sql statements (select, insert, update, create, delete etc)?
Regards,
PrakashSince you mention other database engines, you should get into the habit of using the ANSI SQL
compliant way to delimit identifiers: double-quotes and not square brackets.
There's a list in Books Online of all the reserved keywords.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<prakash_bande@.hotmail.com> wrote in message
news:1189098048.395246.253570@.w3g2000hsg.googlegroups.com...
> Hi,
> When I run the following sql statement
> CREATE TABLE t1 (Desc varchar(50))
> I get an error saying Incorrect syntax near keyword Desc.
> However, I can run the same command on other database engines.
> Where can I find situations like this where an sql statement will work
> on other databases but not SqlServer?
> The statement
> CREATE TABLE t1 ([Desc] varchar(50))
> works fine.
> Do you suggest that I enclose column names in square brackets ([]) for
> all sql statements (select, insert, update, create, delete etc)?
>
> Regards,
> Prakash
>|||Desc is a reserved keyword in SQL Server. It's short for 'descending' and is
used in syntax such as ORDER BY column_name DESC. If you want to use
reserved words as object or column names, you must delimit the name by
either using [ ] as you show below are by using double quotes " ". Keep in
mind that you will need to delimit this name in every statement in which you
reference the name. You might consider just choosing a longer (and more
descriptive) name such as "Description".
For a list of reserved keywords, see this Books Online topic:
http://msdn2.microsoft.com/en-us/library/ms189822.aspx
For more information about delimiting names see this Books Online topic
http://msdn2.microsoft.com/en-us/library/ms176027.aspx
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
<prakash_bande@.hotmail.com> wrote in message
news:1189098048.395246.253570@.w3g2000hsg.googlegroups.com...
> Hi,
> When I run the following sql statement
> CREATE TABLE t1 (Desc varchar(50))
> I get an error saying Incorrect syntax near keyword Desc.
> However, I can run the same command on other database engines.
> Where can I find situations like this where an sql statement will work
> on other databases but not SqlServer?
> The statement
> CREATE TABLE t1 ([Desc] varchar(50))
> works fine.
> Do you suggest that I enclose column names in square brackets ([]) for
> all sql statements (select, insert, update, create, delete etc)?
>
> Regards,
> Prakash
>sql

Desapering rows

Hi.

I have an mdx query which return some rows and for some values there are null values for 1 column (it's ok). But when the report is rendered have disapeared all these rows. I need to show these rows because they have value for other columns.

Thanks in advance.I refresh the datasource by editing the query and refresh and that's all.

I don't know what happend.

Dervied Column - Expression?

Hi:

I have a Dervied Column Component, in which there is a column called RecStatusCode. The criteria to give a value to that column is below in SQL:

(SELECT CASE CertParticipant
WHEN 'Y' THEN 'C'
WHEN NULL THEN 'A'
END
FROM [dbo].[SchoolCertRequest]
WHERE SchoolID = (SELECT SchoolCode FROM [WebStrategy].[LAF].[LoanApplication] WHERE LoanApplicationID = @.LoanApplicationID) and SchoolBranch = (SELECT BranchCode FROM [WebStrategy].[LAF].[LoanApplication] WHERE LoanApplicationID = @.LoanApplicationID) AND LoanType=(SELECT LoanApplicationTypeCode FROM [WebStrategy].[LAF].[LoanApplication] WHERE LoanApplicationID = @.LoanApplicationID))

My question is how can I use this SQL in that expression field, or is it possible to have a user variable, with the above sql being the criteria, and the value could be used in the derived column, by just using the variable in the expression. In either case, I hope you understand what I am trying to accomplish. The reason why its like this is because currently in my Data Flow Task, i Have a OLEDB source which returns 25+ column values, the source connects to this derived column item, and then finally the destination oledb where the mapping takes place. So basically I am trying to avoid MERGE JOIN, because i tried using it , and it complicated the whole thing much more then it really needs to be. Like the RecStatusCode column, i have 5 more columns which are dervied, and the values are coming from a SQL statement similar to above. I really need some help here, and I have not seen anything in reference to this in BOL or any articles online. Thanks in advance.

I think a combination of Replace and ISNull functions will do the trick.

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

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

|||Is there a way to put SQL queries in a user variable? I am just trying to figure out, whats the best way to do this? Can someone please advise. Thanks.|||Try doing that in an OLE DB command... You can't execute a SQL statement in a variable. You can store a SQL statement in a variable as it's just a string. You can even make that statement dynamic using variable expressions.|||

Please disregard my first answer; I thought you were asking how to translate the case statement into a SSIS expression.

Anyway; have you consider to include that query with the query in your source component; assuming all tables are in the same DB it would work just fine. In case the data is in different DB/Server; you could use a staging table to load the data on a common place and then have one single query.

Otherwise a second source component with a merge join may be an option.

deriving a new column from another derived column

In a Derived Column data flow transformation, why can't I refer to a derived column (added in that same transformation) in the expression for another derived column? It seems I am forced to chain 2 DC data flows, just to do something as conceptually simple as:

a = x + y; b = a2

On a related note: Can I define a variable that is scoped to each row? Can I bind a variable in an expression to avoid creating a new row, e.g.

let a = x + y; a2

as the expression for new row b?

Kevin Rodgers wrote:

In a Derived Column data flow transformation, why can't I refer to a derived column (added in that same transformation) in the expression for another derived column? It seems I am forced to chain 2 DC data flows, just to do something as conceptually simple as:

a = x + y; b = a2

Kevin,

God yeah. I so wish you could do that. Seems like such simple funcitonality doesn't it?

I have requested it here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127010

Please please please click through and vote for it.

Kevin Rodgers wrote:

On a related note: Can I define a variable that is scoped to each row? Can I bind a variable in an expression to avoid creating a new row, e.g.

let a = x + y; a2

as the expression for new row b?

No, you can't do that!

-Jamie

|||You can't do it in derived column, but it is very easy to do in script component - the component generates the row accessors, so the amount of code you need to write is almost the same as in derived column transform.|||

I haven't used the script component before -- I haven't even written any scripts. Could you show me a simple example of generating row accessors etc. as you've suggested?

Thanks!

|||

It's a good idea Jamie.

Do keep in mind that it adds another layer of complexity. Along with some other difficulties, the user would have to specify an order of execution for the expressions, and the UI would need a way too enable and maintain that. Chaining multiplie derived columns, while not as convenient or as pretty to look at, makes it abundantly clear what order the intermediate expressions occur in and eases some usability concerns.

It's something to look at for the future, though, certainly. Keep the suggestions coming!

Thanks
Mark

|||

Kevin Rodgers wrote:

I haven't used the script component before -- I haven't even written any scripts. Could you show me a simple example of generating row accessors etc. as you've suggested?

Thanks!

Just add a script transform where you would use Derived Column transform, select Transform in the first dialog. Check the columns you want to use in your expressions. Go to Inputs and Outputs tab, add output columns to Output 0.

Now edit the script, and type your code inside Input0_ProcessInputRow function. I quickly setup a "validation" for AdwentureWorks DB (the transform adds two colums CalculatedColumn and TotalOK to each row):

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim t As Decimal
t = Row.OrderQty * Row.UnitPrice * (1 - Row.UnitPriceDiscount)
Row.CalculatedColumn = t

If (t = Row.LineTotal) Then
Row.TotalOK = True
Else
Row.TotalOK = False
End If

End Sub

So the main difference in expressions is that you have to add "Row." prefix to column name :) Of course, the syntax is different, as the script transform uses full VB.NET language, which gives you ability to create temporary variables among other things.

sql

Derived table not updatable

I got an error as follows:
Derived table 'A' is not updatable because a column of the derived table is derived or constant.
when I tried to run this query:
update A set MonthsUnbilled =99999888
FROM (select MonthsUnbilled from dbo.vw_MasterView
WHERE (RecordID =8377396)) A
This is a simplified query in order to pinpoint the culprit. I know I don't need to use a derived table if the real query is this simple.

Thanks in advance!It would appear that either the dbo.vw_MasterView.MonthsUnbilled column is either a constant, a computed column, or derived from one of them.

What is the DDL for dbo.vw_MasterView? What is the DDL for the table that contains the column referenced in dbo.vw_MasterView.MonthsUnbilled ?

-PatP|||Thank you Pat for your response.
I can update dbo.vw_MasterView.MonthsUnbilled without using a derived table. In other words, the following query runs just fine.
update dbo.vw_MasterView set MonthsUnbilled =99999888
FROM dbo.vw_MasterView
WHERE (RecordID =8377396)
But if I use a derived table like this:
update A set MonthsUnbilled =99999888
FROM (select MonthsUnbilled from dbo.vw_MasterView
WHERE (RecordID =8377396)) A
I get that error.|||Is there either a PRIMARY KEY or a UNIQUE constraint on the table column that eventually populates dbo.vw_MasterView.RecordID ? I think that there needs to be a constraint of one of those two types to make the virtualized view (the derived table created from an existing view) updatable.

-PatP|||Pat, you're right. RecordID is the primary key and an identity field. But is this the reason why I got the error?

Derived table and adding another column problem

Hi
I have this query below, which I'm trying to add and group data on the w
number (ISO w, taken from Books online) fn_getISOW returns an integer
and works independently of this query. This query also works as I want when
there is no wno invloved (the 3 places)
The error I get is "Invalid column name 'wno'", I think what I'm doing is
a "copy" of what has been done in the count(case.....) as xx
Can anyone see what I'm doing wrong and how to fix it?
regards
Henry
declare @.ext char(4)
declare @.site int
declare @.calltype char(1)
SET DATEFORMAT mdy
set @.site = 1
set @.calltype = 'E'
set @.ext = '6810'
SELECT wno***, network, centre, center, team, total, besvaret, optaget,
opgivet, ubesvaret
FROM (
SELECT network, centre, center, team,
COUNT(*) AS total,
COUNT(CASE WHEN durationofconversation > 0 THEN 1
END) AS besvaret,
COUNT(CASE WHEN durationofconversation = 0 AND
releasecause = 'OC' THEN 1 END) AS optaget,
COUNT(CASE WHEN durationofconversation = 0 AND
releasecause = 'RL' AND durationofcall < 40 THEN 1 END) AS opgivet,
COUNT(CASE WHEN durationofconversation = 0 AND
durationofcall >= 40 THEN 1 END) AS ubesvaret,
tcv2.dbo.fn_getISOw(DATECALLINITIATED) as wno
***
FROM V2tickets
INNER JOIN
(SELECT s.serie, a.nodename as network, cs.nodename as
centre, c.nodename as center, t.nodename as team
FROM areas a, centers cs, center c, teams t, series s
WHERE (t.nodeid = 1 or t.nodeid=8 or t.nodeid=2 or
t.nodeid=72 or t.nodeid=73) and a.nodeid=cs.parentid and
cs.nodeid=c.parentid and c.nodeid=t.parentid and t.nodeid=s.parentid) AS H
ON V2tickets.digits = H.serie
WHERE siteid = @.site
AND calltype = @.calltype
AND LEN(digits) = 4
GROUP BY wno***, network, centre, center, team) AS D
ORDER BY 1Try this
SELECT wno***, network, centre, center, team, total, besvaret,
optaget,
opgivet, ubesvaret
FROM (
SELECT network, centre, center, team,
COUNT(*) AS total,
COUNT(CASE WHEN durationofconversation > 0
THEN 1
END) AS besvaret,
COUNT(CASE WHEN durationofconversation = 0 AND
releasecause = 'OC' THEN 1 END) AS optaget,
COUNT(CASE WHEN durationofconversation = 0 AND
releasecause = 'RL' AND durationofcall < 40 THEN 1 END) AS opgivet,
COUNT(CASE WHEN durationofconversation = 0 AND
durationofcall >= 40 THEN 1 END) AS ubesvaret,
tcv2.dbo.fn_getISOw(DATECALLINITIATED) as
wno
***
FROM V2tickets
INNER JOIN
(SELECT s.serie, a.nodename as network, cs.nodename as
centre, c.nodename as center, t.nodename as team
FROM areas a, centers cs, center c, teams t, series s
WHERE (t.nodeid = 1 or t.nodeid=8 or t.nodeid=2 or
t.nodeid=72 or t.nodeid=73) and a.nodeid=cs.parentid and
cs.nodeid=c.parentid and c.nodeid=t.parentid and t.nodeid=s.parentid)
AS H
ON V2tickets.digits = H.serie
WHERE siteid = @.site
AND calltype = @.calltype
AND LEN(digits) = 4
) AS D
GROUP BY wno***, network, centre, center, team ORDER BY 1
Madhivanan|||> Try this
> Madhivanan
>
Hi
No I'm sorry that doesn't do the trick either :(
regards
Henry|||> > Try this
> Hi
> No I'm sorry that doesn't do the trick either :(
> regards
> Henry
Solved... I had to use the same syntax in the group by part since wno is
not know at that time.
regards
Henrik

Derived Shape - Replacing a column with itself.

Hi there,

I have a derived column shape in which I replace a column with an expression.

The expression is an IF statement - a true result sets a value for the column and a false result just uses the existing value of the column (ie it replaces it with itself)

Like this.

ColumnName DerivedColumn Expression

ColumnA Replace 'ColumnA' ColumnB != ColumnC ? "y" : ColumnA

But whenever, the condition is NOT met, ColumnA is set to NULL!!!!

Does this mean that the column value is deleted before the Expression is applied?

If this is how it is meant to work, then does anyone have a way of doing what I want to do without creating extra columns in the dataset?

Cheers.

I'm wondering if there are some data typing issues here. Wha is the type of ColumnA?

-Jamie

|||

Hi Jamie,

It's just a unicode string.

Dave.

|||

I mean in the pipeline. Is it a DT_WSTR?

if so, ensure you don't have any implicit conversions going on. i.e. explicitly cast "Y" as a DT_WSTR.

Also, first try to get this working as a new column rather than replacing ColumnA. See if you exhibit the same problems in that scenario.

-Jamie

|||

Jamie,

in my example setting Column A to the value "y" works fine. It's setting Column A to itself (ie II just want it to retain it's original value) which is the problem.

Do you suggest I explicitly cast the ColumnA in the expression? So with my example....

ColumnName DerivedColumn Expression

ColumnA Replace 'ColumnA' ColumnB != ColumnC ? "y" : CAST(ColumnA...)

|||

Dave,

No, that's not quite what i meant.

Try this:

DerivedColumn Expression

Replace 'ColumnA' ColumnB != ColumnC ? (DT_WSTR)"y" : ColumnA

My second suggestion was to see if this worked first:

DerivedColumn Expression

Add as new column ColumnB != ColumnC ? "y" : ColumnA

I'm clutching at straws a little bit but if I were you I would definately try to recreate the problem by adding it as a new column rather than replacing ColumnA.

-Jamie

|||

Hi Jamie,

adding a column works fine. It's replacing an exising column that has the "problem".

I should point out as well that the issue only arises when you use an IF condition in the expression.

So this is OK

DerivedColumn Expression

Replace ColumnA ColumnA+"Hello"

This will replace ColumnA with what was in ColumnA + "Hello"

This is NOT OK

DerivedColumn Expression

Replace ColumnA ColumnB != ColumnC ? "y" : ColumnA

So if ColumnB = ColumnC, then ColumnA is set to NULL - NOT what ColumnA was before the expression was applied.

I reckon its a bug.

|||

Hi Dave,

Can you post a simple repro that doesn't reply on external data sources (i.e. just create the same data using a script source component) and then post it up here?

To post up here, just copy the contents of your .dtsx file into your reply.

Thanks

Jamie

|||

Sure,

I'll do it tomorrow.

Speak then.

|||Have you checked if Column B or C is NULL? If so you will always get NULL out. Example here http://wiki.sqlis.com/default.aspx/SQLISWiki/Expressions.html|||

You can quickly test for what Darren is saying by changing your expression like this:

false ? "y" : ColumnA

If ColumnB or ColumnC are NULL, and you want to fail the comparison in that case,you can make your expression like this:

!ISNULL(ColumnB) && !ISNULL(ColumnC) && ColumnB != ColumnC ? "y" : ColumnA

If you want the condition to do NULL comparisions (that is, you want ColumnB != ColumnC to return true if one is NULL and the other is not, you might need something more like:

(ISNULL(ColumnB) != ISNULL(ColumnC)) || !ISNULL(ColumnB) && !ISNULL(ColumnC) && ColumnB != ColumnC ? "y" : ColumnA

|||

Your right Darren - it was the NULL values. aaargh!!!

Thanks for everyone's input.

Derived Columns in one to many relationships

I'm trying to write a query that concatenates multiple records into one
derived column. Let's say I have an author (Joe Writer) who has written
three books (Book 1, Book2 and Book 3). The author is in tblAuthors, his
books are in the tblBooks and they are joined by the AuthorID field
(number). If I use a simple select query to give me the author name and the
title, I will get three records, one for each book written.

What I want is to have all three books combined into one derived column. So
if I do the select statement, I will get one column with the author name,
and the second column will put together all three names of the book
separated by a column. So it will look like:

Author Title

Joe Writer Book 1, Book 2, Book 3,

Rather than having it appear as 3 records:

Joe Writer Book 1
Joe Writer Book 2
Joe Writer Book 3

Could someone help me with the SQL involved in this?

Thanks for the help.

Cheers,

MikeOne approach is shown in http://www.mvps.org/access/modules/mdl0008.htm at
"The Access Web"

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Big Time" <big-time-grizz@.remove-for-spam-hotmail.com> wrote in message
news:cfu2e7$18gm$1@.lettuce.bcit.ca...
> I'm trying to write a query that concatenates multiple records into one
> derived column. Let's say I have an author (Joe Writer) who has written
> three books (Book 1, Book2 and Book 3). The author is in tblAuthors, his
> books are in the tblBooks and they are joined by the AuthorID field
> (number). If I use a simple select query to give me the author name and
the
> title, I will get three records, one for each book written.
> What I want is to have all three books combined into one derived column.
So
> if I do the select statement, I will get one column with the author name,
> and the second column will put together all three names of the book
> separated by a column. So it will look like:
> Author Title
> Joe Writer Book 1, Book 2, Book 3,
> Rather than having it appear as 3 records:
> Joe Writer Book 1
> Joe Writer Book 2
> Joe Writer Book 3
> Could someone help me with the SQL involved in this?
> Thanks for the help.
> Cheers,
> Mike|||Mike,

read this article...

http://www.mvps.org/access/modules/mdl0004.htm

It has code that does this.|||Try this out
DECLARE @.BookNames varchar(1000)
SET @.BookNames = ''
SELECT @.BookNames = @.BookNames +Book + ', ' FROM Books
where author = 'Joe Writer'
SELECT 'Joe Writer',@.BookNames|||JK (jaikrishnan_nair@.hotmail.com) writes:
> Try this out
> DECLARE @.BookNames varchar(1000)
> SET @.BookNames = ''
> SELECT @.BookNames = @.BookNames +Book + ', ' FROM Books
> where author = 'Joe Writer'
> SELECT 'Joe Writer',@.BookNames

This may work. Or not work. The result of this sort of operation is
undefined in SQL Server. This is one of the few situations where
iterating over the data is a better option.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Derived column usage when column does not exist in source (but exists in destination)

Posted - 09/10/2007 : 15:53:26

---

Hey all - got a problem that seems like it would be simple (and probably is : )

I'm importing a csv file into a SQL 2005 table and would like to add 2 columns that exist in the table but not in the csv file. I need these 2 columns to contain the current month and year (columns are named CM and CY respectively). How do I go about adding this data to each row during the transformation? A derived column task? Script task? None of these seem to be able to do this for me.

Here's a portion of the transformation script I was using to accomplish this when we were using SQL 2000 DTS jobs:

'**********************************************************************
' Visual Basic Transformation Script
'************************************************************************

' Copy each source column to the destination column
Function Main()
DTSDestination("CM") = Month(Now)
DTSDestination("CY") = Year(Now)
DTSDestination("Comments") = DTSSource("Col031")
DTSDestination("Manufacturer") = DTSSource("Col030")
DTSDestination("Model") = DTSSource("Col029")
DTSDestination("Last Check-in Date") = DTSSource("Col028")
Main = DTSTransformStat_OK
End Function
***********************************************************
Hopefully this question isnt answered somewhere else, but I did a quick search and came up with nothing. I've actually tried to utilize the script component and the "Row" object, but the only properties I'm given with that are the ones from the source data.

thanks in advance!

jm

It could be easily done by using the Derived Column task.

Just specify 2 new derived column names:

Code Snippet

Derived Column Derived Column Expression

============================================================

Month <add as a new column> MONTH(@.[System::StartTime])

Year <add as a new column> YEAR(@.[System::StartTime])

Regards,

Yitzhak

derived column transformation expression

Hi.

I am using the following expression to check if the first charcter of a string is not the letter "E" and if it is, strip it off by selecting the remainder of the string:

SUBSTRING([Derv.comno],1,1) == "E" ? SUBSTRING([Derv.comno],2,10) : [Derv.comno]

This is ok in 99.9% of cases, but ideally I would like to be able to check, and alter the string if the first charcter is anything but numeric

I had something like this in mind:

SUBSTRING([Derv.comno],1,1) != ("1","2","3") ? SUBSTRING([Derv.comno],2,10) : [Derv.comno]

but the syntax is incorrect.

Could you tell me if what I am attempting is actually possible, and if so, point me in the right direction regarding the syntax!

Thanks

CODEPOINT(@.[User::CCC]) >= 47 && CODEPOINT(@.[User::CCC]) <= 57 ? @.[User::CCC] : SUBSTRING(@.[User::CCC], 2, LEN(@.[User::CCC]) - 1)|||

Excellent, works a treat!

Thanks very much

sql

Derived Column Transformation Editor Question

Help...

I'm having trouble coming up with a valid expression in my derived column transformation editor that tests the input column for NULL and responds something like this:

if[message] isNull then "NA" else [message]

where [message] is the input column.

Thanks!

Try:

ISNULL([message]) ? "NA" : [message]

|||SWEET!! Thanks!

Derived Column Transformation Editor Question

Help...

I'm having trouble coming up with a valid expression in my derived column transformation editor that tests the input column for NULL and responds something like this:

if[message] isNull then "NA" else [message]

where [message] is the input column.

Thanks!

Try:

ISNULL([message]) ? "NA" : [message]

Derived Column Transformation - Error

Can i call the FUNCTION within another FUNCTION

Like SUBSTRING(CHECK_NO,2,LEN(CHECK_NO) - 1) ?

I am reading the Check_No "1234321" from the flat file. The file holds all the value within double quote and values are sepearated by comma.

Objective: I am trying to elimiate the double quote using "Dervied Column'.

Strange: The above FUNCTION is working fine while construct the SQL Query.

Pls help me. Thank you.

Why don't you use the REPLACE function:

REPLACE(CHECK_NO, ""","") - I am replacing the double quotes with a null value

|||

The syntax becomes RED in color REPLACE(CHECK_NO, ""","")

It means syntax error. Am i right ?

Sorry, I am new to SSIS. Thanks for your help|||

Try

Code Snippet

REPLACE(CHECK_NO, "\"", "")

The quote needs to be escaped.

Derived Column Transformation

Hello.
I am using Derived Column transformation for calculating the age of individual and then adding the column to my final destination. In SQL, the DOB is varchar(50) and the output column I am creating should be Integer.
Here is the expression I am using for calculating the age:
(DATEDIFF("DAY",(DT_DBTIMESTAMP)TRIM(DOB),(DT_DBTIMESTAMP)TRIM([Service Date])) / 365.25)

In SQL, I have no problems getting the age of a person, but I am having difficulties using Derived Column Transformation.
I get the following error when executing my package:

Error: 0xC0049067 at Data Flow Task, Derived Column [2086]: An error occurred while evaluating the function.
Error: 0xC0209029 at Data Flow Task, Derived Column [2086]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Derived Column" (2086)" failed because error code 0xC0049067 occurred, and the error row disposition on "output column "_AGE" (2877)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

Any assistnace would be greatly appreciated.
I've come across a similar issue before. It seems that derived column transformation doesn't like converting char or integer data to dt_dbtimestamp. The way I got around it was using a lookup to a date dimension on the date in char format and pulling back the corresponding datetime format for the record. Is this a possibility?|||

Thank you for your reply.

I guess there are other options I can choose from. However, since this is a SSIS component, I would like to make it work and find out why is it so difficult to deal with. I understand that the problem is data type, nevertheless, the data is already in SQL and it is using SQL data type. If I were importing data from a flat file, I would accept the fact that data type could be more of an issue.

When I run my SQL query (please see two versions), it works like a charm.:

select convert(int,datediff("dd", convert(datetime,DOB),getdate())/365.25) FROM [DATABASE].[dbo].[TABLENAME]

OR

select cast(datediff("day",cast(DOB as datetime),cast([Service Date] as datetime))/365.25 as int) from FROM [DATABASE].[dbo].[TABLENAME]

|||

What format is your date field thats stored as char?

I just tried '20070101' and it didn't work but '01012007' works

|||

Hi there:
Once again, thank you for your reply.

The format I use in SQL is YYYYMMDD; Example: 19900925

|||

Try converting it around with some string functions...

Code Snippet

(DT_DBTIMESTAMP)(RIGHT(testDate,2) + SUBSTRING(testDate,5,2) + SUBSTRING(testDate,1,4))

|||

Hi there:
I will try your suggestion and I will let you know.

|||

Hi there:

I did tried your suggestion. Here is what I wrote:

(DATEDIFF("DAY",(DT_DBTIMESTAMP)(SUBSTRING(DOB,7,2) + SUBSTRING(DOB,5,2) + SUBSTRING(DOB,1,4)),(DT_DBTIMESTAMP)(SUBSTRING([Service Date],7,2) + SUBSTRING([Service Date],5,2) + SUBSTRING([Service Date],1,4))) / 365.25)

I am still having the same error:

Error: 0xC0049067 at Data Flow Task, Derived Column [3949]: An error occurred while evaluating the function.

Error: 0xC0209029 at Data Flow Task, Derived Column [3949]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Derived Column" (3949)" failed because error code 0xC0049067 occurred, and the error row disposition on "output column "_AGE" (4008)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

|||

Alright, just did some more testing. For some reason you can't go from char to datetime, you have to go from char to int to datetime. Also, the int format has to be mmddyyyy

Code Snippet

(DT_DBTIMESTAMP)((DT_I4)((DT_STR,8,1252)RIGHT(testDate,2) + SUBSTRING(testDate,5,2) + SUBSTRING(testDate,1,4)))

|||

Hi there:
Unfortunately I tried your last suggestion and I still cannot process the package. I did put a data viewer between my SQL DB Source and the Derived Column and the DOB format comes as 19900921 which is what I have in my DB.

I agree with you that there is something with the conversion that is not working.

I tried the following cast in SQL using the following format(ddmmyyyy):

select cast('20092007'as datetime)--The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

I wonder if the way CAST works is what is causing the problem.

By the way, thank you for assisting me. Appreciate your time and effort

|||

I used the following in a variable and it worked:

(DT_I8) (DATEDIFF("DAY",(DT_DBTIMESTAMP) @.[User::CurrentDate], (DT_DBTIMESTAMP) @.[User::QuotaStartDt] )/365 )

The variables I pass into the expression are in the standard Datetime format. The result I get is a 0 but it did not give me any errors.

|||Hi there:
Thank you for your suggestion.

My DOB string is formatted as: yyyymmdd and casting a string to datetime using this format works fine in SQL. If the format is ddmmyyyy, then casting does not work at all, unless I cast the string several times.

I tried using substring, casting etc and I have not seen any positive result. I think I will perform the age calculation in sql and added to my query. Too bad something simple gets so complicated....!

I would like to thank you all for your time and effort.

Derived Column Transformation

Hello.
I am using Derived Column transformation for calculating the age of individual and then adding the column to my final destination. In SQL, the DOB is varchar(50) and the output column I am creating should be Integer.
Here is the expression I am using for calculating the age:
(DATEDIFF("DAY",(DT_DBTIMESTAMP)TRIM(DOB),(DT_DBTIMESTAMP)TRIM([Service Date])) / 365.25)

In SQL, I have no problems getting the age of a person, but I am having difficulties using Derived Column Transformation.
I get the following error when executing my package:

Error: 0xC0049067 at Data Flow Task, Derived Column [2086]: An error occurred while evaluating the function.
Error: 0xC0209029 at Data Flow Task, Derived Column [2086]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Derived Column" (2086)" failed because error code 0xC0049067 occurred, and the error row disposition on "output column "_AGE" (2877)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

Any assistnace would be greatly appreciated.
I've come across a similar issue before. It seems that derived column transformation doesn't like converting char or integer data to dt_dbtimestamp. The way I got around it was using a lookup to a date dimension on the date in char format and pulling back the corresponding datetime format for the record. Is this a possibility?|||

Thank you for your reply.

I guess there are other options I can choose from. However, since this is a SSIS component, I would like to make it work and find out why is it so difficult to deal with. I understand that the problem is data type, nevertheless, the data is already in SQL and it is using SQL data type. If I were importing data from a flat file, I would accept the fact that data type could be more of an issue.

When I run my SQL query (please see two versions), it works like a charm.:

select convert(int,datediff("dd", convert(datetime,DOB),getdate())/365.25) FROM [DATABASE].[dbo].[TABLENAME]

OR

select cast(datediff("day",cast(DOB as datetime),cast([Service Date] as datetime))/365.25 as int) from FROM [DATABASE].[dbo].[TABLENAME]

|||

What format is your date field thats stored as char?

I just tried '20070101' and it didn't work but '01012007' works

|||

Hi there:
Once again, thank you for your reply.

The format I use in SQL is YYYYMMDD; Example: 19900925

|||

Try converting it around with some string functions...

Code Snippet

(DT_DBTIMESTAMP)(RIGHT(testDate,2) + SUBSTRING(testDate,5,2) + SUBSTRING(testDate,1,4))

|||

Hi there:
I will try your suggestion and I will let you know.

|||

Hi there:

I did tried your suggestion. Here is what I wrote:

(DATEDIFF("DAY",(DT_DBTIMESTAMP)(SUBSTRING(DOB,7,2) + SUBSTRING(DOB,5,2) + SUBSTRING(DOB,1,4)),(DT_DBTIMESTAMP)(SUBSTRING([Service Date],7,2) + SUBSTRING([Service Date],5,2) + SUBSTRING([Service Date],1,4))) / 365.25)

I am still having the same error:

Error: 0xC0049067 at Data Flow Task, Derived Column [3949]: An error occurred while evaluating the function.

Error: 0xC0209029 at Data Flow Task, Derived Column [3949]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Derived Column" (3949)" failed because error code 0xC0049067 occurred, and the error row disposition on "output column "_AGE" (4008)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

|||

Alright, just did some more testing. For some reason you can't go from char to datetime, you have to go from char to int to datetime. Also, the int format has to be mmddyyyy

Code Snippet

(DT_DBTIMESTAMP)((DT_I4)((DT_STR,8,1252)RIGHT(testDate,2) + SUBSTRING(testDate,5,2) + SUBSTRING(testDate,1,4)))

|||

Hi there:
Unfortunately I tried your last suggestion and I still cannot process the package. I did put a data viewer between my SQL DB Source and the Derived Column and the DOB format comes as 19900921 which is what I have in my DB.

I agree with you that there is something with the conversion that is not working.

I tried the following cast in SQL using the following format(ddmmyyyy):

select cast('20092007'as datetime)--The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

I wonder if the way CAST works is what is causing the problem.

By the way, thank you for assisting me. Appreciate your time and effort

|||

I used the following in a variable and it worked:

(DT_I8) (DATEDIFF("DAY",(DT_DBTIMESTAMP) @.[User::CurrentDate], (DT_DBTIMESTAMP) @.[User::QuotaStartDt] )/365 )

The variables I pass into the expression are in the standard Datetime format. The result I get is a 0 but it did not give me any errors.

|||Hi there:
Thank you for your suggestion.

My DOB string is formatted as: yyyymmdd and casting a string to datetime using this format works fine in SQL. If the format is ddmmyyyy, then casting does not work at all, unless I cast the string several times.

I tried using substring, casting etc and I have not seen any positive result. I think I will perform the age calculation in sql and added to my query. Too bad something simple gets so complicated....!

I would like to thank you all for your time and effort.

sql

derived column transform (flat file blanks to 0)

Hi,

Is it possible using derived column transform to change all blank values in a flat file to say a "0"

Basically convert "" to "0"

Thanks for any help,

Slash.

Hi,

I think you can generate your derived column like this :

([source_column] == "") ? "0" : [source_column]

Arno.

|||

Top man,

Worked a treat,

Thanks again,

Slash.

Derived Column Task Replace Quotes

How do you replace quotes in an expression

I mean for example if I needed to replace xx in a string with empty string then the following works: REPLACE(SelectedString, "xx","")

But the example I have needs to actually replace quote marks in a string with an empty string and REPLACE(SelectedString, " " ","") doesn't work. I tried guessing a few option like &QUOTE or &QTE or something...

Any ideas ?

Thanks

Richard

Got it !! :

REPLACE([Selected Closing],"\"","")

Derived Column Task failing with error 0xC0049067

I have a package that fails as soon as it hits the first Data Flow that contains a Derived Column task. The task takes three date columns and looks for a date of 6/6/2079. If it is there, it is replaced with a NULL. This task worked fine until I installed the Non-CTP version of SQL 2005 SP1, earlier today. (I went from RTM 9.0.1399 to SP1 9.0.2047)Does anyone have any ideas?

Here is the error I am trapping:

An error occurred while evaluating the function.

The "component "Update Max Date Value to NULL" (346)" failed because error code 0xC0049067 occurred, and the error row disposition on "input column "WeekEndingDate" (455)" specifies failure on error. An error occurred on the specified object of the specified component.

The ProcessInput method on component "Update Max Date Value to NULL" (346) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

Thread "WorkThread0" has exited with error code 0xC0209029.

I tried to configure the error output to ignore the failure. The task succeeded. It did not insert anything, however, for the three columns in question. It then failed on the next Data Flow that contained a Derived Column task. I tried to pre-populate the columns with a default date, but no go. Any help would be appreciated.

Nothing wrong with the concept and I can do a similar thing in SP1. What is your expression and column types?|||

The date columns come out of an Access database. I convert them to a string in a Data Conversion task and then validate that they are valid dates in a Script Component task. If they are invalid, I assign them a date of 6/6/2079. When they exit the Script task they are converted back to a date. The Derived Column task is next and it looks for the year 2079 to convert it to a NULL. Here is an example of one of the expressions:

YEAR([Check for Valid Dates].LastWorkedDate) == 2079 ? NULL(DT_DBTIMESTAMP) : [Check for Valid Dates].LastWorkedDate

This is where it fails. One thing I failed to mention before is that if I execute the task by itself it works properly. However, if I run the entire package (100+ tasks) it fails as soon as it hits the first task that contains a Derived Column task.

|||Turns out it is not the first Data Flow with a Derived Column task that is failing. I have one about 8 steps prior that runs just fine. I am wondering if it has something to do with the expression language. I saw a post where someone got the same error if they tried to use an expression language date function with a date value outside of SQL range (1/1/1753 to 12/31/9999). This post however was pre-SP1. I wonder whether that issue was addressed and if so how. Any suggestions from anyone would be greatly appreciated. I have been stuck on this for a couple of days and I really need to move on. I am dreading the thought of uninstalling\reinstalling SQL2000 and 2005 on my machine but will if I have to. Please help!!!!|||I uninstalled and reinstalled SQL 2005 and then attempted to run my package and it worked. There must be something that changed about the way it evaluates expressions in Derived Column tasks or maybe it is the expression language itself. Either way I am going to have to move on without SP1 until residual issues like these are dealt with. I am going to submit a bug to Microsoft about it. If anyone comes up with anything, please post a response.|||

I have been unable to reproduce this either on SQL Server 2005 RTM or SP1.

Could you perhaps hook up the error output of the derived column and redirect error rows, and then see what value(s) of LastWorkedDate it's failing on?

Thanks,
mark

|||I apologize for taking so long to get back to you. Unfortunately, I have already reinstalled SQL to RTM -SP1. I lost two days trying to troubleshoot the problem and had to give up for now. As soon as I have a chance I will reinstall SP1 and try again. For now however I have to live without it. Thank you very much for trying to reproduce the error. I will post more about this once I have the time.|||

DatabaseOgre wrote:

I saw a post where someone got the same error if they tried to use an expression language date function with a date value outside of SQL range (1/1/1753 to 12/31/9999). This post however was pre-SP1. I wonder whether that issue was addressed and if so how.

I suspect that what you suggested is indeed the problem. YEAR will fail if the date is outside of the SQL range.

If you have a chance to try redirecting rows to see if the values are indeed outside of that range, that would help us be sure that we have figured this one out. In the meantime, I will update the bug with this information.

|||

Sorry. I went back to the post and reade it a little slower this time. The post refers specifically to the DATEPART function with the YEAR argument. Here is a quote:

Can someone confirm this for me? The expression language in SSIS has the same limitations on date ranges as Sql Server? That limitation is that valid date ranges are from Jan 1, 1753 to Dec 31, 9999.

When ever I try to do a date function (DATEPART, for example) in a Derived Column Transformation on a date less than 1/1/1753, I get an error. I initially discovered this when bringing data over from Oracle to Sql Server. Just as a test, I created a text file filled with various dates and tried to import it. Whenever a date is less than 1/1/1753, it blows up.

For example, this expression code - DATEPART("YEAR",Date) will yield this error - [Derived Column [24]] Error: The "component "Derived Column" (24)" failed because error code 0xC0049067 occurred, and the error row disposition on "output column "YEAR" (80)" specifies failure on error. An error occurred on the specified object of the specified component.

As a workaround, I've been using a Script Component to do date checking, but this is obviously not ideal.

Jamie Thomson verified it for him and it was submitted as a bug for not being able to handle dates outside of the T-SQL date range. I checked my data to look for any dates that were outside of the range (pre-package) and did not find any. I also tried handling any such dates inside of a Script Task (as a just in case) which ran prior to the Derived Column Task and still received the error. When I get some time I will take a look at my source data again and see if there is anything there. I doubt it however because I am using the same package and same data source with SQL2005 RTM and I am not getting any errors. I probably wouldn't change much about the bug I submitted just yet.

|||

The value being out of range was my best guess for the cause of the error. If you know the values are all in range, then something else is wrong.

The best thing now, I think, would be for you to try redirecting the error rows and see what values are causing the problem. Once we know that, I will be able to try to reproduce the problem.

Thanks!
Mark