Post reply

How to create the table in a different server from a given sql query

  • July 16, 2008 at 3:33 am

    #70963

    Hi All,

    I need to create the SQL table in a different server using given SQL query.

    I'm using the following statements but its giving error:

    Select * into X.Y.dbo.table1

    from select * from table2 aa

    Error is as follows:

    The object name 'X.Y.dbo.' contains more than the maximum number of prefixes. The maximum is 2.

    From a different server I'm trying to create the table in server X, database Y.

    Any ideas?

    Thanks in advance

    Shahtech

  • July 16, 2008 at 3:50 am

    #843637

    Create a linked server. That should solve your problem

  • July 16, 2008 at 3:55 am

    #843640

    Go to Enterprise manager, under the Security folder go to Linked servers. Right click, add new linked server. Choose the server that you want. Then you will be able to access it the way you want to.

  • July 16, 2008 at 4:56 am

    #843673

    Thanks for quick response,

    the server is added to linked servers list already, but giving the error.

    any more ideas?

  • July 16, 2008 at 5:12 am

    #843680

    I am actually doing the same as you want to do. All I did was created the linked server, and my code is :

    INSERT INTO Sysprocesses_local

    SELECT * FROM ExternalServer01.master.dbo.sysprocesses

    No problem. I did however have a problem without ExternalServer01 being linked.

  • July 16, 2008 at 7:56 pm

    #844111

    Where do we find the enterprise manager?

  • July 17, 2008 at 12:13 am

    #844160

    Did you try executing a statement like this one?

    INSERT INTO X.Y.dbo.table1

    select * from table2

    wherein you are in the server where table2 resides

    "-=Still Learning=-"

    Lester Policarpio

  • July 22, 2008 at 5:34 am

    #846293

    Hi,

    Did You try with this query...it may be useful...

    EXEC ('Select * into Y.dbo.table1

    from select * from table2 aa') at X

  • July 22, 2008 at 5:53 am

    #846313

    Thanks for response,

    but getting same error as follows:

    The object name '..." contains more than the maximum number of prefixes. The maximum is 2.

    Any more ideas?

  • July 22, 2008 at 6:00 am

    #846326

    Please use this query... rabdul

    EXEC ('Select * into Y.dbo.table1

    from ddw.dbo.table2 aa') at X

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply