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.
Showing posts with label exists. Show all posts
Showing posts with label exists. Show all posts
Thursday, March 29, 2012
Sunday, March 25, 2012
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.
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.
Wednesday, March 21, 2012
DROP TABLE IF EXISTS
Hello,
Forgive me if I am being stupid, but I cannot get some syntax to work and I
need it urgently. I want to drop a table if it exists and make a new table
in its place. I though I could do,
DROP TABLE IF EXISTS tablename;
CREATE TABLE tablename ([ID] [int] NOT NULL)
but I get the first time as the table doesn't exist yet!
Server: Msg 3701, Level 11, State 5, Line 1
Cannot drop the table 'tablename' , because it does not exist in the system
catalog.
Please could someone help me with the synatax!if exists (select * from dbo.sysobjects where id =
object_id(N'[<owner>].[<tablename>]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [<owner>].[<tablename>]
GO
CREATE TABLE [<owner>].[<tablename>] (
...
) GO
"11Oppidan" wrote:
> Hello,
> Forgive me if I am being stupid, but I cannot get some syntax to work and
I
> need it urgently. I want to drop a table if it exists and make a new tabl
e
> in its place. I though I could do,
> DROP TABLE IF EXISTS tablename;
> CREATE TABLE tablename ([ID] [int] NOT NULL)
> but I get the first time as the table doesn't exist yet!
> Server: Msg 3701, Level 11, State 5, Line 1
> Cannot drop the table 'tablename' , because it does not exist in the syste
m
> catalog.
> Please could someone help me with the synatax!
>
>|||You can try as follows...
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tablename]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[tablename]
GO
CREATE TABLE [dbo].[tablename] (
[aId] [int] IDENTITY (1, 1) NOT NULL ,
[nDayId] [int] NOT NULL
) ON [PRIMARY]
GO|||11Oppidan wrote on Wed, 23 Mar 2005 09:15:07 -0000:
> Forgive me if I am being stupid, but I cannot get some syntax to work and
> I need it urgently. I want to drop a table if it exists and make a new
> table in its place. I though I could do,
> DROP TABLE IF EXISTS tablename;
> CREATE TABLE tablename ([ID] [int] NOT NULL)
> but I get the first time as the table doesn't exist yet!
> Server: Msg 3701, Level 11, State 5, Line 1
> Cannot drop the table 'tablename' , because it does not exist in the
> system catalog.
> Please could someone help me with the synatax!
You can use EM to script out table creation, and if you keep the option to
create the DROP syntax ticked you'll get something like:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Tablename]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Tablename]
GO
followed by the create statement.
Dan|||try this:
if object_id('tablename') is not null
drop table tablename
go
or this:
if exists(select * from sysobjects where xtype='u' and name='tablename')
drop table tablename
go
hth
dean
"11Oppidan" <11Oppidan@.community.nospam> wrote in message
news:OpV06i4LFHA.3196@.TK2MSFTNGP10.phx.gbl...
> Hello,
> Forgive me if I am being stupid, but I cannot get some syntax to work and
I
> need it urgently. I want to drop a table if it exists and make a new
table
> in its place. I though I could do,
> DROP TABLE IF EXISTS tablename;
> CREATE TABLE tablename ([ID] [int] NOT NULL)
> but I get the first time as the table doesn't exist yet!
> Server: Msg 3701, Level 11, State 5, Line 1
> Cannot drop the table 'tablename' , because it does not exist in the
system
> catalog.
> Please could someone help me with the synatax!
>|||Many thanks guys!
"11Oppidan" <11Oppidan@.community.nospam> wrote in message
news:OpV06i4LFHA.3196@.TK2MSFTNGP10.phx.gbl...
> Hello,
> Forgive me if I am being stupid, but I cannot get some syntax to work and
> I need it urgently. I want to drop a table if it exists and make a new
> table in its place. I though I could do,
> DROP TABLE IF EXISTS tablename;
> CREATE TABLE tablename ([ID] [int] NOT NULL)
> but I get the first time as the table doesn't exist yet!
> Server: Msg 3701, Level 11, State 5, Line 1
> Cannot drop the table 'tablename' , because it does not exist in the
> system catalog.
> Please could someone help me with the synatax!
>
Forgive me if I am being stupid, but I cannot get some syntax to work and I
need it urgently. I want to drop a table if it exists and make a new table
in its place. I though I could do,
DROP TABLE IF EXISTS tablename;
CREATE TABLE tablename ([ID] [int] NOT NULL)
but I get the first time as the table doesn't exist yet!
Server: Msg 3701, Level 11, State 5, Line 1
Cannot drop the table 'tablename' , because it does not exist in the system
catalog.
Please could someone help me with the synatax!if exists (select * from dbo.sysobjects where id =
object_id(N'[<owner>].[<tablename>]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [<owner>].[<tablename>]
GO
CREATE TABLE [<owner>].[<tablename>] (
...
) GO
"11Oppidan" wrote:
> Hello,
> Forgive me if I am being stupid, but I cannot get some syntax to work and
I
> need it urgently. I want to drop a table if it exists and make a new tabl
e
> in its place. I though I could do,
> DROP TABLE IF EXISTS tablename;
> CREATE TABLE tablename ([ID] [int] NOT NULL)
> but I get the first time as the table doesn't exist yet!
> Server: Msg 3701, Level 11, State 5, Line 1
> Cannot drop the table 'tablename' , because it does not exist in the syste
m
> catalog.
> Please could someone help me with the synatax!
>
>|||You can try as follows...
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tablename]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[tablename]
GO
CREATE TABLE [dbo].[tablename] (
[aId] [int] IDENTITY (1, 1) NOT NULL ,
[nDayId] [int] NOT NULL
) ON [PRIMARY]
GO|||11Oppidan wrote on Wed, 23 Mar 2005 09:15:07 -0000:
> Forgive me if I am being stupid, but I cannot get some syntax to work and
> I need it urgently. I want to drop a table if it exists and make a new
> table in its place. I though I could do,
> DROP TABLE IF EXISTS tablename;
> CREATE TABLE tablename ([ID] [int] NOT NULL)
> but I get the first time as the table doesn't exist yet!
> Server: Msg 3701, Level 11, State 5, Line 1
> Cannot drop the table 'tablename' , because it does not exist in the
> system catalog.
> Please could someone help me with the synatax!
You can use EM to script out table creation, and if you keep the option to
create the DROP syntax ticked you'll get something like:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Tablename]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Tablename]
GO
followed by the create statement.
Dan|||try this:
if object_id('tablename') is not null
drop table tablename
go
or this:
if exists(select * from sysobjects where xtype='u' and name='tablename')
drop table tablename
go
hth
dean
"11Oppidan" <11Oppidan@.community.nospam> wrote in message
news:OpV06i4LFHA.3196@.TK2MSFTNGP10.phx.gbl...
> Hello,
> Forgive me if I am being stupid, but I cannot get some syntax to work and
I
> need it urgently. I want to drop a table if it exists and make a new
table
> in its place. I though I could do,
> DROP TABLE IF EXISTS tablename;
> CREATE TABLE tablename ([ID] [int] NOT NULL)
> but I get the first time as the table doesn't exist yet!
> Server: Msg 3701, Level 11, State 5, Line 1
> Cannot drop the table 'tablename' , because it does not exist in the
system
> catalog.
> Please could someone help me with the synatax!
>|||Many thanks guys!
"11Oppidan" <11Oppidan@.community.nospam> wrote in message
news:OpV06i4LFHA.3196@.TK2MSFTNGP10.phx.gbl...
> Hello,
> Forgive me if I am being stupid, but I cannot get some syntax to work and
> I need it urgently. I want to drop a table if it exists and make a new
> table in its place. I though I could do,
> DROP TABLE IF EXISTS tablename;
> CREATE TABLE tablename ([ID] [int] NOT NULL)
> but I get the first time as the table doesn't exist yet!
> Server: Msg 3701, Level 11, State 5, Line 1
> Cannot drop the table 'tablename' , because it does not exist in the
> system catalog.
> Please could someone help me with the synatax!
>
DROP TABLE IF EXISTS (not working)!
I need to drop a table if exist ?? how can i do that ??
thanx !if object_id('<table_name>') is not null drop table <table_name>|||Originally posted by ms_sql_dba
if object_id('<table_name>') is not null drop table <table_name>
thanx !!!!|||The M$ way...
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Company]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Company]
GO
thanx !if object_id('<table_name>') is not null drop table <table_name>|||Originally posted by ms_sql_dba
if object_id('<table_name>') is not null drop table <table_name>
thanx !!!!|||The M$ way...
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Company]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Company]
GO
Sunday, March 11, 2012
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.
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
>
>
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
>
>
Friday, March 9, 2012
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
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
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
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
Wednesday, March 7, 2012
Drop A Temp Table
Hi,
I can't seem to find a query to drop a temp table IF it exists. I know how to drop a table, but I don't want to get the error if the table is not there.
Thanks
if object_id('tempdb..#temptable')>0drop table #temptable|||
if object_id('tempdb..#temptable') is not null
drop table #temptable
-- because if #table doesn't exist, object_id will be null, not 0
Friday, February 17, 2012
Drillthrough
Hi all,
I'm new to OLAP and especially drillthrough and just have a question about
the way it works.
I can see clearly why the process exists and the complete benefits of it,
but correct me if i'm wrong but:
1. It appears to issue an SQL query against the data source to return the
information and not from the cube?
2. I assume I am able to send parameters to the query , for example the
current dimension selection as parameters? If so, how?
Currently, everytime i try a drillthrough, i get a timeout message from
AS2000 and my server CPU hits 100%.
I am correct in saying, for example, that If i have a value rolled up to a
week, I should be able to use the drillthrough feature to access the
underlying data that makes up that value, AND ONLY THAT DATA and not the
whole fact table which curerntly appears to be happening to me?
Many thanks
Immyyou have to capture the SQL query to verify what's appends exactly.
you have to insure that there is enough joins between your tables, sometimes
AS don't add corectly the right joins which result in a large table scan.
in the drill through option page, you can add static filters which also can
act has join between tables to insure the usage of joins.
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:eI$0whKXGHA.4132@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> I'm new to OLAP and especially drillthrough and just have a question about
> the way it works.
> I can see clearly why the process exists and the complete benefits of it,
> but correct me if i'm wrong but:
> 1. It appears to issue an SQL query against the data source to return the
> information and not from the cube?
> 2. I assume I am able to send parameters to the query , for example the
> current dimension selection as parameters? If so, how?
> Currently, everytime i try a drillthrough, i get a timeout message from
> AS2000 and my server CPU hits 100%.
> I am correct in saying, for example, that If i have a value rolled up to a
> week, I should be able to use the drillthrough feature to access the
> underlying data that makes up that value, AND ONLY THAT DATA and not the
> whole fact table which curerntly appears to be happening to me?
> Many thanks
> Immy
>
I'm new to OLAP and especially drillthrough and just have a question about
the way it works.
I can see clearly why the process exists and the complete benefits of it,
but correct me if i'm wrong but:
1. It appears to issue an SQL query against the data source to return the
information and not from the cube?
2. I assume I am able to send parameters to the query , for example the
current dimension selection as parameters? If so, how?
Currently, everytime i try a drillthrough, i get a timeout message from
AS2000 and my server CPU hits 100%.
I am correct in saying, for example, that If i have a value rolled up to a
week, I should be able to use the drillthrough feature to access the
underlying data that makes up that value, AND ONLY THAT DATA and not the
whole fact table which curerntly appears to be happening to me?
Many thanks
Immyyou have to capture the SQL query to verify what's appends exactly.
you have to insure that there is enough joins between your tables, sometimes
AS don't add corectly the right joins which result in a large table scan.
in the drill through option page, you can add static filters which also can
act has join between tables to insure the usage of joins.
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:eI$0whKXGHA.4132@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> I'm new to OLAP and especially drillthrough and just have a question about
> the way it works.
> I can see clearly why the process exists and the complete benefits of it,
> but correct me if i'm wrong but:
> 1. It appears to issue an SQL query against the data source to return the
> information and not from the cube?
> 2. I assume I am able to send parameters to the query , for example the
> current dimension selection as parameters? If so, how?
> Currently, everytime i try a drillthrough, i get a timeout message from
> AS2000 and my server CPU hits 100%.
> I am correct in saying, for example, that If i have a value rolled up to a
> week, I should be able to use the drillthrough feature to access the
> underlying data that makes up that value, AND ONLY THAT DATA and not the
> whole fact table which curerntly appears to be happening to me?
> Many thanks
> Immy
>
Subscribe to:
Comments (Atom)