EXECUTE AS question: app

Showing posts with label app. Show all posts

Thursday, March 29, 2012

EXECuting sps within sps (using return params)

Hi all,
To optimise certain functionality in my app I want to do a few inserts after another by executing the whole lot in one procedure. I want to use the return param from some procedures (RETURN @.@.IDENTITY) as input for some of the other procedures.

I am getting errors when I compile the proc:
Line 10: Incorrect syntax near the keyword "EXEC"
Same error on Line 11...


CREATE PROCEDURE addTemplateDetail
@.TemplateID int,
@.GroupNameID int,
@.SubGroupNameID int=null,
@.MethodID int,
@.AnalyteID int
AS
DECLARE @.TemplateGroupNameID int
DECLARE @.TemplateMethodID int
SET @.TemplateGroupNameID=(EXEC addTemplateGroupName @.TemplateID, @.GroupNameID)
SET @.TemplateMethodID=(EXEC addTemplateMethod @.TemplateGroupNameID, @.SubGroupNameID, @.MethodID)
EXEC addTemplateAnalyte(@.TemplateMethodID,@.AnalyteID)

I also tried adding brackets around the input params for the EXECed sp's, but that generated even more errors...
Can somebody see what I am doing wrong?
TIA.Never mind.
I changed the two lines with return params to:


EXEC @.TemplateGroupNameID=addTemplateGroupName @.TemplateID, @.GroupNameID
EXEC@.TemplateMethodID=addTemplateMethod @.TemplateGroupNameID, @.SubGroupNameID, @.MethodID

Hope that will somebody else too...sql

Tuesday, March 27, 2012

Executing large result queries

Hi all,
I've developed an app (C#) that connects to SQL Server 2000 without using
thread pooling (using SqlConnection) and I run a query that returns ~4000
tuples each time it runs and displays the results in a listview. The problem
is that after I run it 3-4 times the results are read very slowly from the
DB. The first times the results are displayed in 1-2 secs but the 4,5 time
it takes more than 2 minutes! What am I doing wrong? Are there any
in-between buffers that need to be emptied?
Thanks,
-peterHi Peter,
If you could show us some code we might be able to make a better
diagnosis.
This is a windows forms application?
Any chance you are appending the results from the second query to the
listbox without clearning the list box first?
--
Scott
http://www.OdeToCode.com
On Sun, 8 Aug 2004 13:14:04 +0300, "pnp" <pnp.at.softlab.ece.ntua.gr>
wrote:
>Hi all,
>I've developed an app (C#) that connects to SQL Server 2000 without using
>thread pooling (using SqlConnection) and I run a query that returns ~4000
>tuples each time it runs and displays the results in a listview. The problem
>is that after I run it 3-4 times the results are read very slowly from the
>DB. The first times the results are displayed in 1-2 secs but the 4,5 time
>it takes more than 2 minutes! What am I doing wrong? Are there any
>in-between buffers that need to be emptied?
>Thanks,
>-peter
>|||The results are displayed in a listview and its' items are always cleared
before putting in the new ones...
"Scott Allen" <bitmask@.[nospam].fred.net> wrote in message
news:llbch0dojiom2vgf88iltuhpngpl4ikllh@.4ax.com...
> Hi Peter,
> If you could show us some code we might be able to make a better
> diagnosis.
> This is a windows forms application?
> Any chance you are appending the results from the second query to the
> listbox without clearning the list box first?
> --
> Scott
> http://www.OdeToCode.com
> On Sun, 8 Aug 2004 13:14:04 +0300, "pnp" <pnp.at.softlab.ece.ntua.gr>
> wrote:
> >Hi all,
> >I've developed an app (C#) that connects to SQL Server 2000 without using
> >thread pooling (using SqlConnection) and I run a query that returns ~4000
> >tuples each time it runs and displays the results in a listview. The
problem
> >is that after I run it 3-4 times the results are read very slowly from
the
> >DB. The first times the results are displayed in 1-2 secs but the 4,5
time
> >it takes more than 2 minutes! What am I doing wrong? Are there any
> >in-between buffers that need to be emptied?
> >
> >Thanks,
> >-peter
> >
>|||Start by using Profiler to see if you get the same execution plan between the executions. Depending on whether
you do or not, you can determine whether this is a SQL Server issue or a client app issue.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message news:OiAfzBTfEHA.4092@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> I've developed an app (C#) that connects to SQL Server 2000 without using
> thread pooling (using SqlConnection) and I run a query that returns ~4000
> tuples each time it runs and displays the results in a listview. The problem
> is that after I run it 3-4 times the results are read very slowly from the
> DB. The first times the results are displayed in 1-2 secs but the 4,5 time
> it takes more than 2 minutes! What am I doing wrong? Are there any
> in-between buffers that need to be emptied?
> Thanks,
> -peter
>|||well i checked it with the profiler and while the SQL:BatchCompleted takes
about the same amount of time for each execution (~2200) the Audit Logout is
different and when the program slows down it takes numbers up to ~584263
while a normal execution takes up to ~20296.
What does this mean?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23q6Ow0XfEHA.224@.TK2MSFTNGP10.phx.gbl...
> Start by using Profiler to see if you get the same execution plan between
the executions. Depending on whether
> you do or not, you can determine whether this is a SQL Server issue or a
client app issue.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
news:OiAfzBTfEHA.4092@.TK2MSFTNGP10.phx.gbl...
> > Hi all,
> > I've developed an app (C#) that connects to SQL Server 2000 without
using
> > thread pooling (using SqlConnection) and I run a query that returns
~4000
> > tuples each time it runs and displays the results in a listview. The
problem
> > is that after I run it 3-4 times the results are read very slowly from
the
> > DB. The first times the results are displayed in 1-2 secs but the 4,5
time
> > it takes more than 2 minutes! What am I doing wrong? Are there any
> > in-between buffers that need to be emptied?
> >
> > Thanks,
> > -peter
> >
> >
>|||If you check Books Online, you will find following for the "Audit Logout" event:
Duration The approximate amount of time since the user logged in.
But the SQL:BatchCompleted are the same, so this indicates that the query executes in the same time, the added
time for the logout is the time for the client to process the results before it can logout from the SQL
Server.
I.e., the problem is with the client app (at least as far as I can see). You might want to post this (with
appropriate details) to an ADO.NET group...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message news:%23LQX4gZfEHA.3016@.tk2msftngp13.phx.gbl...
> well i checked it with the profiler and while the SQL:BatchCompleted takes
> about the same amount of time for each execution (~2200) the Audit Logout is
> different and when the program slows down it takes numbers up to ~584263
> while a normal execution takes up to ~20296.
> What does this mean?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%23q6Ow0XfEHA.224@.TK2MSFTNGP10.phx.gbl...
> > Start by using Profiler to see if you get the same execution plan between
> the executions. Depending on whether
> > you do or not, you can determine whether this is a SQL Server issue or a
> client app issue.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
> news:OiAfzBTfEHA.4092@.TK2MSFTNGP10.phx.gbl...
> > > Hi all,
> > > I've developed an app (C#) that connects to SQL Server 2000 without
> using
> > > thread pooling (using SqlConnection) and I run a query that returns
> ~4000
> > > tuples each time it runs and displays the results in a listview. The
> problem
> > > is that after I run it 3-4 times the results are read very slowly from
> the
> > > DB. The first times the results are displayed in 1-2 secs but the 4,5
> time
> > > it takes more than 2 minutes! What am I doing wrong? Are there any
> > > in-between buffers that need to be emptied?
> > >
> > > Thanks,
> > > -peter
> > >
> > >
> >
> >
>|||I'll try to post it there... Thanks anyway.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:upyMcDjfEHA.708@.TK2MSFTNGP09.phx.gbl...
> If you check Books Online, you will find following for the "Audit Logout"
event:
> Duration The approximate amount of time since the user logged in.
>
> But the SQL:BatchCompleted are the same, so this indicates that the query
executes in the same time, the added
> time for the logout is the time for the client to process the results
before it can logout from the SQL
> Server.
> I.e., the problem is with the client app (at least as far as I can see).
You might want to post this (with
> appropriate details) to an ADO.NET group...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
news:%23LQX4gZfEHA.3016@.tk2msftngp13.phx.gbl...
> > well i checked it with the profiler and while the SQL:BatchCompleted
takes
> > about the same amount of time for each execution (~2200) the Audit
Logout is
> > different and when the program slows down it takes numbers up to ~584263
> > while a normal execution takes up to ~20296.
> >
> > What does this mean?
> >
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> > message news:%23q6Ow0XfEHA.224@.TK2MSFTNGP10.phx.gbl...
> > > Start by using Profiler to see if you get the same execution plan
between
> > the executions. Depending on whether
> > > you do or not, you can determine whether this is a SQL Server issue or
a
> > client app issue.
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > >
> > >
> > > "pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
> > news:OiAfzBTfEHA.4092@.TK2MSFTNGP10.phx.gbl...
> > > > Hi all,
> > > > I've developed an app (C#) that connects to SQL Server 2000 without
> > using
> > > > thread pooling (using SqlConnection) and I run a query that returns
> > ~4000
> > > > tuples each time it runs and displays the results in a listview. The
> > problem
> > > > is that after I run it 3-4 times the results are read very slowly
from
> > the
> > > > DB. The first times the results are displayed in 1-2 secs but the
4,5
> > time
> > > > it takes more than 2 minutes! What am I doing wrong? Are there any
> > > > in-between buffers that need to be emptied?
> > > >
> > > > Thanks,
> > > > -peter
> > > >
> > > >
> > >
> > >
> >
> >
>

Monday, March 26, 2012

Executing App with DTS, trouble with scheduling package

Hello,

I've created a test application in windows ( vb.net ). When executing this app with the DTS package it seems to work fine ( by right clicking on the package and selecting Execute Package ). It finishes successfully and does what it supposed to. But when I try to schedule the package with the SQL Server Enterprise Manager in Management > SQL Server Agent > Jobs it Fails every time. I schedule it by right clicking on the package and selecting the Schedule Package option. Does any one have an idea why the schedule keeps on failing? Thanks in advance.

Daniel B1) Locate the job (under SQL Agent)
2) Right click on the job
3) Pick View Job History...
4) Check the Show Job Details box
5) Read the error message(s)

-PatP|||This is the error message i get.

The job failed. The Job was invoked by User REPEATSEAT\dberezniak. The last step to run was step 1 (DTS Execute App).

This doesn't tell me much. It blows up when trying to execute the app.
( The application is test and does work on its own and within the package. )|||Did you click on the "Show Job Details" box to be sure that it is checked? That message sounds like what you'd get if that box wasn't checked.

-PatPsql

Executing App with DTS, trouble with scheduling package

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

Thanks for any help!
Dinesh

formHandlingServlet.class

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

Connection dbConn = null;

// create a persistent conneciton to the SQL server

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

//extract parameter information from register.jsp

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

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.

Sunday, February 26, 2012

Execute Permissions on 400 SPROCs

Our developers are rolling out an app with 400 new SPROCS. All data access i
s
done through them. I need to give a single user execute permissions on all
400 SPROCS. It would be easiest to just give the user execute permsissions o
n
all stored procs and remove access from the few that don't apply.
Is there a fast way to do this?The preferred way is to create a database User Role and provide execute
permissions to the User Role. And the same applies for creating a user Role
for DENY EXECUTE.
Then as users come and go, they only have to be added to or removed from the
User Role. The 'Best Practice' is to add the something like the following to
each stored procedure script file (You do have them in source
control -right?).
GRANT EXECUTE ON {StoredProcedureName} TO {UserRole}
And if necessary,
DENY EXECUTE ON {StoredProcedureName} TO {DenyUserRole}
Then, when the files are run on any server, the permissions are correct.
There are some stored procedures for which it is probably not a good idea to
provide users EXECUTE permissions. It is much better to explicitly grant
permissions to each stored procedure rather than use 'blanket' permissions
for all objects. I would much rather know that permissions were explicit
provided than accidentally supplied due to 'sloppiness'.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:82DEA919-4FB4-4BE1-B495-3FB1722122BB@.microsoft.com...
> Our developers are rolling out an app with 400 new SPROCS. All data access
> is
> done through them. I need to give a single user execute permissions on all
> 400 SPROCS. It would be easiest to just give the user execute permsissions
> on
> all stored procs and remove access from the few that don't apply.
> Is there a fast way to do this?|||Dan
SELECT 'GRANT EXECUTE ON [' + USER_NAME(uid) + '].[' + name + '] TO
' +
'[UserNameHere]'
FROM sysobjects
WHERE
type = 'P'
AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(USER_
NAME(uid)) + '.' +
QUOTENAME(name)), 'IsMSShipped') = 0
--Run the output in QA
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:82DEA919-4FB4-4BE1-B495-3FB1722122BB@.microsoft.com...
> Our developers are rolling out an app with 400 new SPROCS. All data access
> is
> done through them. I need to give a single user execute permissions on all
> 400 SPROCS. It would be easiest to just give the user execute permsissions
> on
> all stored procs and remove access from the few that don't apply.
> Is there a fast way to do this?

Friday, February 24, 2012

EXECUTE permission denied on [various DB objects] within SQL Express db

First attempt at using SQL Express developing web app. All works fine within VS2005 dev web server. However, after compiling and creating IIS7 site on same machine without changingconnectionString="Server=xxxx\SQLEXPVISTA;Database=ABCtest;Integrated Security=true" (SQLVISTA is named instance of SQL Express)

The only easy way I can avoid the 'EXECTUTE permissions denied...' is to give the NT AUTHORITY\NETWORK SERVICE db_owner role membership. Should I worry? Or should I go through the objects individually and specify permissions? Eventually, this will be on public web server.

In advance, thanks.

ASM

Hi,

From your description, it seems that you met "'EXECTUTE permissions denied..." error when you are trying to run your application from your IIS, right?

Based on my understanding, the cause of the issue is the account of IIS has not the permission to access your database. Generally, the ASPNET account is be authorized by default in your SQLExpress, so your application should work while running from the development server of VisualStudio. But when you deploy your application into IIS, the login account has changed, so you should add the login account into the logins of SQLExpress.

Thanks.

Execute permission denied

I am accessing an s2k database from a web app using .net.
My server died and I had to reinstall s2k and now I am
getting "Execute permission denied" on my stored
procedures. The web app has not changed so I infer that
something's wrong with the setup on the database. The
connection string is as follows:
"Password=xxxx;Persist Security Info=True;User
ID=WebUser;Initial Catalog=ScottWeb;Data
Source=SRV;Trusted_Connection=Yes"
WebUser exists and has owner permission on the db.
Thanks for any help.Hi,
After the reinstall of SQL 2000, did you restore the Master database and
then followed the restore of all user databases. If it is the case then
all the permissions for all Logins and users will be syncronized back.
If you have not resored the Master database then the SID in sysLogins table
will not syncronize with sysusers table. Refer the system procedure
"sp_change_users_login" in books online. This will re-sync the logins and
users.
Thanks
Hari
MCDBA
"Jon" <jsb@.watersteward.com> wrote in message
news:1aa501c48c8f$7c4a42b0$a501280a@.phx.gbl...
> I am accessing an s2k database from a web app using .net.
> My server died and I had to reinstall s2k and now I am
> getting "Execute permission denied" on my stored
> procedures. The web app has not changed so I infer that
> something's wrong with the setup on the database. The
> connection string is as follows:
> "Password=xxxx;Persist Security Info=True;User
> ID=WebUser;Initial Catalog=ScottWeb;Data
> Source=SRV;Trusted_Connection=Yes"
> WebUser exists and has owner permission on the db.
> Thanks for any help.|||To add to Hari's response, note that your connection string has conflicting
security credentials; both standard security (User ID and Password) and
Windows authentication (Trusted_Connection=Yes) are specified. To connect
with SQL authentication using the WebUser login, ensure your SQL Server is
configured to allow both Windows and SQL authentication and remove
Trusted_Connection from the connection string.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon" <jsb@.watersteward.com> wrote in message
news:1aa501c48c8f$7c4a42b0$a501280a@.phx.gbl...
>I am accessing an s2k database from a web app using .net.
> My server died and I had to reinstall s2k and now I am
> getting "Execute permission denied" on my stored
> procedures. The web app has not changed so I infer that
> something's wrong with the setup on the database. The
> connection string is as follows:
> "Password=xxxx;Persist Security Info=True;User
> ID=WebUser;Initial Catalog=ScottWeb;Data
> Source=SRV;Trusted_Connection=Yes"
> WebUser exists and has owner permission on the db.
> Thanks for any help.

Thursday, March 29, 2012

EXECuting sps within sps (using return params)

Tuesday, March 27, 2012

Executing large result queries

Executing large result queries

Executing large result queries

Monday, March 26, 2012

Executing App with DTS, trouble with scheduling package

Executing App with DTS, trouble with scheduling package

Executing an MS SQL stored procedure from a java servlet

Sunday, February 26, 2012

Execute Permissions on 400 SPROCs

Friday, February 24, 2012

EXECUTE permission denied on [various DB objects] within SQL Express db

Execute permission denied

EXECUTE AS question

Blog Archive

About Me