Reputation: 187
I am trying to execute the following piece of code:
use projects
set nocount on
CREATE TABLE #t (dbname VARCHAR(20), col2 datetime)
declare @sql nvarchar(max)
declare @proc_sql nvarchar(max)
declare @table_car table (rowid int not null primary key identity(1,1), carrier varchar(500))
insert into @table_car (carrier)
select distinct t.db_name from table t with(nolock)
inner join table_t1 t1 with(nolock) on t.id = t1.id
declare @rowstoprocess int
declare @currentrow int
declare @dbname varchar(500)
set @rowstoprocess = @@rowcount
set @currentrow = 0
while @currentrow < @rowstoprocess
begin
set @currentrow = @currentrow+1
select @dbname = carrier
from @table_car
where rowid = @currentrow
select @sql = '
insert into #t (dbname, col2)
SELECT DISTINCT db_name(), col_x from'+@dbname+'..table_xyz'
set @proc_sql = replace(@sql, @dbname, @dbname)
exec (@proc_sql)
end
However, when I execute this, I am getting an error:
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.
which I believe has nothing to do with Line 3 but the has a syntax error near the @dbname
variable being used inside the while loop.
Please help
Upvotes: 0
Views: 147
Reputation: 923
I believe you are just missing a space between right after the from in "col_x from'+@db_name". Should be like this:
SELECT DISTINCT db_name(), col_x from '+@dbname+'..table_xyz'
-------------------------------------^
One thing you can do is PRINT out your sql strings when debugging and then run them on their own. Easier to debug. So in this example, instead of EXEC (@proc_sql) do PRINT @proc_sql.
Upvotes: 3