Cursors vs. Queries: incorrect

Showing posts with label incorrect. Show all posts

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...
>

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
>

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/
>

Thursday, March 22, 2012

Current User Roles List seems Incorrect

Our app uses the ROLES column of the CATALOGS rowset from the schema, and I seem to be seeing what I think are incorrect results.

The documenation says "Username is appended to ROLES if one of the roles uses dynamic security". OK.

But I often get the username even when there are no MDX expressions used for dimension or cell access. For example, every Foodmart 2000 cube I have migrated shows the username in the role list.

What is the definition of dynamic security for the purposes of the CATALOGS rowset?

I should have noted, this is SSAS 2005.

This is generally how I am retrieving the roles list, if any one wishes to replicate.

OleDbConnection conn = new OleDbConnection( "provider=msolap;SSPI=Negotiate;Prompt=1" ); conn.Open(); DataTable schema = conn.GetSchema("Catalogs"); foreach(DataRow row in schema.Rows) { if (row["ROLES"].ToString().Length > 0) // only available for the current catalog txtRoles.Text = row["ROLES"].ToString(); } conn.Close();

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

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:

It sounds like you're asking about Analysis Services for Project Server. Moving to the SQL Analysis Services forum.

Cheers,

Adam

Cube Build Fails - The syntax for ''Number'' is incorrect

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:

It sounds like you're asking about Analysis Services for Project Server. Moving to the SQL Analysis Services forum.

Cheers,

Adam

Cube Build Fails - The syntax for ''Number'' is incorrect

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:

It sounds like you're asking about Analysis Services for Project Server. Moving to the SQL Analysis Services forum.

Cheers,

Adam

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

SET QUOTED_IDENTIFIER ON

CREATE FUNCTION ClassificationsInTree(@.ClassificationTreeId int)

RETURNS @.ClassificationsInTree TABLE (ClassificationId int)

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

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,

Sunday, March 25, 2012

cursor How to >> use @dbname

cursor How to >> use @dbname

Cursor Declaration

Thursday, March 22, 2012

Current User Roles List seems Incorrect

Friday, February 24, 2012

Cube Build Fails - The syntax for ''Number'' is incorrect

Cube Build Fails - The syntax for ''Number'' is incorrect

Cube Build Fails - The syntax for ''Number'' is incorrect

Cube Build Fails - The syntax for ''Number'' is incorrect

Sunday, February 19, 2012

CTE Error: Incorrect syntax near the keyword 'with'. If this statement is a common table expre

Cursors vs. Queries

Blog Archive

About Me