November 5, 2013 at 4:20 am
Hi there - I rarely use Cursors (mainly due to performance reasons) but when I do I get an error stating that I must declare a variable which is already declared. I have attached my code and also the error message;
declare @dbname varchar (50)
declare @dbpath1 varchar (500)
declare @dbpath2 varchar (500)
declare @SQL1 varchar (max)
declare @SQL2 varchar (max)
declare db_cursor cursor for
select [Database] from [sapDatabaseLOV]
open db_cursor
Truncate table [dbo].[sapINV1]
Truncate table [dbo].[sapJDT1]
fetch next from db_cursor into @dbname
while @@FETCH_STATUS = 0
begin set @dbpath1 = '[ServerName].' + @dbname + '.dbo.INV1'
set @SQL1 = ' Insert into [dbo].[sapINV1]
(DocEntry,
Dscription,
Currency,
LineTotal,
AcctCode,
OcrCode,
[Source]
)
select
DocEntry,
Description,
Currency,
LineTotal,
AcctCode,
OcrCode,
@dbname
from ' + @dbpath1
set @dbpath2 = '[TBX-APP01].' + @dbname + '.dbo.JDT1'
set @SQL2 = ' Insert into [dbo].[sapJDT1]
([TransID],
[Line_ID],
[Account],
[Debit],
[Credit],
[ContraAct],
[LineMemo],
[Ref3Line],
[RefDate],
[Ref1],
[Ref2],
[ProfitCode],
[Source]
)
select
[TransID],
[Line_ID],
[Account],
[Debit],
[Credit],
[ContraAct],
[LineMemo],
[Ref3Line],
[RefDate],
[Ref1],
[Ref2],
[ProfitCode],
@dbname
from ' + @dbpath2
exec (@SQL1)
exec (@SQL2)
FETCH NEXT FROM db_cursor INTO @dbname
end
close db_cursor
deallocate db_cursor
Error message; Msg 137, Level 15, State 2, Line 17
Must declare the scalar variable "@dbname"
Any help would be great appreciated.
--------------------------------------------
Laughing in the face of contention...
November 5, 2013 at 4:46 am
You've used your variable in a different context to where it is defined.
Try: -
set @SQL1 = ' Insert into [dbo].[sapINV1]
(DocEntry,
Dscription,
Currency,
LineTotal,
AcctCode,
OcrCode,
[Source]
)
select
DocEntry,
Description,
Currency,
LineTotal,
AcctCode,
OcrCode,
'+CHAR(39)+@dbname+CHAR(39)+'
from ' + @dbpath1
set @dbpath2 = '[TBX-APP01].' + @dbname + '.dbo.JDT1'
set @SQL2 = ' Insert into [dbo].[sapJDT1]
([TransID],
[Line_ID],
[Account],
[Debit],
[Credit],
[ContraAct],
[LineMemo],
[Ref3Line],
[RefDate],
[Ref1],
[Ref2],
[ProfitCode],
[Source]
)
select
[TransID],
[Line_ID],
[Account],
[Debit],
[Credit],
[ContraAct],
[LineMemo],
[Ref3Line],
[RefDate],
[Ref1],
[Ref2],
[ProfitCode],
'+CHAR(39)+@dbname+CHAR(39)+'
from ' + @dbpath2
November 5, 2013 at 4:54 am
Nice! Thanks for that, works perfectly.
--------------------------------------------
Laughing in the face of contention...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply