calling rs.exe from a DTS

Hi everyone,
i'm trying to call a stored procedure that runs rs.exe from a DTS
package,the package works perfectly giving me no errors but when i look at
the results rs.exe has done nothing.
If I call the stored procedure directly it works just fine rendering my pdf
report on filesystem
Anyone has ideas on what is happenning?
Thank YouLnZ wrote:
> Anyone has ideas on what is happenning?
Check the permissions of the account under wich the SQL-ServerAgent-service
is running.
regards
Franksql

Calling an .EXE from a SQL Job?

I'm trying to call a .exe as a Job Step that pulls some data and sends out
custom emails that will execute after a DTS package runs. I tested the EXE
from the command line and it works but I don't know how to call it from with
the Job Step.
I have the step called ''Example of calling an EXE" and set an Operating
System Command (CmdExec) with:
xp_cmdshell 'C:\MyApp\SendAlert.exe' , no_output
When it runs, I check the Windows event and it says:
SQL Server Scheduled Job 'Example of calling an EXE'
(0x0AE46A0A26688D48B29A3C71684CB284) - Status: Failed - Invoked on:
2005-10-27 13:04:45 - Message: The job failed. The Job was invoked by User
sa. The last step to run was step 1 (Calling the EXE).
I have no idea why it's failing when called as a job?
One other thing I found while executing the following in query analyzer:
xp_cmdshell 'C:\MyApp\SendAlert.exe'
returns:
"The system cannot find the path specified"
Thanks again.
"Dave" wrote:

> I'm trying to call a .exe as a Job Step that pulls some data and sends out
> custom emails that will execute after a DTS package runs. I tested the EXE
> from the command line and it works but I don't know how to call it from with
> the Job Step.
> I have the step called ''Example of calling an EXE" and set an Operating
> System Command (CmdExec) with:
> xp_cmdshell 'C:\MyApp\SendAlert.exe' , no_output
> When it runs, I check the Windows event and it says:
> SQL Server Scheduled Job 'Example of calling an EXE'
> (0x0AE46A0A26688D48B29A3C71684CB284) - Status: Failed - Invoked on:
> 2005-10-27 13:04:45 - Message: The job failed. The Job was invoked by User
> sa. The last step to run was step 1 (Calling the EXE).
> I have no idea why it's failing when called as a job?
|||Dave wrote:
> I'm trying to call a .exe as a Job Step that pulls some data and
> sends out custom emails that will execute after a DTS package runs.
> I tested the EXE from the command line and it works but I don't know
> how to call it from with the Job Step.
> I have the step called ''Example of calling an EXE" and set an
> Operating System Command (CmdExec) with:
> xp_cmdshell 'C:\MyApp\SendAlert.exe' , no_output
> When it runs, I check the Windows event and it says:
> SQL Server Scheduled Job 'Example of calling an EXE'
> (0x0AE46A0A26688D48B29A3C71684CB284) - Status: Failed - Invoked on:
> 2005-10-27 13:04:45 - Message: The job failed. The Job was invoked
> by User sa. The last step to run was step 1 (Calling the EXE).
> I have no idea why it's failing when called as a job?
xp_cmdshell is in the master database. You need to fully qualify the
name if you are not in master:
exec master..xp_cmdshell 'dir'
Also, you need to make sure the SQL Server service account has access to
the folder where you placed the EXE. If not, you need to grant the
account access of move the file(s) to a folder where the account has
access.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Dave
You can also run cmd commands from SQL jobs In the same job you are running
DTS
Paul Zanbaka
DBA, Programmer Analyst
"David Gugick" wrote:

> Dave wrote:
> xp_cmdshell is in the master database. You need to fully qualify the
> name if you are not in master:
> exec master..xp_cmdshell 'dir'
> Also, you need to make sure the SQL Server service account has access to
> the folder where you placed the EXE. If not, you need to grant the
> account access of move the file(s) to a folder where the account has
> access.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>

Calling an .EXE from a SQL Job?

I'm trying to call a .exe as a Job Step that pulls some data and sends out
custom emails that will execute after a DTS package runs. I tested the EXE
from the command line and it works but I don't know how to call it from with
the Job Step.
I have the step called ''Example of calling an EXE" and set an Operating
System Command (CmdExec) with:
xp_cmdshell 'C:\MyApp\SendAlert.exe' , no_output
When it runs, I check the Windows event and it says:
SQL Server Scheduled Job 'Example of calling an EXE'
(0x0AE46A0A26688D48B29A3C71684CB284) - Status: Failed - Invoked on:
2005-10-27 13:04:45 - Message: The job failed. The Job was invoked by User
sa. The last step to run was step 1 (Calling the EXE).
I have no idea why it's failing when called as a job?One other thing I found while executing the following in query analyzer:
xp_cmdshell 'C:\MyApp\SendAlert.exe'
returns:
"The system cannot find the path specified"
Thanks again.
"Dave" wrote:

> I'm trying to call a .exe as a Job Step that pulls some data and sends out
> custom emails that will execute after a DTS package runs. I tested the EX
E
> from the command line and it works but I don't know how to call it from wi
th
> the Job Step.
> I have the step called ''Example of calling an EXE" and set an Operating
> System Command (CmdExec) with:
> xp_cmdshell 'C:\MyApp\SendAlert.exe' , no_output
> When it runs, I check the Windows event and it says:
> SQL Server Scheduled Job 'Example of calling an EXE'
> (0x0AE46A0A26688D48B29A3C71684CB284) - Status: Failed - Invoked on:
> 2005-10-27 13:04:45 - Message: The job failed. The Job was invoked by Use
r
> sa. The last step to run was step 1 (Calling the EXE).
> I have no idea why it's failing when called as a job?|||Dave wrote:
> I'm trying to call a .exe as a Job Step that pulls some data and
> sends out custom emails that will execute after a DTS package runs.
> I tested the EXE from the command line and it works but I don't know
> how to call it from with the Job Step.
> I have the step called ''Example of calling an EXE" and set an
> Operating System Command (CmdExec) with:
> xp_cmdshell 'C:\MyApp\SendAlert.exe' , no_output
> When it runs, I check the Windows event and it says:
> SQL Server Scheduled Job 'Example of calling an EXE'
> (0x0AE46A0A26688D48B29A3C71684CB284) - Status: Failed - Invoked on:
> 2005-10-27 13:04:45 - Message: The job failed. The Job was invoked
> by User sa. The last step to run was step 1 (Calling the EXE).
> I have no idea why it's failing when called as a job?
xp_cmdshell is in the master database. You need to fully qualify the
name if you are not in master:
exec master..xp_cmdshell 'dir'
Also, you need to make sure the SQL Server service account has access to
the folder where you placed the EXE. If not, you need to grant the
account access of move the file(s) to a folder where the account has
access.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Dave
You can also run cmd commands from SQL jobs In the same job you are running
DTS
--
Paul Zanbaka
DBA, Programmer Analyst
"David Gugick" wrote:

> Dave wrote:
> xp_cmdshell is in the master database. You need to fully qualify the
> name if you are not in master:
> exec master..xp_cmdshell 'dir'
> Also, you need to make sure the SQL Server service account has access to
> the folder where you placed the EXE. If not, you need to grant the
> account access of move the file(s) to a folder where the account has
> access.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>

Calling an .EXE from a SQL Job?

I'm trying to call a .exe as a Job Step that pulls some data and sends out
custom emails that will execute after a DTS package runs. I tested the EXE
from the command line and it works but I don't know how to call it from with
the Job Step.
I have the step called ''Example of calling an EXE" and set an Operating
System Command (CmdExec) with:
xp_cmdshell 'C:\MyApp\SendAlert.exe' , no_output
When it runs, I check the Windows event and it says:
SQL Server Scheduled Job 'Example of calling an EXE'
(0x0AE46A0A26688D48B29A3C71684CB284) - Status: Failed - Invoked on:
2005-10-27 13:04:45 - Message: The job failed. The Job was invoked by User
sa. The last step to run was step 1 (Calling the EXE).
I have no idea why it's failing when called as a job?One other thing I found while executing the following in query analyzer:
xp_cmdshell 'C:\MyApp\SendAlert.exe'
returns:
"The system cannot find the path specified"
Thanks again.
"Dave" wrote:
> I'm trying to call a .exe as a Job Step that pulls some data and sends out
> custom emails that will execute after a DTS package runs. I tested the EXE
> from the command line and it works but I don't know how to call it from with
> the Job Step.
> I have the step called ''Example of calling an EXE" and set an Operating
> System Command (CmdExec) with:
> xp_cmdshell 'C:\MyApp\SendAlert.exe' , no_output
> When it runs, I check the Windows event and it says:
> SQL Server Scheduled Job 'Example of calling an EXE'
> (0x0AE46A0A26688D48B29A3C71684CB284) - Status: Failed - Invoked on:
> 2005-10-27 13:04:45 - Message: The job failed. The Job was invoked by User
> sa. The last step to run was step 1 (Calling the EXE).
> I have no idea why it's failing when called as a job?|||Dave wrote:
> I'm trying to call a .exe as a Job Step that pulls some data and
> sends out custom emails that will execute after a DTS package runs.
> I tested the EXE from the command line and it works but I don't know
> how to call it from with the Job Step.
> I have the step called ''Example of calling an EXE" and set an
> Operating System Command (CmdExec) with:
> xp_cmdshell 'C:\MyApp\SendAlert.exe' , no_output
> When it runs, I check the Windows event and it says:
> SQL Server Scheduled Job 'Example of calling an EXE'
> (0x0AE46A0A26688D48B29A3C71684CB284) - Status: Failed - Invoked on:
> 2005-10-27 13:04:45 - Message: The job failed. The Job was invoked
> by User sa. The last step to run was step 1 (Calling the EXE).
> I have no idea why it's failing when called as a job?
xp_cmdshell is in the master database. You need to fully qualify the
name if you are not in master:
exec master..xp_cmdshell 'dir'
Also, you need to make sure the SQL Server service account has access to
the folder where you placed the EXE. If not, you need to grant the
account access of move the file(s) to a folder where the account has
access.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Dave
You can also run cmd commands from SQL jobs In the same job you are running
DTS
--
Paul Zanbaka
DBA, Programmer Analyst
"David Gugick" wrote:
> Dave wrote:
> > I'm trying to call a .exe as a Job Step that pulls some data and
> > sends out custom emails that will execute after a DTS package runs.
> > I tested the EXE from the command line and it works but I don't know
> > how to call it from with the Job Step.
> >
> > I have the step called ''Example of calling an EXE" and set an
> > Operating System Command (CmdExec) with:
> >
> > xp_cmdshell 'C:\MyApp\SendAlert.exe' , no_output
> >
> > When it runs, I check the Windows event and it says:
> >
> > SQL Server Scheduled Job 'Example of calling an EXE'
> > (0x0AE46A0A26688D48B29A3C71684CB284) - Status: Failed - Invoked on:
> > 2005-10-27 13:04:45 - Message: The job failed. The Job was invoked
> > by User sa. The last step to run was step 1 (Calling the EXE).
> >
> > I have no idea why it's failing when called as a job?
> xp_cmdshell is in the master database. You need to fully qualify the
> name if you are not in master:
> exec master..xp_cmdshell 'dir'
> Also, you need to make sure the SQL Server service account has access to
> the folder where you placed the EXE. If not, you need to grant the
> account access of move the file(s) to a folder where the account has
> access.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>

Call DTS Package from ASP via SP runs it 2x. Runs fine from w/in SQL

I have a DTS package that is called from a SP in SQL Server 2000. When it is run from within SQL, the profiler shows that the Application "DTS Designer" is only called once and does it's tasks and returns the recordset....

When the SP is called from via ASP, the profiler shows that the Application "DTS Designer" actually runs 2x before returning... asside from killing efficiency, the extra run is also causing my page to take forever (15 minutes)

ASP Call:
myDSN=<DSNString> (works fine...)
Set DataConn = Server.CreateObject("ADODB.Connection")
DataConn.Open myDSN
pcuserid = "DBUsername"
pcpassword = "DBPassword"
strQuery = "exec usa_runimportcreditdtspkg @.importfilename= '" & psFileName & "', @.userid= '" & pcuserid & "',@.password='" & pcpassword & "' "
Set rsMain = Server.CreateObject("ADODB.RecordSet")
rsMain.Open strQuery,DataConn,1,3

Here is the SP code:
declare @.hr as int,
@.opkg as int -- the object token that will refer to the created PKG

--Creating the DTS Package Object:
EXEC @.hr = sp_OACreate 'DTS.Package', @.oPKG OUT
IF @.hr <> 0
BEGIN
PRINT '*** Create Package object failed'
EXEC sp_displayoaerrorinfo @.oPKG, @.hr
RETURN
END

--Loading the Package:
declare @.loadstring as varchar(250)
set @.loadstring = 'LoadFromSQLServer("WEBDEV1", "'+rTrim(@.userid)+'", "'+rTrim(@.password)+'", 0, , , , "importcredits")'

EXEC @.hr = sp_OAMethod @.oPKG,@.loadstring, NULL
IF @.hr <> 0
BEGIN
PRINT '*** Load Package failed'
EXEC sp_displayoaerrorinfo @.oPKG, @.hr
RETURN
END

-- clear out the table before proceeding if it exists
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ImportCredits]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
delete from ImportCredits where employeeSSN is not null
END

-- use the passed filename to set the global variable inside the pkg
EXEC @.hr = sp_OASetProperty @.oPKG, 'GlobalVariables("ImportFilename").Value', @.importfilename

--Executing the Package:
EXEC @.hr = sp_OAMethod @.oPKG, 'Execute'
IF @.hr <> 0
BEGIN
PRINT '*** Execute failed'
EXEC sp_displayoaerrorinfo @.oPKG , @.hr
RETURN
END

--Cleaning up:
EXEC @.hr = sp_OADestroy @.oPKG
IF @.hr <> 0
BEGIN
PRINT '*** Destroy Package failed'
EXEC sp_displayoaerrorinfo @.oPKG, @.hr
RETURN
END

select <Fields>
from <DTSPopulatedTable> i
where substring(invoiceno,1,3)<> 'PSI' and amount > 0
order by customer,employeessn,invoicenoHere's a few ideas...I'd try each of these steps in order one at a time until you get the performance you want.

1) It looks like you are using a DSN as your connection when you create the connection object. First, I'd try using the OLE DB for SQL Server instead. I have found that using the OLE DB provider, rather than a DSN or a "DSN-less" connection, is a lot faster. Either way, you want to do this step. By using an OLE DB connection, you won't need a DSN at all for the ASP connection. This has its advantages too.

2) Next, I would explicitly tell the Recordset object that I am executing a stored procedure, rather than passing SQL that states "EXEC spXXXX", by using the adCmdStoredProc constant in the last argument of the Open method. You may see some improvements there too.

3) Last, try using a command object with the recordset object since you are executing stored procedures.

Here is some sample code to get you started...

' try these one at a time, but build them in order. Do step 1 AND step 2, or do step 1 AND step 3
Dim strcon, con, rst, cmd ' need cmd object variable only if you try step 3

' >>> step 1
' create string to use to connect to SQL Server
strcon = "Provider=SQLOLEDB; Data Source=ServerName; Initial Catalog=DBName; User ID=UserName; Password=Password;"

' instantiate connection object
Set con = Server.CreateObject("ADODB.Connection")

' open connection to SQL Server using the connection string
con.Open strcon

' >>> step 2
' instantiate recordset object
Set rst = Server.CreateObject("ADODB.Recordset")

' open the recordset object (execute the stored procedure)
' you need to use the integer constants for the CursorType, LockType, and Options arguments in ASP (sorry, I don't know them off hand)
rst.Open "sp_name", con, CursorType, LockType, adCmdStoredProc

' >>> step 3
' using the command object can be a bit tricky. I never tried it in ASP, just VB 6, so I'm only guessing on my conversions since the syntax is totally different...

' instantiate command object
Set cmd = Server.CreateObject("ADODB.Command")

' set command type and command text
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "sp_name"

' create and set the parameter values (only if there are input parms for the sp) otherwise, you don't need these next two groups of code...
cmd.Parameters.Append("parm1", DataType, adParamInput)
cmd.Parameters.Append("parm2", DataType, adParamInput)
cmd.Parameters("parm1").Value = "value for first parameter"
cmd.Parameters("parm2").Value = "value for second parameter"

' associate the command object with the connection object
cmd.ActiveConnection = con

' open the recordset object (execute the stored procedure)
Set rst = cmd.Execute

Another thing is that you may want to try tweaking the CursorType and LockType arguments whenever you call the Recordset.Open method for either steps 2 or 3 depending on what you want to do with the recordset. Be sure to only use the minimum amount of locking or cursor fetching capabilities you absolutely need to maximize performance.

Lastly...whew! With regards to VBScript and ASP, you may want to use the Response.Buffer and flush it at times depending on how you want to display the page in the browser.

If you have any questions, feel free to ask.

HTH!

Kael

calculated members from a cube using the OPENQUERY

We have a DTS package that runs once a month. It has been working for more than four years now, but all of the sudden it started to fail. The package starts with loading data from an sql database and then updating the cube and then send the calculated results to a SQL2000 server table.

the problem starts at the last part when reading from cube into sql2000 table. below is my select which fails:

select

convert(decimal(18,2), replace("[Measures].[Sales]", ',', '.')),

convert(decimal(18,2), replace("[Measures].[Cost]", ',', '.')),

convert(decimal(18,2), replace("[Measures].[Hours A1]", ',', '.')),

convert(decimal(18,2), replace("[Measures].[Data]", ',', '.'))

convert(decimal(18,2), replace("[Measures].[Orderlines A3]", ',', '.')),

convert(decimal(18,2), replace("[Measures].[Invoices E3]", ',', '.'))

from openquery

( GONS021_OLAP, 'select

{[Measures].[Sales], [Measures].[Cost], [Measures].[TB], [Measures].[TG], [Measures].M1, [Measures].[M2], [Measures].[A1], [Measures].[A2], [Measures].[A3], [Measures].[A4], [Measures].[A5], [Measures].[A6], [Measures].[A7], [Measures].[A8],[Measures].[T1], [Measures].[L1], [Measures].[L2], [Measures].[L3], [Measures].[E1], [Measures].[E2], [Measures].[E3], [Measures].[E4], [Measures].[E5],[Measures].[Hours A1],[Measures].[Data],[Measures].[Orderlines A3],[Measures].[Qty Returned A8],[Measures].[Invoices E3] } on columns,

{[Customer].[All Customer], [Customer].[Customer No].Members} on rows

from TotalTable where ([Time].&[2007])')

The error message:

The MDX queries executed through the OPENQUERY function returns the following error messages for some members of the dimension used to construct them:

Could not get the data of the row from the OLE DB provider 'MSOLAP'.

OLE DB error trace [OLE/DB Provider 'MSOLAP' IRowset::GetData returned 0x80040e21:Data status returned from the provider: [COLUMN_NAME=[Measures].[Data] STATUS=DBSTATUS_E_UNAVAILABLE], [COLUMN_NAME=[Measures].[Invoices E3] STATUS=DBSTATUS_E_UNAVAILABLE]]

The same MDX Queries execute without any problems through the MDX Sample

Application!

OBS: I have SP4 on both SQL and Analysis

Hi there i am wondering if you managed to solve this, as I had exactly the same problem? A query that worked previously, is now not working. I am also on SQL Server 2000 Analysis SP 4

calculated members from a cube using the OPENQUERY

We have a DTS package that runs once a month. It has been working for more than four years now, but all of the sudden it started to fail. The package starts with loading data from an sql database and then updating the cube and then send the calculated results to a SQL2000 server table.

the problem starts at the last part when reading from cube into sql2000 table. below is my select which fails:

select

convert(decimal(18,2), replace("[Measures].[Sales]", ',', '.')),

convert(decimal(18,2), replace("[Measures].[Cost]", ',', '.')),

convert(decimal(18,2), replace("[Measures].[Hours A1]", ',', '.')),

convert(decimal(18,2), replace("[Measures].[Data]", ',', '.'))

convert(decimal(18,2), replace("[Measures].[Orderlines A3]", ',', '.')),

convert(decimal(18,2), replace("[Measures].[Invoices E3]", ',', '.'))

from openquery

( GONS021_OLAP, 'select

{[Measures].[Sales], [Measures].[Cost], [Measures].[TB], [Measures].[TG], [Measures].M1, [Measures].[M2], [Measures].[A1], [Measures].[A2], [Measures].[A3], [Measures].[A4], [Measures].[A5], [Measures].[A6], [Measures].[A7], [Measures].[A8],[Measures].[T1], [Measures].[L1], [Measures].[L2], [Measures].[L3], [Measures].[E1], [Measures].[E2], [Measures].[E3], [Measures].[E4], [Measures].[E5],[Measures].[Hours A1],[Measures].[Data],[Measures].[Orderlines A3],[Measures].[Qty Returned A8],[Measures].[Invoices E3] } on columns,

{[Customer].[All Customer], [Customer].[Customer No].Members} on rows

from TotalTable where ([Time].&[2007])')

The error message:

The MDX queries executed through the OPENQUERY function returns the following error messages for some members of the dimension used to construct them:

Could not get the data of the row from the OLE DB provider 'MSOLAP'.

OLE DB error trace [OLE/DB Provider 'MSOLAP' IRowset::GetData returned 0x80040e21:Data status returned from the provider: [COLUMN_NAME=[Measures].[Data] STATUS=DBSTATUS_E_UNAVAILABLE], [COLUMN_NAME=[Measures].[Invoices E3] STATUS=DBSTATUS_E_UNAVAILABLE]]

The same MDX Queries execute without any problems through the MDX Sample

Application!

OBS: I have SP4 on both SQL and Analysis

Hi there i am wondering if you managed to solve this, as I had exactly the same problem? A query that worked previously, is now not working. I am also on SQL Server 2000 Analysis SP 4