[RESOLVED] Cursor stays on first record in table

[conwa02976]

Hello!! I am using SQL Server 2008 and I am attempting to create a stored procedure to Truncate certain tables in my database so that I can rewrite records to those tables on a nightly basis from Access tables. The problem I am having is that my fetch always returns only the first record from my table (AccessCopyTables). Can someone please tell me what I am doing wrong? Here is the sproc:

USE [Mercury]
GO
/****** Object:  StoredProcedure [dbo].[TruncTables]    Script Date: 04/12/2010 14:52:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[TruncTables] AS
DECLARE @TName nchar(30)
DECLARE @query nvarchar(max)
DECLARE @curs cursor

SET @curs = CURSOR FOR SELECT TableName FROM AccessCopyTables
OPEN @curs
FETCH  NEXT FROM @curs INTO @TName
WHILE @@FETCH_STATUS = 0
	BEGIN
		PRINT 'Table Name ' + @TName
		set @query = N'TRUNCATE TABLE ' + @TName
		exec sp_executesql @query
	END
	FETCH NEXT FROM @curs INTO @TName
END
CLOSE @curs
DEALLOCATE @curs

Thanks for the help in advance!!

[GaryMazzone]

Are all the table names exactly 20 chars?

[conwa02976]

No, anywhere from 6 characters to 30.

[GaryMazzone]

Then I would not use nchar for the tablename var but NVARCHAR(30)

[conwa02976]

I will try that next. I had not tried to actually compile the sproc yet so I just tried it and I got an error. I got an "Incorrect syntax near the keyword 'CLOSE'. There is a squiggly line there and when I hover over it I get: Expecting CONVERSATION. Where in the world is that coming from?

[GaryMazzone]

Here is an example I use to drop some tables

DECLARE
    @TNAme VARCHAR(255) ,
    @sql NVARCHAR(MAX)
DECLARE GettmTables_cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT LTRIM(RTRIM(TABLE_NAME)) FROM INFORMATION_SCHEMA.tables
WHERE TABLE_NAME LIKE 'tmp%' AND TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA = 'dbo' AND TABLE_NAME <> 'TMPLATE'
OPEN GettmTables_cursor
FETCH NEXT FROM GettmTables_cursor INTO @TName
WHILE @@FETCH_STATUS = 0 
    BEGIN
        SET @sql = 'DROP TABLE ' + @TNAme
        EXECUTE sys.sp_executesql 
            @sql
        FETCH NEXT FROM GettmTables_cursor INTO @TName
    END
CLOSE GettmTables_cursor
DEALLOCATE GettmTables_cursor

[conwa02976]

Gary, thanks for the help. I copied your sproc over mine and updated the select statement with my select statement and it worked like a charm. I see a few syntax differences but nothing really major so I still don't know what was wrong with my original but it doesn't matter now since yours works.
I will mark this as closed!!