 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / SQL Server / ADP / January 2007Fatal errors with SQL 2005
I have an ADP in Access 2003 that we've been running against SS 2000 on a win2000 server for a couple years. It's got a bunch of vbscript behind everything. Recently, we moved the databases to a win2003 server with SS2005. Most everything works properly. However, I have a field on one of the forms that, when updated, will cause a db trigger to fire and validate the value against another database on the same server. The process works when I run it in the management studio, but when I try to use the Access form to change it, I immediately get errors that are fatal: [DBNETLIB] ConnectionWrite(Send()).] General Network Error. Check your network documentation Connection Failure -2147467259 I have run a sql profile. these are the last two events before it crashes: exec sp_executesql N'UPDATE "dbStyleMaster"."dbo"."tblSampleRequests" SET "SKU"=@P1 WHERE "SRNumberID"=@P2 AND "SKU"=@P3',N'@P1 varchar(3),@P2 int,@P3 varchar(4)','234',28631,'234x' exec sp_executesql N'EXEC sp_resyncexecutesql N''SELECT * FROM "dbo"."vw_frm_pbd_Sub1" WHERE ((@CDM7 = "projectnumber"))'', N'' @CDM7 varchar(6)'', ''ZZ076'', @P1 ',N'@P1 int',33872 The first update works because it shows correctly when I look at it in the database afterward. In the logs, there is a bunch of error messages: /2007 15:42:59,spid159,Unknown,* n t & P 6e 00 74 00 00 00 26 04 04 50 84 00 00 01/05/2007 15:42:59,spid159,Unknown,* 2 @ P 1 i 04 00 01 32 0e 00 00 00 40 00 50 00 31 00 20 00 69 00 01/05/2007 15:42:59,spid159,Unknown,* @ P 1 c 20 00 40 00 50 00 31 00 20 00 00 00 63 0e 00 00 00 09 01/05/2007 15:42:59,spid159,Unknown,* ' Z Z 0 7 6 ' <c/> 20 00 27 00 5a 00 5a 00 30 00 37 00 36 00 27 00 2c 00 01/05/2007 15:42:59,spid159,Unknown,* c h a r ( 6 ) ' <c/> 63 00 68 00 61 00 72 00 28 00 36 00 29 00 27 00 2c 00 01/05/2007 15:42:59,spid159,Unknown,* @ C D M 7 v a r 40 00 43 00 44 00 4d 00 37 00 20 00 76 00 61 00 72 00 01/05/2007 15:42:59,spid159,Unknown,* " ) ) ' <c/> N ' 22 00 29 00 29 00 27 00 2c 00 20 00 4e 00 27 00 20 00 01/05/2007 15:42:59,spid159,Unknown,* e c t n u m b e r 65 00 63 00 74 00 6e 00 75 00 6d 00 62 00 65 00 72 00 01/05/2007 15:42:59,spid159,Unknown,* 7 = " p r o j 37 00 20 00 3d 00 20 00 22 00 70 00 72 00 6f 00 6a 00 01/05/2007 15:42:59,spid159,Unknown,* R E ( ( @ C D M 52 00 45 00 20 00 28 00 28 00 40 00 43 00 44 00 4d 00 01/05/2007 15:42:59,spid159,Unknown,* S u b 1 " W H E 53 00 75 00 62 00 31 00 22 00 20 00 57 00 48 00 45 00 01/05/2007 15:42:59,spid159,Unknown,* _ f r m _ p b d _ 5f 00 66 00 72 00 6d 00 5f 00 70 00 62 00 64 00 5f 00 01/05/2007 15:42:59,spid159,Unknown,* " d b o " . " v w 22 00 64 00 62 00 6f 00 22 00 2e 00 22 00 76 00 77 00 01/05/2007 15:42:59,spid159,Unknown,* * F R O M 20 00 2a 00 20 00 20 00 46 00 52 00 4f 00 4d 00 20 00 01/05/2007 15:42:59,spid159,Unknown,* N ' S E L E C T 4e 00 27 00 53 00 45 00 4c 00 45 00 43 00 54 00 20 00 01/05/2007 15:42:59,spid159,Unknown,* e c u t e s q l 65 00 63 00 75 00 74 00 65 00 73 00 71 00 6c 00 20 00 01/05/2007 15:42:59,spid159,Unknown,* _ r e s y n c e x 5f 00 72 00 65 00 73 00 79 00 6e 00 63 00 65 00 78 00 01/05/2007 15:42:59,spid159,Unknown,* E X E C s p 14 01 00 00 45 00 58 00 45 00 43 00 20 00 73 00 70 00 01/05/2007 15:42:59,spid159,Unknown,* ÿÿ c 2 ff ff 0a 00 00 00 00 00 63 14 01 00 00 09 04 00 01 32 01/05/2007 15:42:59,spid159,Unknown,* Input Buffer 255 bytes - 01/05/2007 15:42:59,spid159,Unknown,* Access Violation occurred reading address 00000010 01/05/2007 15:42:59,spid159,Unknown,* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION 01/05/2007 15:42:59,spid159,Unknown,* Exception Address = 014B49C9 Module(sqlservr+004B49C9) 01/05/2007 15:42:59,spid159,Unknown,* 01/05/2007 15:42:59,spid159,Unknown,* 01/05/2007 15:42:59,spid159,Unknown,* 01/05/07 15:42:59 spid 159 01/05/2007 15:42:59,spid159,Unknown,* BEGIN STACK DUMP: 01/05/2007 15:42:59,spid159,Unknown,* 01/05/2007 15:42:59,spid159,Unknown,* ******************************************************************************* 01/05/2007 15:42:59,spid159,Unknown,SqlDumpExceptionHandler: Process 159 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process. 01/05/2007 15:42:59,spid159,Unknown,***Stack Dump being sent to E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLDump0031.txt 01/05/2007 15:42:59,spid159,Unknown,Using 'dbghelp.dll' version '4.0.5' There's a bunch more stuff there, but that's the basic idea. The I have put breakpoints all over the place in the ADP VBScript code, but can't seem to find the place where it goes bad. What should I be looking at? Why does it work with SS2000, and not SS2005? Thanks! Chris Wild guess: try setting the database in the SQL-2000 compatibility mode or better, create your own ResyncCommand (and set the UniqueTable property, too). And make sure that the permission for the linked server are set correctly to be used from the account used in the Access ADP.  SignatureSylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: sylvain aei ca (fill the blanks, no spam please) I have an ADP in Access 2003 that we've been running against SS 2000 on a win2000 server for a couple years. It's got a bunch of vbscript behind everything. Recently, we moved the databases to a win2003 server with SS2005. Most everything works properly. However, I have a field on one of the forms that, when updated, will cause a db trigger to fire and validate the value against another database on the same server. The process works when I run it in the management studio, but when I try to use the Access form to change it, I immediately get errors that are fatal: [DBNETLIB] ConnectionWrite(Send()).] General Network Error. Check your network documentation Connection Failure -2147467259 I have run a sql profile. these are the last two events before it crashes: exec sp_executesql N'UPDATE "dbStyleMaster"."dbo"."tblSampleRequests" SET "SKU"=@P1 WHERE "SRNumberID"=@P2 AND "SKU"=@P3',N'@P1 varchar(3),@P2 int,@P3 varchar(4)','234',28631,'234x' exec sp_executesql N'EXEC sp_resyncexecutesql N''SELECT * FROM "dbo"."vw_frm_pbd_Sub1" WHERE ((@CDM7 = "projectnumber"))'', N'' @CDM7 varchar(6)'', ''ZZ076'', @P1 ',N'@P1 int',33872 The first update works because it shows correctly when I look at it in the database afterward. In the logs, there is a bunch of error messages: /2007 15:42:59,spid159,Unknown,* n t & P" 6e 00 74 00 00 00 26 04 04 50 84 00 00 01/05/2007 15:42:59,spid159,Unknown,* 2 @ P 1 i 04 00 01 32 0e 00 00 00 40 00 50 00 31 00 20 00 69 00 01/05/2007 15:42:59,spid159,Unknown,* @ P 1 c 20 00 40 00 50 00 31 00 20 00 00 00 63 0e 00 00 00 09 01/05/2007 15:42:59,spid159,Unknown,* ' Z Z 0 7 6 ' <c/> 20 00 27 00 5a 00 5a 00 30 00 37 00 36 00 27 00 2c 00 01/05/2007 15:42:59,spid159,Unknown,* c h a r ( 6 ) ' <c/> 63 00 68 00 61 00 72 00 28 00 36 00 29 00 27 00 2c 00 01/05/2007 15:42:59,spid159,Unknown,* @ C D M 7 v a r 40 00 43 00 44 00 4d 00 37 00 20 00 76 00 61 00 72 00 01/05/2007 15:42:59,spid159,Unknown,* " ) ) ' <c/> N ' 22 00 29 00 29 00 27 00 2c 00 20 00 4e 00 27 00 20 00 01/05/2007 15:42:59,spid159,Unknown,* e c t n u m b e r 65 00 63 00 74 00 6e 00 75 00 6d 00 62 00 65 00 72 00 01/05/2007 15:42:59,spid159,Unknown,* 7 = " p r o j 37 00 20 00 3d 00 20 00 22 00 70 00 72 00 6f 00 6a 00 01/05/2007 15:42:59,spid159,Unknown,* R E ( ( @ C D M 52 00 45 00 20 00 28 00 28 00 40 00 43 00 44 00 4d 00 01/05/2007 15:42:59,spid159,Unknown,* S u b 1 " W H E 53 00 75 00 62 00 31 00 22 00 20 00 57 00 48 00 45 00 01/05/2007 15:42:59,spid159,Unknown,* _ f r m _ p b d _ 5f 00 66 00 72 00 6d 00 5f 00 70 00 62 00 64 00 5f 00 01/05/2007 15:42:59,spid159,Unknown,* " d b o " . " v w 22 00 64 00 62 00 6f 00 22 00 2e 00 22 00 76 00 77 00 01/05/2007 15:42:59,spid159,Unknown,* * F R O M 20 00 2a 00 20 00 20 00 46 00 52 00 4f 00 4d 00 20 00 01/05/2007 15:42:59,spid159,Unknown,* N ' S E L E C T 4e 00 27 00 53 00 45 00 4c 00 45 00 43 00 54 00 20 00 01/05/2007 15:42:59,spid159,Unknown,* e c u t e s q l 65 00 63 00 75 00 74 00 65 00 73 00 71 00 6c 00 20 00 01/05/2007 15:42:59,spid159,Unknown,* _ r e s y n c e x 5f 00 72 00 65 00 73 00 79 00 6e 00 63 00 65 00 78 00 01/05/2007 15:42:59,spid159,Unknown,* E X E C s p 14 01 00 00 45 00 58 00 45 00 43 00 20 00 73 00 70 00 01/05/2007 15:42:59,spid159,Unknown,* ÿÿ c 2 ff ff 0a 00 00 00 00 00 63 14 01 00 00 09 04 00 01 32 01/05/2007 15:42:59,spid159,Unknown,* Input Buffer 255 bytes - 01/05/2007 15:42:59,spid159,Unknown,* Access Violation occurred reading address 00000010 01/05/2007 15:42:59,spid159,Unknown,* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION 01/05/2007 15:42:59,spid159,Unknown,* Exception Address = 014B49C9 Module(sqlservr+004B49C9) 01/05/2007 15:42:59,spid159,Unknown,* 01/05/2007 15:42:59,spid159,Unknown,* 01/05/2007 15:42:59,spid159,Unknown,* 01/05/07 15:42:59 spid 159 01/05/2007 15:42:59,spid159,Unknown,* BEGIN STACK DUMP: 01/05/2007 15:42:59,spid159,Unknown,* 01/05/2007 15:42:59,spid159,Unknown,* ******************************************************************************* 01/05/2007 15:42:59,spid159,Unknown,SqlDumpExceptionHandler: Process 159 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process. 01/05/2007 15:42:59,spid159,Unknown,***Stack Dump being sent to E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLDump0031.txt 01/05/2007 15:42:59,spid159,Unknown,Using 'dbghelp.dll' version '4.0.5' There's a bunch more stuff there, but that's the basic idea. The I have put breakpoints all over the place in the ADP VBScript code, but can't seem to find the place where it goes bad. What should I be looking at? Why does it work with SS2000, and not SS2005? Thanks! Chris Hi Sylvain: The SQL Compatability Level is currently set to SQL SERVER 2000. What problems might I have if I try to make it SQL SERVER 2005? I'm not sure what you mean by "create your own ResyncCommand (and set the UniqueTable property". Also, both databases are on the same server; do I need to do anything with 'linked servers'? Thanks! Chris > Wild guess: try setting the database in the SQL-2000 compatibility mode or > better, create your own ResyncCommand (and set the UniqueTable property, > too). > > And make sure that the permission for the linked server are set correctly to > be used from the account used in the Access ADP. Here's another piece to the puzzle: There are multiple forms in the ADP. The problem form won't let me update ANY fields, apparently. However, I am able to update fields on a different form. problem form Record Source view: select t1.a, t2.b, t4.val from vw_table1 t1 inner join vw_table2 t2 on t1.pk = t2.fk inner join tblTable4 t4 on t4.pk = t1.fld Working form Record Source view: select t1.a, t1.b from vw_table1 t1 inner join tblTable4 t4 on t4.pk = t1.fld CB > Hi Sylvain: > [quoted text clipped - 10 lines] > > Chris If the compatibility level is already set to SQL-Server 2000, then your problem is not there. Excerpt for the possibility of having hierarchical schemas and some advanced esoteric features, you shouldn't have any problem making it to SQL-Server 2005. For any Record Source query that is somewhat complicated, you must set the UniqueTable and ResyncCommand properties for the forms, that you will find under the Data tab; otherwise your form will be read-only. Take a look at some of the previous posts in this newsgroup for some exemples on how to set them. For the linked server, I never used them with ADP, so I cannot tell you more about this. To see if this is the source of your problem, you should make a test without a linked server (and without the trigger, too). If the linked server and/or the trigger is the source of the problem, it's possible that setting the UniqueTable and ResyncCommand will also solve it; however it's also possible that this won't help. SignatureSylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: sylvain aei ca (fill the blanks, no spam please) > Hi Sylvain: > [quoted text clipped - 19 lines] >> to >> be used from the account used in the Access ADP. just flat out don't use SQL Server 2005 with ADP. end of f.cking story either use Access 2007 or SQL 2000. Microsoft is too fat and lazy to deliver to us the Access 2003 / SQL 2005 _PATCH_ like they had promised to us so f.ck microsoft stick with SQL 2000 I've tried the compatability mode also; and it's close to worthless sorry If I was a PM at Microsoft it sure as f.ck wouldn't be like this.. I'd FLAME the living daylights out of anyone that stopped me too -Aaron > Hi Sylvain: > [quoted text clipped - 17 lines] > > And make sure that the permission for the linked server are set correctly to > > be used from the account used in the Access ADP. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.