Pulling @variables from a SQL query/sproc into vb/vba vairable

[johnpurdie]

Been struggling with this for a few hours now.

I have a SQL query which gives three vairables (however I can get it to push these vairables out in a 'Select @StatusOn, @StatusOff, @StatusTest' at the end. (this is stored as strSQL)

My code is as follows then I will go through what I have tried

*******************************************************
Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim prm As New ADODB.Parameter

With cnn
.Open "DRIVER={SQL Server};SERVER=MYSERVER;DATABASE=MYDB;"
End With

With cmd
.ActiveConnection = cnn
.CommandTimeout = 0
.CommandType = adCmdText
.CommandText = strSQL
End With



cmd.Execute
Set prm = cmd.CreateParameter("StatusOn", adDate, adParamOutput, , **@SatausOn**)

cmd.Parameters.Append prm

Set prm = cmd.CreateParameter("StatusOff", adDate, adParamOutput, , **@StatusOff**)

cmd.Parameters.Append prm

Set prm = cmd.CreateParameter("StatusTest", adDate, adParamOutput, , **@StatusTest**)
cmd.Parameters.Append prm
Set cnn = Nothing

*******************************************************

I have pushed the SQL and got it to dump it out then ran it manually and the SQL is bringing back values

I have tested the the parameters are being created and appended to the cmd howver I cant get the prm to be filled with the output value of the query.
I dont know what to put in **@StatusOn**
I could get the query to return a single record at a time, however this sits within a loop and dont want to hit the DB 3 times per loop

The query can either put out records as variables or in a single row with 3 fields.

I am at a loss with this, I know it has to be possible

Any help would be fantastic

[gibra]

Why you call cmd.Execute before to pass parameters?

You should be call next CreateParameter. Like:

Set prm = cmd.CreateParameter("StatusOn", adDate, adParamOutput, , **@SatausOn**)
and so on...

then

cmd.Execute

Also, you must call
cnn.Close
before to call
Set cnn = Nothing

HTH

[johnpurdie]

I have tried that, however it still wont populate, i need to assign the parameter with a value from the query (where **@StatusOn** is) I have even put Now() to pass the a date through and this works fine

[gibra]

You code is wrong:

Set prm = cmd.CreateParameter("StatusOn", adDate, adParamOutput, , **@SatausOn**)


This is correct:

Set prm = cmd.CreateParameter("@StatusOn", adDate, adParamOutput, , SatausOn)

Where SatausOn (or StatusOn?) is the value you pass to parameter.

HTH

[johnpurdie]

The param code is fine as have past non query vlaues into them and managed to pull a vlaue out of the object
Have had some more time to look at this today.

It seems there is issue with my SQL (eventhough it works fine when I place it into Query Analyser. the rst is just not getting populated hence why methods such as rst.fields(0).value through an 'does not exist' error

To create the strSQL I am concatinationg allot so there is allot of strSQL = strSQL & "more sql"

The string is large enough to take the entire code as got it to dump it into a cell on the spreadsheet and then pasted and copied it in to query analyser and works fine.

So have broken it down and even added got it to make a temp table on the SQL server insert values, select the values and then drop table (resource heavy I know but good to test) I am doing these under different commands each at a time as follws

With cmd
.ActiveConnection = cnn
.CommandTimeout = 0
.CommandType = adCmdText
.CommandText = strSETUP
.Execute
End With

With cmd
.ActiveConnection = cnn
.CommandTimeout = 0
.CommandType = adCmdText
.CommandText = strINSERT
End With

With rst
.Open strSELECT, cnn, adOpenStatic
End With

With cmd
.ActiveConnection = cnn
.CommandTimeout = 0
.CommandType = adCmdText
.CommandText = strDROP
End With

Everything else tests out fine strSETUP is a create table string, strINSERT is the insert string and so on

the issue seems to be with running the bulk of the load which is in strINSERT as follows

strINSERT = strINSERT & "Declare @ProviderID as int Declare @StartState as varchar(15) Declare @StartStateDate as datetime "
strINSERT = strINSERT & "Declare @StartDate as datetime Declare @StatusStartDate as datetime Declare @StatusEndDate as datetime "
strINSERT = strINSERT & "Declare @StatusLength as datetime Declare @StatusOFF as datetime Declare @StatusOn as datetime "
strINSERT = strINSERT & "Declare @StatusTest as datetime Declare @Status as varchar(15) Declare @Count as int "
strINSERT = strINSERT & " Set @Providerid = " & intProviderID & " Set @StartState = (select top 1 originalstatus from tbl_apfadminlog where datestamp >=GetDate()-31 and ProviderID = @Providerid order by datestamp asc)"
strINSERT = strINSERT & " Set @StartStateDate = (select top 1 datestamp from tbl_apfadminlog where datestamp >=GetDate()-31 and ProviderID = @Providerid order by datestamp asc)"
strINSERT = strINSERT & " Set @StartDate = GetDate()-31 Set @StartStateDate = (select top 1 datestamp from tbl_apfadminlog where datestamp >=GetDate()-31 and ProviderID = @Providerid order by datestamp asc )"
strINSERT = strINSERT & " Set @Count = (select count(datestamp) from tbl_apfadminlog where DateStamp > @StartDate and ProviderID = @Providerid) Set @StatusOff = 0"
strINSERT = strINSERT & " Set @StatusOn = 0 Set @StatusTest = 0 Set @StatusLength = @StartStateDate - @StartDate"
strINSERT = strINSERT & " IF @StartState = 'On' Set @StatusOn = @StatusOn + @StatusLength Else IF @StartState = 'Off' Set @StatusOff = @StatusOff + @StatusLength Else"
strINSERT = strINSERT & " IF @StartState = 'On Test Partner' Set @StatusTest = @StatusTest + @StatusLength Else IF @StartState = 'ISPREVIEW' Set @StatusTest = @StatusTest + @StatusLength"
strINSERT = strINSERT & " While @Count > 0 Begin Set @Count = (select count(datestamp) from tbl_apfadminlog where DateStamp > @StartDate and ProviderID = @Providerid)"
strINSERT = strINSERT & " Set @Status = (Select top 1 UpdatedStatus from tbl_apfadminlog where DateStamp > @StartDate  and ProviderID = @Providerid)"
strINSERT = strINSERT & " Set @StatusStartDate = (Select top 1 datestamp from tbl_apfadminlog where DateStamp > @StartDate  and ProviderID = @Providerid)"
strINSERT = strINSERT & " Set @StartDate = (Select top 1 datestamp from tbl_apfadminlog where DateStamp > @StartDate  and ProviderID = @Providerid)"
strINSERT = strINSERT & " Set @StatusEndDate = (Select top 1 datestamp from tbl_apfadminlog where DateStamp > @StartDate  and ProviderID = @Providerid)"
strINSERT = strINSERT & " IF @StatusEndDate is NULL Set @StatusEndDate = GetDate() Set @StatusLength = @StatusEndDate-@StatusStartDate"
strINSERT = strINSERT & " IF @Status = 'On' Set @StatusOn = @StatusOn + @StatusLength Else IF @Status = 'Off' Set @StatusOff = @StatusOff + @StatusLength Else"
strINSERT = strINSERT & " IF @Status = 'On Test Partner' Set @StatusTest = @StatusTest + @StatusLength Else IF @Status = 'ISPREVIEW' Set @StatusTest = @StatusTest + @StatusLength"
strINSERT = strINSERT & " End INSERT INTO #Status Select @StatusOn,@StatusOff,@StatusTest "

This is the same as the original code I started with however it didn't originaly have the insert as there wasn't a table (obviously)

for easier reading thats

Declare @ProviderID as int 
Declare @StartState as varchar(15) 
Declare @StartStateDate as datetime 
Declare @StartDate as datetime 
Declare @StatusStartDate as datetime 
Declare @StatusEndDate as datetime 
Declare @StatusLength as datetime 
Declare @StatusOFF as datetime 
Declare @StatusOn as datetime 
Declare @StatusTest as datetime 
Declare @Status as varchar(15) 
Declare @Count as int  Set @Providerid = 11 
Set @StartState = (select top 1 originalstatus from tbl_apfadminlog where datestamp >=GetDate()-31 and ProviderID = @Providerid order by datestamp asc) 
Set @StartStateDate = (select top 1 datestamp from tbl_apfadminlog where datestamp >=GetDate()-31 and ProviderID = @Providerid order by datestamp asc) 
Set @StartDate = GetDate()-31 
Set @StartStateDate = (select top 1 datestamp from tbl_apfadminlog where datestamp >=GetDate()-31 and ProviderID = @Providerid order by datestamp asc ) 
Set @Count = (select count(datestamp) from tbl_apfadminlog where DateStamp > @StartDate and ProviderID = @Providerid) Set @StatusOff = 0 
Set @StatusOn = 0 Set @StatusTest = 0 
Set @StatusLength = @StartStateDate - @StartDate IF @StartState = 'On' Set @StatusOn = @StatusOn + @StatusLength Else IF @StartState = 'Off' 
Set @StatusOff = @StatusOff + @StatusLength Else IF @StartState = 'On Test Partner' 
Set @StatusTest = @StatusTest + @StatusLength Else IF @StartState = 'ISPREVIEW' 
Set @StatusTest = @StatusTest + @StatusLength While @Count > 0 
Begin 
Set @Count = (select count(datestamp) from tbl_apfadminlog where DateStamp > @StartDate and ProviderID = @Providerid) 
Set @Status = (Select top 1 UpdatedStatus from tbl_apfadminlog where DateStamp > @StartDate  and ProviderID = @Providerid) 
Set @StatusStartDate = (Select top 1 datestamp from tbl_apfadminlog where DateStamp > @StartDate  and ProviderID = @Providerid) 
Set @StartDate = (Select top 1 datestamp from tbl_apfadminlog where DateStamp > @StartDate  and ProviderID = @Providerid) 
Set @StatusEndDate = (Select top 1 datestamp from tbl_apfadminlog where DateStamp > @StartDate  and ProviderID = @Providerid) IF @StatusEndDate is NULL 
Set @StatusEndDate = GetDate() 
Set @StatusLength = @StatusEndDate-@StatusStartDate IF @Status = 'On' Set @StatusOn = @StatusOn + @StatusLength Else IF @Status = 'Off' 
Set @StatusOff = @StatusOff + @StatusLength Else IF @Status = 'On Test Partner' Set @StatusTest = @StatusTest + @StatusLength Else IF @Status = 'ISPREVIEW' Set @StatusTest = @StatusTest + @StatusLength End 
Select @StatusOn,@StatusOff,@StatusTest

The issue seems to be I just cant get this to transact using the .execute method. It just skips right over it even when I am not expecting any result from it. Unfortunatly I dont have the option of creating a sproc with this in a just calling that.

Is there a constraint on how big that command is, it doesn't lag when running it through Query analyser. (shouldnt be a problem)

Can I not execute this query through VBA (surely not)

This feels like a brick wall

Many Thanks in Advance


Getting closer to what the problem is I think