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 - DBAAre 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|||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|||Here is basic query minus quotes and spaces etc.
select password + name from sysxlogins
Tim - DBA
"John Bell" wrote:
[vbcol=seagreen]
> 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 somethin
g
> 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:
>|||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:
[vbcol=seagreen]
> Here is basic query minus quotes and spaces etc.
> select password + name from sysxlogins
>
> --
> Tim - DBA
>
> "John Bell" wrote:
>sqlsql