Database Search
If you can't find the information using the categories below, post a question over in our WinBatch Tech Support Forum.
TechHome
System_Data
- CLR Scripts to Create Linked Servers
- Create an XML Schema File from a SQL Server Database
- Get Database Results into a WIL Array and Display
- Insert Access Data to Excel
- Insert Excel Data Into Access
- OleDb GetSchema
- OleDb
Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.
CLR Scripts to Create Linked Servers
Keywords: CLR .Net dotNet Scripts to Create Linked Servers
The following scripts only differ in the connection string and the .NET/CLR classes used for a connection and command object. In the script header are a few comments about how to ensure SQL server will correctly create the links. The Access file and server Database are hard-coded from my testing and you may substitute to test.
;Winbatch 2013C - Using WB CLR to create a linked server
; tested on SQL Server Express 2008 R2
; [uses the System.Data.OleDb namespace]
;
;Linked Servers provide a way to attach non-sql server files, viz.
;Access, Excel, Flat files to be used with the ACE.OLEDB Provider
;
;Stan Littlefield January 14, 2014
;/////////////////////////////////////////////////////////////////////////////////////////
;
;SQL Server Checklist:
; ad-hos queries should be enabled. Starting with 2008, the option to enable may be hidden
; to enable: run these command from SQL query window
; sp_configure 'show advanced options', 1;
; RECONFIGURE;
; sp_configure 'Ad Hoc Distributed Queries', 1;
; RECONFIGURE;
;
; Under Providers (Server Object/Linked Servers) make sure the properties for ACE.OLEDB have Dynamic
; Parameter, allow inprocess and Support 'Like' Operator checked
;
; Otherwise you may receive errors even though the script will appear to create a link
;//////////////////////////////////////////////////////////////////////////////////////////
gosub udfs
IntControl(73,1,0,0,0)
;this is a file I used to test. Also, my SQL Server has a database called Main. By substituting
;your own file and server db, the script should work.
cACCDB="C:\RA\CUSTS\Custs.accdb"
If ! FileExist(cACCDB) Then Terminate(@TRUE,"Cannot Continue","Missing: ":cACCDB)
ObjectClrOption("use","System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089")
svr = ".\SQLEXPRESS"
db = "Main"
sName="ACCDB"
cConn ="Provider=SQLOLEDB.1;Data Source=":svr:";Database=":db:";Integrated Security=SSPI;Persist Security Info=False"
;cn = ObjectClrNew("System.Data.SqlClient.SqlConnection",cConn)
cn = ObjectClrNew("System.Data.OleDb.OleDbConnection",cConn)
cn.Open()
oState = ObjectClrNew( "System.Data.ConnectionState")
If cn.State == oState.Open
cm = ObjectClrNew("System.Data.OleDb.OleDbCommand")
cm.Connection = cn
;cm.CommandTimeout=300 ; only needed if the default of 30 seconds times out
;first check if the linked server doesn't already exist
;if not create it.
;as this requires 2 commands, the udf ensures the reader
;is closed before the udf sends a return value
If !chklink()
setlinkedserver()
Else
Display(4,"Linked Server Already Exists",sName)
Endif
Endif
cm.Dispose()
cn.Dispose()
Exit
:WBERRORHANDLER
dr=0
cm=0
cn=0
ErrorProcessing(0,1,0,wberrorarray)
Exit
:udfs
#DefineSubRoutine setlinkedserver()
cSQL="IF NOT EXISTS ( SELECT 1 FROM sys.sysservers WHERE srvname = '%sName%' )":@CRLF
cSQL=cSQL:"BEGIN":@CRLF
cSQL=cSQL:"USE [Main]":@CRLF
cSQL=cSQL:"EXEC sp_addlinkedserver"
cSQL=cSQL:" @server = '%sName%',"
cSQL=cSQL:" @provider = 'Microsoft.ACE.OLEDB.15.0',"
cSQL=cSQL:" @srvproduct = 'MSAccess',"
cSQL=cSQL:" @datasrc = '%cACCDB%';":@CRLF
cSQL=cSQL:"END"
cm.CommandText = cSQL
cm.ExecuteNonQuery()
Return(1)
#EndSubRoutine
#DefineSubRoutine chklink()
Retval=0
cSQL = "select * from master.dbo.sysservers;"
cm.CommandText = cSQL
dr = cm.ExecuteReader()
While dr.Read()
data=dr.GetString(2)
If StrUpper(StrTrim(data))==sName
Retval=1
Break
Endif
EndWhile
dr.Close()
Return(Retval)
#EndSubRoutine
;Generic Error Handler
#DefineSubroutine ErrorProcessing(deleteIni,showerr,logfile,Err_Array)
If Vartype(wberrorarray) ==256
WbError = Err_Array[0]
wberrorhandlerline = Err_Array[1]
wberrorhandleroffset = Err_Array[2]
wberrorhandlerassignment = Err_Array[3]
wberrorhandlerfile = Err_Array[4]
wberrortextstring = Err_Array[5]
wberroradditionalinfo = Err_Array[6]
wberrorinsegment = Err_Array[7]
Else
WbError = LastError()
Endif
WbTextcode = WbError
If WbError==1668||WbError==2669||WbError==3670
WbError = ItemExtract(1,IntControl(34,-1,0,0,0),":")
WbTextcode = -1
EndIf
WbErrorString = IntControl(34,WbTextcode,0,0,0)
WbErrorDateTime = TimeYmdHms()
If deleteIni
WbErrorFile = StrCat(ShortCutDir( 'AppData', 0, 0 ),'\WinBatch\Settings\')
If ! DirExist(WbErrorFile) Then WbErrorFile = DirWindows(0)
WbErrorFile = StrCat(WbErrorFile,"WWWBATCH.INI")
FileDelete(WbErrorFile)
IniWritePvt(WbErrorDateTime,"CurrentScript",WbErrorHandlerFile ,WbErrorFile)
IniWritePvt(WbErrorDateTime,"ErrorValue" ,WbError ,WbErrorFile)
IniWritePvt(WbErrorDateTime,"ErrorString" ,WbErrorString ,WbErrorFile)
IniWritePvt(WbErrorDateTime,"ScriptLine" ,WbErrorHandlerLine ,WbErrorFile)
IniWritePvt(WbErrorDateTime,"ScriptOffset" ,WbErrorHandlerOffset ,WbErrorFile)
IniWritePvt(WbErrorDateTime,"VarAssignment",WbErrorHandlerAssignment,WbErrorFile)
IniWritePvt(WbErrorDateTime,"VarInSegment" ,WbErrorInSegment,WbErrorFile)
IniWritePvt("","","",WbErrorFile)
Endif
WbErrorMsgText = StrCat(WbErrorDateTime,@CRLF)
WbErrorMsgText = StrCat(WbErrorMsgText,"Current Script: ",WbErrorHandlerFile,@CRLF)
WbErrorMsgText = StrCat(WbErrorMsgText,"Error# [",WbError,"]",@CRLF)
WbErrorMsgText = StrCat(WbErrorMsgText,"Error Text: ",wberrortextstring,@CRLF)
WbErrorMsgText = StrCat(WbErrorMsgText,"[Extended Information] ",wberroradditionalinfo,@CRLF,@CRLF)
WbErrorMsgText = StrCat(WbErrorMsgText,"On Line:",@CRLF,WbErrorHandlerLine,@CRLF)
;WbErrorMsgText = StrCat(WbErrorMsgText,"Offset: ",WbErrorHandlerOffset,@CRLF)
If (WbErrorHandlerAssignment>"") Then %WbErrorHandlerAssignment% = "UNKNOWN"
WbErrorMsgText = StrCat(WbErrorMsgText,"Assignment/Variable: ",WbErrorHandlerAssignment,@CRLF)
If (WbErrorInSegment>"") Then WbErrorMsgText = StrCat(WbErrorMsgText,"In UDF/UDS: ",WbErrorInSegment,@CRLF)
If logfile
cSep = StrCat(StrFill("=",50),@CRLF)
cLogFile = StrCat(dirscript(),"log.err")
If ! FileExist(cLogFile) Then FilePut(cLogFile,StrCat("Error Log",@CRLF,cSep))
FilePut(cLogFile,StrCat(FileGet(cLogFile),WbErrorMsgText,cSep))
display(2,"An Error Occured",StrCat("written to ",cLogFile))
Else
If showerr
WbErrorMsgText = StrCat(WbErrorMsgText,"[THIS ERROR NOT WRITTEN TO LOG FILE]",@CRLF)
Message("An Error Was Encountered",WbErrorMsgText)
Endif
Endif
Return(1)
#EndSubroutine
Return
;/////////////////// extra stuff ///////////////////////////////////
;command to drop the linked server if it exists
;"EXEC sp_dropserver @server= '%svr%', @droplogins='droplogins'"
;sql to get data from table in linked server
;select * from openquery(ACCDB, 'SELECT * FROM HD2011')
;Winbatch 2013C - Using WB CLR to create a linked server
; tested on SQL Server Express 2008 R2
; [uses the System.Data.SqlClient namespace]
;
;Linked Servers provide a way to attach non-sql server files, viz.
;Access, Excel, Flat files to be used with the ACE.OLEDB Provider
;
;Stan Littlefield January 14, 2014
;/////////////////////////////////////////////////////////////////////////////////////////
;
;SQL Server Checklist:
; ad-hos queries should be enabled. Starting with 2008, the option to enable may be hidden
; to enable: run these command from SQL query window
; sp_configure 'show advanced options', 1;
; RECONFIGURE;
; sp_configure 'Ad Hoc Distributed Queries', 1;
; RECONFIGURE;
;
; Under Providers (Server Object/Linked Servers) make sure the properties for ACE.OLEDB have Dynamic
; Parameter, allow inprocess and Support 'Like' Operator checked
;
; Otherwise you may receive errors even though the script will appear to create a link
;//////////////////////////////////////////////////////////////////////////////////////////
gosub udfs
IntControl(73,1,0,0,0)
;this is a file I used to test. Also, my SQL Server has a database called Main. By substituting
;your own file and server db, the script should work.
cACCDB="C:\RA\CUSTS\Custs.accdb"
If ! FileExist(cACCDB) Then Terminate(@TRUE,"Cannot Continue","Missing: ":cACCDB)
ObjectClrOption("use","System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089")
svr = ".\SQLEXPRESS"
db = "Main"
sName="ACCDB"
cConn ="Server=":svr:";Database=":db:";Trusted_Connection=yes;"
;The System.Data.SqlClient namespace is built into System.Data and is designed to work
;with SQL Server. It is more robust than the OLEDB classes, offering more options for working
;with T-SQL transactions.
cn = ObjectClrNew("System.Data.SqlClient.SqlConnection",cConn)
cn.Open()
oState = ObjectClrNew( "System.Data.ConnectionState")
If cn.State == oState.Open
cm = ObjectClrNew("System.Data.SqlClient.SqlCommand")
cm.Connection = cn
;cm.CommandTimeout=300 ; only needed if the default of 30 seconds times out
;first check if the linked server doesn't already exist
;if not create it.
;as this requires 2 commands, the udf ensures the reader
;is closed before the udf sends a return value
If !chklink()
setlinkedserver()
Else
Display(4,"Linked Server Already Exists",sName)
Endif
Endif
cm.Dispose()
cn.Dispose()
Exit
:WBERRORHANDLER
dr=0
cm=0
cn=0
ErrorProcessing(0,1,0,wberrorarray)
Exit
:udfs
#DefineSubRoutine setlinkedserver()
cSQL="IF NOT EXISTS ( SELECT 1 FROM sys.sysservers WHERE srvname = '%sName%' )":@CRLF
cSQL=cSQL:"BEGIN":@CRLF
cSQL=cSQL:"USE [Main]":@CRLF
cSQL=cSQL:"EXEC sp_addlinkedserver"
cSQL=cSQL:" @server = '%sName%',"
cSQL=cSQL:" @provider = 'Microsoft.ACE.OLEDB.15.0',"
cSQL=cSQL:" @srvproduct = 'MSAccess',"
cSQL=cSQL:" @datasrc = '%cACCDB%';":@CRLF
cSQL=cSQL:"END"
cm.CommandText = cSQL
cm.ExecuteNonQuery()
Return(1)
#EndSubRoutine
#DefineSubRoutine chklink()
Retval=0
cSQL = "select * from master.dbo.sysservers;"
cm.CommandText = cSQL
dr = cm.ExecuteReader()
While dr.Read()
data=dr.GetString(2)
If StrUpper(StrTrim(data))==sName
Retval=1
Break
Endif
EndWhile
dr.Close()
Return(Retval)
#EndSubRoutine
;Generic Error Handler
#DefineSubroutine ErrorProcessing(deleteIni,showerr,logfile,Err_Array)
If Vartype(wberrorarray) ==256
WbError = Err_Array[0]
wberrorhandlerline = Err_Array[1]
wberrorhandleroffset = Err_Array[2]
wberrorhandlerassignment = Err_Array[3]
wberrorhandlerfile = Err_Array[4]
wberrortextstring = Err_Array[5]
wberroradditionalinfo = Err_Array[6]
wberrorinsegment = Err_Array[7]
Else
WbError = LastError()
Endif
WbTextcode = WbError
If WbError==1668||WbError==2669||WbError==3670
WbError = ItemExtract(1,IntControl(34,-1,0,0,0),":")
WbTextcode = -1
EndIf
WbErrorString = IntControl(34,WbTextcode,0,0,0)
WbErrorDateTime = TimeYmdHms()
If deleteIni
WbErrorFile = StrCat(ShortCutDir( 'AppData', 0, 0 ),'\WinBatch\Settings\')
If ! DirExist(WbErrorFile) Then WbErrorFile = DirWindows(0)
WbErrorFile = StrCat(WbErrorFile,"WWWBATCH.INI")
FileDelete(WbErrorFile)
IniWritePvt(WbErrorDateTime,"CurrentScript",WbErrorHandlerFile ,WbErrorFile)
IniWritePvt(WbErrorDateTime,"ErrorValue" ,WbError ,WbErrorFile)
IniWritePvt(WbErrorDateTime,"ErrorString" ,WbErrorString ,WbErrorFile)
IniWritePvt(WbErrorDateTime,"ScriptLine" ,WbErrorHandlerLine ,WbErrorFile)
IniWritePvt(WbErrorDateTime,"ScriptOffset" ,WbErrorHandlerOffset ,WbErrorFile)
IniWritePvt(WbErrorDateTime,"VarAssignment",WbErrorHandlerAssignment,WbErrorFile)
IniWritePvt(WbErrorDateTime,"VarInSegment" ,WbErrorInSegment,WbErrorFile)
IniWritePvt("","","",WbErrorFile)
Endif
WbErrorMsgText = StrCat(WbErrorDateTime,@CRLF)
WbErrorMsgText = StrCat(WbErrorMsgText,"Current Script: ",WbErrorHandlerFile,@CRLF)
WbErrorMsgText = StrCat(WbErrorMsgText,"Error# [",WbError,"]",@CRLF)
WbErrorMsgText = StrCat(WbErrorMsgText,"Error Text: ",wberrortextstring,@CRLF)
WbErrorMsgText = StrCat(WbErrorMsgText,"[Extended Information] ",wberroradditionalinfo,@CRLF,@CRLF)
WbErrorMsgText = StrCat(WbErrorMsgText,"On Line:",@CRLF,WbErrorHandlerLine,@CRLF)
;WbErrorMsgText = StrCat(WbErrorMsgText,"Offset: ",WbErrorHandlerOffset,@CRLF)
If (WbErrorHandlerAssignment>"") Then %WbErrorHandlerAssignment% = "UNKNOWN"
WbErrorMsgText = StrCat(WbErrorMsgText,"Assignment/Variable: ",WbErrorHandlerAssignment,@CRLF)
If (WbErrorInSegment>"") Then WbErrorMsgText = StrCat(WbErrorMsgText,"In UDF/UDS: ",WbErrorInSegment,@CRLF)
If logfile
cSep = StrCat(StrFill("=",50),@CRLF)
cLogFile = StrCat(dirscript(),"log.err")
If ! FileExist(cLogFile) Then FilePut(cLogFile,StrCat("Error Log",@CRLF,cSep))
FilePut(cLogFile,StrCat(FileGet(cLogFile),WbErrorMsgText,cSep))
display(2,"An Error Occured",StrCat("written to ",cLogFile))
Else
If showerr
WbErrorMsgText = StrCat(WbErrorMsgText,"[THIS ERROR NOT WRITTEN TO LOG FILE]",@CRLF)
Message("An Error Was Encountered",WbErrorMsgText)
Endif
Endif
Return(1)
#EndSubroutine
Return
;/////////////////// extra stuff ///////////////////////////////////
;command to drop the linked server if it exists
;"EXEC sp_dropserver @server= '%svr%', @droplogins='droplogins'"
;sql to get data from table in linked server
;select * from openquery(ACCDB, 'SELECT * FROM HD2011')
Article ID: W17809
Filename: CLR Scripts to Create Linked Servers .txt
File Created: 2014:01:14:09:45:20
Last Updated: 2014:01:14:09:45:20