Hey lads and lasses, I have been playing around with cursors and I just cant get my code to do what I tell it ;)
Below is the code that I have written so far, which is trying to do something like this -
1) Create table - tServiceCategory
2) Create table - tTemp
3) Populate tServiceCategory
4) Declare variables and cursor
5) Run SQL I want to cursor through
6) Open cursor
7) While @.@.FETCH_STATUS = 0 insert the values obtained by the cursor into tTemp
8) Close and deallocate cursor
9) Drop tables
CREATE TABLE tServiceCategory
(
Lower_Limit numeric,
Upper_Limit numeric,
Sort_Order numeric
)
CREATE TABLE tTemp
(
Total numeric,
Limit_Desc varchar(16)
)
INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (0, 6, 1)
INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (7, 12, 2)
INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (13, 24, 3)
INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (25, 60, 4)
INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (61, 120 , 5)
INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (121, 240, 6)
INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (241, 1200, 7)
DECLARE @.Total numeric
DECLARE @.Limit_Description varchar(16)
DECLARE MyCursor CURSOR FOR
SELECT Count(*) AS 'Total'
,CAST(Lower_Limit AS VARCHAR) +
(CASE WHEN Sort_Order = (SELECT Max(Sort_Order) FROM dbo.tServiceCategory)
THEN '+'
ELSE ( + ' to ' + CAST(Upper_Limit AS VARCHAR))
END) + ' months'
AS Limit_Description
FROM Employee e
INNER JOIN tServiceCategory s
ON DateDiff(mm, e.continuous_start_date, GetDate()) BETWEEN s.Lower_Limit AND s.Upper_Limit
GROUP
BY s.Sort_Order, s.Lower_Limit, s.Upper_Limit
ORDER
BY s.Sort_Order
OPEN MyCursor
-- FETCH NEXT FROM MyCursor
-- INTO @.Total, @.Limit_Description
WHILE @.@.FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM MyCursor
INTO @.Total, @.Limit_Description
INSERT INTO tTemp(Total) VALUES (@.Total)
END
SELECT * FROM tTemp
SELECT Count(*) AS 'Total'
,CAST(Lower_Limit AS VARCHAR) +
(CASE WHEN Sort_Order = (SELECT Max(Sort_Order) FROM dbo.tServiceCategory)
THEN '+'
ELSE ( + ' to ' + CAST(Upper_Limit AS VARCHAR))
END) + ' months'
AS Limit_Description
FROM Employee e
INNER JOIN tServiceCategory s
ON DateDiff(mm, e.continuous_start_date, GetDate()) BETWEEN s.Lower_Limit AND s.Upper_Limit
GROUP
BY s.Sort_Order, s.Lower_Limit, s.Upper_Limit
ORDER
BY s.Sort_Order
CLOSE MyCursor
DEALLOCATE MyCursor
DROP TABLE tTemp
DROP TABLE tServiceCategory
Results
Select * FROM Ttemp
Total | Limit_Desc
------
Total | Limit_Desc
------
227 0 to 6 months
448 7 to 12 months
573 13 to 24 months
910 25 to 60 months
911 61 to 120 months
614 121 to 240 months
250 241+ months
So it appears that tTemp is not getting populated... But I'm banging my head against the wall with these cursors, and I can't work out what I'm doing wrong :p
If I havn't provided enough information then let me know please :)
-GeorgeVAre you doing this for fun or because you think you need to? Coz a cursor does not look like the ideal mechanism here.|||Beause I've been asked to make this a query in FoxPro so that our users can run it... Except the system doesn't support the kind of join in a regular query, so I'm making it a process. I am trying to put the results of the SQL statement into a temporary table so I can report on it... Complicated to explain, but this was the solution I came up with!
If you can think of a better way then please feel free to pass opinion :)|||What is the value of @.@.FETCH_STATUS before the first fetch?
I usually fetch before checking that value, like so:
FETCH NEXT FROM MyCursor
INTO @.Total, @.Limit_Description
WHILE @.@.FETCH_STATUS = 0
BEGIN
INSERT INTO tTemp(Total) VALUES (@.Total)
FETCH NEXT FROM MyCursor
INTO @.Total, @.Limit_Description
END|||Are you able to create a view\ stored procedure in sql server and call that from fox pro?|||Also - your problem is the bit you commented out. Why you do that George? Why? :)|||Oops - you also need to jiggle a couple of lines:
CREATE TABLE tServiceCategory
(
Lower_Limit numeric,
Upper_Limit numeric,
Sort_Order numeric
)
CREATE TABLE tTemp
(
Total numeric,
Limit_Desc varchar(16)
)
CREATE TABLE Employee
(
continuous_start_date SMALLDATETIME
)
INSERT INTO employee
SELECT '20050101'
UNION ALL
SELECT '20060101'
UNION ALL
SELECT '20070101'
UNION ALL
SELECT '20070202'
INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (0, 6, 1)
INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (7, 12, 2)
INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (13, 24, 3)
INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (25, 60, 4)
INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (61, 120 , 5)
INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (121, 240, 6)
INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (241, 1200, 7)
DECLARE @.Total numeric
DECLARE @.Limit_Description varchar(16)
DECLARE MyCursor CURSOR FOR
SELECT Count(*) AS 'Total'
,CAST(Lower_Limit AS VARCHAR) +
(CASE WHEN Sort_Order = (SELECT Max(Sort_Order) FROM dbo.tServiceCategory)
THEN '+'
ELSE ( + ' to ' + CAST(Upper_Limit AS VARCHAR))
END) + ' months'
AS Limit_Description
FROM Employee e
INNER JOIN tServiceCategory s
ON DateDiff(mm, e.continuous_start_date, GetDate()) BETWEEN s.Lower_Limit AND s.Upper_Limit
GROUP
BY s.Sort_Order, s.Lower_Limit, s.Upper_Limit
ORDER
BY s.Sort_Order
OPEN MyCursor
FETCH NEXT FROM MyCursor
INTO @.Total, @.Limit_Description
WHILE @.@.FETCH_STATUS = 0
BEGIN
INSERT INTO tTemp(Total) VALUES (@.Total)
FETCH NEXT FROM MyCursor
INTO @.Total, @.Limit_Description
END
SELECT * FROM tTemp
SELECT Count(*) AS 'Total'
,CAST(Lower_Limit AS VARCHAR) +
(CASE WHEN Sort_Order = (SELECT Max(Sort_Order) FROM dbo.tServiceCategory)
THEN '+'
ELSE ( + ' to ' + CAST(Upper_Limit AS VARCHAR))
END) + ' months'
AS Limit_Description
FROM Employee e
INNER JOIN tServiceCategory s
ON DateDiff(mm, e.continuous_start_date, GetDate()) BETWEEN s.Lower_Limit AND s.Upper_Limit
GROUP
BY s.Sort_Order, s.Lower_Limit, s.Upper_Limit
ORDER
BY s.Sort_Order
CLOSE MyCursor
DEALLOCATE MyCursor
DROP TABLE tTemp
DROP TABLE tServiceCategory
DROP TABLE Employee|||Original
OPEN MyCursor
-- FETCH NEXT FROM MyCursor
-- INTO @.Total, @.Limit_Description
WHILE @.@.FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM MyCursor
INTO @.Total, @.Limit_Description
INSERT INTO tTemp(Total) VALUES (@.Total)
END
With changes (they seem so obvious now!)
OPEN MyCursor
FETCH NEXT FROM MyCursor
INTO @.Total, @.Limit_Description
WHILE @.@.FETCH_STATUS = 0
BEGIN
INSERT INTO tTemp(Total) VALUES (@.Total)
FETCH NEXT FROM MyCursor
INTO @.Total, @.Limit_Description
END
Thank you for your help guys, much appreciated. My first attempt at a cursor wasn't so bad ;)
*gives Poots and ivon gold stars* :)|||My first attempt at a cursor wasn't so bad ;)
Apart from the fact that a cursor appears totally inappropriate for what you are doing ;) have you not had the "OMG - don't write cursors unless you are certain there is no other way to do it" speil yet?|||OOh, nobody has given me that talk so far!
The resident SQL expert uses cursors a fair amount, so he's not likely to give me "the talk" :p
So what would be my other options? I'm more than willing to re-evaluate my approach... But coding in our system sucks, I don't need a cursor or anything if I didn't have to make it available to users... I hate users! :D|||If you can think of a better way then please feel free to pass opinion :)Two cursor-less alternatives:
CREATE TABLE tServiceCategory
(
Lower_Limit numeric,
Upper_Limit numeric,
Sort_Order numeric
)
CREATE TABLE tTemp
(
Total numeric,
Limit_Desc varchar(16)
)
CREATE TABLE Employee
(
continuous_start_date SMALLDATETIME
)
INSERT INTO employee
SELECT '20050101'
UNION ALL
SELECT '20060101'
UNION ALL
SELECT '20070101'
UNION ALL
SELECT '20070202'
INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (0, 6, 1)
INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (7, 12, 2)
INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (13, 24, 3)
INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (25, 60, 4)
INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (61, 120 , 5)
INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (121, 240, 6)
INSERT INTO tServiceCategory(Lower_Limit, Upper_Limit, Sort_Order) VALUES (241, 1200, 7)
GO
--Alternative #1 - use a view
CREATE VIEW myview
AS
SELECT Count(*) AS 'Total'
,CAST(Lower_Limit AS VARCHAR) +
(CASE WHEN Sort_Order = (SELECT Max(Sort_Order) FROM dbo.tServiceCategory)
THEN '+'
ELSE ( + ' to ' + CAST(Upper_Limit AS VARCHAR))
END) + ' months'
AS Limit_Description
FROM Employee e
INNER JOIN tServiceCategory s
ON DateDiff(mm, e.continuous_start_date, GetDate()) BETWEEN s.Lower_Limit AND s.Upper_Limit
GROUP BY s.Sort_Order, s.Lower_Limit, s.Upper_Limit
GO
SELECT *
FROM dbo.myview
--Alternative #2 - if you simply MUST populate a temp table use SET BASED logic
INSERT INTO tTemp (total, limit_desc)
SELECT Count(*) AS 'Total'
,CAST(Lower_Limit AS VARCHAR) +
(CASE WHEN Sort_Order = (SELECT Max(Sort_Order) FROM dbo.tServiceCategory)
THEN '+'
ELSE ( + ' to ' + CAST(Upper_Limit AS VARCHAR))
END) + ' months'
AS Limit_Description
FROM Employee e
INNER JOIN tServiceCategory s
ON DateDiff(mm, e.continuous_start_date, GetDate()) BETWEEN s.Lower_Limit AND s.Upper_Limit
GROUP BY s.Sort_Order, s.Lower_Limit, s.Upper_Limit
SELECT *
FROM dbo.ttemp
DROP TABLE tTemp
DROP TABLE tServiceCategory
DROP TABLE Employee
DROP VIEW myview|||The resident SQL expert uses cursors a fair amount, so he's not likely to give me "the talk" :p
Either he is doing a lot of specialised struff or he is not a SQL expert.
SQL Server is rubbish at iterative stuff - SQL is a declarative, set based language. ALWAYS try to do everything set based where possible and only consider curosrs as a last resort. This is an area where a lot of procedural coders get tripped up when programming in SQL.
Cursors are useful for:
Admin DDL (building dynamic strings by looping through "collections" (for example looping through a table with database names and performing the same SQL action on each database)).
Certain DML statements where "previous"\ "later" rows (logically speaking of course ;)) need to be prepared to the "current" row (e.g. culminative row counts). Even here though you are best off testing. I have some links to articles if you would like.
I can't, off the top of my head, think of another time you would want to use a cursor.
HTH|||INSERT INTO! D'oh!
Well, it was nice learning cursors a wee bit anyway ;)
Why didn't I think of that?
Well, I guess it's high time to port this into FoxPro so I can play with it and see what damage I can cause :D
The table isn't neccessarily temporary, as in, it might not be dropped depending on how I can get FoxPro to interact with temporary tables... We'll see I suppose!
I'll definately come back with an answer on how I solved the overall problem :D|||... and finally. Do you remember me saying I am hopeless with cursor syntax? The reason is the above - I barely use them. In fact, I code up loops anyway so I never use them but the point is I do very little iterative stuff in SQL.
:)|||I'll definately come back with an answer on how I solved the overall problem :DHeh heh - if you want one last bit of advice - dump the temp table thing too and just use the view. My last tuppeneth I promise ;)|||Always open to advice :)
I don't think it's going to be a temp table or a view - I think it's simply going to be a table, which will be populated once and left to rot. I just needed a way of putting the results of my SQL statement into a table so I could display them for the user.
Thanks for everything Poots :beer:|||I don't think it's going to be a temp table or a view - I think it's simply going to be a table, which will be populated once and left to rot. I just needed a way of putting the results of my SQL statement into a table so I could display them for the user.
My last tuppeneth I promise ;)must... try ... to ...resist...
it's no good - what happens next month when all your counts are out?|||Ah sorry, meant to clarify that.
This little fella will run before you run the INSERT INTO
DELETE FROM <mytable>|||Hmm, so my insert into wasn't working, so I tried views.
...Which works fantastically well using QA...
But the view is not picked up by the system and cannot be created the same by the system either...
Looks like the temp table + cursor might be the way to go (*sigh*)
I hate FoxPro / our HR system.
I'll keep you posted.|||Hmm, so my insert into wasn't working, so I tried views.
...Which works fantastically well using QA...
But the view is not picked up by the system and cannot be created the same by the system either...
Looks like the temp table + cursor might be the way to go (*sigh*)
I hate FoxPro / our HR system.
I'll keep you posted.No - trouble shoot the view. The cursor is terrible. The delete\ insert is not much cop either.
Volatile data like this should always be derived at run time rather than stored if at all possible.
What do you mean by " the view is not picked up by the system and cannot be created the same by the system either..."?|||the system can only see views that it creates using it's in built data dictionary. Anything I produce in QA or EM simply canot be seen by the system - you cannot query it or even look at it.
The view cannot be built in the system because it exceeds the maximum characters (no matter how much I trim it down)... WHY IS THERE A LIMIT?!?
I'm afraid to say that I'm fed up of this problem today, so I'm going to get on with something else. The problem was solved hours and hours ago in QA, but porting this into our system is an absolute bitch.|||Ah - I think I see.
A third party system?
What is the char limit? Is it a limit on the SQL within the view? Can you not just insert info into one of application tables (*note - please don't try this - just a question)?|||3rd Party... Limit on the SQL... Don't understand :p|||I've effing done it!
Created a view using the software and then altered the view in QA.
Basically tricking it into doing what I wanted it to do :)
Damn that feels good.
(EDIT: want to know the best part? I didn't have to use any FoxPro! :D:D:D:D:D)
Now... to get exclusive access so I can put this live...
Thanks to everybody's 2 cents (and Poots's $2)!
I've learned a lot and finally cracked it (technically, I guess by "hacking" the program, but still!)
Showing posts with label syntax. Show all posts
Showing posts with label syntax. Show all posts
Thursday, March 29, 2012
Sunday, March 25, 2012
cursor How to >> use @dbname
The following query ERRORs out with this >>
Server: Msg 170, Level 15, State 1, Line 14
Line 14: Incorrect syntax near '@.DBname'.
What can I do to get it to accept the cursor name to change DBs
SET NOCOUNT ON
DECLARE @.DBname varchar(11)
declare db_cursor cursor for
select master.dbo.sysdatabases.name from master.dbo.sysdatabases
order by name
open DB_Cursor
fetch next from DB_Cursor
into @.DBname
WHILE @.@.FETCH_STATUS = 0
BEGIN
use @.DBname
select 'Owner' = user_name(uid), 'Table' = name, 'Date' = crdate
from sysobjects
where user_name(uid) !='DBO'
order by uid, name
fetch next from DB_Cursor
into @.DBname
END
CLOSE db_cursor
DEALLOCATE db_cursor
Thanks.Try this...
SET NOCOUNT ON
DECLARE @.DBname varchar(11)
declare db_cursor cursor for
select master.dbo.sysdatabases.name from master.dbo.sysdatabases
order by name
open DB_Cursor
fetch next from DB_Cursor
into @.DBname
WHILE @.@.FETCH_STATUS = 0
BEGIN
declare @.sql varchar(500)
Set @.sql = 'use ' + @.DBname +'
select ''Owner'' = user_name(uid), ''Table'' = name, ''Date'' = crdate
from sysobjects
where user_name(uid) !=''DBO''
order by uid, name'
Exec (@.sql)
fetch next from DB_Cursor
into @.DBname
END
CLOSE db_cursor
DEALLOCATE db_cursor|||You can't say USE @.dbName because USE can't take a variable.
The following is undocumented and unsupported, so use at your own risk and
not in production code, but should work fine for occasional ad hoc usage:
EXEC sp_msForEachDB 'SELECT [Owner] = user_name(uid), [Table] = name
, [Date]
= crdate
FROM ?.dbo.sysobjects
WHERE user_name(uid) != ''dbo''
ORDER BY uid, name'
You can do it in a more structured way using your own cursor instead of the
one in sp_msForEachDB, if this is a requirement, please post back.
"WANNABE" <breichenbach AT istate DOT com> wrote in message
news:eOL7zkF$GHA.4316@.TK2MSFTNGP03.phx.gbl...
> The following query ERRORs out with this >>
> Server: Msg 170, Level 15, State 1, Line 14
> Line 14: Incorrect syntax near '@.DBname'.
> What can I do to get it to accept the cursor name to change DBs
> SET NOCOUNT ON
> DECLARE @.DBname varchar(11)
> declare db_cursor cursor for
> select master.dbo.sysdatabases.name from master.dbo.sysdatabases
> order by name
> open DB_Cursor
> fetch next from DB_Cursor
> into @.DBname
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> use @.DBname
> select 'Owner' = user_name(uid), 'Table' = name, 'Date' = crdate
> from sysobjects
> where user_name(uid) !='DBO'
> order by uid, name
> fetch next from DB_Cursor
> into @.DBname
> END
> CLOSE db_cursor
> DEALLOCATE db_cursor
> Thanks.
>|||Thanks Barry that looks great, but when I apply your changes I get this
error >>
Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'DBO'.
and the error is displayed once for every DB on that server.
===================================
"Barry" <barry.oconnor@.manx.net> wrote in message
news:1162237261.528489.238930@.b28g2000cwb.googlegroups.com...
> Try this...
>
> SET NOCOUNT ON
> DECLARE @.DBname varchar(11)
> declare db_cursor cursor for
> select master.dbo.sysdatabases.name from master.dbo.sysdatabases
> order by name
> open DB_Cursor
> fetch next from DB_Cursor
> into @.DBname
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> declare @.sql varchar(500)
> Set @.sql = 'use ' + @.DBname +'
> select ''Owner'' = user_name(uid), ''Table'' = name, ''Date'' = crdate
> from sysobjects
> where user_name(uid) !=''DBO''
> order by uid, name'
> Exec (@.sql)
> fetch next from DB_Cursor
> into @.DBname
> END
> CLOSE db_cursor
> DEALLOCATE db_cursor
>|||I just figured out tit to be a double quote issue, and I think I can find a
fix... Thanks
===================================
"WANNABE" <breichenbach AT istate DOT com> wrote in message
news:%23UFjjAG$GHA.3860@.TK2MSFTNGP02.phx.gbl...
> Thanks Barry that looks great, but when I apply your changes I get this
> error >>
> Server: Msg 207, Level 16, State 3, Line 2
> Invalid column name 'DBO'.
> and the error is displayed once for every DB on that server.
> ===================================
> "Barry" <barry.oconnor@.manx.net> wrote in message
> news:1162237261.528489.238930@.b28g2000cwb.googlegroups.com...
>
Server: Msg 170, Level 15, State 1, Line 14
Line 14: Incorrect syntax near '@.DBname'.
What can I do to get it to accept the cursor name to change DBs
SET NOCOUNT ON
DECLARE @.DBname varchar(11)
declare db_cursor cursor for
select master.dbo.sysdatabases.name from master.dbo.sysdatabases
order by name
open DB_Cursor
fetch next from DB_Cursor
into @.DBname
WHILE @.@.FETCH_STATUS = 0
BEGIN
use @.DBname
select 'Owner' = user_name(uid), 'Table' = name, 'Date' = crdate
from sysobjects
where user_name(uid) !='DBO'
order by uid, name
fetch next from DB_Cursor
into @.DBname
END
CLOSE db_cursor
DEALLOCATE db_cursor
Thanks.Try this...
SET NOCOUNT ON
DECLARE @.DBname varchar(11)
declare db_cursor cursor for
select master.dbo.sysdatabases.name from master.dbo.sysdatabases
order by name
open DB_Cursor
fetch next from DB_Cursor
into @.DBname
WHILE @.@.FETCH_STATUS = 0
BEGIN
declare @.sql varchar(500)
Set @.sql = 'use ' + @.DBname +'
select ''Owner'' = user_name(uid), ''Table'' = name, ''Date'' = crdate
from sysobjects
where user_name(uid) !=''DBO''
order by uid, name'
Exec (@.sql)
fetch next from DB_Cursor
into @.DBname
END
CLOSE db_cursor
DEALLOCATE db_cursor|||You can't say USE @.dbName because USE can't take a variable.
The following is undocumented and unsupported, so use at your own risk and
not in production code, but should work fine for occasional ad hoc usage:
EXEC sp_msForEachDB 'SELECT [Owner] = user_name(uid), [Table] = name
, [Date]
= crdate
FROM ?.dbo.sysobjects
WHERE user_name(uid) != ''dbo''
ORDER BY uid, name'
You can do it in a more structured way using your own cursor instead of the
one in sp_msForEachDB, if this is a requirement, please post back.
"WANNABE" <breichenbach AT istate DOT com> wrote in message
news:eOL7zkF$GHA.4316@.TK2MSFTNGP03.phx.gbl...
> The following query ERRORs out with this >>
> Server: Msg 170, Level 15, State 1, Line 14
> Line 14: Incorrect syntax near '@.DBname'.
> What can I do to get it to accept the cursor name to change DBs
> SET NOCOUNT ON
> DECLARE @.DBname varchar(11)
> declare db_cursor cursor for
> select master.dbo.sysdatabases.name from master.dbo.sysdatabases
> order by name
> open DB_Cursor
> fetch next from DB_Cursor
> into @.DBname
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> use @.DBname
> select 'Owner' = user_name(uid), 'Table' = name, 'Date' = crdate
> from sysobjects
> where user_name(uid) !='DBO'
> order by uid, name
> fetch next from DB_Cursor
> into @.DBname
> END
> CLOSE db_cursor
> DEALLOCATE db_cursor
> Thanks.
>|||Thanks Barry that looks great, but when I apply your changes I get this
error >>
Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'DBO'.
and the error is displayed once for every DB on that server.
===================================
"Barry" <barry.oconnor@.manx.net> wrote in message
news:1162237261.528489.238930@.b28g2000cwb.googlegroups.com...
> Try this...
>
> SET NOCOUNT ON
> DECLARE @.DBname varchar(11)
> declare db_cursor cursor for
> select master.dbo.sysdatabases.name from master.dbo.sysdatabases
> order by name
> open DB_Cursor
> fetch next from DB_Cursor
> into @.DBname
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> declare @.sql varchar(500)
> Set @.sql = 'use ' + @.DBname +'
> select ''Owner'' = user_name(uid), ''Table'' = name, ''Date'' = crdate
> from sysobjects
> where user_name(uid) !=''DBO''
> order by uid, name'
> Exec (@.sql)
> fetch next from DB_Cursor
> into @.DBname
> END
> CLOSE db_cursor
> DEALLOCATE db_cursor
>|||I just figured out tit to be a double quote issue, and I think I can find a
fix... Thanks
===================================
"WANNABE" <breichenbach AT istate DOT com> wrote in message
news:%23UFjjAG$GHA.3860@.TK2MSFTNGP02.phx.gbl...
> Thanks Barry that looks great, but when I apply your changes I get this
> error >>
> Server: Msg 207, Level 16, State 3, Line 2
> Invalid column name 'DBO'.
> and the error is displayed once for every DB on that server.
> ===================================
> "Barry" <barry.oconnor@.manx.net> wrote in message
> news:1162237261.528489.238930@.b28g2000cwb.googlegroups.com...
>
cursor How to >> use @dbname
The following query ERRORs out with this >>
Server: Msg 170, Level 15, State 1, Line 14
Line 14: Incorrect syntax near '@.DBname'.
What can I do to get it to accept the cursor name to change DBs
SET NOCOUNT ON
DECLARE @.DBname varchar(11)
declare db_cursor cursor for
select master.dbo.sysdatabases.name from master.dbo.sysdatabases
order by name
open DB_Cursor
fetch next from DB_Cursor
into @.DBname
WHILE @.@.FETCH_STATUS = 0
BEGIN
use @.DBname
select 'Owner' = user_name(uid), 'Table' = name, 'Date' = crdate
from sysobjects
where user_name(uid) !='DBO'
order by uid, name
fetch next from DB_Cursor
into @.DBname
END
CLOSE db_cursor
DEALLOCATE db_cursor
Thanks.Try this...
SET NOCOUNT ON
DECLARE @.DBname varchar(11)
declare db_cursor cursor for
select master.dbo.sysdatabases.name from master.dbo.sysdatabases
order by name
open DB_Cursor
fetch next from DB_Cursor
into @.DBname
WHILE @.@.FETCH_STATUS = 0
BEGIN
declare @.sql varchar(500)
Set @.sql = 'use ' + @.DBname +'
select ''Owner'' = user_name(uid), ''Table'' = name, ''Date'' = crdate
from sysobjects
where user_name(uid) !=''DBO''
order by uid, name'
Exec (@.sql)
fetch next from DB_Cursor
into @.DBname
END
CLOSE db_cursor
DEALLOCATE db_cursor|||You can't say USE @.dbName because USE can't take a variable.
The following is undocumented and unsupported, so use at your own risk and
not in production code, but should work fine for occasional ad hoc usage:
EXEC sp_msForEachDB 'SELECT [Owner] = user_name(uid), [Table] = name, [Date]
= crdate
FROM ?.dbo.sysobjects
WHERE user_name(uid) != ''dbo''
ORDER BY uid, name'
You can do it in a more structured way using your own cursor instead of the
one in sp_msForEachDB, if this is a requirement, please post back.
"WANNABE" <breichenbach AT istate DOT com> wrote in message
news:eOL7zkF$GHA.4316@.TK2MSFTNGP03.phx.gbl...
> The following query ERRORs out with this >>
> Server: Msg 170, Level 15, State 1, Line 14
> Line 14: Incorrect syntax near '@.DBname'.
> What can I do to get it to accept the cursor name to change DBs
> SET NOCOUNT ON
> DECLARE @.DBname varchar(11)
> declare db_cursor cursor for
> select master.dbo.sysdatabases.name from master.dbo.sysdatabases
> order by name
> open DB_Cursor
> fetch next from DB_Cursor
> into @.DBname
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> use @.DBname
> select 'Owner' = user_name(uid), 'Table' = name, 'Date' = crdate
> from sysobjects
> where user_name(uid) !='DBO'
> order by uid, name
> fetch next from DB_Cursor
> into @.DBname
> END
> CLOSE db_cursor
> DEALLOCATE db_cursor
> Thanks.
>|||Thanks Barry that looks great, but when I apply your changes I get this
error >>
Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'DBO'.
and the error is displayed once for every DB on that server.
==================================="Barry" <barry.oconnor@.manx.net> wrote in message
news:1162237261.528489.238930@.b28g2000cwb.googlegroups.com...
> Try this...
>
> SET NOCOUNT ON
> DECLARE @.DBname varchar(11)
> declare db_cursor cursor for
> select master.dbo.sysdatabases.name from master.dbo.sysdatabases
> order by name
> open DB_Cursor
> fetch next from DB_Cursor
> into @.DBname
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> declare @.sql varchar(500)
> Set @.sql = 'use ' + @.DBname +'
> select ''Owner'' = user_name(uid), ''Table'' = name, ''Date'' = crdate
> from sysobjects
> where user_name(uid) !=''DBO''
> order by uid, name'
> Exec (@.sql)
> fetch next from DB_Cursor
> into @.DBname
> END
> CLOSE db_cursor
> DEALLOCATE db_cursor
>|||I just figured out tit to be a double quote issue, and I think I can find a
fix... Thanks
==================================="WANNABE" <breichenbach AT istate DOT com> wrote in message
news:%23UFjjAG$GHA.3860@.TK2MSFTNGP02.phx.gbl...
> Thanks Barry that looks great, but when I apply your changes I get this
> error >>
> Server: Msg 207, Level 16, State 3, Line 2
> Invalid column name 'DBO'.
> and the error is displayed once for every DB on that server.
> ===================================> "Barry" <barry.oconnor@.manx.net> wrote in message
> news:1162237261.528489.238930@.b28g2000cwb.googlegroups.com...
>> Try this...
>>
>> SET NOCOUNT ON
>> DECLARE @.DBname varchar(11)
>> declare db_cursor cursor for
>> select master.dbo.sysdatabases.name from master.dbo.sysdatabases
>> order by name
>> open DB_Cursor
>> fetch next from DB_Cursor
>> into @.DBname
>> WHILE @.@.FETCH_STATUS = 0
>> BEGIN
>> declare @.sql varchar(500)
>> Set @.sql = 'use ' + @.DBname +'
>> select ''Owner'' = user_name(uid), ''Table'' = name, ''Date'' = crdate
>> from sysobjects
>> where user_name(uid) !=''DBO''
>> order by uid, name'
>> Exec (@.sql)
>> fetch next from DB_Cursor
>> into @.DBname
>> END
>> CLOSE db_cursor
>> DEALLOCATE db_cursor
>
Server: Msg 170, Level 15, State 1, Line 14
Line 14: Incorrect syntax near '@.DBname'.
What can I do to get it to accept the cursor name to change DBs
SET NOCOUNT ON
DECLARE @.DBname varchar(11)
declare db_cursor cursor for
select master.dbo.sysdatabases.name from master.dbo.sysdatabases
order by name
open DB_Cursor
fetch next from DB_Cursor
into @.DBname
WHILE @.@.FETCH_STATUS = 0
BEGIN
use @.DBname
select 'Owner' = user_name(uid), 'Table' = name, 'Date' = crdate
from sysobjects
where user_name(uid) !='DBO'
order by uid, name
fetch next from DB_Cursor
into @.DBname
END
CLOSE db_cursor
DEALLOCATE db_cursor
Thanks.Try this...
SET NOCOUNT ON
DECLARE @.DBname varchar(11)
declare db_cursor cursor for
select master.dbo.sysdatabases.name from master.dbo.sysdatabases
order by name
open DB_Cursor
fetch next from DB_Cursor
into @.DBname
WHILE @.@.FETCH_STATUS = 0
BEGIN
declare @.sql varchar(500)
Set @.sql = 'use ' + @.DBname +'
select ''Owner'' = user_name(uid), ''Table'' = name, ''Date'' = crdate
from sysobjects
where user_name(uid) !=''DBO''
order by uid, name'
Exec (@.sql)
fetch next from DB_Cursor
into @.DBname
END
CLOSE db_cursor
DEALLOCATE db_cursor|||You can't say USE @.dbName because USE can't take a variable.
The following is undocumented and unsupported, so use at your own risk and
not in production code, but should work fine for occasional ad hoc usage:
EXEC sp_msForEachDB 'SELECT [Owner] = user_name(uid), [Table] = name, [Date]
= crdate
FROM ?.dbo.sysobjects
WHERE user_name(uid) != ''dbo''
ORDER BY uid, name'
You can do it in a more structured way using your own cursor instead of the
one in sp_msForEachDB, if this is a requirement, please post back.
"WANNABE" <breichenbach AT istate DOT com> wrote in message
news:eOL7zkF$GHA.4316@.TK2MSFTNGP03.phx.gbl...
> The following query ERRORs out with this >>
> Server: Msg 170, Level 15, State 1, Line 14
> Line 14: Incorrect syntax near '@.DBname'.
> What can I do to get it to accept the cursor name to change DBs
> SET NOCOUNT ON
> DECLARE @.DBname varchar(11)
> declare db_cursor cursor for
> select master.dbo.sysdatabases.name from master.dbo.sysdatabases
> order by name
> open DB_Cursor
> fetch next from DB_Cursor
> into @.DBname
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> use @.DBname
> select 'Owner' = user_name(uid), 'Table' = name, 'Date' = crdate
> from sysobjects
> where user_name(uid) !='DBO'
> order by uid, name
> fetch next from DB_Cursor
> into @.DBname
> END
> CLOSE db_cursor
> DEALLOCATE db_cursor
> Thanks.
>|||Thanks Barry that looks great, but when I apply your changes I get this
error >>
Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'DBO'.
and the error is displayed once for every DB on that server.
==================================="Barry" <barry.oconnor@.manx.net> wrote in message
news:1162237261.528489.238930@.b28g2000cwb.googlegroups.com...
> Try this...
>
> SET NOCOUNT ON
> DECLARE @.DBname varchar(11)
> declare db_cursor cursor for
> select master.dbo.sysdatabases.name from master.dbo.sysdatabases
> order by name
> open DB_Cursor
> fetch next from DB_Cursor
> into @.DBname
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> declare @.sql varchar(500)
> Set @.sql = 'use ' + @.DBname +'
> select ''Owner'' = user_name(uid), ''Table'' = name, ''Date'' = crdate
> from sysobjects
> where user_name(uid) !=''DBO''
> order by uid, name'
> Exec (@.sql)
> fetch next from DB_Cursor
> into @.DBname
> END
> CLOSE db_cursor
> DEALLOCATE db_cursor
>|||I just figured out tit to be a double quote issue, and I think I can find a
fix... Thanks
==================================="WANNABE" <breichenbach AT istate DOT com> wrote in message
news:%23UFjjAG$GHA.3860@.TK2MSFTNGP02.phx.gbl...
> Thanks Barry that looks great, but when I apply your changes I get this
> error >>
> Server: Msg 207, Level 16, State 3, Line 2
> Invalid column name 'DBO'.
> and the error is displayed once for every DB on that server.
> ===================================> "Barry" <barry.oconnor@.manx.net> wrote in message
> news:1162237261.528489.238930@.b28g2000cwb.googlegroups.com...
>> Try this...
>>
>> SET NOCOUNT ON
>> DECLARE @.DBname varchar(11)
>> declare db_cursor cursor for
>> select master.dbo.sysdatabases.name from master.dbo.sysdatabases
>> order by name
>> open DB_Cursor
>> fetch next from DB_Cursor
>> into @.DBname
>> WHILE @.@.FETCH_STATUS = 0
>> BEGIN
>> declare @.sql varchar(500)
>> Set @.sql = 'use ' + @.DBname +'
>> select ''Owner'' = user_name(uid), ''Table'' = name, ''Date'' = crdate
>> from sysobjects
>> where user_name(uid) !=''DBO''
>> order by uid, name'
>> Exec (@.sql)
>> fetch next from DB_Cursor
>> into @.DBname
>> END
>> CLOSE db_cursor
>> DEALLOCATE db_cursor
>
Cursor Declaration
Hello All!
I am trying to declare a cursor and I keep on gettin the following error
when I try and debug: Incorrect syntax near the keyword 'declare'. I am
stumped and
becuase I have even tried using the numerous exmaples
listed on the internet and modified the select clause.
PLEASE HELP!
Here is my stored PRoc code:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE FXSecondPart
AS
BEGIN
--SET NOCOUNT ON;
declare @.1m as float
declare @.2m as float
declare @.spot as float
declare @.@.ValDt as datetime
declare @.@.PayAmt as money
declare @.@.RcvAmt as money
declare @.BS as char(1)
declare @.CCY as char(3)
declare @.Sell
declare pubcrsr cursor
for select ISIN
from AllTrades
FOR READ ONLY
if i parse this I get a error!READ_ONLY not READ ONLY
declare pubcrsr cursor READ_ONLY
for select ISIN
from AllTrades
and also
declare @.Sell needs a type maybe int?
declare @.Sell int
http://sqlservercode.blogspot.com/|||umm.. what type exactly is the @.Sell variable?
:)
Peter
> declare @.CCY as char(3)
> declare @.Sell
> declare pubcrsr cursor
> for select ISIN
> from AllTrades
> FOR READ ONLY
>
> if i parse this I get a error!
>|||based on what you've posted
1)there should be a END associated with the BEGIN
2) add a datatype to declare @.Sell
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Chris Allison" <ChrisAllison@.discussions.microsoft.com> wrote in message
news:63674EDA-96BF-41B0-A30C-19F6D254A5C1@.microsoft.com...
> Hello All!
> I am trying to declare a cursor and I keep on gettin the following error
> when I try and debug: Incorrect syntax near the keyword 'declare'. I am
> stumped and becuase I have even tried using the numerous exmaples
> listed on the internet and modified the select clause.
> PLEASE HELP!
> Here is my stored PRoc code:
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> CREATE PROCEDURE FXSecondPart
> AS
> BEGIN
> --SET NOCOUNT ON;
> declare @.1m as float
> declare @.2m as float
> declare @.spot as float
> declare @.@.ValDt as datetime
> declare @.@.PayAmt as money
> declare @.@.RcvAmt as money
> declare @.BS as char(1)
> declare @.CCY as char(3)
> declare @.Sell
> declare pubcrsr cursor
> for select ISIN
> from AllTrades
> FOR READ ONLY
>
> if i parse this I get a error!
>|||Hi SQL
That doesnt work! If I parse this I get a incorrect syntax near declare, the
same error!!
It cant be a permissions problem as I have SA privilidges. coulld it be
another system setting? or perhaps im declaring it in the wrong 'section' of
my stored procedure. Im using SQL Express 2005
declare pubcrsr cursor READ_ONLY
for select ISIN
from AllTrades
OPEN pubcrsr
WHILE @.@.Fetch_Status <>-1
Begin
FETCH NEXT from pubcrsr
INTO @.@.ValDt, @.@.PayAmt, @.@.RcvAmt
--SELECT @.spot = (SELECT LastTrade FROM HistoricalFX WHERE (CCY = @.CCY) AND
--(Date = @.p1))
--SELECT @.1m = (SELECT LastTrade FROM HistoricalFXFwrd WHERE (CCY = @.CCY)
AND
--(Period = '1M') AND (Date = @.p1) )
--SELECT @.2m = (SELECT LastTrade FROM HistoricalFXFwrd WHERE (CCY = @.CCY)
AND
--(Period = '2M') AND (Date = @.p1) )
-- pass @.spot, @.1m, @.2m, @.valdt, to calculating procedure
-- with the returned value calculate the PnL and put this in a 'counter'
FETCH NEXT from pubcrsr
End
Close pubcrsr
Deallocate pubcrsr
"SQL" wrote:
> READ_ONLY not READ ONLY
> declare pubcrsr cursor READ_ONLY
> for select ISIN
> from AllTrades
>
> and also
> declare @.Sell needs a type maybe int?
> declare @.Sell int
>
> http://sqlservercode.blogspot.com/
>|||THATS IT!!! I know it was a simple problem Thanks Guys it was the @.sell
variable which ive deleted!!
Great Stuff!
"SQL" wrote:
> READ_ONLY not READ ONLY
> declare pubcrsr cursor READ_ONLY
> for select ISIN
> from AllTrades
>
> and also
> declare @.Sell needs a type maybe int?
> declare @.Sell int
>
> http://sqlservercode.blogspot.com/
>
I am trying to declare a cursor and I keep on gettin the following error
when I try and debug: Incorrect syntax near the keyword 'declare'. I am
stumped and
becuase I have even tried using the numerous exmapleslisted on the internet and modified the select clause.
PLEASE HELP!
Here is my stored PRoc code:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE FXSecondPart
AS
BEGIN
--SET NOCOUNT ON;
declare @.1m as float
declare @.2m as float
declare @.spot as float
declare @.@.ValDt as datetime
declare @.@.PayAmt as money
declare @.@.RcvAmt as money
declare @.BS as char(1)
declare @.CCY as char(3)
declare @.Sell
declare pubcrsr cursor
for select ISIN
from AllTrades
FOR READ ONLY
if i parse this I get a error!READ_ONLY not READ ONLY
declare pubcrsr cursor READ_ONLY
for select ISIN
from AllTrades
and also
declare @.Sell needs a type maybe int?
declare @.Sell int
http://sqlservercode.blogspot.com/|||umm.. what type exactly is the @.Sell variable?
:)
Peter
> declare @.CCY as char(3)
> declare @.Sell
> declare pubcrsr cursor
> for select ISIN
> from AllTrades
> FOR READ ONLY
>
> if i parse this I get a error!
>|||based on what you've posted
1)there should be a END associated with the BEGIN
2) add a datatype to declare @.Sell
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Chris Allison" <ChrisAllison@.discussions.microsoft.com> wrote in message
news:63674EDA-96BF-41B0-A30C-19F6D254A5C1@.microsoft.com...
> Hello All!
> I am trying to declare a cursor and I keep on gettin the following error
> when I try and debug: Incorrect syntax near the keyword 'declare'. I am
> stumped and
becuase I have even tried using the numerous exmaples> listed on the internet and modified the select clause.
> PLEASE HELP!
> Here is my stored PRoc code:
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> CREATE PROCEDURE FXSecondPart
> AS
> BEGIN
> --SET NOCOUNT ON;
> declare @.1m as float
> declare @.2m as float
> declare @.spot as float
> declare @.@.ValDt as datetime
> declare @.@.PayAmt as money
> declare @.@.RcvAmt as money
> declare @.BS as char(1)
> declare @.CCY as char(3)
> declare @.Sell
> declare pubcrsr cursor
> for select ISIN
> from AllTrades
> FOR READ ONLY
>
> if i parse this I get a error!
>|||Hi SQL
That doesnt work! If I parse this I get a incorrect syntax near declare, the
same error!!
It cant be a permissions problem as I have SA privilidges. coulld it be
another system setting? or perhaps im declaring it in the wrong 'section' of
my stored procedure. Im using SQL Express 2005
declare pubcrsr cursor READ_ONLY
for select ISIN
from AllTrades
OPEN pubcrsr
WHILE @.@.Fetch_Status <>-1
Begin
FETCH NEXT from pubcrsr
INTO @.@.ValDt, @.@.PayAmt, @.@.RcvAmt
--SELECT @.spot = (SELECT LastTrade FROM HistoricalFX WHERE (CCY = @.CCY) AND
--(Date = @.p1))
--SELECT @.1m = (SELECT LastTrade FROM HistoricalFXFwrd WHERE (CCY = @.CCY)
AND
--(Period = '1M') AND (Date = @.p1) )
--SELECT @.2m = (SELECT LastTrade FROM HistoricalFXFwrd WHERE (CCY = @.CCY)
AND
--(Period = '2M') AND (Date = @.p1) )
-- pass @.spot, @.1m, @.2m, @.valdt, to calculating procedure
-- with the returned value calculate the PnL and put this in a 'counter'
FETCH NEXT from pubcrsr
End
Close pubcrsr
Deallocate pubcrsr
"SQL" wrote:
> READ_ONLY not READ ONLY
> declare pubcrsr cursor READ_ONLY
> for select ISIN
> from AllTrades
>
> and also
> declare @.Sell needs a type maybe int?
> declare @.Sell int
>
> http://sqlservercode.blogspot.com/
>|||THATS IT!!! I know it was a simple problem Thanks Guys it was the @.sell
variable which ive deleted!!
Great Stuff!
"SQL" wrote:
> READ_ONLY not READ ONLY
> declare pubcrsr cursor READ_ONLY
> for select ISIN
> from AllTrades
>
> and also
> declare @.Sell needs a type maybe int?
> declare @.Sell int
>
> http://sqlservercode.blogspot.com/
>
Tuesday, March 20, 2012
Current month syntax
A little help please...
I need to extract records in which the FileRecDate falls within the current
month/current year range. Also may need to filter by previous month/current
year; current year and prior year.
Any ideas?
Thanks,
Kenselect getdate()
Also:
select datepart(mm,getdate())
select datepart(dd,getdate())
select datepart(yy,getdate())
It is possible to get more fancy. You could declare a "start" and an "end"
variable and set the values according to your business logic. The variables
would then be used to filter your data within your query/stored procedure.
Keith
"Ken D." <KenD@.discussions.microsoft.com> wrote in message
news:49A7FD76-49BF-41D8-8437-9BD015FEF288@.microsoft.com...
>A little help please...
> I need to extract records in which the FileRecDate falls within the
> current
> month/current year range. Also may need to filter by previous
> month/current
> year; current year and prior year.
> Any ideas?
> Thanks,
> Ken|||Keith,
Actually, I tried this logic and it worked.
WHERE Month(FileRecDate) = Month(GetDate())AND
Year(FileRecDate)=Year(GetDate())
Thank you so much for your input...
Ken
"Keith Kratochvil" wrote:
> select getdate()
> Also:
> select datepart(mm,getdate())
> select datepart(dd,getdate())
> select datepart(yy,getdate())
> It is possible to get more fancy. You could declare a "start" and an "end
"
> variable and set the values according to your business logic. The variabl
es
> would then be used to filter your data within your query/stored procedure.
> --
> Keith
>
> "Ken D." <KenD@.discussions.microsoft.com> wrote in message
> news:49A7FD76-49BF-41D8-8437-9BD015FEF288@.microsoft.com...
>
>|||> WHERE Month(FileRecDate) = Month(GetDate())AND
> Year(FileRecDate)=Year(GetDate())
Ugh, how was performance? You might not notice while the table is small,
but wait until you have a reasnable amount of data. If you have an index on
FileRecDate (which you should), you might try something along these lines:
DECLARE @.sd SMALLDATETIME
SET @.sd = DATEDIFF(DAY,DAY(GETDATE())-1,GETDATE())
SELECT ...
WHERE FileRecDate >= @.sd
AND FileRecDate < DATEADD(MONTH, 1, @.sd)
A|||Ken D. wrote:
> Keith,
> Actually, I tried this logic and it worked.
>
> WHERE Month(FileRecDate) = Month(GetDate())AND
> Year(FileRecDate)=Year(GetDate())
VERY BAD and here is why:
You force SQL server to calculate Month(FileRecDate) and
Year(FileRecDate) for each row in the table and prevent
it from using an index that you may have on FileRecDate.
It is much better to write something like this:
WHERE FileRecDate BETWEEN @.MonthStartDateTime and @.MonthEndDateTime
Both @.MonthStartDateTime and @.MonthEndDateTime can be either
calculated by the SQL server (you can use CONVERT function)
or passed as parameters.
NEVER, NEVER force the calculation on a field in the WHERE
clause unless it is absolutely necessary.
Yura
> Thank you so much for your input...
> Ken
> "Keith Kratochvil" wrote:
>|||Ken,
Do not manipulate the column and try to use the pattern:
column <operator> constant
so sql server can considers the expression as a search argument.
Transact-SQL Query
SQL Server Performance Tuning Tips
http://www.sql-server-performance.com/transact_sql.asp
...
where
FileRecDate >= convert(varchar(6), getdate(), 112) + '01'
and FileRecDate < dateadd(month, 1, convert(varchar(6), getdate(), 112) +
'01')
Example:
set showplan_text on
go
select
*
from
northwind.dbo.orders
where
orderdate >= convert(varchar(6), getdate(), 112) + '01'
and orderdate < dateadd(month, 1, convert(varchar(6), getdate(), 112) + '01'
)
go
select
*
from
northwind.dbo.orders
where
year(orderdate) = year(getdate())
and month(orderdate) = month(getdate())
go
set showplan_text off
go
See that in the first execution plan, sql server will performs an index s
in index [OrderDate], but in the second, it will scan index [pk_Orders].
AMB
"Ken D." wrote:
> Keith,
> Actually, I tried this logic and it worked.
>
> WHERE Month(FileRecDate) = Month(GetDate())AND
> Year(FileRecDate)=Year(GetDate())
> Thank you so much for your input...
> Ken
> "Keith Kratochvil" wrote:
>|||> WHERE FileRecDate BETWEEN @.MonthStartDateTime and @.MonthEndDateTime
BETWEEN can be bad too; not necessarily for performance, but certainly when
constructing the end of the range, particularly if the datatype is datetime,
and/or is converted between datetime and smalldatetime, and/or the values in
the table can fall on midnight of the end of the range OR only contain
dates.
http://www.aspfaq.com/2280
A|||Aaron,
I dropped the code in and it works great. Could you explain the syntax for
previous month or current year.
Ken
"Aaron Bertrand [SQL Server MVP]" wrote:
> Ugh, how was performance? You might not notice while the table is small,
> but wait until you have a reasnable amount of data. If you have an index
on
> FileRecDate (which you should), you might try something along these lines:
> DECLARE @.sd SMALLDATETIME
> SET @.sd = DATEDIFF(DAY,DAY(GETDATE())-1,GETDATE())
> SELECT ...
> WHERE FileRecDate >= @.sd
> AND FileRecDate < DATEADD(MONTH, 1, @.sd)
> A
>
>|||Aaron Bertrand [SQL Server MVP] wrote:
>
> BETWEEN can be bad too; not necessarily for performance, but certainly whe
n
> constructing the end of the range, particularly if the datatype is datetim
e,
> and/or is converted between datetime and smalldatetime, and/or the values
in
> the table can fall on midnight of the end of the range OR only contain
> dates.
I simply assumed that Ken knows these caveats.
I usually define
@.StartDateTime and @.EndDateTime
as
'<month>/01/<year>' (i.e. 12 am) and '<month>/<end day>/<year>
11:59:59.9999 pm'
Or it can be as simple as
@.EndDateTime = DATEADD(mm, 1, @.StartDateTime )
and
WHERE (FileRecDate >= @.StartDateTime AND FileRecDate < @.EndDateTime)|||> I simply assumed that Ken knows these caveats.
I try not to make assumptions here. I would rather overload with
information than miss something and his app breaks.
> '<month>/01/<year>' (i.e. 12 am) and '<month>/<end day>/<year>
> 11:59:59.9999 pm'
I see two problems here.
(1) If you have dateformat d/m/y or English regional settings or French
language, some of these might break (depending on the month). If you're
going to use strings (which you don't have to do), you should at least
strive for ISO standard dates that are unambiguous and cannot be by
people or software (e.g. YYYYMMDD or YYYY-MM-DDTHH:MM:SS[.ms]).
(2) If you have smalldatetime, guess what happens? Your 23:59.9999 gets
rounded up to the next day. That might be bad, depending on the nature of
the data (e.g. you might erroneously include data from the next day, but
timestamped at midnight). In many systems, time is irrelevant (and often
stripped), so this is a common pitfall to watch our for.
I strongly recommend the following topics:
http://www.aspfaq.com/2023
http://www.karaszi.com/SQLServer/info_datetime.asp
I need to extract records in which the FileRecDate falls within the current
month/current year range. Also may need to filter by previous month/current
year; current year and prior year.
Any ideas?
Thanks,
Kenselect getdate()
Also:
select datepart(mm,getdate())
select datepart(dd,getdate())
select datepart(yy,getdate())
It is possible to get more fancy. You could declare a "start" and an "end"
variable and set the values according to your business logic. The variables
would then be used to filter your data within your query/stored procedure.
Keith
"Ken D." <KenD@.discussions.microsoft.com> wrote in message
news:49A7FD76-49BF-41D8-8437-9BD015FEF288@.microsoft.com...
>A little help please...
> I need to extract records in which the FileRecDate falls within the
> current
> month/current year range. Also may need to filter by previous
> month/current
> year; current year and prior year.
> Any ideas?
> Thanks,
> Ken|||Keith,
Actually, I tried this logic and it worked.
WHERE Month(FileRecDate) = Month(GetDate())AND
Year(FileRecDate)=Year(GetDate())
Thank you so much for your input...
Ken
"Keith Kratochvil" wrote:
> select getdate()
> Also:
> select datepart(mm,getdate())
> select datepart(dd,getdate())
> select datepart(yy,getdate())
> It is possible to get more fancy. You could declare a "start" and an "end
"
> variable and set the values according to your business logic. The variabl
es
> would then be used to filter your data within your query/stored procedure.
> --
> Keith
>
> "Ken D." <KenD@.discussions.microsoft.com> wrote in message
> news:49A7FD76-49BF-41D8-8437-9BD015FEF288@.microsoft.com...
>
>|||> WHERE Month(FileRecDate) = Month(GetDate())AND
> Year(FileRecDate)=Year(GetDate())
Ugh, how was performance? You might not notice while the table is small,
but wait until you have a reasnable amount of data. If you have an index on
FileRecDate (which you should), you might try something along these lines:
DECLARE @.sd SMALLDATETIME
SET @.sd = DATEDIFF(DAY,DAY(GETDATE())-1,GETDATE())
SELECT ...
WHERE FileRecDate >= @.sd
AND FileRecDate < DATEADD(MONTH, 1, @.sd)
A|||Ken D. wrote:
> Keith,
> Actually, I tried this logic and it worked.
>
> WHERE Month(FileRecDate) = Month(GetDate())AND
> Year(FileRecDate)=Year(GetDate())
VERY BAD and here is why:
You force SQL server to calculate Month(FileRecDate) and
Year(FileRecDate) for each row in the table and prevent
it from using an index that you may have on FileRecDate.
It is much better to write something like this:
WHERE FileRecDate BETWEEN @.MonthStartDateTime and @.MonthEndDateTime
Both @.MonthStartDateTime and @.MonthEndDateTime can be either
calculated by the SQL server (you can use CONVERT function)
or passed as parameters.
NEVER, NEVER force the calculation on a field in the WHERE
clause unless it is absolutely necessary.
Yura
> Thank you so much for your input...
> Ken
> "Keith Kratochvil" wrote:
>|||Ken,
Do not manipulate the column and try to use the pattern:
column <operator> constant
so sql server can considers the expression as a search argument.
Transact-SQL Query
SQL Server Performance Tuning Tips
http://www.sql-server-performance.com/transact_sql.asp
...
where
FileRecDate >= convert(varchar(6), getdate(), 112) + '01'
and FileRecDate < dateadd(month, 1, convert(varchar(6), getdate(), 112) +
'01')
Example:
set showplan_text on
go
select
*
from
northwind.dbo.orders
where
orderdate >= convert(varchar(6), getdate(), 112) + '01'
and orderdate < dateadd(month, 1, convert(varchar(6), getdate(), 112) + '01'
)
go
select
*
from
northwind.dbo.orders
where
year(orderdate) = year(getdate())
and month(orderdate) = month(getdate())
go
set showplan_text off
go
See that in the first execution plan, sql server will performs an index s
in index [OrderDate], but in the second, it will scan index [pk_Orders].
AMB
"Ken D." wrote:
> Keith,
> Actually, I tried this logic and it worked.
>
> WHERE Month(FileRecDate) = Month(GetDate())AND
> Year(FileRecDate)=Year(GetDate())
> Thank you so much for your input...
> Ken
> "Keith Kratochvil" wrote:
>|||> WHERE FileRecDate BETWEEN @.MonthStartDateTime and @.MonthEndDateTime
BETWEEN can be bad too; not necessarily for performance, but certainly when
constructing the end of the range, particularly if the datatype is datetime,
and/or is converted between datetime and smalldatetime, and/or the values in
the table can fall on midnight of the end of the range OR only contain
dates.
http://www.aspfaq.com/2280
A|||Aaron,
I dropped the code in and it works great. Could you explain the syntax for
previous month or current year.
Ken
"Aaron Bertrand [SQL Server MVP]" wrote:
> Ugh, how was performance? You might not notice while the table is small,
> but wait until you have a reasnable amount of data. If you have an index
on
> FileRecDate (which you should), you might try something along these lines:
> DECLARE @.sd SMALLDATETIME
> SET @.sd = DATEDIFF(DAY,DAY(GETDATE())-1,GETDATE())
> SELECT ...
> WHERE FileRecDate >= @.sd
> AND FileRecDate < DATEADD(MONTH, 1, @.sd)
> A
>
>|||Aaron Bertrand [SQL Server MVP] wrote:
>
> BETWEEN can be bad too; not necessarily for performance, but certainly whe
n
> constructing the end of the range, particularly if the datatype is datetim
e,
> and/or is converted between datetime and smalldatetime, and/or the values
in
> the table can fall on midnight of the end of the range OR only contain
> dates.
I simply assumed that Ken knows these caveats.
I usually define
@.StartDateTime and @.EndDateTime
as
'<month>/01/<year>' (i.e. 12 am) and '<month>/<end day>/<year>
11:59:59.9999 pm'
Or it can be as simple as
@.EndDateTime = DATEADD(mm, 1, @.StartDateTime )
and
WHERE (FileRecDate >= @.StartDateTime AND FileRecDate < @.EndDateTime)|||> I simply assumed that Ken knows these caveats.
I try not to make assumptions here. I would rather overload with
information than miss something and his app breaks.
> '<month>/01/<year>' (i.e. 12 am) and '<month>/<end day>/<year>
> 11:59:59.9999 pm'
I see two problems here.
(1) If you have dateformat d/m/y or English regional settings or French
language, some of these might break (depending on the month). If you're
going to use strings (which you don't have to do), you should at least
strive for ISO standard dates that are unambiguous and cannot be
bypeople or software (e.g. YYYYMMDD or YYYY-MM-DDTHH:MM:SS[.ms]).
(2) If you have smalldatetime, guess what happens? Your 23:59.9999 gets
rounded up to the next day. That might be bad, depending on the nature of
the data (e.g. you might erroneously include data from the next day, but
timestamped at midnight). In many systems, time is irrelevant (and often
stripped), so this is a common pitfall to watch our for.
I strongly recommend the following topics:
http://www.aspfaq.com/2023
http://www.karaszi.com/SQLServer/info_datetime.asp
Current Date in Transact SQL
Does anyone know if there's a Transact SQL statement to retrieve the current date? I've looked in the msdn T-SQL syntax pages, but I can't find it.
Thanks.getdate()
Friday, February 24, 2012
Cube Build Fails - The syntax for ''Number'' is incorrect
I am receiving the following error when attempting to build the project server 2007 cube. This error has been occurring since a project manager reported creating an MDX expressions. After creating the MDX expression and getting the cube build failure, the PM deleted the MDX expression. The cube failure has not gone away. Not being a guru in MDX expressions, is that something that is stored somewhere in the Project Server DB(s)? Or does anyone have suggestions on where to to from here?
Thanks for any help anyone can provide.
Project Server - MA854EPMD
DB Server - MA803DBSD\SQL2005_DEV
Error:
Error summary/areas:
CBS message processor failed
CBSOlapProcessingFailure
Queue
GeneralQueueJobFailed
Error details:
<?xml version="1.0" encoding="utf-16"?>
<errinfo>
<general>
<class name="CBS message processor failed">
<error id="17004" name="CBSOlapProcessingFailure" uid="33b225e1-9a18-4861-8745-78e02c0f1732" QueueMessageBody="Setting UID=00007829-4392-48b3-b533-5a5a4797e3c9 ASServerName=MA803DBSD\SQL2005_DEV ASDBName=ProjectServer2007AnalysisServicesRepository ASExtraNetAddress= RangeChoice=2 PastNum=1 PastUnit=0 NextNum=1 NextUnit=0 FromDate=01/01/2007 00:00:00 ToDate=08/22/2007 00:00:00 HighPriority=True" Error="Analysis Services session failed with the following error: Failed to process the Analysis Services database ProjectServer2007AnalysisServicesRepository on the MA803DBSD\SQL2005_DEV server. Error: Server: Operation completed with 210 problems logged.
Parser: The syntax for 'Number' is incorrect.
" />
</class>
<class name="Queue">
<error id="26000" name="GeneralQueueJobFailed" uid="71377bb7-87a9-43e5-969a-11cf8619eb76" JobUID="0e784c52-7e91-46cb-8aab-d54eb484fd33" ComputerName="MA851EPMD" GroupType="CBSRequest" MessageType="CBSQueueMessage" MessageId="2" Stage="" />
</class>
</general>
</errinfo>
It sounds like you're asking about Analysis Services for Project Server. Moving to the SQL Analysis Services forum.
Cheers,
Adam
|||I'm not sure how the project server cubes work, but normally you would open up the cube in the BI Development Studio to view these. This is probably something in the calculations tab of the cube, although there one or two other areas that expressions can be stored.|||Creation of the Cube for Project Server 2007 is managed through the Project Server UI. You basically give Project Server the Analysis Server name, the source (Project Server Reporting Database) and what you want to cube to be called. Then click "Build Cube." Very nice when it works. I am quite unfamiliar with where the MDX expressions are kept. Any suggestions on where I may look to try and clean that up?
Cube Build Fails - The syntax for ''Number'' is incorrect
I am receiving the following error when attempting to build the project server 2007 cube. This error has been occurring since a project manager reported creating an MDX expressions. After creating the MDX expression and getting the cube build failure, the PM deleted the MDX expression. The cube failure has not gone away. Not being a guru in MDX expressions, is that something that is stored somewhere in the Project Server DB(s)? Or does anyone have suggestions on where to to from here?
Thanks for any help anyone can provide.
Project Server - MA854EPMD
DB Server - MA803DBSD\SQL2005_DEV
Error:
Error summary/areas:
CBS message processor failed
CBSOlapProcessingFailure
Queue
GeneralQueueJobFailed
Error details:
<?xml version="1.0" encoding="utf-16"?>
<errinfo>
<general>
<class name="CBS message processor failed">
<error id="17004" name="CBSOlapProcessingFailure" uid="33b225e1-9a18-4861-8745-78e02c0f1732" QueueMessageBody="Setting UID=00007829-4392-48b3-b533-5a5a4797e3c9 ASServerName=MA803DBSD\SQL2005_DEV ASDBName=ProjectServer2007AnalysisServicesRepository ASExtraNetAddress= RangeChoice=2 PastNum=1 PastUnit=0 NextNum=1 NextUnit=0 FromDate=01/01/2007 00:00:00 ToDate=08/22/2007 00:00:00 HighPriority=True" Error="Analysis Services session failed with the following error: Failed to process the Analysis Services database ProjectServer2007AnalysisServicesRepository on the MA803DBSD\SQL2005_DEV server. Error: Server: Operation completed with 210 problems logged.
Parser: The syntax for 'Number' is incorrect.
" />
</class>
<class name="Queue">
<error id="26000" name="GeneralQueueJobFailed" uid="71377bb7-87a9-43e5-969a-11cf8619eb76" JobUID="0e784c52-7e91-46cb-8aab-d54eb484fd33" ComputerName="MA851EPMD" GroupType="CBSRequest" MessageType="CBSQueueMessage" MessageId="2" Stage="" />
</class>
</general>
</errinfo>
It sounds like you're asking about Analysis Services for Project Server. Moving to the SQL Analysis Services forum.
Cheers,
Adam
|||I'm not sure how the project server cubes work, but normally you would open up the cube in the BI Development Studio to view these. This is probably something in the calculations tab of the cube, although there one or two other areas that expressions can be stored.|||Creation of the Cube for Project Server 2007 is managed through the Project Server UI. You basically give Project Server the Analysis Server name, the source (Project Server Reporting Database) and what you want to cube to be called. Then click "Build Cube." Very nice when it works. I am quite unfamiliar with where the MDX expressions are kept. Any suggestions on where I may look to try and clean that up?
Cube Build Fails - The syntax for ''Number'' is incorrect
I am receiving the following error when attempting to build the project server 2007 cube. This error has been occurring since a project manager reported creating an MDX expressions. After creating the MDX expression and getting the cube build failure, the PM deleted the MDX expression. The cube failure has not gone away. Not being a guru in MDX expressions, is that something that is stored somewhere in the Project Server DB(s)? Or does anyone have suggestions on where to to from here?
Thanks for any help anyone can provide.
Project Server - MA854EPMD
DB Server - MA803DBSD\SQL2005_DEV
Error:
Error summary/areas:
CBS message processor failed
CBSOlapProcessingFailure
Queue
GeneralQueueJobFailed
Error details:
<?xml version="1.0" encoding="utf-16"?>
<errinfo>
<general>
<class name="CBS message processor failed">
<error id="17004" name="CBSOlapProcessingFailure" uid="33b225e1-9a18-4861-8745-78e02c0f1732" QueueMessageBody="Setting UID=00007829-4392-48b3-b533-5a5a4797e3c9 ASServerName=MA803DBSD\SQL2005_DEV ASDBName=ProjectServer2007AnalysisServicesRepository ASExtraNetAddress= RangeChoice=2 PastNum=1 PastUnit=0 NextNum=1 NextUnit=0 FromDate=01/01/2007 00:00:00 ToDate=08/22/2007 00:00:00 HighPriority=True" Error="Analysis Services session failed with the following error: Failed to process the Analysis Services database ProjectServer2007AnalysisServicesRepository on the MA803DBSD\SQL2005_DEV server. Error: Server: Operation completed with 210 problems logged.
Parser: The syntax for 'Number' is incorrect.
" />
</class>
<class name="Queue">
<error id="26000" name="GeneralQueueJobFailed" uid="71377bb7-87a9-43e5-969a-11cf8619eb76" JobUID="0e784c52-7e91-46cb-8aab-d54eb484fd33" ComputerName="MA851EPMD" GroupType="CBSRequest" MessageType="CBSQueueMessage" MessageId="2" Stage="" />
</class>
</general>
</errinfo>
It sounds like you're asking about Analysis Services for Project Server. Moving to the SQL Analysis Services forum.
Cheers,
Adam
|||I'm not sure how the project server cubes work, but normally you would open up the cube in the BI Development Studio to view these. This is probably something in the calculations tab of the cube, although there one or two other areas that expressions can be stored.|||Creation of the Cube for Project Server 2007 is managed through the Project Server UI. You basically give Project Server the Analysis Server name, the source (Project Server Reporting Database) and what you want to cube to be called. Then click "Build Cube." Very nice when it works. I am quite unfamiliar with where the MDX expressions are kept. Any suggestions on where I may look to try and clean that up?
Cube Build Fails - The syntax for ''Number'' is incorrect
I am receiving the following error when attempting to build the project server 2007 cube. This error has been occurring since a project manager reported creating an MDX expressions. After creating the MDX expression and getting the cube build failure, the PM deleted the MDX expression. The cube failure has not gone away. Not being a guru in MDX expressions, is that something that is stored somewhere in the Project Server DB(s)? Or does anyone have suggestions on where to to from here?
Thanks for any help anyone can provide.
Project Server - MA854EPMD
DB Server - MA803DBSD\SQL2005_DEV
Error:
Error summary/areas:
CBS message processor failed
CBSOlapProcessingFailure
Queue
GeneralQueueJobFailed
Error details:
<?xml version="1.0" encoding="utf-16"?>
<errinfo>
<general>
<class name="CBS message processor failed">
<error id="17004" name="CBSOlapProcessingFailure" uid="33b225e1-9a18-4861-8745-78e02c0f1732" QueueMessageBody="Setting UID=00007829-4392-48b3-b533-5a5a4797e3c9 ASServerName=MA803DBSD\SQL2005_DEV ASDBName=ProjectServer2007AnalysisServicesRepository ASExtraNetAddress= RangeChoice=2 PastNum=1 PastUnit=0 NextNum=1 NextUnit=0 FromDate=01/01/2007 00:00:00 ToDate=08/22/2007 00:00:00 HighPriority=True" Error="Analysis Services session failed with the following error: Failed to process the Analysis Services database ProjectServer2007AnalysisServicesRepository on the MA803DBSD\SQL2005_DEV server. Error: Server: Operation completed with 210 problems logged.
Parser: The syntax for 'Number' is incorrect.
" />
</class>
<class name="Queue">
<error id="26000" name="GeneralQueueJobFailed" uid="71377bb7-87a9-43e5-969a-11cf8619eb76" JobUID="0e784c52-7e91-46cb-8aab-d54eb484fd33" ComputerName="MA851EPMD" GroupType="CBSRequest" MessageType="CBSQueueMessage" MessageId="2" Stage="" />
</class>
</general>
</errinfo>
It sounds like you're asking about Analysis Services for Project Server. Moving to the SQL Analysis Services forum.
Cheers,
Adam
|||I'm not sure how the project server cubes work, but normally you would open up the cube in the BI Development Studio to view these. This is probably something in the calculations tab of the cube, although there one or two other areas that expressions can be stored.|||Creation of the Cube for Project Server 2007 is managed through the Project Server UI. You basically give Project Server the Analysis Server name, the source (Project Server Reporting Database) and what you want to cube to be called. Then click "Build Cube." Very nice when it works. I am quite unfamiliar with where the MDX expressions are kept. Any suggestions on where I may look to try and clean that up?
Sunday, February 19, 2012
CTE Error: Incorrect syntax near the keyword 'with'. If this statement is a common table expre
I am having this error when using execute query for CTE
Help will be appriciated
Would be interesting to have the code you tried to execute, because this i ibviously a syntax error.HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
|||? As Jens noted, you haven't showed us any code... But I'm betting you just need to use a semicolon before the "WITH": ;WITH myCTE AS ... -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <dba_sql@.discussions.microsoft.com> wrote in message news:35bbde4b-222b-4311-8087-09d80efaa94b@.discussions.microsoft.com... I am having this error when using execute query for CTE Help will be appriciated|||
I am getting this error when running the following code:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION ClassificationsInTree(@.ClassificationTreeId int)
RETURNS @.ClassificationsInTree TABLE (ClassificationId int)
AS
BEGIN
WITH CLINTREE(ClassificationId) AS (
SELECT TopClassificationId FROM ClassificationTree
WHERE ClassificationTreeId = 81203717
UNION ALL
SELECT ClassificationId FROM Classification
INNER JOIN CLINTREE ON
CLINTREE.ClassificationId = Classification.ParentClassificationTree
WHERE Classification.ClassificationId <> CLINTREE.ClassificationId
)
--INSERT @.ClassificationsInTree
SELECT ClassificationId FROM CLINTREE
OPTION (MAXRECURSION 10);
RETURN
END
GO
The error messages:
Msg 156, Level 15, State 1, Procedure ClassificationsInTree, Line 7
Incorrect syntax near the keyword 'WITH'.
Msg 170, Level 15, State 1, Procedure ClassificationsInTree, Line 18
Line 18: Incorrect syntax near 'MAXRECURSION'.
Any thoughts? This is the exact syntax found in the help files, no?
|||? I was able to run that batch on my end with no errors once I uncommented the insert line... -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <JGilbertie@.discussions.microsoft.com> wrote in message news:0b38d9cc-f171-4ae6-8377-fc50393d8045@.discussions.microsoft.com... I am getting this error when running the following code: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION ClassificationsInTree(@.ClassificationTreeId int) RETURNS @.ClassificationsInTree TABLE (ClassificationId int) AS BEGIN WITH CLINTREE(ClassificationId) AS ( SELECT TopClassificationId FROM ClassificationTree WHERE ClassificationTreeId = 81203717 UNION ALL SELECT ClassificationId FROM Classification INNER JOIN CLINTREE ON CLINTREE.ClassificationId = Classification.ParentClassificationTree WHERE Classification.ClassificationId <> CLINTREE.ClassificationId ) --INSERT @.ClassificationsInTree SELECT ClassificationId FROM CLINTREE OPTION (MAXRECURSION 10); RETURN END GO The error messages: Msg 156, Level 15, State 1, Procedure ClassificationsInTree, Line 7 Incorrect syntax near the keyword 'WITH'. Msg 170, Level 15, State 1, Procedure ClassificationsInTree, Line 18 Line 18: Incorrect syntax near 'MAXRECURSION'. Any thoughts? This is the exact syntax found in the help files, no?|||Thanks for the reply.
I get those same two errors whether that line is commented or not...
Is there some kind of configuration I need to do to enable the WITH statement? shot in the dark, but I can't see any difference from examples I've found for using WITH.
|||? None that I know of. The only one I could think of was compatability level -- but I just tested with a database set to compatability level 80 (SQL Server 2000) and was still not able to replicate the error. Regardless, you should probably make sure yours is set correctly. Right-click on your database in SSMS, click Properties, then Options. Make sure Compatability Level is set to SQL Server 2005... Aside from that, though, I'm not sure what's going on. Can you use CTEs at all (outside of UDFs?) And did you try adding a semicolon before the WITH, as I suggested before? It appears to be unnecessary on my end, but it's always a good idea anyway... -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <JGilbertie@.discussions.microsoft.com> wrote in message news:1cbe0923-6981-44eb-b4cd-8bdee48ad315@.discussions.microsoft.com... Thanks for the reply. I get those same two errors whether that line is commented or not... Is there some kind of configuration I need to do to enable the WITH statement? shot in the dark, but I can't see any difference from examples I've found for using WITH.|||So your suggestion to check the compatability level led me to the answer. The database server I was trying to run the query against is a SQL2000 server. We have many instances of SQL Server running for development purposes, and I didn't realize I was working against a SQL2000 instance. That database has the data I need in it, so I will have to move it to another machine.
A silly mistake, but I wouldn't have realized it, Thanks for your help!
|||I got the same error in Crystal Reports XI. I used Toad to write the SQL, then copied it into the command editor in Crystal. It worked fine, until I opened the same saved report on CRXI from a Citrix client. Still works fine using Toad, same datasource, credentials, etc. After reading this thread, I tried putting the semicolon in front of WITH and it worked. Strange to me, but it works,
Subscribe to:
Posts (Atom)