[dba-SQLServer]Re: [AccessD] Simple stored proc problem (I hope)

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



More information about the AccessD mailing list