scalar UDF question in Access ADP

  • Thread starter Thread starter JD
  • Start date Start date
J

JD

I created a scalar user-defined function in an adp - like
this:
----------------------------------------
Create Function joeUser.Function1
(
@parm1 varchar(20)
)
Returns varchar(20)
As
Begin
Set @parm1 = @parm1 + 'test'
Return @parm1
End
------------------------------------------

If I click on this function - I get the dialog box asking
for a param -- I enter 'shmo' and it returns 'shmotest' as
epxected. But if I run this udf in a view like this:

Select joeUser.Function1(fld1), fld2 From Table1

--say table1 contains 2 rows
fld1 fld2
cat bird
dog ball

the view returns instead of
fld1 fld2 fld1 fl2
cat bird cattest bird
dog ball dogtest ball

But if I change the udf Set @parm1 = @parm1 + 'test'
to Set @parm1 = 'test' + @parm1 now the view returns

fld1 fld2
testcat bird
testdog ball

This udf resides in the adp. If I run the same udf in Sql
Server (2000) -udf owned by dbo instead of joeUser - on
the table owned by joeUser, I get the same results as the
adp view. But if I run dbo.Function1 on a dbo owned
table, then I get cattest, dogtest, the correct results.

Is there a difference between creating a udf in the adp or
creating the same udf in Sql Server? Is there a way to
compensate for this difference?

Thanks,
JD
 
If you have access to Enterprise Manager, then you should make sure that
joeUser.Function1 is really [joeUser].[Function1] and not something else
like [joeUser].[joeUser.Function1] or [dbo].[joeUser.Function1].

It is also possible that the view has kept the old compilation or something
like that. You should make a second test but this time but using something
like: Set @parm1 = @parm1 + 'test2' .

Second, if the field type of fld1 if char(20) instead of varchar(20), then
you will get this result.

You can also try with another variable than @parm1 for storing the
intermediate result.

Finally, you should use N'test' instead of 'test'.

S. L.
 
Thanks. I made sure I had [joeUser].[Function1] and I
applied the N'. What I did observe (with and without the
N' and with dbo. or joeUser) is that 'dog ' is
selected when the view comes up in the adp. So spaces are
in fact being appended, but not the text. It must be a
syntax thing with the adp. Just what?
-----Original Message-----
If you have access to Enterprise Manager, then you should make sure that
joeUser.Function1 is really [joeUser].[Function1] and not something else
like [joeUser].[joeUser.Function1] or [dbo]. [joeUser.Function1].

It is also possible that the view has kept the old compilation or something
like that. You should make a second test but this time but using something
like: Set @parm1 = @parm1 + 'test2' .

Second, if the field type of fld1 if char(20) instead of varchar(20), then
you will get this result.

You can also try with another variable than @parm1 for storing the
intermediate result.

Finally, you should use N'test' instead of 'test'.

S. L.

I created a scalar user-defined function in an adp - like
this:
----------------------------------------
Create Function joeUser.Function1
(
@parm1 varchar(20)
)
Returns varchar(20)
As
Begin
Set @parm1 = @parm1 + 'test'
Return @parm1
End
------------------------------------------

If I click on this function - I get the dialog box asking
for a param -- I enter 'shmo' and it returns 'shmotest' as
epxected. But if I run this udf in a view like this:

Select joeUser.Function1(fld1), fld2 From Table1

--say table1 contains 2 rows
fld1 fld2
cat bird
dog ball

the view returns instead of
fld1 fld2 fld1 fl2
cat bird cattest bird
dog ball dogtest ball

But if I change the udf Set @parm1 = @parm1 + 'test'
to Set @parm1 = 'test' + @parm1 now the view returns

fld1 fld2
testcat bird
testdog ball

This udf resides in the adp. If I run the same udf in Sql
Server (2000) -udf owned by dbo instead of joeUser - on
the table owned by joeUser, I get the same results as the
adp view. But if I run dbo.Function1 on a dbo owned
table, then I get cattest, dogtest, the correct results.

Is there a difference between creating a udf in the adp or
creating the same udf in Sql Server? Is there a way to
compensate for this difference?

Thanks,
JD
Click to expand...


.
Click to expand...
 
Looks like that a char() or nchar() is used instead of a varchar() or
nvarchar() but I don't know why.

S. L.

JD said:
Thanks. I made sure I had [joeUser].[Function1] and I
applied the N'. What I did observe (with and without the
N' and with dbo. or joeUser) is that 'dog ' is
selected when the view comes up in the adp. So spaces are
in fact being appended, but not the text. It must be a
syntax thing with the adp. Just what?
-----Original Message-----
If you have access to Enterprise Manager, then you should make sure that
joeUser.Function1 is really [joeUser].[Function1] and not something else
like [joeUser].[joeUser.Function1] or [dbo]. [joeUser.Function1].

It is also possible that the view has kept the old compilation or something
like that. You should make a second test but this time but using something
like: Set @parm1 = @parm1 + 'test2' .

Second, if the field type of fld1 if char(20) instead of varchar(20), then
you will get this result.

You can also try with another variable than @parm1 for storing the
intermediate result.

Finally, you should use N'test' instead of 'test'.

S. L.

I created a scalar user-defined function in an adp - like
this:
----------------------------------------
Create Function joeUser.Function1
(
@parm1 varchar(20)
)
Returns varchar(20)
As
Begin
Set @parm1 = @parm1 + 'test'
Return @parm1
End
------------------------------------------

If I click on this function - I get the dialog box asking
for a param -- I enter 'shmo' and it returns 'shmotest' as
epxected. But if I run this udf in a view like this:

Select joeUser.Function1(fld1), fld2 From Table1

--say table1 contains 2 rows
fld1 fld2
cat bird
dog ball

the view returns instead of
fld1 fld2 fld1 fl2
cat bird cattest bird
dog ball dogtest ball

But if I change the udf Set @parm1 = @parm1 + 'test'
to Set @parm1 = 'test' + @parm1 now the view returns

fld1 fld2
testcat bird
testdog ball

This udf resides in the adp. If I run the same udf in Sql
Server (2000) -udf owned by dbo instead of joeUser - on
the table owned by joeUser, I get the same results as the
adp view. But if I run dbo.Function1 on a dbo owned
table, then I get cattest, dogtest, the correct results.

Is there a difference between creating a udf in the adp or
creating the same udf in Sql Server? Is there a way to
compensate for this difference?

Thanks,
JD
Click to expand...


.
Click to expand...
Click to expand...
 
I will check that out. Thanks.

-----Original Message-----
Looks like that a char() or nchar() is used instead of a varchar() or
nvarchar() but I don't know why.

S. L.

Thanks. I made sure I had [joeUser].[Function1] and I
applied the N'. What I did observe (with and without the
N' and with dbo. or joeUser) is that 'dog ' is
selected when the view comes up in the adp. So spaces are
in fact being appended, but not the text. It must be a
syntax thing with the adp. Just what?
-----Original Message-----
If you have access to Enterprise Manager, then you
Click to expand...
should
make sure that
joeUser.Function1 is really [joeUser].[Function1] and
Click to expand...
not
something else
like [joeUser].[joeUser.Function1] or [dbo]. [joeUser.Function1].

It is also possible that the view has kept the old compilation or something
like that. You should make a second test but this time but using something
like: Set @parm1 = @parm1 + 'test2' .

Second, if the field type of fld1 if char(20) instead of varchar(20), then
you will get this result.

You can also try with another variable than @parm1 for storing the
intermediate result.

Finally, you should use N'test' instead of 'test'.

S. L.

I created a scalar user-defined function in an adp - like
this:
----------------------------------------
Create Function joeUser.Function1
(
@parm1 varchar(20)
)
Returns varchar(20)
As
Begin
Set @parm1 = @parm1 + 'test'
Return @parm1
End
Click to expand...
returns 'shmotest'
as
epxected. But if I run this udf in a view like this:

Select joeUser.Function1(fld1), fld2 From Table1

--say table1 contains 2 rows
fld1 fld2
cat bird
dog ball

the view returns instead of
fld1 fld2 fld1 fl2
cat bird cattest bird
dog ball dogtest ball

But if I change the udf Set @parm1 = @parm1 + 'test'
to Set @parm1 = 'test' + @parm1 now the view returns

fld1 fld2
testcat bird
testdog ball

This udf resides in the adp. If I run the same udf in Sql
Server (2000) -udf owned by dbo instead of joeUser - on
the table owned by joeUser, I get the same results as the
adp view. But if I run dbo.Function1 on a dbo owned
table, then I get cattest, dogtest, the correct results.

Is there a difference between creating a udf in the
Click to expand...
adp
or
creating the same udf in Sql Server? Is there a way to
compensate for this difference?

Thanks,
JD



.
Click to expand...
Click to expand...


.
Click to expand...
 
Hello JD:
You wrote in conference microsoft.public.access.adp.sqlserver on Wed, 6 Apr
2005 13:23:46 -0700:

J> Select joeUser.Function1(fld1), fld2 From Table1

J> --say table1 contains 2 rows
J> fld1 fld2
J> cat bird
J> dog ball

J> the view returns instead of
J> fld1 fld2 fld1 fl2
J> cat bird cattest bird
J> dog ball dogtest ball


Try to widen the first column in the datagrid. Chances are, "test" will show
up after N spaces - in case your fld1 is something like char(50).


Vadim
 
Back
Top