problem with # sign

I have a stored proc that takes a parameter and finds the correct row.
Simple and easy. Unfortunately the data contains various symbols such as #
and -. When I run the proc and try to pass a value that contains a symbol I
get an error. The sp works fine when there are no symbols. Any
suggestions?
Laura KOh and here is the sp.
@.ProductCode nvarchar (5)
AS
SELECT pc.strcolorID, p.intproductID
FROM dbo.tblProducts p join tblJctProductsColors pc
On p.IntProductID=pc.intProductID
WHERE strProductCode = @.ProductCode
Simple sp. but wont work with symbols. I ran the proc with #322 and I get
an error. Sorry I am a newbie. Any help appreciated.
Thanks
Laura K
"Laura K" <klkazanAT@.ATcharter.net> wrote in message
news:%23du%23BpJoFHA.3316@.TK2MSFTNGP14.phx.gbl...
>I have a stored proc that takes a parameter and finds the correct row.
>Simple and easy. Unfortunately the data contains various symbols such as #
>and -. When I run the proc and try to pass a value that contains a symbol
>I get an error. The sp works fine when there are no symbols. Any
>suggestions?
> Laura K
>|||What about the table structures? You don't say what the column definitions
are.
"Laura K" <klkazanAT@.ATcharter.net> wrote in message
news:u1OSNtJoFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Oh and here is the sp.
> @.ProductCode nvarchar (5)
> AS
>
> SELECT pc.strcolorID, p.intproductID
> FROM dbo.tblProducts p join tblJctProductsColors pc
> On p.IntProductID=pc.intProductID
> WHERE strProductCode = @.ProductCode
> Simple sp. but wont work with symbols. I ran the proc with #322 and I get
> an error. Sorry I am a newbie. Any help appreciated.
> Thanks
> Laura K
> "Laura K" <klkazanAT@.ATcharter.net> wrote in message
> news:%23du%23BpJoFHA.3316@.TK2MSFTNGP14.phx.gbl...
>|||Hi
You may want to put checks into the client to stop data being passed that
will not be valid. You do not say what the error is, as #322 I would expect
no rows to be returned rather than any specific error.
John
"Laura K" <klkazanAT@.ATcharter.net> wrote in message
news:u1OSNtJoFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Oh and here is the sp.
> @.ProductCode nvarchar (5)
> AS
>
> SELECT pc.strcolorID, p.intproductID
> FROM dbo.tblProducts p join tblJctProductsColors pc
> On p.IntProductID=pc.intProductID
> WHERE strProductCode = @.ProductCode
> Simple sp. but wont work with symbols. I ran the proc with #322 and I get
> an error. Sorry I am a newbie. Any help appreciated.
> Thanks
> Laura K
> "Laura K" <klkazanAT@.ATcharter.net> wrote in message
> news:%23du%23BpJoFHA.3316@.TK2MSFTNGP14.phx.gbl...
>|||I get specific errors.
When I use 86-283 there error is
Line 1: Incorrect syntax near '-'
I also have spaces in some of the codes so when I use
spGetProductColor TUK Elvis W
Line 1: Incorrect syntax near 'Elvis'.
Upon closer inspection I have no problems with the # sign thus making the
title of my post obsolete. I do however have problems with the - and
spaces.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:OnpT6tLoFHA.632@.tk2msftngp13.phx.gbl...
> Hi
> You may want to put checks into the client to stop data being passed that
> will not be valid. You do not say what the error is, as #322 I would
> expect no rows to be returned rather than any specific error.
> John
> "Laura K" <klkazanAT@.ATcharter.net> wrote in message
> news:u1OSNtJoFHA.2472@.TK2MSFTNGP15.phx.gbl...
>|||The column in question is a brandcode which may include numbers, letters or
what have you depending on the manufactures usage. The datatype is
nvarchar. It is not related to any other table. The table is related by the
productID to many other tables.
Not sure if this is what you are asking.
Laura
"Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
news:5rDLe.88549$G8.69248@.text.news.blueyonder.co.uk...
> What about the table structures? You don't say what the column
> definitions are.
> "Laura K" <klkazanAT@.ATcharter.net> wrote in message
> news:u1OSNtJoFHA.2472@.TK2MSFTNGP15.phx.gbl...
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.|||Laura,
try
exec spGetProductColor 'TUK Elvis W'
and see if that fixes it.
Stu|||It takes care of the error but now it returns nothing. However when I put
quotes around a value that has no spaces or dashes it still returns a color.
Laura
"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1124043325.349594.239730@.o13g2000cwo.googlegroups.com...
> Laura,
> try
> exec spGetProductColor 'TUK Elvis W'
> and see if that fixes it.
> Stu
>|||Excuse the fact that I am new at this but let me know how to post DDL.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1124041314.536726.261720@.f14g2000cwb.googlegroups.com...
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications. It is very hard to debug code when you do not let us
> see it.
>

problem with "Sysname" data type - SQL Server 2000

I have a database where several tables have fields of data
type "SYSNAME". Unfortunately SQL Server 2000 doesn't seem
to recognize this data type and this is causing "Field not
found" error messages in my application. Has anyone
encountered this problem before? How do I get around it?sysname = nvarchar(128) in 7 and 2000. It is equivalent to varchar(30) in
6.5
--
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"noclue6" <sobrienn@.hotmail.com> wrote in message
news:830501c350f2$3defa890$7d02280a@.phx.gbl...
> I have a database where several tables have fields of data
> type "SYSNAME". Unfortunately SQL Server 2000 doesn't seem
> to recognize this data type and this is causing "Field not
> found" error messages in my application. Has anyone
> encountered this problem before? How do I get around it?|||But its not possible to change the data type of a column
in the SYSUSERS table - i.e. a system table? Or is it?
>--Original Message--
>sysname = nvarchar(128) in 7 and 2000. It is equivalent
to varchar(30) in
>6.5
>--
>--
>Allan Mitchell (Microsoft SQL Server MVP)
>MCSE,MCDBA
>www.SQLDTS.com
>I support PASS - the definitive, global community
>for SQL Server professionals - http://www.sqlpass.org
>
>"noclue6" <sobrienn@.hotmail.com> wrote in message
>news:830501c350f2$3defa890$7d02280a@.phx.gbl...
>> I have a database where several tables have fields of
data
>> type "SYSNAME". Unfortunately SQL Server 2000 doesn't
seem
>> to recognize this data type and this is causing "Field
not
>> found" error messages in my application. Has anyone
>> encountered this problem before? How do I get around it?
>
>.
>|||You can but you most certainly do not want to.
What problems exactly are you having with it?
What are you doing at the time.?
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"noclue6" <sobrienn@.hotmail.com> wrote in message
news:0b1901c3510c$8a546530$a401280a@.phx.gbl...
> But its not possible to change the data type of a column
> in the SYSUSERS table - i.e. a system table? Or is it?
>
> >--Original Message--
> >sysname = nvarchar(128) in 7 and 2000. It is equivalent
> to varchar(30) in
> >6.5
> >
> >--
> >
> >--
> >Allan Mitchell (Microsoft SQL Server MVP)
> >MCSE,MCDBA
> >www.SQLDTS.com
> >I support PASS - the definitive, global community
> >for SQL Server professionals - http://www.sqlpass.org
> >
> >
> >
> >"noclue6" <sobrienn@.hotmail.com> wrote in message
> >news:830501c350f2$3defa890$7d02280a@.phx.gbl...
> >> I have a database where several tables have fields of
> data
> >> type "SYSNAME". Unfortunately SQL Server 2000 doesn't
> seem
> >> to recognize this data type and this is causing "Field
> not
> >> found" error messages in my application. Has anyone
> >> encountered this problem before? How do I get around it?
> >
> >
> >.
> >|||the software is trying to access the NAME field of the
SYSUSERS table on logon but its just not finding it
because "Name" is of type SYSNAME.
>--Original Message--
>You can but you most certainly do not want to.
>What problems exactly are you having with it?
>What are you doing at the time.?
>
>--
>--
>Allan Mitchell (Microsoft SQL Server MVP)
>MCSE,MCDBA
>www.SQLDTS.com
>I support PASS - the definitive, global community
>for SQL Server professionals - http://www.sqlpass.org
>
>"noclue6" <sobrienn@.hotmail.com> wrote in message
>news:0b1901c3510c$8a546530$a401280a@.phx.gbl...
>> But its not possible to change the data type of a column
>> in the SYSUSERS table - i.e. a system table? Or is it?
>>
>> >--Original Message--
>> >sysname = nvarchar(128) in 7 and 2000. It is
equivalent
>> to varchar(30) in
>> >6.5
>> >
>> >--
>> >
>> >--
>> >Allan Mitchell (Microsoft SQL Server MVP)
>> >MCSE,MCDBA
>> >www.SQLDTS.com
>> >I support PASS - the definitive, global community
>> >for SQL Server professionals - http://www.sqlpass.org
>> >
>> >
>> >
>> >"noclue6" <sobrienn@.hotmail.com> wrote in message
>> >news:830501c350f2$3defa890$7d02280a@.phx.gbl...
>> >> I have a database where several tables have fields of
>> data
>> >> type "SYSNAME". Unfortunately SQL Server 2000 doesn't
>> seem
>> >> to recognize this data type and this is
causing "Field
>> not
>> >> found" error messages in my application. Has anyone
>> >> encountered this problem before? How do I get around
it?
>> >
>> >
>> >.
>> >
>
>.
>