sql problem

[fluppe689]

Hello Experts,

I have a problem with a bulk insert.
On my local machine (windows xp with a full sql 2008 r2) everything works fine.
The problem exists on the database on location with my client. (windows server 2008 with an sqlexpress 2008 r2)

my instruction is :

sql = "select * FROM " + m.y_db1 + "f_par "
retcode = SQLEXEC(handle,sql,"t_par")
IF retcode > 0
SELECT t_par
GO top
m.xxextdir = T_par.extdir
m.xxprogdir = T_par.progdir
SET defau TO &xxextdir
gnextern = ADIR(gaDatabase, '*.csv')
FOR nCount = 1 TO gnextern
m.bestand = ALLTRIM(m.xxextdir) +gaDatabase(nCount,1)
SQL = "bulk insert " + m.y_db1 + "f_extern from " +CHR(13) +;
"'" + m.bestand + "' " +;
"with (CODEPAGE ='WIDECHAR', MAXERRORS = 100,DATAFILETYPE = 'widechar',FIELDTERMINATOR = ';',ROWTERMINATOR ='\l')"
retcode = SQLEXEC(handle,sql,"")
IF retcode > 0
DELETE FILE gaDatabase(nCount,1)
ELSE
= AERROR(aErrorArray) && Data from most recent error
wait window str(aErrorArray(1),8,0) + chr(13) +;
aErrorArray(2) + chr(13) +;
'02 INSERT F_EXTERN'
return
ENDIF
ENDFOR
SET defau TO &xxprogdir
ELSE
= AERROR(aErrorArray) && Data from most recent error
wait window str(aErrorArray(1),8,0) + chr(13) +;
aErrorArray(2) + chr(13) +;
'02 SELECT T_PAR'
return
ENDIF


the problem is when i execute the bulk insert : STRING IS TOO LONG TO FIT

I hope somebody can help me out here

Wfg,

Filip

 

[Olaf Doschke]

When does the error happen? When you set sql, when doing sqlexec or within bulk insert?

VFP has that error message for error 1903. But this would either mean your sql string is larger than 16 MB or a part of your string literal is larger than 255 chars. I don't spot both reasons.

Your sqlexec might indeed not error, but return 0, because the bulk insert needs some time. If you have an asynch connection sqlexec can return with retocode=0 meaning no error, but simply still running the statement.

What AERROR() then reports might just be the last error, that happened previous to all this code.

Bye, Olaf.

 

[fluppe689]

Olaf,

The error turns on everty time i do the sqlexec, just on the server not on my pc.
This code i wrote above is a part of a class that runs every app. every 10 min.
So when there is a csv file i get the error on the server of my client, and again not on my pc.
Yes in the csv there van be zones of more than 255 characters.


Filip

 

[Olaf Doschke]

That doesn't answer my question about where that error happens: I was talking about the line of code, not which computer.

Also the 255 char limit is a limit of string literals, as said, not of strings withion the csv. A string literal is what you have in your code between string delimiters. litaral more general is, well, a literal, in contrast to a variable or constant...

http://en.wikipedia.org/wiki/Literal_(computer_programming)

In your code one sample literal is "bulk insert ", surely lower than 255 chars. I said this is not the problem, this is just what is a limit and the error points to some limit.

Bye, Olaf.

 

[fluppe689]

Hy Olaf,

the line of code where is happens is
SQL = "bulk insert " + m.y_db1 + "f_extern from " +CHR(13) +;
"'" + m.bestand + "' " +;
"with (CODEPAGE ='WIDECHAR', MAXERRORS = 100,DATAFILETYPE = 'widechar',FIELDTERMINATOR = ';',ROWTERMINATOR ='\l')"

But i have noticed now something where i do not know the anser.
I did the same thing in the management studio and i had to change the dictory from r:\merlijn\aanvragen\aaa.csv to diretorot d:\data\merlijn\aanvragen which should be the same, only the first is the sharename

Wfg,


Filip

 

[Olaf Doschke]

OK,

all the literals ehre are short. Then what is LEN(m.bestand) and LEN(m.y_db1)?

If I set these two variables to short strings this does not error (and is not expected to error.

Bye, Olaf.

 

[fluppe689]

hallo Olaf,

Found the problem:

changed m.bestand = ALLTRIM(m.xxextdir) +gaDatabase(nCount,1)
into "\\server02\data\merlijn\aanvragen\" + gaDatabase(nCount,1)


That does the job,

Thanks in any case for your help

Filip

 

[Olaf Doschke]

If that was the error, then you should change T_par.extdir, where that info comes from. If the directories are not the same for xp and server, well.... configuration, configuration, configuration.

Bye, Olaf.