Accessing table-valued Functions using ADO

[chriscboy]

Hi,

I am using SQL2005 and I have a table-valued function called fn_GetCompanies which returns a table of company names. The function works fine in SQL, but when I try and access the function from a client app using ADO I am getting the following error message:

OLE IDDispatch exception code 0 from Microsoft OLE DB Provider for SQL Server: "fn_GetCompanies" is not a recognised function name...

The code below is how I am calling the function from ADO:
* Execute function against SQL databases
oCmd.ActiveConnection = oConn
oCmd.CommandText = "SELECT * FROM ::fn_GetCompanies()"
oRecs = oCmd.Execute

I am using ADO 2.7. Thanks your help!

 

[NoCoolHandle]

What happens if you change it to..

oCmd.ActiveConnection = oConn
oCmd.CommandText = "SELECT * FROM [blue]dbo[/blue].fn_GetCompanies()"
oRecs = oCmd.Execute

 

[SQLDenis]

try SELECT * FROM dbo.fn_GetCompanies()

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[chriscboy]

Thanks for the help. The command SELECT * FROM dbo.fn_GetCompanies() works fine when using it from SQL2005, but not in the client app. In the client app I am getting the following error message:

OLE IDispatch exception code 0 from Microsoft OLE DB Provider for SQL Server: OLE DB provider "VFPOLEDB" for linked server "VFP_DATABASE" returned message "Invalid path or filename"...

On SQL2005 I have a linked server which is using OLEDB to connect to a Visual FoxPro database somewhere else on our server.

The linked server is working fine from SQL2005 as I can run the function fn_GetCompanies() which returns a list of companies from the Visual FoxPro database.

The path for the linked server is \\fileserver\testdb\databases\live\ems.dbc

My sql server is situated at
\\SQL2005\

Any ideas what could be causing this?

Thanks for your help so far

Chris

 

[NoCoolHandle]

Ok.. my guess is security and login context...

I just ran some tests and ado.2.7 seems ok with simple table style functions... Your error also points towards the linked server connection.

My big question would be is your client connecting the same way that the sql qery tool is? I.E. same connection string (login data etc..)

the following code worked (vb 6 and sql 2005 ctp)

[green]sql code[/green]

Create Database crap
go
use crap
go
create function X ()
returns table
as
return (select 'test1' as 'c1' union select 'test2')
go -- the following line tests from sql..
select * from x()

[green]vb code[/green]

Private Sub Command1_Click()
Dim c As ADODB.Connection
Set c = New ADODB.Connection
Dim r As ADODB.Recordset
Set r = New ADODB.Recordset
c.Open "provider=sqloledb;server=lat600\sql2005;integrated security=sspi;database=crap"
r.Open "select * from dbo.x()", c
End Sub

 

[chriscboy]

I agree with what you are saying.

One interesting thing is that this code fails at the Open command, unless I manually connect the SQL2005 machine via the Run command e.g. Click Start, Run, then type \\SQL2005\c$. After entering the user id and password, I can then run the code below and I get a connection, but the query to run the database function still fails.

I am new to SQL and providing connnections to SQL2005 databases via OLEDB. Is there anywhere I can get more information about providing connection strings as that where I am going wrong, I think!

Below is the code I am using:

oConn = CREATEOBJECT("ADODB.Connection")
oRecs = CREATEOBJECT("ADODB.Recordset")
oCmd = CREATEOBJECT("ADODB.Command")

* This connection string does not work if you do not connect to the computer first
cQuery = "SELECT * FROM dbo.Template"
cDatabase = "emsTarget"
cConn = "Provider=SQLOLEDB;Server=SQL2005;Database="+cDatabase+";Integrated Security=SSPI"
oConn.Open(cConn)
IF oConn.State = 1

* Execute query against SQL database
oRecs.CursorType = 1 && adOpenKeyset
oRecs.LockType = 1 && adLockReadOnly
oRecs.Open(cQuery,oConn)
ShowData(oRecs)
oRecs.Close

* Execute function against SQL databases
oCmd.ActiveConnection = oConn
oCmd.CommandText = "SELECT * FROM dbo.fn_GetCompanies()"
oRecs = oCmd.Execute
ShowData(oRecs)
oRecs.Close

ENDIF

The error mentioned in the previous post is occuring on the line: oRecs = oCmd.Execute
Which is the line that accesses the function that using the VFP OLEDB provider.

 

[chriscboy]

I have just changed the linked server from the VFPOLEDB provider to the "OLEDB For ODBC Drivers" provider and it works!

Something tells me there might be a problem with the VFPOLEDB driver.

 

[NoCoolHandle]

Glad you got it working..