How to export a data to SQL server via ODBC?

[Wenjun Chen]

Hi,
I am trying to export data to SQL server through ODBC connection. I was able to load the data table through ODBC and do some data manipulation. However, it didn't work well when I tried to save the data to SQL server through ODBC. My code below was able to create the data table "SP_GE_cohort" under SP_Data and I could see variable names, but the data cells were all blank, no values. Then I tried to run another same code but replacing -create- with -overwrite- as an option, the program basically froze for hours and didn't run. Could you please help to provide a way to save file into SQL server? Thank you!

odbc insert, table("SP_cohort") dsn("SP_data_server") create


Best,

Wenjun

[Joseph Coveney]

. . . it didn't work well when I tried to save the data to SQL server through ODBC. My code below was able to create the data table "SP_GE_cohort" under SP_Data and I could see variable names, but the data cells were all blank, no values. Then I tried to run another same code but replacing -create- with -overwrite- as an option, the program basically froze for hours and didn't run. . . .

odbc insert, table("SP_cohort") dsn("SP_data_server") create

The create option for odbc insert has been deprecated for a long time. You won't even see it as an option in the help file anymore.

The recommendation now is to first separately create the table using SQL via an odbc exec() or odbc sqlfile() command in Stata, or via Microsoft SQL Server Management Studio (or Azure Data Studio). And then populate the table with odbc insert, . . . insert.

An example of this approach is shown in the code section below. I have a database on my development MSSQL Server instance called Sandbox, and so you'd need to substitute your database's name (SP_Data?) as well as your DSN in order to run it in your setup.

version 18.0

clear *

#delimit ;
local sql_statement
    CREATE TABLE Sandbox.dbo.Automobiles (
        make VARCHAR(100) NOT NULL PRIMARY KEY,
        price INTEGER NOT NULL,
        mpg INTEGER NOT NULL,
        rep78 INTEGER NOT NULL,
        headroom FLOAT NOT NULL,
        trunk INTEGER NOT NULL,
        weight INTEGER NOT NULL,
        length INTEGER NOT NULL,
        turn INTEGER NOT NULL,
        displacement INTEGER NOT NULL,
        gear_ratio FLOAT NOT NULL,
        nondomestic SMALLINT NOT NULL);
#delimit cr

odbc exec("`sql_statement';"), dsn(Sandbox13)

quietly sysuse auto
rename foreign nondomestic
mvencode rep78, mv(99)

odbc insert, table(Sandbox.dbo.Automobiles) dsn(Sandbox13) sqlshow

// Confirmation
local last = make[_N]
odbc exec("SELECT * FROM Sandbox.dbo.Automobiles WHERE make = '`last'';"), ///
    dsn(Sandbox13)

odbc exec("DROP TABLE Sandbox.dbo.Automobiles;"), dsn(Sandbox13)

exit

[Wenjun Chen]

Hi Joseph-

Thank you for the codes. I tried to modify your codes into my data setting by the three steps and got the results as shown below, but the data seem still not to be able to insert. Is there anything I did wrong?


*** Step 1: use -odbc exec- to create a data table named "SPED_2013_20" ***

clear *

.
. #delimit ;
delimiter now ;
. local sql_statement
> CREATE TABLE SPED_2013_20 (
> SchoolYear INTEGER NOT NULL,
> ID VARCHAR(10) NOT NULL,
> GradeLevel VARCHAR(2) NOT NULL,
> isImmigrant FLOAT NOT NULL,
> race INTEGER NOT NULL,
> speakOtherLangAtHome FLOAT NOT NULL,
> primLangNotEng FLOAT NOT NULL,
> male FLOAT NOT NULL,
> lep FLOAT NOT NULL,
> frpl FLOAT NOT NULL,
> edFlag FLOAT NOT NULL,
> plan504 FLOAT NOT NULL);

. #delimit cr
delimiter now cr
.
. odbc exec("`sql_statement';"), dsn(SPEDC_Data)



*** STEP 2: use - odbc load - to read in data and generate variables as listed in STEP 1 ****
(I am skipping pasting the lengthy log file here. There was no error from generating those variables)


*** STEP 3: use - odbc insert - to insert data value into table generated in STEP 1 ****

. odbc insert, table(SPED_2013_20) dsn(SPEDC_Data) sqlshow

INSERT INTO SPED_2013_20 (SchoolYear, ID, GradeLevel, race, speakOther
> LangAtHome, primLangNotEng, male, lep, frpl, edFlag, plan504, isImm
> igrant) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
The ODBC driver reported the following diagnostics
[Microsoft][ODBC SQL Server Driver][SQL Server]The EXECUTE permission was denied
on the object 'sp_execute', database 'mssqlsystemresource', schema 'sys'.
SQLSTATE=42000
r(682);

[Joseph Coveney]

. . . the data seem still not to be able to insert. Is there anything I did wrong?

Thanks for showing the output.

You're not doing anything wrong, per se, but the process of inserting the data into your table via odbc insert invokes an EXECUTE statement. The problem is that you don't have permission to invoke an EXECUTE statement on the system database and schema that's tangentially involved in the insertion into your user database.

I think that you'll need to talk with your database administrator to work things out with that; it's not a Stata matter.

There is a workaround that I've used on the rare occasion when I've had trouble with odbc insert, but it's not pretty: it involves loading up an INSERT INTO SQL statement and executing that via odbc exec() observation-by-observation in a loop down your dataset. But I'm not sure that this will bypass your problem with EXECUTE permission on the system database, and I recommend contacting your database administrator as the preferred approach.

[Wenjun Chen]

I see. Thank you! I'll contact our server administrator.