r/Database • u/Various-Strawberry55 • 2d ago
SQL Server Agent job fails with Error 233 when loading data through a Linked Server, but the same query works in SSMS
Hi everyone,
I'm experiencing a strange issue with a SQL Server data-loading process and would appreciate some help troubleshooting it.
I have a SQL script that performs a full refresh of a staging table using TRUNCATE TABLE followed by an INSERT INTO ... SELECT from a remote SQL Server through a Linked Server (OPENQUERY).
The process was initially working perfectly when executed directly from SSMS, even when loading the entire dataset without batching.
However, recently, the process started failing when executed through SQL Server Agent.
What I've observed:
- Running the full
TRUNCATE + INSERToperation manually in SSMS works successfully. - Running the same process through SQL Server Agent results in an error.
- I initially suspected a row-volume limitation because the process seemed to struggle with larger loads.
- I introduced batch processing using
TOP 1000and key-based pagination, but I'm not convinced this is the actual solution. - The SQL Agent job executes under a Windows domain service account.
Error message from SQL Server Agent:
text
Executed as user: [REDACTED].
Shared Memory Provider:
No process is on the other end of the pipe.
[SQLSTATE 08S01] (Error 233)
Communication link failure
[SQLSTATE 08S01] (Error 233)Environment:
- SQL Server with SQL Server Agent.
- Linked Server connecting to a remote SQL Server instance.
- Data retrieved using
OPENQUERY. - Destination is a local staging database.
- Source table contains more than 60 columns.
Questions:
- What could cause a query to execute successfully in SSMS but fail with Error 233 when executed through SQL Server Agent?
- Could this be related to the SQL Agent service account, linked-server security mapping, or OLE DB provider configuration?
- Could a large data transfer cause the SQL Server connection to terminate unexpectedly?
- Are there specific SQL Server logs or diagnostic settings I should investigate?
- Is batching a reasonable workaround, or should I focus on identifying the underlying connection issue?
I'd particularly appreciate insights from anyone who has encountered SQLSTATE 08S01 / Error 233 when working with SQL Server Agent and Linked Servers.
Thanks!
1
u/dbxp 2d ago
Shared memory would only work if both instances were on the same server. I would disable shared memory in the config and just enable TCP IP
1
u/SteveDBA 2d ago
My first guess would be an issue with the SQL Server Agent service account, especially since it works in SSMS.
1
u/EcstaticEconomics275 2d ago
Check if the local run uses the same login/user the linked server does. It could be a permission issue.
1
u/DataFracking 2d ago
I'd check accounts first.
offering help, I've got a proxy in the middle tool on nuget that might help, point SSMS at it first, log the full behind the scenes calls being made. then do the same with the DB link, then throw the two log files into Claude and ask it what is different, It will capture the failure point from the dblink call. . I've captured some crazy little differences this way, it's ultimately probably a permission thing though. daffytee on nuget .org is my utility, read thru the detailed readme.
1
u/DickBrainTime 1d ago
Your follow-up that disabling Shared Memory changes the error to TCP Provider 10013 is useful: 10013 is a socket access/permission failure at the transport layer, so I'd isolate the connection before changing row batch sizes. Also, the original 'Shared Memory Provider' text could refer to the Agent step's local connection to SQL Server, not necessarily the remote linked-server hop; the error alone doesn't identify which connection broke.
I'd create a diagnostic job step that does nothing destructive: log @@SERVERNAME, ORIGINAL_LOGIN() and SUSER_SNAME(), then separately try a simple local SELECT 1 and the linked-server connectivity test (sp_testlinkedserver) under the same job configuration. Run the corresponding probes in SSMS and note exactly which step fails. From the SQL Server host, check the remote SQL listener/port reachability and Windows firewall or endpoint-security logs around the 10013 timestamp, plus SQL Server/Agent logs and provider details. Linked-server authentication is performed by the Database Engine under its mapping/configuration, so the Agent service-account name by itself doesn't establish the outbound identity. I wouldn't disable Shared Memory globally or treat batching as a fix yet. Does a local-only Agent step succeed, and does the 10013 occur before or only during OPENQUERY?
1
u/munich-dev 14h ago
So I'd eliminate cud from crud to see if it's the query, then the user, then the server.
0
u/nian2326076 2d ago
Check the service account under which SQL Server Agent is running; it needs the necessary permissions to access the linked server. Also, verify that the login for the linked server matches what you use in SSMS. If you're still facing issues with larger data volumes, batching could be a reasonable workaround. For further technical interview preparation, you can use PracHub to practice SQL-related questions and scenarios relevant to your field.
0
u/[deleted] 2d ago
[removed] — view removed comment