Dropping System Stored Procedures

When a user database is created, there are 31 system stored procedures
created. In one of the SQL Server security auditing questions/feedback
session, it was suggested to drop all the system stored procedures from the
user database from security perspective. I have not found or seen any comment
on the web on this. Even Microsoft SQL Server 2000 Security Best Practices
does not state on this. Does someone has some comment on this?
> When a user database is created, there are 31 system stored procedures
> created. In one of the SQL Server security auditing questions/feedback
> session, it was suggested to drop all the system stored procedures from
> the
> user database from security perspective.
Why? System databases that are stored in master will still execute in the
context of the database they are called from.
A
|||Also if you drop your system stored procedures and your system does not
work, perhaps your configuration will not be supported by Microsoft.
Could you mention a couple of these stored procedures and its security risk?
Ben Nevarez
"Aaron Bertrand [SQL Server MVP]" wrote:

> Why? System databases that are stored in master will still execute in the
> context of the database they are called from.
> A
>
>
|||and what kind of security audit tool/application are you using.
Thanks, Liliya
"Ben Nevarez" wrote:
[vbcol=seagreen]
> Also if you drop your system stored procedures and your system does not
> work, perhaps your configuration will not be supported by Microsoft.
> Could you mention a couple of these stored procedures and its security risk?
> Ben Nevarez
>
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
|||Thanks for all the feedback. Currently we are using Lumigent Log Explorer.
This generates DDL alerts. But this is not in the discussion. One of the DB
security/audit person asked us that we should drop all the system stored
procedures in the user database, not master database. I have never heard or
seen anything about this. If you have some knowledge from security
perspective, please let me know. Our IT Management is also keen to know about
it. Thanks again...Fraz
"Liliya Huff" wrote:
[vbcol=seagreen]
> and what kind of security audit tool/application are you using.
> --
> Thanks, Liliya
>
> "Ben Nevarez" wrote:
|||Fraz,
I heard this discussed many years ago by someone (long forgotten) as a
method of locking down a database. I never followed that advice because I
thought it was questionable.
Any change to the deliverable software, even changing permissions to it, has
to be carefully weighed for what it will break and which tools will no
longer work as expected.
I would not delete system stored procedure from a database unless I had a
very clear reason to do so and a good understanding of the impact.
(Actually, 'leave them alone' is how I read the subtext of other comments.)
RLF
"Fraz" <Fraz@.discussions.microsoft.com> wrote in message
news:45F11F19-110C-4E40-A4C1-811DC6B1BC8D@.microsoft.com...[vbcol=seagreen]
> Thanks for all the feedback. Currently we are using Lumigent Log Explorer.
> This generates DDL alerts. But this is not in the discussion. One of the
> DB
> security/audit person asked us that we should drop all the system stored
> procedures in the user database, not master database. I have never heard
> or
> seen anything about this. If you have some knowledge from security
> perspective, please let me know. Our IT Management is also keen to know
> about
> it. Thanks again...Fraz
> "Liliya Huff" wrote:
|||are you talking about SOX auditors?
that is an odd one. never showed up in our case...
dropping system stored procedures is not going to lock down your database
alone. Are you trying to lock down an insider or an outsider?
Thanks, Liliya
"Fraz" wrote:

> Thanks for all the feedback. Currently we are using Lumigent Log Explorer.
> This generates DDL alerts. But this is not in the discussion. One of the DB
> security/audit person asked us that we should drop all the system stored
> procedures in the user database, not master database. I have never heard or
> seen anything about this. If you have some knowledge from security
> perspective, please let me know. Our IT Management is also keen to know about
> it. Thanks again...Fraz
|||Not SOX auditors, they are other auditors. We are trying to protect the
database from oursider.
From all the comments I have received, I feel that it is safe to leave all
the system stored procedures in the user database as-it-is. I appreciate your
comments and thank you for this. Regards...Fraz
"Liliya Huff" wrote:

> are you talking about SOX auditors?
> that is an odd one. never showed up in our case...
> dropping system stored procedures is not going to lock down your database
> alone. Are you trying to lock down an insider or an outsider?
> --
> Thanks, Liliya
>
> "Fraz" wrote:
>
|||if it is an insider attack, then killing system stored procedures is not
going to protect you.
Insider will be trying to get access to your data or if really upset for
example generate a dos kind of attack. On inside an authenticated attack is
your likely attack. Find out where your end-users stash their passwords .
In both cases killing system stored procedures is not going to to anything
except of a trouble for you to locate who when and how. There is no need to
use any of system stored procedures to create an authenticated dos attack,
public is more then enough. To take the data - that one is likely to be an
authenticated attack, because it is more difficult to catch, imitates a real
application.
Thanks, Liliya
"Fraz" wrote:

> Not SOX auditors, they are other auditors. We are trying to protect the
> database from oursider.
> From all the comments I have received, I feel that it is safe to leave all
> the system stored procedures in the user database as-it-is. I appreciate your
> comments and thank you for this. Regards...Fraz

Dropping System Stored Procedures

When a user database is created, there are 31 system stored procedures
created. In one of the SQL Server security auditing questions/feedback
session, it was suggested to drop all the system stored procedures from the
user database from security perspective. I have not found or seen any comment
on the web on this. Even Microsoft SQL Server 2000 Security Best Practices
does not state on this. Does someone has some comment on this?> When a user database is created, there are 31 system stored procedures
> created. In one of the SQL Server security auditing questions/feedback
> session, it was suggested to drop all the system stored procedures from
> the
> user database from security perspective.
Why? System databases that are stored in master will still execute in the
context of the database they are called from.
A|||Also if you drop your system stored procedures and your system does not
work, perhaps your configuration will not be supported by Microsoft.
Could you mention a couple of these stored procedures and its security risk?
Ben Nevarez
"Aaron Bertrand [SQL Server MVP]" wrote:
> > When a user database is created, there are 31 system stored procedures
> > created. In one of the SQL Server security auditing questions/feedback
> > session, it was suggested to drop all the system stored procedures from
> > the
> > user database from security perspective.
> Why? System databases that are stored in master will still execute in the
> context of the database they are called from.
> A
>
>|||and what kind of security audit tool/application are you using.
--
Thanks, Liliya
"Ben Nevarez" wrote:
> Also if you drop your system stored procedures and your system does not
> work, perhaps your configuration will not be supported by Microsoft.
> Could you mention a couple of these stored procedures and its security risk?
> Ben Nevarez
>
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
> > > When a user database is created, there are 31 system stored procedures
> > > created. In one of the SQL Server security auditing questions/feedback
> > > session, it was suggested to drop all the system stored procedures from
> > > the
> > > user database from security perspective.
> >
> > Why? System databases that are stored in master will still execute in the
> > context of the database they are called from.
> >
> > A
> >
> >
> >|||Thanks for all the feedback. Currently we are using Lumigent Log Explorer.
This generates DDL alerts. But this is not in the discussion. One of the DB
security/audit person asked us that we should drop all the system stored
procedures in the user database, not master database. I have never heard or
seen anything about this. If you have some knowledge from security
perspective, please let me know. Our IT Management is also keen to know about
it. Thanks again...Fraz
"Liliya Huff" wrote:
> and what kind of security audit tool/application are you using.
> --
> Thanks, Liliya
>
> "Ben Nevarez" wrote:
> >
> > Also if you drop your system stored procedures and your system does not
> > work, perhaps your configuration will not be supported by Microsoft.
> >
> > Could you mention a couple of these stored procedures and its security risk?
> >
> > Ben Nevarez
> >
> >
> >
> >
> > "Aaron Bertrand [SQL Server MVP]" wrote:
> >
> > > > When a user database is created, there are 31 system stored procedures
> > > > created. In one of the SQL Server security auditing questions/feedback
> > > > session, it was suggested to drop all the system stored procedures from
> > > > the
> > > > user database from security perspective.
> > >
> > > Why? System databases that are stored in master will still execute in the
> > > context of the database they are called from.
> > >
> > > A
> > >
> > >
> > >|||Fraz,
I heard this discussed many years ago by someone (long forgotten) as a
method of locking down a database. I never followed that advice because I
thought it was questionable.
Any change to the deliverable software, even changing permissions to it, has
to be carefully weighed for what it will break and which tools will no
longer work as expected.
I would not delete system stored procedure from a database unless I had a
very clear reason to do so and a good understanding of the impact.
(Actually, 'leave them alone' is how I read the subtext of other comments.)
RLF
"Fraz" <Fraz@.discussions.microsoft.com> wrote in message
news:45F11F19-110C-4E40-A4C1-811DC6B1BC8D@.microsoft.com...
> Thanks for all the feedback. Currently we are using Lumigent Log Explorer.
> This generates DDL alerts. But this is not in the discussion. One of the
> DB
> security/audit person asked us that we should drop all the system stored
> procedures in the user database, not master database. I have never heard
> or
> seen anything about this. If you have some knowledge from security
> perspective, please let me know. Our IT Management is also keen to know
> about
> it. Thanks again...Fraz
> "Liliya Huff" wrote:
>> and what kind of security audit tool/application are you using.
>> --
>> Thanks, Liliya
>>
>> "Ben Nevarez" wrote:
>> >
>> > Also if you drop your system stored procedures and your system does not
>> > work, perhaps your configuration will not be supported by Microsoft.
>> >
>> > Could you mention a couple of these stored procedures and its security
>> > risk?
>> >
>> > Ben Nevarez
>> >
>> >
>> >
>> >
>> > "Aaron Bertrand [SQL Server MVP]" wrote:
>> >
>> > > > When a user database is created, there are 31 system stored
>> > > > procedures
>> > > > created. In one of the SQL Server security auditing
>> > > > questions/feedback
>> > > > session, it was suggested to drop all the system stored procedures
>> > > > from
>> > > > the
>> > > > user database from security perspective.
>> > >
>> > > Why? System databases that are stored in master will still execute
>> > > in the
>> > > context of the database they are called from.
>> > >
>> > > A
>> > >
>> > >
>> > >|||are you talking about SOX auditors?
that is an odd one. never showed up in our case...
dropping system stored procedures is not going to lock down your database
alone. Are you trying to lock down an insider or an outsider?
--
Thanks, Liliya
"Fraz" wrote:
> Thanks for all the feedback. Currently we are using Lumigent Log Explorer.
> This generates DDL alerts. But this is not in the discussion. One of the DB
> security/audit person asked us that we should drop all the system stored
> procedures in the user database, not master database. I have never heard or
> seen anything about this. If you have some knowledge from security
> perspective, please let me know. Our IT Management is also keen to know about
> it. Thanks again...Fraz|||Not SOX auditors, they are other auditors. We are trying to protect the
database from oursider.
From all the comments I have received, I feel that it is safe to leave all
the system stored procedures in the user database as-it-is. I appreciate your
comments and thank you for this. Regards...Fraz
"Liliya Huff" wrote:
> are you talking about SOX auditors?
> that is an odd one. never showed up in our case...
> dropping system stored procedures is not going to lock down your database
> alone. Are you trying to lock down an insider or an outsider?
> --
> Thanks, Liliya
>
> "Fraz" wrote:
> > Thanks for all the feedback. Currently we are using Lumigent Log Explorer.
> > This generates DDL alerts. But this is not in the discussion. One of the DB
> > security/audit person asked us that we should drop all the system stored
> > procedures in the user database, not master database. I have never heard or
> > seen anything about this. If you have some knowledge from security
> > perspective, please let me know. Our IT Management is also keen to know about
> > it. Thanks again...Fraz
>|||if it is an insider attack, then killing system stored procedures is not
going to protect you.
Insider will be trying to get access to your data or if really upset for
example generate a dos kind of attack. On inside an authenticated attack is
your likely attack. Find out where your end-users stash their passwords :).
In both cases killing system stored procedures is not going to to anything
except of a trouble for you to locate who when and how. There is no need to
use any of system stored procedures to create an authenticated dos attack,
public is more then enough. To take the data - that one is likely to be an
authenticated attack, because it is more difficult to catch, imitates a real
application.
--
Thanks, Liliya
"Fraz" wrote:
> Not SOX auditors, they are other auditors. We are trying to protect the
> database from oursider.
> From all the comments I have received, I feel that it is safe to leave all
> the system stored procedures in the user database as-it-is. I appreciate your
> comments and thank you for this. Regards...Frazsql

dropping functions

Bonjour,

I'm as green as can be...and I created a function but could not drop it when
things went south....

create or replace function available(client_id, property_id)
return number
is
ret number := 0;
begin
begin
select nvl2(c.client_id,1,0)
into ret
from client c, property p
where p.property_id = property
and p.asking_price <= c.max_rent(+)
and c.client_id (+) = client;
exception
when no_data_found then
ret := 0;
end;
return ret;
end;
/

:eek:Did you try:
DROP FUNCTION AVAILABLE;
:rolleyes:

Dropping Data Connect String

I've created a data source connection using the sa login and password with
the save password option. Running the stored procedure returns a good result
set. Ok, I'm feeling pretty good at this point . But after clicking on the
Preview tab, I receive the following message: "A connection cannot be made
to the database. Set and test the connection string. Login failed for
DIM\tj."
Of course testing the connection works fine, but why is trying to
authenticate using my credentials when it should be use the connect string?
I'm running RS SP1 against a SQL 2000 db.
--
Any and all contributions are greatly appreciated ...
Regards TJIt couldn't be something inside the stored procedure, could it? Can you do a
straight select?
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"TJ" <nospam@.nowhere.com> wrote in message
news:Oi3Y$gzpEHA.556@.tk2msftngp13.phx.gbl...
> I've created a data source connection using the sa login and password with
> the save password option. Running the stored procedure returns a good
> result
> set. Ok, I'm feeling pretty good at this point . But after clicking on the
> Preview tab, I receive the following message: "A connection cannot be made
> to the database. Set and test the connection string. Login failed for
> DIM\tj."
> Of course testing the connection works fine, but why is trying to
> authenticate using my credentials when it should be use the connect
> string?
> I'm running RS SP1 against a SQL 2000 db.
> --
> Any and all contributions are greatly appreciated ...
> Regards TJ
>|||If it is, I'm not seeing any errors when I execute the stored procedure on
the Data Tab. Is there an error log for the Data Tab?
"Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
news:ek08dp7pEHA.592@.TK2MSFTNGP11.phx.gbl...
> It couldn't be something inside the stored procedure, could it? Can you do
a
> straight select?
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "TJ" <nospam@.nowhere.com> wrote in message
> news:Oi3Y$gzpEHA.556@.tk2msftngp13.phx.gbl...
> > I've created a data source connection using the sa login and password
with
> > the save password option. Running the stored procedure returns a good
> > result
> > set. Ok, I'm feeling pretty good at this point . But after clicking on
the
> > Preview tab, I receive the following message: "A connection cannot be
made
> > to the database. Set and test the connection string. Login failed for
> > DIM\tj."
> >
> > Of course testing the connection works fine, but why is trying to
> > authenticate using my credentials when it should be use the connect
> > string?
> >
> > I'm running RS SP1 against a SQL 2000 db.
> > --
> > Any and all contributions are greatly appreciated ...
> > Regards TJ
> >
> >
>|||I am running into the exact same issue... Any resolution to this yet?
"TJ" wrote:
> If it is, I'm not seeing any errors when I execute the stored procedure on
> the Data Tab. Is there an error log for the Data Tab?
> "Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
> news:ek08dp7pEHA.592@.TK2MSFTNGP11.phx.gbl...
> > It couldn't be something inside the stored procedure, could it? Can you do
> a
> > straight select?
> >
> > --
> > Brian Welcker
> > Group Program Manager
> > SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> > "TJ" <nospam@.nowhere.com> wrote in message
> > news:Oi3Y$gzpEHA.556@.tk2msftngp13.phx.gbl...
> > > I've created a data source connection using the sa login and password
> with
> > > the save password option. Running the stored procedure returns a good
> > > result
> > > set. Ok, I'm feeling pretty good at this point . But after clicking on
> the
> > > Preview tab, I receive the following message: "A connection cannot be
> made
> > > to the database. Set and test the connection string. Login failed for
> > > DIM\tj."
> > >
> > > Of course testing the connection works fine, but why is trying to
> > > authenticate using my credentials when it should be use the connect
> > > string?
> > >
> > > I'm running RS SP1 against a SQL 2000 db.
> > > --
> > > Any and all contributions are greatly appreciated ...
> > > Regards TJ
> > >
> > >
> >
> >
>
>|||Here are a couple of things you might try:
a) I was calling a stored procedure from within a stored procedure using the
exec command; The report data connection account didn't have permissions for
the stored procedure inside the main stored procedure. I found the
permissions issue by running the main stored procedure in Query Analyzer,
but you need to open your Query Analyzer connection using the same data
connection information being used in your report.
b) You can hard code the User Id and password setting in the Connection
String on the Data Source Tab for the report.
Good Luck
TJ
"StanDaMon" <StanDaMon@.discussions.microsoft.com> wrote in message
news:102E7428-08C9-467E-8DC6-B69A4E8D094E@.microsoft.com...
> I am running into the exact same issue... Any resolution to this yet?
> "TJ" wrote:
> > If it is, I'm not seeing any errors when I execute the stored procedure
on
> > the Data Tab. Is there an error log for the Data Tab?
> >
> > "Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
> > news:ek08dp7pEHA.592@.TK2MSFTNGP11.phx.gbl...
> > > It couldn't be something inside the stored procedure, could it? Can
you do
> > a
> > > straight select?
> > >
> > > --
> > > Brian Welcker
> > > Group Program Manager
> > > SQL Server Reporting Services
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > >
> > > "TJ" <nospam@.nowhere.com> wrote in message
> > > news:Oi3Y$gzpEHA.556@.tk2msftngp13.phx.gbl...
> > > > I've created a data source connection using the sa login and
password
> > with
> > > > the save password option. Running the stored procedure returns a
good
> > > > result
> > > > set. Ok, I'm feeling pretty good at this point . But after clicking
on
> > the
> > > > Preview tab, I receive the following message: "A connection cannot
be
> > made
> > > > to the database. Set and test the connection string. Login failed
for
> > > > DIM\tj."
> > > >
> > > > Of course testing the connection works fine, but why is trying to
> > > > authenticate using my credentials when it should be use the connect
> > > > string?
> > > >
> > > > I'm running RS SP1 against a SQL 2000 db.
> > > > --
> > > > Any and all contributions are greatly appreciated ...
> > > > Regards TJ
> > > >
> > > >
> > >
> > >
> >
> >
> >

dropping all statistics

Hi,
I'm wondering if there is a command I can use to drop all system & user
created statistics on a table? And then I can loop through the database and
get rid of all the statistics for the entire database. Thus, if it's not a
command but some SQL code then I can use that too.
I'm running some upgrade scripts for my application and it often fails on
dropping or altering tables based on statistics it doesn't know about.
Thanks,
mattTry below. Untested, just wrote it. Change the PRINT to EXEC(@.sql) to actually execute the DROP
statements:
DECLARE @.tblname sysname, @.statname sysname, @.sql nvarchar(2000)
DECLARE c CURSOR FOR
SELECT object_name(id), name FROM sysindexes WHERE INDEXPROPERTY(id, name, 'IsStatistics') = 1
OPEN c
FETCH NEXT FROM c INTO @.tblname, @.statname
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.sql = 'DROP STATISTICS [' + @.tblname + '].[' + @.statname + ']'
PRINT @.sql
FETCH NEXT FROM c INTO @.tblname, @.statname
END
CLOSE c
DEALLOCATE c
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"matt" <matt@.discussions.microsoft.com> wrote in message
news:6784F0CC-E641-45C8-A3B6-355C537E1FEC@.microsoft.com...
> Hi,
> I'm wondering if there is a command I can use to drop all system & user
> created statistics on a table? And then I can loop through the database and
> get rid of all the statistics for the entire database. Thus, if it's not a
> command but some SQL code then I can use that too.
> I'm running some upgrade scripts for my application and it often fails on
> dropping or altering tables based on statistics it doesn't know about.
> Thanks,
> matt|||Thanks! That works perfectly. All I needed was the index property to check
for stats, but I'll take the script. All worked well.
Matt
"Tibor Karaszi" wrote:
> Try below. Untested, just wrote it. Change the PRINT to EXEC(@.sql) to actually execute the DROP
> statements:
> DECLARE @.tblname sysname, @.statname sysname, @.sql nvarchar(2000)
> DECLARE c CURSOR FOR
> SELECT object_name(id), name FROM sysindexes WHERE INDEXPROPERTY(id, name, 'IsStatistics') = 1
> OPEN c
> FETCH NEXT FROM c INTO @.tblname, @.statname
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.sql = 'DROP STATISTICS [' + @.tblname + '].[' + @.statname + ']'
> PRINT @.sql
> FETCH NEXT FROM c INTO @.tblname, @.statname
> END
> CLOSE c
> DEALLOCATE c
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "matt" <matt@.discussions.microsoft.com> wrote in message
> news:6784F0CC-E641-45C8-A3B6-355C537E1FEC@.microsoft.com...
> > Hi,
> >
> > I'm wondering if there is a command I can use to drop all system & user
> > created statistics on a table? And then I can loop through the database and
> > get rid of all the statistics for the entire database. Thus, if it's not a
> > command but some SQL code then I can use that too.
> >
> > I'm running some upgrade scripts for my application and it often fails on
> > dropping or altering tables based on statistics it doesn't know about.
> >
> > Thanks,
> > matt
>
>

dropping a temp table

I've read here that a temp table is drop automatically when a store procedur
e
that created the temp table ends or when a session that uses a temp table
ends.
If I have a temp table that was created by an activex script inside a dts
and the dts is a scheduled job, would the temp table be dropped when the job
ended? Or should I explicitly drop the table in the script?
Thanks,
--
Dan D.IMHO, you should always drop what you create and never rely on the system to
clean up for you.
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:19839D1E-FAEF-47CB-9047-987EEB3E0663@.microsoft.com...
> I've read here that a temp table is drop automatically when a store
> procedure
> that created the temp table ends or when a session that uses a temp table
> ends.
> If I have a temp table that was created by an activex script inside a dts
> and the dts is a scheduled job, would the temp table be dropped when the
> job
> ended? Or should I explicitly drop the table in the script?
> Thanks,
> --
> Dan D.|||Dan,
Better if we drop it in the sp / job.
if object_id('tempdb.dbo.#temp_table') is not null
drop table #temp_table
AMB
"Dan D." wrote:

> I've read here that a temp table is drop automatically when a store proced
ure
> that created the temp table ends or when a session that uses a temp table
> ends.
> If I have a temp table that was created by an activex script inside a dts
> and the dts is a scheduled job, would the temp table be dropped when the j
ob
> ended? Or should I explicitly drop the table in the script?
> Thanks,
> --
> Dan D.|||Is the stored procedure really going to see the #temp table if it is created
in the DTS package? I would guess they are separately scoped...
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:AFF0DC56-3B04-4E8B-87FF-BFFD3BC9A352@.microsoft.com...
> Dan,
> Better if we drop it in the sp / job.
> if object_id('tempdb.dbo.#temp_table') is not null
> drop table #temp_table|||Aaron,
What I meant was that it is better to drop it before the code finishs.

> Is the stored procedure really going to see the #temp table if it is creat
ed
> in the DTS package? I would guess they are separately scoped...
Function Main()
sConnect="provider=sqloledb;server=weg-256;database=test_db;integrated
security=SSPI"
Set Conn = CreateObject("ADODB.Connection")
Conn.Open sConnect
Conn.Execute ("create table #tmp (c1 int)")
Conn.Execute ("insert into #tmp values(1)")
Conn.Execute ("create procedure #p1 as set nocount on select * from #tmp if
object_id('tempdb.dbo.#tmp') is not null drop table #tmp")
Set Rs = Conn.Execute ("exec #p1")
msgbox Rs.fields.item(0).value
Conn.close
Main = DTSTaskExecResult_Success
End Function
AMB
"Aaron Bertrand [SQL Server MVP]" wrote:

> Is the stored procedure really going to see the #temp table if it is creat
ed
> in the DTS package? I would guess they are separately scoped...
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:AFF0DC56-3B04-4E8B-87FF-BFFD3BC9A352@.microsoft.com...
>
>|||Ah sorry, I saw "in the sp" but not " / job" (or didn't translate " / job"
to " / package/script")
A|||Thanks everyone. That's what I was always taught. Cleanup after yourself.
--
Dan D.
"Alejandro Mesa" wrote:
> Aaron,
> What I meant was that it is better to drop it before the code finishs.
>
> Function Main()
> sConnect="provider=sqloledb;server=weg-256;database=test_db;integrated
> security=SSPI"
> Set Conn = CreateObject("ADODB.Connection")
> Conn.Open sConnect
> Conn.Execute ("create table #tmp (c1 int)")
> Conn.Execute ("insert into #tmp values(1)")
> Conn.Execute ("create procedure #p1 as set nocount on select * from #tmp
if
> object_id('tempdb.dbo.#tmp') is not null drop table #tmp")
> Set Rs = Conn.Execute ("exec #p1")
> msgbox Rs.fields.item(0).value
> Conn.close
> Main = DTSTaskExecResult_Success
> End Function
>
> AMB
> "Aaron Bertrand [SQL Server MVP]" wrote:
>

dropping a constraint

I think the answer to this question will be something like 'you'll have to re-initialize the subscribers', but I need to ask anyway...

I created a publication where my foreign key constraint we NOT created with NOT FOR REPLICATION. My publication is configured to replicate DDL changes. Is there any way I can drop and re-create the constraint in the publisher and get replication to push the change to the subscribers?

Thanks for your help

Graham

Graham,

You can use sp_addscriptexec to post a SQL script to all subscribers of your publication with all the constraint changes you want to make.

http://msdn2.microsoft.com/en-us/library/ms147904.aspx

http://msdn2.microsoft.com/en-us/library/ms174360.aspx

Regards,

Gary

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

Droping Partition Schema SQL 2005

I have a Partition schema PS1 mapped to a table name 'A'. And there
are 4 views v1,v2,v3,v4 are created based on table 'A'. I want drop
the Drop the Partition Schema. Could you help me to drop the Partition
Schema please?.
Thanks
>I have a Partition schema PS1 mapped to a table name 'A'. And there
> are 4 views v1,v2,v3,v4 are created based on table 'A'. I want drop
> the Drop the Partition Schema. Could you help me to drop the Partition
> Schema please?.
You can't drop the partition scheme while the table is still using it, so
the solution is to move the table to a filegroup or another partition
scheme. How you do that depends on whether you have indexes on the table or
not. The following information gives you some alternative methods depending
on your situation.
If you do not have a clustered index on the table.
1. Create a clustered index on the table in the statement specify a
filegroup (or other partition scheme). This places the data in the specified
filegroup by moving the data from the table to the leaf level of the
clustered index. Note that if you have nonclustered indexes on the table,
you should drop these before creating the clustered index. You can recreate
the nonclustered indexes after you have created the clustered index.
The syntax is
CREATE CLUSTERED INDEX index_name ON table_name(column_name)
ON filegroup_name -- for example, ON DEFAULT if you want to use the
default filegroup
2. Drop the clustered index if you don't want to keep it and the data will
remain in the filegroup.
3. Recreate any nonclustered indexes.
If you already have a clustered index on the table.
1. Drop any nonclustered indexes. You can recreate them later.
2. Use the DROP INDEX statement with the MOVE TO clause.
The syntax is
DROP INDEX index_name ON table_name
WITH (MOVE TO filegroup_name);
3. Recreate the clustered index and then any nonclustered indexes on the
table.
An alternative to using DROP INDEX is the CREATE INDEX WITH DROP_EXISTING
statement. The index name and column name(s) must be the same as the
existing index.
CREATE CLUSTERED INDEX index_name ON table_name(column_name)
WITH (DROP_EXISTING = ON)
ON filegroup_name
Once you have the table moved off of the partition, you can drop the
partition schemee using DROP PARTITION SCHEME partition_scheme_name
Hope that gets you going.
Gail
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"Praveen" <apveen@.gmail.com> wrote in message
news:1186027495.971909.295310@.19g2000hsx.googlegro ups.com...
>I have a Partition schema PS1 mapped to a table name 'A'. And there
> are 4 views v1,v2,v3,v4 are created based on table 'A'. I want drop
> the Drop the Partition Schema. Could you help me to drop the Partition
> Schema please?.
> Thanks
>

Droping Partition Schema SQL 2005

I have a Partition schema PS1 mapped to a table name 'A'. And there
are 4 views v1,v2,v3,v4 are created based on table 'A'. I want drop
the Drop the Partition Schema. Could you help me to drop the Partition
Schema please?.
Thanks>I have a Partition schema PS1 mapped to a table name 'A'. And there
> are 4 views v1,v2,v3,v4 are created based on table 'A'. I want drop
> the Drop the Partition Schema. Could you help me to drop the Partition
> Schema please?.
You can't drop the partition scheme while the table is still using it, so
the solution is to move the table to a filegroup or another partition
scheme. How you do that depends on whether you have indexes on the table or
not. The following information gives you some alternative methods depending
on your situation.
If you do not have a clustered index on the table.
1. Create a clustered index on the table in the statement specify a
filegroup (or other partition scheme). This places the data in the specified
filegroup by moving the data from the table to the leaf level of the
clustered index. Note that if you have nonclustered indexes on the table,
you should drop these before creating the clustered index. You can recreate
the nonclustered indexes after you have created the clustered index.
The syntax is
CREATE CLUSTERED INDEX index_name ON table_name(column_name)
ON filegroup_name -- for example, ON DEFAULT if you want to use the
default filegroup
2. Drop the clustered index if you don't want to keep it and the data will
remain in the filegroup.
3. Recreate any nonclustered indexes.
If you already have a clustered index on the table.
1. Drop any nonclustered indexes. You can recreate them later.
2. Use the DROP INDEX statement with the MOVE TO clause.
The syntax is
DROP INDEX index_name ON table_name
WITH (MOVE TO filegroup_name);
3. Recreate the clustered index and then any nonclustered indexes on the
table.
An alternative to using DROP INDEX is the CREATE INDEX WITH DROP_EXISTING
statement. The index name and column name(s) must be the same as the
existing index.
CREATE CLUSTERED INDEX index_name ON table_name(column_name)
WITH (DROP_EXISTING = ON)
ON filegroup_name
Once you have the table moved off of the partition, you can drop the
partition schemee using DROP PARTITION SCHEME partition_scheme_name
Hope that gets you going.
Gail
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/...r/bb428874.aspx
"Praveen" <apveen@.gmail.com> wrote in message
news:1186027495.971909.295310@.19g2000hsx.googlegroups.com...
>I have a Partition schema PS1 mapped to a table name 'A'. And there
> are 4 views v1,v2,v3,v4 are created based on table 'A'. I want drop
> the Drop the Partition Schema. Could you help me to drop the Partition
> Schema please?.
> Thanks
>sql

Droping Partition Schema SQL 2005

I have a Partition schema PS1 mapped to a table name 'A'. And there
are 4 views v1,v2,v3,v4 are created based on table 'A'. I want drop
the Drop the Partition Schema. Could you help me to drop the Partition
Schema please?.
Thanks>I have a Partition schema PS1 mapped to a table name 'A'. And there
> are 4 views v1,v2,v3,v4 are created based on table 'A'. I want drop
> the Drop the Partition Schema. Could you help me to drop the Partition
> Schema please?.
You can't drop the partition scheme while the table is still using it, so
the solution is to move the table to a filegroup or another partition
scheme. How you do that depends on whether you have indexes on the table or
not. The following information gives you some alternative methods depending
on your situation.
If you do not have a clustered index on the table.
1. Create a clustered index on the table in the statement specify a
filegroup (or other partition scheme). This places the data in the specified
filegroup by moving the data from the table to the leaf level of the
clustered index. Note that if you have nonclustered indexes on the table,
you should drop these before creating the clustered index. You can recreate
the nonclustered indexes after you have created the clustered index.
The syntax is
CREATE CLUSTERED INDEX index_name ON table_name(column_name)
ON filegroup_name -- for example, ON DEFAULT if you want to use the
default filegroup
2. Drop the clustered index if you don't want to keep it and the data will
remain in the filegroup.
3. Recreate any nonclustered indexes.
If you already have a clustered index on the table.
1. Drop any nonclustered indexes. You can recreate them later.
2. Use the DROP INDEX statement with the MOVE TO clause.
The syntax is
DROP INDEX index_name ON table_name
WITH (MOVE TO filegroup_name);
3. Recreate the clustered index and then any nonclustered indexes on the
table.
An alternative to using DROP INDEX is the CREATE INDEX WITH DROP_EXISTING
statement. The index name and column name(s) must be the same as the
existing index.
CREATE CLUSTERED INDEX index_name ON table_name(column_name)
WITH (DROP_EXISTING = ON)
ON filegroup_name
Once you have the table moved off of the partition, you can drop the
partition schemee using DROP PARTITION SCHEME partition_scheme_name
Hope that gets you going.
Gail
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"Praveen" <apveen@.gmail.com> wrote in message
news:1186027495.971909.295310@.19g2000hsx.googlegroups.com...
>I have a Partition schema PS1 mapped to a table name 'A'. And there
> are 4 views v1,v2,v3,v4 are created based on table 'A'. I want drop
> the Drop the Partition Schema. Could you help me to drop the Partition
> Schema please?.
> Thanks
>

Dropdown list width

Hi everybody,

I created a multivalue parameter in SSRS 2005 SP2, but the problem is that the width of the dropdown list is too small to fit my data (I'd like to avoid scrolling horizontally).

Is there any way to configure this?

Thanks in advance for your help.
Zoz

By converting the display parameter to a char value using convert(char(50),myvalue) as displayparam you should be able to make the field fixed width.

If you want to shrink the size of the box, you would need to limit the display output by using the substring function.

cheers,

Andrew

|||

Thanks for your answer Andrew.

Unfortunately, it doesn't work... I still need to scroll horizontally even when converting data to char...

May be there is some way to specify the width of the drop down list, but I have no idea where I could do that...

|||

there is a way to do this

-open up the dataset (assuming there is one) that returns values to your report params by clicking the ...

-now select the fields tab

-next edit the value column(by using an expression) for the field that is being displayed for your report parameter. you should be able to set a specified length of characters that you would like. this in turn will limit the width of the report parameter

sql

Drop trigger with a Variable Trigger Name

Hi all in .net I've created an application that allows creation of triggers, i also want to allow the deletion of triggers.

The trigger name is kept in a table, and apon deleting the record i want to use the field name to delete the trigger

I have the following Trigger

the error is at

DROP TRIGGER @.DeleteTrigger

I'm guessing it dosen't like the trigger name being a variable instead of a static name

how do i get around this?

thanks in advance

-- ================================================

-- Template generated from Template Explorer using:

-- Create Trigger (New Menu).SQL

--

-- Use the Specify Values for Template Parameters

-- command (Ctrl-Shift-M) to fill in the parameter

-- values below.

--

-- See additional Create Trigger templates for more

-- examples of different Trigger statements.

--

-- This block of comments will not be included in

-- the definition of the function.

-- ================================================

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================

CREATE TRIGGER RemoveTriggers

ON tblTriggers

AFTER DELETE

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

Declare @.DeleteTrigger as nvarchar(max)

select @.DeleteTrigger = TableName FROM DELETED

IF OBJECT_ID (@.DeleteTrigger,'TR') IS NOT NULL

DROP TRIGGER @.DeleteTrigger

GO

END

GO

You couldn't use

DROP TRIGGER @.DeleteTrigger

But you could use Dynamic SQL:

Code Snippet

DECLARE @.Query varchar(100)

SET @.Query = 'DROP TRIGGER '+@.DeleteTrigger

EXECUTE(@.Query)

But you must avoid SQL Injection in with code.

|||

hiya, thanks for getting abck so quickly...

I've changed the trigger to be...

Declare @.DeleteTrigger as nvarchar(max)

select @.DeleteTrigger = TriggerName FROM DELETED

IF OBJECT_ID (@.DeleteTrigger,'TR') IS NOT NULL

DECLARE @.Query varchar(100)

SET @.Query = 'DROP TRIGGER '+@.DeleteTrigger

EXECUTE(@.Query)

GO

but when I delete from my application i get this error

Incorrect syntax near '\'.

very usefull..... any idea's?

|||

Very strange. You code works on my system

Try to add

PRINT @.Query

before EXECUTE may be something wrong with data in DELETED table

|||

found the error i think, but not how to overcom it...

the printshowed this

DROP TRIGGER KAVBRACK\daveh14

and KAVBRACK\daveh14 is the correct name of the trigger, including the slash

do i need to put it in single quotes? or is there another way?

|||

Use:

Code Snippet

SET @.Query = 'DROP TRIGGER ['+@.DeleteTrigger+']'

|||

got it, it's squared brackets, everythings working fine now

thanks for your help :-)

Drop Trigger from Replicated Table?

I created a trigger on a replicated table in a publishing database on SQL Server 2000. When I attempt to ALTER TABLE ... DISABLE TRIGGER ..., I get a message that I cannot alter the table since it is part of a publication. Does anyone know if I would be able to issue a DROP TRIGGER or ALTER TRIGGER on a replicated table?

Thanks,

Gerald

you can not Disable trigger by alter statment in a replicated table. Drop the trigger is the only way...

Madhu

sql

Drop Table

Created a database which is going to import data from a Oracle database ever
y
w automatically. I need to know is there a way I can either get the impor
t
to override the existing data or a script to 'drop table' before importing.
I've done a test on importing the data and it just adds concurrently the new
data. If I can 'drop table' how would I script it to do it wly before the
import?You could use DROP and re-CREATE.
Or:
TRUNCATE TABLE YourTable.
Or:
DELETE FROM YourTable
Include these as part of whatever process performs the import. Or
schedule it automatically in a job using SQL Agent.
--
David Portas
SQL Server MVP
--|||Hi David,
Thanks for suggestions, I'm not to good with SQL, I found where you do the
job schedule. In the Edit Job Step this is the following info I put in:
Type: Transact-SQL Script
Database:name of database
Command:
DROP TABLE Result
GO
(It failed)
then I tried
TRUNCATE TABLE Result
(It Failed)
Am I using the right syntax for deleting the table. The error in Event
Viewer is Event ID 208 which when you look it up doesn't tell you much.|||"GO" is batch terminator for Query Analyzer. It has nothing to do with
T-SQL. So, create a T-SQL with the following for definition.
if object_id('Result','U') is not null
drop table Result
--
-oj
"Reg Coles" <RegColes@.discussions.microsoft.com> wrote in message
news:36BF20DA-504B-4C3A-AFB9-C2158B86A6F4@.microsoft.com...
> Hi David,
> Thanks for suggestions, I'm not to good with SQL, I found where you do the
> job schedule. In the Edit Job Step this is the following info I put in:
> Type: Transact-SQL Script
> Database:name of database
> Command:
> DROP TABLE Result
> GO
> (It failed)
> then I tried
> TRUNCATE TABLE Result
> (It Failed)
> Am I using the right syntax for deleting the table. The error in Event
> Viewer is Event ID 208 which when you look it up doesn't tell you much.|||Thanks OJ, working like a treat.

Drop system table?

I am trying to get rid of a table that a former employee created... it is lised as a system table. When I try to delete it I get an error telling me I cannot drop a system table. How can I get rid of it?
Thanks for your help.
I assume the table in question was change to xtype 'S' by hacking the
sysobjects table. If you are certain this is a user table, you can change
it back to 'U' and drop it using a script like the example below:
EXEC sp_configure 'allow',1
RECONFIGURE WITH OVERRIDE
GO
UPDATE sysobjects
SET xtype = 'U'
WHERE name = 'MyTable'
GO
DROP TABLE MyTable
GO
EXEC sp_configure 'allow',0
RECONFIGURE
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"dev@.mycompany.com" <devmycompanycom@.discussions.microsoft.com> wrote in
message news:86FCAF98-480C-424B-A281-BF56625B7C2C@.microsoft.com...
> I am trying to get rid of a table that a former employee created... it is
lised as a system table. When I try to delete it I get an error telling me
I cannot drop a system table. How can I get rid of it?
> Thanks for your help.

Drop system table?

I am trying to get rid of a table that a former employee created... it is lised as a system table. When I try to delete it I get an error telling me I cannot drop a system table. How can I get rid of it?
Thanks for your help.I assume the table in question was change to xtype 'S' by hacking the
sysobjects table. If you are certain this is a user table, you can change
it back to 'U' and drop it using a script like the example below:
EXEC sp_configure 'allow',1
RECONFIGURE WITH OVERRIDE
GO
UPDATE sysobjects
SET xtype = 'U'
WHERE name = 'MyTable'
GO
DROP TABLE MyTable
GO
EXEC sp_configure 'allow',0
RECONFIGURE
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"dev@.mycompany.com" <devmycompanycom@.discussions.microsoft.com> wrote in
message news:86FCAF98-480C-424B-A281-BF56625B7C2C@.microsoft.com...
> I am trying to get rid of a table that a former employee created... it is
lised as a system table. When I try to delete it I get an error telling me
I cannot drop a system table. How can I get rid of it?
> Thanks for your help.

Drop system table?

I am trying to get rid of a table that a former employee created... it is li
sed as a system table. When I try to delete it I get an error telling me I
cannot drop a system table. How can I get rid of it?
Thanks for your help.I assume the table in question was change to xtype 'S' by hacking the
sysobjects table. If you are certain this is a user table, you can change
it back to 'U' and drop it using a script like the example below:
EXEC sp_configure 'allow',1
RECONFIGURE WITH OVERRIDE
GO
UPDATE sysobjects
SET xtype = 'U'
WHERE name = 'MyTable'
GO
DROP TABLE MyTable
GO
EXEC sp_configure 'allow',0
RECONFIGURE
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"dev@.mycompany.com" <devmycompanycom@.discussions.microsoft.com> wrote in
message news:86FCAF98-480C-424B-A281-BF56625B7C2C@.microsoft.com...
> I am trying to get rid of a table that a former employee created... it is
lised as a system table. When I try to delete it I get an error telling me
I cannot drop a system table. How can I get rid of it?
> Thanks for your help.