Syntax error or acess violation

[krisboy]

Hi there,

I hope someone can help me here as this is driving me nuts...! Ok - I have an application which calls stored procedures from two machines:

1. Windows 2003, SQL 2005
2. Windows 2003, SQL 2000

If I call the stored procedure on server1, everything works ok. If the same stored proc is called on server2, I get the "syntax error or access violation" error reported. As far as I can see, I have the same setup in DTC permissions on both machines and the user running the stored proc is DBOwner. I feel like I have missed something simple and this is taking WAY too much time to sort out so can someone please help?!

By the way the stored proc contains a query running a simple select statement (not even any parameters) - and is most definitely not a problem. Additionally, the query can be run fine from query analyzer.

 

[manmaria]

May be a silly question, but can you execute the stored procedure from the server 2? Not the query in SP.

 

[krisboy]

Hmmm - yes I could execute the SPO from Query Analyzer on both machines, but this helped me find the problem! It turns out that I was enclosing each SP call parameter in brackets [] in Query Analyzer, but not in my application call out. Usually this wouldn't matter, but my remote server2 has a "-" character in the name, which SQL isn't too happy with, hence the syntax error. Once I had included the brackets in the app call out, it worked fine. I knew it would be something simple (perhaps me in this case) - thanks!