Cann't Remove Data File From User Database

Mar 15, 2004

We are running SQL server 2003 with SP3. I'm trying to
shrink a data files with the emptyfile option so I can
eventually remove the file using the alter database
command. However, I get the following error message when I
run the alter database command:

Error: the file PRADATA4 cannot be removed because it is
not empty.

The file that I'm trying to remove still has 62 extents on it.
I looked MS Knowledge base 254253 and 279511 on this problem but they say it is corrected by SQL server 7.0 with service pack 3.
commands that I'm running are as follows:

1) USE PRA
DBCC TRACEON(8901)
DBCC SHRINKFILE ('PRADATA4', EMPTYFILE)
DBCC TRACEOFF(8901)

2) USE PRA
GO
ALTERDATABASE PRA
REMOVE FILE PRADATA4
GO

Can anyone help?

Thanks

View 8 Replies


ADVERTISEMENT

Cann I Add New Column In Subscription Database Table

Aug 13, 2007

Hi

I am learning Sql server 2005 replication.

My requirement is.

1.) Subscribe a Master database
2.) I have to add a timestamp column in every table of subscriber database ( Note: the column will not be in publication database)

I have two sql server

server 1 : I made Merger publication

server 2 : I made subscription of the same

But when i tried to create a column in the Subscriber database table its giving the following error

Validation warnings

warnings were encountered during the pre-save validation process, and might
result in a failute during save. Do you want to continue attempt to save?

''Table1' table
- Error validating the default for column 'rowguid'.

I clicked "Yes"


Post -Save Notication
Errors were encountered during the save process. Some database objects were not saved.

'Table1' table
- Unable to modify table.
The DDL statement cannot be performed at the Subscriber or Republisher.
The DDL operation failed inside merge DDL replication manipulation.
The transaction ended in the trigger. The batch has been aborted.


clicked ok

User canceled out of save diaglog

Can i add a new column to subscriber database table if so please guide me?

View 5 Replies View Related

How Can Remove The User From A Database In MSSQL2005 Server

Nov 20, 2007

Hi all,

I am new to SQL 2005 and I am trying to drop a user from a database called TESTDB. When I try to delete the user I get the following error message:

User,group or roll 'test' already exists in the database (Microsoft SQL Server,Error :15023)

How can I remove the user from the database in MSSQL2005 server



Please help me

Thank you,

Chamith

View 3 Replies View Related

DB Design :: Remove Backup Option For All User For A Database In Server

Aug 4, 2015

I don't want to any body can backup of my database, even i can also not able to take backup.

View 17 Replies View Related

How To Remove Secondary Data File????

Jan 16, 2003

I have a SQL Server 2000 database with a primary data file (MDF) and a secondary data file (NDF). I would like to remove the secondary file and only have the MDF. Is this possible?

Thanks, Dave

View 3 Replies View Related

Alter Database With Remove File Clause

Apr 4, 2001

I'm attempting to alter a database by removing one of two log files. I have truncated both log files successfully and the database has no processes but I get an error stating file cannot be removed because it is not empty. Help

View 2 Replies View Related

DB Engine :: Cannot Remove One Last Data File From TempDB

May 19, 2015

In tweaking performance of tempdb by adding add'l data files I want to reset back to defaults and remove all add'l files I've created.  I was not able to do it for most as they were in use, but by starting the server in single-user mode with all other sql services shut off, and using sqlcmd I was able to use the ALTER DATABASE tempdb REMOVE FILE <tempdev#> to remove the files... except for one.

I have also tried using the command:

ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev#, FILENAME = '<new location');

Restarted SQL server, and tried the ALTER DATABASE ... REMOVE FILE again but am always denied with the message that the file can't be removed b/c it's still in use.

I also tried to shrink it with EMPTYFILE but that also fails with the message that a page is a work table page and can't be removed.

I really need to get tempdb back to just one data file but am unable to find a way to remove this last data file.

View 2 Replies View Related

Modify File On User Database

Jan 20, 2008

Hello, I have gone to work for a new company and I have seen this company is running out of drive space on a daily basis. The DBA before me created huge databases. For example we have a database that it's created size is 103 GB. Only about 52 GB is ever needed. I know you can resize the tempdb but how about user databases? I have never tried this. Can this be done for a user database?

For tempdb
Start SQL from cmd line sqlserver -c -f -s%instance_name%
alter databse tempdb modify file

(name='tempdev',size=150)

View 3 Replies View Related

Cann&#39;t Schedule The DTS Packages I Created

Jan 10, 2001

Hi Guys,

It sounds unbelievable that I can not schedule the DTS packages I created either on my local mechine or SQL server. I have the SA permission for all SQL servers and I can run the DTS packages manually in the EM or run it on DOS command line using dtsrun utility.

The error message I got says: "Microsoft SQL-DMO (ODBC SQL State: 42000)
[Microsoft][ODBC SQL Server][SQL Server] login failed for user '(null)'. Reason: not associated with a trusted Server connection.". I have the SP2 installed in all the SQL Server and my local mechine. All SQLAgents have Domain Admin permission. I just cound not figure out where that user 'null' come from.

I really appreciate that anyone can give me some hints to make the DTS scheduling work. Thanks!

Lunjun

View 4 Replies View Related

Cann't Able To Take Backup On Network Drive...........

Mar 17, 2008



HI
i am trying to take a backup on Network drive but i am getting following error message. althought when i try to type the address in window explorer. i can access the specific folder. so that there shouldn't be network security issue.... and even thought the backup device name is correct.
when i try to take backup on local disk, it work's fine....


Executed as user: NT AUTHORITYSYSTEM. Cannot open backup device 'FullBackup(\0.0.0.0SQL_BackupsFullBackupsFullBackup.bak)'. Operating system error 5(error not found). [SQLSTATE 42000] (Error 3201) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.

View 6 Replies View Related

Associate User's New Objects With Data File

Apr 20, 2007

I'm currently running SQL Server 2005, and have been exploring the feasibility of grouping users to use a default file group and data file. This would be to group a set of users to have their new tables reside in a specific data file. I only see mechanisms for moving an already existing objects into a file group or to set the entire databases default file group--not a user level association or dynamic assignment of the default for that user, script, etc.

It's not looking good.

I'm looking for confimration that it's not possible or leads on doing so.

Thanx in advance

View 1 Replies View Related

Cannot Remove Dbo Rights From A User

Jun 16, 2006

In the login properties on the Database Access tab, a user's name saysdbo instead of his name. I do not want him to have dbo permissions. Icannot drop the db_owner permissions that he has on the databasebecause I get the error "Error 15405: Cannot use the reserved user orrole name 'dbo'."How do I remove these permissions from this user?

View 3 Replies View Related

An Attempt To Attach An Auto-named Database For File (file Location).../Database.mdf Failed. A Database With The Same Name Exists, Or Specified File Cannot Be Opened, Or It Is Located On UNC Share.

Sep 2, 2007

Greetings, I have just arrived back into the country (NZ) and back into ASP.NET.
 I am having trouble with the following:An attempt to attach an auto-named database for file (file location).../Database.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
It has only begun since i decided i wanted to use IIS, I realise VWD comes with its own localhost, but since it is only temporary, i wanted a permanent shortcut on my desktop to link to my intranet page.
 Anyone have any ideas why i am getting the above error? have searched many places on the internet and not getting any closer.
Cheers ~ J
 

View 3 Replies View Related

Import Excel Data From User-selected Source File

Dec 4, 2007

Is it possible to import data from an Excel spreadsheet using OPENROWSET or OPENDATASOURCE without having to explicitly define the filepath of the source file? Currently, I have this piece of code within a sproc:


INSERT INTO [dbo].[ProductionRequirementDetail]

([ProductionRequirementHeaderID], [SKU], [Quantity])

SELECT @ProductionRequirementHeaderID,

[SKU],

[LAMPS]

FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0','Excel 8.0; Database=C:WeeklySchedule.xls', 'SELECT * FROM [Master$C5:Q65536]') AS XL

LEFT JOIN [dbo].[PartMaster] ON (RIGHT([XL].[CODE], 7) = [PartMaster].[SKU])

WHERE [SKU] IS NOT NULL

AND [CODE] IS NOT NULL

AND [LAMPS] IS NOT NULL

AND [LAMPS] > 0

AND [LampTypeID] = @LampTypeID




I would like to remove the hardcoded reference 'Database=C:WeeklySchedule.xls' and replace it with a parameter for the filepath. Is this possible? This is in SQL Server 2000. Also, if there is a way to do this with DTS I'd be open to doing it that way too.

View 8 Replies View Related

Unable To Remove User From Public Role ?

Dec 22, 2000

Can anybody explain what database user with a prefix of "" in the public role indicates. I have the same two users in every database and cannot remove them?

Any help would be gratefully received.

View 4 Replies View Related

Customer Wants To Remove Power User (was Need Some Advise)

Jul 19, 2007

Hi everyone,

I have a customer that wants to remove a power user
from her company.

She has given him the SA password (against my advise)
and he has his own user account setup in the database.


I am not sure if he has created any backdoor(s) into the
SQL box or not - I left him kinda on his own and didn't
pay too much attention to him.

I assume people have faced this problem before,
and i am looking for a best practices or "here is what we did"
post mortem of how they handled the issue.

thanks
tony

View 4 Replies View Related

Cann't Execute Integration Service Package With SQL Server Agent

Nov 17, 2006

hi, can anyone explain, how to execute package with sql server agent:

the problem is::

i made a integration service package (in visual studio 2005). When I 'm in visual studio and do "execute" the package it works well. Then I build a dtsx file. When i double click on it, it opens and if i do execute, it works well.

I want to execute the package every week automatically. I write a job in SQL server agent, in Steps I choosed Type=SQL server Integration service packege, then General->packacge source -> file system, and then wrote where the dtsx file is (which was built). "Run as" I choose my user name.

then i'm trying to start a job, but it fails, and writes "The package execution failed."

What I'm doing wrong.

(sorry for my english)

View 2 Replies View Related

Failed To Generate A User Instance Of SQL Server Due To Failure In Retrieving The User's Local Application Data Path. Please Make Sure The User Has A Local User Profile On The Computer. The Connection Will Be Closed

Dec 7, 2006

This is my first time to deploy an asp.net2 web site. Everything is working fine on my local computer but when i published the web site on a remote computer i get the error "Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed" (only in pages that try to access the database)
Help pleaseee

View 3 Replies View Related

Autogrow Of File 'Prod' In Database 'Production' Was Cancelled By The User Or Timed Out

May 27, 2008

We are running Microsoft SQL 2005 Express.

All queries on our Production database are timing out. Viewing the error log file the following show up over and over again:

Autogrow of file 'tempdev' in database 'tempdb' was cancelled by user or timed out after 3937 milliseconds. Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size.

Autogrow of file 'Prod' in database 'Production' was cancelled by user or timed out after 33156 milliseconds. Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size.

Our production database is about 1 gig in size with 3.5 million records. I tried setting the autogrow from 30% which it was before to 100MB, but no luck, still timing out and getting the errors above. Permission should be all good, nothing has changed.

There is about 50gigs of available disk space as well, so that's not the problem. Thanks for the help.

View 51 Replies View Related

How To Query A Dbo.file Of User-defined Database That Has The Nvarch Type In A Column?

Nov 3, 2007

Hi all,
In my SQL Server Management Studio Express, I have the following Database "ChemAveRpd", Table "dbo.LabTests", and Content of the Table:
dbo.LabTests Column_name Type Length Prec Scale
AnalyteID int 4 10 0 (Primary Key)
AnalyteName nvarch 510
CasNumber nvarch 510
Result numeric 5 8 2
Unit nvarch 510
SampleID int 4 10 0 (Foreign Key)

AnalyteID AnalyteName CasNumber Result Unit SampleID
1 Acetone 123-456-9 134.0 ug/L 1
2 Bezene 666-12-8 2.0 ug/L 1
3 Cobalt 421-008-7 10.0 ug/L 1
4 Acetone 123-456-9 201.0 ug/Kg 2
5 Bezene 666-12-8 1.0 ug/Kg 2
6 Cobalt 421-008-7 22.0 ug/Kg 2
7 Acetone 123-456-9 357.0 ug/L 3
8 Bezene 666-12-8 9.0 ug/L 3
9 Cobalt 421-008-7 56.0 ug/L 3


When I ran the following T-SQL code:
USE ChemAveRpd

GO

SELECT *

FROM dbo.LabTests as LabResults

Where AnalyteName = Acetone

GO


I got the following error:

Msg 207, Level 16, State 1, Line 3

Invalid column name 'Acetone'.

Please help and tell me what right syntax I should write for [Where AnalyteName = Acetone] to generate a listing of LabResults for Acetone.

Thanks in advance,
Scott Chang



View 4 Replies View Related

How Do I Insert Data From A Flat File Or .csv File Into An Existing SQL Database???

Mar 29, 2006

How do I insert data from a flat file or .csv file into an existing SQL database???

Here what I've come up with thus far and I but it doesn't work. Can someone please help? Let me know if there is a better way to do this... Idealy I'd like to write straight to the sql database and skip the datset all together...

strSvr = "vkrerftg"

StrDb = "Test_DB"

'connection String

strCon = "Server=" & strSvr & ";database=" & StrDb & "; integrated security=SSPI;"

Dim dbconn As New SqlConnection(strCon)

Dim da As New SqlDataAdapter()

Dim insertComm As New SqlCommand("INSERT INTO [Test_DB_RMS].[dbo].[AIR_Ouput] ([Event], [Year], [Contract Loss],[Company Loss], " & _

"[IndInsured Loss Prop],[IndInsured Loss WC],[Event Info]) " & _

"VALUES (@Event, @Year, @ConLoss, @CompLoss, @IndLossProp, @IndLossWC, @eventsInfo)", dbconn)

insertComm.Parameters.Add("@Event", SqlDbType.Int, 4, "Event")

insertComm.Parameters.Add("@Year", SqlDbType.Float, 4, "Year")

insertComm.Parameters.Add("@ConLoss", SqlDbType.Float, 4, "Contract Loss")

insertComm.Parameters.Add("@CompLoss", SqlDbType.Float, 4, "Company Loss")

insertComm.Parameters.Add("@IndLossProp", SqlDbType.Float, 4, "IndInsured Loss Prop")

insertComm.Parameters.Add("@IndLossWC", SqlDbType.Float, 4, "IndInsured Loss WC")

insertComm.Parameters.Add("@eventsInfo", SqlDbType.NVarChar, 255, "Event Info")

da.InsertCommand = insertComm

Dim upComm As New SqlCommand("UPDATE [Test_DB_RMS].[dbo].[AIR_Ouput] " & _

"SET [Event] = @Event " & _

",[Year] = @Year " & _

",[Contract Loss] = @ConLoss " & _

",[Company Loss] = @CompLoss " & _

",[IndInsured Loss Prop] = @IndLossProp " & _

",[IndInsured Loss WC] = @IndLossWC " & _

",[Event Info] = @EventInfo", dbconn)

upComm.Parameters.Add("@Event", SqlDbType.Int, 4, "Event")

upComm.Parameters.Add("@Year", SqlDbType.Float, 4, "Year")

upComm.Parameters.Add("@ConLoss", SqlDbType.Float, 4, "Contract Loss")

upComm.Parameters.Add("@CompLoss", SqlDbType.Float, 4, "Company Loss")

upComm.Parameters.Add("@IndLossProp", SqlDbType.Float, 4, "IndInsured Loss Prop")

upComm.Parameters.Add("@IndLossWC", SqlDbType.Float, 4, "IndInsured Loss WC")

upComm.Parameters.Add("@EventsInfo", SqlDbType.NVarChar, 255, "Event Info")

da.UpdateCommand = upComm

da.Update(dsAIR, "TextDB")



************* ANY HELP WOULD BE GREATLY APPRECIATED************

THANKS

View 6 Replies View Related

How Do I Insert Data From A Flat File Or .csv File Into An Existing SQL Database???

Mar 29, 2006



How do I insert data from a flat file or .csv file into an existing SQL database???

Here what I've come up with thus far and I but it doesn't work. Can someone please help? Let me know if there is a better wway to do this... Idealy I'd like to write straight to the sql database and skip the datset all together...

strSvr = "vkrerftg"

StrDb = "Test_DB"

'connection String

strCon = "Server=" & strSvr & ";database=" & StrDb & "; integrated security=SSPI;"

Dim dbconn As New SqlConnection(strCon)

Dim da As New SqlDataAdapter()

Dim insertComm As New SqlCommand("INSERT INTO [Test_DB_RMS].[dbo].[AIR_Ouput] ([Event], [Year], [Contract Loss],[Company Loss], " & _

"[IndInsured Loss Prop],[IndInsured Loss WC],[Event Info]) " & _

"VALUES (@Event, @Year, @ConLoss, @CompLoss, @IndLossProp, @IndLossWC, @eventsInfo)", dbconn)

insertComm.Parameters.Add("@Event", SqlDbType.Int, 4, "Event")

insertComm.Parameters.Add("@Year", SqlDbType.Float, 4, "Year")

insertComm.Parameters.Add("@ConLoss", SqlDbType.Float, 4, "Contract Loss")

insertComm.Parameters.Add("@CompLoss", SqlDbType.Float, 4, "Company Loss")

insertComm.Parameters.Add("@IndLossProp", SqlDbType.Float, 4, "IndInsured Loss Prop")

insertComm.Parameters.Add("@IndLossWC", SqlDbType.Float, 4, "IndInsured Loss WC")

insertComm.Parameters.Add("@eventsInfo", SqlDbType.NVarChar, 255, "Event Info")

da.InsertCommand = insertComm

Dim upComm As New SqlCommand("UPDATE [Test_DB_RMS].[dbo].[AIR_Ouput] " & _

"SET [Event] = @Event " & _

",[Year] = @Year " & _

",[Contract Loss] = @ConLoss " & _

",[Company Loss] = @CompLoss " & _

",[IndInsured Loss Prop] = @IndLossProp " & _

",[IndInsured Loss WC] = @IndLossWC " & _

",[Event Info] = @EventInfo", dbconn)

upComm.Parameters.Add("@Event", SqlDbType.Int, 4, "Event")

upComm.Parameters.Add("@Year", SqlDbType.Float, 4, "Year")

upComm.Parameters.Add("@ConLoss", SqlDbType.Float, 4, "Contract Loss")

upComm.Parameters.Add("@CompLoss", SqlDbType.Float, 4, "Company Loss")

upComm.Parameters.Add("@IndLossProp", SqlDbType.Float, 4, "IndInsured Loss Prop")

upComm.Parameters.Add("@IndLossWC", SqlDbType.Float, 4, "IndInsured Loss WC")

upComm.Parameters.Add("@EventsInfo", SqlDbType.NVarChar, 255, "Event Info")

da.UpdateCommand = upComm

da.Update(dsAIR, "TextDB")



************* ANY HELP WOULD BE GREATLY APPRECIATED************

THANKS

View 3 Replies View Related

Importing Data To An Existing Database Column From An .xsl File Or .cvs File

May 15, 2006

good morning,

 I want to load data that i receive everydays from my customers in .xls file format (excel) or cvs file format,  to the database that i have created on this purpose. but  when trying to do that whith SSIS; i got an error message .... that i can't import redudant data in my database column.

 

Best regards.

View 1 Replies View Related

How To Remove NT User After Restoring Back Up From Diffrent Domain

Jul 18, 2001

hi everybody.
Scenario

Company X has Sql server and domain x.com

so
on sql server
they have logins
XA
XB
XC

Back of database Production was done

database Production was restored in Company Y

use Production
select name from sysusers
name
----
XA
XB
XC

sp_dropuser 'XA'

Server: Msg 15008, Level 16, State 1, Procedure sp_dropuser, Line 12
User 'XA' does not exist in the current database.

How to drop 'XA' ?

View 2 Replies View Related

Cannot Open User Default Database. Login Failed. Login Failed For User 'DOMAINUser'. (.Net SqlClient Data Provider)

Apr 16, 2008

Hi when i try and open a database in sql server management studio i get the error "Cannot open user default database. Login failed. Login failed for user 'DOMAINUser'. (.Net SqlClient Data Provider)", what can i do to rectify this, i have googled around and still havent found no answers.

View 4 Replies View Related

Package With File System Task Doesn't Work Without Sensitive Data With User Keys

Dec 14, 2006

This problem is a bit weird but I'm just wondering if anybody else experienced this.

I have a package that has file system tasks (copying dtsx files actually). Basically the package copies other packages to a pre-defined destination. Thing is, it only works if one of the packages it is configured to copy has some sort of sensitive data (e.g., a connectionstring with a password), otherwise it reports a success message on execution but doesn't actually do anything. I've checked the forcedexecutionresult and it is set to None for that matter.

Just wondering if anybody else experienced this problem and of course if there's a way to solve it.

Thanks.

View 2 Replies View Related

How Do We Determine Which User Database Tables Are Mostly Retrieved By User Or Modified By User?

May 22, 2008



Hi,
Please give the T-SQL script for this ? Thanks

Shanth


View 4 Replies View Related

Transfer Data From A Foxpro Database File To Sql Server Database

Jun 11, 1999

i have a old database in foxpro and it has to be converted to sql server 6.5 database . the table in the foxpro has been broken into more than 1 tables in the sql server . so how can i transfer the data from 1 foxpro table to different tables in sql server 6.5.
vineet

View 1 Replies View Related

How To Share User-defined Data Type In Different Database?

Nov 6, 2006

Hi,everyone.

I have defined a data type "OC_BUN_NAME" in database "CVPRO". I want to create a table tempdb.dbo.CVPRO in SQL SERVER 2005 system Database tempdb. But SQL SERVER 2005 DBMS gives a Error Messages:"Can not find the data type OC_BUN_NAME".

How can I do? How to use data types in the other database?

Please give me some advice. Thank you in advance.

View 7 Replies View Related

How I Remove Records From The Log File

Oct 20, 2001

I have SQL Server Standart Edition and ý have a database, MDF file size is 650 MB, log file size is 1,23 GB

I tried to shrink the log file but i didnt shrink, I wanr to remove all records from the log file

How I remove records from the log file I want to shrink this file to 1 or 2 MB
Please help me

View 1 Replies View Related

Sysaltfiles , How Can I Remove A File

Jul 28, 2004

I'm taking the Administration of a DB wich it has on system table sysaltfiles some leftoff files that are not being used anymore on TempDB,

how can i remove them ? Every time i restart the SQL Service it tries to open those files on sysaltfiles..

I tried ALTER DATABASE tempdb remove file XXXX , it did not work...

I got this error:

ALTER DATABASE failed. Some disk names listed in the statement were not found. Check that the names exist and are spelled correctly before rerunning the statement.

View 1 Replies View Related

Data Access :: Grant A User Permissions To Only One Table In A Database?

Jul 28, 2015

How do I grant a user permissions to only one table in a database.  How would it affect him using our Main App which is NAV with regards to his user's permission in NAV

View 2 Replies View Related

Inserting Data To Text File From Database And Inserting Data Back To Database From Text File

Aug 7, 2007

Hello friends....
I am looking for 2 things(using c#.net or vb.net and sql svr 2000)
1.convert data from sql server 2000 database (say customers table from northwinds database) to a text file(separated by commas or just plain space)
2.Insert the data from text file back to database.
Can someone pls give me the detailed code to achieve this....really need this on urgent basis.......Thank You.

View 10 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved