August 21, 2008 at 11:42 am
I get this error when i run a script that uses a linked server which is defined on the server I am running this query on
Server: Msg 11, Level 16, State 1, Line 7 General network error. Check your network documentation.
The script is something like
Declare @newBatchNum Int
Set @newBatchNum = (SELECT MAX(intBatchNum) + 100
FROM "172.23.24.36".Dest_Prod.dbo.tblTransactionsC
WHERE (lngTransactionCnt > 27000000) AND not intBatchNum is null)
UPDATE transToUpdate
SET transToUpdate.intBatchNum = @newBatchNum
FROM "172.23.24.36".Dest_Prod.dbo.tblTransactionsC transToUpdate
Where transToUpdate.lngTransactionCnt in (
SELECT TransIdentifier
FROM tblNIPRTransactions With (NOLOCK)
WHERE TransId IN(
SELECT TransId -- Results in 128,872 Records
FROM tblNIPRTransactionBatches With (NOLOCK)
WHERE SubmissionDate Between '20080801' and '20080816'
)
)
Does it have something to do with the security for this linked server.The properties for Linked server are attached. Btw its SQL 2000 server on SP4. The server options on the linked server allow Data Access, RPC and RPC out.
Thanks in Advance!!!
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
September 5, 2008 at 11:18 am
If not the exact solution, I would appreciate any views on it..Thanks!!!
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
September 5, 2008 at 12:46 pm
Have you tried breaking the query down into smaller parts to isolate what is giving that exact error? Have you tested the connection to the linked server from SSMS?
September 5, 2008 at 1:09 pm
Try executing a simple select on the linked server.
check, test the linked server on SMS, for something like , probably a wrong password.
September 5, 2008 at 1:12 pm
You can use this system sp as well "sp_testlinkedserver"
September 5, 2008 at 10:39 pm
I tried doing a select from the remote database and it actually works.But when I do an update referencing the linked server it says 'MS OLEDB unable to start Distributed Transaction'. Here I did not use the term Begin Distributed Transaction when I used the script. Also I created a SP for this script involving just the select statements and it works. But not the update, I have checked the security settings which are fine. There could not be an issue of firewalls too because both the servers are in the same DMZ...any ideas?
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
September 6, 2008 at 11:41 am
talentguy123 (9/5/2008)
I tried doing a select from the remote database and it actually works.But when I do an update referencing the linked server it says 'MS OLEDB unable to start Distributed Transaction'. Here I did not use the term Begin Distributed Transaction when I used the script. Also I created a SP for this script involving just the select statements and it works. But not the update, I have checked the security settings which are fine. There could not be an issue of firewalls too because both the servers are in the same DMZ...any ideas?
Can you post the complete error, including the error number?
The order in which the DTC service and SQL startup service are started is important, but I forget which should be started first.
If you post the exact message, I may be able to help.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
September 6, 2008 at 12:08 pm
Hi,
Go to Component Services and check whether MSDTC is running. Make sure that it is running with NetworkService account. Run profiler in the destination server and see whether your query is reaching destination server.
Note:
Please convert the screenshot to JPEG before uploading to reduce size/time/traffic plz.
Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc
September 7, 2008 at 11:54 am
00008604: Error Message:The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction
This is the error I get when I run the update statement using the Linked server. Now the fact the just a SELECT without the UPDATE gives me the desired output, the select statement also refers the Linked server name. My question here is is the server actually using the DTC to do the select.
To begin with the MS DTC was not enabled which is the case with Windows 2003 Server. After we enabled MS DTC on the server we rebooted the machine by failing over the cluster on which MSSQL is installed. The MS DTC is defined on a Active/Passive Cluster and the configuration of DTC on both nodes is same.
The configuration on MS DTC is done as to allow everything possible. Hope this helps in finding the ever alleviating issue..thanks in advance!!!
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
September 7, 2008 at 11:56 am
The settings I have for the MSDTC are as follows:
Network DTC Access
Allow Remote Clients
Allow Remote Admin
Allow Inbound
Allow Outbound
No Authentication Required
Enable XA Transactions
TIP enabled
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
September 7, 2008 at 1:04 pm
There is an issue with sql2000 linked servers (sp3 and sp4) when called from sql2005.
KB ref at http://support.microsoft.com/default.aspx?scid=kb;en-us;906954
Did you apply at the sql2000 side ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply