Concatenate problem.

Using SQL Server 2000 and trying to generate sql:
sp_password null, 'password', 'name'
selecting from sysxlogins but cannot get past error msg 'Invalid operator
for data type. Operator equals add, type equals nvarchar. I have tried
convert and cast and am aware of precedence order but to no avail. This
should be simple I thought.
Any help would be appreciated.
--
Tim - DBAHi
You example is exactly the same as the example in BOL. Conversion from
varchar to a sysname should be implicit. Therefore it is probably something
else that is giving the error message such as an unescaped apostrophy.
You could try adding an exec in front of each procedure call and printing
out the statements you are executing so that you can run them in Query
Analyser.
John
"Tim" wrote:
> Using SQL Server 2000 and trying to generate sql:
> sp_password null, 'password', 'name'
> selecting from sysxlogins but cannot get past error msg 'Invalid operator
> for data type. Operator equals add, type equals nvarchar. I have tried
> convert and cast and am aware of precedence order but to no avail. This
> should be simple I thought.
> Any help would be appreciated.
> --
> Tim - DBA|||Are you going to show us the query you were using?
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Tim" <Tim@.discussions.microsoft.com> wrote in message
news:49B44438-7868-4B95-8B6C-54D9B6FE6AE5@.microsoft.com...
> Using SQL Server 2000 and trying to generate sql:
> sp_password null, 'password', 'name'
> selecting from sysxlogins but cannot get past error msg 'Invalid operator
> for data type. Operator equals add, type equals nvarchar. I have tried
> convert and cast and am aware of precedence order but to no avail. This
> should be simple I thought.
> Any help would be appreciated.
> --
> Tim - DBA|||Here is basic query minus quotes and spaces etc.
select password + name from sysxlogins
Tim - DBA
"John Bell" wrote:
> Hi
> You example is exactly the same as the example in BOL. Conversion from
> varchar to a sysname should be implicit. Therefore it is probably something
> else that is giving the error message such as an unescaped apostrophy.
> You could try adding an exec in front of each procedure call and printing
> out the statements you are executing so that you can run them in Query
> Analyser.
> John
>
> "Tim" wrote:
> > Using SQL Server 2000 and trying to generate sql:
> > sp_password null, 'password', 'name'
> > selecting from sysxlogins but cannot get past error msg 'Invalid operator
> > for data type. Operator equals add, type equals nvarchar. I have tried
> > convert and cast and am aware of precedence order but to no avail. This
> > should be simple I thought.
> > Any help would be appreciated.
> >
> > --
> > Tim - DBA|||Hi
Try the following (untested)
DECLARE @.cmd nvarchar(200), @.ParmDefinition nvarchar(200)
DECLARE @.password sysname, @.newpassword sysname, @.name sysname
SET @.ParmDefinition = N'@.pwd sysname, @.newpwd sysname, @.loginnm sysname'
SET @.cmd = 'sp_password @.old = @.pwd, @.new = @.newpwd, @.loginname = @.loginnm'
SET @.newpassword = 'unsecure'
SELECT @.password = password, @.name = name
FROM sysxlogins
WHERE name = 'mylogin'
EXEC sp_executesql @.stmt = @.cmd , @.params = @.ParmDefinition, @.pwd =@.password, @.newpwd = @.newpassword, @.loginnm = @.name
If you want to do multiple rows from sysclogins you will need to use a
cursor and call sp_executesql for each iteration.
John
"Tim" wrote:
> Here is basic query minus quotes and spaces etc.
> select password + name from sysxlogins
>
> --
> Tim - DBA
>
> "John Bell" wrote:
> > Hi
> >
> > You example is exactly the same as the example in BOL. Conversion from
> > varchar to a sysname should be implicit. Therefore it is probably something
> > else that is giving the error message such as an unescaped apostrophy.
> >
> > You could try adding an exec in front of each procedure call and printing
> > out the statements you are executing so that you can run them in Query
> > Analyser.
> >
> > John
> >
> >
> >
> > "Tim" wrote:
> >
> > > Using SQL Server 2000 and trying to generate sql:
> > > sp_password null, 'password', 'name'
> > > selecting from sysxlogins but cannot get past error msg 'Invalid operator
> > > for data type. Operator equals add, type equals nvarchar. I have tried
> > > convert and cast and am aware of precedence order but to no avail. This
> > > should be simple I thought.
> > > Any help would be appreciated.
> > >
> > > --
> > > Tim - DBA

Complex queries using WHERE and mix of OR and AND

How do you effectively mix OR and AND together? I have the query below for m
y
SEARCH page. I would like the users to have the option of selecting one fiel
d
to search with OR selecting pairs of fields together to search the database
with. The problem is, with the SQL statement below, OR works (select 1 field
to search with) but AND does not (if I use more than 1 field to search with,
the search returns all entries in the database.
Could someone pls point out to me what I'm doing wrong? I'd really
appreciate it.
SELECT vNXX, vLN, vMN, vDT, vYR, vAGNT, vORD, vSAVE
FROM salesdb
WHERE (vNXX LIKE 'varNXX' AND vLN LIKE 'varLINE') OR (vMN LIKE 'varMONTH'
AND vAGNT LIKE 'varAGENT'AND vYR LIKE 'varYEAR' AND vSAVE LIKE 'varSAVE') OR
(vMN LIKE 'varMONTH' AND vYR LIKE 'varYEAR' AND vSAVE LIKE 'varSAVE') OR (vM
N
LIKE 'varMONTH' AND vYR LIKE 'varYEAR') OR (vNXX LIKE 'varNXX') OR (vLN LIKE
'varLINE') OR (vMN LIKE 'varMONTH') OR (vDT LIKE 'varDATE') OR (vYR LIKE
'varYEAR') OR (vAGNT LIKE 'varAGENT') OR (vORD LIKE 'varORD') OR (vSAVE LIKE
'varSAVE')
ORDER BY vMN DESC, vDT DESC, vYR DESC> AND does not (if I use more than 1 field to search with,
> the search returns all entries in the database.
Could you post a working example of this so that we can understand what you
mean. There's no reason why you can't use as many ANDs and ORs as you need
in a WHERE clause. AND takes precedence over OR unless you use brackets to
alter the order of evaluation.
David Portas
SQL Server MVP
--|||Hi David,
First I formatted your SQL using www.sqlinform.com .
Then I have seen that some conditions are not logic, e.g. using
(
vNXX LIKE 'varNXX'
AND vLN LIKE 'varLINE'
)
together with
(
vNXX LIKE 'varNXX'
)
because this condition is true independent from the value of vLN. You
will need to code your SQL in a different way.
Regards
Guido
SELECT vNXX, vLN, vMN, vDT, vYR, vAGNT, vORD, vSAVE
FROM salesdb
WHERE
(
vNXX LIKE 'varNXX'
AND vLN LIKE 'varLINE'
)
OR
(
vMN LIKE 'varMONTH'
AND vAGNT LIKE 'varAGENT'
AND vYR LIKE 'varYEAR'
AND vSAVE LIKE 'varSAVE'
)
OR
(
vMN LIKE 'varMONTH'
AND vYR LIKE 'varYEAR'
AND vSAVE LIKE 'varSAVE'
)
OR
(
vMN LIKE 'varMONTH'
AND vYR LIKE 'varYEAR'
)
OR
(
vNXX LIKE 'varNXX'
)
OR
(
vLN LIKE 'varLINE'
)
OR
(
vMN LIKE 'varMONTH'
)
OR
(
vDT LIKE 'varDATE'
)
OR
(
vYR LIKE 'varYEAR'
)
OR
(
vAGNT LIKE 'varAGENT'
)
OR
(
vORD LIKE 'varORD'
)
OR
(
vSAVE LIKE 'varSAVE'
)
ORDER BY vMN DESC, vDT DESC, vYR DESC