DSO in SQL2005: dropped

Showing posts with label dropped. Show all posts

Tuesday, March 27, 2012

Dropping article on transaction publications.

In 2005 transactional replication, The following procedure worked (without dropping the subscription) when I dropped an article from a replicated database:

Drop article: On Publication Properties, uncheck the article (table, stored procedure or function).

Create a new snapshot.

Synchronize the push subscription.

DROP the article on the Publication and Subscriber databases.

Replication still works!

However, the following article says the subscription needs to be dropped and re-created when an article is dropped from publication: http://msdn2.microsoft.com/en-us/library/ms152493.aspx (Adding Articles to and Dropping Articles from Existing Publications ). For transactional publications, articles can be dropped with no special considerations prior to subscriptions being created. If an article is dropped after one or more subscriptions is created, the subscriptions must be dropped, recreated, and synchronized.

Under what conditions is dropping the subscription and recreating it absolutely necessary? I do not want to include this extra step.

Linda

Hi Linda, the documentation is correct. If you try to drop the artice via TSQL stored proc sp_droparticle, it will raise an error saying you you cannot drop the article because you have existing subscription. What I think BOL should say is that "the subscriptions to the article must be dropped before you can drop the article".

This is what the UI is doing - it will first drop the article from all existing subscriptions (via sp_dropsubscription), then drop the article from the publication and then invalidate the snapshot. So for existing subscriptions, there isn't anything else you need to do, but if you add a new subscription afterwards, you have to regenerate a new snapshot.

I hope this is a little more clear.

Sunday, March 25, 2012

Dropping a Database

Hi all,
Is there a log entry that would identify the user that dropped a database
out of my server? I have two people here on my dev server that are at each
others throat because they are blaming each other.
I would like to restore peace here.
TIA,
Joe
Joe,
Might be able to determine that *post* op with auditing by using a
third-party transaction log viewer. I.e.,
Trial editions available for download:
http://www.lumigent.com/go/google/
or
http://www.red-gate.com/products/SQL...scue/index.htm
HTH
Jerry
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:08444183-0540-4A4B-81A4-FD7C01D10838@.microsoft.com...
> Hi all,
> Is there a log entry that would identify the user that dropped a database
> out of my server? I have two people here on my dev server that are at
> each
> others throat because they are blaming each other.
> I would like to restore peace here.
> TIA,
> Joe
>

Dropping a Database

Hi all,
Is there a log entry that would identify the user that dropped a database
out of my server? I have two people here on my dev server that are at each
others throat because they are blaming each other.
I would like to restore peace here.
TIA,
JoeJoe,
Might be able to determine that *post* op with auditing by using a
third-party transaction log viewer. I.e.,
Trial editions available for download:
http://www.lumigent.com/go/google/
or
http://www.red-gate.com/products/SQ...escue/index.htm
HTH
Jerry
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:08444183-0540-4A4B-81A4-FD7C01D10838@.microsoft.com...
> Hi all,
> Is there a log entry that would identify the user that dropped a database
> out of my server? I have two people here on my dev server that are at
> each
> others throat because they are blaming each other.
> I would like to restore peace here.
> TIA,
> Joe
>

Dropping a Database

Hi all,
Is there a log entry that would identify the user that dropped a database
out of my server? I have two people here on my dev server that are at each
others throat because they are blaming each other.
I would like to restore peace here.
TIA,
JoeJoe,
Might be able to determine that *post* op with auditing by using a
third-party transaction log viewer. I.e.,
Trial editions available for download:
http://www.lumigent.com/go/google/
or
http://www.red-gate.com/products/SQL_Log_Rescue/index.htm
HTH
Jerry
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:08444183-0540-4A4B-81A4-FD7C01D10838@.microsoft.com...
> Hi all,
> Is there a log entry that would identify the user that dropped a database
> out of my server? I have two people here on my dev server that are at
> each
> others throat because they are blaming each other.
> I would like to restore peace here.
> TIA,
> Joe
>sql

Droppin rule in SQL Server 2000

I am trying to drop a rule but I am getting the following error message:
"The rule 'dbo.RULE_NAME' cannot be dropped because it is bound to one or
more column".
I tried to run sp_unbindrule but it gave me this error message: The data
type "RULE_NAME" does not exist.
How can I resolve this? Thank you in advanceA rule can be bound to either a particular column or to a user defined
datatype. What is the name of the rule? Also what is the name the table and
the column?
Can you post the syntax you were using?
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"David" <David@.discussions.microsoft.com> wrote in message
news:451299E3-867E-44C6-B110-55DE3F8D4517@.microsoft.com...
>I am trying to drop a rule but I am getting the following error message:
> "The rule 'dbo.RULE_NAME' cannot be dropped because it is bound to one or
> more column".
> I tried to run sp_unbindrule but it gave me this error message: The data
> type "RULE_NAME" does not exist.
> How can I resolve this? Thank you in advance|||Thanks for your response Kalen.
I could only get some info using sp_help stored procedure. It says owner is
dbo and type = rule.
It is using the table tblEmpType. I am using the syntax as
DROP RULE [dbo].[Rule_Sim_App]
I forgot to mention this in my previous post. When I check in EM -->
Database --> Rules tab, I do not see any rule created by this name
Rule_Sim_App.
Please advise.
"Kalen Delaney" wrote:
> A rule can be bound to either a particular column or to a user defined
> datatype. What is the name of the rule? Also what is the name the table and
> the column?
> Can you post the syntax you were using?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "David" <David@.discussions.microsoft.com> wrote in message
> news:451299E3-867E-44C6-B110-55DE3F8D4517@.microsoft.com...
> >I am trying to drop a rule but I am getting the following error message:
> >
> > "The rule 'dbo.RULE_NAME' cannot be dropped because it is bound to one or
> > more column".
> >
> > I tried to run sp_unbindrule but it gave me this error message: The data
> > type "RULE_NAME" does not exist.
> >
> > How can I resolve this? Thank you in advance
>
>|||What syntax are you using to try to unbind the rule
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"David" <David@.discussions.microsoft.com> wrote in message
news:7FF32AE3-ABFF-4BFF-8B7E-7CA8BC2ADB0E@.microsoft.com...
> Thanks for your response Kalen.
> I could only get some info using sp_help stored procedure. It says owner
> is
> dbo and type = rule.
> It is using the table tblEmpType. I am using the syntax as
> DROP RULE [dbo].[Rule_Sim_App]
> I forgot to mention this in my previous post. When I check in EM -->
> Database --> Rules tab, I do not see any rule created by this name
> Rule_Sim_App.
> Please advise.
> "Kalen Delaney" wrote:
>> A rule can be bound to either a particular column or to a user defined
>> datatype. What is the name of the rule? Also what is the name the table
>> and
>> the column?
>> Can you post the syntax you were using?
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.solidqualitylearning.com
>>
>> "David" <David@.discussions.microsoft.com> wrote in message
>> news:451299E3-867E-44C6-B110-55DE3F8D4517@.microsoft.com...
>> >I am trying to drop a rule but I am getting the following error message:
>> >
>> > "The rule 'dbo.RULE_NAME' cannot be dropped because it is bound to one
>> > or
>> > more column".
>> >
>> > I tried to run sp_unbindrule but it gave me this error message: The
>> > data
>> > type "RULE_NAME" does not exist.
>> >
>> > How can I resolve this? Thank you in advance
>>|||sp_unbindrule 'Rule_Sim_App' and it gives me this error message: The
data type "RULE_NAME" does not exist.
Thanks again
----
"Kalen Delaney" wrote:
> What syntax are you using to try to unbind the rule
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "David" <David@.discussions.microsoft.com> wrote in message
> news:7FF32AE3-ABFF-4BFF-8B7E-7CA8BC2ADB0E@.microsoft.com...
> > Thanks for your response Kalen.
> >
> > I could only get some info using sp_help stored procedure. It says owner
> > is
> > dbo and type = rule.
> >
> > It is using the table tblEmpType. I am using the syntax as
> > DROP RULE [dbo].[Rule_Sim_App]
> >
> > I forgot to mention this in my previous post. When I check in EM -->
> > Database --> Rules tab, I do not see any rule created by this name
> > Rule_Sim_App.
> >
> > Please advise.
> >
> > "Kalen Delaney" wrote:
> >
> >> A rule can be bound to either a particular column or to a user defined
> >> datatype. What is the name of the rule? Also what is the name the table
> >> and
> >> the column?
> >> Can you post the syntax you were using?
> >>
> >> --
> >> HTH
> >> Kalen Delaney, SQL Server MVP
> >> www.solidqualitylearning.com
> >>
> >>
> >> "David" <David@.discussions.microsoft.com> wrote in message
> >> news:451299E3-867E-44C6-B110-55DE3F8D4517@.microsoft.com...
> >> >I am trying to drop a rule but I am getting the following error message:
> >> >
> >> > "The rule 'dbo.RULE_NAME' cannot be dropped because it is bound to one
> >> > or
> >> > more column".
> >> >
> >> > I tried to run sp_unbindrule but it gave me this error message: The
> >> > data
> >> > type "RULE_NAME" does not exist.
> >> >
> >> > How can I resolve this? Thank you in advance
> >>
> >>
> >>
>
>|||Hi David
Please check the syntax in BOL for sp_unbindrule. The parameter is what you
are unbinding from, either a user defined type or a 'tablename.columnname'.
Since there can only be one rule bound to anything, there is no ambiguity.
The message is indicating that it thinks Rule_Sim_App is a type name, since
it is not in the format of a table and column.
You need to find the name of the table and the column the rule is bound to,
and then use them in the sp_unbindrule statement.
This query might give you a start:
select name as col_name, object_name(id) as table_name from syscolumns
where domain = object_id ('Rule_Sim_App')
Once you have the table and column, you can run sp_unbindrule:
exec sp_unbindrule 'tablename.columnname'
Again, you do not use the name of the rule when calling sp_unbindrule.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"David" <David@.discussions.microsoft.com> wrote in message
news:3A5A21B4-D7BD-48C2-A695-61A70BD3D04E@.microsoft.com...
> sp_unbindrule 'Rule_Sim_App' and it gives me this error message: The
> data type "RULE_NAME" does not exist.
> Thanks again
> ----
> "Kalen Delaney" wrote:
>> What syntax are you using to try to unbind the rule
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.solidqualitylearning.com
>>
>> "David" <David@.discussions.microsoft.com> wrote in message
>> news:7FF32AE3-ABFF-4BFF-8B7E-7CA8BC2ADB0E@.microsoft.com...
>> > Thanks for your response Kalen.
>> >
>> > I could only get some info using sp_help stored procedure. It says
>> > owner
>> > is
>> > dbo and type = rule.
>> >
>> > It is using the table tblEmpType. I am using the syntax as
>> > DROP RULE [dbo].[Rule_Sim_App]
>> >
>> > I forgot to mention this in my previous post. When I check in EM -->
>> > Database --> Rules tab, I do not see any rule created by this name
>> > Rule_Sim_App.
>> >
>> > Please advise.
>> >
>> > "Kalen Delaney" wrote:
>> >
>> >> A rule can be bound to either a particular column or to a user defined
>> >> datatype. What is the name of the rule? Also what is the name the
>> >> table
>> >> and
>> >> the column?
>> >> Can you post the syntax you were using?
>> >>
>> >> --
>> >> HTH
>> >> Kalen Delaney, SQL Server MVP
>> >> www.solidqualitylearning.com
>> >>
>> >>
>> >> "David" <David@.discussions.microsoft.com> wrote in message
>> >> news:451299E3-867E-44C6-B110-55DE3F8D4517@.microsoft.com...
>> >> >I am trying to drop a rule but I am getting the following error
>> >> >message:
>> >> >
>> >> > "The rule 'dbo.RULE_NAME' cannot be dropped because it is bound to
>> >> > one
>> >> > or
>> >> > more column".
>> >> >
>> >> > I tried to run sp_unbindrule but it gave me this error message: The
>> >> > data
>> >> > type "RULE_NAME" does not exist.
>> >> >
>> >> > How can I resolve this? Thank you in advance
>> >>
>> >>
>> >>
>>|||Bull's eye Kalen. I really appreciate it.
I have asked the developer to use CHECK constraint instead of using bind
rule objects. Is check constraint easy to administer than bind rule objects?
Thanks again ...
"Kalen Delaney" wrote:
> Hi David
> Please check the syntax in BOL for sp_unbindrule. The parameter is what you
> are unbinding from, either a user defined type or a 'tablename.columnname'.
> Since there can only be one rule bound to anything, there is no ambiguity.
> The message is indicating that it thinks Rule_Sim_App is a type name, since
> it is not in the format of a table and column.
> You need to find the name of the table and the column the rule is bound to,
> and then use them in the sp_unbindrule statement.
> This query might give you a start:
> select name as col_name, object_name(id) as table_name from syscolumns
> where domain = object_id ('Rule_Sim_App')
> Once you have the table and column, you can run sp_unbindrule:
> exec sp_unbindrule 'tablename.columnname'
> Again, you do not use the name of the rule when calling sp_unbindrule.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "David" <David@.discussions.microsoft.com> wrote in message
> news:3A5A21B4-D7BD-48C2-A695-61A70BD3D04E@.microsoft.com...
> > sp_unbindrule 'Rule_Sim_App' and it gives me this error message: The
> > data type "RULE_NAME" does not exist.
> >
> > Thanks again
> > ----
> >
> > "Kalen Delaney" wrote:
> >
> >> What syntax are you using to try to unbind the rule
> >>
> >> --
> >> HTH
> >> Kalen Delaney, SQL Server MVP
> >> www.solidqualitylearning.com
> >>
> >>
> >> "David" <David@.discussions.microsoft.com> wrote in message
> >> news:7FF32AE3-ABFF-4BFF-8B7E-7CA8BC2ADB0E@.microsoft.com...
> >> > Thanks for your response Kalen.
> >> >
> >> > I could only get some info using sp_help stored procedure. It says
> >> > owner
> >> > is
> >> > dbo and type = rule.
> >> >
> >> > It is using the table tblEmpType. I am using the syntax as
> >> > DROP RULE [dbo].[Rule_Sim_App]
> >> >
> >> > I forgot to mention this in my previous post. When I check in EM -->
> >> > Database --> Rules tab, I do not see any rule created by this name
> >> > Rule_Sim_App.
> >> >
> >> > Please advise.
> >> >
> >> > "Kalen Delaney" wrote:
> >> >
> >> >> A rule can be bound to either a particular column or to a user defined
> >> >> datatype. What is the name of the rule? Also what is the name the
> >> >> table
> >> >> and
> >> >> the column?
> >> >> Can you post the syntax you were using?
> >> >>
> >> >> --
> >> >> HTH
> >> >> Kalen Delaney, SQL Server MVP
> >> >> www.solidqualitylearning.com
> >> >>
> >> >>
> >> >> "David" <David@.discussions.microsoft.com> wrote in message
> >> >> news:451299E3-867E-44C6-B110-55DE3F8D4517@.microsoft.com...
> >> >> >I am trying to drop a rule but I am getting the following error
> >> >> >message:
> >> >> >
> >> >> > "The rule 'dbo.RULE_NAME' cannot be dropped because it is bound to
> >> >> > one
> >> >> > or
> >> >> > more column".
> >> >> >
> >> >> > I tried to run sp_unbindrule but it gave me this error message: The
> >> >> > data
> >> >> > type "RULE_NAME" does not exist.
> >> >> >
> >> >> > How can I resolve this? Thank you in advance
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||Good idea. Bound rules will be going away in a future version.
Check constraints may be easier to keep track of since they are attached to
a single table.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"David" <David@.discussions.microsoft.com> wrote in message
news:65F55A05-C4DC-4E08-803A-FE527C4506A4@.microsoft.com...
> Bull's eye Kalen. I really appreciate it.
> I have asked the developer to use CHECK constraint instead of using bind
> rule objects. Is check constraint easy to administer than bind rule
> objects?
> Thanks again ...
>
> "Kalen Delaney" wrote:
>> Hi David
>> Please check the syntax in BOL for sp_unbindrule. The parameter is what
>> you
>> are unbinding from, either a user defined type or a
>> 'tablename.columnname'.
>> Since there can only be one rule bound to anything, there is no
>> ambiguity.
>> The message is indicating that it thinks Rule_Sim_App is a type name,
>> since
>> it is not in the format of a table and column.
>> You need to find the name of the table and the column the rule is bound
>> to,
>> and then use them in the sp_unbindrule statement.
>> This query might give you a start:
>> select name as col_name, object_name(id) as table_name from syscolumns
>> where domain = object_id ('Rule_Sim_App')
>> Once you have the table and column, you can run sp_unbindrule:
>> exec sp_unbindrule 'tablename.columnname'
>> Again, you do not use the name of the rule when calling sp_unbindrule.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.solidqualitylearning.com
>>
>> "David" <David@.discussions.microsoft.com> wrote in message
>> news:3A5A21B4-D7BD-48C2-A695-61A70BD3D04E@.microsoft.com...
>> > sp_unbindrule 'Rule_Sim_App' and it gives me this error message: The
>> > data type "RULE_NAME" does not exist.
>> >
>> > Thanks again
>> > ----
>> >
>> > "Kalen Delaney" wrote:
>> >
>> >> What syntax are you using to try to unbind the rule
>> >>
>> >> --
>> >> HTH
>> >> Kalen Delaney, SQL Server MVP
>> >> www.solidqualitylearning.com
>> >>
>> >>
>> >> "David" <David@.discussions.microsoft.com> wrote in message
>> >> news:7FF32AE3-ABFF-4BFF-8B7E-7CA8BC2ADB0E@.microsoft.com...
>> >> > Thanks for your response Kalen.
>> >> >
>> >> > I could only get some info using sp_help stored procedure. It says
>> >> > owner
>> >> > is
>> >> > dbo and type = rule.
>> >> >
>> >> > It is using the table tblEmpType. I am using the syntax as
>> >> > DROP RULE [dbo].[Rule_Sim_App]
>> >> >
>> >> > I forgot to mention this in my previous post. When I check in EM -->
>> >> > Database --> Rules tab, I do not see any rule created by this name
>> >> > Rule_Sim_App.
>> >> >
>> >> > Please advise.
>> >> >
>> >> > "Kalen Delaney" wrote:
>> >> >
>> >> >> A rule can be bound to either a particular column or to a user
>> >> >> defined
>> >> >> datatype. What is the name of the rule? Also what is the name the
>> >> >> table
>> >> >> and
>> >> >> the column?
>> >> >> Can you post the syntax you were using?
>> >> >>
>> >> >> --
>> >> >> HTH
>> >> >> Kalen Delaney, SQL Server MVP
>> >> >> www.solidqualitylearning.com
>> >> >>
>> >> >>
>> >> >> "David" <David@.discussions.microsoft.com> wrote in message
>> >> >> news:451299E3-867E-44C6-B110-55DE3F8D4517@.microsoft.com...
>> >> >> >I am trying to drop a rule but I am getting the following error
>> >> >> >message:
>> >> >> >
>> >> >> > "The rule 'dbo.RULE_NAME' cannot be dropped because it is bound
>> >> >> > to
>> >> >> > one
>> >> >> > or
>> >> >> > more column".
>> >> >> >
>> >> >> > I tried to run sp_unbindrule but it gave me this error message:
>> >> >> > The
>> >> >> > data
>> >> >> > type "RULE_NAME" does not exist.
>> >> >> >
>> >> >> > How can I resolve this? Thank you in advance
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>

Dropped Table

Help!! I dropped two tables, what a stupid!!.
However, I have a Complete Backup from my
database "mydata" created at june, 24th 18:21 and after
dropped those tables I create a new complete backup that
was yesterday june 25th 18:55
I restored my backup of 24th on another Sql_server
(hopefully I was installing a new server to change this
one I am using now) but I can't restore the transaction
log of the 25th backup because It says something like, I
need another transaction log, but I don't have it!!.
So I have my data until 24th 18:21 but I need to recover
all the data!!
Please HELP ME!!!!
You should have made a transaction log backup on the 25:th instead of a database backup.
In SQL Server, you can restore a database backup and then a number of transaction log backups. You
cannot skip over a log backup, as all log records need to be applied one after the other. This seems
to be your situation, that you are missing a log backup. You can look in the backup history tables
in msdb to see when the log backups were performed and to where. These tables has a rather complex
structure, though. Also, there are some operations that just cut the log without doing a backup.
This will also infer with a log backup restore sequence. I suggest you open a case with MS PSS so
they can help you through this situation as this is very difficult to do over a newsgroup...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert Duval" <rduval@.discussions.microsoft.com> wrote in message
news:21abc01c45b2f$da9b2e10$a101280a@.phx.gbl...
> Help!! I dropped two tables, what a stupid!!.
> However, I have a Complete Backup from my
> database "mydata" created at june, 24th 18:21 and after
> dropped those tables I create a new complete backup that
> was yesterday june 25th 18:55
> I restored my backup of 24th on another Sql_server
> (hopefully I was installing a new server to change this
> one I am using now) but I can't restore the transaction
> log of the 25th backup because It says something like, I
> need another transaction log, but I don't have it!!.
> So I have my data until 24th 18:21 but I need to recover
> all the data!!
> Please HELP ME!!!!
sql

Dropped Table

Help!! I dropped two tables, what a stupid!!.
However, I have a Complete Backup from my
database "mydata" created at june, 24th 18:21 and after
dropped those tables I create a new complete backup that
was yesterday june 25th 18:55
I restored my backup of 24th on another Sql_server
(hopefully I was installing a new server to change this
one I am using now) but I can't restore the transaction
log of the 25th backup because It says something like, I
need another transaction log, but I don't have it!!.
So I have my data until 24th 18:21 but I need to recover
all the data!!
Please HELP ME!!!!You should have made a transaction log backup on the 25:th instead of a database backup.
In SQL Server, you can restore a database backup and then a number of transaction log backups. You
cannot skip over a log backup, as all log records need to be applied one after the other. This seems
to be your situation, that you are missing a log backup. You can look in the backup history tables
in msdb to see when the log backups were performed and to where. These tables has a rather complex
structure, though. Also, there are some operations that just cut the log without doing a backup.
This will also infer with a log backup restore sequence. I suggest you open a case with MS PSS so
they can help you through this situation as this is very difficult to do over a newsgroup...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert Duval" <rduval@.discussions.microsoft.com> wrote in message
news:21abc01c45b2f$da9b2e10$a101280a@.phx.gbl...
> Help!! I dropped two tables, what a stupid!!.
> However, I have a Complete Backup from my
> database "mydata" created at june, 24th 18:21 and after
> dropped those tables I create a new complete backup that
> was yesterday june 25th 18:55
> I restored my backup of 24th on another Sql_server
> (hopefully I was installing a new server to change this
> one I am using now) but I can't restore the transaction
> log of the 25th backup because It says something like, I
> need another transaction log, but I don't have it!!.
> So I have my data until 24th 18:21 but I need to recover
> all the data!!
> Please HELP ME!!!!

Dropped Table

Help!! I dropped two tables, what a stupid!!.
However, I have a Complete Backup from my
database "mydata" created at june, 24th 18:21 and after
dropped those tables I create a new complete backup that
was yesterday june 25th 18:55
I restored my backup of 24th on another Sql_server
(hopefully I was installing a new server to change this
one I am using now) but I can't restore the transaction
log of the 25th backup because It says something like, I
need another transaction log, but I don't have it!!.
So I have my data until 24th 18:21 but I need to recover
all the data!!
Please HELP ME!!!!You should have made a transaction log backup on the 25:th instead of a data
base backup.
In SQL Server, you can restore a database backup and then a number of transa
ction log backups. You
cannot skip over a log backup, as all log records need to be applied one aft
er the other. This seems
to be your situation, that you are missing a log backup. You can look in the
backup history tables
in msdb to see when the log backups were performed and to where. These table
s has a rather complex
structure, though. Also, there are some operations that just cut the log wit
hout doing a backup.
This will also infer with a log backup restore sequence. I suggest you open
a case with MS PSS so
they can help you through this situation as this is very difficult to do ove
r a newsgroup...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert Duval" <rduval@.discussions.microsoft.com> wrote in message
news:21abc01c45b2f$da9b2e10$a101280a@.phx
.gbl...
> Help!! I dropped two tables, what a stupid!!.
> However, I have a Complete Backup from my
> database "mydata" created at june, 24th 18:21 and after
> dropped those tables I create a new complete backup that
> was yesterday june 25th 18:55
> I restored my backup of 24th on another Sql_server
> (hopefully I was installing a new server to change this
> one I am using now) but I can't restore the transaction
> log of the 25th backup because It says something like, I
> need another transaction log, but I don't have it!!.
> So I have my data until 24th 18:21 but I need to recover
> all the data!!
> Please HELP ME!!!!

Dropped connections, but open on the SQL Server...

I have a frustrating issue that hopefully someone can help with.
I have a few remote users that connect to the sql server through 1433 and
sql authentication (the ISA firewall only allows their IP addresses to
connect). They connect through an ODBC connection with no Idle timeout.
These users are getting their connections closed but I look at the EM and
the connection is still open. If I have them connect via VPN first,
everything works fine - the connection is never closed. Do you know why a
standard ODBC connection would close itself only if it's not behind a VPN?
I can't figure it out for the life of me!! There must be some timeout
parameter somewhere that I have set, but I can't find it...
Any ideas would be greatly appreciated!
Regards,
Brett Wickard
Hi Brett,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood you would like to close ODBC
connection when the user terminate the connection. If I have misunderstood
your concern, please feel free to point it out.
First of all, please use sp_who in the Query Analyzer to make sure the
connnection from these remote customer remain alive.
Secondly, how does your customer connect to SQL Server? If you utilize ADO
in your VB application to access the SQL Server, please try the following
sample code.
Dim cn As New ADODB.connection
Dim cmd As New ADODB.Command
cn.ConnectionTimeout = 45
cmd.CommandTimeout = 15
cn.close
For more information, please refer to Microsoft SQL Server Books Online
with the keywords 'ADO' and 'connectiontimeout' or 'commandtimeout' as the
search topics.
Last but not the least, the following KB describes how to define the
orphaned connection and set OLE DB Provider for ODBC's timeout settings.
INFO: OLE DB Session Pooling Timeout Configuration
http://support.microsoft.com/kb/Q237977
INF: How to Troubleshoot Orphaned Connections in SQL Server
http://support.microsoft.com/kb/Q137983
INFO: Frequently Asked Questions About ODBC Connection Pooling
http://support.microsoft.com/kb/Q169470
Resource Pooling
http://msdn.microsoft.com/library/de...us/oledb/htm/o
ledbresourcepooling.asp
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

Dropped connections, but open on the SQL Server...

I have a frustrating issue that hopefully someone can help with.
I have a few remote users that connect to the sql server through 1433 and
sql authentication (the ISA firewall only allows their IP addresses to
connect). They connect through an ODBC connection with no Idle timeout.
These users are getting their connections closed but I look at the EM and
the connection is still open. If I have them connect via VPN first,
everything works fine - the connection is never closed. Do you know why a
standard ODBC connection would close itself only if it's not behind a VPN?
I can't figure it out for the life of me!! There must be some timeout
parameter somewhere that I have set, but I can't find it...
Any ideas would be greatly appreciated!
Regards,
Brett WickardHi Brett,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood you would like to close ODBC
connection when the user terminate the connection. If I have misunderstood
your concern, please feel free to point it out.
First of all, please use sp_who in the Query Analyzer to make sure the
connnection from these remote customer remain alive.
Secondly, how does your customer connect to SQL Server? If you utilize ADO
in your VB application to access the SQL Server, please try the following
sample code.
--
Dim cn As New ADODB.connection
Dim cmd As New ADODB.Command
cn.ConnectionTimeout = 45
cmd.CommandTimeout = 15
cn.close
--
For more information, please refer to Microsoft SQL Server Books Online
with the keywords 'ADO' and 'connectiontimeout' or 'commandtimeout' as the
search topics.
Last but not the least, the following KB describes how to define the
orphaned connection and set OLE DB Provider for ODBC's timeout settings.
INFO: OLE DB Session Pooling Timeout Configuration
http://support.microsoft.com/kb/Q237977
INF: How to Troubleshoot Orphaned Connections in SQL Server
http://support.microsoft.com/kb/Q137983
INFO: Frequently Asked Questions About ODBC Connection Pooling
http://support.microsoft.com/kb/Q169470
Resource Pooling
http://msdn.microsoft.com/library/d...-us/oledb/htm/o
ledbresourcepooling.asp
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Dropped column being "dropped" before drop ??

(SQL Server 2000 SP3, Developer Edition, Windows XP Professional)
I am getting an error "Invalid column name 'sys_login_name'." when running
the following query. The query is a minor conversion of a table between
versions. One column (userid_fk) had been added in a previous batch. In
this batch several columns are being dropped IF they exist. In the first
column 'sys_login_name', before it is dropped, data is pulled in from
another table (sy_user) before the sys_user.sys_login_name is dropped. I've
used col_length() as a quick way to detect if a column exists (returns null
if it doesn't exist). In the code below, note the section where "if
col_length('sys_user', 'sys_login_name') is not null" which means it only
gets executed when sys_login_name DOES exist. The UPDATE statement in that
IF block pulls data into sys_user from sy_user based upon the sys_login_name
field. The next statement then DROPS the sys_login_name field. In Query
Analyzer, I'm getting an "Invalid column name 'sys_login_name'" error that
points back to the UPDATE statement, but the rest of the batch is executing.
The output from Query Analyzer is:
=====OUTPUT
START===================================
====================================
====
doing v2->v3 on sys_user
updating sys_user
dropping sys_login_name
dropping other columns
Server: Msg 207, Level 16, State 3, Line 14
Invalid column name 'sys_login_name'.
=====OUTPUT
END=====================================
====================================
==
Oddly enough, the error is after the PRINT statement's output, but I've seen
that asynchronous-ness (?) of PRINT and error output enough before to not be
alarmed.
Here's the batch:
=====BATCH
START===================================
====================================
====
-- v2->v3: Check if sy integration changes need to be done STEP 2: convert
and drop fields
if dbo.fn_sy_get_table_version(N'sys_user') = 2
begin
print 'doing v2->v3 on sys_user'
-- if sys_login_name exists, pull data from sy_user for conversion
-- and drop sys_login_name
if col_length('sys_user', 'sys_login_name') is not null
begin
-- fill in userid_fk from sy_user.userid_pk via login name
-- and set password to 'test' for all accounts
-- before dropping login name; entry may not exist in sy_user
print 'updating sys_user'
update sys_user
set userid_fk = isnull(SY.userid_pk, 0),
sys_password = '098f6bcd4621d373cade4e832627b4f6'
from sys_user SYS left join sy_user SY on SYS.sys_login_name =
SY.login
print 'dropping sys_login_name'
alter table sys_user drop column sys_login_name
end
print 'dropping other columns'
-- drop sys_user_first if it exists
if col_length('sys_user', 'sys_user_first') is not null
alter table sys_user drop column sys_user_first
-- drop sys_user_last if it exists
if col_length('sys_user', 'sys_user_last') is not null
alter table sys_user drop column sys_user_last
-- drop timestamp if it exists
if col_length('sys_user', 'timestamp') is not null
alter table sys_user drop column [timestamp]
exec sp_sy_addextprops N'PPD_Version', 3, N'USER', N'dbo', N'TABLE',
N'sys_user'
end
go
=====BATCH
END=====================================
===================================
Here's a sample I did to test to see if ALTER TABLE DROP COLUMN somehow gets
executed before the UPDATE, but it doesn't:
=====SAMPLE
START===================================
====================================
=
create table testdrop
(
ident int identity(100,1) not null primary key,
col1 int null,
col2 int null
)
go
insert testdrop (col1, col2) values (1,2)
update testdrop set col2 = 22 where col1=1
select * from testdrop
alter table testdrop drop column col2
select * from testdrop
go
drop table testdrop
go
=====SAMPLE
END=====================================
===================================
Thanks for any help!
Mike JansenOK, I was able to reproduce the problem by enhancing my sample:
========= BEGIN SAMPLE ================
create table testdrop
(
ident int identity(100,1) not null primary key,
col1 int null,
col2 int null
)
go
create table testdrop2
(
pk int identity(100,1) not null primary key,
col1 int null
)
go
insert testdrop2 (col1) values (1)
insert testdrop2 (col1) values (2)
insert testdrop (col1, col2) values (1,0)
insert testdrop (col1, col2) values (2,0)
insert testdrop (col1, col2) values (3,0)
select * from testdrop
update testdrop
set col2 = T2.pk
from testdrop T1 left join testdrop2 T2 on T1.col1=T2.col1
select * from testdrop
--go
alter table testdrop drop column col2
select * from testdrop
go
drop table testdrop
drop table testdrop2
go
========= END SAMPLE ====================
Note that if you uncomment the one GO statement, it works. If the ALTER
TABLE DROP COLUMN is in the same batch as the UPDATE with the JOIN in the
FROM clause, it has the error.
Thanks,
Mike|||Does anyone have any idea about the following problem with the UPDATE
statement not working (get "Invalid Column" error) when you use a column in
the UPDATE's FROM clause (in a JOIN) and then drop that column via ALTER
TABLE in the same batch?
I can work around the problem, but I'd like to know if this is a SQL bug or
if I am ignorant of something fundamental in SQL Server (working with the
guys I work with, I had to qualify what I might be ignorant about or they
might pipe in all too quickly to confirm that I'm just ignorant <g> )
Thanks,
Mike
"Mike Jansen" <mjansen_nntp@.mail.com> wrote in message
news:ek5KSIaVFHA.1508@.tk2msftngp13.phx.gbl...
> OK, I was able to reproduce the problem by enhancing my sample:
> ========= BEGIN SAMPLE ================
> create table testdrop
> (
> ident int identity(100,1) not null primary key,
> col1 int null,
> col2 int null
> )
> go
> create table testdrop2
> (
> pk int identity(100,1) not null primary key,
> col1 int null
> )
> go
> insert testdrop2 (col1) values (1)
> insert testdrop2 (col1) values (2)
> insert testdrop (col1, col2) values (1,0)
> insert testdrop (col1, col2) values (2,0)
> insert testdrop (col1, col2) values (3,0)
> select * from testdrop
> update testdrop
> set col2 = T2.pk
> from testdrop T1 left join testdrop2 T2 on T1.col1=T2.col1
> select * from testdrop
> --go
> alter table testdrop drop column col2
> select * from testdrop
> go
> drop table testdrop
> drop table testdrop2
> go
> ========= END SAMPLE ====================
> Note that if you uncomment the one GO statement, it works. If the ALTER
> TABLE DROP COLUMN is in the same batch as the UPDATE with the JOIN in the
> FROM clause, it has the error.
> Thanks,
> Mike
>|||Dropping the column forces a recompile of the entire batch. That's why
you get an error. It's the expected behaviour.
For this and other reasons try to keep DDL and DML code entirely
separate. Put your ALTER statements in a separate batch.
David Portas
SQL Server MVP
--|||1. If it's recompiling the batch because of the DDL, why does my "simple
sample" work where I have an UPDATE with no FROM clause but I SET the column
and then drop it in the next line with an ALTER TABLE? If it were
recompiling the batch, I'd think that it would fail on that as well.
Here's a re-post of my simple sample that works:
==== BEGIN SAMPLE ==============
create table testdrop
(
ident int identity(100,1) not null primary key,
col1 int null,
col2 int null
)
go
insert testdrop (col1, col2) values (1,2)
update testdrop set col2 = 22 where col1=1
select * from testdrop
alter table testdrop drop column col2
select * from testdrop
go
drop table testdrop
go
==== END SAMPLE ==============
2. What are the other reasons for putting DDL and DML in separate batches
(besides the re-compile issue)?
Thanks for your help,
Mike
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1115815055.958304.192040@.g47g2000cwa.googlegroups.com...
> Dropping the column forces a recompile of the entire batch. That's why
> you get an error. It's the expected behaviour.
> For this and other reasons try to keep DDL and DML code entirely
> separate. Put your ALTER statements in a separate batch.
> --
> David Portas
> SQL Server MVP
> --
>|||I did a little research and found the answer to question #2 (What are the
other reasons for putting DDL and DML in separate batches - besides the
re-compile issue): It's related to the re-compile issue: performance. The
recompilation obviously causes performance issues. Since the compilation of
the batches is probably 1% or less of the time in the scenario I'm talking
about and it's a once-in-a-while script, that isn't really a significant
factor. I did end up changing my script though to put the DDL and DML in
separate batches since I'm still getting the "invalid column" error -- which
probably is related to the re-compiling (perhaps in the simple example
something is optimized in such a way that the batch didn't need to be
re-compiled ')
"Mike Jansen" <mjansen_nntp@.mail.com> wrote in message
news:O1nV6niVFHA.2420@.TK2MSFTNGP12.phx.gbl...
> 1. If it's recompiling the batch because of the DDL, why does my "simple
> sample" work where I have an UPDATE with no FROM clause but I SET the
column
> and then drop it in the next line with an ALTER TABLE? If it were
> recompiling the batch, I'd think that it would fail on that as well.
> Here's a re-post of my simple sample that works:
> ==== BEGIN SAMPLE ==============
> create table testdrop
> (
> ident int identity(100,1) not null primary key,
> col1 int null,
> col2 int null
> )
> go
> insert testdrop (col1, col2) values (1,2)
> update testdrop set col2 = 22 where col1=1
> select * from testdrop
> alter table testdrop drop column col2
> select * from testdrop
> go
> drop table testdrop
> go
> ==== END SAMPLE ==============
> 2. What are the other reasons for putting DDL and DML in separate batches
> (besides the re-compile issue)?
>
> Thanks for your help,
> Mike
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1115815055.958304.192040@.g47g2000cwa.googlegroups.com...
>

Thursday, March 22, 2012

Drop User

When I attempt to drop a user I receive an error that the user objects
objects in the SQL 2000 DB, and the login could not be dropped. Does anyone
have a workaround to this? How can you get around this. Perhaps changing
ownership globally, and then deleteing the login? Any Ideas?Hi,
You can not drop the user if the user owns any objects.
How to change the object owner:
sp_changeobjectowner 'obj_name','new_owner'
You can also change the owner by updating the sysobjects tables
update sysobjects
set uid=<new uid>
where uid='uid for the user you need to drop'
Thanks
Hari
MCDBA
"Casey" <casey.canales@.bestsoftware.com> wrote in message
news:u0nvimwIEHA.2688@.tk2msftngp13.phx.gbl...
> When I attempt to drop a user I receive an error that the user objects
> objects in the SQL 2000 DB, and the login could not be dropped. Does
anyone
> have a workaround to this? How can you get around this. Perhaps changing
> ownership globally, and then deleteing the login? Any Ideas?
>|||> You can also change the owner by updating the sysobjects tables
> update sysobjects
> set uid=<new uid>
> where uid='uid for the user you need to drop'
Hari, although this may work, Casey should probably use the supported method
(sp_changedbowner).
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:ePdLZpwIEHA.3556@.TK2MSFTNGP10.phx.gbl...
> Hi,
> You can not drop the user if the user owns any objects.
> How to change the object owner:
> sp_changeobjectowner 'obj_name','new_owner'
> You can also change the owner by updating the sysobjects tables
> update sysobjects
> set uid=<new uid>
> where uid='uid for the user you need to drop'
> Thanks
> Hari
> MCDBA
>
> "Casey" <casey.canales@.bestsoftware.com> wrote in message
> news:u0nvimwIEHA.2688@.tk2msftngp13.phx.gbl...
> > When I attempt to drop a user I receive an error that the user objects
> > objects in the SQL 2000 DB, and the login could not be dropped. Does
> anyone
> > have a workaround to this? How can you get around this. Perhaps
changing
> > ownership globally, and then deleteing the login? Any Ideas?
> >
> >
>|||Hi,
I agree with Dan. I just mentioned various possibilities to change the
object owner.
Casey,
Please use sp_changeobjectowner system stored procedure to change the object
owners. This is always safe.
Updating system tables is always risky.
Thanks
Hari
MCDBA
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:OiJAsV1IEHA.3840@.TK2MSFTNGP11.phx.gbl...
> > You can also change the owner by updating the sysobjects tables
> >
> > update sysobjects
> > set uid=<new uid>
> > where uid='uid for the user you need to drop'
> Hari, although this may work, Casey should probably use the supported
method
> (sp_changedbowner).
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:ePdLZpwIEHA.3556@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > You can not drop the user if the user owns any objects.
> >
> > How to change the object owner:
> >
> > sp_changeobjectowner 'obj_name','new_owner'
> >
> > You can also change the owner by updating the sysobjects tables
> >
> > update sysobjects
> > set uid=<new uid>
> > where uid='uid for the user you need to drop'
> >
> > Thanks
> > Hari
> > MCDBA
> >
> >
> >
> > "Casey" <casey.canales@.bestsoftware.com> wrote in message
> > news:u0nvimwIEHA.2688@.tk2msftngp13.phx.gbl...
> > > When I attempt to drop a user I receive an error that the user objects
> > > objects in the SQL 2000 DB, and the login could not be dropped. Does
> > anyone
> > > have a workaround to this? How can you get around this. Perhaps
> changing
> > > ownership globally, and then deleteing the login? Any Ideas?
> > >
> > >
> >
> >
>|||Oops, I meant sp_changeobjectowner.
--
Dan Guzman
SQL Server MVP
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:OiJAsV1IEHA.3840@.TK2MSFTNGP11.phx.gbl...
> > You can also change the owner by updating the sysobjects tables
> >
> > update sysobjects
> > set uid=<new uid>
> > where uid='uid for the user you need to drop'
> Hari, although this may work, Casey should probably use the supported
method
> (sp_changedbowner).
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:ePdLZpwIEHA.3556@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > You can not drop the user if the user owns any objects.
> >
> > How to change the object owner:
> >
> > sp_changeobjectowner 'obj_name','new_owner'
> >
> > You can also change the owner by updating the sysobjects tables
> >
> > update sysobjects
> > set uid=<new uid>
> > where uid='uid for the user you need to drop'
> >
> > Thanks
> > Hari
> > MCDBA
> >
> >
> >
> > "Casey" <casey.canales@.bestsoftware.com> wrote in message
> > news:u0nvimwIEHA.2688@.tk2msftngp13.phx.gbl...
> > > When I attempt to drop a user I receive an error that the user objects
> > > objects in the SQL 2000 DB, and the login could not be dropped. Does
> > anyone
> > > have a workaround to this? How can you get around this. Perhaps
> changing
> > > ownership globally, and then deleteing the login? Any Ideas?
> > >
> > >
> >
> >
>

Wednesday, March 21, 2012

Drop temporary tables whilst connected ?

Just a quicky about temporarary tables. If using QA, when you create a
temporary table, it gets dropped if you close the query. Otherwise you
need to state 'DROP TABLE myTable' so that you can re-run the query
without the table being there.

Sometimes, you can have quite lengthy SQL statements (in a series)
with various drop table sections throughout the query. Ideally you
would put these all at the end, but sometimes you will need to drop
some part way through (for ease of reading and max temp tables etc...)

However, what I was wondering is :

Is there any way to quickly drop the temporary tables for the current
connection without specifying all of the tables individually ? When
testing/checking, you have to work your way through and run each drop
table section individually. This can be time consuming, so being
naturally lazy, is there a quick way of doing this ? When working
through the SQL, it's possible to do this quite a lot.

Example

SQL Statement with several parts, each uses a series of temporary
tables to create a result set. At the end of a section, these work
tables are no longer needed, so drop table commands are used. The
final result set brings back the combined results from each section
and then drops those at the end.

TIA

RyanRyan (ryanofford@.hotmail.com) writes:
> Just a quicky about temporarary tables. If using QA, when you create a
> temporary table, it gets dropped if you close the query. Otherwise you
> need to state 'DROP TABLE myTable' so that you can re-run the query
> without the table being there.
> Sometimes, you can have quite lengthy SQL statements (in a series)
> with various drop table sections throughout the query. Ideally you
> would put these all at the end, but sometimes you will need to drop
> some part way through (for ease of reading and max temp tables etc...)
> However, what I was wondering is :
> Is there any way to quickly drop the temporary tables for the current
> connection without specifying all of the tables individually ? When
> testing/checking, you have to work your way through and run each drop
> table section individually. This can be time consuming, so being
> naturally lazy, is there a quick way of doing this ? When working
> through the SQL, it's possible to do this quite a lot.

No, there is no "DROP TABLE #%".

You could write a cursor over tempdb..sysobjects which finds the tables,
but then you would have to mask out the part which is tacked on to the
table name. Kind of messy.

On the other hand, why not pack everything in a stored procedure? A temp
created in a scope is dropped when that scope exits. Thus, with a stored
procedure, this is a non-problem.

If using a stored procedure is problematic for some reason, a RAISERROR
with level 21 is a brutal way if getting rid of the temp tables - in
fact, this kills your connection. Only do this, if you are your own DBA,
because it may ping an alert for an operator on a big server.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> On the other hand, why not pack everything in a stored procedure? A temp
> created in a scope is dropped when that scope exits. Thus, with a stored
> procedure, this is a non-problem.

Most of this type of query will be put into a stored procedure once
finished, but we most often need to work through it in stages to check
that we have the maths correct at each stage before we progress this
further into an SP. We do a lot of manipulating financials so need to
check our maths throughout. We have a lot of reports based on our
figures and each needs to use the same logic but slightly different
groups of answers which needs checking.

In a lot of cases the SQL can be over a thousand lines long, so we
tend to break it down as much as possible in order to keep it simple.
Hence grouping the drop table statements so we can work with it.

Thanks

Ryan

DROP TABLE BY MISTAKE

I have just dropped a table in Sql Server By mistake
Is there a way to tr retieve this without pulling up an old backup?
TIA
None that I can think of.
Your backup is probably the best bet.
"Grant Merwitz" <grant@.magicalia.com> wrote in message
news:O7EE91v1EHA.3408@.tk2msftngp13.phx.gbl...
> I have just dropped a table in Sql Server By mistake
> Is there a way to tr retieve this without pulling up an old backup?
> TIA
>
|||http://www.aspfaq.com/2449
http://www.aspfaq.com/
(Reverse address to reply.)
"Grant Merwitz" <grant@.magicalia.com> wrote in message
news:O7EE91v1EHA.3408@.tk2msftngp13.phx.gbl...
> I have just dropped a table in Sql Server By mistake
> Is there a way to tr retieve this without pulling up an old backup?
> TIA
>

DROP TABLE BY MISTAKE

I have just dropped a table in Sql Server By mistake
Is there a way to tr retieve this without pulling up an old backup?
TIANone that I can think of.
Your backup is probably the best bet.
"Grant Merwitz" <grant@.magicalia.com> wrote in message
news:O7EE91v1EHA.3408@.tk2msftngp13.phx.gbl...
> I have just dropped a table in Sql Server By mistake
> Is there a way to tr retieve this without pulling up an old backup?
> TIA
>|||http://www.aspfaq.com/2449
http://www.aspfaq.com/
(Reverse address to reply.)
"Grant Merwitz" <grant@.magicalia.com> wrote in message
news:O7EE91v1EHA.3408@.tk2msftngp13.phx.gbl...
> I have just dropped a table in Sql Server By mistake
> Is there a way to tr retieve this without pulling up an old backup?
> TIA
>

DROP TABLE BY MISTAKE

I have just dropped a table in Sql Server By mistake
Is there a way to tr retieve this without pulling up an old backup?
TIANone that I can think of.
Your backup is probably the best bet.
"Grant Merwitz" <grant@.magicalia.com> wrote in message
news:O7EE91v1EHA.3408@.tk2msftngp13.phx.gbl...
> I have just dropped a table in Sql Server By mistake
> Is there a way to tr retieve this without pulling up an old backup?
> TIA
>|||http://www.aspfaq.com/2449
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Grant Merwitz" <grant@.magicalia.com> wrote in message
news:O7EE91v1EHA.3408@.tk2msftngp13.phx.gbl...
> I have just dropped a table in Sql Server By mistake
> Is there a way to tr retieve this without pulling up an old backup?
> TIA
>

Monday, March 19, 2012

Drop Stored Procedure causing Dropped Tables

Hey guys, has anyone ever seen this happen:

Try to move stored proc from one DB to another using DTS, errors on create proc. Create proc manually.

Three tables referenced by that stored proc have been dropped and re-created with the same table structure.

I'm not 100% certain that it happened at exactly the same time, but it seems to be around the same time. Any ideas? Anyone seen this happen before?You will have to check the options you picked for the Transfer task in your DTS package. Did you ask it to move dependent objects also? For more help on the DTS package/tasks, please post in the SQL Server Integration Services forum.|||It was selected for dependent objects, but those tables are not dependent on the stored proc. As far as I know, the DTS method I used was the same as using DROP PROCEDURE, since it failed after the drop.|||I don't know how the DTS task determines dependencies. If it uses say sp_depends SP then you can check by running the SP for your SP to see the dependencies. Note, that this SP only gives immediate dependencies. If this doesn't help find how DTS determines dependencies then ask in the SSIS forum or run your package and trace the calls to SQL Server.

Tuesday, March 27, 2012

Dropping article on transaction publications.

Sunday, March 25, 2012

Dropping a Database

Dropping a Database

Dropping a Database

Droppin rule in SQL Server 2000

Droppin rule in SQL Server 2000

Dropped Table

Dropped Table

Dropped Table

Dropped connections, but open on the SQL Server...

Dropped connections, but open on the SQL Server...

Dropped column being "dropped" before drop ??

Thursday, March 22, 2012

Drop User

Drop User

Drop User

Wednesday, March 21, 2012

Drop temporary tables whilst connected ?

DROP TABLE BY MISTAKE

DROP TABLE BY MISTAKE

DROP TABLE BY MISTAKE

Monday, March 19, 2012

Drop Stored Procedure causing Dropped Tables

DSO in SQL2005

Blog Archive

About Me