none
DBA

    Question

  • HI,

    How can i export tables from the linked servers using  sql server  mgmt studio?

    Thanks

    Ganga


    • Edited by grao1979 Saturday, July 19, 2014 5:00 PM
    Saturday, July 19, 2014 4:59 PM

Answers


  • So, your intention is to provide database dump to the external consultants.

    It would easier if you can do full database backup and give it your consultants.

    Do you have access to linked server directly ? do you have permissions to do full backup. If you do not have permissions, I would advice to check with team/manager and have someone to do it.

    You can script or use copy database wizard to get DDL and data into a single file but you have to see how big the database is, if it is fairly big, it is hard to do that in a single file and for sure, will be a pain for your consultants to use that.

    I feel, giving them a backup is the easiest and best solution here since you want to give them both data and DDL for all tables and consultants can use it by restoring it. if you want to customize like select few tables, you can use copy database wizard and script the ddl and data. 


    Hope it Helps!!




    Sunday, July 20, 2014 2:13 AM

All replies

  • I assume you do not have access to the linked server directly, so,you should be able to do 

    Select * into NewTable from LinkedServer.Dbname.schemaname.Tablename

    But the new table definition may not be exact to the actual one and constraint will not be created.



    Hope it Helps!!



    Saturday, July 19, 2014 5:03 PM
  • Hi,

    Can i export all tables  from the linked server into one file?

    Thanks

    Ganga

    Saturday, July 19, 2014 5:08 PM
  • are you looking to get script the table definitions?? or data as well

    do you have access to linked server directly?


    Hope it Helps!!

    Saturday, July 19, 2014 5:15 PM
  • If you do not have access to the linked server directly and looking only for the table definition,you can do some like this, but the problem, is table defintions might not be exact and constraints will not be created.

    Select 'Select top 1 * into #'+Name+'  from linkedServer.DBName.dbo.'+ Name from sysobjects where type ='U'

    if you run the above the script, it will create all the linked server DB tables on your source with one row. you will have to truncate the tables. 

    please explain clearly what you are trying to do and the need and there may be better solution.


    Hope it Helps!!

    Saturday, July 19, 2014 5:26 PM
  • HI,

    How can i export tables from the linked servers using  sql server  mgmt studio?

    Thanks

    Ganga


    If data size is big I advise you to use Import export wizard which is much easy and does almost all data conversions if required. Linked server has lot of limitations.


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    My TechNet Wiki Articles

    Saturday, July 19, 2014 5:34 PM
  • HI,

    Thank you for your reply!  Let me explain to you ..............I have MaxHire  external server  as linked server in the sql mgmt studio where i can do all the DLL's ...........My question is i want to export all the tables structure and data,views into one file and give it to external consultants or the whole database dump...below are the tables and views  as example

    dbo.activity

    dbo.activitylocations

    dbo.activitytypes

    dbo.activity

    Views  like 

    dbo.contacts

    dbo.placements

    dbo.VM_AI_Activity

    dbo_VM_AI_AICCandidateInformation

    Thanks

    Ganga

    Saturday, July 19, 2014 11:33 PM

  • So, your intention is to provide database dump to the external consultants.

    It would easier if you can do full database backup and give it your consultants.

    Do you have access to linked server directly ? do you have permissions to do full backup. If you do not have permissions, I would advice to check with team/manager and have someone to do it.

    You can script or use copy database wizard to get DDL and data into a single file but you have to see how big the database is, if it is fairly big, it is hard to do that in a single file and for sure, will be a pain for your consultants to use that.

    I feel, giving them a backup is the easiest and best solution here since you want to give them both data and DDL for all tables and consultants can use it by restoring it. if you want to customize like select few tables, you can use copy database wizard and script the ddl and data. 


    Hope it Helps!!




    Sunday, July 20, 2014 2:13 AM