linked server, adodc vs recordset

[frank ashley]

I've set up a linked server to the Index Service. Using the debugger in SQL i've tested my sproc and it returns the filename & size of various files.

On the UI side, if I attach an adodc to the sproc and a datacombo to the adodc i get a recordset returned. But, if I attempt to do this through a recordset I get -1 records returned.

Any ideas?

[paulw]

How are you attempting to get the data from the sp? I would suspect it is the way in which you are calling the sp and how you are using the parameters collection. Could you post the code and maybe the sp itself?

Cheers,

P.

[frank ashley]

Here's the sproc

CREATE proc spDocumentsInHoldingPool
as
SELECT *
FROM OpenQuery(roverholdingarea,
'SELECT FileName, DocAuthor,Size
FROM SCOPE('' "e:\ftptesting\rover\holdingpool" '')
WHERE FileName LIKE ''%.%''
order by FileName '
)

I've tried every permutation of opening a recordset known to Man. ie passing in a string as a connection, trying to use a command object etc

Whats really bugging me is that I can pull back the records using an adodc control then Set recordset=adodc1.recordset and this works!

[paulw]

Can you show me some of the code you are using to get the data back? I just want to see how you are approaching it.

There really shouldn't be a problem.

Cheers,

P.

[frank ashley]

Private Sub Command3_Click()
Dim con As ADODB.Connection
Dim rs3 = New ADODB.Recordset
Set con = New ADODB.Connection
Set rs3 = New ADODB.Recordset

con.ConnectionString = "Provider=SQLOLEDB.1;" & _
"Initial Catalog=Rover;Data Source=xxxx.xxxx.xxxx.xxxx;" & _
"Password=xxxxxxxxxxxx;User ID=RoverAdministration"
con.CursorLocation = adUseClient
con.Open

Set cmd.ActiveConnection = con
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "spDocumentsInHoldingPool"
rs3.Open "exec spdocumentsinholdingpool", con, , adCmdStoredProc

Set DataCombo3.RowSource = rs3
DataCombo3.ListField = "filename"

Debug.Print "rs3 " & rs3.RecordCount

End Sub


The above code results in -2147467900 'The command contained one or more errors'.

[paulw]

I have never done it that way.

Try:

Dim con As ADODB.Connection
Dim rs3 = New ADODB.Recordset
Set con = New ADODB.Connection
Set rs3 = New ADODB.Recordset

con.ConnectionString = "Provider=SQLOLEDB.1;" & _
"Initial Catalog=Rover;Data Source=xxxx.xxxx.xxxx.xxxx;" &_
"Password=xxxxxxxxxxxx;User ID=RoverAdministration"
con.CursorLocation = adUseClient
con.Open


cmd.CommandText = "spDocumentsInHoldingPool"
cmd.CommandType = adCmdStoredProc
cmd.Name = "MyLocalSP"
Set cmd.ActiveConnection = con
con.MyLocalSP rs3


Set DataCombo3.RowSource = rs3
DataCombo3.ListField = "filename"

My bit is in bold (obviously).

Let me know if it works for you.

Cheers,

P.

[frank ashley]

Didn't work I'm afraid.

error - -2147467900 'The command contained one or more errors'.

[paulw]

V. Strange,

why not try the connection method against a simple SELECT sp, ensure that that works first. This is odd but I have never used OpenQuery in an sp before.

Cheers,

P.

[frank ashley]

I tried that earlier, it works fine.


Arrgggghhhhh!

[frank ashley]

Err Oops!

I hadn't set the linked servers permissions.

I can now use

rs.open "exec spxxx",cn

but I get 0 records returned

[paulw]

/slap

Ok. What about my way of connecting? Does that work? You've got me worried now.

Cheers,

P.