Number of connections in XP Pro

We are planning multiple remote sites, each running 5 XP Pro or XP Embedded
which will logon to a Windows Server 2003 machine at Head Office via a WAN.
Each XP machine in the site will be replicating its single db to the 4
others via Merge Replication. We will also be using MSMQ 3 for
communication between 2 computers in any site.
I am unsure of how replication (and MSMQ) affects the 10 connections limit
of XP Pro. In replication, would there only be 1 incoming connection on
each machine? (i.e. The Publisher)
I bump into this limit all the time when running pro. The short answer is
you need to bypass this by using SQL Authentication and use a pull
subscription with FTP.
You may have to disable MSMQ, reboot, then ensure that the first thing you
do when you come up is download the snapshot. Then start up MSMQ.
After the snapshot is deployed you shouldn't have to use NT authentication
any more. You should be good from there.
"Chris" <chris.spamhater@.ihatespam.co.uk> wrote in message
news:OSXzqUPXEHA.1764@.TK2MSFTNGP10.phx.gbl...
> We are planning multiple remote sites, each running 5 XP Pro or XP
Embedded
> which will logon to a Windows Server 2003 machine at Head Office via a
WAN.
> Each XP machine in the site will be replicating its single db to the 4
> others via Merge Replication. We will also be using MSMQ 3 for
> communication between 2 computers in any site.
> I am unsure of how replication (and MSMQ) affects the 10 connections limit
> of XP Pro. In replication, would there only be 1 incoming connection on
> each machine? (i.e. The Publisher)
>
>

Nulls in columns additions when 1 or more column values is blank

I am running into an issue when adding data from multiple columns into
one alias:

P.ADDR1 + ' - ' + P.CITY + ',' + ' ' + P.STATE AS LOCATION

If one of the 3 values is blank, the value LOCATION becomes NULL. How
can I inlcude any of the 3 values without LOCATION becoming NULL?

Example, if ADDR1 and CITY have values but STATE is blank, I get a
NULL statement for LOCATION. I still want it to show ADDR1 and CITY
even if STATE is blank.

ThanksISNULL(P.CITY,'')
Techhead wrote:

Quote:

Originally Posted by

I am running into an issue when adding data from multiple columns into
one alias:
>
P.ADDR1 + ' - ' + P.CITY + ',' + ' ' + P.STATE AS LOCATION
>
If one of the 3 values is blank, the value LOCATION becomes NULL. How
can I inlcude any of the 3 values without LOCATION becoming NULL?
>
Example, if ADDR1 and CITY have values but STATE is blank, I get a
NULL statement for LOCATION. I still want it to show ADDR1 and CITY
even if STATE is blank.
>
Thanks
>

|||You can use COALESCE, something like this will do it:

COALESCE(P.ADDR1, '') + ' - ' + COALESCE(P.CITY, '') + ', ' +
COALESCE(P.STATE, '') AS LOCATION

Also, you can play with formatting variations based on what you want to get
when one of the columns is NULL, like this:

COALESCE(P.ADDR1, '') + COALESCE(' - ' + P.CITY, '') + COALESCE(', ' +
P.STATE, '') AS LOCATION

HTH,

Plamen Ratchev
http://www.SQLStudio.com|||On Jun 4, 3:29 pm, "Plamen Ratchev" <Pla...@.SQLStudio.comwrote:

Quote:

Originally Posted by

You can use COALESCE, something like this will do it:
>
COALESCE(P.ADDR1, '') + ' - ' + COALESCE(P.CITY, '') + ', ' +
COALESCE(P.STATE, '') AS LOCATION
>
Also, you can play with formatting variations based on what you want to get
when one of the columns is NULL, like this:
>
COALESCE(P.ADDR1, '') + COALESCE(' - ' + P.CITY, '') + COALESCE(', ' +
P.STATE, '') AS LOCATION
>
HTH,
>
Plamen Ratchevhttp://www.SQLStudio.com

Somebody at work told me to use this:

SELECT CASE WHEN P.STATE IS NULL THEN '' ELSE P.STATE END

It seems to work. Is this similar as to what is described above?|||Techhead (jorgenson.b@.gmail.com) writes:

Quote:

Originally Posted by

Somebody at work told me to use this:
>
SELECT CASE WHEN P.STATE IS NULL THEN '' ELSE P.STATE END
>
It seems to work. Is this similar as to what is described above?

Yes, coalesce is a shortcut for the above. The nice thing with coalesce is
that it accept a list of values, and will return the first value that
is non-NULL.

--
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

Null values or multiple tables

I have a table that stores SNMP Mib values.
These values can be either numeric or text.
I would like to store the text values in a varchar column and the
numeric values in a double column. This of course would help
performance amongs other things.
The question is this:
Is it better to have two tables, one that stores the numeric values
and one that stores the text values.
ex)
CREATE TABLE [dbo].[SnmpMibDataText] (
[SnmpMibDeviceID] [int] NOT NULL ,
[SampleTimestamp] [datetime] NOT NULL ,
[SampleValue] [nvarchar] (255) NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[SnmpMibDataNumeric] (
[SnmpMibDeviceID] [int] NOT NULL ,
[SampleTimestamp] [datetime] NOT NULL ,
[SampleValue] [float] NOT NULL
) ON [PRIMARY]
Or is it better to have 1 table with two columns, one for text and one
for numeric?
CREATE TABLE [dbo].[SnmpMibData] (
[SnmpMibDeviceID] [int] NOT NULL ,
[SampleTimestamp] [datetime] NOT NULL ,
[SampleValueText] [nvarchar] (255),
[SampleValueNumeric] [float]
) ON [PRIMARY]
Now when the Mib value is numeric the text column would be null, and
when the mib value is text the numeric column would be null.
When i ask what is better, i mean overall better (performance,
usability, extendability, etc)?
Thanks in advance
Mark
The one table is probably the better one. Its row size is only slightly
larger than SnmpMibDataText in the two table approach. You have one less
table to manage.
Wei Xiao
Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mark Oueis" <markoueis@.hotmail.com> wrote in message
news:b1800bd3.0412131123.7d5e2802@.posting.google.c om...
> I have a table that stores SNMP Mib values.
> These values can be either numeric or text.
> I would like to store the text values in a varchar column and the
> numeric values in a double column. This of course would help
> performance amongs other things.
> The question is this:
> Is it better to have two tables, one that stores the numeric values
> and one that stores the text values.
> ex)
> CREATE TABLE [dbo].[SnmpMibDataText] (
> [SnmpMibDeviceID] [int] NOT NULL ,
> [SampleTimestamp] [datetime] NOT NULL ,
> [SampleValue] [nvarchar] (255) NOT NULL
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[SnmpMibDataNumeric] (
> [SnmpMibDeviceID] [int] NOT NULL ,
> [SampleTimestamp] [datetime] NOT NULL ,
> [SampleValue] [float] NOT NULL
> ) ON [PRIMARY]
>
> Or is it better to have 1 table with two columns, one for text and one
> for numeric?
> CREATE TABLE [dbo].[SnmpMibData] (
> [SnmpMibDeviceID] [int] NOT NULL ,
> [SampleTimestamp] [datetime] NOT NULL ,
> [SampleValueText] [nvarchar] (255),
> [SampleValueNumeric] [float]
> ) ON [PRIMARY]
> Now when the Mib value is numeric the text column would be null, and
> when the mib value is text the numeric column would be null.
>
> When i ask what is better, i mean overall better (performance,
> usability, extendability, etc)?
> Thanks in advance
> Mark
|||I would probably go with a three table design: a master table that had the
common definitions and, then, two 1-to1 or 1-to-many child related tables to
store the partiallly populated information. NULL-valued columns suit some
purposes but not usually the condidtion where you just don't know the
information or it is not applicable for some of the rows. That indicates
that it is not an attribute of the parent entitiy but subordinate entity
altogether.
Sincerely,
Anthony Thomas

"wei xiao[MS]" <weix@.online.microsoft.com> wrote in message
news:41beb22b$1@.news.microsoft.com...
The one table is probably the better one. Its row size is only slightly
larger than SnmpMibDataText in the two table approach. You have one less
table to manage.
Wei Xiao
Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mark Oueis" <markoueis@.hotmail.com> wrote in message
news:b1800bd3.0412131123.7d5e2802@.posting.google.c om...
> I have a table that stores SNMP Mib values.
> These values can be either numeric or text.
> I would like to store the text values in a varchar column and the
> numeric values in a double column. This of course would help
> performance amongs other things.
> The question is this:
> Is it better to have two tables, one that stores the numeric values
> and one that stores the text values.
> ex)
> CREATE TABLE [dbo].[SnmpMibDataText] (
> [SnmpMibDeviceID] [int] NOT NULL ,
> [SampleTimestamp] [datetime] NOT NULL ,
> [SampleValue] [nvarchar] (255) NOT NULL
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[SnmpMibDataNumeric] (
> [SnmpMibDeviceID] [int] NOT NULL ,
> [SampleTimestamp] [datetime] NOT NULL ,
> [SampleValue] [float] NOT NULL
> ) ON [PRIMARY]
>
> Or is it better to have 1 table with two columns, one for text and one
> for numeric?
> CREATE TABLE [dbo].[SnmpMibData] (
> [SnmpMibDeviceID] [int] NOT NULL ,
> [SampleTimestamp] [datetime] NOT NULL ,
> [SampleValueText] [nvarchar] (255),
> [SampleValueNumeric] [float]
> ) ON [PRIMARY]
> Now when the Mib value is numeric the text column would be null, and
> when the mib value is text the numeric column would be null.
>
> When i ask what is better, i mean overall better (performance,
> usability, extendability, etc)?
> Thanks in advance
> Mark

Null values or multiple tables

I have a table that stores SNMP Mib values.
These values can be either numeric or text.
I would like to store the text values in a varchar column and the
numeric values in a double column. This of course would help
performance amongs other things.
The question is this:
Is it better to have two tables, one that stores the numeric values
and one that stores the text values.
ex)
CREATE TABLE [dbo].[SnmpMibDataText] (
[SnmpMibDeviceID] [int] NOT NULL ,
[SampleTimestamp] [datetime] NOT NULL ,
[SampleValue] [nvarchar] (255) NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[SnmpMibDataNumeric] (
[SnmpMibDeviceID] [int] NOT NULL ,
[SampleTimestamp] [datetime] NOT NULL ,
[SampleValue] [float] NOT NULL
) ON [PRIMARY]
Or is it better to have 1 table with two columns, one for text and one
for numeric?
CREATE TABLE [dbo].[SnmpMibData] (
[SnmpMibDeviceID] [int] NOT NULL ,
[SampleTimestamp] [datetime] NOT NULL ,
[SampleValueText] [nvarchar] (255),
[SampleValueNumeric] [float]
) ON [PRIMARY]
Now when the Mib value is numeric the text column would be null, and
when the mib value is text the numeric column would be null.
When i ask what is better, i mean overall better (performance,
usability, extendability, etc)?
Thanks in advance
MarkThe one table is probably the better one. Its row size is only slightly
larger than SnmpMibDataText in the two table approach. You have one less
table to manage.
--
Wei Xiao
Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mark Oueis" <markoueis@.hotmail.com> wrote in message
news:b1800bd3.0412131123.7d5e2802@.posting.google.com...
> I have a table that stores SNMP Mib values.
> These values can be either numeric or text.
> I would like to store the text values in a varchar column and the
> numeric values in a double column. This of course would help
> performance amongs other things.
> The question is this:
> Is it better to have two tables, one that stores the numeric values
> and one that stores the text values.
> ex)
> CREATE TABLE [dbo].[SnmpMibDataText] (
> [SnmpMibDeviceID] [int] NOT NULL ,
> [SampleTimestamp] [datetime] NOT NULL ,
> [SampleValue] [nvarchar] (255) NOT NULL
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[SnmpMibDataNumeric] (
> [SnmpMibDeviceID] [int] NOT NULL ,
> [SampleTimestamp] [datetime] NOT NULL ,
> [SampleValue] [float] NOT NULL
> ) ON [PRIMARY]
>
> Or is it better to have 1 table with two columns, one for text and one
> for numeric?
> CREATE TABLE [dbo].[SnmpMibData] (
> [SnmpMibDeviceID] [int] NOT NULL ,
> [SampleTimestamp] [datetime] NOT NULL ,
> [SampleValueText] [nvarchar] (255),
> [SampleValueNumeric] [float]
> ) ON [PRIMARY]
> Now when the Mib value is numeric the text column would be null, and
> when the mib value is text the numeric column would be null.
>
> When i ask what is better, i mean overall better (performance,
> usability, extendability, etc)?
> Thanks in advance
> Mark|||I would probably go with a three table design: a master table that had the
common definitions and, then, two 1-to1 or 1-to-many child related tables to
store the partiallly populated information. NULL-valued columns suit some
purposes but not usually the condidtion where you just don't know the
information or it is not applicable for some of the rows. That indicates
that it is not an attribute of the parent entitiy but subordinate entity
altogether.
Sincerely,
Anthony Thomas
"wei xiao[MS]" <weix@.online.microsoft.com> wrote in message
news:41beb22b$1@.news.microsoft.com...
The one table is probably the better one. Its row size is only slightly
larger than SnmpMibDataText in the two table approach. You have one less
table to manage.
--
Wei Xiao
Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mark Oueis" <markoueis@.hotmail.com> wrote in message
news:b1800bd3.0412131123.7d5e2802@.posting.google.com...
> I have a table that stores SNMP Mib values.
> These values can be either numeric or text.
> I would like to store the text values in a varchar column and the
> numeric values in a double column. This of course would help
> performance amongs other things.
> The question is this:
> Is it better to have two tables, one that stores the numeric values
> and one that stores the text values.
> ex)
> CREATE TABLE [dbo].[SnmpMibDataText] (
> [SnmpMibDeviceID] [int] NOT NULL ,
> [SampleTimestamp] [datetime] NOT NULL ,
> [SampleValue] [nvarchar] (255) NOT NULL
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[SnmpMibDataNumeric] (
> [SnmpMibDeviceID] [int] NOT NULL ,
> [SampleTimestamp] [datetime] NOT NULL ,
> [SampleValue] [float] NOT NULL
> ) ON [PRIMARY]
>
> Or is it better to have 1 table with two columns, one for text and one
> for numeric?
> CREATE TABLE [dbo].[SnmpMibData] (
> [SnmpMibDeviceID] [int] NOT NULL ,
> [SampleTimestamp] [datetime] NOT NULL ,
> [SampleValueText] [nvarchar] (255),
> [SampleValueNumeric] [float]
> ) ON [PRIMARY]
> Now when the Mib value is numeric the text column would be null, and
> when the mib value is text the numeric column would be null.
>
> When i ask what is better, i mean overall better (performance,
> usability, extendability, etc)?
> Thanks in advance
> Mark