Tuesday, March 20, 2012

Problem executing a stored proc

Hi All,

I have stored proc that processes about 60,000 rows using a cursor. When I call the SP from Query Analyzer, I get the following error message after processing about 12,000 records :

Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (InvalidParam()).

Server: Msg 11, Level 16, State 1, Line 0

General network error. Check your network documentation.

ODBC: Msg 0, Level 16, State 1

Communication link failure

Connection Broken

12614 records

What can i do to make this SP run sucessfully ? I even tried using a table variable instead of a cursor, but got the same result.

THE output from SP_CONFIG is

Option config_value

---------------------------------------

affinity mask0
allow updates0
awe enabled0
c2 audit mode0
cost threshold for parallelism5
Cross DB Ownership Chaining0
cursor threshold-1
default full-text language1033
default language0
fill factor (%)0
index create memory (KB)0
lightweight pooling0
locks0
max degree of parallelism0
max server memory (MB)2147483647
max text repl size (B)65536
max worker threads255
media retention0
min memory per query (KB)1024
min server memory (MB)0
nested triggers1
network packet size (B)4096
open objects0
priority boost0
query governor cost limit0
query wait (s)-1
recovery interval (min)0
remote access1
remote login timeout (s)20
remote proc trans0
remote query timeout (s)0
scan for startup procs0
set working set size0
show advanced options1
two digit year cutoff2049
user connections0
user options0

Try rewriting the sproc, only without the cursor...

No comments:

Post a Comment