May 2, 2006 at 5:06 am
Hi,
In the following statement:
select
* from [shl12500].pubs.dbo.authors
I need to replace shl12500 with a variable that stores the machine name.
Consider this:
I have a table by the name Machine which contains a column called macname that contains the name of the machine(i.e.,shl12500).
But when I do this as follows, i get an error.
declare
@machineName varchar(20)
select
@machineName = macname from machine
select
* from [@machineName].pubs.dbo.authors
GO
Is there an alternate way to replace the machine name instead of hardcoding it in the from clause?
Thanks,
sandhya
May 3, 2006 at 4:08 am
Build a sql string and execute.
DECLARE
@SQLString varchar(1000)
DECLARE @S2 nvarchar(1000)
DECLARE @machineName varchar(20)
SELECT
@machineName = macname from machine
SET
@SQLString = 'SELECT * FROM '
SET
@SQLString = @SQLString + @machinename
SET @SQLString = @SQLString + '.[pubs].[dbo].[authors]'
SELECT
@S2 = CAST(@SQLString as NVarchar(1000))
EXECUTE
sp_executesql @S2
-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible. :crazy:
May 4, 2006 at 6:22 am
Hi,
Thanks for the response. the following would also work:
declare @machineName varchar(20)
select @machineName = macname from machine
exec('select * from [' + @machineName + ].pubs.dbo.authors')
Regards,
Sandhya
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply