Cannot display/return SQL Query Output from a Dispatch Variable

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/e...s_task_4gkl.asp .
I have tried to retrieve the results using the example in
http://msdn.microsoft.com/library/d...y/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.examnotes.net ***Hi
You don't seem to have:
http://msdn.microsoft.com/library/d...>
ask_4gkl.asp
listed.
John
"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/e...s_task_4gkl.asp .
> I have tried to retrieve the results using the example in
> http://msdn.microsoft.com/library/d...y/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.examnotes.net ***|||
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.examnotes.net ***|||
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.examnotes.net ***|||Hi
It was the wrong link, should have sync'd
http://msdn.microsoft.com/library/d...asp?frame=true
I have just followed
http://msdn.microsoft.com/library/d...>
ask_4gkl.asp
and the above and there have been no problems.
John
"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.examnotes.net ***