>use [FirstSQLDatabase]; >go >declare @cSecondDb as Varchar(100); >declare @sql as varchar(max); >declare @Category as Varchar(25); >set @Category = '1009F'; >set @cSecondDb = 'SecondSQLDatabase'; >set @sql = 'select * from ' + @cSecondDb + '.dbo.mytable' + ' WHERE Category = ' + @Category >exec (@sql); >>
>>declare @Category as Varchar(25); >>set @Category = '1009F'; >>' WHERE Category = @Category' >>>>I get error no matter what I do with the @Category.
>>>use [FirstSQLDatabase]; >>>go >>>declare @cSecondDb as Varchar(100); >>>declare @sql as varchar(max); >>>set @cSecondDb = 'SecondSQLDatabase'; >>>set @sql = 'select * from ' + @cSecondDb + '.dbo.mytable'; >>>exec (@sql); >>>>>>
>>>>use [FirstSQLDatabase] >>>>go >>>>select * from SecondSQLDatabase..mytable where 1=1 >>>>>>>>
>>>>use [FirstSQLDatabase] >>>>go >>>>declare @cSecondDb as Varchar(100) >>>>set @cSecondDb = 'SecondSQLDatabase' >>>>select * from @cSecondDb..mytable where 1=1 -- this is the line that causes the syntax error. >>>>>>>>