Can't remove empty file group - no files in sysfiles

Hi,
I've dropped all the files in my index filegroup (not default) but I
get this message when I try to remove the filegroup itself.
Server: Msg 5042, Level 16, State 8, Line 1
The filegroup 'xxxxx' cannot be removed because it is not empty.
Sysfiles isnt' listing the deleted files and sysfilegroups is showing
that there are 0 files in the filegroup. I've confirmed this by
looking at the properties of the database through EM. I've tried
cycling the services but I still can't drop the filegroup. Does anyone
know why I can't remove the filegroup? Any help would be greatly
appreciated.
I'm running SQL Server 2000 sp3
Thanks!
TelaanIs this the primary filegroup? If so, you can't remove it.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"telaan" <telaan@.hotmail.com> wrote in message
news:1127245241.370186.158990@.g47g2000cwa.googlegroups.com...
Hi,
I've dropped all the files in my index filegroup (not default) but I
get this message when I try to remove the filegroup itself.
Server: Msg 5042, Level 16, State 8, Line 1
The filegroup 'xxxxx' cannot be removed because it is not empty.
Sysfiles isnt' listing the deleted files and sysfilegroups is showing
that there are 0 files in the filegroup. I've confirmed this by
looking at the properties of the database through EM. I've tried
cycling the services but I still can't drop the filegroup. Does anyone
know why I can't remove the filegroup? Any help would be greatly
appreciated.
I'm running SQL Server 2000 sp3
Thanks!
Telaan|||Nope. Just an additional filegroup I used to put indexes in.|||When you emptied the files, did you use the EMPTYFILE option with DBCC
SHRINKFILE?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"telaan" <telaan@.hotmail.com> wrote in message
news:1127245751.943569.313230@.g44g2000cwa.googlegroups.com...
Nope. Just an additional filegroup I used to put indexes in.|||does the file group is is a default file group?.|||If you are going to drop a filegroup, then you should make another group the
default.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Praveen" <apveen@.gmail.com> wrote in message
news:1127295464.994452.116930@.o13g2000cwo.googlegroups.com...
does the file group is is a default file group?.|||Thanks for the replies. The filegroup wasn't default but I've found
the problem by running the profiler to find out what system table still
had the filegroup referenced. It was the sysindexes table holding old
stats. You can find by running this query.
select name,id from dbo.sysindexes where groupid = 2 --id of filegroup
you want to drop.
I dropped the stats and was able to drop the filegroup.

Can't remove empty file group - no files in sysfiles

Hi,
I've dropped all the files in my index filegroup (not default) but I
get this message when I try to remove the filegroup itself.
Server: Msg 5042, Level 16, State 8, Line 1
The filegroup 'xxxxx' cannot be removed because it is not empty.
Sysfiles isnt' listing the deleted files and sysfilegroups is showing
that there are 0 files in the filegroup. I've confirmed this by
looking at the properties of the database through EM. I've tried
cycling the services but I still can't drop the filegroup. Does anyone
know why I can't remove the filegroup? Any help would be greatly
appreciated.
I'm running SQL Server 2000 sp3
Thanks!
TelaanIs this the primary filegroup? If so, you can't remove it.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"telaan" <telaan@.hotmail.com> wrote in message
news:1127245241.370186.158990@.g47g2000cwa.googlegroups.com...
Hi,
I've dropped all the files in my index filegroup (not default) but I
get this message when I try to remove the filegroup itself.
Server: Msg 5042, Level 16, State 8, Line 1
The filegroup 'xxxxx' cannot be removed because it is not empty.
Sysfiles isnt' listing the deleted files and sysfilegroups is showing
that there are 0 files in the filegroup. I've confirmed this by
looking at the properties of the database through EM. I've tried
cycling the services but I still can't drop the filegroup. Does anyone
know why I can't remove the filegroup? Any help would be greatly
appreciated.
I'm running SQL Server 2000 sp3
Thanks!
Telaan|||Nope. Just an additional filegroup I used to put indexes in.|||When you emptied the files, did you use the EMPTYFILE option with DBCC
SHRINKFILE?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"telaan" <telaan@.hotmail.com> wrote in message
news:1127245751.943569.313230@.g44g2000cwa.googlegroups.com...
Nope. Just an additional filegroup I used to put indexes in.|||does the file group is is a default file group?.|||If you are going to drop a filegroup, then you should make another group the
default.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Praveen" <apveen@.gmail.com> wrote in message
news:1127295464.994452.116930@.o13g2000cwo.googlegroups.com...
does the file group is is a default file group?.|||Thanks for the replies. The filegroup wasn't default but I've found
the problem by running the profiler to find out what system table still
had the filegroup referenced. It was the sysindexes table holding old
stats. You can find by running this query.
select name,id from dbo.sysindexes where groupid = 2 --id of filegroup
you want to drop.
I dropped the stats and was able to drop the filegroup.

Cant register to a named instance

2nd instance of sql was sucessfully installed on the 2nd node of the
cluster. However if i try to register on this 2nd instance it failes with
msg as sql server does not exists or access denied. I tried with the Ip
address too but it fails with same error. I havent installed sp3a on this
instance was trying to get the instance up and running but the initial step
only fails. Has anyone seen this before. any help will be greatly
appreciated. TIA
--
Message posted via http://www.sqlmonster.comHive you seen
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag2k/html/MultipleInstances.asp ?
Peter
"I favor the Civil Rights Act of 1964 and it must be enforced at gunpoint if
necessary."
Ronald Reagan
"ishaan99 via SQLMonster.com" wrote:
> 2nd instance of sql was sucessfully installed on the 2nd node of the
> cluster. However if i try to register on this 2nd instance it failes with
> msg as sql server does not exists or access denied. I tried with the Ip
> address too but it fails with same error. I havent installed sp3a on this
> instance was trying to get the instance up and running but the initial step
> only fails. Has anyone seen this before. any help will be greatly
> appreciated. TIA
> --
> Message posted via http://www.sqlmonster.com
>|||Hi
You might need to add an alias using the SQL Server client network utility.
You can find it in your SQL Server programs group.
What you need to put in depends on your network protocols. We use either
TCP/IP or named pipes. For TCP/IP you need to supply 'server name\instance
name' and the port that SQL Server is running on. For named pipes supply the
server name\instance name.
You then should be able to access the named instance.
Hope this helps
John
"ishaan99 via SQLMonster.com" wrote:
> 2nd instance of sql was sucessfully installed on the 2nd node of the
> cluster. However if i try to register on this 2nd instance it failes with
> msg as sql server does not exists or access denied. I tried with the Ip
> address too but it fails with same error. I havent installed sp3a on this
> instance was trying to get the instance up and running but the initial step
> only fails. Has anyone seen this before. any help will be greatly
> appreciated. TIA
> --
> Message posted via http://www.sqlmonster.com
>|||Thanks but I did try adding an alias from client n/w . also tried using
osql with servername\instancename but i get the same error. However I could
see 2nd instance of sql server and sql agent running from 2nd node services
though. My 2nd node accesses 4 drives and i have all system files on one of
these 4 drives but MSSQL$2INSTANCE is under c:\ProgramFiles.... Has it got
anyting to do with installation? Since i am not able to isql to this server
I feel is sql itself is failing.. any thoughts on this'
--
Message posted via http://www.sqlmonster.com|||This what i see after trying from osql
Unable to connect: SQL Server is unavailable or does not exists. Net-
library error 2: ConnectionOpen (Connect())
--
Message posted via http://www.sqlmonster.com|||Have you looked at the SQL Server logs.
It should tell what types of client connections can be made (if you are
using TCP/IP what ports are being used etc).
e.g. my log has the following lines in it
SQL server listening on 127.0.0.1: 2433
SQL server listening on 169.254.151.241: 2433
...
SQL server listening on TCP, Shared Memory, Named Pipes
"ishaan99 via SQLMonster.com" wrote:
> 2nd instance of sql was sucessfully installed on the 2nd node of the
> cluster. However if i try to register on this 2nd instance it failes with
> msg as sql server does not exists or access denied. I tried with the Ip
> address too but it fails with same error. I havent installed sp3a on this
> instance was trying to get the instance up and running but the initial step
> only fails. Has anyone seen this before. any help will be greatly
> appreciated. TIA
> --
> Message posted via http://www.sqlmonster.com
>

cant open crystal report on client

guys, i've probem with opening cr report via vb on client. The error msg is " server has not yet been opened ", i try to opened manually from crystall report and its working ( on client too ) but when i try to open on my vb an error comes again.

Unfortunelly when i run this application on server, everything is okay.

how ?

erickmaybe u should post ur code to figure it out..

thanks,
erick (same name, same location with u :0)

can't insert into ...because of nulls problme!

Hello,
I am trying to insert some records into a table from an SP but get this
message:
Server: Msg 515, Level 16, State 2, Procedure stp_InsetIntoSubDetail, Line 6
Cannot insert the value NULL into column 'CurEntryDate', table
'Subscriber.dbo.SubDetail'; column does not allow nulls. INSERT fails.
The statement has been terminated.
I am inserting into only a few fields. How can I negotiate this?
Thanks,
RichYou cannot, unless you pass a value for every non-nullable column in the
table.
The column CurEntryDate sounds like a column that captures the insertion
time of the row. So you might be able to pass CURRENT_TIMESTAMP as a value
in the INSERT statement. If being passed through a stored procedure, declare
a local variable that has the current date/time value and pass it as an
input parameter.
You may also consult your database designer and see if he can set a default
on this column to eliminate the need to pass this value in every INSERT
statement.
Anith|||Thanks. What I did was to temporarily allow nulls in these fields. and the
n
restored the table back. Is this practice OK?
"Anith Sen" wrote:

> You cannot, unless you pass a value for every non-nullable column in the
> table.
> The column CurEntryDate sounds like a column that captures the insertion
> time of the row. So you might be able to pass CURRENT_TIMESTAMP as a value
> in the INSERT statement. If being passed through a stored procedure, decla
re
> a local variable that has the current date/time value and pass it as an
> input parameter.
> You may also consult your database designer and see if he can set a defaul
t
> on this column to eliminate the need to pass this value in every INSERT
> statement.
> --
> Anith
>
>|||If constraints have no purpose don't use them at all. I'd suggest going
through the documentation, but then again you may have already done that
without any success. My guess is that if a constraint was ever defined it wa
s
defined for a reason.
If you've now gone and inserted data through a backdoor, this just might
cause problems for you later on - i.e. with queries written against
non-nullable columns which now contain null values.
ML
http://milambda.blogspot.com/|||On Thu, 19 Jan 2006 14:29:02 -0800, Rich wrote:

>Thanks. What I did was to temporarily allow nulls in these fields. and th
en
>restored the table back. Is this practice OK?
Hi Rich,
No, it definitely isn't.
There is probably a reason why this column is defined to not allow
NULLs. If you overide that restriction, you're probably entering invalid
data and endagnering the integrity of your database.
Would you also temporarly disable a CHECK constraint that allows only
'Male' and 'Female' in the gender column to enable the value 'Water' to
be entered there?
Hugo Kornelis, SQL Server MVP|||> Thanks. What I did was to temporarily allow nulls in these fields. and
> then
> restored the table back. Is this practice OK?
The practice doesn't make sense. Don't you think the NOT NULL constraints
are there for a reason? Why bother having them at all if you're just going
to remove them every time you insert data? Isn't this kind of like not
wearing your seatbelt?|||Thank you all for your input on this matter. I undid what I was going to
try. Anyway, No, there is no documentation. I am on my own! Truth be told
,
I am not at the guru level. I am going to have to pay my dues with a lot of
heart ache. The task was one of these add-hoc things, and I just started on
assuming the duties of this project which was already in motion for a while.
So I rushed! then I slowed down and created fake data ('' for varchars and
an agreed upon fake date for date fields) to accommodate the constraints.
That did the trick. The first reply said I can't insert records if nulls ar
e
not allowed unles I have some values. So I created the values. Anyway, I
really appreciate the replies. Even though I was suggesting ideas of an
amateur nature these replies will help to keep me on the proper course.
Rich
"Rich" wrote:

> Hello,
> I am trying to insert some records into a table from an SP but get this
> message:
> Server: Msg 515, Level 16, State 2, Procedure stp_InsetIntoSubDetail, Line
6
> Cannot insert the value NULL into column 'CurEntryDate', table
> 'Subscriber.dbo.SubDetail'; column does not allow nulls. INSERT fails.
> The statement has been terminated.
> I am inserting into only a few fields. How can I negotiate this?
> Thanks,
> Rich|||In case of lacking documentation maybe one of the users can help. And there'
s
got to be at least some shread of documentation. A leaflet, a greasy paper
napkin, something.
ML
http://milambda.blogspot.com/|||Your idea was reasonable for someone new to database programming. You had
some reservations and asked the right folks a question. We all have to ask
questions like this when we start out.
An amateur would have made the change and not bothered asking anyone if it
made sense.
One additional note though...
If you had removed the constraint in order to insert the null values, you
would not have been able to put the constraint back without first changing
the null values to actual values. If your data does not follow the rules of
the constraint you are trying to make, then the database will not let you
create the constraint.
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:43A5D9EE-1103-409F-A440-7EFCDC318DEF@.microsoft.com...
> Thank you all for your input on this matter. I undid what I was going to
> try. Anyway, No, there is no documentation. I am on my own! Truth be
told,
> I am not at the guru level. I am going to have to pay my dues with a lot
of
> heart ache. The task was one of these add-hoc things, and I just started
on
> assuming the duties of this project which was already in motion for a
while.
> So I rushed! then I slowed down and created fake data ('' for varchars
and
> an agreed upon fake date for date fields) to accommodate the constraints.
> That did the trick. The first reply said I can't insert records if nulls
are
> not allowed unles I have some values. So I created the values. Anyway, I
> really appreciate the replies. Even though I was suggesting ideas of an
> amateur nature these replies will help to keep me on the proper course.
> Rich
> "Rich" wrote:
>
Line 6|||> Your idea was reasonable for someone new to database programming. You had
> some reservations and asked the right folks a question. We all have to as
k
> questions like this when we start out.
> An amateur would have made the change and not bothered asking anyone if it
> made sense.
I strongly agree. No one is born smart.
ML
http://milambda.blogspot.com/