Getting a syntax error involving Microsoft.SqlServer.SmoEnum

Last month we upgraded our SQL Server 2000 production database to SQL Server
2005. It also has SP1 applied to it. I was just checking the jobs, making
certain that everything was running OK. We have 7 jobs defined, when I
clicked on the View Job History link on each of the job's property page, I
got a syntax error involving Microsoft.SqlServer.SmoEnum, but really nothing
else mentioned. Here is the text of the error message:
syntax error (ackup_SystemDBs_Weekly'']) (Microsoft.SqlServer.SmoEnum)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
Going to the link didn't help me.
So, how do I find out what's wrong and fix it, please?
RodHi Rod
Does this happen on every job? If it does then how did you upgrade the jobs?
You may want to manually see what is in the sysjobhistory table for that job.
You may want to use SQL Profiler to see what SQL is being called and check
that there is no errors.
Try running DBCC CHECKDB on MSDB.
If that does not work try scripting the job and creating it as a different
one, then disable the original.
John
"Rod" wrote:
> Last month we upgraded our SQL Server 2000 production database to SQL Server
> 2005. It also has SP1 applied to it. I was just checking the jobs, making
> certain that everything was running OK. We have 7 jobs defined, when I
> clicked on the View Job History link on each of the job's property page, I
> got a syntax error involving Microsoft.SqlServer.SmoEnum, but really nothing
> else mentioned. Here is the text of the error message:
> syntax error (ackup_SystemDBs_Weekly'']) (Microsoft.SqlServer.SmoEnum)
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
>
> Going to the link didn't help me.
> So, how do I find out what's wrong and fix it, please?
> Rod
>
>
>|||John,
Of the 7 jobs that were in SQL Server 2000 before the upgrade, 6 of them
cause this error to occur. The 7th does work.
Rod
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:4226D126-BA6B-4E45-A6ED-553F767C2E7A@.microsoft.com...
> Hi Rod
> Does this happen on every job? If it does then how did you upgrade the
> jobs?
> You may want to manually see what is in the sysjobhistory table for that
> job.
> You may want to use SQL Profiler to see what SQL is being called and check
> that there is no errors.
> Try running DBCC CHECKDB on MSDB.
> If that does not work try scripting the job and creating it as a different
> one, then disable the original.
> John
> "Rod" wrote:
>> Last month we upgraded our SQL Server 2000 production database to SQL
>> Server
>> 2005. It also has SP1 applied to it. I was just checking the jobs,
>> making
>> certain that everything was running OK. We have 7 jobs defined, when I
>> clicked on the View Job History link on each of the job's property page,
>> I
>> got a syntax error involving Microsoft.SqlServer.SmoEnum, but really
>> nothing
>> else mentioned. Here is the text of the error message:
>> syntax error (ackup_SystemDBs_Weekly'']) (Microsoft.SqlServer.SmoEnum)
>> For help, click:
>> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
>>
>> Going to the link didn't help me.
>> So, how do I find out what's wrong and fix it, please?
>> Rod
>>
>>|||Hi Rod
How important is the history? If not then try to re-create the job.
John
"Rod" wrote:
> John,
> Of the 7 jobs that were in SQL Server 2000 before the upgrade, 6 of them
> cause this error to occur. The 7th does work.
> Rod
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:4226D126-BA6B-4E45-A6ED-553F767C2E7A@.microsoft.com...
> > Hi Rod
> >
> > Does this happen on every job? If it does then how did you upgrade the
> > jobs?
> >
> > You may want to manually see what is in the sysjobhistory table for that
> > job.
> >
> > You may want to use SQL Profiler to see what SQL is being called and check
> > that there is no errors.
> >
> > Try running DBCC CHECKDB on MSDB.
> >
> > If that does not work try scripting the job and creating it as a different
> > one, then disable the original.
> >
> > John
> >
> > "Rod" wrote:
> >
> >> Last month we upgraded our SQL Server 2000 production database to SQL
> >> Server
> >> 2005. It also has SP1 applied to it. I was just checking the jobs,
> >> making
> >> certain that everything was running OK. We have 7 jobs defined, when I
> >> clicked on the View Job History link on each of the job's property page,
> >> I
> >> got a syntax error involving Microsoft.SqlServer.SmoEnum, but really
> >> nothing
> >> else mentioned. Here is the text of the error message:
> >>
> >> syntax error (ackup_SystemDBs_Weekly'']) (Microsoft.SqlServer.SmoEnum)
> >>
> >> For help, click:
> >> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
> >>
> >>
> >> Going to the link didn't help me.
> >>
> >> So, how do I find out what's wrong and fix it, please?
> >>
> >> Rod
> >>
> >>
> >>
> >>
> >>
>
>

Getting a syntax error involving Microsoft.SqlServer.SmoEnum

Last month we upgraded our SQL Server 2000 production database to SQL Server
2005. It also has SP1 applied to it. I was just checking the jobs, making
certain that everything was running OK. We have 7 jobs defined, when I
clicked on the View Job History link on each of the job's property page, I
got a syntax error involving Microsoft.SqlServer.SmoEnum, but really nothing
else mentioned. Here is the text of the error message:
syntax error (ackup_SystemDBs_Weekly'']) (Microsoft.SqlServer.SmoEnum)
For help, click:
http://go.microsoft.com/fwlink?Prod...er&LinkId=20476
Going to the link didn't help me.
So, how do I find out what's wrong and fix it, please?
RodHi Rod
Does this happen on every job? If it does then how did you upgrade the jobs?
You may want to manually see what is in the sysjobhistory table for that job
.
You may want to use SQL Profiler to see what SQL is being called and check
that there is no errors.
Try running DBCC CHECKDB on MSDB.
If that does not work try scripting the job and creating it as a different
one, then disable the original.
John
"Rod" wrote:

> Last month we upgraded our SQL Server 2000 production database to SQL Serv
er
> 2005. It also has SP1 applied to it. I was just checking the jobs, makin
g
> certain that everything was running OK. We have 7 jobs defined, when I
> clicked on the View Job History link on each of the job's property page, I
> got a syntax error involving Microsoft.SqlServer.SmoEnum, but really nothi
ng
> else mentioned. Here is the text of the error message:
> syntax error (ackup_SystemDBs_Weekly'']) (Microsoft.SqlServer.SmoEnum)
> For help, click:
> http://go.microsoft.com/fwlink?Prod...er&LinkId=20476
>
> Going to the link didn't help me.
> So, how do I find out what's wrong and fix it, please?
> Rod
>
>
>|||John,
Of the 7 jobs that were in SQL Server 2000 before the upgrade, 6 of them
cause this error to occur. The 7th does work.
Rod
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:4226D126-BA6B-4E45-A6ED-553F767C2E7A@.microsoft.com...[vbcol=seagreen]
> Hi Rod
> Does this happen on every job? If it does then how did you upgrade the
> jobs?
> You may want to manually see what is in the sysjobhistory table for that
> job.
> You may want to use SQL Profiler to see what SQL is being called and check
> that there is no errors.
> Try running DBCC CHECKDB on MSDB.
> If that does not work try scripting the job and creating it as a different
> one, then disable the original.
> John
> "Rod" wrote:
>|||Hi Rod
How important is the history? If not then try to re-create the job.
John
"Rod" wrote:

> John,
> Of the 7 jobs that were in SQL Server 2000 before the upgrade, 6 of them
> cause this error to occur. The 7th does work.
> Rod
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:4226D126-BA6B-4E45-A6ED-553F767C2E7A@.microsoft.com...
>
>

getting a date in the past

Hi,
Does anyone knows the select syntax for getting a date in the past but
close the current date.
For example: i have a table of addresses with an id, startdate, street,
etc. Now what i would like to do, is get the date that is close to the
current date. The outcome of it, is the current address of a person.
Is this possible with use of the columns id and startdate or just startdate?Please post DDL if you are refering to columns in your tables
http://www.aspfaq.com/5006
Select TOP 1 <columnlist>
>From SomeTable
Where id = <Someid>
Order by Startdate desc
HTH, Jens Suessmeyer.|||select DATEDIFF(dd, StartDate, getdate()), * from YourTable
order by DATEDIFF(dd, StartDate, getdate())
dd = Days. This can be substitued for hours, minutes, seconds etc. Have a
look at DATEDIFF function in SQL Books Online
HTH. Ryan
"Jason" <jasonlewis@.hotmail.com> wrote in message
news:%23Spc$gZIGHA.1876@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Does anyone knows the select syntax for getting a date in the past but
> close the current date.
> For example: i have a table of addresses with an id, startdate, street,
> etc. Now what i would like to do, is get the date that is close to the
> current date. The outcome of it, is the current address of a person.
> Is this possible with use of the columns id and startdate or just
> startdate?

Gettin data fromdifferent database

Hi

My query syntax is such

select 'INSERT INTO UserCourse(Start_Date, User_ID) values
(Course_Date + ''', ''' + rtrim(convert(varchar(20),AppUser.ID)) + ''');'
FROM DB1..EMPLOYEE_TRAINING, DB2..User
WHERE DB2..User.Employee_Number = DB1..EMPLOYEE_TRAINING.EMPLOYEE_NO

I run this query in the Query Analyser on the database DB1 and get the following error

The column prefix 'User' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'DB1..User' does not match with a table name or alias name used in the query.

What I need is to get the ID and Date from differnt tables in different databases
Any clues or suggestions
NimishaTry changing:

FROM DB1..EMPLOYEE_TRAINING, DB2..User

WHERE DB2..User.Employee_Number = DB1..EMPLOYEE_TRAINING.EMPLOYEE_NO

to

FROM [DB1].dbo.EMPLOYEE_TRAINING, DB2..User

WHERE [DB2].dbo.User.Employee_Number = [DB1].dbo.EMPLOYEE_TRAINING.EMPLOYEE_NO

HTH

Ken|||Hi,
Try this
In clause replace the DB2..User
with servername.databasename.userid.tablename
and in where clause simply use the column name.

e.g

select *
from a, ndokech1.echi.dbo.tx_echi c
where a.myid = c.myid

i think it will work..

Cheers
Gola munjal

Originally posted by Nimisha
Hi

My query syntax is such

select 'INSERT INTO UserCourse(Start_Date, User_ID) values
(Course_Date + ''', ''' + rtrim(convert(varchar(20),AppUser.ID)) + ''');'
FROM DB1..EMPLOYEE_TRAINING, DB2..User
WHERE DB2..User.Employee_Number = DB1..EMPLOYEE_TRAINING.EMPLOYEE_NO

I run this query in the Query Analyser on the database DB1 and get the following error

The column prefix 'User' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'DB1..User' does not match with a table name or alias name used in the query.

What I need is to get the ID and Date from differnt tables in different databases
Any clues or suggestions
Nimisha|||Sorry i thought u asked for getting data from different servers.
i think the problem is with table name user, bcause user is a keyword in sql.
give user table name with in [] this.
select *
from fscms..[user] ,fscms_backup..[user]
where fscms..[user].Employee_Number = fscms_backup..[user].Employee_Number

i tried this ..its working ..

Cheers
gola

Originally posted by GA_KEN
Try changing:

FROM DB1..EMPLOYEE_TRAINING, DB2..User

WHERE DB2..User.Employee_Number = DB1..EMPLOYEE_TRAINING.EMPLOYEE_NO

to

FROM [DB1].dbo.EMPLOYEE_TRAINING, DB2..User

WHERE [DB2].dbo.User.Employee_Number = [DB1].dbo.EMPLOYEE_TRAINING.EMPLOYEE_NO

HTH

Ken|||Thanx guys for all the input apprecaite it.
Nimisha

GetDate() in User Defined Functions, Parameters in View

1) Am I right in believing I can't access the GetDate() function from within
a table valued UDF ... ? That seems to be what the syntax checker is
telling me. Any suggested workarounds ?
2) I tried create a view which used a table valued UDF, with " getdate() "
as a parameter, in the view's from clause and the system didn't like that
either ... Any suggestions ? Are there ways of parameterizing a view ?
3) I need the view construct because I need to reference the returned
dataset from Analysis Services as a dimension. I prefer the UDF construct
(as opposed to selecting off a table with a where clause) because I don't
have to build a process to add new date records to the hypothetical table.
Performance isn't really an issue, because the only place the view is invoke
d
is in processing a cube in Analysis Services.
4) Some SQL
...
CREATE FUNCTION dbo.tfun_Date (
@.EndDate smalldatetime) --added when getdate didn't work
RETURNS @.DateTable table (
DateValue smalldatetime )
BEGIN
Declare @.DateIdx smalldatetime
Declare @.StartDate smalldatetime
Set @.StartDate = dbo.sfun_getdateparmref('DateDim1Start')
Set @.DateIdx = @.StartDate
--Set @.EndDate = getdate()
While @.DateIdx <= @.EndDate
Begin
Insert @.DateTable (DateValue) values (@.DateIdx)
Set @.DateIdx = DateAdd(dd,1, @.DateIdx)
End
Return
END
--
CREATE VIEW dbo.v_date
AS
SELECT top 10000 DateValue
, DateYYYY = Datepart(yyyy, DateValue)
, DateYYAbbrev = RIGHT(CONVERT(Char(4), Datepart(yy, DateValue)), 2)
, DateQtr = CASE Datepart(mm, DateValue)
WHEN 1 THEN 'Q1' WHEN 2 THEN 'Q1' WHEN 3 THEN 'Q1'
WHEN 4 THEN 'Q2' WHEN 5 THEN 'Q2' WHEN 6 THEN 'Q2' WHEN 7 THEN 'Q3' WHEN 8
THEN
'Q3' WHEN 9 THEN 'Q3' WHEN 10 THEN 'Q4' WHEN 11 THEN
'Q4' WHEN 12 THEN 'Q4' ELSE 'Er' END
, DateMM = Datepart(mm, DateValue)
, DateMMAbbrev = CASE Datepart(mm, DateValue)
WHEN 1 THEN 'Jan' WHEN 2 THEN 'Feb' WHEN 3 THEN 'Mar'
WHEN 4 THEN 'Apr' WHEN 5 THEN 'May' WHEN 6 THEN 'Jun' WHEN 7 THEN 'Jul' WHEN
8 THEN 'Aug' WHEN 9 THEN 'Sep' WHEN 10 THEN 'Oct'
WHEN 11 THEN 'Nov' WHEN 12 THEN 'Dec' ELSE 'Err' END
, DateDD = Datepart(dd,
DateValue), DowNbr = Datepart(dw, DateValue)
, DowAbbr = CASE Datepart(dw, DateValue)
WHEN 1 THEN 'Mon' WHEN 2 THEN 'Tue' WHEN 3 THEN 'Wed'
WHEN 4 THEN 'Thu' WHEN 5 THEN 'Fri' WHEN 6 THEN 'Sat' WHEN 7 THEN 'Sun' ELSE
'Err' END
, DayOfYear = DateDiff(d, CONVERT(smalldatetime, CONVERT(char(4),
Datepart(yyyy, DateValue)) + '01' + '01', 112), DateValue) + 1
, WeekOfYear = 1 + (DateDiff(d, CONVERT(smalldatetime, CONVERT(char(4),
Datepart(yyyy, DateValue)) + '01' + '01', 112), DateValue) + 7 - Datepart(dw
,
DateValue)) / 7
, WeekOfYearMondayDate = dateadd(dd, 1 - Datepart(dw, DateValue), DateValue)
, Workday = Case
When Datepart(dw, DateValue) > 5 then 'Weekend / Holiday'
When PublicHolidayFlag = 'H' then 'Weekend / Holiday'
Else 'Workday' End
, HolidayName
, PublicHolidayFlag
--THIS IS THE BOGUS LINE
FROM tfun_date( getdate() ) D
--ENDS HERE
Left outer Join ZR_PublicHols on D.DateValue = ZR_PublicHols.HolidayDate
order by DateValueYou could pass in to the UDF a new parameter which when called, you send it
the GETDATE/CURRENTTIMESTAMP function.
Then just use that new parameter as your getdate()
hth
Eric
MarcusW wrote:
> 1) Am I right in believing I can't access the GetDate() function from
> within a table valued UDF ... ? That seems to be what the syntax
> checker is telling me. Any suggested workarounds ?
> 2) I tried create a view which used a table valued UDF, with "
> getdate() " as a parameter, in the view's from clause and the system
> didn't like that either ... Any suggestions ? Are there ways of
> parameterizing a view ?
> 3) I need the view construct because I need to reference the returned
> dataset from Analysis Services as a dimension. I prefer the UDF
> construct (as opposed to selecting off a table with a where clause)
> because I don't have to build a process to add new date records to
> the hypothetical table. Performance isn't really an issue, because
> the only place the view is invoked is in processing a cube in
> Analysis Services.
> 4) Some SQL
> ...
> CREATE FUNCTION dbo.tfun_Date (
> @.EndDate smalldatetime) --added when getdate didn't work
> RETURNS @.DateTable table (
> DateValue smalldatetime )
> BEGIN
> Declare @.DateIdx smalldatetime
> Declare @.StartDate smalldatetime
> Set @.StartDate = dbo.sfun_getdateparmref('DateDim1Start')
> Set @.DateIdx = @.StartDate
> --Set @.EndDate = getdate()
> While @.DateIdx <= @.EndDate
> Begin
> Insert @.DateTable (DateValue) values (@.DateIdx)
> Set @.DateIdx = DateAdd(dd,1, @.DateIdx)
> End
> Return
> END
> --
> CREATE VIEW dbo.v_date
> AS
> SELECT top 10000 DateValue
> , DateYYYY = Datepart(yyyy, DateValue)
> , DateYYAbbrev = RIGHT(CONVERT(Char(4), Datepart(yy, DateValue)), 2)
> , DateQtr = CASE Datepart(mm, DateValue)
> WHEN 1 THEN 'Q1' WHEN 2 THEN 'Q1' WHEN 3 THEN
> 'Q1' WHEN 4 THEN 'Q2' WHEN 5 THEN 'Q2' WHEN 6 THEN 'Q2' WHEN 7 THEN
> 'Q3' WHEN 8 THEN
> 'Q3' WHEN 9 THEN 'Q3' WHEN 10 THEN 'Q4' WHEN
> 11 THEN 'Q4' WHEN 12 THEN 'Q4' ELSE 'Er' END
> , DateMM = Datepart(mm, DateValue)
> , DateMMAbbrev = CASE Datepart(mm, DateValue)
> WHEN 1 THEN 'Jan' WHEN 2 THEN 'Feb' WHEN 3 THEN
> 'Mar' WHEN 4 THEN 'Apr' WHEN 5 THEN 'May' WHEN 6 THEN 'Jun' WHEN 7
> THEN 'Jul' WHEN 8 THEN 'Aug' WHEN 9 THEN 'Sep'
> WHEN 10 THEN 'Oct'
> WHEN 11 THEN 'Nov' WHEN 12 THEN 'Dec' ELSE 'Err' END
> , DateDD = Datepart(dd,
> DateValue), DowNbr = Datepart(dw, DateValue)
> , DowAbbr = CASE Datepart(dw, DateValue)
> WHEN 1 THEN 'Mon' WHEN 2 THEN 'Tue' WHEN 3 THEN
> 'Wed' WHEN 4 THEN 'Thu' WHEN 5 THEN 'Fri' WHEN 6 THEN 'Sat' WHEN 7
> THEN 'Sun' ELSE 'Err' END
> , DayOfYear = DateDiff(d, CONVERT(smalldatetime, CONVERT(char(4),
> Datepart(yyyy, DateValue)) + '01' + '01', 112), DateValue) + 1
> , WeekOfYear = 1 + (DateDiff(d, CONVERT(smalldatetime,
> CONVERT(char(4), Datepart(yyyy, DateValue)) + '01' + '01', 112),
> DateValue) + 7 - Datepart(dw, DateValue)) / 7
> , WeekOfYearMondayDate = dateadd(dd, 1 - Datepart(dw, DateValue),
> DateValue) , Workday = Case
> When Datepart(dw, DateValue) > 5 then 'Weekend / Holiday'
> When PublicHolidayFlag = 'H' then 'Weekend / Holiday'
> Else 'Workday' End
> , HolidayName
> , PublicHolidayFlag
> --THIS IS THE BOGUS LINE
> FROM tfun_date( getdate() ) D
> --ENDS HERE
> Left outer Join ZR_PublicHols on D.DateValue =
> ZR_PublicHols.HolidayDate order by DateValue|||Marcus

> 1) Am I right in believing I can't access the GetDate() function from
within
> a table valued UDF ... ? That seems to be what the syntax checker is
> telling me. Any suggested workarounds ?
Correct. However, you can create a view that has the GetDate() in it and
then call that view from your UDF. This workaround may or may not continue
to work in future versions.

> 2) I tried create a view which used a table valued UDF, with " getdate() "
> as a parameter, in the view's from clause and the system didn't like that
> either ... Any suggestions ? Are there ways of parameterizing a view ?
An in-line table-valued UDF is a parameterized view. I think the problem
is still the GetDate() which cannot be a parameter to a UDF either. (Why
not? Because. Technically it is the issue of whether a function always
returns the same value or not.)
Russell Fields

Get user syntax

I am building a view for a report and one of the requirements is to grab the
user who is running the report. What would this syntax look like? I thought
it was getuser(), but no luck there.
Thanks,
Ryan
Ryan,
Depending on whether you want a User or a Login, you can try one of the
following: CURRENT_USER, SYSTEM_USER, USER_NAME, SUSER_SNAME. Check them
out in the Books Online, or just run them and choose what you prefer to use.
RLF
"Ryan Mcbee" <RyanMcbee@.discussions.microsoft.com> wrote in message
news:266EBEEE-3FB5-448D-A58B-0EF6D8F006F1@.microsoft.com...
>I am building a view for a report and one of the requirements is to grab
>the
> user who is running the report. What would this syntax look like? I
> thought
> it was getuser(), but no luck there.
> Thanks,
> Ryan

get record with earliest datetime value

Hello all,

Quick sql syntax question:

I have this table:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[REQUESTS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[REQUESTS]
GO

CREATE TABLE [dbo].[REQUESTS] (
[ROW_ID] [uniqueidentifier] NULL ,
[REQUEST_DATE] [datetime] NULL ,
[STATUS] [tinyint] NULL
) ON [PRIMARY]
GO

with these values:

insert into REQUESTS (REQUEST_DATE, STATUS)
values (getdate(), 0)
insert into REQUESTS (REQUEST_DATE, STATUS)
values (getdate(), 1)
insert into REQUESTS (REQUEST_DATE, STATUS)
values (getdate(), 0)

I need to select the single record with a STATUS = 0 with the earliest
REQUEST_DATE

I am using this query:
SELECT TOP 1 ROW_ID FROM REQUEST_LOG WHERE STATUS = 0 ORDER BY
REQUEST_DATE

not sure if this is the way to go...

pointer appreciated
thanksHow about doing this:
1: Change Row_ID from NULL to NOT NULL
CREATE TABLE [dbo].[REQUESTS] (
[ROW_ID] [uniqueidentifier] NOT NULL ,
[REQUEST_DATE] [datetime] NULL ,
[STATUS] [tinyint] NULL
) ON [PRIMARY]
GO

2: Add value for column ROW_ID in INSERT:
insert into REQUESTS (ROW_ID, REQUEST_DATE, STATUS)
values (NEWID(), getdate(), 0)
insert into REQUESTS (ROW_ID, REQUEST_DATE, STATUS)
values (NEWID(), getdate(), 1)
insert into REQUESTS (ROW_ID, REQUEST_DATE, STATUS)
values (NEWID(), getdate(), 0)

3: Use correct table name in SELECT - from REQUEST_LOG to REQUESTS
SELECT TOP 1 ROW_ID FROM REQUESTs WHERE STATUS = 0 ORDER BY
REQUEST_DATE

On Feb 9, 9:59 am, "hharry" <paulquig...@.nyc.comwrote:

Quote:

Originally Posted by

Hello all,
>
Quick sql syntax question:
>
I have this table:
>
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[REQUESTS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[REQUESTS]
GO
>
CREATE TABLE [dbo].[REQUESTS] (
[ROW_ID] [uniqueidentifier] NULL ,
[REQUEST_DATE] [datetime] NULL ,
[STATUS] [tinyint] NULL
) ON [PRIMARY]
GO
>
with these values:
>
insert into REQUESTS (REQUEST_DATE, STATUS)
values (getdate(), 0)
insert into REQUESTS (REQUEST_DATE, STATUS)
values (getdate(), 1)
insert into REQUESTS (REQUEST_DATE, STATUS)
values (getdate(), 0)
>
I need to select the single record with a STATUS = 0 with the earliest
REQUEST_DATE
>
I am using this query:
SELECT TOP 1 ROW_ID FROM REQUEST_LOG WHERE STATUS = 0 ORDER BY
REQUEST_DATE
>
not sure if this is the way to go...
>
pointer appreciated
thanks

|||apologies for the typos

what i should have asked is this:

Is TOP applied after the ORDER BY or before...can someone confirm
this ?|||Yes, TOP is applied after the result set rows are ordered with ORDER BY.

Regards,

Plamen Ratchev
http://www.SQLStudio.com

Get Position() Value in XML.Query

Given the following syntax in SQL Server 2005:
DECLARE @.xmlvar xml
set @.xmlvar = '
<Data>
<Student id="s1">
<Name>Bugs Bunny</Name>
<Class>FFA</Class>
</Student>
<Student id="s2">
<Name>Road Runner</Name>
<Class>Sports</Class>
</Student>
<Student id="s3">
<Name>Wile E. Coyote</Name>
<Class>Science</Class>
</Student>
<Student id="s4">
<Name>Foghorn Leghorn</Name>
<Class>Self Defense</Class>
</Student>
</Data>'
select Data.Student.value('@.id', 'varchar(6)') as id,
Data.Student.value('(Name)[1]', 'varchar(30)') as [name],
Data.Student.value('(Class)[1]', 'varchar(50)') as class
--Data.Student.query('string(position())') as ndx
from @.xmlvar.nodes('/Data/Student') Data(Student)
Which results with:
id name class
s1 Bugs Bunny FFA
s2 Road Runner Sports
s3 Wile E. Coyote Science
s4 Foghorn Leghorn Self Defense
How would I then return another column that gives me the sequence in the
list, such as position() would do in XSL?
id name class ndx
s1 Bugs Bunny FFA 1
s2 Road Runner Sports 2
s3 Wile E. Coyote Science 3
s4 Foghorn Leghorn Self Defense 4
Thanks,
MattMaybe this?
select Data.Student.value('@.id', 'varchar(6)') as id,
Data.Student.value('(Name)[1]', 'varchar(30)') as [name],
Data.Student.value('(Class)[1]', 'varchar(50)') as class ,
Data.Student.value('for $a in . return count($a/../*[. << $a]) +
1','int') as ndx
from @.xmlvar.nodes('/Data/Student') Data(Student)|||Mark,
Thanks that definitly gets me going in the right direction. Can you explain
the statement count($a/../*[. << $a]) , other than count() I get lost after
the "[".
Thanks,
Matt
<markc600@.hotmail.com> wrote in message
news:1169684064.504759.156860@.q2g2000cwa.googlegroups.com...
> Maybe this?
>
> select Data.Student.value('@.id', 'varchar(6)') as id,
> Data.Student.value('(Name)[1]', 'varchar(30)') as [name],
> Data.Student.value('(Class)[1]', 'varchar(50)') as class ,
> Data.Student.value('for $a in . return count($a/../*[. << $a]) +
> 1','int') as ndx
> from @.xmlvar.nodes('/Data/Student') Data(Student)
>|||Matt
This is the count of the preceding siblings for a given node (plus one)
using the << operator which returns true if the
left operand node precedes the right operand node in document order.
This query may make things clearer.
select Data.Student.query('.') as node,
Data.Student.query('for $a in . return $a/../*[. << $a]') as
allprevsiblings
from @.xmlvar.nodes('/Data/Student') Data(Student)
Regards
Mark|||Mark,
Thanks for the explanation but I am by something I am getting with
one of my queries. Using your recommended method works great on one part of
the XML I am working with but in another area the number starts at 7. If I
run different things I get different values. Any ideas? I can't post the
actual XML I am working with but I can send it to you if you would like to
see my issue with my query.
Thanks,
Matt
<markc600@.hotmail.com> wrote in message
news:1169759470.715829.81540@.s48g2000cws.googlegroups.com...
> Matt
> This is the count of the preceding siblings for a given node (plus one)
> using the << operator which returns true if the
> left operand node precedes the right operand node in document order.
> This query may make things clearer.
> select Data.Student.query('.') as node,
> Data.Student.query('for $a in . return $a/../*[. << $a]') as
> allprevsiblings
> from @.xmlvar.nodes('/Data/Student') Data(Student)
>
> Regards
> Mark
>|||That's probably because the element that you select in the nodes() method is
not the first but the 7th child of the parent node.
E.g.
declare @.x xml;
set @.x = N'<a>
<b/>
<b/>
<b/>
<b/>
<b/>
<b/>
<c>1</c>
<c>2</c>
</a>';
select n.value('.' , 'int') as "c"
, n.value('count(for $a in . return $a/../*[.<<$a])+1', 'int') as "pos"
from @.x.nodes('/a/c') N(n)
Best regards
Michael
"Matt" <mdframe@.sorvive-DO-NOT-SEND-SPAM.com> wrote in message
news:O4TVO88QHHA.4844@.TK2MSFTNGP03.phx.gbl...
> Mark,
> Thanks for the explanation but I am by something I am getting
> with one of my queries. Using your recommended method works great on one
> part of the XML I am working with but in another area the number starts at
> 7. If I run different things I get different values. Any ideas? I can't
> post the actual XML I am working with but I can send it to you if you
> would like to see my issue with my query.
> Thanks,
> Matt
>
> <markc600@.hotmail.com> wrote in message
> news:1169759470.715829.81540@.s48g2000cws.googlegroups.com...
>|||Michael,
Is there anyway to always get the result to start with a sequence of 1?
Thanks,
Matt
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:%23yApssPRHHA.1200@.TK2MSFTNGP04.phx.gbl...
> That's probably because the element that you select in the nodes() method
> is not the first but the 7th child of the parent node.
> E.g.
> declare @.x xml;
> set @.x = N'<a>
> <b/>
> <b/>
> <b/>
> <b/>
> <b/>
> <b/>
> <c>1</c>
> <c>2</c>
> </a>';
> select n.value('.' , 'int') as "c"
> , n.value('count(for $a in . return $a/../*[.<<$a])+1', 'int') as "pos"
> from @.x.nodes('/a/c') N(n)
>
> Best regards
> Michael
>
> "Matt" <mdframe@.sorvive-DO-NOT-SEND-SPAM.com> wrote in message
> news:O4TVO88QHHA.4844@.TK2MSFTNGP03.phx.gbl...
>|||There are two ways, one if you know the name you are selecting (note the c
instead of *):
select n.value('.' , 'int') as "c"
, n.value('count(for $a in . return $a/../c[.<<$a])+1', 'int') as
"pos"
from @.x.nodes('/a/c') N(n)
Or if you don't know the name apriori, you can normalize:
with T as
(select n.value('.' , 'int') as "c"
, n.value('count(for $a in . return $a/../*[.<<$a])+1', 'int') as
"pos"
from @.x.nodes('/a/c') N(n))
select c, pos - (select min(pos) from T)+1 from T
Best regards
Michael
"Matt" <mdframe@.sorvive-DO-NOT-SEND-SPAM.com> wrote in message
news:%23OrE24kRHHA.5064@.TK2MSFTNGP02.phx.gbl...
> Michael,
> Is there anyway to always get the result to start with a sequence of 1?
> Thanks,
> Matt
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:%23yApssPRHHA.1200@.TK2MSFTNGP04.phx.gbl...
>|||Michael,
I have been unable to get your solution to work. Can you use my previous
sample and see if you can get this working?
Thanks,
Matt
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:uoGym4lRHHA.2172@.TK2MSFTNGP04.phx.gbl...
> There are two ways, one if you know the name you are selecting (note the c
> instead of *):
> select n.value('.' , 'int') as "c"
> , n.value('count(for $a in . return $a/../c[.<<$a])+1', 'int') as
> "pos"
> from @.x.nodes('/a/c') N(n)
>
> Or if you don't know the name apriori, you can normalize:
> with T as
> (select n.value('.' , 'int') as "c"
> , n.value('count(for $a in . return $a/../*[.<<$a])+1', 'int') as
> "pos"
> from @.x.nodes('/a/c') N(n))
> select c, pos - (select min(pos) from T)+1 from T
> Best regards
> Michael
> "Matt" <mdframe@.sorvive-DO-NOT-SEND-SPAM.com> wrote in message
> news:%23OrE24kRHHA.5064@.TK2MSFTNGP02.phx.gbl...
>|||Can you post the data that starts at the wrong number please?
Thanks
Michael
"Matt" <mdframe@.sorvive-DO-NOT-SEND-SPAM.com> wrote in message
news:%23DY9sEUSHHA.2124@.TK2MSFTNGP06.phx.gbl...
> Michael,
> I have been unable to get your solution to work. Can you use my previous
> sample and see if you can get this working?
> Thanks,
> Matt
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:uoGym4lRHHA.2172@.TK2MSFTNGP04.phx.gbl...
>

Get Position() Value in XML.Query

Given the following syntax in SQL Server 2005:
DECLARE @.xmlvar xml
set @.xmlvar = '
<Data>
<Student id="s1">
<Name>Bugs Bunny</Name>
<Class>FFA</Class>
</Student>
<Student id="s2">
<Name>Road Runner</Name>
<Class>Sports</Class>
</Student>
<Student id="s3">
<Name>Wile E. Coyote</Name>
<Class>Science</Class>
</Student>
<Student id="s4">
<Name>Foghorn Leghorn</Name>
<Class>Self Defense</Class>
</Student>
</Data>'
select Data.Student.value('@.id', 'varchar(6)') as id,
Data.Student.value('(Name)[1]', 'varchar(30)') as [name],
Data.Student.value('(Class)[1]', 'varchar(50)') as class
--Data.Student.query('string(position())') as ndx
from @.xmlvar.nodes('/Data/Student') Data(Student)
Which results with:
id name class
s1 Bugs Bunny FFA
s2 Road Runner Sports
s3 Wile E. Coyote Science
s4 Foghorn Leghorn Self Defense
How would I then return another column that gives me the sequence in the
list, such as position() would do in XSL?
id name class ndx
s1 Bugs Bunny FFA 1
s2 Road Runner Sports 2
s3 Wile E. Coyote Science 3
s4 Foghorn Leghorn Self Defense 4
Thanks,
Matt
Maybe this?
select Data.Student.value('@.id', 'varchar(6)') as id,
Data.Student.value('(Name)[1]', 'varchar(30)') as [name],
Data.Student.value('(Class)[1]', 'varchar(50)') as class ,
Data.Student.value('for $a in . return count($a/../*[. << $a]) +
1','int') as ndx
from @.xmlvar.nodes('/Data/Student') Data(Student)
|||Mark,
Thanks that definitly gets me going in the right direction. Can you explain
the statement count($a/../*[. << $a]) , other than count() I get lost after
the "[".
Thanks,
Matt
<markc600@.hotmail.com> wrote in message
news:1169684064.504759.156860@.q2g2000cwa.googlegro ups.com...
> Maybe this?
>
> select Data.Student.value('@.id', 'varchar(6)') as id,
> Data.Student.value('(Name)[1]', 'varchar(30)') as [name],
> Data.Student.value('(Class)[1]', 'varchar(50)') as class ,
> Data.Student.value('for $a in . return count($a/../*[. << $a]) +
> 1','int') as ndx
> from @.xmlvar.nodes('/Data/Student') Data(Student)
>
|||Matt
This is the count of the preceding siblings for a given node (plus one)
using the << operator which returns true if the
left operand node precedes the right operand node in document order.
This query may make things clearer.
select Data.Student.query('.') as node,
Data.Student.query('for $a in . return $a/../*[. << $a]') as
allprevsiblings
from @.xmlvar.nodes('/Data/Student') Data(Student)
Regards
Mark
|||Mark,
Thanks for the explanation but I am confused by something I am getting with
one of my queries. Using your recommended method works great on one part of
the XML I am working with but in another area the number starts at 7. If I
run different things I get different values. Any ideas? I can't post the
actual XML I am working with but I can send it to you if you would like to
see my issue with my query.
Thanks,
Matt
<markc600@.hotmail.com> wrote in message
news:1169759470.715829.81540@.s48g2000cws.googlegro ups.com...
> Matt
> This is the count of the preceding siblings for a given node (plus one)
> using the << operator which returns true if the
> left operand node precedes the right operand node in document order.
> This query may make things clearer.
> select Data.Student.query('.') as node,
> Data.Student.query('for $a in . return $a/../*[. << $a]') as
> allprevsiblings
> from @.xmlvar.nodes('/Data/Student') Data(Student)
>
> Regards
> Mark
>
|||That's probably because the element that you select in the nodes() method is
not the first but the 7th child of the parent node.
E.g.
declare @.x xml;
set @.x = N'<a>
<b/>
<b/>
<b/>
<b/>
<b/>
<b/>
<c>1</c>
<c>2</c>
</a>';
select n.value('.' , 'int') as "c"
, n.value('count(for $a in . return $a/../*[.<<$a])+1', 'int') as "pos"
from @.x.nodes('/a/c') N(n)
Best regards
Michael
"Matt" <mdframe@.sorvive-DO-NOT-SEND-SPAM.com> wrote in message
news:O4TVO88QHHA.4844@.TK2MSFTNGP03.phx.gbl...
> Mark,
> Thanks for the explanation but I am confused by something I am getting
> with one of my queries. Using your recommended method works great on one
> part of the XML I am working with but in another area the number starts at
> 7. If I run different things I get different values. Any ideas? I can't
> post the actual XML I am working with but I can send it to you if you
> would like to see my issue with my query.
> Thanks,
> Matt
>
> <markc600@.hotmail.com> wrote in message
> news:1169759470.715829.81540@.s48g2000cws.googlegro ups.com...
>
|||Michael,
Is there anyway to always get the result to start with a sequence of 1?
Thanks,
Matt
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:%23yApssPRHHA.1200@.TK2MSFTNGP04.phx.gbl...
> That's probably because the element that you select in the nodes() method
> is not the first but the 7th child of the parent node.
> E.g.
> declare @.x xml;
> set @.x = N'<a>
> <b/>
> <b/>
> <b/>
> <b/>
> <b/>
> <b/>
> <c>1</c>
> <c>2</c>
> </a>';
> select n.value('.' , 'int') as "c"
> , n.value('count(for $a in . return $a/../*[.<<$a])+1', 'int') as "pos"
> from @.x.nodes('/a/c') N(n)
>
> Best regards
> Michael
>
> "Matt" <mdframe@.sorvive-DO-NOT-SEND-SPAM.com> wrote in message
> news:O4TVO88QHHA.4844@.TK2MSFTNGP03.phx.gbl...
>
|||There are two ways, one if you know the name you are selecting (note the c
instead of *):
select n.value('.' , 'int') as "c"
, n.value('count(for $a in . return $a/../c[.<<$a])+1', 'int') as
"pos"
from @.x.nodes('/a/c') N(n)
Or if you don't know the name apriori, you can normalize:
with T as
(select n.value('.' , 'int') as "c"
, n.value('count(for $a in . return $a/../*[.<<$a])+1', 'int') as
"pos"
from @.x.nodes('/a/c') N(n))
select c, pos - (select min(pos) from T)+1 from T
Best regards
Michael
"Matt" <mdframe@.sorvive-DO-NOT-SEND-SPAM.com> wrote in message
news:%23OrE24kRHHA.5064@.TK2MSFTNGP02.phx.gbl...
> Michael,
> Is there anyway to always get the result to start with a sequence of 1?
> Thanks,
> Matt
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:%23yApssPRHHA.1200@.TK2MSFTNGP04.phx.gbl...
>
|||Michael,
I have been unable to get your solution to work. Can you use my previous
sample and see if you can get this working?
Thanks,
Matt
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:uoGym4lRHHA.2172@.TK2MSFTNGP04.phx.gbl...
> There are two ways, one if you know the name you are selecting (note the c
> instead of *):
> select n.value('.' , 'int') as "c"
> , n.value('count(for $a in . return $a/../c[.<<$a])+1', 'int') as
> "pos"
> from @.x.nodes('/a/c') N(n)
>
> Or if you don't know the name apriori, you can normalize:
> with T as
> (select n.value('.' , 'int') as "c"
> , n.value('count(for $a in . return $a/../*[.<<$a])+1', 'int') as
> "pos"
> from @.x.nodes('/a/c') N(n))
> select c, pos - (select min(pos) from T)+1 from T
> Best regards
> Michael
> "Matt" <mdframe@.sorvive-DO-NOT-SEND-SPAM.com> wrote in message
> news:%23OrE24kRHHA.5064@.TK2MSFTNGP02.phx.gbl...
>
|||Can you post the data that starts at the wrong number please?
Thanks
Michael
"Matt" <mdframe@.sorvive-DO-NOT-SEND-SPAM.com> wrote in message
news:%23DY9sEUSHHA.2124@.TK2MSFTNGP06.phx.gbl...
> Michael,
> I have been unable to get your solution to work. Can you use my previous
> sample and see if you can get this working?
> Thanks,
> Matt
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:uoGym4lRHHA.2172@.TK2MSFTNGP04.phx.gbl...
>