March 5, 2008 at 3:13 pm
hi guys i hope you can help me, i am trying to create a stored procedure to archive one of my biggest tables in my database, this is part of my stored procedure
*/code
ALTER PROC [dbo].[uspArchiveOldData]
AS
BEGIN
SET NOCOUNT ON
DECLARE @SWITCHDB VARCHAR(255) , @ARCHIVEDB VARCHAR(255), @TABLENAME VARCHAR(255)
SELECT [highlight]@SWITCHDB ='DBDistributors', [/highlight]@ARCHIVEDB='DBDistributors_ARCH',
[highlight]@TABLENAME='Distributions_'+ CONVERT(CHAR(4),YEAR(GETDATE()) -1)
EXEC ("[/highlight]USE "+ @ARCHIVEDB +"
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE TYPE ='U' AND NAME ='"+ @TABLENAME +"')…………….
code/*
but i keep getting this error
Msg 102, Level 15, State 1, Procedure uspArchiveOldData, Line 11
Incorrect syntax near '
USE '.
Msg 103, Level 15, State 4, Procedure uspArchiveOldData, Line 13
The identifier that starts with '')
any hints what could the problem be?
March 5, 2008 at 3:43 pm
Try this:
EXEC ('USE '+ @ARCHIVEDB +'
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE TYPE =''U'' AND NAME ='''+ @TABLENAME +''')
select 7 else select 8') -- for example only
March 5, 2008 at 3:49 pm
By the way, the 'use db' part seems odd. You can do this instead (mindful of the usual caveats about querying system tables):
exec(' if exists( select * from ' + @ARCHIVEDB + '.dbo.SYSOBJECTS WHERE TYPE =''U'' AND NAME ='''+ @TABLENAME +''')
select 7 else select 8')
March 6, 2008 at 7:37 am
thank you so much, i did your changes but now i get these errors:
ALTER PROC uspArchiveOldData
AS
BEGIN
SET NOCOUNT ON
DECLARE @SWITCHDB VARCHAR(255) , @ARCHIVEDB VARCHAR(255), @TABLENAME VARCHAR(255)
SELECT @SWITCHDB ='DBDistributors' ,
@ARCHIVEDB='DBDistributors_Archive',
@TABLENAME='DistributorsTable_'+ CONVERT(CHAR(4),YEAR(GETDATE()) -1)
EXEC(' IF EXISTS( SELECT * from ' + @ARCHIVEDB + '.dbo.SYSOBJECTS WHERE TYPE =''U'' AND NAME ='''+ @TABLENAME +''')
BEGIN
DECLARE @INSERTEDROWCOUNT INT, @DELETEDROWCOUNT INT , @ERROR INT
BEGIN TRAN ARCHIVE_TABLE
SELECT DistributorID_PK,
CustomerID_FK,
ID,
ActivityType_FK,
Status,....
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.
Msg 208, Level 16, State 1, Procedure uspArchiveolddata, Line 17
Invalid object name '+ @SWITCHDB +.DBO.DistributorsTable'.
Msg 266, Level 16, State 2, Procedure uspArchiveolddata, Line 17
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.
any suggestions please?
March 6, 2008 at 8:00 am
I don't have time right now to work on this, but I can give you a suggestion. When building a dynamic sql statement, build it in steps. Select or print each section until you have the query you are after.
😎
March 6, 2008 at 9:29 am
I'll echo the suggestion to build strings and use print statements.
-- your other delares and sets then
declare @sql Nvarchar(1000)
set @sql = 'IF EXISTS( SELECT * from ' + @ARCHIVEDB + '.dbo.SYSOBJECTS ' -- and so on
print (@sql)
exec (@sql)
The part of the error message that says
Invalid object name '+ @SWITCHDB +.DBO.DistributorsTable'.
shows you are missing at least one quotation mark before .DBO. You want
'+ @SWITCHDB +'.DBO.DistributorsTable'
March 6, 2008 at 12:12 pm
thanks i tried your advice and i get the print so you are right i need to add ' ' but when i do it goes back to
WHERE TYPE ='U'AND NAME =[highlight] '+@TABLENAME+ '[/highlight]
now the table name does not display anymore :crazy:
March 6, 2008 at 12:25 pm
You can't "build" the expression INSIDE Exec. You have to build it FIRST, then pass it fully built into Exec.
As in - (I haven't checked to see if you missed any ', etc...)
...
DECLARE @SWITCHDB VARCHAR(255) , @ARCHIVEDB VARCHAR(255), @TABLENAME VARCHAR(255)
declare @sql varchar(2000)
SELECT @SWITCHDB ='DBDistributors' ,
@ARCHIVEDB='DBDistributors_Archive',
@TABLENAME='DistributorsTable_'+ CONVERT(CHAR(4),YEAR(GETDATE()) -1)
set @sql=' IF EXISTS( SELECT * from ' + @ARCHIVEDB + '.dbo.SYSOBJECTS WHERE TYPE =''U'' AND NAME ='''+ @TABLENAME +'''
Exec (@SQL)
...
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 6, 2008 at 12:31 pm
Before we go too much farther, are you positive you need to use dynamic sql to do this archive? As you are experiencing, the more code you have to add, the more likely there will be trouble with quotes and plus signs, etc. From the code posted so far the only unknown table or db name has the year at the end. Will this proc run more than once a year?
Anyway, you are probably missing a quote or have 1 too many somewhere. You would need to see more of the code to find out where.
March 6, 2008 at 12:37 pm
yes, i am going to run this every night.
March 6, 2008 at 12:54 pm
You can run it every night but it does not have to be Dynamic SQL. Why complicate your life when you can do it without. You know the name of the table that you are going to archive. Right?
-Roy
March 7, 2008 at 6:42 am
the name of the table will change according to the date the record was created. I have a table for each year. what the sp does is that it gets the record 's year and if it is more than a year old then the record will go to the archive database, in there i have a table for each year. Can you tell me which way would be better for you? i am open to any suggestions.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply