Running SQL Server job from Access

I rewrote several Access programs to run as a single SQL stored procedure on
SQL Server. A job created in DTS executes the SQL stored procedure. This
job is executed from a batch file as listed below:
.....
Dim RetVal
Dim strPathFile
strPathFile = "C:\BTExe.cmd"
RetVal = Shell(strPathFile,1)
......
As soon as the code is submitted it returns the return value. Is there any
way to have the job operate as if it was being run within Access ? For
example, display the hour glass until the job completes. Is there a
recommended procedure to run jobs from Access on SQL server, which prevents
the user from using the system until the job completes ?"rmcompute" <rmcompute@.discussions.microsoft.com> wrote in message
news:143795DE-B03D-418E-99D3-A946461A3016@.microsoft.com...
>I rewrote several Access programs to run as a single SQL stored procedure
>on
> SQL Server. A job created in DTS executes the SQL stored procedure. This
> job is executed from a batch file as listed below:
> .....
> Dim RetVal
> Dim strPathFile
> strPathFile = "C:\BTExe.cmd"
> RetVal = Shell(strPathFile,1)
> ......
> As soon as the code is submitted it returns the return value. Is there
> any
> way to have the job operate as if it was being run within Access ? For
> example, display the hour glass until the job completes. Is there a
> recommended procedure to run jobs from Access on SQL server, which
> prevents
> the user from using the system until the job completes ?
>
Why don't you just run the stored procedure directly from Access. You can
do this with a pass-through query or in code.
David|||Can you give an example of the code and would the procedure run the same wa
y
a procedure runs in Access in that the user cannot use the system until the
procedure is complete ?
"David Browne" wrote:

> "rmcompute" <rmcompute@.discussions.microsoft.com> wrote in message
> news:143795DE-B03D-418E-99D3-A946461A3016@.microsoft.com...
> Why don't you just run the stored procedure directly from Access. You ca
n
> do this with a pass-through query or in code.
> David
>|||Hi rmcompute
Here is a real live example. Access will stop when it hits cmd.execute and
not continue until the stored Procedure executes.
Sub DoSomething
dim cmd as ADODB.Command
dim conn as ADODB.Connection
Set cmd = New ADODB.Command
'Assuming I want to connection to the SQLAlerts database and my sa
password is "sapassword".
strconnect = GetConnectionString(strSQLServerName, "SQLAlerts",
"sa", "sapassword", False)
set conn = New ADODB.Connection
conn.ConnectionTimeout = 15
conn.Open strconnect
Set cmd.ActiveConnection = conn
cmd.CommandText = " record_sql_server_database_backup_histor
y" '
My Stored Procedure Name
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter("@.sql_server_database_id",
adInteger, adParamInput, , lngSqlServerDatabaseID)
cmd.Parameters.Append cmd.CreateParameter("@.run_date", adDBDate,
adParamInput, , Now)
cmd.Parameters.Append cmd.CreateParameter("@.last_full_backup",
adDBDate, adParamInput, , dtLastFullBackup)
cmd.Parameters.Append
cmd.CreateParameter("@.last_incremental_backup", adDBDate, adParamInput, ,
dtLastIncrementalBackup)
cmd.Parameters.Append
cmd.CreateParameter("@.last_transactionlog_backup", adDBDate, adParamInput, ,
dtLastTransactionLogBackup)
cmd.Execute
END SUB
Function GetConnectionString(strSQLServerName As String, strDatabaseName As
String, strUsername As String, strPassword As String, blnNTAuthentication As
Boolean)
If blnNTAuthentication Then
GetConnectionString = "Provider=SQLOLEDB.1;Integrated
Security=SSPI;Persist Security Info=False;Initial Catalog=" &
strDatabaseName & ";Data Source=" & strSQLServerName
Else
Dim strPassword 'As String
GetConnectionString = "Provider=SQLOLEDB.1;Password=" &
strPassword & ";Persist Security Info=True;User ID=" & strUsername &
";Initial Catalog=" & strDatabaseName & ";Data Source=" & strSQLServerName
End If
End Function
-Dick Christoph
"rmcompute" <rmcompute@.discussions.microsoft.com> wrote in message
news:06EB646F-DD8F-40C6-AEA2-B612E6D7A3AB@.microsoft.com...
> Can you give an example of the code and would the procedure run the same
> way
> a procedure runs in Access in that the user cannot use the system until
> the
> procedure is complete ?
>
> "David Browne" wrote:
>

Running SQL JOB for SQL Express >>>>>

I need to run a SQL script as a job for SQL Express. How can I do this ? Are there any programs that will allow me to do this ?

The solution for running schedule tasks in SQL Express is to use a Task in the Windows Task Scheduler to call SQLCmd and run the script you want to run. You can set schedules, etc. using Windows Tasks.

SQL Express does not include the SQL Agent, which is the tool higher level Editions of SQL use to schedule jobs.

Regards,

Mike Wachal
SQL Express team

-
Please mark your thread as Answered when you get your solution.

|||

Hi Mike,

Thanks for the information. I will try that. I also did a search and there is a program created by a company called Vale Software. They have a program named Express Agent that looks like it may work too. Thanks !!!

|||

I do not understand why Microsoft went through the trouble of removing the Sql Agent scheduler from SQL 2005 Express. This is wrong for any product path/roadmap. There are many small applications, both in-house IT as well as distributed, that depend on the reliable SQL Server Agent Job scheduler.

I stopped using the Windows Task Scheduler years ago because you cannot count on it. It is not reliable. While it has improved since the NT4 and early Win2000 days, it is only marginally better due to a UI instead of the AT command line.

We need the service, SQLSERVERAGENT, to be included with SQL 2005 Express in order to migrate applications to 2005.

If you have a system that is configured for Client - Server - Notebook/offline and you utilize SQL2005 on the servers, you need SQL2005 Express on the Client and notebooks for replication. For the same reasons we distribute MSDE now, we would distribute SQL2005 Express in the future for the Notebook/offline engine. Out installations, updates, and backups, depend on the server and notebook being the same, (i.e. the same service names, the same scheduler, etc.). If the notbooks cannot have Express with the SQLSERVERAGENT service, then that complicates everything and increases the cost to everyone involved.

|||

Yes, I definitely agree. I'm working on a product that uses a database, and that uses a free database for small business customers. We used to rely on MSDE. Now, we may rely on other non-MS databases, knowing that OLE DB allows us to access any database we need.

I don't think this is such a good commercial decision from MS.

Moreover, you can find in SQL Express all tools (stored procedures, tables) in msdb for creating and setting up jobs. The only thing you can't do regarding jobs is...starting them!!! This either does not give a good image of MS...

Running SQL JOB for SQL Express >>>>>

I need to run a SQL script as a job for SQL Express. How can I do this ? Are there any programs that will allow me to do this ?

The solution for running schedule tasks in SQL Express is to use a Task in the Windows Task Scheduler to call SQLCmd and run the script you want to run. You can set schedules, etc. using Windows Tasks.

SQL Express does not include the SQL Agent, which is the tool higher level Editions of SQL use to schedule jobs.

Regards,

Mike Wachal
SQL Express team

-
Please mark your thread as Answered when you get your solution.

|||

Hi Mike,

Thanks for the information. I will try that. I also did a search and there is a program created by a company called Vale Software. They have a program named Express Agent that looks like it may work too. Thanks !!!

|||

I do not understand why Microsoft went through the trouble of removing the Sql Agent scheduler from SQL 2005 Express. This is wrong for any product path/roadmap. There are many small applications, both in-house IT as well as distributed, that depend on the reliable SQL Server Agent Job scheduler.

I stopped using the Windows Task Scheduler years ago because you cannot count on it. It is not reliable. While it has improved since the NT4 and early Win2000 days, it is only marginally better due to a UI instead of the AT command line.

We need the service, SQLSERVERAGENT, to be included with SQL 2005 Express in order to migrate applications to 2005.

If you have a system that is configured for Client - Server - Notebook/offline and you utilize SQL2005 on the servers, you need SQL2005 Express on the Client and notebooks for replication. For the same reasons we distribute MSDE now, we would distribute SQL2005 Express in the future for the Notebook/offline engine. Out installations, updates, and backups, depend on the server and notebook being the same, (i.e. the same service names, the same scheduler, etc.). If the notbooks cannot have Express with the SQLSERVERAGENT service, then that complicates everything and increases the cost to everyone involved.

|||

Yes, I definitely agree. I'm working on a product that uses a database, and that uses a free database for small business customers. We used to rely on MSDE. Now, we may rely on other non-MS databases, knowing that OLE DB allows us to access any database we need.

I don't think this is such a good commercial decision from MS.

Moreover, you can find in SQL Express all tools (stored procedures, tables) in msdb for creating and setting up jobs. The only thing you can't do regarding jobs is...starting them!!! This either does not give a good image of MS...

Running SQL JOB for SQL Express >>>>>

I need to run a SQL script as a job for SQL Express. How can I do this ? Are there any programs that will allow me to do this ?

The solution for running schedule tasks in SQL Express is to use a Task in the Windows Task Scheduler to call SQLCmd and run the script you want to run. You can set schedules, etc. using Windows Tasks.

SQL Express does not include the SQL Agent, which is the tool higher level Editions of SQL use to schedule jobs.

Regards,

Mike Wachal
SQL Express team

-
Please mark your thread as Answered when you get your solution.

|||

Hi Mike,

Thanks for the information. I will try that. I also did a search and there is a program created by a company called Vale Software. They have a program named Express Agent that looks like it may work too. Thanks !!!

|||

I do not understand why Microsoft went through the trouble of removing the Sql Agent scheduler from SQL 2005 Express. This is wrong for any product path/roadmap. There are many small applications, both in-house IT as well as distributed, that depend on the reliable SQL Server Agent Job scheduler.

I stopped using the Windows Task Scheduler years ago because you cannot count on it. It is not reliable. While it has improved since the NT4 and early Win2000 days, it is only marginally better due to a UI instead of the AT command line.

We need the service, SQLSERVERAGENT, to be included with SQL 2005 Express in order to migrate applications to 2005.

If you have a system that is configured for Client - Server - Notebook/offline and you utilize SQL2005 on the servers, you need SQL2005 Express on the Client and notebooks for replication. For the same reasons we distribute MSDE now, we would distribute SQL2005 Express in the future for the Notebook/offline engine. Out installations, updates, and backups, depend on the server and notebook being the same, (i.e. the same service names, the same scheduler, etc.). If the notbooks cannot have Express with the SQLSERVERAGENT service, then that complicates everything and increases the cost to everyone involved.

|||

Yes, I definitely agree. I'm working on a product that uses a database, and that uses a free database for small business customers. We used to rely on MSDE. Now, we may rely on other non-MS databases, knowing that OLE DB allows us to access any database we need.

I don't think this is such a good commercial decision from MS.

Moreover, you can find in SQL Express all tools (stored procedures, tables) in msdb for creating and setting up jobs. The only thing you can't do regarding jobs is...starting them!!! This either does not give a good image of MS...