Query runs with error when passed parameters

Question

Query runs with error when passed parameters

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

What error?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

kd_um SSC-Addicted Points: 460 More actions · Answer 2

Sorry for missing that out. The error is:

1). [Microsoft][SQL Server Native Client 10.0][SQL Server]Must declare the scalar variable "@CardName". 2). [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement 'Service Contracts' (OCTR) (s) could not be prepared.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

And exactly how are you calling that piece of code?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

kd_um SSC-Addicted Points: 460 More actions · Answer 4

I am running it directly in the application's (SAP in this case) query generator.

Tried to run it SQL server management studio but it runs without error but with no result, so thought its not the place to run it.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 5

How *exactly* are you calling that? Are you calling the entire piece as you've written there? Are you calling just the query? If so, have you defined the parameters?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

kd_um SSC-Addicted Points: 460 More actions · Answer 6

I am calling the entire piece as written, with parameter @CardName declared exactly how I have above.

Regards.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 7

As you have it above, @CardName is not a parameter, it's a local variable.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

kd_um SSC-Addicted Points: 460 More actions · Answer 8

Thanks for your reply.

I have brain freeze right now. Without disputing what you just said, I have more queries successfully running with the same syntax; what's uniquly stupid I am doing?

Brandie Tarvin SSC Guru Points: 173108 More actions · Answer 9

Fair warning. I only know the barest bit about SAP because I work with someone in another state who uses it. That being said, does this article help?

http://scn.sap.com/thread/1703641

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

kd_um SSC-Addicted Points: 460 More actions · Answer 10

Hi Brandie,

You pointed me to the right direction and sometimes that's the best thing to do.

Added this in the start of my query and everything was good:

/* SELECT FROM [dbo].[OINV] T2 */

DECLARE @Cust AS nvarchar(100)

/* WHERE */

SET @Cust = /* T2.CardName */ '[%0]'

[Although I must admit I spent more time reading your website and profile then working on the solution.]

Thank you.

Best Regards

Kanu

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 11

I thought you said you were calling the exact piece of code as you gave above, with the declare and set in it?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Brandie Tarvin SSC Guru Points: 173108 More actions · Answer 12

Glad I could help. @=)

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Brandie Tarvin SSC Guru Points: 173108 More actions · Answer 13

GilaMonster (1/8/2013)
I thought you said you were calling the exact piece of code as you gave above, with the declare and set in it?

Gail, believe it or not, that additional comment is required in the SAP interface. The code failed because it wasn't in his original code.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

kd_um SSC-Addicted Points: 460 More actions · Answer 14

Brandie Tarvin (1/8/2013)
GilaMonster (1/8/2013)
I thought you said you were calling the exact piece of code as you gave above, with the declare and set in it?
Gail, believe it or not, that additional comment is required in the SAP interface. The code failed because it wasn't in his original code.

And I am looking for the reason why this extra bit is required, or rather how does a comment make so much difference to a program.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 15

Brandie Tarvin (1/8/2013)
GilaMonster (1/8/2013)
I thought you said you were calling the exact piece of code as you gave above, with the declare and set in it?
Gail, believe it or not, that additional comment is required in the SAP interface.

A comment is required???? What kind of messed up program need specific comments to work?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass