Cannot display/return SQL Query Output from a Dispatch Variable in DTS
-
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/howtosql/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.
-
Take a look at this article.
http://www.sqldts.com/default.aspx?298
--------------------
Colt 45 - the original point and click interface
-
Thanks, this article looks promising.
-
I don't know what was causing all the problems getting the data from the dispatch recordset variable, but I reviewed that article, made some changes, and now everything is file. I also changed it to do the query in vbscript rather than as a DTS execute SQL task, but that shouldn't matter for getting the data out of the dispatch variable. Thanks for your suggestion.
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
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply