Problem with LIKE in stored procedure

I Can't seem to get LIKE to work in a stored procedure. For instance this
sql works in a query...
SELECT username, approle, billingcustomer, emailredundant, groupadmin,
branch, nakey, companyadmin, bcstring, dateapproved
FROM users
WHERE (branch LIKE '%%') AND (bcstring LIKE '%%') AND (dateapproved IS
NULL)
ORDER BY username
But this stored procedure returns no records when %% is supplied in the two
argumants...
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetUnapprovedUsers]
(
@.branch nchar(4),
@.bcstring nvarchar(20)
)
AS
SET NOCOUNT ON;
SELECT username, approle, billingcustomer, emailredundant, groupadmin,
branch, nakey, companyadmin, bcstring, dateapproved
FROM users
WHERE (branch LIKE @.branch) AND (bcstring LIKE @.bcstring) AND
(dateapproved IS NULL)
ORDER BY username
Anyone know why?
Thanks,
Gary
> @.branch nchar(4),
Note that nchar is fixed length so:
GetUnapprovedUsers
@.branch = N'%%'
@.bcstring = N'%%'
Is equivalent to:
GetUnapprovedUsers
@.branch = N'%% '
@.bcstring = N'%%'
You won't get any matches unless you have branches with spaces. I'm not
sure I understand why you specify 2 wildcard characters.
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"GaryDean" <gdeanblakely@.newsgroup.nospam> wrote in message
news:%23lvBQm9gIHA.3352@.TK2MSFTNGP04.phx.gbl...
>I Can't seem to get LIKE to work in a stored procedure. For instance this
>sql works in a query...
> SELECT username, approle, billingcustomer, emailredundant, groupadmin,
> branch, nakey, companyadmin, bcstring, dateapproved
> FROM users
> WHERE (branch LIKE '%%') AND (bcstring LIKE '%%') AND (dateapproved IS
> NULL)
> ORDER BY username
> But this stored procedure returns no records when %% is supplied in the
> two argumants...
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> GO
> ALTER PROCEDURE [dbo].[GetUnapprovedUsers]
> (
> @.branch nchar(4),
> @.bcstring nvarchar(20)
> )
> AS
> SET NOCOUNT ON;
> SELECT username, approle, billingcustomer, emailredundant, groupadmin,
> branch, nakey, companyadmin, bcstring, dateapproved
> FROM users
> WHERE (branch LIKE @.branch) AND (bcstring LIKE @.bcstring) AND
> (dateapproved IS NULL)
> ORDER BY username
> Anyone know why?
> Thanks,
> Gary
>
|||Dan,
you are not making sense to me. % means "any string of zero or more
characters". WHERE branch LIKE '%%' is equivilent to not having the WHERE
clause at all. % is not a "wild card character." My example stored
procedure and plain sql are totally equivilent - one works the other does
not.
Gary
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:DD64A043-B281-4ED9-8770-101A9EDC93C2@.microsoft.com...
> Note that nchar is fixed length so:
> GetUnapprovedUsers
> @.branch = N'%%'
> @.bcstring = N'%%'
> Is equivalent to:
> GetUnapprovedUsers
> @.branch = N'%% '
> @.bcstring = N'%%'
> You won't get any matches unless you have branches with spaces. I'm not
> sure I understand why you specify 2 wildcard characters.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
> "GaryDean" <gdeanblakely@.newsgroup.nospam> wrote in message
> news:%23lvBQm9gIHA.3352@.TK2MSFTNGP04.phx.gbl...
>
|||> My example stored procedure and plain sql are totally equivilent - one
> works the other does not.
These are not equivalent and that is why you get different results. Let me
try to explain another way.
The select statement works because you are passing only wildcards (exactly 2
percent signs). This is almost the same as no WHERE clause except that NULL
values will be excluded.
The stored procedure is different because the @.branch parameter is declared
as fixed length of 4. When you pass 2 percent signs, the actual value used
in the LIKE expression is the 2 percent signs plus 2 blanks ('%% '). This
means that you will only find branches that end in 2 blanks. I think you
will get results you expect if you declare the parameter as nvarchar(4)
instead of nchar(4).
As I mentioned earlier, although 2 leading percent signs will work, the
second is superfluous.
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"GaryDean" <gdeanblakely@.newsgroup.nospam> wrote in message
news:uXs7YfFhIHA.1184@.TK2MSFTNGP04.phx.gbl...
> Dan,
> you are not making sense to me. % means "any string of zero or more
> characters". WHERE branch LIKE '%%' is equivilent to not having the WHERE
> clause at all. % is not a "wild card character." My example stored
> procedure and plain sql are totally equivilent - one works the other does
> not.
> Gary
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:DD64A043-B281-4ED9-8770-101A9EDC93C2@.microsoft.com...
>
|||Yes, that was the problem. Thanks for the help.
Gary
www.deanblakely.com
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:4D2F5517-B34F-4742-8763-AC46A34CE0FD@.microsoft.com...
> These are not equivalent and that is why you get different results. Let
> me try to explain another way.
> The select statement works because you are passing only wildcards (exactly
> 2 percent signs). This is almost the same as no WHERE clause except that
> NULL values will be excluded.
> The stored procedure is different because the @.branch parameter is
> declared as fixed length of 4. When you pass 2 percent signs, the actual
> value used in the LIKE expression is the 2 percent signs plus 2 blanks
> ('%% '). This means that you will only find branches that end in 2
> blanks. I think you will get results you expect if you declare the
> parameter as nvarchar(4) instead of nchar(4).
> As I mentioned earlier, although 2 leading percent signs will work, the
> second is superfluous.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
> "GaryDean" <gdeanblakely@.newsgroup.nospam> wrote in message
> news:uXs7YfFhIHA.1184@.TK2MSFTNGP04.phx.gbl...
>

Problem with LIKE in stored procedure

I Can't seem to get LIKE to work in a stored procedure. For instance this
sql works in a query...
SELECT username, approle, billingcustomer, emailredundant, groupadmin,
branch, nakey, companyadmin, bcstring, dateapproved
FROM users
WHERE (branch LIKE '%%') AND (bcstring LIKE '%%') AND (dateapproved IS
NULL)
ORDER BY username
But this stored procedure returns no records when %% is supplied in the two
argumants...
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetUnapprovedUsers]
(
@.branch nchar(4),
@.bcstring nvarchar(20)
)
AS
SET NOCOUNT ON;
SELECT username, approle, billingcustomer, emailredundant, groupadmin,
branch, nakey, companyadmin, bcstring, dateapproved
FROM users
WHERE (branch LIKE @.branch) AND (bcstring LIKE @.bcstring) AND
(dateapproved IS NULL)
ORDER BY username
Anyone know why?
Thanks,
Gary> @.branch nchar(4),
Note that nchar is fixed length so:
GetUnapprovedUsers
@.branch = N'%%'
@.bcstring = N'%%'
Is equivalent to:
GetUnapprovedUsers
@.branch = N'%% '
@.bcstring = N'%%'
You won't get any matches unless you have branches with spaces. I'm not
sure I understand why you specify 2 wildcard characters.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"GaryDean" <gdeanblakely@.newsgroup.nospam> wrote in message
news:%23lvBQm9gIHA.3352@.TK2MSFTNGP04.phx.gbl...
>I Can't seem to get LIKE to work in a stored procedure. For instance this
>sql works in a query...
> SELECT username, approle, billingcustomer, emailredundant, groupadmin,
> branch, nakey, companyadmin, bcstring, dateapproved
> FROM users
> WHERE (branch LIKE '%%') AND (bcstring LIKE '%%') AND (dateapproved IS
> NULL)
> ORDER BY username
> But this stored procedure returns no records when %% is supplied in the
> two argumants...
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> GO
> ALTER PROCEDURE [dbo].[GetUnapprovedUsers]
> (
> @.branch nchar(4),
> @.bcstring nvarchar(20)
> )
> AS
> SET NOCOUNT ON;
> SELECT username, approle, billingcustomer, emailredundant, groupadmin,
> branch, nakey, companyadmin, bcstring, dateapproved
> FROM users
> WHERE (branch LIKE @.branch) AND (bcstring LIKE @.bcstring) AND
> (dateapproved IS NULL)
> ORDER BY username
> Anyone know why?
> Thanks,
> Gary
>|||Dan,
you are not making sense to me. % means "any string of zero or more
characters". WHERE branch LIKE '%%' is equivilent to not having the WHERE
clause at all. % is not a "wild card character." My example stored
procedure and plain sql are totally equivilent - one works the other does
not.
Gary
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:DD64A043-B281-4ED9-8770-101A9EDC93C2@.microsoft.com...
>> @.branch nchar(4),
> Note that nchar is fixed length so:
> GetUnapprovedUsers
> @.branch = N'%%'
> @.bcstring = N'%%'
> Is equivalent to:
> GetUnapprovedUsers
> @.branch = N'%% '
> @.bcstring = N'%%'
> You won't get any matches unless you have branches with spaces. I'm not
> sure I understand why you specify 2 wildcard characters.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
> "GaryDean" <gdeanblakely@.newsgroup.nospam> wrote in message
> news:%23lvBQm9gIHA.3352@.TK2MSFTNGP04.phx.gbl...
>>I Can't seem to get LIKE to work in a stored procedure. For instance this
>>sql works in a query...
>> SELECT username, approle, billingcustomer, emailredundant,
>> groupadmin, branch, nakey, companyadmin, bcstring, dateapproved
>> FROM users
>> WHERE (branch LIKE '%%') AND (bcstring LIKE '%%') AND (dateapproved
>> IS NULL)
>> ORDER BY username
>> But this stored procedure returns no records when %% is supplied in the
>> two argumants...
>> set ANSI_NULLS ON
>> set QUOTED_IDENTIFIER ON
>> GO
>> ALTER PROCEDURE [dbo].[GetUnapprovedUsers]
>> (
>> @.branch nchar(4),
>> @.bcstring nvarchar(20)
>> )
>> AS
>> SET NOCOUNT ON;
>> SELECT username, approle, billingcustomer, emailredundant,
>> groupadmin, branch, nakey, companyadmin, bcstring, dateapproved
>> FROM users
>> WHERE (branch LIKE @.branch) AND (bcstring LIKE @.bcstring) AND
>> (dateapproved IS NULL)
>> ORDER BY username
>> Anyone know why?
>> Thanks,
>> Gary
>|||> My example stored procedure and plain sql are totally equivilent - one
> works the other does not.
These are not equivalent and that is why you get different results. Let me
try to explain another way.
The select statement works because you are passing only wildcards (exactly 2
percent signs). This is almost the same as no WHERE clause except that NULL
values will be excluded.
The stored procedure is different because the @.branch parameter is declared
as fixed length of 4. When you pass 2 percent signs, the actual value used
in the LIKE expression is the 2 percent signs plus 2 blanks ('%% '). This
means that you will only find branches that end in 2 blanks. I think you
will get results you expect if you declare the parameter as nvarchar(4)
instead of nchar(4).
As I mentioned earlier, although 2 leading percent signs will work, the
second is superfluous.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"GaryDean" <gdeanblakely@.newsgroup.nospam> wrote in message
news:uXs7YfFhIHA.1184@.TK2MSFTNGP04.phx.gbl...
> Dan,
> you are not making sense to me. % means "any string of zero or more
> characters". WHERE branch LIKE '%%' is equivilent to not having the WHERE
> clause at all. % is not a "wild card character." My example stored
> procedure and plain sql are totally equivilent - one works the other does
> not.
> Gary
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:DD64A043-B281-4ED9-8770-101A9EDC93C2@.microsoft.com...
>> @.branch nchar(4),
>> Note that nchar is fixed length so:
>> GetUnapprovedUsers
>> @.branch = N'%%'
>> @.bcstring = N'%%'
>> Is equivalent to:
>> GetUnapprovedUsers
>> @.branch = N'%% '
>> @.bcstring = N'%%'
>> You won't get any matches unless you have branches with spaces. I'm not
>> sure I understand why you specify 2 wildcard characters.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> http://weblogs.sqlteam.com/dang/
>> "GaryDean" <gdeanblakely@.newsgroup.nospam> wrote in message
>> news:%23lvBQm9gIHA.3352@.TK2MSFTNGP04.phx.gbl...
>>I Can't seem to get LIKE to work in a stored procedure. For instance
>>this sql works in a query...
>> SELECT username, approle, billingcustomer, emailredundant,
>> groupadmin, branch, nakey, companyadmin, bcstring, dateapproved
>> FROM users
>> WHERE (branch LIKE '%%') AND (bcstring LIKE '%%') AND (dateapproved
>> IS NULL)
>> ORDER BY username
>> But this stored procedure returns no records when %% is supplied in the
>> two argumants...
>> set ANSI_NULLS ON
>> set QUOTED_IDENTIFIER ON
>> GO
>> ALTER PROCEDURE [dbo].[GetUnapprovedUsers]
>> (
>> @.branch nchar(4),
>> @.bcstring nvarchar(20)
>> )
>> AS
>> SET NOCOUNT ON;
>> SELECT username, approle, billingcustomer, emailredundant,
>> groupadmin, branch, nakey, companyadmin, bcstring, dateapproved
>> FROM users
>> WHERE (branch LIKE @.branch) AND (bcstring LIKE @.bcstring) AND
>> (dateapproved IS NULL)
>> ORDER BY username
>> Anyone know why?
>> Thanks,
>> Gary
>>
>|||Yes, that was the problem. Thanks for the help.
Gary
www.deanblakely.com
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:4D2F5517-B34F-4742-8763-AC46A34CE0FD@.microsoft.com...
>> My example stored procedure and plain sql are totally equivilent - one
>> works the other does not.
> These are not equivalent and that is why you get different results. Let
> me try to explain another way.
> The select statement works because you are passing only wildcards (exactly
> 2 percent signs). This is almost the same as no WHERE clause except that
> NULL values will be excluded.
> The stored procedure is different because the @.branch parameter is
> declared as fixed length of 4. When you pass 2 percent signs, the actual
> value used in the LIKE expression is the 2 percent signs plus 2 blanks
> ('%% '). This means that you will only find branches that end in 2
> blanks. I think you will get results you expect if you declare the
> parameter as nvarchar(4) instead of nchar(4).
> As I mentioned earlier, although 2 leading percent signs will work, the
> second is superfluous.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
> "GaryDean" <gdeanblakely@.newsgroup.nospam> wrote in message
> news:uXs7YfFhIHA.1184@.TK2MSFTNGP04.phx.gbl...
>> Dan,
>> you are not making sense to me. % means "any string of zero or more
>> characters". WHERE branch LIKE '%%' is equivilent to not having the
>> WHERE clause at all. % is not a "wild card character." My example
>> stored procedure and plain sql are totally equivilent - one works the
>> other does not.
>> Gary
>> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
>> news:DD64A043-B281-4ED9-8770-101A9EDC93C2@.microsoft.com...
>> @.branch nchar(4),
>> Note that nchar is fixed length so:
>> GetUnapprovedUsers
>> @.branch = N'%%'
>> @.bcstring = N'%%'
>> Is equivalent to:
>> GetUnapprovedUsers
>> @.branch = N'%% '
>> @.bcstring = N'%%'
>> You won't get any matches unless you have branches with spaces. I'm not
>> sure I understand why you specify 2 wildcard characters.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> http://weblogs.sqlteam.com/dang/
>> "GaryDean" <gdeanblakely@.newsgroup.nospam> wrote in message
>> news:%23lvBQm9gIHA.3352@.TK2MSFTNGP04.phx.gbl...
>>I Can't seem to get LIKE to work in a stored procedure. For instance
>>this sql works in a query...
>> SELECT username, approle, billingcustomer, emailredundant,
>> groupadmin, branch, nakey, companyadmin, bcstring, dateapproved
>> FROM users
>> WHERE (branch LIKE '%%') AND (bcstring LIKE '%%') AND (dateapproved
>> IS NULL)
>> ORDER BY username
>> But this stored procedure returns no records when %% is supplied in the
>> two argumants...
>> set ANSI_NULLS ON
>> set QUOTED_IDENTIFIER ON
>> GO
>> ALTER PROCEDURE [dbo].[GetUnapprovedUsers]
>> (
>> @.branch nchar(4),
>> @.bcstring nvarchar(20)
>> )
>> AS
>> SET NOCOUNT ON;
>> SELECT username, approle, billingcustomer, emailredundant,
>> groupadmin, branch, nakey, companyadmin, bcstring, dateapproved
>> FROM users
>> WHERE (branch LIKE @.branch) AND (bcstring LIKE @.bcstring) AND
>> (dateapproved IS NULL)
>> ORDER BY username
>> Anyone know why?
>> Thanks,
>> Gary
>>
>>
>

Problem with install if machine.config username and password are stored in registry

Hi,
For information.
It looks like the SQL Reporting Services install doesn't work properly
if you have followed Microsoft's advice and have stored the server's
.net credentials (in the machine.config <processModel> element) in the
registry using aspnet_setreg. (You would do this to remove the plain
text entries from the machine.config file).
I have tried this several times and each time, the install just reads
the username and password entries as text, and can't deal with the
fact that these entries are actually pointing to the registry for the
credentials.
When you get to the Service Account screen on the install wizard, the
section entitled "The Report Server Web service will run under the
following account:" shows the following:
registy:HKLM\SOFTWARE\...\ASPNET_SETREG,userName
(or whatever the location is to your encrypted machine.config
settings).
The install starts after you've completed all of the screens in the
wizard, but then can't start the ReportServer web service
(unsurprisingly!).
The only way that I can see to get round this is to change the
machine.config processModel section before the SQL Reporting Services
install so that the userName and password entries are present in
plaintext in the file. This isn't great - particularly if your
entries have been stored in the registry because it's impossible to
find out what the password is if you haven't got a note of it (and
maybe only the Security team were allowed to have the password, etc.).
It also means that there's a possibility that .net things will stop
working on your server while you're doing the install (ie. just
because you don't really want to have to change those entries, for
risk of typos, etc.).
I hope these comments are useful.
Regards,
Rich WFor info, the same is true for when you install SQL Reporting Services
SP1 - ie. you have to change the machine.config processModel username
and password attributes so that they are not being read from the
registry.
Regards,
Rich