Cannot display/return SQL Query Output from a 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 either into a
msgbox or for populating a portion of the body of a mail task.
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. Four rows are returned. Basically, I wish to put these
results in the body of a mail task or right now I would settle for a
msgbox just to see it work.
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 dotnetbips.com/displayarticle.aspx?id=228 and I was unable
to write the recordset to a file.
I feel like I am going about this all wrong and the answer is just
staring me in the face and I dont get it.
I welcome suggestions and comments on how to achieve the goal of putting
the query results into the email body.
Thanks.
*** Sent via Developersdex http://www.examnotes.net ***Hi
You may want to use call xp_sendmail directly (see Books online) rather than
the DTS Send Mail task. If not this may help:
http://www.sqldts.com/default.aspx?235
John
"SJM" <nospam@.devdex.com> wrote in message
news:O8fvDgusFHA.596@.TK2MSFTNGP12.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 either into a
> msgbox or for populating a portion of the body of a mail task.
> 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. Four rows are returned. Basically, I wish to put these
> results in the body of a mail task or right now I would settle for a
> msgbox just to see it work.
> 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 dotnetbips.com/displayarticle.aspx?id=228 and I was unable
> to write the recordset to a file.
> I feel like I am going about this all wrong and the answer is just
> staring me in the face and I don't get it.
> I welcome suggestions and comments on how to achieve the goal of putting
> the query results into the email body.
> Thanks.
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Indeed, I am considering sending the mail from within the script, but
right now I am concentrating on getting the data and other info into the
message body. Thanks for the suggestion.
*** Sent via Developersdex http://www.examnotes.net ***sql

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 ***

Cannot create subscription for a SQL Server 2005 Mobile database

I have been trying for almost 2 days now to create a subscription to a
SQL Server 2005 database but I haven't had any success. After reading
all of the documentation and installing everything, the problem seems
to have something to do with permissions. Here is what works:
* Created the a merge publication and checked to see that the
Replication Monitor shows that it works.
* Checked the directory where the snapshot files are located and they
exist.
* Can access the snapshot files from a browser, so I know security is
setup
* Tried setting the account for IIS 6.0 on the virtual directory to my
Windows account which has full admin permissions. Even tried
anonymous.
* Set the permission on all of the files and directories where the
snapshot files are stored.
* Added my Windows account to the PAL in the publication.
* Added my Windows account to the SQL Server login accounts
I then created an empty SQL Server 2005 mobile database locally on my
PC. Then I accessed it in SQL Server Management Studio.
Finally, I selected the New Subscription Wizard on the node of my
mobile database and tried using all kinds of login accounts.
Where it hangs is when I get to the last screen where it attempts to
create the subscription. After hanging several seconds on the part
that says "Synchronizing Data", it comes back with an error message
that either tells me that the SQL Server does not exist and/or the
user account does not exist. If I try screwing around with the various
types of login accounts, it can sometimes come back and say the IIS
account is not in SQL Server. There are a lot of accounts to setup
everywhere - publiccations, distributor, IIS, the database. Man, this
is getting to be one ugly headache. Can someone please give me some
solid steps on how to create a subscription.
Much appreciated,
Johann
I would first rule out publication properties. In SSMS create a local
sdf/mobile database and pull to it. If this works your publication is OK.
Then examine the IIS logs to see where it is getting stuck. Chances are it
is a permissions/authentication problem.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Polaris431" <polaris431@.gmail.com> wrote in message
news:1173019861.533730.317880@.n33g2000cwc.googlegr oups.com...
>I have been trying for almost 2 days now to create a subscription to a
> SQL Server 2005 database but I haven't had any success. After reading
> all of the documentation and installing everything, the problem seems
> to have something to do with permissions. Here is what works:
> * Created the a merge publication and checked to see that the
> Replication Monitor shows that it works.
> * Checked the directory where the snapshot files are located and they
> exist.
> * Can access the snapshot files from a browser, so I know security is
> setup
> * Tried setting the account for IIS 6.0 on the virtual directory to my
> Windows account which has full admin permissions. Even tried
> anonymous.
> * Set the permission on all of the files and directories where the
> snapshot files are stored.
> * Added my Windows account to the PAL in the publication.
> * Added my Windows account to the SQL Server login accounts
> I then created an empty SQL Server 2005 mobile database locally on my
> PC. Then I accessed it in SQL Server Management Studio.
> Finally, I selected the New Subscription Wizard on the node of my
> mobile database and tried using all kinds of login accounts.
> Where it hangs is when I get to the last screen where it attempts to
> create the subscription. After hanging several seconds on the part
> that says "Synchronizing Data", it comes back with an error message
> that either tells me that the SQL Server does not exist and/or the
> user account does not exist. If I try screwing around with the various
> types of login accounts, it can sometimes come back and say the IIS
> account is not in SQL Server. There are a lot of accounts to setup
> everywhere - publiccations, distributor, IIS, the database. Man, this
> is getting to be one ugly headache. Can someone please give me some
> solid steps on how to create a subscription.
> Much appreciated,
> Johann
>

cannot connect to SQL Server from VB.NET

I have an application written in VB.NET that connects directly to a
SQL Server 2000 database over the internet. It works fine on the
development machine (XP Pro), as well as two outside machines running
XP Pro. It does NOT work on two other outside machines, both running
Win2000. All 4 outside machines have the latest .NET framework
installed. I assume that XP has something by default that the 2000
machines do not, but I have no idea what that something could be that
would affect my software. I tried installing MDAC 2.8 as well as the
"sqlredist.exe" on both 2000 machines, but no dice. Is there anything
else I need to install, which XP just happens to already include? The
code doesn't appear to be the issue, since it works on the two XP
machines. And permissions and/or firewalls don't appear to be an
issue for the same reason. Here is the code in question, if that
helps:

Public conTemp As SqlClient.SqlConnection

conTemp = New SqlClient.SqlConnection("Data
Source=xxx.xxx.xxx.xxx\servername;Network Library=DBMSSOCN;Initial
Catalog=somename;User ID=username;Password=password;")

conTemp.Open() 'this is the line where the error occurs

The actual error is "Object reference not set to an instance of an
object", as though the second line above was absent (I get the same
error if I comment out the second line of code and run it on the
development machine). I thought that maybe certain SQL Server drivers
were needed in order for the SQLConnection to operate correctly, but I
assume the MDAC and "sqlredist.exe" would have taken care of that if
it was the problem. I'm out of ideas and appreciate any you guys
might have.

AndrewHi

I can't think of an obvious reason for this. The XP firewall could
have caused problems, but it seems to be the reverse of that,
therefore is there any third party application that is being run
instead on the 2000 machines or do these machines have something else
using the port you are using?

You don't say if the XP and 2000 machines are at the same location, on
the same network/segment?

Are the network configurations the same?

Can you talk to other SQL servers?

HTH

John

magicsoft714@.yahoo.com (Andrew) wrote in message news:<888f9cb6.0311261307.2c59bde7@.posting.google.com>...
> I have an application written in VB.NET that connects directly to a
> SQL Server 2000 database over the internet. It works fine on the
> development machine (XP Pro), as well as two outside machines running
> XP Pro. It does NOT work on two other outside machines, both running
> Win2000. All 4 outside machines have the latest .NET framework
> installed. I assume that XP has something by default that the 2000
> machines do not, but I have no idea what that something could be that
> would affect my software. I tried installing MDAC 2.8 as well as the
> "sqlredist.exe" on both 2000 machines, but no dice. Is there anything
> else I need to install, which XP just happens to already include? The
> code doesn't appear to be the issue, since it works on the two XP
> machines. And permissions and/or firewalls don't appear to be an
> issue for the same reason. Here is the code in question, if that
> helps:
> Public conTemp As SqlClient.SqlConnection
> conTemp = New SqlClient.SqlConnection("Data
> Source=xxx.xxx.xxx.xxx\servername;Network Library=DBMSSOCN;Initial
> Catalog=somename;User ID=username;Password=password;")
> conTemp.Open() 'this is the line where the error occurs
> The actual error is "Object reference not set to an instance of an
> object", as though the second line above was absent (I get the same
> error if I comment out the second line of code and run it on the
> development machine). I thought that maybe certain SQL Server drivers
> were needed in order for the SQLConnection to operate correctly, but I
> assume the MDAC and "sqlredist.exe" would have taken care of that if
> it was the problem. I'm out of ideas and appreciate any you guys
> might have.
> Andrew|||I don't have any other SQL Servers which I can test against, and
installing SQL Enterprise Manager on the 2000 machines to see if that
works and/or fixes the issue isn't really an option I'd like to take.
The XP and 2000 machines are all in the same house, on the same
network. There isn't anything installed on the 2000 machines which
would interfere with my app. As far as I can tell, everything is
equal, other than the OS.

Andrew

> Hi
> I can't think of an obvious reason for this. The XP firewall could
> have caused problems, but it seems to be the reverse of that,
> therefore is there any third party application that is being run
> instead on the 2000 machines or do these machines have something else
> using the port you are using?
> You don't say if the XP and 2000 machines are at the same location, on
> the same network/segment?
> Are the network configurations the same?
> Can you talk to other SQL servers?
> HTH
> John|||I don't have any other SQL Servers which I can test against, and
installing SQL Enterprise Manager on the 2000 machines to see if that
works and/or fixes the issue isn't really an option I'd like to take.
The XP and 2000 machines are all in the same house, on the same
network. There isn't anything installed on the 2000 machines which
would interfere with my app. As far as I can tell, everything is
equal, other than the OS.

Andrew

> Hi
> I can't think of an obvious reason for this. The XP firewall could
> have caused problems, but it seems to be the reverse of that,
> therefore is there any third party application that is being run
> instead on the 2000 machines or do these machines have something else
> using the port you are using?
> You don't say if the XP and 2000 machines are at the same location, on
> the same network/segment?
> Are the network configurations the same?
> Can you talk to other SQL servers?
> HTH
> John|||Hi

The easiest way to check the network would probably be to telnet into the
given port on the server. This will show that the routing is ok. If that
works it is probably something to do with the machine/software
configuration.

John

"Andrew" <magicsoft714@.yahoo.com> wrote in message
news:888f9cb6.0311271609.5a68472b@.posting.google.c om...
> I don't have any other SQL Servers which I can test against, and
> installing SQL Enterprise Manager on the 2000 machines to see if that
> works and/or fixes the issue isn't really an option I'd like to take.
> The XP and 2000 machines are all in the same house, on the same
> network. There isn't anything installed on the 2000 machines which
> would interfere with my app. As far as I can tell, everything is
> equal, other than the OS.
> Andrew
> > Hi
> > I can't think of an obvious reason for this. The XP firewall could
> > have caused problems, but it seems to be the reverse of that,
> > therefore is there any third party application that is being run
> > instead on the 2000 machines or do these machines have something else
> > using the port you are using?
> > You don't say if the XP and 2000 machines are at the same location, on
> > the same network/segment?
> > Are the network configurations the same?
> > Can you talk to other SQL servers?
> > HTH
> > John