Mike and Doris Manning
mikedorism at ntelos.net
Wed Apr 9 07:16:43 CDT 2003
Yes it is. When you are in Access Query Design, change your view to SQL View. Doris Manning Database Administrator Hargrove Inc. www.hargroveinc.com -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark Hayes Sent: Tuesday, April 08, 2003 06:48 PM To: accessd at databaseadvisors.com Subject: RE: [dba-SQLServer]Re: [AccessD] Simple stored proc problem (I hope) DOH! Just realised I had declared the @psnID without length, i.e. @psnID_1 [nvarchar] as opposed to @psnID_1 [nvarchar] (10)... Out of interest... The SQL string viewd in access is very different from that viewed in QA. I can see the SP parameters in the properties window, but isn't it possible to view the entire sql string as you would in Enterprise manager? Thanks again Mark -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark Hayes Sent: 08 April 2003 23:35 To: accessd at databaseadvisors.com; dba-SQLServer at databaseadvisors.com Subject: RE: [dba-SQLServer]Re: [AccessD] Simple stored proc problem (I hope) Thanks Francisco The stored proc is.... CREATE PROCEDURE dbo.update_tbl_psn_1(@psnID_1 [nvarchar], @person_name_2 [nvarchar](100)) AS UPDATE dbo.tbl_psn SET person_name = @person_name_2 WHERE (psnID = @psnID_1) GO in query analyser if I execute SELECT * FROM tbl_psn WHERE psnID='555' I get the record for the person whose ID is "555" however, still in QA, if I execute the SP like this exec update_tbl_psn_1 '555','Updated Name' then its the record with an ID of "5" thats updated. I have tried this from QA and Access and get the same result. If I execute the SP from access by just double clicking on it, I get prompted for the parameters (psnID and person_name), if I enter "555" as the psnID record "5" gets updated. I have even had the same result working with a separate sql server installation and a different data set / table. This is really confusing me as I have written SP's before (a couple of years) ago that work just fine. Really scratching my head on this one Cheers Mark -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Francisco H Tapia Sent: 08 April 2003 22:57 To: accessd at databaseadvisors.com; dba-SQLServer at databaseadvisors.com Subject: [dba-SQLServer]Re: [AccessD] Simple stored proc problem (I hope) Mark, as this is getting more SQL Server related than simply Access ADP's I'm also posting to the dba-SqlServer list (to subscribe follow this link: http://databaseadvisors.com/mailman/listinfo/dba-sqlserver) Now on to the problem... How are you passing the parameters over to SQL server? could you post that info? also, do you have QA (Query Analyzer) if so I would suggest trying out what is happening there... I was going to ask why is the psnID declared as varchar, but you did mentioned you were testing so I am assuming that your planning on using alphanumeric psnID's. in QA you would execute the following Select * FROM tbl_psn Where psnID = '11' what are your results? -Francisco http://rcm.netfirms.com On Tuesday, April 08, 2003 2:03 PM [GMT-8], Mark Hayes <markH at bitgen.co.uk> wrote: : Just had another go and it's the same for characters... If I enter : "AAA" then recod "A" gets updated... : : If I do the same update from code using a simple update sql string : and a command object then its ok. : : ???? : : Mark : : -----Original Message----- : From: accessd-bounces at databaseadvisors.com : [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark Hayes : Sent: 08 April 2003 21:47 : To: accessd at databaseadvisors.com : Subject: [AccessD] Simple stored proc problem (I hope) : : : Hello again... : : I am using XP data project and have a problem with a stored proc. : : I am attempting to update a person record by passing a psnID and a new : name (just playing) : : The problem is that although I may pass the psnID as "11" the record : with the ID "1" is changed. If I am trying to update the psnID "55" : then "5" gets updated. : : The sproc is v.simple and I really can't see whats going wrong : : UPDATE tbl_psn : SET person_name=@person_name_2 : WHERE (psnID=@psnID_1) : : The psnID field is nvarchar 10 : : This XP stuffs getting confusing :o( : : Any help much appreciated : : Mark : _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.470 / Virus Database: 268 - Release Date: 08/04/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.470 / Virus Database: 268 - Release Date: 08/04/2003 _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.470 / Virus Database: 268 - Release Date: 08/04/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.470 / Virus Database: 268 - Release Date: 08/04/2003 _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com