CODEWITHSUNDEEP
sqlstata
Tosha Kurzynske Fraley
Tosha Kurzynske Fraley

Reputation: 13

Running SQL in Stata

I am trying to load data from SQL server management studio into Stata. How do I get Stata to run the .sql file? I have used the -ado- procedure from another post, but it does not work because my database has a username and password.

Original -ado- code:

program define loadsql
*! Load the output of an SQL file into Stata, version 1.2 ([email protected])
version 12.1
syntax using/, DSN(string) [CLEAR NOQuote LOWercase SQLshow ALLSTRing DATESTRing]

#delimit;
tempname mysqlfile exec line;

file open `mysqlfile' using `"`using'"', read text;
file read `mysqlfile' `line';

while r(eof)==0 {;
    local `exec' `"``exec'' ``line''"';
    file read `mysqlfile' `line';
};

file close `mysqlfile';


odbc load, exec(`"``exec''"') dsn(`"`dsn'"') `clear' `noquote' `lowercase' `sqlshow' `allstring' `datestring';

end;

Upvotes: 0

Views: 4459

Answers (1)

ander2ed
ander2ed

Reputation: 1338

help odbc discusses connect_options for connecting to odbc data sources. Two of which are u(userId) and p(password) which can be added to the original code written by @Dimitriy V. Masterov (see post here).

I believe you should be able to connect using SQL Server authentication by adding the u(string) and p(string) as additional options following syntax in the ado file, and then again down below following 

 odbc load, exec(`"``exec''"') dsn(`"`dsn'"')

This would also require that you pass these arguments to the program when you call it:

loadsql using "./sqlfile.sql", dsn("mysqlodbcdata") u(userId) p(Password)

Upvotes: 1

Related Questions