finding lower case data

I have a table with a VIN number column, I need to find all entries where th
e
VIN contains a lower case character, example '4d0131P2X4E146985'. This recor
d
should be included in the results because it contains 'd'.. What would be th
e
easiest way to write a query to return these, can I use regular expression i
n
T-SQL...?
Thanks,
DanAlien2_51,
Check the view INFORMATION_SCHEMA.COLUMNS to see the collation of the column
in question. I guess it is using a case insensitive one. Use the COLLATE
clause to tell SQL Server which collation to use during the operation and us
e
a case sensitive one. You can also use function binary_checksum.
select *
from
(select '4d0131P2X4E146985' as c1
union all
select '4D0131P2X4E146985' as c1
) as t1
where c1 != upper(c1) collate SQL_Latin1_General_CP1_CS_AS
select *
from
(select '4d0131P2X4E146985' as c1
union all
select '4D0131P2X4E146985' as c1
) as t1
where binary_checksum(c1) != binary_checksum(upper(c1))
AMB
"Alien2_51" wrote:

> I have a table with a VIN number column, I need to find all entries where
the
> VIN contains a lower case character, example '4d0131P2X4E146985'. This rec
ord
> should be included in the results because it contains 'd'.. What would be
the
> easiest way to write a query to return these, can I use regular expression
in
> T-SQL...?
> Thanks,
> Dan|||You could do this:
select VIN
from yourTable
where VIN like '%[abcdefghijklmnopqrstuvwxyz]%'
collate Latin1_General_CS_AS
-- or
select VIN
from yourTable
where VIN like '%[a-z]%'
collate Latin1_General_BIN
Note that it will not work to use the range [a-z] in the first
example, because in the CS_AS collation, [a-z] contains the
uppercase letters A through Y.
Steve Kass
Drew University
http://www.stevekass.com
Alien2_51 wrote:

>I have a table with a VIN number column, I need to find all entries where t
he
>VIN contains a lower case character, example '4d0131P2X4E146985'. This reco
rd
>should be included in the results because it contains 'd'.. What would be t
he
>easiest way to write a query to return these, can I use regular expression
in
>T-SQL...?
>Thanks,
>Dan
>|||Thanks Alejandro... Just what I needed, and so simple...
DB
"Alejandro Mesa" wrote:
> Alien2_51,
> Check the view INFORMATION_SCHEMA.COLUMNS to see the collation of the colu
mn
> in question. I guess it is using a case insensitive one. Use the COLLATE
> clause to tell SQL Server which collation to use during the operation and
use
> a case sensitive one. You can also use function binary_checksum.
> select *
> from
> (select '4d0131P2X4E146985' as c1
> union all
> select '4D0131P2X4E146985' as c1
> ) as t1
> where c1 != upper(c1) collate SQL_Latin1_General_CP1_CS_AS
> select *
> from
> (select '4d0131P2X4E146985' as c1
> union all
> select '4D0131P2X4E146985' as c1
> ) as t1
> where binary_checksum(c1) != binary_checksum(upper(c1))
>
> AMB
>
> "Alien2_51" wrote:
>|||Alien, I guess you might want to check this out
http://vadivel.blogspot.com/2003/12...e-searches.html
Best Regards
Vadivel
http://vadivel.blogspot.com
"Alien2_51" wrote:

> I have a table with a VIN number column, I need to find all entries where
the
> VIN contains a lower case character, example '4d0131P2X4E146985'. This rec
ord
> should be included in the results because it contains 'd'.. What would be
the
> easiest way to write a query to return these, can I use regular expression
in
> T-SQL...?
> Thanks,
> Dan

Finding duplicate entries in a "smart" way - by comparing first two words

What is the best way to compare two entries in a single table where
the two fields are "almost" the same?

For example, I would like to write a query that would compare the
first two words in a "company" field. If they are the same, I would
like to output them.

For example, "20th Century" and "20th Century Fox" in the company
field would be the same.

How do I do this? Do I need to use a cursor? Is it as simple as using
"Like?"Here's an example:

CREATE TABLE Sometable (company VARCHAR(20) PRIMARY KEY)

INSERT INTO Sometable VALUES ('20th Century')
INSERT INTO Sometable VALUES ('20th Century Fox')
INSERT INTO Sometable VALUES ('Something Else')

SELECT company
FROM Sometable AS S
JOIN
(SELECT first_two_words
FROM
(SELECT company,
LEFT(company,
CHARINDEX(' ',company+' ',CHARINDEX(' ',company)+1))
AS first_two_words
FROM Sometable) AS X
GROUP BY first_two_words
HAVING COUNT(*)>1) AS T
ON T.first_two_words =
LEFT(company,CHARINDEX(' ',company+' ',CHARINDEX(' ',company)+1))

--
David Portas
----
Please reply only to the newsgroup
--|||>> a query that would compare the first two words in a "company"
field [sic]. If they are the same, I would like to output them. <<

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.

CREATE TABLE Foobar
(foo_key INTEGER NOT NULL PRIMARY KEY,
company CHAR(35) NOT NULL,
...);

SELECT F1.foo_key, F2.foo_key
FROM Foobar AS F1, Foobar AS F2
WHERE CHARINDEX(F1.company, F2.company) = 1
AND F1.foo_key < F2.foo_key;

finding a row number

Is there a function or something similar in sql 2005 that can tell you what
row a record is in a table without any indexes?
For example, if I tried to execute something like
select row_number() from mytable where id = '30532'
and the record were the 100th record, I'd get back a result of 100.
MGWhy is that important? Rows are in a table in no particular order. For it
to have meaning, the ROW_NUMBER() function has to have an ORDER BY
associated with it.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Hurme" <michael.geles@.thomson.com> wrote in message
news:59CFB5F8-0709-4FB7-B178-4127F99A2FDC@.microsoft.com...
Is there a function or something similar in sql 2005 that can tell you what
row a record is in a table without any indexes?
For example, if I tried to execute something like
select row_number() from mytable where id = '30532'
and the record were the 100th record, I'd get back a result of 100.
MG|||Wrong newsgroup but you could try
SELECT *
FROM
(
SELECT row_number() OVER (ORDER BY column_your_dbtable_is_sortedby
asc) as rownum, id
FROM mytable
) a
WHERE id = '30532'
"Hurme" <michael.geles@.thomson.com> wrote in message
news:59CFB5F8-0709-4FB7-B178-4127F99A2FDC@.microsoft.com...
> Is there a function or something similar in sql 2005 that can tell you
> what
> row a record is in a table without any indexes?
> For example, if I tried to execute something like
> select row_number() from mytable where id = '30532'
> and the record were the 100th record, I'd get back a result of 100.
> --
> MG|||"Hurme" <michael.geles@.thomson.com> wrote in message
news:59CFB5F8-0709-4FB7-B178-4127F99A2FDC@.microsoft.com...
> Is there a function or something similar in sql 2005 that can tell you
> what
> row a record is in a table without any indexes?
> For example, if I tried to execute something like
> select row_number() from mytable where id = '30532'
> and the record were the 100th record, I'd get back a result of 100.
> --
> MG
Tables have no order so there is no such thing as a determinate row number.
"100th record" is arbitrary and meaningless unless you state some ordering
based on the data itself.
If you just want some arbitrary row to call the "100th" you can just do TOP
1:
SELECT TOP 1 col1, col2, col3
FROM mytable;
David Portas

finding a row number

Is there a function or something similar in sql 2005 that can tell you what
row a record is in a table without any indexes?
For example, if I tried to execute something like
select row_number() from mytable where id = '30532'
and the record were the 100th record, I'd get back a result of 100.
MG
Why is that important? Rows are in a table in no particular order. For it
to have meaning, the ROW_NUMBER() function has to have an ORDER BY
associated with it.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Hurme" <michael.geles@.thomson.com> wrote in message
news:59CFB5F8-0709-4FB7-B178-4127F99A2FDC@.microsoft.com...
Is there a function or something similar in sql 2005 that can tell you what
row a record is in a table without any indexes?
For example, if I tried to execute something like
select row_number() from mytable where id = '30532'
and the record were the 100th record, I'd get back a result of 100.
MG
|||Wrong newsgroup but you could try
SELECT *
FROM
(
SELECT row_number() OVER (ORDER BY column_your_dbtable_is_sortedby
asc) as rownum, id
FROM mytable
) a
WHERE id = '30532'
"Hurme" <michael.geles@.thomson.com> wrote in message
news:59CFB5F8-0709-4FB7-B178-4127F99A2FDC@.microsoft.com...
> Is there a function or something similar in sql 2005 that can tell you
> what
> row a record is in a table without any indexes?
> For example, if I tried to execute something like
> select row_number() from mytable where id = '30532'
> and the record were the 100th record, I'd get back a result of 100.
> --
> MG
|||"Hurme" <michael.geles@.thomson.com> wrote in message
news:59CFB5F8-0709-4FB7-B178-4127F99A2FDC@.microsoft.com...
> Is there a function or something similar in sql 2005 that can tell you
> what
> row a record is in a table without any indexes?
> For example, if I tried to execute something like
> select row_number() from mytable where id = '30532'
> and the record were the 100th record, I'd get back a result of 100.
> --
> MG
Tables have no order so there is no such thing as a determinate row number.
"100th record" is arbitrary and meaningless unless you state some ordering
based on the data itself.
If you just want some arbitrary row to call the "100th" you can just do TOP
1:
SELECT TOP 1 col1, col2, col3
FROM mytable;
David Portas
sql

finding a row number

Is there a function or something similar in sql 2005 that can tell you what
row a record is in a table without any indexes?
For example, if I tried to execute something like
select row_number() from mytable where id = '30532'
and the record were the 100th record, I'd get back a result of 100.
--
MGWhy is that important? Rows are in a table in no particular order. For it
to have meaning, the ROW_NUMBER() function has to have an ORDER BY
associated with it.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Hurme" <michael.geles@.thomson.com> wrote in message
news:59CFB5F8-0709-4FB7-B178-4127F99A2FDC@.microsoft.com...
Is there a function or something similar in sql 2005 that can tell you what
row a record is in a table without any indexes?
For example, if I tried to execute something like
select row_number() from mytable where id = '30532'
and the record were the 100th record, I'd get back a result of 100.
--
MG|||Wrong newsgroup but you could try
SELECT *
FROM
(
SELECT row_number() OVER (ORDER BY column_your_dbtable_is_sortedby
asc) as rownum, id
FROM mytable
) a
WHERE id = '30532'
"Hurme" <michael.geles@.thomson.com> wrote in message
news:59CFB5F8-0709-4FB7-B178-4127F99A2FDC@.microsoft.com...
> Is there a function or something similar in sql 2005 that can tell you
> what
> row a record is in a table without any indexes?
> For example, if I tried to execute something like
> select row_number() from mytable where id = '30532'
> and the record were the 100th record, I'd get back a result of 100.
> --
> MG|||"Hurme" <michael.geles@.thomson.com> wrote in message
news:59CFB5F8-0709-4FB7-B178-4127F99A2FDC@.microsoft.com...
> Is there a function or something similar in sql 2005 that can tell you
> what
> row a record is in a table without any indexes?
> For example, if I tried to execute something like
> select row_number() from mytable where id = '30532'
> and the record were the 100th record, I'd get back a result of 100.
> --
> MG
Tables have no order so there is no such thing as a determinate row number.
"100th record" is arbitrary and meaningless unless you state some ordering
based on the data itself.
If you just want some arbitrary row to call the "100th" you can just do TOP
1:
SELECT TOP 1 col1, col2, col3
FROM mytable;
--
David Portas

Finding a column in all tables within the DB

How do I list all tables that contain a particular column? For example, how do I list all tables that contain the column lastname within my database called manager? Thanx.

Quote:

Originally Posted by Rob0000

How do I list all tables that contain a particular column? For example, how do I list all tables that contain the column lastname within my database called manager? Thanx.

i ran into this issue the other day

SELECT name FROM sysobjects WHERE id IN ( SELECT id FROM syscolumns WHERE name like '%lastname %' )

if your doing this in SQL server just open up a sql command and execute this

Find number of records in text file.

Hi All,
Is there a way to find out how many records (rows) in the text file in
sql. ( It is a fix lengh record)
Example: I have a text file in my local machine
c:\test\t1.txt
c:\test\t2.txt
in t1.txt I have these rows:
AAAAAAAAAAAAAAAAAAAAAAAAAAA
BBBBBBBBBBBBBBBBBBBBBBBBBBB
CCCCCCCCCCCCCCCCCCCCCCCCCCC
and t2.txt have this rows:
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
so t1.txt should give me 3 rows and t2.txt should give me 1 row.
Please let me know. Thanks in advance
Teed Leentuyen01@.yahoo.com wrote:
> Hi All,
> Is there a way to find out how many records (rows) in the text file in
> sql. ( It is a fix lengh record)
> Example: I have a text file in my local machine
> c:\test\t1.txt
> c:\test\t2.txt
> in t1.txt I have these rows:
> AAAAAAAAAAAAAAAAAAAAAAAAAAA
> BBBBBBBBBBBBBBBBBBBBBBBBBBB
> CCCCCCCCCCCCCCCCCCCCCCCCCCC
> and t2.txt have this rows:
> XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
> so t1.txt should give me 3 rows and t2.txt should give me 1 row.
> Please let me know. Thanks in advance
> Teed Lee
>
One way would be to use OPENROWSET, documented in Books Online, and lots
of samples online.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thank,
I did try it
SELECT count(*) FROM OpenRowset('MSDASQL', 'Driver={Microsoft Text
Driver (*.txt; *.csv)};DefaultDir=c:\;','select * from testing.txt')
I got these errors:
OLE DB provider "MSDASQL" for linked server "(null)" returned message
"[Microsoft][ODBC Text Driver] Disk or network error.".
OLE DB provider "MSDASQL" for linked server "(null)" returned message
"[Microsoft][ODBC Text Driver]General error Unable to open registry
key
'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
0x482c06b4 Text'.".
OLE DB provider "MSDASQL" for linked server "(null)" returned message
"[Microsoft][ODBC Text Driver]General error Unable to open registry
key
'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
0x482c06b4 Text'.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL"
for linked server "(null)".
I try different provider : Microsoft.Jet.OLEDB.4.0, SQLOLEDB ...
but all of them give me error.
Please help. Thanks
Tracy McKibben wrote:
> ntuyen01@.yahoo.com wrote:
> One way would be to use OPENROWSET, documented in Books Online, and lots
> of samples online.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||It will work with the Jet driver. Something along the lines
of the following works with a text file located at
C:\Test.txt:
select count(*)
from OpenRowset('Microsoft.Jet.OLEDB.4.0',
'Text;Database=C:\;HDR=NO','select * from Test.txt')
-Sue
On 14 Aug 2006 15:13:18 -0700, ntuyen01@.yahoo.com wrote:
[vbcol=seagreen]
>Thank,
>I did try it
>SELECT count(*) FROM OpenRowset('MSDASQL', 'Driver={Microsoft Text
>Driver (*.txt; *.csv)};DefaultDir=c:\;','select * from testing.txt')
>I got these errors:
>OLE DB provider "MSDASQL" for linked server "(null)" returned message
>"[Microsoft][ODBC Text Driver] Disk or network error.".
>OLE DB provider "MSDASQL" for linked server "(null)" returned message
>"[Microsoft][ODBC Text Driver]General error Unable to open registry
key
>'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
>0x482c06b4 Text'.".
>OLE DB provider "MSDASQL" for linked server "(null)" returned message
>"[Microsoft][ODBC Text Driver]General error Unable to open registry
key
>'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
>0x482c06b4 Text'.".
>Msg 7303, Level 16, State 1, Line 1
>Cannot initialize the data source object of OLE DB provider "MSDASQL"
>for linked server "(null)".
>
>I try different provider : Microsoft.Jet.OLEDB.4.0, SQLOLEDB ...
>but all of them give me error.
>Please help. Thanks
>Tracy McKibben wrote:|||Thank You,
I try this statement
select count(*)
from OpenRowset('Microsoft.Jet.OLEDB.4.0',
'Text;Database=\\testingserver\filedir;H
DR=NO','select * from
Test.txt')
but when I try to run your statement I got this error:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider
"Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
The different between your statement vs. mine is "my file is on the
network"
Do I have to setup ODBC connect on the server where the file locate or
somehting.
Would you please tell me what else I need to setup or what do I do
wrong here.
Thanks again,
Teed Lee
Sue Hoegemeier wrote:[vbcol=seagreen]
> It will work with the Jet driver. Something along the lines
> of the following works with a text file located at
> C:\Test.txt:
> select count(*)
> from OpenRowset('Microsoft.Jet.OLEDB.4.0',
> 'Text;Database=C:\;HDR=NO','select * from Test.txt')
> -Sue
> On 14 Aug 2006 15:13:18 -0700, ntuyen01@.yahoo.com wrote:
>|||Hi,
I also try this:
EXEC sp_addlinkedserver filedir, 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0', '\\testserver\filedir', NULL, 'Text'
Sue Hoegemeier wrote:[vbcol=seagreen]
> It will work with the Jet driver. Something along the lines
> of the following works with a text file located at
> C:\Test.txt:
> select count(*)
> from OpenRowset('Microsoft.Jet.OLEDB.4.0',
> 'Text;Database=C:\;HDR=NO','select * from Test.txt')
> -Sue
> On 14 Aug 2006 15:13:18 -0700, ntuyen01@.yahoo.com wrote:
>|||ntuyen01@.yahoo.com wrote:
> Thank You,
> I try this statement
> select count(*)
> from OpenRowset('Microsoft.Jet.OLEDB.4.0',
> 'Text;Database=\\testingserver\filedir;H
DR=NO','select * from
> Test.txt')
> but when I try to run your statement I got this error:
> OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
> returned message "Unspecified error".
> Msg 7303, Level 16, State 1, Line 1
> Cannot initialize the data source object of OLE DB provider
> "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
> The different between your statement vs. mine is "my file is on the
> network"
> Do I have to setup ODBC connect on the server where the file locate or
> somehting.
> Would you please tell me what else I need to setup or what do I do
> wrong here.
>
Does the SQL service account have permission to access that network
share? Does this work if you put the text file on a local drive?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I work fine on the local account. On the local account I have to setup
the "SQL Server Surface Area Configuration"
The problem I have is:
my sql server is on different machine Server "MachineA"
my files is on difrrent server "MachineB"
I am login as my account and I am able to get in (permission) to both
server.
On the "MachineA" do I have to set something to connect to "MachineB"
or
On the MachineB server do I have to set something
('Microsoft.Jet.OLEDB.4.0')?
I am not sure. Please help.
Teed
Tracy McKibben wrote:
> ntuyen01@.yahoo.com wrote:
> Does the SQL service account have permission to access that network
> share? Does this work if you put the text file on a local drive?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||ntuyen01@.yahoo.com wrote:
> I work fine on the local account. On the local account I have to setup
> the "SQL Server Surface Area Configuration"
> The problem I have is:
> my sql server is on different machine Server "MachineA"
> my files is on difrrent server "MachineB"
> I am login as my account and I am able to get in (permission) to both
> server.
> On the "MachineA" do I have to set something to connect to "MachineB"
> or
> On the MachineB server do I have to set something
> ('Microsoft.Jet.OLEDB.4.0')?
> I am not sure. Please help.
> Teed
Huh? I repeat my question - "Does the SQL service account have
permission to access that network share?"
If I understand your response, it sounds like you have SQL running under
the "Local System" context. If so, you cannot access network resources
this way - SQL Server MUST run under a domain account, and that domain
account MUST have permission to access the desired network resources.
This has nothing to do with the login that you use to connect to SQL server.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi,
Yes,
I login (domain\username) is part of the group (domain\groupname) in
the SQL service account and also my login (domain\username) also have
permission to access that network share.
Teed
Tracy McKibben wrote:
> ntuyen01@.yahoo.com wrote:
> Huh? I repeat my question - "Does the SQL service account have
> permission to access that network share?"
> If I understand your response, it sounds like you have SQL running under
> the "Local System" context. If so, you cannot access network resources
> this way - SQL Server MUST run under a domain account, and that domain
> account MUST have permission to access the desired network resources.
> This has nothing to do with the login that you use to connect to SQL serve
r.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Find number of records in text file.

Hi All,
Is there a way to find out how many records (rows) in the text file in
sql. ( It is a fix lengh record)
Example: I have a text file in my local machine
c:\test\t1.txt
c:\test\t2.txt
in t1.txt I have these rows:
AAAAAAAAAAAAAAAAAAAAAAAAAAA
BBBBBBBBBBBBBBBBBBBBBBBBBBB
CCCCCCCCCCCCCCCCCCCCCCCCCCC
and t2.txt have this rows:
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
so t1.txt should give me 3 rows and t2.txt should give me 1 row.
Please let me know. Thanks in advance
Teed Leentuyen01@.yahoo.com wrote:
> Hi All,
> Is there a way to find out how many records (rows) in the text file in
> sql. ( It is a fix lengh record)
> Example: I have a text file in my local machine
> c:\test\t1.txt
> c:\test\t2.txt
> in t1.txt I have these rows:
> AAAAAAAAAAAAAAAAAAAAAAAAAAA
> BBBBBBBBBBBBBBBBBBBBBBBBBBB
> CCCCCCCCCCCCCCCCCCCCCCCCCCC
> and t2.txt have this rows:
> XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
> so t1.txt should give me 3 rows and t2.txt should give me 1 row.
> Please let me know. Thanks in advance
> Teed Lee
>
One way would be to use OPENROWSET, documented in Books Online, and lots
of samples online.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thank,
I did try it
SELECT count(*) FROM OpenRowset('MSDASQL', 'Driver={Microsoft Text
Driver (*.txt; *.csv)};DefaultDir=c:\;','select * from testing.txt')
I got these errors:
OLE DB provider "MSDASQL" for linked server "(null)" returned message
"[Microsoft][ODBC Text Driver] Disk or network error.".
OLE DB provider "MSDASQL" for linked server "(null)" returned message
"[Microsoft][ODBC Text Driver]General error Unable to open registry key
'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
0x482c06b4 Text'.".
OLE DB provider "MSDASQL" for linked server "(null)" returned message
"[Microsoft][ODBC Text Driver]General error Unable to open registry key
'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
0x482c06b4 Text'.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL"
for linked server "(null)".
I try different provider : Microsoft.Jet.OLEDB.4.0, SQLOLEDB ...
but all of them give me error.
Please help. Thanks
Tracy McKibben wrote:
> ntuyen01@.yahoo.com wrote:
> > Hi All,
> >
> > Is there a way to find out how many records (rows) in the text file in
> > sql. ( It is a fix lengh record)
> >
> > Example: I have a text file in my local machine
> >
> > c:\test\t1.txt
> > c:\test\t2.txt
> >
> > in t1.txt I have these rows:
> > AAAAAAAAAAAAAAAAAAAAAAAAAAA
> > BBBBBBBBBBBBBBBBBBBBBBBBBBB
> > CCCCCCCCCCCCCCCCCCCCCCCCCCC
> >
> > and t2.txt have this rows:
> > XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
> >
> > so t1.txt should give me 3 rows and t2.txt should give me 1 row.
> >
> > Please let me know. Thanks in advance
> >
> > Teed Lee
> >
> One way would be to use OPENROWSET, documented in Books Online, and lots
> of samples online.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||It will work with the Jet driver. Something along the lines
of the following works with a text file located at
C:\Test.txt:
select count(*)
from OpenRowset('Microsoft.Jet.OLEDB.4.0',
'Text;Database=C:\;HDR=NO','select * from Test.txt')
-Sue
On 14 Aug 2006 15:13:18 -0700, ntuyen01@.yahoo.com wrote:
>Thank,
>I did try it
>SELECT count(*) FROM OpenRowset('MSDASQL', 'Driver={Microsoft Text
>Driver (*.txt; *.csv)};DefaultDir=c:\;','select * from testing.txt')
>I got these errors:
>OLE DB provider "MSDASQL" for linked server "(null)" returned message
>"[Microsoft][ODBC Text Driver] Disk or network error.".
>OLE DB provider "MSDASQL" for linked server "(null)" returned message
>"[Microsoft][ODBC Text Driver]General error Unable to open registry key
>'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
>0x482c06b4 Text'.".
>OLE DB provider "MSDASQL" for linked server "(null)" returned message
>"[Microsoft][ODBC Text Driver]General error Unable to open registry key
>'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
>0x482c06b4 Text'.".
>Msg 7303, Level 16, State 1, Line 1
>Cannot initialize the data source object of OLE DB provider "MSDASQL"
>for linked server "(null)".
>
>I try different provider : Microsoft.Jet.OLEDB.4.0, SQLOLEDB ...
>but all of them give me error.
>Please help. Thanks
>Tracy McKibben wrote:
>> ntuyen01@.yahoo.com wrote:
>> > Hi All,
>> >
>> > Is there a way to find out how many records (rows) in the text file in
>> > sql. ( It is a fix lengh record)
>> >
>> > Example: I have a text file in my local machine
>> >
>> > c:\test\t1.txt
>> > c:\test\t2.txt
>> >
>> > in t1.txt I have these rows:
>> > AAAAAAAAAAAAAAAAAAAAAAAAAAA
>> > BBBBBBBBBBBBBBBBBBBBBBBBBBB
>> > CCCCCCCCCCCCCCCCCCCCCCCCCCC
>> >
>> > and t2.txt have this rows:
>> > XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
>> >
>> > so t1.txt should give me 3 rows and t2.txt should give me 1 row.
>> >
>> > Please let me know. Thanks in advance
>> >
>> > Teed Lee
>> >
>> One way would be to use OPENROWSET, documented in Books Online, and lots
>> of samples online.
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com|||Thank You,
I try this statement
select count(*)
from OpenRowset('Microsoft.Jet.OLEDB.4.0',
'Text;Database=\\testingserver\filedir;HDR=NO','select * from
Test.txt')
but when I try to run your statement I got this error:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider
"Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
The different between your statement vs. mine is "my file is on the
network"
Do I have to setup ODBC connect on the server where the file locate or
somehting.
Would you please tell me what else I need to setup or what do I do
wrong here.
Thanks again,
Teed Lee
Sue Hoegemeier wrote:
> It will work with the Jet driver. Something along the lines
> of the following works with a text file located at
> C:\Test.txt:
> select count(*)
> from OpenRowset('Microsoft.Jet.OLEDB.4.0',
> 'Text;Database=C:\;HDR=NO','select * from Test.txt')
> -Sue
> On 14 Aug 2006 15:13:18 -0700, ntuyen01@.yahoo.com wrote:
> >Thank,
> >
> >I did try it
> >SELECT count(*) FROM OpenRowset('MSDASQL', 'Driver={Microsoft Text
> >Driver (*.txt; *.csv)};DefaultDir=c:\;','select * from testing.txt')
> >
> >I got these errors:
> >OLE DB provider "MSDASQL" for linked server "(null)" returned message
> >"[Microsoft][ODBC Text Driver] Disk or network error.".
> >OLE DB provider "MSDASQL" for linked server "(null)" returned message
> >"[Microsoft][ODBC Text Driver]General error Unable to open registry key
> >'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
> >0x482c06b4 Text'.".
> >OLE DB provider "MSDASQL" for linked server "(null)" returned message
> >"[Microsoft][ODBC Text Driver]General error Unable to open registry key
> >'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
> >0x482c06b4 Text'.".
> >Msg 7303, Level 16, State 1, Line 1
> >Cannot initialize the data source object of OLE DB provider "MSDASQL"
> >for linked server "(null)".
> >
> >
> >I try different provider : Microsoft.Jet.OLEDB.4.0, SQLOLEDB ...
> >but all of them give me error.
> >
> >Please help. Thanks
> >
> >Tracy McKibben wrote:
> >> ntuyen01@.yahoo.com wrote:
> >> > Hi All,
> >> >
> >> > Is there a way to find out how many records (rows) in the text file in
> >> > sql. ( It is a fix lengh record)
> >> >
> >> > Example: I have a text file in my local machine
> >> >
> >> > c:\test\t1.txt
> >> > c:\test\t2.txt
> >> >
> >> > in t1.txt I have these rows:
> >> > AAAAAAAAAAAAAAAAAAAAAAAAAAA
> >> > BBBBBBBBBBBBBBBBBBBBBBBBBBB
> >> > CCCCCCCCCCCCCCCCCCCCCCCCCCC
> >> >
> >> > and t2.txt have this rows:
> >> > XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
> >> >
> >> > so t1.txt should give me 3 rows and t2.txt should give me 1 row.
> >> >
> >> > Please let me know. Thanks in advance
> >> >
> >> > Teed Lee
> >> >
> >>
> >> One way would be to use OPENROWSET, documented in Books Online, and lots
> >> of samples online.
> >>
> >>
> >> --
> >> Tracy McKibben
> >> MCDBA
> >> http://www.realsqlguy.com|||Hi,
I also try this:
EXEC sp_addlinkedserver filedir, 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0', '\\testserver\filedir', NULL, 'Text'
Sue Hoegemeier wrote:
> It will work with the Jet driver. Something along the lines
> of the following works with a text file located at
> C:\Test.txt:
> select count(*)
> from OpenRowset('Microsoft.Jet.OLEDB.4.0',
> 'Text;Database=C:\;HDR=NO','select * from Test.txt')
> -Sue
> On 14 Aug 2006 15:13:18 -0700, ntuyen01@.yahoo.com wrote:
> >Thank,
> >
> >I did try it
> >SELECT count(*) FROM OpenRowset('MSDASQL', 'Driver={Microsoft Text
> >Driver (*.txt; *.csv)};DefaultDir=c:\;','select * from testing.txt')
> >
> >I got these errors:
> >OLE DB provider "MSDASQL" for linked server "(null)" returned message
> >"[Microsoft][ODBC Text Driver] Disk or network error.".
> >OLE DB provider "MSDASQL" for linked server "(null)" returned message
> >"[Microsoft][ODBC Text Driver]General error Unable to open registry key
> >'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
> >0x482c06b4 Text'.".
> >OLE DB provider "MSDASQL" for linked server "(null)" returned message
> >"[Microsoft][ODBC Text Driver]General error Unable to open registry key
> >'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
> >0x482c06b4 Text'.".
> >Msg 7303, Level 16, State 1, Line 1
> >Cannot initialize the data source object of OLE DB provider "MSDASQL"
> >for linked server "(null)".
> >
> >
> >I try different provider : Microsoft.Jet.OLEDB.4.0, SQLOLEDB ...
> >but all of them give me error.
> >
> >Please help. Thanks
> >
> >Tracy McKibben wrote:
> >> ntuyen01@.yahoo.com wrote:
> >> > Hi All,
> >> >
> >> > Is there a way to find out how many records (rows) in the text file in
> >> > sql. ( It is a fix lengh record)
> >> >
> >> > Example: I have a text file in my local machine
> >> >
> >> > c:\test\t1.txt
> >> > c:\test\t2.txt
> >> >
> >> > in t1.txt I have these rows:
> >> > AAAAAAAAAAAAAAAAAAAAAAAAAAA
> >> > BBBBBBBBBBBBBBBBBBBBBBBBBBB
> >> > CCCCCCCCCCCCCCCCCCCCCCCCCCC
> >> >
> >> > and t2.txt have this rows:
> >> > XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
> >> >
> >> > so t1.txt should give me 3 rows and t2.txt should give me 1 row.
> >> >
> >> > Please let me know. Thanks in advance
> >> >
> >> > Teed Lee
> >> >
> >>
> >> One way would be to use OPENROWSET, documented in Books Online, and lots
> >> of samples online.
> >>
> >>
> >> --
> >> Tracy McKibben
> >> MCDBA
> >> http://www.realsqlguy.com|||ntuyen01@.yahoo.com wrote:
> Thank You,
> I try this statement
> select count(*)
> from OpenRowset('Microsoft.Jet.OLEDB.4.0',
> 'Text;Database=\\testingserver\filedir;HDR=NO','select * from
> Test.txt')
> but when I try to run your statement I got this error:
> OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
> returned message "Unspecified error".
> Msg 7303, Level 16, State 1, Line 1
> Cannot initialize the data source object of OLE DB provider
> "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
> The different between your statement vs. mine is "my file is on the
> network"
> Do I have to setup ODBC connect on the server where the file locate or
> somehting.
> Would you please tell me what else I need to setup or what do I do
> wrong here.
>
Does the SQL service account have permission to access that network
share? Does this work if you put the text file on a local drive?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I work fine on the local account. On the local account I have to setup
the "SQL Server Surface Area Configuration"
The problem I have is:
my sql server is on different machine Server "MachineA"
my files is on difrrent server "MachineB"
I am login as my account and I am able to get in (permission) to both
server.
On the "MachineA" do I have to set something to connect to "MachineB"
or
On the MachineB server do I have to set something
('Microsoft.Jet.OLEDB.4.0')?
I am not sure. Please help.
Teed
Tracy McKibben wrote:
> ntuyen01@.yahoo.com wrote:
> > Thank You,
> > I try this statement
> >
> > select count(*)
> > from OpenRowset('Microsoft.Jet.OLEDB.4.0',
> > 'Text;Database=\\testingserver\filedir;HDR=NO','select * from
> > Test.txt')
> >
> > but when I try to run your statement I got this error:
> >
> > OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
> > returned message "Unspecified error".
> > Msg 7303, Level 16, State 1, Line 1
> > Cannot initialize the data source object of OLE DB provider
> > "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
> >
> > The different between your statement vs. mine is "my file is on the
> > network"
> > Do I have to setup ODBC connect on the server where the file locate or
> > somehting.
> > Would you please tell me what else I need to setup or what do I do
> > wrong here.
> >
> Does the SQL service account have permission to access that network
> share? Does this work if you put the text file on a local drive?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||ntuyen01@.yahoo.com wrote:
> I work fine on the local account. On the local account I have to setup
> the "SQL Server Surface Area Configuration"
> The problem I have is:
> my sql server is on different machine Server "MachineA"
> my files is on difrrent server "MachineB"
> I am login as my account and I am able to get in (permission) to both
> server.
> On the "MachineA" do I have to set something to connect to "MachineB"
> or
> On the MachineB server do I have to set something
> ('Microsoft.Jet.OLEDB.4.0')?
> I am not sure. Please help.
> Teed
Huh? I repeat my question - "Does the SQL service account have
permission to access that network share?"
If I understand your response, it sounds like you have SQL running under
the "Local System" context. If so, you cannot access network resources
this way - SQL Server MUST run under a domain account, and that domain
account MUST have permission to access the desired network resources.
This has nothing to do with the login that you use to connect to SQL server.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi,
Yes,
I login (domain\username) is part of the group (domain\groupname) in
the SQL service account and also my login (domain\username) also have
permission to access that network share.
Teed
Tracy McKibben wrote:
> ntuyen01@.yahoo.com wrote:
> > I work fine on the local account. On the local account I have to setup
> > the "SQL Server Surface Area Configuration"
> >
> > The problem I have is:
> > my sql server is on different machine Server "MachineA"
> > my files is on difrrent server "MachineB"
> >
> > I am login as my account and I am able to get in (permission) to both
> > server.
> >
> > On the "MachineA" do I have to set something to connect to "MachineB"
> > or
> > On the MachineB server do I have to set something
> > ('Microsoft.Jet.OLEDB.4.0')?
> > I am not sure. Please help.
> >
> > Teed
> Huh? I repeat my question - "Does the SQL service account have
> permission to access that network share?"
> If I understand your response, it sounds like you have SQL running under
> the "Local System" context. If so, you cannot access network resources
> this way - SQL Server MUST run under a domain account, and that domain
> account MUST have permission to access the desired network resources.
> This has nothing to do with the login that you use to connect to SQL server.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Okay...but that doesn't make sense. Looks like we're losing
things in translation here.
Try this. Open up Enterprise Manager. Select the server.
Right click and select properties. Then click on the
security tab. In the bottom of the screen on the security
tab, there is a section named Start up service account.
Which one of the two is selected - System Account or This
Account? It can only be one of the two.
-Sue
On 15 Aug 2006 08:42:39 -0700, ntuyen01@.yahoo.com wrote:
>Hi,
>Yes,
>I login (domain\username) is part of the group (domain\groupname) in
>the SQL service account and also my login (domain\username) also have
>permission to access that network share.
>Teed
>
>Tracy McKibben wrote:
>> ntuyen01@.yahoo.com wrote:
>> > I work fine on the local account. On the local account I have to setup
>> > the "SQL Server Surface Area Configuration"
>> >
>> > The problem I have is:
>> > my sql server is on different machine Server "MachineA"
>> > my files is on difrrent server "MachineB"
>> >
>> > I am login as my account and I am able to get in (permission) to both
>> > server.
>> >
>> > On the "MachineA" do I have to set something to connect to "MachineB"
>> > or
>> > On the MachineB server do I have to set something
>> > ('Microsoft.Jet.OLEDB.4.0')?
>> > I am not sure. Please help.
>> >
>> > Teed
>> Huh? I repeat my question - "Does the SQL service account have
>> permission to access that network share?"
>> If I understand your response, it sounds like you have SQL running under
>> the "Local System" context. If so, you cannot access network resources
>> this way - SQL Server MUST run under a domain account, and that domain
>> account MUST have permission to access the desired network resources.
>> This has nothing to do with the login that you use to connect to SQL server.
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com|||I did not see the option you said in the sql 2005. Do you where it is
on 2005.
Ok, here is another thing, if I login I login as "sa", then I am able
to run the query find.
I get my NT login does not have enough persimision on the server
somewhere right?
Thanks for your time and help.
Teed
Sue Hoegemeier wrote:
> Okay...but that doesn't make sense. Looks like we're losing
> things in translation here.
> Try this. Open up Enterprise Manager. Select the server.
> Right click and select properties. Then click on the
> security tab. In the bottom of the screen on the security
> tab, there is a section named Start up service account.
> Which one of the two is selected - System Account or This
> Account? It can only be one of the two.
> -Sue
> On 15 Aug 2006 08:42:39 -0700, ntuyen01@.yahoo.com wrote:
> >Hi,
> >Yes,
> >
> >I login (domain\username) is part of the group (domain\groupname) in
> >the SQL service account and also my login (domain\username) also have
> >permission to access that network share.
> >
> >Teed
> >
> >
> >Tracy McKibben wrote:
> >> ntuyen01@.yahoo.com wrote:
> >> > I work fine on the local account. On the local account I have to setup
> >> > the "SQL Server Surface Area Configuration"
> >> >
> >> > The problem I have is:
> >> > my sql server is on different machine Server "MachineA"
> >> > my files is on difrrent server "MachineB"
> >> >
> >> > I am login as my account and I am able to get in (permission) to both
> >> > server.
> >> >
> >> > On the "MachineA" do I have to set something to connect to "MachineB"
> >> > or
> >> > On the MachineB server do I have to set something
> >> > ('Microsoft.Jet.OLEDB.4.0')?
> >> > I am not sure. Please help.
> >> >
> >> > Teed
> >>
> >> Huh? I repeat my question - "Does the SQL service account have
> >> permission to access that network share?"
> >>
> >> If I understand your response, it sounds like you have SQL running under
> >> the "Local System" context. If so, you cannot access network resources
> >> this way - SQL Server MUST run under a domain account, and that domain
> >> account MUST have permission to access the desired network resources.
> >> This has nothing to do with the login that you use to connect to SQL server.
> >>
> >>
> >> --
> >> Tracy McKibben
> >> MCDBA
> >> http://www.realsqlguy.com|||I did not see the option you said in the sql 2005. Do you where it is
on 2005.
Ok, here is another thing, at my local machine I bring up the "SQL
Server Management Studio" then connect to that dabase as "sa", then I
am able to run the query fine. Also if I remote login that server as as
Administrator, then I am able to run the query fine, but at my local
machine I bring up the "SQL Server Management Studio" and login that
database as my "NT domain username" then I got error message above.
Thanks for your time and help. I am new to this.
ntuyen01@.yahoo.com wrote:
> Teed
>
> Sue Hoegemeier wrote:
> > Okay...but that doesn't make sense. Looks like we're losing
> > things in translation here.
> > Try this. Open up Enterprise Manager. Select the server.
> > Right click and select properties. Then click on the
> > security tab. In the bottom of the screen on the security
> > tab, there is a section named Start up service account.
> > Which one of the two is selected - System Account or This
> > Account? It can only be one of the two.
> >
> > -Sue
> >
> > On 15 Aug 2006 08:42:39 -0700, ntuyen01@.yahoo.com wrote:
> >
> > >Hi,
> > >Yes,
> > >
> > >I login (domain\username) is part of the group (domain\groupname) in
> > >the SQL service account and also my login (domain\username) also have
> > >permission to access that network share.
> > >
> > >Teed
> > >
> > >
> > >Tracy McKibben wrote:
> > >> ntuyen01@.yahoo.com wrote:
> > >> > I work fine on the local account. On the local account I have to setup
> > >> > the "SQL Server Surface Area Configuration"
> > >> >
> > >> > The problem I have is:
> > >> > my sql server is on different machine Server "MachineA"
> > >> > my files is on difrrent server "MachineB"
> > >> >
> > >> > I am login as my account and I am able to get in (permission) to both
> > >> > server.
> > >> >
> > >> > On the "MachineA" do I have to set something to connect to "MachineB"
> > >> > or
> > >> > On the MachineB server do I have to set something
> > >> > ('Microsoft.Jet.OLEDB.4.0')?
> > >> > I am not sure. Please help.
> > >> >
> > >> > Teed
> > >>
> > >> Huh? I repeat my question - "Does the SQL service account have
> > >> permission to access that network share?"
> > >>
> > >> If I understand your response, it sounds like you have SQL running under
> > >> the "Local System" context. If so, you cannot access network resources
> > >> this way - SQL Server MUST run under a domain account, and that domain
> > >> account MUST have permission to access the desired network resources.
> > >> This has nothing to do with the login that you use to connect to SQL server.
> > >>
> > >>
> > >> --
> > >> Tracy McKibben
> > >> MCDBA
> > >> http://www.realsqlguy.com

Find Non-Matching End Points

Hello,

I'm trying to manipulate some data to find occurances where a certain end point it missing. For example, in my table I have the following record:

Sample

SHAPE_ID

SEQ_NUM

X_COORDINATE

Y_COORDINATE

82

0

606494.606

4820354.142

82

1

606487.996

4820344.571

82

2

606512.455

4820319.828

82

3

606590.877

4820420.208

82

4

606611.069

4820404.432

82

5

606634.138

4820434.514

82

6

606614.812

4820449.179

82

7

606670.587

4820520.016

82

8

606638.161

4820546.216

82

9

606500.606

4820400.142

For shape_ id = 82 (I have thousands of these to process), Seq_num = 0 is the beginning of the item, while seq_num 9 is the end point. The end point (x_coordinate/y_coordinate)i.e. Seq_num=9 is supposed to match the begin x_coordinate/y_coordinate values, but in this example, they do not match.

How do I find each occurance of the same shape_id where the end seq_num (#9, or whatever number is has been incremented to) x_coordinate/y_cooordinate, does not match the begin x_coordinate/y_coordinate seq_num (#0) ?

As well, if any of these records are indeed found, how do I increment to the next highest sequence number and update that record with the begin point (seq_num=0) x_coordinate/y_coordinate values?

I would appreciate any help with this problem....Thx. in advance...Walt

Moving to the Transact-SQL forum. I believe that's better equipped to provide an answer.

|||

Walt:

Is this close to what you are looking for:

set nocount on

declare @.sample table
( shape_id integer,
seq_num integer,
x_coordinate numeric (12,3),
y_coordinate numeric (12,3),
primary key (shape_id, seq_num)
)
insert into @.sample values (82, 0, 606494.606, 4820354.142 )
insert into @.sample values (82, 1, 606487.996, 4820344.571 )
insert into @.sample values (82, 2, 606512.455, 4820319.828 )
insert into @.sample values (82, 3, 606590.877, 4820420.208 )
insert into @.sample values (82, 4, 606611.069, 4820404.432 )
insert into @.sample values (82, 5, 606634.138, 4820434.514 )
insert into @.sample values (82, 6, 606614.812, 4820449.179 )
insert into @.sample values (82, 7, 606670.587, 4820520.016 )
insert into @.sample values (82, 8, 606638.161, 4820546.216 )
insert into @.sample values (82, 9, 606500.606, 4820400.142 )
--insert into @.sample values (82,10, 606494.606, 4820354.142 )

--select * from @.sample

select a.shape_id,
a.seq_num,
a.x_coordinate as [Last X],
a.y_coordinate as [Last Y],
z.x_coordinate as [Zero X],
z.y_coordinate as [Zero Y]
from ( select shape_id,
seq_num,
x_coordinate,
y_coordinate
from @.sample p
where seq_num
= ( select max (seq_num) from @.sample q
where p.shape_id = q.shape_id
and q.seq_num > 0
)
) a
inner join @.sample z
on a.shape_id = z.shape_id
and z.seq_num = 0
and ( a.x_coordinate <> z.x_coordinate or
a.y_coordinate <> z.y_coordinate
)

-- shape_id seq_num Last X Last Y Zero X Zero Y
-- -- -- -- -- -- --
-- 82 9 606500.606 4820400.142 606494.606 4820354.142

update @.sample
set x_coordinate = a.x_coordinate,
y_coordinate = a.y_coordinate
from ( select shape_id,
seq_num,
x_coordinate,
y_coordinate
from @.sample p
where seq_num
= ( select max (seq_num) from @.sample q
where p.shape_id = q.shape_id
and q.seq_num > 0
)
) a
inner join @.sample z
on a.shape_id = z.shape_id
and z.seq_num = 0
and ( a.x_coordinate <> z.x_coordinate or
a.y_coordinate <> z.y_coordinate
)

select * from @.sample where seq_num = 0

-- shape_id seq_num x_coordinate y_coordinate
-- -- -- -- --
-- 82 0 606500.606 4820400.142

Dave

|||

Dave,

Thanks for your prompt reply. I'll hopefully give your code a try tomorrow.

Is this a sql statement that I can paste into the Sql View of a new sql statement?

Sorry for such a dumb question......Walt

|||

Dave,

I'm in the process of importing my table into my Oracle schema and am just in the process of creating a new table.

I have over 500,000 records to load so I'll give your reply a try.

thx again....Walt

find no.of pages and levels deep

Take the authors table as an example in the pubs database...
How can I find how many levels within the B-Tree from the root to the level
that contains the data rows (clustered index) ? Also how many pages at each
level within that B-Tree ?
Will i have 2 B-Trees now, one for the clustered index and one for the
non-clustered ? Using SQL 2000Hi,
Have a look into DBCC SHOWCONTIG command in books online.
Thanks
Hari
MCDBA
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:udoCUF7bEHA.2972@.TK2MSFTNGP12.phx.gbl...
> Take the authors table as an example in the pubs database...
> How can I find how many levels within the B-Tree from the root to the
level
> that contains the data rows (clustered index) ? Also how many pages at
each
> level within that B-Tree ?
> Will i have 2 B-Trees now, one for the clustered index and one for the
> non-clustered ? Using SQL 2000
>|||Can your provide an example ? I did look at it but cant find any
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eiGzQM7bEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Have a look into DBCC SHOWCONTIG command in books online.
> Thanks
> Hari
> MCDBA
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:udoCUF7bEHA.2972@.TK2MSFTNGP12.phx.gbl...
> level
> each
>|||Hi,
DBCC SHOWCONTIG will not give the exact infrmation you require. But it shows
information about the data and Index pages for a table.
THis gives you pages, extends for a table/index.
Infor from books online:-
Statistic Description
ObjectName Name of the table or view processed.
ObjectId ID of the object name.
IndexName Name of the index processed. IndexName is NULL for a heap.
IndexId ID of the index. IndexId is 0 for a heap.
Level Level of the index. Level 0 is the leaf (or data) level of the
index. The level number increases moving up the tree toward the index root.
Level is 0 for a heap.
Pages Number of pages comprising that level of the index or entire
heap.
Rows Number of data or index records at that level of the index. For a
heap, this is the number of data records in the entire heap.
MinimumRecordSize Minimum record size in that level of the index or
entire heap.
MaximumRecordSize Maximum record size in that level of the index or
entire heap.
AverageRecordSize Average record size in that level of the index or
entire heap.
ForwardedRecords Number of forwarded records in that level of the
index or entire heap.
Extents Number of extents in that level of the index or entire heap.
Usage :
DBCC SHOWCONTIG(tablene,[index_name])
Thanks
Hari
MCDBA
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:#2Pk8g7bEHA.2660@.tk2msftngp13.phx.gbl...
> Can your provide an example ? I did look at it but cant find any
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eiGzQM7bEHA.2544@.TK2MSFTNGP10.phx.gbl...
>|||Hassan wrote:
> Take the authors table as an example in the pubs database...
> How can I find how many levels within the B-Tree from the root to the leve
l
> that contains the data rows (clustered index) ? Also how many pages at eac
h
> level within that B-Tree ?
With the INDEXPROPERTY function. The data rows are always at the leaf
level of the clustered index.

> Will i have 2 B-Trees now, one for the clustered index and one for the
> non-clustered ? Using SQL 2000
Yes, each index has its own B-Tree.
Gert-Jan
(Please reply only to the newsgroup)

find no.of pages and levels deep

Take the authors table as an example in the pubs database...
How can I find how many levels within the B-Tree from the root to the level
that contains the data rows (clustered index) ? Also how many pages at each
level within that B-Tree ?
Will i have 2 B-Trees now, one for the clustered index and one for the
non-clustered ? Using SQL 2000Hi,
Have a look into DBCC SHOWCONTIG command in books online.
Thanks
Hari
MCDBA
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:udoCUF7bEHA.2972@.TK2MSFTNGP12.phx.gbl...
> Take the authors table as an example in the pubs database...
> How can I find how many levels within the B-Tree from the root to the
level
> that contains the data rows (clustered index) ? Also how many pages at
each
> level within that B-Tree ?
> Will i have 2 B-Trees now, one for the clustered index and one for the
> non-clustered ? Using SQL 2000
>|||Can your provide an example ? I did look at it but cant find any
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eiGzQM7bEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Have a look into DBCC SHOWCONTIG command in books online.
> Thanks
> Hari
> MCDBA
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:udoCUF7bEHA.2972@.TK2MSFTNGP12.phx.gbl...
> > Take the authors table as an example in the pubs database...
> >
> > How can I find how many levels within the B-Tree from the root to the
> level
> > that contains the data rows (clustered index) ? Also how many pages at
> each
> > level within that B-Tree ?
> >
> > Will i have 2 B-Trees now, one for the clustered index and one for the
> > non-clustered ? Using SQL 2000
> >
> >
>|||Hi,
DBCC SHOWCONTIG will not give the exact infrmation you require. But it shows
information about the data and Index pages for a table.
THis gives you pages, extends for a table/index.
Infor from books online:-
Statistic Description
ObjectName Name of the table or view processed.
ObjectId ID of the object name.
IndexName Name of the index processed. IndexName is NULL for a heap.
IndexId ID of the index. IndexId is 0 for a heap.
Level Level of the index. Level 0 is the leaf (or data) level of the
index. The level number increases moving up the tree toward the index root.
Level is 0 for a heap.
Pages Number of pages comprising that level of the index or entire
heap.
Rows Number of data or index records at that level of the index. For a
heap, this is the number of data records in the entire heap.
MinimumRecordSize Minimum record size in that level of the index or
entire heap.
MaximumRecordSize Maximum record size in that level of the index or
entire heap.
AverageRecordSize Average record size in that level of the index or
entire heap.
ForwardedRecords Number of forwarded records in that level of the
index or entire heap.
Extents Number of extents in that level of the index or entire heap.
Usage :
DBCC SHOWCONTIG(tablene,[index_name])
Thanks
Hari
MCDBA
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:#2Pk8g7bEHA.2660@.tk2msftngp13.phx.gbl...
> Can your provide an example ? I did look at it but cant find any
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eiGzQM7bEHA.2544@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > Have a look into DBCC SHOWCONTIG command in books online.
> >
> > Thanks
> > Hari
> > MCDBA
> >
> >
> >
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:udoCUF7bEHA.2972@.TK2MSFTNGP12.phx.gbl...
> > > Take the authors table as an example in the pubs database...
> > >
> > > How can I find how many levels within the B-Tree from the root to the
> > level
> > > that contains the data rows (clustered index) ? Also how many pages at
> > each
> > > level within that B-Tree ?
> > >
> > > Will i have 2 B-Trees now, one for the clustered index and one for the
> > > non-clustered ? Using SQL 2000
> > >
> > >
> >
> >
>|||Hassan wrote:
> Take the authors table as an example in the pubs database...
> How can I find how many levels within the B-Tree from the root to the level
> that contains the data rows (clustered index) ? Also how many pages at each
> level within that B-Tree ?
With the INDEXPROPERTY function. The data rows are always at the leaf
level of the clustered index.
> Will i have 2 B-Trees now, one for the clustered index and one for the
> non-clustered ? Using SQL 2000
Yes, each index has its own B-Tree.
Gert-Jan
--
(Please reply only to the newsgroup)

find no.of pages and levels deep

Take the authors table as an example in the pubs database...
How can I find how many levels within the B-Tree from the root to the level
that contains the data rows (clustered index) ? Also how many pages at each
level within that B-Tree ?
Will i have 2 B-Trees now, one for the clustered index and one for the
non-clustered ? Using SQL 2000
Hi,
Have a look into DBCC SHOWCONTIG command in books online.
Thanks
Hari
MCDBA
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:udoCUF7bEHA.2972@.TK2MSFTNGP12.phx.gbl...
> Take the authors table as an example in the pubs database...
> How can I find how many levels within the B-Tree from the root to the
level
> that contains the data rows (clustered index) ? Also how many pages at
each
> level within that B-Tree ?
> Will i have 2 B-Trees now, one for the clustered index and one for the
> non-clustered ? Using SQL 2000
>
|||Can your provide an example ? I did look at it but cant find any
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eiGzQM7bEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Have a look into DBCC SHOWCONTIG command in books online.
> Thanks
> Hari
> MCDBA
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:udoCUF7bEHA.2972@.TK2MSFTNGP12.phx.gbl...
> level
> each
>
|||Hi,
DBCC SHOWCONTIG will not give the exact infrmation you require. But it shows
information about the data and Index pages for a table.
THis gives you pages, extends for a table/index.
Infor from books online:-
Statistic Description
ObjectName Name of the table or view processed.
ObjectId ID of the object name.
IndexName Name of the index processed. IndexName is NULL for a heap.
IndexId ID of the index. IndexId is 0 for a heap.
Level Level of the index. Level 0 is the leaf (or data) level of the
index. The level number increases moving up the tree toward the index root.
Level is 0 for a heap.
Pages Number of pages comprising that level of the index or entire
heap.
Rows Number of data or index records at that level of the index. For a
heap, this is the number of data records in the entire heap.
MinimumRecordSize Minimum record size in that level of the index or
entire heap.
MaximumRecordSize Maximum record size in that level of the index or
entire heap.
AverageRecordSize Average record size in that level of the index or
entire heap.
ForwardedRecords Number of forwarded records in that level of the
index or entire heap.
Extents Number of extents in that level of the index or entire heap.
Usage :
DBCC SHOWCONTIG(tablene,[index_name])
Thanks
Hari
MCDBA
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:#2Pk8g7bEHA.2660@.tk2msftngp13.phx.gbl...
> Can your provide an example ? I did look at it but cant find any
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eiGzQM7bEHA.2544@.TK2MSFTNGP10.phx.gbl...
>
|||Hassan wrote:
> Take the authors table as an example in the pubs database...
> How can I find how many levels within the B-Tree from the root to the level
> that contains the data rows (clustered index) ? Also how many pages at each
> level within that B-Tree ?
With the INDEXPROPERTY function. The data rows are always at the leaf
level of the clustered index.

> Will i have 2 B-Trees now, one for the clustered index and one for the
> non-clustered ? Using SQL 2000
Yes, each index has its own B-Tree.
Gert-Jan
(Please reply only to the newsgroup)

Find minimum value in a list

How do I find the minimum value in a list if provided in a function?
For example: FindMin('323', '29', '991')
The answer should be '29'.
I could pull this off by comparing each value to the other until I determine
which is the least value. Although I have experience in other languages, I'
m
a bit new to SQL and am a bit stumped on how to pull this one off. I'm
assuming it should be rather easy.
PatrickMin() function
e.g. SELECT MIN(field1) FROM Table1
-Jason
"Patrix317" <Patrix317@.discussions.microsoft.com> wrote in message
news:6DF760EE-FF9B-4577-A7B9-76A45A7A71C3@.microsoft.com...
> How do I find the minimum value in a list if provided in a function?
> For example: FindMin('323', '29', '991')
> The answer should be '29'.
> I could pull this off by comparing each value to the other until I
determine
> which is the least value. Although I have experience in other languages,
I'm
> a bit new to SQL and am a bit stumped on how to pull this one off. I'm
> assuming it should be rather easy.
> Patrick|||Create a user defined function that split the list and return a table and us
e
it as the source to:
select min(colA)
from dbo.ufn_table_from_list('323, 29, 991')
go
Arrays and Lists in SQL Server
http://www.sommarskog.se/arrays-in-sql.html
Faking arrays in T-SQL stored procedures
http://www.bizdatasolutions.com/tsql/sqlarrays.asp
How do I simulate an array inside a stored procedure?
http://www.aspfaq.com/show.asp?id=2248
AMB
"Patrix317" wrote:

> How do I find the minimum value in a list if provided in a function?
> For example: FindMin('323', '29', '991')
> The answer should be '29'.
> I could pull this off by comparing each value to the other until I determi
ne
> which is the least value. Although I have experience in other languages,
I'm
> a bit new to SQL and am a bit stumped on how to pull this one off. I'm
> assuming it should be rather easy.
> Patrick|||Jason:
Thanks, Jason, but this isn't quite what I'm looking for. I'm passing three
separate felds not one.
Example record:
Farmer: John
Apples: 323
Oranges: 29
Peache: 991
Of all of John's produce, he has the least in oranges.
FindMin(Apples, Oranges, Peaches)
Answer: Oranges
Hope this clears up my question.
Patrick
"Jason Mauss" wrote:

> Min() function
> e.g. SELECT MIN(field1) FROM Table1
> -Jason
> "Patrix317" <Patrix317@.discussions.microsoft.com> wrote in message
> news:6DF760EE-FF9B-4577-A7B9-76A45A7A71C3@.microsoft.com...
> determine
> I'm
>
>|||What format is the data in? If it is a column in a database then
SELECT TOP 1 MyValue
FROM MyTable
ORDER BY MyValue
If it's in a string list, then it isn't properly normalized and you may have
issues with your database design. You should probably give a little more
information.
"Patrix317" <Patrix317@.discussions.microsoft.com> wrote in message
news:6DF760EE-FF9B-4577-A7B9-76A45A7A71C3@.microsoft.com...
> How do I find the minimum value in a list if provided in a function?
> For example: FindMin('323', '29', '991')
> The answer should be '29'.
> I could pull this off by comparing each value to the other until I
> determine
> which is the least value. Although I have experience in other languages,
> I'm
> a bit new to SQL and am a bit stumped on how to pull this one off. I'm
> assuming it should be rather easy.
> Patrick|||Or that. DUH.
"Jason Mauss" <jason.mauss@.nospamgmail.com> wrote in message
news:egHAUAJNFHA.3708@.TK2MSFTNGP14.phx.gbl...
> Min() function
> e.g. SELECT MIN(field1) FROM Table1
> -Jason
> "Patrix317" <Patrix317@.discussions.microsoft.com> wrote in message
> news:6DF760EE-FF9B-4577-A7B9-76A45A7A71C3@.microsoft.com...
> determine
> I'm
>|||Patrick,
A UDF to split a delimited list and return a table, as suggested by Alej,
is:
-- *********************************
Create Function dbo.ParseString (
@.S VarChar(8000), @.delim Char(1))
Returns @.tOut Table
(ValNum Integer Primary Key Identity,
sVal VarChar(1000))
As
Begin
Declare @.sVal VarChar(1000)
Declare @.dPos Integer
Declare @.Start Integer Set @.Start = 1
-- --
If @.S = @.delim Or Len(@.S) = 0 Return
Else If Right(@.S,1) <> @.Delim Set @.S = @.S + @.Delim
-- --
Set @.dPos = CharIndex(@.delim, @.S, 1)
While @.dPos <> 0
Begin
Set @.sVal = LTrim(Substring(@.S, @.Start, @.dPos - @.Start))
Insert @.tOut (sVal) Values (@.sVal)
Set @.Start = @.dPos + 1
Set @.dPos = CharIndex(@.delim, @.S, @.Start)
End
Return
-- ---
End
-- *********************************
Then you can just take the minimum of the values in the table returned by
this function..
Select Min(Cast(sVal as Integer))
From dbo.ParseString('323, 29, 991', ',')
"Patrix317" wrote:

> How do I find the minimum value in a list if provided in a function?
> For example: FindMin('323', '29', '991')
> The answer should be '29'.
> I could pull this off by comparing each value to the other until I determi
ne
> which is the least value. Although I have experience in other languages,
I'm
> a bit new to SQL and am a bit stumped on how to pull this one off. I'm
> assuming it should be rather easy.
> Patrick|||Woops, sorry about that...didn't entirely understand what you were looking
for. Alejandro's suggestion is probably what you're looking for.
-Jason
"Patrix317" <Patrix317@.discussions.microsoft.com> wrote in message
news:C5DCE464-E3FB-4E3F-A02A-2D5FF3BDCED6@.microsoft.com...
> Jason:
> Thanks, Jason, but this isn't quite what I'm looking for. I'm passing
three
> separate felds not one.
> Example record:
> Farmer: John
> Apples: 323
> Oranges: 29
> Peache: 991
> Of all of John's produce, he has the least in oranges.
> FindMin(Apples, Oranges, Peaches)
> Answer: Oranges
> Hope this clears up my question.
> Patrick
> "Jason Mauss" wrote:
>
languages,
I'm|||Bob,
Please see my reply to Jason for more information.
"Bob Castleman" wrote:

> What format is the data in? If it is a column in a database then...|||CBretana,
Thanks for the code. It's not as cryptic as I had suspected. I'm going to
try this now and check it out. It's certainly a different way of handling i
t!
Patrick
"CBretana" wrote:
> Patrick,
> A UDF to split a delimited list and return a table, as suggested by Ale
j,
> is:
> -- *********************************
> Create Function dbo.ParseString (
> @.S VarChar(8000), @.delim Char(1))
> Returns @.tOut Table
> (ValNum Integer Primary Key Identity,
> sVal VarChar(1000))
> As
> Begin
> Declare @.sVal VarChar(1000)
> Declare @.dPos Integer
> Declare @.Start Integer Set @.Start = 1
> -- --
> If @.S = @.delim Or Len(@.S) = 0 Return
> Else If Right(@.S,1) <> @.Delim Set @.S = @.S + @.Delim
> -- --
> Set @.dPos = CharIndex(@.delim, @.S, 1)
> While @.dPos <> 0
> Begin
> Set @.sVal = LTrim(Substring(@.S, @.Start, @.dPos - @.Start))
> Insert @.tOut (sVal) Values (@.sVal)
> Set @.Start = @.dPos + 1
> Set @.dPos = CharIndex(@.delim, @.S, @.Start)
> End
> Return
> -- ---
> End
> -- *********************************
>
> Then you can just take the minimum of the values in the table returned by
> this function..
> Select Min(Cast(sVal as Integer))
> From dbo.ParseString('323, 29, 991', ',')
> "Patrix317" wrote:
>

Find max entry in table

I would like to select the highest id in a table for each computer record.

example data:
ID Computer name
24 computer1
23 computer1
22 computer2
24 computer3

The max id is not going to be the same for every computer.

When the script below is run, I receive the highest number only. All IDs with 24 will be listed. I need all the computer names and the highest ID they are associated with. In the case above, 24 computer1 should be on the list and 23 computer1 should not.

Thanks,

Script...

Select s.id_snapshot, s.server_name, texttype, sp, install_date, bios_date, os, ram, ramused, dns_nameservers, manufacturer, s.model, s.serial, mbmanufacturer, lastuser, boottime, idletime, ieversion, iespnumber, vendor, model2
From servers s Join processors p
On s.server_name = p.server_name and s.id_snapshot = p.id_snapshot
where s.id_snapshot = (Select MAX (id_snapshot)
From servers)Select s.id_snapshot, s.server_name, texttype, sp, install_date, bios_date, os, ram, ramused, dns_nameservers, manufacturer, s.model, s.serial, mbmanufacturer, lastuser, boottime, idletime, ieversion, iespnumber, vendor, model2
From servers s Join processors p
On s.server_name = p.server_name and s.id_snapshot = p.id_snapshot
inner join (Select sid=MAX (id_snapshot) From servers group by [Computer Name]) maxsid on s.id_snapshot = maxsid.sid|||mpereziii, your query should work too ... Do you have any issues?

Originally posted by rdjabarov
Select s.id_snapshot, s.server_name, texttype, sp, install_date, bios_date, os, ram, ramused, dns_nameservers, manufacturer, s.model, s.serial, mbmanufacturer, lastuser, boottime, idletime, ieversion, iespnumber, vendor, model2
From servers s Join processors p
On s.server_name = p.server_name and s.id_snapshot = p.id_snapshot
inner join (Select sid=MAX (id_snapshot) From servers group by [Computer Name]) maxsid on s.id_snapshot = maxsid.sid|||With the original query mpereziii was getting only rows where id_snapshot was equal to the maximum value in the table.|||Thanks for the response.
I adjusted my script with your suggestion. I received too many rows. 424,000 rows and it searched for 18sec.

The query returned every id_snapshot for every server_name (computer name).

Select s.id_snapshot, s.server_name, texttype, sp, install_date, bios_date, os, ram, ramused, dns_nameservers, manufacturer, s.model, s.serial, mbmanufacturer, lastuser, boottime, idletime, ieversion, iespnumber, vendor, model2
From servers s Join processors p
On s.server_name = p.server_name and s.id_snapshot = p.id_snapshot
inner join (Select sid=MAX (id_snapshot) From servers group by [server_name]) maxsid on s.id_snapshot = maxsid.sid|||How about this:

Select s.id_snapshot, s.server_name, texttype, sp, install_date, bios_date, os, ram, ramused, dns_nameservers, manufacturer, s.model, s.serial, mbmanufacturer, lastuser, boottime, idletime, ieversion, iespnumber, vendor, model2
From servers s Join processors p
On s.server_name = p.server_name and s.id_snapshot = p.id_snapshot
inner join (Select server_name, sid=MAX (id_snapshot) From servers group by [server_name]) maxsid on s.id_snapshot = maxsid.sid and s.server_name = maxsid.server_name|||Thank you sjp. The query now works very well. I really appreciate everyones input. Thank you.

Find Fileds Type

How Can I Find Fileds type (for example Bit or nvarchar or ntext ,...) in Sql Server;
with how sql parametr or query ?You could start by look at syscolumns|||Here is what I do in the EntityBroker O/R mapper (in the part about syncrhonizing database schemata) fto find out how the db does look at the moment.

Field types:

::SELECT *
::FROM information_schema.columns
::INNER JOIN information_schema.tables
::ON information_schema.columns.table_catalog = information_schema.tables.table_catalog
::AND information_schema.columns.table_Name = information_schema.tables.table_name
::ORDER BY information_schema.columns.table_catalog,
::information_schema.columns.table_schema,
::information_schema.columns.table_name, information_schema.columns.ordinal_position

Find Counties in area

We have the need to determine surrounding counties of a particular county.

For example how can I determine, if I entered a zip code for Contra Costa and the property is also right on the line of alameda that Alameda is a neighboring county of Contra Costa

Could it be determined by the longitude and latitude of the zip code in comparison with the counties?

Hello,

Although I'm sure a geospacial co-ordinate system could be used to determine this, most people think it overkill and just use a proximity table :)

tCounty (CountyID int, CountyName varchar)

tCountyAdjoined(CountyID int FK to tCounty.CountyID, AdjoiningCountyID int FK to tCounty.CountyID)

So if Contra Costa was near Alameda, tCountry might contain rows:

1, Contra Costa

2, Alameda

And tCountyAdjoined would contain:

1, 2

Obviously, a simple join then returns all counties that adjoin a particular County.

Unfortunatly, you'll need to manually determine and input the proximity values (unless your postal service supplies these details as in Australia.)

Cheers,

Rob

Find a word in entire database

SQL server 2000
Hi
I want to search a field value in entire database.
Example: Search for word "Ontario" in all rows of all tables in the
database. I do not want to search column_names but basically all rows in all
columns of the database for a particular word.
Thanks
ontario, canada
http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"db" <db@.discussions.microsoft.com> wrote in message
news:5B906D6F-9018-41C2-BB73-22A4AACDF41F@.microsoft.com...
> SQL server 2000
> Hi
> I want to search a field value in entire database.
> Example: Search for word "Ontario" in all rows of all tables in the
> database. I do not want to search column_names but basically all rows in
> all
> columns of the database for a particular word.
> Thanks
> ontario, canada
|||If you need to do rather convoluted search (e.g. with regular expressions),
you could also consider dump everything out to files and search files for
your string. Probably more troublesome than it's worth, but could be useful
for certain scenarios.
Linchi
"db" wrote:

> SQL server 2000
> Hi
> I want to search a field value in entire database.
> Example: Search for word "Ontario" in all rows of all tables in the
> database. I do not want to search column_names but basically all rows in all
> columns of the database for a particular word.
> Thanks
> ontario, canada
|||IIRC ApexSQL Edit has this functionality built in. Pretty slick.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"db" <db@.discussions.microsoft.com> wrote in message
news:5B906D6F-9018-41C2-BB73-22A4AACDF41F@.microsoft.com...
> SQL server 2000
> Hi
> I want to search a field value in entire database.
> Example: Search for word "Ontario" in all rows of all tables in the
> database. I do not want to search column_names but basically all rows in
> all
> columns of the database for a particular word.
> Thanks
> ontario, canada

Find a word in entire database

SQL server 2000
Hi
I want to search a field value in entire database.
Example: Search for word "Ontario" in all rows of all tables in the
database. I do not want to search column_names but basically all rows in all
columns of the database for a particular word.
Thanks
ontario, canadahttp://vyaskn.tripod.com/search_all_columns_in_all_tables.htm
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"db" <db@.discussions.microsoft.com> wrote in message
news:5B906D6F-9018-41C2-BB73-22A4AACDF41F@.microsoft.com...
> SQL server 2000
> Hi
> I want to search a field value in entire database.
> Example: Search for word "Ontario" in all rows of all tables in the
> database. I do not want to search column_names but basically all rows in
> all
> columns of the database for a particular word.
> Thanks
> ontario, canada|||If you need to do rather convoluted search (e.g. with regular expressions),
you could also consider dump everything out to files and search files for
your string. Probably more troublesome than it's worth, but could be useful
for certain scenarios.
Linchi
"db" wrote:
> SQL server 2000
> Hi
> I want to search a field value in entire database.
> Example: Search for word "Ontario" in all rows of all tables in the
> database. I do not want to search column_names but basically all rows in all
> columns of the database for a particular word.
> Thanks
> ontario, canada|||IIRC ApexSQL Edit has this functionality built in. Pretty slick.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"db" <db@.discussions.microsoft.com> wrote in message
news:5B906D6F-9018-41C2-BB73-22A4AACDF41F@.microsoft.com...
> SQL server 2000
> Hi
> I want to search a field value in entire database.
> Example: Search for word "Ontario" in all rows of all tables in the
> database. I do not want to search column_names but basically all rows in
> all
> columns of the database for a particular word.
> Thanks
> ontario, canada

Financial functions for SQL Server

Hi everybody,

Does anyone have financial functions to be run in SQL Server 2000? For example, future value, interest rate, payments, and so on. Or where can I find them on Internet?

Thanks in advance

Jaime

Here might be a good place to start?
http://www.google.se/search?hl=sv&q=future+value%2C+interest+rate%2C+payments+function+%2BSQL+Server&meta=

/Kenneth

|||

No so good...

I have found some commercial libraries but none (free) that lists all financial functions. At least, I have found future value implementation. That's why I asked if someone has already a list of functions ready to use.

Jaime

Filtering with StartTime and Endtime problem

Hello,

I have a problem with the following task:

The user is prompted to specify a starttime like 02 and a endtime 04.

The user for example wants to search all events between 2 am and 10 am regardless of the date.

I use the following SQL ti filter:

Where (DATEPART(hour, Event.EventDateTime) BETWEEN @.StartHour AND @.EndHour)

This works fine as long starhour is smaller than endhour.

When i want to filter Events beteween 23 (11 pm.) and 02 (2 am) i get no results. I have to make a search possible for a time span that is over midnight.

Anyone who has some ideas?

Thank you i advance!

Hello Luskan,

Try this:

select *

from Table1

where 1 =

case

when @.StartHour > @.EndHour and (datepart(hour, Event.EventDateTime) >= @.StartHour or datepart(hour, Event.EventDateTime) <= @.EndHour) then 1

when @.StartHour <= @.EndHour and datepart(hour, Event.EventDateTime) between @.StartHour and @.EndHour then 1

end

Hope this helps.

Jarret