Cannot display/return SQL Query Output from a Dispatch Variable

8 Sep 2005 4:05 PM

SJM

I have been having a very difficult time trying to get the output of a
sql query that is in a DTS global variable to return, presently to a
messagebox.

I created an Execute SQL task with the query I wish to use. The query
has been tested in Query Analyzer and works fine and returns the results
I am looking for. I set a global variable to the result set. Basically,
I wish to display the results as a string or similar in a messagebox.

I have created the Execute SQL task as described in
http://msdn.microsoft.com/library/en-us/howtosql/ht_dts_task_4gkl.asp .

I have tried to retrieve the results using the example in
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosq
l/ht_dts_task_6llt.asp but I have been unable to do so.

I also tried the GetString method of the recordset without success.

I also tried the using the “Storing the resultset in a flat file”
example from http://dotnetbips.com/displayarticle.aspx?id=228 and I was
unable to write the recordset to a file.

What is the proper way to display a variable of type dispatch? I feel
like I am going about this the wrong way.

I welcome suggestions and comments on how to achieve the goal of
displaying the query results.

Thanks.

*** Sent via Developersdex http://www.developersdex.com ***

8 Sep 2005 8:16 PM

John Bell

Hi

You don't seem to have:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_dts_task_4gkl.asp

listed.

John

Show quote

"SJM" <nospam@devdex.com> wrote in message
news:u6hU08ItFHA.304@TK2MSFTNGP11.phx.gbl...
>
>
> I have been having a very difficult time trying to get the output of a
> sql query that is in a DTS global variable to return, presently to a
> messagebox.
>
> I created an Execute SQL task with the query I wish to use. The query
> has been tested in Query Analyzer and works fine and returns the results
> I am looking for. I set a global variable to the result set. Basically,
> I wish to display the results as a string or similar in a messagebox.
>
> I have created the Execute SQL task as described in
> http://msdn.microsoft.com/library/en-us/howtosql/ht_dts_task_4gkl.asp .
>
> I have tried to retrieve the results using the example in
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosq
> l/ht_dts_task_6llt.asp but I have been unable to do so.
>
> I also tried the GetString method of the recordset without success.
>
> I also tried the using the "Storing the resultset in a flat file"
> example from http://dotnetbips.com/displayarticle.aspx?id=228 and I was
> unable to write the recordset to a file.
>
> What is the proper way to display a variable of type dispatch? I feel
> like I am going about this the wrong way.
>
> I welcome suggestions and comments on how to achieve the goal of
> displaying the query results.
>
> Thanks.
>
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***

9 Sep 2005 1:07 PM

SJM

Indeed, that MSDN is what I followed to get the data into the variable,
getting it out is the problem.

The workaround I am using is to execute the SQL statements in the
VBscript and not use the execute SQL DTS task. For example:

Option Explicit

Function Main()

dim cnn
dim rs
dim intLoop

set cnn=createobject("ADODB.Connection")

cnn.Open "Provider=sqloledb;" & _
           "Data Source=SERVER;" & _
           "Initial Catalog=msdb;" & _
           "Integrated Security=SSPI"

set rs=cnn.execute("SQL STATEMENT HERE")

if rs.eof and rs.bof then

msgbox "Error: No records"

else

do until rs.EOF

For intLoop = 0 To rs.Fields.Count - 1
    msgbox "   " & rs.Fields(intLoop).Name
Next

set rs = rs.Next

loop

end if

Main = DTSTaskExecResult_Success

End Function

*** Sent via Developersdex http://www.developersdex.com ***

9 Sep 2005 1:45 PM

SJM

Everything is fine with the code listed below. I am going to use this
instead of the DTS execute sql task.

Option Explicit

Function Main()

dim cnn
dim rs
dim intLoop
dim strText

set cnn=createobject("ADODB.Connection")

cnn.Open "Provider=sqloledb;" & _
           "Data Source=SERVER;" & _
           "Initial Catalog=msdb;" & _
           "Integrated Security=SSPI"

set rs=cnn.execute("SQL QUERY")

If rs.BOF then

msgbox "No Records Found"

else

do until rs.EOF

For intLoop = 0 To rs.Fields.Count - 1

strText = strText & rs.fields(intLoop).value & " " & vbCrLf

Next

rs.MoveNext

loop

msgbox strText

end if

set rs = nothing

Main = DTSTaskExecResult_Success

End Function

*** Sent via Developersdex http://www.developersdex.com ***

9 Sep 2005 4:49 PM

John Bell

Hi

It was the wrong link, should have sync'd
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_dts_task_489x.asp?frame=true

I have just followed
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_dts_task_4gkl.asp
and the above and there have been no problems.

John

Show quote

"SJM" <nospam@devdex.com> wrote in message
news:euD9JTUtFHA.2624@TK2MSFTNGP12.phx.gbl...
>
>
> Everything is fine with the code listed below. I am going to use this
> instead of the DTS execute sql task.
>
> Option Explicit
>
> Function Main()
>
> dim cnn
> dim rs
> dim intLoop
> dim strText
>
> set cnn=createobject("ADODB.Connection")
>
> cnn.Open "Provider=sqloledb;" & _
>           "Data Source=SERVER;" & _
>           "Initial Catalog=msdb;" & _
>           "Integrated Security=SSPI"
>
> set rs=cnn.execute("SQL QUERY")
>
> If rs.BOF then
>
> msgbox "No Records Found"
>
> else
>
> do until rs.EOF
>
> For intLoop = 0 To rs.Fields.Count - 1
>
> strText = strText & rs.fields(intLoop).value & " " & vbCrLf
>
> Next
>
> rs.MoveNext
>
> loop
>
> msgbox strText
>
> end if
>
> set rs = nothing
>
> Main = DTSTaskExecResult_Success
>
> End Function
>
>
> *** Sent via Developersdex http://www.developersdex.com ***