December 6, 2005 at 3:12 pm
I am trying to run the query below and have tried similar queries but have can't seem to figure out how to use a variable in place of the server name. I have also in the past tried to do the same with table names and can't get it to work either.
The query is just trying to query for all linked servers and using a cursor loop through those and fix some phone numbers that need updating. If you have any suggestions or need any other information feel free to ask.
Use master
Declare @srvname sysname
Declare SrvName cursor for
select srvname
from sysservers
order by srvname
Open SrvName
Fetch Next From SrvName into @srvname
While @@fetch_status = 0
Begin
Update @srvname.dbname.dbo.tblname
SET PHONE1 = STUFF('('+STUFF(PHONE1, 4, 0, ')'), 10, 0, '-')
WHERE (LEN(PHONE1) = 10) AND (NOT (PHONE1 LIKE '%(%')) AND (NOT (PHONE1 LIKE '%)%')) AND (RTRIM(LTRIM(PHONE1)) <> '') AND (isnumeric(PHONE1)
> 0) AND (PHONE1 != '0000000000')
Fetch Next From SrvName into @srvname
End
Close SrvName
Deallocate SrvName
By the way, I am currently running sql 2000 with sp4 on a win2k server.
Thank you!
December 6, 2005 at 4:22 pm
You cannot perform your query using servername as a parameter,
Link to dynamic sql, but you still cannot use a variable servername, Look towards the bottom of article, and look at linkserver, and openquery
http://www.sommarskog.se/dynamic_sql.html
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply