DSN Connection to SQL Server

I am using the code below to create a DSN connection if it does not exist. Is there a way to set the DSN to use NT Authentication?

lngResult = SQLConfigDataSource(0, _
ODBC_ADD_SYS_DSN, _
"SQL Server", _
"DSN=" & JDS_DSN_name & Chr(0) & _
"Server=" & JDS_Server_name & Chr(0) & _
"Database=RMAData" & Chr(0) & _
"UseProcForPrepare=Yes" & Chr(0) & _
"Description=RMA Database" & Chr(0) & Chr(0))Figured it out|||Try specifying "Trusted_Connection=No" attribute along with the other attributes specified in SQLConfigDataSource().

Dropping a file in a filegroup that does not exist.

Hi,
I moved a database from an older sql box to one of our new servers. One of
the files in the prmiary file group was not moved and the server was wiped...
the filename is sysft_ix_STS_neo_1414639615. Everytime I try to do a backup
of that database I get a "file or firegroup is not online..." error message
and SQL won't let me drop it because it does not exist. Can anyone help me
out?Hi Henry
If there was data in this filegroup then you would have to resort to your
last backup.
John
"Henry" wrote:
> Hi,
> I moved a database from an older sql box to one of our new servers. One of
> the files in the prmiary file group was not moved and the server was wiped...
> the filename is sysft_ix_STS_neo_1414639615. Everytime I try to do a backup
> of that database I get a "file or firegroup is not online..." error message
> and SQL won't let me drop it because it does not exist. Can anyone help me
> out?|||I'm pretty certain this is the full text index and that rebuilding or removing full-text indexing
would solve this. This is what I recall from earlier post with the same problem.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:7E297789-FEFB-41D9-BF17-90BF96129198@.microsoft.com...
> Hi Henry
> If there was data in this filegroup then you would have to resort to your
> last backup.
> John
> "Henry" wrote:
>> Hi,
>> I moved a database from an older sql box to one of our new servers. One of
>> the files in the prmiary file group was not moved and the server was wiped...
>> the filename is sysft_ix_STS_neo_1414639615. Everytime I try to do a backup
>> of that database I get a "file or firegroup is not online..." error message
>> and SQL won't let me drop it because it does not exist. Can anyone help me
>> out?|||Hi Tibor
You are probably right! sp_help_fulltext_catalogs might verify this!
John
"Tibor Karaszi" wrote:
> I'm pretty certain this is the full text index and that rebuilding or removing full-text indexing
> would solve this. This is what I recall from earlier post with the same problem.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:7E297789-FEFB-41D9-BF17-90BF96129198@.microsoft.com...
> > Hi Henry
> >
> > If there was data in this filegroup then you would have to resort to your
> > last backup.
> >
> > John
> >
> > "Henry" wrote:
> >
> >> Hi,
> >>
> >> I moved a database from an older sql box to one of our new servers. One of
> >> the files in the prmiary file group was not moved and the server was wiped...
> >> the filename is sysft_ix_STS_neo_1414639615. Everytime I try to do a backup
> >> of that database I get a "file or firegroup is not online..." error message
> >> and SQL won't let me drop it because it does not exist. Can anyone help me
> >> out?
>

Dropping a file in a filegroup that does not exist.

Hi,
I moved a database from an older sql box to one of our new servers. One of
the files in the prmiary file group was not moved and the server was wiped..
.
the filename is sysft_ix_STS_neo_1414639615. Everytime I try to do a backup
of that database I get a "file or firegroup is not online..." error message
and SQL won't let me drop it because it does not exist. Can anyone help me
out?Hi Henry
If there was data in this filegroup then you would have to resort to your
last backup.
John
"Henry" wrote:

> Hi,
> I moved a database from an older sql box to one of our new servers. One o
f
> the files in the prmiary file group was not moved and the server was wiped
..
> the filename is sysft_ix_STS_neo_1414639615. Everytime I try to do a back
up
> of that database I get a "file or firegroup is not online..." error messag
e
> and SQL won't let me drop it because it does not exist. Can anyone help m
e
> out?|||Hi Henry
If there was data in this filegroup then you would have to resort to your
last backup.
John
"Henry" wrote:

> Hi,
> I moved a database from an older sql box to one of our new servers. One o
f
> the files in the prmiary file group was not moved and the server was wiped
..
> the filename is sysft_ix_STS_neo_1414639615. Everytime I try to do a back
up
> of that database I get a "file or firegroup is not online..." error messag
e
> and SQL won't let me drop it because it does not exist. Can anyone help m
e
> out?|||I'm pretty certain this is the full text index and that rebuilding or removi
ng full-text indexing
would solve this. This is what I recall from earlier post with the same prob
lem.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:7E297789-FEFB-41D9-BF17-90BF96129198@.microsoft.com...[vbcol=seagreen]
> Hi Henry
> If there was data in this filegroup then you would have to resort to your
> last backup.
> John
> "Henry" wrote:
>|||Hi Tibor
You are probably right! sp_help_fulltext_catalogs might verify this!
John
"Tibor Karaszi" wrote:

> I'm pretty certain this is the full text index and that rebuilding or remo
ving full-text indexing
> would solve this. This is what I recall from earlier post with the same pr
oblem.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:7E297789-FEFB-41D9-BF17-90BF96129198@.microsoft.com...
>|||I'm pretty certain this is the full text index and that rebuilding or removi
ng full-text indexing
would solve this. This is what I recall from earlier post with the same prob
lem.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:7E297789-FEFB-41D9-BF17-90BF96129198@.microsoft.com...[vbcol=seagreen]
> Hi Henry
> If there was data in this filegroup then you would have to resort to your
> last backup.
> John
> "Henry" wrote:
>|||Hi Tibor
You are probably right! sp_help_fulltext_catalogs might verify this!
John
"Tibor Karaszi" wrote:

> I'm pretty certain this is the full text index and that rebuilding or remo
ving full-text indexing
> would solve this. This is what I recall from earlier post with the same pr
oblem.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:7E297789-FEFB-41D9-BF17-90BF96129198@.microsoft.com...
>

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

drop schema and its objects

I want to be able to drop a schema and all its objects if they exist. Can someone help me with such a stored procedure. I see the sql server does not allow dropping schema directly if it contains some objects.

It is not always true. if you use indexed views, foreign keys then you are correct.

You can use sp_depends 'Your table name' to find all the dependent views/sps & others..

|||

I think I did not explain my question clearly.

I am using :

Code Snippet

DROP SCHEMA [new]

Msg 3729, Level 16, State 1, Line 1

Cannot drop schema 'new' because it is being referenced by object 'cattr'.

I want to be able to drop the schema, even if some objects reference it. If it is mandatory to first drop all objects that the schema contains, then how can I programmitically delete all such objects and then delete the schema ?

So given a schema name the stored procedure should be able to drop the schema.

|||

Is there any way to do the above mentioned.

|||

You could use following statement for determining objects in schema and there types:

Code Snippet

select obj.type, obj.name

from sys.objects obj join sys.schemas s on (s.schema_id=obj.schema_id)

where s.name='<SCHEMA NAME>'

Then, you could use cursor for calling needed DROP statements

|||

I think there is one problem with the above approach. We need to drop tables in a schema in a particular order as there may be referencences, foreign key relationships etc.

How do I determine the order ?

|||

You could delete constrains at first, then delete tables.

Also you could include each DROP statement in TRY/CATCH block and when some DROP fail analyze error message

|||

Or even try following:

Code Snippet

declare @.cnt int

select @.cnt=count(*)

from sys.objects obj join sys.schemas s on (s.schema_id=obj.schema_id)

where s.name='<you schema>'

--iterate while all objects deleted

while @.cnt >0

Begin

--Drop objects with cursor

--Each drop statemnt include into TRY/CATCH block, but ignore error

--New objects count

select @.cnt=count(*)

from sys.objects obj join sys.schemas s on (s.schema_id=obj.schema_id)

where s.name='<you schema>'

end

|||

>> obj.type, obj.name

How do I construct the drop statement from these ?

Say I got 'U' as type and 'cattr' as table name, I need the statement "drop table cattr"

But how to get the mapping between type 'U' and what it represents 'table'

or do I have write explit if else statements like

if type = 'U'

drop table

elsif type = 'P'

drop procedure....

But that's going to be a long list.

|||

I am not sure as to why it is so difficult to drop a schema.... especially when someone has permissions to do it. When we can drop a table eventhough it contains some data, why can't we drop a schema even if it contains some objects ?

And How do I construct drop statements for each object , the problem which I described above ?

|||

You could use dynamic sql for constructing drop statements.

Use CASE statement for constructing varchar variable:

@.dropQuery = 'DROP '+<case statements that returns TABLE, CONSTRAINT etc>+' '+obj.name

Then: EXECUTE(@.dropQuery)

Drop role, user, login

Okay I figured out how to determine if stored procs and funcs exist before dropping them.

How do I do the same for ROLE, LOGIN, USER?

I want get rid of annoying messages in my scripts when trying to drop something that doesn't exist.

Server 2005 and Server Express 2005

Thanks

For logins you can query sys.server_principals, for users you can query sys.database_principals or USER_ID() and for roles sys.database_principals. Ex:

-- logins (if you want to drop certificate based logins then you need to check for other types)

if exists(select * from sys.server_principals

where type IN ('S', 'U', 'G') and name = @.name)

begin

set @.name = quotename('somelogin')

exec('drop login ' + @.name)

end

-- users

if USER_ID(@.name) is not null

begin

set @.name = quotename('someuser')

exec('drop user ' + @.name)

end

-- users

if exists(select * from sys.database_principals

where type IN ('S') and name = @.name)

begin

set @.name = quotename('someuser')

exec('drop user ' + @.name)

end

-- roles

if exists(select * from sys.database_principals

where type IN ('R') and name = @.name)

begin

set @.name = quotename('someuser')

exec('drop role ' + @.name)

end

See the BOL "security catalog views" topic for more details.

|||Many thanks

Drop Primary key constraint of (#) Hash

Hi there,

I have created a hash table. After using it, somehow the primary key
constraint of this hash table still exist in database. Which cause
error.

When I delete this constraint with Alter table Drop con...
It gives no table exist error.

Can anybody give any idea.

Thanks in Adv.,
T.S.Negi> I have created a hash table. After using it, somehow the primary key
> constraint of this hash table still exist in database. Which cause
> error.

I assume that by "hash table" you mean a temporary table? What makes you
think that the PK exists after the table is dropped? (Don't trust the output
of Enterprise Manager as that isn't always refreshed when you would expect
it to be) What error is caused and what is the code that produces the error?

--
David Portas
SQL Server MVP
--|||tilak.negi@.mind-infotech.com (T.S.Negi) wrote in message news:<a1930058.0408102110.40ec8809@.posting.google.com>...
> Hi there,
> I have created a hash table. After using it, somehow the primary key
> constraint of this hash table still exist in database. Which cause
> error.
> When I delete this constraint with Alter table Drop con...
> It gives no table exist error.
>
> Can anybody give any idea.
> Thanks in Adv.,
> T.S.Negi

I don't quite understand your issue - this works fine for me:

create table #t (col1 int not null)
alter table #t add constraint PK_t primary key (col1)
alter table #t drop constraint PK_t
drop table #t

It would be best to post your version of MSSQL, the exact error
messages you get, as well as some explanation of why you believe the
constraint exists but the table doesn't.

Simon

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 an extended proc that doesn't exist

the latest hotfix for sql2000 gets an error when it tries
to replace an extended proc named xp_mergexpusage in the
replsys.sql file. Here is a small test case to show the
issue. Any ideas on how to resolve this?
Script:--
select * from sysobjects
where name = 'xp_mergexpusage'
go
execute dbo.sp_dropextendedproc 'xp_mergexpusage'
go
execute sp_addextendedproc 'xp_mergexpusage', 'xprepl.dll'
go
result:---
(0 row(s) affected)
Server: Msg 3701, Level 11, State 5, Procedure
sp_dropextendedproc, Line 18
Cannot drop the procedure 'xp_mergexpusage', because it
does not exist in the system catalog.
Server: Msg 2714, Level 16, State 7, Procedure
sp_addextendedproc, Line 26
There is already an object named 'xp_mergexpusage' in the
database.If you are in this state all sp_addextendedproc calls should fail.
Try running this:
select max(id) from master.dbo.sysobjects
dbcc traceon(3604)
dbcc dbinfo('master')
dbcc traceoff(3604)
What is the value returned by the first query and the value of dbi_nextid =returned by the dbcc dbinfo ?
If they are indentical, try doing:
create table master.dbo.t1(c1 int)
drop table master.dbo.t1
And run the query again, see if the values are no longer identical.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2003 All rights reserved.
"Gerald Bowers" <gerald.bowers@.wachovia.com> wrote in message
news:018201c356cb$5a3294c0$a501280a@.phx.gbl...
> the latest hotfix for sql2000 gets an error when it tries
> to replace an extended proc named xp_mergexpusage in the
> replsys.sql file. Here is a small test case to show the
> issue. Any ideas on how to resolve this?
> Script:--
> select * from sysobjects
> where name = 'xp_mergexpusage'
> go
> execute dbo.sp_dropextendedproc 'xp_mergexpusage'
> go
> execute sp_addextendedproc 'xp_mergexpusage', 'xprepl.dll'
> go
> result:---
> (0 row(s) affected)
> Server: Msg 3701, Level 11, State 5, Procedure
> sp_dropextendedproc, Line 18
> Cannot drop the procedure 'xp_mergexpusage', because it
> does not exist in the system catalog.
> Server: Msg 2714, Level 16, State 7, Procedure
> sp_addextendedproc, Line 26
> There is already an object named 'xp_mergexpusage' in the
> database.
>|||You are correct, upon working with it further, later sp_addextendedproc calls did in fact fail.
You solution did rectify the situation. Do you know what series of events gets it into that state?
Thanks,
Gerald
>--Original Message--
>If you are in this state all sp_addextendedproc calls should fail.
>Try running this:
>select max(id) from master.dbo.sysobjects
>dbcc traceon(3604)
>dbcc dbinfo('master')
>dbcc traceoff(3604)
>What is the value returned by the first query and the value of dbi_nextid =3D
>returned by the dbcc dbinfo ?
>If they are indentical, try doing:
>create table master.dbo.t1(c1 int)
>drop table master.dbo.t1
>And run the query again, see if the values are no longer identical.
>GertD@.SQLDev.Net
>Please reply only to the newsgroups.
>This posting is provided "AS IS" with no warranties, and confers no rights.
>You assume all risk for your use.
>Copyright =A9 SQLDev.Net 1991-2003 All rights reserved.
>"Gerald Bowers" <gerald.bowers@.wachovia.com> wrote in message
>news:018201c356cb$5a3294c0$a501280a@.phx.gbl...
>> the latest hotfix for sql2000 gets an error when it tries
>> to replace an extended proc named xp_mergexpusage in the
>> replsys.sql file. Here is a small test case to show the
>> issue. Any ideas on how to resolve this?
>> Script:--
>> select * from sysobjects
>> where name =3D 'xp_mergexpusage'
>> go
>> execute dbo.sp_dropextendedproc 'xp_mergexpusage'
>> go
>> execute sp_addextendedproc 'xp_mergexpusage', 'xprepl.dll'
>> go
>> result:---
>> (0 row(s) affected)
>> Server: Msg 3701, Level 11, State 5, Procedure
>> sp_dropextendedproc, Line 18
>> Cannot drop the procedure 'xp_mergexpusage', because it
>> does not exist in the system catalog.
>> Server: Msg 2714, Level 16, State 7, Procedure
>> sp_addextendedproc, Line 26
>> There is already an object named 'xp_mergexpusage' in the
>> database.
>
>.
>

Drop a Role in Report Sever

I create a new Role "TestRole"under Security/ Roles in the Report Server.
I i want to drop this Role, i get the Error that the Role does not exist ?
But the Role exist !!
Anyone can help ?

For UI operations, please post to SQL Server Tools General.

Thanks
Laurentiu

Drop a Role in Report Sever

I create a new Role "TestRole"under Security/ Roles in the Report Server.
I i want to drop this Role, i get the Error that the Role does not exist ?
But the Role exist !!
Anyone can help ?

For UI operations, please post to SQL Server Tools General.

Thanks
Laurentiu