topic Re: Script error help in QlikView

Script error help

hopkinsc — Tue, 08 Mar 2016 15:12:55 GMT

Hi all,

i am getting a script error which i am having trouble resolving. i dont get this error on server A but we have just moved to a brand new server and i am now getting this error.

08/03/2016 14:52:55: 0090 CONCATENATE(SCADAFile)

08/03/2016 14:52:55: 0091 LOAD 'E:\VeoliaData\SCADA Data\MacDuff\DLGLOG\bankhead ps\2016 01 01 0000 (Wide).DBF' as [SCADA File Loaded],

08/03/2016 14:52:55: 0092 num(Date)+num(Time) as DateTime_Raw,

08/03/2016 14:52:55: 0093 num(Date)+num(Time) as DateTime_Raw1,

08/03/2016 14:52:55: 0094 [0] as [1]

08/03/2016 14:52:55: 0095 SQL SELECT Date,Time,`0`

08/03/2016 14:52:55: 0096 FROM `..\..\..\VeoliaData\SCADA Data\MacDuff\DLGLOG\bankhead ps`\`2016 01 01 0000 (Wide).DBF`

08/03/2016 14:52:56: Error: SQL##f - SqlState: S0002, ErrorCode: 4294965991, ErrorMsg: [Microsoft][ODBC dBASE Driver] The Microsoft Access database engine could not find the object '2016 01 01 0000 [Wide].DBF'. Make sure the object exists and that you spell its name and the path name correctly. If '2016 01 01 0000 [Wide].DBF' is not a local object, check your network connection or contact the server administrator.

08/03/2016 14:52:56: 0098 DISCONNECT

08/03/2016 14:52:56: 0099 NEXT file

it says that the file doesnt exist but when looking in the folder i can see the file. what i have noticed is that on line 0091 the file is loaded in and the file itself is 2016 01 01 0000 (Wide).DBF but the error message has the file name as 2016 01 01 0000 [Wide].DBF - it has square brackets, so i assume this is the problem. i just dont understand why its correct when loaded in (0091) but incorrect in the error.

can anyone help?

my script is.

LET Data = chr(96) & '$(vDirectory)' & chr(96) & '\' & chr(96) & Subfield(GetShortName('$(file)'),'\',-1) & chr(96);

// use short name to get around ODBC driver issue

ODBC CONNECT32 TO [DBF Driver];

// Load required fields from DBF file

CONCATENATE(SCADAFile)

LOAD '$(file)' as [SCADA File Loaded],

num(Date)+num(Time) as DateTime_Raw,

num(Date)+num(Time) as DateTime_Raw1,

$(vFieldAlias);

SQL SELECT $(vSQLFields)

FROM $(Data);

DISCONNECT;

NEXT file

Re: Script error help

swuehl — Tue, 08 Mar 2016 15:21:42 GMT

Not sure if this makes a difference, but why do you add additional grave accents around the last \ ?

...ad ps`\`2016 ...

LET Data = chr(96) & '$(vDirectory)' & chr(96) & '\' & chr(96) & Subfield(GetShortName('$(file)'),'\',-1) & chr(96);

Re: Script error help

hopkinsc — Tue, 08 Mar 2016 15:27:41 GMT

Hi Stefan, thanks for your reply

i wondered this also ( i didnt create this script).

i tried removing them but still get an error.

Error: SQL##f - SqlState: 37000, ErrorCode: 4294966294, ErrorMsg: [Microsoft][ODBC dBASE Driver] '..\..\..\VeoliaData\SCADA Data\MacDuff\DLGLOG\bankhead ps\2016 01 01 0000 [Wide].DBF' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.

again, the fielname contains [Wide] where it should be (Wide) but im unsure if this is relevant.

Re: Script error help

syukyo_zhu — Tue, 08 Mar 2016 15:36:07 GMT

hi,

your file is in folder E:\VeoliaData$..\VeoliaData$ or '...\..\..\VeoliaData\'.(E:\xxx\xxx\VeoliaData)

Re: Script error help

hopkinsc — Tue, 08 Mar 2016 16:17:53 GMT

the files are in E:\VeoliaData which is ..\..\..\VeoliaData when using relative paths

Re: Script error help

swuehl — Tue, 08 Mar 2016 16:23:11 GMT

And what is GetShortName() doing?

Have you tried

SQL SELECT $(vSQLFields)

FROM $(file);

And are you loading any other table files from the same path with the same ODBC connection?

Re: Script error help

hopkinsc — Tue, 08 Mar 2016 16:31:59 GMT

the GetShortName() gets the windows short name as long file names in .DBF files cause errors with the ODBC connection.

see

http://stackoverflow.com/questions/15760940/dbf-file-name-truncated

reading from $(file) doesn't work because the filename is over 8 characters and causes the above odbc error.

the ODBC connection disconnects after every file then re connects at the start of the next file. there are multiple DBF files in the directory.

Re: Script error help

swuehl — Tue, 08 Mar 2016 16:37:21 GMT

So this is what you expect from GetShortName()?

`2016 01 01 0000 (Wide).DBF`

Re: Script error help

hopkinsc — Tue, 08 Mar 2016 17:05:35 GMT

well as the link i gave earlier says the maximum file name size cant be more than 8 characters i assumed the actual file name would be shortened but it doesnt look like GetShortName is doing anything as the file name is the same!

i do get a different error though when trying to use FROM $(file).

i dont understand why the brackets change to the square brackets though in the error message

Re: Script error help

hopkinsc — Wed, 09 Mar 2016 10:20:35 GMT

i have just looked at the 'working' server and i can see that the GetShortName returns the following

..\..\..\VeoliaData\SCADA Data\MacDuff\DLGLOG\bankhead ps`\`20A398~1.DBF

for the following file

..\..\..\VeoliaData\SCADA Data\MacDuff\DLGLOG\bankhead ps' & chr(96) & '\' & chr(96) & Subfield(GetShortName('C:\VeoliaData\SCADA Data\MacDuff\DLGLOG\bankhead ps\2016 01 05 0000 (Wide).DBF'),'\',-1) & chr(96)

this isnt working on the new server, but the new server is 2012 and the old server is 2008 so im wondering if that is what is stopping it from working.

Re: Script error help

swuehl — Wed, 09 Mar 2016 12:05:19 GMT

Well, I would definitely look into this direction next, i.e. check why the macro is not returning the short name.

Maybe someone followed

Server 2012: Best Practice – Disable short file name creation | IT-Notes

I would suggest that you get together with the server admin to see how you can resolve the issue.

Re: Script error help

hopkinsc — Wed, 09 Mar 2016 12:08:07 GMT

oh ok, i didnt realize you could disable that.

thats my next step then

Thanks very much