Post reply

Selecting records from sql2008 express to sql 2008 enterprise ed

  • October 4, 2010 at 6:29 am

    #224703

    Greetings all,

    When I select records from one db and insert into another db using sql server 2005, everything works fine.

    However, when I select records from sql server 2008 express edition to sql server 2008 enterprise edition, I get following error:

    I get a "Cannot find the object xxx because it does not exist or you do not have permissions" error

    I think one reason is because source and destination dbs are on different versions (2k8 express & 2k8 ent)

    and another difference between the 2 is that they are on different ports.

    2k8 express is on 1434 and 2k8 ent is on 1433.

    Can someone please give me an example of how the query should be coded to account for the 2 different ports if possible?

    Thanks a lot in advance.

  • October 4, 2010 at 12:44 pm

    #1231199

    OPENROWSET('SQLOLEDB','uid=sa;pwd=password;Network=WORKGROUP;Address=192.168.1.1,1434;timeout=5', 'SELECT * FROM MyTable')

    is one way to pass a different port number.... see the comma.

    The prefered way though would be to create a linked server entry and define your port info there. then call the linked server with 4 part naming like;

    Select * from [linkerservername].[dbname].[dbo].[MyTable]

  • October 4, 2010 at 11:27 pm

    #1231459

    Hi

    Can you post your's wretten query. You can also use Export wizard.

    Ali
    MCTS SQL Server2k8

  • October 4, 2010 at 11:30 pm

    #1231460

    Geoff A (10/4/2010)

    Select * from [linkerservername].[dbname].[dbo].[MyTable]

    Geoff's above query should work.

    Ali
    MCTS SQL Server2k8

Viewing 4 posts - 1 through 3 (of 3 total)

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