July 16, 2013 at 5:50 am
Hi Team,
Am having two datbases in one instance,
whenever i execute any scripts, i'll use "USE DATABASE" and execute the scripts,
Is there any way to mention instance name, instead of Database name.
Eg:
USE INSTANCE
GO
USE DATABASE
GO
EXEC Store_spoc..
Please Suggest...
July 16, 2013 at 6:04 am
Hi,
If you have the one instance as a linked server, you can reference it from the other instance by:-
[Server Name].[Database Name].[Schema Name].[Object Name]
Andrew
July 16, 2013 at 6:23 am
You could open a query window in SQLCMD mode and then use the following syntax
:CONNECT Server\InstanceName
USE DATABASE
GO
EXEC STORED PROC...
[font="Verdana"]Markus Bohse[/font]
July 16, 2013 at 9:08 pm
Minnu (7/16/2013)
Hi Team,Am having two datbases in one instance,
whenever i execute any scripts, i'll use "USE DATABASE" and execute the scripts,
Is there any way to mention instance name, instead of Database name.
Eg:
USE INSTANCE
GO
USE DATABASE
GO
EXEC Store_spoc..
Please Suggest...
This script should do it:
-- this sets up a linked server (or you can right-click "Server Objects|Linked Servers"
-- and set up the link manually using the wizard)
USE [YOUR_DATABASE]
GO
EXEC master.dbo.sp_addlinkedserver
@server = N'[LINKED_SERVER_NAME]'
,@srvproduct = N'SQL Server'
/* For security reasons the linked server remote logins password is changed with ######## */
GO
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = N'[LINKED_SERVER_NAME]'
,@useself = N'False'
,@locallogin = NULL
,@rmtuser = N'[USER_NAME]'
,@rmtpassword = '[PASSWORD]'
GO
-- Once you have the server link set up then you can connect to it
-- just like any other object but you must provide a fully qualified reference
SELECT
*
FROM
[YOUR_DATABASE].[DBO].[YOUR_TABLE] AS a
INNER JOIN
[LINKED_SERVER_NAME].[LINKED_DB_NAME].[DBO].[LINKED_TABLE] AS b
ON a.ID = b.ID
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply