Dropping An Indexed Column

I have inherited a table with dozens of columns that I no longer want. I want to drop these columns.

So I tried
"ALTER TABLE mydata DROP BLOCK_ID"

and it get an error of: cannot delete a field that is part of an index. How do I get around this?

(BLOCK_ID is the field name of my indexed column)
(The non-indexed ones drop fine.)First remove the column you want to drop from all the indexes that refer it. If there are indexes that refer only that column just drop those. Then you can drop the column.

Cheers,
Suren.|||Yes, I get that I have to drop the index(es) -- but how do I find out which indexes this column is in?

I'm building up to write some scripts to automatically drop a long-list of unwanted columns - how does one go about figuring out what index a field is in? And/or is there a sql way of saying "drop this column and it's indexes" ?|||Well to do that the mist easiest way is to use a graphical tool that organise indexes unser each table and to go through the index and remove the coloms.

If you are thinking of writing scripts then you should select from catalog tables such as user_indexes and user_inx_cols. I think I got the names correct.

Dropdownlist in Textfield like Excel 'autofilter'

I'd like to build a dropdownlist in the texfield which describes the columns. This dropdownlist should show all distinct values of the column like the 'autofilter' in excel. This sholuld be done directly in the report not as a parameter in the head. Any ideas ?
*****************************************
* This message was posted via http://www.sqlmonster.com
*
* Report spam or abuse by clicking the following URL:
* http://www.sqlmonster.com/Uwe/Abuse.aspx?aid=fb75b6199de248a2988f407b48fc9791
*****************************************Hi
This really interests me 2. Have you already received feedback on this issue?
Koen
"Psycho Dad via SQLMonster.com" wrote:
> I'd like to build a dropdownlist in the texfield which describes the columns. This dropdownlist should show all distinct values of the column like the 'autofilter' in excel. This sholuld be done directly in the report not as a parameter in the head. Any ideas ?
> *****************************************
> * This message was posted via http://www.sqlmonster.com
> *
> * Report spam or abuse by clicking the following URL:
> * http://www.sqlmonster.com/Uwe/Abuse.aspx?aid=fb75b6199de248a2988f407b48fc9791
> *****************************************
>sql

Drop index error

Dear
I would like to using script to drop index and columns automatically. When I
run the script , it occur a error :
Cannot drop the index 'ADDRESS._WA_Sys_SHORTNAME_5A2A0B13', because it does
not exist in the system catalog.
I don't know what is the problem here and the "XXX_WA_Sys_XXXX" work for,
Can anyone point out the problem and give me a solution. Thanks
And my script is :
Declare Live2StdCur Scroll Cursor For
select Table_Name, Column_Name from Live.Information_Schema.columns Live
where not Exists (select * from Std.Information_Schema.columns Std where
Live.Table_Name = Std.Table_Name and Live.Column_Name = Std.Column_Name)
Order by Table_Name
For Read Only
Open Live2StdCur
Fetch First From Live2StdCur Into @.TableName, @.ColumnName
While @.@.Fetch_Status = 0
Begin
Set @.TableId = Object_id(@.TableName)
Set @.ColumnId = (select colid from syscolumns where id =
Object_id(@.TableName) and name = @.ColumnName )
-- Drop default value
Set @.constraint_name = (select name from sysobjects where parent_obj =
@.TableId and info = @.ColumnId)
EXEC ('ALTER TABLE ' +@.TableName + ' DROP CONSTRAINT ' + @.constraint_name)
-- Drop index
Declare IndexCur Scroll Cursor For
select name from sysindexes where indid in (select indid from sysindexkeys
where id = @.TableId and colid = @.ColumnId) and id = @.TableId
For Read Only
Open IndexCur
Fetch First From IndexCur Into @.index_name
While @.@.Fetch_Status = 0
Begin
EXEC ('Drop Index ' + @.TableName + '.' + @.index_name)
Fetch Next From IndexCur Into @.index_name
End
Close IndexCur
-- Drop Column
EXEC ('ALTER TABLE ' + @.TableName + ' DROP COLUMN ' + @.ColumnName)
-- Show information
print 'Table = ' + @.TableName + ', Column = ' + @.ColumnName
Fetch Next From Live2StdCur Into @.TableName, @.ColumnName
End
Close Live2StdCur
Deallocate IndexCur
Deallocate Live2StdCurTypically the indexes beginning with "_WA_Sys_" are statistics that SQL
Server generates. You ought to exclude them from your script by adding
and INDEXPROPERTY([id], [name], N'IsStatistics') = 0
to your inner cursor where you deal with the indexes.
HTH
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Gary wrote:

>Dear
>I would like to using script to drop index and columns automatically. When
I
>run the script , it occur a error :
>Cannot drop the index 'ADDRESS._WA_Sys_SHORTNAME_5A2A0B13', because it does
>not exist in the system catalog.
>I don't know what is the problem here and the "XXX_WA_Sys_XXXX" work for,
>Can anyone point out the problem and give me a solution. Thanks
>And my script is :
>Declare Live2StdCur Scroll Cursor For
>select Table_Name, Column_Name from Live.Information_Schema.columns Live
>where not Exists (select * from Std.Information_Schema.columns Std where
>Live.Table_Name = Std.Table_Name and Live.Column_Name = Std.Column_Name)
>Order by Table_Name
>For Read Only
>Open Live2StdCur
>Fetch First From Live2StdCur Into @.TableName, @.ColumnName
>While @.@.Fetch_Status = 0
>Begin
>Set @.TableId = Object_id(@.TableName)
>Set @.ColumnId = (select colid from syscolumns where id =
>Object_id(@.TableName) and name = @.ColumnName )
>-- Drop default value
>Set @.constraint_name = (select name from sysobjects where parent_obj =
>@.TableId and info = @.ColumnId)
>EXEC ('ALTER TABLE ' +@.TableName + ' DROP CONSTRAINT ' + @.constraint_name)
>-- Drop index
> Declare IndexCur Scroll Cursor For
> select name from sysindexes where indid in (select indid from sysindexkey
s
>where id = @.TableId and colid = @.ColumnId) and id = @.TableId
> For Read Only
> Open IndexCur
> Fetch First From IndexCur Into @.index_name
> While @.@.Fetch_Status = 0
> Begin
> EXEC ('Drop Index ' + @.TableName + '.' + @.index_name)
> Fetch Next From IndexCur Into @.index_name
> End
> Close IndexCur
>-- Drop Column
>EXEC ('ALTER TABLE ' + @.TableName + ' DROP COLUMN ' + @.ColumnName)
>-- Show information
>print 'Table = ' + @.TableName + ', Column = ' + @.ColumnName
>Fetch Next From Live2StdCur Into @.TableName, @.ColumnName
>End
>Close Live2StdCur
>Deallocate IndexCur
>Deallocate Live2StdCur
>
>
>|||Dear Mike
Thanks for your reply. I have solved the problem
but there is another problem here. It seem like the index type cannot drop
again
The index 'I_698DIMIDX' is dependent on column 'INVENTPROJID'.
Should I add any criteria again on the select statment
Gary
"Mike Hodgson" wrote:

> Typically the indexes beginning with "_WA_Sys_" are statistics that SQL
> Server generates. You ought to exclude them from your script by adding
> and INDEXPROPERTY([id], [name], N'IsStatistics') = 0
> to your inner cursor where you deal with the indexes.
> HTH
> --
> *mike hodgson* |/ database administrator/ | mallesons stephen jaques
> *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
> *E* mailto:mike.hodgson@.mallesons.nospam.com |* W* [url]http://www.mallesons.com[/url
]
>
> Gary wrote:
>
>|||Sorry
The whole error message are:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'CONSTRAINT'.
Server: Msg 5074, Level 16, State 8, Line 1
The index 'I_698DIMIDX' is dependent on column 'INVENTPROJID'.
Server: Msg 5074, Level 16, State 1, Line 1
The index 'I_698PROJIDIDX' is dependent on column 'INVENTPROJID'.
Server: Msg 5074, Level 16, State 1, Line 1
The index 'I_698DIMIDX' is dependent on column 'INVENTPROJID'.
Server: Msg 5074, Level 16, State 1, Line 1
The index 'I_698PROJIDIDX' is dependent on column 'INVENTPROJID'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN INVENTPROJID failed because one or more objects
access this column.
"Gary" wrote:
> Dear Mike
> Thanks for your reply. I have solved the problem
> but there is another problem here. It seem like the index type cannot drop
> again
> The index 'I_698DIMIDX' is dependent on column 'INVENTPROJID'.
> Should I add any criteria again on the select statment
> Gary
> "Mike Hodgson" wrote:
>|||Gary,
Cursor loops (especially nested loops) can be difficult to debug. What
I often do, which I find quite helpful especially when building up
complex dynamic strings to EXEC at runtime, is change the "EXEC (...)"
statements to "PRINT (...)" so I can see exactly what T-SQL commands the
SQL server is trying to execute. Then you can take the results of that
(with all the print statements) and, as that should be a valid T-SQL
script, execute the statements either one at a time (in order) or in
small batches to see where it's going wrong. It often becomes blatantly
obvious where the mistake is when you do this.
One thing I notice is you are declaring the inner cursor (IndexCur)
inside the outer WHILE loop but you're only deallocating it outside the
outer loop. The OPEN & CLOSE are fine and open & close the resultset
appropriately, but how you've got the DECLARE & DEALLOCATE I would think
would result in the same cursor being declared multiple times but the
resources used by the cursor would not get released each time. SQL
Server may be able to handle this odd case (not sure), but in most
programming languages that would result in a memory/resource leak.
I think the problem is in the fact that you're using variables in the
declaration of your inner cursor (IndexCur) but since you're not
deallocating the cursor before you declare it again (i.e. at the end of
the inner loop), the different variable values each time through the
outer loop will not be taken into account when the cursor is declared
again. This would mean that for each iteration of the outer loop, the
inner cursor would have the same declaration and so you'd be working on
the same resultset for IndexCur each time...I think. BOL describes this
on its "DECLARE CURSOR" page:
Variables may be used as part of the /select_statement/ that
declares a cursor. Cursor variable values do not change after a
cursor is declared. In SQL Server version 6.5 and earlier, variable
values are refreshed every time a cursor is reopened.
I may be off-base with this thought but you should be able to tell if
this is the case or not pretty quickly by changing your "EXEC (...)"
statements to "PRINT (...)" and looking at the resultant T-SQL statements.
It's always a good idea to have your DECLARE/DEALLOCATE and OPEN/CLOSE
statements at the same scope as each other so they're always a matching
pair in terms of scope.
Also, there's not much point in making the cursors SCROLL cursors since
the only operation you're doing on them is FETCH NEXT (the FETCH FIRST
statements in this context do the same as a FETCH NEXT).
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Gary wrote:
>Sorry
>The whole error message are:
>Server: Msg 170, Level 15, State 1, Line 1
>Line 1: Incorrect syntax near 'CONSTRAINT'.
>Server: Msg 5074, Level 16, State 8, Line 1
>The index 'I_698DIMIDX' is dependent on column 'INVENTPROJID'.
>Server: Msg 5074, Level 16, State 1, Line 1
>The index 'I_698PROJIDIDX' is dependent on column 'INVENTPROJID'.
>Server: Msg 5074, Level 16, State 1, Line 1
>The index 'I_698DIMIDX' is dependent on column 'INVENTPROJID'.
>Server: Msg 5074, Level 16, State 1, Line 1
>The index 'I_698PROJIDIDX' is dependent on column 'INVENTPROJID'.
>Server: Msg 4922, Level 16, State 1, Line 1
>ALTER TABLE DROP COLUMN INVENTPROJID failed because one or more objects
>access this column.
>
>"Gary" wrote:
>
>

Drop extended property "MS_Description" of ALL tables and ALL columns

Hi,

Is there an easy way (a sql script) to drop the "MS_Description" of all tables and all columns in my database?

Regards,
Alejandroo

These queries will produce a script that you can run to drop these properties. You can automate with a cursor, or you might want to add a GO to the end of each EXEC statement.

--tables

select 'EXEC sp_dropextendedproperty

@.name = ''MS_Description''

,@.level0type = ''schema''

,@.level0name = ' + object_schema_name(extended_properties.major_id) + '

,@.level1type = ''table''

,@.level1name = ' + object_name(extended_properties.major_id)

from sys.extended_properties

where extended_properties.class_desc = 'OBJECT_OR_COLUMN'

and extended_properties.minor_id = 0

and extended_properties.name = 'MS_Description'

--columns

select 'EXEC sp_dropextendedproperty

@.name = ''MS_Description''

,@.level0type = ''schema''

,@.level0name = ' + object_schema_name(extended_properties.major_id) + '

,@.level1type = ''table''

,@.level1name = ' + object_name(extended_properties.major_id) + '

,@.level2type = ''column''

,@.level2name = ' + columns.name

from sys.extended_properties

join sys.columns

on columns.object_id = extended_properties.major_id

and columns.column_id = extended_properties.minor_id

where extended_properties.class_desc = 'OBJECT_OR_COLUMN'

and extended_properties.minor_id > 0

and extended_properties.name = 'MS_Description'

|||Thanks a lot Louis, this works perfectly!

Drop down list for columns

What is the best practice to build drop down lists for a database?

for what?

for the login

|||You know a drop down list so on the front-end a user has predefined choices instead of having to type everything in each time.|||

You're asking about the best practices for the UI on the client side, then?

I think that depends on a coulpe of different things. If the query to generate the list is very simple, then I'd probably write code to retreive the data and populate the list each time the UI is shown.

But there are some exceptions. If the data is likely to change (that is, other users are adding or removing things that would show up in the list) I would end up adding a "Refresh" pushbutton next to the dropdown. That way, the user can refresh the content of the list without leaving and re-opening the form or dialog where the control lives. If the control actually is a part of more stable UI (like in a toolbar that's alive as long as the application is alive) you'll certainly want to provdie a refresh button. In places where screen area is limited, you might let the user hold CTRL while clicking the drop-down arrow to cause a refresh.

If the query is complicated and has a long run time, I'd look at ways to either cache the result (by storing it locally with the app; in a config file, or the registry, or so on) or by storing the user's most recent choice. (And I'd see if there was a way to tune the query!) This might no work if there's also lots of changes to the list.

If there are lots of results in the list, you should be sure to implement word wheeling for the control.

And that's all that I can think of for now. I hope it helps; if you have further questions, please do give us some more details about what it is you're doing and we'll see if we can help.

Drop Default values in Columns

I have Column A and Column B in my Table they have Default values 'A' and 0 respectively.

I want to alter table.

I wrote

ALTER TABLE EMPLOYEE

DROP DEFAULT FOR COLUMN A,

DROP DEFAULT FOR COLUMN B

GO

It does not work. I am new to Sql Server. Can you help me how to write alter table statement for dropping those default values.

I will really appreciate it.

Nature:

Run an SP_HELP on your table:

sp_help employee

and look for something like this:

-- constraint_type
-- -
-- DEFAULT on column a

-- constraint_name
-- -
-- DF__EMPLOYEE__A__461FBB34

And then execute something like this:

alter table dropDefault
drop constraint DF__EMPLOYEE__A__461FBB34

Dave

|||Sorry, that table name in the DROP statement must be EMPLOYEE and not "dropDefault"|||

Mugambo wrote:

Sorry, that table name in the DROP statement must be EMPLOYEE and not "dropDefault"

You can edit your posts...|||

Thanks, Phil, I keep forgetting. PLEASE keep reminding me about this until I get it right.

Dave

Drop Column with default constraint in T-SQL

Hello,
I'm making an SQL script that has to drop some columns. The problem is that
the column has a default value and therefore I can't use the DROP COLUMN
directly. I've got to drop the 'default' constraint, but the problem is that
we have to use this script on different database and then the constraint nam
e
is not always the same.
(example:)
DB1 -> DF_COLUMNX_ddf87d67s68
DB2 -> DF_COLUMNX_ddf79djks90
I know how to get the Constraint name but I can't use that as a variable in
the DROP CONSTRAINT function.
Has someone a solution for this problem? It has to be done with scripting!examnotes (Martijn@.discussions.microsoft.com) writes:
> I'm making an SQL script that has to drop some columns. The problem is
> that the column has a default value and therefore I can't use the DROP
> COLUMN directly. I've got to drop the 'default' constraint, but the
> problem is that we have to use this script on different database and
> then the constraint name is not always the same.
> (example:)
> DB1 -> DF_COLUMNX_ddf87d67s68
> DB2 -> DF_COLUMNX_ddf79djks90
> I know how to get the Constraint name but I can't use that as a variable
> in the DROP CONSTRAINT function.
> Has someone a solution for this problem? It has to be done with scripting!
SELECT @.default_name = o2.name
FROM syscolumns c
JOIN sysobjects o ON c.id = o.id
JOIN sysobjects o2 ON c.cdefault = o2.id
WHERE o.name = @.tbl
AND c.name = @.col
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||You'll have to resort to dynamic SQL:
declare @.constraint varchar(8000), @.str varchar (8000)
set @.constraint = 'DF_COLUMNX_ddf87d67s68'
set @.str = 'alter table MyTable drop constraint ' + @.constraint
exec (@.str)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Martijn" <Martijn@.discussions.microsoft.com> wrote in message
news:3C62E773-C60A-46ED-A8A0-32E5AFDC7ACF@.microsoft.com...
Hello,
I'm making an SQL script that has to drop some columns. The problem is that
the column has a default value and therefore I can't use the DROP COLUMN
directly. I've got to drop the 'default' constraint, but the problem is that
we have to use this script on different database and then the constraint
name
is not always the same.
(example:)
DB1 -> DF_COLUMNX_ddf87d67s68
DB2 -> DF_COLUMNX_ddf79djks90
I know how to get the Constraint name but I can't use that as a variable in
the DROP CONSTRAINT function.
Has someone a solution for this problem? It has to be done with scripting!|||To have more control over constraint names, create constraints using the
ALTER TABLE:
alter table owner.table
add constraint constraint_name
default (<default_value | default_expression> )
for column_name
with values
go
This way you control the names of constraints (in this case the name of the
default constraint).
ML

drop ##temp

due to unavoidable reasons i had to use a ## temp table in a SP,

ie i had to dynamically create a table whose (number of)columns i come to know at runtime..

if i do thi ::set @.sql = 'create #table....select some columns _ append varchar(10)'

then insert into #temp....temp is not valid here..so i used ##temp

now i need to explicitly drop it...also in catch block , i need to make a provision for droping it incase of an error in runnin proc...some kind os IF EXISTS drop ##temp.... as i dont know if it'll be created by that time or not..how do i do it..there is ofcource no entry in sysobjects....where is the entry for temp tables...tempdb dosent have system tables..!!

Can you provide SQl of SP...

~Mandip

|||

Nitin:

Since in this case it is a global temp table -- that is, it starts with ##, it will explicitly appear in sysobjects in tempdb:

create table dbo.##temp
( what varchar (5)
)

select uid, id, left (name, 20) as name from tempdb.dbo.sysobjects where name = '##temp'

if exists
( select 0 from tempdb.dbo.sysobjects
where name = '##temp'
and type = 'U'
)
begin
print 'Dropping the table.'
print ' '
drop table ##temp
end

select id, left (name, 20) as name from tempdb.dbo.sysobjects where name = '##temp'

-- -
-- O U T P U T :
-- -

-- uid id name
-- -- --
-- 1 853242293 ##temp
--
-- (1 row(s) affected)

-- Dropping the table.
--
-- id name
-- -- --

-- (0 row(s) affected)

Dave

|||

Thanks Dave.....

actually i was tryin this...

select * from sysobjects where name like '##temp'

hence the question.....now as u told it wors fine while i do this :: select * from tempdb.dbo.sysobjects where name like '##temp'

tell me if 2 ppl r running this sp simultaneously , will 1 of them get an error (##temp already exist..) or he'll have to wait for a tempdb lock.. ?

|||

Nitin:

Yes, because you are using a GLOBAL temp table this is a potential problem. However, since you are in a stored procedure, the procedure will automatically drop the temp table when it goes out of context should you use a non-global temp table -- one that starts with # instead of ##. I don't see why this would not be acceptable. Are you doing something in which you potentially need the temp table to have persistence beyond the scope of the procedure?

Dave

|||

dave :

i am creating my temp table by creating a sql string for it and then executing it..as num of columns is decided on the runtime..below is the query..

declare @.mytable varchar(500)

set @.mytable = 'CREATE TABLE ##temp (UserId int,'

select @.mytable = @.mytable + 'Plan_' + cast(PlanCode AS nvarchar(10)) + ' varchar(5),'

FROM (SELECT distinct PaymentPlanPlanTypeCode FROM #sometable) p

SET @.mytable = LEFT(@.mytable, LEN(@.mytable) - 1)

SET @.mytable = @.mytable + ')'

print @.mytable

exec (@.mytable)

i need this temp table in this 1 SP only but when i replace the ##temp with , #temp , the table is not getting created... i dont know why..probably some scope problem... also i ran out of the option of using table datatype as my proc further refers this table and i cant declare it inside a string..

see this as well :

--1

declare @.sql varchar(100)

set @.sql='create table ##tem (a int)'

exec (@.sql)

select * from #tem

error : invalid object #tem

--2

create table #tem (a int)

select * from #tem

gives the result.

|||

Nitin:

You are right, you do have a scope problem. I have a rather"dirty" idea, but I will have to test it out and unfortunately I won't have any time for at least an hour or so. Hopefully, you can get a better idea from somebody else can get you a better idea than what I have. I will check back in a while and if nobody else has come up with something I will test out my "dirty trick."

Dave

|||

Nitin:

One more question: I am assuming that this is not a "performance critical" procedure and that even though it is possible for multiple users to execute this procedure at the same time it is not likely. Is that assumption correct or is it rather likely that multiple users will execute your procedure concurrently?

Dave

|||

hi ,

this proc is for some mis report...though data will be large, its unlikely that more then a few users will use it... but agn..can be more then 1 at a time...

so i guess ur assumption can hold..

|||

Nitin:

This is my "dirty" suggestion. I tried it out and it seems to work. If you get another idea it will probably be better. Good luck.

Dave

-- --
-- First, I think I might have used the TABLOCK optimizer hint less than a handful
-- of times over my entire carreer. I don't think I've ever used TABLOCKX other
-- than in demo code. So to begin with I am iffy on the code that follows.
--
-- With that said, understand that what this code tries to use the execute string.
-- to build the format of the target table into a global temp table. Once that is
-- done a SELECT INTO is used to grab that format and use it to create the
-- intended local temp table. Once that is done the global temp table can be
-- dropped. This TABLOCKX optimizer hint means that this portion of the code
-- is single-threaded and is definite bottleneck. If this is not an intensely
-- used query this might be sufficient.
--
-- Unfortunately, there are additional bottlenecking problems. As I suspected,
-- The "select into" portion of the query puts exclusive locks on keys (1)
-- SYSOBJECTS, (2) SYSINDEXES and (3) SYSCOLUMNS of the tempdb database. Also,
-- exclusive intent locks are put on all three of these tables plus
-- (1) SYSCOMMENTS, (2) SYSDEPENDS, and (3) SYSPERMISSIONS and (4) SYSPROPERTIES.
--
-- If you wish to test this out, just comment out the COMMIT TRAN command,
-- run the procedure and then run SP_LOCK. To release the locks exeucte the
-- COMMIT TRAN statement.
--
-- Therefore, it is critical that if this kind of code is used in production that
-- the construction of the TEMP table take place OUTSIDE of the actual processing
-- transaction so that the code below executes in as few microseconds as possible
-- and reduces the profile of the bottleneck.
--
-- I also tested this with no transaction enclosures and exeucted the WAITFOR so
-- that I could see how it locked outside of any transaction enclosures. I
-- commented out my BEGIN TRAN and END TRAN satements, ran with the WAITFOR in
-- affect and did an SP_LOCK with an outside connection. No locks were retained.
-- What to understand with all of this is that (1) the TABLOCKX hint will single
-- thread this and that is not particular good; however, (2) SQL Server issues
-- locks that might otherwise single-thread you briefly anyway; so this might not
-- be TOO bad. (3) Always be careful how you use SELECT INTO syntax; it can
-- also bite you.
--
-- I really don't like this code; but if you don't get any other suggestion, it
-- might be worth a try.
--
-- Dave
--
-- --
--begin tran doWhat

exec ( 'create table ##what (what varchar (20) ) ' )

select * into #what from ##what (TABLOCKX) where 1=0

if exists
( select 0 from tempdb.dbo.sysobjects
where type = 'U'
and name = '##what'
)
drop table ##what

select * from #what

--waitfor delay '0:01:00.000'

drop table #what

--commit tran

go

-- -
--
-- -

-- spid dbid ObjId IndId Type Resource Mode Status
-- -- - - --
-- 55 2 6 0 TAB IX GRANT
-- 55 2 1 0 TAB IX GRANT
-- 55 2 2 0 TAB IX GRANT
-- 55 2 12 0 TAB IX GRANT
-- 55 2 9 0 TAB IX GRANT
-- 55 2 11 0 TAB IX GRANT
-- 55 2 1529959413 0 TAB Sch-M GRANT
-- 55 2 3 2 KEY (9401125ee398) X GRANT
-- 55 2 1 3 KEY (f50024d97b7f) X GRANT
-- 55 2 1 2 KEY (fc00a92d9bd9) X GRANT
-- 55 2 2 1 KEY (bc009dbece03) X GRANT
-- 55 2 1 1 KEY (bc004820585b) X GRANT
-- 55 2 3 1 KEY (bd00770e8a50) X GRANT
-- 55 2 1513959356 0 TAB Sch-M GRANT
-- 55 2 2 1 KEY (f500783daf5c) X GRANT
-- 55 2 3 1 KEY (f60025843207) X GRANT
-- 55 2 1 3 KEY (bc003d203e1f) X GRANT
-- 55 2 1 1 KEY (f50051d91d3b) X GRANT
-- 55 2 1 2 KEY (9b16f50455d2) X GRANT
-- 55 2 3 2 KEY (cd019d01c693) X GRANT
-- 55 33 0 0 DB S GRANT
-- 55 2 3 0 TAB IX GRANT

|||

thanks a lot for ur time dave....im tempted to use this..i'll do some testing and go for it... ya i cant run away from select into...infact that was the thing i was trying earlier while generating the query string...so im kinda ready for that...

cheers

nitin

|||

You can do below:

create table #tbl( /* put fixed columns here. those that are not decided dynamically )

-- use ALTER TABLE to add the columns dynamically

exec('alter table "#tbl" add c1 int, c2 int...')

select ...

Note, however that this has performance implications due to the excessive amount of recompilations triggered due to the schema changes. In above case, pretty much every line will trigger recompile of the entire SP (SQL2000) or statement (SQL2005).

Drill-To-Detail in ProClarity

Hi

When we do a Drill-To-Detail in ProClarity, I get the "Key" columns of the dimensions. This would not make sense to the user.

All my dimensions except a few like Date dimension, have 'KEY' as one of the attributes. This 'Key' attribute is set with Usage = Key. I presume that this is a sort of primary key for the dimension. The relational tables of all dimensions contain the Key column, which is referenced in the Fact table.

Although I dont display the Key/Id attribute in any cubes, I thought that they are necessary for faster access to the data and for aggregations.

Either I have to remove the 'Drill-To-Detail' feature from ProClarity or make it sensible to users.

What I tried is: I deleted the Key attribute and made Name attribute as Usage=Key in one of the dimensions. Then I made all attributes directly or indirectly related to the new key attribute (NAME). Next I re-deployed the ProClarity graph without the 'Key' column attribute. Now 'NAME' is the key attribute. ........ but when i did a Drill-To-Detail, I am only seeing 'Unknown' for the paticulary dimension.

so finally, what do i need to do to solve this problem?

Regards

Hi Vijay,

From your description, it sounds like you're using AS 2005. In that case, you should be able to control the columns returned by a Drill-Through query (which is what I think the Proclarity Drill-To-Detail executes) by configuring a default Drillthrough Action. I think that tweaking the key columns of dimensions for this purpose may cause other issues.

This MSDN paper describes how to configure DrillThrough actions - you can also refer to the Adventure Works "xxDetails" actions for examples:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5_anservdrill.asp

>>

Enabling Drillthrough in Analysis Services 2005

T.K. Anand
Microsoft Corporation

July 2005

Applies to:
SQL Server 2005 Analysis Services

Summary: Discover the new Analysis Services 2005 drillthrough architecture. See how to set up drillthrough in Analysis Services 2005 and get guidance on migrating drillthrough settings from Analysis Services 2000 databases.

...

Clearly drillthrough fits in very cleanly into the actions framework. But the real advantage of drillthrough actions is that it provides the cube designer with the ability to pre-define the return columns of the DRILLTHROUGH statement (Figure 2). This is analogous to the Analysis Services 2000 experience where the database administrator specifies the tables and columns in the Drillthrough Options dialog in Analysis Manager.

There is an interesting Boolean property called Default on a drillthrough action. A cube can have multiple drillthrough actions with Default=true. The Default property does not affect the behavior of the action itself. When a client sends a DRILLTHROUGH statement that does not contain the RETURN clause, the server looks for a default drillthrough action whose target subspace contains the cell coordinate for which drillthrough is being executed. If such an action is found, the server uses the return columns from that action. If there are multiple drillthrough actions that meet these criteria, the server picks one arbitrarily. Thus default drillthrough actions enable the cube designer to override the default RETURN clause.

...

>>

|||

Thanks Deepak.

Will try this and let you know.

Regards

drill-through from matrix subtotals

I have a matrix report (Report1) with rowgroup and columngroup. Both
contain also totals (defined as group subtotals). The number of rows and
columns varies.
Area1 Area2 Totals
Product 1 10 12 22
Product 2 7 11 18
Totals 17 23 40
I also have another report where I can list this data more detailed
(Report2). Report2 uses Product and Area as parameters where possible
values are 'Product 1', 'Product2' and 'All Products' (Area respectively).
Now I want to drill-through from Report1 into details in Report2 by
clicking the cell containing the number. I use 'Jump to report'-navigation
and fill the parameters from Report1 matrix dataset. This works fine as
long as I start drill-through from data cell ie. click numbers 10,12,7 or
11. The problem is that also the 'Totals' appear as links, but the
parameters are not filled in correctly.
So the question is how to define the parameters in 'Jump to report' so that
when clicking Product 1 totals (22) the parameters to Report2 are set to
Product 1 - All Areas.
-pasi
--
Message posted via http://www.sqlmonster.comPlease check the MSDN documentation about the InScope function:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_0jmt.asp
With InScope you can determine the current scope of a matrix cell and set
the drillthrough parameters accordingly. I.e. you would use an
IIF-expression to set the value of the drillthrough parameters correctly
based on the InScope return values. Note: a matrix cell is "in scope" of
column and row groupings, so you need at least two InScope function calls in
the case where you have 1 dynamic row and 1 dynamic column grouping. E.g.
=iif(InScope("ColumnGroup1"), iif(InScope("RowGroup1"), "In Cell", "In
Subtotal of RowGroup1"), iif(InScope("RowGroup1"), "In Subtotal of
ColumnGroup1", "In Subtotal of entire matrix"))
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Pasi Norrbacka via SQLMonster.com" <forum@.nospam.SQLMonster.com> wrote in
message news:dc114ab8cd48487a97ef6575f7bc7185@.SQLMonster.com...
>I have a matrix report (Report1) with rowgroup and columngroup. Both
> contain also totals (defined as group subtotals). The number of rows and
> columns varies.
> Area1 Area2 Totals
> Product 1 10 12 22
> Product 2 7 11 18
> Totals 17 23 40
> I also have another report where I can list this data more detailed
> (Report2). Report2 uses Product and Area as parameters where possible
> values are 'Product 1', 'Product2' and 'All Products' (Area respectively).
> Now I want to drill-through from Report1 into details in Report2 by
> clicking the cell containing the number. I use 'Jump to report'-navigation
> and fill the parameters from Report1 matrix dataset. This works fine as
> long as I start drill-through from data cell ie. click numbers 10,12,7 or
> 11. The problem is that also the 'Totals' appear as links, but the
> parameters are not filled in correctly.
> So the question is how to define the parameters in 'Jump to report' so
> that
> when clicking Product 1 totals (22) the parameters to Report2 are set to
> Product 1 - All Areas.
> -pasi
> --
> Message posted via http://www.sqlmonster.com|||Thanks, it works.
--
Message posted via http://www.sqlmonster.com