MDF File Still Exists After DROP TABLE
Nov 5, 2005
Hello,
Using Enterprise Manager, I deleted from my database the only table
that contained records (Right-Click on Table, Choose "Delete Table").
My expectation was that the LARGE .mdf file would be reduced to minimal
size (at least as small as the Northwind MDF). However, it's still 4+
Gig in size!! (Northwind is 2.62 MB). This is a problem, bc I deleted
the table in order to recapture hard drive space.
NOTE: I already tried using Shrink Database in EM. This significantly
reduced the size of the .mdf file from its original (i.e. pre-delete)
size of 38 Gig to present size of 4 Gig.
What can I do to further reduce the size of the MDF file?
Thank you.
View 1 Replies
ADVERTISEMENT
Feb 9, 2008
i am using vb.net and ms sql server 2005 express.....what is the syntax for dropping a table if existsi have used this but it says incorrect syntax near if Dim cmda As New SqlCommand("drop table " + test + " if exists", New SqlConnection(strdb)) cmda.Connection.Open() cmda.ExecuteNonQuery() cmda.Connection.Close()any solutions???? plz only answer in vb.net and sql server express
View 8 Replies
View Related
Feb 18, 2004
in mysql i can drop a table or db if it currently exists using
drop table if exists [table1] or
drop database if exists [db1]
is there an equalivant in ms sql
thanks
View 4 Replies
View Related
Mar 31, 2004
I need to drop a table if exist ?? how can i do that ??
thanx !
View 3 Replies
View Related
Jan 21, 2010
What is the best way to drop a temp table if it exists? I am looking something similar to this: if exists (select top 1 * from #TableName) then drop #TableName else end
View 5 Replies
View Related
May 20, 2008
Hi,
Here is the steps I should take...
1- Check for the log table and find run status ( there is a date field which tells the day run)
2- Lets say last day was 2008-05-15, So I have to check A1.DDDDMMYYY file exists in the folder for each
day like A1.20080516,A1.20080517 and A1.20060518 ( until today)
3- if A1.20080516 text file exist then I have to move it to the table and same thing for other dates
like if A1.20080517 exists I have to load it to table and so on
it looks like for each loop, first I have to get the last date and then I have to check the file exists for each date and
if the date file exists then I have to load it into table...
Please tell me How can I do it. it looks complex looping...
thanks,
J
View 4 Replies
View Related
Feb 8, 2008
I'm trying to drop indexes if they already exist then recreate them as needed, however, I'm getting an error if the index does not exist and I thought that's what the "IF Exists" statement was for.
syntax:
IF EXISTS (SELECT name FROM sysindexes WHERE name = 'idx_acct_no') DROP INDEX accounts.idx_acct_no
go
create index [idx_acct_no] ON [dbo].[accounts] ([acct_no])
go
Error when index does not exist:
Server: Msg 3703, Level 11, State 7, Line 1
Cannot drop the index 'accounts.idx_acct_no', because it does not exist in the system catalog.
Is there anyway to not get this error?
View 13 Replies
View Related
Feb 24, 2007
Hi all,
I come from MySQL.
In sqlserverce
I'm looking for an equivalent command..to
"DROP INDEX IF EXISTS inventory.idxItems"
or somthing similar so that I do not get an error or crash the C# app when I try to drop a nonexisting index..
also for droping a table.
View 1 Replies
View Related
May 20, 2007
how can I drop a propcedure or a view without error for MS SQL 2000/2500
for a table
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[myTABLE]') AND type in (N'U'))
DROP TABLE [dbo].[myTABLE]
and for a procedure
IF EXISTS ???
DROP PROCEDURE [dbo].[SP_myTABLE_Count]
thank you for helping
View 4 Replies
View Related
Aug 8, 2006
Hi all.
First, sorry about my poor english.
I have a database which above 6 gb data and i have droped all table in my database.
I try to restore from log file but my database is set to simple backup and auto shrink so log file doesn't help anymore.
I have used some software to recovery from log file too but useless.
My only hope right now is mdf file.
Please help me. How could i restore my mdf file to state before i droped tables.
Thanks
View 14 Replies
View Related
Oct 9, 2006
Hi,I found this SQL in the news group to drop indexs in a table. I need ascript that will drop all indexes in all user tables of a givendatabase:DECLARE @indexName NVARCHAR(128)DECLARE @dropIndexSql NVARCHAR(4000)DECLARE tableIndexes CURSOR FORSELECT name FROM sysindexesWHERE id = OBJECT_ID(N'F_BI_Registration_Tracking_Summary')AND indid 0AND indid < 255AND INDEXPROPERTY(id, name, 'IsStatistics') = 0OPEN tableIndexesFETCH NEXT FROM tableIndexes INTO @indexNameWHILE @@fetch_status = 0BEGINSET @dropIndexSql = N' DROP INDEXF_BI_Registration_Tracking_Summary.' + @indexNameEXEC sp_executesql @dropIndexSqlFETCH NEXT FROM tableIndexes INTO @indexNameENDCLOSE tableIndexesDEALLOCATE tableIndexesTIARob
View 2 Replies
View Related
Sep 20, 2006
Hi,
This is on Sybase but I'm guessing that the same situation would happen on SQL Server. (Please confirm if you know).
I'm looking at these new databases and I'm seeing code similar to this all over the place:
if not exists (select 1 from dbo.t1 where f1 = @p1)
begin
select @errno = @errno | 1
end
There's a unique clustered in dex on t1.f1.
The execution plan shows this for this statement:
FROM TABLE
dbo.t1
EXISTS TABLE : nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
It's not using my index!!!!!
It seems to be the case with EXISTS statements. Can anybody confirm?
I also hinted to use the index but it still didn't use it.
If the existence check really doesn't use the index, what's a good code alternative to this check?
I did this and it's working great but I wonder if there's a better alternative. I don't really like doing the SET ROWCOUNT 1 and then SET ROWCOUNT 0 thing. SELECT TOP 1 won't work on Sybase, :-(.
SET ROWCOUNT 1
SELECT @cnt = (SELECT 1 FROM dbo.t1 (index ix01)
WHERE f1 = @p1
)
SET ROWCOUNT 0
Appreciate your help.
View 3 Replies
View Related
Jan 3, 2007
Dear all,
I am deploying programatically an Excel 2007 file to a SQL Server 2005 Reporting Server. The problem is that if a file with the same name already exists, that file isn't replaced. I would like the opposite to happen. I'm using the following code:
--Executable
set svr=http://w3sdwsqld1/reportserver
set src_fld="\w3sdwsqld1\deploy\SAD\ECRANS\UPDATES_20061127_190000\Ecrans\AM\Associados\"
set dest_fld="Associados"
set script="\w3sdwsqld1\deploy\SADECRANS\UPDATES_20061127_190000\Ecrans\AM\Associados\PublishReports.rss"
REM Sample: deploy.bat http://w3sdwsqld1/reportserver "\w3sdwsqld1\deploy\SAD\ECRANS\UPDATES_20061127_190000\Ecrans\AM\Associados\" "Associados" "\w3sdwsqld1\deploy\SADECRANS\UPDATES_20061127_190000\Ecrans\AM\Associados\PublishReports.rss"
for /R %src_fld% %%f in (*.xlsx) do rs -i %script% -s %svr% -v ParentFolder=%dest_fld% -v reportP="%%~nf" -v path=%src_fld%
PAUSE
--rss Code
'
' Script Variables
'
' Variables that are passed on the command line with the -v switch:
'
' (a) parentFolder - corresponds to the folder that the script creates and uses
' to contain your published reports
' (b) reportP - corresponds to the report to publish
Dim ROOT As String = "/SAD/Ecrans/Ecrans/AM"
Dim definition As [Byte]() = Nothing
Dim warnings As Warning() = Nothing
Dim parentPath As String = ROOT + "/"+ parentFolder
Dim filePath As String = path
Dim report As String = reportP
Public Sub Main()
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
'Create the parent folder
Try
rs.CreateFolder(parentFolder, ROOT,Nothing)
Console.WriteLine("Parent folder {0} created successfully", parentFolder)
Catch e As Exception
Console.WriteLine(e.Message)
End Try
'Create shared data source
'CreateSampleDataSource("Solucao_Integrada", "OLEDB-MD", "Data Source=dwareas1;Initial Catalog=SAD_Solucao_Integrada")
'Publish the sample reports
PublishReport(report)
End Sub
Public Sub CreateSampleDataSource(name As String, extension As String, connectionString As String)
'Define the data source definition.
Dim definition As New DataSourceDefinition()
definition.CredentialRetrieval = CredentialRetrievalEnum.Integrated
definition.ConnectString = connectionString
definition.Enabled = True
definition.EnabledSpecified = True
definition.Extension = extension
definition.ImpersonateUser = False
definition.ImpersonateUserSpecified = True
'Use the default prompt string.
definition.Prompt = Nothing
definition.WindowsCredentials = False
Try
rs.CreateDataSource(name, parentPath, False, definition, Nothing)
Console.WriteLine("Data source {0} created successfully", name)
Catch e As Exception
Console.WriteLine(e.Message)
End Try
End Sub
Public Sub PublishReport(ByVal reportName As String)
Try
Dim stream As FileStream = File.OpenRead(filePath + reportName + ".xlsx")
Console.WriteLine(reportName)
definition = New [Byte](stream.Length) {}
stream.Read(definition, 0, CInt(stream.Length))
stream.Close()
Catch e As IOException
Console.WriteLine(e.Message)
End Try
Try
rs.CreateResource(reportName + ".xlsx", parentPath, True, definition, "application/x-excel", Nothing)
Catch e As Exception
Console.WriteLine(e.Message)
Console.WriteLine("Failed to publish report")
End Try
End Sub
--------------------------------------------------------------------------------------------------------------------
Any thoughts? Many thanks,
Pedro Martins
Portugal
View 3 Replies
View Related
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
Aug 18, 2014
Executed as user: S233683-AD01S233683NJ3SQL05. ...at file already exists.' [SQLSTATE 42000] (Error 22048) ERROR -- Could not backup database: master - BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 50000) xp_create_subdir() returned error 183, 'Cannot create a file when that file already exists.'
View 2 Replies
View Related
Nov 21, 2006
For reasons that are not relevant (though I explain them below *), Iwant, for all my users whatever privelige level, an SP which createsand inserts into a temporary table and then another SP which reads anddrops the same temporary table.My users are not able to create dbo tables (eg dbo.tblTest), but arepermitted to create tables under their own user (eg MyUser.tblTest). Ihave found that I can achieve my aim by using code like this . . .SET @SQL = 'CREATE TABLE ' + @MyUserName + '.' + 'tblTest(tstIDDATETIME)'EXEC (@SQL)SET @SQL = 'INSERT INTO ' + @MyUserName + '.' + 'tblTest(tstID) VALUES(GETDATE())'EXEC (@SQL)This becomes exceptionally cumbersome for the complex INSERT & SELECTcode. I'm looking for a simpler way.Simplified down, I am looking for something like this . . .CREATE PROCEDURE dbo.TestInsert ASCREATE TABLE tblTest(tstID DATETIME)INSERT INTO tblTest(tstID) VALUES(GETDATE())GOCREATE PROCEDURE dbo.TestSelect ASSELECT * FROM tblTestDROP TABLE tblTestIn the above example, if the SPs are owned by dbo (as above), CREATETABLE & DROP TABLE use MyUser.tblTest while INSERT & SELECT usedbo.tblTest.If the SPs are owned by the user (eg MyUser.TestInsert), it workscorrectly (MyUser.tblTest is used throughout) but I would have to havea pair of SPs for each user.* I have MS Access ADP front end linked to a SQL Server database. Forreports with complex datasets, it times out. Therefore it suit mypurposes to create a temporary table first and then to open the reportbased on that temporary table.
View 6 Replies
View Related
Nov 17, 2005
I am going to use the BCP utility to export data from a table in a SQL Server 2000 database to .csv file, but I must determine if the file already exists. The BCP export code it going to be in a stored procedure and will be configured to run every two hours so I must have a way of checking if the file exists prior to executing the BCP instruction. If the file exists then no big deal just abort and two hours later it will try again, at some point the file will not exist and the export will export all the data since the last export - but normally this will happen every two hours; I just need to make sure that the "other peoples" utility has proccessed the file (and by definition deleted it).
It makes me no difference whether the best way to do this would be using a batch file that execute and returns true/false or a VBScript or if there really is a way to do it in TSQL. I just cannot seem to find any way.
View 1 Replies
View Related
Jun 18, 2008
I would like to look for .mdf i.e. databasename.mdf file and see if it exists. I would also like to do the same for other files such as databasename.ldf, etc.
For each one of these files I do know their directories but would like to know how to check for that file in those related directories.
Thanks
View 1 Replies
View Related
Mar 24, 2006
I know allot of folks are having this problem and I tried lots of things but nothing works. I understand the problem is coping the SQL Express on another server is the problem - I just not sure what to do?
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42
This is the last statement on the Stack Trace:
SqlException (0x80131904): An attempt to attach an auto-named database for file e:wwwdata81d0493fwwwApp_DataTestDatabase.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +735091
I checked my server forum and they said I had to name a database:
Example: Database=(unique name);
But this didn't work either.
I just tried a simple web project that has only one database and one table in SQL Express with one sqldatasource and one datagrid. It works fine on my pc but when I use the copy function in Visio Studio 2005 Pro - I can't run the site on the remote server: www.myjewelrydirect.com
I tried coping the database manually. I tried disconnecting the database before I copy it. Below is my connection statement:
<connectionStrings>
<add name="TestDB" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|TestDatabase.mdf;Integrated Security=True;User Instance=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
With all the comments in these forums - this must be a bug.
I have been working on this problem for over 2 weeks - HELP!
View 1 Replies
View Related
Mar 10, 2008
Hello World,
I'm new to SSIS and would like a little assistance getting started, if possible...
Here is what I want to do:
Check if file exist (C:DTS UpgradeFilexxx.txt) --->
Archive file (C:DTS UpgradeArchive) --->
Check if file has data (true or false)
AND/OR
If there are any good website that have good direction, let me know
Thanks in advance for your help!!!
View 5 Replies
View Related
Oct 7, 2003
Is there is a piece for code ot sample code that can let me check if a file exists? Has any one done this before?
Thanks
View 2 Replies
View Related
Mar 18, 2008
Greetings all.
How can I check for file exitence in a specified folder using TSQL?
I can think of a couple of ways to do this so any feedback would be appreciated.
Load the contents of a folder in to a temporay table and check the table for file name. I can do this with something like:
insert into myFiles
exec master..xp_cmdshell 'dir /B /OND filePath*.txt'
Or maybe use OLE Automation Sproc? But I am not sure which method I should use.
Please advise.
View 4 Replies
View Related
Apr 7, 2008
Hi, I'm importing 10 csv files with a different name daily. If the file does not exists in a directory (which I have in variable) I want to write an error message, if it does I want to proceed to the for each loop. How do I do this? Any suggestions?
View 6 Replies
View Related
Jul 25, 2006
Hi, can someone tell me how to check if a file exists. If it exists then i want to continue with the process and if it fails then send a mail to me.
Thanks in advane.
View 6 Replies
View Related
Aug 21, 2006
In SSIS, I need an easy way to see if a file exists, and if not wait for it until a timeout period expires. Here are the options I've discovered, along with the issues I've had:
a) The File Watcher task from www.sqlis.com
This was my first attempt. The task works great, BUT only detects when there is a change on the file. If the file already exists, it keeps waiting which is not the behavior I need.
b) The WMI Event Task
There is very sparce documentation on this event and how to write a WQL query. There are numerous examples of monitoring a folder and if any files appear, cause an event to happen. I need to detect for a specific file. I found maybe one example of this using "PartComponent" but wasn't able to get the sytax right to make it work for me. I also need to access a remote file share using a UNC path (e.g. \servernamepathfile.txt) which I could not get to work.
c) Script Task using the File.Exists() method
I imported the System.IO namespace, and used a File.Exists(\servernamepathfile.txt) with actual success, but am not sure of the best way to continue to wait if the file is not found immediately. I also want to modularize this approach so I can wait for several files simultaneously so was thinking of implementing this script task as a package by itself to accept variables (filepath & timeout period) but need to know if anyone has had success with this approach.
I'm open to suggestions or ways to get options a) and b) to work for my needs.
Thanks!
Kory
View 13 Replies
View Related
Jun 21, 2015
Previously same records exists in table having primary key and table having foreign key . we have faced 7 records were lost from primary key table but same record exists in foreign key table.
View 3 Replies
View Related
Feb 19, 2007
My goal is to monitor all failed login attempts to server. I have:
DECLARE @traceidnum INT, @on BIT, @file_path NVARCHAR(50), @maxsize bigint;
SET @on = 1; SET @maxsize =5; SET @file_path = 'c: race'; --Create trace EXEC sp_trace_create @traceid = @traceidnum OUTPUT, @options = 2, @tracefile = @file_path, @maxfilesize = @maxsize, @stoptime=null, @filecount =0; --trace stop EXEC sp_trace_setstatus @traceidnum, 0 --Set events EXEC sp_trace_setevent @traceidnum, 14, 1, @on --trace start EXEC sp_trace_setstatus @traceidnum, 1
this works fine, but the problem is when sql server restarts I loose trace but trace-file remains. When I want to create new trace with the same parameters I receive Error = 0x80070050(File Exists) .
Is there any way to reuse trace-file? Or to change trace's file path to indicate file already created?RegardsKonRi
View 4 Replies
View Related
Nov 9, 2007
I'm following these instructions: http://technet.microsoft.com/en-us/library/ms156421.aspx
I run this restore letting it create the database. It works fine:
RESTORE DATABASE ReportServer
FROM DISK='\MYMACHINEe$BackupsReportingServicesReportServerData.bak'
WITH NORECOVERY,
MOVE 'ReportServer' TO
'G:DataReportServer.mdf',
MOVE 'ReportServer_log' TO
'J:LogsReportServer_Log.ldf';
GO
I run this command and get this result set, with the fileIDs in red.
Restore filelistonly from disk = '\MYMACHINEe$BackupsReportingServicesReportServerLog.bak'
ReportServer2005
d:Program FilesMicrosoft SQL ServerMSSQLdataReportServer2005.mdf D
PRIMARY 3407872 35184372080640 1 0 0 9A65122E-36B7-4D93-A0FB-F1A894641A09 0 0 0 512 1 NULL 62000000029300037 D49504CB-7E10-4BA3-B737-F2F9F2070F39 0 1
ReportServer2005_log
d:Program FilesMicrosoft SQL ServerMSSQLdataReportServer2005_log.LDF L NULL 3211264 2199023255552 2 0 0 0D6FE3C7-C5CC-49FB-A66C-43272EB47763 0 0 0 512 0 NULL 0 00000000-0000-0000-0000-000000000000 0 1
then I run this LOG restore:
RESTORE LOG ReportServer
FROM DISK='\MYMACHINEe$BackupsReportingServicesReportServerLog.bak'
WITH NORECOVERY, FILE=2,
MOVE 'ReportServer2005' TO
'G:DataReportServer.mdf',
MOVE 'ReportServer2005_log' TO
'J:LogsReportServer_Log.ldf';
GO
Msg 4038, Level 16, State 1, Line 1
Cannot find file ID 2 on device '\MYMACHINEe$BackupsReportingServicesReportServerLog.bak'.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
I see file ID 2 identifying the log file. I've never done a restore like this - using copy_only and backing the database and log into separate .bak files, so maybe I'm misunderstanding something here.
Thanks for any help
Sam
View 2 Replies
View Related
Jun 23, 2006
I want execute my package when a set of files exists in a
directory. What is the best way of doing this?
I have been successful in creating a WMI Event Watcher Task
that executes when any file (the first file) is added to a directory. But
I can not figure out the WQL for a specific file or set files.
What have you done with in SSIS to trigger the package?
Thanks
View 8 Replies
View Related
Jul 14, 2015
I need to find out if a Transaction ID exists in Table A that does not exist in Table B. If that is the case, then I need to insert into Table B all of the Transaction IDs and Descriptions that are not already in. Seems to me this would involve If Not Exists and then an insert into Table B. This would work easily if there were only one row. What is the best way to handle it if there are a bunch of rows? Should there be some type of looping?
View 12 Replies
View Related
Dec 6, 2006
I need to create a file that removes (drops) all of your database objects.
View 12 Replies
View Related
Jan 16, 2001
How to drop transaction log file and create another one
or change logical name of transaction log file?
Thank for any help
View 1 Replies
View Related
Dec 11, 2014
I am not able to to drop few file groups that has been created to add partition range.
Steps taken so far:
--Empty files started
DBCC ShrinkFile(YEAR2015_FG,EmptyFile);
GO
--Removing files
Alter DATABASE ETL_MART REMOVE FILEGROUP YEAR2015_FG;
GO
-- Remove parition scheme depednecy
[code]...
View 2 Replies
View Related
