COLLATION in bulk insert

Hi,
I have a bulk insert statement:
BULK INSERT DatabaseTMP_CONVERT FROM 'CONVERT.CSV'
WITH (
DATAFILETYPE = 'WIDECHAR'
FIRSTROW = 1,
FIELDTERMINATOR = 'COL_SEP',
ROWTERMINATOR = 'ROW_SEP',
CODEPAGE = 'RAW')
Some fields that are inserted are encrypted using special characters
beyond the normal ascii character set. I have a database that has a
collation set to Arabic. When the collation is set to arabic the import
does not transfer some data across properly.
When I set the collation to latin on this database, the data comes
across correctly. Can someone help me to explain why this is the case?Hi
You don't say how the data file is created! Have you tried using code page
1256?
John
"Feldaspar" wrote:

> Hi,
> I have a bulk insert statement:
> BULK INSERT DatabaseTMP_CONVERT FROM 'CONVERT.CSV'
> WITH (
> DATAFILETYPE = 'WIDECHAR'
> FIRSTROW = 1,
> FIELDTERMINATOR = 'COL_SEP',
> ROWTERMINATOR = 'ROW_SEP',
> CODEPAGE = 'RAW')
> Some fields that are inserted are encrypted using special characters
> beyond the normal ascii character set. I have a database that has a
> collation set to Arabic. When the collation is set to arabic the import
> does not transfer some data across properly.
> When I set the collation to latin on this database, the data comes
> across correctly. Can someone help me to explain why this is the case?
>|||Hi,
I have tried using codepage 1256 but to no luck. The data file is
created in code just by running through some old DBF files and
extracting there values and putting them in to a csv file.
John Bell wrote:
[vbcol=seagreen]
> Hi
> You don't say how the data file is created! Have you tried using code page
> 1256?
> John
> "Feldaspar" wrote:
>|||Hi
Is the file created unicode? If not then you may wish to change the
Datafiletype. Also make sure that contents of the file are as expected for
the Arabic characters.
John
"Feldaspar" wrote:

> Hi,
> I have tried using codepage 1256 but to no luck. The data file is
> created in code just by running through some old DBF files and
> extracting there values and putting them in to a csv file.
>
> John Bell wrote:
>
>|||Hi,
The believe the datafile is created in whatever language is default on
the machine creating it. If it is created on an Arabic machine then it
will be arabic, if it is created on an english machine then it will be
english. So I believe it could be of all different types.
John Bell wrote:
[vbcol=seagreen]
> Hi
> Is the file created unicode? If not then you may wish to change the
> Datafiletype. Also make sure that contents of the file are as expected for
> the Arabic characters.
> John
> "Feldaspar" wrote:
>|||Hi
You may want to post DDL (see http://www.aspfaq.com/etiquette.asp?id=5006)
and an some example records that you have tried, You may want to cut this
down to a smaller size if necessary. Make sure that you have tried the sampl
e
before posting. Some newsreaders (such as the one I am using) do no accept
attachments.
John
"Feldaspar" wrote:

> Hi,
> The believe the datafile is created in whatever language is default on
> the machine creating it. If it is created on an Arabic machine then it
> will be arabic, if it is created on an english machine then it will be
> english. So I believe it could be of all different types.
>
> John Bell wrote:
>
>

COLLATION in bulk insert

Hi,
I have a bulk insert statement:
BULK INSERT DatabaseTMP_CONVERT FROM 'CONVERT.CSV'
WITH (
DATAFILETYPE = 'WIDECHAR'
FIRSTROW = 1,
FIELDTERMINATOR = 'COL_SEP',
ROWTERMINATOR = 'ROW_SEP',
CODEPAGE = 'RAW')
Some fields that are inserted are encrypted using special characters
beyond the normal ascii character set. I have a database that has a
collation set to Arabic. When the collation is set to arabic the import
does not transfer some data across properly.
When I set the collation to latin on this database, the data comes
across correctly. Can someone help me to explain why this is the case?Hi
You don't say how the data file is created! Have you tried using code page
1256?
John
"Feldaspar" wrote:
> Hi,
> I have a bulk insert statement:
> BULK INSERT DatabaseTMP_CONVERT FROM 'CONVERT.CSV'
> WITH (
> DATAFILETYPE = 'WIDECHAR'
> FIRSTROW = 1,
> FIELDTERMINATOR = 'COL_SEP',
> ROWTERMINATOR = 'ROW_SEP',
> CODEPAGE = 'RAW')
> Some fields that are inserted are encrypted using special characters
> beyond the normal ascii character set. I have a database that has a
> collation set to Arabic. When the collation is set to arabic the import
> does not transfer some data across properly.
> When I set the collation to latin on this database, the data comes
> across correctly. Can someone help me to explain why this is the case?
>|||Hi,
I have tried using codepage 1256 but to no luck. The data file is
created in code just by running through some old DBF files and
extracting there values and putting them in to a csv file.
John Bell wrote:
> Hi
> You don't say how the data file is created! Have you tried using code page
> 1256?
> John
> "Feldaspar" wrote:
> > Hi,
> >
> > I have a bulk insert statement:
> >
> > BULK INSERT DatabaseTMP_CONVERT FROM 'CONVERT.CSV'
> > WITH (
> > DATAFILETYPE = 'WIDECHAR'
> > FIRSTROW = 1,
> > FIELDTERMINATOR = 'COL_SEP',
> > ROWTERMINATOR = 'ROW_SEP',
> > CODEPAGE = 'RAW')
> >
> > Some fields that are inserted are encrypted using special characters
> > beyond the normal ascii character set. I have a database that has a
> > collation set to Arabic. When the collation is set to arabic the import
> > does not transfer some data across properly.
> >
> > When I set the collation to latin on this database, the data comes
> > across correctly. Can someone help me to explain why this is the case?
> >
> >|||Hi
Is the file created unicode? If not then you may wish to change the
Datafiletype. Also make sure that contents of the file are as expected for
the Arabic characters.
John
"Feldaspar" wrote:
> Hi,
> I have tried using codepage 1256 but to no luck. The data file is
> created in code just by running through some old DBF files and
> extracting there values and putting them in to a csv file.
>
> John Bell wrote:
> > Hi
> >
> > You don't say how the data file is created! Have you tried using code page
> > 1256?
> >
> > John
> >
> > "Feldaspar" wrote:
> >
> > > Hi,
> > >
> > > I have a bulk insert statement:
> > >
> > > BULK INSERT DatabaseTMP_CONVERT FROM 'CONVERT.CSV'
> > > WITH (
> > > DATAFILETYPE = 'WIDECHAR'
> > > FIRSTROW = 1,
> > > FIELDTERMINATOR = 'COL_SEP',
> > > ROWTERMINATOR = 'ROW_SEP',
> > > CODEPAGE = 'RAW')
> > >
> > > Some fields that are inserted are encrypted using special characters
> > > beyond the normal ascii character set. I have a database that has a
> > > collation set to Arabic. When the collation is set to arabic the import
> > > does not transfer some data across properly.
> > >
> > > When I set the collation to latin on this database, the data comes
> > > across correctly. Can someone help me to explain why this is the case?
> > >
> > >
>|||Hi,
The believe the datafile is created in whatever language is default on
the machine creating it. If it is created on an Arabic machine then it
will be arabic, if it is created on an english machine then it will be
english. So I believe it could be of all different types.
John Bell wrote:
> Hi
> Is the file created unicode? If not then you may wish to change the
> Datafiletype. Also make sure that contents of the file are as expected for
> the Arabic characters.
> John
> "Feldaspar" wrote:
> > Hi,
> >
> > I have tried using codepage 1256 but to no luck. The data file is
> > created in code just by running through some old DBF files and
> > extracting there values and putting them in to a csv file.
> >
> >
> > John Bell wrote:
> >
> > > Hi
> > >
> > > You don't say how the data file is created! Have you tried using code page
> > > 1256?
> > >
> > > John
> > >
> > > "Feldaspar" wrote:
> > >
> > > > Hi,
> > > >
> > > > I have a bulk insert statement:
> > > >
> > > > BULK INSERT DatabaseTMP_CONVERT FROM 'CONVERT.CSV'
> > > > WITH (
> > > > DATAFILETYPE = 'WIDECHAR'
> > > > FIRSTROW = 1,
> > > > FIELDTERMINATOR = 'COL_SEP',
> > > > ROWTERMINATOR = 'ROW_SEP',
> > > > CODEPAGE = 'RAW')
> > > >
> > > > Some fields that are inserted are encrypted using special characters
> > > > beyond the normal ascii character set. I have a database that has a
> > > > collation set to Arabic. When the collation is set to arabic the import
> > > > does not transfer some data across properly.
> > > >
> > > > When I set the collation to latin on this database, the data comes
> > > > across correctly. Can someone help me to explain why this is the case?
> > > >
> > > >
> >
> >|||Hi
You may want to post DDL (see http://www.aspfaq.com/etiquette.asp?id=5006)
and an some example records that you have tried, You may want to cut this
down to a smaller size if necessary. Make sure that you have tried the sample
before posting. Some newsreaders (such as the one I am using) do no accept
attachments.
John
"Feldaspar" wrote:
> Hi,
> The believe the datafile is created in whatever language is default on
> the machine creating it. If it is created on an Arabic machine then it
> will be arabic, if it is created on an english machine then it will be
> english. So I believe it could be of all different types.
>
> John Bell wrote:
> > Hi
> >
> > Is the file created unicode? If not then you may wish to change the
> > Datafiletype. Also make sure that contents of the file are as expected for
> > the Arabic characters.
> >
> > John
> >
> > "Feldaspar" wrote:
> >
> > > Hi,
> > >
> > > I have tried using codepage 1256 but to no luck. The data file is
> > > created in code just by running through some old DBF files and
> > > extracting there values and putting them in to a csv file.
> > >
> > >
> > > John Bell wrote:
> > >
> > > > Hi
> > > >
> > > > You don't say how the data file is created! Have you tried using code page
> > > > 1256?
> > > >
> > > > John
> > > >
> > > > "Feldaspar" wrote:
> > > >
> > > > > Hi,
> > > > >
> > > > > I have a bulk insert statement:
> > > > >
> > > > > BULK INSERT DatabaseTMP_CONVERT FROM 'CONVERT.CSV'
> > > > > WITH (
> > > > > DATAFILETYPE = 'WIDECHAR'
> > > > > FIRSTROW = 1,
> > > > > FIELDTERMINATOR = 'COL_SEP',
> > > > > ROWTERMINATOR = 'ROW_SEP',
> > > > > CODEPAGE = 'RAW')
> > > > >
> > > > > Some fields that are inserted are encrypted using special characters
> > > > > beyond the normal ascii character set. I have a database that has a
> > > > > collation set to Arabic. When the collation is set to arabic the import
> > > > > does not transfer some data across properly.
> > > > >
> > > > > When I set the collation to latin on this database, the data comes
> > > > > across correctly. Can someone help me to explain why this is the case?
> > > > >
> > > > >
> > >
> > >
>sqlsql

CmdExec in a Nightly Job

I have to verify a .CSV file exists before I run a BULK INSERT. I am using XP_FileExist in SQL 2000 to accomplish this. After the Bulk Insert is completed and validated, I need to Rename the file and Move the file to archive the folder. For testing I figure If I can rename the file I can move it. I suspect I have permission issues and need to provide the SQL Server Agent permissions to this folder and file. I have my PC setup as a SQL 2000 Server and am attempting to get this step only working on my local machine. I created a nightly Job that remanes a file that I created in a Job and that is all it does. I am running the Job as SA but am still having issues.

The step being executed by the Job is "Ren C:\MyTestFile.csv C:\MyTestFile1.csv" (with the quotes). If I run this statment (without the Quotes from a command prompt, the file is renamed.

I have set the Type as "Operating System Command (CmdEXEC)". The Job history shows "The process could not be created for step 2 of job 0x71D51027F920A140A2913234DB7FF509 (reason: The system cannot find the file specified). The step failed."

As I said, I suspect that it is a permissions issue as the command works from the command prompt. What is the windows account that the SQL Server Agent uses to commit these commands? I added "Everyone" with Full access to the folder and I still get the same failure.

I would appreciate any assistance anyone could provide. Thanks in advance!

SQL Server Agent has its own service account -it doesn't use the 'sa' account.

In Enterprise Manager, right-click on SQL Agent, then Properties, and you can view (and/or change) the service account for the SQL Server Agent service.

Here are a couple of resources about setting the SQL Agent account:

Configuration -Service Accounts, SQL Server 2005 - Setting Up Windows Service Accounts
http://msdn2.microsoft.com/en-us/library/ms143691.aspx
http://msdn2.microsoft.com/en-us/library/ms143504.aspx

Configuration -Service Accounts, SQL Server or SQL Server Agent service account
http://support.microsoft.com/kb/283811/en-us
http://msdn2.microsoft.com/en-us/library/ms143691.aspx

Configuration -Service Accounts,Selecting an Account for the SQL Server Agent Service
http://msdn2.microsoft.com/en-us/library/ms191543.aspx
http://support.microsoft.com/kb/907557