.NET SQL Server stored fn not working in Where clause

[a_sivababu]

Hi Gupta,
I feel the SQL Server, SQL Server Native, SqlClient for SQL Server, OLEDB for SQL Server though SqlConnectDotNet() is not working for all the cases as I get the issue in different places for different reasons.

However, the Sqlconnect() is working fine .NET in almost all the cases but I am not sure whether connection pooling is implemented in traditional SqlConnect().

In attached sample and tested in TD 6.2.2.2 .NET,
No row fetched if I user System.Data.SqlClient.
System Access Violation if I use OLEDB
Works fine If I use traditional sqlconnect()


script:
CREATE function [dbo].[DATE_TO_DATE](
@DateValue DATETIME,
@Dateformat INT )
RETURNS DATETIME
AS
BEGIN
RETURN CAST(CONVERT(VARCHAR(20),@DateValue,@Dateformat) AS DATETIME )
END

---------------
create table temptest(test datetime)
go
insert into temptest values(dbo.date_to_date(CURRENT_TIMESTAMP,106))

------------------


Thanks!

Siva A

[VEspinal]

Stored Fn - Works.png

Hi Siva:

It is working for me.

See attached picture.

HTH

Regards,
Victor Espinal

[a_sivababu]

Hi Victor,
Thanks. As mentioned, It does not work for the below connectivity. I am bit confused what type of connectivity will work properly.

Call SqlConnectDotNet( hSql, "Server=SERVER01;Database=TESTDB; User Id=sa;Password=sa;","System.Data.SqlClient", DBP_PROVIDER_SQLSERVER_SQLCLIENT )

OR

call SqlConnectDotNet( hSql, 'Provider=sqloledb;Data Source=SERVER01;Initial Catalog=TESTDB;
User Id=sa;Password=sa;', "", DBP_PROVIDER_SQLSERVER_OLEDB)


My question is that, Should I use only your connectivity for my development and forget the above connection type. Please advice.

Thanks
Siva A