Job SQL Limitation

Does anyone know what is the limitation on SQL Server job
SQL statement is ?. I am trying to create a job that has a
long SQL (Select) statement but it does not allow me to
put the whole statement (It truncates). What are the
alternatives (NOT DTS or QA)? These are more that 15
separeate SQL statements I need to schedule and get the
result in 1 file.
T.I.AWhen you say one file do you mean the output file for the job? If so why
not place these in stored procedures and simply run them?
Andrew J. Kelly SQL MVP
"Ken" <anonymous@.discussions.microsoft.com> wrote in message
news:210b01c50239$2df26960$a401280a@.phx.gbl...
> Does anyone know what is the limitation on SQL Server job
> SQL statement is ?. I am trying to create a job that has a
> long SQL (Select) statement but it does not allow me to
> put the whole statement (It truncates). What are the
> alternatives (NOT DTS or QA)? These are more that 15
> separeate SQL statements I need to schedule and get the
> result in 1 file.
> T.I.A|||> SQL statement is ?. I am trying to create a job that has a
> long SQL (Select) statement but it does not allow me to
> put the whole statement (It truncates). What are the
> alternatives
Put your T-SQL code in a stored procedure?
http://www.aspfaq.com/
(Reverse address to reply.)|||If you cannot or do not want to create a stored procedure (for whatever
reason) I suggest you save your T-SQL to a file on the local hard drive.
You can use osql.exe to execute the script file within the job step.
Keith
"Ken" <anonymous@.discussions.microsoft.com> wrote in message
news:210b01c50239$2df26960$a401280a@.phx.gbl...
> Does anyone know what is the limitation on SQL Server job
> SQL statement is ?. I am trying to create a job that has a
> long SQL (Select) statement but it does not allow me to
> put the whole statement (It truncates). What are the
> alternatives (NOT DTS or QA)? These are more that 15
> separeate SQL statements I need to schedule and get the
> result in 1 file.
> T.I.A|||Just to add...the limit is 3200 characters.
-Sue
On Mon, 24 Jan 2005 09:21:45 -0800, "Ken"
<anonymous@.discussions.microsoft.com> wrote:

>Does anyone know what is the limitation on SQL Server job
>SQL statement is ?. I am trying to create a job that has a
>long SQL (Select) statement but it does not allow me to
>put the whole statement (It truncates). What are the
>alternatives (NOT DTS or QA)? These are more that 15
>separeate SQL statements I need to schedule and get the
>result in 1 file.
>T.I.A|||Thanks to all.........
I will use SP.......

>--Original Message--
has a[vbcol=seagreen]
>Put your T-SQL code in a stored procedure?
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>.
>|||Although, I put the statements into a SP, I don't get the
same output in Scheduled job output as I get out of the QA
(I like to get it as it is in QA output).
Thanks.

>--Original Message--
has a[vbcol=seagreen]
>Put your T-SQL code in a stored procedure?
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>.
>|||But EM might be more restrictive then 3200 characters. I recall a case a lon
g time ago when I tested
it, and conclusion was that if you need more than what EM allows, you need t
o add the jobsteps using
sp_add_jobstep.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:djeav0140graejhho9kee8u1qkd68olvgf@.
4ax.com...
> Just to add...the limit is 3200 characters.
> -Sue
> On Mon, 24 Jan 2005 09:21:45 -0800, "Ken"
> <anonymous@.discussions.microsoft.com> wrote:
>
>|||I have 1 Select statement that is 3750 characters which
does not go into any job steps.........

>--Original Message--
>But EM might be more restrictive then 3200 characters. I
recall a case a long time ago when I tested
>it, and conclusion was that if you need more than what EM
allows, you need to add the jobsteps using
>sp_add_jobstep.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>http://www.sqlug.se/
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:djeav0140graejhho9kee8u1qkd68olvgf@.
4ax.com...
job[vbcol=seagreen]
has a[vbcol=seagreen]
>
>.
>|||If you are > 3200 characters, you cannot have it directly in the jobstep reg
ardless of whether you
are using EM or call sp_add_jobstep directly. Use any of the other suggested
methods (put it in a
stored procedure, or use CmdExec jobstep which calls OSQL.EXE with an inputf
ile).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Ken" <anonymous@.discussions.microsoft.com> wrote in message
news:062b01c502e5$e2bc1330$a601280a@.phx.gbl...[vbcol=seagreen]
>I have 1 Select statement that is 3750 characters which
> does not go into any job steps.........
>
> recall a case a long time ago when I tested
> allows, you need to add the jobsteps using
> job
> has a

Job SQL Limitation

Does anyone know what is the limitation on SQL Server job
SQL statement is ?. I am trying to create a job that has a
long SQL (Select) statement but it does not allow me to
put the whole statement (It truncates). What are the
alternatives (NOT DTS or QA)? These are more that 15
separeate SQL statements I need to schedule and get the
result in 1 file.
T.I.AWhen you say one file do you mean the output file for the job? If so why
not place these in stored procedures and simply run them?
--
Andrew J. Kelly SQL MVP
"Ken" <anonymous@.discussions.microsoft.com> wrote in message
news:210b01c50239$2df26960$a401280a@.phx.gbl...
> Does anyone know what is the limitation on SQL Server job
> SQL statement is ?. I am trying to create a job that has a
> long SQL (Select) statement but it does not allow me to
> put the whole statement (It truncates). What are the
> alternatives (NOT DTS or QA)? These are more that 15
> separeate SQL statements I need to schedule and get the
> result in 1 file.
> T.I.A|||> SQL statement is ?. I am trying to create a job that has a
> long SQL (Select) statement but it does not allow me to
> put the whole statement (It truncates). What are the
> alternatives
Put your T-SQL code in a stored procedure?
--
http://www.aspfaq.com/
(Reverse address to reply.)|||If you cannot or do not want to create a stored procedure (for whatever
reason) I suggest you save your T-SQL to a file on the local hard drive.
You can use osql.exe to execute the script file within the job step.
--
Keith
"Ken" <anonymous@.discussions.microsoft.com> wrote in message
news:210b01c50239$2df26960$a401280a@.phx.gbl...
> Does anyone know what is the limitation on SQL Server job
> SQL statement is ?. I am trying to create a job that has a
> long SQL (Select) statement but it does not allow me to
> put the whole statement (It truncates). What are the
> alternatives (NOT DTS or QA)? These are more that 15
> separeate SQL statements I need to schedule and get the
> result in 1 file.
> T.I.A|||Just to add...the limit is 3200 characters.
-Sue
On Mon, 24 Jan 2005 09:21:45 -0800, "Ken"
<anonymous@.discussions.microsoft.com> wrote:
>Does anyone know what is the limitation on SQL Server job
>SQL statement is ?. I am trying to create a job that has a
>long SQL (Select) statement but it does not allow me to
>put the whole statement (It truncates). What are the
>alternatives (NOT DTS or QA)? These are more that 15
>separeate SQL statements I need to schedule and get the
>result in 1 file.
>T.I.A|||Thanks to all.........
I will use SP.......
>--Original Message--
>> SQL statement is ?. I am trying to create a job that
has a
>> long SQL (Select) statement but it does not allow me to
>> put the whole statement (It truncates). What are the
>> alternatives
>Put your T-SQL code in a stored procedure?
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>.
>|||Although, I put the statements into a SP, I don't get the
same output in Scheduled job output as I get out of the QA
(I like to get it as it is in QA output).
Thanks.
>--Original Message--
>> SQL statement is ?. I am trying to create a job that
has a
>> long SQL (Select) statement but it does not allow me to
>> put the whole statement (It truncates). What are the
>> alternatives
>Put your T-SQL code in a stored procedure?
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>.
>|||But EM might be more restrictive then 3200 characters. I recall a case a long time ago when I tested
it, and conclusion was that if you need more than what EM allows, you need to add the jobsteps using
sp_add_jobstep.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:djeav0140graejhho9kee8u1qkd68olvgf@.4ax.com...
> Just to add...the limit is 3200 characters.
> -Sue
> On Mon, 24 Jan 2005 09:21:45 -0800, "Ken"
> <anonymous@.discussions.microsoft.com> wrote:
>>Does anyone know what is the limitation on SQL Server job
>>SQL statement is ?. I am trying to create a job that has a
>>long SQL (Select) statement but it does not allow me to
>>put the whole statement (It truncates). What are the
>>alternatives (NOT DTS or QA)? These are more that 15
>>separeate SQL statements I need to schedule and get the
>>result in 1 file.
>>T.I.A
>|||I have 1 Select statement that is 3750 characters which
does not go into any job steps.........
>--Original Message--
>But EM might be more restrictive then 3200 characters. I
recall a case a long time ago when I tested
>it, and conclusion was that if you need more than what EM
allows, you need to add the jobsteps using
>sp_add_jobstep.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>http://www.sqlug.se/
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:djeav0140graejhho9kee8u1qkd68olvgf@.4ax.com...
>> Just to add...the limit is 3200 characters.
>> -Sue
>> On Mon, 24 Jan 2005 09:21:45 -0800, "Ken"
>> <anonymous@.discussions.microsoft.com> wrote:
>>Does anyone know what is the limitation on SQL Server
job
>>SQL statement is ?. I am trying to create a job that
has a
>>long SQL (Select) statement but it does not allow me to
>>put the whole statement (It truncates). What are the
>>alternatives (NOT DTS or QA)? These are more that 15
>>separeate SQL statements I need to schedule and get the
>>result in 1 file.
>>T.I.A
>
>.
>|||If you are > 3200 characters, you cannot have it directly in the jobstep regardless of whether you
are using EM or call sp_add_jobstep directly. Use any of the other suggested methods (put it in a
stored procedure, or use CmdExec jobstep which calls OSQL.EXE with an inputfile).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Ken" <anonymous@.discussions.microsoft.com> wrote in message
news:062b01c502e5$e2bc1330$a601280a@.phx.gbl...
>I have 1 Select statement that is 3750 characters which
> does not go into any job steps.........
>
>>--Original Message--
>>But EM might be more restrictive then 3200 characters. I
> recall a case a long time ago when I tested
>>it, and conclusion was that if you need more than what EM
> allows, you need to add the jobsteps using
>>sp_add_jobstep.
>>--
>>Tibor Karaszi, SQL Server MVP
>>http://www.karaszi.com/sqlserver/default.asp
>>http://www.solidqualitylearning.com/
>>http://www.sqlug.se/
>>
>>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>>news:djeav0140graejhho9kee8u1qkd68olvgf@.4ax.com...
>> Just to add...the limit is 3200 characters.
>> -Sue
>> On Mon, 24 Jan 2005 09:21:45 -0800, "Ken"
>> <anonymous@.discussions.microsoft.com> wrote:
>>Does anyone know what is the limitation on SQL Server
> job
>>SQL statement is ?. I am trying to create a job that
> has a
>>long SQL (Select) statement but it does not allow me to
>>put the whole statement (It truncates). What are the
>>alternatives (NOT DTS or QA)? These are more that 15
>>separeate SQL statements I need to schedule and get the
>>result in 1 file.
>>T.I.A
>>
>>.|||But my output is not the same as it is in the QA.
>--Original Message--
>If you are > 3200 characters, you cannot have it directly
in the jobstep regardless of whether you
>are using EM or call sp_add_jobstep directly. Use any of
the other suggested methods (put it in a
>stored procedure, or use CmdExec jobstep which calls
OSQL.EXE with an inputfile).
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>http://www.sqlug.se/
>
>"Ken" <anonymous@.discussions.microsoft.com> wrote in
message
>news:062b01c502e5$e2bc1330$a601280a@.phx.gbl...
>>I have 1 Select statement that is 3750 characters which
>> does not go into any job steps.........
>>
>>--Original Message--
>>But EM might be more restrictive then 3200 characters. I
>> recall a case a long time ago when I tested
>>it, and conclusion was that if you need more than what
EM
>> allows, you need to add the jobsteps using
>>sp_add_jobstep.
>>--
>>Tibor Karaszi, SQL Server MVP
>>http://www.karaszi.com/sqlserver/default.asp
>>http://www.solidqualitylearning.com/
>>http://www.sqlug.se/
>>
>>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>>news:djeav0140graejhho9kee8u1qkd68olvgf@.4ax.com...
>> Just to add...the limit is 3200 characters.
>> -Sue
>> On Mon, 24 Jan 2005 09:21:45 -0800, "Ken"
>> <anonymous@.discussions.microsoft.com> wrote:
>>Does anyone know what is the limitation on SQL Server
>> job
>>SQL statement is ?. I am trying to create a job that
>> has a
>>long SQL (Select) statement but it does not allow me
to
>>put the whole statement (It truncates). What are the
>>alternatives (NOT DTS or QA)? These are more that 15
>>separeate SQL statements I need to schedule and get
the
>>result in 1 file.
>>T.I.A
>>
>>.
>
>.
>|||In which way does it differ? And where do you see this output?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Ken" <anonymous@.discussions.microsoft.com> wrote in message
news:1c8001c502ee$1e5e3690$a501280a@.phx.gbl...
> But my output is not the same as it is in the QA.
>
>>--Original Message--
>>If you are > 3200 characters, you cannot have it directly
> in the jobstep regardless of whether you
>>are using EM or call sp_add_jobstep directly. Use any of
> the other suggested methods (put it in a
>>stored procedure, or use CmdExec jobstep which calls
> OSQL.EXE with an inputfile).
>>--
>>Tibor Karaszi, SQL Server MVP
>>http://www.karaszi.com/sqlserver/default.asp
>>http://www.solidqualitylearning.com/
>>http://www.sqlug.se/
>>
>>"Ken" <anonymous@.discussions.microsoft.com> wrote in
> message
>>news:062b01c502e5$e2bc1330$a601280a@.phx.gbl...
>>I have 1 Select statement that is 3750 characters which
>> does not go into any job steps.........
>>
>>--Original Message--
>>But EM might be more restrictive then 3200 characters. I
>> recall a case a long time ago when I tested
>>it, and conclusion was that if you need more than what
> EM
>> allows, you need to add the jobsteps using
>>sp_add_jobstep.
>>--
>>Tibor Karaszi, SQL Server MVP
>>http://www.karaszi.com/sqlserver/default.asp
>>http://www.solidqualitylearning.com/
>>http://www.sqlug.se/
>>
>>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>>news:djeav0140graejhho9kee8u1qkd68olvgf@.4ax.com...
>> Just to add...the limit is 3200 characters.
>> -Sue
>> On Mon, 24 Jan 2005 09:21:45 -0800, "Ken"
>> <anonymous@.discussions.microsoft.com> wrote:
>>Does anyone know what is the limitation on SQL Server
>> job
>>SQL statement is ?. I am trying to create a job that
>> has a
>>long SQL (Select) statement but it does not allow me
> to
>>put the whole statement (It truncates). What are the
>>alternatives (NOT DTS or QA)? These are more that 15
>>separeate SQL statements I need to schedule and get
> the
>>result in 1 file.
>>T.I.A
>>
>>.
>>
>>.

Job SQL Limitation

Does anyone know what is the limitation on SQL Server job
SQL statement is ?. I am trying to create a job that has a
long SQL (Select) statement but it does not allow me to
put the whole statement (It truncates). What are the
alternatives (NOT DTS or QA)? These are more that 15
separeate SQL statements I need to schedule and get the
result in 1 file.
T.I.A
When you say one file do you mean the output file for the job? If so why
not place these in stored procedures and simply run them?
Andrew J. Kelly SQL MVP
"Ken" <anonymous@.discussions.microsoft.com> wrote in message
news:210b01c50239$2df26960$a401280a@.phx.gbl...
> Does anyone know what is the limitation on SQL Server job
> SQL statement is ?. I am trying to create a job that has a
> long SQL (Select) statement but it does not allow me to
> put the whole statement (It truncates). What are the
> alternatives (NOT DTS or QA)? These are more that 15
> separeate SQL statements I need to schedule and get the
> result in 1 file.
> T.I.A
|||> SQL statement is ?. I am trying to create a job that has a
> long SQL (Select) statement but it does not allow me to
> put the whole statement (It truncates). What are the
> alternatives
Put your T-SQL code in a stored procedure?
http://www.aspfaq.com/
(Reverse address to reply.)
|||If you cannot or do not want to create a stored procedure (for whatever
reason) I suggest you save your T-SQL to a file on the local hard drive.
You can use osql.exe to execute the script file within the job step.
Keith
"Ken" <anonymous@.discussions.microsoft.com> wrote in message
news:210b01c50239$2df26960$a401280a@.phx.gbl...
> Does anyone know what is the limitation on SQL Server job
> SQL statement is ?. I am trying to create a job that has a
> long SQL (Select) statement but it does not allow me to
> put the whole statement (It truncates). What are the
> alternatives (NOT DTS or QA)? These are more that 15
> separeate SQL statements I need to schedule and get the
> result in 1 file.
> T.I.A
|||Just to add...the limit is 3200 characters.
-Sue
On Mon, 24 Jan 2005 09:21:45 -0800, "Ken"
<anonymous@.discussions.microsoft.com> wrote:

>Does anyone know what is the limitation on SQL Server job
>SQL statement is ?. I am trying to create a job that has a
>long SQL (Select) statement but it does not allow me to
>put the whole statement (It truncates). What are the
>alternatives (NOT DTS or QA)? These are more that 15
>separeate SQL statements I need to schedule and get the
>result in 1 file.
>T.I.A
|||Thanks to all.........
I will use SP.......
[vbcol=seagreen]
>--Original Message--
has a
>Put your T-SQL code in a stored procedure?
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>.
>
|||Although, I put the statements into a SP, I don't get the
same output in Scheduled job output as I get out of the QA
(I like to get it as it is in QA output).
Thanks.
[vbcol=seagreen]
>--Original Message--
has a
>Put your T-SQL code in a stored procedure?
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>.
>
|||But EM might be more restrictive then 3200 characters. I recall a case a long time ago when I tested
it, and conclusion was that if you need more than what EM allows, you need to add the jobsteps using
sp_add_jobstep.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:djeav0140graejhho9kee8u1qkd68olvgf@.4ax.com...
> Just to add...the limit is 3200 characters.
> -Sue
> On Mon, 24 Jan 2005 09:21:45 -0800, "Ken"
> <anonymous@.discussions.microsoft.com> wrote:
>
|||I have 1 Select statement that is 3750 characters which
does not go into any job steps.........

>--Original Message--
>But EM might be more restrictive then 3200 characters. I
recall a case a long time ago when I tested
>it, and conclusion was that if you need more than what EM
allows, you need to add the jobsteps using[vbcol=seagreen]
>sp_add_jobstep.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>http://www.sqlug.se/
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:djeav0140graejhho9kee8u1qkd68olvgf@.4ax.com.. .
job[vbcol=seagreen]
has a
>
>.
>
|||If you are > 3200 characters, you cannot have it directly in the jobstep regardless of whether you
are using EM or call sp_add_jobstep directly. Use any of the other suggested methods (put it in a
stored procedure, or use CmdExec jobstep which calls OSQL.EXE with an inputfile).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Ken" <anonymous@.discussions.microsoft.com> wrote in message
news:062b01c502e5$e2bc1330$a601280a@.phx.gbl...[vbcol=seagreen]
>I have 1 Select statement that is 3750 characters which
> does not go into any job steps.........
>
> recall a case a long time ago when I tested
> allows, you need to add the jobsteps using
> job
> has a
sql

job fails

Hi all,

Sql server 7

I have created a Web assistant job which select
and displays data from a table (very small amt of data)
on to the webpage. the job is schedule to run
every 1 minute. the job is not running, in job view
history its gives the error msg
"could not open the output file" 16821 Error no.

pls suggest
Awaiting for ur reply

TIA
AdilYou need to make sure that the service account (it HAS to have an account, not System Account) for SQLAgent has Create/Write permissions on the destination.

Jet datasource on x64

Running SSRS on a 64bit machine. Have a legacy MS Access database that
we need to use as a datasource for select reports. Appears that Jet
was not ported to x64. Is my only recourse for connecting from SSRS to
Jet to uninstall SSRS x64 and install 32bit version?On Aug 22, 9:30 am, Jim <emailje...@.yahoo.com> wrote:
> Running SSRS on a 64bit machine. Have a legacy MS Access database that
> we need to use as a datasource for select reports. Appears that Jet
> was not ported to x64. Is my only recourse for connecting from SSRS to
> Jet to uninstall SSRS x64 and install 32bit version?
Just a quick followup that I decided against the 32bit version of SSRS
and instead installed an instance of SQL Express which runs 32 bit
inside WOW. SQL Express can use Jet as a linked server. and x64 SQL
Server can link to 32bit express. Not ideal, but gets me the data I
need untill we can do away with MS Access/Jet.
Cheers

JDBC Driver Settings

Hello,
I saw the below in one of settings..where do i find them and where are they
defined..
see lot of select statements with sp_cursoropen with thousands of reads in
the
profiler but if i take the query and run it in the QA it will be 1/4 of
those reads .I
am wondering we will have to tweak somethings like you mentioned below.Could
you help me to find out where i could find those settings like fetchsize and
selectmode..
If you do decide to use cursor mode with ResultSets defined as
TYPE_FORWARD_ONLY and either CONCUR_READONLY or CONCUR_UPDATABLE, then you,
you can additionally test various values for the fetch size to tune the
performance. Otherwise, your fetch size will only be 1.
| Thread-Topic: JDBC Driver Settings
| thread-index: AcVKCoOrRDJWhsIWSTSRvlg4RlTrKQ==
| X-WBNR-Posting-Host: 12.214.173.82
| From: "=?Utf-8?B?Y2hpbm4=?=" <chinn@.discussions.microsoft.com>
| Subject: JDBC Driver Settings
| Date: Mon, 25 Apr 2005 19:49:06 -0700
| Lines: 20
| Message-ID: <7959E226-7B55-4C1E-BD44-F74307B59BF2@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.jdbcdriver:1049
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
|
| Hello,
| I saw the below in one of settings..where do i find them and where are
they
| defined..
|
|
| see lot of select statements with sp_cursoropen with thousands of reads
in
| the
| profiler but if i take the query and run it in the QA it will be 1/4 of
| those reads .I
| am wondering we will have to tweak somethings like you mentioned
below.Could
| you help me to find out where i could find those settings like fetchsize
and
| selectmode..
|
|
| If you do decide to use cursor mode with ResultSets defined as
| TYPE_FORWARD_ONLY and either CONCUR_READONLY or CONCUR_UPDATABLE, then
you,
| you can additionally test various values for the fetch size to tune the
| performance. Otherwise, your fetch size will only be 1.
|
|
|
You can set the fetch size using the setFetchSize() method from your
Statement/PreparedStatement/CallableStatement object. SelectMethod can be
set to either Direct or Cursor, and this is done in the connection string:
SelectMethod=cursor
or
SelectMethod=direct
The default is Direct.
Carb Simien, MCSE MCDBA MCAD
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
|||Carb,
But somebody is saying this..
Is this correct..
Microsoft JDBC driver doesn't support 'DIRECT' select mode for XADatasources
(XA database connections). So we have to use cursor method.
When I tried to switch to Direct, App didn't start with the error that
'direct' select method can not be used for XA Connections.
thanks,
""Carb Simien [MSFT]"" wrote:

> --
> | Thread-Topic: JDBC Driver Settings
> | thread-index: AcVKCoOrRDJWhsIWSTSRvlg4RlTrKQ==
> | X-WBNR-Posting-Host: 12.214.173.82
> | From: "=?Utf-8?B?Y2hpbm4=?=" <chinn@.discussions.microsoft.com>
> | Subject: JDBC Driver Settings
> | Date: Mon, 25 Apr 2005 19:49:06 -0700
> | Lines: 20
> | Message-ID: <7959E226-7B55-4C1E-BD44-F74307B59BF2@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.jdbcdriver
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.jdbcdriver:1049
> | X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
> |
> | Hello,
> | I saw the below in one of settings..where do i find them and where are
> they
> | defined..
> |
> |
> | see lot of select statements with sp_cursoropen with thousands of reads
> in
> | the
> | profiler but if i take the query and run it in the QA it will be 1/4 of
> | those reads .I
> | am wondering we will have to tweak somethings like you mentioned
> below.Could
> | you help me to find out where i could find those settings like fetchsize
> and
> | selectmode..
> |
> |
> | If you do decide to use cursor mode with ResultSets defined as
> | TYPE_FORWARD_ONLY and either CONCUR_READONLY or CONCUR_UPDATABLE, then
> you,
> | you can additionally test various values for the fetch size to tune the
> | performance. Otherwise, your fetch size will only be 1.
> |
> |
> |
> You can set the fetch size using the setFetchSize() method from your
> Statement/PreparedStatement/CallableStatement object. SelectMethod can be
> set to either Direct or Cursor, and this is done in the connection string:
> SelectMethod=cursor
> or
> SelectMethod=direct
> The default is Direct.
> Carb Simien, MCSE MCDBA MCAD
> Microsoft Developer Support - Web Data
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Are you secure? For information about the Strategic Technology Protection
> Program and to order your FREE Security Tool Kit, please visit
> http://www.microsoft.com/security.
>
|||Chinn:
Yes, this is correct -- see below a quote from the Connection String
Properties section of the docs.
===
Direct-The direct method sends the complete result set in one
request to the driver. It is useful for queries that only produce a
small amount of data that you fetch completely. You should avoid
using direct when executing queries that produce a large amount
of data, as the result set is cached completely on the client and
constrains memory. In this mode, each statement requires its own
connection to the database. This is accomplished by "cloning"
connections. Cloned connections use the same connection
properties as the original connection; however, because
transactions must occur on a single connection, auto commit
mode is required. Due to this, JTA is not supported in direct mode.
In addition, some operations, such as updating an insensitive
result set, are not supported in direct mode because the driver
must create a second statement internally. Exceptions generated
due to the creation of cloned statements usually return an error
message similar to "Cannot start a cloned connection while in
manual transaction mode."
===
-shelby
Shelby Goerlitz
Microsoft, SQL Server
"chinn" <chinn@.discussions.microsoft.com> wrote in message
news:BDF3530D-CF92-43E6-85B2-9E0D0DAFF400@.microsoft.com...[vbcol=seagreen]
> Carb,
> But somebody is saying this..
> Is this correct..
> Microsoft JDBC driver doesn't support 'DIRECT' select mode for
> XADatasources
> (XA database connections). So we have to use cursor method.
> When I tried to switch to Direct, App didn't start with the error that
> 'direct' select method can not be used for XA Connections.
> thanks,
> ""Carb Simien [MSFT]"" wrote:
|||Shelby
Thanks Very Much For Explaining that.
Are there any settings we can tweak when using SelectMethod=Cursor..IF so
what are good and bad
Thanks,
"Shelby Goerlitz [MSFT]" wrote:

> Chinn:
> Yes, this is correct -- see below a quote from the Connection String
> Properties section of the docs.
> ===
> Direct-The direct method sends the complete result set in one
> request to the driver. It is useful for queries that only produce a
> small amount of data that you fetch completely. You should avoid
> using direct when executing queries that produce a large amount
> of data, as the result set is cached completely on the client and
> constrains memory. In this mode, each statement requires its own
> connection to the database. This is accomplished by "cloning"
> connections. Cloned connections use the same connection
> properties as the original connection; however, because
> transactions must occur on a single connection, auto commit
> mode is required. Due to this, JTA is not supported in direct mode.
> In addition, some operations, such as updating an insensitive
> result set, are not supported in direct mode because the driver
> must create a second statement internally. Exceptions generated
> due to the creation of cloned statements usually return an error
> message similar to "Cannot start a cloned connection while in
> manual transaction mode."
> ===
> -shelby
> Shelby Goerlitz
> Microsoft, SQL Server
>
> "chinn" <chinn@.discussions.microsoft.com> wrote in message
> news:BDF3530D-CF92-43E6-85B2-9E0D0DAFF400@.microsoft.com...
>
>
|||Chinn:
The main tweak w/ cursor mode is the setFetchSize setting discussed below..
-shelby
Shelby Goerlitz
Microsoft, SQL Server
"chinn" <chinn@.discussions.microsoft.com> wrote in message
news:93F68045-55C3-4276-AA2D-70B47877A26A@.microsoft.com...[vbcol=seagreen]
> Shelby
> Thanks Very Much For Explaining that.
> Are there any settings we can tweak when using SelectMethod=Cursor..IF so
> what are good and bad
>
> Thanks,
> "Shelby Goerlitz [MSFT]" wrote:
|||Hi Shelby..
Thanks For Your Pateince..
Can you tell me what do i set the value of fetchsize and what are the pro's
and con's
of that or is there a link where i can look up this information ..would be
great
if you can help me with this..
Thx
"Shelby Goerlitz [MSFT]" wrote:

> Chinn:
> The main tweak w/ cursor mode is the setFetchSize setting discussed below..
> -shelby
> Shelby Goerlitz
> Microsoft, SQL Server
> "chinn" <chinn@.discussions.microsoft.com> wrote in message
> news:93F68045-55C3-4276-AA2D-70B47877A26A@.microsoft.com...
>
>
|||Hello Chinn,
The setFetchSize setting allows you to fetch more rows at a time. Setting
it to a non-default value (larger than 1) will result in fewer calls to
sp_cursorfetch. You can reduce the number of round-trips to the database by
doing so. If you will be fetching a lot of rows this could significantly
improve the performance of your queries.
You will probably have to experiment with the right setting for you but,
for example, if your application typically works with 10 rows at a time,
you might want to set it to 10, etc.
Thanks,
Kamil
Kamil Sykora
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/securXity.
| From: "=?Utf-8?B?Y2hpbm4=?=" <chinn@.discussions.microsoft.com>
| Subject: Re: JDBC Driver Settings
| Date: Wed, 4 May 2005 18:49:13 -0700
| Lines: 195
|
| Hi Shelby..
|
| Thanks For Your Pateince..
| Can you tell me what do i set the value of fetchsize and what are the
pro's
| and con's
| of that or is there a link where i can look up this information ..would
be
| great
| if you can help me with this..
|
| Thx
|
| "Shelby Goerlitz [MSFT]" wrote:
|
| > Chinn:
| >
| > The main tweak w/ cursor mode is the setFetchSize setting discussed
below..
| >
| > -shelby
| >
| > Shelby Goerlitz
| > Microsoft, SQL Server
| >
| > "chinn" <chinn@.discussions.microsoft.com> wrote in message
| > news:93F68045-55C3-4276-AA2D-70B47877A26A@.microsoft.com...
| > > Shelby
| > >
| > > Thanks Very Much For Explaining that.
| > > Are there any settings we can tweak when using
SelectMethod=Cursor..IF so
| > > what are good and bad
| > >
| > >
| > > Thanks,
| > >
| > > "Shelby Goerlitz [MSFT]" wrote:
| > >
| > >> Chinn:
| > >>
| > >> Yes, this is correct -- see below a quote from the Connection String
| > >> Properties section of the docs.
| > >>
| > >> ===
| > >> Direct-The direct method sends the complete result set in one
| > >>
| > >> request to the driver. It is useful for queries that only produce a
| > >>
| > >> small amount of data that you fetch completely. You should avoid
| > >>
| > >> using direct when executing queries that produce a large amount
| > >>
| > >> of data, as the result set is cached completely on the client and
| > >>
| > >> constrains memory. In this mode, each statement requires its own
| > >>
| > >> connection to the database. This is accomplished by "cloning"
| > >>
| > >> connections. Cloned connections use the same connection
| > >>
| > >> properties as the original connection; however, because
| > >>
| > >> transactions must occur on a single connection, auto commit
| > >>
| > >> mode is required. Due to this, JTA is not supported in direct mode.
| > >>
| > >> In addition, some operations, such as updating an insensitive
| > >>
| > >> result set, are not supported in direct mode because the driver
| > >>
| > >> must create a second statement internally. Exceptions generated
| > >>
| > >> due to the creation of cloned statements usually return an error
| > >>
| > >> message similar to "Cannot start a cloned connection while in
| > >>
| > >> manual transaction mode."
| > >>
| > >> ===
| > >>
| > >> -shelby
| > >>
| > >> Shelby Goerlitz
| > >> Microsoft, SQL Server
| > >>
| > >>
| > >>
| > >> "chinn" <chinn@.discussions.microsoft.com> wrote in message
| > >> news:BDF3530D-CF92-43E6-85B2-9E0D0DAFF400@.microsoft.com...
| > >> > Carb,
| > >> >
| > >> > But somebody is saying this..
| > >> >
| > >> > Is this correct..
| > >> >
| > >> > Microsoft JDBC driver doesn't support 'DIRECT' select mode for
| > >> > XADatasources
| > >> > (XA database connections). So we have to use cursor method.
| > >> > When I tried to switch to Direct, App didn't start with the error
that
| > >> > 'direct' select method can not be used for XA Connections.
| > >> >
| > >> > thanks,
| > >> >
| > >> > ""Carb Simien [MSFT]"" wrote:
| > >> >
| > >> >>
| > >> >> --
| > >> >> | Thread-Topic: JDBC Driver Settings
| > >> >> | thread-index: AcVKCoOrRDJWhsIWSTSRvlg4RlTrKQ==
| > >> >> | X-WBNR-Posting-Host: 12.214.173.82
| > >> >> | From: "=?Utf-8?B?Y2hpbm4=?=" <chinn@.discussions.microsoft.com>
| > >> >> | Subject: JDBC Driver Settings
| > >> >> | Date: Mon, 25 Apr 2005 19:49:06 -0700
| > >> >> | Lines: 20
| > >> >> | Message-ID: <7959E226-7B55-4C1E-BD44-F74307B59BF2@.microsoft.com>
| > >> >> | MIME-Version: 1.0
| > >> >> | Content-Type: text/plain;
| > >> >> | charset="Utf-8"
| > >> >> | Content-Transfer-Encoding: 7bit
| > >> >> | X-Newsreader: Microsoft CDO for Windows 2000
| > >> >> | Content-Class: urn:content-classes:message
| > >> >> | Importance: normal
| > >> >> | Priority: normal
| > >> >> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| > >> >> | Newsgroups: microsoft.public.sqlserver.jdbcdriver
| > >> >> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| > >> >> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| > >> >> | Xref: TK2MSFTNGXA01.phx.gbl
| > >> >> microsoft.public.sqlserver.jdbcdriver:1049
| > >> >> | X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
| > >> >> |
| > >> >> | Hello,
| > >> >> | I saw the below in one of settings..where do i find them and
where
| > >> >> are
| > >> >> they
| > >> >> | defined..
| > >> >> |
| > >> >> |
| > >> >> | see lot of select statements with sp_cursoropen with thousands
of
| > >> >> reads
| > >> >> in
| > >> >> | the
| > >> >> | profiler but if i take the query and run it in the QA it will
be 1/4
| > >> >> of
| > >> >> | those reads .I
| > >> >> | am wondering we will have to tweak somethings like you mentioned
| > >> >> below.Could
| > >> >> | you help me to find out where i could find those settings like
| > >> >> fetchsize
| > >> >> and
| > >> >> | selectmode..
| > >> >> |
| > >> >> |
| > >> >> | If you do decide to use cursor mode with ResultSets defined as
| > >> >> | TYPE_FORWARD_ONLY and either CONCUR_READONLY or
CONCUR_UPDATABLE,
| > >> >> then
| > >> >> you,
| > >> >> | you can additionally test various values for the fetch size to
tune
| > >> >> the
| > >> >> | performance. Otherwise, your fetch size will only be 1.
| > >> >> |
| > >> >> |
| > >> >> |
| > >> >>
| > >> >> You can set the fetch size using the setFetchSize() method from
your
| > >> >> Statement/PreparedStatement/CallableStatement object.
SelectMethod
| > >> >> can
| > >> >> be
| > >> >> set to either Direct or Cursor, and this is done in the connection
| > >> >> string:
| > >> >>
| > >> >> SelectMethod=cursor
| > >> >>
| > >> >> or
| > >> >>
| > >> >> SelectMethod=direct
| > >> >>
| > >> >> The default is Direct.
| > >> >>
| > >> >> Carb Simien, MCSE MCDBA MCAD
| > >> >> Microsoft Developer Support - Web Data
| > >> >>
| > >> >> Please reply only to the newsgroups.
| > >> >> This posting is provided "AS IS" with no warranties, and confers
no
| > >> >> rights.
| > >> >>
| > >> >> Are you secure? For information about the Strategic Technology
| > >> >> Protection
| > >> >> Program and to order your FREE Security Tool Kit, please visit
| > >> >> http://www.microsoft.com/security.
| > >> >>
| > >> >>
| > >>
| > >>
| > >>
| >
| >
| >
|