DSO in SQL2005: statement

Showing posts with label statement. Show all posts

Thursday, March 29, 2012

Dropping Permissions

Does anyone know what is the statement to drop the
following permissions in QA (Not DENY).
There are too many of them to drop from the E.M.
GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE
ON [dbo].[mytable] TO [public]
GO
Thanks.
look at the REVOKE statement.
Example: REVOKE SELECT ON Budget_Data TO Mary
"Eric" wrote:

> Does anyone know what is the statement to drop the
> following permissions in QA (Not DENY).
> There are too many of them to drop from the E.M.
>
> GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE
> ON [dbo].[mytable] TO [public]
> GO
> Thanks.
>
|||Thanks...........
[vbcol=seagreen]
>--Original Message--
>look at the REVOKE statement.
>Example: REVOKE SELECT ON Budget_Data TO Mary
>
>"Eric" wrote:
DELETE
>.
>

Dropping Permissions

Does anyone know what is the statement to drop the
following permissions in QA (Not DENY).
There are too many of them to drop from the E.M.
GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE
ON [dbo].[mytable] TO [public]
GO
Thanks.look at the REVOKE statement.
Example: REVOKE SELECT ON Budget_Data TO Mary
"Eric" wrote:
> Does anyone know what is the statement to drop the
> following permissions in QA (Not DENY).
> There are too many of them to drop from the E.M.
>
> GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE
> ON [dbo].[mytable] TO [public]
> GO
> Thanks.
>|||Thanks...........
>--Original Message--
>look at the REVOKE statement.
>Example: REVOKE SELECT ON Budget_Data TO Mary
>
>"Eric" wrote:
>> Does anyone know what is the statement to drop the
>> following permissions in QA (Not DENY).
>> There are too many of them to drop from the E.M.
>>
>> GRANT REFERENCES , SELECT , UPDATE , INSERT ,
DELETE
>> ON [dbo].[mytable] TO [public]
>> GO
>> Thanks.
>.
>

Dropping Permissions

Does anyone know what is the statement to drop the
following permissions in QA (Not DENY).
There are too many of them to drop from the E.M.
GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE
ON [dbo].[mytable] TO [public]
GO
Thanks.look at the REVOKE statement.
Example: REVOKE SELECT ON Budget_Data TO Mary
"Eric" wrote:

> Does anyone know what is the statement to drop the
> following permissions in QA (Not DENY).
> There are too many of them to drop from the E.M.
>
> GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE
> ON [dbo].[mytable] TO [public]
> GO
> Thanks.
>|||Thanks...........

>--Original Message--
>look at the REVOKE statement.
>Example: REVOKE SELECT ON Budget_Data TO Mary
>
>"Eric" wrote:
>
DELETE[vbcol=seagreen]
>.
>

Thursday, March 22, 2012

drop. temp. table proc.

CREATE PROCEDURE DT @.TEMP_TABLE_NAME SYSNAME
AS
DECLARE @.STATEMENT VARCHAR(8000)
SET @.STATEMENT ='DROP TABLE '+@.TEMP_TABLE_NAME
IF EXISTS(SELECT NAME FROM TEMPDB..SYSOBJECTS WHERE NAME= @.TEMP_TABLE_NAME)
BEGIN
EXEC(@.STATEMENT)
END
SELECT *
INTO #AA
FROM a_table
DT '#AA'
SELECT * FROM #AA--the table #AA is still existing.
How can I change the procedure to enable dropping.I think that this line is where your proc is going wrong:
IF EXISTS(SELECT NAME FROM TEMPDB..SYSOBJECTS WHERE NAME= @.TEMP_TABLE_NAME)
The object name in tempdbs sysobjects table will be
#AA_________________somethinghere
Therefore, you'd have to change your = to LIKE, something like this:
@.TEMP_TABLE_NAME + '___%'
To check for an object's existence, I always try to retrieve the object's ID
using OBJECT_ID('objectname') function. If a non-null value is returned,
delete the object.
IF OBJECT_ID('tempdb..' + @.TEMP_TABLE_NAME) IS NOT NULL
I wouldn't normally recommend dynamic SQL due to the risk of a SQL injection
attack, but if it's only for your own use?
Dan.
"Alur" <Alur@.discussions.microsoft.com> wrote in message
news:8A70D6C8-790A-4EA9-9D61-DDE408345A3E@.microsoft.com...
> CREATE PROCEDURE DT @.TEMP_TABLE_NAME SYSNAME
> AS
> DECLARE @.STATEMENT VARCHAR(8000)
> SET @.STATEMENT ='DROP TABLE '+@.TEMP_TABLE_NAME
> IF EXISTS(SELECT NAME FROM TEMPDB..SYSOBJECTS WHERE NAME=
@.TEMP_TABLE_NAME)
> BEGIN
> EXEC(@.STATEMENT)
> END
> SELECT *
> INTO #AA
> FROM a_table
> DT '#AA'
> SELECT * FROM #AA--the table #AA is still existing.
> How can I change the procedure to enable dropping.
>|||On Mon, 15 Aug 2005 12:52:41 +0100, Daniel Doyle wrote:

>The object name in tempdbs sysobjects table will be
>#AA_________________somethinghere
>Therefore, you'd have to change your = to LIKE, something like this:
>@.TEMP_TABLE_NAME + '___%'
Hi Daniel,
I think that you meant to write
LIKE @.TEMP_TABLE_NAME + '[_][_][_]%'
or
LIKE @.TEMP_TABLE_NAME + '\_\_\_%' ESCAPE ''
The _ character in a LIKE pattern will match any single character.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thank you.
"Daniel Doyle" wrote:

> I think that this line is where your proc is going wrong:
> IF EXISTS(SELECT NAME FROM TEMPDB..SYSOBJECTS WHERE NAME= @.TEMP_TABLE_NAME
)
> The object name in tempdbs sysobjects table will be
> #AA_________________somethinghere
> Therefore, you'd have to change your = to LIKE, something like this:
> @.TEMP_TABLE_NAME + '___%'
> To check for an object's existence, I always try to retrieve the object's
ID
> using OBJECT_ID('objectname') function. If a non-null value is returned,
> delete the object.
> IF OBJECT_ID('tempdb..' + @.TEMP_TABLE_NAME) IS NOT NULL
> I wouldn't normally recommend dynamic SQL due to the risk of a SQL injecti
on
> attack, but if it's only for your own use?
> Dan.
> "Alur" <Alur@.discussions.microsoft.com> wrote in message
> news:8A70D6C8-790A-4EA9-9D61-DDE408345A3E@.microsoft.com...
> @.TEMP_TABLE_NAME)
>
>|||Yes, of course you are correct Hugo, it slippled my mind that _ is a
wildcard character.
Thanks. Dan.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:vbt1g1tbdoovaq2tt15vahkm5d0qhevn4n@.
4ax.com...
> On Mon, 15 Aug 2005 12:52:41 +0100, Daniel Doyle wrote:
>
> Hi Daniel,
> I think that you meant to write
> LIKE @.TEMP_TABLE_NAME + '[_][_][_]%'
> or
> LIKE @.TEMP_TABLE_NAME + '\_\_\_%' ESCAPE ''
> The _ character in a LIKE pattern will match any single character.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Wednesday, March 21, 2012

Drop table statement didn't actually drop table...

I have a scheduled job that runs every minute to monitor for
long-running jobs in production databases. This job has been running
without issue for at least 8 months now. I have not done ANY work on
this server in at least 2 months and no one has rights to do anything
within the SQL Server on this box except me. A couple of days ago, I
started getting job failure messages sporradically and the reason was
that '##jobs already exists in the database'. This is a global temp
table that gets created, populated, and dropped in the stored procedure
that's run in my job.
I ran a trace to see what's going on and why the job thinks the temp
table still exists. I can see where the DROP TABLE statement for the
temp table executes and completes on a job run that succeeds, but if
the next job run fails on the statement that creates the temp table, I
can do a select for that table and it does exist and is still populated
with the records from the previous run. I also ran the trace with
errors/exceptions and none were picked up.
Has anyone ever seen where a DROP TABLE statement is issued and
completes, but the table doesn't actually drop? Below you will find
the stored procedure, the results of the trace (where the first job run
wraps up with the final statements and the next job run tries to
execute and fails), and the select from the temp table after the DROP
TABLE statement is executed. Sorry for the length.
Any ideas would be appreciated!
STORED PROCEDURE
--
CREATE PROCEDURE [dbo].[LongRunningJobs]
@.emailaddress varchar(100)
AS
-- Description: This stored procedure will detect long running jobs.
-- A long running job is defined as a job that has
-- been running longer than what is specified as the time
-- limit for the job in the LongRunningJobs_JobTimeLimits
-- table. If it detects a long running job an email is sent.
-- DECLARE variables
SET NOCOUNT ON
DECLARE @.jobcount int
DECLARE @.jobid uniqueidentifier
DECLARE @.jobname sysname
DECLARE @.spid smallint
DECLARE @.logintime datetime
DECLARE @.duration int
-- CREATE TABLE to hold job information
CREATE TABLE #enum_job (
Job_ID uniqueidentifier,
Last_Run_Date int,
Last_Run_Time int,
Next_Run_Date int,
Next_Run_Time int,
Next_Run_Schedule_ID int,
Requested_To_Run int,
Request_Source int,
Request_Source_ID varchar(100),
Running int,
Current_Step int,
Current_Retry_Attempt int,
State int
)
-- CREATE TABLE to hold job_id and the job_id in hex character format
CREATE TABLE ##jobs (
job_id uniqueidentifier ,
job_id_char varchar(100)
)
-- Get a list of jobs
INSERT INTO #enum_job
EXEC master.dbo.xp_sqlagent_enum_jobs 1, '' -- doesn't seem to
matter what you put here
-- calculate the #jobs TABLE with job_id's
-- and their hex character representation
INSERT INTO ##jobs
SELECT job_id, dbo.fn_hex_to_char(job_id,16) FROM #enum_job
-- get a COUNT of long running jobs
SELECT @.jobcount = COUNT(*)
FROM master.dbo.sysprocesses a
join ##jobs b
ON SUBSTRING(a.program_name,32,32)= b.job_id_char
join msdb.dbo.sysjobs c
ON b.job_id = c.job_id
join dbo.LongRunningJobs_JobTimeLimits d
ON c.job_id = d.job_id
WHERE login_time < DATEADD(mi,-(d.minutes),GETDATE()) -- check for jobs
that have been running longer than 1 minute.
-- record long running jobs in LongRunningJobs_Jobs
IF @.jobcount > 0
BEGIN
UPDATE LongRunningJobs_Jobs
SET still_running = 0
DECLARE jobs_cursor CURSOR
FOR
SELECT SUBSTRING(c.name,1,78), c.job_id, a.spid, a.login_time
FROM master.dbo.sysprocesses a
join ##jobs b
ON SUBSTRING(a.program_name,32,32)= b.job_id_char
join msdb.dbo.sysjobs c
ON b.job_id = c.job_id
join dbo.LongRunningJobs_JobTimeLimits d
ON c.job_id = d.job_id
WHERE login_time < DATEADD(mi,-(d.minutes),GETDATE())
OPEN jobs_cursor
FETCH NEXT FROM jobs_cursor INTO @.jobname, @.jobid, @.spid, @.logintime
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF NOT EXISTS (SELECT job_id, login_time FROM
dbo.LongRunningJobs_Jobs
WHERE job_id = @.jobid AND login_time = @.logintime)
BEGIN
SET @.duration = (SELECT DATEDIFF(mi, @.logintime, getdate()))
EXEC LongRunningJobs_Notification @.jobid, @.logintime, @.duration,
@.emailaddress
INSERT INTO dbo.LongRunningJobs_Jobs (job_id, job_name, spid,
login_time, still_running)
VALUES (@.jobid, @.jobname, @.spid, @.logintime, 1)
FETCH NEXT FROM jobs_cursor INTO @.jobname, @.jobid, @.spid, @.logintime
END
ELSE
UPDATE LongRunningJobs_Jobs
SET still_running = 1
WHERE job_id = @.jobid AND login_time = @.logintime
FETCH NEXT FROM jobs_cursor INTO @.jobname, @.jobid, @.spid, @.logintime
END
CLOSE jobs_cursor
DEALLOCATE jobs_cursor
END
ELSE
UPDATE LongRunningJobs_Jobs
SET still_running = 0
DELETE FROM LongRunningJobs_Jobs
WHERE still_running = 0
DROP TABLE #enum_job
DROP TABLE ##jobs
GO
TRACE RESULTS
--
EventClass TextData
-- --
SP:StmtStarting -- LongRunningJobs
UPDATE LongRunningJobs_Jobs
SET still_running = 0
SP:StmtCompleted -- LongRunningJobs
UPDATE LongRunningJobs_Jobs
SET still_running = 0
SP:StmtStarting -- LongRunningJobs
DELETE FROM LongRunningJobs_Jobs
WHERE still_running = 0
SP:StmtCompleted -- LongRunningJobs
DELETE FROM LongRunningJobs_Jobs
WHERE still_running = 0
SP:StmtStarting -- LongRunningJobs
DROP TABLE #enum_job
SP:StmtCompleted -- LongRunningJobs
DROP TABLE #enum_job
SP:StmtStarting -- LongRunningJobs
DROP TABLE ##jobs
SP:StmtCompleted -- LongRunningJobs
DROP TABLE ##jobs
SP:Completed EXEC ISS_DBAData_Production.dbo.LongRunningJobs
'rfsupport@.kroger.com'
SQL:StmtCompleted EXEC ISS_DBAData_Production.dbo.LongRunningJobs
'rfsupport@.kroger.com'
SQL:BatchCompleted EXEC ISS_DBAData_Production.dbo.LongRunningJobs
'rfsupport@.kroger.com'
SQL:BatchStarting EXEC ISS_DBAData_Production.dbo.LongRunningJobs
'rfsupport@.kroger.com'
SQL:StmtStarting EXEC ISS_DBAData_Production.dbo.LongRunningJobs
'rfsupport@.kroger.com'
SP:Starting EXEC ISS_DBAData_Production.dbo.LongRunningJobs
'rfsupport@.kroger.com'
SP:StmtStarting -- LongRunningJobs
SET NOCOUNT ON
SP:StmtCompleted -- LongRunningJobs
SET NOCOUNT ON
SP:StmtStarting -- LongRunningJobs
CREATE TABLE #enum_job (
Job_ID uniqueidentifier,
Last_Run_Date int,
Last_Run_Time int,
Next_Run_Date int,
Next_Run_Time int,
Next_Run_Schedule_ID int,
Requested_To_Run int,
Request_Source int,
Request_Source_ID varch
SP:StmtCompleted -- LongRunningJobs
CREATE TABLE #enum_job (
Job_ID uniqueidentifier,
Last_Run_Date int,
Last_Run_Time int,
Next_Run_Date int,
Next_Run_Time int,
Next_Run_Schedule_ID int,
Requested_To_Run int,
Request_Source int,
Request_Source_ID varch
SP:StmtStarting -- LongRunningJobs
CREATE TABLE ##jobs (
job_id uniqueidentifier ,
job_id_char varchar(100)
)
-- Get a list of jobs
SQL:BatchCompleted EXEC ISS_DBAData_Production.dbo.LongRunningJobs
'rfsupport@.kroger.com'
SELECT OF ##jobs AFTER DROP STATEMENT HAS BEEN EXECUTED SHOWS TABLE
STILL EXISTS
---
job_id job_id_char
--
----
0887FA02-2785-415B-A1E7-0F70EA9B2BF9 02FA870885275B41A1E70F70EA9B2BF9
A22E5C05-9331-4D13-A2E0-C4470EF51BEE 055C2EA23193134DA2E0C4470EF51BEE
B45F740A-3847-42E4-B5F6-0A7AC400E5B8 0A745FB44738E442B5F60A7AC400E5B8
7EDEBA0D-730C-4C57-BD79-4ABAF8D2FB8F 0DBADE7E0C73574CBD794ABAF8D2FB8F
6152F60D-7CA7-4ADD-A117-43D1E6A9E07E 0DF65261A77CDD4AA11743D1E6A9E07E
F7810610-F203-4C52-BE0C-BBF9CF68C887 100681F703F2524CBE0CBBF9CF68C887
F482D610-0171-4E1F-B43F-1266AE7506A2 10D682F471011F4EB43F1266AE7506A2
9FB2FA11-B69F-41DF-AD25-49FF5C605F37 11FAB29F9FB6DF41AD2549FF5C605F37
6CBB1318-95D3-4B03-B5B5-E8C0B526087C 1813BB6CD395034BB5B5E8C0B526087CForgot to mention:
SQL Server 2000 Enterprise Edition with sp4.
This is an active/active cluster.
The job is set up on both nodes and is only failing on the first node.|||Apan,
Why not try making the table a local temporary table instead of global
temporary table?...proc won't see it as an existing table...since you're
dropping it at the end of the proc anyway.
HTH
Jerry
"apan" <anapatterson@.yahoo.com> wrote in message
news:1148661240.078643.204250@.y43g2000cwc.googlegroups.com...
> Forgot to mention:
> SQL Server 2000 Enterprise Edition with sp4.
> This is an active/active cluster.
> The job is set up on both nodes and is only failing on the first node.
>|||I had originally set it up as a global temp table because there were
going to be other sprocs querying it. I've since changed this (nothing
else in the database calls on this temp table), so I probably could
change it to a local temp table.
However, that really doesn't answer my question as to why it's
occasionally not being dropped even though the DROP TABLE command
executes. I'm not sure why it would matter if it's local or global if
the DROP is called.|||FYI - I've gone ahead and changed the global to a local to see if this
fixes the problem since I don't really need a global. But I'd still
like to see if anyone's got any ideas as to my previous question...
BTW, thanks for your input Jerry...forgot to say that before.|||It wouldn't...any blocking occuring that could prevent this? My solution
was an imediate "get you past this" solution.
HTH
Jerry
"apan" <anapatterson@.yahoo.com> wrote in message
news:1148663035.537739.42460@.y43g2000cwc.googlegroups.com...
>I had originally set it up as a global temp table because there were
> going to be other sprocs querying it. I've since changed this (nothing
> else in the database calls on this temp table), so I probably could
> change it to a local temp table.
> However, that really doesn't answer my question as to why it's
> occasionally not being dropped even though the DROP TABLE command
> executes. I'm not sure why it would matter if it's local or global if
> the DROP is called.
>|||Nope, no blocking. I also traced locks and errors/exceptions in my
profile trace when I ran it and none showed up. Also checked the
Locks/Process ID in Enterprise Manager for blocking when I could see
that the temp table hadn't been dropped and didn't see any blocking
there.|||Hmmm...if you query for the ## table in TEMPDB does it still exist?
"apan" <anapatterson@.yahoo.com> wrote in message
news:1148664132.029575.250840@.38g2000cwa.googlegroups.com...
> Nope, no blocking. I also traced locks and errors/exceptions in my
> profile trace when I ran it and none showed up. Also checked the
> Locks/Process ID in Enterprise Manager for blocking when I could see
> that the temp table hadn't been dropped and didn't see any blocking
> there.
>|||When I queried the global table (see last 20 or so lines of original
post for query details), I believe I was just doing it from one of the
user db's and it found it and brought back data. I don't think it
would matter if I queried for it from TEMPDB since it was a global
table...or would it?|||The global temporary table IS stored in the TEMPDB database. But if you're
getting records back the it does exist.
"apan" <anapatterson@.yahoo.com> wrote in message
news:1148666005.668894.271130@.y43g2000cwc.googlegroups.com...
> When I queried the global table (see last 20 or so lines of original
> post for query details), I believe I was just doing it from one of the
> user db's and it found it and brought back data. I don't think it
> would matter if I queried for it from TEMPDB since it was a global
> table...or would it?
>

Drop table statement didn't actually drop table...

DROP TABLE

Dear All,
Can I use DROP TABLE statement to drop more than one table at once. If
not, how can I drop (delete) so many tables from the data base at the same
time with the condition that I know a constant part on its name.
Best Regards
--
*********
IT Manager
DeLaval Ltd.
Cairo-Egypt
*********
|--|
|Islam is peace not Terror|
|--|Ibrahim
create table #t1 (col int)
create table #t2 (col int)
create table #t3 (col int)
DROP TABLE #t1,#t2,#t3
"Ibrahim Awwad" <ibrahim_awwad(at)hotmail(dot)com(antispam)> wrote in
message news:B0BB5831-AC8D-4184-A5F8-4E7E73CB9EB7@.microsoft.com...
> Dear All,
> Can I use DROP TABLE statement to drop more than one table at once. If
> not, how can I drop (delete) so many tables from the data base at the same
> time with the condition that I know a constant part on its name.
> Best Regards
> --
> *********
> IT Manager
> DeLaval Ltd.
> Cairo-Egypt
> *********
> |--|
> |Islam is peace not Terror|
> |--||||Ibrahim
Copy-Paste the output in the QA and press F5
USE NorthWind
SELECT 'DROP TABLE '+
QUOTENAME(TABLE_SCHEMA) +
QUOTENAME(TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE' AND
OBJECTPROPERTY(OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) +
'.' +
QUOTENAME(TABLE_NAME)),
'IsMSShipped') = 0 AND QUOTENAME(TABLE_NAME) LIKE
'%Customers%'
"Ibrahim Awwad" <ibrahim_awwad(at)hotmail(dot)com(antispam)> wrote in
message news:E52B7B9F-0B25-4503-A625-96FEC3526E2E@.microsoft.com...
> Dear Uri,
> First thanks for your reply, but let me give you an idea about the
> problem I have. Our DB is having about thousand tables and some of them
> replicated automatically each year. So the tables take the pattern
(SC010001)
> so for example this file become (SC010101) for year 2001 and (SC010201)
for
> year 2002 ...etc. So all what I need for example is to delete all
'01'
> tables as example one time without riting theiere name one by one. I hope
> that you got my idea.
> Best Regards
> "Uri Dimant" wrote:
> > Ibrahim
> > create table #t1 (col int)
> > create table #t2 (col int)
> > create table #t3 (col int)
> >
> > DROP TABLE #t1,#t2,#t3
> >
> >
> >
> >
> >
> > "Ibrahim Awwad" <ibrahim_awwad(at)hotmail(dot)com(antispam)> wrote in
> > message news:B0BB5831-AC8D-4184-A5F8-4E7E73CB9EB7@.microsoft.com...
> > > Dear All,
> > > Can I use DROP TABLE statement to drop more than one table at once.
If
> > > not, how can I drop (delete) so many tables from the data base at the
same
> > > time with the condition that I know a constant part on its name.
> > >
> > > Best Regards
> > > --
> > > *********
> > > IT Manager
> > > DeLaval Ltd.
> > > Cairo-Egypt
> > > *********
> > > |--|
> > > |Islam is peace not Terror|
> > > |--|
> >
> >
> >|||Hi Again Uri,
I tried what you did, it gave me the result DROP TABLE [dbo][Customers]
.. What was this for... Can you tell me more in depth -if you can-...
"Uri Dimant" wrote:
> Ibrahim
> Copy-Paste the output in the QA and press F5
> USE NorthWind
> SELECT 'DROP TABLE '+
> QUOTENAME(TABLE_SCHEMA) +
> QUOTENAME(TABLE_NAME)
> FROM INFORMATION_SCHEMA.TABLES
> WHERE
> TABLE_TYPE = 'BASE TABLE' AND
> OBJECTPROPERTY(OBJECT_ID(
> QUOTENAME(TABLE_SCHEMA) +
> '.' +
> QUOTENAME(TABLE_NAME)),
> 'IsMSShipped') = 0 AND QUOTENAME(TABLE_NAME) LIKE
> '%Customers%'
>
>
> "Ibrahim Awwad" <ibrahim_awwad(at)hotmail(dot)com(antispam)> wrote in
> message news:E52B7B9F-0B25-4503-A625-96FEC3526E2E@.microsoft.com...
> > Dear Uri,
> >
> > First thanks for your reply, but let me give you an idea about the
> > problem I have. Our DB is having about thousand tables and some of them
> > replicated automatically each year. So the tables take the pattern
> (SC010001)
> > so for example this file become (SC010101) for year 2001 and (SC010201)
> for
> > year 2002 ...etc. So all what I need for example is to delete all
> '01'
> > tables as example one time without riting theiere name one by one. I hope
> > that you got my idea.
> >
> > Best Regards
> >
> > "Uri Dimant" wrote:
> >
> > > Ibrahim
> > > create table #t1 (col int)
> > > create table #t2 (col int)
> > > create table #t3 (col int)
> > >
> > > DROP TABLE #t1,#t2,#t3
> > >
> > >
> > >
> > >
> > >
> > > "Ibrahim Awwad" <ibrahim_awwad(at)hotmail(dot)com(antispam)> wrote in
> > > message news:B0BB5831-AC8D-4184-A5F8-4E7E73CB9EB7@.microsoft.com...
> > > > Dear All,
> > > > Can I use DROP TABLE statement to drop more than one table at once.
> If
> > > > not, how can I drop (delete) so many tables from the data base at the
> same
> > > > time with the condition that I know a constant part on its name.
> > > >
> > > > Best Regards
> > > > --
> > > > *********
> > > > IT Manager
> > > > DeLaval Ltd.
> > > > Cairo-Egypt
> > > > *********
> > > > |--|
> > > > |Islam is peace not Terror|
> > > > |--|
> > >
> > >
> > >
>
>|||Ibrahim
I tested it on Nortwind database.You have to modify it for your needs.
Modify a LIKE operator in the WHERE condition to the actual table you want
to remove.
"Ibrahim Awwad" <ibrahim_awwad(at)hotmail(dot)com(antispam)> wrote in
message news:E2FFB248-D667-4D0D-A4BE-56E5C38B1C3C@.microsoft.com...
> Hi Again Uri,
> I tried what you did, it gave me the result DROP TABLE [dbo][Customers]
> .. What was this for... Can you tell me more in depth -if you can-...
>
> "Uri Dimant" wrote:
> > Ibrahim
> > Copy-Paste the output in the QA and press F5
> >
> > USE NorthWind
> > SELECT 'DROP TABLE '+
> > QUOTENAME(TABLE_SCHEMA) +
> > QUOTENAME(TABLE_NAME)
> >
> > FROM INFORMATION_SCHEMA.TABLES
> > WHERE
> > TABLE_TYPE = 'BASE TABLE' AND
> > OBJECTPROPERTY(OBJECT_ID(
> > QUOTENAME(TABLE_SCHEMA) +
> > '.' +
> > QUOTENAME(TABLE_NAME)),
> > 'IsMSShipped') = 0 AND QUOTENAME(TABLE_NAME) LIKE
> > '%Customers%'
> >
> >
> >
> >
> > "Ibrahim Awwad" <ibrahim_awwad(at)hotmail(dot)com(antispam)> wrote in
> > message news:E52B7B9F-0B25-4503-A625-96FEC3526E2E@.microsoft.com...
> > > Dear Uri,
> > >
> > > First thanks for your reply, but let me give you an idea about the
> > > problem I have. Our DB is having about thousand tables and some of
them
> > > replicated automatically each year. So the tables take the pattern
> > (SC010001)
> > > so for example this file become (SC010101) for year 2001 and
(SC010201)
> > for
> > > year 2002 ...etc. So all what I need for example is to delete all
> > '01'
> > > tables as example one time without riting theiere name one by one. I
hope
> > > that you got my idea.
> > >
> > > Best Regards
> > >
> > > "Uri Dimant" wrote:
> > >
> > > > Ibrahim
> > > > create table #t1 (col int)
> > > > create table #t2 (col int)
> > > > create table #t3 (col int)
> > > >
> > > > DROP TABLE #t1,#t2,#t3
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > "Ibrahim Awwad" <ibrahim_awwad(at)hotmail(dot)com(antispam)> wrote
in
> > > > message news:B0BB5831-AC8D-4184-A5F8-4E7E73CB9EB7@.microsoft.com...
> > > > > Dear All,
> > > > > Can I use DROP TABLE statement to drop more than one table at
once.
> > If
> > > > > not, how can I drop (delete) so many tables from the data base at
the
> > same
> > > > > time with the condition that I know a constant part on its name.
> > > > >
> > > > > Best Regards
> > > > > --
> > > > > *********
> > > > > IT Manager
> > > > > DeLaval Ltd.
> > > > > Cairo-Egypt
> > > > > *********
> > > > > |--|
> > > > > |Islam is peace not Terror|
> > > > > |--|
> > > >
> > > >
> > > >
> >
> >
> >|||Hi Again,
I think I got your idea, that to get a statement saying Drop Table ******
with all table names and execute them as a punch from the QA?
Am I right..
"Uri Dimant" wrote:
> Ibrahim
> Copy-Paste the output in the QA and press F5
> USE NorthWind
> SELECT 'DROP TABLE '+
> QUOTENAME(TABLE_SCHEMA) +
> QUOTENAME(TABLE_NAME)
> FROM INFORMATION_SCHEMA.TABLES
> WHERE
> TABLE_TYPE = 'BASE TABLE' AND
> OBJECTPROPERTY(OBJECT_ID(
> QUOTENAME(TABLE_SCHEMA) +
> '.' +
> QUOTENAME(TABLE_NAME)),
> 'IsMSShipped') = 0 AND QUOTENAME(TABLE_NAME) LIKE
> '%Customers%'
>
>
> "Ibrahim Awwad" <ibrahim_awwad(at)hotmail(dot)com(antispam)> wrote in
> message news:E52B7B9F-0B25-4503-A625-96FEC3526E2E@.microsoft.com...
> > Dear Uri,
> >
> > First thanks for your reply, but let me give you an idea about the
> > problem I have. Our DB is having about thousand tables and some of them
> > replicated automatically each year. So the tables take the pattern
> (SC010001)
> > so for example this file become (SC010101) for year 2001 and (SC010201)
> for
> > year 2002 ...etc. So all what I need for example is to delete all
> '01'
> > tables as example one time without riting theiere name one by one. I hope
> > that you got my idea.
> >
> > Best Regards
> >
> > "Uri Dimant" wrote:
> >
> > > Ibrahim
> > > create table #t1 (col int)
> > > create table #t2 (col int)
> > > create table #t3 (col int)
> > >
> > > DROP TABLE #t1,#t2,#t3
> > >
> > >
> > >
> > >
> > >
> > > "Ibrahim Awwad" <ibrahim_awwad(at)hotmail(dot)com(antispam)> wrote in
> > > message news:B0BB5831-AC8D-4184-A5F8-4E7E73CB9EB7@.microsoft.com...
> > > > Dear All,
> > > > Can I use DROP TABLE statement to drop more than one table at once.
> If
> > > > not, how can I drop (delete) so many tables from the data base at the
> same
> > > > time with the condition that I know a constant part on its name.
> > > >
> > > > Best Regards
> > > > --
> > > > *********
> > > > IT Manager
> > > > DeLaval Ltd.
> > > > Cairo-Egypt
> > > > *********
> > > > |--|
> > > > |Islam is peace not Terror|
> > > > |--|
> > >
> > >
> > >
>
>|||Correct
"Ibrahim Awwad" <ibrahim_awwad(at)hotmail(dot)com(antispam)> wrote in
message news:27F96530-6006-4E08-8E65-A08E816D5050@.microsoft.com...
> Hi Again,
> I think I got your idea, that to get a statement saying Drop Table
******
> with all table names and execute them as a punch from the QA?
> Am I right..
>
> "Uri Dimant" wrote:
> > Ibrahim
> > Copy-Paste the output in the QA and press F5
> >
> > USE NorthWind
> > SELECT 'DROP TABLE '+
> > QUOTENAME(TABLE_SCHEMA) +
> > QUOTENAME(TABLE_NAME)
> >
> > FROM INFORMATION_SCHEMA.TABLES
> > WHERE
> > TABLE_TYPE = 'BASE TABLE' AND
> > OBJECTPROPERTY(OBJECT_ID(
> > QUOTENAME(TABLE_SCHEMA) +
> > '.' +
> > QUOTENAME(TABLE_NAME)),
> > 'IsMSShipped') = 0 AND QUOTENAME(TABLE_NAME) LIKE
> > '%Customers%'
> >
> >
> >
> >
> > "Ibrahim Awwad" <ibrahim_awwad(at)hotmail(dot)com(antispam)> wrote in
> > message news:E52B7B9F-0B25-4503-A625-96FEC3526E2E@.microsoft.com...
> > > Dear Uri,
> > >
> > > First thanks for your reply, but let me give you an idea about the
> > > problem I have. Our DB is having about thousand tables and some of
them
> > > replicated automatically each year. So the tables take the pattern
> > (SC010001)
> > > so for example this file become (SC010101) for year 2001 and
(SC010201)
> > for
> > > year 2002 ...etc. So all what I need for example is to delete all
> > '01'
> > > tables as example one time without riting theiere name one by one. I
hope
> > > that you got my idea.
> > >
> > > Best Regards
> > >
> > > "Uri Dimant" wrote:
> > >
> > > > Ibrahim
> > > > create table #t1 (col int)
> > > > create table #t2 (col int)
> > > > create table #t3 (col int)
> > > >
> > > > DROP TABLE #t1,#t2,#t3
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > "Ibrahim Awwad" <ibrahim_awwad(at)hotmail(dot)com(antispam)> wrote
in
> > > > message news:B0BB5831-AC8D-4184-A5F8-4E7E73CB9EB7@.microsoft.com...
> > > > > Dear All,
> > > > > Can I use DROP TABLE statement to drop more than one table at
once.
> > If
> > > > > not, how can I drop (delete) so many tables from the data base at
the
> > same
> > > > > time with the condition that I know a constant part on its name.
> > > > >
> > > > > Best Regards
> > > > > --
> > > > > *********
> > > > > IT Manager
> > > > > DeLaval Ltd.
> > > > > Cairo-Egypt
> > > > > *********
> > > > > |--|
> > > > > |Islam is peace not Terror|
> > > > > |--|
> > > >
> > > >
> > > >
> >
> >
> >sql

Sunday, March 11, 2012

DROP INDEX gets Msg 3624

Hi all,
Please help.
I am running into problem of droping an Idex.
My statement is:
DROP INDEX tblPkgInfo.Tracking

I get the following:
Server: Msg 3624, Level 20, State 1, Line 1

Location: recbase.cpp:1371
Expression: m_nVars > 0
SPID: 51
Process ID: 2068

Connection Broken
----------

What do I do?What do you get when you run DBCC CHECKTABLE (tablename)?

where tablename is the name of your table

Friday, March 9, 2012

DROP DATABASE <@variable>

Hi,
I am getting an error for a very simple SQL statement below. I cannot figure
out why it fails. Is specifying a variable for the Drop Database command not
supported?
DECLARE @.mydb varchar(255)
set @.mydb = 'mytestdb'
DROP DATABASE @.mydbYou have to put it in dynamic sql:
DECLARE @.mydb varchar(255)
set @.mydb = 'mytestdb'
EXEC('DROP DATABASE ' + @.mydb)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Kevin" <nospam@.discussion.com> schrieb im Newsbeitrag
news:OT1ssOxWFHA.3676@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I am getting an error for a very simple SQL statement below. I cannot
> figure out why it fails. Is specifying a variable for the Drop Database
> command not supported?
> DECLARE @.mydb varchar(255)
> set @.mydb = 'mytestdb'
> DROP DATABASE @.mydb
>
>|||Hi
Variables are not supported.
Build a string up and use EXEC
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Kevin" <nospam@.discussion.com> wrote in message
news:OT1ssOxWFHA.3676@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I am getting an error for a very simple SQL statement below. I cannot
> figure out why it fails. Is specifying a variable for the Drop Database
> command not supported?
> DECLARE @.mydb varchar(255)
> set @.mydb = 'mytestdb'
> DROP DATABASE @.mydb
>
>|||Your sample statement worked. Thank you!
"Jens S $B!& (Bmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wro
te in
message news:%23LKU$UxWFHA.2540@.tk2msftngp13.phx.gbl...
> You have to put it in dynamic sql:
> DECLARE @.mydb varchar(255)
> set @.mydb = 'mytestdb'
> EXEC('DROP DATABASE ' + @.mydb)
>
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Kevin" <nospam@.discussion.com> schrieb im Newsbeitrag
> news:OT1ssOxWFHA.3676@.TK2MSFTNGP10.phx.gbl...
>|||Now the statement worked. Thank you.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:%23tnFKWxWFHA.2256@.TK2MSFTNGP14.phx.gbl...
> Hi
> Variables are not supported.
> Build a string up and use EXEC
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Kevin" <nospam@.discussion.com> wrote in message
> news:OT1ssOxWFHA.3676@.TK2MSFTNGP10.phx.gbl...
>

Wednesday, March 7, 2012

drop ALL indexes statement available?

I am writing a script that takes a database and will make all new indexes.
I have about 150 identical databases but they all have slightly different
indexes due to usage, profiler and tuning wizard set them all up separately.
So I want to start from scratch. Is there a statement I can use to drop all
of them at once, preferably also cropping the primary key and clustered
index? Otherwise I will have to look at the system tables, loop over the
user indexes, then drop them one at a time. If a public example script is
available that does that, I'd like to see it as well.
thanks,
CoryCory
SELECT
'DROP INDEX ' +
QUOTENAME(USER_NAME(o.uid)) +
'.' +
QUOTENAME(o.name) +
'.' +
QUOTENAME(i.name)
FROM sysobjects o
JOIN sysindexes i ON
i.id = o.id
WHERE i.indid BETWEEN 1 AND 254 AND
INDEXPROPERTY(i.id, i.name, 'IsStatistics') = 0 AND
INDEXPROPERTY(i.id, i.name, 'IsHypothetical') = 0 AND
NOT EXISTS
(
SELECT *
FROM sysobjects po
WHERE
po.parent_obj = o.id AND
po.name = i.name
) AND
OBJECTPROPERTY(o.id, 'IsMSShipped') = 0
"Cory Harrison" <charrison@.csiweb.com> wrote in message
news:uAN%23vg8LFHA.1156@.TK2MSFTNGP09.phx.gbl...
> I am writing a script that takes a database and will make all new indexes.
> I have about 150 identical databases but they all have slightly different
> indexes due to usage, profiler and tuning wizard set them all up
separately.
> So I want to start from scratch. Is there a statement I can use to drop
all
> of them at once, preferably also cropping the primary key and clustered
> index? Otherwise I will have to look at the system tables, loop over the
> user indexes, then drop them one at a time. If a public example script is
> available that does that, I'd like to see it as well.
>
> thanks,
> Cory
>
>|||Thank you, that works great. Now I just need to figure out what to do with
these clustered indexes...
thanks,
Cory
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eq0kYYEMFHA.3420@.tk2msftngp13.phx.gbl...
> Cory
> SELECT
> 'DROP INDEX ' +
> QUOTENAME(USER_NAME(o.uid)) +
> '.' +
> QUOTENAME(o.name) +
> '.' +
> QUOTENAME(i.name)
> FROM sysobjects o
> JOIN sysindexes i ON
> i.id = o.id
> WHERE i.indid BETWEEN 1 AND 254 AND
> INDEXPROPERTY(i.id, i.name, 'IsStatistics') = 0 AND
> INDEXPROPERTY(i.id, i.name, 'IsHypothetical') = 0 AND
> NOT EXISTS
> (
> SELECT *
> FROM sysobjects po
> WHERE
> po.parent_obj = o.id AND
> po.name = i.name
> ) AND
> OBJECTPROPERTY(o.id, 'IsMSShipped') = 0
>
> "Cory Harrison" <charrison@.csiweb.com> wrote in message
> news:uAN%23vg8LFHA.1156@.TK2MSFTNGP09.phx.gbl...
> separately.
> all
>

Sunday, February 19, 2012

DRILLTHROUGH MDX statement or SQL statement

Ours is homogeneous OLAP with source data coming from SQL Server.The staging server pulls data from Production server and serves the purpose of source for the Analysis server.

We are using web based client to display data returned after executing queries these queries can be of both types SQL as well as MDX.We can use DRILLTHROUGH MDX statement to get row level information but the same can also achieved by writing SQL statements that can be executed against the Staging database to get the desired records [rows].

I think 2nd option would be better in the case where we need the row level details in reports. The main reason for my assumption is, since the row level data doesn’t contain any aggregation; MDX or analysis server is useful if we require aggregation. So I believe the 2nd [executing SQL query] option would be better/faster in the case where we need the row level details in reports.

Please tell me what you think about the same. Which option is better; MDX with DRILLTHROUGH or SQL queries for row level detail reports?

I hope; I am clear with what I mean to say :)

Thanks in advance for any feedback.

Regards;

Rakesh

Hi Rakesh,

Assuming that you're using AS 2005, this Aug. 2005 OLAP Newsgroup thread also discusses drillthrough vs. straight SQL, amongst other issues:

http://groups.google.co.uk/group/microsoft.public.sqlserver.olap/browse_frm/thread/b67c7fc032e313a3?hl=en&

From:		Mosha Pasumansky [MS] - view profile
Date:		Wed, Aug 24 2005 7:46 pm
Email:		"Mosha Pasumansky [MS]" <mos...@.online.microsoft.com>
Groups:		microsoft.public.sqlserver.olap

> What I'm still seeking for is how to manage
> query performance and overhead with this increased flexibility, so that
> the solution works well.

As long as you don't query these fact (aka degenerate dimensions), there
will be no impact on performance. You can even make them hidden, so users
don't drag-and-drop them accidently, but you can still refer to them inside
your report or drillthrough actions.

> It seems that now, the only way for doing so is to write an application
> who
> SQL queries the relational database having the cell coordinates. Do you
> agree?

This may turn out to be pretty complex application, because it will have to
support all the flexibility and power of DSVs in addition to having to deal
with multiple partitions etc. So creating fact dimensions, is indeed the
correct approach.

--
==================================================
Mosha Pasumansky - http://www.mosha.com/msolap
Analysis Services blog at http://www.sqljunkies.com/WebLog/mosha
Development Lead in the Analysis Server team
>>

DRILLTHROUGH MDX statement n SQL AS 2005

With SQL RS 2000 and SQL AS 2000 I'm using DRILLTHROUGH MDX statement. But when I try to use it with SQL RS 2005 and SQL AS 2005 I get an error:

TITLE: Microsoft Visual Studio

Query preparation failed.

ADDITIONAL INFORMATION:

Failed to parse the query to detect if it is MDX or DMX. (MDXQueryGenerator)

Do you have any solution?

If you're trying to use the Analysis Services Provider in RS 2005, you may want to try the OLE DB for OLAP 9.0 Provider instead, based on this thread from the SQL Server OLAP newsgroup:

http://groups.google.com/group/microsoft.public.sqlserver.olap/browse_frm/thread/f5e2bc488f4f78bf/4870097ed8fcffda#4870097ed8fcffda

How about trying the OLE DB option? I couldn't get the following AW
Drillthrough query to work with the RS 2005 Analysis Services Provider;
but it returned 8 records when I used OLE DB for OLAP 9.0 instead:

Drillthrough
Select [Ship Date].[Calendar].[Calendar Year].&[2001] on columns,
[Customer].[Customer Geography].[State-Province].&[TAS]& Automobile on rows
from [Adventure Works]
where [Measures].[Internet Order Quantity]

Thursday, March 29, 2012

Dropping Permissions

Dropping Permissions

Dropping Permissions

Thursday, March 22, 2012

drop. temp. table proc.

Wednesday, March 21, 2012

Drop table statement didn't actually drop table...

Drop table statement didn't actually drop table...

DROP TABLE

DROP TABLE

DROP TABLE

Sunday, March 11, 2012

DROP INDEX gets Msg 3624

Friday, March 9, 2012

DROP DATABASE <@variable>

Wednesday, March 7, 2012

drop ALL indexes statement available?

Sunday, February 19, 2012

DRILLTHROUGH MDX statement or SQL statement

DRILLTHROUGH MDX statement n SQL AS 2005

DSO in SQL2005

Blog Archive

About Me