Accepting Null values in Trigger

The trigger below was automatically generated when I transferred my data from
Access to SQL. Right now it needs to find a match between
Dressing.Dressing_ID and inserted.Day_Dressing_ID, that's great EXCEPT I want
to put in an exception to say insert if Dressing.Dressing_ID =
inserted.Day_Dressing_ID or inserted.Day_Dressing_ID is null. I can't get
the syntax right. Any thoughts?
/* * PREVENT INSERTS IF NO MATCHING KEY IN 'Dressing' */
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM Dressing, inserted WHERE (Dressing.Dressing_ID =
inserted.Day_Dressing_ID))
BEGIN
RAISERROR 44447 'The record can''t be added or changed. Referential
integrity rules require a related record in table ''Dressing''.'
ROLLBACK TRANSACTION
END
You mean that : ?
SELECT COUNT(*) FROM Dressing, inserted WHERE (Dressing.Dressing_ID =
> inserted.Day_Dressing_ID OR inserted.Day_Dressing_ID is null)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Can" <Can@.discussions.microsoft.com> schrieb im Newsbeitrag
news:E19D9F15-A048-404F-9610-6826BF9E61D6@.microsoft.com...
> The trigger below was automatically generated when I transferred my data
> from
> Access to SQL. Right now it needs to find a match between
> Dressing.Dressing_ID and inserted.Day_Dressing_ID, that's great EXCEPT I
> want
> to put in an exception to say insert if Dressing.Dressing_ID =
> inserted.Day_Dressing_ID or inserted.Day_Dressing_ID is null. I can't get
> the syntax right. Any thoughts?
> /* * PREVENT INSERTS IF NO MATCHING KEY IN 'Dressing' */
> IF (SELECT COUNT(*) FROM inserted) !=
> (SELECT COUNT(*) FROM Dressing, inserted WHERE (Dressing.Dressing_ID =
> inserted.Day_Dressing_ID))
> BEGIN
> RAISERROR 44447 'The record can''t be added or changed. Referential
> integrity rules require a related record in table ''Dressing''.'
> ROLLBACK TRANSACTION
> END
>
|||Try,
/* * PREVENT INSERTS IF NO MATCHING KEY IN 'Dressing' */
IF (SELECT COUNT(*) FROM inserted where Day_Dressing_ID is not null) !=
(SELECT COUNT(*) FROM Dressing, inserted WHERE (Dressing.Dressing_ID =
inserted.Day_Dressing_ID))
BEGIN
RAISERROR 44447 'The record can''t be added or changed. Referential
integrity rules require a related record in table ''Dressing''.'
ROLLBACK TRANSACTION
END
AMB
"Can" wrote:

> The trigger below was automatically generated when I transferred my data from
> Access to SQL. Right now it needs to find a match between
> Dressing.Dressing_ID and inserted.Day_Dressing_ID, that's great EXCEPT I want
> to put in an exception to say insert if Dressing.Dressing_ID =
> inserted.Day_Dressing_ID or inserted.Day_Dressing_ID is null. I can't get
> the syntax right. Any thoughts?
> /* * PREVENT INSERTS IF NO MATCHING KEY IN 'Dressing' */
> IF (SELECT COUNT(*) FROM inserted) !=
> (SELECT COUNT(*) FROM Dressing, inserted WHERE (Dressing.Dressing_ID =
> inserted.Day_Dressing_ID))
> BEGIN
> RAISERROR 44447 'The record can''t be added or changed. Referential
> integrity rules require a related record in table ''Dressing''.'
> ROLLBACK TRANSACTION
> END
>
|||Jens,
[vbcol=seagreen]
This condition is saying to join a row from table Dressing to a row in table
inserted if the column Dressing_ID are equal in both tables or
inserted.Day_Dressing_ID is null, so if Dressing.Dressing_ID = 1 and
inserted.Dressing_ID is null those rows are joined.
This will not give you what you expect, see an example.
use northwind
go
create table t1 (
c1 int unique
)
go
create table t2 (
c1 int null
)
go
insert into t1 values(1)
insert into t1 values(2)
insert into t1 values(3)
go
insert into t2 values(null)
insert into t2 values(2)
go
select *
from
t1
inner join
t2
on t1.c1 = t2.c1
or t2.c1 is null
go
drop table t1, t2
go
AMB
"Jens Sü?meyer" wrote:

> You mean that : ?
> SELECT COUNT(*) FROM Dressing, inserted WHERE (Dressing.Dressing_ID =
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Can" <Can@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:E19D9F15-A048-404F-9610-6826BF9E61D6@.microsoft.com...
>
>
|||Why not use a FOREIGN KEY to enforce referential integrity?
David Portas
SQL Server MVP
|||Good question!!!
AMB
"David Portas" wrote:

> Why not use a FOREIGN KEY to enforce referential integrity?
> --
> David Portas
> SQL Server MVP
> --
>
>

Accepting Null values in Trigger

The trigger below was automatically generated when I transferred my data fro
m
Access to SQL. Right now it needs to find a match between
Dressing.Dressing_ID and inserted.Day_Dressing_ID, that's great EXCEPT I wan
t
to put in an exception to say insert if Dressing.Dressing_ID =
inserted.Day_Dressing_ID or inserted.Day_Dressing_ID is null. I can't get
the syntax right. Any thoughts?
/* * PREVENT INSERTS IF NO MATCHING KEY IN 'Dressing' */
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM Dressing, inserted WHERE (Dressing.Dressing_ID =
inserted.Day_Dressing_ID))
BEGIN
RAISERROR 44447 'The record can''t be added or changed. Referential
integrity rules require a related record in table ''Dressing''.'
ROLLBACK TRANSACTION
ENDYou mean that : ?
SELECT COUNT(*) FROM Dressing, inserted WHERE (Dressing.Dressing_ID =
> inserted.Day_Dressing_ID OR inserted.Day_Dressing_ID is null)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Can" <Can@.discussions.microsoft.com> schrieb im Newsbeitrag
news:E19D9F15-A048-404F-9610-6826BF9E61D6@.microsoft.com...
> The trigger below was automatically generated when I transferred my data
> from
> Access to SQL. Right now it needs to find a match between
> Dressing.Dressing_ID and inserted.Day_Dressing_ID, that's great EXCEPT I
> want
> to put in an exception to say insert if Dressing.Dressing_ID =
> inserted.Day_Dressing_ID or inserted.Day_Dressing_ID is null. I can't get
> the syntax right. Any thoughts?
> /* * PREVENT INSERTS IF NO MATCHING KEY IN 'Dressing' */
> IF (SELECT COUNT(*) FROM inserted) !=
> (SELECT COUNT(*) FROM Dressing, inserted WHERE (Dressing.Dressing_ID =
> inserted.Day_Dressing_ID))
> BEGIN
> RAISERROR 44447 'The record can''t be added or changed. Referential
> integrity rules require a related record in table ''Dressing''.'
> ROLLBACK TRANSACTION
> END
>|||Try,
/* * PREVENT INSERTS IF NO MATCHING KEY IN 'Dressing' */
IF (SELECT COUNT(*) FROM inserted where Day_Dressing_ID is not null) !=
(SELECT COUNT(*) FROM Dressing, inserted WHERE (Dressing.Dressing_ID =
inserted.Day_Dressing_ID))
BEGIN
RAISERROR 44447 'The record can''t be added or changed. Referential
integrity rules require a related record in table ''Dressing''.'
ROLLBACK TRANSACTION
END
AMB
"Can" wrote:

> The trigger below was automatically generated when I transferred my data f
rom
> Access to SQL. Right now it needs to find a match between
> Dressing.Dressing_ID and inserted.Day_Dressing_ID, that's great EXCEPT I w
ant
> to put in an exception to say insert if Dressing.Dressing_ID =
> inserted.Day_Dressing_ID or inserted.Day_Dressing_ID is null. I can't get
> the syntax right. Any thoughts?
> /* * PREVENT INSERTS IF NO MATCHING KEY IN 'Dressing' */
> IF (SELECT COUNT(*) FROM inserted) !=
> (SELECT COUNT(*) FROM Dressing, inserted WHERE (Dressing.Dressing_ID =
> inserted.Day_Dressing_ID))
> BEGIN
> RAISERROR 44447 'The record can''t be added or changed. Referentia
l
> integrity rules require a related record in table ''Dressing''.'
> ROLLBACK TRANSACTION
> END
>|||Jens,

This condition is saying to join a row from table Dressing to a row in table
inserted if the column Dressing_ID are equal in both tables or
inserted.Day_Dressing_ID is null, so if Dressing.Dressing_ID = 1 and
inserted.Dressing_ID is null those rows are joined.
This will not give you what you expect, see an example.
use northwind
go
create table t1 (
c1 int unique
)
go
create table t2 (
c1 int null
)
go
insert into t1 values(1)
insert into t1 values(2)
insert into t1 values(3)
go
insert into t2 values(null)
insert into t2 values(2)
go
select *
from
t1
inner join
t2
on t1.c1 = t2.c1
or t2.c1 is null
go
drop table t1, t2
go
AMB
"Jens Sü?meyer" wrote:
[vbcol=seagreen]
> You mean that : ?
> SELECT COUNT(*) FROM Dressing, inserted WHERE (Dressing.Dressing_ID =
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Can" <Can@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:E19D9F15-A048-404F-9610-6826BF9E61D6@.microsoft.com...
>
>|||Why not use a FOREIGN KEY to enforce referential integrity?
David Portas
SQL Server MVP
--|||Good question!!!
AMB
"David Portas" wrote:

> Why not use a FOREIGN KEY to enforce referential integrity?
> --
> David Portas
> SQL Server MVP
> --
>
>

Accepting Null values in Trigger

The trigger below was automatically generated when I transferred my data from
Access to SQL. Right now it needs to find a match between
Dressing.Dressing_ID and inserted.Day_Dressing_ID, that's great EXCEPT I want
to put in an exception to say insert if Dressing.Dressing_ID = inserted.Day_Dressing_ID or inserted.Day_Dressing_ID is null. I can't get
the syntax right. Any thoughts?
/* * PREVENT INSERTS IF NO MATCHING KEY IN 'Dressing' */
IF (SELECT COUNT(*) FROM inserted) != (SELECT COUNT(*) FROM Dressing, inserted WHERE (Dressing.Dressing_ID = inserted.Day_Dressing_ID))
BEGIN
RAISERROR 44447 'The record can''t be added or changed. Referential
integrity rules require a related record in table ''Dressing''.'
ROLLBACK TRANSACTION
ENDYou mean that : ?
SELECT COUNT(*) FROM Dressing, inserted WHERE (Dressing.Dressing_ID => inserted.Day_Dressing_ID OR inserted.Day_Dressing_ID is null)
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Can" <Can@.discussions.microsoft.com> schrieb im Newsbeitrag
news:E19D9F15-A048-404F-9610-6826BF9E61D6@.microsoft.com...
> The trigger below was automatically generated when I transferred my data
> from
> Access to SQL. Right now it needs to find a match between
> Dressing.Dressing_ID and inserted.Day_Dressing_ID, that's great EXCEPT I
> want
> to put in an exception to say insert if Dressing.Dressing_ID => inserted.Day_Dressing_ID or inserted.Day_Dressing_ID is null. I can't get
> the syntax right. Any thoughts?
> /* * PREVENT INSERTS IF NO MATCHING KEY IN 'Dressing' */
> IF (SELECT COUNT(*) FROM inserted) !=> (SELECT COUNT(*) FROM Dressing, inserted WHERE (Dressing.Dressing_ID => inserted.Day_Dressing_ID))
> BEGIN
> RAISERROR 44447 'The record can''t be added or changed. Referential
> integrity rules require a related record in table ''Dressing''.'
> ROLLBACK TRANSACTION
> END
>|||Try,
/* * PREVENT INSERTS IF NO MATCHING KEY IN 'Dressing' */
IF (SELECT COUNT(*) FROM inserted where Day_Dressing_ID is not null) != (SELECT COUNT(*) FROM Dressing, inserted WHERE (Dressing.Dressing_ID =inserted.Day_Dressing_ID))
BEGIN
RAISERROR 44447 'The record can''t be added or changed. Referential
integrity rules require a related record in table ''Dressing''.'
ROLLBACK TRANSACTION
END
AMB
"Can" wrote:
> The trigger below was automatically generated when I transferred my data from
> Access to SQL. Right now it needs to find a match between
> Dressing.Dressing_ID and inserted.Day_Dressing_ID, that's great EXCEPT I want
> to put in an exception to say insert if Dressing.Dressing_ID => inserted.Day_Dressing_ID or inserted.Day_Dressing_ID is null. I can't get
> the syntax right. Any thoughts?
> /* * PREVENT INSERTS IF NO MATCHING KEY IN 'Dressing' */
> IF (SELECT COUNT(*) FROM inserted) !=> (SELECT COUNT(*) FROM Dressing, inserted WHERE (Dressing.Dressing_ID => inserted.Day_Dressing_ID))
> BEGIN
> RAISERROR 44447 'The record can''t be added or changed. Referential
> integrity rules require a related record in table ''Dressing''.'
> ROLLBACK TRANSACTION
> END
>|||Jens,
> > inserted.Day_Dressing_ID OR inserted.Day_Dressing_ID is null)
This condition is saying to join a row from table Dressing to a row in table
inserted if the column Dressing_ID are equal in both tables or
inserted.Day_Dressing_ID is null, so if Dressing.Dressing_ID = 1 and
inserted.Dressing_ID is null those rows are joined.
This will not give you what you expect, see an example.
use northwind
go
create table t1 (
c1 int unique
)
go
create table t2 (
c1 int null
)
go
insert into t1 values(1)
insert into t1 values(2)
insert into t1 values(3)
go
insert into t2 values(null)
insert into t2 values(2)
go
select *
from
t1
inner join
t2
on t1.c1 = t2.c1
or t2.c1 is null
go
drop table t1, t2
go
AMB
"Jens Sü�meyer" wrote:
> You mean that : ?
> SELECT COUNT(*) FROM Dressing, inserted WHERE (Dressing.Dressing_ID => > inserted.Day_Dressing_ID OR inserted.Day_Dressing_ID is null)
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Can" <Can@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:E19D9F15-A048-404F-9610-6826BF9E61D6@.microsoft.com...
> > The trigger below was automatically generated when I transferred my data
> > from
> > Access to SQL. Right now it needs to find a match between
> > Dressing.Dressing_ID and inserted.Day_Dressing_ID, that's great EXCEPT I
> > want
> > to put in an exception to say insert if Dressing.Dressing_ID => > inserted.Day_Dressing_ID or inserted.Day_Dressing_ID is null. I can't get
> > the syntax right. Any thoughts?
> >
> > /* * PREVENT INSERTS IF NO MATCHING KEY IN 'Dressing' */
> > IF (SELECT COUNT(*) FROM inserted) !=> > (SELECT COUNT(*) FROM Dressing, inserted WHERE (Dressing.Dressing_ID => > inserted.Day_Dressing_ID))
> > BEGIN
> > RAISERROR 44447 'The record can''t be added or changed. Referential
> > integrity rules require a related record in table ''Dressing''.'
> > ROLLBACK TRANSACTION
> > END
> >
> >
>
>|||Why not use a FOREIGN KEY to enforce referential integrity?
--
David Portas
SQL Server MVP
--|||Good question!!!
AMB
"David Portas" wrote:
> Why not use a FOREIGN KEY to enforce referential integrity?
> --
> David Portas
> SQL Server MVP
> --
>
>

accepting multiple values in data-driven subscription

I have set up a data-driven subscription based off an SQL table. It works in
all cases except when I am trying to select all or multiple values for a
parameter. Does anyone know how this needs to be set up in the SQL table so
that the report will accept multiple values?
ThanksHi Marie,
I encountered the same problem. Did you get any solution yet from other
forums?
Thanks,
Rares
"Marie M." wrote:
> I have set up a data-driven subscription based off an SQL table. It works in
> all cases except when I am trying to select all or multiple values for a
> parameter. Does anyone know how this needs to be set up in the SQL table so
> that the report will accept multiple values?
> Thanks

Accept Multiple Values

Hi ,
Is it possible to declare a variable to accept mutiple values at a time ?
for example :
Declare @.Country char(100)
Set @.country = 'TH','MY'
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:DF03833A-4A65-4969-A661-D98DCB859489@.microsoft.com...
> Hi ,
> Is it possible to declare a variable to accept mutiple values at a time
> ?
> for example :
> Declare @.Country char(100)
> Set @.country = 'TH','MY'
>
No arrays in SQL Server. What are you trying to do? Maybe this will help:
http://www.sommarskog.se/arrays-in-sql.html
David Portas
SQL Server MVP
|||Do not think there are arrays you can use fixed length , delimited or
XML
Regards ,
C#, VB.NET , SQL SERVER , UML , DESIGN Patterns Interview question book
http://www.geocities.com/dotnetinterviews/
My Interview Blog
http://spaces.msn.com/members/dotnetinterviews/

Accept Multiple Values

Hi ,
Is it possible to declare a variable to accept mutiple values at a time ?
for example :
Declare @.Country char(100)
Set @.country = 'TH','MY'"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:DF03833A-4A65-4969-A661-D98DCB859489@.microsoft.com...
> Hi ,
> Is it possible to declare a variable to accept mutiple values at a time
> ?
> for example :
> Declare @.Country char(100)
> Set @.country = 'TH','MY'
>
No arrays in SQL Server. What are you trying to do? Maybe this will help:
http://www.sommarskog.se/arrays-in-sql.html
--
David Portas
SQL Server MVP
--|||Do not think there are arrays you can use fixed length , delimited or
XML
--
Regards ,
C#, VB.NET , SQL SERVER , UML , DESIGN Patterns Interview question book
http://www.geocities.com/dotnetinterviews/
My Interview Blog
http://spaces.msn.com/members/dotnetinterviews/

Accept Multiple Values

Hi ,
Is it possible to declare a variable to accept mutiple values at a time ?
for example :
Declare @.Country char(100)
Set @.country = 'TH','MY'"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:DF03833A-4A65-4969-A661-D98DCB859489@.microsoft.com...
> Hi ,
> Is it possible to declare a variable to accept mutiple values at a time
> ?
> for example :
> Declare @.Country char(100)
> Set @.country = 'TH','MY'
>
No arrays in SQL Server. What are you trying to do? Maybe this will help:
http://www.sommarskog.se/arrays-in-sql.html
David Portas
SQL Server MVP
--|||Do not think there are arrays you can use fixed length , delimited or
XML
--
Regards ,
C#, VB.NET , SQL SERVER , UML , DESIGN Patterns Interview question book
http://www.geocities.com/dotnetinterviews/
My Interview Blog
http://spaces.msn.com/members/dotnetinterviews/

abt RTRIM/LTRIM ,Null values

Hi
can any one solve my problem; i could not get solution for following
query
I have two tables like tblOrg and tblorgchartreport
so i need to update tblorg for newly inserted rows with
tblorgchartreport, suppose there are firstname, lastname
so i need to update these fields in tblorg by triming two fields like
i need to get firstname + lastname from tblorgchartreport into
firstname of tblorg. so i have few null values in lastname fields .
so can any one tell me how to solve avoid null values while using RTRIM
or LTRIM when inserting values
waiting for reply thank you in advance
*** Sent via Developersdex http://www.codecomments.com ***
Hi
You can use this way:
SELECT LTRIM(RTRIM(ISNULL(FirstName,''))) + LTRIM(RTRIM(ISNULL(LastName,'')))
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
"krishna nellutla" wrote:

> Hi
> can any one solve my problem; i could not get solution for following
> query
> I have two tables like tblOrg and tblorgchartreport
> so i need to update tblorg for newly inserted rows with
> tblorgchartreport, suppose there are firstname, lastname
> so i need to update these fields in tblorg by triming two fields like
> i need to get firstname + lastname from tblorgchartreport into
> firstname of tblorg. so i have few null values in lastname fields .
> so can any one tell me how to solve avoid null values while using RTRIM
> or LTRIM when inserting values
> waiting for reply thank you in advance
>
> *** Sent via Developersdex http://www.codecomments.com ***
>
|||Does this help?
use tempdb
go
create table names (f varchar(30), l varchar(30))
go
insert names (f,l) values ('krishna','nellutla')
insert names (f,l) values ('mark','allison')
insert names (f,l) values ('ben',null)
set concat_null_yields_null off
select rtrim(l) + ', ' + ltrim(f) from names
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
krishna nellutla wrote:
> Hi
> can any one solve my problem; i could not get solution for following
> query
> I have two tables like tblOrg and tblorgchartreport
> so i need to update tblorg for newly inserted rows with
> tblorgchartreport, suppose there are firstname, lastname
> so i need to update these fields in tblorg by triming two fields like
> i need to get firstname + lastname from tblorgchartreport into
> firstname of tblorg. so i have few null values in lastname fields .
> so can any one tell me how to solve avoid null values while using RTRIM
> or LTRIM when inserting values
> waiting for reply thank you in advance
>
> *** Sent via Developersdex http://www.codecomments.com ***

abt RTRIM/LTRIM ,Null values

Hi
can any one solve my problem; i could not get solution for following
query
I have two tables like tblOrg and tblorgchartreport
so i need to update tblorg for newly inserted rows with
tblorgchartreport, suppose there are firstname, lastname
so i need to update these fields in tblorg by triming two fields like
i need to get firstname + lastname from tblorgchartreport into
firstname of tblorg. so i have few null values in lastname fields .
so can any one tell me how to solve avoid null values while using RTRIM
or LTRIM when inserting values
waiting for reply thank you in advance
*** Sent via Developersdex http://www.developersdex.com ***Hi
You can use this way:
SELECT LTRIM(RTRIM(ISNULL(FirstName,''))) + LTRIM(RTRIM(ISNULL(LastName,'')))
--
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"krishna nellutla" wrote:
> Hi
> can any one solve my problem; i could not get solution for following
> query
> I have two tables like tblOrg and tblorgchartreport
> so i need to update tblorg for newly inserted rows with
> tblorgchartreport, suppose there are firstname, lastname
> so i need to update these fields in tblorg by triming two fields like
> i need to get firstname + lastname from tblorgchartreport into
> firstname of tblorg. so i have few null values in lastname fields .
> so can any one tell me how to solve avoid null values while using RTRIM
> or LTRIM when inserting values
> waiting for reply thank you in advance
>
> *** Sent via Developersdex http://www.developersdex.com ***
>|||Does this help?
use tempdb
go
create table names (f varchar(30), l varchar(30))
go
insert names (f,l) values ('krishna','nellutla')
insert names (f,l) values ('mark','allison')
insert names (f,l) values ('ben',null)
set concat_null_yields_null off
select rtrim(l) + ', ' + ltrim(f) from names
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
krishna nellutla wrote:
> Hi
> can any one solve my problem; i could not get solution for following
> query
> I have two tables like tblOrg and tblorgchartreport
> so i need to update tblorg for newly inserted rows with
> tblorgchartreport, suppose there are firstname, lastname
> so i need to update these fields in tblorg by triming two fields like
> i need to get firstname + lastname from tblorgchartreport into
> firstname of tblorg. so i have few null values in lastname fields .
> so can any one tell me how to solve avoid null values while using RTRIM
> or LTRIM when inserting values
> waiting for reply thank you in advance
>
> *** Sent via Developersdex http://www.developersdex.com ***

abt RTRIM/LTRIM ,Null values

Hi
can any one solve my problem; i could not get solution for following
query
I have two tables like tblOrg and tblorgchartreport
so i need to update tblorg for newly inserted rows with
tblorgchartreport, suppose there are firstname, lastname
so i need to update these fields in tblorg by triming two fields like
i need to get firstname + lastname from tblorgchartreport into
firstname of tblorg. so i have few null values in lastname fields .
so can any one tell me how to solve avoid null values while using RTRIM
or LTRIM when inserting values
waiting for reply thank you in advance
*** Sent via Developersdex http://www.codecomments.com ***Hi
You can use this way:
SELECT LTRIM(RTRIM(ISNULL(FirstName,''))) + LTRIM(RTRIM(ISNULL(LastName,''))
)
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"krishna nellutla" wrote:

> Hi
> can any one solve my problem; i could not get solution for following
> query
> I have two tables like tblOrg and tblorgchartreport
> so i need to update tblorg for newly inserted rows with
> tblorgchartreport, suppose there are firstname, lastname
> so i need to update these fields in tblorg by triming two fields like
> i need to get firstname + lastname from tblorgchartreport into
> firstname of tblorg. so i have few null values in lastname fields .
> so can any one tell me how to solve avoid null values while using RTRIM
> or LTRIM when inserting values
> waiting for reply thank you in advance
>
> *** Sent via Developersdex http://www.codecomments.com ***
>|||Does this help?
use tempdb
go
create table names (f varchar(30), l varchar(30))
go
insert names (f,l) values ('krishna','nellutla')
insert names (f,l) values ('mark','allison')
insert names (f,l) values ('ben',null)
set concat_null_yields_null off
select rtrim(l) + ', ' + ltrim(f) from names
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
krishna nellutla wrote:
> Hi
> can any one solve my problem; i could not get solution for following
> query
> I have two tables like tblOrg and tblorgchartreport
> so i need to update tblorg for newly inserted rows with
> tblorgchartreport, suppose there are firstname, lastname
> so i need to update these fields in tblorg by triming two fields like
> i need to get firstname + lastname from tblorgchartreport into
> firstname of tblorg. so i have few null values in lastname fields .
> so can any one tell me how to solve avoid null values while using RTRIM
> or LTRIM when inserting values
> waiting for reply thank you in advance
>
> *** Sent via Developersdex http://www.codecomments.com ***

About Identity Column

Dear all,
I use the identity property that makes generating unique numeric
values in my table "OrderDetail". Its primary keys include the column
"OrderHeaderNo" and the identity column. It's fine up to now. But I wonder
that if the application keep going to use for many years. Is it possible the
identity value will be overflowed? or it will restart from 1 again
automatically after the overflow. And now, i make this column type is "Int".
Anyone can help? Thanks.
Best Rdgs
EllisYou'll get an overflow when the identity value exceeds the maximum value for
the data type If this is a possibility in your situation, consider using a
data type with a larger maximum value, such as bigint or decimal.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ellis Yu" <ellis.yu@.transfield.com> wrote in message
news:ecrdWi1LFHA.904@.tk2msftngp13.phx.gbl...
> Dear all,
> I use the identity property that makes generating unique numeric
> values in my table "OrderDetail". Its primary keys include the column
> "OrderHeaderNo" and the identity column. It's fine up to now. But I wonder
> that if the application keep going to use for many years. Is it possible
> the
> identity value will be overflowed? or it will restart from 1 again
> automatically after the overflow. And now, i make this column type is
> "Int".
> Anyone can help? Thanks.
> Best Rdgs
> Ellis
>|||Many Thanks !! Otherwise I'll be in big trouble when the system get
overflow. But now, I can I solve this problem now? and I'm using int data
type, what's its maximum value of it?
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:O7k81B2LFHA.732@.TK2MSFTNGP12.phx.gbl...
> You'll get an overflow when the identity value exceeds the maximum value
for
> the data type If this is a possibility in your situation, consider using
a
> data type with a larger maximum value, such as bigint or decimal.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ellis Yu" <ellis.yu@.transfield.com> wrote in message
> news:ecrdWi1LFHA.904@.tk2msftngp13.phx.gbl...
> > Dear all,
> >
> > I use the identity property that makes generating unique numeric
> > values in my table "OrderDetail". Its primary keys include the column
> > "OrderHeaderNo" and the identity column. It's fine up to now. But I
wonder
> > that if the application keep going to use for many years. Is it possible
> > the
> > identity value will be overflowed? or it will restart from 1 again
> > automatically after the overflow. And now, i make this column type is
> > "Int".
> > Anyone can help? Thanks.
> >
> > Best Rdgs
> > Ellis
> >
> >
>|||Hello Ellis,
from books online:
Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1
(2,147,483,647). Storage size is 4 bytes. The SQL-92 synonym for int is
integer.
Regards,
Tomislav Kralj
"Ellis Yu" <ellis.yu@.transfield.com> wrote in message
news:OX2T1V2LFHA.2888@.TK2MSFTNGP12.phx.gbl...
> Many Thanks !! Otherwise I'll be in big trouble when the system get
> overflow. But now, I can I solve this problem now? and I'm using int data
> type, what's its maximum value of it?
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:O7k81B2LFHA.732@.TK2MSFTNGP12.phx.gbl...
>> You'll get an overflow when the identity value exceeds the maximum value
> for
>> the data type If this is a possibility in your situation, consider using
> a
>> data type with a larger maximum value, such as bigint or decimal.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Ellis Yu" <ellis.yu@.transfield.com> wrote in message
>> news:ecrdWi1LFHA.904@.tk2msftngp13.phx.gbl...
>> > Dear all,
>> >
>> > I use the identity property that makes generating unique numeric
>> > values in my table "OrderDetail". Its primary keys include the column
>> > "OrderHeaderNo" and the identity column. It's fine up to now. But I
> wonder
>> > that if the application keep going to use for many years. Is it
>> > possible
>> > the
>> > identity value will be overflowed? or it will restart from 1 again
>> > automatically after the overflow. And now, i make this column type is
>> > "Int".
>> > Anyone can help? Thanks.
>> >
>> > Best Rdgs
>> > Ellis
>> >
>> >
>>
>|||Which means that if your IDENTITY is set with the default (1,1), you can
store one row per second for the next 68 years. Just to put things in
perspective :)
--
Jacco Schalkwijk
SQL Server MVP
"Tomislav Kralj" <tomislav.kralj1@.zg.htnet.hr> wrote in message
news:eqOb%23t3LFHA.3852@.tk2msftngp13.phx.gbl...
> Hello Ellis,
> from books online:
> Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1
> (2,147,483,647). Storage size is 4 bytes. The SQL-92 synonym for int is
> integer.
>
> Regards,
> Tomislav Kralj
> "Ellis Yu" <ellis.yu@.transfield.com> wrote in message
> news:OX2T1V2LFHA.2888@.TK2MSFTNGP12.phx.gbl...
>> Many Thanks !! Otherwise I'll be in big trouble when the system get
>> overflow. But now, I can I solve this problem now? and I'm using int data
>> type, what's its maximum value of it?
>>
>> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
>> news:O7k81B2LFHA.732@.TK2MSFTNGP12.phx.gbl...
>> You'll get an overflow when the identity value exceeds the maximum value
>> for
>> the data type If this is a possibility in your situation, consider
>> using
>> a
>> data type with a larger maximum value, such as bigint or decimal.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Ellis Yu" <ellis.yu@.transfield.com> wrote in message
>> news:ecrdWi1LFHA.904@.tk2msftngp13.phx.gbl...
>> > Dear all,
>> >
>> > I use the identity property that makes generating unique
>> > numeric
>> > values in my table "OrderDetail". Its primary keys include the column
>> > "OrderHeaderNo" and the identity column. It's fine up to now. But I
>> wonder
>> > that if the application keep going to use for many years. Is it
>> > possible
>> > the
>> > identity value will be overflowed? or it will restart from 1 again
>> > automatically after the overflow. And now, i make this column type is
>> > "Int".
>> > Anyone can help? Thanks.
>> >
>> > Best Rdgs
>> > Ellis
>> >
>> >
>>
>>
>|||Or with bigint, you can store one mullion rows per second for 1142 years
:-)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:e2g%23QR5LFHA.568@.TK2MSFTNGP09.phx.gbl...
> Which means that if your IDENTITY is set with the default (1,1), you can
> store one row per second for the next 68 years. Just to put things in
> perspective :)
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Tomislav Kralj" <tomislav.kralj1@.zg.htnet.hr> wrote in message
> news:eqOb%23t3LFHA.3852@.tk2msftngp13.phx.gbl...
>> Hello Ellis,
>> from books online:
>> Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1
>> (2,147,483,647). Storage size is 4 bytes. The SQL-92 synonym for int is
>> integer.
>>
>> Regards,
>> Tomislav Kralj
>> "Ellis Yu" <ellis.yu@.transfield.com> wrote in message
>> news:OX2T1V2LFHA.2888@.TK2MSFTNGP12.phx.gbl...
>> Many Thanks !! Otherwise I'll be in big trouble when the system get
>> overflow. But now, I can I solve this problem now? and I'm using int
>> data
>> type, what's its maximum value of it?
>>
>> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
>> news:O7k81B2LFHA.732@.TK2MSFTNGP12.phx.gbl...
>> You'll get an overflow when the identity value exceeds the maximum
>> value
>> for
>> the data type If this is a possibility in your situation, consider
>> using
>> a
>> data type with a larger maximum value, such as bigint or decimal.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Ellis Yu" <ellis.yu@.transfield.com> wrote in message
>> news:ecrdWi1LFHA.904@.tk2msftngp13.phx.gbl...
>> > Dear all,
>> >
>> > I use the identity property that makes generating unique
>> > numeric
>> > values in my table "OrderDetail". Its primary keys include the column
>> > "OrderHeaderNo" and the identity column. It's fine up to now. But I
>> wonder
>> > that if the application keep going to use for many years. Is it
>> > possible
>> > the
>> > identity value will be overflowed? or it will restart from 1 again
>> > automatically after the overflow. And now, i make this column type is
>> > "Int".
>> > Anyone can help? Thanks.
>> >
>> > Best Rdgs
>> > Ellis
>> >
>> >
>>
>>
>>
>

About E-mail delivery Settings, where can I set E-mail account and password

Hello, everyone,
During RS setup, we can only set two values about E-mail delivery, one is
SMTP server address or name, the other is Sender E-mail address.
My question is , now the popular SMTP server all require Authentication,
where can I set E-mail account and password?
Thanks in advance.
EdwardRS only supports anonymous and NTLM authentication. If you need to use basic
auth your best option is to set RS to use the local smtp server to relay the
messages. In the RSReportServer.config file, set the smtppickupdirectory to
you local smtp pickup directory and then set SendUsing to 1.
You will then need to configure the local smtp server to do the actual
relaying.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Haitian" <xinhaitian@.126.com> wrote in message
news:%23WZRXrPWFHA.3076@.TK2MSFTNGP12.phx.gbl...
> Hello, everyone,
> During RS setup, we can only set two values about E-mail delivery, one
> is
> SMTP server address or name, the other is Sender E-mail address.
> My question is , now the popular SMTP server all require Authentication,
> where can I set E-mail account and password?
> Thanks in advance.
> Edward
>|||Thanks, Daniel.
BTW, do you mean to use the SMTP service built in IIS ?
> RS only supports anonymous and NTLM authentication. If you need to use
basic
> auth your best option is to set RS to use the local smtp server to relay
the
> messages. In the RSReportServer.config file, set the smtppickupdirectory
to
> you local smtp pickup directory and then set SendUsing to 1.
> You will then need to configure the local smtp server to do the actual
> relaying.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Haitian" <xinhaitian@.126.com> wrote in message
> news:%23WZRXrPWFHA.3076@.TK2MSFTNGP12.phx.gbl...
> > Hello, everyone,
> >
> > During RS setup, we can only set two values about E-mail delivery, one
> > is
> > SMTP server address or name, the other is Sender E-mail address.
> >
> > My question is , now the popular SMTP server all require
Authentication,
> > where can I set E-mail account and password?
> >
> > Thanks in advance.
> >
> > Edward
> >
> >
>|||Yes, that is what I was referring to.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Haitian" <xinhaitian@.126.com> wrote in message
news:OfK9IDfWFHA.3188@.TK2MSFTNGP09.phx.gbl...
> Thanks, Daniel.
> BTW, do you mean to use the SMTP service built in IIS ?
>
>> RS only supports anonymous and NTLM authentication. If you need to use
> basic
>> auth your best option is to set RS to use the local smtp server to relay
> the
>> messages. In the RSReportServer.config file, set the smtppickupdirectory
> to
>> you local smtp pickup directory and then set SendUsing to 1.
>> You will then need to configure the local smtp server to do the actual
>> relaying.
>> --
>> -Daniel
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>>
>> "Haitian" <xinhaitian@.126.com> wrote in message
>> news:%23WZRXrPWFHA.3076@.TK2MSFTNGP12.phx.gbl...
>> > Hello, everyone,
>> >
>> > During RS setup, we can only set two values about E-mail delivery,
>> > one
>> > is
>> > SMTP server address or name, the other is Sender E-mail address.
>> >
>> > My question is , now the popular SMTP server all require
> Authentication,
>> > where can I set E-mail account and password?
>> >
>> > Thanks in advance.
>> >
>> > Edward
>> >
>> >
>>
>

About Disconnected Record Sets

Hi,
I am using disconnected ADO record sets while using an Access DB for
storing
my intermediate values in processing. I had initially used a server side
cursor, however it gave a bad performance while inserting a record one at a
time.
Now using a client side cursor gave a decent performance while inserting
records, but while finding records within the record set, I get deteriorated
performance which far overrides the benefits I receive during record
inserts.
I am using 'Find' while Deleting and updating records within the
recordset. My understanding is the Find will check the disconnected set for
the record that matches the Find criteria and fetches the record from the
server if no record matching the criteria is found. Is this assumption
right?
Any ideas why the performance is bad while using disconnected record
sets for Find? If anything, it should be as bad as it was while using server
side cursor because at that point, I used to fire a SELECT query everytime.
Thanks in advance,
MadhuHi
Does this posting by Bob help?
http://tinyurl.com/gkrv4
John
"Madz" <noemail@.none.net> wrote in message
news:eLXqL4sYGHA.3704@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I am using disconnected ADO record sets while using an Access DB for
> storing
> my intermediate values in processing. I had initially used a server side
> cursor, however it gave a bad performance while inserting a record one at
> a
> time.
> Now using a client side cursor gave a decent performance while
> inserting
> records, but while finding records within the record set, I get
> deteriorated
> performance which far overrides the benefits I receive during record
> inserts.
> I am using 'Find' while Deleting and updating records within the
> recordset. My understanding is the Find will check the disconnected set
> for
> the record that matches the Find criteria and fetches the record from the
> server if no record matching the criteria is found. Is this assumption
> right?
> Any ideas why the performance is bad while using disconnected record
> sets for Find? If anything, it should be as bad as it was while using
> server
> side cursor because at that point, I used to fire a SELECT query
> everytime.
> Thanks in advance,
> Madhu
>
>|||Hi,
I am not using disconnected record sets per se. I am using a client side
cursor. I have set the the cursorlocation property to use client side
cursor.
Let me briefly explain wherein I am facing these problems. I set the
cursorlocation and the maxrows properties on the recordset and open the
recordset with a "SELECT * FROM Table". Now, while looping through the
recordset, I do not see any benefits of using a client side cursor in terms
of time i.e. it takes as much time as it did while using a server located
cursor. I got around my other performance deteriorations by using command
executes for inserts and deletes.
Why does looping thru' the recordset in this case take the same amount
of time as a server located cursor?
Thanks,
Madhu
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:%23J1hv4tYGHA.2376@.TK2MSFTNGP03.phx.gbl...
> Hi
> Does this posting by Bob help?
> http://tinyurl.com/gkrv4
> John
> "Madz" <noemail@.none.net> wrote in message
> news:eLXqL4sYGHA.3704@.TK2MSFTNGP03.phx.gbl...
>|||Hi
Without knowing exact details of your systems it may be hard to tell why
but...using SELECT * may mean you are returning unnecessary data back to
your client. The quality of the client system may effect the response times.
If you are continually using find then it could mean that you need to order
the recordset better! I would expect MoveNext to be quicker than find. Do
you always movefirst before the find, in which case it could be slower.
John
"Madz" <noemail@.none.net> wrote in message
news:OhHzB06YGHA.4580@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I am not using disconnected record sets per se. I am using a client
> side cursor. I have set the the cursorlocation property to use client side
> cursor.
> Let me briefly explain wherein I am facing these problems. I set the
> cursorlocation and the maxrows properties on the recordset and open the
> recordset with a "SELECT * FROM Table". Now, while looping through the
> recordset, I do not see any benefits of using a client side cursor in
> terms of time i.e. it takes as much time as it did while using a server
> located cursor. I got around my other performance deteriorations by using
> command executes for inserts and deletes.
> Why does looping thru' the recordset in this case take the same amount
> of time as a server located cursor?
> Thanks,
> Madhu
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:%23J1hv4tYGHA.2376@.TK2MSFTNGP03.phx.gbl...
>|||Hi John,
I am no longer using Find method. Let me explain :-)...
I had 2 connections open to two different databases. One of them
required serial access to data while the other needed random. I was
initially using the Find method in both the cases but was well advised
against it because I read in forums that recordset method of updating data
is ill served against using command object.
The database that needed random access to data has now been modified to
use command object thru'out [and hence, I have no issues with this] while
the one requiring serial access to data uses client side cursor for obvious
reasons. Now my question :-)...
Why does using client side cursor still not tremendously improve my
response time? I see that all the records are dragged into my process even
though I have set MaxRows property on the client side cursor. Even with
this, I see that traversing thru a recordset of about 20000 records takes
about 2-3 minutes. I am using Access 2000 as my database. I hope I have been
clear!
Thanks,
Madhu
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:e0jVwP7YGHA.4060@.TK2MSFTNGP02.phx.gbl...
> Hi
> Without knowing exact details of your systems it may be hard to tell why
> but...using SELECT * may mean you are returning unnecessary data back to
> your client. The quality of the client system may effect the response
> times. If you are continually using find then it could mean that you need
> to order the recordset better! I would expect MoveNext to be quicker than
> find. Do you always movefirst before the find, in which case it could be
> slower.
> John
>
> "Madz" <noemail@.none.net> wrote in message
> news:OhHzB06YGHA.4580@.TK2MSFTNGP03.phx.gbl...
>|||Hi
Once all the recordset has been retrieved locally I would also expect moving
through the recordset to be quicker, therefore I can't say why in this
instance it is not. If you were using SQL Server you could tell when the
server was being accessed i.e. you were re-fetching the data, but I do know
know how to tell this for Access. The only suggestion I can put forward is
to check that you have a suitable amount of free space on the system, make
sure that your disc is not fragmented and use perfmon to check that you
don't have any resource bottlenecks.
John
"Madz" <noemail@.none.net> wrote in message
news:%23Vn%23Li7YGHA.2208@.TK2MSFTNGP03.phx.gbl...
> Hi John,
> I am no longer using Find method. Let me explain :-)...
> I had 2 connections open to two different databases. One of them
> required serial access to data while the other needed random. I was
> initially using the Find method in both the cases but was well advised
> against it because I read in forums that recordset method of updating data
> is ill served against using command object.
> The database that needed random access to data has now been modified to
> use command object thru'out [and hence, I have no issues with this] while
> the one requiring serial access to data uses client side cursor for
> obvious reasons. Now my question :-)...
> Why does using client side cursor still not tremendously improve my
> response time? I see that all the records are dragged into my process even
> though I have set MaxRows property on the client side cursor. Even with
> this, I see that traversing thru a recordset of about 20000 records takes
> about 2-3 minutes. I am using Access 2000 as my database. I hope I have
> been clear!
> Thanks,
> Madhu
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:e0jVwP7YGHA.4060@.TK2MSFTNGP02.phx.gbl...
>