I have a strange problem. I have some T-SQL that functions properly in
Analyzer but fails when I write the same code into a SQL Job:
DECLARE @.DateTime datetime
DECLARE @.DateNoTime varchar(10)
SET @.DateTime = (GetDate())
SET @.DateNoTime = rtrim(cast(datepart(dd,@.DateTime) as char(2))) + '/'
+ rtrim(cast(datepart(mm,@.DateTime) as char(2))) + '/' +
rtrim(cast(datepart(yyyy,@.DateTime) as char(4)))
DECLARE @.Result datetime
SET @.Result = CONVERT(datetime, @.DateNoTime)
The last line, the CONVERT, fails in the SQL Job. I've tried using
CAST to see if that made a difference but I received the same error.
Here is the error SQL Agent generates:
The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value. [SQLSTATE 22007] (Error 242)
Associated statement is not prepared [SQLSTATE HY007] (Error 0). The
step failed.
Does anyone have any thoughts? I'm running SQL 2000.You should be able to use the convert function directly:
SET @.Result = CONVERT (datetime, CONVERT (char (10), getdate(), 103))
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
<stephan@.pathcom.com> wrote in message
news:1104419748.745035.54770@.c13g2000cwb.googlegroups.com...
I have a strange problem. I have some T-SQL that functions properly in
Analyzer but fails when I write the same code into a SQL Job:
DECLARE @.DateTime datetime
DECLARE @.DateNoTime varchar(10)
SET @.DateTime = (GetDate())
SET @.DateNoTime = rtrim(cast(datepart(dd,@.DateTime) as char(2))) + '/'
+ rtrim(cast(datepart(mm,@.DateTime) as char(2))) + '/' +
rtrim(cast(datepart(yyyy,@.DateTime) as char(4)))
DECLARE @.Result datetime
SET @.Result = CONVERT(datetime, @.DateNoTime)
The last line, the CONVERT, fails in the SQL Job. I've tried using
CAST to see if that made a difference but I received the same error.
Here is the error SQL Agent generates:
The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value. [SQLSTATE 22007] (Error 242)
Associated statement is not prepared [SQLSTATE HY007] (Error 0). The
step failed.
Does anyone have any thoughts? I'm running SQL 2000.|||Thanks for the advice Tom but I still receive the same error when it
tries to convert the data. Like I said, this works well in Analyzer,
but not when it's set up as a step in a Job.
SR
Tom Moreau wrote:
> You should be able to use the convert function directly:
> SET @.Result = CONVERT (datetime, CONVERT (char (10), getdate(), 103))
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> <stephan@.pathcom.com> wrote in message
> news:1104419748.745035.54770@.c13g2000cwb.googlegroups.com...
> I have a strange problem. I have some T-SQL that functions properly
in
> Analyzer but fails when I write the same code into a SQL Job:
> DECLARE @.DateTime datetime
> DECLARE @.DateNoTime varchar(10)
> SET @.DateTime = (GetDate())
> SET @.DateNoTime = rtrim(cast(datepart(dd,@.DateTime) as char(2))) +
'/'
> + rtrim(cast(datepart(mm,@.DateTime) as char(2))) + '/' +
> rtrim(cast(datepart(yyyy,@.DateTime) as char(4)))
> DECLARE @.Result datetime
> SET @.Result = CONVERT(datetime, @.DateNoTime)
> The last line, the CONVERT, fails in the SQL Job. I've tried using
> CAST to see if that made a difference but I received the same error.
> Here is the error SQL Agent generates:
> The conversion of a char data type to a datetime data type resulted
in
> an out-of-range datetime value. [SQLSTATE 22007] (Error 242)
> Associated statement is not prepared [SQLSTATE HY007] (Error 0). The
> step failed.
> Does anyone have any thoughts? I'm running SQL 2000.|||Tom's advice should've worked. Your convert statement
is faulty.
What version of SQL Server are you running?
<stephan@.pathcom.com> wrote in message
news:1104421071.721624.39040@.z14g2000cwz.googlegroups.com...
> Thanks for the advice Tom but I still receive the same error when it
> tries to convert the data. Like I said, this works well in Analyzer,
> but not when it's set up as a step in a Job.
> SR
> Tom Moreau wrote:
> > You should be able to use the convert function directly:
> >
> > SET @.Result = CONVERT (datetime, CONVERT (char (10), getdate(), 103))
> >
> > --
> > Tom
> >
> > ---
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON Canada
> > www.pinnaclepublishing.com
> >
> >
> > <stephan@.pathcom.com> wrote in message
> > news:1104419748.745035.54770@.c13g2000cwb.googlegroups.com...
> > I have a strange problem. I have some T-SQL that functions properly
> in
> > Analyzer but fails when I write the same code into a SQL Job:
> >
> > DECLARE @.DateTime datetime
> > DECLARE @.DateNoTime varchar(10)
> >
> > SET @.DateTime = (GetDate())
> > SET @.DateNoTime = rtrim(cast(datepart(dd,@.DateTime) as char(2))) +
> '/'
> > + rtrim(cast(datepart(mm,@.DateTime) as char(2))) + '/' +
> > rtrim(cast(datepart(yyyy,@.DateTime) as char(4)))
> >
> > DECLARE @.Result datetime
> > SET @.Result = CONVERT(datetime, @.DateNoTime)
> >
> > The last line, the CONVERT, fails in the SQL Job. I've tried using
> > CAST to see if that made a difference but I received the same error.
> > Here is the error SQL Agent generates:
> >
> > The conversion of a char data type to a datetime data type resulted
> in
> > an out-of-range datetime value. [SQLSTATE 22007] (Error 242)
> > Associated statement is not prepared [SQLSTATE HY007] (Error 0). The
> > step failed.
> >
> > Does anyone have any thoughts? I'm running SQL 2000.
>|||Stephan,
Try replacing the final statement with
SET @.Result = CONVERT(datetime, @.DateNoTime,103)
For whatever reason, the context in which this runs as a job step
has different language or dateformat settings than your Query Analyzer,
and you are applying CONVERT without specifying the format of
the date string, hence leaving the interpretation dependent on the context.
By the way, you can remove the time from a date without anything
depending on string formats for datetime:
SET @.Result = DATEADD(d,DATEDIFF(d,0,@.DateTime),0)
Steve Kass
Drew University
stephan@.pathcom.com wrote:
>I have a strange problem. I have some T-SQL that functions properly in
>Analyzer but fails when I write the same code into a SQL Job:
>DECLARE @.DateTime datetime
>DECLARE @.DateNoTime varchar(10)
>SET @.DateTime = (GetDate())
>SET @.DateNoTime = rtrim(cast(datepart(dd,@.DateTime) as char(2))) + '/'
>+ rtrim(cast(datepart(mm,@.DateTime) as char(2))) + '/' +
>rtrim(cast(datepart(yyyy,@.DateTime) as char(4)))
>DECLARE @.Result datetime
>SET @.Result = CONVERT(datetime, @.DateNoTime)
>The last line, the CONVERT, fails in the SQL Job. I've tried using
>CAST to see if that made a difference but I received the same error.
>Here is the error SQL Agent generates:
>The conversion of a char data type to a datetime data type resulted in
>an out-of-range datetime value. [SQLSTATE 22007] (Error 242)
>Associated statement is not prepared [SQLSTATE HY007] (Error 0). The
>step failed.
>Does anyone have any thoughts? I'm running SQL 2000.
>
>|||Armando,
Tom's statement didn't work because it did not specify a format for
the conversion back to datetime. Tom's statement would have worked this
way:
SET @.Result = CONVERT (datetime, CONVERT (char (10), getdate(), 103), 103)
SK
Armando Prato wrote:
>Tom's advice should've worked. Your convert statement
>is faulty.
>What version of SQL Server are you running?
><stephan@.pathcom.com> wrote in message
>news:1104421071.721624.39040@.z14g2000cwz.googlegroups.com...
>
>>Thanks for the advice Tom but I still receive the same error when it
>>tries to convert the data. Like I said, this works well in Analyzer,
>>but not when it's set up as a step in a Job.
>>SR
>>Tom Moreau wrote:
>>
>>You should be able to use the convert function directly:
>>SET @.Result = CONVERT (datetime, CONVERT (char (10), getdate(), 103))
>>--
>>Tom
>>---
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com
>>
>><stephan@.pathcom.com> wrote in message
>>news:1104419748.745035.54770@.c13g2000cwb.googlegroups.com...
>>I have a strange problem. I have some T-SQL that functions properly
>>
>>in
>>
>>Analyzer but fails when I write the same code into a SQL Job:
>>DECLARE @.DateTime datetime
>>DECLARE @.DateNoTime varchar(10)
>>SET @.DateTime = (GetDate())
>>SET @.DateNoTime = rtrim(cast(datepart(dd,@.DateTime) as char(2))) +
>>
>>'/'
>>
>>+ rtrim(cast(datepart(mm,@.DateTime) as char(2))) + '/' +
>>rtrim(cast(datepart(yyyy,@.DateTime) as char(4)))
>>DECLARE @.Result datetime
>>SET @.Result = CONVERT(datetime, @.DateNoTime)
>>The last line, the CONVERT, fails in the SQL Job. I've tried using
>>CAST to see if that made a difference but I received the same error.
>>Here is the error SQL Agent generates:
>>The conversion of a char data type to a datetime data type resulted
>>
>>in
>>
>>an out-of-range datetime value. [SQLSTATE 22007] (Error 242)
>>Associated statement is not prepared [SQLSTATE HY007] (Error 0). The
>>step failed.
>>Does anyone have any thoughts? I'm running SQL 2000.
>>
>
>|||Ah yes, I see.
Thanks.
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23mhjopo7EHA.1408@.TK2MSFTNGP10.phx.gbl...
> Armando,
> Tom's statement didn't work because it did not specify a format for
> the conversion back to datetime. Tom's statement would have worked this
> way:
> SET @.Result = CONVERT (datetime, CONVERT (char (10), getdate(), 103), 103)
> SK
>
> Armando Prato wrote:
> >Tom's advice should've worked. Your convert statement
> >is faulty.
> >
> >What version of SQL Server are you running?
> >
> ><stephan@.pathcom.com> wrote in message
> >news:1104421071.721624.39040@.z14g2000cwz.googlegroups.com...
> >
> >
> >>Thanks for the advice Tom but I still receive the same error when it
> >>tries to convert the data. Like I said, this works well in Analyzer,
> >>but not when it's set up as a step in a Job.
> >>
> >>SR
> >>
> >>Tom Moreau wrote:
> >>
> >>
> >>You should be able to use the convert function directly:
> >>
> >>SET @.Result = CONVERT (datetime, CONVERT (char (10), getdate(), 103))
> >>
> >>--
> >>Tom
> >>
> >>---
> >>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> >>SQL Server MVP
> >>Columnist, SQL Server Professional
> >>Toronto, ON Canada
> >>www.pinnaclepublishing.com
> >>
> >>
> >><stephan@.pathcom.com> wrote in message
> >>news:1104419748.745035.54770@.c13g2000cwb.googlegroups.com...
> >>I have a strange problem. I have some T-SQL that functions properly
> >>
> >>
> >>in
> >>
> >>
> >>Analyzer but fails when I write the same code into a SQL Job:
> >>
> >>DECLARE @.DateTime datetime
> >>DECLARE @.DateNoTime varchar(10)
> >>
> >>SET @.DateTime = (GetDate())
> >>SET @.DateNoTime = rtrim(cast(datepart(dd,@.DateTime) as char(2))) +
> >>
> >>
> >>'/'
> >>
> >>
> >>+ rtrim(cast(datepart(mm,@.DateTime) as char(2))) + '/' +
> >>rtrim(cast(datepart(yyyy,@.DateTime) as char(4)))
> >>
> >>DECLARE @.Result datetime
> >>SET @.Result = CONVERT(datetime, @.DateNoTime)
> >>
> >>The last line, the CONVERT, fails in the SQL Job. I've tried using
> >>CAST to see if that made a difference but I received the same error.
> >>Here is the error SQL Agent generates:
> >>
> >>The conversion of a char data type to a datetime data type resulted
> >>
> >>
> >>in
> >>
> >>
> >>an out-of-range datetime value. [SQLSTATE 22007] (Error 242)
> >>Associated statement is not prepared [SQLSTATE HY007] (Error 0). The
> >>step failed.
> >>
> >>Does anyone have any thoughts? I'm running SQL 2000.
> >>
> >>
> >
> >
> >
> >|||Thanks guys. That worked like a charm.
Thanks Steve for the explanation why it didn't work. Strange that the
Agent and the Analyzer use a different engine...
SR|||Stephan,
you may find that the owner of the sql agent job(and/or the sqlserver agent
account) uses a different account to the one you have used to access QA and
as a result possibly has a different default language setting which has
resulted in a different date format.
HTH
<stephan@.pathcom.com> wrote in message
news:1104433299.670932.135410@.c13g2000cwb.googlegroups.com...
> Thanks guys. That worked like a charm.
> Thanks Steve for the explanation why it didn't work. Strange that the
> Agent and the Analyzer use a different engine...
> SR
>
Showing posts with label functions. Show all posts
Showing posts with label functions. Show all posts
Friday, March 30, 2012
Problems with T-SQL in SQL Job
I have a strange problem. I have some T-SQL that functions properly in
Analyzer but fails when I write the same code into a SQL Job:
DECLARE @.DateTime datetime
DECLARE @.DateNoTime varchar(10)
SET @.DateTime = (GetDate())
SET @.DateNoTime = rtrim(cast(datepart(dd,@.DateTime) as char(2))) + '/'
+ rtrim(cast(datepart(mm,@.DateTime) as char(2))) + '/' +
rtrim(cast(datepart(yyyy,@.DateTime) as char(4)))
DECLARE @.Result datetime
SET @.Result = CONVERT(datetime, @.DateNoTime)
The last line, the CONVERT, fails in the SQL Job. I've tried using
CAST to see if that made a difference but I received the same error.
Here is the error SQL Agent generates:
The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value. [SQLSTATE 22007] (Error 242)
Associated statement is not prepared [SQLSTATE HY007] (Error 0). The
step failed.
Does anyone have any thoughts? I'm running SQL 2000.You should be able to use the convert function directly:
SET @.Result = CONVERT (datetime, CONVERT (char (10), getdate(), 103))
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
<stephan@.pathcom.com> wrote in message
news:1104419748.745035.54770@.c13g2000cwb.googlegroups.com...
I have a strange problem. I have some T-SQL that functions properly in
Analyzer but fails when I write the same code into a SQL Job:
DECLARE @.DateTime datetime
DECLARE @.DateNoTime varchar(10)
SET @.DateTime = (GetDate())
SET @.DateNoTime = rtrim(cast(datepart(dd,@.DateTime) as char(2))) + '/'
+ rtrim(cast(datepart(mm,@.DateTime) as char(2))) + '/' +
rtrim(cast(datepart(yyyy,@.DateTime) as char(4)))
DECLARE @.Result datetime
SET @.Result = CONVERT(datetime, @.DateNoTime)
The last line, the CONVERT, fails in the SQL Job. I've tried using
CAST to see if that made a difference but I received the same error.
Here is the error SQL Agent generates:
The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value. [SQLSTATE 22007] (Error 242)
Associated statement is not prepared [SQLSTATE HY007] (Error 0). The
step failed.
Does anyone have any thoughts? I'm running SQL 2000.|||Thanks for the advice Tom but I still receive the same error when it
tries to convert the data. Like I said, this works well in Analyzer,
but not when it's set up as a step in a Job.
SR
Tom Moreau wrote:
> You should be able to use the convert function directly:
> SET @.Result = CONVERT (datetime, CONVERT (char (10), getdate(), 103))
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> <stephan@.pathcom.com> wrote in message
> news:1104419748.745035.54770@.c13g2000cwb.googlegroups.com...
> I have a strange problem. I have some T-SQL that functions properly
in
> Analyzer but fails when I write the same code into a SQL Job:
> DECLARE @.DateTime datetime
> DECLARE @.DateNoTime varchar(10)
> SET @.DateTime = (GetDate())
> SET @.DateNoTime = rtrim(cast(datepart(dd,@.DateTime) as char(2))) +
'/'
> + rtrim(cast(datepart(mm,@.DateTime) as char(2))) + '/' +
> rtrim(cast(datepart(yyyy,@.DateTime) as char(4)))
> DECLARE @.Result datetime
> SET @.Result = CONVERT(datetime, @.DateNoTime)
> The last line, the CONVERT, fails in the SQL Job. I've tried using
> CAST to see if that made a difference but I received the same error.
> Here is the error SQL Agent generates:
> The conversion of a char data type to a datetime data type resulted
in
> an out-of-range datetime value. [SQLSTATE 22007] (Error 242)
> Associated statement is not prepared [SQLSTATE HY007] (Error 0). The
> step failed.
> Does anyone have any thoughts? I'm running SQL 2000.|||Tom's advice should've worked. Your convert statement
is faulty.
What version of SQL Server are you running?
<stephan@.pathcom.com> wrote in message
news:1104421071.721624.39040@.z14g2000cwz.googlegroups.com...
> Thanks for the advice Tom but I still receive the same error when it
> tries to convert the data. Like I said, this works well in Analyzer,
> but not when it's set up as a step in a Job.
> SR
> Tom Moreau wrote:
> in
> '/'
> in
>|||Stephan,
Try replacing the final statement with
SET @.Result = CONVERT(datetime, @.DateNoTime,103)
For whatever reason, the context in which this runs as a job step
has different language or dateformat settings than your Query Analyzer,
and you are applying CONVERT without specifying the format of
the date string, hence leaving the interpretation dependent on the context.
By the way, you can remove the time from a date without anything
depending on string formats for datetime:
SET @.Result = DATEADD(d,DATEDIFF(d,0,@.DateTime),0)
Steve Kass
Drew University
stephan@.pathcom.com wrote:
>I have a strange problem. I have some T-SQL that functions properly in
>Analyzer but fails when I write the same code into a SQL Job:
>DECLARE @.DateTime datetime
>DECLARE @.DateNoTime varchar(10)
>SET @.DateTime = (GetDate())
>SET @.DateNoTime = rtrim(cast(datepart(dd,@.DateTime) as char(2))) + '/'
>+ rtrim(cast(datepart(mm,@.DateTime) as char(2))) + '/' +
>rtrim(cast(datepart(yyyy,@.DateTime) as char(4)))
>DECLARE @.Result datetime
>SET @.Result = CONVERT(datetime, @.DateNoTime)
>The last line, the CONVERT, fails in the SQL Job. I've tried using
>CAST to see if that made a difference but I received the same error.
>Here is the error SQL Agent generates:
>The conversion of a char data type to a datetime data type resulted in
>an out-of-range datetime value. [SQLSTATE 22007] (Error 242)
>Associated statement is not prepared [SQLSTATE HY007] (Error 0). The
>step failed.
>Does anyone have any thoughts? I'm running SQL 2000.
>
>|||Armando,
Tom's statement didn't work because it did not specify a format for
the conversion back to datetime. Tom's statement would have worked this
way:
SET @.Result = CONVERT (datetime, CONVERT (char (10), getdate(), 103), 103)
SK
Armando Prato wrote:
>Tom's advice should've worked. Your convert statement
>is faulty.
>What version of SQL Server are you running?
><stephan@.pathcom.com> wrote in message
>news:1104421071.721624.39040@.z14g2000cwz.googlegroups.com...
>
>
>|||Ah yes, I see.
Thanks.
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23mhjopo7EHA.1408@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Armando,
> Tom's statement didn't work because it did not specify a format for
> the conversion back to datetime. Tom's statement would have worked this
> way:
> SET @.Result = CONVERT (datetime, CONVERT (char (10), getdate(), 103), 103)
> SK
>
> Armando Prato wrote:
>|||Thanks guys. That worked like a charm.
Thanks Steve for the explanation why it didn't work. Strange that the
Agent and the Analyzer use a different engine...
SR|||Stephan,
you may find that the owner of the sql agent job(and/or the sqlserver agent
account) uses a different account to the one you have used to access QA and
as a result possibly has a different default language setting which has
resulted in a different date format.
HTH
<stephan@.pathcom.com> wrote in message
news:1104433299.670932.135410@.c13g2000cwb.googlegroups.com...
> Thanks guys. That worked like a charm.
> Thanks Steve for the explanation why it didn't work. Strange that the
> Agent and the Analyzer use a different engine...
> SR
>sql
Analyzer but fails when I write the same code into a SQL Job:
DECLARE @.DateTime datetime
DECLARE @.DateNoTime varchar(10)
SET @.DateTime = (GetDate())
SET @.DateNoTime = rtrim(cast(datepart(dd,@.DateTime) as char(2))) + '/'
+ rtrim(cast(datepart(mm,@.DateTime) as char(2))) + '/' +
rtrim(cast(datepart(yyyy,@.DateTime) as char(4)))
DECLARE @.Result datetime
SET @.Result = CONVERT(datetime, @.DateNoTime)
The last line, the CONVERT, fails in the SQL Job. I've tried using
CAST to see if that made a difference but I received the same error.
Here is the error SQL Agent generates:
The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value. [SQLSTATE 22007] (Error 242)
Associated statement is not prepared [SQLSTATE HY007] (Error 0). The
step failed.
Does anyone have any thoughts? I'm running SQL 2000.You should be able to use the convert function directly:
SET @.Result = CONVERT (datetime, CONVERT (char (10), getdate(), 103))
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
<stephan@.pathcom.com> wrote in message
news:1104419748.745035.54770@.c13g2000cwb.googlegroups.com...
I have a strange problem. I have some T-SQL that functions properly in
Analyzer but fails when I write the same code into a SQL Job:
DECLARE @.DateTime datetime
DECLARE @.DateNoTime varchar(10)
SET @.DateTime = (GetDate())
SET @.DateNoTime = rtrim(cast(datepart(dd,@.DateTime) as char(2))) + '/'
+ rtrim(cast(datepart(mm,@.DateTime) as char(2))) + '/' +
rtrim(cast(datepart(yyyy,@.DateTime) as char(4)))
DECLARE @.Result datetime
SET @.Result = CONVERT(datetime, @.DateNoTime)
The last line, the CONVERT, fails in the SQL Job. I've tried using
CAST to see if that made a difference but I received the same error.
Here is the error SQL Agent generates:
The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value. [SQLSTATE 22007] (Error 242)
Associated statement is not prepared [SQLSTATE HY007] (Error 0). The
step failed.
Does anyone have any thoughts? I'm running SQL 2000.|||Thanks for the advice Tom but I still receive the same error when it
tries to convert the data. Like I said, this works well in Analyzer,
but not when it's set up as a step in a Job.
SR
Tom Moreau wrote:
> You should be able to use the convert function directly:
> SET @.Result = CONVERT (datetime, CONVERT (char (10), getdate(), 103))
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> <stephan@.pathcom.com> wrote in message
> news:1104419748.745035.54770@.c13g2000cwb.googlegroups.com...
> I have a strange problem. I have some T-SQL that functions properly
in
> Analyzer but fails when I write the same code into a SQL Job:
> DECLARE @.DateTime datetime
> DECLARE @.DateNoTime varchar(10)
> SET @.DateTime = (GetDate())
> SET @.DateNoTime = rtrim(cast(datepart(dd,@.DateTime) as char(2))) +
'/'
> + rtrim(cast(datepart(mm,@.DateTime) as char(2))) + '/' +
> rtrim(cast(datepart(yyyy,@.DateTime) as char(4)))
> DECLARE @.Result datetime
> SET @.Result = CONVERT(datetime, @.DateNoTime)
> The last line, the CONVERT, fails in the SQL Job. I've tried using
> CAST to see if that made a difference but I received the same error.
> Here is the error SQL Agent generates:
> The conversion of a char data type to a datetime data type resulted
in
> an out-of-range datetime value. [SQLSTATE 22007] (Error 242)
> Associated statement is not prepared [SQLSTATE HY007] (Error 0). The
> step failed.
> Does anyone have any thoughts? I'm running SQL 2000.|||Tom's advice should've worked. Your convert statement
is faulty.
What version of SQL Server are you running?
<stephan@.pathcom.com> wrote in message
news:1104421071.721624.39040@.z14g2000cwz.googlegroups.com...
> Thanks for the advice Tom but I still receive the same error when it
> tries to convert the data. Like I said, this works well in Analyzer,
> but not when it's set up as a step in a Job.
> SR
> Tom Moreau wrote:
> in
> '/'
> in
>|||Stephan,
Try replacing the final statement with
SET @.Result = CONVERT(datetime, @.DateNoTime,103)
For whatever reason, the context in which this runs as a job step
has different language or dateformat settings than your Query Analyzer,
and you are applying CONVERT without specifying the format of
the date string, hence leaving the interpretation dependent on the context.
By the way, you can remove the time from a date without anything
depending on string formats for datetime:
SET @.Result = DATEADD(d,DATEDIFF(d,0,@.DateTime),0)
Steve Kass
Drew University
stephan@.pathcom.com wrote:
>I have a strange problem. I have some T-SQL that functions properly in
>Analyzer but fails when I write the same code into a SQL Job:
>DECLARE @.DateTime datetime
>DECLARE @.DateNoTime varchar(10)
>SET @.DateTime = (GetDate())
>SET @.DateNoTime = rtrim(cast(datepart(dd,@.DateTime) as char(2))) + '/'
>+ rtrim(cast(datepart(mm,@.DateTime) as char(2))) + '/' +
>rtrim(cast(datepart(yyyy,@.DateTime) as char(4)))
>DECLARE @.Result datetime
>SET @.Result = CONVERT(datetime, @.DateNoTime)
>The last line, the CONVERT, fails in the SQL Job. I've tried using
>CAST to see if that made a difference but I received the same error.
>Here is the error SQL Agent generates:
>The conversion of a char data type to a datetime data type resulted in
>an out-of-range datetime value. [SQLSTATE 22007] (Error 242)
>Associated statement is not prepared [SQLSTATE HY007] (Error 0). The
>step failed.
>Does anyone have any thoughts? I'm running SQL 2000.
>
>|||Armando,
Tom's statement didn't work because it did not specify a format for
the conversion back to datetime. Tom's statement would have worked this
way:
SET @.Result = CONVERT (datetime, CONVERT (char (10), getdate(), 103), 103)
SK
Armando Prato wrote:
>Tom's advice should've worked. Your convert statement
>is faulty.
>What version of SQL Server are you running?
><stephan@.pathcom.com> wrote in message
>news:1104421071.721624.39040@.z14g2000cwz.googlegroups.com...
>
>
>|||Ah yes, I see.
Thanks.
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23mhjopo7EHA.1408@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Armando,
> Tom's statement didn't work because it did not specify a format for
> the conversion back to datetime. Tom's statement would have worked this
> way:
> SET @.Result = CONVERT (datetime, CONVERT (char (10), getdate(), 103), 103)
> SK
>
> Armando Prato wrote:
>|||Thanks guys. That worked like a charm.
Thanks Steve for the explanation why it didn't work. Strange that the
Agent and the Analyzer use a different engine...
SR|||Stephan,
you may find that the owner of the sql agent job(and/or the sqlserver agent
account) uses a different account to the one you have used to access QA and
as a result possibly has a different default language setting which has
resulted in a different date format.
HTH
<stephan@.pathcom.com> wrote in message
news:1104433299.670932.135410@.c13g2000cwb.googlegroups.com...
> Thanks guys. That worked like a charm.
> Thanks Steve for the explanation why it didn't work. Strange that the
> Agent and the Analyzer use a different engine...
> SR
>sql
Problems with T-SQL in SQL Job
I have a strange problem. I have some T-SQL that functions properly in
Analyzer but fails when I write the same code into a SQL Job:
DECLARE @.DateTime datetime
DECLARE @.DateNoTime varchar(10)
SET @.DateTime = (GetDate())
SET @.DateNoTime = rtrim(cast(datepart(dd,@.DateTime) as char(2))) + '/'
+ rtrim(cast(datepart(mm,@.DateTime) as char(2))) + '/' +
rtrim(cast(datepart(yyyy,@.DateTime) as char(4)))
DECLARE @.Result datetime
SET @.Result = CONVERT(datetime, @.DateNoTime)
The last line, the CONVERT, fails in the SQL Job. I've tried using
CAST to see if that made a difference but I received the same error.
Here is the error SQL Agent generates:
The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value. [SQLSTATE 22007] (Error 242)
Associated statement is not prepared [SQLSTATE HY007] (Error 0). The
step failed.
Does anyone have any thoughts? I'm running SQL 2000.
You should be able to use the convert function directly:
SET @.Result = CONVERT (datetime, CONVERT (char (10), getdate(), 103))
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
<stephan@.pathcom.com> wrote in message
news:1104419748.745035.54770@.c13g2000cwb.googlegro ups.com...
I have a strange problem. I have some T-SQL that functions properly in
Analyzer but fails when I write the same code into a SQL Job:
DECLARE @.DateTime datetime
DECLARE @.DateNoTime varchar(10)
SET @.DateTime = (GetDate())
SET @.DateNoTime = rtrim(cast(datepart(dd,@.DateTime) as char(2))) + '/'
+ rtrim(cast(datepart(mm,@.DateTime) as char(2))) + '/' +
rtrim(cast(datepart(yyyy,@.DateTime) as char(4)))
DECLARE @.Result datetime
SET @.Result = CONVERT(datetime, @.DateNoTime)
The last line, the CONVERT, fails in the SQL Job. I've tried using
CAST to see if that made a difference but I received the same error.
Here is the error SQL Agent generates:
The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value. [SQLSTATE 22007] (Error 242)
Associated statement is not prepared [SQLSTATE HY007] (Error 0). The
step failed.
Does anyone have any thoughts? I'm running SQL 2000.
|||Thanks for the advice Tom but I still receive the same error when it
tries to convert the data. Like I said, this works well in Analyzer,
but not when it's set up as a step in a Job.
SR
Tom Moreau wrote:
> You should be able to use the convert function directly:
> SET @.Result = CONVERT (datetime, CONVERT (char (10), getdate(), 103))
> --
> Tom
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> <stephan@.pathcom.com> wrote in message
> news:1104419748.745035.54770@.c13g2000cwb.googlegro ups.com...
> I have a strange problem. I have some T-SQL that functions properly
in
> Analyzer but fails when I write the same code into a SQL Job:
> DECLARE @.DateTime datetime
> DECLARE @.DateNoTime varchar(10)
> SET @.DateTime = (GetDate())
> SET @.DateNoTime = rtrim(cast(datepart(dd,@.DateTime) as char(2))) +
'/'
> + rtrim(cast(datepart(mm,@.DateTime) as char(2))) + '/' +
> rtrim(cast(datepart(yyyy,@.DateTime) as char(4)))
> DECLARE @.Result datetime
> SET @.Result = CONVERT(datetime, @.DateNoTime)
> The last line, the CONVERT, fails in the SQL Job. I've tried using
> CAST to see if that made a difference but I received the same error.
> Here is the error SQL Agent generates:
> The conversion of a char data type to a datetime data type resulted
in
> an out-of-range datetime value. [SQLSTATE 22007] (Error 242)
> Associated statement is not prepared [SQLSTATE HY007] (Error 0). The
> step failed.
> Does anyone have any thoughts? I'm running SQL 2000.
|||Tom's advice should've worked. Your convert statement
is faulty.
What version of SQL Server are you running?
<stephan@.pathcom.com> wrote in message
news:1104421071.721624.39040@.z14g2000cwz.googlegro ups.com...
> Thanks for the advice Tom but I still receive the same error when it
> tries to convert the data. Like I said, this works well in Analyzer,
> but not when it's set up as a step in a Job.
> SR
> Tom Moreau wrote:
> in
> '/'
> in
>
|||Stephan,
Try replacing the final statement with
SET @.Result = CONVERT(datetime, @.DateNoTime,103)
For whatever reason, the context in which this runs as a job step
has different language or dateformat settings than your Query Analyzer,
and you are applying CONVERT without specifying the format of
the date string, hence leaving the interpretation dependent on the context.
By the way, you can remove the time from a date without anything
depending on string formats for datetime:
SET @.Result = DATEADD(d,DATEDIFF(d,0,@.DateTime),0)
Steve Kass
Drew University
stephan@.pathcom.com wrote:
>I have a strange problem. I have some T-SQL that functions properly in
>Analyzer but fails when I write the same code into a SQL Job:
>DECLARE @.DateTime datetime
>DECLARE @.DateNoTime varchar(10)
>SET @.DateTime = (GetDate())
>SET @.DateNoTime = rtrim(cast(datepart(dd,@.DateTime) as char(2))) + '/'
>+ rtrim(cast(datepart(mm,@.DateTime) as char(2))) + '/' +
>rtrim(cast(datepart(yyyy,@.DateTime) as char(4)))
>DECLARE @.Result datetime
>SET @.Result = CONVERT(datetime, @.DateNoTime)
>The last line, the CONVERT, fails in the SQL Job. I've tried using
>CAST to see if that made a difference but I received the same error.
>Here is the error SQL Agent generates:
>The conversion of a char data type to a datetime data type resulted in
>an out-of-range datetime value. [SQLSTATE 22007] (Error 242)
>Associated statement is not prepared [SQLSTATE HY007] (Error 0). The
>step failed.
>Does anyone have any thoughts? I'm running SQL 2000.
>
>
|||Armando,
Tom's statement didn't work because it did not specify a format for
the conversion back to datetime. Tom's statement would have worked this
way:
SET @.Result = CONVERT (datetime, CONVERT (char (10), getdate(), 103), 103)
SK
Armando Prato wrote:
>Tom's advice should've worked. Your convert statement
>is faulty.
>What version of SQL Server are you running?
><stephan@.pathcom.com> wrote in message
>news:1104421071.721624.39040@.z14g2000cwz.googlegr oups.com...
>
>
>
|||Ah yes, I see.
Thanks.
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23mhjopo7EHA.1408@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Armando,
> Tom's statement didn't work because it did not specify a format for
> the conversion back to datetime. Tom's statement would have worked this
> way:
> SET @.Result = CONVERT (datetime, CONVERT (char (10), getdate(), 103), 103)
> SK
>
> Armando Prato wrote:
|||Thanks guys. That worked like a charm.
Thanks Steve for the explanation why it didn't work. Strange that the
Agent and the Analyzer use a different engine...
SR
|||Stephan,
you may find that the owner of the sql agent job(and/or the sqlserver agent
account) uses a different account to the one you have used to access QA and
as a result possibly has a different default language setting which has
resulted in a different date format.
HTH
<stephan@.pathcom.com> wrote in message
news:1104433299.670932.135410@.c13g2000cwb.googlegr oups.com...
> Thanks guys. That worked like a charm.
> Thanks Steve for the explanation why it didn't work. Strange that the
> Agent and the Analyzer use a different engine...
> SR
>
Analyzer but fails when I write the same code into a SQL Job:
DECLARE @.DateTime datetime
DECLARE @.DateNoTime varchar(10)
SET @.DateTime = (GetDate())
SET @.DateNoTime = rtrim(cast(datepart(dd,@.DateTime) as char(2))) + '/'
+ rtrim(cast(datepart(mm,@.DateTime) as char(2))) + '/' +
rtrim(cast(datepart(yyyy,@.DateTime) as char(4)))
DECLARE @.Result datetime
SET @.Result = CONVERT(datetime, @.DateNoTime)
The last line, the CONVERT, fails in the SQL Job. I've tried using
CAST to see if that made a difference but I received the same error.
Here is the error SQL Agent generates:
The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value. [SQLSTATE 22007] (Error 242)
Associated statement is not prepared [SQLSTATE HY007] (Error 0). The
step failed.
Does anyone have any thoughts? I'm running SQL 2000.
You should be able to use the convert function directly:
SET @.Result = CONVERT (datetime, CONVERT (char (10), getdate(), 103))
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
<stephan@.pathcom.com> wrote in message
news:1104419748.745035.54770@.c13g2000cwb.googlegro ups.com...
I have a strange problem. I have some T-SQL that functions properly in
Analyzer but fails when I write the same code into a SQL Job:
DECLARE @.DateTime datetime
DECLARE @.DateNoTime varchar(10)
SET @.DateTime = (GetDate())
SET @.DateNoTime = rtrim(cast(datepart(dd,@.DateTime) as char(2))) + '/'
+ rtrim(cast(datepart(mm,@.DateTime) as char(2))) + '/' +
rtrim(cast(datepart(yyyy,@.DateTime) as char(4)))
DECLARE @.Result datetime
SET @.Result = CONVERT(datetime, @.DateNoTime)
The last line, the CONVERT, fails in the SQL Job. I've tried using
CAST to see if that made a difference but I received the same error.
Here is the error SQL Agent generates:
The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value. [SQLSTATE 22007] (Error 242)
Associated statement is not prepared [SQLSTATE HY007] (Error 0). The
step failed.
Does anyone have any thoughts? I'm running SQL 2000.
|||Thanks for the advice Tom but I still receive the same error when it
tries to convert the data. Like I said, this works well in Analyzer,
but not when it's set up as a step in a Job.
SR
Tom Moreau wrote:
> You should be able to use the convert function directly:
> SET @.Result = CONVERT (datetime, CONVERT (char (10), getdate(), 103))
> --
> Tom
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> <stephan@.pathcom.com> wrote in message
> news:1104419748.745035.54770@.c13g2000cwb.googlegro ups.com...
> I have a strange problem. I have some T-SQL that functions properly
in
> Analyzer but fails when I write the same code into a SQL Job:
> DECLARE @.DateTime datetime
> DECLARE @.DateNoTime varchar(10)
> SET @.DateTime = (GetDate())
> SET @.DateNoTime = rtrim(cast(datepart(dd,@.DateTime) as char(2))) +
'/'
> + rtrim(cast(datepart(mm,@.DateTime) as char(2))) + '/' +
> rtrim(cast(datepart(yyyy,@.DateTime) as char(4)))
> DECLARE @.Result datetime
> SET @.Result = CONVERT(datetime, @.DateNoTime)
> The last line, the CONVERT, fails in the SQL Job. I've tried using
> CAST to see if that made a difference but I received the same error.
> Here is the error SQL Agent generates:
> The conversion of a char data type to a datetime data type resulted
in
> an out-of-range datetime value. [SQLSTATE 22007] (Error 242)
> Associated statement is not prepared [SQLSTATE HY007] (Error 0). The
> step failed.
> Does anyone have any thoughts? I'm running SQL 2000.
|||Tom's advice should've worked. Your convert statement
is faulty.
What version of SQL Server are you running?
<stephan@.pathcom.com> wrote in message
news:1104421071.721624.39040@.z14g2000cwz.googlegro ups.com...
> Thanks for the advice Tom but I still receive the same error when it
> tries to convert the data. Like I said, this works well in Analyzer,
> but not when it's set up as a step in a Job.
> SR
> Tom Moreau wrote:
> in
> '/'
> in
>
|||Stephan,
Try replacing the final statement with
SET @.Result = CONVERT(datetime, @.DateNoTime,103)
For whatever reason, the context in which this runs as a job step
has different language or dateformat settings than your Query Analyzer,
and you are applying CONVERT without specifying the format of
the date string, hence leaving the interpretation dependent on the context.
By the way, you can remove the time from a date without anything
depending on string formats for datetime:
SET @.Result = DATEADD(d,DATEDIFF(d,0,@.DateTime),0)
Steve Kass
Drew University
stephan@.pathcom.com wrote:
>I have a strange problem. I have some T-SQL that functions properly in
>Analyzer but fails when I write the same code into a SQL Job:
>DECLARE @.DateTime datetime
>DECLARE @.DateNoTime varchar(10)
>SET @.DateTime = (GetDate())
>SET @.DateNoTime = rtrim(cast(datepart(dd,@.DateTime) as char(2))) + '/'
>+ rtrim(cast(datepart(mm,@.DateTime) as char(2))) + '/' +
>rtrim(cast(datepart(yyyy,@.DateTime) as char(4)))
>DECLARE @.Result datetime
>SET @.Result = CONVERT(datetime, @.DateNoTime)
>The last line, the CONVERT, fails in the SQL Job. I've tried using
>CAST to see if that made a difference but I received the same error.
>Here is the error SQL Agent generates:
>The conversion of a char data type to a datetime data type resulted in
>an out-of-range datetime value. [SQLSTATE 22007] (Error 242)
>Associated statement is not prepared [SQLSTATE HY007] (Error 0). The
>step failed.
>Does anyone have any thoughts? I'm running SQL 2000.
>
>
|||Armando,
Tom's statement didn't work because it did not specify a format for
the conversion back to datetime. Tom's statement would have worked this
way:
SET @.Result = CONVERT (datetime, CONVERT (char (10), getdate(), 103), 103)
SK
Armando Prato wrote:
>Tom's advice should've worked. Your convert statement
>is faulty.
>What version of SQL Server are you running?
><stephan@.pathcom.com> wrote in message
>news:1104421071.721624.39040@.z14g2000cwz.googlegr oups.com...
>
>
>
|||Ah yes, I see.
Thanks.
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23mhjopo7EHA.1408@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Armando,
> Tom's statement didn't work because it did not specify a format for
> the conversion back to datetime. Tom's statement would have worked this
> way:
> SET @.Result = CONVERT (datetime, CONVERT (char (10), getdate(), 103), 103)
> SK
>
> Armando Prato wrote:
|||Thanks guys. That worked like a charm.
Thanks Steve for the explanation why it didn't work. Strange that the
Agent and the Analyzer use a different engine...
SR
|||Stephan,
you may find that the owner of the sql agent job(and/or the sqlserver agent
account) uses a different account to the one you have used to access QA and
as a result possibly has a different default language setting which has
resulted in a different date format.
HTH
<stephan@.pathcom.com> wrote in message
news:1104433299.670932.135410@.c13g2000cwb.googlegr oups.com...
> Thanks guys. That worked like a charm.
> Thanks Steve for the explanation why it didn't work. Strange that the
> Agent and the Analyzer use a different engine...
> SR
>
problems with sum()over(order by field) syntax
Hello
I'm experimenting with window functions and I'm having troubles with the sum()over() syntax.
I've followed the syntax from many examples and i'm still getting this error. I can get it work with sum(field1) over (partition by field2) but every time i add order by i get an error
I have the following query, this is from the adventureworks sample
use adventureworksdw
select *, sum(scenariokey)over(order by amount) as test1
from FactFinance
and I get the following error
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'order'.
Any suggestions.
Thanks in Advance
Daniel
When you use aggregation you have to use Partition By instead of Order BY.
select *, sum(scenariokey)over(Partition By amount) as test1
When you use Ranking functions then you have to use Order By & you can use Partition By if nessasary..
select *, Rank() over (Partition By OrderDate Order By amount) as test1
select *, Row_Number() over (Partition By OrderDate Order By amount) as test1
|||SQL Server 2005 only supports partition clause for aggregate window functions. The ranking window functions support partition and order by. So we implement only a small subset of the OVER clause as specified in the ANSI SQL standards.Wednesday, March 21, 2012
Problems with MultiStatement functions
Hi,
has anyone ever had a bad exerience with MultiStatement functions?
I've a query that i runs and works fine,if i create a multistatement function of this query, the query runs very slowly ...
The same query in "inline function" hasn't this behavior ... why??
:confused: :confused: :confused: :confused:Where are you using the function in your query?
Your function may be executing once for every row in your result set, possibly thousands of times, and that will of course slow down the query.
Post your code if you would like us to review it.
has anyone ever had a bad exerience with MultiStatement functions?
I've a query that i runs and works fine,if i create a multistatement function of this query, the query runs very slowly ...
The same query in "inline function" hasn't this behavior ... why??
:confused: :confused: :confused: :confused:Where are you using the function in your query?
Your function may be executing once for every row in your result set, possibly thousands of times, and that will of course slow down the query.
Post your code if you would like us to review it.
Monday, February 20, 2012
Problems using TOP and User Functions
Hi! I'm trying to create an User Defined function that gives me the top rows
of a table. I must be able to define wich is the top number of rows to be r
eturned. I assume I could do this:
CREATE FUNCTION ListTopRegions (@.reg_id int, @.num int)
RETURNS @.table table ([TerritoryID] [nvarchar] (20), [TerritoryDescription] [nchar] (50))
AS
begin
if exists (select 1 from Region where RegionID = @.reg_id)
begin
insert into @.table select top @.num TerritoryID, TerritoryDescription from Te
rritories
where RegionID=@.reg_id
end
return
end
but it keeps saying : Incorrect syntax near '@.num'. Any idea what I'm doing
wrong? I'm using the Northwing DB, and I intend to get the first 5 entries,
for instance.You have to use a hard-coded value in a TOP clause in SQL Server 2000.
You'll be able to use expressions in SQL Server 2005. You didn't say what
determines the order. For example, is it the TOP (n) sorted by TerritoryID?
In that case, try:
select
o.TerritoryID
, o.TerritoryDescription
from
Territories o
join
Territories i on i.TerritoryID <= o.TerritoryID
and i.RegionID = o.RegionID
where
o.RegionID = @.reg_id
group by
o.TerritoryID
, o.TerritoryDescription
having
count (*) <= @.num
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"TechnoSpike" <TechnoSpike.1mdf1a@.mail.webservertalk.com> wrote in message
news:TechnoSpike.1mdf1a@.mail.webservertalk.com...
Hi! I'm trying to create an User Defined function that gives me the top
rows of a table. I must be able to define wich is the top number of
rows to be returned. I assume I could do this:
CREATE FUNCTION ListTopRegions (@.reg_id int, @.num int)
RETURNS @.table table ([TerritoryID] [nvarchar] (20),
[TerritoryDescription] [nchar] (50))
AS
begin
if exists (select 1 from Region where RegionID = @.reg_id)
begin
insert into @.table select top @.num TerritoryID, TerritoryDescription
from Territories
where RegionID=@.reg_id
end
return
end
but it keeps saying : Incorrect syntax near '@.num'. Any idea what I'm
doing wrong? I'm using the Northwing DB, and I intend to get the first
5 entries, for instance.
TechnoSpike
---
Posted via http://www.webservertalk.com
---
View this thread: http://www.webservertalk.com/message967438.html|||You could use the SET ROWCOUNT command. This will limit the number of rows
returned from the query similar to the TOP function.
"TechnoSpike" wrote:
> Hi! I'm trying to create an User Defined function that gives me the top
> rows of a table. I must be able to define wich is the top number of
> rows to be returned. I assume I could do this:
> CREATE FUNCTION ListTopRegions (@.reg_id int, @.num int)
> RETURNS @.table table ([TerritoryID] [nvarchar] (20),
> [TerritoryDescription] [nchar] (50))
> AS
> begin
> if exists (select 1 from Region where RegionID = @.reg_id)
> begin
> insert into @.table select top @.num TerritoryID, TerritoryDescription
> from Territories
> where RegionID=@.reg_id
> end
> return
> end
> but it keeps saying : Incorrect syntax near '@.num'. Any idea what I'm
> doing wrong? I'm using the Northwing DB, and I intend to get the first
> 5 entries, for instance.
>
> --
> TechnoSpike
> ---
> Posted via http://www.webservertalk.com
> ---
> View this thread: http://www.webservertalk.com/message967438.html
>
of a table. I must be able to define wich is the top number of rows to be r
eturned. I assume I could do this:
CREATE FUNCTION ListTopRegions (@.reg_id int, @.num int)
RETURNS @.table table ([TerritoryID] [nvarchar] (20), [TerritoryDescription] [nchar] (50))
AS
begin
if exists (select 1 from Region where RegionID = @.reg_id)
begin
insert into @.table select top @.num TerritoryID, TerritoryDescription from Te
rritories
where RegionID=@.reg_id
end
return
end
but it keeps saying : Incorrect syntax near '@.num'. Any idea what I'm doing
wrong? I'm using the Northwing DB, and I intend to get the first 5 entries,
for instance.You have to use a hard-coded value in a TOP clause in SQL Server 2000.
You'll be able to use expressions in SQL Server 2005. You didn't say what
determines the order. For example, is it the TOP (n) sorted by TerritoryID?
In that case, try:
select
o.TerritoryID
, o.TerritoryDescription
from
Territories o
join
Territories i on i.TerritoryID <= o.TerritoryID
and i.RegionID = o.RegionID
where
o.RegionID = @.reg_id
group by
o.TerritoryID
, o.TerritoryDescription
having
count (*) <= @.num
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"TechnoSpike" <TechnoSpike.1mdf1a@.mail.webservertalk.com> wrote in message
news:TechnoSpike.1mdf1a@.mail.webservertalk.com...
Hi! I'm trying to create an User Defined function that gives me the top
rows of a table. I must be able to define wich is the top number of
rows to be returned. I assume I could do this:
CREATE FUNCTION ListTopRegions (@.reg_id int, @.num int)
RETURNS @.table table ([TerritoryID] [nvarchar] (20),
[TerritoryDescription] [nchar] (50))
AS
begin
if exists (select 1 from Region where RegionID = @.reg_id)
begin
insert into @.table select top @.num TerritoryID, TerritoryDescription
from Territories
where RegionID=@.reg_id
end
return
end
but it keeps saying : Incorrect syntax near '@.num'. Any idea what I'm
doing wrong? I'm using the Northwing DB, and I intend to get the first
5 entries, for instance.
TechnoSpike
---
Posted via http://www.webservertalk.com
---
View this thread: http://www.webservertalk.com/message967438.html|||You could use the SET ROWCOUNT command. This will limit the number of rows
returned from the query similar to the TOP function.
"TechnoSpike" wrote:
> Hi! I'm trying to create an User Defined function that gives me the top
> rows of a table. I must be able to define wich is the top number of
> rows to be returned. I assume I could do this:
> CREATE FUNCTION ListTopRegions (@.reg_id int, @.num int)
> RETURNS @.table table ([TerritoryID] [nvarchar] (20),
> [TerritoryDescription] [nchar] (50))
> AS
> begin
> if exists (select 1 from Region where RegionID = @.reg_id)
> begin
> insert into @.table select top @.num TerritoryID, TerritoryDescription
> from Territories
> where RegionID=@.reg_id
> end
> return
> end
> but it keeps saying : Incorrect syntax near '@.num'. Any idea what I'm
> doing wrong? I'm using the Northwing DB, and I intend to get the first
> 5 entries, for instance.
>
> --
> TechnoSpike
> ---
> Posted via http://www.webservertalk.com
> ---
> View this thread: http://www.webservertalk.com/message967438.html
>
Problems using TOP and User Functions
Hi! I'm trying to create an User Defined function that gives me the top
rows of a table. I must be able to define wich is the top number of
rows to be returned. I assume I could do this:
CREATE FUNCTION ListTopRegions (@.reg_id int, @.num int)
RETURNS @.table table ([TerritoryID] [nvarchar] (20),
[TerritoryDescription] [nchar] (50))
AS
begin
if exists (select 1 from Region where RegionID = @.reg_id)
begin
insert into @.table select top @.num TerritoryID, TerritoryDescription
from Territories
where RegionID=@.reg_id
end
return
end
but it keeps saying : Incorrect syntax near '@.num'. Any idea what I'm
doing wrong? I'm using the Northwing DB, and I intend to get the first
5 entries, for instance.
--
TechnoSpike
---
Posted via http://www.webservertalk.com
---
View this thread: http://www.webservertalk.com/message967438.htmlYou have to use a hard-coded value in a TOP clause in SQL Server 2000.
You'll be able to use expressions in SQL Server 2005. You didn't say what
determines the order. For example, is it the TOP (n) sorted by TerritoryID?
In that case, try:
select
o.TerritoryID
, o.TerritoryDescription
from
Territories o
join
Territories i on i.TerritoryID <= o.TerritoryID
and i.RegionID = o.RegionID
where
o.RegionID = @.reg_id
group by
o.TerritoryID
, o.TerritoryDescription
having
count (*) <= @.num
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"TechnoSpike" <TechnoSpike.1mdf1a@.mail.webservertalk.com> wrote in message
news:TechnoSpike.1mdf1a@.mail.webservertalk.com...
Hi! I'm trying to create an User Defined function that gives me the top
rows of a table. I must be able to define wich is the top number of
rows to be returned. I assume I could do this:
CREATE FUNCTION ListTopRegions (@.reg_id int, @.num int)
RETURNS @.table table ([TerritoryID] [nvarchar] (20),
[TerritoryDescription] [nchar] (50))
AS
begin
if exists (select 1 from Region where RegionID = @.reg_id)
begin
insert into @.table select top @.num TerritoryID, TerritoryDescription
from Territories
where RegionID=@.reg_id
end
return
end
but it keeps saying : Incorrect syntax near '@.num'. Any idea what I'm
doing wrong? I'm using the Northwing DB, and I intend to get the first
5 entries, for instance.
TechnoSpike
---
Posted via http://www.webservertalk.com
---
View this thread: http://www.webservertalk.com/message967438.html|||You could use the SET ROWCOUNT command. This will limit the number of rows
returned from the query similar to the TOP function.
"TechnoSpike" wrote:
> Hi! I'm trying to create an User Defined function that gives me the top
> rows of a table. I must be able to define wich is the top number of
> rows to be returned. I assume I could do this:
> CREATE FUNCTION ListTopRegions (@.reg_id int, @.num int)
> RETURNS @.table table ([TerritoryID] [nvarchar] (20),
> [TerritoryDescription] [nchar] (50))
> AS
> begin
> if exists (select 1 from Region where RegionID = @.reg_id)
> begin
> insert into @.table select top @.num TerritoryID, TerritoryDescription
> from Territories
> where RegionID=@.reg_id
> end
> return
> end
> but it keeps saying : Incorrect syntax near '@.num'. Any idea what I'm
> doing wrong? I'm using the Northwing DB, and I intend to get the first
> 5 entries, for instance.
>
> --
> TechnoSpike
> ---
> Posted via http://www.webservertalk.com
> ---
> View this thread: http://www.webservertalk.com/message967438.html
>
rows of a table. I must be able to define wich is the top number of
rows to be returned. I assume I could do this:
CREATE FUNCTION ListTopRegions (@.reg_id int, @.num int)
RETURNS @.table table ([TerritoryID] [nvarchar] (20),
[TerritoryDescription] [nchar] (50))
AS
begin
if exists (select 1 from Region where RegionID = @.reg_id)
begin
insert into @.table select top @.num TerritoryID, TerritoryDescription
from Territories
where RegionID=@.reg_id
end
return
end
but it keeps saying : Incorrect syntax near '@.num'. Any idea what I'm
doing wrong? I'm using the Northwing DB, and I intend to get the first
5 entries, for instance.
--
TechnoSpike
---
Posted via http://www.webservertalk.com
---
View this thread: http://www.webservertalk.com/message967438.htmlYou have to use a hard-coded value in a TOP clause in SQL Server 2000.
You'll be able to use expressions in SQL Server 2005. You didn't say what
determines the order. For example, is it the TOP (n) sorted by TerritoryID?
In that case, try:
select
o.TerritoryID
, o.TerritoryDescription
from
Territories o
join
Territories i on i.TerritoryID <= o.TerritoryID
and i.RegionID = o.RegionID
where
o.RegionID = @.reg_id
group by
o.TerritoryID
, o.TerritoryDescription
having
count (*) <= @.num
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"TechnoSpike" <TechnoSpike.1mdf1a@.mail.webservertalk.com> wrote in message
news:TechnoSpike.1mdf1a@.mail.webservertalk.com...
Hi! I'm trying to create an User Defined function that gives me the top
rows of a table. I must be able to define wich is the top number of
rows to be returned. I assume I could do this:
CREATE FUNCTION ListTopRegions (@.reg_id int, @.num int)
RETURNS @.table table ([TerritoryID] [nvarchar] (20),
[TerritoryDescription] [nchar] (50))
AS
begin
if exists (select 1 from Region where RegionID = @.reg_id)
begin
insert into @.table select top @.num TerritoryID, TerritoryDescription
from Territories
where RegionID=@.reg_id
end
return
end
but it keeps saying : Incorrect syntax near '@.num'. Any idea what I'm
doing wrong? I'm using the Northwing DB, and I intend to get the first
5 entries, for instance.
TechnoSpike
---
Posted via http://www.webservertalk.com
---
View this thread: http://www.webservertalk.com/message967438.html|||You could use the SET ROWCOUNT command. This will limit the number of rows
returned from the query similar to the TOP function.
"TechnoSpike" wrote:
> Hi! I'm trying to create an User Defined function that gives me the top
> rows of a table. I must be able to define wich is the top number of
> rows to be returned. I assume I could do this:
> CREATE FUNCTION ListTopRegions (@.reg_id int, @.num int)
> RETURNS @.table table ([TerritoryID] [nvarchar] (20),
> [TerritoryDescription] [nchar] (50))
> AS
> begin
> if exists (select 1 from Region where RegionID = @.reg_id)
> begin
> insert into @.table select top @.num TerritoryID, TerritoryDescription
> from Territories
> where RegionID=@.reg_id
> end
> return
> end
> but it keeps saying : Incorrect syntax near '@.num'. Any idea what I'm
> doing wrong? I'm using the Northwing DB, and I intend to get the first
> 5 entries, for instance.
>
> --
> TechnoSpike
> ---
> Posted via http://www.webservertalk.com
> ---
> View this thread: http://www.webservertalk.com/message967438.html
>
Problems using TOP and User Functions
Hi! I'm trying to create an User Defined function that gives me the top
rows of a table. I must be able to define wich is the top number of
rows to be returned. I assume I could do this:
CREATE FUNCTION ListTopRegions (@.reg_id int, @.num int)
RETURNS @.table table ([TerritoryID] [nvarchar] (20),
[TerritoryDescription] [nchar] (50))
AS
begin
if exists (select 1 from Region where RegionID = @.reg_id)
begin
insert into @.table select top @.num TerritoryID, TerritoryDescription
from Territories
where RegionID=@.reg_id
end
return
end
but it keeps saying : Incorrect syntax near '@.num'. Any idea what I'm
doing wrong? I'm using the Northwing DB, and I intend to get the first
5 entries, for instance.
TechnoSpike
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message967438.html
You have to use a hard-coded value in a TOP clause in SQL Server 2000.
You'll be able to use expressions in SQL Server 2005. You didn't say what
determines the order. For example, is it the TOP (n) sorted by TerritoryID?
In that case, try:
select
o.TerritoryID
, o.TerritoryDescription
from
Territories o
join
Territories i on i.TerritoryID <= o.TerritoryID
and i.RegionID = o.RegionID
where
o.RegionID = @.reg_id
group by
o.TerritoryID
, o.TerritoryDescription
having
count (*) <= @.num
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"TechnoSpike" <TechnoSpike.1mdf1a@.mail.webservertalk.com> wrote in message
news:TechnoSpike.1mdf1a@.mail.webservertalk.com...
Hi! I'm trying to create an User Defined function that gives me the top
rows of a table. I must be able to define wich is the top number of
rows to be returned. I assume I could do this:
CREATE FUNCTION ListTopRegions (@.reg_id int, @.num int)
RETURNS @.table table ([TerritoryID] [nvarchar] (20),
[TerritoryDescription] [nchar] (50))
AS
begin
if exists (select 1 from Region where RegionID = @.reg_id)
begin
insert into @.table select top @.num TerritoryID, TerritoryDescription
from Territories
where RegionID=@.reg_id
end
return
end
but it keeps saying : Incorrect syntax near '@.num'. Any idea what I'm
doing wrong? I'm using the Northwing DB, and I intend to get the first
5 entries, for instance.
TechnoSpike
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message967438.html
|||You could use the SET ROWCOUNT command. This will limit the number of rows
returned from the query similar to the TOP function.
"TechnoSpike" wrote:
> Hi! I'm trying to create an User Defined function that gives me the top
> rows of a table. I must be able to define wich is the top number of
> rows to be returned. I assume I could do this:
> CREATE FUNCTION ListTopRegions (@.reg_id int, @.num int)
> RETURNS @.table table ([TerritoryID] [nvarchar] (20),
> [TerritoryDescription] [nchar] (50))
> AS
> begin
> if exists (select 1 from Region where RegionID = @.reg_id)
> begin
> insert into @.table select top @.num TerritoryID, TerritoryDescription
> from Territories
> where RegionID=@.reg_id
> end
> return
> end
> but it keeps saying : Incorrect syntax near '@.num'. Any idea what I'm
> doing wrong? I'm using the Northwing DB, and I intend to get the first
> 5 entries, for instance.
>
> --
> TechnoSpike
> Posted via http://www.webservertalk.com
> View this thread: http://www.webservertalk.com/message967438.html
>
rows of a table. I must be able to define wich is the top number of
rows to be returned. I assume I could do this:
CREATE FUNCTION ListTopRegions (@.reg_id int, @.num int)
RETURNS @.table table ([TerritoryID] [nvarchar] (20),
[TerritoryDescription] [nchar] (50))
AS
begin
if exists (select 1 from Region where RegionID = @.reg_id)
begin
insert into @.table select top @.num TerritoryID, TerritoryDescription
from Territories
where RegionID=@.reg_id
end
return
end
but it keeps saying : Incorrect syntax near '@.num'. Any idea what I'm
doing wrong? I'm using the Northwing DB, and I intend to get the first
5 entries, for instance.
TechnoSpike
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message967438.html
You have to use a hard-coded value in a TOP clause in SQL Server 2000.
You'll be able to use expressions in SQL Server 2005. You didn't say what
determines the order. For example, is it the TOP (n) sorted by TerritoryID?
In that case, try:
select
o.TerritoryID
, o.TerritoryDescription
from
Territories o
join
Territories i on i.TerritoryID <= o.TerritoryID
and i.RegionID = o.RegionID
where
o.RegionID = @.reg_id
group by
o.TerritoryID
, o.TerritoryDescription
having
count (*) <= @.num
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"TechnoSpike" <TechnoSpike.1mdf1a@.mail.webservertalk.com> wrote in message
news:TechnoSpike.1mdf1a@.mail.webservertalk.com...
Hi! I'm trying to create an User Defined function that gives me the top
rows of a table. I must be able to define wich is the top number of
rows to be returned. I assume I could do this:
CREATE FUNCTION ListTopRegions (@.reg_id int, @.num int)
RETURNS @.table table ([TerritoryID] [nvarchar] (20),
[TerritoryDescription] [nchar] (50))
AS
begin
if exists (select 1 from Region where RegionID = @.reg_id)
begin
insert into @.table select top @.num TerritoryID, TerritoryDescription
from Territories
where RegionID=@.reg_id
end
return
end
but it keeps saying : Incorrect syntax near '@.num'. Any idea what I'm
doing wrong? I'm using the Northwing DB, and I intend to get the first
5 entries, for instance.
TechnoSpike
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message967438.html
|||You could use the SET ROWCOUNT command. This will limit the number of rows
returned from the query similar to the TOP function.
"TechnoSpike" wrote:
> Hi! I'm trying to create an User Defined function that gives me the top
> rows of a table. I must be able to define wich is the top number of
> rows to be returned. I assume I could do this:
> CREATE FUNCTION ListTopRegions (@.reg_id int, @.num int)
> RETURNS @.table table ([TerritoryID] [nvarchar] (20),
> [TerritoryDescription] [nchar] (50))
> AS
> begin
> if exists (select 1 from Region where RegionID = @.reg_id)
> begin
> insert into @.table select top @.num TerritoryID, TerritoryDescription
> from Territories
> where RegionID=@.reg_id
> end
> return
> end
> but it keeps saying : Incorrect syntax near '@.num'. Any idea what I'm
> doing wrong? I'm using the Northwing DB, and I intend to get the first
> 5 entries, for instance.
>
> --
> TechnoSpike
> Posted via http://www.webservertalk.com
> View this thread: http://www.webservertalk.com/message967438.html
>
Subscribe to:
Posts (Atom)