Dropping tempe tables

My query creates a new temp table every time a report is run. I need to check
if the table exists before I drop it and perform a select into. Since SQL
Server appends an ID (session ID?) to the temp table name, I am unable to
drop the table prior to running the query. Is there a better way to handle
this? Any help would be appreciated.Try this. make sure the temp table does not exist, and you can do it this way.
Drop your new table at the end if your query.
CREATE PROCEDURE [dbo].[Test] AS
Select top 5 * into #temp from Orders
Select * from #temp
Drop Table #temp
GO
"DrM" wrote:
> My query creates a new temp table every time a report is run. I need to check
> if the table exists before I drop it and perform a select into. Since SQL
> Server appends an ID (session ID?) to the temp table name, I am unable to
> drop the table prior to running the query. Is there a better way to handle
> this? Any help would be appreciated.

Dropping stored procedures

Hello!
I've written a script to drop all the SPs from a given DB:
DECLARE @.name nvarchar(200)
DECLARE list CURSOR FOR
SELECT name FROM sysobjects where xtype = 'p'
OPEN list
FETCH NEXT FROM list INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.name = 'drop procedure ' + @.name
EXEC sp_executesql @.name
FETCH NEXT FROM list INTO @.name
END
CLOSE list
DEALLOCATE list
GO
When I'm running it from Query Analyzer it works fine, but when running it
from a small utility (which all id does is opening a connection to the DB
and running the same script), only some of the SPs are dropped.
I'm now looking at the utility, but is there any problem with the SQL scirpt
(or a better way to the task (drop all of the SPs in a DB, without knowing
the names of them all)).
Thanks!
RoeeTry to explicitly spcify the owner of the procedure in the Drop command.
--
Dejan Sarka, SQL Server MVP
Please reply only to the newsgroups.
"Roee Friedman" <necnecnecnec@.hotmail.com> wrote in message
news:bppqas$1rcn6n$1@.ID-200860.news.uni-berlin.de...
> Hello!
> I've written a script to drop all the SPs from a given DB:
> DECLARE @.name nvarchar(200)
> DECLARE list CURSOR FOR
> SELECT name FROM sysobjects where xtype = 'p'
> OPEN list
> FETCH NEXT FROM list INTO @.name
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.name = 'drop procedure ' + @.name
> EXEC sp_executesql @.name
> FETCH NEXT FROM list INTO @.name
> END
> CLOSE list
> DEALLOCATE list
> GO
> When I'm running it from Query Analyzer it works fine, but when running it
> from a small utility (which all id does is opening a connection to the DB
> and running the same script), only some of the SPs are dropped.
> I'm now looking at the utility, but is there any problem with the SQL
scirpt
> (or a better way to the task (drop all of the SPs in a DB, without knowing
> the names of them all)).
> Thanks!
> Roee
>
>

Dropping article

Hi,
I have setup merge replication to run between two SQL 2k (SP3) across the internet. But while selecting the articles via EM I forgot to select "Identity Resolver" for a particular table. And this is creating havoc in that table.
I have to solve this by dropping the article. Or is there any other way?
But if I try to drop the article it says that there is an active subscription for this publication. Would this mean that I will have to drop my entire publication and create the entire thing again?
Can someone help me with this?
Thank you.
Regards,
Karthik
Karthik,
it is not mandatory to reinitialize - you can drop the subscription to a particular article using sp_dropsubscription then drop the article using sp_droparticle, then sp_addarticle to readd then sp_refreshsubscriptions.
Incidentally, what is the error message you see?
Regards,
Paul Ibison
|||Hi Paul,
Thank you for the quick response.
I see a primary key violation for a column that has IDENTITY property set and marked as "YES NOT FOR REPLICATION".
So according to you I do the following -
1. Run sp_dropsubscription at the publisher.
2. Run sp_droparticle
3. Run sp_addarticle
4. Run sp_refreshsubscriptions
I am not all that experienced in Replication. So I still have a few queries on what you have said. I would like to be really clear before I do something as this is the production database.
If I drop a subscription from the publisher, will it not mean that I would need to initialize via the snapshots again?
And what does sp_refreshsubscriptions do actually? I did look at BOL, but didn't understand too much.
Regards,
Karthik.
|||Karthik,
apologies - I just reread your post and noticed that you are using merge replication. In this case my route won't work - it only works for snapshot and transactional. What you can do though is to script out your publication (inc subscriptions),delete the
publication (and prevent users from editing data on the subscriber), make necessary changes to the table, then recreate the publication. For each of the other articles you set @.sync_type to 'none' to establish that the table already exists on the subscrib
er. For the table that you want the identity value, you'll need to have @.sync_type set to 'automatic'.
Alternatively you can have several publications to separate the articles if this is a common issue.
As another point, I'd mention that you can be sure to avoid these PK violations if you set the increment of the identity column in such a way as there can never be an overlap eg publisher uses even numbers and subscriber odd ones. There is an article by M
ichael Hotek on this (http://www.mssqlserver.com/replicati...h_identity.asp).
HTH,
Paul Ibison
|||Hi Paul,
I did generate a script of both the publisher and subscribers. But nowhere could I see a @.sync_type. I searched the entire text file.
May be I forgot to specify that I have a pull subscription.
Do let me know if my posting part of the script file would help.
Thank you once again.
Regards,
Karthik
|||Karthik,
it's a parameter off sp_addmergepullsubscription or sp_addmergesubscription.
However, unfortunately it is not valid on a per article basis - hadn't
appreciated this subtle difference between transactional and merge, so you'd
have to set @.sync_type set to 'automatic' for the complete subscription. In
your case you might be able to make the required table modification to the
table on the publisher and subscriber then run this. As it involves identity
column changes whose ranges are saved to system tables I suspect this is not
viable and would advise another route:
(a) drop the publication! Add 2 new publications - one with the old tables
apart from the problemmatic one, one with the problemmatic table. For the
first one have @.sync_type set to 'none' (subscriber has schema) and have
@.sync_type set to 'automatic' for the second.
(b) if this is not feasible (due to PK-FK constraints between articles),
then go down the initial route of a complete recreation of the publication.
Sorry it's not easier advice.
Regards,
Paul Ibison
|||Hi Paul,
What you seemed to have mentioned seems quite complicated. I would like to stall it as much as possible. Anyways, may be I am trying to go about this the wrong way as I didn't specify why I want to drop the article.
Is there any way I can specify idenity ranges for an article after it has been published? What I need to do basically is, specify that my table "FTPFile" has a range of 1-1000 from my publisher with a threshold of 80%.
Is there any way of doing this?
Thank you once again!
Regards,
Karthik.
|||Karthik,
the only sp wich might be relevant is:
sp_adjustpublisheridentityrange
From BOL: For a publication which has the auto identity range enabled, the
Distribution Agent or Merge Agent is responsible for automatically adjusting
the identity range in a publication based on its threshold value. However,
if for some reason the Distribution Agent or Merge Agent has not been run
for a period of time, and identity range resource have been consumed heavily
to the point of threshold, users can call sp_adjustpublisheridentityrange to
allocate a new range of values for a Publisher.
The identity ranges are held in a system table and the infrastructure is
also implemented using check constraints, but I would not advise you to
change these manually.
My recommendation would be to set it up again and ensure there can't be any
problems by following Michael Hotek's example: eg publisher uses even
numbers and subscriber odd ones. There is an article on this
(http://www.mssqlserver.com/replicati...h_identity.asp).
HTH,
Paul Ibison
|||Hi Paul,
Thank you for all the help.
I was able to fix the problem as suggested in the link.
Thank you once again.
Regards,
Karthik

Dropping all foreign keys

I would like to have a procedure that I can call that will drop all foreign
keys. I can get the constraint name from sysobjects if I select all fkey
constraints, but how can I get the associated table name so that I can plug
that into an alter table statement?
I appreciate any suggestions.
ThanksSee the first query at http://www.aspfaq.com/2520
Just change it from "SELECT FK_Table ... FROM" to the following:
SELECT 'ALTER TABLE '+FK.TABLE_NAME+' DROP CONSTRAINT '+C.CONSTRAINT_NAME
FROM
Run it in query analyzer and you will generate a script in the bottom pane,
which you can then copy and run in a new Query Analyzer window.
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:A6D72FCA-0943-426F-A28D-5F3E0596A4CD@.microsoft.com...
>I would like to have a procedure that I can call that will drop all foreign
> keys. I can get the constraint name from sysobjects if I select all fkey
> constraints, but how can I get the associated table name so that I can
> plug
> that into an alter table statement?
> I appreciate any suggestions.
>
> Thanks|||"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:A6D72FCA-0943-426F-A28D-5F3E0596A4CD@.microsoft.com...
>I would like to have a procedure that I can call that will drop all foreign
> keys. I can get the constraint name from sysobjects if I select all fkey
> constraints, but how can I get the associated table name so that I can
> plug
> that into an alter table statement?
> I appreciate any suggestions.
>
> Thanks
Here is one that I wrote. It's not pretty and it's not optimized, but it
works.
Rick Sawtell
----
PRINT ''
PRINT ''
PRINT ''
PRINT '**************************************'
PRINT '* Dropping Foreign Key Constraints *'
PRINT '**************************************'
SET NOCOUNT ON
DECLARE @.TableNames TABLE(TableName nvarchar(256))
DECLARE @.TableCount int
DECLARE @.TableName nvarchar(256),
@.FKName nvarchar(256)
DECLARE @.FKNames TABLE(FKName nvarchar(256))
DECLARE @.FKCount int
INSERT @.TableNames
SELECT name
FROM sysobjects
WHERE TYPE = 'U'
AND OBJECTPROPERTY(object_id(name), 'IsTable') = 1
AND OBJECTPROPERTY(object_id(name), 'IsSystemTable') = 0
AND name NOT LIKE 'dt_%'
ORDER BY name
SELECT @.TableCount = Count(*) FROM @.TableNames
WHILE @.TableCount > 0
BEGIN
SELECT @.TableName = MIN(TableName)
FROM @.TableNames
SET @.FKName = NULL
INSERT @.FKNames (FKName)
SELECT name
FROM sysobjects
WHERE TYPE = 'F'
AND parent_obj = OBJECT_ID(@.TableName)
AND OBJECTPROPERTY(OBJECT_ID(name), 'IsForeignKey') = 1
SELECT @.FKCount = COUNT(*)
FROM @.FKNames
WHILE @.FKCount > 0
BEGIN
SELECT @.FKName = MIN(FKName)
FROM @.FKNames
PRINT ' Dropping Constraint ' + @.TableName + '.' + @.FKName
EXECUTE('ALTER TABLE ' + @.TableName + ' DROP CONSTRAINT ' + @.FKName)
DELETE @.FKNames
WHERE FKName = @.FKName
SET @.FKCount = @.FKCount - 1
END
DELETE FROM @.TableNames WHERE TableName = @.TableName
SET @.TableCount = @.TableCount - 1
END|||WHY? Do you really want to sail through the windshield?
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:A6D72FCA-0943-426F-A28D-5F3E0596A4CD@.microsoft.com...
> I would like to have a procedure that I can call that will drop all
foreign
> keys. I can get the constraint name from sysobjects if I select all fkey
> constraints, but how can I get the associated table name so that I can
plug
> that into an alter table statement?
> I appreciate any suggestions.
>
> Thanks|||*All* of them? I just hope some of them will make it back... someday.
ML

droping a CONSTRAINT

I am having problem to find the right syntax to DROP a column with contrainst and recrate it
I get an error

if exists ( select * from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='myTable'
and COLUMN_NAME='myDate' )
ALTER TABLE [dbo].[myTable] DROP COLUMN myDate
GO

ALTER TABLE [dbo].[myTable] WITH NOCHECK ADD
myDate datetime CONSTRAINT [DF_myDate] DEFAULT (GetDate())
GO

Query Analyser says :
Server: Msg 5074, Level 16, State 1, Line 5
The object 'DF_myDate' is dependent on column 'myDate'.
Server: Msg 4922, Level 16, State 1, Line 5
ALTER TABLE DROP COLUMN myDate failed because one or more objects access this column.
Server: Msg 2705, Level 16, State 4, Line 2
Column names in each table must be unique. Column name 'myDate' in table 'dbo.myTable' is specified more than once.

thank you for helpingYou do know that the first statement failed, and since it was isolated in it's own batch by the GO, the second statement tried to run. So that's uderstandable, since the column did not drop, you can't re-add it.

Do this, go in to Enterprise Manager, right click on the table and chose design make your changes, DON'T SAVE them, and click on the save script icon

It will show you exactly what to do|||I get an incredible script !!

I really dont understant
here I dop the column

if exists ( select * from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='myTable'
and COLUMN_NAME='myDate' )
ALTER TABLE [dbo].[myTable] DROP COLUMN myDate
GO

then it doesnt exist any more ! why I cannot create it after ?|||because u failed to drop it....... u got error message :
Server: Msg 5074, Level 16, State 1, Line 5
The object 'DF_myDate' is dependent on column 'myDate'.
Server: Msg 4922, Level 16, State 1, Line 5
ALTER TABLE DROP COLUMN myDate failed because one or more objects access this column.

u cant drop the column, coz it has constraint 'DF_myDate'. drop the constraint first, then drop the column, then create the column with constraint again.

Drop table problem

In my SP, i will select some data and put into my cursor.
select * in myTempTable_1 from myCustomer
select * in myTempTable_2 from myInvoice.
at the end of the sp ,
i will drop all temptable.
The problem is .. Does this approach is correct ?
If there is several people use this store procedure, 1st people drop the
table, 2dn people start to run that sp, any problem '> select * in myTempTable_1 from myCustomer
This isn't valid syntax. I assume you mean SELECT * INTO ...
If you need to create tables then use a local temp table or table
variable rather than a permanent one. That way the table has local
scope and there is no potential conflict in a multi-user environment.
Many times you can avoid having to create temp objects by restructuring
procedural code as set-based queries. I'm not sure in what context you
would use statements like the example you gave. There seems to be
little point in selecting an entire table into a new one. In any case,
you should avoid using cursors. Cursors are rarely a good idea in SQL
Server.
Hope this helps.
David Portas
SQL Server MVP
--
.|||Could you tell me how to write this SQL sytnax ?
Thanks a lot
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org>
'?:1108304357.347054.43940@.f14g2000cwb.googlegroups.com...
> This isn't valid syntax. I assume you mean SELECT * INTO ...
> If you need to create tables then use a local temp table or table
> variable rather than a permanent one. That way the table has local
> scope and there is no potential conflict in a multi-user environment.
> Many times you can avoid having to create temp objects by restructuring
> procedural code as set-based queries. I'm not sure in what context you
> would use statements like the example you gave. There seems to be
> little point in selecting an entire table into a new one. In any case,
> you should avoid using cursors. Cursors are rarely a good idea in SQL
> Server.
> Hope this helps.
> --
> David Portas
> SQL Server MVP
> --
>
> .
>|||Here is an example:
http://www.extremeexperts.com/SQL/A...TSQLResult.aspx
that allows you to create a looping logic with T-SQL
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:uV4tgKlEFHA.2572@.tk2msftngp13.phx.gbl...
> Could you tell me how to write this SQL sytnax ?
> Thanks a lot
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org>
> '?:1108304357.347054.43940@.f14g2000cwb.googlegroups.com...
>|||BY USING SET ROWCOUNT ' I CAN MAKE THE TEMPTABLE RUN IN LOCAL MACHINE ?
"Vinod Kumar" <vinodk_sct@.NO_SPAM_hotmail.com> glsD:cupg3k$41l$1@.news01.intel.com
..
> Here is an example:
> http://www.extremeexperts.com/SQL/A...TSQLResult.aspx
> that allows you to create a looping logic with T-SQL
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD, MCSD
> http://www.extremeexperts.com
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
> "Agnes" <agnes@.dynamictech.com.hk> wrote in message
> news:uV4tgKlEFHA.2572@.tk2msftngp13.phx.gbl...
>|||I'm not sure that Vinod's example is relevant to what you are trying to
do - mainly because you haven't stated explicitly what you ARE trying
to do. It think Vinod was responding to your mention of cursors in your
original post while I was addressing the temp table question.
In answer to the additional questions you have asked: Here's an example
of a SELECT INTO statement:
SELECT col1, col2
INTO NewTable
FROM YourTable
this creates a new table and populates it with the data from
"YourTable".
Secondly, the question of putting a temptable on the "local machine".
Usually your goal should be to avoid processing data on your local
machine. This is a SQL Server programming newsgroup and SQL code runs
on the server and then returns results to the client for display
printing or further processing. You should consult a resource for your
development language or client application for more information on how
to process data at the client.
If you need more help with SQL Server, please consult the following
article which explains the best way to describe your problem for the
group:
http://www.aspfaq.com/etiquette.asp?id=5006
Hope this helps.
David Portas
SQL Server MVP
--|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications. It is very hard
to debug a procedure without seeing it.
And, no, that approach is horrible. In SQL, the goals are:
1) Do not use cursors
2) Do use temp tables
3) Do not use SELECT * in production code.
4) Try to do everything in one statement.
Based on absolutely nothing you told us, I will guess that you want to
do a JOIN between Customers and their invoices.

drop table

I upgraded my system from Sql2000 to sql2005.

And now i have a problem.

I have job wich runs every minute and perform the following

select * into tabler_1 from .....

begin tran

if exists(select * from sysobjects where xtype ='u' and name = 'tabler')

drop table tabler

exec sp_rename tabler_1, tabler

commit

And on client side i have IIS which runs asp pages which connect to database with

ado connction using ODBC and runs procedures wich performs select * from tabler

and there is a problem they some times failes with error tabler does not exists.

It worked fine in sql2000

all connections opened with default setting.

(transaction isolation level read commited)

What abou this here:

if not exists

(

SELECT * FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = 'tabler_1'

AND TABLE_TYPE = 'BASE_TABLE'

)

select * into tabler_1 from SomeTable

if exists

(

SELECT * FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = 'tabler'

AND TABLE_TYPE = 'BASE_TABLE'

)

BEGIN

DROP TABLE tabler

EXEC sp_rename tabler_1, tabler

END

Make sure that you check the existence of the table before doing a SELECT INTO.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

It is not a problem.

it make drop very nice.

the problem is on the client which runs store procedure like this

create procedure test_sp

as

select * from tabler

go

OR

create procedure test_sp_d

as

exec ('select * from tabler')

go

when i droping table i do it in transaction so schema is locked

the procedures have to wait for commit and then select

it worked fine sql2000

sql

drop index

Hi
How to check the indexname is exist in the table (WHERE name =
mytable.indexname)?
IF EXISTS (SELECT name FROM sysindexes WHERE name = mytable.indexname)
DROP INDEX mytable.indexname
Thanks.Hi js
Try this:
IF EXISTS (SELECT name FROM sysindexes WHERE name = indexname AND id =
object_id('mytable') )
DROP INDEX mytable.indexname
However, if the index was created because of a declared PRIMARY KEY or
UNIQUE constraint, it will exist in sysindexes, but you will not be able to
drop it using the DROP INDEX syntax. You will have to use ALTER TABLE ...
DROP CONSTRAINT.
HTH
Kalen Delaney
www.solidqualitylearning.com
"js" <js@.someone@.hotmail.com> wrote in message
news:eyFz2I8sFHA.3628@.TK2MSFTNGP14.phx.gbl...
> Hi
> How to check the indexname is exist in the table (WHERE name =
> mytable.indexname)?
> IF EXISTS (SELECT name FROM sysindexes WHERE name = mytable.indexname)
> DROP INDEX mytable.indexname
> Thanks.
>
>|||Thanks Kalen.

Drop function

Hey
I got this noob problem, I cannot drop a function.
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'NONUMBER')
DROP FUNCTION NONUMBER
This works fine on one of my other servers, first thing I though is
security. Since this code works on another server.
My permissions are the same, dbadmin , it's sa role. Both servers were
installed the same way so I can't see how I'm having this problem. So it's
not security my I think it can be those DB options where you specify with
sp_configure.
Ok, few minutes past I tried the sp_configure.
Still can't drop function. I'm sure it's just small setting.
Thanks in advance.
Mal> Hey
> I got this noob problem, I cannot drop a function.
> IF EXISTS (SELECT *
> FROM sysobjects
> WHERE name = N'NONUMBER')
> DROP FUNCTION NONUMBER
> This works fine on one of my other servers, first thing I though is
> security. Since this code works on another server.
> My permissions are the same, dbadmin , it's sa role. Both servers were
> installed the same way so I can't see how I'm having this problem. So
> it's not security my I think it can be those DB options where you
> specify with sp_configure.
> Ok, few minutes past I tried the sp_configure.
> Still can't drop function. I'm sure it's just small setting.
>
Do you get an error? If so, what error?
Or does nothing happen? Modify your script to this:
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'NONUMBER')
BEGIN
print 'Dropping function'
DROP FUNCTION NONUMBER
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'NONUMBER')
'Could not drop fucntion'
ELSE
'Function has been dropped'
END
ELSE
print 'Function Does Not Exist'
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Mal,
What is the error you are getting?

> IF EXISTS (SELECT *
> FROM sysobjects
> WHERE name = N'NONUMBER')
> DROP FUNCTION NONUMBER
The previous statement is checking for the existence of an object named
N'NONUMBER', but it is not checking that this object is a user defined
function. Check the diff with the following statement.
if exists (select * from information_schema.routines where routine_name =
N'nonumber' and routine_type = 'function)
...
AMB
"Mal" wrote:

> Hey
> I got this noob problem, I cannot drop a function.
> IF EXISTS (SELECT *
> FROM sysobjects
> WHERE name = N'NONUMBER')
> DROP FUNCTION NONUMBER
> This works fine on one of my other servers, first thing I though is
> security. Since this code works on another server.
> My permissions are the same, dbadmin , it's sa role. Both servers were
> installed the same way so I can't see how I'm having this problem. So it's
> not security my I think it can be those DB options where you specify with
> sp_configure.
> Ok, few minutes past I tried the sp_configure.
> Still can't drop function. I'm sure it's just small setting.
> Thanks in advance.
> Mal|||Sorry for not pasting ddl
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = 'NONUMBER')
begin
DROP FUNCTION NONUMBER
end
Code again, Error to follow
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near 'FUNCTION'.
Also tried grant access to my user to create function
UserName GroupName LoginName DefDBName UserID SUserID
-- -- -- -- -- --
dbo db_owner sa master 1 1
Grant create function to dbo
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'function'.
Same problem.
This is error desc. from BOL
170 15 Line %d: Incorrect syntax near '%.*ls'.
Thanks in advance .
"Alejandro Mesa" wrote:
> Mal,
> What is the error you are getting?
>
> The previous statement is checking for the existence of an object named
> N'NONUMBER', but it is not checking that this object is a user defined
> function. Check the diff with the following statement.
> if exists (select * from information_schema.routines where routine_name =
> N'nonumber' and routine_type = 'function)
> ...
>
> AMB
> "Mal" wrote:
>|||For starters, I would suggest two things. First, check the compatibility of
the database, using sp_dbcmptlevel; Only databases with level 80 or above
can do any function related DDLs. Second, check the owner of the fuction to
see if the DROP is executed by a user/ role having same or higher
previleges.
Anith|||Hey thanks for everyone's help
I did post a thanks but I used curseword in it so it got blocked I think.
Here what the results are.
sp_dbcmptlevel ->
Valid values of database compatibility level are 60, 65, or 70.
Thanks for help I'll have a look at compatibility in BOL and online .
Thanks
"Anith Sen" wrote:

> For starters, I would suggest two things. First, check the compatibility o
f
> the database, using sp_dbcmptlevel; Only databases with level 80 or above
> can do any function related DDLs. Second, check the owner of the fuction t
o
> see if the DROP is executed by a user/ role having same or higher
> previleges.
> --
> Anith
>
>

Drop Down Parameters Not Working

If you select an option from the dropdown list, the secondary dropdown list
does
not populate automatically. It appears that the focus is not being made on
the frame that allows for the user to enter parameters.
Is there a way to make the focus on a certain frame of the Report parameters
form?
I found that once the page loads, as long as I don't select anything in the
primary dropdown list, I can select the frame that the drop down is in, and
then select an option and it works correctly. If I select the option first,
then select the frame, it does not work. I'm not an expert, but to me, this
means that the focus needs to be put on the drop down list frame by Reporting
Service.I had this exact same problem, took a while to figure it out. Some IE
browsers (maybe others too) do this, not really the reporting services. I
believe if you update your browser it can solve this. Otherwise you can add
"&rc:Stylesheet=fix" to the end of you link to the report and it should work.
Hope this helps.
"A.J." wrote:
> If you select an option from the dropdown list, the secondary dropdown list
> does
> not populate automatically. It appears that the focus is not being made on
> the frame that allows for the user to enter parameters.
> Is there a way to make the focus on a certain frame of the Report parameters
> form?
> I found that once the page loads, as long as I don't select anything in the
> primary dropdown list, I can select the frame that the drop down is in, and
> then select an option and it works correctly. If I select the option first,
> then select the frame, it does not work. I'm not an expert, but to me, this
> means that the focus needs to be put on the drop down list frame by Reporting
> Service.|||I tried to update my browser and that didn't work. I already have the latest
from Microsoft. As far as the &rc goes, I didn't development the report, so
I'm not sure how to do this, but I'll pass it on to the owner.
"scraejtp" wrote:
> I had this exact same problem, took a while to figure it out. Some IE
> browsers (maybe others too) do this, not really the reporting services. I
> believe if you update your browser it can solve this. Otherwise you can add
> "&rc:Stylesheet=fix" to the end of you link to the report and it should work.
> Hope this helps.
> "A.J." wrote:
> > If you select an option from the dropdown list, the secondary dropdown list
> > does
> > not populate automatically. It appears that the focus is not being made on
> > the frame that allows for the user to enter parameters.
> >
> > Is there a way to make the focus on a certain frame of the Report parameters
> > form?
> >
> > I found that once the page loads, as long as I don't select anything in the
> > primary dropdown list, I can select the frame that the drop down is in, and
> > then select an option and it works correctly. If I select the option first,
> > then select the frame, it does not work. I'm not an expert, but to me, this
> > means that the focus needs to be put on the drop down list frame by Reporting
> > Service.|||Looks like I got it to work, but I had to revert to a previous version of
I.E. browser. I went from Update Version
"SP1;Q832894;Q867801;Q871260;Q823353;" to simply "SP1", which brings us back
to our original problem occurring after we pushed out Microsoft patch 888113.
This should be a Microsoft issue, if someone knows how to report it.
"scraejtp" wrote:
> I had this exact same problem, took a while to figure it out. Some IE
> browsers (maybe others too) do this, not really the reporting services. I
> believe if you update your browser it can solve this. Otherwise you can add
> "&rc:Stylesheet=fix" to the end of you link to the report and it should work.
> Hope this helps.
> "A.J." wrote:
> > If you select an option from the dropdown list, the secondary dropdown list
> > does
> > not populate automatically. It appears that the focus is not being made on
> > the frame that allows for the user to enter parameters.
> >
> > Is there a way to make the focus on a certain frame of the Report parameters
> > form?
> >
> > I found that once the page loads, as long as I don't select anything in the
> > primary dropdown list, I can select the frame that the drop down is in, and
> > then select an option and it works correctly. If I select the option first,
> > then select the frame, it does not work. I'm not an expert, but to me, this
> > means that the focus needs to be put on the drop down list frame by Reporting
> > Service.

Drop Down list with many elements

Hello,

I am using SSRS 2005 SP2 and I need to allow my users to select the products they want in the report.
I created a data set to get the list of the products and then used it in a multivalue parameter to generate a drop down list.
My problem is that the list is too long (more than 2000 elements) and thus, it takes 10 sec to collapse the drop down list after tickinging the products.

Does anyone have an miraculous solution?
Thanks in advance for your answers.

Zoz

Did you create the drop down list using visual studio 2005?

You may want to divide this HUGE dropdownlist into maybe 26 dropdown lists -- categorize them by first letter in the product.

Only populate the A dropdown list with products that start with A, etc.

Then, handle each dropdown list's selection changed event separately. (I would imagine that each event would have the same code though)

Or maybe 26 is too many. Just do one dropdown for the first half of the alphabet and then another for the second half.

You get the idea though: divide and conquer.

|||I like the idea of conquering :-)
Your idea is good but I alreday have many drop down lists because I use cascading parameters (8!). Then it would become too heavy visually...

Any other solution?
Thanks.

PS: Yes, I used Visual Studio 2005.
|||

You can have a muti-page report.

The idea is to create a parameter called "Search Product" which takes string as an argument.

Then output products based on the value of the parameter.

Then click on the product which will take you to your current report.

Thanks,

-Rohit

|||Hi,

Can you detail a little bit more? I am not sure to understand what you mean...
Do you mean that I should create a search field?

Thanks.
Zoz
|||

See what I am trying to say is:

1. Create a summary page where you have a parameter which will search for productName (say we call it @.searchProduct) and generate a list of products related to that name. In SQL you can do (where productName like '% + @.searchProduct + %')

2. Then if the user click on a product it will take you to another report where you have all other parameters.

But the good thing is you don't have to worry about the product because you already selected your product and passed the productID from report1 to report2. Report2 is basically the same report you are working on but without the parameter for productID. You can make it hidden.

Feel free to ask me more questions...

|||

I really like your idea and I'm very curious to see how to implement it. Would you mind sending me an example by email (an rdl file for example)? I' ve tried something like that in the past, but I've never succeeded to do it... :-(

For the problem I popsted previously, your solution doesn't meet the requirements because users need to choose several products...

I tried to implement cascading parameters although it is not the best one in terms of performance... So I'm still looking for a better idea.

Thanks

|||

It is very simple....

For exmple if you are using table to output the products then right click on the detail textbox and go to properties and then click on navigation. There you can select jump to a report, select the report you want to jump to and pass productId as a parameter. Now when you run the report you will be able to click on the products and it will take you to the next report that you specified in the properties.

-Rohit

|||

Thanks for your answer! Looks like it is a great idea! I'll try it!

And is there any way to have a "select all" ? ( it is also requiered by my users...)

Thanks

Zoz

Drop Down list with many elements

Hello,

I am using SSRS 2005 SP2 and I need to allow my users to select the products they want in the report.
I created a data set to get the list of the products and then used it in a multivalue parameter to generate a drop down list.
My problem is that the list is too long (more than 2000 elements) and thus, it takes 10 sec to collapse the drop down list after tickinging the products.

Does anyone have an miraculous solution?
Thanks in advance for your answers.

Zoz

Did you create the drop down list using visual studio 2005?

You may want to divide this HUGE dropdownlist into maybe 26 dropdown lists -- categorize them by first letter in the product.

Only populate the A dropdown list with products that start with A, etc.

Then, handle each dropdown list's selection changed event separately. (I would imagine that each event would have the same code though)

Or maybe 26 is too many. Just do one dropdown for the first half of the alphabet and then another for the second half.

You get the idea though: divide and conquer.

|||I like the idea of conquering :-)
Your idea is good but I alreday have many drop down lists because I use cascading parameters (8!). Then it would become too heavy visually...

Any other solution?
Thanks.

PS: Yes, I used Visual Studio 2005.
|||

You can have a muti-page report.

The idea is to create a parameter called "Search Product" which takes string as an argument.

Then output products based on the value of the parameter.

Then click on the product which will take you to your current report.

Thanks,

-Rohit

|||Hi,

Can you detail a little bit more? I am not sure to understand what you mean...
Do you mean that I should create a search field?

Thanks.
Zoz
|||

See what I am trying to say is:

1. Create a summary page where you have a parameter which will search for productName (say we call it @.searchProduct) and generate a list of products related to that name. In SQL you can do (where productName like '% + @.searchProduct + %')

2. Then if the user click on a product it will take you to another report where you have all other parameters.

But the good thing is you don't have to worry about the product because you already selected your product and passed the productID from report1 to report2. Report2 is basically the same report you are working on but without the parameter for productID. You can make it hidden.

Feel free to ask me more questions...

|||

I really like your idea and I'm very curious to see how to implement it. Would you mind sending me an example by email (an rdl file for example)? I' ve tried something like that in the past, but I've never succeeded to do it... :-(

For the problem I popsted previously, your solution doesn't meet the requirements because users need to choose several products...

I tried to implement cascading parameters although it is not the best one in terms of performance... So I'm still looking for a better idea.

Thanks

|||

It is very simple....

For exmple if you are using table to output the products then right click on the detail textbox and go to properties and then click on navigation. There you can select jump to a report, select the report you want to jump to and pass productId as a parameter. Now when you run the report you will be able to click on the products and it will take you to the next report that you specified in the properties.

-Rohit

|||

Thanks for your answer! Looks like it is a great idea! I'll try it!

And is there any way to have a "select all" ? ( it is also requiered by my users...)

Thanks

Zoz

Drop Database failed as already in use - how do I close existing connections as per Management S

I have some code to delete a database. This allows a user to enum the databases and select one to delete. It the determines the filename, so it can remove the physical files, drops the database and deletes the files. However, it frequently fails saying the database is currently in use....

I noticed the same bahaviour when deleting a database via Management Studio, however checking the box to close active connections does the trick and the db is successfully deleted.

Question: how do I close the active connection in smo ?

Thanks, Nick

Hi,

http://www.sqlserver2005.de/sqlserver2005/MyBlog/tabid/56/EntryID/9/Default.aspx

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Thanks Jens this is really helpful

Drop Database failed as already in use - how do I close existing connections as per Manageme

I have some code to delete a database. This allows a user to enum the databases and select one to delete. It the determines the filename, so it can remove the physical files, drops the database and deletes the files. However, it frequently fails saying the database is currently in use....

I noticed the same bahaviour when deleting a database via Management Studio, however checking the box to close active connections does the trick and the db is successfully deleted.

Question: how do I close the active connection in smo ?

Thanks, Nick

Hi,

http://www.sqlserver2005.de/sqlserver2005/MyBlog/tabid/56/EntryID/9/Default.aspx

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Thanks Jens this is really helpful

Drop Database failed as already in use - how do I close existing connections as per Manageme

I have some code to delete a database. This allows a user to enum the databases and select one to delete. It the determines the filename, so it can remove the physical files, drops the database and deletes the files. However, it frequently fails saying the database is currently in use....

I noticed the same bahaviour when deleting a database via Management Studio, however checking the box to close active connections does the trick and the db is successfully deleted.

Question: how do I close the active connection in smo ?

Thanks, Nick

Hi,

http://www.sqlserver2005.de/sqlserver2005/MyBlog/tabid/56/EntryID/9/Default.aspx

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Thanks Jens this is really helpful

Drop database

I cannot drop a user defined database in the following situation:
In enterprise manager, I select that database, select tables.
In query analyser, run the statement: use master, go, drop that user
database, an error said that database is currently in use.
Then I back to enterprise manager, collapse the database folder. Run the
statement in query analyser again but got the same error.
The way I get around was go back to enterprise manager, select root level or
upper level database, press refresh.
Back to query analyser and run that SQL again and it worked.
Why do I need to refresh enterprise manager after select the root level
folder or collapse the database folder ?Because EM doesn't want to do unecessary database work unless it has to. So, the connection is still
in the old database until you refresh in EM.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Alan" <alanpltse@.yahoo.com.au> wrote in message news:OnzQiTEaDHA.440@.tk2msftngp13.phx.gbl...
> I cannot drop a user defined database in the following situation:
> In enterprise manager, I select that database, select tables.
> In query analyser, run the statement: use master, go, drop that user
> database, an error said that database is currently in use.
> Then I back to enterprise manager, collapse the database folder. Run the
> statement in query analyser again but got the same error.
> The way I get around was go back to enterprise manager, select root level or
> upper level database, press refresh.
> Back to query analyser and run that SQL again and it worked.
> Why do I need to refresh enterprise manager after select the root level
> folder or collapse the database folder ?
>

drop clustered index - fails on duplicate key

When I try to drop my unique clustered index with the following code:
if exists (select * from dbo.sysindexes
where name = N'my_Index' and id = object_id(N'[dbo].[my_Table]'))
drop index [dbo].[my_Table].[my_Index]
GO
it fails with the error:
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index
ID 2. Most significant primary key is '23497698'.
The statement has been terminated.
I understand that when you drop a clustered index unclustered indexes are
atomatically created and I am guessing one of them is causing the problem. Is
there a way to drop the clustered index without creating new indexes? Or have
the new indexes ignore dulicates?
Thanks
Have you got a complete repro? I'm wondering if there is a foreign key from
another table.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:7007DC7D-5C2D-46B8-B912-5F9D85F1F694@.microsoft.com...
When I try to drop my unique clustered index with the following code:
if exists (select * from dbo.sysindexes
where name = N'my_Index' and id = object_id(N'[dbo].[my_Table]'))
drop index [dbo].[my_Table].[my_Index]
GO
it fails with the error:
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index
ID 2. Most significant primary key is '23497698'.
The statement has been terminated.
I understand that when you drop a clustered index unclustered indexes are
atomatically created and I am guessing one of them is causing the problem.
Is
there a way to drop the clustered index without creating new indexes? Or
have
the new indexes ignore dulicates?
Thanks
|||My guess is that you have a corruption problem. Seem you have a unique nc index in which you have
managed to get duplicates. So the dropping of the clustered index will re-create the nc index and it
fails because of this. I'd do DBCC CHECKDB and see what it says.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:7007DC7D-5C2D-46B8-B912-5F9D85F1F694@.microsoft.com...
> When I try to drop my unique clustered index with the following code:
> if exists (select * from dbo.sysindexes
> where name = N'my_Index' and id = object_id(N'[dbo].[my_Table]'))
> drop index [dbo].[my_Table].[my_Index]
> GO
>
> it fails with the error:
> Server: Msg 1505, Level 16, State 1, Line 1
> CREATE UNIQUE INDEX terminated because a duplicate key was found for index
> ID 2. Most significant primary key is '23497698'.
> The statement has been terminated.
>
> I understand that when you drop a clustered index unclustered indexes are
> atomatically created and I am guessing one of them is causing the problem. Is
> there a way to drop the clustered index without creating new indexes? Or have
> the new indexes ignore dulicates?
> Thanks

drop clustered index - fails on duplicate key

When I try to drop my unique clustered index with the following code:
if exists (select * from dbo.sysindexes
where name = N'my_Index' and id = object_id(N'[dbo].[my_Table]'))
drop index [dbo].[my_Table].[my_Index]
GO
it fails with the error:
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index
ID 2. Most significant primary key is '23497698'.
The statement has been terminated.
I understand that when you drop a clustered index unclustered indexes are
atomatically created and I am guessing one of them is causing the problem. I
s
there a way to drop the clustered index without creating new indexes? Or hav
e
the new indexes ignore dulicates?
ThanksHave you got a complete repro? I'm wondering if there is a foreign key from
another table.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:7007DC7D-5C2D-46B8-B912-5F9D85F1F694@.microsoft.com...
When I try to drop my unique clustered index with the following code:
if exists (select * from dbo.sysindexes
where name = N'my_Index' and id = object_id(N'[dbo].[my_Table]'))
drop index [dbo].[my_Table].[my_Index]
GO
it fails with the error:
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index
ID 2. Most significant primary key is '23497698'.
The statement has been terminated.
I understand that when you drop a clustered index unclustered indexes are
atomatically created and I am guessing one of them is causing the problem.
Is
there a way to drop the clustered index without creating new indexes? Or
have
the new indexes ignore dulicates?
Thanks|||My guess is that you have a corruption problem. Seem you have a unique nc in
dex in which you have
managed to get duplicates. So the dropping of the clustered index will re-cr
eate the nc index and it
fails because of this. I'd do DBCC CHECKDB and see what it says.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:7007DC7D-5C2D-46B8-B912-5F9D85F1F694@.microsoft.com...
> When I try to drop my unique clustered index with the following code:
> if exists (select * from dbo.sysindexes
> where name = N'my_Index' and id = object_id(N'[dbo].[my_Table]'))
> drop index [dbo].[my_Table].[my_Index]
> GO
>
> it fails with the error:
> Server: Msg 1505, Level 16, State 1, Line 1
> CREATE UNIQUE INDEX terminated because a duplicate key was found for index
> ID 2. Most significant primary key is '23497698'.
> The statement has been terminated.
>
> I understand that when you drop a clustered index unclustered indexes are
> atomatically created and I am guessing one of them is causing the problem.
Is
> there a way to drop the clustered index without creating new indexes? Or h
ave
> the new indexes ignore dulicates?
> Thanks

drop clustered index - fails on duplicate key

When I try to drop my unique clustered index with the following code:
if exists (select * from dbo.sysindexes
where name = N'my_Index' and id = object_id(N'[dbo].[my_Table]'))
drop index [dbo].[my_Table].[my_Index]
GO
it fails with the error:
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index
ID 2. Most significant primary key is '23497698'.
The statement has been terminated.
I understand that when you drop a clustered index unclustered indexes are
atomatically created and I am guessing one of them is causing the problem. Is
there a way to drop the clustered index without creating new indexes? Or have
the new indexes ignore dulicates?
ThanksHave you got a complete repro? I'm wondering if there is a foreign key from
another table.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:7007DC7D-5C2D-46B8-B912-5F9D85F1F694@.microsoft.com...
When I try to drop my unique clustered index with the following code:
if exists (select * from dbo.sysindexes
where name = N'my_Index' and id = object_id(N'[dbo].[my_Table]'))
drop index [dbo].[my_Table].[my_Index]
GO
it fails with the error:
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index
ID 2. Most significant primary key is '23497698'.
The statement has been terminated.
I understand that when you drop a clustered index unclustered indexes are
atomatically created and I am guessing one of them is causing the problem.
Is
there a way to drop the clustered index without creating new indexes? Or
have
the new indexes ignore dulicates?
Thanks|||My guess is that you have a corruption problem. Seem you have a unique nc index in which you have
managed to get duplicates. So the dropping of the clustered index will re-create the nc index and it
fails because of this. I'd do DBCC CHECKDB and see what it says.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:7007DC7D-5C2D-46B8-B912-5F9D85F1F694@.microsoft.com...
> When I try to drop my unique clustered index with the following code:
> if exists (select * from dbo.sysindexes
> where name = N'my_Index' and id = object_id(N'[dbo].[my_Table]'))
> drop index [dbo].[my_Table].[my_Index]
> GO
>
> it fails with the error:
> Server: Msg 1505, Level 16, State 1, Line 1
> CREATE UNIQUE INDEX terminated because a duplicate key was found for index
> ID 2. Most significant primary key is '23497698'.
> The statement has been terminated.
>
> I understand that when you drop a clustered index unclustered indexes are
> atomatically created and I am guessing one of them is causing the problem. Is
> there a way to drop the clustered index without creating new indexes? Or have
> the new indexes ignore dulicates?
> Thanks

DrillThrough Actions not working in Proclarity web standard (Vs Proclarity professional)

Hi,

I created some Drillthrough actions in Proclarity web professional. When I right click in professional and select the aciton they open a new window and display the correct corresponding values.

after publishing the graph, I tried the same in the ProClarity Web Standard. Here the actions are not displayed in the right-click menu, but are displayed in the left navigation tab.

Clicking on the same drillthrough action, for the same cell and dimension values, opens a new window, which only keeps showing "Please wait, Loading Details....." I waited for 30 minutes. and still it did not show any results.

Since there is only one row that it has to display (as seen in proclarity professional for the same data), I think that there is something else that has to be configured for the proclarity web standard.

Please help.

thanks and regards

Anybody worked on ProClarity and ssas 2005?|||

On the web, there is no right click, just click on the cell and a menu will pop up for Drill to detail. You cannot drill on calculated measure cells.

Also check the permissions of the user in IIS that is running the PAS site. Try using an administrator account as anonymous and see if that works.

|||

Hi,

thanks for the reply,

Yeah , there is no right click on the web standard. (only in professional).

but i am able to see the drillthrough actions!!!! in web standard also.

it appears in the right side navigation tab , under the "Actions" header.

but clicking on those actions , opens a window, but does not get results.

I will try out what you specified.

Regards