locked
Pass through query for the below query ? RRS feed

  • Question

  • Hi all,

                     Please help me in writing a pass through query for the below query :

    UPDATE MS   SET MS.IsGeoTag=MS1.IsGeoTag,MS.IslevelNamed=MS1.IslevelNamed,MS.Level1Name=MS1.Level1Name,
    MS.Level2Name=MS1.Level2Name,MS.Level3Name=MS1.Level3Name,MS.Level4Name=MS1.Level4Name,MS.geopoint=MS1.geopoint,
    MS.GeoUpdateDate=MS1.GeoUpdateDate,MS.LevelNameUpdateDate=MS1.LevelNameUpdateDate,MS.GeoAccur=MS1.GeoAccur,
    MS.GeoLati=MS1.GeoLati,MS.GeoLong=MS1.GeoLong,MS.Batch_Name=MS1.Batch_Name
       FROM Mapsupplier MS
    		INNER JOIN [10.12.11.15\Posqa].Elektra.dbo.Mapsupplier MS1
    		ON MS.Supplier_id=MS1.Supplier_id

    Any help would be appreciated

    Thanks and Regards

    Chaithanya M


    Regards Chaithu.. If it is usefull,Mark this as Answer.

    Thursday, September 20, 2012 10:27 AM

Answers

  • UPDATE MS   SET MS.IsGeoTag=MS1.IsGeoTag,MS.IslevelNamed=MS1.IslevelNamed,MS.Level1Name=MS1.Level1Name,
    MS.Level2Name=MS1.Level2Name,MS.Level3Name=MS1.Level3Name,MS.Level4Name=MS1.Level4Name,MS.geopoint=MS1.geopoint,
    MS.GeoUpdateDate=MS1.GeoUpdateDate,MS.LevelNameUpdateDate=MS1.LevelNameUpdateDate,MS.GeoAccur=MS1.GeoAccur,
    MS.GeoLati=MS1.GeoLati,MS.GeoLong=MS1.GeoLong,MS.Batch_Name=MS1.Batch_Name
       FROM Mapsupplier MS
    		INNER JOIN [10.12.11.15\Posqa].Elektra.dbo.Mapsupplier MS1
    		ON MS.Supplier_id=MS1.Supplier_id

    with cte

    as

    (

    select MS.IsGeoTag AS Target_IsGeoTag,MS1.IsGeoTag=Source_IsGeoTag

      *fromOPENQUERY([10.12.11.15\Posqa], 'Elektra.dbo.Mapsupplier') MS1 JOIN Mapsupplier MS

    ON MS.Supplier_id=MS1.Supplier_id

    ) update cte set Target_IsGeoTag=Source_IsGeoTag




    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    MS SQL Consultants: Improves MS SQL Database Performance


    Thursday, September 20, 2012 11:16 AM
    Answerer

All replies

  • Do you get an error?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    MS SQL Consultants: Improves MS SQL Database Performance

    Thursday, September 20, 2012 10:41 AM
    Answerer
  • Hi  Uri,

                     Yes i got and error, saying the below :

    Msg 7325, Level 16, State 1, Line 1
    Objects exposing columns with CLR types are not allowed in distributed queries. Please use a pass-through query to access remote object '"Elektra"."dbo"."Mapsupplier"'.

    Please help me in re-writing the query .

    Thanks

    Chaithanya M


    Regards Chaithu.. If it is usefull,Mark this as Answer.

    Thursday, September 20, 2012 10:47 AM
  • is [10.12.11.15\Posqa] a linked server? if nto you may need to create one.

    http://geographika.co.uk/copying-spatial-data-between-sql-server-databases

    regards

    joon


    • Edited by Joon84 Thursday, September 20, 2012 11:04 AM
    Thursday, September 20, 2012 11:04 AM
  • UPDATE MS   SET MS.IsGeoTag=MS1.IsGeoTag,MS.IslevelNamed=MS1.IslevelNamed,MS.Level1Name=MS1.Level1Name,
    MS.Level2Name=MS1.Level2Name,MS.Level3Name=MS1.Level3Name,MS.Level4Name=MS1.Level4Name,MS.geopoint=MS1.geopoint,
    MS.GeoUpdateDate=MS1.GeoUpdateDate,MS.LevelNameUpdateDate=MS1.LevelNameUpdateDate,MS.GeoAccur=MS1.GeoAccur,
    MS.GeoLati=MS1.GeoLati,MS.GeoLong=MS1.GeoLong,MS.Batch_Name=MS1.Batch_Name
       FROM Mapsupplier MS
    		INNER JOIN [10.12.11.15\Posqa].Elektra.dbo.Mapsupplier MS1
    		ON MS.Supplier_id=MS1.Supplier_id

    with cte

    as

    (

    select MS.IsGeoTag AS Target_IsGeoTag,MS1.IsGeoTag=Source_IsGeoTag

      *fromOPENQUERY([10.12.11.15\Posqa], 'Elektra.dbo.Mapsupplier') MS1 JOIN Mapsupplier MS

    ON MS.Supplier_id=MS1.Supplier_id

    ) update cte set Target_IsGeoTag=Source_IsGeoTag




    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    MS SQL Consultants: Improves MS SQL Database Performance


    Thursday, September 20, 2012 11:16 AM
    Answerer