.NET SQL Server stored fn not working in Where clause

General discussion forum about all databases other than SqlBase.
a_sivababu
India
Posts: 505
Joined: 10 Mar 2017, 08:46
Location: Chennai,India

.NET SQL Server stored fn not working in Where clause

Post by a_sivababu » 12 Oct 2014, 04:59

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
You do not have the required permissions to view the files attached to this post.

VEspinal

Re: .NET SQL Server stored fn not working in Where clause

Post by VEspinal » 13 Oct 2014, 22:40

Stored Fn - Works.png
Hi Siva:

It is working for me.

See attached picture.

HTH

Regards,
Victor Espinal
You do not have the required permissions to view the files attached to this post.

a_sivababu
India
Posts: 505
Joined: 10 Mar 2017, 08:46
Location: Chennai,India

Re: .NET SQL Server stored fn not working in Where clause

Post by a_sivababu » 14 Oct 2014, 15:44

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

Return to “General Discussion”

Who is online

Users browsing this forum: [Ccbot] and 0 guests