Showing posts with label via. Show all posts
Showing posts with label via. Show all posts

Monday, March 19, 2012

mission impossible: How to protect a Sql Server (with VB.NET) from somebody who as adminis

Hi,
I'm sorry for the probable cross-posting, but I need a solution that can be
either via Sql Server (preferable) or via .NET...
In one of our commpany's we're having a huge problem with a crazy situation:
A lot of people which are working for the company are also supplier to the
company. So they are manage their own delivery's, and their own payments...
They used to have applications in Access, but if they want to (and they do,
hehe) thay can change their own payments they earn. Some of them have even
administration rights, and full access to the Sql Server Database (it
personell etc).
So what I need is:
- make the application in VB.NET, without giving them the source offcourse
- a protection so that, even with administrator rights, they can't change
their own data. I was thinking about something like a checksum-algeorithm.
Does anybody has experience with this? how should I implement an
checksum-algorithm? What other options do I have?
Any help will be really appreciated!!
Thanks a lot in advance,
PieterYou can't protect your data against users with the SYSADMIN role. What
you should be able to do is deny admin level access to the users. You
didn't explain why they need that. If it's an issue that they need to
administer some things and not others then separate off the restricted
data into another server or instance to which they don't need admin
level access.
If it's just an issue of them historically having had an admin user
name and password, then change the user name or password and/or issue
new user names as appropriate.
--
David Portas
SQL Server MVP
--|||Pieter skrev:
> In one of our commpany's we're having a huge problem with a crazy situation:
> A lot of people which are working for the company are also supplier to the
> company. So they are manage their own delivery's, and their own payments...
Hi Pieter,
I won't provide you with the answer you want, I just want to recommend
you and the company you work for to clear up the situation instead of
trying to patch it with some software solution... The people that are
suppliers
should obviously NOT be authorized to change/access such data.
Hope you'll sort this situation out!
Best regards,
/impslayer, aka Birger Johansson|||The problem is: the IT staff that must do the maintenance of the SQL Server
really needs the sysadmin...
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1131018930.858186.322060@.z14g2000cwz.googlegroups.com...
> You can't protect your data against users with the SYSADMIN role. What
> you should be able to do is deny admin level access to the users. You
> didn't explain why they need that. If it's an issue that they need to
> administer some things and not others then separate off the restricted
> data into another server or instance to which they don't need admin
> level access.
> If it's just an issue of them historically having had an admin user
> name and password, then change the user name or password and/or issue
> new user names as appropriate.
> --
> David Portas
> SQL Server MVP
> --
>|||Hehe well I do know that, and everybody concerned does, but it's just not
this simple...
It's foolish to alow the people of the staff to become their own suppliers.
But they did it, and going back will be hard and even impossible.
We just can try to make it as hard as possible, and have at least a much
smaller chance on fraude...
"impslayer" <impslayer@.hotmail.com> wrote in message
news:1131019112.339033.60950@.o13g2000cwo.googlegroups.com...
> Pieter skrev:
>> In one of our commpany's we're having a huge problem with a crazy
>> situation:
>> A lot of people which are working for the company are also supplier to
>> the
>> company. So they are manage their own delivery's, and their own
>> payments...
> Hi Pieter,
> I won't provide you with the answer you want, I just want to recommend
> you and the company you work for to clear up the situation instead of
> trying to patch it with some software solution... The people that are
> suppliers
> should obviously NOT be authorized to change/access such data.
> Hope you'll sort this situation out!
> Best regards,
> /impslayer, aka Birger Johansson
>|||Rather than attempt to change their rights for their data, consider putting
a mechanism into place that stores historical data so you have a way to
prove who change the data and when. This, in the long run, will have a
greater effect, as you can say "you change your payments from $1000 to $2000
on December 5, 2005 at 12:35:36.001 AM", which is against our rules. Rather
than locking them out, logging changes and even sending alerts to key staff
when certain fields are changed -- priceless.
This can certainly be done in SQL Server with triggers, but realize that the
sysadmin can change or delete triggers. If you put a VB.NET front end, you
have the power to do what you want, as long as they cannot circumvent the
front end you create. If they have table access, you are toast.
I cannot give you a firm direction without a better understanding of the
product, which is beyond the scope of an open forum. Now, if you want to
stop changes to certain fields, you can add a trigger that refuses updates
on that field. Realize they can disable it, however.
One more option: Have a second copy of the data that you do not want them to
change. You can then compare deltas and find cases where they are playing
games and then present them with the evidence.
--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***********************************************
Think Outside the Box!
***********************************************
"Pieter" <pietercoucke@.hotmail.com> wrote in message
news:uAtUqlG4FHA.4076@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I'm sorry for the probable cross-posting, but I need a solution that can
> be either via Sql Server (preferable) or via .NET...
> In one of our commpany's we're having a huge problem with a crazy
> situation: A lot of people which are working for the company are also
> supplier to the company. So they are manage their own delivery's, and
> their own payments...
> They used to have applications in Access, but if they want to (and they
> do, hehe) thay can change their own payments they earn. Some of them have
> even administration rights, and full access to the Sql Server Database (it
> personell etc).
> So what I need is:
> - make the application in VB.NET, without giving them the source offcourse
> - a protection so that, even with administrator rights, they can't change
> their own data. I was thinking about something like a checksum-algeorithm.
> Does anybody has experience with this? how should I implement an
> checksum-algorithm? What other options do I have?
> Any help will be really appreciated!!
> Thanks a lot in advance,
>
> Pieter
>|||SQL Server permissions are incredibly configurable. You don't have to make
people administrators to enable them to perform specific types of tasks. You
can create accounts that have these specific permissions and no other.
--
HTH,
Kevin Spencer
Microsoft MVP
.Net Developer
A watched clock never boils.
"Pieter" <pietercoucke@.hotmail.com> wrote in message
news:eSfAs4G4FHA.3292@.tk2msftngp13.phx.gbl...
> The problem is: the IT staff that must do the maintenance of the SQL
> Server really needs the sysadmin...
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1131018930.858186.322060@.z14g2000cwz.googlegroups.com...
>> You can't protect your data against users with the SYSADMIN role. What
>> you should be able to do is deny admin level access to the users. You
>> didn't explain why they need that. If it's an issue that they need to
>> administer some things and not others then separate off the restricted
>> data into another server or instance to which they don't need admin
>> level access.
>> If it's just an issue of them historically having had an admin user
>> name and password, then change the user name or password and/or issue
>> new user names as appropriate.
>> --
>> David Portas
>> SQL Server MVP
>> --
>|||Ok, here is a really ugly solution...
Encrypt and decrypt the data in your application. Store encrypted data in
the database, at least for the fields in question. This adds a lot of
overhead to your application, but as long as you are not dealing with large
amounts of data you may still be ok. Only those with access to the
application will be able to store the data in the proper encrypted format.
Note, in order for such an encryption to work you have to make it complex
enough so that "1000" will be encrypted differently for every record. i.e
make the primary key part of your encryption algorithm.
Now, you probably need to have access to this data for reporting, in which
case you can store the critical values in both encrypted and plain text
form. A simple procedure in the application will find all the records that
were tampered with. Also, when it comes time to pay out this money, make
sure it is the encrypted value that is being used.
"Pieter" <pietercoucke@.hotmail.com> wrote in message
news:uAtUqlG4FHA.4076@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I'm sorry for the probable cross-posting, but I need a solution that can
be
> either via Sql Server (preferable) or via .NET...
> In one of our commpany's we're having a huge problem with a crazy
situation:
> A lot of people which are working for the company are also supplier to the
> company. So they are manage their own delivery's, and their own
payments...
> They used to have applications in Access, but if they want to (and they
do,
> hehe) thay can change their own payments they earn. Some of them have even
> administration rights, and full access to the Sql Server Database (it
> personell etc).
> So what I need is:
> - make the application in VB.NET, without giving them the source offcourse
> - a protection so that, even with administrator rights, they can't change
> their own data. I was thinking about something like a checksum-algeorithm.
> Does anybody has experience with this? how should I implement an
> checksum-algorithm? What other options do I have?
> Any help will be really appreciated!!
> Thanks a lot in advance,
>
> Pieter
>|||Thanks guys for the help and hints.
I guess I will indeed need to encrypt the values, or better: add a hash so
the values can be used in reports.
The only problem with reports is thatonce the report is exported, values can
be changed afterwurths... Some kind of solution may be to hash the total of
the report and print iton the report, and give the people that receive the
report a tool that recreates the hash to be able to verify it...
"Jim Underwood" <james.underwood@.fallonclinic.com> wrote in message
news:%23aB0Z6U4FHA.1420@.TK2MSFTNGP09.phx.gbl...
> Ok, here is a really ugly solution...
> Encrypt and decrypt the data in your application. Store encrypted data in
> the database, at least for the fields in question. This adds a lot of
> overhead to your application, but as long as you are not dealing with
> large
> amounts of data you may still be ok. Only those with access to the
> application will be able to store the data in the proper encrypted format.
> Note, in order for such an encryption to work you have to make it complex
> enough so that "1000" will be encrypted differently for every record. i.e
> make the primary key part of your encryption algorithm.
> Now, you probably need to have access to this data for reporting, in which
> case you can store the critical values in both encrypted and plain text
> form. A simple procedure in the application will find all the records
> that
> were tampered with. Also, when it comes time to pay out this money, make
> sure it is the encrypted value that is being used.
>
> "Pieter" <pietercoucke@.hotmail.com> wrote in message
> news:uAtUqlG4FHA.4076@.TK2MSFTNGP15.phx.gbl...
>> Hi,
>> I'm sorry for the probable cross-posting, but I need a solution that can
> be
>> either via Sql Server (preferable) or via .NET...
>> In one of our commpany's we're having a huge problem with a crazy
> situation:
>> A lot of people which are working for the company are also supplier to
>> the
>> company. So they are manage their own delivery's, and their own
> payments...
>> They used to have applications in Access, but if they want to (and they
> do,
>> hehe) thay can change their own payments they earn. Some of them have
>> even
>> administration rights, and full access to the Sql Server Database (it
>> personell etc).
>> So what I need is:
>> - make the application in VB.NET, without giving them the source
>> offcourse
>> - a protection so that, even with administrator rights, they can't change
>> their own data. I was thinking about something like a
>> checksum-algeorithm.
>> Does anybody has experience with this? how should I implement an
>> checksum-algorithm? What other options do I have?
>> Any help will be really appreciated!!
>> Thanks a lot in advance,
>>
>> Pieter
>>
>|||It looks like you have a few technical solutions to work with, but I want to
stress one point...
Very often the proper solution to a problem like this is not technology, but
policy. This sounds like one of those cases.
While you can make it difficult for anyone to manipulate the data, you
should not have to go to these extents. With a system of this type, that
controls payments, very few should have access that allows them to change
data. Suppliers should not be managing the database that pays them, and
anyone who changes the data in anyway should be terminated on the spot.
Companies often look for technical solutions for bad business practices.
"Pieter" <pietercoucke@.hotmail.com> wrote in message
news:uAtUqlG4FHA.4076@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I'm sorry for the probable cross-posting, but I need a solution that can
be
> either via Sql Server (preferable) or via .NET...
> In one of our commpany's we're having a huge problem with a crazy
situation:
> A lot of people which are working for the company are also supplier to the
> company. So they are manage their own delivery's, and their own
payments...
> They used to have applications in Access, but if they want to (and they
do,
> hehe) thay can change their own payments they earn. Some of them have even
> administration rights, and full access to the Sql Server Database (it
> personell etc).
> So what I need is:
> - make the application in VB.NET, without giving them the source offcourse
> - a protection so that, even with administrator rights, they can't change
> their own data. I was thinking about something like a checksum-algeorithm.
> Does anybody has experience with this? how should I implement an
> checksum-algorithm? What other options do I have?
> Any help will be really appreciated!!
> Thanks a lot in advance,
>
> Pieter
>

Missing xp_sqlagent_notify

I'm trying to have an alert fire when it receives an error via the RAISERROR
call. I have it working in SQL 2000, but can't get it to work for SQL 2005.
When I look at the history of the alert, it says that it has not been raised
yet.
When I look at the log from SQLServerAgent, I see that it is missing the
stored procedure xp_sqlagent_notify. Where would I find this missing
procedure? I see that I have an sp_sqlagent_notify -- should I just rename o
r
copy it?
SteveIt sounds like it may not be logging if the history of the
alert indicates it hasn't been raised. Did you create the
message using @.with_log = true or are you raising the error
using with log?
Try testing by doing a raiserror with log. Or you can use
sp_altermessage YourMessageNumber, 'with log', 'true'
to log the message.
-Sue
On Fri, 19 May 2006 11:04:01 -0700, SteveIrwin
<SteveIrwin@.discussions.microsoft.com> wrote:

>I'm trying to have an alert fire when it receives an error via the RAISERRO
R
>call. I have it working in SQL 2000, but can't get it to work for SQL 2005.
>When I look at the history of the alert, it says that it has not been raise
d
>yet.
>When I look at the log from SQLServerAgent, I see that it is missing the
>stored procedure xp_sqlagent_notify. Where would I find this missing
>procedure? I see that I have an sp_sqlagent_notify -- should I just rename
or
>copy it?
>Steve|||That worked, Sue. Thanks!
Steve
"Sue Hoegemeier" wrote:

> It sounds like it may not be logging if the history of the
> alert indicates it hasn't been raised. Did you create the
> message using @.with_log = true or are you raising the error
> using with log?
> Try testing by doing a raiserror with log. Or you can use
> sp_altermessage YourMessageNumber, 'with log', 'true'
> to log the message.
> -Sue
> On Fri, 19 May 2006 11:04:01 -0700, SteveIrwin
> <SteveIrwin@.discussions.microsoft.com> wrote:
>
>

Missing xp_sqlagent_notify

I'm trying to have an alert fire when it receives an error via the RAISERROR
call. I have it working in SQL 2000, but can't get it to work for SQL 2005.
When I look at the history of the alert, it says that it has not been raised
yet.
When I look at the log from SQLServerAgent, I see that it is missing the
stored procedure xp_sqlagent_notify. Where would I find this missing
procedure? I see that I have an sp_sqlagent_notify -- should I just rename or
copy it?
SteveIt sounds like it may not be logging if the history of the
alert indicates it hasn't been raised. Did you create the
message using @.with_log = true or are you raising the error
using with log?
Try testing by doing a raiserror with log. Or you can use
sp_altermessage YourMessageNumber, 'with log', 'true'
to log the message.
-Sue
On Fri, 19 May 2006 11:04:01 -0700, SteveIrwin
<SteveIrwin@.discussions.microsoft.com> wrote:
>I'm trying to have an alert fire when it receives an error via the RAISERROR
>call. I have it working in SQL 2000, but can't get it to work for SQL 2005.
>When I look at the history of the alert, it says that it has not been raised
>yet.
>When I look at the log from SQLServerAgent, I see that it is missing the
>stored procedure xp_sqlagent_notify. Where would I find this missing
>procedure? I see that I have an sp_sqlagent_notify -- should I just rename or
>copy it?
>Steve|||That worked, Sue. Thanks!
Steve
"Sue Hoegemeier" wrote:
> It sounds like it may not be logging if the history of the
> alert indicates it hasn't been raised. Did you create the
> message using @.with_log = true or are you raising the error
> using with log?
> Try testing by doing a raiserror with log. Or you can use
> sp_altermessage YourMessageNumber, 'with log', 'true'
> to log the message.
> -Sue
> On Fri, 19 May 2006 11:04:01 -0700, SteveIrwin
> <SteveIrwin@.discussions.microsoft.com> wrote:
> >I'm trying to have an alert fire when it receives an error via the RAISERROR
> >call. I have it working in SQL 2000, but can't get it to work for SQL 2005.
> >When I look at the history of the alert, it says that it has not been raised
> >yet.
> >
> >When I look at the log from SQLServerAgent, I see that it is missing the
> >stored procedure xp_sqlagent_notify. Where would I find this missing
> >procedure? I see that I have an sp_sqlagent_notify -- should I just rename or
> >copy it?
> >
> >Steve
>

Monday, March 12, 2012

Missing Text in Notifications via SMTP

Hello,

We are seeing portions of text missing from our notification emails using NS 2.0 and the built in SMTP delivery protocol.

The notification looks fine in the MS notifications table -- no missing text in the field that becomes the email body, so it may be a bug in the xsl transform code where the text disappears. Our xslt is very simple and it has been tested. The field is plain text. We run perhaps thousands of "short" notifications through the system each day with no problems.

I can work around the problem by reducing the size of the message from it's original size of 2000 characters to 1600. Nothing in the text looks suspect.

Suggestions?

Thanks,

Marcus

We have not seen this - you might try replacing your XSLT with Noop.xslt that comes with some of our samples. This will cause the XSLT not to do anything. This will help you isolate whether the XSLT is actually the problem. The other thing to try is change the delivery protocol to file - this will let you see the full output that is going out to the delivery provider. If you use both these techniques together you'll see where the trunction is occurring. The last possibility is that the event field is too short for the content and truncation is occuring on insert.

-Lukasz


This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Thanks Lukasz,

Unfortunately, we're not seeing a simple truncation, but rather a portion of the text within the message is missing, text that is not at the end of the notification.

The text is not missing in the notifications table, so it's in the XSLT step or after.

Marcus

|||

Can you post sample text and the part of the XSLT that is supposed to be formatting the text?

-Lukasz


This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Here is the incoming text (I've added quotes):

"The OFCM, in conjunction with its Committee for Space Weather, is now supporting a decadal review of the NSWP to quantify and document the progress toward meeting program goals. A committee has been formed to carry out the assessment (Chair: Louis J. Lanzerotti, New Jersey Institute of Technology; Daniel N. Baker, University of Colorado; Tammy E. Jernigan, Lawrence Livermore National Laboratory; Delores J. Knipp, U.S. Air Force Academy; Ray A. Williamson, George Washington University; S. Pete Worden, University of Arizona; Robert J. Rizza, Executive Secretary, Office of the Federal Coordinator for Meteorology). The Committee is gathering input from the broad space weather research and applications communities in the United States, including operations and users and will perform a comprehensive review of the program. This can best be achieved with very strong community participation. Therefore, all those interested in the NSWP are encouraged to complete the questionnaire (please comment on successes, shortfalls, and possible future directions) at http://www.nswp.gov/nswp_userinput.php.

Thank you,"

Here is the generated email message (I've added the quotes again). Note the missing text from the last sentence of the paragraph, before the "thank you":

"The OFCM, in conjunction with its Committee for Space Weather, is now supporting a decadal review of the NSWP to quantify and document the progress toward meeting program goals. A committee has been formed to carry out the assessment (Chair: Louis J. Lanzerotti, New Jersey Institute of Technology; Daniel N. Baker, University of Colorado; Tammy E. Jernigan, Lawrence Livermore National Laboratory; Delores J. Knipp, U.S. Air Force Academy; Ray A. Williamson, George Washington University; S. Pete Worden, University of Arizona; Robert J. Rizza, Executive Secretary, Office of the Federal Coordinator for Meteorology). The Committee is gathering input from the broad space weather research and applications communities in the United States, including operations and users and will perform a comprehensive review of the program. This can best be achieved with very strong community participation. Therefore, all those interested in the NSWP are encouraged to complete the questionnaire (please c Thank you,

Thank you for using the SEC Product Subscription Service. If you would like to remove a product subscription or update the personal information in your account, go to: https://pss.sec.noaa.gov. For problems, contact: mailto:secproducts@.noaa.gov."

Here is the the Application.xslt:

<?xml version="1.0" encoding="UTF-8" ?>

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:output method="text"/>

<xsl:template match="notifications">

<xsl:apply-templates/>

</xsl:template>

<xsl:template match="notification">

<xsl:value-of select="message"/>

Thank you for using the SEC Product Subscription Service. If you would like to remove a product subscription or update the personal information in your account, go to: https://pss.sec.noaa.gov. For problems, contact: mailto:secproducts@.noaa.gov.

</xsl:template>

</xsl:stylesheet>

|||

Marcus -

I've never seen the problem you're describing here.

How did you capture this text? Did you copy it out of an email? Or from a file?

Have you tried sending the notifications through noop.xslt as Lukasz suggested?

If 200 notifications go out with 2000 characters each, do all have the same text missing?


--
Joe Webb
SQL Server MVP
http://www.sqlns.com


~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/tg/detail/-/0972688811

I support PASS, the Professional Association for SQL Server.
(www.sqlpass.org)

|||

Joe -

This text was copied directly from an email notification.

I have not tried noop.xslt yet, as it would require setting up a test string -- I cannot "play" with the production string. :)

All notifications with this text exhibit the problem. And again, the text is fine in the MS notifications table.

Sounds like I need to set up a test service - ugh.

Thanks,

Marcus

|||

CLUES!

I have tried using the noop.xslt and the text is still missing.

But, if I switch to the File protocol it works! No missing text.

So perhaps the bug is in the SMTP delivery protocol.

Thanks,

Marcus

|||

Lukasz Pawlowski, are you there? I was hoping for a response to this, as you asked for additional information.

Thanks,

Marcus

Missing Text in Notifications via SMTP

Hello,

We are seeing portions of text missing from our notification emails using NS 2.0 and the built in SMTP delivery protocol.

The notification looks fine in the MS notifications table -- no missing text in the field that becomes the email body, so it may be a bug in the xsl transform code where the text disappears. Our xslt is very simple and it has been tested. The field is plain text. We run perhaps thousands of "short" notifications through the system each day with no problems.

I can work around the problem by reducing the size of the message from it's original size of 2000 characters to 1600. Nothing in the text looks suspect.

Suggestions?

Thanks,

Marcus

We have not seen this - you might try replacing your XSLT with Noop.xslt that comes with some of our samples. This will cause the XSLT not to do anything. This will help you isolate whether the XSLT is actually the problem. The other thing to try is change the delivery protocol to file - this will let you see the full output that is going out to the delivery provider. If you use both these techniques together you'll see where the trunction is occurring. The last possibility is that the event field is too short for the content and truncation is occuring on insert.

-Lukasz


This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Thanks Lukasz,

Unfortunately, we're not seeing a simple truncation, but rather a portion of the text within the message is missing, text that is not at the end of the notification.

The text is not missing in the notifications table, so it's in the XSLT step or after.

Marcus

|||

Can you post sample text and the part of the XSLT that is supposed to be formatting the text?

-Lukasz


This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Here is the incoming text (I've added quotes):

"The OFCM, in conjunction with its Committee for Space Weather, is now supporting a decadal review of the NSWP to quantify and document the progress toward meeting program goals. A committee has been formed to carry out the assessment (Chair: Louis J. Lanzerotti, New Jersey Institute of Technology; Daniel N. Baker, University of Colorado; Tammy E. Jernigan, Lawrence Livermore National Laboratory; Delores J. Knipp, U.S. Air Force Academy; Ray A. Williamson, George Washington University; S. Pete Worden, University of Arizona; Robert J. Rizza, Executive Secretary, Office of the Federal Coordinator for Meteorology). The Committee is gathering input from the broad space weather research and applications communities in the United States, including operations and users and will perform a comprehensive review of the program. This can best be achieved with very strong community participation. Therefore, all those interested in the NSWP are encouraged to complete the questionnaire (please comment on successes, shortfalls, and possible future directions) at http://www.nswp.gov/nswp_userinput.php.

Thank you,"

Here is the generated email message (I've added the quotes again). Note the missing text from the last sentence of the paragraph, before the "thank you":

"The OFCM, in conjunction with its Committee for Space Weather, is now supporting a decadal review of the NSWP to quantify and document the progress toward meeting program goals. A committee has been formed to carry out the assessment (Chair: Louis J. Lanzerotti, New Jersey Institute of Technology; Daniel N. Baker, University of Colorado; Tammy E. Jernigan, Lawrence Livermore National Laboratory; Delores J. Knipp, U.S. Air Force Academy; Ray A. Williamson, George Washington University; S. Pete Worden, University of Arizona; Robert J. Rizza, Executive Secretary, Office of the Federal Coordinator for Meteorology). The Committee is gathering input from the broad space weather research and applications communities in the United States, including operations and users and will perform a comprehensive review of the program. This can best be achieved with very strong community participation. Therefore, all those interested in the NSWP are encouraged to complete the questionnaire (please c Thank you,

Thank you for using the SEC Product Subscription Service. If you would like to remove a product subscription or update the personal information in your account, go to: https://pss.sec.noaa.gov. For problems, contact: mailto:secproducts@.noaa.gov."

Here is the the Application.xslt:

<?xml version="1.0" encoding="UTF-8" ?>

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:output method="text"/>

<xsl:template match="notifications">

<xsl:apply-templates/>

</xsl:template>

<xsl:template match="notification">

<xsl:value-of select="message"/>

Thank you for using the SEC Product Subscription Service. If you would like to remove a product subscription or update the personal information in your account, go to: https://pss.sec.noaa.gov. For problems, contact: mailto:secproducts@.noaa.gov.

</xsl:template>

</xsl:stylesheet>

|||

Marcus -

I've never seen the problem you're describing here.

How did you capture this text? Did you copy it out of an email? Or from a file?

Have you tried sending the notifications through noop.xslt as Lukasz suggested?

If 200 notifications go out with 2000 characters each, do all have the same text missing?


--
Joe Webb
SQL Server MVP
http://www.sqlns.com


~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/tg/detail/-/0972688811

I support PASS, the Professional Association for SQL Server.
(www.sqlpass.org)

|||

Joe -

This text was copied directly from an email notification.

I have not tried noop.xslt yet, as it would require setting up a test string -- I cannot "play" with the production string. :)

All notifications with this text exhibit the problem. And again, the text is fine in the MS notifications table.

Sounds like I need to set up a test service - ugh.

Thanks,

Marcus

|||

CLUES!

I have tried using the noop.xslt and the text is still missing.

But, if I switch to the File protocol it works! No missing text.

So perhaps the bug is in the SMTP delivery protocol.

Thanks,

Marcus

|||

Lukasz Pawlowski, are you there? I was hoping for a response to this, as you asked for additional information.

Thanks,

Marcus

Missing Text in Notifications via SMTP

Hello,

We are seeing portions of text missing from our notification emails using NS 2.0 and the built in SMTP delivery protocol.

The notification looks fine in the MS notifications table -- no missing text in the field that becomes the email body, so it may be a bug in the xsl transform code where the text disappears. Our xslt is very simple and it has been tested. The field is plain text. We run perhaps thousands of "short" notifications through the system each day with no problems.

I can work around the problem by reducing the size of the message from it's original size of 2000 characters to 1600. Nothing in the text looks suspect.

Suggestions?

Thanks,

Marcus

We have not seen this - you might try replacing your XSLT with Noop.xslt that comes with some of our samples. This will cause the XSLT not to do anything. This will help you isolate whether the XSLT is actually the problem. The other thing to try is change the delivery protocol to file - this will let you see the full output that is going out to the delivery provider. If you use both these techniques together you'll see where the trunction is occurring. The last possibility is that the event field is too short for the content and truncation is occuring on insert.

-Lukasz


This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Thanks Lukasz,

Unfortunately, we're not seeing a simple truncation, but rather a portion of the text within the message is missing, text that is not at the end of the notification.

The text is not missing in the notifications table, so it's in the XSLT step or after.

Marcus

|||

Can you post sample text and the part of the XSLT that is supposed to be formatting the text?

-Lukasz


This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Here is the incoming text (I've added quotes):

"The OFCM, in conjunction with its Committee for Space Weather, is now supporting a decadal review of the NSWP to quantify and document the progress toward meeting program goals. A committee has been formed to carry out the assessment (Chair: Louis J. Lanzerotti, New Jersey Institute of Technology; Daniel N. Baker, University of Colorado; Tammy E. Jernigan, Lawrence Livermore National Laboratory; Delores J. Knipp, U.S. Air Force Academy; Ray A. Williamson, George Washington University; S. Pete Worden, University of Arizona; Robert J. Rizza, Executive Secretary, Office of the Federal Coordinator for Meteorology). The Committee is gathering input from the broad space weather research and applications communities in the United States, including operations and users and will perform a comprehensive review of the program. This can best be achieved with very strong community participation. Therefore, all those interested in the NSWP are encouraged to complete the questionnaire (please comment on successes, shortfalls, and possible future directions) at http://www.nswp.gov/nswp_userinput.php.

Thank you,"

Here is the generated email message (I've added the quotes again). Note the missing text from the last sentence of the paragraph, before the "thank you":

"The OFCM, in conjunction with its Committee for Space Weather, is now supporting a decadal review of the NSWP to quantify and document the progress toward meeting program goals. A committee has been formed to carry out the assessment (Chair: Louis J. Lanzerotti, New Jersey Institute of Technology; Daniel N. Baker, University of Colorado; Tammy E. Jernigan, Lawrence Livermore National Laboratory; Delores J. Knipp, U.S. Air Force Academy; Ray A. Williamson, George Washington University; S. Pete Worden, University of Arizona; Robert J. Rizza, Executive Secretary, Office of the Federal Coordinator for Meteorology). The Committee is gathering input from the broad space weather research and applications communities in the United States, including operations and users and will perform a comprehensive review of the program. This can best be achieved with very strong community participation. Therefore, all those interested in the NSWP are encouraged to complete the questionnaire (please c Thank you,

Thank you for using the SEC Product Subscription Service. If you would like to remove a product subscription or update the personal information in your account, go to: https://pss.sec.noaa.gov. For problems, contact: mailto:secproducts@.noaa.gov."

Here is the the Application.xslt:

<?xml version="1.0" encoding="UTF-8" ?>

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:output method="text"/>

<xsl:template match="notifications">

<xsl:apply-templates/>

</xsl:template>

<xsl:template match="notification">

<xsl:value-of select="message"/>

Thank you for using the SEC Product Subscription Service. If you would like to remove a product subscription or update the personal information in your account, go to: https://pss.sec.noaa.gov. For problems, contact: mailto:secproducts@.noaa.gov.

</xsl:template>

</xsl:stylesheet>

|||

Marcus -

I've never seen the problem you're describing here.

How did you capture this text? Did you copy it out of an email? Or from a file?

Have you tried sending the notifications through noop.xslt as Lukasz suggested?

If 200 notifications go out with 2000 characters each, do all have the same text missing?


--
Joe Webb
SQL Server MVP
http://www.sqlns.com


~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/tg/detail/-/0972688811

I support PASS, the Professional Association for SQL Server.
(www.sqlpass.org)

|||

Joe -

This text was copied directly from an email notification.

I have not tried noop.xslt yet, as it would require setting up a test string -- I cannot "play" with the production string. :)

All notifications with this text exhibit the problem. And again, the text is fine in the MS notifications table.

Sounds like I need to set up a test service - ugh.

Thanks,

Marcus

|||

CLUES!

I have tried using the noop.xslt and the text is still missing.

But, if I switch to the File protocol it works! No missing text.

So perhaps the bug is in the SMTP delivery protocol.

Thanks,

Marcus

|||

Lukasz Pawlowski, are you there? I was hoping for a response to this, as you asked for additional information.

Thanks,

Marcus

Missing Text in Notifications via SMTP

Hello,

We are seeing portions of text missing from our notification emails using NS 2.0 and the built in SMTP delivery protocol.

The notification looks fine in the MS notifications table -- no missing text in the field that becomes the email body, so it may be a bug in the xsl transform code where the text disappears. Our xslt is very simple and it has been tested. The field is plain text. We run perhaps thousands of "short" notifications through the system each day with no problems.

I can work around the problem by reducing the size of the message from it's original size of 2000 characters to 1600. Nothing in the text looks suspect.

Suggestions?

Thanks,

Marcus

We have not seen this - you might try replacing your XSLT with Noop.xslt that comes with some of our samples. This will cause the XSLT not to do anything. This will help you isolate whether the XSLT is actually the problem. The other thing to try is change the delivery protocol to file - this will let you see the full output that is going out to the delivery provider. If you use both these techniques together you'll see where the trunction is occurring. The last possibility is that the event field is too short for the content and truncation is occuring on insert.

-Lukasz


This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Thanks Lukasz,

Unfortunately, we're not seeing a simple truncation, but rather a portion of the text within the message is missing, text that is not at the end of the notification.

The text is not missing in the notifications table, so it's in the XSLT step or after.

Marcus

|||

Can you post sample text and the part of the XSLT that is supposed to be formatting the text?

-Lukasz


This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Here is the incoming text (I've added quotes):

"The OFCM, in conjunction with its Committee for Space Weather, is now supporting a decadal review of the NSWP to quantify and document the progress toward meeting program goals. A committee has been formed to carry out the assessment (Chair: Louis J. Lanzerotti, New Jersey Institute of Technology; Daniel N. Baker, University of Colorado; Tammy E. Jernigan, Lawrence Livermore National Laboratory; Delores J. Knipp, U.S. Air Force Academy; Ray A. Williamson, George Washington University; S. Pete Worden, University of Arizona; Robert J. Rizza, Executive Secretary, Office of the Federal Coordinator for Meteorology). The Committee is gathering input from the broad space weather research and applications communities in the United States, including operations and users and will perform a comprehensive review of the program. This can best be achieved with very strong community participation. Therefore, all those interested in the NSWP are encouraged to complete the questionnaire (please comment on successes, shortfalls, and possible future directions) at http://www.nswp.gov/nswp_userinput.php.

Thank you,"

Here is the generated email message (I've added the quotes again). Note the missing text from the last sentence of the paragraph, before the "thank you":

"The OFCM, in conjunction with its Committee for Space Weather, is now supporting a decadal review of the NSWP to quantify and document the progress toward meeting program goals. A committee has been formed to carry out the assessment (Chair: Louis J. Lanzerotti, New Jersey Institute of Technology; Daniel N. Baker, University of Colorado; Tammy E. Jernigan, Lawrence Livermore National Laboratory; Delores J. Knipp, U.S. Air Force Academy; Ray A. Williamson, George Washington University; S. Pete Worden, University of Arizona; Robert J. Rizza, Executive Secretary, Office of the Federal Coordinator for Meteorology). The Committee is gathering input from the broad space weather research and applications communities in the United States, including operations and users and will perform a comprehensive review of the program. This can best be achieved with very strong community participation. Therefore, all those interested in the NSWP are encouraged to complete the questionnaire (please c Thank you,

Thank you for using the SEC Product Subscription Service. If you would like to remove a product subscription or update the personal information in your account, go to: https://pss.sec.noaa.gov. For problems, contact: mailto:secproducts@.noaa.gov."

Here is the the Application.xslt:

<?xml version="1.0" encoding="UTF-8" ?>

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:output method="text"/>

<xsl:template match="notifications">

<xsl:apply-templates/>

</xsl:template>

<xsl:template match="notification">

<xsl:value-of select="message"/>

Thank you for using the SEC Product Subscription Service. If you would like to remove a product subscription or update the personal information in your account, go to: https://pss.sec.noaa.gov. For problems, contact: mailto:secproducts@.noaa.gov.

</xsl:template>

</xsl:stylesheet>

|||

Marcus -

I've never seen the problem you're describing here.

How did you capture this text? Did you copy it out of an email? Or from a file?

Have you tried sending the notifications through noop.xslt as Lukasz suggested?

If 200 notifications go out with 2000 characters each, do all have the same text missing?


--
Joe Webb
SQL Server MVP
http://www.sqlns.com


~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/tg/detail/-/0972688811

I support PASS, the Professional Association for SQL Server.
(www.sqlpass.org)

|||

Joe -

This text was copied directly from an email notification.

I have not tried noop.xslt yet, as it would require setting up a test string -- I cannot "play" with the production string. :)

All notifications with this text exhibit the problem. And again, the text is fine in the MS notifications table.

Sounds like I need to set up a test service - ugh.

Thanks,

Marcus

|||

CLUES!

I have tried using the noop.xslt and the text is still missing.

But, if I switch to the File protocol it works! No missing text.

So perhaps the bug is in the SMTP delivery protocol.

Thanks,

Marcus

|||

Lukasz Pawlowski, are you there? I was hoping for a response to this, as you asked for additional information.

Thanks,

Marcus

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

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

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