I swear I saw, but exists no more

I have all the installed Control Flow Tasks. At one time I did create a Execute T-SQL Statement task, but after a couple of days when I revisited the project it is still there on my design area, but has disappeared from the Control Flow Tasks group in the toolbox. I did a check of all items. It has disappeared. Any clue(s)? I have posted a screen shot in my blog.

Jay

http://hodentek.blogspot.com/2007/08/strange-behaviour-visual-studio-2005.html

That's because the T-SQL task is under the Maintenance Plan Tasks, not Control Flow Items.

The Execute SQL Task under the Control Flow items, though, is a much better fit long term and will do the same thing as the Execute T-SQL Task.|||

Right you are. Need changing my glasses.

Jay

I need to execute TWO SQL statements...how?

I basically have this:

<%
Response.Expires = -1000

Dim oConn
Dim oRS
Dim sSQL

Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("\isclassof2003\db\ForSale.mdb"))

sSQL = "SELECT NetID FROM Student"
Set oRS = oConn.Execute(sSQL)

oConn.Close
Set oRS = Nothing
Set oConn = Nothing
%>

But right after I execute the first SQL statement, I wanna run this too:

sSQL = "INSERT INTO Student (NetID,Password) VALUES ('" & netid & "','" & pass1 & "')"

Do i need to close the first connecttion and open a new one? if so, do i need to assign new variables to it or just re-use the ones i have now?

Or can I just insert the second SQL statement straight in wherever i need it?

ThanksHi Delphi,

You should be able to execute your INSERT statement after opening the recordset, using the same data connection.

You might need to open the recordset into a dedicated object, to separate the connection object and the recordset.

Try this:

<%
Response.Expires = -1000

Dim oConn
Dim oRS
Dim sSQL

Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("\isclassof2003\db\ForSale.mdb"))

Set oRS = Server.CreateObject("ADODB.Recordset")
oRS.ActiveConnection = oConn
oRS.Source = "SELECT NetID FROM Student"
oRS.Open()

oConn.Execute "INSERT INTO Student (NetID,Password) VALUES ('" & netid & "','" & pass1 & "')"

oConn.Close
Set oRS = Nothing
Set oConn = Nothing
%>|||thanks!|||You do not need to close the connection if you execute the queries in the same database. You just close the record set and re-open it for another query execution. In your case, you do not need a record set for your second query.

<%
Response.Expires = -1000

Dim oConn
Dim oRS
Dim sSQL

Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("\isclassof2003\db\ForSale.mdb"))

sSQL = "SELECT NetID FROM Student"
Set oRS = oConn.Execute(sSQL)
.....
oRS.Close

sSQL = "INSERT INTO Student (NetID,Password) VALUES ('" & netid & "','" & pass1 & "')"

oConn.Execute(sSQL)

oConn.Close
Set oRS = Nothing
Set oConn = Nothing
%>|||Originally posted by gyuan
You do not need to close the connection if you execute the queries in the same database. You just close the record set and re-open it for another query execution. In your case, you do not need a record set for your second query.

<%
Response.Expires = -1000

Dim oConn
Dim oRS
Dim sSQL

Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("\isclassof2003\db\ForSale.mdb"))

sSQL = "SELECT NetID FROM Student"
Set oRS = oConn.Execute(sSQL)
.....
oRS.Close

sSQL = "INSERT INTO Student (NetID,Password) VALUES ('" & netid & "','" & pass1 & "')"

oConn.Execute(sSQL)

oConn.Close
Set oRS = Nothing
Set oConn = Nothing
%>

Should the second "oConn.Execute(sSQL)" be "Set oRS = oConn.Execute(sSQL)"?|||Delphi,

Only queries which return a set of records need the 'Set oRS = ' statement. (Generally SELECT queries).

'Action' commands, such as INSERT, DELETE, UPDATE don't return a recordset, so they can just be executed like so:

oConn.Execute(sSQL)

Hope this helps!

I need to execute a procedure at a spesific hour

Hello, i need some help on this...

I need to make a job that execute a procedure at 7 am , and other at 8 pm, can any one give a hand with this?

Regards

What version of SQL Server? If you are using a sku with agent, it does a great job and is pretty easy to set up.

If not, you might just consider using sqlcmd or isql and using the AT facility built into Windows. It is easy enough to do once you get the command you want to run.

|||Im working with SQL 2005|||Set up a SQL Agent Job to execute a Transact-SQL command to execute the stored procedure, and schedule it for when you want it to run.|||

open sql server management studio 2005

connect to your database server.

in the object explorer. click sql server agent.

right click jobs. then new jobs.

place your codes

|||Which version? Unless it is express, then Agent will be quite easy to set up (as long as whoever set up the server installed/enabled it.) Books online covers this pretty well, or some of the other posts outline the basics.