• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 327
  • Last Modified:

the I in SSIS

I am working between two databases on the same server. I am using SSIS to move data between the two databases -simple example... I am copying a table from Production to Development.
Is there a way from a -Query in SQL server Management Studio -to do a CREATE, DROP, SELECT, UPDATE, INSERT DELETE between databases on the same Server WITHOUT using SSIS?
0
dmlyo150
Asked:
dmlyo150
2 Solutions
 
winrarCommented:
Sorry,

But there are no current available methods to execute those functions without the use of SSIS.

If you need any further help using it please ask.
0
 
EmesCommented:
Yes there is an  easy  way using linked server

0
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
You have made the comment :

"Is there a way from a -Query in SQL server Management Studio -to do a CREATE, DROP, SELECT, UPDATE, INSERT DELETE between databases on the same Server WITHOUT using SSIS?"

In that question you say "on the same server".

If that is the intention, then yes you can do it between databases on the same server, you just reference the fully qualified name:

DATABASE.dbo.Tablename
DATABASE.dbo.StoredProcedureName
etc.

Just specify the DATABASE name in the CREATE, DROP, SELECT, UPDATE, INSERT and DELETE statements.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
dmlyo150Author Commented:
thanks -dbabuck -but Emes' comment brings me to another point of inquiry. I looked around the Internet for sp_addlinkedserver and found how I can query between -not just databases on the same server -but between databases on different servers. however, I am having trouble with the code. can you help me?
my server name is LALFAPRD (Production)
with a Database Named (SQLLERIP)
and my other server name is LALFADEV (Development)
with a Database Named (SQLLERID)
if I am working in Development and I want to SELECT from a table in Production, can you help me with the following:

sp_addlinkedserver
sp_addlinkedsrvlogin
...and the other sp_...linked... stored procedures that would help me design the ultimate state of the are SQL Server data source.
thanks. -
davlyo
0
 
EmesCommented:
EXEC sp_addlinkedserver
@server='Linked', -- local SQL name given to the linked server
@provider='sqlncli', -- OLE DB provider (the .2 means the SQL2K version)
@datasrc='LALFAPRD', -- analysis server name (machine name)
@catalog='SQLLERID', -- default catalog/database
@srvproduct='' -- not used but needed here

set this call from your dev sql server
0
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
Aside from that you can do this, I would not necessarily recommend setting up that kind of relationship as Development servers having access to production is a very nice security hole.

But Emes has provided you with the script to help you get this done if that is the direction that you want to go in.
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now