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

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

Concatenate Input Columns

Hi,

In my data flow taks, The Source data is coming from AS400 has 4 columns,

I need to achieve the followings and require your help.

1. Generate a new column which will be combination of concating these 4 columns.

2. Need to add an extra row for Header & Footer.

Please Help.

Concatenation is achieved using the Derived Column component.

Adding your own header and footer rows is a bit more difficult because they need to have the same metadata as the data row. For this reason, concatenate all columns together so as to make a single, very wide, column. You can then use the UNION ALL component to put your header, data and footer together. The header and footer will probably be created using a source script component - though I'll leave that up to you.

-Jamie

|||

Re. Regarding Derived Column component

Source Columns

Col1 smallint, Col2 smallint, Col3 Decimal(12,4), Col4 Decimal(16,4)

Data will be the new derived column

Data = @.[User::TimeStamp] + "D" + Col1 + Col2 + Col3 + Col4

I need to know how to use Cast Operators as Data is DT_STR and Col1 & Col2 are smallint.

Thanks

|||

Look in the top right hand corner of the Derived Column UI. All the type cast operators are in there.

They are also all in BOL which you obviously haven't looked at:

http://msdn2.microsoft.com/en-us/library/ms141260.aspx

http://msdn2.microsoft.com/en-us/library/ms141704.aspx

BOL should ALWAYS be your first port of call. Not this forum!

-Jamie

|||

I have created two source script component one for Header and one for Footer. I am using OLE DB source for the detail records. While doing the union All, It just put the detail first and then Header and Footer.

Although Union All is setup like that

Union All Input 1 is Header

Union All Input 2 is detail and

Union All Input 3 is Footer

How can I make sure they go by order (Header,Detail,Footer).

Thanks again for your help

|||

Ahhh..I didn't think about that. Sorry. There is no way to guarantee the order.

I've thought of another way actually. Use a single script component transform to add the header and footer. It will have to be an asynchronous component.

Sorry for putting you on the wrong path.

-Jamie