from sql - checking if a file exists in a given share

Hi,
from sql, what is the best way to check if a file exists in a share - given
the file's partial name (for example the first 20 characters) ?
I thought about saving the result set of "xp_cmdShell dir shareName ... "
into a temp table and query the temp table for the filename... but are there
better ways that do not require xp_cmdShell (that needs special permissions
to execute)?
ThanksYou could try using the sp_OA* procedures and the FileSystemObject in VBS.
ML
http://milambda.blogspot.com/|||This sounds interesting. Could you please point me to where I can find
sample code?
Thank you.
"ML" <ML@.discussions.microsoft.com> wrote in message
news:D4DE0DE1-BA83-4810-B385-FC6005DE4B45@.microsoft.com...
> You could try using the sp_OA* procedures and the FileSystemObject in VBS.
>
> ML
> --
> http://milambda.blogspot.com/|||A few samples are available in Books Online, the rest comes down to your
experience with VBS.
This sample demonstrates the use of the sp_OA* procedures:
http://msdn.microsoft.com/library/d...r />
_2ktw.asp
ML
http://milambda.blogspot.com/|||If your on 2005, you could do a UDF such as:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static bool FileExists(string path)
{
return File.Exists(path);
}
};
William Stacey [MVP]
"Ramadan" <noOne@.hotmail.com> wrote in message
news:eIcSTaPAGHA.532@.TK2MSFTNGP15.phx.gbl...
> Hi,
> from sql, what is the best way to check if a file exists in a share -
> given
> the file's partial name (for example the first 20 characters) ?
> I thought about saving the result set of "xp_cmdShell dir shareName ...
> "
> into a temp table and query the temp table for the filename... but are
> there
> better ways that do not require xp_cmdShell (that needs special
> permissions
> to execute)?
> Thanks
>|||You can check this via the undocumented procedure (but be aware that
this is undocumented, not supported and could be deppricated in further
versions):
http://www.sql-server-performance.c..._procedures.asp
xp_fileexist
You can use this extended stored procedure to determine whether a
particular file exists on the disk or not. The syntax for this xp is:
EXECUTE xp_fileexist filename [, file_exists INT OUTPUT]
For example, to check whether the file boot.ini exists on disk c: or
not, run:
EXEC master..xp_fileexist 'c:\boot.ini'
HTH, jens Suessmeyer.sql

Freaking column names

I'm dealing with a database with tables that have freaking columns.
Partial DDL:
Create table [tbl IMN] ([Ave Cost-Mn] varchar(10))
-- yeah, this column contains string value

Now, I'd like to rename all these freaking columns without special
charaters like '-', 'whitespace' etc systematically (meaning loop
through all tables and columns dynamically). It seems that the
sp_rename proc can't handle some function call or ...?
e.g.
exec sp_rename '[tbl-IMN].[Ave Cost-Mn]',replace('[Ave
Cost-Mn]','-',''),'COLUMN'

better if it can work,
exec sp_rename '[tbl-IMN].[Ave Cost-Mn]',replace('[Ave Cost-Mn]','-|
',''),'COLUMN'

TIANickName (dadada@.rock.com) writes:
> I'm dealing with a database with tables that have freaking columns.
> Partial DDL:
> Create table [tbl IMN] ([Ave Cost-Mn] varchar(10))
> -- yeah, this column contains string value
> Now, I'd like to rename all these freaking columns without special
> charaters like '-', 'whitespace' etc systematically (meaning loop
> through all tables and columns dynamically). It seems that the
> sp_rename proc can't handle some function call or ...?
> e.g.
> exec sp_rename '[tbl-IMN].[Ave Cost-Mn]',replace('[Ave
> Cost-Mn]','-',''),'COLUMN'
> better if it can work,
> exec sp_rename '[tbl-IMN].[Ave Cost-Mn]',replace('[Ave Cost-Mn]','-|
> ',''),'COLUMN'

Correct. In difference to most other languages, you cannot pass
expressions as parameters to stored procedures. You can only psss
constants and variables.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||How about this? Rather than a stored procedure, generate a script to
run against all of your columns, like so:

SELECT 'exec sp_rename ''' + Table_Name + '.'
+ Column_name + ''', ''' +
REPLACE(COLUMN_NAME, '-', '') + ''', ''COLUMN'''
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%-%'

Cut and paste the results of the query into a new QA window and execute
the statements.

HTH,
Stu|||Actually, I could do something like
select @.colTemp = Replace(col,'-','')
then
exec sp_rename '[tbl-IMN].[Ave Cost-Mn]',@.colTemp,'COLUMN'

I thought about that before posting but I did not try (just don't know
why somtimes I'm so freaking lazy).

Thanks for the note though.

Don|||Interesting idea, however, I don't think it works.|||Worked in my test bed; what error did you get? Just to make sure that
I was clear, the above command will NOT execute the stored procedure;
it'll just generate a script of all the columns in all the tables in
your database with a '-' in the name, like so:

exec sp_rename 'SPLAT.Check-ID', 'CheckID', 'COLUMN'
exec sp_rename 'SPLAT.Check-ID2', 'CheckID2', 'COLUMN'

You have to cut and paste that script into a new window in query
analyzer to actually execute the changes.

You could, of course, modify the SQL statement to actually execute the
stored procedure for you; I just prefer to do it this way so I can
visually check what I'm about to execute.

Stu|||Yeah, I see. Problem resolved. Thanks though.
Stu wrote:
> Worked in my test bed; what error did you get? Just to make sure that
> I was clear, the above command will NOT execute the stored procedure;
> it'll just generate a script of all the columns in all the tables in
> your database with a '-' in the name, like so:
> exec sp_rename 'SPLAT.Check-ID', 'CheckID', 'COLUMN'
> exec sp_rename 'SPLAT.Check-ID2', 'CheckID2', 'COLUMN'
> You have to cut and paste that script into a new window in query
> analyzer to actually execute the changes.
> You could, of course, modify the SQL statement to actually execute the
> stored procedure for you; I just prefer to do it this way so I can
> visually check what I'm about to execute.
> Stu