missing object warning when adding column to table with sp_repladd

Added a column to one table with sp_repladdcolumn in publisher/distributor.
This warning comes from every publication this table belongs to:
Cannot add rows to sysdepends for the current stored procedure because it
depends on the missing object 'sp_sel_C245F32C38A611D525494BFC6FD140A7_pal'.
The stored procedure will still be created.
In use: Merge replication and static filtered publications referencing this
table.
Is it safe to continue and trust that everything works after this? What is
this _pal ending sp? What happens if it does not exist and what should we do
next?
Thanks - M
Its fine as long as this proc is present in all subscribers.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Untamo" <Untamo@.discussions.microsoft.com> wrote in message
news:0BC7CA5F-872E-4AB9-B1EB-F0ADCDD435B6@.microsoft.com...
> Added a column to one table with sp_repladdcolumn in
publisher/distributor.
> This warning comes from every publication this table belongs to:
> Cannot add rows to sysdepends for the current stored procedure because it
> depends on the missing object
'sp_sel_C245F32C38A611D525494BFC6FD140A7_pal'.
> The stored procedure will still be created.
> In use: Merge replication and static filtered publications referencing
this
> table.
> Is it safe to continue and trust that everything works after this? What is
> this _pal ending sp? What happens if it does not exist and what should we
do
> next?
> Thanks - M

missing notifications

Hello,

We are building a simple distributed application around the service broker where each queue is serviced from a windows service. We have a windows service running constantly as a "listener" on an event queue for each of what we call "request" queues. When a message is received by the request, the listener service instantiates a reader to process the request.

As we are in development, things get out of sync at times and the event queues sometimes stop getting the event notification message. I can correct this usually emptying the queues by receiving all messages from the "request" and "event" queues but sometimes it becomes necessary to drop and recreate the broker objects. Can someone tell me what are the fail points event_notification and what corrective actions are needed at these points?

For instance, if I see that a queue has a state of "Notified" in dm_broker_queue_monitors, I just do a receive to drain the appropriate queue. I have been unable to determine what state the service broker is in when I have to delete and recreate the event notification so I don't know what is causing it and how to programmatically determine when it is necessary to take this action. We would of course prefer not to get to this state but since it can happen during development, I would guess this can happen in production so I need to develop the recovery logic to detect and correct for this condition.

Jim

If you consume the notification message from the 'events' queue but you fail to launch the reader child thread/process to consume the messages from the the 'request' queue, it will end up exactly in the situation you described. Once a notification is sent, the queue monitor goes into 'Notified' state until a RECEIVE occurs on the queue that triggered the event. It does NOT continue to send notifications, otherwhise it could end up sending notifications for ever to a service that has stopped listening.

The most likely cause of the problem is that you are failing to launch the child thread/process when you receive the notification. See my reply on the previous thread ('5 times a charm' http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=202594&SiteID=1) for a sugestion on how to process messages when the processing involves some inherently unrelaible work (like launching a thread or a process).

Also, make sure to check the 'External Activator' sample at http://www.gotdotnet.com/codegallery/codegallery.aspx?id=9f7ae2af-31aa-44dd-9ee8-6b6b6d3d6319 , it does exactly what you want.

HTH,
~ Remus

|||

Hi Remus,

No, there is something else going on. When I get this condition, I manually do a receive in query analyzer until I clean out the "request" queue. When I have emptied the queue completely and then do a new send, the messages show up in the request queue but I get no notification event. If I drop and recreate the event notification, I will then see the event message on the next request message.

Jim

|||

Does any error show up in the notifications error queue [EventNotificationErrorsQueue]?

What is the state of the notification (it will be in sys.event_notifications) and od the dialog that carries the notification (it will be in sys.conversation_endpoints)?

HTH,
~ Remus

|||

Hi Remus,

I don't see a state in sys.event_notifications but all of the event notifications I created are listed there (6). As for the sys.conversation_endpoints, I do see some conversations in disconnected state but when we have our problem, I have a stored proc that goes through sys.conversation_endpoints and ends all these conversations with cleanup. Even with doing that, and all queues apparently empty I have had to recreate the event notificaiton.

I don't have a scenario to recreate this reliably so I just have to wait until we muck things up enough for it to happen and I am looking for what things I need to check. FYI, we are really using this as a monolog, the use pattern is begin dialog, send, end dialog. I have a periodic task that looks for missing task completions and just resends the request message if a task fails to complete.

Jim

|||

You need to investigate what are thos disconnected conversations. Are they initiator or targets? What 'far_service' do they have? What near service (service_id)? Are they system (is_system)? The state is 'DO' or 'DI'?

If you end the dialog that carries the notifications you are basically canceling the notification. Make sure you don't end the dialogs on the 'events' queue, that would cause the problem of having to drop and recreate the notification.

You definitely shouldn't need to have a periodic task that resends the message, that's the whole purpose of Service Broker, to avoit such problems.

One problem with the begin/send/end pattern is that you're missing any info if the message was delivered or not. In general, a better ppattern is to do begin/send and do the end on the initiator side as a response to the end from the target side.

HTH,
~ Remus

|||

Hi Remus,

No, I am not ending the dialog on the events queue, at least not directly or intentionally. Are there errors that would cause the dialog to end?

Yes, I realize that I could have used the service broker to monitor for task completion by keeping a conversation active and responding with a completion message. However, I would still need some other task responsible for deciding the task wasn't going to complete (service died, someone shut down that server, network unplugged etc) even though the message was successfully received, and generating a new request. I'm not looking for a message failure but a task failure in a service on any system connected to a Service Broker queue.

I'm relatively sure that the disconnected conversations are due us developers debugging our services and not allowing processing to complete. While debugging we are doing sends out of query analyzer manually, we are stopping and starting our code that reads the queues etc. The more we are learning, the more stable we are getting things.

So where can I find a list of things to work through when notifications stop coming? For example, I saw the state of one of my queues was NOTIFIED a couple of days ago and nothing I could do would get it back to INACTIVE. I tried clearing all conversations, made sure all of my queues were empty etc but I wound up having to drop and recreate the event notification.

Jim

PS - Aside from these little problems we are creating for ourselves, SSB is awesome!!

|||

Hi Jim,

I'm really glad you like SSB! I'm a big fan myself ;-)

Is not much I can do to help you in this case, w/o a repro case or a more precise description of the problem. Rushi might have some aditional ideas, he knows the activation machinery and queue monitors better than I do.

For your development environment, there is one big switch button that you can use:

ALTER DATABASE [dbname] SET DISABLE_BROKER;
ALTER DATABASE [dbname] SET ENABLE_BROKER;

This will basically reinitialize everything broker related in the database, including queue monitors and such. Of course, this is not a production solution, as it requires an exclusive lock on the database (no users connected).

As to investigate what's happening, try looking at these things for investigation:

- is the notification dialog still active? look in sys.event_notifications and in sys.conversation_endpoints
- is the notification message lingering on the sender's database? look in sys.transmission_queue
- check state of the notification dialog on the target side (the conversation_id is the same on both sides). Compare the send_sequence_number from the initiator with the receive_sequence_number from the target
- make sure that if the monitoring service has consumed a notification, it did launched a thread or a process that RECEIVED from that queue.

HTH,
~ Remus

|||

Jim Stallings wrote:

No, I am not ending the dialog on the events queue, at least not directly or intentionally. Are there errors that would cause the dialog to end?

I think this are all the conditions that could end the notification dialog from the target side:
- explicit END DIALOG, or END DIALOG .. WITH ERROR
- ALTER DATABASE [...] WITH ERROR_BROKER_CONVERSATIONS
- Sender service is denied SEND permission on the target service. Can only happen on the first message (so you won't get any notification at all)
- restore of target database to a point back in time when the target did not yet exist. At the next notification message sent the target will reply with an error that will end the dialog.
- END DIALOG ... WITH CLEANUP. It will wipe out the target w/o actually notfying the initiator, so the initiator will be errored out only next time it sends a notification message, same as above.

ALTER DATABASE [...] SET NEW_BROKER is a special case, it will wipe out the target w/o notifying the initiator, but since the broker is completely erased, the inittiator can never find again the old broker to deliver the message. If the initiator happens to be on the same database as the target then the initiator is wiped out as well, so there is no problem.

Just for the record, there are two more conditions that could end the dialog, but they cannot happen on notifications:
- sending an incorrectly formatted XML message, this cannot happen on notifications since notifications message bodies are outside your control
- dialog timeout, but notifications dialog have the maximum timeout, so it cannot happen until sometime in 2074.

HTH,
~ Remus

|||

Ending the dialog at the notification service: You could check the [EventNotificationsErrorQueue] to find error messages in case the dialog was closed due to some error. Also, you said that you have a stored proc that deliberately ends dialogs at the notification service; are you sure this is not being called somehow?

Programming pattern: Could you explain what the application is and why you are choosing the fire-and-forget (begin/send/end) pattern? I did not understand what you mean by 'task wasn't going to complete due to server shutdown, network unplugged, etc'. The reliable transport of service broker guarantees that messages will be delivered even if you run into such problems. The standard pattern we recommend is initiator begins dialog, sends the request and goes around with its own business (maybe accepting next request from user and doing begin/send). Sometime in the future, the message is delivered and a queue reader receives the message, processes it and sends back a response. If the intiator doesn't really need any excess information, the response could be as simple as ending the dialog. A background thread or a periodic program on the initiator side receives the end dialog response and ends the dialog on its side.

Task failures: If the request cannot be processed immediately, then we come across some interesting problems. A typical example is that the request generates an HttpRequest which could fail. You could rollback the transaction, but then you are going to get the message back almost immediately the next time you do a RECEIVE. If you rollback 5 times, your queue is going to get disabled. A common solution is that if the request cannot be processed immediately, you log it to a table and begin a conversation timer to retry the request after some X units of time.

My queue is stuck in NOTIFIED state: The NOTIFIED state indicates that we delivered that notification and now it's your job to perform a RECEIVE on the queue. A correctly written app will never orphan the queue in NOTIFIED state but always try to schedule something that will eventually come around and do that RECEIVE we've been waiting for. The RECEIVE moves the queue monitor state machine to the RECEIVES_OCCURING state. When the queue is drained and the last queue reader releases the conversation group lock (i.e. commits/rollsback a transaction doing the receive), we notice that there is nothing in the queue and take the queue monitor back to INACTIVE state. There is no way to reset the queue monitor from NOTIFIED to INACTIVE state. You can only reset from NOTIFIED to RECEIVES_OCCURING state. It's only when the queue is drained that we will go back to INACTIVE state.

INACTIVE is not EMPTY: INACTIVE does not really mean queue is empty. We could also go into INACTIVE state when all messages in the queue belong to conversation groups that are currently locked by some transaction or the other. In such a scenario, it doesn't make sense to activate a new queue reader since it will be unable to RECEIVE any messages. So INACTIVE is really the union of EMPTY and NO_UNLOCKED_MESSAGES state.

The external activator that we've released is a good example of how to write the kind of app you are trying to build. You should certainly take a look at that. We will be releasing an update to that shortly.

|||

Hi Rushi

This application uses a highly modified version of the ServiceBrokerInterface project from the activator example so yes, I have been through your examples many times including the PDC 2005.

As to what this project is, it is a print queue that gets kicked off by a periodic stored proc that examines a legacy database for new jobs. When a job is found, it is put into an active print table and two windows services receive a message to begin their processing. One does an httpRequest to an outside vendor the other creates a pdf from the data in the print table.

These may or may not be on the same server as other processes but definitely is not on the server running Service Broker. Yes I could have done a rollback/retry as you suggest but regardless, if we have a failure, we have to send this information to some controlling process to decide if it's a one time failure that should be retried, a single request that fails everytime due to bad data and should be logged or retried, whether we should send an alert to the users or sys admin etc, etc.

We also need to send the controlling process a success message when a task completes so that it can begin the next step, send to printer, archive files etc. I just elected to send completion messages directly to the controller process rather than using service broker conversation. Using a conversation, I would have to use activation for each initiator queue which would mean starting another service for each queue as I have built the application as I have my event listener windows service doing receive with WAITFOR so that they can check for shutdown and/or confguration changes between receives. Whether I use service broker conversations to encapsulate task completion or just fire and forget as you describe it, I still wind up with a controlling process listening to some queue.

With a more standard implementation as you suggest, my controller would listen to all task queues, in mine I have a dedicatcontroller queue that all the tasks report completion to. In any case, since I will have services running on multiple systems, I do need a timer thread running in my controller to see if a service has stopped processing a queue and take whatever actions are called for, alerts, redirect to another queue etc. That was it's primary function and I wanted the logic as to how to handle this not tied up in completing a conversation.

Thanks for the explanation on the queue state. When I was seeing this problem, it wasn't that the queue was inactive when I still had messages, it was that it was in Notified state and I couldn't receive from the notification queue. This can result from the developer stepping through the listener code and not completing the transaction but the lock should release either when he stopped and the connection terminated or when the command timed out so I'm at a loss as to what else to look at when this occurs.

Thanks

Jim

|||

There is a well-known race condition that can cause similar symptoms. The race occurs when the following conditions are met:

1) a given queue-reader is configured to a given max (e.g. 1)

2) there are currently max queue-readers running

3) a notification is delivered to the notification queue.

If these conditions occur while the queue reader(s) is still running, but in the process of shutting down it is quite likely that the notification queue will be drained, but the messages in the app queue will not be processed resulting in the app queue being in NOTIFIED state and the notification queue being in the RECEIVING state.

To deal with this the activating process should keep record of notifications received while the max queue readers condition is in effect and kick another off (just in case) when the # of queue readers falls below the max.

This is what the current version of our external activator sample does. (I'm not sure if this one is posted to the web yet or not).

I'm not sure if this condition is what you are seeing or not, but thought it worthy of calling out for both you and others reading this thread anyway.

-Gerald

|||

I believe that I have finally tracked down my problem. When I do an "end conversation with cleanup", my event notification gets dropped. I thought that since I still had an entry in sys.dm_broker_queue_monitors, that the event notification was active. I didn't see anything in the help for "end conversation" that led me to believe that the cleanup option should do this.

Thanks,

Jim

Missing New Data Driven Subscriptions

Hello, I just migrated my reporting services to 2005 from 2000. My reports
have come across fine but I don't see the schedules that were set up for them.
Also, when I try to create a 'New Data-driven subscription' I don't see this
option; I used this feature in 2000. Is there some setting I need to change
somewhere?
ThanksOn Oct 4, 6:59 pm, Don <D...@.discussions.microsoft.com> wrote:
> Hello, I just migrated my reporting services to 2005 from 2000. My reports
> have come across fine but I don't see the schedules that were set up for them.
> Also, when I try to create a 'New Data-driven subscription' I don't see this
> option; I used this feature in 2000. Is there some setting I need to change
> somewhere?
> Thanks
If you do not have the SQL Server 2005 Enterprise Edition, this will
be the reason that they are not available (reference:
http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
). Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||So it was a feature in the regular version of 2000 but limited to Enterprise
in 2005
"EMartinez" wrote:
> On Oct 4, 6:59 pm, Don <D...@.discussions.microsoft.com> wrote:
> > Hello, I just migrated my reporting services to 2005 from 2000. My reports
> > have come across fine but I don't see the schedules that were set up for them.
> >
> > Also, when I try to create a 'New Data-driven subscription' I don't see this
> > option; I used this feature in 2000. Is there some setting I need to change
> > somewhere?
> >
> > Thanks
>
> If you do not have the SQL Server 2005 Enterprise Edition, this will
> be the reason that they are not available (reference:
> http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
> ). Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>

Missing namespace ReportingService

The type or namespace name 'ReportingService' could not be found (are you missing a using directive or an assembly reference?)

What is missing ?

Do I need to install something because in the I do get it in intelliscence

i am using this code

ReportingService.ReportingService rs =new ReportingService.ReportingService();

Thanks

Make sure that you have added that as a reference in your reference section in the project.

|||

how do I add a reference to my project|||

Goto Solutions Explorer. This can be found in the View menu (Ctrl + W + S) in visual studio 2005

under the project, you should see a link called Reference

right click the reference and select add reference.

you will see list of component name. Select the neccessary component / assembly and click OK

|||

there are so many which one should I select for ReportingService

Thanks

|||

Microsoft.ReportingServices

missing Microsoft.SqlServer.ManagedDTS

Hi

I am trying to run a SSIS package first time through vb.net console application. As the first step, i was trying to add the reference for Microsoft.SqlServer.ManagedDTS assembly but it was not in the available assemblies list box in visual studio 2003!

Please guide what i am supposed to do to get it, or if there any other alternative.

Thanks in advance

Utsav

In the Add Reference dialog window, click on the browse tab. Then navigate your way to:

C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies

There you will find the Microsoft.SQLServer.ManagedDTS.dll, select it and hit okay...

|||ManagedDTS runs in process. If the console app is running on a machine that does not have SSIS loaded on it you will not be able to reference the dll. The application will also crash at the point that you attempt to access DTS objects. This also causes problems if you are executing packages that contain static paths. Because the package runs on the client machine, the paths will be assumed to be local. My solution to this was to create a web service that executes the packages on the server. Unfortunately, this aproach seems to be a huge chore, as I am having problems passing the credentials to the package app object. If anyone has any experience with this, some guidance would be greatly appreciated.|||See Michael Entin's recent post on this (http://blogs.msdn.com/michen/default.aspx).

missing Microsoft.SqlServer.ManagedDTS

Hi

I am trying to run a SSIS package first time through vb.net console application. As the first step, i was trying to add the reference for Microsoft.SqlServer.ManagedDTS assembly but it was not in the available assemblies list box in visual studio 2003!

Please guide what i am supposed to do to get it, or if there any other alternative.

Thanks in advance

Utsav

In the Add Reference dialog window, click on the browse tab. Then navigate your way to:

C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies

There you will find the Microsoft.SQLServer.ManagedDTS.dll, select it and hit okay...

|||ManagedDTS runs in process. If the console app is running on a machine that does not have SSIS loaded on it you will not be able to reference the dll. The application will also crash at the point that you attempt to access DTS objects. This also causes problems if you are executing packages that contain static paths. Because the package runs on the client machine, the paths will be assumed to be local. My solution to this was to create a web service that executes the packages on the server. Unfortunately, this aproach seems to be a huge chore, as I am having problems passing the credentials to the package app object. If anyone has any experience with this, some guidance would be greatly appreciated.|||See Michael Entin's recent post on this (http://blogs.msdn.com/michen/default.aspx).

Missing Microsoft.ReportViewer.ProcessingObjectModel.dll

How can I get this dll so that I can copy it over to the hosting server?

I am using a third party to host a web application I can not run ReportViewer.exe to get the correct dlls installed.

I have tried to use CMD to copy the file Microsoft.ReportViewer.ProcessingObjectModel to the bin directory. This file does not have the .dll extension and it will not copy.

What else can I do?

Thanks,

enak

follow the following link:

http://drowningintechnicaldebt.com/blogs/dennisbottjer/archive/2006/10/16/Hacking-Report-Viewer-Redistributable.aspx

Your problem will be solved.

Missing MDF files but databases run fine?

About two months ago, we had a problem with a couple SQL 2005 databases. We
got everything working and all has been fine. Today, I noticed that the
databases that we had a problem with have LDF file but no MDF files. I don't
know much about SQL but does that make sense? Is there any way to make the
MDF file at this point?
Thanks!
Arch WillinghamWhat do you mean by 'no MDF files'? You can have a scenario where the drive
that hosts a database file disappears and the database may still function as
usual for a while until it has to access the disk.
Linchi
"Arch Willingham" wrote:
> About two months ago, we had a problem with a couple SQL 2005 databases. We
> got everything working and all has been fine. Today, I noticed that the
> databases that we had a problem with have LDF file but no MDF files. I don't
> know much about SQL but does that make sense? Is there any way to make the
> MDF file at this point?
> Thanks!
> Arch Willingham
>
>|||Never mind....weird thing. I am a holdout thatstill uses the old file
manager (WINFILE.EXE). For whatever reason, teh MDF files are hidden to it
but they are there when you look at the directory with Explorer.
Weird as I can see all other hidden and system files.
Arch
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:CEE64FB0-73F3-483E-A4DF-3EEB750908DC@.microsoft.com...
> What do you mean by 'no MDF files'? You can have a scenario where the
> drive
> that hosts a database file disappears and the database may still function
> as
> usual for a while until it has to access the disk.
> Linchi
> "Arch Willingham" wrote:
>> About two months ago, we had a problem with a couple SQL 2005 databases.
>> We
>> got everything working and all has been fine. Today, I noticed that the
>> databases that we had a problem with have LDF file but no MDF files. I
>> don't
>> know much about SQL but does that make sense? Is there any way to make
>> the
>> MDF file at this point?
>> Thanks!
>> Arch Willingham
>>

Missing log file...

Hi,
OK - I did stupid thing but... ;-)
I put database offline and then I deleted log file.
Then I was surprised that database do not want to
go into on-line status...
I am interested in object of this db not in data.
Is there any hope?
(I know - for me - not, but for this database? ;-)
Radek
Hi,
You might loose the consistency if you delete the LDF file directly.. Since
you are not that bothered bothered about data you could try
1. Try to attach the database using
EXEC sp_attach_single_file_db @.dbname = 'pubs', @.physname =
'D:\MSSQL\data\dbname.mdf'
This will attach the database with MDF file and will create a new LDF file.
This command will fail some times, since you have not detached the database
properly.. in that use the below method
2.
1. Create a new database with the same name and same MDF and LDF files
2. Stop sql server and rename the existing MDF to a new one and copy the
original MDF to this location and delete the LDF files.
3. Start SQL Server
4. Now your database will be marked suspect
5. Update the sysdatabases to update to Emergency mode. This will not use
LOG files in start up
Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO
6. Restart sql server. now the database will be in emergency mode ( You
could access the database)
7. Use enterprise manager to script all your objects
Thanks
Hari
MCDBA
"Radek" <radek@.obecniezwarszawy.pl> wrote in message
news:cfv1kb$4r3$1@.nemesis.news.tpi.pl...
> Hi,
> OK - I did stupid thing but... ;-)
> I put database offline and then I deleted log file.
> Then I was surprised that database do not want to
> go into on-line status...
> I am interested in object of this db not in data.
> Is there any hope?
> (I know - for me - not, but for this database? ;-)
> Radek
>
|||Before you do Hari's 2nd option make sure you copy off your original MDF
first...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Radek" <radek@.obecniezwarszawy.pl> wrote in message
news:cfv1kb$4r3$1@.nemesis.news.tpi.pl...
> Hi,
> OK - I did stupid thing but... ;-)
> I put database offline and then I deleted log file.
> Then I was surprised that database do not want to
> go into on-line status...
> I am interested in object of this db not in data.
> Is there any hope?
> (I know - for me - not, but for this database? ;-)
> Radek
>
|||I agree with Wayne...Thanks for pointing out.
Thanks
Hari
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:OOIrMERhEHA.3964@.TK2MSFTNGP12.phx.gbl...
> Before you do Hari's 2nd option make sure you copy off your original MDF
> first...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Radek" <radek@.obecniezwarszawy.pl> wrote in message
> news:cfv1kb$4r3$1@.nemesis.news.tpi.pl...
>
|||> 1. Try to attach the database using
> EXEC sp_attach_single_file_db @.dbname = 'pubs', @.physname =
> 'D:\MSSQL\data\dbname.mdf'
>
It works
I was sure that something like this command should exist
... but I couldn't find it in manuals.
Thank You
Radek

Missing log file...

Hi,
OK - I did stupid thing but... ;-)
I put database offline and then I deleted log file.
Then I was surprised that database do not want to
go into on-line status...
I am interested in object of this db not in data.
Is there any hope?
(I know - for me - not, but for this database? ;-)
RadekHi,
You might loose the consistency if you delete the LDF file directly.. Since
you are not that bothered bothered about data you could try
1. Try to attach the database using
EXEC sp_attach_single_file_db @.dbname = 'pubs', @.physname =
'D:\MSSQL\data\dbname.mdf'
This will attach the database with MDF file and will create a new LDF file.
This command will fail some times, since you have not detached the database
properly.. in that use the below method
2.
1. Create a new database with the same name and same MDF and LDF files
2. Stop sql server and rename the existing MDF to a new one and copy the
original MDF to this location and delete the LDF files.
3. Start SQL Server
4. Now your database will be marked suspect
5. Update the sysdatabases to update to Emergency mode. This will not use
LOG files in start up
Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO
6. Restart sql server. now the database will be in emergency mode ( You
could access the database)
7. Use enterprise manager to script all your objects
Thanks
Hari
MCDBA
"Radek" <radek@.obecniezwarszawy.pl> wrote in message
news:cfv1kb$4r3$1@.nemesis.news.tpi.pl...
> Hi,
> OK - I did stupid thing but... ;-)
> I put database offline and then I deleted log file.
> Then I was surprised that database do not want to
> go into on-line status...
> I am interested in object of this db not in data.
> Is there any hope?
> (I know - for me - not, but for this database? ;-)
> Radek
>|||Before you do Hari's 2nd option make sure you copy off your original MDF
first...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Radek" <radek@.obecniezwarszawy.pl> wrote in message
news:cfv1kb$4r3$1@.nemesis.news.tpi.pl...
> Hi,
> OK - I did stupid thing but... ;-)
> I put database offline and then I deleted log file.
> Then I was surprised that database do not want to
> go into on-line status...
> I am interested in object of this db not in data.
> Is there any hope?
> (I know - for me - not, but for this database? ;-)
> Radek
>|||I agree with Wayne...Thanks for pointing out.
Thanks
Hari
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:OOIrMERhEHA.3964@.TK2MSFTNGP12.phx.gbl...
> Before you do Hari's 2nd option make sure you copy off your original MDF
> first...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Radek" <radek@.obecniezwarszawy.pl> wrote in message
> news:cfv1kb$4r3$1@.nemesis.news.tpi.pl...
>|||> 1. Try to attach the database using
> EXEC sp_attach_single_file_db @.dbname = 'pubs', @.physname =
> 'D:\MSSQL\data\dbname.mdf'
>
It works
I was sure that something like this command should exist
... but I couldn't find it in manuals.
Thank You
Radek

Missing log file...

Hi,
OK - I did stupid thing but... ;-)
I put database offline and then I deleted log file.
Then I was surprised that database do not want to
go into on-line status...
I am interested in object of this db not in data.
Is there any hope?
(I know - for me - not, but for this database? ;-)
RadekHi,
You might loose the consistency if you delete the LDF file directly.. Since
you are not that bothered bothered about data you could try
1. Try to attach the database using
EXEC sp_attach_single_file_db @.dbname = 'pubs', @.physname ='D:\MSSQL\data\dbname.mdf'
This will attach the database with MDF file and will create a new LDF file.
This command will fail some times, since you have not detached the database
properly.. in that use the below method
2.
1. Create a new database with the same name and same MDF and LDF files
2. Stop sql server and rename the existing MDF to a new one and copy the
original MDF to this location and delete the LDF files.
3. Start SQL Server
4. Now your database will be marked suspect
5. Update the sysdatabases to update to Emergency mode. This will not use
LOG files in start up
Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO
6. Restart sql server. now the database will be in emergency mode ( You
could access the database)
7. Use enterprise manager to script all your objects
Thanks
Hari
MCDBA
"Radek" <radek@.obecniezwarszawy.pl> wrote in message
news:cfv1kb$4r3$1@.nemesis.news.tpi.pl...
> Hi,
> OK - I did stupid thing but... ;-)
> I put database offline and then I deleted log file.
> Then I was surprised that database do not want to
> go into on-line status...
> I am interested in object of this db not in data.
> Is there any hope?
> (I know - for me - not, but for this database? ;-)
> Radek
>|||Before you do Hari's 2nd option make sure you copy off your original MDF
first...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Radek" <radek@.obecniezwarszawy.pl> wrote in message
news:cfv1kb$4r3$1@.nemesis.news.tpi.pl...
> Hi,
> OK - I did stupid thing but... ;-)
> I put database offline and then I deleted log file.
> Then I was surprised that database do not want to
> go into on-line status...
> I am interested in object of this db not in data.
> Is there any hope?
> (I know - for me - not, but for this database? ;-)
> Radek
>|||I agree with Wayne...Thanks for pointing out.
Thanks
Hari
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:OOIrMERhEHA.3964@.TK2MSFTNGP12.phx.gbl...
> Before you do Hari's 2nd option make sure you copy off your original MDF
> first...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Radek" <radek@.obecniezwarszawy.pl> wrote in message
> news:cfv1kb$4r3$1@.nemesis.news.tpi.pl...
> > Hi,
> >
> > OK - I did stupid thing but... ;-)
> > I put database offline and then I deleted log file.
> > Then I was surprised that database do not want to
> > go into on-line status...
> > I am interested in object of this db not in data.
> > Is there any hope?
> > (I know - for me - not, but for this database? ;-)
> >
> > Radek
> >
> >
>|||> 1. Try to attach the database using
> EXEC sp_attach_single_file_db @.dbname = 'pubs', @.physname => 'D:\MSSQL\data\dbname.mdf'
>
It works :)
I was sure that something like this command should exist
... but I couldn't find it in manuals.
Thank You
Radek

Missing Log - Suspect Datanbase - HELP!

A customer called me and said they ran the disk out of space, rebooted the
machine and the activity log is complaining about the database log being
already in use, and being out of disk. The database is in suspect mode, and
the database log is MIA!
Any ideas'
Thanks
RichardTry looking into system procs,
sp_add_data_file_recover_suspect_db and sp_add_log_file_recover_suspect_db
in books online
Thanks
"Richard Douglass" wrote:
> A customer called me and said they ran the disk out of space, rebooted the
> machine and the activity log is complaining about the database log being
> already in use, and being out of disk. The database is in suspect mode, and
> the database log is MIA!
> Any ideas'
> Thanks
> Richard
>
>|||Richard
http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:OfDesPeVHHA.1000@.TK2MSFTNGP05.phx.gbl...
>A customer called me and said they ran the disk out of space, rebooted the
>machine and the activity log is complaining about the database log being
>already in use, and being out of disk. The database is in suspect mode,
>and the database log is MIA!
> Any ideas'
> Thanks
> Richard
>

Missing Log - Suspect Datanbase - HELP!

A customer called me and said they ran the disk out of space, rebooted the
machine and the activity log is complaining about the database log being
already in use, and being out of disk. The database is in suspect mode, and
the database log is MIA!
Any ideas'
Thanks
RichardTry looking into system procs,
sp_add_data_file_recover_suspect_db and sp_add_log_file_recover_suspect_db
in books online
Thanks
"Richard Douglass" wrote:

> A customer called me and said they ran the disk out of space, rebooted the
> machine and the activity log is complaining about the database log being
> already in use, and being out of disk. The database is in suspect mode, a
nd
> the database log is MIA!
> Any ideas'
> Thanks
> Richard
>
>|||Richard
http://www.karaszi.com/SQLServer/in..._suspect_db.asp
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:OfDesPeVHHA.1000@.TK2MSFTNGP05.phx.gbl...
>A customer called me and said they ran the disk out of space, rebooted the
>machine and the activity log is complaining about the database log being
>already in use, and being out of disk. The database is in suspect mode,
>and the database log is MIA!
> Any ideas'
> Thanks
> Richard
>

missing ldf file

Hello
I have been given an sql database but its just the .mdf file
the .ldf file is not recoverable
Im unable to attach this as the server needs the ldf file which is thinks is
mapped to a t: drive but this doesnt exist
is there anyway i can get this attached to an sql server as i dont have the
ldf file
thank you for any comments
Martin"Martin Palmer" <MartinPalmer@.discussions.microsoft.com> wrote in message
news:D252C85C-B7C0-4C8D-BBA5-4E0892AE2480@.microsoft.com...
> Hello
> I have been given an sql database but its just the .mdf file
> the .ldf file is not recoverable
> Im unable to attach this as the server needs the ldf file which is thinks
> is
> mapped to a t: drive but this doesnt exist
> is there anyway i can get this attached to an sql server as i dont have
> the
> ldf file
I assume you have tried sp_attach_single_file_db?
If that doesn't work, the answer is:
"Maybe".
MS Customer Service has a procedure they can walk you through that MIGHT
work.
As I am not MS, I won't provide it here. (Though if you google sometimes
you can provide it.)
> thank you for any comments
> Martin
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||hello i forgot to add this is sql server 2000
any help would be mostly appriciated
regards
"Martin Palmer" wrote:
> Hello
> I have been given an sql database but its just the .mdf file
> the .ldf file is not recoverable
> Im unable to attach this as the server needs the ldf file which is thinks is
> mapped to a t: drive but this doesnt exist
> is there anyway i can get this attached to an sql server as i dont have the
> ldf file
> thank you for any comments
> Martin|||Attach database may not work in below situations:-
1. If the database is not detached using sp_detach_db or using enterprise
manager.
2. sp_attach_single_file_db will fail incase if you have multiple LDF files
associated.
Since the database attach failed you could try below steps:-
---
1. Create a new database with same MDF and LDF name
2. stop sql server and delete the new MDF file and copy the old MDF file
3. Start SQL Server and see the status. If the database is suspect do the
below steps:-
Setting the database status to emergency mode tells SQL Server to skip
automatic recovery and lets you access the data. To get your data, use this
script:
Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
Sp_configure "allow updates", 0
go
Reconfigure with override
GO
Create a new empty database and you might be able to use bulk copy program
(bcp), simple SELECT commands, or use DTS to extract your data /objects from
problematic database to new database.
Note:
In both the above approch the data integrity will be lost. So it is
recommended to contact Microsoft PSS or restore from a good backup file.
Thanks
Hari
"Martin Palmer" <MartinPalmer@.discussions.microsoft.com> wrote in message
news:D252C85C-B7C0-4C8D-BBA5-4E0892AE2480@.microsoft.com...
> Hello
> I have been given an sql database but its just the .mdf file
> the .ldf file is not recoverable
> Im unable to attach this as the server needs the ldf file which is thinks
> is
> mapped to a t: drive but this doesnt exist
> is there anyway i can get this attached to an sql server as i dont have
> the
> ldf file
> thank you for any comments
> Martin

missing ldf file

Hello
I have been given an sql database but its just the .mdf file
the .ldf file is not recoverable
Im unable to attach this as the server needs the ldf file which is thinks is
mapped to a t: drive but this doesnt exist
is there anyway i can get this attached to an sql server as i dont have the
ldf file
thank you for any comments
Martin
"Martin Palmer" <MartinPalmer@.discussions.microsoft.com> wrote in message
news:D252C85C-B7C0-4C8D-BBA5-4E0892AE2480@.microsoft.com...
> Hello
> I have been given an sql database but its just the .mdf file
> the .ldf file is not recoverable
> Im unable to attach this as the server needs the ldf file which is thinks
> is
> mapped to a t: drive but this doesnt exist
> is there anyway i can get this attached to an sql server as i dont have
> the
> ldf file
I assume you have tried sp_attach_single_file_db?
If that doesn't work, the answer is:
"Maybe".
MS Customer Service has a procedure they can walk you through that MIGHT
work.
As I am not MS, I won't provide it here. (Though if you google sometimes
you can provide it.)
> thank you for any comments
> Martin
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||hello i forgot to add this is sql server 2000
any help would be mostly appriciated
regards
"Martin Palmer" wrote:

> Hello
> I have been given an sql database but its just the .mdf file
> the .ldf file is not recoverable
> Im unable to attach this as the server needs the ldf file which is thinks is
> mapped to a t: drive but this doesnt exist
> is there anyway i can get this attached to an sql server as i dont have the
> ldf file
> thank you for any comments
> Martin
|||Attach database may not work in below situations:-
1. If the database is not detached using sp_detach_db or using enterprise
manager.
2. sp_attach_single_file_db will fail incase if you have multiple LDF files
associated.
Since the database attach failed you could try below steps:-
1. Create a new database with same MDF and LDF name
2. stop sql server and delete the new MDF file and copy the old MDF file
3. Start SQL Server and see the status. If the database is suspect do the
below steps:-
Setting the database status to emergency mode tells SQL Server to skip
automatic recovery and lets you access the data. To get your data, use this
script:
Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
Sp_configure "allow updates", 0
go
Reconfigure with override
GO
Create a new empty database and you might be able to use bulk copy program
(bcp), simple SELECT commands, or use DTS to extract your data /objects from
problematic database to new database.
Note:
In both the above approch the data integrity will be lost. So it is
recommended to contact Microsoft PSS or restore from a good backup file.
Thanks
Hari
"Martin Palmer" <MartinPalmer@.discussions.microsoft.com> wrote in message
news:D252C85C-B7C0-4C8D-BBA5-4E0892AE2480@.microsoft.com...
> Hello
> I have been given an sql database but its just the .mdf file
> the .ldf file is not recoverable
> Im unable to attach this as the server needs the ldf file which is thinks
> is
> mapped to a t: drive but this doesnt exist
> is there anyway i can get this attached to an sql server as i dont have
> the
> ldf file
> thank you for any comments
> Martin

missing ldf file

Hello
I have been given an sql database but its just the .mdf file
the .ldf file is not recoverable
Im unable to attach this as the server needs the ldf file which is thinks is
mapped to a t: drive but this doesnt exist
is there anyway i can get this attached to an sql server as i dont have the
ldf file
thank you for any comments
Martin"Martin Palmer" <MartinPalmer@.discussions.microsoft.com> wrote in message
news:D252C85C-B7C0-4C8D-BBA5-4E0892AE2480@.microsoft.com...
> Hello
> I have been given an sql database but its just the .mdf file
> the .ldf file is not recoverable
> Im unable to attach this as the server needs the ldf file which is thinks
> is
> mapped to a t: drive but this doesnt exist
> is there anyway i can get this attached to an sql server as i dont have
> the
> ldf file
I assume you have tried sp_attach_single_file_db?
If that doesn't work, the answer is:
"Maybe".
MS Customer Service has a procedure they can walk you through that MIGHT
work.
As I am not MS, I won't provide it here. (Though if you google sometimes
you can provide it.)
> thank you for any comments
> Martin
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||hello i forgot to add this is sql server 2000
any help would be mostly appriciated
regards
"Martin Palmer" wrote:

> Hello
> I have been given an sql database but its just the .mdf file
> the .ldf file is not recoverable
> Im unable to attach this as the server needs the ldf file which is thinks
is
> mapped to a t: drive but this doesnt exist
> is there anyway i can get this attached to an sql server as i dont have th
e
> ldf file
> thank you for any comments
> Martin|||Attach database may not work in below situations:-
1. If the database is not detached using sp_detach_db or using enterprise
manager.
2. sp_attach_single_file_db will fail incase if you have multiple LDF files
associated.
Since the database attach failed you could try below steps:-
---
1. Create a new database with same MDF and LDF name
2. stop sql server and delete the new MDF file and copy the old MDF file
3. Start SQL Server and see the status. If the database is suspect do the
below steps:-
Setting the database status to emergency mode tells SQL Server to skip
automatic recovery and lets you access the data. To get your data, use this
script:
Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
Sp_configure "allow updates", 0
go
Reconfigure with override
GO
Create a new empty database and you might be able to use bulk copy program
(bcp), simple SELECT commands, or use DTS to extract your data /objects from
problematic database to new database.
Note:
In both the above approch the data integrity will be lost. So it is
recommended to contact Microsoft PSS or restore from a good backup file.
Thanks
Hari
"Martin Palmer" <MartinPalmer@.discussions.microsoft.com> wrote in message
news:D252C85C-B7C0-4C8D-BBA5-4E0892AE2480@.microsoft.com...
> Hello
> I have been given an sql database but its just the .mdf file
> the .ldf file is not recoverable
> Im unable to attach this as the server needs the ldf file which is thinks
> is
> mapped to a t: drive but this doesnt exist
> is there anyway i can get this attached to an sql server as i dont have
> the
> ldf file
> thank you for any comments
> Martin

Missing Last Row in Excel Output

I am experiencing an issue when I try to export a few of my reports to Excel.
Sometimes when I export to Excel I lose the last row. My reports are very
simple and do not include anything other than a table. I can export it one
day and all of the rows will be included and the next day I will lose the
last row. This is causing confusion among my end users because many of them
also receive a pdf document with the same information; however the pdf is
always correct.
Would someone from Microsoft please let me know if this is a known bug
because I can see nothing wrong with my report.
Thank you,
TimTim,
I am experiencing the same behaviour. Did you ever found out what the
issue was? If so, please share.
One of my report has 161 rows (including headers). When I export this
report to Excel, the last row is missing. Export to other formats work
fine. If I limit the number of rows in the report to 160, all of them
show up in the exported excel. Is there a limit on number of rows
exported on a tab to excel? How do I reset it?
Thanks|||Try adding an additional footer row to the table you are trying to export.
In the first cell enter a value of =" ". This isn't a fix but at least will
you will be able export the table with all of your data.
Tim
"kkaps" wrote:
> Tim,
> I am experiencing the same behaviour. Did you ever found out what the
> issue was? If so, please share.
> One of my report has 161 rows (including headers). When I export this
> report to Excel, the last row is missing. Export to other formats work
> fine. If I limit the number of rows in the report to 160, all of them
> show up in the exported excel. Is there a limit on number of rows
> exported on a tab to excel? How do I reset it?
> Thanks
>|||I had the same issue with a report that contains a matrix object...the
last row of matrix data was being dropped upon Export to Excel. I
simply put a small, empty textbox object directly below my matrix
object...this fixed the problem!

Missing labels in chart bar the X axis

Hi All

I have a report with a bar chart. Within each category of the bar chart I have 3 bars that are compared with each other.

The problem is that not all the categories labels are shown, even though there is enough free space on the axis (the font is very small).

Can I configure the chart thus all the values will apear?

Thank you.

In the chart properties dialog, go to the x-axis tab and turn on "margins" for the x-axis. This should always resolve the issue.

-- Robert

|||

Robert:

This action does not seem to work when using the "cross at" parameter on the same axis.

|||

This action does not seem to work at all... the "Side margins" is allready turned on

Do you have any other idea?

Thanks

Missing Labels in Chart

I have a line chart that has several series, and it routinely leaves off 1 or 2 point labels on the last X-axis.

Is anyone else experiencing this? If so, does anyone know what the cause is, and is there a fix?

Thanks!

BobP

There is a setting in the X-Axis tab for major and minor interval. Try setting one of them to 1.|||

They are both set to 1 and still no labels.

BobP

|||

I am missing point labels (on bar charts) too, seems to happen when there are an odd number of instances of a grouping on the x axis. It is always the last bar that is missing the point label too.

Is this a bug in SSRS, or a misunderstanding on my part?

|||

One way around this is to turn on margins on the x-axis.

-- Robert

|||Thanks, that works OK now.....

Missing Labels in Chart

I have a line chart that has several series, and it routinely leaves off 1 or 2 point labels on the last X-axis.

Is anyone else experiencing this? If so, does anyone know what the cause is, and is there a fix?

Thanks!

BobP

There is a setting in the X-Axis tab for major and minor interval. Try setting one of them to 1.|||

They are both set to 1 and still no labels.

BobP

|||

I am missing point labels (on bar charts) too, seems to happen when there are an odd number of instances of a grouping on the x axis. It is always the last bar that is missing the point label too.

Is this a bug in SSRS, or a misunderstanding on my part?

|||

One way around this is to turn on margins on the x-axis.

-- Robert

|||Thanks, that works OK now.....

Missing Keys & Relationships

(Cross posted to the active server pages listserv at 15Seconds.com)
A few weeks ago I posted a message about keys and relationships that
mysteriously disappeared on our production SQL Server 2000 database. I
managed to restore all of the missing attributes with success. But it
happened again this morning
How can all of the keys and relationships in my database be wiped out in one
deft move? Is there anything in the Master database or elsewhere that could
cause everything to disappear at once?
The system administrator is not a DBA, so human-error is definitely a
possibility. They are going to blame the scripts that are used to update
the stored procedures, but the same scripts are tested on our test
deployment server and work flawlessly. The scripts contained no changes to
database tables - they strictly updated stored procedures.
Thanks for your help.
DaveThis is a multi-part message in MIME format.
--=_NextPart_000_02E7_01C36018.E9B80A20
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Is it possible that some sort of scheduled process executes a script to =drop all constraints?
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Dave" <dave@.glimmernet.com> wrote in message =news:eskCToDYDHA.2484@.TK2MSFTNGP09.phx.gbl...
(Cross posted to the active server pages listserv at 15Seconds.com)
A few weeks ago I posted a message about keys and relationships that
mysteriously disappeared on our production SQL Server 2000 database. I
managed to restore all of the missing attributes with success. But it
happened again this morning
How can all of the keys and relationships in my database be wiped out in =one
deft move? Is there anything in the Master database or elsewhere that =could
cause everything to disappear at once?
The system administrator is not a DBA, so human-error is definitely a
possibility. They are going to blame the scripts that are used to =update
the stored procedures, but the same scripts are tested on our test
deployment server and work flawlessly. The scripts contained no changes =to
database tables - they strictly updated stored procedures.
Thanks for your help.
Dave
--=_NextPart_000_02E7_01C36018.E9B80A20
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Is it possible that some sort of =scheduled process executes a script to drop all constraints?
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Dave" wrote in message news:eskCToDYDHA.2484=@.TK2MSFTNGP09.phx.gbl...(Cross posted to the active server pages listserv at 15Seconds.com)A =few weeks ago I posted a message about keys and relationships thatmysteriously =disappeared on our production SQL Server 2000 database. =Imanaged to restore all of the missing attributes with success. But ithappened =again this morningHow can all of the keys and relationships in my =database be wiped out in onedeft move? Is there anything in the Master =database or elsewhere that couldcause everything to disappear at =once?The system administrator is not a DBA, so human-error is definitely =apossibility. They are going to blame the scripts that are used to updatethe =stored procedures, but the same scripts are tested on our testdeployment =server and work flawlessly. The scripts contained no changes todatabase =tables - they strictly updated stored procedures.Thanks for your help.Dave

---

--=_NextPart_000_02E7_01C36018.E9B80A20--|||The data definitions didn't change, so there was no move during this time.
What we did the first time when they all disapeared was to detach the
database (jsut the database, not Master). Then I attached it on the
developmet server, updated the keys, detached it and reattached it to the
production database.
Today we updated some stored procedures, and the script we used was
generated by SQL Server. It's pretty standard: drops the stored procedures
if they exist and then re/create them. Simple stuff, really, so I'm not
convinced it is our script. I haven't totally ruled it out, but I would
guess it's not my script.
Dave
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:u5snHqDYDHA.416@.tk2msftngp13.phx.gbl...
> How are you moving the data/definitions?
> From what to what?
> It certainly should not miss anything out especially if you are using the
exact same script on another server and it works fine.
>
> --
> Allan Mitchell (Microsoft SQL Server MVP)
> MCSE,MCDBA
> www.SQLDTS.com
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org|||This is a multi-part message in MIME format.
--=_NextPart_000_030E_01C3601A.4BFC0690
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
No. To drop constraints takes a deliberate act and to drop them all =would require some sort of script. I'd be curious if there was a stored =proc in your database that does this. If you want to be absolutely =certain of when this happens, I'd use the profiler.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Dave" <dave@.glimmernet.com> wrote in message =news:OVaposDYDHA.1644@.TK2MSFTNGP10.phx.gbl...
I sure hope not because it's not anything I configured.
Are there any SQL defaults that might mimic this?
Dave
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:eRRZfqDYDHA.2256@.TK2MSFTNGP10.phx.gbl...
Is it possible that some sort of scheduled process executes a script =to drop all constraints?
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Dave" <dave@.glimmernet.com> wrote in message =news:eskCToDYDHA.2484@.TK2MSFTNGP09.phx.gbl...
(Cross posted to the active server pages listserv at 15Seconds.com)
A few weeks ago I posted a message about keys and relationships that
mysteriously disappeared on our production SQL Server 2000 database. =I
managed to restore all of the missing attributes with success. But it
happened again this morning
How can all of the keys and relationships in my database be wiped out =in one
deft move? Is there anything in the Master database or elsewhere that =could
cause everything to disappear at once?
The system administrator is not a DBA, so human-error is definitely a
possibility. They are going to blame the scripts that are used to =update
the stored procedures, but the same scripts are tested on our test
deployment server and work flawlessly. The scripts contained no =changes to
database tables - they strictly updated stored procedures.
Thanks for your help.
Dave
--=_NextPart_000_030E_01C3601A.4BFC0690
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

No. To drop constraints takes a =deliberate act and to drop them all would require some sort of script. I'd be =curious if there was a stored proc in your database that does this. If you =want to be absolutely certain of when this happens, I'd use the =profiler.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Dave" wrote in message news:OVaposDYDHA.1644=@.TK2MSFTNGP10.phx.gbl...
I sure hope not because it's not =anything I configured.
Are there any SQL defaults that might =mimic this?
Dave
"Tom Moreau" = wrote in message news:eRRZfqDYDHA.2256=@.TK2MSFTNGP10.phx.gbl...
Is it possible that some sort of =scheduled process executes a script to drop all constraints?
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"Dave" wrote =in message news:eskCToDYDHA.2484=@.TK2MSFTNGP09.phx.gbl...(Cross posted to the active server pages listserv at 15Seconds.com)A =few weeks ago I posted a message about keys and relationships =thatmysteriously disappeared on our production SQL Server 2000 database. =Imanaged to restore all of the missing attributes with success. But ithappened =again this morningHow can all of the keys and relationships in my =database be wiped out in onedeft move? Is there anything in the =Master database or elsewhere that couldcause everything to disappear at once?The system administrator is not a DBA, so human-error is definitely apossibility. They are going to blame the scripts =that are used to updatethe stored procedures, but the same scripts are =tested on our testdeployment server and work flawlessly. The =scripts contained no changes todatabase tables - they strictly updated =stored procedures.Thanks for your help.Dave

---

--=_NextPart_000_030E_01C3601A.4BFC0690--|||This is a multi-part message in MIME format.
--=_NextPart_000_0036_01C3601B.602B9BC0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
That's what I thought - that deleting these constraints is a chore and =can't really happen "accidentally"
Once, our script was accidentally run against the master database and =ended up creating a bunch of unncessary stored procedures. I know that =the system admin went to clean that out, and I was wondering if she =touched some other thing in the process and that something in the master =database is now causing this behavior.
I keep thinking it's accidental, but, if it were intentional, how would =this get done?
Is there any way to retroactively figure out exactly when this happend?
Thanks for all of your help, Tom
Dave
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:eNjQZvDYDHA.536@.TK2MSFTNGP10.phx.gbl...
No. To drop constraints takes a deliberate act and to drop them all =would require some sort of script. I'd be curious if there was a stored =proc in your database that does this. If you want to be absolutely =certain of when this happens, I'd use the profiler.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Dave" <dave@.glimmernet.com> wrote in message =news:OVaposDYDHA.1644@.TK2MSFTNGP10.phx.gbl...
I sure hope not because it's not anything I configured.
Are there any SQL defaults that might mimic this?
Dave
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:eRRZfqDYDHA.2256@.TK2MSFTNGP10.phx.gbl...
Is it possible that some sort of scheduled process executes a script =to drop all constraints?
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Dave" <dave@.glimmernet.com> wrote in message =news:eskCToDYDHA.2484@.TK2MSFTNGP09.phx.gbl...
(Cross posted to the active server pages listserv at 15Seconds.com)
A few weeks ago I posted a message about keys and relationships that
mysteriously disappeared on our production SQL Server 2000 database. = I
managed to restore all of the missing attributes with success. But =it
happened again this morning
How can all of the keys and relationships in my database be wiped =out in one
deft move? Is there anything in the Master database or elsewhere =that could
cause everything to disappear at once?
The system administrator is not a DBA, so human-error is definitely =a
possibility. They are going to blame the scripts that are used to =update
the stored procedures, but the same scripts are tested on our test
deployment server and work flawlessly. The scripts contained no =changes to
database tables - they strictly updated stored procedures.
Thanks for your help.
Dave
--=_NextPart_000_0036_01C3601B.602B9BC0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =charset=3Dwindows-1252">
<META content=3D"MSHTML 6.00.2800.1170" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#c0c0c0>
<DIV><FONT face=3DArial size=3D2>That's what I thought - that deleting =these constraints is a chore and can't really happen ="accidentally"</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Once, our script was accidentally run =against the master database and ended up creating a bunch of unncessary stored procedures. I know that the system admin went to clean that out, =and I was wondering if she touched some other thing in the process and that =something in the master database is now causing this behavior.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>I keep thinking it's accidental, but, =if it were intentional, how would this get done?</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Is there any way to retroactively =figure out exactly when this happend?</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Thanks for all of your help, =Tom</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Dave</FONT></DIV>
<BLOCKQUOTE dir=3Dltr style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV>"Tom Moreau" <<A =href=3D"mailto:tom@.dont.spam.me.cips.ca">tom@.dont.spam.me.cips.ca</A>>= wrote in message <A =href=3D"news:eNjQZvDYDHA.536@.TK2MSFTNGP10.phx.gbl">news:eNjQZvDYDHA.536@.T=K2MSFTNGP10.phx.gbl</A>...</DIV>
<DIV><FONT face=3DTahoma size=3D2>No. To drop constraints takes =a deliberate act and to drop them all would require some sort of script. I'd =be curious if there was a stored proc in your database that does =this. If you want to be absolutely certain of when this happens, I'd use the profiler.</FONT></DIV>
<DIV><BR>-- <BR>Tom</DIV>
<DIV> </DIV>
=<DIV>---<BR>T=homas A. Moreau, BSc, PhD, MCSE, MCDBA<BR>SQL Server MVP<BR>Columnist, SQL =Server Professional<BR>Toronto, ON Canada<BR><A =href=3D"www.pinnaclepublishing.com=">http://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=
/sql</A></DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV>"Dave" <<A href=3D"mailto:dave@.glimmernet.com">dave@.glimmernet.com</A>> wrote =in message <A =href=3D"news:OVaposDYDHA.1644@.TK2MSFTNGP10.phx.gbl">news:OVaposDYDHA.1644=@.TK2MSFTNGP10.phx.gbl</A>...</DIV>
<DIV><FONT face=3DArial size=3D2>I sure hope not because it's not =anything I configured.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Are there any SQL defaults that might =mimic this?</FONT><FONT face=3DArial size=3D2></FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Dave</FONT></DIV>
<BLOCKQUOTE style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV>"Tom Moreau" <<A =href=3D"mailto:tom@.dont.spam.me.cips.ca">tom@.dont.spam.me.cips.ca</A>>= wrote in message <A =href=3D"news:eRRZfqDYDHA.2256@.TK2MSFTNGP10.phx.gbl">news:eRRZfqDYDHA.2256=@.TK2MSFTNGP10.phx.gbl</A>...</DIV>
<DIV><FONT face=3DTahoma size=3D2>Is it possible that some sort of =scheduled process executes a script to drop all constraints?</FONT></DIV>
<DIV><BR>-- <BR>Tom</DIV>
<DIV> </DIV>
=<DIV>---<BR>T=homas A. Moreau, BSc, PhD, MCSE, MCDBA<BR>SQL Server MVP<BR>Columnist, SQL =Server Professional<BR>Toronto, ON Canada<BR><A =href=3D"www.pinnaclepublishing.com=">http://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=
/sql</A></DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV>"Dave" <<A href=3D"mailto:dave@.glimmernet.com">dave@.glimmernet.com</A>> =wrote in message <A =href=3D"news:eskCToDYDHA.2484@.TK2MSFTNGP09.phx.gbl">news:eskCToDYDHA.2484=@.TK2MSFTNGP09.phx.gbl</A>...</DIV>(Cross posted to the active server pages listserv at =15Seconds.com)<BR><BR>A few weeks ago I posted a message about keys and relationships that<BR>mysteriously disappeared on our production SQL Server 2000 database. I<BR>managed to restore all of the missing =attributes with success. But it<BR>happened again this morning<BR><BR>How can all of =the keys and relationships in my database be wiped out in one<BR>deft move? Is there anything in the Master database or elsewhere =that could<BR>cause everything to disappear at once?<BR><BR>The system administrator is not a DBA, so human-error is definitely a<BR>possibility. They are going to blame the scripts that are =used to update<BR>the stored procedures, but the same scripts are tested on =our test<BR>deployment server and work flawlessly. The scripts =contained no changes to<BR>database tables - they strictly updated stored procedures.<BR><BR>Thanks for your =help.<BR><BR>Dave<BR><BR><BR><BR><BR></BLOCKQUOTE></BLOCKQUOTE></BODY></H=TML>
--=_NextPart_000_0036_01C3601B.602B9BC0--|||OK
detaching the DB is sound.
Attach is sound
To this point here there most definitley will not be key losses.
By Updated the keys do you mean ?
EXEC sp_UPDATESTATS
I would profiler the drop procs script or read it very carefully
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org|||This is a multi-part message in MIME format.
--=_NextPart_000_0346_01C3601D.559545B0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
You may be able to find out by using Lumigent's Log Explorer - =www.lumigent.com - although I don't know if it tracks constraint =removal. To add to Allan's comments, I'd search through your scripts =for the phase 'drop constraint'.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Dave" <dave@.glimmernet.com> wrote in message =news:OTsepzDYDHA.1492@.TK2MSFTNGP12.phx.gbl...
That's what I thought - that deleting these constraints is a chore and =can't really happen "accidentally"
Once, our script was accidentally run against the master database and =ended up creating a bunch of unncessary stored procedures. I know that =the system admin went to clean that out, and I was wondering if she =touched some other thing in the process and that something in the master =database is now causing this behavior.
I keep thinking it's accidental, but, if it were intentional, how would =this get done?
Is there any way to retroactively figure out exactly when this happend?
Thanks for all of your help, Tom
Dave
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:eNjQZvDYDHA.536@.TK2MSFTNGP10.phx.gbl...
No. To drop constraints takes a deliberate act and to drop them all =would require some sort of script. I'd be curious if there was a stored =proc in your database that does this. If you want to be absolutely =certain of when this happens, I'd use the profiler.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Dave" <dave@.glimmernet.com> wrote in message =news:OVaposDYDHA.1644@.TK2MSFTNGP10.phx.gbl...
I sure hope not because it's not anything I configured.
Are there any SQL defaults that might mimic this?
Dave
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:eRRZfqDYDHA.2256@.TK2MSFTNGP10.phx.gbl...
Is it possible that some sort of scheduled process executes a script =to drop all constraints?
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Dave" <dave@.glimmernet.com> wrote in message =news:eskCToDYDHA.2484@.TK2MSFTNGP09.phx.gbl...
(Cross posted to the active server pages listserv at 15Seconds.com)
A few weeks ago I posted a message about keys and relationships that
mysteriously disappeared on our production SQL Server 2000 database. = I
managed to restore all of the missing attributes with success. But =it
happened again this morning
How can all of the keys and relationships in my database be wiped =out in one
deft move? Is there anything in the Master database or elsewhere =that could
cause everything to disappear at once?
The system administrator is not a DBA, so human-error is definitely =a
possibility. They are going to blame the scripts that are used to =update
the stored procedures, but the same scripts are tested on our test
deployment server and work flawlessly. The scripts contained no =changes to
database tables - they strictly updated stored procedures.
Thanks for your help.
Dave
--=_NextPart_000_0346_01C3601D.559545B0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =charset=3Dwindows-1252">
<META content=3D"MSHTML 5.50.4207.2601" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#c0c0c0>
<DIV><FONT face=3DTahoma size=3D2>You may be able to find out by using =Lumigent's Log Explorer - <A href=3D"www.lumigent.com</A>">http://www.lumigent.com">www.lumigent.com</A> =- although I don't know if it tracks constraint removal. To add to Allan's =comments, I'd search through your scripts for the phase 'drop =constraint'.</FONT></DIV>
<DIV><BR>-- <BR>Tom</DIV>
<DIV> </DIV>
<DIV>---<BR>T=homas A. Moreau, BSc, PhD, MCSE, MCDBA<BR>SQL Server MVP<BR>Columnist, SQL =Server Professional<BR>Toronto, ON Canada<BR><A href=3D"www.pinnaclepublishing.com=">http://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=
/sql</A></DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV>"Dave" <<A =href=3D"mailto:dave@.glimmernet.com">dave@.glimmernet.com</A>> wrote in message <A href=3D"news:OTsepzDYDHA.1492@.TK2MSFTNGP12.phx.gbl">news:OTsepzDYDHA.1492=@.TK2MSFTNGP12.phx.gbl</A>...</DIV>
<DIV><FONT face=3DArial size=3D2>That's what I thought - that deleting =these constraints is a chore and can't really happen ="accidentally"</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Once, our script was accidentally run =against the master database and ended up creating a bunch of unncessary stored procedures. I know that the system admin went to clean that out, =and I was wondering if she touched some other thing in the process and that =something in the master database is now causing this behavior.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>I keep thinking it's accidental, but, =if it were intentional, how would this get done?</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Is there any way to retroactively =figure out exactly when this happend?</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Thanks for all of your help, =Tom</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Dave</FONT></DIV>
<BLOCKQUOTE dir=3Dltr style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV>"Tom Moreau" <<A =href=3D"mailto:tom@.dont.spam.me.cips.ca">tom@.dont.spam.me.cips.ca</A>>= wrote in message <A =href=3D"news:eNjQZvDYDHA.536@.TK2MSFTNGP10.phx.gbl">news:eNjQZvDYDHA.536@.T=K2MSFTNGP10.phx.gbl</A>...</DIV>
<DIV><FONT face=3DTahoma size=3D2>No. To drop constraints takes =a deliberate act and to drop them all would require some sort of script. I'd =be curious if there was a stored proc in your database that does =this. If you want to be absolutely certain of when this happens, I'd use the profiler.</FONT></DIV>
<DIV><BR>-- <BR>Tom</DIV>
<DIV> </DIV>
=<DIV>---<BR>T=homas A. Moreau, BSc, PhD, MCSE, MCDBA<BR>SQL Server MVP<BR>Columnist, SQL =Server Professional<BR>Toronto, ON Canada<BR><A =href=3D"www.pinnaclepublishing.com=">http://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=
/sql</A></DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV>"Dave" <<A href=3D"mailto:dave@.glimmernet.com">dave@.glimmernet.com</A>> wrote =in message <A =href=3D"news:OVaposDYDHA.1644@.TK2MSFTNGP10.phx.gbl">news:OVaposDYDHA.1644=@.TK2MSFTNGP10.phx.gbl</A>...</DIV>
<DIV><FONT face=3DArial size=3D2>I sure hope not because it's not =anything I configured.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Are there any SQL defaults that might =mimic this?</FONT><FONT face=3DArial size=3D2></FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Dave</FONT></DIV>
<BLOCKQUOTE style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV>"Tom Moreau" <<A =href=3D"mailto:tom@.dont.spam.me.cips.ca">tom@.dont.spam.me.cips.ca</A>>= wrote in message <A =href=3D"news:eRRZfqDYDHA.2256@.TK2MSFTNGP10.phx.gbl">news:eRRZfqDYDHA.2256=@.TK2MSFTNGP10.phx.gbl</A>...</DIV>
<DIV><FONT face=3DTahoma size=3D2>Is it possible that some sort of =scheduled process executes a script to drop all constraints?</FONT></DIV>
<DIV><BR>-- <BR>Tom</DIV>
<DIV> </DIV>
=<DIV>---<BR>T=homas A. Moreau, BSc, PhD, MCSE, MCDBA<BR>SQL Server MVP<BR>Columnist, SQL =Server Professional<BR>Toronto, ON Canada<BR><A =href=3D"www.pinnaclepublishing.com=">http://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=
/sql</A></DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV>"Dave" <<A href=3D"mailto:dave@.glimmernet.com">dave@.glimmernet.com</A>> =wrote in message <A =href=3D"news:eskCToDYDHA.2484@.TK2MSFTNGP09.phx.gbl">news:eskCToDYDHA.2484=@.TK2MSFTNGP09.phx.gbl</A>...</DIV>(Cross posted to the active server pages listserv at =15Seconds.com)<BR><BR>A few weeks ago I posted a message about keys and relationships that<BR>mysteriously disappeared on our production SQL Server 2000 database. I<BR>managed to restore all of the missing =attributes with success. But it<BR>happened again this morning<BR><BR>How can all of =the keys and relationships in my database be wiped out in one<BR>deft move? Is there anything in the Master database or elsewhere =that could<BR>cause everything to disappear at once?<BR><BR>The system administrator is not a DBA, so human-error is definitely a<BR>possibility. They are going to blame the scripts that are =used to update<BR>the stored procedures, but the same scripts are tested on =our test<BR>deployment server and work flawlessly. The scripts =contained no changes to<BR>database tables - they strictly updated stored procedures.<BR><BR>Thanks for your =help.<BR><BR>Dave<BR><BR><BR><BR><BR></BLOCKQUOTE></BLOCKQUOTE></BODY></H=TML>
--=_NextPart_000_0346_01C3601D.559545B0--|||Well, this problem has been resolved.
The one deft move: apparently the system admin detached the database and
reattached an old version...
I can't win.
But thanks to everyone who helped out. I still have no idea what happened
to the constraints the first time, but it's working now so that's all I can
ask for.
Dave
"Dave" <dave@.glimmernet.com> wrote in message
news:eskCToDYDHA.2484@.TK2MSFTNGP09.phx.gbl...
> (Cross posted to the active server pages listserv at 15Seconds.com)
> A few weeks ago I posted a message about keys and relationships that
> mysteriously disappeared on our production SQL Server 2000 database. I
> managed to restore all of the missing attributes with success. But it
> happened again this morning
> How can all of the keys and relationships in my database be wiped out in
one
> deft move? Is there anything in the Master database or elsewhere that
could
> cause everything to disappear at once?
> The system administrator is not a DBA, so human-error is definitely a
> possibility. They are going to blame the scripts that are used to update
> the stored procedures, but the same scripts are tested on our test
> deployment server and work flawlessly. The scripts contained no changes
to
> database tables - they strictly updated stored procedures.
> Thanks for your help.
> Dave
>
>
>|||This is a multi-part message in MIME format.
--=_NextPart_000_038D_01C36020.23BE9B60
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Looks like she owes you lunch. No ... wait ... she owes us lunch. Make =mine medium rare ... with a baked potato. ;-)
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Dave" <dave@.glimmernet.com> wrote in message =news:uSYgT8DYDHA.1492@.TK2MSFTNGP12.phx.gbl...
Well, this problem has been resolved.
The one deft move: apparently the system admin detached the database and
reattached an old version...
I can't win.
But thanks to everyone who helped out. I still have no idea what =happened
to the constraints the first time, but it's working now so that's all I =can
ask for.
Dave
"Dave" <dave@.glimmernet.com> wrote in message
news:eskCToDYDHA.2484@.TK2MSFTNGP09.phx.gbl...
> (Cross posted to the active server pages listserv at 15Seconds.com)
> A few weeks ago I posted a message about keys and relationships that
> mysteriously disappeared on our production SQL Server 2000 database. =I
> managed to restore all of the missing attributes with success. But it
> happened again this morning
> How can all of the keys and relationships in my database be wiped out =in
one
> deft move? Is there anything in the Master database or elsewhere that
could
> cause everything to disappear at once?
> The system administrator is not a DBA, so human-error is definitely a
> possibility. They are going to blame the scripts that are used to =update
> the stored procedures, but the same scripts are tested on our test
> deployment server and work flawlessly. The scripts contained no =changes
to
> database tables - they strictly updated stored procedures.
> Thanks for your help.
> Dave
>
>
>
--=_NextPart_000_038D_01C36020.23BE9B60
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Looks like she owes you lunch. =No ... wait ... she owes us lunch. Make mine medium =rare ... with a baked potato. ;-)
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Dave" wrote in message news:uSYgT8DYDHA.1492=@.TK2MSFTNGP12.phx.gbl...Well, this problem has been resolved.The one deft move: apparently the =system admin detached the database andreattached an old =version...I can't win.But thanks to everyone who helped out. I still have no =idea what happenedto the constraints the first time, but it's working now =so that's all I canask for.Dave"Dave" wrote in =messagenews:eskCToDYDHA.2484=@.TK2MSFTNGP09.phx.gbl...> (Cross posted to the active server pages listserv at 15Seconds.com)>> A few weeks ago I posted a message about =keys and relationships that> mysteriously disappeared on our production =SQL Server 2000 database. I> managed to restore all of the missing =attributes with success. But it> happened again this morning>> =How can all of the keys and relationships in my database be wiped out =inone> deft move? Is there anything in the Master database or elsewhere thatcould> cause everything to disappear at =once?>> The system administrator is not a DBA, so human-error is definitely =a> possibility. They are going to blame the scripts that are used to update> the stored procedures, but the same scripts are tested on =our test> deployment server and work flawlessly. The scripts =contained no changesto> database tables - they strictly updated stored procedures.>> Thanks for your help.>> Dave>>>>>

---

--=_NextPart_000_038D_01C36020.23BE9B60--|||There's no answer to that <grin>
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

Missing Join Predicate error

All,
We did an inplace upgrade of our "SQLServer 2000 SP3a" production server to
"SQLServer 2005 SP2 Enterprise Edition" on saturday. The system has 8 CPUs
and 4GB of RAM. This evening, the server crashed and I had to reboot the box.
I checked the eventvwr and this is what I get. I also found a stack dump in
the log. I checked the blackbox trace for any signs and I found "MIssing Join
Predicate" in the EventClass for one of the databases. This leads me to
believe that this is the reason why SQLServer crashed though we never
encountered this problem in the SQLServer 2000 installation before we
upgraded -- the application has been working fine without any problem over
the past several years. I could have trouble shooted this issue more but
unfortunately the text of the code that resulted in the "MIssing Join
Predicate" is empty in the Text column.
Event Type:Error
Event Source:MSSQLSERVER
Event Category:(2)
Event ID:17311
Date:1/14/2008
Time:5:59:11 PM
User:N/A
Computer:ENSQLP2
Description:
SQL Server is terminating because of fatal exception c0000005. This error
may be caused by an unhandled Win32 or C++ exception, or by an access
violation encountered during exception handling. Check the SQL error log for
any related stack dumps or messages. This exception forces SQL Server to
shutdown. To recover from this error, restart the server (unless SQLAgent is
configured to auto restart).
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 9f 43 00 00 10 00 00 00 ?C.....
0008: 08 00 00 00 45 00 4e 00 ...E.N.
0010: 53 00 51 00 4c 00 50 00 S.Q.L.P.
0018: 32 00 00 00 00 00 00 00 2......
Has anyone encountered this problem before? I will start a user defined
trace and get to the core tomorrow. Neverthless, I was wondering if anyone
has encountered this issue before
Here is the stack dump. Note that at this point I also dont know which
query/SP caused the crash.
================================================== ===================
BugCheck Dump
================================================== ===================
This file is generated by Microsoft SQL Server
version 9.00.3042.00
upon detection of fatal unexpected error. Please return this file,
the query or program that produced the bugcheck, the database and
the error log, and any other pertinent information with a Service Request.
Computer type is AT/AT COMPATIBLE.
Bios Version is HP - 2
Current time is 17:59:12 01/14/08.
8 Intel x86 level 15, 2833 Mhz processor (s).
Windows NT 5.2 Build 3790 CSD Service Pack 1.
Memory
MemoryLoad = 51%
Total Physical = 4094 MB
Available Physical = 1986 MB
Total Page File = 10069 MB
Available Page File = 7980 MB
Total Virtual = 2047 MB
Available Virtual = 217 MB
**Dump thread - spid = 0, PSS = 0x00000000, EC = 0x00000000
***Stack Dump being sent to e:\MSSQL\MSSQL\LOG\SQLDump0005.txt
*
************************************************** *****************************
*
* BEGIN STACK DUMP:
* 01/14/08 17:59:12 spid 0
*
* ex_handle_except encountered exception C0000005 - Server terminating
*
*
* MODULE BASE END SIZE
* sqlservr 01000000 02C09FFF 01c0a000
* ntdll 7C800000 7C8BFFFF 000c0000
* kernel32 77E40000 77F41FFF 00102000
* MSVCR80 78130000 781CAFFF 0009b000
* msvcrt 77BA0000 77BF9FFF 0005a000
* MSVCP80 7C420000 7C4A6FFF 00087000
* ADVAPI32 77F50000 77FEBFFF 0009c000
* RPCRT4 77C50000 77CEEFFF 0009f000
* USER32 77380000 77411FFF 00092000
* GDI32 77C00000 77C48FFF 00049000
* CRYPT32 761B0000 76242FFF 00093000
* MSASN1 76190000 761A1FFF 00012000
* Secur32 76F50000 76F62FFF 00013000
* MSWSOCK 71B20000 71B60FFF 00041000
* WS2_32 71C00000 71C16FFF 00017000
* WS2HELP 71BF0000 71BF7FFF 00008000
* USERENV 76920000 769E3FFF 000c4000
* opends60 333E0000 333E6FFF 00007000
* NETAPI32 71C40000 71C97FFF 00058000
* SHELL32 7C8D0000 7D0D3FFF 00804000
* SHLWAPI 77DA0000 77DF1FFF 00052000
* comctl32 77420000 77522FFF 00103000
* psapi 76B70000 76B7AFFF 0000b000
* instapi 48060000 48069FFF 0000a000
* sqlevn70 4F610000 4F7B8FFF 001a9000
* SQLOS 344D0000 344D4FFF 00005000
* rsaenh 68000000 6802EFFF 0002f000
* AUTHZ 76C40000 76C53FFF 00014000
* MSCOREE 79000000 79044FFF 00045000
* ole32 77670000 777A3FFF 00134000
* msv1_0 76C90000 76CB6FFF 00027000
* iphlpapi 76CF0000 76D09FFF 0001a000
* kerberos 62810000 62867FFF 00058000
* cryptdll 766E0000 766EBFFF 0000c000
* schannel 76750000 76776FFF 00027000
* COMRES 77010000 770D5FFF 000c6000
* XOLEHLP 628D0000 628D5FFF 00006000
* MSDTCPRX 628E0000 62957FFF 00078000
* msvcp60 780C0000 78120FFF 00061000
* MTXCLU 62960000 62978FFF 00019000
* VERSION 77B90000 77B97FFF 00008000
* WSOCK32 71BB0000 71BB8FFF 00009000
* OLEAUT32 77D00000 77D8BFFF 0008c000
* CLUSAPI 62980000 62991FFF 00012000
* RESUTILS 629A0000 629B2FFF 00013000
* DNSAPI 76ED0000 76EFEFFF 0002f000
* winrnr 76F70000 76F76FFF 00007000
* WLDAP32 76F10000 76F3DFFF 0002e000
* rasadhlp 76F80000 76F87FFF 00008000
* security 62DF0000 62DF3FFF 00004000
* msfte 63750000 639A8FFF 00259000
* dbghelp 639B0000 63AC4FFF 00115000
* WINTRUST 76BB0000 76BDAFFF 0002b000
* imagehlp 76C10000 76C38FFF 00029000
* dssenh 68100000 68123FFF 00024000
* hnetcfg 63D20000 63D78FFF 00059000
* wshtcpip 71AE0000 71AE7FFF 00008000
* NTMARTA 77E00000 77E21FFF 00022000
* SAMLIB 63DC0000 63DCEFFF 0000f000
* ntdsapi 766F0000 76704FFF 00015000
* xpsp2res 63E50000 64114FFF 002c5000
* CLBCatQ 777B0000 77832FFF 00083000
* sqlncli 64120000 64343FFF 00224000
* COMCTL32 77530000 775C6FFF 00097000
* comdlg32 762B0000 762F9FFF 0004a000
* SQLNCLIR 007A0000 007D2FFF 00033000
* msftepxy 645D0000 645E4FFF 00015000
* EntApi 65E90000 65EA2FFF 00013000
* WININET 77210000 772B7FFF 000a8000
* xpsqlbot 66100000 66105FFF 00006000
* xpstar90 66120000 66168FFF 00049000
* SQLSCM90 66180000 66188FFF 00009000
* ODBC32 661A0000 661DCFFF 0003d000
* BatchParser90 661E0000 661FEFFF 0001f000
* ATL80 7C630000 7C64AFFF 0001b000
* odbcint 664D0000 664E6FFF 00017000
* xpstar90 664F0000 66515FFF 00026000
* xplog70 66520000 6652BFFF 0000c000
* xplog70 66540000 66542FFF 00003000
* odsole70 66550000 6655EFFF 0000f000
* SXS 66BD0000 66C8BFFF 000bc000
* oledb32 668E0000 66958FFF 00079000
* MSDART 64510000 64529FFF 0001a000
* OLEDB32R 64530000 64540FFF 00011000
* msdaora 66FF0000 67030FFF 00041000
* MSDATL3 66BA0000 66BB4FFF 00015000
* MSDAORAR 644F0000 644F3FFF 00004000
* oci 67040000 67096FFF 00057000
* MSVCR71 7C340000 7C395FFF 00056000
* OCIW32 670A0000 670F5FFF 00056000
* ORACLIENT10 67100000 67351FFF 00252000
* oracore10 67360000 67442FFF 000e3000
* oranls10 67450000 67513FFF 000c4000
* oraunls10 67520000 67537FFF 00018000
* orauts 66BC0000 66BC6FFF 00007000
* WINMM 76AA0000 76ACCFFF 0002d000
* oravsn10 67540000 67545FFF 00006000
* oracommon10 67550000 67641FFF 000f2000
* orageneric10 67650000 67BA6FFF 00557000
* orasnls10 67BB0000 67BD6FFF 00027000
* oraxml10 67BE0000 67D3FFFF 00160000
* MSVCIRT 67D40000 67D4FFFF 00010000
* oran10 67D50000 67E5CFFF 0010d000
* oranl10 67E60000 67E9CFFF 0003d000
* oranldap10 67EA0000 67EB6FFF 00017000
* orannzsbb10 67EC0000 67FC8FFF 00109000
* oraldapclnt10 68030000 68082FFF 00053000
* orancrypt10 67FD0000 67FEAFFF 0001b000
* oranro10 68090000 680DBFFF 0004c000
* oranhost10 67FF0000 67FF6FFF 00007000
* orancds10 680E0000 680E5FFF 00006000
* orantns10 680F0000 680F7FFF 00008000
* orapls10 68130000 68487FFF 00358000
* oraslax10 68490000 68498FFF 00009000
* oraplp10 684A0000 686A3FFF 00204000
* orahasgen10 686B0000 6873AFFF 0008b000
* oraocr10 68740000 687A9FFF 0006a000
* oraocrb10 687B0000 687EFFFF 00040000
* orantcp10 687F0000 687FFFFF 00010000
* orasql10 68800000 68899FFF 0009a000
* comsvcs 68B10000 68C48FFF 00139000
* activeds 76DF0000 76E22FFF 00033000
* adsldpc 76DC0000 76DE6FFF 00027000
* credui 76B80000 76BADFFF 0002e000
* ATL 76A80000 76A97FFF 00018000
* msadce 68DD0000 68E27FFF 00058000
* msadcer 68D70000 68D74FFF 00005000
* sqlmap90 6A090000 6A0ADFFF 0001e000
* MAPI32 6A0C0000 6A0DEFFF 0001f000
* sqlmap90 6A0E0000 6A0E5FFF 00006000
* MSMAPI32 6A0F0000 6A24BFFF 0015c000
* MAPIR 6A570000 6A62FFFF 000c0000
* mso 6AAC0000 6B67CFFF 00bbd000
* EMSABP32 6B690000 6B6D2FFF 00043000
* MPRAPI 76CD0000 76CE8FFF 00019000
* rtutils 76E30000 76E3BFFF 0000c000
* SETUPAPI 770E0000 771E9FFF 0010a000
* netman 77840000 77882FFF 00043000
* netshell 76300000 764C0FFF 001c1000
* RASAPI32 76E90000 76ECEFFF 0003f000
* rasman 76E40000 76E51FFF 00012000
* TAPI32 76E60000 76E8EFFF 0002f000
* WZCSAPI 6B820000 6B82CFFF 0000d000
* WZCSvc 77890000 77900FFF 00071000
* WMI 76CC0000 76CC4FFF 00005000
* DHCPCSVC 76D10000 76D2EFFF 0001f000
* WTSAPI32 76F00000 76F07FFF 00008000
* WINSTA 771F0000 77200FFF 00011000
* ESENT 6B830000 6B934FFF 00105000
* contab32 6C4E0000 6C4FEFFF 0001f000
* EMSMDB32 6C510000 6C5BEFFF 000af000
* encryption 6C930000 6C936FFF 00007000
* MSVBVM60 6C940000 6CA91FFF 00152000
* Dciph32 02C40000 02C4DFFF 0000e000
* dbghelp 69530000 69644FFF 00115000
*
* Edi: 6995E178: 6533D2F8 77F5E95E 00000000 6995E2C8 00000000
77FC2190
* Esi: 00000000:
* Eax: 6995E05C: 000042AC 00000000 00000000 77E55E02 00000000
0000000C
* Ebx: 0000003F:
* Ecx: 6995E6CC: 00000000 00010007 00000000 00460044 6533D2FC
6995E08C
* Edx: 0000003D:
* Eip: 77E55E02: 10C2C95E 90909000 A1649090 00000018 C334408B
891C428B
* Ebp: 6995E0AC: 6995E0F0 021A29C4 000042AC 00000000 00000000
00000000
* SegCs: 0000001B:
* EFlags: 00000246:
* Esp: 6995E058: 00000000 000042AC 00000000 00000000 77E55E02
00000000
* SegSs: 78130023: 00000000 00000000 00000000 00000000 00000000
00000000
*
************************************************** *****************************
*
* Short Stack Dump
77E55E02 Module(kernel32+00015E02)
021A29C4 Module(sqlservr+011A29C4)
021A6880 Module(sqlservr+011A6880)
021A642D Module(sqlservr+011A642D)
02414429 Module(sqlservr+01414429)
77E99F7A Module(kernel32+00059F7A)
78138B5E Module(MSVCR80+00008B5E)
781329C1 Module(MSVCR80+000029C1)
78132A36 Module(MSVCR80+00002A36)