Can I access SQL Server from client Linux?

Can I access SQL Server from client Linux?
And Any functions in Postgre are in SqlServer?By access to you mean that you want to code a program to connect to and
work with SQL Server? If so, then yes...If you're on Linux you'll have
to use ODBC or JDBC (if you have the JDBC driver for SQL Server
installed on the machine hosting SQL Server) and the appropriate
connection string (which I don't remember right now, sorry, but it
should be somewhere if you Google it or probably someone in the
newsgroup remembers it) in your program.
If you want something that you can use to manager/administrate your SQL
Server from Linix (like Enterprise Manager/Management Studio as you
would have on Windows) then sorry but I am not aware of any tools out
there for this purpose. Maybe someone else can help on this?
As for Postgre functions in SQL Server. Well, I'm not sure...All the
standard SQL you know should cross over fine but I would suggest taking
a bit of time to learn Transact-SQL and what it has to offer (or at
least have a reference handy). Thankfully, SQL Server Books Online can
be accessed from the web on:
SQL Server 2005: http://msdn2.microsoft.com/en-us/library/ms130214.aspx
SQL Server 2000:
http://msdn.microsoft.com/library/d...
ap1.asp
Hope that helps a little but sorry if it didn't

can define UDF in DLL

Dear Friends
can I write some functions in DLL and use it in msmsql as udf and in sql
statements? how?
any advice?
Thanks
Tarvirdi
answered in microsoft.public.sqlserver.programming
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"M_Tarvirdi" <email@.tarvirdi.com> wrote in message
news:eiDuCG9AGHA.2664@.TK2MSFTNGP15.phx.gbl...
> Dear Friends
> can I write some functions in DLL and use it in msmsql as udf and in sql
> statements? how?
> any advice?
> Thanks
> Tarvirdi
>

can define UDF in DLL

Dear Friends
can I write some functions in DLL and use it in msmsql as udf and in sql
statements? how?
any advice?
Thanks
TarvirdiHi
SQL Server 2005 allows you to create .NET CLR objects that can be used
inside SQL Server.
http://www.microsoft.com/sql/prodin...ation-demo.mspx
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"M_Tarvirdi" <email@.tarvirdi.com> wrote in message
news:e0DgGG9AGHA.2664@.TK2MSFTNGP15.phx.gbl...
> Dear Friends
> can I write some functions in DLL and use it in msmsql as udf and in sql
> statements? how?
> any advice?
> Thanks
> Tarvirdi
>|||The best way to implement this depends on what version of SQL Server you are
using.
Extended Stored Procedure Architecture
http://msdn.microsoft.com/library/d...r />
_67vp.asp
Adding an Extended Stored Procedure to SQL Server
http://msdn2.microsoft.com/en-us/library/ms164653.aspx
For reasons of performance, security and maintainability, it is best to
avoid the use of external library function calls unless there is a very
special need. As an example, if you are basically wanting access to
functions for string parsing or financial calculations, then this could be
implemented using advanced SQL techniques or perhaps implemented at the
application level.
"M_Tarvirdi" <email@.tarvirdi.com> wrote in message
news:e0DgGG9AGHA.2664@.TK2MSFTNGP15.phx.gbl...
> Dear Friends
> can I write some functions in DLL and use it in msmsql as udf and in sql
> statements? how?
> any advice?
> Thanks
> Tarvirdi
>

can any one help in writing quries to sqlserver 2005 analysis service

I just want to calculate the measures using functions ie i want to calculate the YTD MTD and QTD . Can anyone help me out

Have you tried using the time intelligence wizard to do this? Depending on the service pack that you have it may not write the mose efficient MDX, but at least it should get you started.

Can aggregate functions be used in a check-constraint or a computed-column?

Hi,

Can aggregate functions (e.g. Sum, Avg) be used in a check contraint or in a table's computed column?

Thanks!

Green:

I don't think you can use subqueries for either circumstance; therefore, I am not sure that you can apply either one to a table. I think that the best you can do for a function is a scalar function. Can somebody else verify this?

Dave

|||evn i dont think its possible...as they r the values that can be entered for a particular column , while aggregate function will need a group....still will check it out soon and confirm..|||

You cannot do it directly, but as Dave suggested, you can do it by creating a function. Here's a simple example

create function SumTestOne()
returns int
as
begin
declare @.retVal int
select @.retVal = sum(one) from Test
return @.retVal
end

create table Test
(one int,
two int,
check (two > dbo.SumTestOne()))

insert Test values (100, 200)

select * from Test

|||Thanks for the suggestion to use a function. Tried it and it works well!

Calulated Fields vs UDF's

Does anyone have any suggestions on the use of Calculated fields over scalar
User Defined functions? I would think that the calulated field may be faster
than a scalar UDF but I don't know much about the mechanisms SQL uses to run
each method. Also, where might Table-valued UDF's possibly fit into this
scenario?
thank you for your time.Scalar UDFs (in SQL Server 2000) can be fairly slow, depending on the
implementation. If the same code can be placed in-line in a Select, for
example, the select will run faster than the version calling the UDF. Often
much, much faster.
I have not done a performance comparison, but with that in mind I would
expect a calculated field to run faster than a scalar UDF.
Table-valued UDFs return tables, so they are not a good fit for
high-performance operations on a column. The performance of table-valued
UDFs can be summed up this way:
In-line table valued UDF = View with parameters, therefore the UDF is
'compiled into' the plan if used in a join.
Multi-statement table valued UDF = Stored Procedure that returns a table.
If included in a join, it executes first and returns a result set.
RLF
"J. Askey" <JAskey@.discussions.microsoft.com> wrote in message
news:44F255E8-8DE8-4449-9104-C3463EEFD3F4@.microsoft.com...
> Does anyone have any suggestions on the use of Calculated fields over
> scalar
> User Defined functions? I would think that the calulated field may be
> faster
> than a scalar UDF but I don't know much about the mechanisms SQL uses to
> run
> each method. Also, where might Table-valued UDF's possibly fit into this
> scenario?
>
> thank you for your time.|||Scalar UDFs are slow when they must do a lookup (i.e., they contain a
SELECT statement). When this happens it serializes your reads such that
your queries that contain the scalar UDF behave like cursor operations.
If your UDF simply performs a calculation without any lookups, the
performance should be similar to computed columns. I think you can
create indexes on computed columns though, but I'm not totally certain.
-Alan|||Thank you RLF and Alan for your explanations. My column is indeed a general
computation on other columns in the table so it sounds like they may be
similar in performance between both methods. The one advantage of using a
scalar UDF that I have figured out is that if I have multiple tables using
this similar computation, then I simply have to make the change in one place
to effect both table queries. I suppose I could simply call the UDF in the
calculated field as well to centralize the definition.
I typically would have created a veiw with a call tothe UDF with in it and
on top of the base table columns but I wanted to careful not to spread my
data access out all over the place for columns that might naturally seem to
be contained in the base table.
Maybe I will do a little playing with this today and post some varied
results using these different approaches. It might be interesting.
"Alan Samet" wrote:

> Scalar UDFs are slow when they must do a lookup (i.e., they contain a
> SELECT statement). When this happens it serializes your reads such that
> your queries that contain the scalar UDF behave like cursor operations.
> If your UDF simply performs a calculation without any lookups, the
> performance should be similar to computed columns. I think you can
> create indexes on computed columns though, but I'm not totally certain.
>
> -Alan
>|||A few more details.
SQL Server's Query Optimizer does not understand the output distributions of
UDFs. As such, it can cause cardinality estimates in query plan generation
to be sub-optimal.
(Eventually, we may be able to improve this story in a future release)
What I tell customers now - if you can write it as scalar logic, please do
so. If you need to use a UDF, please consider a computed column over the
result of this as well so that the optimzier can create the statistics it
needs to do a good job in plan generation.
I would specifically *not* recommend the use of UDFs to perform "singleton
(index) lookups" into another table. I've seen this pattern used in a
number of deployments. While it "works", it is very hard for the query
optimizer to pick a proper join order (which means that sometimes it picks a
poor order and plan performance can become very slow). If you can represent
these as joins, I recommend that you do so.
Best of luck to you,
Conor Cunningham
SQL Server Query Optimization Development Lead
"J. Askey" <JAskey@.discussions.microsoft.com> wrote in message
news:0940C18A-9E28-4F12-B926-95B00B336D42@.microsoft.com...
> Thank you RLF and Alan for your explanations. My column is indeed a
> general
> computation on other columns in the table so it sounds like they may be
> similar in performance between both methods. The one advantage of using a
> scalar UDF that I have figured out is that if I have multiple tables using
> this similar computation, then I simply have to make the change in one
> place
> to effect both table queries. I suppose I could simply call the UDF in the
> calculated field as well to centralize the definition.
> I typically would have created a veiw with a call tothe UDF with in it and
> on top of the base table columns but I wanted to careful not to spread my
> data access out all over the place for columns that might naturally seem
> to
> be contained in the base table.
> Maybe I will do a little playing with this today and post some varied
> results using these different approaches. It might be interesting.
> "Alan Samet" wrote:
>

Calulated Fields in DataSet

Does anyone know if Reporting Services 2005 support calculated fields
in the dataset with RunningValue and Previous functions? Everytime I
trie it gives me an error?
Here's what I did, on the dataset I added a calulated field and put the
following expression in "=RunningValue(fields!xxxx, Sum)" and when I
hit preview it returned an error?
Is this a limitation of reporting services?Amarnath,
Thanks for the reply, I know this function works in the tables but I
wanted to use the output in a chart. Do you have any suggestions on
doing that? Can I use table calculated data in a chart?
- tuong
On Jan 22, 2:29 am, Amarnath <Amarn...@.discussions.microsoft.com>
wrote:
> Dont give this in a calculated field instead insert say a "Sr.No" column in
> the left side of your table and just in the properties put this syntax and it
> will work.
> Amarnath
>
> "tuong.k...@.gmail.com" wrote:
> > Does anyone know if Reporting Services 2005 support calculated fields
> > in the dataset with RunningValue and Previous functions? Everytime I
> > trie it gives me an error?
> > Here's what I did, on the dataset I added a calulated field and put the
> > following expression in "=RunningValue(fields!xxxx, Sum)" and when I
> > hit preview it returned an error?
> > Is this a limitation of reporting services... Hide quoted text -- Show quoted text -|||Infact to give running value you need to give scope which you are not and you
cant because it is like global definition for the table.
Amarnath
"tuong.k.lam@.gmail.com" wrote:
> Amarnath,
> Thanks for the reply, I know this function works in the tables but I
> wanted to use the output in a chart. Do you have any suggestions on
> doing that? Can I use table calculated data in a chart?
> - tuong
> On Jan 22, 2:29 am, Amarnath <Amarn...@.discussions.microsoft.com>
> wrote:
> > Dont give this in a calculated field instead insert say a "Sr.No" column in
> > the left side of your table and just in the properties put this syntax and it
> > will work.
> >
> > Amarnath
> >
> >
> >
> > "tuong.k...@.gmail.com" wrote:
> > > Does anyone know if Reporting Services 2005 support calculated fields
> > > in the dataset with RunningValue and Previous functions? Everytime I
> > > trie it gives me an error?
> >
> > > Here's what I did, on the dataset I added a calulated field and put the
> > > following expression in "=RunningValue(fields!xxxx, Sum)" and when I
> > > hit preview it returned an error?
> >
> > > Is this a limitation of reporting services... Hide quoted text -- Show quoted text -
>

Calling VBA functions from SQL

I have a rather complex function (part of a production planning engine) that is written in VBA, and is part of my front end app. I'd like to be able to somehow call this function from either a sproc or DTS package in SQL. Is this possible, or am I going to have to convert the function to a SQL sproc (ugh)?Plop the VBA script into an Active-X task within a DTS package, and you should be "good to go". Another choice that folks often overlook is to make the VBA an ActiveX Script step in a SQL Agent job.

-PatP|||Thanks, Pat.

I was thinking along those lines, so I did a little more research while waiting for an answer. It appears that VBScript (and ActiveX??) doesn't like arrays, especially the dynamic variety. I think what I'm trying to do may be misuse of a sproc, but since I want the task to run as part of a rather large string of scheduled tasks, I'm stuck with converting it.

Calling user-defined functions in OLE DB Command transformation

Hi

We have a user-defined function that can be called directly via SQL (in SQL Server Management Studio) without error. We would like to use this function to populate a column, whist data is being processed within Integration Services. Using an OLE DB Command transformation to achieve this would seem the most appropriate.

The following was inserted for the SQLCommand property:

EXEC ? = dbo.GetOrderlineStatus(@.dt_required = ?, @.dt_invoice = ?, @.dt_despatch = ?, @.ch_status = ?, @.si_suffix = ?, @.re_quantity = ?, @.vc_invoice_id = ?, @.vc_order_id = ?)

However, when the Refresh button is pressed we are presented with the error below:

Error at Load Orderline [OLE DB Command [15171]]: An OLE DB error has occurred. Error code: 0x8004E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x8004E14 Description: "Invalid parameter number".

If we use SET instead of EXEC (e.g. SET ? = dbo.GetOrderlineStatus(@.dt_required = ?, @.dt_invoice = ?, @.dt_despatch = ?, @.ch_status = ?, @.si_suffix = ?, @.re_quantity = ?, @.vc_invoice_id = ?, @.vc_order_id = ?)) the following error is produced:

Error at Load Orderline [OLE DB Command [15171]]: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Syntax error, permission violation, or other nonspecific error".

Any assistance would be greatly appreciated.

Thanks

Neil

You cannot use SET or EXEC with a UDF. You need to use

SELECT @.ReturnVar = [dbo].fnYourFunctionName ( @.InputVar )

Calling user-defined functions in OLE DB Command transformation

Hi

We have a user-defined function that can be called directly via SQL (in SQL Server Management Studio) without error. We would like to use this function to populate a column, whist data is being processed within Integration Services. Using an OLE DB Command transformation to achieve this would seem the most appropriate.

The following was inserted for the SQLCommand property:

EXEC ? = dbo.GetOrderlineStatus(@.dt_required = ?, @.dt_invoice = ?, @.dt_despatch = ?, @.ch_status = ?, @.si_suffix = ?, @.re_quantity = ?, @.vc_invoice_id = ?, @.vc_order_id = ?)

However, when the Refresh button is pressed we are presented with the error below:

Error at Load Orderline [OLE DB Command [15171]]: An OLE DB error has occurred. Error code: 0x8004E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x8004E14 Description: "Invalid parameter number".

If we use SET instead of EXEC (e.g. SET ? = dbo.GetOrderlineStatus(@.dt_required = ?, @.dt_invoice = ?, @.dt_despatch = ?, @.ch_status = ?, @.si_suffix = ?, @.re_quantity = ?, @.vc_invoice_id = ?, @.vc_order_id = ?)) the following error is produced:

Error at Load Orderline [OLE DB Command [15171]]: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Syntax error, permission violation, or other nonspecific error".

Any assistance would be greatly appreciated.

Thanks

Neil

I apologize if I missed something obvious, but can you tell me where in the product you're using this? Is it in a dgen plan?|||

I beleive this post belongs in the SS Integration Services Forum.

Alle

|||

EXEC ? = dbo.GetOrderlineStatus(@.dt_required = ?, @.dt_invoice = ?, @.dt_despatch = ?, @.ch_status = ?, @.si_suffix = ?, @.re_quantity = ?, @.vc_invoice_id = ?, @.vc_order_id = ?)

Even though you are in the wrong forum, I think the first parameter in red could be your problem. Check the assignment in the Input / Output properties of the Ole DB Command component. The names of the Inputs and Outputs must be specific "Param_0", "Param_1", etc.. Check SSIS help on the subject.

HTH
Alle

|||

Correct, this is an Integration Services question - I posted to the wrong forum, apologies for that.

The Input and Output Properties tab does not yet display any Inputs or Outputs, as the error prevents the creation of the parameters. As soon as the statement is entered into the SqlCommand box and the Refresh button pressed, the error is produced with no parameters created, hence preventing further progress.

Thanks

Neil

|||Moved to the SQL Server integration services forum :).|||

Did you resolve this? I'm having the same prob. too!

Cheers,

Tamim.

|||

Tamin,

The origianl poster seems to be providing a worng sintax to call the function...

Can you provide the syntax your are using and the error generated?

Calling user-defined functions in OLE DB Command transformation

Hi

We have a user-defined function that can be called directly via SQL (in SQL Server Management Studio) without error. We would like to use this function to populate a column, whist data is being processed within Integration Services. Using an OLE DB Command transformation to achieve this would seem the most appropriate.

The following was inserted for the SQLCommand property:

EXEC ? = dbo.GetOrderlineStatus(@.dt_required = ?, @.dt_invoice = ?, @.dt_despatch = ?, @.ch_status = ?, @.si_suffix = ?, @.re_quantity = ?, @.vc_invoice_id = ?, @.vc_order_id = ?)

However, when the Refresh button is pressed we are presented with the error below:

Error at Load Orderline [OLE DB Command [15171]]: An OLE DB error has occurred. Error code: 0x8004E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x8004E14 Description: "Invalid parameter number".

If we use SET instead of EXEC (e.g. SET ? = dbo.GetOrderlineStatus(@.dt_required = ?, @.dt_invoice = ?, @.dt_despatch = ?, @.ch_status = ?, @.si_suffix = ?, @.re_quantity = ?, @.vc_invoice_id = ?, @.vc_order_id = ?)) the following error is produced:

Error at Load Orderline [OLE DB Command [15171]]: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Syntax error, permission violation, or other nonspecific error".

Any assistance would be greatly appreciated.

Thanks

Neil

I apologize if I missed something obvious, but can you tell me where in the product you're using this? Is it in a dgen plan?|||

I beleive this post belongs in the SS Integration Services Forum.

Alle

|||

EXEC ? = dbo.GetOrderlineStatus(@.dt_required = ?, @.dt_invoice = ?, @.dt_despatch = ?, @.ch_status = ?, @.si_suffix = ?, @.re_quantity = ?, @.vc_invoice_id = ?, @.vc_order_id = ?)

Even though you are in the wrong forum, I think the first parameter in red could be your problem. Check the assignment in the Input / Output properties of the Ole DB Command component. The names of the Inputs and Outputs must be specific "Param_0", "Param_1", etc.. Check SSIS help on the subject.

HTH
Alle

|||

Correct, this is an Integration Services question - I posted to the wrong forum, apologies for that.

The Input and Output Properties tab does not yet display any Inputs or Outputs, as the error prevents the creation of the parameters. As soon as the statement is entered into the SqlCommand box and the Refresh button pressed, the error is produced with no parameters created, hence preventing further progress.

Thanks

Neil

|||Moved to the SQL Server integration services forum :).|||

Did you resolve this? I'm having the same prob. too!

Cheers,

Tamim.

|||

Tamin,

The origianl poster seems to be providing a worng sintax to call the function...

Can you provide the syntax your are using and the error generated?

calling user-defined functions in another DB

I have a number of databases that require a set of common functions. I'd like to place all those functions in a central DB. I'm having trouble calling them using the syntax FunctionDB.GetParamLength() for example, which works within the FunctionDB database.

Any ideas/suggestions? I really don't want to maintain seperate copies of the functions across 5+ databases.

You have to qualify the function name with owner name, as in

FunctionDB.dbo.GetParamLength()

|||I've tried that, unfortunately no luck.|||Should work for you like this here:

USE Master

GO

CREATE FUNCTION dbo.DisplaySomething()

RETURNS VARCHAR(10)

AS

BEGIN

RETURN('Something')

END

GO

USE AdventureWorks

GO

SELECT master.dbo.DisplaySomething()

USE Master

GO

DROP FUNCTION dbo.DisplaySomething

Is the database case sensitive ? Then you have use the right case sensitive name. Is the owner of the function dbo ? Otherwise you have to name the original owner.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||There was an error within the function I was calling which confused me, thanks for putting me on the right track :)

Calling user-created functions with default values

The documentation talks about default values, but gives no examples. I'm
trying to get this to work...
ALTER FUNCTION FirstDayOfYear(@.date datetime = getdate) RETURNS datetime
BEGIN
RETURN convert(datetime, '1/1/' + convert(varchar, YEAR(@.date)))
END
They say you need to pass in "default", but I can't figure it out. I tried...
select dbo.FirstDayOfYear(default)
select default dbo.FirstDayOfYear()
select dbo.FirstDayOfYear() default
Any pointers?
Maury
I do not know why SQL Server let us create the function with the keyword
GETDATE as a parameter's default value. GETDATE is a function, so it should
be:
...
@.date datetime = getdate()
...
but then sql server give an error and this make sense. Try using a number
and you will see that it works (using default keyword when you call the
function).
alter FUNCTION FirstDayOfYear(
@.date datetime = 32500
)
RETURNS datetime
as
BEGIN
RETURN convert(datetime, '1/1/' + convert(varchar, YEAR(@.date)))
END
go
select dbo.FirstDayOfYear(default)
go
I think you have to get rid of the default and allways pass a value to this
function.
AMB
"Maury Markowitz" wrote:

> The documentation talks about default values, but gives no examples. I'm
> trying to get this to work...
> ALTER FUNCTION FirstDayOfYear(@.date datetime = getdate) RETURNS datetime
> BEGIN
> RETURN convert(datetime, '1/1/' + convert(varchar, YEAR(@.date)))
> END
> They say you need to pass in "default", but I can't figure it out. I tried...
> select dbo.FirstDayOfYear(default)
> select default dbo.FirstDayOfYear()
> select dbo.FirstDayOfYear() default
> Any pointers?
> Maury
|||That's actually looking for the string, 'getdate' -- actually calling the
GETDATE function requires parens (GETDATE()) -- however, SQL Server will not
accept that (I'm not sure why). You'll have to actually call the function
with GETDATE() as the argument to do what you need.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:24DEC598-40A2-491E-A975-D5E9026BD10D@.microsoft.com...
> The documentation talks about default values, but gives no examples. I'm
> trying to get this to work...
> ALTER FUNCTION FirstDayOfYear(@.date datetime = getdate) RETURNS datetime
> BEGIN
> RETURN convert(datetime, '1/1/' + convert(varchar, YEAR(@.date)))
> END
> They say you need to pass in "default", but I can't figure it out. I
tried...
> select dbo.FirstDayOfYear(default)
> select default dbo.FirstDayOfYear()
> select dbo.FirstDayOfYear() default
> Any pointers?
> Maury
|||"Adam Machanic" wrote:

> That's actually looking for the string, 'getdate' -- actually calling the
> GETDATE function requires parens (GETDATE()) -- however, SQL Server will not
> accept that (I'm not sure why). You'll have to actually call the function
> with GETDATE() as the argument to do what you need.
Got it. It doesn't really need to have this feature -- a default that is --
but it would make the callee syntax a little nicer.
Maury

Calling user-created functions with default values

The documentation talks about default values, but gives no examples. I'm
trying to get this to work...
ALTER FUNCTION FirstDayOfYear(@.date datetime = getdate) RETURNS datetime
BEGIN
RETURN convert(datetime, '1/1/' + convert(varchar, YEAR(@.date)))
END
They say you need to pass in "default", but I can't figure it out. I tried..
.
select dbo.FirstDayOfYear(default)
select default dbo.FirstDayOfYear()
select dbo.FirstDayOfYear() default
Any pointers?
MauryI do not know why SQL Server let us create the function with the keyword
GETDATE as a parameter's default value. GETDATE is a function, so it should
be:
...
@.date datetime = getdate()
...
but then sql server give an error and this make sense. Try using a number
and you will see that it works (using default keyword when you call the
function).
alter FUNCTION FirstDayOfYear(
@.date datetime = 32500
)
RETURNS datetime
as
BEGIN
RETURN convert(datetime, '1/1/' + convert(varchar, YEAR(@.date)))
END
go
select dbo.FirstDayOfYear(default)
go
I think you have to get rid of the default and allways pass a value to this
function.
AMB
"Maury Markowitz" wrote:

> The documentation talks about default values, but gives no examples. I'm
> trying to get this to work...
> ALTER FUNCTION FirstDayOfYear(@.date datetime = getdate) RETURNS datetime
> BEGIN
> RETURN convert(datetime, '1/1/' + convert(varchar, YEAR(@.date)))
> END
> They say you need to pass in "default", but I can't figure it out. I tried
..
> select dbo.FirstDayOfYear(default)
> select default dbo.FirstDayOfYear()
> select dbo.FirstDayOfYear() default
> Any pointers?
> Maury|||That's actually looking for the string, 'getdate' -- actually calling the
GETDATE function requires parens (GETDATE()) -- however, SQL Server will not
accept that (I'm not sure why). You'll have to actually call the function
with GETDATE() as the argument to do what you need.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:24DEC598-40A2-491E-A975-D5E9026BD10D@.microsoft.com...
> The documentation talks about default values, but gives no examples. I'm
> trying to get this to work...
> ALTER FUNCTION FirstDayOfYear(@.date datetime = getdate) RETURNS datetime
> BEGIN
> RETURN convert(datetime, '1/1/' + convert(varchar, YEAR(@.date)))
> END
> They say you need to pass in "default", but I can't figure it out. I
tried...
> select dbo.FirstDayOfYear(default)
> select default dbo.FirstDayOfYear()
> select dbo.FirstDayOfYear() default
> Any pointers?
> Maury|||"Adam Machanic" wrote:

> That's actually looking for the string, 'getdate' -- actually calling the
> GETDATE function requires parens (GETDATE()) -- however, SQL Server will n
ot
> accept that (I'm not sure why). You'll have to actually call the function
> with GETDATE() as the argument to do what you need.
Got it. It doesn't really need to have this feature -- a default that is --
but it would make the callee syntax a little nicer.
Maury

Calling user-created functions with default values

The documentation talks about default values, but gives no examples. I'm
trying to get this to work...
ALTER FUNCTION FirstDayOfYear(@.date datetime = getdate) RETURNS datetime
BEGIN
RETURN convert(datetime, '1/1/' + convert(varchar, YEAR(@.date)))
END
They say you need to pass in "default", but I can't figure it out. I tried...
select dbo.FirstDayOfYear(default)
select default dbo.FirstDayOfYear()
select dbo.FirstDayOfYear() default
Any pointers?
MauryI do not know why SQL Server let us create the function with the keyword
GETDATE as a parameter's default value. GETDATE is a function, so it should
be:
...
@.date datetime = getdate()
...
but then sql server give an error and this make sense. Try using a number
and you will see that it works (using default keyword when you call the
function).
alter FUNCTION FirstDayOfYear(
@.date datetime = 32500
)
RETURNS datetime
as
BEGIN
RETURN convert(datetime, '1/1/' + convert(varchar, YEAR(@.date)))
END
go
select dbo.FirstDayOfYear(default)
go
I think you have to get rid of the default and allways pass a value to this
function.
AMB
"Maury Markowitz" wrote:
> The documentation talks about default values, but gives no examples. I'm
> trying to get this to work...
> ALTER FUNCTION FirstDayOfYear(@.date datetime = getdate) RETURNS datetime
> BEGIN
> RETURN convert(datetime, '1/1/' + convert(varchar, YEAR(@.date)))
> END
> They say you need to pass in "default", but I can't figure it out. I tried...
> select dbo.FirstDayOfYear(default)
> select default dbo.FirstDayOfYear()
> select dbo.FirstDayOfYear() default
> Any pointers?
> Maury|||That's actually looking for the string, 'getdate' -- actually calling the
GETDATE function requires parens (GETDATE()) -- however, SQL Server will not
accept that (I'm not sure why). You'll have to actually call the function
with GETDATE() as the argument to do what you need.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:24DEC598-40A2-491E-A975-D5E9026BD10D@.microsoft.com...
> The documentation talks about default values, but gives no examples. I'm
> trying to get this to work...
> ALTER FUNCTION FirstDayOfYear(@.date datetime = getdate) RETURNS datetime
> BEGIN
> RETURN convert(datetime, '1/1/' + convert(varchar, YEAR(@.date)))
> END
> They say you need to pass in "default", but I can't figure it out. I
tried...
> select dbo.FirstDayOfYear(default)
> select default dbo.FirstDayOfYear()
> select dbo.FirstDayOfYear() default
> Any pointers?
> Maury|||"Adam Machanic" wrote:
> That's actually looking for the string, 'getdate' -- actually calling the
> GETDATE function requires parens (GETDATE()) -- however, SQL Server will not
> accept that (I'm not sure why). You'll have to actually call the function
> with GETDATE() as the argument to do what you need.
Got it. It doesn't really need to have this feature -- a default that is --
but it would make the callee syntax a little nicer.
Maury

Calling UDF in where clause

I am new to user-defined functions in SQL Server. Can someone please shed
some light on my question.
I am trying to call a user-defined function from within a where clause but I
am getting errors. If I move the same UDF call to the 'select' part, it
works. It appears to me that it is not possible to call a UDF from within a
where clause but I am not really sure. Can someone please let me know if i
t
is possible or not.David wrote:
> I am new to user-defined functions in SQL Server. Can someone please shed
> some light on my question.
> I am trying to call a user-defined function from within a where clause but
I
> am getting errors. If I move the same UDF call to the 'select' part, it
> works. It appears to me that it is not possible to call a UDF from within
a
> where clause but I am not really sure. Can someone please let me know if
it
> is possible or not.
Works perfectly... Post your query and your error message...|||That's good news.
If I do it this way, it works great:
SELECT activity_id, md_group_id, dbo.udfTest() AS dave
FROM dbo.ImpactedMD
WHERE (md_group_id = 2)
If I try this, it doesn't work.
SELECT activity_id, md_group_id, dbo.udfTest() AS dave
FROM dbo.ImpactedMD
WHERE dbo.udfTest()
I get this error and I have already tried everything I could think of.
"Line 1: Incorrect syntax near ')'. "
and
"Error in list of function arguments: 'dbo' not recognized. Unable to
parse query text."
Here is my UDF function:
CREATE FUNCTION dbo.udfTest()
RETURNS varchar(255) AS
BEGIN
return '(id = 2)'
END
"Tracy McKibben" wrote:

> David wrote:
> Works perfectly... Post your query and your error message...
>|||Where clause expects some form of evaluation. You are just providing a value
(the results of the udf).
WHERE dbo.udfTest()
is like
WHERE 2
So, add the evaluation criteria, compare the results of the udf to
something. For example,
WHERE dbo.udfTest() <> 0
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"David" <dilworth@.newsgroups.nospam> wrote in message
news:CECD5327-DC62-4C47-B998-B9C691CF2A06@.microsoft.com...
> That's good news.
> If I do it this way, it works great:
> SELECT activity_id, md_group_id, dbo.udfTest() AS dave
> FROM dbo.ImpactedMD
> WHERE (md_group_id = 2)
> If I try this, it doesn't work.
> SELECT activity_id, md_group_id, dbo.udfTest() AS dave
> FROM dbo.ImpactedMD
> WHERE dbo.udfTest()
> I get this error and I have already tried everything I could think of.
> "Line 1: Incorrect syntax near ')'. "
> and
> "Error in list of function arguments: 'dbo' not recognized. Unable to
> parse query text."
> Here is my UDF function:
> CREATE FUNCTION dbo.udfTest()
> RETURNS varchar(255) AS
> BEGIN
> return '(id = 2)'
> END
>
> "Tracy McKibben" wrote:
>|||That's not the way UDFs work in SQL. The function returns a value that can b
e
returned (if called from the SELECT statement) or that can be compared to
another value (if called from within the WHERE/HAVING clauses).
Like this:
SELECT activity_id, md_group_id, dbo.udfTest() AS dave
FROM dbo.ImpactedMD
WHERE (dbo.udfTest() = <some value or column name> )
Maybe:
SELECT activity_id, md_group_id, dbo.udfTest() AS dave
FROM dbo.ImpactedMD
WHERE (dbo.udfTest() = id)
Change your function to return a value, rather than what looks like part of
some dynamic SQL statement:
CREATE FUNCTION dbo.udfTest()
RETURNS varchar(255) AS
BEGIN
return '2'
END
ML
http://milambda.blogspot.com/|||So, I am not able to return a string from my UDF that has the value and the
column name?
something like: (column_name=2)
"ML" wrote:

> That's not the way UDFs work in SQL. The function returns a value that can
be
> returned (if called from the SELECT statement) or that can be compared to
> another value (if called from within the WHERE/HAVING clauses).
> Like this:
> SELECT activity_id, md_group_id, dbo.udfTest() AS dave
> FROM dbo.ImpactedMD
> WHERE (dbo.udfTest() = <some value or column name> )
> Maybe:
> SELECT activity_id, md_group_id, dbo.udfTest() AS dave
> FROM dbo.ImpactedMD
> WHERE (dbo.udfTest() = id)
>
> Change your function to return a value, rather than what looks like part o
f
> some dynamic SQL statement:
> CREATE FUNCTION dbo.udfTest()
> RETURNS varchar(255) AS
> BEGIN
> return '2'
> END
>
> ML
> --
> http://milambda.blogspot.com/|||You're trying to use a VARCHAR as a boolean comparison in your WHERE clause.
That doesn't work unless you're using dynamic SQL, which you're not.
"David" <dilworth@.newsgroups.nospam> wrote in message
news:CECD5327-DC62-4C47-B998-B9C691CF2A06@.microsoft.com...
> That's good news.
> If I do it this way, it works great:
> SELECT activity_id, md_group_id, dbo.udfTest() AS dave
> FROM dbo.ImpactedMD
> WHERE (md_group_id = 2)
> If I try this, it doesn't work.
> SELECT activity_id, md_group_id, dbo.udfTest() AS dave
> FROM dbo.ImpactedMD
> WHERE dbo.udfTest()
> I get this error and I have already tried everything I could think of.
> "Line 1: Incorrect syntax near ')'. "
> and
> "Error in list of function arguments: 'dbo' not recognized. Unable to
> parse query text."
> Here is my UDF function:
> CREATE FUNCTION dbo.udfTest()
> RETURNS varchar(255) AS
> BEGIN
> return '(id = 2)'
> END
>
> "Tracy McKibben" wrote:
>|||You could, but why? It's best to avoid dynamic SQL. Here's a very nice
article on dynamic SQL by Erland Sommarskog (a must-read):
http://www.sommarskog.se/dynamic_sql.html
ML
http://milambda.blogspot.com/|||Thanks for everyone help. I guess I can't use UDF in the way that I thought
I could. Let me explain what I'm trying to do and let me know the best way
I
should do it.
I am trying to construct my where clause in a way where the comparison (like
'=', '>=', or '<=') would change depending on a value from another column.
I
thought that I could use UDF to return a string but I guess I can't. Does
anyone else have anymore ideas. What about a case statement? Would that
work?
"Mike C#" wrote:

> You're trying to use a VARCHAR as a boolean comparison in your WHERE claus
e.
> That doesn't work unless you're using dynamic SQL, which you're not.
> "David" <dilworth@.newsgroups.nospam> wrote in message
> news:CECD5327-DC62-4C47-B998-B9C691CF2A06@.microsoft.com...
>
>|||DECLARE @.which_operator VARCHAR(2)
SELECT @.which_operator = '='
SELECT activity_id, md_group_id
FROM dbo.ImpactedMD
WHERE (@.which_operator = '=' AND md_group_id = 2)
OR (@.which_operator = '<=' AND md_group_id <= 2)
OR (@.which_operator = '>=' AND md_group_id >= 2)
I don't know if the OR's will have a seriously adverse affect on your query
time or not... depends on if SQL Server is smart enough to short-circuit the
WHERE clause.
"David" <dilworth@.newsgroups.nospam> wrote in message
news:99AACBDE-6EFA-4863-84D6-404759E3C4A3@.microsoft.com...
> Thanks for everyone help. I guess I can't use UDF in the way that I
> thought
> I could. Let me explain what I'm trying to do and let me know the best
> way I
> should do it.
> I am trying to construct my where clause in a way where the comparison
> (like
> '=', '>=', or '<=') would change depending on a value from another column.
> I
> thought that I could use UDF to return a string but I guess I can't.
> Does
> anyone else have anymore ideas. What about a case statement? Would that
> work?
> "Mike C#" wrote:
>