I have had to add Calculated columns to some tables in my Data Source View in
order to correctly link some tables but this kills the model generation.
For example, although the job table has a CompanyID column, the job_task
table does not - but I need to be able to lookup a status in CompanyReason
which is indexed on CompanyID and PauseReason (this column is in job_task).
So I added 'theCompanyID' as a Calculated columns to job_task with the
following code:
SELECT TOP 1 j.company_id FROM mh4_job_task jt INNER JOIN mh4_job j on
j.job_id = jt.job_id
Makes sense? But when I create a model with it - this is the error message:
--
An error occurred while executing a command.
Message: Cannot perform an aggregate function on an expression containing an
aggregate or a subquery.
Line 5: Incorrect syntax near ')'.
Command:
SELECT
COUNT([notes]),
COUNT(DISTINCT [notes]),
COUNT((SELECT TOP 1 j.company_id FROM job_task jt INNER JOIN job j on
j.job_id = jt.job_id)),
COUNT(DISTINCT (SELECT TOP 1 j.company_id FROM job_task jt INNER JOIN job j
on j.job_id = jt.job_id)),
COUNT('task_status'),
COUNT(DISTINCT 'task_status')
FROM [dbo].[job_task] t
--
Of course, the error is correct - what I need to know is how to either
bypass the column uniqueness test FOR THIS ONE column or another method to
create the link in the Data Source View.
Thank you,
ScottI would use SSIS to get this set up. I have not done such a thing, but since
SSIS can provide report datasources that's the route I would look at.
"ScottB" <ScottB@.discussions.microsoft.com> wrote in message
news:95A89A5B-F36B-4056-96BF-A748B613CA25@.microsoft.com...
>I have had to add Calculated columns to some tables in my Data Source View
>in
> order to correctly link some tables but this kills the model generation.
> For example, although the job table has a CompanyID column, the job_task
> table does not - but I need to be able to lookup a status in CompanyReason
> which is indexed on CompanyID and PauseReason (this column is in
> job_task).
> So I added 'theCompanyID' as a Calculated columns to job_task with the
> following code:
> SELECT TOP 1 j.company_id FROM mh4_job_task jt INNER JOIN mh4_job j on
> j.job_id = jt.job_id
>
> Makes sense? But when I create a model with it - this is the error
> message:
> --
> An error occurred while executing a command.
> Message: Cannot perform an aggregate function on an expression containing
> an
> aggregate or a subquery.
> Line 5: Incorrect syntax near ')'.
> Command:
> SELECT
> COUNT([notes]),
> COUNT(DISTINCT [notes]),
> COUNT((SELECT TOP 1 j.company_id FROM job_task jt INNER JOIN job j on
> j.job_id = jt.job_id)),
> COUNT(DISTINCT (SELECT TOP 1 j.company_id FROM job_task jt INNER JOIN job
> j
> on j.job_id = jt.job_id)),
> COUNT('task_status'),
> COUNT(DISTINCT 'task_status')
> FROM [dbo].[job_task] t
> --
> Of course, the error is correct - what I need to know is how to either
> bypass the column uniqueness test FOR THIS ONE column or another method to
> create the link in the Data Source View.
> Thank you,
> Scott|||Hi,
I am new to SSIS. I have been searching for some examples to
use Expression Builder in SSIS to use derived columns
transformation...I basically want to add a new column to my data source
on the run and add this column as the 2nd column before loading into
destination. How can I achieve this? Plz help. Is there a link where I
can get examples online'
Showing posts with label link. Show all posts
Showing posts with label link. Show all posts
Wednesday, March 28, 2012
Friday, March 23, 2012
Mobile Database Security
i research about mobile database security. please link all resources about this issue. thank you.WHat do you want to achieve or what is problem with that ?
Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||i want resources about algorithms, protocols and all rules for secure mobile database. i google but few and poor content is listed. i want references for this issue.|||
You might look into the latest BOoks online update for SQL Server 2005, it has various options and information on the aspect of security.
http://msdn2.microsoft.com/en-us/library/ms171955.aspx
http://support.microsoft.com/kb/314783
http://vyaskn.tripod.com/sql_server_security_distilled.htm
sqlMonday, March 12, 2012
Missing table
I have a SQL 7.0 server holding our Agency's most critical
database. At my deskstop, I link to this database in
Access via ODBC drivers. I am linked to a specific table
which has disappeared from the server. It does not appear
in Enterprise Manager. I can find no "hidden" attributes
to reverse...the table is simply gone. However, I can use
the Query Analyser to get a count of records in this table
and it returns the correct number of records. Furthermore,
all data prior to 5/18/04 has also disappeared. Can anyone
help me find this table AND more importantly, help me
discover why the data disappeared. No, I have no backups
going back that far..... Thanks in advance.
I am guessing that the table is "owned" by some other user.
Using Query Analyzer
Connnect to the server
switch to the appropriate database
SELECT TABLE_SCHEMA, TABLE_TYPE, TABLE_NAME FROM =
INFORMATION_SCHEMA.tables
SELECT crdate, name FROM sysobjects WHERE TYPE =3D 'u' ORDER BY crdate =
DESC, name
Regarding the dissapearing data, I am guessing that someone who thought =
they knew what they were doing (on or around 5/18/2004) created a new =
table (with a different owner) and dropped the old table. That is =
speculation, but based on the information that you have provided it =
sounds logical. The other possibility is that someone deleted the data =
within the table and changed the owner.
Whatever the case may be, you have some investigation to do!
--=20
Keith
"Bean" <anonymous@.discussions.microsoft.com> wrote in message =
news:1371901c44404$a95e1610$a601280a@.phx.gbl...
> I have a SQL 7.0 server holding our Agency's most critical=20
> database. At my deskstop, I link to this database in=20
> Access via ODBC drivers. I am linked to a specific table=20
> which has disappeared from the server. It does not appear=20
> in Enterprise Manager. I can find no "hidden" attributes=20
> to reverse...the table is simply gone. However, I can use=20
> the Query Analyser to get a count of records in this table=20
> and it returns the correct number of records. Furthermore,=20
> all data prior to 5/18/04 has also disappeared. Can anyone=20
> help me find this table AND more importantly, help me=20
> discover why the data disappeared. No, I have no backups=20
> going back that far..... Thanks in advance.
|||Am I to type out the 2 commands you list, as is? What am
I looking for in the results?
I executed the first one and it gave me a list of 24
items, most saying DBO, while the last ones said
INFORMATION_SCHEMA. What does this mean?
>--Original Message--
>I am guessing that the table is "owned" by some other
user.
>Using Query Analyzer
>Connnect to the server
>switch to the appropriate database
>SELECT TABLE_SCHEMA, TABLE_TYPE, TABLE_NAME FROM
INFORMATION_SCHEMA.tables
>SELECT crdate, name FROM sysobjects WHERE TYPE = 'u'
ORDER BY crdate DESC, name
>
>Regarding the dissapearing data, I am guessing that
someone who thought they knew what they were doing (on or
around 5/18/2004) created a new table (with a different
owner) and dropped the old table. That is speculation,
but based on the information that you have provided it
sounds logical. The other possibility is that someone
deleted the data within the table and changed the owner.
>Whatever the case may be, you have some investigation to
do!
>--
>Keith
>
>"Bean" <anonymous@.discussions.microsoft.com> wrote in
message news:1371901c44404$a95e1610$a601280a@.phx.gbl...[vbcol=seagreen]
critical[vbcol=seagreen]
table[vbcol=seagreen]
appear[vbcol=seagreen]
attributes[vbcol=seagreen]
use[vbcol=seagreen]
table[vbcol=seagreen]
Furthermore,[vbcol=seagreen]
anyone[vbcol=seagreen]
backups
>.
>
|||Yes, execute the commands within Query Analyzer.
The first piece of sql displays the table owner, type (table or view), =
and the name of the table.
The second piece of sql displays the creation date for the table as well =
as the table name.
Knowing these pieces of information might help in your investigation to =
find out what happened.
You mentioned that "most" are owned by dbo. How about the one table =
that is causing problems? Is it owned by someone else?
You mentioned that it disappeared from the [tables] view within =
Enterprise Manager. Hit the "views" node. Is your "table" listed =
there? Jump out to Query Analyzer and perform=20
exec sp_helptext YourTableNameGoesHere=20
That will give you the text of the view (if there is a view with the =
same name as the table). Perhaps the view is limiting the rows (making =
it look like some data is missing).
--=20
Keith
<anonymous@.discussions.microsoft.com> wrote in message =
news:13a0501c44413$262bab40$a001280a@.phx.gbl...[vbcol=seagreen]
> Am I to type out the 2 commands you list, as is? What am=20
> I looking for in the results? =20
> I executed the first one and it gave me a list of 24=20
> items, most saying DBO, while the last ones said=20
> INFORMATION_SCHEMA. What does this mean?
>=20
> user.
> INFORMATION_SCHEMA.tables
> ORDER BY crdate DESC, name
> someone who thought they knew what they were doing (on or=20
> around 5/18/2004) created a new table (with a different=20
> owner) and dropped the old table. That is speculation,=20
> but based on the information that you have provided it=20
> sounds logical. The other possibility is that someone=20
> deleted the data within the table and changed the owner.
> do!
> message news:1371901c44404$a95e1610$a601280a@.phx.gbl...
> critical=20
> table=20
> appear=20
> attributes=20
> use=20
> table=20
> Furthermore,=20
> anyone=20
> backups=20
database. At my deskstop, I link to this database in
Access via ODBC drivers. I am linked to a specific table
which has disappeared from the server. It does not appear
in Enterprise Manager. I can find no "hidden" attributes
to reverse...the table is simply gone. However, I can use
the Query Analyser to get a count of records in this table
and it returns the correct number of records. Furthermore,
all data prior to 5/18/04 has also disappeared. Can anyone
help me find this table AND more importantly, help me
discover why the data disappeared. No, I have no backups
going back that far..... Thanks in advance.
I am guessing that the table is "owned" by some other user.
Using Query Analyzer
Connnect to the server
switch to the appropriate database
SELECT TABLE_SCHEMA, TABLE_TYPE, TABLE_NAME FROM =
INFORMATION_SCHEMA.tables
SELECT crdate, name FROM sysobjects WHERE TYPE =3D 'u' ORDER BY crdate =
DESC, name
Regarding the dissapearing data, I am guessing that someone who thought =
they knew what they were doing (on or around 5/18/2004) created a new =
table (with a different owner) and dropped the old table. That is =
speculation, but based on the information that you have provided it =
sounds logical. The other possibility is that someone deleted the data =
within the table and changed the owner.
Whatever the case may be, you have some investigation to do!
--=20
Keith
"Bean" <anonymous@.discussions.microsoft.com> wrote in message =
news:1371901c44404$a95e1610$a601280a@.phx.gbl...
> I have a SQL 7.0 server holding our Agency's most critical=20
> database. At my deskstop, I link to this database in=20
> Access via ODBC drivers. I am linked to a specific table=20
> which has disappeared from the server. It does not appear=20
> in Enterprise Manager. I can find no "hidden" attributes=20
> to reverse...the table is simply gone. However, I can use=20
> the Query Analyser to get a count of records in this table=20
> and it returns the correct number of records. Furthermore,=20
> all data prior to 5/18/04 has also disappeared. Can anyone=20
> help me find this table AND more importantly, help me=20
> discover why the data disappeared. No, I have no backups=20
> going back that far..... Thanks in advance.
|||Am I to type out the 2 commands you list, as is? What am
I looking for in the results?
I executed the first one and it gave me a list of 24
items, most saying DBO, while the last ones said
INFORMATION_SCHEMA. What does this mean?
>--Original Message--
>I am guessing that the table is "owned" by some other
user.
>Using Query Analyzer
>Connnect to the server
>switch to the appropriate database
>SELECT TABLE_SCHEMA, TABLE_TYPE, TABLE_NAME FROM
INFORMATION_SCHEMA.tables
>SELECT crdate, name FROM sysobjects WHERE TYPE = 'u'
ORDER BY crdate DESC, name
>
>Regarding the dissapearing data, I am guessing that
someone who thought they knew what they were doing (on or
around 5/18/2004) created a new table (with a different
owner) and dropped the old table. That is speculation,
but based on the information that you have provided it
sounds logical. The other possibility is that someone
deleted the data within the table and changed the owner.
>Whatever the case may be, you have some investigation to
do!
>--
>Keith
>
>"Bean" <anonymous@.discussions.microsoft.com> wrote in
message news:1371901c44404$a95e1610$a601280a@.phx.gbl...[vbcol=seagreen]
critical[vbcol=seagreen]
table[vbcol=seagreen]
appear[vbcol=seagreen]
attributes[vbcol=seagreen]
use[vbcol=seagreen]
table[vbcol=seagreen]
Furthermore,[vbcol=seagreen]
anyone[vbcol=seagreen]
backups
>.
>
|||Yes, execute the commands within Query Analyzer.
The first piece of sql displays the table owner, type (table or view), =
and the name of the table.
The second piece of sql displays the creation date for the table as well =
as the table name.
Knowing these pieces of information might help in your investigation to =
find out what happened.
You mentioned that "most" are owned by dbo. How about the one table =
that is causing problems? Is it owned by someone else?
You mentioned that it disappeared from the [tables] view within =
Enterprise Manager. Hit the "views" node. Is your "table" listed =
there? Jump out to Query Analyzer and perform=20
exec sp_helptext YourTableNameGoesHere=20
That will give you the text of the view (if there is a view with the =
same name as the table). Perhaps the view is limiting the rows (making =
it look like some data is missing).
--=20
Keith
<anonymous@.discussions.microsoft.com> wrote in message =
news:13a0501c44413$262bab40$a001280a@.phx.gbl...[vbcol=seagreen]
> Am I to type out the 2 commands you list, as is? What am=20
> I looking for in the results? =20
> I executed the first one and it gave me a list of 24=20
> items, most saying DBO, while the last ones said=20
> INFORMATION_SCHEMA. What does this mean?
>=20
> user.
> INFORMATION_SCHEMA.tables
> ORDER BY crdate DESC, name
> someone who thought they knew what they were doing (on or=20
> around 5/18/2004) created a new table (with a different=20
> owner) and dropped the old table. That is speculation,=20
> but based on the information that you have provided it=20
> sounds logical. The other possibility is that someone=20
> deleted the data within the table and changed the owner.
> do!
> message news:1371901c44404$a95e1610$a601280a@.phx.gbl...
> critical=20
> table=20
> appear=20
> attributes=20
> use=20
> table=20
> Furthermore,=20
> anyone=20
> backups=20
Missing table
I have a SQL 7.0 server holding our Agency's most critical
database. At my deskstop, I link to this database in
Access via ODBC drivers. I am linked to a specific table
which has disappeared from the server. It does not appear
in Enterprise Manager. I can find no "hidden" attributes
to reverse...the table is simply gone. However, I can use
the Query Analyser to get a count of records in this table
and it returns the correct number of records. Furthermore,
all data prior to 5/18/04 has also disappeared. Can anyone
help me find this table AND more importantly, help me
discover why the data disappeared. No, I have no backups
going back that far..... Thanks in advance.I am guessing that the table is "owned" by some other user.
Using Query Analyzer
Connnect to the server
switch to the appropriate database
SELECT TABLE_SCHEMA, TABLE_TYPE, TABLE_NAME FROM =
INFORMATION_SCHEMA.tables
SELECT crdate, name FROM sysobjects WHERE TYPE =3D 'u' ORDER BY crdate =
DESC, name
Regarding the dissapearing data, I am guessing that someone who thought =
they knew what they were doing (on or around 5/18/2004) created a new =
table (with a different owner) and dropped the old table. That is =
speculation, but based on the information that you have provided it =
sounds logical. The other possibility is that someone deleted the data =
within the table and changed the owner.
Whatever the case may be, you have some investigation to do!
--=20
Keith
"Bean" <anonymous@.discussions.microsoft.com> wrote in message =
news:1371901c44404$a95e1610$a601280a@.phx
.gbl...
> I have a SQL 7.0 server holding our Agency's most critical=20
> database. At my deskstop, I link to this database in=20
> Access via ODBC drivers. I am linked to a specific table=20
> which has disappeared from the server. It does not appear=20
> in Enterprise Manager. I can find no "hidden" attributes=20
> to reverse...the table is simply gone. However, I can use=20
> the Query Analyser to get a count of records in this table=20
> and it returns the correct number of records. Furthermore,=20
> all data prior to 5/18/04 has also disappeared. Can anyone=20
> help me find this table AND more importantly, help me=20
> discover why the data disappeared. No, I have no backups=20
> going back that far..... Thanks in advance.|||Am I to type out the 2 commands you list, as is? What am
I looking for in the results?
I executed the first one and it gave me a list of 24
items, most saying DBO, while the last ones said
INFORMATION_SCHEMA. What does this mean?
>--Original Message--
>I am guessing that the table is "owned" by some other
user.
>Using Query Analyzer
>Connnect to the server
>switch to the appropriate database
>SELECT TABLE_SCHEMA, TABLE_TYPE, TABLE_NAME FROM
INFORMATION_SCHEMA.tables
>SELECT crdate, name FROM sysobjects WHERE TYPE = 'u'
ORDER BY crdate DESC, name
>
>Regarding the dissapearing data, I am guessing that
someone who thought they knew what they were doing (on or
around 5/18/2004) created a new table (with a different
owner) and dropped the old table. That is speculation,
but based on the information that you have provided it
sounds logical. The other possibility is that someone
deleted the data within the table and changed the owner.
>Whatever the case may be, you have some investigation to
do!
>--
>Keith
>
>"Bean" <anonymous@.discussions.microsoft.com> wrote in
message news:1371901c44404$a95e1610$a601280a@.phx
.gbl...
critical[vbcol=seagreen]
table[vbcol=seagreen]
appear[vbcol=seagreen]
attributes[vbcol=seagreen]
use[vbcol=seagreen]
table[vbcol=seagreen]
Furthermore,[vbcol=seagreen]
anyone[vbcol=seagreen]
backups[vbcol=seagreen]
>.
>|||Yes, execute the commands within Query Analyzer.
The first piece of sql displays the table owner, type (table or view), =
and the name of the table.
The second piece of sql displays the creation date for the table as well =
as the table name.
Knowing these pieces of information might help in your investigation to =
find out what happened.
You mentioned that "most" are owned by dbo. How about the one table =
that is causing problems? Is it owned by someone else?
You mentioned that it disappeared from the [tables] view within =
Enterprise Manager. Hit the "views" node. Is your "table" listed =
there? Jump out to Query Analyzer and perform=20
exec sp_helptext YourTableNameGoesHere=20
That will give you the text of the view (if there is a view with the =
same name as the table). Perhaps the view is limiting the rows (making =
it look like some data is missing).
--=20
Keith
<anonymous@.discussions.microsoft.com> wrote in message =
news:13a0501c44413$262bab40$a001280a@.phx
.gbl...[vbcol=seagreen]
> Am I to type out the 2 commands you list, as is? What am=20
> I looking for in the results? =20
> I executed the first one and it gave me a list of 24=20
> items, most saying DBO, while the last ones said=20
> INFORMATION_SCHEMA. What does this mean?
>=20
> user.
> INFORMATION_SCHEMA.tables
> ORDER BY crdate DESC, name
> someone who thought they knew what they were doing (on or=20
> around 5/18/2004) created a new table (with a different=20
> owner) and dropped the old table. That is speculation,=20
> but based on the information that you have provided it=20
> sounds logical. The other possibility is that someone=20
> deleted the data within the table and changed the owner.
> do!
> message news:1371901c44404$a95e1610$a601280a@.phx
.gbl...
> critical=20
> table=20
> appear=20
> attributes=20
> use=20
> table=20
> Furthermore,=20
> anyone=20
> backups=20
database. At my deskstop, I link to this database in
Access via ODBC drivers. I am linked to a specific table
which has disappeared from the server. It does not appear
in Enterprise Manager. I can find no "hidden" attributes
to reverse...the table is simply gone. However, I can use
the Query Analyser to get a count of records in this table
and it returns the correct number of records. Furthermore,
all data prior to 5/18/04 has also disappeared. Can anyone
help me find this table AND more importantly, help me
discover why the data disappeared. No, I have no backups
going back that far..... Thanks in advance.I am guessing that the table is "owned" by some other user.
Using Query Analyzer
Connnect to the server
switch to the appropriate database
SELECT TABLE_SCHEMA, TABLE_TYPE, TABLE_NAME FROM =
INFORMATION_SCHEMA.tables
SELECT crdate, name FROM sysobjects WHERE TYPE =3D 'u' ORDER BY crdate =
DESC, name
Regarding the dissapearing data, I am guessing that someone who thought =
they knew what they were doing (on or around 5/18/2004) created a new =
table (with a different owner) and dropped the old table. That is =
speculation, but based on the information that you have provided it =
sounds logical. The other possibility is that someone deleted the data =
within the table and changed the owner.
Whatever the case may be, you have some investigation to do!
--=20
Keith
"Bean" <anonymous@.discussions.microsoft.com> wrote in message =
news:1371901c44404$a95e1610$a601280a@.phx
.gbl...
> I have a SQL 7.0 server holding our Agency's most critical=20
> database. At my deskstop, I link to this database in=20
> Access via ODBC drivers. I am linked to a specific table=20
> which has disappeared from the server. It does not appear=20
> in Enterprise Manager. I can find no "hidden" attributes=20
> to reverse...the table is simply gone. However, I can use=20
> the Query Analyser to get a count of records in this table=20
> and it returns the correct number of records. Furthermore,=20
> all data prior to 5/18/04 has also disappeared. Can anyone=20
> help me find this table AND more importantly, help me=20
> discover why the data disappeared. No, I have no backups=20
> going back that far..... Thanks in advance.|||Am I to type out the 2 commands you list, as is? What am
I looking for in the results?
I executed the first one and it gave me a list of 24
items, most saying DBO, while the last ones said
INFORMATION_SCHEMA. What does this mean?
>--Original Message--
>I am guessing that the table is "owned" by some other
user.
>Using Query Analyzer
>Connnect to the server
>switch to the appropriate database
>SELECT TABLE_SCHEMA, TABLE_TYPE, TABLE_NAME FROM
INFORMATION_SCHEMA.tables
>SELECT crdate, name FROM sysobjects WHERE TYPE = 'u'
ORDER BY crdate DESC, name
>
>Regarding the dissapearing data, I am guessing that
someone who thought they knew what they were doing (on or
around 5/18/2004) created a new table (with a different
owner) and dropped the old table. That is speculation,
but based on the information that you have provided it
sounds logical. The other possibility is that someone
deleted the data within the table and changed the owner.
>Whatever the case may be, you have some investigation to
do!
>--
>Keith
>
>"Bean" <anonymous@.discussions.microsoft.com> wrote in
message news:1371901c44404$a95e1610$a601280a@.phx
.gbl...
critical[vbcol=seagreen]
table[vbcol=seagreen]
appear[vbcol=seagreen]
attributes[vbcol=seagreen]
use[vbcol=seagreen]
table[vbcol=seagreen]
Furthermore,[vbcol=seagreen]
anyone[vbcol=seagreen]
backups[vbcol=seagreen]
>.
>|||Yes, execute the commands within Query Analyzer.
The first piece of sql displays the table owner, type (table or view), =
and the name of the table.
The second piece of sql displays the creation date for the table as well =
as the table name.
Knowing these pieces of information might help in your investigation to =
find out what happened.
You mentioned that "most" are owned by dbo. How about the one table =
that is causing problems? Is it owned by someone else?
You mentioned that it disappeared from the [tables] view within =
Enterprise Manager. Hit the "views" node. Is your "table" listed =
there? Jump out to Query Analyzer and perform=20
exec sp_helptext YourTableNameGoesHere=20
That will give you the text of the view (if there is a view with the =
same name as the table). Perhaps the view is limiting the rows (making =
it look like some data is missing).
--=20
Keith
<anonymous@.discussions.microsoft.com> wrote in message =
news:13a0501c44413$262bab40$a001280a@.phx
.gbl...[vbcol=seagreen]
> Am I to type out the 2 commands you list, as is? What am=20
> I looking for in the results? =20
> I executed the first one and it gave me a list of 24=20
> items, most saying DBO, while the last ones said=20
> INFORMATION_SCHEMA. What does this mean?
>=20
> user.
> INFORMATION_SCHEMA.tables
> ORDER BY crdate DESC, name
> someone who thought they knew what they were doing (on or=20
> around 5/18/2004) created a new table (with a different=20
> owner) and dropped the old table. That is speculation,=20
> but based on the information that you have provided it=20
> sounds logical. The other possibility is that someone=20
> deleted the data within the table and changed the owner.
> do!
> message news:1371901c44404$a95e1610$a601280a@.phx
.gbl...
> critical=20
> table=20
> appear=20
> attributes=20
> use=20
> table=20
> Furthermore,=20
> anyone=20
> backups=20
Missing table
I have a SQL 7.0 server holding our Agency's most critical
database. At my deskstop, I link to this database in
Access via ODBC drivers. I am linked to a specific table
which has disappeared from the server. It does not appear
in Enterprise Manager. I can find no "hidden" attributes
to reverse...the table is simply gone. However, I can use
the Query Analyser to get a count of records in this table
and it returns the correct number of records. Furthermore,
all data prior to 5/18/04 has also disappeared. Can anyone
help me find this table AND more importantly, help me
discover why the data disappeared. No, I have no backups
going back that far..... Thanks in advance.I am guessing that the table is "owned" by some other user.
Using Query Analyzer
Connnect to the server
switch to the appropriate database
SELECT TABLE_SCHEMA, TABLE_TYPE, TABLE_NAME FROM =INFORMATION_SCHEMA.tables
SELECT crdate, name FROM sysobjects WHERE TYPE =3D 'u' ORDER BY crdate =DESC, name
Regarding the dissapearing data, I am guessing that someone who thought =they knew what they were doing (on or around 5/18/2004) created a new =table (with a different owner) and dropped the old table. That is =speculation, but based on the information that you have provided it =sounds logical. The other possibility is that someone deleted the data =within the table and changed the owner.
Whatever the case may be, you have some investigation to do!
-- Keith
"Bean" <anonymous@.discussions.microsoft.com> wrote in message =news:1371901c44404$a95e1610$a601280a@.phx.gbl...
> I have a SQL 7.0 server holding our Agency's most critical > database. At my deskstop, I link to this database in > Access via ODBC drivers. I am linked to a specific table > which has disappeared from the server. It does not appear > in Enterprise Manager. I can find no "hidden" attributes > to reverse...the table is simply gone. However, I can use > the Query Analyser to get a count of records in this table > and it returns the correct number of records. Furthermore, > all data prior to 5/18/04 has also disappeared. Can anyone > help me find this table AND more importantly, help me > discover why the data disappeared. No, I have no backups > going back that far..... Thanks in advance.|||Am I to type out the 2 commands you list, as is? What am
I looking for in the results?
I executed the first one and it gave me a list of 24
items, most saying DBO, while the last ones said
INFORMATION_SCHEMA. What does this mean?
>--Original Message--
>I am guessing that the table is "owned" by some other
user.
>Using Query Analyzer
>Connnect to the server
>switch to the appropriate database
>SELECT TABLE_SCHEMA, TABLE_TYPE, TABLE_NAME FROM
INFORMATION_SCHEMA.tables
>SELECT crdate, name FROM sysobjects WHERE TYPE = 'u'
ORDER BY crdate DESC, name
>
>Regarding the dissapearing data, I am guessing that
someone who thought they knew what they were doing (on or
around 5/18/2004) created a new table (with a different
owner) and dropped the old table. That is speculation,
but based on the information that you have provided it
sounds logical. The other possibility is that someone
deleted the data within the table and changed the owner.
>Whatever the case may be, you have some investigation to
do!
>--
>Keith
>
>"Bean" <anonymous@.discussions.microsoft.com> wrote in
message news:1371901c44404$a95e1610$a601280a@.phx.gbl...
>> I have a SQL 7.0 server holding our Agency's most
critical
>> database. At my deskstop, I link to this database in
>> Access via ODBC drivers. I am linked to a specific
table
>> which has disappeared from the server. It does not
appear
>> in Enterprise Manager. I can find no "hidden"
attributes
>> to reverse...the table is simply gone. However, I can
use
>> the Query Analyser to get a count of records in this
table
>> and it returns the correct number of records.
Furthermore,
>> all data prior to 5/18/04 has also disappeared. Can
anyone
>> help me find this table AND more importantly, help me
>> discover why the data disappeared. No, I have no
backups
>> going back that far..... Thanks in advance.
>.
>|||Yes, execute the commands within Query Analyzer.
The first piece of sql displays the table owner, type (table or view), =and the name of the table.
The second piece of sql displays the creation date for the table as well =as the table name.
Knowing these pieces of information might help in your investigation to =find out what happened.
You mentioned that "most" are owned by dbo. How about the one table =that is causing problems? Is it owned by someone else?
You mentioned that it disappeared from the [tables] view within =Enterprise Manager. Hit the "views" node. Is your "table" listed =there? Jump out to Query Analyzer and perform exec sp_helptext YourTableNameGoesHere That will give you the text of the view (if there is a view with the =same name as the table). Perhaps the view is limiting the rows (making =it look like some data is missing).
-- Keith
<anonymous@.discussions.microsoft.com> wrote in message =news:13a0501c44413$262bab40$a001280a@.phx.gbl...
> Am I to type out the 2 commands you list, as is? What am > I looking for in the results? > I executed the first one and it gave me a list of 24 > items, most saying DBO, while the last ones said > INFORMATION_SCHEMA. What does this mean?
> > >--Original Message--
> >I am guessing that the table is "owned" by some other > user.
> >
> >Using Query Analyzer
> >Connnect to the server
> >switch to the appropriate database
> >
> >SELECT TABLE_SCHEMA, TABLE_TYPE, TABLE_NAME FROM > INFORMATION_SCHEMA.tables
> >
> >SELECT crdate, name FROM sysobjects WHERE TYPE =3D 'u' > ORDER BY crdate DESC, name
> >
> >
> >Regarding the dissapearing data, I am guessing that > someone who thought they knew what they were doing (on or > around 5/18/2004) created a new table (with a different > owner) and dropped the old table. That is speculation, > but based on the information that you have provided it > sounds logical. The other possibility is that someone > deleted the data within the table and changed the owner.
> >
> >Whatever the case may be, you have some investigation to > do!
> >
> >-- > >Keith
> >
> >
> >"Bean" <anonymous@.discussions.microsoft.com> wrote in > message news:1371901c44404$a95e1610$a601280a@.phx.gbl...
> >> I have a SQL 7.0 server holding our Agency's most > critical > >> database. At my deskstop, I link to this database in > >> Access via ODBC drivers. I am linked to a specific > table > >> which has disappeared from the server. It does not > appear > >> in Enterprise Manager. I can find no "hidden" > attributes > >> to reverse...the table is simply gone. However, I can > use > >> the Query Analyser to get a count of records in this > table > >> and it returns the correct number of records. > Furthermore, > >> all data prior to 5/18/04 has also disappeared. Can > anyone > >> help me find this table AND more importantly, help me > >> discover why the data disappeared. No, I have no > backups > >> going back that far..... Thanks in advance.
> >.
> >
database. At my deskstop, I link to this database in
Access via ODBC drivers. I am linked to a specific table
which has disappeared from the server. It does not appear
in Enterprise Manager. I can find no "hidden" attributes
to reverse...the table is simply gone. However, I can use
the Query Analyser to get a count of records in this table
and it returns the correct number of records. Furthermore,
all data prior to 5/18/04 has also disappeared. Can anyone
help me find this table AND more importantly, help me
discover why the data disappeared. No, I have no backups
going back that far..... Thanks in advance.I am guessing that the table is "owned" by some other user.
Using Query Analyzer
Connnect to the server
switch to the appropriate database
SELECT TABLE_SCHEMA, TABLE_TYPE, TABLE_NAME FROM =INFORMATION_SCHEMA.tables
SELECT crdate, name FROM sysobjects WHERE TYPE =3D 'u' ORDER BY crdate =DESC, name
Regarding the dissapearing data, I am guessing that someone who thought =they knew what they were doing (on or around 5/18/2004) created a new =table (with a different owner) and dropped the old table. That is =speculation, but based on the information that you have provided it =sounds logical. The other possibility is that someone deleted the data =within the table and changed the owner.
Whatever the case may be, you have some investigation to do!
-- Keith
"Bean" <anonymous@.discussions.microsoft.com> wrote in message =news:1371901c44404$a95e1610$a601280a@.phx.gbl...
> I have a SQL 7.0 server holding our Agency's most critical > database. At my deskstop, I link to this database in > Access via ODBC drivers. I am linked to a specific table > which has disappeared from the server. It does not appear > in Enterprise Manager. I can find no "hidden" attributes > to reverse...the table is simply gone. However, I can use > the Query Analyser to get a count of records in this table > and it returns the correct number of records. Furthermore, > all data prior to 5/18/04 has also disappeared. Can anyone > help me find this table AND more importantly, help me > discover why the data disappeared. No, I have no backups > going back that far..... Thanks in advance.|||Am I to type out the 2 commands you list, as is? What am
I looking for in the results?
I executed the first one and it gave me a list of 24
items, most saying DBO, while the last ones said
INFORMATION_SCHEMA. What does this mean?
>--Original Message--
>I am guessing that the table is "owned" by some other
user.
>Using Query Analyzer
>Connnect to the server
>switch to the appropriate database
>SELECT TABLE_SCHEMA, TABLE_TYPE, TABLE_NAME FROM
INFORMATION_SCHEMA.tables
>SELECT crdate, name FROM sysobjects WHERE TYPE = 'u'
ORDER BY crdate DESC, name
>
>Regarding the dissapearing data, I am guessing that
someone who thought they knew what they were doing (on or
around 5/18/2004) created a new table (with a different
owner) and dropped the old table. That is speculation,
but based on the information that you have provided it
sounds logical. The other possibility is that someone
deleted the data within the table and changed the owner.
>Whatever the case may be, you have some investigation to
do!
>--
>Keith
>
>"Bean" <anonymous@.discussions.microsoft.com> wrote in
message news:1371901c44404$a95e1610$a601280a@.phx.gbl...
>> I have a SQL 7.0 server holding our Agency's most
critical
>> database. At my deskstop, I link to this database in
>> Access via ODBC drivers. I am linked to a specific
table
>> which has disappeared from the server. It does not
appear
>> in Enterprise Manager. I can find no "hidden"
attributes
>> to reverse...the table is simply gone. However, I can
use
>> the Query Analyser to get a count of records in this
table
>> and it returns the correct number of records.
Furthermore,
>> all data prior to 5/18/04 has also disappeared. Can
anyone
>> help me find this table AND more importantly, help me
>> discover why the data disappeared. No, I have no
backups
>> going back that far..... Thanks in advance.
>.
>|||Yes, execute the commands within Query Analyzer.
The first piece of sql displays the table owner, type (table or view), =and the name of the table.
The second piece of sql displays the creation date for the table as well =as the table name.
Knowing these pieces of information might help in your investigation to =find out what happened.
You mentioned that "most" are owned by dbo. How about the one table =that is causing problems? Is it owned by someone else?
You mentioned that it disappeared from the [tables] view within =Enterprise Manager. Hit the "views" node. Is your "table" listed =there? Jump out to Query Analyzer and perform exec sp_helptext YourTableNameGoesHere That will give you the text of the view (if there is a view with the =same name as the table). Perhaps the view is limiting the rows (making =it look like some data is missing).
-- Keith
<anonymous@.discussions.microsoft.com> wrote in message =news:13a0501c44413$262bab40$a001280a@.phx.gbl...
> Am I to type out the 2 commands you list, as is? What am > I looking for in the results? > I executed the first one and it gave me a list of 24 > items, most saying DBO, while the last ones said > INFORMATION_SCHEMA. What does this mean?
> > >--Original Message--
> >I am guessing that the table is "owned" by some other > user.
> >
> >Using Query Analyzer
> >Connnect to the server
> >switch to the appropriate database
> >
> >SELECT TABLE_SCHEMA, TABLE_TYPE, TABLE_NAME FROM > INFORMATION_SCHEMA.tables
> >
> >SELECT crdate, name FROM sysobjects WHERE TYPE =3D 'u' > ORDER BY crdate DESC, name
> >
> >
> >Regarding the dissapearing data, I am guessing that > someone who thought they knew what they were doing (on or > around 5/18/2004) created a new table (with a different > owner) and dropped the old table. That is speculation, > but based on the information that you have provided it > sounds logical. The other possibility is that someone > deleted the data within the table and changed the owner.
> >
> >Whatever the case may be, you have some investigation to > do!
> >
> >-- > >Keith
> >
> >
> >"Bean" <anonymous@.discussions.microsoft.com> wrote in > message news:1371901c44404$a95e1610$a601280a@.phx.gbl...
> >> I have a SQL 7.0 server holding our Agency's most > critical > >> database. At my deskstop, I link to this database in > >> Access via ODBC drivers. I am linked to a specific > table > >> which has disappeared from the server. It does not > appear > >> in Enterprise Manager. I can find no "hidden" > attributes > >> to reverse...the table is simply gone. However, I can > use > >> the Query Analyser to get a count of records in this > table > >> and it returns the correct number of records. > Furthermore, > >> all data prior to 5/18/04 has also disappeared. Can > anyone > >> help me find this table AND more importantly, help me > >> discover why the data disappeared. No, I have no > backups > >> going back that far..... Thanks in advance.
> >.
> >
Subscribe to:
Posts (Atom)