help with a query

I need some help creating a QUERY..
here's an example of what I need to do: say for instance my database
contains 3 values:
4012345
3012345
4055555
I want to create a query that will count the number of records where
the last 5 are unique. In the example that count would equal 2. I won't
know what the last 5 digits are.
How would I go about this? My first though would be to use DISTINCT,
but I don't know how to format the SELECT DISINCT part of the query..
is there some kind of RIGHT(field,5) method I can use?
any help would be appreciated.SELECT COUNT(DISTINCT RIGHT(colname,5))
FROM TableName
On a big table, however, this will perform horribly, because every single
row will need to be passed through the string function.
If the right-most five digits of the number mean something special, then
perhaps you should consider storing those digits in a separate column.
<kevin@.questionmark.com> wrote in message
news:1122924156.826395.283970@.g49g2000cwa.googlegroups.com...
>I need some help creating a QUERY..
> here's an example of what I need to do: say for instance my database
> contains 3 values:
> 4012345
> 3012345
> 4055555
> I want to create a query that will count the number of records where
> the last 5 are unique. In the example that count would equal 2. I won't
> know what the last 5 digits are.
> How would I go about this? My first though would be to use DISTINCT,
> but I don't know how to format the SELECT DISINCT part of the query..
> is there some kind of RIGHT(field,5) method I can use?
> any help would be appreciated.
>|||>> is there some kind of RIGHT(field,5) method I can use?
Yes.
Anith|||Please post DDL along with the sample data and expected results, so we can
better answer your question [http://www.aspfaq.com/etiquette.asp?id=5006]
How about this (completed untested, due to lack of DDL):
SELECT COUNT(*) AS Count of FROM
(SELECT DISTINCT(SUBSTRING(MadeUpColumn, 4, 4) FROM MadeUpTable) FakeTable
Do you find yourself needing to break out the right 4 digits on a regular
basis? If so, you might want to consider creating separate columns for the
leading digits and trailing digits - that way, you would not need to use
parsing functions and you could place appropriate indices on the columns to
be searched/summarized.
IHTH
Jeremy
<kevin@.questionmark.com> wrote in message
news:1122924156.826395.283970@.g49g2000cwa.googlegroups.com...
> I need some help creating a QUERY..
> here's an example of what I need to do: say for instance my database
> contains 3 values:
> 4012345
> 3012345
> 4055555
> I want to create a query that will count the number of records where
> the last 5 are unique. In the example that count would equal 2. I won't
> know what the last 5 digits are.
> How would I go about this? My first though would be to use DISTINCT,
> but I don't know how to format the SELECT DISINCT part of the query..
> is there some kind of RIGHT(field,5) method I can use?
> any help would be appreciated.
>|||Sorry, for some reason I mistook your requirement as a need to split up a
phone number, so I used the right four characters instead of the right 5
characters - this should be better:
SELECT COUNT(DISTINCT(SUBSTRING(MadeUpColumn, 3, 5)) FROM MadeUpTable
"Jeremy Williams" <jeremydwill@.netscape.net> wrote in message
news:eX8sSItlFHA.2860@.TK2MSFTNGP15.phx.gbl...
> Please post DDL along with the sample data and expected results, so we can
> better answer your question [http://www.aspfaq.com/etiquette.asp?id=5006]
> How about this (completed untested, due to lack of DDL):
> SELECT COUNT(*) AS Count of FROM
> (SELECT DISTINCT(SUBSTRING(MadeUpColumn, 4, 4) FROM MadeUpTable) FakeTable
> Do you find yourself needing to break out the right 4 digits on a regular
> basis? If so, you might want to consider creating separate columns for the
> leading digits and trailing digits - that way, you would not need to use
> parsing functions and you could place appropriate indices on the columns
to
> be searched/summarized.
> IHTH
> Jeremy
> <kevin@.questionmark.com> wrote in message
> news:1122924156.826395.283970@.g49g2000cwa.googlegroups.com...
>|||> SELECT COUNT(DISTINCT(SUBSTRING(MadeUpColumn, 3, 5)) FROM MadeUpTable
Well, this assumes the value will always be exactly 8 digits... more sample
data would be useful, but the OP said right 5 characters...|||So what are you saying, I should have used RIGHT instead of SUBSTRING, just
because the OP said they wanted the right 5 characters? :-)
Just another example of "open mouth, insert foot". Thanks for the
correction, Aaron!
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23pPg4btlFHA.3336@.tk2msftngp13.phx.gbl...
> Well, this assumes the value will always be exactly 8 digits... more
sample
> data would be useful, but the OP said right 5 characters...
>|||Well, I don't necessarily think your solution is bad (or constitutes
foot-in-mouth syndrome); I prefer substring myself. Just trying to pre-empt
the inevitable, "Hey, Jeremy, this only works on columns where the value has
exactly 8 digits! Thanks a LOT!" ;-)
"Jeremy Williams" <jeremydwill@.netscape.net> wrote in message
news:urH3w3tlFHA.3316@.TK2MSFTNGP14.phx.gbl...
> So what are you saying, I should have used RIGHT instead of SUBSTRING,
> just
> because the OP said they wanted the right 5 characters? :-)
> Just another example of "open mouth, insert foot". Thanks for the
> correction, Aaron!
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. No wonder that you are mimicing
a COBOL program and file.
If these "last five digits" (are they CHAR(7) or INTEGER or what?)
have a meaning apart from the **column**, then they need to be modeled
as another data element.

Help to Configure SQL 2005 to listen on a specific Port

I am trying to configure my port for my SQL 2005 named instance. When
I look under the TCP/IP properties dialog box on the IP Address tab
in the see several IP addresses in format IP1,IP2,IP3 and finally
IPALL. Which one am I suppose to configure? When I look at the IP
addresses I don't see the virtual IP address for my SQL Server
Instance. Can anyone help me on this.
I looked at BOL and it says "Right-click each address, and then click
Properties to identify the IP address that you wish to configure." But
right cliking on the address does not give me the properties option.
The server that I am working with is in a Cluster.
ThanksThe multiple IP addresses indicate you have multiple network connections.
You can set a different port for each connection OR you can set the same
port on all connections. I would use the 'ALL' option on a cluster.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"shub" <shubtech@.gmail.com> wrote in message
news:1193252815.417451.311320@.y27g2000pre.googlegroups.com...
>I am trying to configure my port for my SQL 2005 named instance. When
> I look under the TCP/IP properties dialog box on the IP Address tab
> in the see several IP addresses in format IP1,IP2,IP3 and finally
> IPALL. Which one am I suppose to configure? When I look at the IP
> addresses I don't see the virtual IP address for my SQL Server
> Instance. Can anyone help me on this.
> I looked at BOL and it says "Right-click each address, and then click
> Properties to identify the IP address that you wish to configure." But
> right cliking on the address does not give me the properties option.
>
> The server that I am working with is in a Cluster.
> Thanks
>|||On Oct 24, 9:05 pm, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
> The multiple IP addresses indicate you have multiple network connections.
> You can set a different port for each connection OR you can set the same
> port on all connections. I would use the 'ALL' option on a cluster.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
> "shub" <shubt...@.gmail.com> wrote in message
> news:1193252815.417451.311320@.y27g2000pre.googlegroups.com...
>
> >I am trying to configure my port for my SQL 2005 named instance. When
> > I look under the TCP/IP properties dialog box on the IP Address tab
> > in the see several IP addresses in format IP1,IP2,IP3 and finally
> > IPALL. Which one am I suppose to configure? When I look at the IP
> > addresses I don't see the virtual IP address for my SQL Server
> > Instance. Can anyone help me on this.
> > I looked at BOL and it says "Right-click each address, and then click
> > Properties to identify the IP address that you wish to configure." But
> > right cliking on the address does not give me the properties option.
> > The server that I am working with is in a Cluster.
> > Thanks- Hide quoted text -
> - Show quoted text -
Thanks for your help. As far as deciding the port number itself do you
follow any guidelines or is there any guidelines by Microsoft?|||For whatever reason, SQL2005 Configuration Manager does NOT list the virtual
server IP address used by the SQL instance. I have no idea why.
But if ListenAll is enabled, you can use Configuration Manager to set TCP
Port under IP All on the Ip Address tab to a port of your choice, and upon
restart the instance the port will be used. Also, you only need to do this on
one of the nodes, and the cluster service will replicate the setting to the
other nodes.
Linchi
"shub" wrote:
> I am trying to configure my port for my SQL 2005 named instance. When
> I look under the TCP/IP properties dialog box on the IP Address tab
> in the see several IP addresses in format IP1,IP2,IP3 and finally
> IPALL. Which one am I suppose to configure? When I look at the IP
> addresses I don't see the virtual IP address for my SQL Server
> Instance. Can anyone help me on this.
> I looked at BOL and it says "Right-click each address, and then click
> Properties to identify the IP address that you wish to configure." But
> right cliking on the address does not give me the properties option.
>
> The server that I am working with is in a Cluster.
> Thanks
>|||I either use the autoconfig port and lock it in or I pick something that
isn't in use.
I don't recall any guidelines for this from Microsoft.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"shub" <shubtech@.gmail.com> wrote in message
news:1193279519.381435.155240@.i13g2000prf.googlegroups.com...
> On Oct 24, 9:05 pm, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
>> The multiple IP addresses indicate you have multiple network connections.
>> You can set a different port for each connection OR you can set the same
>> port on all connections. I would use the 'ALL' option on a cluster.
>> --
>> Geoff N. Hiten
>> Senior SQL Infrastructure Consultant
>> Microsoft SQL Server MVP
>> "shub" <shubt...@.gmail.com> wrote in message
>> news:1193252815.417451.311320@.y27g2000pre.googlegroups.com...
>>
>> >I am trying to configure my port for my SQL 2005 named instance. When
>> > I look under the TCP/IP properties dialog box on the IP Address tab
>> > in the see several IP addresses in format IP1,IP2,IP3 and finally
>> > IPALL. Which one am I suppose to configure? When I look at the IP
>> > addresses I don't see the virtual IP address for my SQL Server
>> > Instance. Can anyone help me on this.
>> > I looked at BOL and it says "Right-click each address, and then click
>> > Properties to identify the IP address that you wish to configure." But
>> > right cliking on the address does not give me the properties option.
>> > The server that I am working with is in a Cluster.
>> > Thanks- Hide quoted text -
>> - Show quoted text -
> Thanks for your help. As far as deciding the port number itself do you
> follow any guidelines or is there any guidelines by Microsoft?
>

Help setting up replication on remote IIS

Hello,

I am trying to setup merge replication for a sqlmobile database. I was able to set it up on a test environment which the sql instance and the iis were on the same box. I am trying now to go live. Our sql instance is on another box than the iis. I am now getting a 404 error when trying to test in the url https://www.domain.com/sqlmobile/sqlcesa30.dll?diag Is there a walk through for iis running on a different box, or does someone know of a solution?

HTTP Error 404 - File or directory not found.
Internet Information Services (IIS)

Hi John
questions:
-what do you understand under a "box"?
-can you open the https://www.domain.com/sqlmobile/sqlcesa30.dll?diag site on the server where you are running the IIS? with the ipAdress of the server instead of the www.domain.com
-if so, can you access it from another computer in the network?
you can't synchronize until you can find this dialog from your mobile browser.
-have you updatet the Client Agent (sqlcesa30.dll) with the WebSynchronisation Wizard accordning to the new configuration?

- as you probably know, you are connecting with the anonymous user IUSR_servername of the SQL Server from the mobile device to the webserver, so on the IIS-Server you have to know this user an give him the needed accessrights

It seems to me that there is a problem with the configuration or the access rights

Greets Florian
|||

there are only two big "gotchas" with setting up replication with a separate IIS and SQL Server machine.

1. the account you plan to use that will provide credentials between the ISAPI dll (sqcesa30.dll) and the distributor must be known to both machines. If you were using the IIS Anonymous user's credentials (IUSR_{your machine name}), this will no longer work, because the distributor has no knowledge of this account when running on a separate machine. if both machines are on a domain, instead of IUSR_, use a domain account that it known to both IIS and SQL Server machines and grant it permissions on the IIS Virtual Directory, the publication, and the published articles themselves.

2. you must install the SQL Mobile server tools on both the IIS and SQL Server machine. use the SQL Server 2005 setup disc on the IIS machine, do a custom install, and select only the server tools for SQL Mobile.

Darren