Linking a MSSQL stored procedure to a report

[RockyRural]

I've written a VB6 application that uses a MSSQL 2000 server as the data source and make comprehensive use of stored procedures to access the tables.
My problem is linking a stored procedure to a report at runtime.

I have no problems doing it with a MSAccess database but when it comes to SQL server I can't seem to get it to work.

Can some please show me an example.

Bob

 

[synapsevampire]

You don't really link to a stored procedure, you just select it as the data source.

When you're in the Field Explorer selecting the data source to use, make sure that you click the Options button and check the Stored Procedures checkbox so that they're shown in the Data Explorer.

I use OLE DB (for SQL Server) as the connectivity for various reasons, I believe that you cannot use direct connectivity for SQL Server 2000 SP's, perhaps that's your problem.

Anyway, ODBC is a dying technology, so you might as well switch over.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[RockyRural]

synapsevampire, I understand what you have stated and I am infact using OLE DB to connect to the server.
At design time when building the report I can see the results from a stored query. I've substituted the fields in the report with a Activedata source(field definitions only).

When I go to attach the results of a recordset at runtime nothing happens, all I get is the sample data in the TTX file

This is the code I run to attach to the report at runtime

Dim crxDatabaseTable As CRAXDRT.DatabaseTable

Dim params As ADODB.Parameters
Dim param As ADODB.Parameter

' Create connection and command objects

' Open the data connection
Set cn = New ADODB.Connection

' Set connection properties and open
cn.ConnectionString = "Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;" & _
"Persist Security Info=False;" & _
"Initial Catalog=" & DB_CATALOGE & _
"Data Source=" & DATASOURCE

cn.CursorLocation = adUseClient
cn.Open

Set cmd = New ADODB.Command

' Set command properties
With cmd
Set .ActiveConnection = cn
.CommandText = "sp_RptData"
.CommandType = adCmdStoredProc
Set params = .Parameters
End With

' Refresh parameters from database
params.Refresh
' Specify input parameter values
params("@OWNERID&quot = 2

'' ' Execute the command
Set adors = cmd.Execute

'hardcoded for testing
Set m_Report = crxApplication.OpenReport("C:\Development\chronos\ESCRMS\FileListing.rpt", 1)


With m_Report
.Database.SetDataSource adors
.ReadRecords
.PaperSize = crPaperA4
.PaperOrientation = crPortrait
End With

crvFileList.ReportSource = m_Report
crvFileList.ViewReport

Any help would be appreciated as I becomng extremely frustrated.

Bob