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.

Executing an MS SQL stored procedure from a java servlet

I'm trying to use a servlet to process a form, then send that data to
an SQL server stored procedure. I'm using the WebLogic 8 App. server.
I am able to retrieve database information, so I know my application
server can talk to the database.
I've determined the failure occurs when the the following statement is
executed: cstmt.execute(); (due to the failure of println statements
placed afterwards). I get the following error after trying to execute
the stored procedure call:
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored
procedure 'insertTheForm'

The username and password i'm using to connect is a Windows user with
admin rights. It is also associated with the Odbc connection--and of
course is a database user..with full rights. I have executable
permissions on the stored procedure set up as well. I did a microsoft
recommended registry fix as well (for a previous
error:http://support.microsoft.com/defaul...;en-us;Q238971).
Am I missing something? I posted my servlet code below.

Thanks for any help!
Dinesh

formHandlingServlet.class

--------
package showme;
/*
* formHandlingServlet.java
*
* Created on July 6, 2003, 7:01 PM
*/
import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import java.sql.*;
import java.text.DateFormat;

/**
*
* @.author Administrator
*/
public class formHandlingServlet extends HttpServlet {

private static final String email1 = "email";
private static final String password1 = "password1";
private static final String password2 = "password2";
private static final String displayname = "displayname";

Connection dbConn = null;

// create a persistent conneciton to the SQL server

public void init() throws ServletException
{
String jdbcDriver = "sun.jdbc.odbc.JdbcOdbcDriver";
String dbURL = "jdbc:odbc:Con2";
String usernameDbConn = "dinesh";
String passwordDbConn = "werty6969";

try
{
Class.forName(jdbcDriver).newInstance();
dbConn = DriverManager.getConnection(dbURL, usernameDbConn,
passwordDbConn);
}
catch (ClassNotFoundException e)
{
throw new UnavailableException("jdbc driver not found:" + dbURL);
}
catch (SQLException e)
{
throw new UnavailableException("error: " + e);
}
catch (Exception e)
{
throw new UnavailableException("error: " +e);
}
}

public void doPost(HttpServletRequest request, HttpServletResponse
response) throws ServletException, IOException
{
response.setContentType("text/plain");
PrintWriter out = response.getWriter();

//extract parameter information from register.jsp

String email1 = request.getParameter("email1");
String password1 = request.getParameter("password1");
String password2 = request.getParameter("password2");
String displayname = request.getParameter("displayname");

try
{
//make a callable statement for a stored procedure.
//It has four parameters

CallableStatement cstmt = dbConn.prepareCall(
"{call insertTheForm(?, ?, ?, ?)}");

//set the values of the stored procedure's input parameters

out.println("calling stored procedure . . .");
cstmt.setString(1, email1);
cstmt.setString(2, password1);
cstmt.setString(3, password2);
cstmt.setString(4, displayname);
//now that the input parameters are set, we can proceed to execute the
insertTheForm stored procedure

cstmt.execute();
out.println("stored procedure executed");
}

catch (SQLException e)
{
throw new UnavailableException("error: " + e);

}
}

}
dinesh wrote:

> Hi Joseph its nice to get a reply from a BEA employee..I will check out the
> bea groups. Well, yes I am able to execute the query from the MS query
> analyzer. I am also able to perform a table read from a servlet, I run into
> problems when trying to insert data. I tried to use the ms jdbc and
> implement it as instructed by bea edocs. here is my error:
> formHandlingServlet.java [79:1] cannot resolve symbol
> symbol : variable conn
> location: class showme.formHandlingServlet
> CallableStatement cstmt = conn.prepareCall(

The source need some work. You define a connection object in a try block.
That's the full scope of the object (ie: no one sees it outside the try block).
Don't create a connection in init(). Just do it in the post() right before you're
going to use it, and close it in a finally block:

Connect ion conn = null; // outside try block

try {
...
conn = d.connect(...);
... do all jdbc ...
} catch (Exception e) {
...
} finally {
try { conn.close();} catch (Exception (ignore){}
}

Joe Weinstrein

> ^
> source
> ---
> package showme;
> /*
> * formHandlingServlet.java
> *
> * Created on July 6, 2003, 7:01 PM
> */
> import javax.servlet.*;
> import javax.servlet.http.*;
> import java.io.*;
> import java.sql.*;
> import java.text.DateFormat;
> import java.util.*;
> /**
> *
> * @.author Administrator
> */
> public class formHandlingServlet extends HttpServlet {
> private static final String email1 = "email";
> private static final String password1 = "password1";
> private static final String password2 = "password2";
> private static final String displayname = "displayname";
> // create a persistent conneciton to the SQL server
> public void init() throws ServletException
> {
> Properties props = new Properties();
> props.put("user", "dinesh");
> props.put("password", "xyxyxyxy6969");
> props.put("db", "users");
> props.put("server", "COMPAQSERVER");
> try
> {
> Driver myDriver = (java.sql.Driver)Class.forName
> ("weblogic.jdbc.mssqlserver4.Driver").newInstance();
> Connection conn = myDriver.connect("jdbc:weblogic:mssqlserver4",
> props);
> }
> catch (ClassNotFoundException e)
> {
> //throw new UnavailableException("jdbc driver not found:" +
> dbURL);
> }
> catch (SQLException e)
> {
> throw new UnavailableException("error: " + e);
> }
> catch (Exception e)
> {
> throw new UnavailableException("error: " +e);
> }
> }
> public void doPost(HttpServletRequest request, HttpServletResponse
> response) throws ServletException, IOException
> {
> response.setContentType("text/plain");
> PrintWriter out = response.getWriter();
> //extract parameter information from register.jsp
> String email1 = request.getParameter("email1");
> String password1 = request.getParameter("password1");
> String password2 = request.getParameter("password2");
> String displayname = request.getParameter("displayname");
> try
> {
> //make a callable statement for a stored procedure.
> //It has four parameters
> CallableStatement cstmt = conn.prepareCall(
> "{call dbo.insertTheForm(?, ?, ?, ?)}");
> //set the values of the stored procedure's input parameters
> out.println("calling stored procedure . . .");
> cstmt.setString(1, email1);
> cstmt.setString(2, password1);
> cstmt.setString(3, password2);
> cstmt.setString(4, displayname);
> //now that the input parameters are set, we can proceed to
> execute the insertTheForm stored procedure
> cstmt.execute();
> out.println("stored procedure executed");
> out.close();
> }
> catch (SQLException e)
> {
> throw new UnavailableException("error: " + e);
> }
> }
> }
> "Joseph Weinstein" <joe.remove_this@.bea.com.remove_this> wrote in message
> news:3F0A582A.DE7760DD@.bea.com.remove_this...
> > dinesh prasad wrote:
> > > I'm trying to use a servlet to process a form, then send that data to
> > > an SQL server stored procedure. I'm using the WebLogic 8 App. server.
> > > I am able to retrieve database information, so I know my application
> > > server can talk to the database.
> > Hi! Two or three things:
> > 1 - We don't support the use of the jdbc-odbc bridge because it's flakey
> and
> > not threadsafe. You should download and use MS's own type-4 jdbc driver.
> > 2 - Can you execute this stored procedure from a commandline MS DBMS
> client
> > when you log in with the same user? I ask this, because this user's
> default database
> > context might not be in the database where the procedure is.
> > 3 - You can get quick weblogic-specific help in our support newsgroups,
> which
> > you can find under the support page at www.bea.com.
> > Joe Weinstein at BEA
> > > > I've determined the failure occurs when the the following statement is
> > > executed: cstmt.execute(); (due to the failure of println statements
> > > placed afterwards). I get the following error after trying to execute
> > > the stored procedure call:
> > > [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored
> > > procedure 'insertTheForm'
> > > > The username and password i'm using to connect is a Windows user with
> > > admin rights. It is also associated with the Odbc connection--and of
> > > course is a database user..with full rights. I have executable
> > > permissions on the stored procedure set up as well. I did a microsoft
> > > recommended registry fix as well (for a previous
> > > error:http://support.microsoft.com/defaul...;en-us;Q238971).
> > > Am I missing something? I posted my servlet code below.
> > > > Thanks for any help!
> > > Dinesh
> > > > formHandlingServlet.class
> > > > --------
> > > package showme;
> > > /*
> > > * formHandlingServlet.java
> > > *
> > > * Created on July 6, 2003, 7:01 PM
> > > */
> > > import javax.servlet.*;
> > > import javax.servlet.http.*;
> > > import java.io.*;
> > > import java.sql.*;
> > > import java.text.DateFormat;
> > > > /**
> > > *
> > > * @.author Administrator
> > > */
> > > public class formHandlingServlet extends HttpServlet {
> > > > private static final String email1 = "email";
> > > private static final String password1 = "password1";
> > > private static final String password2 = "password2";
> > > private static final String displayname = "displayname";
> > > > Connection dbConn = null;
> > > > // create a persistent conneciton to the SQL server
> > > > public void init() throws ServletException
> > > {
> > > String jdbcDriver = "sun.jdbc.odbc.JdbcOdbcDriver";
> > > String dbURL = "jdbc:odbc:Con2";
> > > String usernameDbConn = "dinesh";
> > > String passwordDbConn = "werty6969";
> > > > try
> > > {
> > > Class.forName(jdbcDriver).newInstance();
> > > dbConn = DriverManager.getConnection(dbURL, usernameDbConn,
> > > passwordDbConn);
> > > }
> > > catch (ClassNotFoundException e)
> > > {
> > > throw new UnavailableException("jdbc driver not found:" + dbURL);
> > > }
> > > catch (SQLException e)
> > > {
> > > throw new UnavailableException("error: " + e);
> > > }
> > > catch (Exception e)
> > > {
> > > throw new UnavailableException("error: " +e);
> > > }
> > > }
> > > > public void doPost(HttpServletRequest request, HttpServletResponse
> > > response) throws ServletException, IOException
> > > {
> > > response.setContentType("text/plain");
> > > PrintWriter out = response.getWriter();
> > > > //extract parameter information from register.jsp
> > > > String email1 = request.getParameter("email1");
> > > String password1 = request.getParameter("password1");
> > > String password2 = request.getParameter("password2");
> > > String displayname = request.getParameter("displayname");
> > > > try
> > > {
> > > //make a callable statement for a stored procedure.
> > > //It has four parameters
> > > > CallableStatement cstmt = dbConn.prepareCall(
> > > "{call insertTheForm(?, ?, ?, ?)}");
> > > > //set the values of the stored procedure's input parameters
> > > > out.println("calling stored procedure . . .");
> > > cstmt.setString(1, email1);
> > > cstmt.setString(2, password1);
> > > cstmt.setString(3, password2);
> > > cstmt.setString(4, displayname);
> > > //now that the input parameters are set, we can proceed to execute the
> > > insertTheForm stored procedure
> > > > cstmt.execute();
> > > out.println("stored procedure executed");
> > > }
> > > > catch (SQLException e)
> > > {
> > > throw new UnavailableException("error: " + e);
> > > > }
> > > }
> > > > }|||ok, great I have it working now, thanks Joe!!

Dinesh

"Joseph Weinstein" <joe.remove_this@.bea.com.remove_this> wrote in message
news:3F0AD70E.8876F92@.bea.com.remove_this...
>
> dinesh wrote:
> > Hi Joseph its nice to get a reply from a BEA employee..I will check out
the
> > bea groups. Well, yes I am able to execute the query from the MS query
> > analyzer. I am also able to perform a table read from a servlet, I run
into
> > problems when trying to insert data. I tried to use the ms jdbc and
> > implement it as instructed by bea edocs. here is my error:
> > formHandlingServlet.java [79:1] cannot resolve symbol
> > symbol : variable conn
> > location: class showme.formHandlingServlet
> > CallableStatement cstmt = conn.prepareCall(
> The source need some work. You define a connection object in a try block.
> That's the full scope of the object (ie: no one sees it outside the try
block).
> Don't create a connection in init(). Just do it in the post() right before
you're
> going to use it, and close it in a finally block:
> Connect ion conn = null; // outside try block
> try {
> ...
> conn = d.connect(...);
> ... do all jdbc ...
> } catch (Exception e) {
> ...
> } finally {
> try { conn.close();} catch (Exception (ignore){}
> }
> Joe Weinstrein
> > ^
> > source
> > ---
> > package showme;
> > /*
> > * formHandlingServlet.java
> > *
> > * Created on July 6, 2003, 7:01 PM
> > */
> > import javax.servlet.*;
> > import javax.servlet.http.*;
> > import java.io.*;
> > import java.sql.*;
> > import java.text.DateFormat;
> > import java.util.*;
> > /**
> > *
> > * @.author Administrator
> > */
> > public class formHandlingServlet extends HttpServlet {
> > private static final String email1 = "email";
> > private static final String password1 = "password1";
> > private static final String password2 = "password2";
> > private static final String displayname = "displayname";
> > // create a persistent conneciton to the SQL server
> > public void init() throws ServletException
> > {
> > Properties props = new Properties();
> > props.put("user", "dinesh");
> > props.put("password", "xyxyxyxy6969");
> > props.put("db", "users");
> > props.put("server", "COMPAQSERVER");
> > try
> > {
> > Driver myDriver = (java.sql.Driver)Class.forName
> > ("weblogic.jdbc.mssqlserver4.Driver").newInstance();
> > Connection conn =
myDriver.connect("jdbc:weblogic:mssqlserver4",
> > props);
> > }
> > catch (ClassNotFoundException e)
> > {
> > //throw new UnavailableException("jdbc driver not
found:" +
> > dbURL);
> > }
> > catch (SQLException e)
> > {
> > throw new UnavailableException("error: " + e);
> > }
> > catch (Exception e)
> > {
> > throw new UnavailableException("error: " +e);
> > }
> > }
> > public void doPost(HttpServletRequest request, HttpServletResponse
> > response) throws ServletException, IOException
> > {
> > response.setContentType("text/plain");
> > PrintWriter out = response.getWriter();
> > //extract parameter information from register.jsp
> > String email1 = request.getParameter("email1");
> > String password1 = request.getParameter("password1");
> > String password2 = request.getParameter("password2");
> > String displayname = request.getParameter("displayname");
> > try
> > {
> > //make a callable statement for a stored procedure.
> > //It has four parameters
> > CallableStatement cstmt = conn.prepareCall(
> > "{call dbo.insertTheForm(?, ?, ?, ?)}");
> > //set the values of the stored procedure's input parameters
> > out.println("calling stored procedure . . .");
> > cstmt.setString(1, email1);
> > cstmt.setString(2, password1);
> > cstmt.setString(3, password2);
> > cstmt.setString(4, displayname);
> > //now that the input parameters are set, we can proceed to
> > execute the insertTheForm stored procedure
> > cstmt.execute();
> > out.println("stored procedure executed");
> > out.close();
> > }
> > catch (SQLException e)
> > {
> > throw new UnavailableException("error: " + e);
> > }
> > }
> > }
> > "Joseph Weinstein" <joe.remove_this@.bea.com.remove_this> wrote in
message
> > news:3F0A582A.DE7760DD@.bea.com.remove_this...
> > > > > dinesh prasad wrote:
> > > > > I'm trying to use a servlet to process a form, then send that data
to
> > > > an SQL server stored procedure. I'm using the WebLogic 8 App.
server.
> > > > I am able to retrieve database information, so I know my application
> > > > server can talk to the database.
> > > > Hi! Two or three things:
> > > 1 - We don't support the use of the jdbc-odbc bridge because it's
flakey
> > and
> > > not threadsafe. You should download and use MS's own type-4 jdbc
driver.
> > > 2 - Can you execute this stored procedure from a commandline MS DBMS
> > client
> > > when you log in with the same user? I ask this, because this user's
> > default database
> > > context might not be in the database where the procedure is.
> > > 3 - You can get quick weblogic-specific help in our support
newsgroups,
> > which
> > > you can find under the support page at www.bea.com.
> > > > Joe Weinstein at BEA
> > > > > > > I've determined the failure occurs when the the following statement
is
> > > > executed: cstmt.execute(); (due to the failure of println statements
> > > > placed afterwards). I get the following error after trying to
execute
> > > > the stored procedure call:
> > > > [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored
> > > > procedure 'insertTheForm'
> > > > > > The username and password i'm using to connect is a Windows user
with
> > > > admin rights. It is also associated with the Odbc connection--and of
> > > > course is a database user..with full rights. I have executable
> > > > permissions on the stored procedure set up as well. I did a
microsoft
> > > > recommended registry fix as well (for a previous
> > error:http://support.microsoft.com/defaul...;en-us;Q238971).
> > > > Am I missing something? I posted my servlet code below.
> > > > > > Thanks for any help!
> > > > Dinesh
> > > > > > formHandlingServlet.class
> > > > > > --------
> > > > package showme;
> > > > /*
> > > > * formHandlingServlet.java
> > > > *
> > > > * Created on July 6, 2003, 7:01 PM
> > > > */
> > > > import javax.servlet.*;
> > > > import javax.servlet.http.*;
> > > > import java.io.*;
> > > > import java.sql.*;
> > > > import java.text.DateFormat;
> > > > > > /**
> > > > *
> > > > * @.author Administrator
> > > > */
> > > > public class formHandlingServlet extends HttpServlet {
> > > > > > private static final String email1 = "email";
> > > > private static final String password1 = "password1";
> > > > private static final String password2 = "password2";
> > > > private static final String displayname = "displayname";
> > > > > > Connection dbConn = null;
> > > > > > // create a persistent conneciton to the SQL server
> > > > > > public void init() throws ServletException
> > > > {
> > > > String jdbcDriver = "sun.jdbc.odbc.JdbcOdbcDriver";
> > > > String dbURL = "jdbc:odbc:Con2";
> > > > String usernameDbConn = "dinesh";
> > > > String passwordDbConn = "werty6969";
> > > > > > try
> > > > {
> > > > Class.forName(jdbcDriver).newInstance();
> > > > dbConn = DriverManager.getConnection(dbURL, usernameDbConn,
> > > > passwordDbConn);
> > > > }
> > > > catch (ClassNotFoundException e)
> > > > {
> > > > throw new UnavailableException("jdbc driver not found:" + dbURL);
> > > > }
> > > > catch (SQLException e)
> > > > {
> > > > throw new UnavailableException("error: " + e);
> > > > }
> > > > catch (Exception e)
> > > > {
> > > > throw new UnavailableException("error: " +e);
> > > > }
> > > > }
> > > > > > public void doPost(HttpServletRequest request, HttpServletResponse
> > > > response) throws ServletException, IOException
> > > > {
> > > > response.setContentType("text/plain");
> > > > PrintWriter out = response.getWriter();
> > > > > > //extract parameter information from register.jsp
> > > > > > String email1 = request.getParameter("email1");
> > > > String password1 = request.getParameter("password1");
> > > > String password2 = request.getParameter("password2");
> > > > String displayname = request.getParameter("displayname");
> > > > > > try
> > > > {
> > > > //make a callable statement for a stored procedure.
> > > > //It has four parameters
> > > > > > CallableStatement cstmt = dbConn.prepareCall(
> > > > "{call insertTheForm(?, ?, ?, ?)}");
> > > > > > //set the values of the stored procedure's input parameters
> > > > > > out.println("calling stored procedure . . .");
> > > > cstmt.setString(1, email1);
> > > > cstmt.setString(2, password1);
> > > > cstmt.setString(3, password2);
> > > > cstmt.setString(4, displayname);
> > > > //now that the input parameters are set, we can proceed to execute
the
> > > > insertTheForm stored procedure
> > > > > > cstmt.execute();
> > > > out.println("stored procedure executed");
> > > > }
> > > > > > catch (SQLException e)
> > > > {
> > > > throw new UnavailableException("error: " + e);
> > > > > > }
> > > > }
> > > > > > }
>|||

Quote:

Originally Posted by dinesh prasad

I'm trying to use a servlet to process a form, then send that data to
an SQL server stored procedure. I'm using the WebLogic 8 App. server.
I am able to retrieve database information, so I know my application
server can talk to the database.
I've determined the failure occurs when the the following statement is
executed: cstmt.execute(); (due to the failure of println statements
placed afterwards). I get the following error after trying to execute
the stored procedure call:
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored
procedure 'insertTheForm'

The username and password i'm using to connect is a Windows user with
admin rights. It is also associated with the Odbc connection--and of
course is a database user..with full rights. I have executable
permissions on the stored procedure set up as well. I did a microsoft
recommended registry fix as well (for a previous
error:http://support.microsoft.com/defaul...;en-us;Q238971).
Am I missing something? I posted my servlet code below.

Thanks for any help!
Dinesh

formHandlingServlet.class

--------
package showme;
/*
* formHandlingServlet.java
*
* Created on July 6, 2003, 7:01 PM
*/
import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import java.sql.*;
import java.text.DateFormat;

/**
*
* @.author Administrator
*/
public class formHandlingServlet extends HttpServlet {

private static final String email1 = "email";
private static final String password1 = "password1";
private static final String password2 = "password2";
private static final String displayname = "displayname";

Connection dbConn = null;

// create a persistent conneciton to the SQL server

public void init() throws ServletException
{
String jdbcDriver = "sun.jdbc.odbc.JdbcOdbcDriver";
String dbURL = "jdbc:odbc:Con2";
String usernameDbConn = "dinesh";
String passwordDbConn = "werty6969";

try
{
Class.forName(jdbcDriver).newInstance();
dbConn = DriverManager.getConnection(dbURL, usernameDbConn,
passwordDbConn);
}
catch (ClassNotFoundException e)
{
throw new UnavailableException("jdbc driver not found:" + dbURL);
}
catch (SQLException e)
{
throw new UnavailableException("error: " + e);
}
catch (Exception e)
{
throw new UnavailableException("error: " +e);
}
}

public void doPost(HttpServletRequest request, HttpServletResponse
response) throws ServletException, IOException
{
response.setContentType("text/plain");
PrintWriter out = response.getWriter();

//extract parameter information from register.jsp

String email1 = request.getParameter("email1");
String password1 = request.getParameter("password1");
String password2 = request.getParameter("password2");
String displayname = request.getParameter("displayname");

try
{
//make a callable statement for a stored procedure.
//It has four parameters

CallableStatement cstmt = dbConn.prepareCall(
"{call insertTheForm(?, ?, ?, ?)}");

//set the values of the stored procedure's input parameters

out.println("calling stored procedure . . .");
cstmt.setString(1, email1);
cstmt.setString(2, password1);
cstmt.setString(3, password2);
cstmt.setString(4, displayname);
//now that the input parameters are set, we can proceed to execute the
insertTheForm stored procedure

cstmt.execute();
out.println("stored procedure executed");
}

catch (SQLException e)
{
throw new UnavailableException("error: " + e);

}
}

}

Hi,
You cannot call MS SQL Server stored procedure using the "call" verb. You need to use "exec" verb. CallableStatement cstmt = dbConn.prepareCall(
"{exec insertTheForm(?, ?, ?, ?)}");

Hope it helps you.
Sanjeev.

Executing an external process() in SQLCLR Project

I can create an external text file from within the SQLCLR project, but I cannot run an external executable. Just in case you are asking, I need to do this to push data into a legacy application using a different DB format. I have found it best to simply use my old language (Clipper) for data validation, etc. - and especially since I require multiple indices to be open. So, if you could just take my word on this.

The following code:

Process newProcess = new Process();

string path = @."C:\TEST.BAT";

newProcess.StartInfo.FileName = path;

newProcess.Start();

Executes without error, but does not actually run the external.

Now, I can have a DOS (Clipper) application poll a directory for text files, but I am trying to get away from all these "mini" data transformation applications. If an exception is caused in the DOS app, a remote user on the other side of the country has no idea it is broke and his data (coming from a SQL Mobile Device) never gets to our legacy database structure.

So, am I out of luck?

Is your assembly deployed at the UNSAFE permission level? What happens if you try to run the same code in a console app run under the same user account (SQL Server service account or impersonated account, as appropriate)?|||

Hi Nicole - thx for responding.

I am able to execute a console app - the issue is that SQL Server will not allow touching any network drive or network resource period (from a SQLCLR Project). So my cmd app can update a DB on the C drive, but not one of my network drives.

As well, I can establish an OLEDB connection (Visual Foxpro) to a local directory, but not a network directory. I'm surprised that CLR will not even allow an external app to touch a network resource. Wild, eh? Although I understand why there is such security, there must be a way for me to execute an external console app that can update a DB on a network resource.

Oh - and yes, I am running the assembly at the UNSAFE permission level.

|||Might the problem be the user context rather than anything having to do with SQLCLR? Unless you deliberately impersonate another user (e.g.: via SqlContext.WindowsIdentity.Impersonate), your SQLCLR code will run under the user context of the SQL Server service account, which is highly unlikely to have any permissions on any network resources.|||

I think you're correct - it has to do with attempting to authenticate a local user (IUSR_Computername) on a network resource. I will look into impersonation (something my wife says I'm horrible at...)

You know, Nicole, you are the first person to assist me in these forums. I had actually thought of using a female handle - seems they get a pretty quick response... :)

I'll post the results of my efforts... and thx again.

|||

After impersonation, I can do something simple like use a streamwriter to create a text file on a network share. But it still throws an exception when attempting to execute an executable on the same network share. The impersonation rights are those of administrator (only for testing!). Oh, in case you're wondering - I simply change the Process StartInfo from @."C:\Mobile.exe" to @."F:\Mobile.exe", and you will have to take my word for it that the Mobile exists in both places. It runs without exception on the C drive.

What's really wild, is that the actual Mobile.exe code (clipper) will update a dbf on the C drive, but not on the F drive. What does the CLR do? Freeze all resources when in process? geez!

I'm also using:

[PermissionSetAttribute(SecurityAction.Demand, Name = "FullTrust")]

So... I really thought this would allow my CLR code to access a network share. I can't understand why the answer to this wouldn't be just a little more simple, even for an old programmer like me.

Execute SQL from File: How Can I process the record set?

I want to be able to pass the location of a file (contains SQL to be executed) to my package at run time. To do this I was going to override the connection string for the file.

I've created a 'Execute SQL Task' that opens the sql script and stores the full result set into an variable (system.object). I can execute this and it works fine i.e turns green :).

However I can't work out how to get the data back out of the variable. I have found a doc on SQLIS (The ExecuteSQL Task) that explained how to get the data in to a variable but didn't tell me how to process the data afterwards. There is another article on there that shows how to shred a recordset (Shredding a Recordset) but this example uses an OLE DB source and the 'Recordset Destination' object. This would work but but the only options are sql from a variable or the option to type in the command.

I really have two questions here.

1. Using the first method how can I pass the data stored in the variable into a data flow so that I can use it.

2. using the second method, how can I pass the SQL into a variable at runtime from a file?

Has anyone got examples of how they read SQL from a file and process the data without having to hard code the sql or sql file name in the package.

#1) If you have data in an object and want to use it in a data-flow then you're going to need to loop over the records in the object and add them to the pipeline. Its custom source adapter time!!!

Here's how you do it in a script task: http://blogs.conchango.com/jamiethomson/archive/2005/02/08/960.aspx I dare say you can take this code and adapt it to use it in a script component. I have to say, I haven't actually tried it.

#2) Again you may need a custom/script task to do this. I don't have time to look at this now but I'll try later. It shouldn't be too difficult, just use System.IO namespace.

-Jamie|||To configure the file connection at runtime. You need to use expressions. On the properties of the data flow task select expressions. In here you can set the connection string property of the flat file to the variable (containing a filename).

Execute Process Task; How to use user variable as an argument

I am trying to call a executable that takes an argument. I am using an "execute process task" and have declared a user string variable "file_name" (c:\file.txt)

How do I use this variable so that the executable will see it as an argument.

You can use expressions to define the path and parameters of you executable... Then you don't pass the variable but construct the "command line" with an expression including the parameter (which is the value of your variable)...

Execute Process task: Unexpected exit code

In Executing "E:\EmailDelivery.exe" "EP12A 4" at "", The process exit code was "-532459699" while the expected was "0".
It is your process, so why do you ask this forum why it returned this exit code? The Execute Process task just compares the the process exit code with expected that you set in task properties, and reports an error if the exit code does not match.|||Thanks Michael. Turns out it had to do with the package being executed via a windows service under the local system account and not having the correct credentials for the db connection.

Execute Process Task using StandardInputVariable

Greetings!

I am using a Excute Process Task calling a selfwritten EXE and I'm trying to pass a command line argument to it. The code for the EXE is quit simple:

Public Sub Main()
Try
Dim info As String = My.Application.CommandLineArgs.Item(0)
MessageBox.Show("Info:" & info)
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
End Sub

According to the help files there are two ways to pass an argument, using the property Arguments, which I guess is for hard-coded stuff, and the StandardInputVariable for passing dynamic info.

When I use the Arguments property everything works fine. I get the info messagebox with correct data. But when I leave that blank and instead use the StandardInputVariable property setting it to my package variable User::Info then I crash into an exception meaning that no arguments existed (array is out of bonds).

The variable User::Info was at first filled from a previous SSIS task and using the OnPreExecute breakpoint I verified that it contained a stringvalue. I then hardcoded a string value into the variable, but nothing helps. The task refuses to start my EXE with the data in the StandardInputVariable as an argument.

Why is this not working?

When you use the StandardInputVariable property setting, are you declaring the StandardInputVariable property in the code for the EXE?|||

I am not sure what you mean by this. The StandardInputVariable is just a property in the SSIS task Execute Process. It's supposed to just pass the content of the selected variable as a start-up argument to the selected EXE. Is there some kind of a global variable inside my VS2005 VB project that automatically gets some info that is not passed as an argument? Or can I declare something like that?

All my EXE file should need to do is look at the arguments (the first only in this case) passed to it like this:

Dim arg as string = My.Application.CommandLineArgs.Item(0)

If i use the Argument propert it works fine. But that is for hard coded start-up values like standard /H for help or /Q for quiet. I need to pass a dynamic value and that's what StandardInputVariable is for. I have verified that my SSIS variable contains the correct data. Yet no argument is delivered to the EXE file.

|||

Use an expression on the Arguments property for passing both static and dynamic command line arguments to an execute process task.

By using an expression, you can incorporate any combination. For example, the expression on Arguments could be: "/Q" + @.User::FilePath

The StandardInputVariable is a task property pointing to a variable who's contents will be streamed in on the process' standard input file handle. If you log the Execute Process specific event entitled "ExecuteProcessVariableRouting", and make use of the StandardInputVariable task property, note that that the log message says, "Routing stdin from variable "User:<your variable here>".

|||

It turns out that you need to include a call to Console.Readline, as follows:

Dim info As String = Console.Readline

And, of course, make sure that the StandardInputVariable property is set to the package variable containing the value you want to pass. I also left the Auguments property setting blank when I tested this procedure.

Carla

|||

Thank you very much. This solved the problem.

I never thought of using Console since my application is a Windows Forms executable, but it worked like a charm.

|||

Please!

Is it possible to link two o more system variables (StartTime + UserName) in a user variable?

I tried Name=User::MyVar and Value=System::StartTime + "Test" but when I try to read by using your

Dim Info As String = Console.ReadLine

it give me the string "System::StartTime + "Test""

Thanks in advance.

Alex.|||

Not sure if I completely understand your question, but it sounds like you need to use an expression for your variable. You can do this by setting the EvaluateAsExpression property of the variable to TRUE, and then enter your expression (i.e., User::MyVar + User::MyVar2) into the Expression property.

You may need to cast the variables to concatenate them. The expression builder will help you validate that the expression is correct.

Execute Process Task using StandardInputVariable

Greetings!

I am using a Excute Process Task calling a selfwritten EXE and I'm trying to pass a command line argument to it. The code for the EXE is quit simple:

Public Sub Main()
Try
Dim info As String = My.Application.CommandLineArgs.Item(0)
MessageBox.Show("Info:" & info)
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
End Sub

According to the help files there are two ways to pass an argument, using the property Arguments, which I guess is for hard-coded stuff, and the StandardInputVariable for passing dynamic info.

When I use the Arguments property everything works fine. I get the info messagebox with correct data. But when I leave that blank and instead use the StandardInputVariable property setting it to my package variable User::Info then I crash into an exception meaning that no arguments existed (array is out of bonds).

The variable User::Info was at first filled from a previous SSIS task and using the OnPreExecute breakpoint I verified that it contained a stringvalue. I then hardcoded a string value into the variable, but nothing helps. The task refuses to start my EXE with the data in the StandardInputVariable as an argument.

Why is this not working?

When you use the StandardInputVariable property setting, are you declaring the StandardInputVariable property in the code for the EXE?|||

I am not sure what you mean by this. The StandardInputVariable is just a property in the SSIS task Execute Process. It's supposed to just pass the content of the selected variable as a start-up argument to the selected EXE. Is there some kind of a global variable inside my VS2005 VB project that automatically gets some info that is not passed as an argument? Or can I declare something like that?

All my EXE file should need to do is look at the arguments (the first only in this case) passed to it like this:

Dim arg as string = My.Application.CommandLineArgs.Item(0)

If i use the Argument propert it works fine. But that is for hard coded start-up values like standard /H for help or /Q for quiet. I need to pass a dynamic value and that's what StandardInputVariable is for. I have verified that my SSIS variable contains the correct data. Yet no argument is delivered to the EXE file.

|||

Use an expression on the Arguments property for passing both static and dynamic command line arguments to an execute process task.

By using an expression, you can incorporate any combination. For example, the expression on Arguments could be: "/Q" + @.User::FilePath

The StandardInputVariable is a task property pointing to a variable who's contents will be streamed in on the process' standard input file handle. If you log the Execute Process specific event entitled "ExecuteProcessVariableRouting", and make use of the StandardInputVariable task property, note that that the log message says, "Routing stdin from variable "User:<your variable here>".

|||

It turns out that you need to include a call to Console.Readline, as follows:

Dim info As String = Console.Readline

And, of course, make sure that the StandardInputVariable property is set to the package variable containing the value you want to pass. I also left the Auguments property setting blank when I tested this procedure.

Carla

|||

Thank you very much. This solved the problem.

I never thought of using Console since my application is a Windows Forms executable, but it worked like a charm.

|||

Please!

Is it possible to link two o more system variables (StartTime + UserName) in a user variable?

I tried Name=User::MyVar and Value=System::StartTime + "Test" but when I try to read by using your

Dim Info As String = Console.ReadLine

it give me the string "System::StartTime + "Test""

Thanks in advance.

Alex.|||

Not sure if I completely understand your question, but it sounds like you need to use an expression for your variable. You can do this by setting the EvaluateAsExpression property of the variable to TRUE, and then enter your expression (i.e., User::MyVar + User::MyVar2) into the Expression property.

You may need to cast the variables to concatenate them. The expression builder will help you validate that the expression is correct.

Execute Process Task Error on copy

Hi,

I am trying to run a very simple copy command from an execute process task:

copy O:\myFolder\myFile*.txt D:\myFolder

I have a working directory set also.

However, there is an error icon on the task, and if I attempt to run it I get the following error:

Error at Execute Process Task [Execute Process Task]: File/Process "" does not exist in directory "copy O:\myFolder\myFile*.txt D:\myFolder".

Error at Execute Process Task: There were errors during task validation.

This command works fine from the command line. What am I doing wrong here?

Thanks

O:\myFolder\myFile*.txt D:\myFolder

The above should go in the arguments parameter box if you're not doing that.|||

It seems that it's looking for an executable, like a .bat file. It doesn't seem to recognize the dos copy command.

for example:

Executable: copy

Arguments: o:\myFolder\myFile.txt D:\myFolder

but it still complains that copy is not an executable.

So, it seems I have to put this in a batch file, which seems dumb, or I need to use the File System Task, but I don't know how to specify which files to copy using this method.

?

|||Instead, try using the full path to xcopy

c:\windows\xcopy.exe|||

xcopy is not installed

where can one find just the "copy" command exe?

|||

sadie519590 wrote:

xcopy is not installed

where can one find just the "copy" command exe?

xcopy is not installed? You're kidding me? It's distributed with Windows.

There is no "copy.exe" as it's an internal command within "command.exe or cmd.exe."|||

Actually you are correct. I was thinking of robocopy.

At any rate, SSIS is complaining it can't find the xcopy exe. Never mind at this point, I'm not going to use it for now.

But for future use, it seems to me that there should be a way to use the File System task to specify file names with wildcards?

|||

sadie519590 wrote:

Actually you are correct. I was thinking of robocopy.

At any rate, SSIS is complaining it can't find the xcopy exe. Never mind at this point, I'm not going to use it for now.

But for future use, it seems to me that there should be a way to use the File System task to specify file names with wildcards?

You need to specify the full path to xcopy.exe in the Executable parameter..|||But I'm still wondering if I can use wild cards to specify files names in the File System Task when deleting files? Seems like this would be easier approach if possible.|||

sadie519590 wrote:

But I'm still wondering if I can use wild cards to specify files names in the File System Task when deleting files? Seems like this would be easier approach if possible.

I think the way to do this is to use a foreach loop to scan the source directory for the files you want to copy (you can use a wildcard there) and then have it populate some variables (source filename, etc...). Then, inside the foreach loop, you use a filesystem task to perform the operation for each file found by the foreach loop.

Does that make sense?|||Yes, thanks. I'm surprised there's no built-in way to do this. Product request, I guess?|||

sadie519590 wrote:

Yes, thanks. I'm surprised there's no built-in way to do this. Product request, I guess?

Sure thing! http://connect.microsoft.com/sqlserver/feedback

Execute process task depending on query result

Hi Guys,

I wonder if you can help with the following requirement.

I want to be able to conditionally execute an 'execute process task' depending on the result of a query. I have a table which I will select one record/row from upon each execution, this record has a char 1 'type' field which is the indicator for what process to then execute.

This should be quite a simple package and will be run every 60 seconds so needs to be as efficient as possible.

I am thinking I should go along the lines of using an Execute SQL task to select my row in to a result set, and using a series of precedence expressions to determine what process to execute. But im not really sure how.....

I am a newbie to SSIS and 2005 in general so would appreciate any help you can provide

Chris

You can use your Execute SQL Task to store the column value to a variable. This post explains how to do so (it uses Excel as the data source, but the process is the same for a SQL source) http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=772416&SiteID=1.

Drag a precedence constaint from the Execute SQL to the Execute Process. Double-click the precedence constraint, change the Evaluation option to Expression and Constraint, set Value to success, and set the Expression to @.your_variable=="1" (replace 1 with the appropriate value for this task). Continue adding Execute Process tasks and precedence constraints, altering the expression as appropriate.

Hope this helps.

|||

Thanks for your help!

All works

Execute Process Task Arguments

HI,

Is it possible to provide variables ( multiple variables ) in the arguments parameters of the Execute Process Task?

Thanks

Shafiq

You can use multiple command arguements in one task by using spaces to delimit arguements.

Thanks,
Loonysan

|||To use variables in the process arguments, use Expressions tab and define an expression for Arguments property. You can use multiple variables there.

Execute Process Task arguments

We are attempting to use SSIS execute process task.

In a cmd.exe session the executable runs with normal arguments:

mycmd < myscript.txt > foo.txt 2>error.txt

We have attempted to several permutations of this without success. We declared variables and assigned them to stdin, stdout, stderr. However, mycmd opens in the SSIS session as if no arguments have been supplied.

Is there some expression that is required to connect the stdin/out/err variables in the Arguments within Execute Process Task. The help/examples are terse.

Redirections using < and > are not arguments, they are special symbols treated by cmd.exe.

To get the same behavior from Execute Process Task, you'll need to read content of myscript.txt into a variable and redirect program input to this variable; redirect output and errors to two other variables, then after program ends save these variables into foo.txt and error.txt.

But the easiest way to solve it is to use the 'cmd.exe' as the task executable, and supply your command (/C mycmd < myscript.txt > foo.txt 2>error.txt) as agruments for cmd.exe (/C added to instruct cmd.exe to executed specified command).|||

Thanks! We somehow missed the /C switch and that solved the problem.

Loading myscript.txt into a variable for use by stdin seems a bit problematic in our situation since it could be several hundred lines long and contain a wide spectrum of characters related to regular expressions that just seem like the perfect opportunity to tip it over.

We may look at the use of variables in the argument and see if we can move the scrum ahead.

|||Hello.

I'm trying to use the "Execute Process" task to compress several ASCII files in a folder in one ZIP archive. The command line for it looks as follows:

c:\programme\7zip\7z.exe a -bd U:\Projekte\TFG\Software\Log_storage\SCD_Demo_isHASH_34.zip SCD_Demo_isHASH_*.log

The result file has size of 2 KB.

When I put this command in the "Arguments" property of the "Execute Process" component (with "/C" switch and cmd.exe as executable), I get an empty ZIP file. What could be a reason for it? Has anyone already experienced such behaviour?

Regards,
Andrey

Execute Process Task / Console Output

I'm just starting to find my way around SSIS, coming from SQL 2000 DTS, but I can't see a way of including a Win32 Console application's output into the logging process.

I've started playing around with SSIS Logs (OnTaskFailed etc..), but I can find no where to allow me to capture the output from a console app. In SQL 2000 DTS I could capture this by specifying an "output file" on the advanced tab of the job step definition. Is there something as straight forward as this in SSIS ?

I'd be grateful for a few pointers in the right direction.

One way would be to pipe the console output to a text file using appropriate calling syntax (myconsoleapp.exe >> myoutput.txt) , then read it in via a flat file source in a following step. of course the console output might need to be structured or parsed in a way that meets your needs for the further processing of the data, but it's one way.

Ken

|||

Thanks for the suggestions Ken. I see where you are going with it, but it's not that I need to further process the output from console app's, just log what they are producing. In fact, they do already produce a flat file which mirrors the console output it's just that I'd like the whole output from the package to be in one place .

Under SQL 2000, the job steps manage to allow you to easily capture both the SQL server messages for tasks along with the output from an application. It seems that the logging in 2005 is much more complete and varied, but makes this simple task more complex to accomplish. I'm sure it can be done, I just don't know the best avenue for investigation. My last thought was to try using the StandardOutputVariable property on the execute process task to capture the data to a string. I haven't persued this as it seemed a bit of a long shot, but that's where I'm at.

|||Using StandardOutputVariable is not a long shot. Its a sure bet. Just use that variable, and in a subsequent Script task, call Dts.Events.FireInformation() using that variable as the message parameter.

Provided your logging options are set on the script task for the event type ("OnInformation") and for whatever log provider you choose, you can have the data in one place.|||

jaegd wrote:

Using StandardOutputVariable is not a long shot. Its a sure bet. Just use that variable, and in a subsequent Script task, call Dts.Events.FireInformation() using that variable as the message parameter.

Provided your logging options are set on the script task for the event type ("OnInformation") and for whatever log provider you choose, you can have the data in one place.

I just tried setting the StandardOutputVariable and StandardErrorVariable properties, and now the task pops up a window each time the process is executed (it's in a loop), even though I've also set the WindowStyle property to Hidden. Is that to be expected?

Execute Process Task / Console Output

I'm just starting to find my way around SSIS, coming from SQL 2000 DTS, but I can't see a way of including a Win32 Console application's output into the logging process.

I've started playing around with SSIS Logs (OnTaskFailed etc..), but I can find no where to allow me to capture the output from a console app. In SQL 2000 DTS I could capture this by specifying an "output file" on the advanced tab of the job step definition. Is there something as straight forward as this in SSIS ?

I'd be grateful for a few pointers in the right direction.

One way would be to pipe the console output to a text file using appropriate calling syntax (myconsoleapp.exe >> myoutput.txt) , then read it in via a flat file source in a following step. of course the console output might need to be structured or parsed in a way that meets your needs for the further processing of the data, but it's one way.

Ken

|||

Thanks for the suggestions Ken. I see where you are going with it, but it's not that I need to further process the output from console app's, just log what they are producing. In fact, they do already produce a flat file which mirrors the console output it's just that I'd like the whole output from the package to be in one place .

Under SQL 2000, the job steps manage to allow you to easily capture both the SQL server messages for tasks along with the output from an application. It seems that the logging in 2005 is much more complete and varied, but makes this simple task more complex to accomplish. I'm sure it can be done, I just don't know the best avenue for investigation. My last thought was to try using the StandardOutputVariable property on the execute process task to capture the data to a string. I haven't persued this as it seemed a bit of a long shot, but that's where I'm at.

|||Using StandardOutputVariable is not a long shot. Its a sure bet. Just use that variable, and in a subsequent Script task, call Dts.Events.FireInformation() using that variable as the message parameter.

Provided your logging options are set on the script task for the event type ("OnInformation") and for whatever log provider you choose, you can have the data in one place.|||

jaegd wrote:

Using StandardOutputVariable is not a long shot. Its a sure bet. Just use that variable, and in a subsequent Script task, call Dts.Events.FireInformation() using that variable as the message parameter.

Provided your logging options are set on the script task for the event type ("OnInformation") and for whatever log provider you choose, you can have the data in one place.

I just tried setting the StandardOutputVariable and StandardErrorVariable properties, and now the task pops up a window each time the process is executed (it's in a loop), even though I've also set the WindowStyle property to Hidden. Is that to be expected?

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

Execute process task

I want to pass in a text file to execute process task.Is it possible?

You can pass the location of the file to a process. For example, in the execute process task, you can set the following properties:

Executable: notepad.exe

Arguments: abc.txt

WorkingDirectory: C:\temp

When you execute the task, notepad will attempt to open abc.txt from C:\temp folder.

Execute Process Task

Hi,

We have an SSIS Execute Process Task which calls an executable along with the required parameters.

When we run this package, it intermittently gives the error as shown below in red

Executing "ppscmd.exe" "StagingDB /Server http://SERVERNAME:46787 /path OSB_FY08.Planning.dimensionECFuncArea /Operation LoadDataFromStaging" at "", The process exit code was "1" while the expected was "0". End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 5:05:08 PM Finished: 5:08:40 PM Elapsed: 212.203 seconds. The package execution failed. The step failed.

We are not able to debug this issue. We had a look at the logging information as well but we are not getting any information on this issue.

How can we resolve this issue ?

Any help on this would be highly appreciated.

Thanks & Regards

Joseph Samuel

You are getting the error simply because the process you are exeuting is returning some error code (non-zero). If you want to simply ignore the error, you can set the "ForceExecutionResult" property.|||

If you want to capture the error from PPSCMD, redirect the output to a file. See this post for details (you have to call it from cmd.exe):

http://blogs.msdn.com/michen/archive/2007/08/02/redirecting-output-of-execute-process-task.aspx

Execute Process Task

Hi,

We have an SSIS Execute Process Task which calls an executable along with the required parameters.

When we run this package, it intermittently gives the error as shown below in red

Executing "ppscmd.exe" "StagingDB /Server http://SERVERNAME:46787 /path OSB_FY08.Planning.dimensionECFuncArea /Operation LoadDataFromStaging" at "", The process exit code was "1" while the expected was "0". End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 5:05:08 PM Finished: 5:08:40 PM Elapsed: 212.203 seconds. The package execution failed. The step failed.

We are not able to debug this issue. We had a look at the logging information as well but we are not getting any information on this issue.

How can we resolve this issue ?

Any help on this would be highly appreciated.

Thanks & Regards

Joseph Samuel

You are getting the error simply because the process you are exeuting is returning some error code (non-zero). If you want to simply ignore the error, you can set the "ForceExecutionResult" property.|||

If you want to capture the error from PPSCMD, redirect the output to a file. See this post for details (you have to call it from cmd.exe):

http://blogs.msdn.com/michen/archive/2007/08/02/redirecting-output-of-execute-process-task.aspx

Execute privileges on sp

Hi. I'm trying to test something on a test db I have installed on my pc, but I am unable to process as I'm doing it. So, basically what I want is to give execute privilege on a procedure to a user, so the user can execute this procedure without having the privileges explicity granted on it (what this procedure do is to truncate a table on which the user has no access). As I've read, SQL Server stored procedures privileges runs with the definers permissions, not the one that is actually executing the procedure. So, what I'm doing is this: in query analyzer, logged in as sa, I did

use test

create table t ( a integer )

create procedure can_truncate as
truncate table t

sp_addlogin 'jmartinez',''

sp_grantdbaccess 'jmartinez','jmartinez'

grant execute on can_truncate to jmartinez

Then I went to connect again, as jmartinez and did:

exec can_truncate

and I get

Server: Msg 3704, Level 16, State 1, Procedure can_truncate, Line 2
User does not have permission to perform this operation on table 't'.

So, I wonder what more permissions would user jmartinez need in order to execute this procedure successfully. I hope you all understand what I am trying to achieve.

Thanks!Shake things up a bit, and try:use test
GO
sp_addlogin 'jmartinez',''

sp_grantdbaccess 'jmartinez','jmartinez'
GO
create table t ( a integer )
GO
create procedure can_truncate as
truncate table t
RETURN
GO

grant execute on can_truncate to jmartinez-PatP|||I get the same exact error message using your method. Btw, I am using MSDE, this is what I get when I do SELECT @.@.VERSION:

Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 2)|||Reading through BOL, I found out this:

Permissions
TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable.
I guess this mean I cannot transfer TRUNCATE TABLE privilege to a user, but since I am executing a procedure I created on my own ( as sa in this case ), woulnd't it have to run with my privileges instead of the user who is executing it ( in this case, jmartinez ) ?|||You need to run this code after granting rights:

sp_addrolemember 'db_ddladmin', 'jmartinez'|||But then I'm giving the user privileges to basically *destroy* my db..|||Hmm.. TRUNCATE TABLE is an operation that requires high priviledges as long as I know, so you will have to give a user them.

By the way, why does user need to perform this? IMHO, You may create something like and administration panel which will login to database on its own and perform TRUNCATE TABLE instead of user - e.g. he selects a table from the combo box and then presses SUBMIT button, and then script connects as someone special with appropriate role membership and runs TRUNCATE TABLE.|||You might use
Delete
From <TableName>|||You might use
Delete
From <TableName>

The question is about

what this procedure do is to truncate a table on which the user has no access

Execute external process from CLR based Stored procedure

Hi All,

I am trying to create a CLR based stored procedure in C#. When i tried printing simple "Hello" from it, it works fine.

Now requirement is to run an exe file from it. For that i use process.start. But when i try to execute the procedure i get all the security execptions. Can someone please help. Following is the code snippet.

-

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void RunProc(string arg)

{

SqlPipe pipe = SqlContext.Pipe;

pipe.Send("Hello");

Process.Start("E:\test.exe");

}

}

CREATE ASSEMBLY [RunProcess]

FROM 'RunProcess.dll'

CREATE PROCEDURE dbo.sqlclr_RunProc

(

@.arg nvarchar(1024)

)

AS EXTERNAL NAME [RunProcess].[StoredProcedures].[RunProc]

--

Thanks

Sid

Also the Error that i get is

Msg 6522, Level 16, State 1, Procedure sqlclr_RunProc, Line 0

A .NET Framework error occurred during execution of user defined routine or aggregate 'sqlclr_RunProc':

System.Security.SecurityException: Request failed.

System.Security.SecurityException:

at System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Assembly asm, PermissionSet granted, PermissionSet refused, RuntimeMethodHandle rmh, SecurityAction action, Object demand, IPermission permThatFailed)

at System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Object assemblyOrString, PermissionSet granted, PermissionSet refused, RuntimeMethodHandle rmh, SecurityAction action, Object demand, IPermission permThatFailed)

at System.Security.CodeAccessSecurityEngine.CheckSetHelper(PermissionSet grants, PermissionSet refused, PermissionSet demands, RuntimeMethodHandle rmh, Object assemblyOrString, SecurityAction action, Boolean throwException)

at System.Security.CodeAccessSecurityEngine.CheckSetHelper(CompressedStack cs, PermissionSet grants, PermissionSet refused, PermissionSet demands, RuntimeMethodHandle rmh, Assembly asm, SecurityAction action)

at StoredProcedures.RunProc(String arg)

|||hi

i have done my sp to write eventlog in .txt i too faced some problems try these queries

use master
go
exec sp_dbcmptlevel 'databasename', 90
go

to know more about it see

http://msdn2.microsoft.com/en-us/library/ms178653.aspx

then make the sql server databae trustworthy

ALTER DATABASE databasename SET TRUSTWORTHY ON

try this and let me now|||Your assembly the you deploy to the the database has to have the unsafe permission set:

Code Snippet

CREATE ASSEMBLY some_name
FROM 'some_path'
WITH PERMISSION_SET = UNSAFE

However, in order to create an unsafe assembly some other permissions need to be in place. This can be done in two ways:
1. by using certificates
2. by setting the database to be trustworthy and to grant UNSAFE ASSEMBLY to the login of the owber of the database.

The second way is the easiest, but I would not recommend it for production. Assuming the database is created by dbo and the dbo is also admin on the box, you do something like this:

Code Snippet

use master;
go

GRANT UNSAFE ASSEMBLY to [Builtin\Administrators];
go

ALTER database your_db_name
set trustworthy on;
go

Oh, and notice that when you then finally execute your proc and your external process runs, it will run under the SQL Server Service account.

Hope this helps.

Niels

|||

Thanks Neils, That helps.

|||Hi

altering database to trustworthy only for executing sql clr is very easy but not advicable method too
see my below post u will get some more option to do this without enabling trustworthy for the database

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2027982&SiteID=1

Thanks