Job to truncate database records more than 15 days old

I have a SQL Server 2K db with some 10 tables and I want to setup a nightly job to truncate all db records which are more than 15 days old. Can anyone provide me with steps involved? Any help will be highly appreciated.You do have a datetime field with the record createion date on the tables, right?

And you do not have any relational integrity that may get in your way?|||Thats correct.|||Then create a stored procedure perform,ing the delete and let it run nightly. SQL Server Assistant has to be running for this to happen. Pretty staraightforward.sql

Job Failure

From the job history, we find that a scheduled job has failed after we have
updated the structure of some tables.
We would like to know what is the best way to find out what causes the
failure ? Is there any built-in error log ?
Thanks
Jason,
In SQLAgent, right click the job and select View Job History..., tick
the show step details checkbox at the top right. Navigate to the step
that failed and the error message should present itself in the bottom pane.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Jason wrote:
> From the job history, we find that a scheduled job has failed after we have
> updated the structure of some tables.
> We would like to know what is the best way to find out what causes the
> failure ? Is there any built-in error log ?
> Thanks
>
|||The job is written by consultant. From memory, the message is something
like "It takes xx second to finish Task 1. It takes yy seconds to finish
Task 2. ...... Job failed".
Jason
"Mark Allison" <mark@.no.tinned.meat.mvps.org> wrote in message
news:eMd$pWqYFHA.580@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Jason,
> In SQLAgent, right click the job and select View Job History..., tick the
> show step details checkbox at the top right. Navigate to the step that
> failed and the error message should present itself in the bottom pane.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
>
> Jason wrote:
|||"Show step details" checkbox. This is essential!
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jason" <anonymous@.discussions.microsoft.com> wrote in message
news:OtaCYYqYFHA.1028@.TK2MSFTNGP10.phx.gbl...
> The job is written by consultant. From memory, the message is something like "It takes xx second
> to finish Task 1. It takes yy seconds to finish Task 2. ...... Job failed".
> Jason
> "Mark Allison" <mark@.no.tinned.meat.mvps.org> wrote in message
> news:eMd$pWqYFHA.580@.TK2MSFTNGP15.phx.gbl...
>
|||Dear Tibor and Mark,
Thank you for your advice.
There is only 1 step. It seems that the contractor has written the message
out after finishing each step. In this way, it says "It takes xxx seconds
to finish Task 1 .. etc". However, since there are too many Tasks (around
150), in this way, at the last row, it shows "dot dot dot Job Failed" but I
am not able to see clearly which Task fails.
Thanking you in anticipation.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uEvUcdqYFHA.2588@.TK2MSFTNGP14.phx.gbl...
> "Show step details" checkbox. This is essential!
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jason" <anonymous@.discussions.microsoft.com> wrote in message
> news:OtaCYYqYFHA.1028@.TK2MSFTNGP10.phx.gbl...
>
|||Hi,
Try to look if external log has been turned on or not.
Go to the job's properties, choose the step, click 'Edit'
then choose 'Advanced' tab.
There would be 'Output file' field.
Check if it is set and if it is, then look at the file.
Regards.
"Jason" wrote:

> Dear Tibor and Mark,
> Thank you for your advice.
> There is only 1 step. It seems that the contractor has written the message
> out after finishing each step. In this way, it says "It takes xxx seconds
> to finish Task 1 .. etc". However, since there are too many Tasks (around
> 150), in this way, at the last row, it shows "dot dot dot Job Failed" but I
> am not able to see clearly which Task fails.
> Thanking you in anticipation.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:uEvUcdqYFHA.2588@.TK2MSFTNGP14.phx.gbl...
>
>
|||How about setting *in the job step* to output to a file? You can then open the file and see where
you get error messages etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jason" <anonymous@.discussions.microsoft.com> wrote in message
news:%23apBohqYFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Dear Tibor and Mark,
> Thank you for your advice.
> There is only 1 step. It seems that the contractor has written the message out after finishing
> each step. In this way, it says "It takes xxx seconds to finish Task 1 .. etc". However, since
> there are too many Tasks (around 150), in this way, at the last row, it shows "dot dot dot Job
> Failed" but I am not able to see clearly which Task fails.
> Thanking you in anticipation.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:uEvUcdqYFHA.2588@.TK2MSFTNGP14.phx.gbl...
>
|||Hi,
I find that there is no external log is enabled. I would like to follow
your suggestion. Where is the best place to put the external log ? I don't
want to mix it with other logs.
Thanks
"SkyWalker" <SkyWalker@.discussions.microsoft.com> wrote in message
news:DEA5C8F3-F87E-4707-8F2D-5406C86A7F19@.microsoft.com...[vbcol=seagreen]
> Hi,
> Try to look if external log has been turned on or not.
> Go to the job's properties, choose the step, click 'Edit'
> then choose 'Advanced' tab.
> There would be 'Output file' field.
> Check if it is set and if it is, then look at the file.
> Regards.
> "Jason" wrote:

Job Failure

From the job history, we find that a scheduled job has failed after we have
updated the structure of some tables.
We would like to know what is the best way to find out what causes the
failure ? Is there any built-in error log ?
ThanksJason,
In SQLAgent, right click the job and select View Job History..., tick
the show step details checkbox at the top right. Navigate to the step
that failed and the error message should present itself in the bottom pane.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Jason wrote:
> From the job history, we find that a scheduled job has failed after we hav
e
> updated the structure of some tables.
> We would like to know what is the best way to find out what causes the
> failure ? Is there any built-in error log ?
> Thanks
>|||The job is written by consultant. From memory, the message is something
like "It takes xx second to finish Task 1. It takes yy seconds to finish
Task 2. ...... Job failed".
Jason
"Mark Allison" <mark@.no.tinned.meat.mvps.org> wrote in message
news:eMd$pWqYFHA.580@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Jason,
> In SQLAgent, right click the job and select View Job History..., tick the
> show step details checkbox at the top right. Navigate to the step that
> failed and the error message should present itself in the bottom pane.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
>
> Jason wrote:|||"Show step details" checkbox. This is essential!
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jason" <anonymous@.discussions.microsoft.com> wrote in message
news:OtaCYYqYFHA.1028@.TK2MSFTNGP10.phx.gbl...
> The job is written by consultant. From memory, the message is something l
ike "It takes xx second
> to finish Task 1. It takes yy seconds to finish Task 2. ...... Job faile
d".
> Jason
> "Mark Allison" <mark@.no.tinned.meat.mvps.org> wrote in message
> news:eMd$pWqYFHA.580@.TK2MSFTNGP15.phx.gbl...
>|||Dear Tibor and Mark,
Thank you for your advice.
There is only 1 step. It seems that the contractor has written the message
out after finishing each step. In this way, it says "It takes xxx seconds
to finish Task 1 .. etc". However, since there are too many Tasks (around
150), in this way, at the last row, it shows "dot dot dot Job Failed" but I
am not able to see clearly which Task fails.
Thanking you in anticipation.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uEvUcdqYFHA.2588@.TK2MSFTNGP14.phx.gbl...
> "Show step details" checkbox. This is essential!
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jason" <anonymous@.discussions.microsoft.com> wrote in message
> news:OtaCYYqYFHA.1028@.TK2MSFTNGP10.phx.gbl...
>|||Hi,
Try to look if external log has been turned on or not.
Go to the job's properties, choose the step, click 'Edit'
then choose 'Advanced' tab.
There would be 'Output file' field.
Check if it is set and if it is, then look at the file.
Regards.
"Jason" wrote:

> Dear Tibor and Mark,
> Thank you for your advice.
> There is only 1 step. It seems that the contractor has written the messag
e
> out after finishing each step. In this way, it says "It takes xxx seconds
> to finish Task 1 .. etc". However, since there are too many Tasks (around
> 150), in this way, at the last row, it shows "dot dot dot Job Failed" but
I
> am not able to see clearly which Task fails.
> Thanking you in anticipation.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:uEvUcdqYFHA.2588@.TK2MSFTNGP14.phx.gbl...
>
>|||How about setting *in the job step* to output to a file? You can then open t
he file and see where
you get error messages etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jason" <anonymous@.discussions.microsoft.com> wrote in message
news:%23apBohqYFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Dear Tibor and Mark,
> Thank you for your advice.
> There is only 1 step. It seems that the contractor has written the messag
e out after finishing
> each step. In this way, it says "It takes xxx seconds to finish Task 1 ..
etc". However, since
> there are too many Tasks (around 150), in this way, at the last row, it sh
ows "dot dot dot Job
> Failed" but I am not able to see clearly which Task fails.
> Thanking you in anticipation.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:uEvUcdqYFHA.2588@.TK2MSFTNGP14.phx.gbl...
>|||Hi,
I find that there is no external log is enabled. I would like to follow
your suggestion. Where is the best place to put the external log ? I don't
want to mix it with other logs.
Thanks
"SkyWalker" <SkyWalker@.discussions.microsoft.com> wrote in message
news:DEA5C8F3-F87E-4707-8F2D-5406C86A7F19@.microsoft.com...[vbcol=seagreen]
> Hi,
> Try to look if external log has been turned on or not.
> Go to the job's properties, choose the step, click 'Edit'
> then choose 'Advanced' tab.
> There would be 'Output file' field.
> Check if it is set and if it is, then look at the file.
> Regards.
> "Jason" wrote:
>

Job Failure

From the job history, we find that a scheduled job has failed after we have
updated the structure of some tables.
We would like to know what is the best way to find out what causes the
failure ? Is there any built-in error log ?
ThanksJason,
In SQLAgent, right click the job and select View Job History..., tick
the show step details checkbox at the top right. Navigate to the step
that failed and the error message should present itself in the bottom pane.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Jason wrote:
> From the job history, we find that a scheduled job has failed after we have
> updated the structure of some tables.
> We would like to know what is the best way to find out what causes the
> failure ? Is there any built-in error log ?
> Thanks
>|||The job is written by consultant. From memory, the message is something
like "It takes xx second to finish Task 1. It takes yy seconds to finish
Task 2. ...... Job failed".
Jason
"Mark Allison" <mark@.no.tinned.meat.mvps.org> wrote in message
news:eMd$pWqYFHA.580@.TK2MSFTNGP15.phx.gbl...
> Jason,
> In SQLAgent, right click the job and select View Job History..., tick the
> show step details checkbox at the top right. Navigate to the step that
> failed and the error message should present itself in the bottom pane.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
>
> Jason wrote:
>> From the job history, we find that a scheduled job has failed after we
>> have updated the structure of some tables.
>> We would like to know what is the best way to find out what causes the
>> failure ? Is there any built-in error log ?
>> Thanks|||"Show step details" checkbox. This is essential!
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jason" <anonymous@.discussions.microsoft.com> wrote in message
news:OtaCYYqYFHA.1028@.TK2MSFTNGP10.phx.gbl...
> The job is written by consultant. From memory, the message is something like "It takes xx second
> to finish Task 1. It takes yy seconds to finish Task 2. ...... Job failed".
> Jason
> "Mark Allison" <mark@.no.tinned.meat.mvps.org> wrote in message
> news:eMd$pWqYFHA.580@.TK2MSFTNGP15.phx.gbl...
>> Jason,
>> In SQLAgent, right click the job and select View Job History..., tick the show step details
>> checkbox at the top right. Navigate to the step that failed and the error message should present
>> itself in the bottom pane.
>> --
>> Mark Allison, SQL Server MVP
>> http://www.markallison.co.uk
>> Looking for a SQL Server replication book?
>> http://www.nwsu.com/0974973602m.html
>>
>>
>> Jason wrote:
>> From the job history, we find that a scheduled job has failed after we have updated the
>> structure of some tables.
>> We would like to know what is the best way to find out what causes the failure ? Is there any
>> built-in error log ?
>> Thanks
>|||Dear Tibor and Mark,
Thank you for your advice.
There is only 1 step. It seems that the contractor has written the message
out after finishing each step. In this way, it says "It takes xxx seconds
to finish Task 1 .. etc". However, since there are too many Tasks (around
150), in this way, at the last row, it shows "dot dot dot Job Failed" but I
am not able to see clearly which Task fails.
Thanking you in anticipation.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uEvUcdqYFHA.2588@.TK2MSFTNGP14.phx.gbl...
> "Show step details" checkbox. This is essential!
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jason" <anonymous@.discussions.microsoft.com> wrote in message
> news:OtaCYYqYFHA.1028@.TK2MSFTNGP10.phx.gbl...
>> The job is written by consultant. From memory, the message is something
>> like "It takes xx second to finish Task 1. It takes yy seconds to finish
>> Task 2. ...... Job failed".
>> Jason
>> "Mark Allison" <mark@.no.tinned.meat.mvps.org> wrote in message
>> news:eMd$pWqYFHA.580@.TK2MSFTNGP15.phx.gbl...
>> Jason,
>> In SQLAgent, right click the job and select View Job History..., tick
>> the show step details checkbox at the top right. Navigate to the step
>> that failed and the error message should present itself in the bottom
>> pane.
>> --
>> Mark Allison, SQL Server MVP
>> http://www.markallison.co.uk
>> Looking for a SQL Server replication book?
>> http://www.nwsu.com/0974973602m.html
>>
>>
>> Jason wrote:
>> From the job history, we find that a scheduled job has failed after we
>> have updated the structure of some tables.
>> We would like to know what is the best way to find out what causes the
>> failure ? Is there any built-in error log ?
>> Thanks
>>
>|||Hi,
Try to look if external log has been turned on or not.
Go to the job's properties, choose the step, click 'Edit'
then choose 'Advanced' tab.
There would be 'Output file' field.
Check if it is set and if it is, then look at the file.
Regards.
"Jason" wrote:
> Dear Tibor and Mark,
> Thank you for your advice.
> There is only 1 step. It seems that the contractor has written the message
> out after finishing each step. In this way, it says "It takes xxx seconds
> to finish Task 1 .. etc". However, since there are too many Tasks (around
> 150), in this way, at the last row, it shows "dot dot dot Job Failed" but I
> am not able to see clearly which Task fails.
> Thanking you in anticipation.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:uEvUcdqYFHA.2588@.TK2MSFTNGP14.phx.gbl...
> > "Show step details" checkbox. This is essential!
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Jason" <anonymous@.discussions.microsoft.com> wrote in message
> > news:OtaCYYqYFHA.1028@.TK2MSFTNGP10.phx.gbl...
> >> The job is written by consultant. From memory, the message is something
> >> like "It takes xx second to finish Task 1. It takes yy seconds to finish
> >> Task 2. ...... Job failed".
> >>
> >> Jason
> >>
> >> "Mark Allison" <mark@.no.tinned.meat.mvps.org> wrote in message
> >> news:eMd$pWqYFHA.580@.TK2MSFTNGP15.phx.gbl...
> >> Jason,
> >>
> >> In SQLAgent, right click the job and select View Job History..., tick
> >> the show step details checkbox at the top right. Navigate to the step
> >> that failed and the error message should present itself in the bottom
> >> pane.
> >>
> >> --
> >> Mark Allison, SQL Server MVP
> >> http://www.markallison.co.uk
> >>
> >> Looking for a SQL Server replication book?
> >> http://www.nwsu.com/0974973602m.html
> >>
> >>
> >>
> >>
> >>
> >> Jason wrote:
> >> From the job history, we find that a scheduled job has failed after we
> >> have updated the structure of some tables.
> >>
> >> We would like to know what is the best way to find out what causes the
> >> failure ? Is there any built-in error log ?
> >>
> >> Thanks
> >>
> >>
> >
> >
>
>|||How about setting *in the job step* to output to a file? You can then open the file and see where
you get error messages etc.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jason" <anonymous@.discussions.microsoft.com> wrote in message
news:%23apBohqYFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Dear Tibor and Mark,
> Thank you for your advice.
> There is only 1 step. It seems that the contractor has written the message out after finishing
> each step. In this way, it says "It takes xxx seconds to finish Task 1 .. etc". However, since
> there are too many Tasks (around 150), in this way, at the last row, it shows "dot dot dot Job
> Failed" but I am not able to see clearly which Task fails.
> Thanking you in anticipation.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:uEvUcdqYFHA.2588@.TK2MSFTNGP14.phx.gbl...
>> "Show step details" checkbox. This is essential!
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Jason" <anonymous@.discussions.microsoft.com> wrote in message
>> news:OtaCYYqYFHA.1028@.TK2MSFTNGP10.phx.gbl...
>> The job is written by consultant. From memory, the message is something like "It takes xx
>> second to finish Task 1. It takes yy seconds to finish Task 2. ...... Job failed".
>> Jason
>> "Mark Allison" <mark@.no.tinned.meat.mvps.org> wrote in message
>> news:eMd$pWqYFHA.580@.TK2MSFTNGP15.phx.gbl...
>> Jason,
>> In SQLAgent, right click the job and select View Job History..., tick the show step details
>> checkbox at the top right. Navigate to the step that failed and the error message should
>> present itself in the bottom pane.
>> --
>> Mark Allison, SQL Server MVP
>> http://www.markallison.co.uk
>> Looking for a SQL Server replication book?
>> http://www.nwsu.com/0974973602m.html
>>
>>
>> Jason wrote:
>> From the job history, we find that a scheduled job has failed after we have updated the
>> structure of some tables.
>> We would like to know what is the best way to find out what causes the failure ? Is there any
>> built-in error log ?
>> Thanks
>>
>>
>|||Hi,
I find that there is no external log is enabled. I would like to follow
your suggestion. Where is the best place to put the external log ? I don't
want to mix it with other logs.
Thanks
"SkyWalker" <SkyWalker@.discussions.microsoft.com> wrote in message
news:DEA5C8F3-F87E-4707-8F2D-5406C86A7F19@.microsoft.com...
> Hi,
> Try to look if external log has been turned on or not.
> Go to the job's properties, choose the step, click 'Edit'
> then choose 'Advanced' tab.
> There would be 'Output file' field.
> Check if it is set and if it is, then look at the file.
> Regards.
> "Jason" wrote:
>> Dear Tibor and Mark,
>> Thank you for your advice.
>> There is only 1 step. It seems that the contractor has written the
>> message
>> out after finishing each step. In this way, it says "It takes xxx
>> seconds
>> to finish Task 1 .. etc". However, since there are too many Tasks
>> (around
>> 150), in this way, at the last row, it shows "dot dot dot Job Failed" but
>> I
>> am not able to see clearly which Task fails.
>> Thanking you in anticipation.
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in
>> message news:uEvUcdqYFHA.2588@.TK2MSFTNGP14.phx.gbl...
>> > "Show step details" checkbox. This is essential!
>> >
>> > --
>> > Tibor Karaszi, SQL Server MVP
>> > http://www.karaszi.com/sqlserver/default.asp
>> > http://www.solidqualitylearning.com/
>> >
>> >
>> > "Jason" <anonymous@.discussions.microsoft.com> wrote in message
>> > news:OtaCYYqYFHA.1028@.TK2MSFTNGP10.phx.gbl...
>> >> The job is written by consultant. From memory, the message is
>> >> something
>> >> like "It takes xx second to finish Task 1. It takes yy seconds to
>> >> finish
>> >> Task 2. ...... Job failed".
>> >>
>> >> Jason
>> >>
>> >> "Mark Allison" <mark@.no.tinned.meat.mvps.org> wrote in message
>> >> news:eMd$pWqYFHA.580@.TK2MSFTNGP15.phx.gbl...
>> >> Jason,
>> >>
>> >> In SQLAgent, right click the job and select View Job History..., tick
>> >> the show step details checkbox at the top right. Navigate to the step
>> >> that failed and the error message should present itself in the bottom
>> >> pane.
>> >>
>> >> --
>> >> Mark Allison, SQL Server MVP
>> >> http://www.markallison.co.uk
>> >>
>> >> Looking for a SQL Server replication book?
>> >> http://www.nwsu.com/0974973602m.html
>> >>
>> >>
>> >>
>> >>
>> >>
>> >> Jason wrote:
>> >> From the job history, we find that a scheduled job has failed after
>> >> we
>> >> have updated the structure of some tables.
>> >>
>> >> We would like to know what is the best way to find out what causes
>> >> the
>> >> failure ? Is there any built-in error log ?
>> >>
>> >> Thanks
>> >>
>> >>
>> >
>> >
>>

job fails when run with sqlagent.

I have a storeded procedure 'refresh_all' that denormalizes some tables for
easier reporting. I can run it fine from isqlw command window, but when I
put it into a job it consistently fails.
I'm probably over looking something really basic.
Here is the error from the single step
Executed as user: son\kevinrug. String or binary data would be truncated.
[SQLSTATE 22001] (Error 8152) The statement has been terminated. [SQLSTATE
01000] (Error 3621). The step failed.
At first I thought it was the user account, but I verified the it has
permissions in the database and on the procedure. Beside, I am system admin
and dbo so that shouldn't have been a problem.
Also, I am starting the service (sqlagent) under my network account.
Help greatly appreciated.
kevin
>> ... String or binary data would be truncated.
The error message suggests it has to do with the data in the tables
involved. Check your processes for datatype conversions, lengthy character
data inserted into tables with types of shorter length, mismatched sql
operations like joins on columns with incompatible types etc.
Anith
|||ok I'll look at that. I was wondering if this is what it meant, but since it
ran ok interactively I wasn't sure.
Also, will try running each sub-proc to see which is failing.
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:eoJP1GJEFHA.612@.TK2MSFTNGP15.phx.gbl...
> The error message suggests it has to do with the data in the tables
> involved. Check your processes for datatype conversions, lengthy character
> data inserted into tables with types of shorter length, mismatched sql
> operations like joins on columns with incompatible types etc.
> --
> Anith
>
|||Thanks again.
After investigating, I found I had turned ansi_warnings OFF, once I put this
in the procedure it runs great.
kevin
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:eoJP1GJEFHA.612@.TK2MSFTNGP15.phx.gbl...
> The error message suggests it has to do with the data in the tables
> involved. Check your processes for datatype conversions, lengthy character
> data inserted into tables with types of shorter length, mismatched sql
> operations like joins on columns with incompatible types etc.
> --
> Anith
>

job fails when run with sqlagent.

I have a storeded procedure 'refresh_all' that denormalizes some tables for
easier reporting. I can run it fine from isqlw command window, but when I
put it into a job it consistently fails.
I'm probably over looking something really basic.
Here is the error from the single step
Executed as user: son\kevinrug. String or binary data would be truncated.
[SQLSTATE 22001] (Error 8152) The statement has been terminated. [S
QLSTATE
01000] (Error 3621). The step failed.
At first I thought it was the user account, but I verified the it has
permissions in the database and on the procedure. Beside, I am system admin
and dbo so that shouldn't have been a problem.
Also, I am starting the service (sqlagent) under my network account.
Help greatly appreciated.
kevin>> ... String or binary data would be truncated.
The error message suggests it has to do with the data in the tables
involved. Check your processes for datatype conversions, lengthy character
data inserted into tables with types of shorter length, mismatched sql
operations like joins on columns with incompatible types etc.
Anith|||ok I'll look at that. I was wondering if this is what it meant, but since it
ran ok interactively I wasn't sure.
Also, will try running each sub-proc to see which is failing.
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:eoJP1GJEFHA.612@.TK2MSFTNGP15.phx.gbl...
> The error message suggests it has to do with the data in the tables
> involved. Check your processes for datatype conversions, lengthy character
> data inserted into tables with types of shorter length, mismatched sql
> operations like joins on columns with incompatible types etc.
> --
> Anith
>|||Thanks again.
After investigating, I found I had turned ansi_warnings OFF, once I put this
in the procedure it runs great.
kevin
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:eoJP1GJEFHA.612@.TK2MSFTNGP15.phx.gbl...
> The error message suggests it has to do with the data in the tables
> involved. Check your processes for datatype conversions, lengthy character
> data inserted into tables with types of shorter length, mismatched sql
> operations like joins on columns with incompatible types etc.
> --
> Anith
>

job fails when run with sqlagent.

I have a storeded procedure 'refresh_all' that denormalizes some tables for
easier reporting. I can run it fine from isqlw command window, but when I
put it into a job it consistently fails.
I'm probably over looking something really basic.
Here is the error from the single step
Executed as user: son\kevinrug. String or binary data would be truncated.
[SQLSTATE 22001] (Error 8152) The statement has been terminated. [SQLSTATE
01000] (Error 3621). The step failed.
At first I thought it was the user account, but I verified the it has
permissions in the database and on the procedure. Beside, I am system admin
and dbo so that shouldn't have been a problem.
Also, I am starting the service (sqlagent) under my network account.
Help greatly appreciated.
kevin>> ... String or binary data would be truncated.
The error message suggests it has to do with the data in the tables
involved. Check your processes for datatype conversions, lengthy character
data inserted into tables with types of shorter length, mismatched sql
operations like joins on columns with incompatible types etc.
--
Anith|||ok I'll look at that. I was wondering if this is what it meant, but since it
ran ok interactively I wasn't sure.
Also, will try running each sub-proc to see which is failing.
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:eoJP1GJEFHA.612@.TK2MSFTNGP15.phx.gbl...
> >> ... String or binary data would be truncated.
> The error message suggests it has to do with the data in the tables
> involved. Check your processes for datatype conversions, lengthy character
> data inserted into tables with types of shorter length, mismatched sql
> operations like joins on columns with incompatible types etc.
> --
> Anith
>|||Thanks again.
After investigating, I found I had turned ansi_warnings OFF, once I put this
in the procedure it runs great.
kevin
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:eoJP1GJEFHA.612@.TK2MSFTNGP15.phx.gbl...
> >> ... String or binary data would be truncated.
> The error message suggests it has to do with the data in the tables
> involved. Check your processes for datatype conversions, lengthy character
> data inserted into tables with types of shorter length, mismatched sql
> operations like joins on columns with incompatible types etc.
> --
> Anith
>

Job Execution Information not written to system tables

SS 2005 64Bit SP2

Hello Chaps

Intermittent problem with the SQL Agent job history not getting written to the history table. Background:
Today we noticed the account SQL Agent runs under cropping up in sp_who2. A quick check of the activity monitor said nothing was running. We ran a trace and, based on the SQL being executed, had a word with one of the developers who confirmed they had manually executed one of the jobs.

There was no record anywhere that the job had run. There has been an issue with this particular job, when executed by this user, not showing up in history before but, as mentioned, this had been intermittent and we thought that a restart of the service had sorted it.

Stuff run to try to track the job:
EXEC sp_help_jobactivity @.job_name = 'MyJob'

EXEC sp_help_jobhistory @.job_name = 'MyJob'

SELECT *
FROM dbo.sysjobhistory
WHERE job_id = 'MyJob GUID'

The first returned a row with no details in the columns indicating activity (e.g. last_executed_step_date and other columns were null).

sp_help_jobhistory had some historical records but nothing since mid last month.

sysjobhistory correlated with sp_help_jobhistory as you would expect.

Right clicking the job in SSMS and viewing history correlated with sp_help_jobhistory (i.e. some records but nothing since mid-June).

We edited the SQL in the job step and got the developer to rerun the job and, typically, everything appeared as it should in all the above result sets.
Obviously this is tricky to track down since it has been intermittent but does anyone recognise anything that I have described above? I have of course googled but there doesn't really seem to be anything about it.

We have considered there may be a problem in MSDB and may try running CHECKDB to see if anything comes up but somehow I doubt it will.

Ta!I have never seen this.

perhaps you dev was trying to clean up after himself? ;)|||I have never seen this.

perhaps you dev was trying to clean up after himself? ;)I'll take this opportunity to rebuke you for posting totally irrelevent information. Boo! :p

.....
and coincidently bump my post ;)|||I'll take this opportunity to rebuke you for posting totally irrelevent information. Boo! :p

.....
and coincidently bump my post ;)

What are your job history retention settings? What's the maximum number of rows of history total and per job?

By default, I believe that the settings for job history retention are 1000 rows total and 100 rows per job. It's one of the first things I change when setting up a new server. My "default" settings are 10,000 rows total and 1,000 rows per job. Sometimes I feel that even this is not enough.

Also, are there other jobs (such as replication) that run with a great deal of frequency? Like once per minute or so? This would have the tendency of "hogging" the history table.

Ta (or whatever it is you Brits say),

hmscott|||Thanks for responding :)

Yeah - our settings were at the default. However we don't run many jobs (no replication, no "sniffers" running every minute). I checked the number of records in the tables when we had the problem and neither the total nor per job numbers had quite hit the limit. I did up them just to be sure. AFAIK (and I would be shocked if it was otherwise) these work on a LIFO basis though anyway.

Whoop whooop (or whatever you Yanks say :D)|||Thanks for responding :)

You're welcome; I didn't see it the first time around or I would have said something.


AFAIK (and I would be shocked if it was otherwise) these work on a LIFO basis though anyway.

You are correct. I have, however, seen some situations where the history gets overwritten rather quickly. I wasn't certain how long after job execution you were looking in the history tables.

The only other thing I can suggest is to run a trace on a job that completes normally (and writes history to the history tables) and then run another trace on the job with which you are having issues.

Whoop whooop (or whatever you Yanks say :D)

No, no, you obviously have no understanding of us d@.mn Yankees. We say things like, "Reach for the sky, partner!" or "This here forum ain't big enough for the two of us" or, sometimes, "Make mine a double latte soy skinny harmless, and my friend Serge here will have a half-caf grande with legs. Oooo, is that diamond in your ear real?"

:D

Regards,

hmscott|||By the way poots, I wiki'd Pootle Flump and came across the following entry:

The Flumps (http://en.wikipedia.org/wiki/The_Flumps) :shocked:

Now I am afraid I will be having nightmares for the next month.

[shudder /]

:D

hmscott|||By the way poots, I wiki'd Pootle Flump and came across the following entry:

The Flumps (http://en.wikipedia.org/wiki/The_Flumps) :shocked:

Now I am afraid I will be having nightmares for the next month.

[shudder /]To help your nightmares along, I am the one in the white hat :)

Ta re trace. It is tricky catching it from the beginning since it is intermittent. We did run profiler whilst it was running this time but there seemed to be nothing unusual going on...

Job Change History

Anyone know how to tell who is changing a db maintenance plan? I found
the system tables in msdb but I don't see how to tell who is doing it.
Only when.
On Feb 26, 2:29Xam, songstre <songs...@.gmail.com> wrote:
> Anyone know how to tell who is changing a db maintenance plan? I found
> the system tables in msdb but I don't see how to tell who is doing it.
> Only when.
Please check who has invoked the job last time if somebody is
modifying the job the he may want to execute also . With the help of
job history you can find who has invoked the job last time.
Assuming the person doesn't have direct access to server.
Thanks
Ajay Rengunthwar
MCTS
|||On Feb 25, 11:03Xpm, Ajay Rengunthwar <aju...@.gmail.com> wrote:
> On Feb 26, 2:29Xam, songstre <songs...@.gmail.com> wrote:
>
> Please check who has invoked the job last time if somebody is
> modifying the job the he may want to execute also . With the help of
> job history you can find who has invoked the job last time.
> Assuming the person doesn't have direct access to server.
> Thanks
> Ajay Rengunthwar
> MCTS
It was last executed by NT AUTHORITY\SYSTEM on a schedule. So this
doesn't help much.
|||Hi,
I'm always wondering is it possible to check?
Regards,
anxcomp

Job Change History

Anyone know how to tell who is changing a db maintenance plan? I found
the system tables in msdb but I don't see how to tell who is doing it.
Only when.On Feb 26, 2:29=A0am, songstre <songs...@.gmail.com> wrote:
> Anyone know how to tell who is changing a db maintenance plan? I found
> the system tables in msdb but I don't see how to tell who is doing it.
> Only when.
Please check who has invoked the job last time if somebody is
modifying the job the he may want to execute also . With the help of
job history you can find who has invoked the job last time.
Assuming the person doesn't have direct access to server.
Thanks
Ajay Rengunthwar
MCTS|||On Feb 25, 11:03=A0pm, Ajay Rengunthwar <aju...@.gmail.com> wrote:
> On Feb 26, 2:29=A0am, songstre <songs...@.gmail.com> wrote:
> > Anyone know how to tell who is changing a db maintenance plan? I found
> > the system tables in msdb but I don't see how to tell who is doing it.
> > Only when.
> Please check who has invoked the job last time if somebody is
> modifying the job the he may want to execute also . With the help of
> job history you can find who has invoked the job last time.
> Assuming the person doesn't have direct access to server.
> Thanks
> Ajay Rengunthwar
> MCTS
It was last executed by NT AUTHORITY\SYSTEM on a schedule. So this
doesn't help much.|||Hi,
I'm always wondering is it possible to check?
--
Regards,
anxcomp

Job & Temporal tables

Hi everyone. Thanks in advance for any answer you can provide.
I got a sp with lots of temporal tables (not global, just #) and it runs ok
with osql. However when I schedule it in a job it returns an error that tell
s
me that #temp does not exist.
Someone told me that I need to create global temporary tables. Just wanna
check with you if this solution corrects the issue.It's pretty tough to tell without seeing exactly what you are doing. It is
certainly possible to create a temp table and use it from a scheduled job.
Can you post the actual code?
Andrew J. Kelly SQL MVP
"popootts" <popootts@.discussions.microsoft.com> wrote in message
news:3066A7C0-76AA-4521-8E76-2DE678091F17@.microsoft.com...
> Hi everyone. Thanks in advance for any answer you can provide.
> I got a sp with lots of temporal tables (not global, just #) and it runs
> ok
> with osql. However when I schedule it in a job it returns an error that
> tells
> me that #temp does not exist.
> Someone told me that I need to create global temporary tables. Just wanna
> check with you if this solution corrects the issue.|||>> I got sp with lots of temporal tables (not global, just #) .. <<
I think you meant TEMPORARY tables :)
And in the non-standard, non-portable T-SQL syntax, they disappear
after the module in whcih they were created closes.
wanna check with you if this solution corrects the issue. <<
That will work, but it is crappy programming. What you really want to
do it write is standard, portable code that uses derived tables, VIEWs
or simple queries.
Temp tables are a sign that the approach you are using is based on
procedural steps and not a declarative approach. Basically, in the
1960's each temp table would have been a scratch tape in a COBOL
program.
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications as to what you are actually trying to do.|||Hi again and thanks for the answer.
Here's a piece of code (too long to copy it all):
****************************************
***
CREATE procedure user_dbo.test_statistics @.p_date datetime
as
create table #tm0
(qid int, qmsg varchar(100), qdate datetime)
create table #tm1
(
qid int, qdate datetime, qcat varchar(9), qno int, qct int
)
insert into #tm1
select did, ddate, dcat, dno, dct from
all_values where ddate=dateadd(day,-4,@.p_date)
... more code...
****************************************
**************
then, when I execute this on osql it runs ok, however scheduled on a job it
returns the error thar #tmp1 does not exist.
Any help will be appreciated.
Regards
"popootts" wrote:

> Hi everyone. Thanks in advance for any answer you can provide.
> I got a sp with lots of temporal tables (not global, just #) and it runs o
k
> with osql. However when I schedule it in a job it returns an error that te
lls
> me that #temp does not exist.
> Someone told me that I need to create global temporary tables. Just wanna
> check with you if this solution corrects the issue.|||See if this helps:
http://tinyurl.com/67hsr
-oj
"popootts" <popootts@.discussions.microsoft.com> wrote in message
news:2BFDDE44-4932-45F0-9148-49F75C953239@.microsoft.com...
> Hi again and thanks for the answer.
> Here's a piece of code (too long to copy it all):
> ****************************************
***
> CREATE procedure user_dbo.test_statistics @.p_date datetime
> as
> create table #tm0
> (qid int, qmsg varchar(100), qdate datetime)
> create table #tm1
> (
> qid int, qdate datetime, qcat varchar(9), qno int, qct int
> )
> insert into #tm1
> select did, ddate, dcat, dno, dct from
> all_values where ddate=dateadd(day,-4,@.p_date)
> ... more code...
> ****************************************
**************
> then, when I execute this on osql it runs ok, however scheduled on a job
> it
> returns the error thar #tmp1 does not exist.
> Any help will be appreciated.
> Regards
>
> "popootts" wrote:
>|||Oj, thanks for the suggestion. Everything is normal on that side.
in my Sql Server EM I can schedule the job and run it without problems.
However in other EM (where the prod. version is in) when they check semantic
s
they get the error.
Since the temporary table is created while the sesion is running I don't
get why if fails on the syntax check. It makes no sense.
Thanks for any help .
"oj" wrote:

> See if this helps:
> http://tinyurl.com/67hsr
>
> --
> -oj
>
> "popootts" <popootts@.discussions.microsoft.com> wrote in message
> news:2BFDDE44-4932-45F0-9148-49F75C953239@.microsoft.com...
>
>

jet oledb from tsql

I wish to create a new access database each week whos name includes the
curent date, then to create 7 tables in the database eg '27 Jul 2006.mdb' on
e
for each day
I then wish to insert rows into these tables,
currently I use managed adox & ado to do this, is it possible to use tsql to
get
sql server write the files from its oledb drivers, and would there be any
problems
doing this
in particular I do not know what arguments to pass to opendatasource to
create a new database.Hello Nick,
I understand that you 'd like to create Access database and access it from
SQL Server via TSQL directly. If it is not correct, let me know.
You may want to create linked server to Access database or use open
openquery/openrowset or four-part name syntax to query access database.
Please refer to teh following links for more informaitoin:
http://msdn2.microsoft.com/en-us/library/ms190479.aspx
287414 You cannot link tables into a Microsoft Access project by using an
ODBC Data Source Name (DSN)
http://support.microsoft.com/defaul...kb;EN-US;287414
As for creating database, it is not feasible to create directly from
opendatasource etc from SQL Server. You need to use adox/ado to do this as
you'v done.
317881 How to create an Access database by using ADOX and Visual C# .NET
http://support.microsoft.com/defaul...kb;EN-US;317881
If you have further questions, please let's know.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Nick,
I understand that you 'd like to create Access database and access it from
SQL Server via TSQL directly. If it is not correct, let me know.
You may want to create linked server to Access database or use open
openquery/openrowset or four-part name syntax to query access database.
Please refer to teh following links for more informaitoin:
http://msdn2.microsoft.com/en-us/library/ms190479.aspx
287414 You cannot link tables into a Microsoft Access project by using an
ODBC Data Source Name (DSN)
http://support.microsoft.com/defaul...kb;EN-US;287414
As for creating database, it is not feasible to create directly from
opendatasource etc from SQL Server. You need to use adox/ado to do this as
you'v done.
317881 How to create an Access database by using ADOX and Visual C# .NET
http://support.microsoft.com/defaul...kb;EN-US;317881
If you have further questions, please let's know.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||I have posted following question in sqlprogramming but have
not recieved a response so wondered whether I have posted
it correctly
https://msdn.microsoft.com/newsgrou...59-dfac726d711e
Please help,
since the introduction of SQL2005 the default mode for opening access mdb
databases with Openrowset appears to be in exclusive open mode
I wish to open the database in share mode so the other (non net) ADO threads
can open the database at the same time
I only wish to open the database in read only mode
and so to use the mode param
I have tried the following
SELECT COUNT(*)
FROM
OPENROWSET ( 'Microsoft.Jet.OLEDB.4.0',
'"Database = h:\alarmlogfiles\new folder (3)\alarms 04 feb
2007.mdb; Mode = Share Deny None;"'
, 'SELECT [ID],
[System],
[Group],
[Message],
[Acknowledged],
[AckReq],
[Duplicates],
[Group Name],
[Importance],
[Ms],
[Alarm Date],
[LastDupe],
[Viewers],
[Row_Date],
[Colours],
[Status] From [Alarms 08 Feb 2007] Where 1 = 0' )
but get the following error from sql2005 & sql 2000
Server: Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider
"Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
returned message "Could not find installable ISAM.".

SELECT CustomerID, CompanyName
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';
'admin';'',Customers)
GO
but there is no opportunity here to add a open mode argument?
please help
best wishes nick
""privatenews"" wrote:
[vbcol=seagreen]
> Hello Nick,
> I understand that you 'd like to create Access database and access it from
> SQL Server via TSQL directly. If it is not correct, let me know.
> You may want to create linked server to Access database or use open
> openquery/openrowset or four-part name syntax to query access database.
> Please refer to teh following links for more informaitoin:
> http://msdn2.microsoft.com/en-us/library/ms190479.aspx
> 287414 You cannot link tables into a Microsoft Access project by using an
> ODBC Data Source Name (DSN)
> http://support.microsoft.com/defaul...kb;EN-US;287414
> As for creating database, it is not feasible to create directly from
> opendatasource etc from SQL Server. You need to use adox/ado to do this as
> you'v done.
> 317881 How to create an Access database by using ADOX and Visual C# .NET
> http://support.microsoft.com/defaul...kb;EN-US;317881
> If you have further questions, please let's know.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
>
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
>|||Hello Nick,
Sorry for the late response and it seems there is sync issue in our
internal application for your account. I'v reported this issue to the
related team.
Per your question, I was able to reproduce the issue on my side. It seems
that we are not able to use provider or connection string in the
"datasource" parameter. It also doesn't work if I use provider string
directly:
SELECT CustomerID, CompanyName from
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Data Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data
Source=C:\northwind.mdb;Passord=""',
'select * from customers')
I'v forward your feedback to the product team. In the meantime, I also
encourage you submit via the link below
http://lab.msdn.microsoft.com/produ...ck/default.aspx
Please let's know if you have any further feedback.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============

jdbc driver getTables(String , String ,Sring ,String[] )can't get tables whose catalog is pure

i have a database named "1", then i want to get the tables which type is "TABLE" under this catalog but it can't be done.
Check the kode:
conn.setCatalog("\"1\"");
DatabaseMetaData meta = conn.getMetaData();
ResultSet tables = meta.getTables("\"1\"", "dbo","ttt" , new String [] {"TABLE"});
if i change the last sentence to :
ResultSet tables = meta.getTables("1", "dbo","ttt" , new String [] {"TABLE"}), it still can't be done.
it work when "ResultSet tables = meta.getTables(null, "dbo","ttt" , new String [] {"TABLE"})

I tested this with the SQL Server 2005 JDBC driver and it worked fine.

I created a database named ["1"].

Which SQL Server JDBC driver are you using?

Also try bracketizing the database name like so, this will probably make it work:

ResultSet tables = meta.getTables("[\"1\"]", "dbo","ttt" , new String [] {"TABLE"});

Matt

|||k, i am working under sqlserver 2000 , i change the kode as u do, but it still can't work fine.

steps:
create database through sql : "create database 1".
change the kode to :
ResultSet tables = meta.getTables("[\"1\"]", null,null, new String [] {"TABLE"});
but it still can't work.no table returned.
Have u any idear?

|||i also tried this:
conn.getCatalog() returns "1";
but meta.getCatalogs returns 1;
is it a bug?|||

If you created the database named 1, then use 1 and not "1", so:

ResultSet tables = meta.getTables("[1]", null,null, new String [] {"TABLE"});

Also, if you have SQL Server Profiler you can see what it sent to the SQL Server.

With the SQL 2005 driver I see:

exec sp_tables @.table_qualifier = '"1"', @.table_owner = 'dbo', @.table_name = null , @.table_type = '''TABLE'''

This might help you figure out what the SQL 2000 driver is doing wrong.

I have the SQL 2000 driver setup on my machine I will give it a quick test.

|||

Yes I tested with SQL 2000 JDBC driver and this failed, so it is a bug.

Works fine with SQL 2005 JDBC driver.

I reported this bug to the JDBC PM.

|||3X a lot.