Executing MAXL Scripts in Execute Process Task

Hello,

first of all a few words about our achitecture:

we have a windows 2003 server with sql sever 2005 and connect on this server with 3 clients (Visual Studio) on the Windows 2003 Server there is also an Hyperion Essbase Server installed.

In the Visual Studio I try to execute a batch script which is located on the server. But how i develop that? I have tried to rebuilt the servers File structure and save the Package on the Server. < didn't worked

My Question how can I make a package in which I execute a bat file which is located on the Server without developing it on the Server?

Thank you in advance!

Can you put the batch file in a share, so that you can use a UNC path to reference it? That will work from server or client.

ExecuteQuery taking a long time

Hi:

I have a query which returns approximately 50000 records, I am using a linked server to connect to two databases and retrieve data. For some reason it is taking a liitle more than hour to execute the query, but on MS Sql Server query window it comes after few minutes but the query runs for a long time.

How can expediate my query execution process.

Environment details

Database: MS Sql Server 64bit 2005
MS Sql jar file: sqljdbc_1.2.jar
OS: Windows both server and client.

Connect String in java code:

jdbcqlserver://sample_server:1433;databaseName=sample_db;user=admin_user;password=admin_pwd

and use PreparedStatement and ResultSet.

Regards
Arup
Try using a stored procedure if you can (dont know if you can do that in JDBC )|||

Hello Arup,

I would agree that the best way to solve long running query executions is to use a stored procedure which does all the processing of the data on the server and only return to the client the necessary amount of processed data via output parameters. You can use the CallableStatement API provided by the JDBC driver for doing this.

That being said, the Microsoft SQL Server 2005 v1.2 JDBC driver includes a critical 'feature' called "Adaptive Response Buffering" which allow developers to retrieve large amounts of data. The lengthy latency you are encountering is not because of the query execution, but because of the time it takes the driver to spool the data on the client machine before control is given back to the application.

You can ready more about "Adaptive Reponse Buffering" at http://blogs.msdn.com/jdbcteam/archive/2007/05/02/what-is-adaptive-response-buffering-and-why-should-i-use-it.aspx. This functionality if available in the V1.2 CTP1 driver which has the version 1.2.2323.101. You can download it at http://www.microsoft.com/downloads/details.aspx?familyid=f914793a-6fb4-475f-9537-b8fcb776befd&displaylang=en.

We appreciate your feedback using the newly added functionality of this driver.

Kind Regards,

Jaaved

Execute Process Task & FTP -- NEED HELP 911

Guys!

Need some help on to figure out some solution for my problem. I have Execute process task in my DTS to execute the BAT file to connect to FTP site using simple DOS commands. All, i need, after executing the BAT file using Execute process task, need to send an Email to the administrator about the status of execustion.

The problem occured here, I specifically, take out the password field in the BAT file to connect to the FTP site. In general, the batch file could not connect to FTP site, since i taken out the password field in the BAT file. And after that, in DTS package i tried executing the Execute process Task seperately, and all i can get the messages "command executed successfully".

Based upon the status of the execute process task, I am writing a "Not Successful" message in the table. From that, table i am using the stautus to send an Email to an Administrator.

So Folks please help me out, how can I sql server know the status of the Execute Process task status, whether is it Successful or not.

In my case, this execute process task simply says successful, een though the BAT file could execute the command.

Guys help me out and I am in a little presure to accomblish this ASAP.

Baski.Hey, the bat file did complete successfully..it didn't blow up.

It just didn't do what you wanted...

Why not echo the results of the bat to a file and interogate that?

Or set up 2 osql bat files that are called within the ftp..one for success one for failure and have them insert a row in to a log...

Just an idea...

But why use bcp and a sproc?

That's what I would do...|||Hey Bratt,

I totally understand your way of thought and appriciate it. Great, Since, the DTS which we wrote was completely huge. So any thoughts about error handlimg or pass the error code for Execute process task.

Is there anyway, can i able to check the status of the Execute process task as well as BAT file, that called inside the Execute Process task.

Thanks for your time Guys.

Thoughts are most welcome.

Baski.

Originally posted by Brett Kaiser
Hey, the bat file did complete successfully..it didn't blow up.

It just didn't do what you wanted...

Why not echo the results of the bat to a file and interogate that?

Or set up 2 osql bat files that are called within the ftp..one for success one for failure and have them insert a row in to a log...

Just an idea...

But why use bcp and a sproc?

That's what I would do...|||Check inside the bat file

f errorlevel 1 goto ERROR
if errorlevel 0 goto SUCCESS

:ERROR
echo transfer failed
goto ENDFTMS

:SUCCESS
echo transfer successful
goto ENDFTMS

:ENDFTMS
echo batch program complete|||Bratt,

Good to hear your thought on this. Well, I believe you aware that, the DOS BAT file is executing in FTP location. Also, is there any thought about, CHECKING THE FILE EXISTENCE IN THE FTP LOCATION USING BAT FILE.

Once, i figure out if the xyz.abc file is located in the FTP file, I can pass the parameter or return code as 0/1 to Execute process task to find whether the TASk is successful or not.

Make sence? GUYS IS THERE ANY THOUGHT TO CHECK THE FILE EXISTENCE IN THE FTP LOCATION & RETURN WITH CODE 0 OR 1 AFTER EXECUTING THE BAT FILE.

THANKS IN ADVANCE GUYS.|||Yeah, in a sproc

Delete From Ledger_Folder

If @.Error_Out <> 0
BEGIN
Select @.Error_Loc = 4
Select @.Error_Type = 50001
GOTO Load_Ledger_Init_sp_Error
END

Select @.Command_String = @.FilePath + '\*.*'

-- Select @.Command_String

-- Insert Into Ledger_Folder exec master..xp_cmdshell @.Command_String

Insert Into Ledger_Folder exec master..xp_cmdshell 'Dir d:\Data\Tax\SmartStreamExtracts\*.*'

SELECT @.Result_Count = @.@.ROWCOUNT, @.error_out = @.@.error

If @.Error_Out <> 0
BEGIN
Select @.Error_Loc = 5
Select @.Error_Type = 50001
GOTO Load_Ledger_Init_sp_Error
END

-- select * from ledger_folder

Delete From Ledger_Folder_Parsed

SELECT @.Result_Count = @.@.ROWCOUNT, @.error_out = @.@.error

If @.Error_Out <> 0
BEGIN
Select @.Error_Loc = 6
Select @.Error_Type = 50001
GOTO Load_Ledger_Init_sp_Error
END

Insert Into Ledger_Folder_Parsed (Create_Time, File_Size, File_Name )
Select Convert(datetime,Substring(dir_output,1,8)
+ ' '
+ (Substring(dir_output,11,5)
+ Case When Substring(dir_output,16,1) = 'a' Then ' AM' Else ' PM' End)) As Create_Time
, Convert(Int,LTrim(RTrim(Replace(Substring(dir_outp ut,17,22),',','')))) As File_Size
, Substring(dir_output,40,(Len(dir_output)-39)) As File_Name
From Ledger_Folder
Where Substring(dir_output,1,1) <> ' '
And (Substring(dir_output,1,1) <> ' '
And Substring(dir_output,25,5) <> '<DIR>')

SELECT @.Result_Count = @.@.ROWCOUNT, @.error_out = @.@.error

If @.Error_Out <> 0
BEGIN
Select @.Error_Loc = 7
Select @.Error_Type = 50001
GOTO Load_Ledger_Init_sp_Error
END

-- Get File For the 1st Month of a Quarter

If ( Select Count(*)
From Ledger_Folder_Parsed
Where Substring(File_Name,16,2)= @.Month1 And Substring(File_Name,11,4)= @.Proof_Year

) = 0
BEGIN
SELECT @.Error_Loc = 8
SELECT @.Error_Message = 'First Monthly File Not Found. Check Syntax for File Name. '
+ ' Syntax is: ' + @.Fn1 + '_yymmdd.txt'
SELECT @.Error_Type = 50002
GOTO Load_Ledger_Init_sp_Error
END|||Hey Bratt,

Thanks a Lot and ofcourse, I may not be using the sproc for checking the file existence. Since, we have been trying to upload the file to a MAINFRAME BASED FTP SITE.

So it would be more welcome, if you provide some solution or step based on the DOS mode or command line statements.

Thanks in Advance.

Baski.|||You can also use the ActiveX Script task in dts to test for file existence using the fso object - this would be cleaner. Why don't you use the ftp task in dts rather than using a batch file ?|||The Problem is you may not be using the FTP task for upload or download function in Mainframe based FTP site. SO i have to use the DOS version of file existence as BAT file in Execute process task.

Thanks