Executing Variable

• Adam Campbell

  SSC Eights!

  Points: 982

  More actions

  July 11, 2006 at 9:03 am

  #173162

  I have a stored procedure that queries data on a linked server that is designated by the location selected.  Below is an example of my query.

  create proc dbo.testproc(@location varchar(10))

  as

  declare @server varchar(30)

  select @server = servername from servers where location = @location

  exec('select * from openquery(' + @server + ',''select * from testtable'')')

   

  My question is does reporting services support this type of procedure.  I can run it and get results from the data designer.  But I can't get it to work for the report.  Does anyone know if it will work, and if not what I can do?

  Thanks,

  ACFalcon

• Martin N

  Ten Centuries

  Points: 1216

  More actions

  July 12, 2006 at 7:53 pm

  #648858

  Mght be a security problem. Whatever SQL user the report is using to run then query needs to exist and have permissions on the table on the source server.

  ---

  Kindest Regards,

  Martin