Hello
I have written this stored procedure but I am getting following error.


Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '1'.
Server: Msg 50000, Level 16, State 1, Procedure CopyDataArchive, Line
24
Error occured while copying data to December

Here is the code for stored procedure:
CREATE PROC dbo.CopyDataArchive AS
BEGIN
    SET nocount ON
    Declare @CutOffDate datetime
    SET @CutOffDate ='2005-12-11'
    declare @tablename as varchar(20)
    set @tablename = datename(month, @CutOffDate)
    Declare @fdgm datetime
    Declare @fdnm datetime
    SET @fdgm =  DATEADD(mm, DATEDIFF(mm,0,@CutOffDate), 0)
    SET @fdnm=DATEADD(mm,0,DATEADD(mm, DATEDIFF(m,0,@CutOffDate)+1, 0))

    Print cast(@fdgm as varchar(20)) + 'firstdaygivenmonth'
    print cast(@fdnm as varchar(20)) + 'firstdaynextmonth'


    BEGIN TRAN
        EXEC(' INSERT INTO  dbo.' + @tablename  + '  SELECT * FROM dbo.Events
WHERE TimeGenerated>='+ @fdgm  +  ' and TimeGenerated< = '+@fdnm)

        IF @@ERROR <> 0
        BEGIN
            ROLLBACK TRAN
            RAISERROR ('Error occured while copying data to %s', 16, 1,
@tablename)
            RETURN -1
        END

        DELETE dbo.Events WHERE TimeGenerated>=@fdgm and TimeGenerated<
=@fdnm

        IF @@ERROR <> 0
        BEGIN
            ROLLBACK TRAN
            RAISERROR ('Error occured while deleting data from dbo.Orders', 16,
1)
            RETURN -1
        END
    IF @@TRANCOUNT > 0
    BEGIN
        COMMIT TRAN
        RETURN 0
    END
END
GO


Thanks in advance for the help.

Instead of executing the query string:

EXEC(' INSERT INTO  dbo.' + @tablename  + '  SELECT * FROM dbo.Events
WHERE TimeGenerated>='+ @fdgm  +  ' and TimeGenerated< = '+@fdnm)

....try printing it first:

print (' INSERT INTO  dbo.' + @tablename  + '  SELECT * FROM dbo.Events
WHERE TimeGenerated>='+ @fdgm  +  ' and TimeGenerated< = '+@fdnm)

....then copy the result to a new window in QA and try to parse it, then
execute it.


ML

---
http://milambda.blogspot.com/

On second glance I suspect the culprits are @fdgm and @fdnm variables. They
are of data type datetime and you concatenate them into a character string.

The best way to avoid that would IMHO be to use the sp_executesql system
procedure instead of the dreaded EXECUTE(). You can familiarize yourself with
it here: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ea-ez_2h7w.asp

You can parameterize your dynamically created query string and use the
capabilities of sp_executesql to execute it without mangling data (i.e. by
converting datetime values to character values).


ML

---
http://milambda.blogspot.com/

So you are getting an error at execution time right ? (Because the sql
is valid).

I would put something more in to trace the execution. Instead of
executing the stringon the fly, put it in a VARCHAR variable and
execute this. With that you can PRINT the sqlstatement which apparantly
produces the error.

DELCARE @SQLStatement NVARCHAR(4000)
SET @SQLStatement = 'INSERT INTO  dbo.' + @tablename  + '  SELECT *
FROM dbo.Events
WHERE TimeGenerated>='+ @fdgm  +  ' and TimeGenerated< = '+@fdnm

PRINT @SQLStatement

EXEC(@SQLStatement)

Without knowing your data its hardly possible to see where the error is
based on, for you this should be no problem with the debugged code.

HTH, jens Suessmeyer.

When you concatenate @fdgm and @fdnm with a string they get converted to
strings, so you get something like

.... WHERE TimeGenerated >= 2005/12/08 12:34:56.72 and TimeGenerated ...

From this one sees that quotes are required around the dates.  So you should
modify the SP to read

EXEC(' INSERT INTO  dbo.' + @tablename  + '  SELECT * FROM dbo.Events
WHERE TimeGenerated>='''+ @fdgm  +  ''' and TimeGenerated< = '''+@fdnm+'''')

HTH,

Mike

Show quoteHide quote

OBJECTPROPERTY problem
T-SQL: How to loop through the resultset from another procedure?
Count Occurances in a column
Who has the highest bid?
Need Help Please...