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
Showing posts with label limitation. Show all posts
Showing posts with label limitation. Show all posts
Monday, March 26, 2012
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
>>
>>.
>>
>>.
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
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
Monday, March 19, 2012
Job history - After the limitation 0f 1000 entries
Hi,
Can some tell me what the behaviour on the refreshment on the job history
when it reaches the limitation of 1000 entries?
Is it flushing all ( and start over from scratch ) or does it use FIFO (
first in first out ).
Guus Kramer
FIFO
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Guus Kramer" <Guus Kramer@.discussions.microsoft.com> wrote in message
news:C24E8EF8-BCEE-4E44-94F7-8A06578504A9@.microsoft.com...
> Hi,
> Can some tell me what the behaviour on the refreshment on the job history
> when it reaches the limitation of 1000 entries?
> Is it flushing all ( and start over from scratch ) or does it use FIFO (
> first in first out ).
> Guus Kramer
|||Just wondering why the log history has vanished when no other actions have
occured. This happens on a random server and is not prodictable.
Any idea?
Guus
"Tibor Karaszi" wrote:
> FIFO
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Guus Kramer" <Guus Kramer@.discussions.microsoft.com> wrote in message
> news:C24E8EF8-BCEE-4E44-94F7-8A06578504A9@.microsoft.com...
>
|||Are you telling me that you get 0 from below query?
SELECT COUNT(*) FROM msdb.dbo.sysjobhistory
If so, you should find out who it emptying the job history. If not, then you have some job which is
executing frequently enough to hit the limit in a way that the other job's history is all aged out.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Guus Kramer" <GuusKramer@.discussions.microsoft.com> wrote in message
news:F9DC2715-4F0A-44C8-B8C9-84A8D054A819@.microsoft.com...[vbcol=seagreen]
> Just wondering why the log history has vanished when no other actions have
> occured. This happens on a random server and is not prodictable.
> Any idea?
> Guus
> "Tibor Karaszi" wrote:
|||The job history table is also limited by space in msdb. Expand the msdb
database manually and you will hold a longer job history.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Guus Kramer" <GuusKramer@.discussions.microsoft.com> wrote in message
news:F9DC2715-4F0A-44C8-B8C9-84A8D054A819@.microsoft.com...[vbcol=seagreen]
> Just wondering why the log history has vanished when no other actions have
> occured. This happens on a random server and is not prodictable.
> Any idea?
> Guus
> "Tibor Karaszi" wrote:
|||Tibor / Geoff,
You're both right...
SELECT COUNT(*) FROM msdb.dbo.sysjobhistory
contains 1000 entries but this is serverwide. on some servers 40 to 50 jobs
are running and some of them each 5 minutes. So the job history can be aged
out ( and dissapear ) for some jobs when logging other history.
Expanding the msdb database seems to be the only option on this.
Thank you guys for giving me input on this.
Guus Kramer
The Netherlands
"Guus Kramer" wrote:
[vbcol=seagreen]
> Just wondering why the log history has vanished when no other actions have
> occured. This happens on a random server and is not prodictable.
> Any idea?
> Guus
> "Tibor Karaszi" wrote:
|||... and configure Agent to keep > 1000 rows totally...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Guus Kramer" <GuusKramer@.discussions.microsoft.com> wrote in message
news:8F7E2067-45C6-48B0-85C0-00297BCD078E@.microsoft.com...[vbcol=seagreen]
> Tibor / Geoff,
> You're both right...
> SELECT COUNT(*) FROM msdb.dbo.sysjobhistory
> contains 1000 entries but this is serverwide. on some servers 40 to 50 jobs
> are running and some of them each 5 minutes. So the job history can be aged
> out ( and dissapear ) for some jobs when logging other history.
> Expanding the msdb database seems to be the only option on this.
> Thank you guys for giving me input on this.
> Guus Kramer
> The Netherlands
> "Guus Kramer" wrote:
Can some tell me what the behaviour on the refreshment on the job history
when it reaches the limitation of 1000 entries?
Is it flushing all ( and start over from scratch ) or does it use FIFO (
first in first out ).
Guus Kramer
FIFO
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Guus Kramer" <Guus Kramer@.discussions.microsoft.com> wrote in message
news:C24E8EF8-BCEE-4E44-94F7-8A06578504A9@.microsoft.com...
> Hi,
> Can some tell me what the behaviour on the refreshment on the job history
> when it reaches the limitation of 1000 entries?
> Is it flushing all ( and start over from scratch ) or does it use FIFO (
> first in first out ).
> Guus Kramer
|||Just wondering why the log history has vanished when no other actions have
occured. This happens on a random server and is not prodictable.
Any idea?
Guus
"Tibor Karaszi" wrote:
> FIFO
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Guus Kramer" <Guus Kramer@.discussions.microsoft.com> wrote in message
> news:C24E8EF8-BCEE-4E44-94F7-8A06578504A9@.microsoft.com...
>
|||Are you telling me that you get 0 from below query?
SELECT COUNT(*) FROM msdb.dbo.sysjobhistory
If so, you should find out who it emptying the job history. If not, then you have some job which is
executing frequently enough to hit the limit in a way that the other job's history is all aged out.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Guus Kramer" <GuusKramer@.discussions.microsoft.com> wrote in message
news:F9DC2715-4F0A-44C8-B8C9-84A8D054A819@.microsoft.com...[vbcol=seagreen]
> Just wondering why the log history has vanished when no other actions have
> occured. This happens on a random server and is not prodictable.
> Any idea?
> Guus
> "Tibor Karaszi" wrote:
|||The job history table is also limited by space in msdb. Expand the msdb
database manually and you will hold a longer job history.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Guus Kramer" <GuusKramer@.discussions.microsoft.com> wrote in message
news:F9DC2715-4F0A-44C8-B8C9-84A8D054A819@.microsoft.com...[vbcol=seagreen]
> Just wondering why the log history has vanished when no other actions have
> occured. This happens on a random server and is not prodictable.
> Any idea?
> Guus
> "Tibor Karaszi" wrote:
|||Tibor / Geoff,
You're both right...
SELECT COUNT(*) FROM msdb.dbo.sysjobhistory
contains 1000 entries but this is serverwide. on some servers 40 to 50 jobs
are running and some of them each 5 minutes. So the job history can be aged
out ( and dissapear ) for some jobs when logging other history.
Expanding the msdb database seems to be the only option on this.
Thank you guys for giving me input on this.
Guus Kramer
The Netherlands
"Guus Kramer" wrote:
[vbcol=seagreen]
> Just wondering why the log history has vanished when no other actions have
> occured. This happens on a random server and is not prodictable.
> Any idea?
> Guus
> "Tibor Karaszi" wrote:
|||... and configure Agent to keep > 1000 rows totally...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Guus Kramer" <GuusKramer@.discussions.microsoft.com> wrote in message
news:8F7E2067-45C6-48B0-85C0-00297BCD078E@.microsoft.com...[vbcol=seagreen]
> Tibor / Geoff,
> You're both right...
> SELECT COUNT(*) FROM msdb.dbo.sysjobhistory
> contains 1000 entries but this is serverwide. on some servers 40 to 50 jobs
> are running and some of them each 5 minutes. So the job history can be aged
> out ( and dissapear ) for some jobs when logging other history.
> Expanding the msdb database seems to be the only option on this.
> Thank you guys for giving me input on this.
> Guus Kramer
> The Netherlands
> "Guus Kramer" wrote:
Subscribe to:
Posts (Atom)