You might also consider investing in StatTransfer.
I had to find an easy, automated SQL-server-to-Stata setup recently,
and I ended up using StatTransfer batch files, executed from a Stata
do-file.
The key is the StatTransfer commands -dbr- and -dbw- (db read/write)
with the connstr option (connect string). With connstr you can store
the ODBC connection information, including password, in an encrypted
file.
Example:
//====== Begin ImportDataFromSQLServer.stcmd =============//
log using ImportDataFromSQLServer.txt
dbr connstr <# ConnectStr_ODBC.str
dbr table PATIENTView
copy ODBC Data_org\PATIENTView.dta /y
dbr table dugastandard
copy ODBC Data_org\dugastandard.dta /y
[...]
quit
//======== End ImportDataFromSQLServer.stcmd =============//
//================ Begin import.do =======================//
shell $StatTransfer_path ImportDataFromSQLServer.stcmd
type ImportDataFromSQLServer.txt , asis
//================== End import.do =======================//
You have to create the connstr file interactively before first use.
See the StatTransfer manual for more information.
- Jesper K. Hansen
2007/12/14, Joseph Coveney <[email protected]>:
> I wrote:
>
> I haven't tested this out, and so it might need a little tweaking.
>
> --------------------------------------------------------------------------------
>
> The do-file below should help in any tweaking efforts. For use, substitute
> "odbc" for "display in smcl as result" in the program.
>
> Joseph Coveney
>
> clear *
> set more off
> capture program drop myodbc
> program define myodbc
> version 10
> syntax anything(everything) [, *]
> capture assert "$us" == ""
> if !_rc display in smcl as text "What's your user ID" _request(us)
> capture assert "$pw" == ""
> if !_rc display in smcl as text "What's your password" _request(pw)
> if (`"`anything'"' == "list") display in smcl as result "odbc list"
> else display in smcl as result ///
> "odbc " `"`anything'"' ", " `"`options'"' " user($us) password($pw)"
> end
> *
> local dsn MyDSN
> local file C:/Files/My File.SQL
> myodbc list
> myodbc query "`dsn'", dialog(required)
> myodbc describe "[My Table]", dsn("`dsn'") dialog(complete)
> myodbc load here there everywhere ///
> if nowhere == "nothing", table("[My Table]")
> myodbc load , exec("SELECT * FROM [My Table]") dsn("`dsn'")
> myodbc sqlfile("`file'"), dsn("`dsn'")
> myodbc exec("SELECT a, b FROM [My Table]"), dsn("`dsn'")
> exit
>
>
> *
> * For searches and help try:
> * http://www.stata.com/support/faqs/res/findit.html
> * http://www.stata.com/support/statalist/faq
> * http://www.ats.ucla.edu/stat/stata/
>
>
*
* For searches and help try:
* http://www.stata.com/support/faqs/res/findit.html
* http://www.stata.com/support/statalist/faq
* http://www.ats.ucla.edu/stat/stata/
