1

Topic: Way to tables as a variable

Hello!
Let there is such simple SQL query. This request is launched through the server which addresses or to one  to the server, or to another. But titles of databases identical on these  servers. In requests the full path to tables is used.

SELECT
1;
2,
3
FROM
>>> Server1. 1.dbo. <<<Tablitsa1 Tablitsa1

The selected line needs to be made as a variable.

>>> Declare @ varchar (100) = ' Server1. 1.dbo ' <<<
SELECT
1;
2,
3
FROM
>>> @ <<<.1 Tablitsa1

It is clear that this request not the worker.
correctly to write request?

2

Re: Way to tables as a variable

Correctly - to provide IDENTICAL names on all servers.
View or Synonym those in curve pens.

3

Re: Way to tables as a variable

aleks222 wrote:

it is correct - to provide IDENTICAL names on all servers

Names of tables on different servers identical, and names of servers the different.

4

Re: Way to tables as a variable

aleks222 wrote:

View or Synonym

And how in request to write?

5

Re: Way to tables as a variable

ferzmikk wrote:

Names of tables on different servers identical, and names of servers the different.

Your task dares only dynamic request.

6

Re: Way to tables as a variable

Klick wrote:

it is passed...
Your task dares only dynamic request.

Here and consequently I want to write such request that in parameter specified server name.

7

Re: Way to tables as a variable

DECLARE @QUERY NVARCHAR (1000), @SCHEMA NVARCHAR (50);
SET @SCHEMA = N'Server1. 1.dbo ';
SET @QUERY = N'SELECT FIELD1, FIELD2, FIELD3 FROM ' + @SCHEMA + '.TABLE1 ';
EXEC SP_EXECUTE @QUERY;

I suppose, something similar.

8

Re: Way to tables as a variable

ferzmikk wrote:

Hello!
Let there is such simple SQL query. This request is launched through the server which addresses or to one  to the server, or to another. But titles of databases identical on these  servers. In requests the full path to tables is used.

SELECT
1;
2,
3
FROM
>>> Server1. 1.dbo. <<<Tablitsa1 Tablitsa1

The selected line needs to be made as a variable.

>>> Declare @ varchar (100) = ' Server1. 1.dbo ' <<<
SELECT
1;
2,
3
FROM
>>> @ <<<.1 Tablitsa1

It is clear that this request not the worker.
correctly to write request?

It is possible with perversions smile

SELECT
1;
2,
3
FROM openquery (linked server on itself with an identical title on all servers, ' select _ from .dbo. ')

9

Re: Way to tables as a variable

Andy_OLAP;
It if is valid the statement of the author of a subject "But titles of databases identical on these  servers", certainly.

10

Re: Way to tables as a variable

"The request is launched through the server which addresses or to one  to the server, or to another".
As already told either dynamic request, or synonyms. If request difficult enough it is better to select synonyms.
Reversal to the first server:

drop synonym dbo. Table1; create synonym dbo. Table1 for Server1.DB.scheme. Table1;
select * from dbo. Table1;

Reversal to the second server:

drop synonym dbo. Table1; create synonym dbo. Table1 for Server2.DB.scheme. Table1;
select * from dbo. Table1;

11

Re: Way to tables as a variable

Wlr-l wrote:

"the request is launched through the server which addresses or to one  to the server, or to another".
As already told either dynamic request, or synonyms. If request difficult enough it is better to select synonyms.

Do not learn people to the bad
https://blogs.msdn.microsoft.com/sqlsak … ql-server/

12

Re: Way to tables as a variable

Maxx;
Teach the good.

13

Re: Way to tables as a variable

declare
@table nvarchar (256) = ' DBName.schema. TableName'
,@sql nvarchar (2048) ="
set @sql = ' select... from openquery (ServerName, "select. from '" + table + ' ")'
exec (@sql) / sp_executesql @sql

14

Re: Way to tables as a variable

Maxx;
It agree.
Still it is possible to conclude request in stored procedures on  servers.
To them to make a synonym and to cause by name a synonym.
Here there is dynamics.

15

Re: Way to tables as a variable

ferzmikk wrote:

Here and consequently I want to write such request that in parameter specified server name.

declare @proc sysname = ' Server1. 1.sys.sp_executesql ';
declare @query nvarchar (max) = N'SELECT
1;
2,
3
FROM
dbo.1 ';
exec @proc @query;