SQL Server Select to .dbf not curstor

[TonyScarpelli]

I am using VFP 9 SP2.

I am using the following to access a SQL Server table:
lnErr=SQLEXEC(gnConnHandle, lcStr, "MyResults")

The results are going into a cursor. Is there a way to put the results directly into a .dbf file?

Thanks.

Tony Scarpelli
Clinical Engineering Dept.
Maine Medical Center
Portland, Maine 04102

 

[Zografski]

NO

 

[Olaf Doschke]

Why do you want a dbf? Is your goal to make a middle tier component in vfp as the data acess component and the frontend is not vfp?

Otherwise you can do almost anything with a cursor, that you can do with a dbf, eg use it as source for controls, reports, write to it, index it. The only big difference is, it's not persistent. But you can do COPY TO.

Bye, Olaf.

 

[Mike Lewis]

Tony,

Yes, you can do that. The cursor you get back from SQL Server is an ordinary VFP cursor. You can copy it to a permanent physical table, just as you would with any other cursor.

For example, you can use CREATE TABLE to create the table (if it doesn't already exist), and then APPEND FROM DBF("MyResults") to copy the cursor to it. Or, you can do SELECT * FROM MyResults INTO TABLE MyNewTable.

Bear in mind that the cursor might have long field names, which will be truncated if the new table is not part of a DBC. There might also be issues with data types and NULLs. For instance, any date fields will in fact be datetimes.

Hope this helps.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[TonyScarpelli]

Thanks all for the info. And how you doing, Mike?

That's about what I did. I pulled in a cursor and COPYied it to a table.

The reason I did this was because this long weekend I am working at home and our SQL Server 2005 is at work, and I needed a table to work on at home to do some reports.

I was able to connect into work, make the table, then copy the table to my home computer. My company is behind a very secure firewall, and wasn't able to figure out whether I could access the SQL Server outside the firewall using a Remote view or something.

But I can log into my work computer, make the table and send it home.

Got the report done, though.

I won't be back to work until Wednesday, so got to do it again a couple more times.

CU all.


Tony Scarpelli
Clinical Engineering Dept.
Maine Medical Center
Portland, Maine 04102

 

[Mike Lewis]

And how you doing, Mike?

Nice and busy, I'm pleased to say. Thanks for asking, Tony.

The reason I did this was because this long weekend I am working at home and our SQL Server 2005 is at work, and I needed a table to work on at home to do some reports.

I'm often in that situation. Where possible, I like to detach the database from the server, take a copy of the physical files away with me, re-attach the database, then attach the copy to my local copy of SQL Server at home. But if you're in a production environment, you probably won't be able to do that. Your approach is a good alternative.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Olaf Doschke]

You could also copy single tables from SQL Server to another instance, eg Compact Edition to take that home.

Bye, Olaf.

 

[TonyScarpelli]

Unfortunately I only have read-only access to our database.

I tried to make a back up the database and I couldn't do that.

Can I do a copy of the table if I only have RO access?

Thanks.


Tony Scarpelli
Clinical Engineering Dept.
Maine Medical Center
Portland, Maine 04102

 

[Olaf Doschke]

Well, you can setup compact edition on a usb stick and have all access rights there.

When copying a sql table with the Sql Server Management Studio you need more rights than just data_reader for the source database too, but I don't know the minimum requirements. As a developer I'm admin and don't need to care for such details. If you can get into Sql Server Management Studio, that's pretty much rights you'd already have. You'd not backup the table but copy it, from the server instance to your local usb stick instance.

Bye, Olaf.

 

[TonyScarpelli]

I am using "Microsoft SQL Server Management Studio Express" for SQL Server 2005.

In order to copy a database:
"You must be a member of the sysadmin fixed server role on both the source and destination servers." which I am not on the source.

I can't even see the Copy Wizard in Tasks.

But this is OK now anyway. I am just doing reports, and can get a query to give me a cursor, then copy to a table if I need it.

Since I am at work now, after a nice long weekend, I can get the cursor in a nice FP application. FoxPro rules.

CU


Tony Scarpelli
Clinical Engineering Dept.
Maine Medical Center
Portland, Maine 04102