Checking If File Exists Or Not
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
ADVERTISEMENT
Jun 18, 2008
I know there is a way to determine if a file exists using T-SQL, but I can't seem to find a way to determine if a directory exists. I need to be able to determine this so I can delete the directory if it already exists before I run other queries.
View 17 Replies
View Related
Feb 14, 2007
I can't seem to get this work. I'm using SQL2005
I want to check if a record exists before entering it. I just can't figure out how to check it before hand.
Thanks in advance. Protected Sub BTNCreateProdIDandName_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles BTNCreateProdIDandName.Click
' Define data objects
Dim conn As SqlConnection
Dim comm As SqlCommand
' Reads the connection string from Web.config
Dim connectionString As String = ConfigurationManager.ConnectionStrings("HbAdminMaintenance").ConnectionString
' Initialize connection
conn = New SqlConnection(connectionString)
' Check to see if the record exists
If
comm = New SqlCommand("EXISTS (SELECT (BuilderID, OptionNO FROM optionlist WHERE (BuilderID = @BuilderID) AND (OptionNO = @OptionNO)", conn)
Then
'if the record is exists display this message.
LBerror.Text = "This item already exists in your Option List."
Else
'If the record does not exist, add it. FYI - This part works fine by itself.
comm = New SqlCommand("INSERT INTO [OptionList] ([BuilderID], [OptionNO], [OptionName]) VALUES (@BuilderID, @OptionNO, @OptionName)", conn)
comm.Parameters.Add("@BuilderID", System.Data.SqlDbType.Int)
comm.Parameters("@BuilderID").Value = LBBuilderID.Text
comm.Parameters.Add("@OptionNO", System.Data.SqlDbType.NVarChar)
comm.Parameters("@OptionNO").Value = DDLProdID.SelectedItem.Value
comm.Parameters.Add("@OptionName", System.Data.SqlDbType.NVarChar)
comm.Parameters("@OptionName").Value = DDLProdname.SelectedItem.Value
LBerror.Text = DDLProdname.SelectedItem.Value & " was added to your Option List."
Try
'open connection
conn.Open()
'execute
comm.ExecuteNonQuery()
Catch
'Display error message
LBerror.Text = "There was an error adding this Option. Please try again."
Finally
'close connection
conn.Close()
End Try
End If
End Sub
View 8 Replies
View Related
Apr 22, 2006
I would like to be able to check if a certain entry exists in a SQL table. Ideally, the output would be a boolean so I can use it in an IF statement which would tell it what to do depending on whether or not the entry exists. Thanks for anyone that helps. :)
View 6 Replies
View Related
Sep 19, 2007
I've got two tables, one containing a list of company names(approx 10,000 records), the other containing a list of company employees (approx 30,000 records) joined by the CompanyID column.
I have a third table (approx 700 records) containing new employees to be added to the employee table. Each record in this table has the employees details plus the name of their company.
I want to write a query that will check each row in the third table to see if
a) the employee exists in the Employees table
b) the company exists in the Companies table and
c) the employee is listed under the correct company
The query should also handle any combination of the above. So if the company doesn't exist but the employee does, create the company on the companies table and update the appropriate record on the employees table with the new CompanyID etc. etc.
Oh, forgot to mention. The company names in the third table won't be exactly the same as the ones in the Company table so will need to use CharIndex.
Anybody got any ideas?
View 4 Replies
View Related
Jan 10, 2014
I'm trying to check which price grids are in use using the price grid_id, and seeing whether this grid_id exists in another query that checks all active contracts. If the grid_id is present (active) I want to return 'Yes', if not I want it to return 'No'.
There are 385 price grids, but my query is only returning the 315 that are active, and ignoring any that are not used. My code is below, how I can see all the records whether Yes or No:
Select distinct
pg.grid_id [Price Grid],
pg.grid_name [Grid Name],
case when exists
(Select
c.grid_id from
customers c
inner join deltickhdr dh on dh.acct = c.custnum and dh.stage <5
where
c.type = 'C' and
dh.dticket is not null) then 'Yes' else 'No' end [Active]
From gridhdr pg inner join customers c on c.grid_id = pg.grid_id
Order by pg.grid_id
View 4 Replies
View Related
Apr 1, 2007
I am getting an error between the IF THEN Else statement. The copy works after the THEN! I need some help!
Ernie
I have two tables with script in a view. 1. newusers 2. blacklist I want to compare that "PresentEmail, RemoteComputer,Email" thats in newuser table is not in blacklists, and if this is true, then copy from newuser (the record not on blacklist) to blacklist using a where statement for second condition.
IF
(NOT EXISTS
(SELECT *
FROM newusers AS d
WHERE ISNULL(PresentEmail, 'NULL') IS BadEmail))
THEN
Insert into blacklist (BadNewEmail, BadIpAddress, BadEmail)
Select NewEmail, RemoteComputerName, PresentEmail
From newusers
Where Account_Active = 'Spammer'
Else
GO
View 7 Replies
View Related
Apr 30, 2008
I am checking to see if the source record is available in the target table using a lookup transformation and if not found i have to insert this record.
I have connected the error flow of the lookup transformation to the target. I am acheiving expected results, but is this the best practise? I have not connected to the green arrow to any task.
View 6 Replies
View Related
Oct 27, 2005
I'm trying to put scripts to create our stored procedures under version control.
However I don't want these scripts to be run if the stored procedure already exists (we'll be using update scripts to alter existing stored procedure I think).
Anyway I tried :
Code:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/****** Object: Stored Procedure dbo.getEnglandHotelsByATOPResort Script Date: 26/10/2005 10:40:01 ******/
if NOT EXISTS (SELECT object_id('procedureName','p'))
CREATE PROCEDURE [dbo].[procedureName]
@location char(2)
AS
...Procedure...
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
I know that the if NOT EXISTS ... part works as doing the following returns the expected result :
Code:
IF NOT EXISTS (SELECT object_id('procedureName','p'))
SELECT 'FALSE'
ELSE
SELECT 'TRUE'
However when trying to do this before the CREATE PROCEDURE I get :
Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'PROCEDURE'.
Server: Msg 137, Level 15, State 1, Line 12
Must declare the variable '@location'.
Any ideas how to do this?
-D
View 3 Replies
View Related
Jan 6, 2015
I have a table called as Events and below are its columns
ID int
EventFromDate datetime
EventToDate datetime
EventDesc nvarchar
IsHoliday bit
This is a master table where the admin would enter the Events/Holidays for the entire year.
The data would be as below:
IDEventFromdateEventTodateEventDesc isHoliday
126-01-201526-01-2015RepublicdayYes
230-01-201531-01-2015TeamOutingNo
301-05-201501-05-2015Labour day Yes
Now, suppose a employee applies leave on 26/01/2015 to 26/01/2015 then it should not insert into table and return a value "Not updated"
How to handle the scenario if a employee applies leave between the range 23/01/2015 to 27/01/2015, since 26/01/2015 is a holiday in between. how the data can still be inserted excluding 26/01/2015
Can we exclude a non-working day or a sunday.
Leavedetails table to insert leaves applied by employee is as follows
LeaveDetailID int
LeaveTypeId int
FromDate datetime
EndDate datetime
Remarks nvarchar
View 0 Replies
View Related
Feb 9, 2007
I've decided to post this as a sticky given the frequency this question is asked.
For those of you wishing to build a package that determines if a source row exists in the destination and if so update it else insert it, this link is for you.
http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx
Thanks Jamie!
If you want to do a similar concept to Jamie's blog post above, but with the Konesan's Checksum Transformation to quickly compare MANY fields, you can visit here:
http://www.ssistalk.com/2007/03/09/ssis-using-a-checksum-to-determine-if-a-row-has-changed/
Phil
View 60 Replies
View Related
Aug 21, 2007
I have 1+ CSV files (using a foreach loop) which I'm doing a lot of transform work on and then inserting into a SQL database table.
Each CSV file usually contains about 2 days worth of data (contains date stamps) - somewhere in the region of 60k records per day.
The destination table currently contains 3 million+ rows and will get bigger.
I need to make sure that before inserting into the destination table, the data doesn't already exist.
I've read the following article: http://www.sqlis.com/311.aspx
While the lookup method works, it takes ages and eats up memory as it caches the 3m+ records before running for each CSV. Obviously this will only get worse as the table grows in size.
To make things a little more efficient what I'd like to do, is first derive the dates I'm dealing with in the current file - essentially storing the max(date) and min(date) in variables. Then in the lookup SQL use those vars, to reduce the amount of data that needs to be brought into the transformation to check against before inserting into the destination table.
Lookup SQL eg. SELECT * FROM MyTable WHERE Date BETWEEN varMinDate AND varMaxDate.
Ideally I'd use an aggregate transformation and then use the subsequent output from that either in the lookup query or store the output in vars, but I don't think you can do that and I get the feeling I'm approaching this with the wrong mindset.
Any thoughts would be great!
View 6 Replies
View Related
Aug 3, 2007
Thanks for your time:
Background: After Insert Trigger runs a sproc that inserts values into another table IF items on the form were
populated. THose all work great, but last scenario wont work: Creating a row insert based on Checking that all 22 other items from the prior insert (values of i.columns) were NULL:
IF EXISTS(select DISTINCT i.notes, i.M_Prior, i.M_Worksheet, ...
from inserted i
WHERE i.notes IS NOT NULL AND i.M_Prior = NULL AND i.M_Worksheet = NULL AND...)
BEGIN
Insert into dbo.Items2Fix ...
From inserted i
END
View 1 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
Jul 23, 2005
Hi All,I have several questions about checking backup file :a. When SQL Server receive a command :BACKUP DATABASE test to DISK = '\pc1etdbBTEST',does it verify BTEST file before ending the job ?b. Is there any special command for checking / verifyinga backup file ?c. If I send a restore command using a file infectedby a virus, what action will SQL Server take ?Thanks in advanceJohn S*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies
View Related
Oct 29, 2007
Hello guys,
Using SSIS, I want to check if a specific file exists on an FTP server or not. If yes, then i'll go with a flow, and if not, i'll go with another flow. Any help with that?
Thanks in advance
SHIKO
View 1 Replies
View Related
Aug 20, 2007
hi frineds i was checked the file exist in a dir or not it's working file
if file not exist i want to create a file and directory with the bellow condition
i was placed the file system object for dir and file it working fine indepenedent but now
we have to club with file check condition and if not exist we have to create how please tell me
If System.IO.File.Exists((CStr(Dts.Variables("ProductBuyListFileLocation").Value) + (CStr(Dts.Variables("ProductBuyListFileName").Value)))) ThenDts.Variables("FileFound").Value = 1
'MsgBox("FILE FOUND")
ElseDts.Variables("FileFound").Value = 0
'MsgBox("FILE NOT FOUND")
End If
regards
koti
View 1 Replies
View Related
Mar 5, 2015
I need to be able to see if the incoming csv file had a head row different than the previous files header row. That will tell me that I have new columns.
View 3 Replies
View Related
Aug 29, 2007
OK. Here's my situation. I check for the existence of a dummy .txt file using a script. I send an e-mail if it does not exist and exit package. The .txt file only exists if another .xls file is present which I import. However, during the validation phase of the package, the package fails because the .xls file does not exist. Is there a way to bypass the validation step? The only solution I came up with is to have a two-step job. The first runs the file check step and sends the e-mail. The second attemps to run the package and fails. Not a very graceful exit.
View 3 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
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 3, 2015
I wrote the below script to print all folders and files located in the share path. How to extend my script to mention by adding another column whether the file is a folder/file , sort of 0 or 1.
declare @chkdirectory1 varchar(4000) = 'shared_pathfolder';
declare @finalserver3 varchar(4000);
create table #tmp (directory_name varchar(4000))
SET @finalserver3 = '''"DIR ' + @chkdirectory1 + ' /B"''';
--select @finalserver3
--SELECT @finalServer
DECLARE @ExecCmd varchar(100)
--SELECT @ExecCmd = 'EXEC master.dbo.xp_cmdshell ' + char(50) + 'mkdir D:'+ CONVERT(varchar(8), getdate(), 112) + '' + char(50)
SET @ExecCmd = 'EXEC master.dbo.xp_cmdshell ' + @finalserver3
--SELECT @ExecCmd
exec(@ExecCmd)
drop table #tmp
View 0 Replies
View Related
May 29, 2015
How to design ta SSIS package which loops through DESTINATION folder files and checks whether that file is there in the SOURCE or not.
If the file exists then I have to check the modified date on DESTINATION file if it is greater than 1 day delete that file. If the modified date is less than that SOURCE file then I have to copy that
file to DESTINATION<o:p></o:p>
<o:p></o:p>
If there are files which exists in SOURCE and not in DESTINATION, then how shall we copy all the files to the DESTINATION that are created on the day of execution of package.<o:p></o:p>
View 2 Replies
View Related
Nov 5, 2005
Hello,Using Enterprise Manager, I deleted from my database the only tablethat contained records (Right-Click on Table, Choose "Delete Table").My expectation was that the LARGE .mdf file would be reduced to minimalsize (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 deletedthe table in order to recapture hard drive space.NOTE: I already tried using Shrink Database in EM. This significantlyreduced 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
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
