0

I need to transfer data from a linked server to our main SQL server. The issue is, that the table name changes everyday. I have looked around this site to find out if it is even possible to have a variable database name, which it is, but also to see if it is possible to have variables in a OPENQUERY, which it also is.

But i am struggling to combine those needs, so i have a variable table name in a OPENQUERY.

I need something like this:

Declare @LinkedServer as varchar(max) = 'LinkedServer'
Declare @TName as varchar(max) = 'TName'+substring(cast(cast(getdate() as date) as
varchar(50)),1,4)+substring(cast(cast(getdate() as date) as
varchar(50)),6,2)+substring(cast(cast(getdate() as date) as
varchar(50)),9,2)

SELECT * FROM OPENQUERY(@LinkedServer, 'SELECT * FROM dbo.@TName')

Is there any way i can make a variable table name in a OPENQUERY ?

Thank you for your help.

/Mikkel

Mikkel Paulsen
  • 75
  • 8
  • Do you have access to create synonyms on the linked server?. preferably create the synonym on the source server, if not the main SQL server could house it... – Intern87 Mar 31 '17 at 10:09

2 Answers2

1

I'd write a synonym which gets updated every day before you kick off your data extraction job. Then you don't need to be updating (potentially a tonne of) references.

CREATE SYNONYM LinkedTableA
FOR
ServerName.DBName.dbo.TName20170331

SELECT * FROM LinkedTableA
Intern87
  • 469
  • 1
  • 6
  • 18
  • We only have read access on the linked server, we cannot create anything nor can we get tables, databases og Synonyms created on the linked server. – Mikkel Paulsen Mar 31 '17 at 10:18
  • well try adding this to your main SQL server. you can make a stored procedure to update the synonym name and trigger it each day. – Intern87 Mar 31 '17 at 10:53
0

The answer i have, is this:

USE [DataBase]
GO

DROP SYNONYM [dbo].[eCallByCallStat]
GO

declare @tablename varchar(50) 

set @tablename = 'Server1..dbo.eCallByCallStat'+substring(cast(cast(getdate()-1 as date) as varchar(50)),1,4)+substring(cast(cast(getdate()-1 as date) as varchar(50)),6,2)+substring(cast(cast(getdate()-1 as date) as varchar(50)),9,2)

declare @sql varchar(500)

set @sql = 'CREATE SYNONYM [dbo].[eCallByCallStat] FOR ' + @tablename 

exec (@sql)

This will run everymorning updating the table name in the synonym, and then we will insert that data into a prober table so we have all the data.

Mikkel Paulsen
  • 75
  • 8