Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

USE Database Expand / Collapse
Author
Message
Posted Tuesday, July 16, 2013 5:50 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, December 4, 2014 4:34 AM
Points: 233, Visits: 739
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...
Post #1474075
Posted Tuesday, July 16, 2013 6:04 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 2:59 AM
Points: 1,356, Visits: 1,180
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

Post #1474080
Posted Tuesday, July 16, 2013 6:23 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Tuesday, December 9, 2014 5:31 AM
Points: 4,429, Visits: 4,183
You could open a query window in SQLCMD mode and then use the following syntax

:CONNECT Server\InstanceName
USE DATABASE
GO
EXEC STORED PROC...


Markus Bohse
Post #1474092
Posted Tuesday, July 16, 2013 9:08 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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


 
Post #1474356
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse