Microsoft KB Archive/238904

= How to use SQL pass-through to SELECT INTO a new SQL Server table in Visual FoxPro =

Article ID: 238904

Article Last Modified on 3/16/2005

-

APPLIES TO


 * Microsoft Visual FoxPro 3.0 Standard Edition
 * Microsoft Visual FoxPro 3.0b Standard Edition
 * Microsoft Visual FoxPro 5.0 Standard Edition
 * Microsoft Visual FoxPro 5.0a
 * Microsoft Visual FoxPro 6.0 Professional Edition
 * Microsoft Visual FoxPro 7.0 Professional Edition
 * Microsoft Visual FoxPro 8.0 Professional Edition
 * Microsoft Visual FoxPro 9.0 Professional Edition

-



This article was previously published under Q238904



SUMMARY
Under some circumstances, developers might want to issue an SELECT-SQL statement against tables on SQL Server and create a new table on SQL Server. This article illustrates how to use SQL Pass Through from Visual FoxPro to SELECT INTO a new SQL Server table.



MORE INFORMATION
In order to issue a SELECT-SQL statement that creates a new SQL Server table, the select into/bulkcopy option must be enabled for the SQL Server database. By default, the select into/bulkcopy option is disabled in newly-created SQL Server databases. The select into/bulkcopy option can be modified with the sp_dboption stored procedure. The syntax for changing the status of the select into/bulkcopy option is: sp_dboption 'pubs','select into/bulkcopy','true' -or-

sp_dboption 'pubs','select into/bulkcopy','false' sp_dboption changes settings for a SQL Server database.

Note Executing permissions to change an option in SQL Server, using the sp_dboption stored procedure, defaults to members of the sysadmin fixed server role or the db_owner fixed database role for the database for which the option is to be changed.

The following code snippet uses SQL Pass Through to select all records from the Authors table of the Pubs database into a new table called Mytable in the Pubs database. This assumes that the user has the appropriate permissions and that the correct values are supplied. *!* Note Please change the SERVER= segment of the following line to reflect gnConnHandle=SQLSTRINGCONN('DRIVER={SQL Server};SERVER=MY_SERVER;DATABASE=MASTER;' + ;  'UID=UserName;PWD=StrongPassword') IF gnConnHandle>0 *!* Query to determine whether SELECT INTO and Fast Bulk Copy are allowed sqlcommand="sp_dboption 'pubs','select into/bulkcopy'" =sqlexec(gnConnHandle,sqlcommand,'bulkcopy') sqlcommand="IF EXISTS (SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE " + ;     "table_name = 'mytable') " +CHR(13) + CHR(10) + "DROP TABLE mytable" =sqlexec(gnConnHandle,sqlcommand) IF UPPER(ALLTRIM(BULKCOPY.CURRENTSETTING))="OFF" *!* SELECT INTO and Fast Bulk Copy not allowed by default. *!* Enable SELECT INTO and Fast Bulk Copy in the selected database sqlcommand="EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'true'" =sqlexec(gnConnHandle,sqlcommand) ENDIF *!* Use the PUBS database sqlcommand="USE pubs" =sqlexec(gnConnHandle,sqlcommand) *!* Issue a SQL Select command and SELECT INTO a new table on SQL Server sqlcommand="SELECT * INTO mytable" + CHR(13)+CHR(10) + ; "FROM authors" =sqlexec(gnConnHandle,sqlcommand) *!* Use the MASTER database sqlcommand="USE master" =sqlexec(gnConnHandle,sqlcommand) IF UPPER(ALLTRIM(BULKCOPY.CURRENTSETTING))="OFF" *!* If SELECT INTO and Fast Bulk Copy were originally disabled, *!* Reset SELECT INTO and Fast Bulk Copy in the selected database to original value sqlcommand="EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'false'" =sqlexec(gnConnHandle,sqlcommand) ENDIF *!* Select from the table that was just created. sqlcommand="select * from pubs..mytable" =sqlexec(gnConnHandle,sqlcommand,'mycursor') =sqldisconn(gnConnHandle) SELECT mycursor BROWSE NOWAIT ENDIF
 * !* the name of the SQL Server you will be connecting with.

Keywords: kbhowto kbsqlprog kbdatabase KB238904

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.