MS Access to SQL Server Pass through Query

[powersurg]

HEllo all I have a stored procedure in SQL Server I need to execute from Access. Here is the SQL inside the Access Pass Through Query.

Declare @EndDt datetime
Select @EndDt = GetDate()
EXEC fusion_loss_billing 500, -1, '01/01/1997', @EndDt, 'All'

When I execute this Pass Through query I get an error telling me that no records were returned.

I execute the same code in ISQL_w and I get "(1 row(s) affected)"
And then the data from the Stored Procedure.

I would venture to say that Access is not displaying anything after the Select @EndDt = GetDate() part.
The "No Records Returned" error is because the Select statement does not in fact return anything.

I originally had this code

EXEC fusion_loss_billing 500, -1, '01/01/1997', GetDate(), 'All'

But it wouldn't compile. It didn't know what to do with GetDate().

That is why I declared the Datetime variable and am setting it to GetDate(). It compiles this way but I am not getting and records returned.

Records Returned Property is set to YES also. Please help. This is becoming a tremendous waste of time!!

[powersurg]

Never Mind. Here is how I solved it. I went and created a module and did it through VB instead.

Function Poptables()
Dim dbs As Database
Dim qdfPassThrough As QueryDef
Dim qdfApp As QueryDef
Dim qdfFormat As QueryDef
Dim SDate, strDate As String
On Error GoTo Poptables_Err

SDate = Year(Date) & Month(Date) & Day(Date)
strDate = Date
Set dbs = CurrentDb
Set qdfPassThrough = dbs.CreateQueryDef("qry_fusion_loss_billing")

With DoCmd
.Hourglass True
With dbs
'.Execute "Delete * From tblMetrics_tmp"
With qdfPassThrough
.Connect = "ODBC;DSN=DMSBKP32;Description=DMS Data Warehouse;SERVER=ilivdwhs1; " _
& "UID=dmsdwhsp_dbo;PWD=dmsdwhsp_dbo;Network=DBMSSOC N;Address=xxx.xxx.xxx.xx"
.ODBCTimeout = 10000
.SQL = "EXEC fusion_loss_billing 500, -1, '01/01/1997', '" & strDate & "', 'ALL'"

End With
........


I think that it is almost impossible to pass a function through to SQL Server from Access. Doing it through code never fails

[PinkPanther2003]

I know you've solved your problem but just as a matter of interest.....

SELECT GETDATE()
works a treat on my system.

DECLARE @dt as DATETIME
SET @dt = GETDATE()
SELECT @dt

works a treat too.

DECLARE @dt as DATETIME
SELECT @dt = GETDATE()
SELECT @dt

fails.

I would suggest using

SET @EndDt = GetDate()

so you are not confusing the server as it is expecting to return something when it sees 'SELECT' but understands you are setting a variable with 'SET'

HTH,

Peter

[powersurg]

Peter,

You could use @EndDt as a variable being passed in the stored procedure and it would still work?

I never even thought to use SET. Oh well I have the VB so I might as well stick with it.

Thanks A lot. I just learned something new.

[KnooKie]

Just make sure you select SQL Specific from the query drop-down menu and then type in. Obviously you want to pass in the params from code or whatever but initially you must hard code as it were any values that fit them.........


EXECUTE <spname> <param1>, <param2>, etc


EXECUTE usp_IndustrySectorAnalysts_sel TST1999, 4

here i have an SP called usp_IndustrySectorAnalysts_sel
it excepts 2 params which i have hard coded TST1999 and 4

this does not matter as i pass the param values in via code so they will change anyway.

You can save this pass through query in Access as eg. qry_MySQLPass

soooo from code you would pass in the necessary params like..........

This will populate a combo based on the values you pass to your pass through query.....................

'Populate the drop-down Fmg list combo on the details form
Dim qdfList As QueryDef
Dim strSQL As String

Set db = CurrentDb

Set qdfList = db.QueryDefs("qry_MySQLPass") 'passthrough query
strSQL = "EXECUTE usp_IndustrySectorAnalysts_sel " & gsUID & gintANumber 'passing in 2 global variables
qdfList.sql = strSQL

Me.cbo_FMGList.RowSource = "qry_MySQLPass"
Set qdfList = Nothing

might need to change this line to..........

strSQL = "EXECUTE usp_IndustrySectorAnalysts_sel " & gsUID & ", " & gintANumber 'passing in 2 global variables


may help ?

[powersurg]

Thanks Man, The DB Server is down right now so there will be no queries of any kind occurring over here.!!

[powersurg]

Ok lets try this again.
When I run the following code in Access and ISQL/W....
DECLARE @EndDt Datetime
SET @EndDt = GetDate()
SELECT @EndDt

I get the following error.
Msg 170, Level 15, State 1
Line 2: Incorrect syntax near '@EndDt'.

What am I doing wrong!!! I feel like an idiot.

[PinkPanther2003]

You are using SQL Server 7 or 2000 aren't you cause the syntax works fine on my system.

You haven't fiddled with any of the default system settings?

Sorry can't help you with this one....

Peter

[powersurg]

Bingo!!!! No I am using SQL 6.5

Go figure!!!!

I went back to the VB code approach