Call getdate from linked server

Question

_{Monday, May 12, 2008 9:34 PM}

Hello,

i need get value of getdate function on local and oon linked server by one select something like this:

select

getdate() 'locladate'
, [SERVER2\SQL05_01, 9999].getdate() 'remotedate'

first problem i dont know in which database is getdate stored, second is it possible call function from linked server in select statement?

Thanks Jakub.

All replies (5)

_{Monday, May 12, 2008 10:09 PM ✅Answered | 1 vote}

We do not support using multi-part names for system functions (few exceptions like $partition which is different). Similarly, you cannot use 4-part names to call UDFs or system functions. So you will have to use pass-through query to get the remote timestamp. Below are two methods:

Code Snippet

select CURRENT_TIMESTAMP as local_time, rmt_time
  from OPENQUERY([remote_server], 'select CURRENT_TIMESTAMP as rmt_time') as t;

declare @rmt_time datetime;
exec('SET ? = CURRENT_TIMESTAMP', @rmt_time OUTPUT) at [remote_server];
select CURRENT_TIMESTAMP as local_time, @rmt_time as rmt_time;

_{Monday, May 12, 2008 10:04 PM | 1 vote}

You have to use open query to do this.  I believe that if you use getdate() in a linked server it will process the current datetime from the local server, not the remote server.

Try something like this:

Code Snippet

select RemoteServerTime, getdate() as [CurrentServerTime]

from OPENQUERY ([MyLinkedServerName] ,'select getdate() as RemoteServerTime' )

_{Monday, May 12, 2008 10:23 PM | 1 vote}

How bout this:

On the remote server, create a view like this:

Code Snippet

Use Master

create view vw_CurrentDate

as select getdate() as [CurrentLocalDate]

Then, any time you want, you can use:

Code Snippet

select * from [RemoteServer].[Master].[dbo].[vw_CurrentDate]

This provides the functionality at near-zero cost.

_{Tuesday, May 13, 2008 6:02 AM}

Hello,

You can execute pass-through queries targeting linked servers in the following form:

Code Snippet

EXEC ('SELECT GetDate()') AT [LinkedServerName]

Eralper

MS SQL Server articles and tips-tricks

_{Tuesday, May 13, 2008 7:00 PM}

Note that this requires creating the view, providing appropriate SELECT permission on the view to remote logins etc. A simple pass-through query is preferable since you don't need to do anything special on the remote server.