problem passing UDF scalar result to UDF table function

I'm having difficulties invoking a user defined table function,
when passing to it a parameter that is the result of another
user defined function.

My functions are defined like so:

drop function dbo.scalar_func
go
create function dbo.scalar_func()
returns int
begin
return 1
end
go

drop function dbo.table_func
go
create function dbo.table_func(@.p int)
returns table
return (select @.p as id )
go

Given the above, I can do the following:

Select from the scalar function works:
1> select dbo.scalar_func() as scalar_result
2> go
scalar_result
----
1

Selecting from the table function works, if i pass a
constant value (or a variable)

1> select id from dbo.table_func(1)
2> go
id
----
1

But, if I try to pass the table function the return value
of the scalar function in one call, it doesn't work,
producing the following error:

1> select id from dbo.table_func( dbo.scalar_func() )
2> go
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '.'.

What am I missing here?

Thanks kindly"roger" <rogerr@.softix.com> wrote in message news:f6e08af1.0405012257.4c2472cf@.posting.google.c om...

<snip
> But, if I try to pass the table function the return value
> of the scalar function in one call, it doesn't work,
> producing the following error:
> 1> select id from dbo.table_func( dbo.scalar_func() )
> 2> go
> Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '.'.
> What am I missing here?
> Thanks kindly

It's barking at the period in the nested call. Try running it without qualifying the function name:

1> select id from dbo.table_func( scalar_func() )

--
Paul Horan
VCI Springfield, MA|||roger (rogerr@.softix.com) writes:
> But, if I try to pass the table function the return value
> of the scalar function in one call, it doesn't work,
> producing the following error:
> 1> select id from dbo.table_func( dbo.scalar_func() )
> 2> go
> Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '.'.
> What am I missing here?

I don't think you can pass expressions as parameters to table-valued
functions; you can only pass constants and variables.

However, I was looking around in Books Online, but I could not find
a passage which actually says so.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"-P-" <ent_must_dieAThotmailDOTcom> wrote in message news:<UI2dnT_yvLdgPQndRVn-hQ@.adelphia.com>...
> "roger" <rogerr@.softix.com> wrote in message news:f6e08af1.0405012257.4c2472cf@.posting.google.c om...
> <snip>
> It's barking at the period in the nested call. Try running it without qualifying the function name:
> 1> select id from dbo.table_func( scalar_func() )

No, that isn't it.

You have to qualify a scalar function with the owner name.
Curiously, you don't have to qualify a table valued function
in this way.

So, this works
select * from table_function()
but this does not
select scalar_function() from table
requiring instead
select dbo.scalar_function() from table.

I can't see any particular rhyme or reason to this,
it's just the way it seems to be.|||roger (rogerr@.softix.com) writes:
> So, this works
> select * from table_function()
> but this does not
> select scalar_function() from table
> requiring instead
> select dbo.scalar_function() from table.
>
> I can't see any particular rhyme or reason to this,
> it's just the way it seems to be.

The reason is that with out the reqiurement of a two-part name there
would be no possibility to distinguish between scalar system functions
and scalar UDF. And if they look the same syntactically, they share the
same name space, which would mean that each time MS added a new system
function, they would risk to break existing code.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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

problem passing UDF scalar result to UDF table function

I'm having difficulties invoking a user defined table function,
when passing to it a parameter that is the result of another
user defined function.

My functions are defined like so:

drop function dbo.scalar_func
go
create function dbo.scalar_func()
returns int
begin
return 1
end
go

drop function dbo.table_func
go
create function dbo.table_func(@.p int)
returns table
return (select @.p as id )
go

Given the above, I can do the following:

Select from the scalar function works:
1> select dbo.scalar_func() as scalar_result
2> go
scalar_result
----
1

Selecting from the table function works, if i pass a
constant value (or a variable)

1> select id from dbo.table_func(1)
2> go
id
----
1

But, if I try to pass the table function the return value
of the scalar function in one call, it doesn't work,
producing the following error:

1> select id from dbo.table_func( dbo.scalar_func() )
2> go
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '.'.

What am I missing here?

Thanks kindly"roger" <rogerr@.softix.com> wrote in message news:f6e08af1.0405012257.4c2472cf@.posting.google.c om...

<snip
> But, if I try to pass the table function the return value
> of the scalar function in one call, it doesn't work,
> producing the following error:
> 1> select id from dbo.table_func( dbo.scalar_func() )
> 2> go
> Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '.'.
> What am I missing here?
> Thanks kindly

It's barking at the period in the nested call. Try running it without qualifying the function name:

1> select id from dbo.table_func( scalar_func() )

--
Paul Horan
VCI Springfield, MA|||roger (rogerr@.softix.com) writes:
> But, if I try to pass the table function the return value
> of the scalar function in one call, it doesn't work,
> producing the following error:
> 1> select id from dbo.table_func( dbo.scalar_func() )
> 2> go
> Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '.'.
> What am I missing here?

I don't think you can pass expressions as parameters to table-valued
functions; you can only pass constants and variables.

However, I was looking around in Books Online, but I could not find
a passage which actually says so.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"-P-" <ent_must_dieAThotmailDOTcom> wrote in message news:<UI2dnT_yvLdgPQndRVn-hQ@.adelphia.com>...
> "roger" <rogerr@.softix.com> wrote in message news:f6e08af1.0405012257.4c2472cf@.posting.google.c om...
> <snip>
> It's barking at the period in the nested call. Try running it without qualifying the function name:
> 1> select id from dbo.table_func( scalar_func() )

No, that isn't it.

You have to qualify a scalar function with the owner name.
Curiously, you don't have to qualify a table valued function
in this way.

So, this works
select * from table_function()
but this does not
select scalar_function() from table
requiring instead
select dbo.scalar_function() from table.

I can't see any particular rhyme or reason to this,
it's just the way it seems to be.|||roger (rogerr@.softix.com) writes:
> So, this works
> select * from table_function()
> but this does not
> select scalar_function() from table
> requiring instead
> select dbo.scalar_function() from table.
>
> I can't see any particular rhyme or reason to this,
> it's just the way it seems to be.

The reason is that with out the reqiurement of a two-part name there
would be no possibility to distinguish between scalar system functions
and scalar UDF. And if they look the same syntactically, they share the
same name space, which would mean that each time MS added a new system
function, they would risk to break existing code.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns94DEEA82AECD9Yazorman@.127.0.0.1>...
> roger (rogerr@.softix.com) writes:
> > So, this works
> > select * from table_function()
> > but this does not
> > select scalar_function() from table
> > requiring instead
> > select dbo.scalar_function() from table.
> > I can't see any particular rhyme or reason to this,
> > it's just the way it seems to be.
> The reason is that with out the reqiurement of a two-part name there
> would be no possibility to distinguish between scalar system functions
> and scalar UDF. And if they look the same syntactically, they share the
> same name space, which would mean that each time MS added a new system
> function, they would risk to break existing code.

Oracle PL/SQL uses the keyword TABLE to introduce a function
that returns a table. eg

select * from table(my_user_function(args))

I would have thought that the context in which the function
is being called would tell you whether a scalar or table result
is required, but maybe there are more scenarios than I can imagine
off hand.

Using the qualified name to distinguish the function return type,
if that is really what that is about, is truly horrid.

As is not being able to pass the result of another function as
an argument. Kind of smells like this stuff was added in a hurry.

Still, I'm thankful that it is there at all I guess.|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns94DEEA82AECD9Yazorman@.127.0.0.1>...
> roger (rogerr@.softix.com) writes:
> > So, this works
> > select * from table_function()
> > but this does not
> > select scalar_function() from table
> > requiring instead
> > select dbo.scalar_function() from table.
> > I can't see any particular rhyme or reason to this,
> > it's just the way it seems to be.
> The reason is that with out the reqiurement of a two-part name there
> would be no possibility to distinguish between scalar system functions
> and scalar UDF. And if they look the same syntactically, they share the
> same name space, which would mean that each time MS added a new system
> function, they would risk to break existing code.

Oracle PL/SQL uses the keyword TABLE to introduce a function
that returns a table. eg

select * from table(my_user_function(args))

I would have thought that the context in which the function
is being called would tell you whether a scalar or table result
is required, but maybe there are more scenarios than I can imagine
off hand.

Using the qualified name to distinguish the function return type,
if that is really what that is about, is truly horrid.

As is not being able to pass the result of another function as
an argument. Kind of smells like this stuff was added in a hurry.

Still, I'm thankful that it is there at all I guess.|||roger (rogerr@.softix.com) writes:
> Oracle PL/SQL uses the keyword TABLE to introduce a function
> that returns a table. eg
> select * from table(my_user_function(args))
> I would have thought that the context in which the function
> is being called would tell you whether a scalar or table result
> is required, but maybe there are more scenarios than I can imagine
> off hand.

The issue is about distinguishing scalar functions from table functions.
The issue is about distinguishing different sorts of scalar functions,
system functions vs. user-defined ones. SQL Server have no problem
to tell whether you are using a table-valued or scalar function. Or
more correctly: there are no places in the grammar where you can use both.

The system functions had already invaded the flat name space for

SELECT fun(x)

Of course, there are a couple of more options MS could have chosen. For
instance decide that all future scalar system functions would have a
prefix.

This is actually what they have done for table-valued functions:

SELECT * FROM ::fn_helpcollations()

This was possible, since there was no table-valued system functions
prior to SQL2000.

> As is not being able to pass the result of another function as
> an argument. Kind of smells like this stuff was added in a hurry.

It is quite consistent with how you call stored procedures. You
cannot pass expressions in calls to stored procedures with the
EXEC statement. And in fact, you can invoke scalar functions with
the EXEC statement too.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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

Problem Loading Data From FlatFile Source Data For Column overflowed the disk I/O buffer

Hi i am trying to do a straight forward load from a Flatfile source , i have defined the columns according to the lenghts defined in the Data Dictionary Provided but when i am trying to run the Task i am encounterring this error

The column data for column "Column 20" overflowed the disk I/O buffer.

I tried to add another column 21 at the end and truncate or leave that column unmapped to destination but the same problem occurs for column 21 what should i do to over come this .

In case of Bad Data how to clean up the source.. Please help me with this

You can find a lot of posts in this forum about the way SSIS parses flat files. It does that column by column. It means if your column delimiter is missing at column 21 it will continue parsing until that delimiter is found and it will eat all the columns in between and eventually make the data oveflow the reserved buffer.

In this case I would try to find the row where your data is misconigured. You can do that by cutting your file in halfs until you find the place which breaks parsing.

HTH,

Bob

|||

Hi Bob,

I understand the internal working. But then what will be the solution to handle? Again the question is "In case of Bad Data how to clean up the source via SSIS" - already raised by Pradeep here. Kristen's reply is partial. It doesn't solve here as there exist no workaround for skipping these rows. The clue is to use the Ragged right, where the Column size cannot exceed length of 4000 (Limitation to be noted) and splitting hundreds of such columns (if do have) and checking if the length of these individual columns exceed max length and finally mapping them is all not only too laborious but also inefficient.

On the other I tried ignoring or redirecting these rows, but the Flat File Source OLEDB Adapter doesn't seem to handle these rows at all. How will I skip these rows. I am using ForEach Loop loads GBs of data. and probability is that any of the file might contain row corruption.

Thanks

Subhash Subramanyam

|||

Hi Subhash,

Unfortunately, there is no in-box SSIS solution for this. As the current flat file parser cannot identify the broken rows it does not have any way to skip or report errors about them.

You'll have to craft your own solution and there are examples of how other people did it in this forum. It certanly isn't pretty but I cannot offer better, for now.

Thanks,

Bob

Problem Loading Data From FlatFile Source Data For Column overflowed the disk I/O buffer

Hi i am trying to do a straight forward load from a Flatfile source , i have defined the columns according to the lenghts defined in the Data Dictionary Provided but when i am trying to run the Task i am encounterring this error

The column data for column "Column 20" overflowed the disk I/O buffer.

I tried to add another column 21 at the end and truncate or leave that column unmapped to destination but the same problem occurs for column 21 what should i do to over come this .

In case of Bad Data how to clean up the source.. Please help me with this

You can find a lot of posts in this forum about the way SSIS parses flat files. It does that column by column. It means if your column delimiter is missing at column 21 it will continue parsing until that delimiter is found and it will eat all the columns in between and eventually make the data oveflow the reserved buffer.

In this case I would try to find the row where your data is misconigured. You can do that by cutting your file in halfs until you find the place which breaks parsing.

HTH,

Bob

|||

Hi Bob,

I understand the internal working. But then what will be the solution to handle? Again the question is "In case of Bad Data how to clean up the source via SSIS" - already raised by Pradeep here. Kristen's reply is partial. It doesn't solve here as there exist no workaround for skipping these rows. The clue is to use the Ragged right, where the Column size cannot exceed length of 4000 (Limitation to be noted) and splitting hundreds of such columns (if do have) and checking if the length of these individual columns exceed max length and finally mapping them is all not only too laborious but also inefficient.

On the other I tried ignoring or redirecting these rows, but the Flat File Source OLEDB Adapter doesn't seem to handle these rows at all. How will I skip these rows. I am using ForEach Loop loads GBs of data. and probability is that any of the file might contain row corruption.

Thanks

Subhash Subramanyam

|||

Hi Subhash,

Unfortunately, there is no in-box SSIS solution for this. As the current flat file parser cannot identify the broken rows it does not have any way to skip or report errors about them.

You'll have to craft your own solution and there are examples of how other people did it in this forum. It certanly isn't pretty but I cannot offer better, for now.

Thanks,

Bob

Problem Loading Data From FlatFile Source Data For Column overflowed the disk I/O buffer

Hi i am trying to do a straight forward load from a Flatfile source , i have defined the columns according to the lenghts defined in the Data Dictionary Provided but when i am trying to run the Task i am encounterring this error

The column data for column "Column 20" overflowed the disk I/O buffer.

I tried to add another column 21 at the end and truncate or leave that column unmapped to destination but the same problem occurs for column 21 what should i do to over come this .

In case of Bad Data how to clean up the source.. Please help me with this

You can find a lot of posts in this forum about the way SSIS parses flat files. It does that column by column. It means if your column delimiter is missing at column 21 it will continue parsing until that delimiter is found and it will eat all the columns in between and eventually make the data oveflow the reserved buffer.

In this case I would try to find the row where your data is misconigured. You can do that by cutting your file in halfs until you find the place which breaks parsing.

HTH,

Bob

|||

Hi Bob,

I understand the internal working. But then what will be the solution to handle? Again the question is "In case of Bad Data how to clean up the source via SSIS" - already raised by Pradeep here. Kristen's reply is partial. It doesn't solve here as there exist no workaround for skipping these rows. The clue is to use the Ragged right, where the Column size cannot exceed length of 4000 (Limitation to be noted) and splitting hundreds of such columns (if do have) and checking if the length of these individual columns exceed max length and finally mapping them is all not only too laborious but also inefficient.

On the other I tried ignoring or redirecting these rows, but the Flat File Source OLEDB Adapter doesn't seem to handle these rows at all. How will I skip these rows. I am using ForEach Loop loads GBs of data. and probability is that any of the file might contain row corruption.

Thanks

Subhash Subramanyam

|||

Hi Subhash,

Unfortunately, there is no in-box SSIS solution for this. As the current flat file parser cannot identify the broken rows it does not have any way to skip or report errors about them.

You'll have to craft your own solution and there are examples of how other people did it in this forum. It certanly isn't pretty but I cannot offer better, for now.

Thanks,

Bob