Hi All ,
I have 3 sql servers located at different time zones. Say, CST,PST,EST.
Now how can I get current time at EST,PST from the SQL server located
at CST? Is there any query to do that?
I have a stored proc located in SQL server at CST zone where I need to
query for the current date/time of the other zone sql servers.
I tried below query at CST SQL server
SELECT TOP 1 GETDATE() FROM [SERVER-PST].master.dbo.syslocks
But it always gives CST datetime.
Please reply...
Thanks
RP
Haven't tested but it should work using Openquery instead of
the 4 part name. The statement passed in the openquery is
executed on the remote server.
-Sue
On Fri, 27 Apr 2007 12:24:01 -0700, Ram
<Ram@.discussions.microsoft.com> wrote:
>Hi All ,
>I have 3 sql servers located at different time zones. Say, CST,PST,EST.
>Now how can I get current time at EST,PST from the SQL server located
>at CST? Is there any query to do that?
>I have a stored proc located in SQL server at CST zone where I need to
>query for the current date/time of the other zone sql servers.
>I tried below query at CST SQL server
>SELECT TOP 1 GETDATE() FROM [SERVER-PST].master.dbo.syslocks
>But it always gives CST datetime.
>Please reply...
>Thanks
>RP
|||Hi Sue,
It works gr8...
Thanks for the help...
"Sue Hoegemeier" wrote:
> Haven't tested but it should work using Openquery instead of
> the 4 part name. The statement passed in the openquery is
> executed on the remote server.
> -Sue
> On Fri, 27 Apr 2007 12:24:01 -0700, Ram
> <Ram@.discussions.microsoft.com> wrote:
>
>
|||Hi,
Can you please write the exact syntax you used for Openquery? I also have
requirement similar to this.
Thanks for your help.
Namwar
"Ram" wrote:
[vbcol=seagreen]
> Hi Sue,
> It works gr8...
> Thanks for the help...
>
> "Sue Hoegemeier" wrote:
Showing posts with label remote. Show all posts
Showing posts with label remote. Show all posts
Friday, March 9, 2012
How to get remote query timeout value
Hi,
We can use
EXEC sp_configure 'remote query timeout', 6000
to set the remote query timeout value.
How can we get the value remote query timeout ?> How can we get the value remote query timeout ?
You can execute sp_configure without the @.configvalue parameter to show the
existing configuration value:
EXEC sp_configure 'remote query timeout'
If you need to retrieve the value into a variable using Transact-SQL, here's
one method:
IF OBJECT_ID('tempdb..#ConfigValues') IS NOT NULL
DROP TABLE #ConfigValues
DECLARE @.run_value int
CREATE TABLE #ConfigValues
(
name varchar (35) NOT NULL,
minimum int,
maximum int,
config_value int,
run_value int
)
INSERT INTO #ConfigValues
EXEC sp_configure 'remote query timeout'
SELECT @.run_value = run_value FROM #ConfigValues
DROP TABLE #ConfigValues
Hope this helps.
Dan Guzman
SQL Server MVP
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:OpBzNAOyGHA.4548@.TK2MSFTNGP05.phx.gbl...
> Hi,
> We can use
> EXEC sp_configure 'remote query timeout', 6000
> to set the remote query timeout value.
> How can we get the value remote query timeout ?
>
We can use
EXEC sp_configure 'remote query timeout', 6000
to set the remote query timeout value.
How can we get the value remote query timeout ?> How can we get the value remote query timeout ?
You can execute sp_configure without the @.configvalue parameter to show the
existing configuration value:
EXEC sp_configure 'remote query timeout'
If you need to retrieve the value into a variable using Transact-SQL, here's
one method:
IF OBJECT_ID('tempdb..#ConfigValues') IS NOT NULL
DROP TABLE #ConfigValues
DECLARE @.run_value int
CREATE TABLE #ConfigValues
(
name varchar (35) NOT NULL,
minimum int,
maximum int,
config_value int,
run_value int
)
INSERT INTO #ConfigValues
EXEC sp_configure 'remote query timeout'
SELECT @.run_value = run_value FROM #ConfigValues
DROP TABLE #ConfigValues
Hope this helps.
Dan Guzman
SQL Server MVP
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:OpBzNAOyGHA.4548@.TK2MSFTNGP05.phx.gbl...
> Hi,
> We can use
> EXEC sp_configure 'remote query timeout', 6000
> to set the remote query timeout value.
> How can we get the value remote query timeout ?
>
How to get remote query timeout value
Hi,
We can use
EXEC sp_configure 'remote query timeout', 6000
to set the remote query timeout value.
How can we get the value remote query timeout ?> How can we get the value remote query timeout ?
You can execute sp_configure without the @.configvalue parameter to show the
existing configuration value:
EXEC sp_configure 'remote query timeout'
If you need to retrieve the value into a variable using Transact-SQL, here's
one method:
IF OBJECT_ID('tempdb..#ConfigValues') IS NOT NULL
DROP TABLE #ConfigValues
DECLARE @.run_value int
CREATE TABLE #ConfigValues
(
name varchar (35) NOT NULL,
minimum int,
maximum int,
config_value int,
run_value int
)
INSERT INTO #ConfigValues
EXEC sp_configure 'remote query timeout'
SELECT @.run_value = run_value FROM #ConfigValues
DROP TABLE #ConfigValues
--
Hope this helps.
Dan Guzman
SQL Server MVP
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:OpBzNAOyGHA.4548@.TK2MSFTNGP05.phx.gbl...
> Hi,
> We can use
> EXEC sp_configure 'remote query timeout', 6000
> to set the remote query timeout value.
> How can we get the value remote query timeout ?
>
We can use
EXEC sp_configure 'remote query timeout', 6000
to set the remote query timeout value.
How can we get the value remote query timeout ?> How can we get the value remote query timeout ?
You can execute sp_configure without the @.configvalue parameter to show the
existing configuration value:
EXEC sp_configure 'remote query timeout'
If you need to retrieve the value into a variable using Transact-SQL, here's
one method:
IF OBJECT_ID('tempdb..#ConfigValues') IS NOT NULL
DROP TABLE #ConfigValues
DECLARE @.run_value int
CREATE TABLE #ConfigValues
(
name varchar (35) NOT NULL,
minimum int,
maximum int,
config_value int,
run_value int
)
INSERT INTO #ConfigValues
EXEC sp_configure 'remote query timeout'
SELECT @.run_value = run_value FROM #ConfigValues
DROP TABLE #ConfigValues
--
Hope this helps.
Dan Guzman
SQL Server MVP
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:OpBzNAOyGHA.4548@.TK2MSFTNGP05.phx.gbl...
> Hi,
> We can use
> EXEC sp_configure 'remote query timeout', 6000
> to set the remote query timeout value.
> How can we get the value remote query timeout ?
>
How to get Remote Backup
Is there any way that i can get backup from other(Remote) location on LAN
Thanks
You can use Transact-SQL to do this using UNC naming, like this:
RESTORE DATABASE [my_database] FROM DISK = N'\\LanServer\MSSQL\BACKUP\my_database_backup.bak' WITH FILE = 1, NOUNLOAD, STATS = 10
GO
Could you write me source code
Thanks
|||Hi,
you already did this in your other post:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1194939&SiteID=1
BackupDeviceItem bkpDevice = new BackupDeviceItem(openBackupDialog.FileName, DeviceType.File);
Replace the openBackupDialog.FileName with the UNC name.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
Sir i can use SMO on computer where sql server installed, but when i access sqlserver from other computer (from my client application which i have developed using C#) then i get an exception.
Thanks
|||Which one do you get ? if you want to deploy the application with SMO functionality you will have to package the SMO classes as well.
http://www.microsoft.com/downloads/details.aspx?FamilyID=7A9AD90F-7F95-4369-A206-E84053D63FD3&
HTH, jens K. Suessmeyer.
http://www.sqlserver2005.de|||
when i created setup by default all sqlserver smo dlls were added with setup, and when i installed on other computer following dll were also isntalled with it
Microsoft.SqlServer.BatchParser.dll
Microsoft.SqlServer.ConnectionInfo.dll
Microsoft.SqlServer.RegSvrEnum.dll
Microsoft.SqlServer.Replication.dll
Microsoft.SqlServer.Rmo.dll
Microsoft.SqlServer.ServiceBrokerEnum.dll
Microsoft.SqlServer.Smo.dll
Microsoft.SqlServer.SmoEnum.dll
Microsoft.SqlServer.SqlEnum.dll
Microsoft.SqlServer.WmiEnum.dll
is still i am missing some redistributable
Thanks
|||Which error do you get ?
Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||************** Exception Text **************Microsoft.SqlServer.Management.Smo.FailedOperationException: Backup failed for Server 'xyz,1433'. > Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. > System.Data.SqlClient.SqlException: Cannot open backup device 'C:\Program Files\abcd\Setup1\Backup\asdasd.bak'. Operating system error 3(The system cannot find the path specified.).
BACKUP DATABASE is terminating abnormally.|||YOu will have to aware that the backup file has to be reachable from the relative location of the server NOT the client. If you want to use a backup file on the client you will either have to copy it first to a server-reachable destination OR define the backup path to the client share.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
How to get Remote Backup
Is there any way that i can get backup from other(Remote) location on LAN
Thanks
You can use Transact-SQL to do this using UNC naming, like this:
RESTORE DATABASE [my_database] FROM DISK = N'\\LanServer\MSSQL\BACKUP\my_database_backup.bak' WITH FILE = 1, NOUNLOAD, STATS = 10
GO
Could you write me source code
Thanks
|||Hi,
you already did this in your other post:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1194939&SiteID=1
BackupDeviceItem bkpDevice = new BackupDeviceItem(openBackupDialog.FileName, DeviceType.File);
Replace the openBackupDialog.FileName with the UNC name.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
Sir i can use SMO on computer where sql server installed, but when i access sqlserver from other computer (from my client application which i have developed using C#) then i get an exception.
Thanks
|||Which one do you get ? if you want to deploy the application with SMO functionality you will have to package the SMO classes as well.
http://www.microsoft.com/downloads/details.aspx?FamilyID=7A9AD90F-7F95-4369-A206-E84053D63FD3&
HTH, jens K. Suessmeyer.
http://www.sqlserver2005.de|||
when i created setup by default all sqlserver smo dlls were added with setup, and when i installed on other computer following dll were also isntalled with it
Microsoft.SqlServer.BatchParser.dll
Microsoft.SqlServer.ConnectionInfo.dll
Microsoft.SqlServer.RegSvrEnum.dll
Microsoft.SqlServer.Replication.dll
Microsoft.SqlServer.Rmo.dll
Microsoft.SqlServer.ServiceBrokerEnum.dll
Microsoft.SqlServer.Smo.dll
Microsoft.SqlServer.SmoEnum.dll
Microsoft.SqlServer.SqlEnum.dll
Microsoft.SqlServer.WmiEnum.dll
is still i am missing some redistributable
Thanks
|||Which error do you get ?
Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||************** Exception Text **************Microsoft.SqlServer.Management.Smo.FailedOperationException: Backup failed for Server 'xyz,1433'. > Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. > System.Data.SqlClient.SqlException: Cannot open backup device 'C:\Program Files\abcd\Setup1\Backup\asdasd.bak'. Operating system error 3(The system cannot find the path specified.).
BACKUP DATABASE is terminating abnormally.|||YOu will have to aware that the backup file has to be reachable from the relative location of the server NOT the client. If you want to use a backup file on the client you will either have to copy it first to a server-reachable destination OR define the backup path to the client share.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
Subscribe to:
Posts (Atom)