Question ... How To Test/verify Existence Of Local File Or Folder Using T-SQL (2005)?
Jan 25, 2008
Two questions actually ...
1) Need a simple routine or system function for testing for or verifying the existence of a file or folder on the local server's file system. Returning a simple boolean value or 1 or 0 would be fine.
2) Need a syntax and use description of the new "master.dbo.xp_create_subdir" function ... anyone have some documentation or links? MS technet and MSDN have nothing.
Thanx
EHammer
View 18 Replies
ADVERTISEMENT
Oct 26, 2007
I have a data flow task inside a foreach loop container which will take multiple excel files and load into a sql table. Once all the excel files are loaded into the table, then at the end one of the column gets updated using execute sql task. Now I am trying to check for a file existence, if the file is not present in the folder then the data flow task should not be executed. Any help is greatly appreciated, I am thinking of using file system task for this, but not exactly sure. Thanks in advance.
View 13 Replies
View Related
Sep 15, 2007
When attempting to backup a database using Management Studio, I receive a ""Cannot verify the esistence of the backup file location. Do you want to use the backup file location anyway?" yes/no messsage afer entering a file directory which exists. When entering a new directory, when I select "yes," a file locaiton will not be created, however, the backup operation continues to success. Originally, I had configured the sql services to execute under a user account with domain admin and administrative privledges, and I vverified the folder's security config permited write access. I reinstalled and now al SQL services are executing as "Local System" (or "Network Service") with same problem.
Any recommendations as to how to correct what I am doing wrong? (Thanks).
Jamie
__________________________
SQL Server Enterprise 2005 running w/all patches on Windows Server 2003 Server.
View 3 Replies
View Related
Jun 15, 2008
Hi,
I am trying to test a replication or at least get the feel for it on my local copy before I set it up on the real server.
Can I do that by setting my local database as the publisher as well as the subscriber?
I am getting an error message but I am wondering if there are any settings I can change to make this work. Or any other ideas of how to see the replication tool before actually doing it live?
Error message:
"Server 'MyComputerName' is neither a Publisher nor a Distributor, or you do not have permission to access replication functionality on this server.
I might add that I am not only new to the project but also new to MS SQL Server.
Thank you!
View 1 Replies
View Related
Feb 10, 2004
Hi all,
I'm trying to be slick, but so far am just all wet *snicker*
I've got a table with rows that may or may not exist, and am trying to retrieve a column if an associate row DOES exist.
For argument's sake, my PIndex table looks like:
PID int
CreateDate smalldatetime
CloseID float
Here is my select logic that I thought would save an extra select to load my local variable if the row is actually in the table...
DECLARE @CloseID float
IF NOT EXISTS (SELECT @CloseID = CloseID
FROM PIndex
WHERE ((PID = '14') and
(CreateDate = '2004-02-06')))
SET @CloseID = 100
The SET afterwards is just to initialize the variable if it can't be had from an existing row in the table.
The trouble is, that it fails to compile with the following error:
>>>>> Line 3: Incorrect syntax near '='.
Any insights? My goal is to use a single select to load the value into my local variable if the associated row exists, or to set my local variable to 100 if it doesn't.
Thanks!
Paul
View 5 Replies
View Related
Jul 23, 2005
I am in a situation where I need to get a copy of test database that ison production server running MSSQL 2000 Standard to my local machinerunning MSSQL 2000 personel. I tried to use the copy wizard where itappears I get connected to the source server OK but when I try toindicate the destination server which is my local machine I get errorspoping up about cannot connect to (local) etc.I am NOT a DBA just a programmer trying to get a local test environmentup to be more productive.Lsumnler
View 2 Replies
View Related
Mar 6, 2014
Is there any way sa user can access folder located in local system.
Server Info:
SQL Server 2008 r2
OS: Windows Server 2008
View 1 Replies
View Related
Apr 20, 2007
Hi,
i'm doing POS system by using C# in visual studio 2005. Recently, i installed the microsoft sql server management studio on my pc, i plan to do merge replication through local publications between my pocket pc and my pc. So, i opened the replication file which is under Object Explorer in microsoft sql server management studio, it ONLY show me the local subscription folder under the replication file.Do i install incomplete set of microsoft sql server management studio?Local publications and local subscription, which one is more suitable for POS system?
View 11 Replies
View Related
Mar 28, 2008
I am having problem with moving a file from one folder to another folder. Here is the detailed scenario:
I want to move a input.csv file from shared input folder to shared archive folder. i am using the below code to do this.
declare @inpath varchar(100)
SET @inpath = 'move "\abcdefINPUTinput*.csv" "\abcdefARCHIVEarchive.csv"'
EXEC @filestatus = master..xp_cmdshell @inpath
but the problem was it was cutting the input.csv file from INPUT folder but not pasting it in the ARCHIVE folder.
I really appreciate if anyone can help me to solve this or anyone can tell some workarounds.
Thanks,
View 20 Replies
View Related
Sep 3, 2007
Hi,
Does TSQL provide methods to check if a file exists?
For example, a TSQL script will read data in a .dbf file into SQL Server. It will check if the file exists before read. How to do this check?
Thank you.
View 1 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
Apr 29, 2008
I am trying to trigger SSIS package if a file is present in a folder, can I use any of the SSIS tasks in doing that??
Thanks!!
View 13 Replies
View Related
May 13, 2008
I am not sure if we can execute SSIS package using t-sql, but I want to do the following:
Step1. Execute SSIS package1
Step2. Check for the file1.txt in c drive using the below code:
Declare @result int
exec xp_fileexist 'c:file1.txt', @result output
IF (@result = 1) --if file exists exec ssis package
begin
Execute SSIS package2
end
If we can't use t-sql, please let me know how I can do that as part of sql job. Thanks!!
View 1 Replies
View Related
Apr 29, 2008
I am trying to trigger SSIS package if a file is present in a folder, can I use any of the SSIS tasks in doing that??
Thanks!!
View 1 Replies
View Related
Mar 16, 2008
Hello All
Just wondered if someone could help me with a bit of T-SQL, i have a application in ASP.NET/VB that allows the user to update a message board by clicking a button "update" this in turn triggers my Stored Procedure for inserting this data into a table, which works great.
It inserts the data into its respective fields and also takes The Title, Line 1, Line 2 and so on and creates a XML file (Using FOR XML) which is stored in the same Table under a column call XML_Data. Which again works great.
My problem now is how do i output this XML_Data to an actual XML file that is on my local machine, i.e. It be created in say C:Inetpubwwwrootxmlfiles("xml file name inserted here from another column that holds xml file name").xml
Any help on this would be greatfully apreciated
Thanks In Advance
Neil
View 4 Replies
View Related
Aug 12, 2015
I have to access the remote system folder files in local machine using SSIS.
View 2 Replies
View Related
Jul 12, 2006
Hi,
I tried to backup the master key by the following syntax :
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'mypassword'
BACKUP MASTER KEY TO FILE = 'c: empmaster' ENCRYPTION BY PASSWORD = 'mypassword'
but it failed and i got the following message:
Cannot write into file 'c: empmaster'. Verify that you have write permissions, that the file path is valid, and that the file does not already exist.
NB: I am using the "sa" user to execute this command.
I know that we have a security permission issue , but where and how ?
Regards,
Tarek Ghazali
SQL Server MVP
View 12 Replies
View Related
Feb 8, 2007
Is there a way to verify the version of the files that are used to connect to SQL 2005?? This stems from an ongoing problem I am having with having SQL 2000 and 2005 on the same machine... It has come done to the client connectivity files....
Thanks
View 4 Replies
View Related
Mar 22, 2007
Hello,
I would like a way to check a file before attempting to restore it. I want to be able to tell that this is a valid SQL backup file and not just some random file before I attempt a restore.
Any help would be appreciated!
hassiah
View 5 Replies
View Related
Mar 22, 2007
Hello,
I have some C# (.NET 2.0) code that performs back-ups and also can perform restores of back-up files. I would like to validate the integrity of a file prior to initiating a restore operation. In other words, I want to know, before I try to restore, if the file is just a random file (picture, work doc, zipped backup, etc.) or if it is a valid SQL backup file.
Any help would be greatly appreciated.
Thanks,
hassiah
View 3 Replies
View Related
May 10, 2006
Hi,I need to maintain both SQL Server 2000 and 2005 on my PC for supportpurposes. I am using the developer additions. After I installeverything (with no install errors) I can use SQL 2005 Mgt Studio fineuntil I bring up SQL 2000 Query Analyzer or Enterprise Mgr. After that,I cannot bring up SQL 2005 Mgt Studio. It get this message:Package 'Microsoft SQL Management Studio package' failed to loadI click OK, the message comes up again, I click OK again and then MgtStudio disappears.My default instance is under SQL 2000 and I created a named instanceunder SQL 2005 on my PC both with Local System Account.My order of installs was:1) SQL Server 2000 (Server tools)2) SQL Server 2000 SP43) SQL Server 2005 (DB Engine, Integration Services, and Workstationcomponents)I also have .NET 2005 Professional installed.The frustrating thing is I can find no log information anywhere --event log, under my Documents and Settings, or in the SQL Server 2005directories where it was installed.My guess is that when I used Query Analyzer it changed a settingsomewhere or prevented me from using Mgt Studio somehow, but I justcan't figure out what it did. I'd like to avoid a complete re-installwhere it may just happen again.Any help would be appreciated. Thanks much.Tom Vicker
View 2 Replies
View Related
Jan 17, 2007
How can I verify if the sp1 is installed on my sql server 2005 server ?
Cosimo
View 1 Replies
View Related
Sep 21, 2006
Hi,
How can I verify whether the particular SQL Server 2005 installtion is 32-bit SQL Server 2005 or 64-bit SQL server 2005?
Thanks,
Ritesh
View 1 Replies
View Related
Feb 2, 2008
Hi,
I inherited some SQL 2005 server boxes with NO CDs to be found...Is there any easy way to verfiy if these SQL boxes were trial edtions or full licensed production version...?
thx in advance
magellan
View 3 Replies
View Related
Apr 24, 2008
In the first step of my SSIS package I need to get files from FTP and dump it/them in a local directory, but it's more than that, the logic is like this:
1. If no file(s) found, stop executing and send email saying no file(s) found;
2. If file(s) found, then compare it/them with existing files in our archive folder; if file(s) already exist in archive folder, stop executing and send email saying file(s) already existed, if file(s) not in archive folder yet, then transfer it/them to the local directory for processing.
I know i have to use a script task to do this and i did some research and found examples for each of the above 2 steps and not both combined, so that's why I need some help here to get the logic incorporated right.
Thanks for the help in advance and i apologize for the long lines of code!
example for step 1:
----------------------------------------------------------------------------------------------------------
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports Microsoft.VisualBasic.FileIO.FileSystem
Imports System.IO.FileSystemInfo
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
Dim cDataFileName As String
Dim cFileType As String
Dim cFileFlgVar As String
WriteVariable("SCFileFlg", False)
WriteVariable("OOFileFlg", False)
WriteVariable("INFileFlg", False)
WriteVariable("IAFileFlg", False)
WriteVariable("RCFileFlg", False)
cDataFileName = ReadVariable("DataFileName").ToString
cFileType = Left(Right(cDataFileName, 4), 2)
cFileFlgVar = cFileType.ToUpper + "FileFlg"
WriteVariable(cFileFlgVar, True)
Dts.TaskResult = Dts.Results.Success
End Sub
Private Sub WriteVariable(ByVal varName As String, ByVal varValue As Object)
Try
Dim vars As Variables
Dts.VariableDispenser.LockForWrite(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
vars(varName).Value = varValue
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
End Sub
Private Function ReadVariable(ByVal varName As String) As Object
Dim result As Object
Try
Dim vars As Variables
Dts.VariableDispenser.LockForRead(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
result = vars(varName).Value
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
Return result
End Function
End Class
example for step 2:
-------------------------------------------------------------------------------------------------------
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
Try
'Create the connection to the ftp server
Dim cm As ConnectionManager = Dts.Connections.Add("FTP")
'Set the properties like username & password
cm.Properties("ServerName").SetValue(cm, "ftp.name.com")
cm.Properties("ServerUserName").SetValue(cm, "username")
cm.Properties("ServerPassword").SetValue(cm, "password")
cm.Properties("ServerPort").SetValue(cm, "21")
cm.Properties("Timeout").SetValue(cm, "0") 'The 0 setting will make it not timeout
cm.Properties("ChunkSize").SetValue(cm, "1000") '1000 kb
cm.Properties("Retries").SetValue(cm, "1")
'create the FTP object that sends the files and pass it the connection created above.
Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing))
'Connects to the ftp server
ftp.Connect()
'ftp.SetWorkingDirectory("..")
ftp.SetWorkingDirectory("directoryname")
Dim folderNames() As String
Dim fileNames() As String
ftp.GetListing(folderNames, fileNames)
Dim maxname As String = ""
For Each filename As String In fileNames
' whatever operation you need to do to find the correct file...
Next
Dim files(0) As String
files(0) = maxname
ftp.ReceiveFiles(files, "C: emp", True, True)
' Close the ftp connection
ftp.Close()
'Set the filename you retreive for use in data flow
Dts.Variables.Item("FILENAME").Value = maxname
Catch ex As Exception
Dts.TaskResult = Dts.Results.Failure
End Try
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
View 16 Replies
View Related
Jun 27, 2006
I upgraded a MS SQL 2005 Enterprise Evaluation/Trial to MS SQL 2005 Standard Edition with 5 User CAL.
I used the following command to upgrade:
start /wait setup.exe ADDLOCAL=SQL_Engine INSTANCENAME=MSSQLSERVER UPGRADE=SQL_Engine SKUUPGRADE=1 PIDKEY=<mykey> /qb
Once complete, "select SERVERPROPERTY('LicenseType')" returns DISABLED, but I expected that it would return PER_SEAT.
Thankfully, after the upgrade it does show EDITION=Standard Edition.
Questions:
1. How do I verify the license installed is the Client Access License of 5?
-- SQL Server 2005 does not seem to have the same SQL Licence control panel as did SQL 2000. It is not listed under Control Panel -> Licensing (only thing listed is 'Windows Server') nor under Administrator Tools -> Licensing.
2. How do you add CALs or change license type in SQL 2005?
-- I was also puzzled that I was not asked during setup of the license mode and/or number of licenses ( I did not get this on a fresh install of SQL 2005 SE either ).
Thanks for any assistance you can provide.
Ajay
View 3 Replies
View Related
May 9, 2008
In my script task I have the following code. The task I'm trying to accomplish is:
If the filename on FTP can be found in the local archive folder of e: drive then show message "FileAlreadyThere" (I will ultimatley change it to do nothing); if the filename on FTP cannot be found in the local archive folder of e: drive then transfer the file to the local package folder on d: drive.
While the script task is executing I was watching it closely, but the problem i saw is that:
If some files on FTP are already in local archive folder and some are not, then it the files which are already in the archive folder are dumped to the package folder; then after that the files which are not in the archive folder are then dumped to the package folder. But I only want the new files on FTP to be transferred to the package folder for further processing.
Then after this is finished, I saw all the files in the package folder are refreshed one after another, after the first round of refresh the second round starts, after the second round finishes it then stopped. I saw it refreshes itself because the 'Date Modified' of the file changes. And I saw the script task turned green.
I don't see how the code below produced this result. Something is wrong in the logic of the loop? Anyone has any idea why it's behaving the way it is now? And how to change the code to accomplish what I want? Thanks a lot!!
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Imports System
Imports System.IO
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Public Sub Main()
Dim cm As ConnectionManager = Dts.Connections.Add("FTP")
cm.Properties("ServerName").SetValue(cm, "ftp2.name.com")
cm.Properties("ServerUserName").SetValue(cm, "username")
cm.Properties("ServerPassword").SetValue(cm, "password")
cm.Properties("ServerPort").SetValue(cm, "21")
cm.Properties("Timeout").SetValue(cm, "0")
cm.Properties("ChunkSize").SetValue(cm, "1000") '1000 kb
cm.Properties("Retries").SetValue(cm, "1")
Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing))
ftp.Connect()
ftp.SetWorkingDirectory("/directory")
Dim fileNames() As String
Dim folderNames() As String
ftp.GetListing(folderNames, fileNames)
If fileNames Is Nothing Then
MsgBox("NoFileOnFTP")
Else
Dim fileName As String
For Each fileName In fileNames
If File.Exists("c: emp" + fileName) Then
MsgBox("FileAlreadyThere")
Else
ftp.ReceiveFiles(fileNames, "c: emp", True, True)
End If
Next
End If
ftp.Close()
End Sub
End Class
View 3 Replies
View Related
Aug 4, 2015
I am looking for a Sql query to verify the inserted values from one table(in CSV file) with another table(in sql database)
For example: I have below Values column that is present in once CSV file, after my data migration the values get stored in Results table under Message column.
I need to very whether values(1X,1Y) are inserted in Message record "successfully inserted value 1X"
Values (CSV)
1X
1Y
Results Table(SQL)
CreatedDate Message
2015-08-04 08:45:29.203 successfully inserted value 1X
2015-08-04 08:44:29.103 TEst pass
2015-08-04 08:43:29.103 successfully inserted value 1X
2015-08-04 08:42:29.203 test point
2015-08-04 08:35:29.203 successfully inserted value 1Y
2015-08-04 08:30:29.203 Test Pass
2015-08-04 08:28:29.203 successfully inserted value 1Y
If all values are inserted:
Output:
All values from values table are inserted successfully
Total count of values inserted: 2
If only few values are inserted, example only 1X from Values table is inserted in Message
Example:
Results Table CreatedDate Message
2015-08-04 08:45:29.203 successfully inserted value 1X
2015-08-04 08:44:29.103 TEst pass
2015-08-04 08:43:29.103 successfully inserted value 1X
2015-08-04 08:42:29.203 test point
Output:
All values from values are not inserted successfully in result table.
Total count of values inserted: 1
Missing Values not inserted in results table are: 1Y
View 3 Replies
View Related
Jul 20, 2005
A test database that we used in one of our implementation pilots wasabandoned around 4 months back. The database when abandoned had a logfile size of less than 500MB. The log file has been steadily bloating(just came to my attention) and has reached 8.5GB. The database hasnot been used since abandoned 4 months back at 500MB. Considering thefact that our live DB has a combined (data + log) file size of lessthan 2GB I do not want to delete the database before exploring thecause.Any ideas??? Thanks and appreciate your helpSQL Server 2000
View 1 Replies
View Related
Mar 13, 2007
Since i have 5 different projects all has the same kind of reports., but calling via 5 different sites all sites on same webserver.
The problem i have is the .rds file name is same in all 5 report projects and it is a shared datasource., now when i compile the report project it will not load or overwrite the .rds file since the name is same, for that reason i tried to have the .rds file in reports folder as a datasource not a shared datasource. but i tried to add it, after i create the datasource, it is automatically going into the shared datasource folder, how can i create a datasource not shared under reports folder.
Thank you very much for all your help and information.
View 3 Replies
View Related
Mar 8, 2004
Hello Everyone,
Hope someone will be able to help me out here.
I have a text file exported from my DTS package and it requires an '!' as a custom column delimiter.
Does anyone have any idea how I can use the '!' mark instead of the Tab or Vertical Bar as my Column Delimiter?
Would appreciate any suggestions.
Thanks,
Kay
View 3 Replies
View Related
Mar 20, 2008
Problem:
like in .net or any other language,we can get the file list in a specified folder
How we can do this through sqlserver (stored procedure or any thing else)
I want to show the file name list(by providing the path of any folder on system) in sql query analyser by running any script for that
Thanks........
View 1 Replies
View Related
