Results 1 to 4 of 4
  1. #1
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6

    Unanswered: Issues copying data to linked server

    I'm having an issue with a job that calls a stored procedure. The SP is fairly complicated, but basically creates a temp table, then inserts the records from the temp table into a table on a linked server (via VPN). The basic premise here is that I'm trying to update a web-accessible server from the production server. The funny thing is that the process works correctly for 4 out of 5 companies (company is the only parameter fed to the SP). The company that fails is by far the largest, but the process has worked in the past.

    I'm not strong with all the tools available in SQL Server that might help me figure out what's going wrong. In the SQL Server Agent, it simply tells me that the job failed. If I use Query Analyzer and run the SP in Debug mode (if I'm stating that correctly), I get this error for any company I try it for, even companies that run fine (line 51 is the DELETE line):

    Server: Msg 7391, Level 16, State 1, Procedure procStmtDataToReno, Line 51
    [Microsoft][ODBC SQL Server Driver][SQL Server]The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.


    So I suppose my long-winded question is, how can I determine why this SP fails for the one company? If relevant, the job is simply:

    EXEC procStmtDataToReno 'B'

    and some snippets of the SP:

    Code:
    CREATE  PROCEDURE [dbo].[procStmtDataToReno] 
    
    @CoCode as varchar(2)
    
    AS
    
    --various declarations and such
    
    DELETE
    FROM       [10.1.1.4].OnlineAR.dbo.StatementData
    WHERE CompanyID = @CoCode AND (Line1Date < Convert(varchar(10), GETDATE() - 60, 101) OR Line1Date >= Convert(varchar(10), GETDATE() - 5, 101) )
    
    --code to build temp table
    
    INSERT INTO  [10.1.1.4].OnlineAR.dbo.StatementData
    SELECT * FROM #Statement
    GO
    TIA
    Paul

  2. #2
    Join Date
    May 2002
    Posts
    299
    I normally would create a stored procedure on the remote server side and call it via openquery() or sp_executesql. That is the *fastest* way to do and DML.

    Anyway, I suggest you take a look at the following article for guidance on resolving 7391 error.

    http://support.microsoft.com/kb/306212
    --
    -oj
    http://www.rac4sql.net

  3. #3
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Thanks for that. Because some companies worked, my suspicion all along was that it was "data" related rather than "process" related. I finally stumbled into the answer late last night. I had tried changing the SP to simply select the records, and it ran fine from Query Analyzer. I changed the SP back to an INSERT and executed it again from QA, and it failed, but I finally got a message that made sense:

    Server: Msg 8152, Level 16, State 4, Procedure procStmtDataToReno, Line 389
    String or binary data would be truncated.
    The statement has been terminated.


    My problem was as simple as tracking down the field that was wider on the “source” side than it was on the “destination” side. The process now works fine. I just didn’t know what tool to use to get better info on what was going wrong.

    I am interested in your recommendation to set it up on the remote side. I would have thought it was better to have the SP on the source side, so it would only have to transfer the results of the process “over the wire”. If the SP were on the destination side, wouldn’t it have to bring all the data across to work with?
    Paul

  4. #4
    Join Date
    May 2002
    Posts
    299
    yes, you have to pass the data to the remote server in order for it to be processed.

    e.g.

    @sql='exec my_sp ' + @inputvar

    exec linked.db..sp_executesql @sql
    --
    -oj
    http://www.rac4sql.net

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •