DataTime for linked Server

DataTime for linked Server

I have a SQL server in Country A (Server A) and another Server in Country B (Server B) Server A has Server B as linked server, I run a stored procedure in Server A and getdate()gives me current date/time for Server A, how can I do getdate() for Server B?

The Getdate() on ServerB might not be the same as ServerA and can cause conflicts for time sensitive data/logic.

Generally for applications spanning countries and timezones it is advised to use UTC time getUTCDate() instead of getdate(). Any localized information to the user can be done by converting the UTC time into local timezone.

|||

Ok. Thanks, how can I convert UTC to local time?

|||

you can use any of the date functions - DateAdd, DateDiff etc..

DataTime for linked Server

DataTime for linked Server

I have a SQL server in Country A (Server A) and another Server in Country B (Server B) Server A has Server B as linked server, I run a stored procedure in Server A and getdate()gives me current date/time for Server A, how can I do getdate() for Server B?

Hi Jim.H.

You should be able to do this with openquery - try this:

SELECT

*

FROM

OPENQUERY(yourLinkedServer,'SELECT getdate()')

|||

If you're on sql2k5, you can also do this.

Code Snippet

exec ('select getdate() as [date]') AT linked_server_B

Datasource credentials and linked reports

Hi,

we have a problem with linked reports. We are using the same reports that are run on about 70 different Oracle schemas. The credential information is passed when calling the report. This works fine for reports and reports with subreports. But when linking to another reports, the credential information is lost.

Is the a possibuility to pass the datasource credential information to a linked report?

Thanks in advance for your help

Michael

you could try setting up a credential parameter on the second report, and pass the credentials of the first report through to that report using the parameter...

dataset linked to stored procedure return no data

I created a new dataset for a new report that gets data from a stored
procedure. But when I run the dataset in the Data tab, it only returns the
column names with no data. The stored procedure runs fine in the Query
Analyzer with several returned records. Any help will be appreciated!On Mar 7, 4:14 pm, obnddc <obn...@.discussions.microsoft.com> wrote:
> I created a new dataset for a new report that gets data from a stored
> procedure. But when I run the dataset in the Data tab, it only returns the
> column names with no data. The stored procedure runs fine in the Query
> Analyzer with several returned records. Any help will be appreciated!
Have you verified that the command type of the dataset is set to
stored procedure (instead of text)?
Enrique Martinez
Sr. SQL Server Developer|||Since it is returning all the columns it means it has accessed stored proc,
Just check the datasource using "test connection" if possible recreate the
datasource, more over do a "Refresh". Check for the server you connected
using Query Analyzer and the datasource are same.
Amarnath
"obnddc" wrote:
> I created a new dataset for a new report that gets data from a stored
> procedure. But when I run the dataset in the Data tab, it only returns the
> column names with no data. The stored procedure runs fine in the Query
> Analyzer with several returned records. Any help will be appreciated!|||Thanks for the input, Emartinez and Amarnath!
I found out that the problem lay in the data itself. I have an input
parameter used in the where condition, like "WHERE tbl_name.customerID LIKE
@.custID"
The test data I used has several spaces after cutomerID charactors. It is
interesting to see the LIKE statement will ignore the spaces in Query
Analyzer but fail in SQL reporting services.
I tried to LTRIM and RTRIM customerID, or use @.custID+'%' but none worked.
Anyone here can help? Thanks.|||Finally I found out, it's not the spaces but the parameters.
I have begin date and end date as input parameters, and end date is
optional. When it is null, getdate(). In Query Analyzer, I left it empty, it
returned records. In SQL report, I have to enter an end date and the date I
entered happens to be the only date that has records. So no record met the
dates, no record reurned.
What I learned:
Before you conclude same process ran differently in different envirements,
make sure they ran under EXACTLY same conditions!
Thanks to all!

DATABASEPROPERTYEX linked server

It is possible to grap a database property from a database on a linked server? Like this?
Select DATABASEPROPERTYEX('servername.databasename','Reco very')
Thanks!
TommyI Tried a variety of ways, and it doesn't look like it...

Select DATABASEPROPERTYEX('QA.dbo.Northwind','Recovery')|||Same results, here, I just get null. I'm going to try to look at the sysdatabases.status field. I just need to get the recovery model, and see if the database is online or not.

Thanks

Tommy|||Why not create a sproc on each (master) db and do a remotr sproc call? That should work...|||..know what?

If you could do that then you would easily have the ability to know...they shouldn't change strategies at all...

If you're the dba you should know...

You should have an inventory of everything...are these out of your control, and what are you trying to accomplish? (inventory automation?)|||Brett:

Thanks for your help. I'm an ISP, and my users have the ability to set their db's the simple. I run my own log shipping scripts. When I backup the logs on the source, it's easy to check the status, and NOT back up logs if it's set to simple. The restorelog runs on the failover server, and I have to tell my script to not try to restore if the original db is set to simple. Other wise, my job shows as failed, even it it fails on one database that is set to simple.

Tommy|||Are these physical servers or instances of sql 2k?

In either case you have to build both, just store a sproc in master, and open a cursor to find the dbs with

select * from sysdatabases