Finding last entries

Hi, I am searching for the most easy SQL solution:

Lets say i have 500000 rows of cars in one table with 30000 different car ID's.
This table contains no keys, no date values, but I need the last row of these 30000 different cars. How do I get them without adapting table structure and without using cursors ?

Thx for reply.

dajmHow's your resume' looking these days? Is it up to date?|||The last row updated? The last row inserted? Physical location might not really mean anything.

Are there any related tables that could help us out.

You might be screwed if you want to figure this out in your current setup.

Going forward you could use a trigger to populate a new table for this purpose.|||If you use TOP 1 you get a result that is as good as any.
Noone can say that it is false, because if they can, they must say why, and if they say why, then you have a rule you could use in your select statement.

That's what I should've done in your shoes, given that there was noone to ask that is ...

As Thrasymachus says, there is no way (with the given info) that the solution can be found, atleast not that I can see.|||O.K. Here's an example for everybody

Car-ID |sales price

1|5
3|3
2|6
1|7
3|4

As result I need to get
2|6
1|7
3|4

With Navision language C/Side it is very easy to catch the last entries of database by setting a filter on car-id, but how do I get this result by using SQL ?|||Example pasted from earlier post :

Car-ID |sales price

1|5
3|3
2|6
1|7
3|4

As result I need to get
2|6
1|7
3|4

Nothing tells you that these three last entries in your result is in fact the three last ones. All you know is that some means of presentation choose to show you those results in that order. Running the same question again or from another client might return other results. Therefore you still haven't presented any useful info to solve the problem, unless you wanna use my TOP solution earlier. In that case replace the TOP 1 with TOP 3 ...|||Data in a database is not guaranteed to be in any order unless specified in the table design. If this table has a clustered index (as it should), then the data is no longer in the order in which it was entered.

Period.

If you do not store the datetime the record was entered, you cannot expect to sort or filter on the datetime the record was entered.|||There exists no index, but the no. of cars is dynamic.
I cannot use a "select top n" statement as I never know "n".
Can I combine this however with a "select count (distinct car-id)" ?

dajm|||Sorry, all back. There is a clustered index on a integer field with incremental no., used as a uniqueidentifier.
Does this help somehow ?|||well yes. but you said there were no keys earlier no keys...

and then you said the keys were not dynamic

"Lets say i have 500000 rows of cars in one table with 30000 different car ID's"

just use the max() function on your newly discovered field.|||A max() on this field returns just the last row, but not every last row of my 30000 vehicles, means 30000 rows.|||See this thread for a solution to your problem:

http://www.dbforums.com/showthread.php?t=1094858&page=1&pp=15

Finding and updating all rows with the same value in a column

This integer column should be unique, s? prior to altering it to unique i
need to traverse all rows and update the rows with the same value. This tabl
e
has another colun which is the primary key and when 2 rows has the same valu
e
the row with the highest value of the primary key will be updated.
Any easy T-SQL way to do it?
Regards,
Olavupdate your_table
set your_column = whatever you wish to set it to
where exists(select 1 from your_table yt
where your_table.your_column = yt.your_column
and your_table.PK_column > yt.PK_column)
Just curious: what kind of information do you have in that column so
that you can easily update it just to enforse uniqueness?sql

finding and removing duplicates rows

Say I had a table called 'RAS', with 4 columns:
Date, Time, UserName, GroupName
How can I check for any duplicate rows based on all those columns
combined.
And once they are found, can the duplicates be deleted?
It would be nice to see 2 solutions for learning purposes... one
statement just to show the duplicates, and another separate statement
to find and delete the duplicates.
Thanks in advance!
StephenCREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Stephen" <facetoface72@.hotmail.com> wrote in message
news:d6e21d67.0403160535.5ac3afa7@.posting.google.com...
> Say I had a table called 'RAS', with 4 columns:
> Date, Time, UserName, GroupName
> How can I check for any duplicate rows based on all those columns
> combined.
> And once they are found, can the duplicates be deleted?
> It would be nice to see 2 solutions for learning purposes... one
> statement just to show the duplicates, and another separate statement
> to find and delete the duplicates.
> Thanks in advance!
> Stephen|||Thanks for the message, it definately helped solve a lot of issues.
Is there a way to do everything mentioned, but without having a unique
key for each record?
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:<#6uUEw1CEHA.1452@.TK2MSFTNGP09.phx.gb
l>...
> CREATE TABLE #Demo (
> idNo int identity(1,1),
> colA int,
> colB int
> )
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (2,4)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (4,2)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (5,1)
> INSERT INTO #Demo(colA,colB) VALUES (8,1)
> PRINT 'Table'
> SELECT * FROM #Demo
> PRINT 'Duplicates in Table'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo <> B.idNo
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Duplicates to Delete'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> DELETE FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Cleaned-up Table'
> SELECT * FROM #Demo
> DROP TABLE #Demo
> "Stephen" <facetoface72@.hotmail.com> wrote in message
> news:d6e21d67.0403160535.5ac3afa7@.posting.google.com...|||On 17 Mar 2004 05:24:49 -0800, Stephen wrote:

>Thanks for the message, it definately helped solve a lot of issues.
>Is there a way to do everything mentioned, but without having a unique
>key for each record?
No. If all columns in two rows are the same, there's no way to tell
them apart. Any where clause will either select both rows or none of
them. If you want to eliminate duplicates, you have to add an IDENTITY
or GUID column, or else you will delete both rows instead of one of
the duplicates.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||DELETE *
FROM table3
WHERE sno not in (SELECT min(sno)
FROM table3
GROUP BY fd1, fd2, fd3);

Find Table Size

Env: SQL Server 2000

The following sql stmt seems to find a particular table's size
programmatically:

select top 1 [rows],rowcnt
from sysindexes
where ID = object_id('aUserTable')
and status = 0
and used > 0

However,
a) I'm not 100% sure of its consistency;
b) Both [rows] col and [rowcnt] col seems to produce same data, which
one is supposed to be more accurate (or more up to date)?

TIA.NickName (dadada@.rock.com) writes:
> The following sql stmt seems to find a particular table's size
> programmatically:
> select top 1 [rows],rowcnt
> from sysindexes
> where ID = object_id('aUserTable')
> and status = 0
> and used > 0
> However,
> a) I'm not 100% sure of its consistency;
> b) Both [rows] col and [rowcnt] col seems to produce same data, which
> one is supposed to be more accurate (or more up to date)?

A TOP 1 without ORDER BY is not a good thing. However, if you with
"size" means rowcount, this may do. Better though is to add the
"AND indid IN (0, 1)" to the WHERE clause. (There is never rows for
both 0 and 1, but always for exactly one of them.)

The values in sysindexes are not the exact values, for that you need to
do SELECT COUNT(*). However, a DBCC UPDATEUSAGE before you run the
SELECT query will give you good accuracy.

Judging from the documentation, rowcnt is the better column to use.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland,

Yes, when I say table size I meant row count of a table.

Results of some testing of the following queries:

select rowcnt
from sysindexes
where ID = object_id('customer')
-- and status = 0
and used > 0
AND indid IN (0, 1)
produces correct resultset

select rowcnt
from sysindexes
where ID = object_id('customer')
and status = 0
and used > 0
AND indid IN (0, 1)

produces incorrect resultset (zero count)

Not to use SELECT COUNT(*) ... is because I intend to get row count for
each and all tables of a huge database, COUNT would take considerable
longer to do.

You're the man!

Don

find rows within seconds of top of hour

I have rows of data that have a datetime stamp. I need to find rows
that have a datetime stamp within 10 seconds of the top of each hour.
I started with datediff(s,getdate(),LeadDate) but I am stumped on how
to process for each hour.
I.E. records between 9:00:00 and 9:00:10 , 10:00:00 10:00:10, and so
forth for each hour.
Ideas?On Wed, 24 Oct 2007 06:06:53 -0700, rcamarda
<robert.a.camarda@.gmail.comwrote:

Quote:

Originally Posted by

>I have rows of data that have a datetime stamp. I need to find rows
>that have a datetime stamp within 10 seconds of the top of each hour.
>I started with datediff(s,getdate(),LeadDate) but I am stumped on how
>to process for each hour.
>I.E. records between 9:00:00 and 9:00:10 , 10:00:00 10:00:10, and so
>forth for each hour.
>Ideas?

To select just the rows from those intervals:

SELECT *
FROM Whatever
WHERE DATEPART(minute,LeadDate) = 0
AND DATEPART(second,LeadDate) BETWEEN 0 AND 10

Roy Harvey
Beacon Falls, CT

Find Records with duplicate values in 1 row keyed to 2 other rows

My table looks like this:
rowid, tenantid, chargecode, date, amount, ..........
I need to get all rows that have multiple entries on the same date(mm/yyyy)
for a given combination of tenantID and chargecode.
for example:
1 tenant1 5 1/1999 $500
2 tenant1 6 1/1999 $25
3 tenant1 5 1/1999 $35
4 tenant2 5 1/1999 $30
5 tenant2 5 2/1999 $30
6 tenant2 6 2/1999 $40
7 tenant2 6 2/1999 $50
I would need to get that tenant1 has 2 records with a chargecode of 5 for
1/1999
and that tenant2 has 2 records with chargecode 6 for 2/1999
SELECT tenantID, chargecode, date, count(*) as Duplciates
FROM TheTable
GROUP BY tenantID, chargecode, date
HAVING count(*) > 1
Roy Harvey
Beacon Falls, CT
On Thu, 22 Feb 2007 16:19:00 -0800, Joe S. <joecrew@.news.postalias>
wrote:

>My table looks like this:
>rowid, tenantid, chargecode, date, amount, ..........
>
>I need to get all rows that have multiple entries on the same date(mm/yyyy)
>for a given combination of tenantID and chargecode.
>for example:
>1 tenant1 5 1/1999 $500
>2 tenant1 6 1/1999 $25
>3 tenant1 5 1/1999 $35
>4 tenant2 5 1/1999 $30
>5 tenant2 5 2/1999 $30
>6 tenant2 6 2/1999 $40
>7 tenant2 6 2/1999 $50
>I would need to get that tenant1 has 2 records with a chargecode of 5 for
>1/1999
>and that tenant2 has 2 records with chargecode 6 for 2/1999

Find Records with duplicate values in 1 row keyed to 2 other rows

My table looks like this:
rowid, tenantid, chargecode, date, amount, ..........
I need to get all rows that have multiple entries on the same date(mm/yyyy)
for a given combination of tenantID and chargecode.
for example:
1 tenant1 5 1/1999 $500
2 tenant1 6 1/1999 $25
3 tenant1 5 1/1999 $35
4 tenant2 5 1/1999 $30
5 tenant2 5 2/1999 $30
6 tenant2 6 2/1999 $40
7 tenant2 6 2/1999 $50
I would need to get that tenant1 has 2 records with a chargecode of 5 for
1/1999
and that tenant2 has 2 records with chargecode 6 for 2/1999SELECT tenantID, chargecode, date, count(*) as Duplciates
FROM TheTable
GROUP BY tenantID, chargecode, date
HAVING count(*) > 1
Roy Harvey
Beacon Falls, CT
On Thu, 22 Feb 2007 16:19:00 -0800, Joe S. <joecrew@.news.postalias>
wrote:

>My table looks like this:
>rowid, tenantid, chargecode, date, amount, ..........
>
>I need to get all rows that have multiple entries on the same date(mm/yyyy)
>for a given combination of tenantID and chargecode.
>for example:
>1 tenant1 5 1/1999 $500
>2 tenant1 6 1/1999 $25
>3 tenant1 5 1/1999 $35
>4 tenant2 5 1/1999 $30
>5 tenant2 5 2/1999 $30
>6 tenant2 6 2/1999 $40
>7 tenant2 6 2/1999 $50
>I would need to get that tenant1 has 2 records with a chargecode of 5 for
>1/1999
>and that tenant2 has 2 records with chargecode 6 for 2/1999

Find Records with duplicate values in 1 row keyed to 2 other rows

My table looks like this:
rowid, tenantid, chargecode, date, amount, ..........
I need to get all rows that have multiple entries on the same date(mm/yyyy)
for a given combination of tenantID and chargecode.
for example:
1 tenant1 5 1/1999 $500
2 tenant1 6 1/1999 $25
3 tenant1 5 1/1999 $35
4 tenant2 5 1/1999 $30
5 tenant2 5 2/1999 $30
6 tenant2 6 2/1999 $40
7 tenant2 6 2/1999 $50
I would need to get that tenant1 has 2 records with a chargecode of 5 for
1/1999
and that tenant2 has 2 records with chargecode 6 for 2/1999SELECT tenantID, chargecode, date, count(*) as Duplciates
FROM TheTable
GROUP BY tenantID, chargecode, date
HAVING count(*) > 1
Roy Harvey
Beacon Falls, CT
On Thu, 22 Feb 2007 16:19:00 -0800, Joe S. <joecrew@.news.postalias>
wrote:
>My table looks like this:
>rowid, tenantid, chargecode, date, amount, ..........
>
>I need to get all rows that have multiple entries on the same date(mm/yyyy)
>for a given combination of tenantID and chargecode.
>for example:
>1 tenant1 5 1/1999 $500
>2 tenant1 6 1/1999 $25
>3 tenant1 5 1/1999 $35
>4 tenant2 5 1/1999 $30
>5 tenant2 5 2/1999 $30
>6 tenant2 6 2/1999 $40
>7 tenant2 6 2/1999 $50
>I would need to get that tenant1 has 2 records with a chargecode of 5 for
>1/1999
>and that tenant2 has 2 records with chargecode 6 for 2/1999

Find out who deleted rows?

Yesterday, I had an occurence where someone (one of our developers :) ) deleted many vital rows in a database. I was able to recover via a backup but I'd like to see what was run to delete those rows, when and who if possible? I have the transaction log and the _log.ldf.

Can I read those somehow to find out this information? I have since added auditing to said tables, but I'd really like to go back and see what happened?

I do know about Lumigent, but is there a different production/solution?

Thoughts?

Thank youDid you add triggers? Write all transaction to a history table...|||Brett.

See above, "I have since added auditing to said tables, but I'd really like to go back and see what happened?"....

Thanks for the thoughts though....

Anybody else on the read log file or transaction log files?

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 matching sets of rows

Given an ID (column B), I need to find which IDs have identical data.

That is, given '200', I want the desired result to be:
100

The idea is that the system sees that id=200 has 5 records with the
indicated data in cols C and D.

It should then find any other ids with the exact same data for those
columns.

Note, in this case, both 200 and 100 have (30:1, 30:2, 30:3, 40:4,
40:5) so they match. 300 and 400 should NOT be returned.

Any bright ideas out there? Thanks!

DECLARE @.a TABLE(A int, B int, C int, D int)
DECLARE @.b TABLE(A int, B int, C int, D int)

INSERT INTO @.a (A, B, C, D) VALUES (1, 100, 30, 1)
INSERT INTO @.a (A, B, C, D) VALUES (2, 100, 30, 2)
INSERT INTO @.a (A, B, C, D) VALUES (3, 100, 30, 3)
INSERT INTO @.a (A, B, C, D) VALUES (4, 100, 40, 4)
INSERT INTO @.a (A, B, C, D) VALUES (5, 100, 40, 5)

INSERT INTO @.a (A, B, C, D) VALUES (6, 200, 30, 1)
INSERT INTO @.a (A, B, C, D) VALUES (7, 200, 30, 2)
INSERT INTO @.a (A, B, C, D) VALUES (8, 200, 30, 3)
INSERT INTO @.a (A, B, C, D) VALUES (9, 200, 40, 4)
INSERT INTO @.a (A, B, C, D) VALUES (10, 200, 40, 5)

INSERT INTO @.a (A, B, C, D) VALUES (11, 300, 30, 1)
INSERT INTO @.a (A, B, C, D) VALUES (12, 300, 30, 2)
INSERT INTO @.a (A, B, C, D) VALUES (13, 300, 40, 3)
INSERT INTO @.a (A, B, C, D) VALUES (14, 400, 40, 4)
INSERT INTO @.a (A, B, C, D) VALUES (15, 400, 40, 5)

SELECT * FROM @.afigital wrote:
> Given an ID (column B), I need to find which IDs have identical data.
> That is, given '200', I want the desired result to be:
> 100
> The idea is that the system sees that id=200 has 5 records with the
> indicated data in cols C and D.
> It should then find any other ids with the exact same data for those
> columns.
> Note, in this case, both 200 and 100 have (30:1, 30:2, 30:3, 40:4,
> 40:5) so they match. 300 and 400 should NOT be returned.
> Any bright ideas out there? Thanks!
>
> DECLARE @.a TABLE(A int, B int, C int, D int)
> DECLARE @.b TABLE(A int, B int, C int, D int)
> INSERT INTO @.a (A, B, C, D) VALUES (1, 100, 30, 1)
> INSERT INTO @.a (A, B, C, D) VALUES (2, 100, 30, 2)
> INSERT INTO @.a (A, B, C, D) VALUES (3, 100, 30, 3)
> INSERT INTO @.a (A, B, C, D) VALUES (4, 100, 40, 4)
> INSERT INTO @.a (A, B, C, D) VALUES (5, 100, 40, 5)
> INSERT INTO @.a (A, B, C, D) VALUES (6, 200, 30, 1)
> INSERT INTO @.a (A, B, C, D) VALUES (7, 200, 30, 2)
> INSERT INTO @.a (A, B, C, D) VALUES (8, 200, 30, 3)
> INSERT INTO @.a (A, B, C, D) VALUES (9, 200, 40, 4)
> INSERT INTO @.a (A, B, C, D) VALUES (10, 200, 40, 5)
> INSERT INTO @.a (A, B, C, D) VALUES (11, 300, 30, 1)
> INSERT INTO @.a (A, B, C, D) VALUES (12, 300, 30, 2)
> INSERT INTO @.a (A, B, C, D) VALUES (13, 300, 40, 3)
> INSERT INTO @.a (A, B, C, D) VALUES (14, 400, 40, 4)
> INSERT INTO @.a (A, B, C, D) VALUES (15, 400, 40, 5)
> SELECT * FROM @.a

Thanks for posting the sample data. It really does help however if you
include KEYS with your DDL. Your table doesn't seem to have a key - all
the columns are nullable. That may make your problem a lot harder to
solve.

Assuming you can rewrite the table variable as:

DECLARE @.a TABLE(A int, B int, C int, D int, PRIMARY KEY (b,c,d));

Then you can do:

DECLARE @.i INT ;
SET @.i = 100 ;

SELECT B.b
FROM @.a AS A
JOIN @.a AS B
ON A.b = @.i
AND A.c = B.c
AND A.d = B.d
AND B.b <> @.i
GROUP BY B.b
HAVING COUNT(*)=
(SELECT COUNT(*)
FROM @.a
WHERE b = @.i);

If I'm wrong and you don't have such a key then it's not clear how you
want to handle duplicates. Here's a different example, assuming that A
is the key and that duplicates are significant, i.e. you want the same
number of rows in each set identified by column B:

SELECT B.b
FROM @.a AS A
JOIN @.a AS B
ON A.b = @.i
AND A.c = B.c
AND A.d = B.d
AND B.b <> @.i
GROUP BY B.b
HAVING COUNT(DISTINCT A.a)=
(SELECT COUNT(DISTINCT a)
FROM @.a
WHERE b = @.i);

Hope this helps.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||Try:

SELECT b.B
FROM @.a a
join @.a b on b.C = a.C
and b.D = a.D
where a.B = 200
and b.B <> 200
group by
b.B
having
count (*) = (select count (*) from @.a where B = 200)

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
"figital" <mharen@.gmail.com> wrote in message
news:1144788286.896210.141080@.i40g2000cwc.googlegr oups.com...
Given an ID (column B), I need to find which IDs have identical data.

That is, given '200', I want the desired result to be:
100

The idea is that the system sees that id=200 has 5 records with the
indicated data in cols C and D.

It should then find any other ids with the exact same data for those
columns.

Note, in this case, both 200 and 100 have (30:1, 30:2, 30:3, 40:4,
40:5) so they match. 300 and 400 should NOT be returned.

Any bright ideas out there? Thanks!

DECLARE @.a TABLE(A int, B int, C int, D int)
DECLARE @.b TABLE(A int, B int, C int, D int)

INSERT INTO @.a (A, B, C, D) VALUES (1, 100, 30, 1)
INSERT INTO @.a (A, B, C, D) VALUES (2, 100, 30, 2)
INSERT INTO @.a (A, B, C, D) VALUES (3, 100, 30, 3)
INSERT INTO @.a (A, B, C, D) VALUES (4, 100, 40, 4)
INSERT INTO @.a (A, B, C, D) VALUES (5, 100, 40, 5)

INSERT INTO @.a (A, B, C, D) VALUES (6, 200, 30, 1)
INSERT INTO @.a (A, B, C, D) VALUES (7, 200, 30, 2)
INSERT INTO @.a (A, B, C, D) VALUES (8, 200, 30, 3)
INSERT INTO @.a (A, B, C, D) VALUES (9, 200, 40, 4)
INSERT INTO @.a (A, B, C, D) VALUES (10, 200, 40, 5)

INSERT INTO @.a (A, B, C, D) VALUES (11, 300, 30, 1)
INSERT INTO @.a (A, B, C, D) VALUES (12, 300, 30, 2)
INSERT INTO @.a (A, B, C, D) VALUES (13, 300, 40, 3)
INSERT INTO @.a (A, B, C, D) VALUES (14, 400, 40, 4)
INSERT INTO @.a (A, B, C, D) VALUES (15, 400, 40, 5)

SELECT * FROM @.a|||The key is Column A (ident). Sorry for not providing more complete ddl.
I will check out these suggestions, thanks!|||DECLARE @.a TABLE(A int IDENTITY(1,1) PRIMARY KEY, B int, C int, D int);

INSERT INTO @.a (B, C, D) VALUES (100, 30, 1)
INSERT INTO @.a (B, C, D) VALUES (100, 30, 2)
INSERT INTO @.a (B, C, D) VALUES (100, 30, 3)
INSERT INTO @.a (B, C, D) VALUES (100, 40, 4)
INSERT INTO @.a (B, C, D) VALUES (100, 40, 5)
INSERT INTO @.a (B, C, D) VALUES (200, 30, 1)
INSERT INTO @.a (B, C, D) VALUES (200, 30, 2)
INSERT INTO @.a (B, C, D) VALUES (200, 30, 3)
INSERT INTO @.a (B, C, D) VALUES (200, 40, 4)
INSERT INTO @.a (B, C, D) VALUES (200, 40, 5)
INSERT INTO @.a (B, C, D) VALUES (300, 30, 1)
INSERT INTO @.a (B, C, D) VALUES (300, 30, 2)
INSERT INTO @.a (B, C, D) VALUES (300, 40, 3)
INSERT INTO @.a (B, C, D) VALUES (400, 40, 4)
INSERT INTO @.a (B, C, D) VALUES (400, 40, 5)
INSERT INTO @.a (B, C, D) VALUES (500, 30, 1)
INSERT INTO @.a (B, C, D) VALUES (500, 30, 2)
INSERT INTO @.a (B, C, D) VALUES (500, 30, 3)
INSERT INTO @.a (B, C, D) VALUES (500, 40, 4)
INSERT INTO @.a (B, C, D) VALUES (500, 40, 5)
INSERT INTO @.a (B, C, D) VALUES (500, 31, 6)

--SELECT * FROM @.a

DECLARE @.i INT ;
SET @.i = 200 ;

-- solution

The above solutions return 500 even though it contains _6_ records and
200 contains 5 records.|||Our solutions fall into the category of "Relational Division". In both
solutions, we allow for a remainder. What you want is exact division.
Here's a solution for exact division:

SELECT a.B
FROM @.a a
left
join @.a b on b.C = a.C
and b.D = a.D
and b.B = 200
where a.B <> 200
group by
a.B
having
count (distinct a.D) = (select count (distinct D) from @.a where B = 200)

If A is an identity, you could use count (distinct A) where applicable.

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
"figital" <mharen@.gmail.com> wrote in message
news:1144792950.602964.118760@.g10g2000cwb.googlegr oups.com...
DECLARE @.a TABLE(A int IDENTITY(1,1) PRIMARY KEY, B int, C int, D int);

INSERT INTO @.a (B, C, D) VALUES (100, 30, 1)
INSERT INTO @.a (B, C, D) VALUES (100, 30, 2)
INSERT INTO @.a (B, C, D) VALUES (100, 30, 3)
INSERT INTO @.a (B, C, D) VALUES (100, 40, 4)
INSERT INTO @.a (B, C, D) VALUES (100, 40, 5)
INSERT INTO @.a (B, C, D) VALUES (200, 30, 1)
INSERT INTO @.a (B, C, D) VALUES (200, 30, 2)
INSERT INTO @.a (B, C, D) VALUES (200, 30, 3)
INSERT INTO @.a (B, C, D) VALUES (200, 40, 4)
INSERT INTO @.a (B, C, D) VALUES (200, 40, 5)
INSERT INTO @.a (B, C, D) VALUES (300, 30, 1)
INSERT INTO @.a (B, C, D) VALUES (300, 30, 2)
INSERT INTO @.a (B, C, D) VALUES (300, 40, 3)
INSERT INTO @.a (B, C, D) VALUES (400, 40, 4)
INSERT INTO @.a (B, C, D) VALUES (400, 40, 5)
INSERT INTO @.a (B, C, D) VALUES (500, 30, 1)
INSERT INTO @.a (B, C, D) VALUES (500, 30, 2)
INSERT INTO @.a (B, C, D) VALUES (500, 30, 3)
INSERT INTO @.a (B, C, D) VALUES (500, 40, 4)
INSERT INTO @.a (B, C, D) VALUES (500, 40, 5)
INSERT INTO @.a (B, C, D) VALUES (500, 31, 6)

--SELECT * FROM @.a

DECLARE @.i INT ;
SET @.i = 200 ;

-- solution

The above solutions return 500 even though it contains _6_ records and
200 contains 5 records.|||Tom and David,

Thank you very much for your help!

I had to add a check because of the left join but otherwise, awesome!

SELECT a.B
FROM @.a a
left join @.a b on b.C = a.C
and b.D = a.D
and b.B = @.i
where a.B <> @.i and NOT (B.C IS NULL OR B.D IS NULL)
group by
a.B
having
count (distinct a.D) = (select count (distinct D) from @.a where B =
@.i)|||Oh, OK. The input data didn't have nulls, so I didn't go there. Glad you
now have a solution. :-)

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
"figital" <mharen@.gmail.com> wrote in message
news:1144797933.088430.155480@.g10g2000cwb.googlegr oups.com...
Tom and David,

Thank you very much for your help!

I had to add a check because of the left join but otherwise, awesome!

SELECT a.B
FROM @.a a
left join @.a b on b.C = a.C
and b.D = a.D
and b.B = @.i
where a.B <> @.i and NOT (B.C IS NULL OR B.D IS NULL)
group by
a.B
having
count (distinct a.D) = (select count (distinct D) from @.a where B =
@.i)

Find Duplicated value

I have a table having 50000 rows. One field(Name) of the table may have duplicated data. How can I get all the duplicated data?

Hi,

what about

SELECT SomeColumn
FROM SomeTable
GROUP BY SomeCOlumn
HAVING COUNT(*) > 1

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

find avg no. of rows in a page and no. of pages in a table ?

Id like to find the avg no. of rows in a page of a table along with total
number of pages.
Thanks
Hi Hassan
What version?
In SQL 2005, number of rows is stored in sys.partitions and number of pages
is stored in sys.allocation_units. The whole storage picture is a bit
different in SQL 2005, because of partitioning, and because of the ways that
special data (LOB and row_overflow) is kept track of. So take a look at
those two views in BOL and look at the columns they contain. Try to join
them to get the info you need, and then post back if you get stuck
In SQL 2000, everything is in sysindexes.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Hassan" <hassan@.test.com> wrote in message
news:%23k7zHFLTIHA.280@.TK2MSFTNGP03.phx.gbl...
> Id like to find the avg no. of rows in a page of a table along with total
> number of pages.
> Thanks

find avg no. of rows in a page and no. of pages in a table ?

Id like to find the avg no. of rows in a page of a table along with total
number of pages.
ThanksHi Hassan
What version?
In SQL 2005, number of rows is stored in sys.partitions and number of pages
is stored in sys.allocation_units. The whole storage picture is a bit
different in SQL 2005, because of partitioning, and because of the ways that
special data (LOB and row_overflow) is kept track of. So take a look at
those two views in BOL and look at the columns they contain. Try to join
them to get the info you need, and then post back if you get stuck
In SQL 2000, everything is in sysindexes.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Hassan" <hassan@.test.com> wrote in message
news:%23k7zHFLTIHA.280@.TK2MSFTNGP03.phx.gbl...
> Id like to find the avg no. of rows in a page of a table along with total
> number of pages.
> Thanks

find avg no. of rows in a page and no. of pages in a table ?

Id like to find the avg no. of rows in a page of a table along with total
number of pages.
ThanksHi Hassan
What version?
In SQL 2005, number of rows is stored in sys.partitions and number of pages
is stored in sys.allocation_units. The whole storage picture is a bit
different in SQL 2005, because of partitioning, and because of the ways that
special data (LOB and row_overflow) is kept track of. So take a look at
those two views in BOL and look at the columns they contain. Try to join
them to get the info you need, and then post back if you get stuck
In SQL 2000, everything is in sysindexes.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Hassan" <hassan@.test.com> wrote in message
news:%23k7zHFLTIHA.280@.TK2MSFTNGP03.phx.gbl...
> Id like to find the avg no. of rows in a page of a table along with total
> number of pages.
> Thanks

find and replace

how do i write a query that will go through all the rows of a text field and
replace the search word with a new word.
like the find and replace feature in MS Access
REPLACE "ABC" WITH "CBS" IN DBTABLE.DBTEXT
Thanks,
AaronHi,
Use Update statement with Replace function in SET clause. This will work out
if ur data type is
Varchar/Char/Nchar,Nvarchar.
Thanks
Hari
SQL Server MVP
"Aaron" <kuya789@.yahoo.com> wrote in message
news:uvdHyxNOFHA.3396@.TK2MSFTNGP10.phx.gbl...
> how do i write a query that will go through all the rows of a text field
> and replace the search word with a new word.
> like the find and replace feature in MS Access
> REPLACE "ABC" WITH "CBS" IN DBTABLE.DBTEXT
> Thanks,
> Aaron
>|||Could you give me an example?
"Hari Pra" <hari_pra_k@.hotmail.com> wrote in message
news:Oormu$NOFHA.904@.tk2msftngp13.phx.gbl...
> Hi,
> Use Update statement with Replace function in SET clause. This will work
> out if ur data type is
> Varchar/Char/Nchar,Nvarchar.
> Thanks
> Hari
> SQL Server MVP
> "Aaron" <kuya789@.yahoo.com> wrote in message
> news:uvdHyxNOFHA.3396@.TK2MSFTNGP10.phx.gbl...
>|||Aaron
CREATE TABLE #Test
(
col VARCHAR(10)
)
INSERT INTO #Test VALUES ('FDABC')
UPDATE #Test SET col=REPLACE(col,'ABC','CBS')
--or if you know the start point and length of the string
UPDATE #Test SET col=STUFF(col,3,3,'CBS')
SELECT * FROM #Test
"Aaron" <kuya789@.yahoo.com> wrote in message
news:eSVOunOOFHA.2520@.tk2msftngp13.phx.gbl...
> Could you give me an example?
>
> "Hari Pra" <hari_pra_k@.hotmail.com> wrote in message
> news:Oormu$NOFHA.904@.tk2msftngp13.phx.gbl...
field
>

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

Filters ! Filters ! Filters - Cascading parameters

Hi,
I have a table with 100 rows in the following format
---
country name state name city name
---
USA NY NY
USA NJ Newark
USA FL Orlando
USA FL Miami
USA CA LA
USA CA SF
China XX XX
India XX XX
Australia XX XX
China XX XX
India XX XX
----
I have 3 dropdown cascading parameters (all of them have default values
when the report loads) - say USA - CA - LA
Country
State
City
So here is my problem. The report loads first time once i click on the
report name. (because it has the default values). But when the user
clicks on the country drop down to select another country say China,
the report down goes blank (white page) UNITL THE USER CLICKS THE "VIEW
REPORT" BUTTON (WHICH IS VERY VERY ANNOYIG). Is there anyway i can
leave the table as it is without making it blank.
Please help
Thanks
Prashhi
I don't think there is a work around to this (but I would be interested in
finding out one). This is just the default behavior which appears to be a
postback on change.
Also, coming from the other direction AND just as annoying (my defaults, by
request of the customer, are 'ALL','ALL','ALL' - which is a waste of computer
resources). Fortunately, we have an ASP.NET interface which 90% of the end
users use.
Rob
"Prash" wrote:
> Hi,
> I have a table with 100 rows in the following format
> ---
> country name state name city name
> ---
> USA NY NY
> USA NJ Newark
> USA FL Orlando
> USA FL Miami
> USA CA LA
> USA CA SF
> China XX XX
> India XX XX
> Australia XX XX
> China XX XX
> India XX XX
> ----
>
> I have 3 dropdown cascading parameters (all of them have default values
> when the report loads) - say USA - CA - LA
> Country
> State
> City
>
> So here is my problem. The report loads first time once i click on the
> report name. (because it has the default values). But when the user
> clicks on the country drop down to select another country say China,
> the report down goes blank (white page) UNITL THE USER CLICKS THE "VIEW
> REPORT" BUTTON (WHICH IS VERY VERY ANNOYIG). Is there anyway i can
> leave the table as it is without making it blank.
>
> Please help
> Thanks
> Prash
>