Andrew
> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
> (The above unfortunately doesn't work).
No , you have to know how many "columns" stored procedures will be returned
insert into tablename exec sp
"Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
news:ueWGWKyAHHA.1012@.TK2MSFTNGP04.phx.gbl...
> Dear all,
> There is a stored procedure that returns a result set consisting of some
> 30
> columns of different types.
> How can I automatically (i.e. not by means of CREATE TABLE statement)
> create
> a table with a structure that correspons to the returned result set?
> I mean something like:
> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
> (The above unfortunately doesn't work).
> To be specific: the stored procedure in question is:
> sp_MSenum_replication_agents @.type = 4, @.exclude_anonymous = 1
> Any help would be greatly appreciated!
> Thank you in advance.
> Best regards,
> Andrew
>
Andrew
Well, in that case take a look into dynamic sql
"Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
news:u7PBTayAHHA.4328@.TK2MSFTNGP03.phx.gbl...
> Dear Uri,
> As I wrote, the idea is to create the table automatically, i.e. NOT to use
> the CREATE TABLE statement.
> Best regards,
> Andrew
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:esBsGPyAHHA.3536@.TK2MSFTNGP03.phx.gbl...
>
|||Andrew
--This procedure returns three or two columns from Orders Table depends on
parameter
create proc usp_test
@.orderid int =10248
as
if @.orderid=10248
select orderid,orderdate from orders where orderid=@.orderid
else
select orderid,orderdate,CustomerID from orders where orderid=@.orderid
--I use OPENROWSET command to run this SP and get the data out to temporary
table
select * into #t
from
openrowset('SQLOLEDB','SERVER=NT_SQL_4;DATABASE=No rthwind;UID=appdev;PWD=np,jho;',
'set fmtonly off; exec usp_test 10249') as p
select * from #t
drop table #t
"Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
news:OzpHB2yAHHA.1220@.TK2MSFTNGP04.phx.gbl...
> Dear Uri,
> Thank you for the hint. If I understand you correctly, you suggest that I
> should CREATE TABLE dynamically (EXEC ('CREATE TABLE...')).
> That's OK, but how can I get (programmatically) the list of "columns" that
> are returned by the stored procedure?
> My basic question is
> So, the situation is as follows: I have got a stored procedure and want to
> put its result set into a new table.
> Something like SELECT * INTO MyNewTable FROM ExistingTableOrView,
> but instead of ExistingTableOrView there is a stored procedure.
> Best regards,
> Andrew
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23N7N5hyAHHA.5068@.TK2MSFTNGP02.phx.gbl...
>
>
|||> P.S. Isn't it a bit strange that we have to refer to our own SQL server
> via
> 'openrowset'?
:-)
"Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
news:ezPsolzAHHA.3540@.TK2MSFTNGP03.phx.gbl...
> Dear Uri,
> Great! It works!
> Thank you very much for your help!
> Best regards,
> Andrew
> P.S. Isn't it a bit strange that we have to refer to our own SQL server
> via
> 'openrowset'?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OboMuXzAHHA.5060@.TK2MSFTNGP02.phx.gbl...
>
>
|||Tibor
I did not want to create a linked server that OPENQUERY required unlike
OPENROWSET when you can reference to the server without having a linked
server
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OfQkCm0AHHA.1224@.TK2MSFTNGP04.phx.gbl...
> OPENQUERY() is another option...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
> news:ezPsolzAHHA.3540@.TK2MSFTNGP03.phx.gbl...
>
Showing posts with label execute. Show all posts
Showing posts with label execute. Show all posts
Wednesday, March 21, 2012
How to create a storeprocedure
Hi,
I'm very very new in SQLServer.
I need to create a store procedure that execute a sql file located in a PATH
for example:
C:\Inetpub\Reserved\update.sql
HOw can I made this?
Thanks a lot!Hi
what version of sql server you using.
if you have sql server installed on your computer, then you can open .sql
file in query analyser or management studio to execute it
i still don't understand why you a need a SP to execute update.sql file
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Andrea" <noreply@.nospam.net> wrote in message
news:5Fkni.2101$BM.412@.tornado.fastwebnet.it...
> Hi,
> I'm very very new in SQLServer.
> I need to create a store procedure that execute a sql file located in a
> PATH
> for example:
>
> C:\Inetpub\Reserved\update.sql
>
>
> HOw can I made this?
>
> Thanks a lot!
>
>|||Andrea
Sorry, I type it from the memory, please test it
EXEC master..xp_cmdshell 'osql.exe -S SVR -U sa -P pswd -i
"c:\Inetpub\Reserved\update.sql" -o "C:\output.txt"'
"Andrea" <noreply@.nospam.net> wrote in message
news:5Fkni.2101$BM.412@.tornado.fastwebnet.it...
> Hi,
> I'm very very new in SQLServer.
> I need to create a store procedure that execute a sql file located in a
> PATH
> for example:
>
> C:\Inetpub\Reserved\update.sql
>
>
> HOw can I made this?
>
> Thanks a lot!
>
>sql
I'm very very new in SQLServer.
I need to create a store procedure that execute a sql file located in a PATH
for example:
C:\Inetpub\Reserved\update.sql
HOw can I made this?
Thanks a lot!Hi
what version of sql server you using.
if you have sql server installed on your computer, then you can open .sql
file in query analyser or management studio to execute it
i still don't understand why you a need a SP to execute update.sql file
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Andrea" <noreply@.nospam.net> wrote in message
news:5Fkni.2101$BM.412@.tornado.fastwebnet.it...
> Hi,
> I'm very very new in SQLServer.
> I need to create a store procedure that execute a sql file located in a
> PATH
> for example:
>
> C:\Inetpub\Reserved\update.sql
>
>
> HOw can I made this?
>
> Thanks a lot!
>
>|||Andrea
Sorry, I type it from the memory, please test it
EXEC master..xp_cmdshell 'osql.exe -S SVR -U sa -P pswd -i
"c:\Inetpub\Reserved\update.sql" -o "C:\output.txt"'
"Andrea" <noreply@.nospam.net> wrote in message
news:5Fkni.2101$BM.412@.tornado.fastwebnet.it...
> Hi,
> I'm very very new in SQLServer.
> I need to create a store procedure that execute a sql file located in a
> PATH
> for example:
>
> C:\Inetpub\Reserved\update.sql
>
>
> HOw can I made this?
>
> Thanks a lot!
>
>sql
How to create a storeprocedure
Hi,
I'm very very new in SQLServer.
I need to create a store procedure that execute a sql file located in a PATH
for example:
C:\Inetpub\Reserved\update.sql
HOw can I made this?
Thanks a lot!
Andrea
Sorry, I type it from the memory, please test it
EXEC master..xp_cmdshell 'osql.exe -S SVR -U sa -P pswd -i
"c:\Inetpub\Reserved\update.sql" -o "C:\output.txt"'
"Andrea" <noreply@.nospam.net> wrote in message
news:5Fkni.2101$BM.412@.tornado.fastwebnet.it...
> Hi,
> I'm very very new in SQLServer.
> I need to create a store procedure that execute a sql file located in a
> PATH
> for example:
>
> C:\Inetpub\Reserved\update.sql
>
>
> HOw can I made this?
>
> Thanks a lot!
>
>
I'm very very new in SQLServer.
I need to create a store procedure that execute a sql file located in a PATH
for example:
C:\Inetpub\Reserved\update.sql
HOw can I made this?
Thanks a lot!
Andrea
Sorry, I type it from the memory, please test it
EXEC master..xp_cmdshell 'osql.exe -S SVR -U sa -P pswd -i
"c:\Inetpub\Reserved\update.sql" -o "C:\output.txt"'
"Andrea" <noreply@.nospam.net> wrote in message
news:5Fkni.2101$BM.412@.tornado.fastwebnet.it...
> Hi,
> I'm very very new in SQLServer.
> I need to create a store procedure that execute a sql file located in a
> PATH
> for example:
>
> C:\Inetpub\Reserved\update.sql
>
>
> HOw can I made this?
>
> Thanks a lot!
>
>
How to create a storeprocedure
Hi,
I'm very very new in SQLServer.
I need to create a store procedure that execute a sql file located in a PATH
for example:
C:\Inetpub\Reserved\update.sql
HOw can I made this?
Thanks a lot!Hi
what version of sql server you using.
if you have sql server installed on your computer, then you can open .sql
file in query analyser or management studio to execute it
i still don't understand why you a need a SP to execute update.sql file
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Andrea" <noreply@.nospam.net> wrote in message
news:5Fkni.2101$BM.412@.tornado.fastwebnet.it...
> Hi,
> I'm very very new in SQLServer.
> I need to create a store procedure that execute a sql file located in a
> PATH
> for example:
>
> C:\Inetpub\Reserved\update.sql
>
>
> HOw can I made this?
>
> Thanks a lot!
>
>|||Andrea
Sorry, I type it from the memory, please test it
EXEC master..xp_cmdshell 'osql.exe -S SVR -U sa -P pswd -i
"c:\Inetpub\Reserved\update.sql" -o "C:\output.txt"'
"Andrea" <noreply@.nospam.net> wrote in message
news:5Fkni.2101$BM.412@.tornado.fastwebnet.it...
> Hi,
> I'm very very new in SQLServer.
> I need to create a store procedure that execute a sql file located in a
> PATH
> for example:
>
> C:\Inetpub\Reserved\update.sql
>
>
> HOw can I made this?
>
> Thanks a lot!
>
>
I'm very very new in SQLServer.
I need to create a store procedure that execute a sql file located in a PATH
for example:
C:\Inetpub\Reserved\update.sql
HOw can I made this?
Thanks a lot!Hi
what version of sql server you using.
if you have sql server installed on your computer, then you can open .sql
file in query analyser or management studio to execute it
i still don't understand why you a need a SP to execute update.sql file
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Andrea" <noreply@.nospam.net> wrote in message
news:5Fkni.2101$BM.412@.tornado.fastwebnet.it...
> Hi,
> I'm very very new in SQLServer.
> I need to create a store procedure that execute a sql file located in a
> PATH
> for example:
>
> C:\Inetpub\Reserved\update.sql
>
>
> HOw can I made this?
>
> Thanks a lot!
>
>|||Andrea
Sorry, I type it from the memory, please test it
EXEC master..xp_cmdshell 'osql.exe -S SVR -U sa -P pswd -i
"c:\Inetpub\Reserved\update.sql" -o "C:\output.txt"'
"Andrea" <noreply@.nospam.net> wrote in message
news:5Fkni.2101$BM.412@.tornado.fastwebnet.it...
> Hi,
> I'm very very new in SQLServer.
> I need to create a store procedure that execute a sql file located in a
> PATH
> for example:
>
> C:\Inetpub\Reserved\update.sql
>
>
> HOw can I made this?
>
> Thanks a lot!
>
>
Monday, March 19, 2012
how to create a script to execute a package?
hello,
I am loading data from one DB to another. I wish to load them every day at night. I know to do that, I must write a script. But how to do it? I don't know. Could someone help me?
thanks in advance
Let se I got it right.
If you have a package or set of packages; you can use SQL Server agent to create a Job that rons on a scheduled basis. Why would you need an script?
|||The easiest way to create a script is to use DTEXECUI.EXE to build the necessary command-line parameters for DTEXEC.EXE. You can then copy this into a batch file and schedule it using whatever tool you want.
With this said, Rafael is right - using SQL Server Agent is probably the simplest way to execute your packages on a schedule.
how to create a script to execute a package?
hello,
I am loading data from one DB to another. I wish to load them every day at night. I know to do that, I must write a script. But how to do it? I don't know. Could someone help me?
thanks in advance
Let se I got it right.
If you have a package or set of packages; you can use SQL Server agent to create a Job that rons on a scheduled basis. Why would you need an script?
|||The easiest way to create a script is to use DTEXECUI.EXE to build the necessary command-line parameters for DTEXEC.EXE. You can then copy this into a batch file and schedule it using whatever tool you want.
With this said, Rafael is right - using SQL Server Agent is probably the simplest way to execute your packages on a schedule.
Monday, March 12, 2012
How to create a job to execute an Access program?
I am trying to schedule an Access Job using SQL Server Agent. I have
created a .BAT file with the full path to a Access .mdb file. The Access
program will automatically start up a form and copy data from an Orcale
database into the SQL Server. When I execute the .BAT file by hand (by
double-clicking it) everything works fine. I can't get things to work
when I create a job and specify the full path to the .BAT file in the
command window. The following is what I enter in the command window: "G:
\SQLJobs\GetData.mdb". I get no errors in the event log. Can someone
give me an idea on what I need to do. I assume I am missing something.
Thanks.
John
Hi
Scheduling an interactive program is not a good idea. You may want to change
this to use a linked server and have it totally within SQL Server itself or
you may want to look at DTS as an another alternative.
John
"John" <nomail@.none.com> wrote in message
news:MPG.1ccc2ae08c2915d9989682@.news.comcast.gigan ews.com...
>I am trying to schedule an Access Job using SQL Server Agent. I have
> created a .BAT file with the full path to a Access .mdb file. The Access
> program will automatically start up a form and copy data from an Orcale
> database into the SQL Server. When I execute the .BAT file by hand (by
> double-clicking it) everything works fine. I can't get things to work
> when I create a job and specify the full path to the .BAT file in the
> command window. The following is what I enter in the command window: "G:
> \SQLJobs\GetData.mdb". I get no errors in the event log. Can someone
> give me an idea on what I need to do. I assume I am missing something.
> Thanks.
> John
|||I think you should point the path to "access.exe", something like this:
"C:\program files\microsoft office\office\access.exe g:\...\GetData.mdb"
But I really do not know if it good idea to run such external process by
using SQL Server Agaent. SQL Server agent (and SQL Server) has no control to
the Access app except for starting it. That is why you do not have error
logged. To schedule this Access app, I'd simply use Windows' "Scheduled
Tasks" applet in Control Panel.
Specific to your task, using DTS might be more suitable.
"John" <nomail@.none.com> wrote in message
news:MPG.1ccc2ae08c2915d9989682@.news.comcast.gigan ews.com...
> I am trying to schedule an Access Job using SQL Server Agent. I have
> created a .BAT file with the full path to a Access .mdb file. The Access
> program will automatically start up a form and copy data from an Orcale
> database into the SQL Server. When I execute the .BAT file by hand (by
> double-clicking it) everything works fine. I can't get things to work
> when I create a job and specify the full path to the .BAT file in the
> command window. The following is what I enter in the command window: "G:
> \SQLJobs\GetData.mdb". I get no errors in the event log. Can someone
> give me an idea on what I need to do. I assume I am missing something.
> Thanks.
> John
created a .BAT file with the full path to a Access .mdb file. The Access
program will automatically start up a form and copy data from an Orcale
database into the SQL Server. When I execute the .BAT file by hand (by
double-clicking it) everything works fine. I can't get things to work
when I create a job and specify the full path to the .BAT file in the
command window. The following is what I enter in the command window: "G:
\SQLJobs\GetData.mdb". I get no errors in the event log. Can someone
give me an idea on what I need to do. I assume I am missing something.
Thanks.
John
Hi
Scheduling an interactive program is not a good idea. You may want to change
this to use a linked server and have it totally within SQL Server itself or
you may want to look at DTS as an another alternative.
John
"John" <nomail@.none.com> wrote in message
news:MPG.1ccc2ae08c2915d9989682@.news.comcast.gigan ews.com...
>I am trying to schedule an Access Job using SQL Server Agent. I have
> created a .BAT file with the full path to a Access .mdb file. The Access
> program will automatically start up a form and copy data from an Orcale
> database into the SQL Server. When I execute the .BAT file by hand (by
> double-clicking it) everything works fine. I can't get things to work
> when I create a job and specify the full path to the .BAT file in the
> command window. The following is what I enter in the command window: "G:
> \SQLJobs\GetData.mdb". I get no errors in the event log. Can someone
> give me an idea on what I need to do. I assume I am missing something.
> Thanks.
> John
|||I think you should point the path to "access.exe", something like this:
"C:\program files\microsoft office\office\access.exe g:\...\GetData.mdb"
But I really do not know if it good idea to run such external process by
using SQL Server Agaent. SQL Server agent (and SQL Server) has no control to
the Access app except for starting it. That is why you do not have error
logged. To schedule this Access app, I'd simply use Windows' "Scheduled
Tasks" applet in Control Panel.
Specific to your task, using DTS might be more suitable.
"John" <nomail@.none.com> wrote in message
news:MPG.1ccc2ae08c2915d9989682@.news.comcast.gigan ews.com...
> I am trying to schedule an Access Job using SQL Server Agent. I have
> created a .BAT file with the full path to a Access .mdb file. The Access
> program will automatically start up a form and copy data from an Orcale
> database into the SQL Server. When I execute the .BAT file by hand (by
> double-clicking it) everything works fine. I can't get things to work
> when I create a job and specify the full path to the .BAT file in the
> command window. The following is what I enter in the command window: "G:
> \SQLJobs\GetData.mdb". I get no errors in the event log. Can someone
> give me an idea on what I need to do. I assume I am missing something.
> Thanks.
> John
How to create a job to execute an Access program?
I am trying to schedule an Access Job using SQL Server Agent. I have
created a .BAT file with the full path to a Access .mdb file. The Access
program will automatically start up a form and copy data from an Orcale
database into the SQL Server. When I execute the .BAT file by hand (by
double-clicking it) everything works fine. I can't get things to work
when I create a job and specify the full path to the .BAT file in the
command window. The following is what I enter in the command window: "G:
\SQLJobs\GetData.mdb". I get no errors in the event log. Can someone
give me an idea on what I need to do. I assume I am missing something.
Thanks.
JohnHi
Scheduling an interactive program is not a good idea. You may want to change
this to use a linked server and have it totally within SQL Server itself or
you may want to look at DTS as an another alternative.
John
"John" <nomail@.none.com> wrote in message
news:MPG.1ccc2ae08c2915d9989682@.news.comcast.giganews.com...
>I am trying to schedule an Access Job using SQL Server Agent. I have
> created a .BAT file with the full path to a Access .mdb file. The Access
> program will automatically start up a form and copy data from an Orcale
> database into the SQL Server. When I execute the .BAT file by hand (by
> double-clicking it) everything works fine. I can't get things to work
> when I create a job and specify the full path to the .BAT file in the
> command window. The following is what I enter in the command window: "G:
> \SQLJobs\GetData.mdb". I get no errors in the event log. Can someone
> give me an idea on what I need to do. I assume I am missing something.
> Thanks.
> John|||I think you should point the path to "access.exe", something like this:
"C:\program files\microsoft office\office\access.exe g:\...\GetData.mdb"
But I really do not know if it good idea to run such external process by
using SQL Server Agaent. SQL Server agent (and SQL Server) has no control to
the Access app except for starting it. That is why you do not have error
logged. To schedule this Access app, I'd simply use Windows' "Scheduled
Tasks" applet in Control Panel.
Specific to your task, using DTS might be more suitable.
"John" <nomail@.none.com> wrote in message
news:MPG.1ccc2ae08c2915d9989682@.news.comcast.giganews.com...
> I am trying to schedule an Access Job using SQL Server Agent. I have
> created a .BAT file with the full path to a Access .mdb file. The Access
> program will automatically start up a form and copy data from an Orcale
> database into the SQL Server. When I execute the .BAT file by hand (by
> double-clicking it) everything works fine. I can't get things to work
> when I create a job and specify the full path to the .BAT file in the
> command window. The following is what I enter in the command window: "G:
> \SQLJobs\GetData.mdb". I get no errors in the event log. Can someone
> give me an idea on what I need to do. I assume I am missing something.
> Thanks.
> John
created a .BAT file with the full path to a Access .mdb file. The Access
program will automatically start up a form and copy data from an Orcale
database into the SQL Server. When I execute the .BAT file by hand (by
double-clicking it) everything works fine. I can't get things to work
when I create a job and specify the full path to the .BAT file in the
command window. The following is what I enter in the command window: "G:
\SQLJobs\GetData.mdb". I get no errors in the event log. Can someone
give me an idea on what I need to do. I assume I am missing something.
Thanks.
JohnHi
Scheduling an interactive program is not a good idea. You may want to change
this to use a linked server and have it totally within SQL Server itself or
you may want to look at DTS as an another alternative.
John
"John" <nomail@.none.com> wrote in message
news:MPG.1ccc2ae08c2915d9989682@.news.comcast.giganews.com...
>I am trying to schedule an Access Job using SQL Server Agent. I have
> created a .BAT file with the full path to a Access .mdb file. The Access
> program will automatically start up a form and copy data from an Orcale
> database into the SQL Server. When I execute the .BAT file by hand (by
> double-clicking it) everything works fine. I can't get things to work
> when I create a job and specify the full path to the .BAT file in the
> command window. The following is what I enter in the command window: "G:
> \SQLJobs\GetData.mdb". I get no errors in the event log. Can someone
> give me an idea on what I need to do. I assume I am missing something.
> Thanks.
> John|||I think you should point the path to "access.exe", something like this:
"C:\program files\microsoft office\office\access.exe g:\...\GetData.mdb"
But I really do not know if it good idea to run such external process by
using SQL Server Agaent. SQL Server agent (and SQL Server) has no control to
the Access app except for starting it. That is why you do not have error
logged. To schedule this Access app, I'd simply use Windows' "Scheduled
Tasks" applet in Control Panel.
Specific to your task, using DTS might be more suitable.
"John" <nomail@.none.com> wrote in message
news:MPG.1ccc2ae08c2915d9989682@.news.comcast.giganews.com...
> I am trying to schedule an Access Job using SQL Server Agent. I have
> created a .BAT file with the full path to a Access .mdb file. The Access
> program will automatically start up a form and copy data from an Orcale
> database into the SQL Server. When I execute the .BAT file by hand (by
> double-clicking it) everything works fine. I can't get things to work
> when I create a job and specify the full path to the .BAT file in the
> command window. The following is what I enter in the command window: "G:
> \SQLJobs\GetData.mdb". I get no errors in the event log. Can someone
> give me an idea on what I need to do. I assume I am missing something.
> Thanks.
> John
How to create a job to execute an Access program?
I am trying to schedule an Access Job using SQL Server Agent. I have
created a .BAT file with the full path to a Access .mdb file. The Access
program will automatically start up a form and copy data from an Orcale
database into the SQL Server. When I execute the .BAT file by hand (by
double-clicking it) everything works fine. I can't get things to work
when I create a job and specify the full path to the .BAT file in the
command window. The following is what I enter in the command window: "G:
\SQLJobs\GetData.mdb". I get no errors in the event log. Can someone
give me an idea on what I need to do. I assume I am missing something.
Thanks.
JohnHi
Scheduling an interactive program is not a good idea. You may want to change
this to use a linked server and have it totally within SQL Server itself or
you may want to look at DTS as an another alternative.
John
"John" <nomail@.none.com> wrote in message
news:MPG.1ccc2ae08c2915d9989682@.news.comcast.giganews.com...
>I am trying to schedule an Access Job using SQL Server Agent. I have
> created a .BAT file with the full path to a Access .mdb file. The Access
> program will automatically start up a form and copy data from an Orcale
> database into the SQL Server. When I execute the .BAT file by hand (by
> double-clicking it) everything works fine. I can't get things to work
> when I create a job and specify the full path to the .BAT file in the
> command window. The following is what I enter in the command window: "G:
> \SQLJobs\GetData.mdb". I get no errors in the event log. Can someone
> give me an idea on what I need to do. I assume I am missing something.
> Thanks.
> John|||I think you should point the path to "access.exe", something like this:
"C:\program files\microsoft office\office\access.exe g:\...\GetData.mdb"
But I really do not know if it good idea to run such external process by
using SQL Server Agaent. SQL Server agent (and SQL Server) has no control to
the Access app except for starting it. That is why you do not have error
logged. To schedule this Access app, I'd simply use Windows' "Scheduled
Tasks" applet in Control Panel.
Specific to your task, using DTS might be more suitable.
"John" <nomail@.none.com> wrote in message
news:MPG.1ccc2ae08c2915d9989682@.news.comcast.giganews.com...
> I am trying to schedule an Access Job using SQL Server Agent. I have
> created a .BAT file with the full path to a Access .mdb file. The Access
> program will automatically start up a form and copy data from an Orcale
> database into the SQL Server. When I execute the .BAT file by hand (by
> double-clicking it) everything works fine. I can't get things to work
> when I create a job and specify the full path to the .BAT file in the
> command window. The following is what I enter in the command window: "G:
> \SQLJobs\GetData.mdb". I get no errors in the event log. Can someone
> give me an idea on what I need to do. I assume I am missing something.
> Thanks.
> John
created a .BAT file with the full path to a Access .mdb file. The Access
program will automatically start up a form and copy data from an Orcale
database into the SQL Server. When I execute the .BAT file by hand (by
double-clicking it) everything works fine. I can't get things to work
when I create a job and specify the full path to the .BAT file in the
command window. The following is what I enter in the command window: "G:
\SQLJobs\GetData.mdb". I get no errors in the event log. Can someone
give me an idea on what I need to do. I assume I am missing something.
Thanks.
JohnHi
Scheduling an interactive program is not a good idea. You may want to change
this to use a linked server and have it totally within SQL Server itself or
you may want to look at DTS as an another alternative.
John
"John" <nomail@.none.com> wrote in message
news:MPG.1ccc2ae08c2915d9989682@.news.comcast.giganews.com...
>I am trying to schedule an Access Job using SQL Server Agent. I have
> created a .BAT file with the full path to a Access .mdb file. The Access
> program will automatically start up a form and copy data from an Orcale
> database into the SQL Server. When I execute the .BAT file by hand (by
> double-clicking it) everything works fine. I can't get things to work
> when I create a job and specify the full path to the .BAT file in the
> command window. The following is what I enter in the command window: "G:
> \SQLJobs\GetData.mdb". I get no errors in the event log. Can someone
> give me an idea on what I need to do. I assume I am missing something.
> Thanks.
> John|||I think you should point the path to "access.exe", something like this:
"C:\program files\microsoft office\office\access.exe g:\...\GetData.mdb"
But I really do not know if it good idea to run such external process by
using SQL Server Agaent. SQL Server agent (and SQL Server) has no control to
the Access app except for starting it. That is why you do not have error
logged. To schedule this Access app, I'd simply use Windows' "Scheduled
Tasks" applet in Control Panel.
Specific to your task, using DTS might be more suitable.
"John" <nomail@.none.com> wrote in message
news:MPG.1ccc2ae08c2915d9989682@.news.comcast.giganews.com...
> I am trying to schedule an Access Job using SQL Server Agent. I have
> created a .BAT file with the full path to a Access .mdb file. The Access
> program will automatically start up a form and copy data from an Orcale
> database into the SQL Server. When I execute the .BAT file by hand (by
> double-clicking it) everything works fine. I can't get things to work
> when I create a job and specify the full path to the .BAT file in the
> command window. The following is what I enter in the command window: "G:
> \SQLJobs\GetData.mdb". I get no errors in the event log. Can someone
> give me an idea on what I need to do. I assume I am missing something.
> Thanks.
> John
Subscribe to:
Posts (Atom)