Job Question

HI,
I have multiple SQL servers and i am planning to create a job and deploy to
all the servers instead of deploying that job one by one in all servers and
generate report from one server instead of all (consolidation) later to see
on which server the job has failed. What best options do i have to link all
of them together? SO far i have known of linked server and making master and
target server. Anything else? or which one is better bewteen this two?
TIA
Rupart,
I would prefer using a master server and several target servers in this
scenario. The job status can be viewed locally on the target server or
remotely on the master server. Note, the job definition cannot be changed
on the target server. Also note that a target server (to my knowledge) can
only be associated with one master server at a time.
HTH
Jerry
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:F6C965C6-D4A8-4697-B482-7D37C0BCE9F7@.microsoft.com...
> HI,
> I have multiple SQL servers and i am planning to create a job and deploy
> to
> all the servers instead of deploying that job one by one in all servers
> and
> generate report from one server instead of all (consolidation) later to
> see
> on which server the job has failed. What best options do i have to link
> all
> of them together? SO far i have known of linked server and making master
> and
> target server. Anything else? or which one is better bewteen this two?
> TIA

Job Question

HI,
I have multiple SQL servers and i am planning to create a job and deploy to
all the servers instead of deploying that job one by one in all servers and
generate report from one server instead of all (consolidation) later to see
on which server the job has failed. What best options do i have to link all
of them together? SO far i have known of linked server and making master and
target server. Anything else? or which one is better bewteen this two?
TIARupart,
I would prefer using a master server and several target servers in this
scenario. The job status can be viewed locally on the target server or
remotely on the master server. Note, the job definition cannot be changed
on the target server. Also note that a target server (to my knowledge) can
only be associated with one master server at a time.
HTH
Jerry
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:F6C965C6-D4A8-4697-B482-7D37C0BCE9F7@.microsoft.com...
> HI,
> I have multiple SQL servers and i am planning to create a job and deploy
> to
> all the servers instead of deploying that job one by one in all servers
> and
> generate report from one server instead of all (consolidation) later to
> see
> on which server the job has failed. What best options do i have to link
> all
> of them together? SO far i have known of linked server and making master
> and
> target server. Anything else? or which one is better bewteen this two?
> TIA

Job Question

HI,
I have multiple SQL servers and i am planning to create a job and deploy to
all the servers instead of deploying that job one by one in all servers and
generate report from one server instead of all (consolidation) later to see
on which server the job has failed. What best options do i have to link all
of them together? SO far i have known of linked server and making master and
target server. Anything else? or which one is better bewteen this two?
TIARupart,
I would prefer using a master server and several target servers in this
scenario. The job status can be viewed locally on the target server or
remotely on the master server. Note, the job definition cannot be changed
on the target server. Also note that a target server (to my knowledge) can
only be associated with one master server at a time.
HTH
Jerry
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:F6C965C6-D4A8-4697-B482-7D37C0BCE9F7@.microsoft.com...
> HI,
> I have multiple SQL servers and i am planning to create a job and deploy
> to
> all the servers instead of deploying that job one by one in all servers
> and
> generate report from one server instead of all (consolidation) later to
> see
> on which server the job has failed. What best options do i have to link
> all
> of them together? SO far i have known of linked server and making master
> and
> target server. Anything else? or which one is better bewteen this two?
> TIAsql

JDBC: calling a stored procedure with multiple return values.

Using JDBC, is there a way to call a stored procedure with multiple
return values? Thanks.
randy.p.ho@.gmail.com wrote:

> Using JDBC, is there a way to call a stored procedure with multiple
> return values? Thanks.

Absolutely. What do you mean by 'multiple return values'? Multiple output
parameters? Multiple result sets and/or update counts? Multiple mixes of
result sets and update counts?
If you will show the procedure signature and maybe even the text? Tell
us what the body of the procedure returns.

Joe Weinstein at BEA|||Thanks for the reply. I meant "multiple output parameters".

Here is how I execute the stored procedure:

declare @.ErrorID int
declare @.ErrorStr varchar(255)
exec procName
@.customerId = '1234567890',
@.customerName = 'some name',
@.error_code = @.ErrorID,
@.error_state = @.ErrorStr

Here is the procedure:

create procedure uxt1.procName
@.customerId char(15) output,
@.customerName char(64) output,
@.error_code int output,
@.error_state varchar(255) output
... ...
/* all the business logic */
... ...
return (@.error_state)
GO

Here's what SQL server gives me if I do a "Script object as Execute":
DECLARE @.RC int
DECLARE @.customerId char(15)
DECLARE @.customerName char(64)
DECLARE @.error_code int
DECLARE @.error_state varchar(255)
EXEC @.RC = [uxt1].[procName] @.customerId, @.customerName, @.error_code
OUTPUT , @.error_state OUTPUT

The following is what I've tried in a Java program:
...
CallableStatement cs = conn.prepareCall(" {? = call
uxt1.procName(?,?,?,?)}" );
cs.registerOutParameter(1,java.sql.Types.INTEGER);
cs.setString(2,"some ID");
cs.setString(3,"some Name");
cs.registerOutParameter(4,java.sql.Types.INTEGER);
cs.registerOutParameter(5,java.sql.Types.VARCHAR);
ResultSet rs = cs.executeQuery();
...

My code doesn't throw any exception; but the procedure was not executed
correctly (i.e. it's not doing what it's supposed to do, which is to
simply insert some values into a table).
Any help is appreciated. Thanks in advance.|||
randy.p.ho@.gmail.com wrote:

> Thanks for the reply. I meant "multiple output parameters".
> Here is how I execute the stored procedure:
> declare @.ErrorID int
> declare @.ErrorStr varchar(255)
> exec procName
> @.customerId = '1234567890',
> @.customerName = 'some name',
> @.error_code = @.ErrorID,
> @.error_state = @.ErrorStr
>
> Here is the procedure:
> create procedure uxt1.procName
> @.customerId char(15) output,
> @.customerName char(64) output,
> @.error_code int output,
> @.error_state varchar(255) output
> ... ...
> /* all the business logic */
> ... ...
> return (@.error_state)
> GO
>
> Here's what SQL server gives me if I do a "Script object as Execute":
> DECLARE @.RC int
> DECLARE @.customerId char(15)
> DECLARE @.customerName char(64)
> DECLARE @.error_code int
> DECLARE @.error_state varchar(255)
> EXEC @.RC = [uxt1].[procName] @.customerId, @.customerName, @.error_code
> OUTPUT , @.error_state OUTPUT
>
> The following is what I've tried in a Java program:
> ...
> CallableStatement cs = conn.prepareCall(" {? = call
> uxt1.procName(?,?,?,?)}" );
> cs.registerOutParameter(1,java.sql.Types.INTEGER);
> cs.setString(2,"some ID");
> cs.setString(3,"some Name");
> cs.registerOutParameter(4,java.sql.Types.INTEGER);
> cs.registerOutParameter(5,java.sql.Types.VARCHAR);
> ResultSet rs = cs.executeQuery();
> ...
> My code doesn't throw any exception; but the procedure was not executed
> correctly (i.e. it's not doing what it's supposed to do, which is to
> simply insert some values into a table).
> Any help is appreciated. Thanks in advance.

Is the procedure executing at all? You shouldn't be calling executeQuery()
unless the first thing the procedure does is a select. Use execute() and
then loop:

cs.execute();
while (true)
{
int update_count = ps.getUpdateCount();
ResultSet rs = ps.getResultSet();
if ((rs == null && (update_count == -1)) break; // done

if (rs != null) process rs;
ps.getMoreResults();
}
// after processing inline results, call ps.getXXX() to get output parameters.

Whatever jdbc driver you're suing is pretty flakey if it
returns a result set from executeQuery() and the
procedure didn't do a select for data to go to the caller...

Joe Weinstein at BEA|||I have closely the same problem, excepting that my stored procs returns a "TABLE".

In fact, I have a SQL function that must return a TABLE and get 2 parameters. Its signature is as follow:

FUNCTION myFunction (@.codett varchar(40), @.codet varchar(40) )
RETURNS @.Tab TABLE([cp] [varchar] (40), [pourcentage] [int])

I'd like to call it from my DAO (using JDBC). having that 'cs' is a CallableStatement, I tried:

cs = jdbcConnection.prepareCall("{? = call myFunction(?, ?)}");
cs.registerOutParameter( 1, Types.OTHER );
cs.setString(2, codett);//codett contains a String
cs.setString(3, codet);//codet contains a String
ResultSet rset = cs.executeQuery();

However, when a get to the "executeQuery()" method, I get an SQLException saying that "myFunction" is a function Object.

have you met such a problem ?

jdbc performance issuses with 2005 vs 2000

I cannot get performance out of sql server 2005 through jdbc connections.
I have used multiple drivers against 2000 and 2005. 2000 always comes out on top.
I installed the os the same, configured the raid array the same, configured the os the same, configured the database the same, installed the software the same, etc.

It seems to come down to the jdbc driver and the way the database handles batch requests from jdbc. Is there some configuration in 2005 that I need to alter to improve the performance of batched inserts through the jdbc driver?

I am sorry to hear that the 2005 driver isn't delivering the same performance as the 2000 driver for you here. Do you have a small code sample that you could share that demonstrates where you are seeing the decreased performance? It would be helpful to know whether the inserts are through Statement or PreparedStatement objects, what types are involved, and the driver version (v1.0, v1.1 CTP?). Also, what are you measuring (e.g. CPU utilization, client response time, inserts/second, etc.)?

Thank you,

--David Olix

JDBC Development

|||Here is the trace from the profiler.

I tried setting the following options on the url connection but it doesn't seem to make that much of a difference in increased performance.
selectMethod=cursor;sendStringParametersAsUnicode=False

I am using the 1.1 version of the jdbc driver.

Measuring inserts per second.

declare @.p2 int
set @.p2=0
declare @.p5 int
set @.p5=0
exec sp_cursorexecute 91,@.p2 output,16,8193,@.p5 output,584964,1,140830000,113364,19000101,99991231,'JERSEY','JERSEY',3,2,2
select @.p2, @.p5|||

For better performance, you generally should not use selectMethod=cursor. Setting selectMethod=cursor forces every forward only/read only query to request a server cursor. For queries that return only a few rows, the overhead of creating and destroying a server cursor can be relatively high. Server cursors (and selectMethod=cursor) typically yield better performance for queries that return a large number of rows.

Note that you should not need to use server cursors at all with any INSERT or other DML statement, because these kinds of statements do not produce a result set. Are you using the executeUpdate() method or the execute() method to execute the INSERT statements? By using executeUpdate(), you let the driver know that the SQL is expected to produce an update count, rather than a result set, so it won't request a server cursor, even when selectMethod=cursor is set. Also, you may want to consider doing the inserts in a batch with autocommit set to false.

Thank you,

--David Olix

JDBC Development