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

French characters on English Platform dropped

Hi all,
I Had installed an english platform with full text indexing enable.
My data are French (with some accents for example).
When I search with a CONTAINS Statement on my Data, SQL Server drops my
accentuates characters.
How can I resolve this problem ?
What must I install on my platform to resolve this problem ?
Thanks for your answers.
Alex.
Exactly what do you mean by this.
Do you mean that a search on cafe will not match with a search on the
accented version of cafe?
This problem is fixed in SQL 2005.
In the meantime you have to expand your search on both the accented and
unaccented version of the search term.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Alexandre BARBIER" <(del_this)abarbier@.sopragroup.com> wrote in message
news:e8S11xylEHA.704@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I Had installed an english platform with full text indexing enable.
> My data are French (with some accents for example).
> When I search with a CONTAINS Statement on my Data, SQL Server drops my
> accentuates characters.
> How can I resolve this problem ?
> What must I install on my platform to resolve this problem ?
> Thanks for your answers.
> Alex.
>
|||Hi,
No, I mean that my data are in french version and my full text search turn
on an English Platform.
When I search a french word, the fulltext search dropped my accented
characters so my search doesn't work.
Any Ideas ?
Thanks.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23CSrWe0lEHA.3016@.tk2msftngp13.phx.gbl...
> Exactly what do you mean by this.
> Do you mean that a search on cafe will not match with a search on the
> accented version of cafe?
> This problem is fixed in SQL 2005.
> In the meantime you have to expand your search on both the accented and
> unaccented version of the search term.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Alexandre BARBIER" <(del_this)abarbier@.sopragroup.com> wrote in message
> news:e8S11xylEHA.704@.TK2MSFTNGP09.phx.gbl...
>
|||Alexandre,
This has been a long outstanding bug in SQL Server 7.0 and SQL Server 2000,
that is only "fixed" in SQL Server 2005 (Yukon) via the following new T-SQL
syntax:
CREATE FULLTEXT CATALOG Employee_FTC WITH ACCENT_SENSITIVITY = ON
CREATE FULLTEXT CATALOG Employee_FTC WITH ACCENT_SENSITIVITY=OFF
-- Or by using ALTER FULLTEXT CATALOG:
ALTER FULLTEXT CATALOG Employee_FTC REBUILD WITH ACCENT_SENSITIVITY=ON
The SQL Server 7.0 or SQL Server 2000 solution requires the duplication of
the accented data with the removal of accents via a UDF and insert/update
trigger to maintain the duplicate data. You would create a FT Index on the
non-accented data and return the accented data to your searcher.
Regards,
John
"Alexandre BARBIER" <(del_this)abarbier@.sopragroup.com> wrote in message
news:eE9gBIWmEHA.412@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Hi,
> No, I mean that my data are in french version and my full text search turn
> on an English Platform.
> When I search a french word, the fulltext search dropped my accented
> characters so my search doesn't work.
> Any Ideas ?
> Thanks.
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23CSrWe0lEHA.3016@.tk2msftngp13.phx.gbl...
my
>

French characters are not imported properly with bcp

Hi
I have a script that uses bcp to import data from an ascii text file
into SQL tables. The french characters are not copied properly. They
are converted to letters of the alphabet. I tried to change all the
fields to nvarchar instead of varchar and nchar instead of char, but I
got Greek characters instead.

How can I fix this?

Here is some code:
--------
CREATE TABLE [dbo].[1_HLGT_HLT_COMP_f9.0] (
[hlgt_code] [int] NOT NULL ,
[hlt_code] [int] NOT NULL
) ON [PRIMARY]
GO

PRINT 'HLGT_HLT'
DECLARE @.s as nvarchar(300)
SET @.s='bcp MedDRA..[1_hlgt_hlt_comp_f9.0] in ' + char(34) +
'F:\MedDRA\9.0French\MedAscii\hlgt_hlt.asc' + char(34) + ' -c -t' +
char(34) + '$' + char(34) + ' -r$\n -e' + char(34) +
'F:\MedDRA\9.0French\logs\hlgt_hlt.err.txt' + char(34) + ' -b250 -m50
-SDEV -Usa -Ppassword -h' + char(34) + 'TABLOCK' + char(34)
EXEC master..xp_cmdshell @.sWael (sedky@.rocketmail.com) writes:
> I have a script that uses bcp to import data from an ascii text file
> into SQL tables. The french characters are not copied properly. They
> are converted to letters of the alphabet. I tried to change all the
> fields to nvarchar instead of varchar and nchar instead of char, but I
> got Greek characters instead.

Add -C RAW to the list of BCP options. By default, BCP assumes that
characters are in the OEM code page and will convert them to the ANSI
code page. Which results in a mess when data is already in the ANSI
code page.

> How can I fix this?
> Here is some code:
> --------
> CREATE TABLE [dbo].[1_HLGT_HLT_COMP_f9.0] (
> [hlgt_code] [int] NOT NULL ,
> [hlt_code] [int] NOT NULL
> ) ON [PRIMARY]
> GO

Eh, there are character columns in that table?

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Does this require any special install on the server? I tried -C863 for
Canadian French, but the server does not recognize that code page.|||Wael (sedky@.rocketmail.com) writes:
> Does this require any special install on the server? I tried -C863 for
> Canadian French, but the server does not recognize that code page.

I have not heard of 863 before, but it sounds like an OEM code page.

Playing around a little, it appears that you can only use code pages
for which there are one or more collations. For instace, I tried CP852
which is Eastern Europe, but this gave me an error. On the the other
hand, 874 worked. (There are a number of Thai collations with that code
page.)

But is your file really in code page 863? And in such case how different
would it be from code page 850? (My standard source for checking out
code pages is down, so I can't check right now.)

Did you try -C RAW?

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I tried -C RAW but i got an error. If you check the windows regional
settings, you'll see 863 is the code page for Canadian French.

Regional and Language Options\Advanced|||Wael (sedky@.rocketmail.com) writes:
> I tried -C RAW but i got an error.

An error? Care to specify what? It's difficult to assist without knowing
the error message.

> If you check the windows regional
> settings, you'll see 863 is the code page for Canadian French.
> Regional and Language Options\Advanced

Yes, I also see that it is an OEM code page. Is the data file in this
code page?

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||For question number 1, the error is pasted below.
For the second one I am not sure. The original text is a plain text
file.

NULL
Starting copy...
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Code page 863 is
not supported by SQL Server
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Unable to resolve column
level collations
NULL
BCP copy in failed
NULL

(9 row(s) affected)|||Wael (sedky@.rocketmail.com) writes:
> For question number 1, the error is pasted below.
> For the second one I am not sure. The original text is a plain text
> file.

If you the open file in Notepad, does it look good then? If it does,
it is not an OEM file. If the French characters are replaced with
other single characters, it could be CP863.

If the file looks bad in Notepad, does it look good when you type it
from a command-line window.

I'm a little surprised of the message you got with -C RAW. I was under
the impression that this would suppress all code-page conversion.

Anyway, if the file looks good in Notepad, use -C ACP or -C 1252.

If it looks goot in the command-line window, try -C 850. I don't know
about CP 863, but I would expected to be a version of CP 850.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||It worked. ACP worked.

THank you very much.