execlinked-serveropenrowsetsql serversql-server-2008-r2
This seems like a basic question but I can't find any answers out there – I need to be able to get the server name/instance etc. from a linked server. I've tried a couple of things:
select <linked server>.@@SERVERNAME;
select <linked server>.SERVERPROPERTY('ServerName');
… but no joy. Any ideas?
This is SQL 2008 R2 & 2014 (2008R2 is the linked server)
EDIT: Errors are:
Msg 102, Level 15, State 1, Line 2 Incorrect syntax near
'@@SERVERNAME'.
Quotes must be escaped in the OPENQUERY argument.
The return type for the TRIM function is text, so you should cast it back to varchar.
Select * from OPENQUERY(LinkedServer,
'Select CAST(TRIM(trailing '' '' from ad.userid) AS varchar(8)) As "User ID"
FROM alphadawg ad')
Not sure where you copied this code from, but it looks like it came from a web site that translated minus signs (-) to long dashes (–). You can see this in Management Studio if you turn IntelliSense on:
Here is the easy way to compare the same range as the year before:
DECLARE @startdate date = '20170101',
@enddate date = '20170212';
SELECT @startdate, DATEADD(YEAR, -1, @startdate),
@enddate, DATEADD(YEAR, -1, @enddate);
This compares the same period as last year, but I'm not sure that's what you're after since you are also using the zero date and datediff - not clear why. It does not account for leap year; you'll need to define what should happen if the range involves 2/29 either this year or last year.
When you use those dates in a query, don't do this subtracting 3 milliseconds nonsense - it only leads to problems. If you're trying to get all the data for February 12th even if it has time, then use < DATEADD(DAY, 1, @enddate). Also, don't use YY, just type out YEAR. It is also far easier to get the full range of last year:
-- for SQL Server 2012+
WHERE col >= DATEFROMPARTS(YEAR(@startdate)-1, 1, 1)
AND col < DATEFROMPARTS(YEAR(@startdate), 1, 1);
-- for older, unsupported versions:
WHERE col >= DATEADD(YEAR, YEAR(@startdate)-1901, '19000101'),
AND col < DATEADD(YEAR, YEAR(@startdate)-1900, '19000101');
To sum up:
DECLARE @startdate date = '20170101',
@enddate date = '20170212';
-- sales from that period this year:
SELECT SUM(some_column) FROM dbo.SomeTable
WHERE some_datetime_column >= @startdate
AND some_datetime_column < DATEADD(DAY, 1, @enddate);
-- sales from same period previous year (again, not accounting for leap special case):
SELECT SUM(some_column) FROM dbo.SomeTable
WHERE some_datetime_column >= DATEADD(YEAR, -1, @startdate)
AND some_datetime_column < DATEADD(YEAR, -1, DATEADD(DAY, 1, @enddate));
-- sales from all of previous year:
SELECT SUM(some_column) FROM dbo.SomeTable
WHERE some_datetime_column >= DATEADD(YEAR, YEAR(@startdate)-1901, '19000101'),
AND some_datetime_column < DATEADD(YEAR, YEAR(@startdate)-1900, '19000101');
Suggested reading:
Best Answer
You can use
OPENQUERYas explained in MSDN to get the information of Linked server:i.e using
OPENQUERY ( linked_server ,'query' )with something like below will give you server name using
sys.dm_exec_connectionsdmv