In Oracle, there is something called dbms_application_info
where developers can set some application information for
a current session and that piece of information can be
retrieved or updated within the same session. Is there
something similar in SQL Server? Thanks."Peter" <pchow@.ureach.com> wrote in message
news:06b201c36061$a50fdbc0$a301280a@.phx.gbl...
> In Oracle, there is something called dbms_application_info
> where developers can set some application information for
> a current session and that piece of information can be
> retrieved or updated within the same session. Is there
> something similar in SQL Server?
Package variables do that much better, BTW.
The closest SQLServer has is
SET CONTEXT_INFO
You can store 128 bytes of binary data on the session.
The data just goes into master.dbo.sysprocesses.context_info
This is also the only way to store data outside of the current transaction
context.
David|||I found it a lot easier to use a permanent table with information relevant
to the spid than to use CONTEXT_INFO (but that was also caused by our
particular requirements), see the code below.
CREATE TABLE session_role(
spid INT NOT NULL,
role_id INT NOT NULL
CONSTRAINT PK_session_role PRIMARY KEY CLUSTERED (spid),
CONSTRAINT FK_session_role_role FOREIGN KEY (role_id)
REFERENCES role_definitions (id))
GO
CREATE PROCEDURE SetSessionRole @.nRoleID INT
AS
SET NOCOUNT ON
IF EXISTS(SELECT * FROM session_role WHERE spid = @.@.spid)
UPDATE session_role
SET role_id = @.nRoleID
WHERE spid = @.@.spid
ELSE
INSERT INTO session_role (spid, role_id)
VALUES (@.@.spid, @.nRoleID)
GO
CREATE PROCEDURE GetSessionRole @.nRoleID INT OUPUT
AS
SET NOCOUNT ON
SET @.nRoleID = (SELECT role_id FROM session_role WHERE spid = @.@.spid)
GO
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:Obu0gMGYDHA.2200@.TK2MSFTNGP09.phx.gbl...
> "Peter" <pchow@.ureach.com> wrote in message
> news:06b201c36061$a50fdbc0$a301280a@.phx.gbl...
> > In Oracle, there is something called dbms_application_info
> > where developers can set some application information for
> > a current session and that piece of information can be
> > retrieved or updated within the same session. Is there
> > something similar in SQL Server?
> Package variables do that much better, BTW.
> The closest SQLServer has is
> SET CONTEXT_INFO
> You can store 128 bytes of binary data on the session.
> The data just goes into master.dbo.sysprocesses.context_info
> This is also the only way to store data outside of the current transaction
> context.
> David
>|||"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:OJ4rYXNYDHA.1492@.TK2MSFTNGP12.phx.gbl...
> I found it a lot easier to use a permanent table with information relevant
> to the spid than to use CONTEXT_INFO (but that was also caused by our
> particular requirements), see the code below.
>
The caviat here is that this session table can become a trouble spot for
locking in the application. Two otherwise unrelated transactions can
serialize or even deadlock because they involve reads and writes to this
table.
David|||Unlikely, as there is an index and a primary key on the spid column, and no
two transactions can access the same row because they have different spids.
The only situation I can think of when blocking might occur is when a new
row is inserted, as it will then take out a range lock on the index, and the
spids next to it won't be available. This won't happen very often and can
even be avoided by prepopulating the table with an appropriate range of
spids.
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:uOn4EGOYDHA.2648@.TK2MSFTNGP09.phx.gbl...
> "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> news:OJ4rYXNYDHA.1492@.TK2MSFTNGP12.phx.gbl...
> > I found it a lot easier to use a permanent table with information
relevant
> > to the spid than to use CONTEXT_INFO (but that was also caused by our
> > particular requirements), see the code below.
> >
> The caviat here is that this session table can become a trouble spot for
> locking in the application. Two otherwise unrelated transactions can
> serialize or even deadlock because they involve reads and writes to this
> table.
> David
>|||"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:OwGlUbOYDHA.888@.TK2MSFTNGP10.phx.gbl...
> Unlikely, as there is an index and a primary key on the spid column, and
no
> two transactions can access the same row because they have different
spids.
> The only situation I can think of when blocking might occur is when a new
> row is inserted, as it will then take out a range lock on the index, and
the
> spids next to it won't be available. This won't happen very often and can
> even be avoided by prepopulating the table with an appropriate range of
> spids.
>
As written, the EXISTS check in SetSessionRole will require a shared read
lock on every row in session_role. This will block if another transaction
has run SetSessionRole inside an open transaction. And if SetSessionRole is
run in SERIALIZABLE isolation, these shared read locks will be held until
the end of that transaction.
It's probably not a big deal, but it's a big difference from Oracle package
variables, and it's something to keep an eye on.
David|||Hi David,
The EXISTS check can be run WITH(NOLOCK) and then there will be no blocking,
and every process will only lock the row with it's own spid. Thanks for
pointing out the problem.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
IF EXISTS(SELECT NULL FROM session_role WITH(NOLOCK) WHERE spid = @.@.spid )
UPDATE session_role SET role_id = 1 WHERE spid = @.@.spid
ELSE
INSERT INTO session_role (spid, role_id) VALUES(@.@.spid, 1)
-- COMMIT TRAN
The whole thing of working with spids is a bit dubious by the way if you
work with connection pooling. You have the same connection but it doesn't
have to be the same user, in which case it might be better to keep the
information on the middle tier.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23GUoJlOYDHA.1736@.TK2MSFTNGP10.phx.gbl...
> "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> news:OwGlUbOYDHA.888@.TK2MSFTNGP10.phx.gbl...
> > Unlikely, as there is an index and a primary key on the spid column, and
> no
> > two transactions can access the same row because they have different
> spids.
> > The only situation I can think of when blocking might occur is when a
new
> > row is inserted, as it will then take out a range lock on the index, and
> the
> > spids next to it won't be available. This won't happen very often and
can
> > even be avoided by prepopulating the table with an appropriate range of
> > spids.
> >
> As written, the EXISTS check in SetSessionRole will require a shared read
> lock on every row in session_role. This will block if another transaction
> has run SetSessionRole inside an open transaction. And if SetSessionRole
is
> run in SERIALIZABLE isolation, these shared read locks will be held until
> the end of that transaction.
> It's probably not a big deal, but it's a big difference from Oracle
package
> variables, and it's something to keep an eye on.
> David
>
Showing posts with label comparison. Show all posts
Showing posts with label comparison. Show all posts
Sunday, February 12, 2012
Comparison with multiple tables
HELP!!!!
I am using SQL in Access and need to pull all of the records that don't
match in the key field. The key fields are the same name in both tables and
I
have built a relationship on a different field. Both tables have some
matching records and some non matching. I want all of the records from both
tables that do not match!SELECT a.<column list>, b.<column list>
FROM a
FULL OUTER JOIN b
ON a.<primary key> = b.<primary key>
WHERE a.<primary key> IS NULL OR b.<primary key> IS NULL
Jacco Schalkwijk
SQL Server MVP
"Tess9126" <Tess9126@.discussions.microsoft.com> wrote in message
news:40EB8FA3-B1C8-412A-BC20-4E3E273EDD72@.microsoft.com...
> HELP!!!!
> I am using SQL in Access and need to pull all of the records that don't
> match in the key field. The key fields are the same name in both tables
> and I
> have built a relationship on a different field. Both tables have some
> matching records and some non matching. I want all of the records from
> both
> tables that do not match!|||Try,
select *
from t1 full outer join t2
on t1.pk_col = t2.pk_col
where t1.pk_col is null or t2.pk_col is null
AMB
"Tess9126" wrote:
> HELP!!!!
> I am using SQL in Access and need to pull all of the records that don't
> match in the key field. The key fields are the same name in both tables an
d I
> have built a relationship on a different field. Both tables have some
> matching records and some non matching. I want all of the records from bot
h
> tables that do not match!|||Access did not like the word outer. I am at work with absolutely no referenc
e
material. I am familure with SQL but I am really stuck. I want to thank you
for trying to help me!
"Jacco Schalkwijk" wrote:
> SELECT a.<column list>, b.<column list>
> FROM a
> FULL OUTER JOIN b
> ON a.<primary key> = b.<primary key>
> WHERE a.<primary key> IS NULL OR b.<primary key> IS NULL
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Tess9126" <Tess9126@.discussions.microsoft.com> wrote in message
> news:40EB8FA3-B1C8-412A-BC20-4E3E273EDD72@.microsoft.com...
>
>|||Ah, Access. My Access SQL is rather rusty, so maybe the following will work:
SELECT a.<column list>, b.<column list>
FROM a
LEFT OUTER JOIN b
ON a.<primary key> = b.<primary key>
WHERE b.<primary key> IS NULL
UNION
SELECT a.<column list>, b.<column list>
FROM b
LEFT OUTER JOIN a
ON b.<primary key> = a.<primary key>
WHERE a.<primary key> IS NULL
If it doesn't work, the best thing to do is post your question on an Access
newsgroup.
Jacco Schalkwijk
SQL Server MVP
"Tess9126" <Tess9126@.discussions.microsoft.com> wrote in message
news:43F5BF5D-9B8C-401C-B1F7-136381102C7C@.microsoft.com...
> Access did not like the word outer. I am at work with absolutely no
> reference
> material. I am familure with SQL but I am really stuck. I want to thank
> you
> for trying to help me!
> "Jacco Schalkwijk" wrote:
>|||Then you will need a union all of two outer joins.
select t1.c1, ..., t1.cn
from t1 left join t2 on t1.pk_col = t2.pk_col
where t2.pk_col is null
union all
select t2.c1, ..., t2.cn
from t1 right join t2 on t1.pk_col = t2.pk_col
where t1.pk_col is null
AMB
"Tess9126" wrote:
> Access did not like the word outer. I am at work with absolutely no refere
nce
> material. I am familure with SQL but I am really stuck. I want to thank yo
u
> for trying to help me!
> "Jacco Schalkwijk" wrote:
>
I am using SQL in Access and need to pull all of the records that don't
match in the key field. The key fields are the same name in both tables and
I
have built a relationship on a different field. Both tables have some
matching records and some non matching. I want all of the records from both
tables that do not match!SELECT a.<column list>, b.<column list>
FROM a
FULL OUTER JOIN b
ON a.<primary key> = b.<primary key>
WHERE a.<primary key> IS NULL OR b.<primary key> IS NULL
Jacco Schalkwijk
SQL Server MVP
"Tess9126" <Tess9126@.discussions.microsoft.com> wrote in message
news:40EB8FA3-B1C8-412A-BC20-4E3E273EDD72@.microsoft.com...
> HELP!!!!
> I am using SQL in Access and need to pull all of the records that don't
> match in the key field. The key fields are the same name in both tables
> and I
> have built a relationship on a different field. Both tables have some
> matching records and some non matching. I want all of the records from
> both
> tables that do not match!|||Try,
select *
from t1 full outer join t2
on t1.pk_col = t2.pk_col
where t1.pk_col is null or t2.pk_col is null
AMB
"Tess9126" wrote:
> HELP!!!!
> I am using SQL in Access and need to pull all of the records that don't
> match in the key field. The key fields are the same name in both tables an
d I
> have built a relationship on a different field. Both tables have some
> matching records and some non matching. I want all of the records from bot
h
> tables that do not match!|||Access did not like the word outer. I am at work with absolutely no referenc
e
material. I am familure with SQL but I am really stuck. I want to thank you
for trying to help me!
"Jacco Schalkwijk" wrote:
> SELECT a.<column list>, b.<column list>
> FROM a
> FULL OUTER JOIN b
> ON a.<primary key> = b.<primary key>
> WHERE a.<primary key> IS NULL OR b.<primary key> IS NULL
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Tess9126" <Tess9126@.discussions.microsoft.com> wrote in message
> news:40EB8FA3-B1C8-412A-BC20-4E3E273EDD72@.microsoft.com...
>
>|||Ah, Access. My Access SQL is rather rusty, so maybe the following will work:
SELECT a.<column list>, b.<column list>
FROM a
LEFT OUTER JOIN b
ON a.<primary key> = b.<primary key>
WHERE b.<primary key> IS NULL
UNION
SELECT a.<column list>, b.<column list>
FROM b
LEFT OUTER JOIN a
ON b.<primary key> = a.<primary key>
WHERE a.<primary key> IS NULL
If it doesn't work, the best thing to do is post your question on an Access
newsgroup.
Jacco Schalkwijk
SQL Server MVP
"Tess9126" <Tess9126@.discussions.microsoft.com> wrote in message
news:43F5BF5D-9B8C-401C-B1F7-136381102C7C@.microsoft.com...
> Access did not like the word outer. I am at work with absolutely no
> reference
> material. I am familure with SQL but I am really stuck. I want to thank
> you
> for trying to help me!
> "Jacco Schalkwijk" wrote:
>|||Then you will need a union all of two outer joins.
select t1.c1, ..., t1.cn
from t1 left join t2 on t1.pk_col = t2.pk_col
where t2.pk_col is null
union all
select t2.c1, ..., t2.cn
from t1 right join t2 on t1.pk_col = t2.pk_col
where t1.pk_col is null
AMB
"Tess9126" wrote:
> Access did not like the word outer. I am at work with absolutely no refere
nce
> material. I am familure with SQL but I am really stuck. I want to thank yo
u
> for trying to help me!
> "Jacco Schalkwijk" wrote:
>
Comparison with Crystal Reports
I showed my boss the functionality of ad hoc reporting in SRSS and he likes
it. He asked me if we could completely move away from Crystal Reports (CR) to
SRSS.
Does anyone have any issues with SRSS that didnt exist in CR.
One question i have is programmability capabilities. Some time back i had to
make a very complex report in CR. I create a table in the dataset. Designed
the report to pull data from that table and the table was being populated
through some VB.NET code. So the users will open a GUI and hit RUN, which is
when i will populate the table and show the report. You can assume that
populating the table required complex programming and couldnt have been done
through user defined functions in a report.
I dont suppose stuff like this can be done in SRSS?RS is designed a lot more around creating the query or the stored procedure
as the source for the report. You can have multiple datasets in a report and
you can easily create 1-many reports using subreports. If you absolutely
must create the dataset via code you can do this either using the VS 2005
report control in local mode (no server, you had the control the report and
the dataset) or you can create a data process extension. Neither is
non-trivial. I have done very complex reports and have never flelt the need
for creating the dataset in code. All my reports either have the query or a
stored procedure with it.
The reports have parameters that the user selects (can be either from a list
(which can be filled by a query) or date control etc). The parameters can be
cascading. i.e the second parameter list depends on what is picked with the
first parameter. Also you can have multi-select parameters as well.
The only issue I have seen are people that have a set of business objects
coded or they want to code that way. You can do this but it is harder (data
processing extension).
HTH,
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Tk_Neo" <TkNeo@.discussions.microsoft.com> wrote in message
news:9DB999DE-15AF-4E6F-BAB9-AF0D71FAC886@.microsoft.com...
>I showed my boss the functionality of ad hoc reporting in SRSS and he likes
> it. He asked me if we could completely move away from Crystal Reports (CR)
> to
> SRSS.
> Does anyone have any issues with SRSS that didnt exist in CR.
> One question i have is programmability capabilities. Some time back i had
> to
> make a very complex report in CR. I create a table in the dataset.
> Designed
> the report to pull data from that table and the table was being populated
> through some VB.NET code. So the users will open a GUI and hit RUN, which
> is
> when i will populate the table and show the report. You can assume that
> populating the table required complex programming and couldnt have been
> done
> through user defined functions in a report.
> I dont suppose stuff like this can be done in SRSS?
it. He asked me if we could completely move away from Crystal Reports (CR) to
SRSS.
Does anyone have any issues with SRSS that didnt exist in CR.
One question i have is programmability capabilities. Some time back i had to
make a very complex report in CR. I create a table in the dataset. Designed
the report to pull data from that table and the table was being populated
through some VB.NET code. So the users will open a GUI and hit RUN, which is
when i will populate the table and show the report. You can assume that
populating the table required complex programming and couldnt have been done
through user defined functions in a report.
I dont suppose stuff like this can be done in SRSS?RS is designed a lot more around creating the query or the stored procedure
as the source for the report. You can have multiple datasets in a report and
you can easily create 1-many reports using subreports. If you absolutely
must create the dataset via code you can do this either using the VS 2005
report control in local mode (no server, you had the control the report and
the dataset) or you can create a data process extension. Neither is
non-trivial. I have done very complex reports and have never flelt the need
for creating the dataset in code. All my reports either have the query or a
stored procedure with it.
The reports have parameters that the user selects (can be either from a list
(which can be filled by a query) or date control etc). The parameters can be
cascading. i.e the second parameter list depends on what is picked with the
first parameter. Also you can have multi-select parameters as well.
The only issue I have seen are people that have a set of business objects
coded or they want to code that way. You can do this but it is harder (data
processing extension).
HTH,
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Tk_Neo" <TkNeo@.discussions.microsoft.com> wrote in message
news:9DB999DE-15AF-4E6F-BAB9-AF0D71FAC886@.microsoft.com...
>I showed my boss the functionality of ad hoc reporting in SRSS and he likes
> it. He asked me if we could completely move away from Crystal Reports (CR)
> to
> SRSS.
> Does anyone have any issues with SRSS that didnt exist in CR.
> One question i have is programmability capabilities. Some time back i had
> to
> make a very complex report in CR. I create a table in the dataset.
> Designed
> the report to pull data from that table and the table was being populated
> through some VB.NET code. So the users will open a GUI and hit RUN, which
> is
> when i will populate the table and show the report. You can assume that
> populating the table required complex programming and couldnt have been
> done
> through user defined functions in a report.
> I dont suppose stuff like this can be done in SRSS?
Comparison with Crystal Reports
I showed my boss the functionality of ad hoc reporting in SRSS and he likes it. He asked me if we could completely move away from Crystal Reports (CR) to SRSS.
Does anyone have any issues with SRSS that didnt exist in CR.
One question i have is programmability capabilities. Some time back i had to make a very complex report in CR. I create a table in the dataset. Designed the report to pull data from that table and the table was being populated through some VB.NET code. So the users will open a GUI and hit RUN, which is when i will populate the table and show the report. You can assume that populating the table required complex programming and couldnt have been done through user defined functions in a report.
I dont suppose stuff like this can be done in SRSS?
Depending on the version of Crystal you have, there are various differences, though it is a good path to move to in my opinion.
Here is one summary I have found outlining differences:
http://www.crystalreportsbook.com/SSRSandCR_ExecSummary.asp
There's lots of information out there on this.
It is also possible to use a dataset as the source of report data in a custom application with Reporting Services.
cheers,
Andrew
Comparison to NULL and '' fields
Guys, can anybody remind the settings responsible for the results of the
search by the NULL fields? I saw this database setting several months ago
but forgot this switch. The problem is the following. We're trying to run a
search in a few tables using some SQL query with a multilevel combination of
AND/OR with
WHERE SOMEFIELD LIKE '%SOMETEXT%'
If the field is NULL or '' (zero length string) then two possible results
are available depending on this database setting, true or false. The default
setting is so that if the field is NULL these records are excluded from my
result list but I need all these records to be included. So I need to find
this switch to get TRUE always when I compare to NULL strings to include the
records with NULL fields into the result list.
Thanks,
Dmitri.Do you mean
SET ANSI_NULLS
Specifies SQL-92 compliant behavior of the Equals (=) and Not Equal to (<> )
comparison operators when used with null values.
Jens SUessmeyer.
"Just D." <no@.spam.please> schrieb im Newsbeitrag
news:Ojtbe.70654$lz2.58655@.fed1read07...
> Guys, can anybody remind the settings responsible for the results of the
> search by the NULL fields? I saw this database setting several months ago
> but forgot this switch. The problem is the following. We're trying to run
> a search in a few tables using some SQL query with a multilevel
> combination of AND/OR with
> WHERE SOMEFIELD LIKE '%SOMETEXT%'
> If the field is NULL or '' (zero length string) then two possible results
> are available depending on this database setting, true or false. The
> default setting is so that if the field is NULL these records are excluded
> from my result list but I need all these records to be included. So I need
> to find this switch to get TRUE always when I compare to NULL strings to
> include the records with NULL fields into the result list.
> Thanks,
> Dmitri.
>|||I strongly recommend not to do this. Take a look at this interesting puzzle
and see the consequnces of setting ANSI_NULLS OFF.
http://groups-beta.google.com/group...aca91a7912bdc76
To accomplish what you want, use:
...
WHERE SOMEFIELD LIKE '%SOMETEXT%' or soemfield is null
AMB
"Just D." wrote:
> Guys, can anybody remind the settings responsible for the results of the
> search by the NULL fields? I saw this database setting several months ago
> but forgot this switch. The problem is the following. We're trying to run
a
> search in a few tables using some SQL query with a multilevel combination
of
> AND/OR with
> WHERE SOMEFIELD LIKE '%SOMETEXT%'
> If the field is NULL or '' (zero length string) then two possible results
> are available depending on this database setting, true or false. The defau
lt
> setting is so that if the field is NULL these records are excluded from my
> result list but I need all these records to be included. So I need to find
> this switch to get TRUE always when I compare to NULL strings to include t
he
> records with NULL fields into the result list.
> Thanks,
> Dmitri.
>
>|||Hi Jens,
Probably you're right. But if it works with LIKE ? Or only with <> = ?
Thanks,
Dmitri.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OIOIIenSFHA.2136@.TK2MSFTNGP14.phx.gbl...
> Do you mean
> SET ANSI_NULLS
> Specifies SQL-92 compliant behavior of the Equals (=) and Not Equal to
> (<> ) comparison operators when used with null values.
> Jens SUessmeyer.
> "Just D." <no@.spam.please> schrieb im Newsbeitrag
> news:Ojtbe.70654$lz2.58655@.fed1read07...
>|||I think it only changes the behavior of comparisons that include
at least one literal or variable that is NULL. Column-to-column
comparisons between NULLs are never true, regardless of the
setting. I think <column> LIKE NULL is never true also.
set ansi_nulls off
go
declare @.t table (
a int,
c char
)
insert into @.t values (null, null)
insert into @.t values (0, '0')
select * from @.t
where a = null
select * from @.t
where a = c
select * from @.t
where c like null
go
set ansi_nulls on
Steve Kass
Drew University
Just D. wrote:
>Hi Jens,
>Probably you're right. But if it works with LIKE ? Or only with <> = ?
>Thanks,
>Dmitri.
>"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
>message news:OIOIIenSFHA.2136@.TK2MSFTNGP14.phx.gbl...
>
>
>|||Hi Steve,
My parameters that I provide to SQL are never NULL, the string should look
like this:
SELECT * FROM blablabla WHERE Param1 LIKE '%Col1%' OR Param2 LIKE '%Col2%'
OR Param3 LIKE '%Col3%'
This is a very simplified string because actually it's a very long
combination of OR/ANDs and braces. The Param# are never NULL, but if the
value in the column is NULL then I lose this record from the result and this
is a problem. If I had some global setting it would be nice but if I don't
have this way then I need to evaluate all these cells manually and it will
be nightmare because i need to search in 20-30 fields in a few tables, the
tables are long enough and not necessarily all these value are set to
something instead of NULL.
Dmitri.
"Steve Kass" <skass@.drew.edu> wrote in message
news:uABhz7rSFHA.3312@.TK2MSFTNGP12.phx.gbl...
>I think it only changes the behavior of comparisons that include
> at least one literal or variable that is NULL. Column-to-column
> comparisons between NULLs are never true, regardless of the
> setting. I think <column> LIKE NULL is never true also.
> set ansi_nulls off
> go
> declare @.t table (
> a int,
> c char
> )
> insert into @.t values (null, null)
> insert into @.t values (0, '0')
> select * from @.t
> where a = null
> select * from @.t
> where a = c
> select * from @.t
> where c like null
> go
> set ansi_nulls on
> Steve Kass
> Drew University
> Just D. wrote:
>|||> My parameters that I provide to SQL are never NULL, the string should look
> like this:
>
> SELECT * FROM blablabla WHERE Param1 LIKE '%Col1%' OR Param2 LIKE '%Col2%'
OR
> Param3 LIKE '%Col3%'
> This is a very simplified string because actually it's a very long combina
tion
> of OR/ANDs and braces. The Param# are never NULL, but if the value in the
> column is NULL then I lose this record from the result and this is a problem.[/col
or]
As I see it, there are a couple of solutions. The first, most obvious soluti
on
is to populate those nulls with something (e.g. an empty string (ugh) ) and
set
the column to be non-nullable. Then you won't have to worry about null colum
n
values.
The other solution is to change your where clause to account for nulls like
so
(depending on the actual logic desired):
Select F1...Fn
From Table
Where Param1 Is Null
Or Param1 Like '%Col1%'
Or Param2 Is Null
Or Param2 Like '%Col2%'
Or Param3 Is Null
Or Param3 Like '%Col3%'
> If I had some global setting it would be nice but if I don't have this way
> then I need to evaluate all these cells manually and it will be nightmare
> because i need to search in 20-30 fields in a few tables, the tables are l
ong
> enough and not necessarily all these value are set to something instead of
> NULL.
I don't see that you have a whole lot of choices. It sounds like populating
those fields with something would be your easiest route. I would definitely
not
recommend playing with the ANSI_NULLS option. It will confuse the hell out o
f
any other developer that looks at your code. It is one of those unobvious
"gotchas" that developers hate.
Thomas|||Dmitri,
What you want isn't going to be found in a setting,
if you want NULL LIKE '%Col1%' to be true.
If you want rows where any column is null, along with
the once returned by the query below, try
WHERE COALESCE(Param1,'Col1') LIKE '%Col1%'
OR COALESCE(Param2,'Col2') LIKE '%Col2%'
OR COALESCE(Param3,'Col3') LIKE '%Col3%'
It seems very strange to have columns named ParamX where you
search for values called Col1, so if this is not what you want, it
will be best if you provide sample table data that includes rows
you want (matching LIKE), rows you want (because of NULLs)
and some rows you don't want, so you can explain and show us
what you want to retrieve.
SK
Just D. wrote:
>Hi Steve,
>My parameters that I provide to SQL are never NULL, the string should look
>like this:
>
>SELECT * FROM blablabla WHERE Param1 LIKE '%Col1%' OR Param2 LIKE '%Col2%'
>OR Param3 LIKE '%Col3%'
>This is a very simplified string because actually it's a very long
>combination of OR/ANDs and braces. The Param# are never NULL, but if the
>value in the column is NULL then I lose this record from the result and thi
s
>is a problem. If I had some global setting it would be nice but if I don't
>have this way then I need to evaluate all these cells manually and it will
>be nightmare because i need to search in 20-30 fields in a few tables, the
>tables are long enough and not necessarily all these value are set to
>something instead of NULL.
>Dmitri.
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:uABhz7rSFHA.3312@.TK2MSFTNGP12.phx.gbl...
>
>
>
search by the NULL fields? I saw this database setting several months ago
but forgot this switch. The problem is the following. We're trying to run a
search in a few tables using some SQL query with a multilevel combination of
AND/OR with
WHERE SOMEFIELD LIKE '%SOMETEXT%'
If the field is NULL or '' (zero length string) then two possible results
are available depending on this database setting, true or false. The default
setting is so that if the field is NULL these records are excluded from my
result list but I need all these records to be included. So I need to find
this switch to get TRUE always when I compare to NULL strings to include the
records with NULL fields into the result list.
Thanks,
Dmitri.Do you mean
SET ANSI_NULLS
Specifies SQL-92 compliant behavior of the Equals (=) and Not Equal to (<> )
comparison operators when used with null values.
Jens SUessmeyer.
"Just D." <no@.spam.please> schrieb im Newsbeitrag
news:Ojtbe.70654$lz2.58655@.fed1read07...
> Guys, can anybody remind the settings responsible for the results of the
> search by the NULL fields? I saw this database setting several months ago
> but forgot this switch. The problem is the following. We're trying to run
> a search in a few tables using some SQL query with a multilevel
> combination of AND/OR with
> WHERE SOMEFIELD LIKE '%SOMETEXT%'
> If the field is NULL or '' (zero length string) then two possible results
> are available depending on this database setting, true or false. The
> default setting is so that if the field is NULL these records are excluded
> from my result list but I need all these records to be included. So I need
> to find this switch to get TRUE always when I compare to NULL strings to
> include the records with NULL fields into the result list.
> Thanks,
> Dmitri.
>|||I strongly recommend not to do this. Take a look at this interesting puzzle
and see the consequnces of setting ANSI_NULLS OFF.
http://groups-beta.google.com/group...aca91a7912bdc76
To accomplish what you want, use:
...
WHERE SOMEFIELD LIKE '%SOMETEXT%' or soemfield is null
AMB
"Just D." wrote:
> Guys, can anybody remind the settings responsible for the results of the
> search by the NULL fields? I saw this database setting several months ago
> but forgot this switch. The problem is the following. We're trying to run
a
> search in a few tables using some SQL query with a multilevel combination
of
> AND/OR with
> WHERE SOMEFIELD LIKE '%SOMETEXT%'
> If the field is NULL or '' (zero length string) then two possible results
> are available depending on this database setting, true or false. The defau
lt
> setting is so that if the field is NULL these records are excluded from my
> result list but I need all these records to be included. So I need to find
> this switch to get TRUE always when I compare to NULL strings to include t
he
> records with NULL fields into the result list.
> Thanks,
> Dmitri.
>
>|||Hi Jens,
Probably you're right. But if it works with LIKE ? Or only with <> = ?
Thanks,
Dmitri.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OIOIIenSFHA.2136@.TK2MSFTNGP14.phx.gbl...
> Do you mean
> SET ANSI_NULLS
> Specifies SQL-92 compliant behavior of the Equals (=) and Not Equal to
> (<> ) comparison operators when used with null values.
> Jens SUessmeyer.
> "Just D." <no@.spam.please> schrieb im Newsbeitrag
> news:Ojtbe.70654$lz2.58655@.fed1read07...
>|||I think it only changes the behavior of comparisons that include
at least one literal or variable that is NULL. Column-to-column
comparisons between NULLs are never true, regardless of the
setting. I think <column> LIKE NULL is never true also.
set ansi_nulls off
go
declare @.t table (
a int,
c char
)
insert into @.t values (null, null)
insert into @.t values (0, '0')
select * from @.t
where a = null
select * from @.t
where a = c
select * from @.t
where c like null
go
set ansi_nulls on
Steve Kass
Drew University
Just D. wrote:
>Hi Jens,
>Probably you're right. But if it works with LIKE ? Or only with <> = ?
>Thanks,
>Dmitri.
>"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
>message news:OIOIIenSFHA.2136@.TK2MSFTNGP14.phx.gbl...
>
>
>|||Hi Steve,
My parameters that I provide to SQL are never NULL, the string should look
like this:
SELECT * FROM blablabla WHERE Param1 LIKE '%Col1%' OR Param2 LIKE '%Col2%'
OR Param3 LIKE '%Col3%'
This is a very simplified string because actually it's a very long
combination of OR/ANDs and braces. The Param# are never NULL, but if the
value in the column is NULL then I lose this record from the result and this
is a problem. If I had some global setting it would be nice but if I don't
have this way then I need to evaluate all these cells manually and it will
be nightmare because i need to search in 20-30 fields in a few tables, the
tables are long enough and not necessarily all these value are set to
something instead of NULL.
Dmitri.
"Steve Kass" <skass@.drew.edu> wrote in message
news:uABhz7rSFHA.3312@.TK2MSFTNGP12.phx.gbl...
>I think it only changes the behavior of comparisons that include
> at least one literal or variable that is NULL. Column-to-column
> comparisons between NULLs are never true, regardless of the
> setting. I think <column> LIKE NULL is never true also.
> set ansi_nulls off
> go
> declare @.t table (
> a int,
> c char
> )
> insert into @.t values (null, null)
> insert into @.t values (0, '0')
> select * from @.t
> where a = null
> select * from @.t
> where a = c
> select * from @.t
> where c like null
> go
> set ansi_nulls on
> Steve Kass
> Drew University
> Just D. wrote:
>|||> My parameters that I provide to SQL are never NULL, the string should look
> like this:
>
> SELECT * FROM blablabla WHERE Param1 LIKE '%Col1%' OR Param2 LIKE '%Col2%'
OR
> Param3 LIKE '%Col3%'
> This is a very simplified string because actually it's a very long combina
tion
> of OR/ANDs and braces. The Param# are never NULL, but if the value in the
> column is NULL then I lose this record from the result and this is a problem.[/col
or]
As I see it, there are a couple of solutions. The first, most obvious soluti
on
is to populate those nulls with something (e.g. an empty string (ugh) ) and
set
the column to be non-nullable. Then you won't have to worry about null colum
n
values.
The other solution is to change your where clause to account for nulls like
so
(depending on the actual logic desired):
Select F1...Fn
From Table
Where Param1 Is Null
Or Param1 Like '%Col1%'
Or Param2 Is Null
Or Param2 Like '%Col2%'
Or Param3 Is Null
Or Param3 Like '%Col3%'
> If I had some global setting it would be nice but if I don't have this way
> then I need to evaluate all these cells manually and it will be nightmare
> because i need to search in 20-30 fields in a few tables, the tables are l
ong
> enough and not necessarily all these value are set to something instead of
> NULL.
I don't see that you have a whole lot of choices. It sounds like populating
those fields with something would be your easiest route. I would definitely
not
recommend playing with the ANSI_NULLS option. It will confuse the hell out o
f
any other developer that looks at your code. It is one of those unobvious
"gotchas" that developers hate.
Thomas|||Dmitri,
What you want isn't going to be found in a setting,
if you want NULL LIKE '%Col1%' to be true.
If you want rows where any column is null, along with
the once returned by the query below, try
WHERE COALESCE(Param1,'Col1') LIKE '%Col1%'
OR COALESCE(Param2,'Col2') LIKE '%Col2%'
OR COALESCE(Param3,'Col3') LIKE '%Col3%'
It seems very strange to have columns named ParamX where you
search for values called Col1, so if this is not what you want, it
will be best if you provide sample table data that includes rows
you want (matching LIKE), rows you want (because of NULLs)
and some rows you don't want, so you can explain and show us
what you want to retrieve.
SK
Just D. wrote:
>Hi Steve,
>My parameters that I provide to SQL are never NULL, the string should look
>like this:
>
>SELECT * FROM blablabla WHERE Param1 LIKE '%Col1%' OR Param2 LIKE '%Col2%'
>OR Param3 LIKE '%Col3%'
>This is a very simplified string because actually it's a very long
>combination of OR/ANDs and braces. The Param# are never NULL, but if the
>value in the column is NULL then I lose this record from the result and thi
s
>is a problem. If I had some global setting it would be nice but if I don't
>have this way then I need to evaluate all these cells manually and it will
>be nightmare because i need to search in 20-30 fields in a few tables, the
>tables are long enough and not necessarily all these value are set to
>something instead of NULL.
>Dmitri.
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:uABhz7rSFHA.3312@.TK2MSFTNGP12.phx.gbl...
>
>
>
comparison Sybase Adaptive server 9 and MS SQL Server 2005
Hello
for a new product, we need to make a choice between Sybase ASA 9 (that
we know) annd MS SQL server 2005.
Does someone have a comparison available?
Is SQL server that much better than Sybase and how?
TIA
Mario"sledge" <Witdoek@.gmail.com> wrote in message
news:1190311113.772701.67030@.r29g2000hsg.googlegroups.com...
> Hello
> for a new product, we need to make a choice between Sybase ASA 9 (that
> we know) annd MS SQL server 2005.
> Does someone have a comparison available?
> Is SQL server that much better than Sybase and how?
Why are you not **at least** comparing current versions? ASA is now at
version 10 - and this isn't the enterprise-level dbms. I'm sure that you
can find plenty of opinions and documents advocating each. IMO - any dbms
provided by a major vendor will be sufficient for most needs. In any event,
there is no substitute for an actual evaluation if you REALLY need to choose
the best one for your environment.
for a new product, we need to make a choice between Sybase ASA 9 (that
we know) annd MS SQL server 2005.
Does someone have a comparison available?
Is SQL server that much better than Sybase and how?
TIA
Mario"sledge" <Witdoek@.gmail.com> wrote in message
news:1190311113.772701.67030@.r29g2000hsg.googlegroups.com...
> Hello
> for a new product, we need to make a choice between Sybase ASA 9 (that
> we know) annd MS SQL server 2005.
> Does someone have a comparison available?
> Is SQL server that much better than Sybase and how?
Why are you not **at least** comparing current versions? ASA is now at
version 10 - and this isn't the enterprise-level dbms. I'm sure that you
can find plenty of opinions and documents advocating each. IMO - any dbms
provided by a major vendor will be sufficient for most needs. In any event,
there is no substitute for an actual evaluation if you REALLY need to choose
the best one for your environment.
Comparison SQL Server and Active Directory
Hi all,
we develop some asp.net 2.0 workflows which need to access several data from
our Active Directory (e.g. Telephone number, city etc.). We wonder whether
there is a recommendation how to do it best: Shall we access the data from
Active Directory directly or is it better to have a daily job which puts all
the necessary data into a SQL 2000 database and we access the SQL database
then? Especially I'm interested about the effect on the performance of both
our workflows and also our network environment or else. Are there any
official comparisons / recommendations or what are your personal opinions
about it?
Many thanks!Hi
"Kai" wrote:
> Hi all,
> we develop some asp.net 2.0 workflows which need to access several data from
> our Active Directory (e.g. Telephone number, city etc.). We wonder whether
> there is a recommendation how to do it best: Shall we access the data from
> Active Directory directly or is it better to have a daily job which puts all
> the necessary data into a SQL 2000 database and we access the SQL database
> then? Especially I'm interested about the effect on the performance of both
> our workflows and also our network environment or else. Are there any
> official comparisons / recommendations or what are your personal opinions
> about it?
> Many thanks!
This will depend on several factors such as your AD design, network, number
of enquiries, how much latency you can have for changes. I don't know of any
comparison regarding speed or effect, but if there was, there would be the
caveat that it was on their setup and may not be reproducable on other
environments; therefore you could only really ascertain the impact by trying
it yourself and doing some controlled load testing.
If there was a significant number of enquiries then I would recommend using
SQL Server to store a copy of the information especially if you can afford a
higher latency for updated information.
John|||Hi John,
okay, many thanks. Actually I don't have exact details of our Active
Directory Setup, I hope our network admins did a good job :-) So we will try
with Active Directoy and see about the performance.|||Hi
"Kai" wrote:
> Hi John,
> okay, many thanks. Actually I don't have exact details of our Active
> Directory Setup, I hope our network admins did a good job :-) So we will try
> with Active Directoy and see about the performance.
There are plenty of resources regarding extracting data from from AD and
also using ADSI for a linked server, so if it doesn't work it would be quite
easy to get the information e.g. http://www.rlmueller.net/freecode3.htm
John
we develop some asp.net 2.0 workflows which need to access several data from
our Active Directory (e.g. Telephone number, city etc.). We wonder whether
there is a recommendation how to do it best: Shall we access the data from
Active Directory directly or is it better to have a daily job which puts all
the necessary data into a SQL 2000 database and we access the SQL database
then? Especially I'm interested about the effect on the performance of both
our workflows and also our network environment or else. Are there any
official comparisons / recommendations or what are your personal opinions
about it?
Many thanks!Hi
"Kai" wrote:
> Hi all,
> we develop some asp.net 2.0 workflows which need to access several data from
> our Active Directory (e.g. Telephone number, city etc.). We wonder whether
> there is a recommendation how to do it best: Shall we access the data from
> Active Directory directly or is it better to have a daily job which puts all
> the necessary data into a SQL 2000 database and we access the SQL database
> then? Especially I'm interested about the effect on the performance of both
> our workflows and also our network environment or else. Are there any
> official comparisons / recommendations or what are your personal opinions
> about it?
> Many thanks!
This will depend on several factors such as your AD design, network, number
of enquiries, how much latency you can have for changes. I don't know of any
comparison regarding speed or effect, but if there was, there would be the
caveat that it was on their setup and may not be reproducable on other
environments; therefore you could only really ascertain the impact by trying
it yourself and doing some controlled load testing.
If there was a significant number of enquiries then I would recommend using
SQL Server to store a copy of the information especially if you can afford a
higher latency for updated information.
John|||Hi John,
okay, many thanks. Actually I don't have exact details of our Active
Directory Setup, I hope our network admins did a good job :-) So we will try
with Active Directoy and see about the performance.|||Hi
"Kai" wrote:
> Hi John,
> okay, many thanks. Actually I don't have exact details of our Active
> Directory Setup, I hope our network admins did a good job :-) So we will try
> with Active Directoy and see about the performance.
There are plenty of resources regarding extracting data from from AD and
also using ADSI for a linked server, so if it doesn't work it would be quite
easy to get the information e.g. http://www.rlmueller.net/freecode3.htm
John
Comparison SQL Server and Active Directory
Hi all,
we develop some asp.net 2.0 workflows which need to access several data from
our Active Directory (e.g. Telephone number, city etc.). We wonder whether
there is a recommendation how to do it best: Shall we access the data from
Active Directory directly or is it better to have a daily job which puts all
the necessary data into a SQL 2000 database and we access the SQL database
then? Especially I'm interested about the effect on the performance of both
our workflows and also our network environment or else. Are there any
official comparisons / recommendations or what are your personal opinions
about it?
Many thanks!
Hi
"Kai" wrote:
> Hi all,
> we develop some asp.net 2.0 workflows which need to access several data from
> our Active Directory (e.g. Telephone number, city etc.). We wonder whether
> there is a recommendation how to do it best: Shall we access the data from
> Active Directory directly or is it better to have a daily job which puts all
> the necessary data into a SQL 2000 database and we access the SQL database
> then? Especially I'm interested about the effect on the performance of both
> our workflows and also our network environment or else. Are there any
> official comparisons / recommendations or what are your personal opinions
> about it?
> Many thanks!
This will depend on several factors such as your AD design, network, number
of enquiries, how much latency you can have for changes. I don't know of any
comparison regarding speed or effect, but if there was, there would be the
caveat that it was on their setup and may not be reproducable on other
environments; therefore you could only really ascertain the impact by trying
it yourself and doing some controlled load testing.
If there was a significant number of enquiries then I would recommend using
SQL Server to store a copy of the information especially if you can afford a
higher latency for updated information.
John
|||Hi John,
okay, many thanks. Actually I don't have exact details of our Active
Directory Setup, I hope our network admins did a good job :-) So we will try
with Active Directoy and see about the performance.
|||Hi
"Kai" wrote:
> Hi John,
> okay, many thanks. Actually I don't have exact details of our Active
> Directory Setup, I hope our network admins did a good job :-) So we will try
> with Active Directoy and see about the performance.
There are plenty of resources regarding extracting data from from AD and
also using ADSI for a linked server, so if it doesn't work it would be quite
easy to get the information e.g. http://www.rlmueller.net/freecode3.htm
John
we develop some asp.net 2.0 workflows which need to access several data from
our Active Directory (e.g. Telephone number, city etc.). We wonder whether
there is a recommendation how to do it best: Shall we access the data from
Active Directory directly or is it better to have a daily job which puts all
the necessary data into a SQL 2000 database and we access the SQL database
then? Especially I'm interested about the effect on the performance of both
our workflows and also our network environment or else. Are there any
official comparisons / recommendations or what are your personal opinions
about it?
Many thanks!
Hi
"Kai" wrote:
> Hi all,
> we develop some asp.net 2.0 workflows which need to access several data from
> our Active Directory (e.g. Telephone number, city etc.). We wonder whether
> there is a recommendation how to do it best: Shall we access the data from
> Active Directory directly or is it better to have a daily job which puts all
> the necessary data into a SQL 2000 database and we access the SQL database
> then? Especially I'm interested about the effect on the performance of both
> our workflows and also our network environment or else. Are there any
> official comparisons / recommendations or what are your personal opinions
> about it?
> Many thanks!
This will depend on several factors such as your AD design, network, number
of enquiries, how much latency you can have for changes. I don't know of any
comparison regarding speed or effect, but if there was, there would be the
caveat that it was on their setup and may not be reproducable on other
environments; therefore you could only really ascertain the impact by trying
it yourself and doing some controlled load testing.
If there was a significant number of enquiries then I would recommend using
SQL Server to store a copy of the information especially if you can afford a
higher latency for updated information.
John
|||Hi John,
okay, many thanks. Actually I don't have exact details of our Active
Directory Setup, I hope our network admins did a good job :-) So we will try
with Active Directoy and see about the performance.
|||Hi
"Kai" wrote:
> Hi John,
> okay, many thanks. Actually I don't have exact details of our Active
> Directory Setup, I hope our network admins did a good job :-) So we will try
> with Active Directoy and see about the performance.
There are plenty of resources regarding extracting data from from AD and
also using ADSI for a linked server, so if it doesn't work it would be quite
easy to get the information e.g. http://www.rlmueller.net/freecode3.htm
John
Comparison SQL Server and Active Directory
Hi all,
we develop some asp.net 2.0 workflows which need to access several data from
our Active Directory (e.g. Telephone number, city etc.). We wonder whether
there is a recommendation how to do it best: Shall we access the data from
Active Directory directly or is it better to have a daily job which puts all
the necessary data into a SQL 2000 database and we access the SQL database
then? Especially I'm interested about the effect on the performance of both
our workflows and also our network environment or else. Are there any
official comparisons / recommendations or what are your personal opinions
about it?
Many thanks!Hi
"Kai" wrote:
> Hi all,
> we develop some asp.net 2.0 workflows which need to access several data fr
om
> our Active Directory (e.g. Telephone number, city etc.). We wonder whether
> there is a recommendation how to do it best: Shall we access the data from
> Active Directory directly or is it better to have a daily job which puts a
ll
> the necessary data into a SQL 2000 database and we access the SQL database
> then? Especially I'm interested about the effect on the performance of bot
h
> our workflows and also our network environment or else. Are there any
> official comparisons / recommendations or what are your personal opinions
> about it?
> Many thanks!
This will depend on several factors such as your AD design, network, number
of enquiries, how much latency you can have for changes. I don't know of any
comparison regarding speed or effect, but if there was, there would be the
caveat that it was on their setup and may not be reproducable on other
environments; therefore you could only really ascertain the impact by trying
it yourself and doing some controlled load testing.
If there was a significant number of enquiries then I would recommend using
SQL Server to store a copy of the information especially if you can afford a
higher latency for updated information.
John|||Hi John,
okay, many thanks. Actually I don't have exact details of our Active
Directory Setup, I hope our network admins did a good job :-) So we will try
with Active Directoy and see about the performance.|||Hi
"Kai" wrote:
> Hi John,
> okay, many thanks. Actually I don't have exact details of our Active
> Directory Setup, I hope our network admins did a good job :-) So we will t
ry
> with Active Directoy and see about the performance.
There are plenty of resources regarding extracting data from from AD and
also using ADSI for a linked server, so if it doesn't work it would be quite
easy to get the information e.g. http://www.rlmueller.net/freecode3.htm
John
we develop some asp.net 2.0 workflows which need to access several data from
our Active Directory (e.g. Telephone number, city etc.). We wonder whether
there is a recommendation how to do it best: Shall we access the data from
Active Directory directly or is it better to have a daily job which puts all
the necessary data into a SQL 2000 database and we access the SQL database
then? Especially I'm interested about the effect on the performance of both
our workflows and also our network environment or else. Are there any
official comparisons / recommendations or what are your personal opinions
about it?
Many thanks!Hi
"Kai" wrote:
> Hi all,
> we develop some asp.net 2.0 workflows which need to access several data fr
om
> our Active Directory (e.g. Telephone number, city etc.). We wonder whether
> there is a recommendation how to do it best: Shall we access the data from
> Active Directory directly or is it better to have a daily job which puts a
ll
> the necessary data into a SQL 2000 database and we access the SQL database
> then? Especially I'm interested about the effect on the performance of bot
h
> our workflows and also our network environment or else. Are there any
> official comparisons / recommendations or what are your personal opinions
> about it?
> Many thanks!
This will depend on several factors such as your AD design, network, number
of enquiries, how much latency you can have for changes. I don't know of any
comparison regarding speed or effect, but if there was, there would be the
caveat that it was on their setup and may not be reproducable on other
environments; therefore you could only really ascertain the impact by trying
it yourself and doing some controlled load testing.
If there was a significant number of enquiries then I would recommend using
SQL Server to store a copy of the information especially if you can afford a
higher latency for updated information.
John|||Hi John,
okay, many thanks. Actually I don't have exact details of our Active
Directory Setup, I hope our network admins did a good job :-) So we will try
with Active Directoy and see about the performance.|||Hi
"Kai" wrote:
> Hi John,
> okay, many thanks. Actually I don't have exact details of our Active
> Directory Setup, I hope our network admins did a good job :-) So we will t
ry
> with Active Directoy and see about the performance.
There are plenty of resources regarding extracting data from from AD and
also using ADSI for a linked server, so if it doesn't work it would be quite
easy to get the information e.g. http://www.rlmueller.net/freecode3.htm
John
comparison Query
Hi guys,
I'm trying to do a comparison of 2 data sets. Basically what I want is: 'where event date from event number -24 is earlier than the event date for event number -13'
To get the eventdate for the eventno's, I have the following 2 queries:
select eventdate
from caseevent, cases
where eventno = -24
select eventdate
from caseevent, cases
where eventno = -13
So what i'm trying to say is: I want it so that the value of the first query is compared to be LESS than the value of the second query...
Any help please?
Thank you!
Do you want this for a specific event ? I did not get your point in your query, could you describe the functionality in non SQL words ?
Jens K. Suessmeyer
http://www.sqlserver2005.de
|||basically eventdate and eventno are 2 different fields. The first query works out the eventdate for the event number -24, and the second query works out the eventdate for the event number -13. Then, the eventdate values for the first query have to be compared with the eventdate parts for the 2nd query.
Overall this is meant to be one query, so once I've worked out the comparison part, I will need to figure out how to make it one query. Can I somehow compare the retrieved results?
Many thanks
|||Hi,
Can you provide data for those two tables for better understanding and also in your query you have mentioned two tables: caseevent and cases. Is there any specific relationship between those two tables.
Regards,
Kiran.Y
|||the relationship is that once we've sorted out this comparison issue, i need to show certain values from each table, although for this situation now, they are not been joined or cross referenced...
If I run each query invidualy i do get results, but like after 3 minutes!! the closest i've got so far is:
select distinct eventdate
from caseevent, cases
where eventno = -24
and eventdate < all
(select distinct eventdate
from cases
where eventno = -13)
This gives results straight away. Just wondering if a subquery is the best method for doing this
Thanks,
|||anyone? anything? Help please!!
EEK!!
|||As you did not post any DDL yet, this is based on assumptions.
Maybe an exists will speed up your data retrieval:
select distinct eventdate
from caseevent A
where eventno = -24
and EXISTS
(select distinct eventdate
from cases B
where B.eventno = -13
AND A.EventDate < B.eventdate)
Jens K. Suessmeyer
http://www.sqlserver2005.de
Cool, I didn't think about the EXIST clause. I'll try this 2mo as am off work 2day, but then logic seems right in it from the looks of it. When I do run the previous query I showed u, it was a bit slow but if the EXIST clause speeds things up then it should be better
Thank you!!
|||Hello!!
I tried the query you sent me and I think it gives the right search facility, so that should be fine. But another part of the query is that I need to show other columns within the result.
so the query I have so far is:
select eventdate
from caseevent A
where A.eventno = -24
and EXISTS
(select distinct eventdate
from caseevent B
where B.eventno = -13
AND A.EventDate < B.eventdate)
What I'm trying to do is the following:
Report of cases where eventdate for eventno = -24 is earlier than the eventdate for eventno = -13. Need report to include IRN, eventdate for eventno = -24, eventdate for eventno = -13, and initials.
The columns available from the tables are:
Caseevent: eventdate, eventno, caseid
cases: irn, caseid
name: initials
I don't know if this should be a query or a view! I'm still fresh with stuff like Views so wouldn't know how to do this. So far the logic of comparing the results has been understood, but how do I show the other values in my final results table?
Many Thanks
|||Hi Guys,
Any comments or ideas on my previous email please?
The main thing I'm not aware of is how to combine to columns into one final column in my final results table...
Still trying to do this as a query. I'm looking into views to see if it can be a more efficient approach.
Thanks!|||TSQL SELECT clauses support expressions or concatenations as
SELECT FirstName + ' ' + LastName AS FullName
Does this do what you want?
comparison Query
Hi guys,
I'm trying to do a comparison of 2 data sets. Basically what I want is: 'where event date from event number -24 is earlier than the event date for event number -13'
To get the eventdate for the eventno's, I have the following 2 queries:
select eventdate
from caseevent, cases
where eventno = -24
select eventdate
from caseevent, cases
where eventno = -13
So what i'm trying to say is: I want it so that the value of the first query is compared to be LESS than the value of the second query...
Any help please?
Thank you!
Do you want this for a specific event ? I did not get your point in your query, could you describe the functionality in non SQL words ?
Jens K. Suessmeyer
http://www.sqlserver2005.de
|||basically eventdate and eventno are 2 different fields. The first query works out the eventdate for the event number -24, and the second query works out the eventdate for the event number -13. Then, the eventdate values for the first query have to be compared with the eventdate parts for the 2nd query.
Overall this is meant to be one query, so once I've worked out the comparison part, I will need to figure out how to make it one query. Can I somehow compare the retrieved results?
Many thanks
|||Hi,
Can you provide data for those two tables for better understanding and also in your query you have mentioned two tables: caseevent and cases. Is there any specific relationship between those two tables.
Regards,
Kiran.Y
|||the relationship is that once we've sorted out this comparison issue, i need to show certain values from each table, although for this situation now, they are not been joined or cross referenced...
If I run each query invidualy i do get results, but like after 3 minutes!! the closest i've got so far is:
select distinct eventdate
from caseevent, cases
where eventno = -24
and eventdate < all
(select distinct eventdate
from cases
where eventno = -13)
This gives results straight away. Just wondering if a subquery is the best method for doing this
Thanks,
|||anyone? anything? Help please!!
EEK!!
|||As you did not post any DDL yet, this is based on assumptions.
Maybe an exists will speed up your data retrieval:
select distinct eventdate
from caseevent A
where eventno = -24
and EXISTS
(select distinct eventdate
from cases B
where B.eventno = -13
AND A.EventDate < B.eventdate)
Jens K. Suessmeyer
http://www.sqlserver2005.de
Cool, I didn't think about the EXIST clause. I'll try this 2mo as am off work 2day, but then logic seems right in it from the looks of it. When I do run the previous query I showed u, it was a bit slow but if the EXIST clause speeds things up then it should be better
Thank you!!
|||Hello!!
I tried the query you sent me and I think it gives the right search facility, so that should be fine. But another part of the query is that I need to show other columns within the result.
so the query I have so far is:
select eventdate
from caseevent A
where A.eventno = -24
and EXISTS
(select distinct eventdate
from caseevent B
where B.eventno = -13
AND A.EventDate < B.eventdate)
What I'm trying to do is the following:
Report of cases where eventdate for eventno = -24 is earlier than the eventdate for eventno = -13. Need report to include IRN, eventdate for eventno = -24, eventdate for eventno = -13, and initials.
The columns available from the tables are:
Caseevent: eventdate, eventno, caseid
cases: irn, caseid
name: initials
I don't know if this should be a query or a view! I'm still fresh with stuff like Views so wouldn't know how to do this. So far the logic of comparing the results has been understood, but how do I show the other values in my final results table?
Many Thanks
|||Hi Guys,
Any comments or ideas on my previous email please?
The main thing I'm not aware of is how to combine to columns into one final column in my final results table...
Still trying to do this as a query. I'm looking into views to see if it can be a more efficient approach.
Thanks!|||TSQL SELECT clauses support expressions or concatenations as
SELECT FirstName + ' ' + LastName AS FullName
Does this do what you want?
comparison Query
Hi guys,
I'm trying to do a comparison of 2 data sets. Basically what I want is: 'where event date from event number -24 is earlier than the event date for event number -13'
To get the eventdate for the eventno's, I have the following 2 queries:
select eventdate
from caseevent, cases
where eventno = -24
select eventdate
from caseevent, cases
where eventno = -13
So what i'm trying to say is: I want it so that the value of the first query is compared to be LESS than the value of the second query...
Any help please?
Thank you!
Do you want this for a specific event ? I did not get your point in your query, could you describe the functionality in non SQL words ?
Jens K. Suessmeyer
http://www.sqlserver2005.de
|||basically eventdate and eventno are 2 different fields. The first query works out the eventdate for the event number -24, and the second query works out the eventdate for the event number -13. Then, the eventdate values for the first query have to be compared with the eventdate parts for the 2nd query.
Overall this is meant to be one query, so once I've worked out the comparison part, I will need to figure out how to make it one query. Can I somehow compare the retrieved results?
Many thanks
|||Hi,
Can you provide data for those two tables for better understanding and also in your query you have mentioned two tables: caseevent and cases. Is there any specific relationship between those two tables.
Regards,
Kiran.Y
|||the relationship is that once we've sorted out this comparison issue, i need to show certain values from each table, although for this situation now, they are not been joined or cross referenced...
If I run each query invidualy i do get results, but like after 3 minutes!! the closest i've got so far is:
select distinct eventdate
from caseevent, cases
where eventno = -24
and eventdate < all
(select distinct eventdate
from cases
where eventno = -13)
This gives results straight away. Just wondering if a subquery is the best method for doing this
Thanks,
|||anyone? anything? Help please!!
EEK!!
|||As you did not post any DDL yet, this is based on assumptions.
Maybe an exists will speed up your data retrieval:
select distinct eventdate
from caseevent A
where eventno = -24
and EXISTS
(select distinct eventdate
from cases B
where B.eventno = -13
AND A.EventDate < B.eventdate)
Jens K. Suessmeyer
http://www.sqlserver2005.de
Cool, I didn't think about the EXIST clause. I'll try this 2mo as am off work 2day, but then logic seems right in it from the looks of it. When I do run the previous query I showed u, it was a bit slow but if the EXIST clause speeds things up then it should be better
Thank you!!
|||Hello!!
I tried the query you sent me and I think it gives the right search facility, so that should be fine. But another part of the query is that I need to show other columns within the result.
so the query I have so far is:
select eventdate
from caseevent A
where A.eventno = -24
and EXISTS
(select distinct eventdate
from caseevent B
where B.eventno = -13
AND A.EventDate < B.eventdate)
What I'm trying to do is the following:
Report of cases where eventdate for eventno = -24 is earlier than the eventdate for eventno = -13. Need report to include IRN, eventdate for eventno = -24, eventdate for eventno = -13, and initials.
The columns available from the tables are:
Caseevent: eventdate, eventno, caseid
cases: irn, caseid
name: initials
I don't know if this should be a query or a view! I'm still fresh with stuff like Views so wouldn't know how to do this. So far the logic of comparing the results has been understood, but how do I show the other values in my final results table?
Many Thanks
|||Hi Guys,
Any comments or ideas on my previous email please?
The main thing I'm not aware of is how to combine to columns into one final column in my final results table...
Still trying to do this as a query. I'm looking into views to see if it can be a more efficient approach.
Thanks!|||TSQL SELECT clauses support expressions or concatenations as
SELECT FirstName + ' ' + LastName AS FullName
Does this do what you want?
comparison Query
Hi guys,
I'm trying to do a comparison of 2 data sets. Basically what I want is: 'where event date from event number -24 is earlier than the event date for event number -13'
To get the eventdate for the eventno's, I have the following 2 queries:
select eventdate
from caseevent, cases
where eventno = -24
select eventdate
from caseevent, cases
where eventno = -13
So what i'm trying to say is: I want it so that the value of the first query is compared to be LESS than the value of the second query...
Any help please?
Thank you!
Do you want this for a specific event ? I did not get your point in your query, could you describe the functionality in non SQL words ?
Jens K. Suessmeyer
http://www.sqlserver2005.de
|||basically eventdate and eventno are 2 different fields. The first query works out the eventdate for the event number -24, and the second query works out the eventdate for the event number -13. Then, the eventdate values for the first query have to be compared with the eventdate parts for the 2nd query.
Overall this is meant to be one query, so once I've worked out the comparison part, I will need to figure out how to make it one query. Can I somehow compare the retrieved results?
Many thanks
|||Hi,
Can you provide data for those two tables for better understanding and also in your query you have mentioned two tables: caseevent and cases. Is there any specific relationship between those two tables.
Regards,
Kiran.Y
|||the relationship is that once we've sorted out this comparison issue, i need to show certain values from each table, although for this situation now, they are not been joined or cross referenced...
If I run each query invidualy i do get results, but like after 3 minutes!! the closest i've got so far is:
select distinct eventdate
from caseevent, cases
where eventno = -24
and eventdate < all
(select distinct eventdate
from cases
where eventno = -13)
This gives results straight away. Just wondering if a subquery is the best method for doing this
Thanks,
|||anyone? anything? Help please!!
EEK!!
|||As you did not post any DDL yet, this is based on assumptions.
Maybe an exists will speed up your data retrieval:
select distinct eventdate
from caseevent A
where eventno = -24
and EXISTS
(select distinct eventdate
from cases B
where B.eventno = -13
AND A.EventDate < B.eventdate)
Jens K. Suessmeyer
http://www.sqlserver2005.de
Cool, I didn't think about the EXIST clause. I'll try this 2mo as am off work 2day, but then logic seems right in it from the looks of it. When I do run the previous query I showed u, it was a bit slow but if the EXIST clause speeds things up then it should be better
Thank you!!
|||Hello!!
I tried the query you sent me and I think it gives the right search facility, so that should be fine. But another part of the query is that I need to show other columns within the result.
so the query I have so far is:
select eventdate
from caseevent A
where A.eventno = -24
and EXISTS
(select distinct eventdate
from caseevent B
where B.eventno = -13
AND A.EventDate < B.eventdate)
What I'm trying to do is the following:
Report of cases where eventdate for eventno = -24 is earlier than the eventdate for eventno = -13. Need report to include IRN, eventdate for eventno = -24, eventdate for eventno = -13, and initials.
The columns available from the tables are:
Caseevent: eventdate, eventno, caseid
cases: irn, caseid
name: initials
I don't know if this should be a query or a view! I'm still fresh with stuff like Views so wouldn't know how to do this. So far the logic of comparing the results has been understood, but how do I show the other values in my final results table?
Many Thanks
|||Hi Guys,
Any comments or ideas on my previous email please?
The main thing I'm not aware of is how to combine to columns into one final column in my final results table...
Still trying to do this as a query. I'm looking into views to see if it can be a more efficient approach.
Thanks!|||TSQL SELECT clauses support expressions or concatenations as
SELECT FirstName + ' ' + LastName AS FullName
Does this do what you want?
Comparison operator in Select list
I want to create a column alias to represent the comparison of two
columns (ie a boolean result of True or False). A simple example is:
Select VehicleFinanceID, SalePrice > PurchasePrice As isProfit
>From VehicleFinance
columns (ie a boolean result of True or False). A simple example is:
Select VehicleFinanceID, SalePrice > PurchasePrice As isProfit
>From VehicleFinance
but I get an error 'Incorrect syntax near >'
Books online states that the select_list can contain column_name or
expression
An expression is a column name, constant, function, any combination of
column names, constants and functions connected by an operator
> is a binary operator
So why do I get this error.
Incidentally, if I use an arithmetic operator such as +, there is no
problem.A gremlin put the erroneous > in front of From :-(
The Select statement was intended to be:
Select VehicleFinanceID, SalePrice > PurchasePrice As isProfit
>From VehicleFinance
Jim|||There it is again. I can't win on this one. Please disregard the > in
front of From
Jim
Jim Devenish wrote:
> A gremlin put the erroneous > in front of From :-(
> The Select statement was intended to be:
> Select VehicleFinanceID, SalePrice > PurchasePrice As isProfit
> >From VehicleFinance
>
> Jim|||Hello, Jim
Try something like this:
SELECT VehicleFinanceID,
CASE WHEN SalePrice > PurchasePrice THEN 1 ELSE 0 END As isProfit
FROM VehicleFinance
SQL Server does not support a boolean data type, so you must use some
convention to represent boolean values (for example: 1=True, 0=False or
'Yes'=True, 'No'=False, etc).
A boolean expression can only be used in an IF statement, a WHILE
statement, a CASE WHEN expression, a WHERE/HAVING clause, etc.
Razvan|||Thanks Razvan. That is what I had done but it seemed cumbersone for
something that is essentially simple.
At least I know what I can and cannot do, now.
Jim
Razvan Socol wrote:
> Hello, Jim
> Try something like this:
> SELECT VehicleFinanceID,
> CASE WHEN SalePrice > PurchasePrice THEN 1 ELSE 0 END As isProfit
> FROM VehicleFinance
> SQL Server does not support a boolean data type, so you must use some
> convention to represent boolean values (for example: 1=True, 0=False or
> 'Yes'=True, 'No'=False, etc).
> A boolean expression can only be used in an IF statement, a WHILE
> statement, a CASE WHEN expression, a WHERE/HAVING clause, etc.
> Razvan|||Jim Devenish (internet.shopping@.foobox.com) writes:
> There it is again. I can't win on this one. Please disregard the > in
> front of From
The reason this happens is because of the mbox format used by old
Unix mailers. This format is also used by some newsreaders for archives
I believe. In this format, a new message always starts with "From ".
Since a preceding message always ends with two or three newlines,
there is some safety precaution in the format, but not waterproof.
For this reason, Unix mailers that uses this format adds a > before
"From" when it appears first on a line. I've noticed that Google news,
that I see that you are using, also does this to be, I guess, a good
net citizen.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
comparison on text field
my query: select * from inventory where ModelRef=' '
but, the result includes all records that having blanked ModelRef field.
I want to know what setting controls this behavior.
Thanks,
LeonardHi
You may want to look at using NULL instead of empty strings.
John
"Leonard Poon" wrote:
> my query: select * from inventory where ModelRef=' '
> but, the result includes all records that having blanked ModelRef field.
> I want to know what setting controls this behavior.
> Thanks,
> Leonard
>
>
but, the result includes all records that having blanked ModelRef field.
I want to know what setting controls this behavior.
Thanks,
LeonardHi
You may want to look at using NULL instead of empty strings.
John
"Leonard Poon" wrote:
> my query: select * from inventory where ModelRef=' '
> but, the result includes all records that having blanked ModelRef field.
> I want to know what setting controls this behavior.
> Thanks,
> Leonard
>
>
Comparison on SQL Server 2000 and SQL Server 2005
Hi all,
I would like to ask if any research conducted on comparisons of SQL Server 2000 and SQL Server 2005? If yes, where can I find them?
Thanks a lot!
Gabriel
That's a pretty open-ended question! This web page lists a bunch of info on SQL Server 2005.
http://www.microsoft.com/sql/prodinfo/news.mspx
If you still have specific questions after reading everything there 
Paul
Comparison of words within a column using "Like" keyword
Hello all!
I have got a problem, when I use this query it returns the expected results
select * from tbl_list
where keyword like '%' + 'abc' + '%'
but when I use the same in stored procedure, it returns unexpected results:
my stored procedure is this
Create proc sp_getlist
@.keyword varchar(2000)
as
select * from tbl_list
where keyword like '%' + @.keyword + '%'
I'm Using SQl Server 2000 (Personal Addition) on Windows XP SP2.
Please help me out, thanx in anticipation.Hi Zubair,
What is the kind of result that u are getting. Can you please display a
sample output and the kind of input that u are sending to the Stored Procedu
re
thanks and regards
Chandra
"zubair" wrote:
> Hello all!
> I have got a problem, when I use this query it returns the expected result
s
> select * from tbl_list
> where keyword like '%' + 'abc' + '%'
> but when I use the same in stored procedure, it returns unexpected results
:
> my stored procedure is this
> Create proc sp_getlist
> @.keyword varchar(2000)
> as
> select * from tbl_list
> where keyword like '%' + @.keyword + '%'
> I'm Using SQl Server 2000 (Personal Addition) on Windows XP SP2.
> Please help me out, thanx in anticipation.
>
>
I have got a problem, when I use this query it returns the expected results
select * from tbl_list
where keyword like '%' + 'abc' + '%'
but when I use the same in stored procedure, it returns unexpected results:
my stored procedure is this
Create proc sp_getlist
@.keyword varchar(2000)
as
select * from tbl_list
where keyword like '%' + @.keyword + '%'
I'm Using SQl Server 2000 (Personal Addition) on Windows XP SP2.
Please help me out, thanx in anticipation.Hi Zubair,
What is the kind of result that u are getting. Can you please display a
sample output and the kind of input that u are sending to the Stored Procedu
re
thanks and regards
Chandra
"zubair" wrote:
> Hello all!
> I have got a problem, when I use this query it returns the expected result
s
> select * from tbl_list
> where keyword like '%' + 'abc' + '%'
> but when I use the same in stored procedure, it returns unexpected results
:
> my stored procedure is this
> Create proc sp_getlist
> @.keyword varchar(2000)
> as
> select * from tbl_list
> where keyword like '%' + @.keyword + '%'
> I'm Using SQl Server 2000 (Personal Addition) on Windows XP SP2.
> Please help me out, thanx in anticipation.
>
>
Friday, February 10, 2012
comparison of SQL Server 7.0 with SQL Server 2000
Hello,
Can any one help me with this topic:
Comparison of SQL Server 7.0 Replication with SQL Server 2000
Replication
Thanks!One doesn't work, the other does (see order of the question)|||Originally posted by rdjabarov
One doesn't work, the other does (see order of the question)
Great Answer, Hope you are genius...keep it up|||No, I'm just learning, but you already developed an attitude. Keep it up ;)
Can any one help me with this topic:
Comparison of SQL Server 7.0 Replication with SQL Server 2000
Replication
Thanks!One doesn't work, the other does (see order of the question)|||Originally posted by rdjabarov
One doesn't work, the other does (see order of the question)
Great Answer, Hope you are genius...keep it up|||No, I'm just learning, but you already developed an attitude. Keep it up ;)
Comparison of different replication types
Greetings,
I'm looking for a document that does a really good comparison of merge
replication, peer-to-peer transactional replication, and transactional
replication with updating subscribers. Does anyone know of a site or
document with such a comparison? I would especially be interested in
scalability/performance information regarding the different types of
replication.
Thanks,
Sam Bendayan
DB Architect
Ultimate Software
sam.bendayan@.gmail.com
*** Sent via Developersdex http://www.codecomments.com ***
Also, I found in one of the blog posts a statement saying that
Peer-To-Peer replication does not scale well beyond 10 nodes. Just to
be perfectly clear, does this mean that if you have more than 10 servers
in the replication topology then you will start to see performance
problems and that there is no good solution to this?
It also states that merge replication is better for a large number of
nodes. Is it possible to have these larger number of nodes synchronized
frequently (every few minutes), or will that not scale well either?
Thanks,
SB
Sam Bendayan
DB Architect
Ultimate Software
sam.bendayan@.gmail.com
*** Sent via Developersdex http://www.codecomments.com ***
|||In a PASS talk on SQL Server 2005 Replication, Philip Vaughn the Microsoft
program manager for Replication said that (IIRC) between 10-15 nodes the
network links become saturated and he did not recommend scaling it beyond
this number.
Regarding scaling merge replication, you have to look at hierarchies.
Depending on the amount of data you sync with each subscriber each time you
might have to limit the number of concurrent merge processes.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Sam Bendayan" <sam.bendayan@.gmail.com> wrote in message
news:u4DP5YFvHHA.3724@.TK2MSFTNGP03.phx.gbl...
> Also, I found in one of the blog posts a statement saying that
> Peer-To-Peer replication does not scale well beyond 10 nodes. Just to
> be perfectly clear, does this mean that if you have more than 10 servers
> in the replication topology then you will start to see performance
> problems and that there is no good solution to this?
> It also states that merge replication is better for a large number of
> nodes. Is it possible to have these larger number of nodes synchronized
> frequently (every few minutes), or will that not scale well either?
> Thanks,
> SB
> Sam Bendayan
> DB Architect
> Ultimate Software
> sam.bendayan@.gmail.com
> *** Sent via Developersdex http://www.codecomments.com ***
I'm looking for a document that does a really good comparison of merge
replication, peer-to-peer transactional replication, and transactional
replication with updating subscribers. Does anyone know of a site or
document with such a comparison? I would especially be interested in
scalability/performance information regarding the different types of
replication.
Thanks,
Sam Bendayan
DB Architect
Ultimate Software
sam.bendayan@.gmail.com
*** Sent via Developersdex http://www.codecomments.com ***
Also, I found in one of the blog posts a statement saying that
Peer-To-Peer replication does not scale well beyond 10 nodes. Just to
be perfectly clear, does this mean that if you have more than 10 servers
in the replication topology then you will start to see performance
problems and that there is no good solution to this?
It also states that merge replication is better for a large number of
nodes. Is it possible to have these larger number of nodes synchronized
frequently (every few minutes), or will that not scale well either?
Thanks,
SB
Sam Bendayan
DB Architect
Ultimate Software
sam.bendayan@.gmail.com
*** Sent via Developersdex http://www.codecomments.com ***
|||In a PASS talk on SQL Server 2005 Replication, Philip Vaughn the Microsoft
program manager for Replication said that (IIRC) between 10-15 nodes the
network links become saturated and he did not recommend scaling it beyond
this number.
Regarding scaling merge replication, you have to look at hierarchies.
Depending on the amount of data you sync with each subscriber each time you
might have to limit the number of concurrent merge processes.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Sam Bendayan" <sam.bendayan@.gmail.com> wrote in message
news:u4DP5YFvHHA.3724@.TK2MSFTNGP03.phx.gbl...
> Also, I found in one of the blog posts a statement saying that
> Peer-To-Peer replication does not scale well beyond 10 nodes. Just to
> be perfectly clear, does this mean that if you have more than 10 servers
> in the replication topology then you will start to see performance
> problems and that there is no good solution to this?
> It also states that merge replication is better for a large number of
> nodes. Is it possible to have these larger number of nodes synchronized
> frequently (every few minutes), or will that not scale well either?
> Thanks,
> SB
> Sam Bendayan
> DB Architect
> Ultimate Software
> sam.bendayan@.gmail.com
> *** Sent via Developersdex http://www.codecomments.com ***
comparison of databases
Hi just wondering if there is a tool that can be used to compare 2 databases
on different servers to see if they are the same or have the same data?
Thanks.
Paul G
Software engineer.
www.red-gate.com SQL Compare and SQL DataCompare.
www.apexsql.com SQLDiff will do it all in one.
"Paul" wrote:
> Hi just wondering if there is a tool that can be used to compare 2 databases
> on different servers to see if they are the same or have the same data?
> Thanks.
> --
> Paul G
> Software engineer.
|||I have used Red-Gate's Data Compare utility 5 times today for this very
purpose...
www.red-gate.com
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:C18BEDD3-C670-4984-86B5-DA0D092F3365@.microsoft.com...
> Hi just wondering if there is a tool that can be used to compare 2
> databases
> on different servers to see if they are the same or have the same data?
> Thanks.
> --
> Paul G
> Software engineer.
|||thanks for the information. Guess this feature is not built into Enterprise
manager or Query Analyser.
Paul G
Software engineer.
"Kevin3NF" wrote:
> I have used Red-Gate's Data Compare utility 5 times today for this very
> purpose...
> www.red-gate.com
>
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> www.experts-exchange.com - experts compete for points to answer your
> questions
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:C18BEDD3-C670-4984-86B5-DA0D092F3365@.microsoft.com...
>
>
|||We've developed Database Comparing Tool on .NET platform
www.databasecompare.com It is still beta version but you can download and
try for free. It allows comparing structure (schema) and data between 2
databases: http://www.databasecompare.com/download.html
Will be nice to have any feedback!
Thanks.
[vbcol=seagreen]
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:C18BEDD3-C670-4984-86B5-DA0D092F3365@.microsoft.com...
> Hi just wondering if there is a tool that can be used to compare 2
> databases
> on different servers to see if they are the same or have the same data?
> Thanks.
> --
> Paul G
|||ok thanks will give it a try!
Paul G
Software engineer.
"databasecompare" wrote:
[vbcol=seagreen]
> We've developed Database Comparing Tool on .NET platform
> www.databasecompare.com It is still beta version but you can download and
> try for free. It allows comparing structure (schema) and data between 2
> databases: http://www.databasecompare.com/download.html
> Will be nice to have any feedback!
> Thanks.
|||Not in the current version...no idea if it is a feature of 2005....works
well for a $200 product :-)
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:8A6FF067-90EC-4371-BFF8-7072DE5437FD@.microsoft.com...[vbcol=seagreen]
> thanks for the information. Guess this feature is not built into
> Enterprise
> manager or Query Analyser.
> --
> Paul G
> Software engineer.
>
> "Kevin3NF" wrote:
on different servers to see if they are the same or have the same data?
Thanks.
Paul G
Software engineer.
www.red-gate.com SQL Compare and SQL DataCompare.
www.apexsql.com SQLDiff will do it all in one.
"Paul" wrote:
> Hi just wondering if there is a tool that can be used to compare 2 databases
> on different servers to see if they are the same or have the same data?
> Thanks.
> --
> Paul G
> Software engineer.
|||I have used Red-Gate's Data Compare utility 5 times today for this very
purpose...
www.red-gate.com
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:C18BEDD3-C670-4984-86B5-DA0D092F3365@.microsoft.com...
> Hi just wondering if there is a tool that can be used to compare 2
> databases
> on different servers to see if they are the same or have the same data?
> Thanks.
> --
> Paul G
> Software engineer.
|||thanks for the information. Guess this feature is not built into Enterprise
manager or Query Analyser.
Paul G
Software engineer.
"Kevin3NF" wrote:
> I have used Red-Gate's Data Compare utility 5 times today for this very
> purpose...
> www.red-gate.com
>
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> www.experts-exchange.com - experts compete for points to answer your
> questions
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:C18BEDD3-C670-4984-86B5-DA0D092F3365@.microsoft.com...
>
>
|||We've developed Database Comparing Tool on .NET platform
www.databasecompare.com It is still beta version but you can download and
try for free. It allows comparing structure (schema) and data between 2
databases: http://www.databasecompare.com/download.html
Will be nice to have any feedback!
Thanks.
[vbcol=seagreen]
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:C18BEDD3-C670-4984-86B5-DA0D092F3365@.microsoft.com...
> Hi just wondering if there is a tool that can be used to compare 2
> databases
> on different servers to see if they are the same or have the same data?
> Thanks.
> --
> Paul G
|||ok thanks will give it a try!
Paul G
Software engineer.
"databasecompare" wrote:
[vbcol=seagreen]
> We've developed Database Comparing Tool on .NET platform
> www.databasecompare.com It is still beta version but you can download and
> try for free. It allows comparing structure (schema) and data between 2
> databases: http://www.databasecompare.com/download.html
> Will be nice to have any feedback!
> Thanks.
|||Not in the current version...no idea if it is a feature of 2005....works
well for a $200 product :-)
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:8A6FF067-90EC-4371-BFF8-7072DE5437FD@.microsoft.com...[vbcol=seagreen]
> thanks for the information. Guess this feature is not built into
> Enterprise
> manager or Query Analyser.
> --
> Paul G
> Software engineer.
>
> "Kevin3NF" wrote:
Subscribe to:
Posts (Atom)