Connectivity Error Migrating DBF to SQL

[SGLong]

I am trying to migrate a DBF into a SQL table and am having a 'keyword' problem. The DBF contains an integer field called 'key'. I've created the SQL table with that value (actually [key]) and it works fine.

The problem is when I try to create a VFP Remote view using the view designer it appears to choke on that column name. The message I get is "Connectivity error: [Microsoft][ODBC SQL Server Drive][SQL Server]Incorrect syntax near the keyword 'key'.

I've tried all sorts of combinations when defining the remote view: table.[key] as key, table.[key] as [key], [table.key] as key, and [table.key] as [key] - all without success.

How can I get the view created without changing the column name?

Steve

 

[Mike Lewis]

Stgeve,

Is there a compelling reason for you to name the field "key"? If it's not too late, I suggest you give it a different name.

"key" is a reserved in Microsoft SQL Server, and probably other SQL databases as well. Although you can get away with it in SQL Server (by enclosing the field name in square brackets), it's bound to cause problems somewhere down the line.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[SGLong]

Mike,

I've been tasked with migrating off of native VFP tables to SQL. There is a fair amount of code that would have to be modified if I were to change the column name. If there's no other alternative that's what I will do but I will need to present a convincing argument to my manager for this "scope creep".

Steve

 

[Mike Lewis]

Steve,

I've just tried doing what you're doing, and I find that tablename.[key] works OK.

To be precise, I used the remote view designer to generate the SELECT. When I tried to run it, I got the same error that you did. Then I went to the SQL editing window, and edited this:

SELECT Test.key, Test.field1;
FROM dbo.Test Test

to this:

SELECT Test.[key], Test.field1;
FROM dbo.Test Test

After that, the view opened correctly (I didn't try to make it updatable).

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[Mike Lewis]

Steve,

I've been messing about a bit more, and I see you can also get it to work by putting Key in single quotes.

This command worked OK:

CREATE SQL VIEW ViewTest ;
  REMOTE CONNECTION Test AS ;
  SELECT 'test.key', test.field1 FROM test

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[SGLong]

Mike,

Thanks for the tip. I'll give it a try and let you know what happens.

Steve

 

[SGLong]

Mike,

Sorry to report that they didn't work here. I don't know what version you used but we're still on VFP 6 here.

For your first suggestion I got 'The multi-part identifier "sp.key" could not be bound.' For the second one I ended up with a column called 'expr' that had 'sk.key' in every row.

Unless someone can come up with a solution I may have to modify the field name (ugh).

Steve

 

[Mike Lewis]

Steve,

I used VFP 9.0 for my testing. It's possible that 6.0 works differently, although I can't confirm that either way.

I hope someone else can come up with a solution.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business