Synax problem driving me nuts 
Author Message
 Synax problem driving me nuts

I would appreciate help on this, I must be brain dead today because I
really do not see what the problem is:

I keep getting this error:
Server: Msg 170, Level 15, State 1, Line 12
Line 12: Incorrect syntax near '+'.

TIA

CREATE PROCEDURE usp_DefragDatabase

AS









    DECLARE PKMS_Tables CURSOR LOCAL FOR
        select cast(tableid as varchar(30)) as tableiD, cast(indexid
as varchar(30)) as indexid

'''' + ' and no_rows > 100
        OPEN PKMS_Tables


    BEGIN

char(39) +  ISQL -E -Q + char(34) + ''DBCC SHOWCONTIG'' ( + char(39) +

char(39) + )  + char(34) + char(39) + char(39) + char(39)
                Insert Into res    


    END
    CLOSE PKMS_Tables')



Tue, 24 Aug 2004 00:22:48 GMT
 Synax problem driving me nuts

John,

These type problems can usually be found by just printing out the value of
the sql string your attempting to execute to see what doesn't look right.

--
Andrew J. Kelly   SQL MVP
Targitinteractive, Inc.


Quote:
> I would appreciate help on this, I must be brain dead today because I
> really do not see what the problem is:

> I keep getting this error:
> Server: Msg 170, Level 15, State 1, Line 12
> Line 12: Incorrect syntax near '+'.

> TIA

> CREATE PROCEDURE usp_DefragDatabase

> AS









>     DECLARE PKMS_Tables CURSOR LOCAL FOR
>         select cast(tableid as varchar(30)) as tableiD, cast(indexid
> as varchar(30)) as indexid

> '''' + ' and no_rows > 100
>         OPEN PKMS_Tables


>     BEGIN

> char(39) +  ISQL -E -Q + char(34) + ''DBCC SHOWCONTIG'' ( + char(39) +

> char(39) + )  + char(34) + char(39) + char(39) + char(39)
> Insert Into res


>     END
>     CLOSE PKMS_Tables')



Tue, 24 Aug 2004 00:52:45 GMT
 Synax problem driving me nuts
WOW!!!!????

I would recommend against using dynamic-sql.  You are opening yourself up to
security issues as well as potential performance problems.

A better approach (in my opinion) would be to avoid dynamic-sql and just use
straight sql within your stored procedures.  Yes, you will need to create
more stored procedures this way, but you also get the benefit of using a
stored procedure (precompiled and cached plan, increased security...).

Read more here (thanks to Erland Sommarskog for posting this)
http://www.algonet.se/~sommar/dynamic_sql.html

In addition to the information above, maybe this will help with your
debugging....


varchar(1)







    DECLARE PKMS_Tables CURSOR LOCAL FOR
        select cast(tableid as varchar(30)) as tableiD, cast(indexid
as varchar(30)) as indexid

'''' + ' and no_rows > 100
        OPEN PKMS_Tables


    BEGIN

char(39) +  ISQL -E -Q + char(34) + ''DBCC SHOWCONTIG'' ( + char(39) +

char(39) + )  + char(34) + char(39) + char(39) + char(39)
Insert Into res


    END
    CLOSE PKMS_Tables')

--
Keith, SQL Server MVP


Quote:
> I would appreciate help on this, I must be brain dead today because I
> really do not see what the problem is:

> I keep getting this error:
> Server: Msg 170, Level 15, State 1, Line 12
> Line 12: Incorrect syntax near '+'.

> TIA

> CREATE PROCEDURE usp_DefragDatabase

> AS









>     DECLARE PKMS_Tables CURSOR LOCAL FOR
>         select cast(tableid as varchar(30)) as tableiD, cast(indexid
> as varchar(30)) as indexid

> '''' + ' and no_rows > 100
>         OPEN PKMS_Tables


>     BEGIN

> char(39) +  ISQL -E -Q + char(34) + ''DBCC SHOWCONTIG'' ( + char(39) +

> char(39) + )  + char(34) + char(39) + char(39) + char(39)
> Insert Into res


>     END
>     CLOSE PKMS_Tables')



Tue, 24 Aug 2004 00:54:19 GMT
 Synax problem driving me nuts
John,

Selecting from stored procedures is not allowed. Find another way to execute
master..xp_cmdshell.

--
Ivan Arjentinski
----------------------------------------------------------------------
Please reply to newsgroups. Inclusion of table schemas (CREATE TABLE
scripts), sample data (INSERT INTO....) and desired result set when asking
for assistance is highly appreciated
----------------------------------------------------------------------


Quote:
> I would appreciate help on this, I must be brain dead today because I
> really do not see what the problem is:

> I keep getting this error:
> Server: Msg 170, Level 15, State 1, Line 12
> Line 12: Incorrect syntax near '+'.

> TIA

> CREATE PROCEDURE usp_DefragDatabase

> AS









>     DECLARE PKMS_Tables CURSOR LOCAL FOR
>         select cast(tableid as varchar(30)) as tableiD, cast(indexid
> as varchar(30)) as indexid

> '''' + ' and no_rows > 100
>         OPEN PKMS_Tables


>     BEGIN

> char(39) +  ISQL -E -Q + char(34) + ''DBCC SHOWCONTIG'' ( + char(39) +

> char(39) + )  + char(34) + char(39) + char(39) + char(39)
> Insert Into res


>     END
>     CLOSE PKMS_Tables')



Tue, 24 Aug 2004 01:00:51 GMT
 Synax problem driving me nuts
John,

If you're bent on doing it this way vs the other sugestions, try creating a
command string in a local vairable and then executing that.  Build it a few
lines at a time instead of one big line and you may find the problem.  Also,
check how you're invoking the DBCC command.  Pretty sure you need an "exec"
in there.

However, since this is already in a proc, why not just keep the entire thing
in there?  The only thing that rally needs to be dynamic is the DBCC call.
The rest is all static and can be executed in the proc as precompiled code.

HTH,
Matt


Quote:
> I would appreciate help on this, I must be brain dead today because I
> really do not see what the problem is:

> I keep getting this error:
> Server: Msg 170, Level 15, State 1, Line 12
> Line 12: Incorrect syntax near '+'.

> TIA

> CREATE PROCEDURE usp_DefragDatabase

> AS









>     DECLARE PKMS_Tables CURSOR LOCAL FOR
>         select cast(tableid as varchar(30)) as tableiD, cast(indexid
> as varchar(30)) as indexid

> '''' + ' and no_rows > 100
>         OPEN PKMS_Tables


>     BEGIN

> char(39) +  ISQL -E -Q + char(34) + ''DBCC SHOWCONTIG'' ( + char(39) +

> char(39) + )  + char(34) + char(39) + char(39) + char(39)
> Insert Into res


>     END
>     CLOSE PKMS_Tables')



Tue, 24 Aug 2004 01:58:31 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. Stored Procedure Problem --- Driving me nuts!

2. MS Access Problem - Driving me Nuts

3. DTS from ASP security driving me nuts!

4. Driving me nuts!!

5. SQL Error 2417 driving me nuts

6. Triggers drive me NUTS!

7. I'm sure it something simple but it's driving me nuts :-(

8. HELP: isql is driving me nuts!

9. Index Tuning Wizard Driving me NUTS

10. Please Help, this is driving me nuts !!

11. SUB QUERY driving me NUTS!

12. This query is driving me nuts - please help !!


 
Powered by phpBB® Forum Software