Skip to content
+1-888-319-3663

COMMUNITY FORUM

VB Scripting using ODBC Connection

David Dery asked 6 years ago
Building a map using SC2014 with GP2015, SQL Server 2012.  Data source is SQL server 2000.  I installed the SQL 2008 ODBC driver so I am able to pull from the old server.  But I have a pre-task that loads a staging table by executing a stored procedure (the staging is what is called via the ODBC).  In the script task, if I try and do this as a SQL connection, it fails because it seems to be only trying to scan the local SQL server sys.sysdatabase and is not opening the connection to the old server.  Assume this is driver based somehow.  
So, I figure I could do this in VB.  However, every attempt at a connection string fails because in the string it appears that SmartConnect will not allow me to define the driver/provider as part of the connection.  Presumably, by default it uses the “local driver” so it fails.  I have also attempted calling the working ODBC (as mentioned above) in the VB script since this is the exact same connection, but this also fails, because the default connection ODBC defines “driver” so this fails just as above.  
Error in both instances: keyword not supported ‘driver’ OR keyword not supported ‘provider’
Here are a couple of my failed attempts at the connections, any advice here is greatly appreciated.
-Dave
____________________________________________________________________________________________________________
‘Declare the connection string based on the configuration variables
Dim conString As New String(“Driver=SQLNCLI10; Data Source=APP-BILLING; Initial Catalog= GPIntegrations; User=sa; Password=password;”)

‘Define the query command to be run
Dim SQL As String = “exec sp_GPIntegration_InventoryJE ‘” & GBL_LOCATION & “‘, ‘” & GBL_START_DATE & “‘, ‘” & GBL_END_DATE & “‘, ‘” & GBL_POST_DATE & “‘”
‘Define the SQL connection and open it
Dim myConn As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(conString)
myConn.Open()
‘Declare the SQL command as the query string
Dim myCmd As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(SQL, myConn)

myCmd.CommandType = System.Data.CommandType.StoredProcedure
‘Close the connection
myConn.Close()
Return True
____________________________________________________________________________________________________________
‘messagebox.show(_GODBC_APP_BILLING)

‘Define the query command to be run
Dim SQL As String = “exec sp_GPIntegration_InventoryJE ‘” & GBL_LOCATION & “‘, ‘” & GBL_START_DATE & “‘, ‘” & GBL_END_DATE & “‘, ‘” & GBL_POST_DATE & “‘”
‘Define the SQL connection and open it
‘ Dim myConn As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(_GODBC_APP_BILLING)
Dim myConn as OdbcConnection = New OdbcConnection(_GODBC_APP_BILLING)
Dim cmd As OdbcCommand = New OdbcCommand(SQL)
myConn .Open()
‘Declare the SQL command as the query string
‘ Dim myCmd As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(SQL, myConn)
‘Close the connection
myConn.Close()
Return True
patrick replied 6 years ago

if your connection string is wrong (ie .net can’t figure out how to get to where you are trying to do) that is the kind of error I’d expect to see.

i’d take a look at the connectionstring for this here
http://www.connectionstrings.com/sql-server-native-client-10-0-odbc-driver/

David Dery replied 6 years ago

If you review connectionstrings.com, you will see that almost all of them dictate to use either “driver” or “provider” in the string, which Smartconnect cannot validate because it doesn’t understand the terms. Additionally, if I try to use standard VB syntax and call OdbcConnection or OdbcCommand, SmartConnect also cannot validate because it doesn’t not understand the System.Data.Odbc syntax and it will not let me load them either.

Dave Dery replied 6 years ago

At another customer, just tried using a pre-validate task and modify the query, and even the built in connections are failing to open the ODBC because presumably you are not loading the required dll’s in the application, this would explain the issues I am having above.

************** Exception Text **************
System.Data.Odbc.OdbcException (0x80131937): ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, OdbcConnectionString constr, OdbcEnvironmentHandle environmentHandle)
at System.Data.Odbc.OdbcConnectionOpen..ctor(OdbcConnection outerConnection, OdbcConnectionString connectionOptions)
at System.Data.Odbc.OdbcConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.Odbc.OdbcConnection.Open()
at eOne.SmartConnect.Engine.QueryBuilder.ODBCMetadataProvider.DoConnect()
at eOne.SmartConnect.Engine.QueryBuilder.BaseMetadataProvider.Connect()
at eOne.SmartConnect.Engine.QueryBuilder.LoadMetadataHelpers.LoadMetadataObjectsMSSQL(BaseSQLContext sqlContext, MetadataContainer metadataContainer, MetadataFilter metadataFilter, SQLQualifiedName database)
at eOne.SmartConnect.Engine.QueryBuilder.MSSQLSyntaxProvider.LoadMetadataObjects(BaseSQLContext sqlContext, MetadataContainer metadataContainer, MetadataFilter metadataFilter, SQLQualifiedName database)
at eOne.SmartConnect.Engine.QueryBuilder.BaseSQLContext.LoadMetadataObjects(MetadataContainer metadataContainer, MetadataFilter metadataFilter, SQLQualifiedName database)
at eOne.SmartConnect.Engine.QueryBuilder.MetadataContainer.LoadMetadataByFilter(MetadataFilter metadataFilter, SQLQualifiedName database)
at eOne.SmartConnect.Engine.QueryBuilder.QueryBuilder.RefreshMetadata()
at eOne.SmartConnect.UI.FormQueryBuilder.InitializeUi()
at eOne.SmartConnect.UI.FormQueryBuilder.Form1_Load(Object sender, EventArgs e)
at System.EventHandler.Invoke(Object sender, EventArgs e)
at System.Windows.Forms.Form.OnLoad(EventArgs e)
at DevExpress.XtraEditors.XtraForm.OnLoad(EventArgs e)
at System.Windows.Forms.Form.OnCreateControl()
at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
at System.Windows.Forms.Control.CreateControl()
at System.Windows.Forms.Control.WmShowWindow(Message& m)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
at System.Windows.Forms.ContainerControl.WndProc(Message& m)
at System.Windows.Forms.Form.WmShowWindow(Message& m)
at System.Windows.Forms.Form.WndProc(Message& m)
at DevExpress.XtraEditors.XtraForm.WndProc(Message& msg)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

************** Loaded Assemblies **************
mscorlib
Assembly Version: 4.0.0.0
Win32 Version: 4.0.30319.34014 built by: FX45W81RTMGDR
CodeBase: file:///C:/Windows/Microsoft.NET/Framework/v4.0.30319/mscorlib.dll
—————————————-
eOne.SmartConnect.UI.External
Assembly Version: 20.14.1.25
Win32 Version: 20.14.1.25
CodeBase: file:///C:/Program%20Files%20(x86)/eOne%20Solutions/SmartConnect/eOne.SmartConnect.UI.External.exe
—————————————-
System.Windows.Forms
Assembly Version: 4.0.0.0
Win32 Version: 4.0.30319.34250 built by: FX452RTMGDR
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Windows.Forms/v4.0_4.0.0.0__b77a5c561934e089/System.Windows.Forms.dll
—————————————-
System.Drawing
Assembly Version: 4.0.0.0
Win32 Version: 4.0.30319.33440 built by: FX45W81RTMREL
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Drawing/v4.0_4.0.0.0__b03f5f7f11d50a3a/System.Drawing.dll
—————————————-
System
Assembly Version: 4.0.0.0
Win32 Version: 4.0.30319.34239 built by: FX452RTMGDR
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System/v4.0_4.0.0.0__b77a5c561934e089/System.dll
—————————————-
DevExpress.Utils.v13.1
Assembly Version: 13.1.5.0
Win32 Version: 13.1.5.0
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/DevExpress.Utils.v13.1/v4.0_13.1.5.0__b88d1754d700e49a/DevExpress.Utils.v13.1.dll
—————————————-
DevExpress.BonusSkins.v13.1
Assembly Version: 13.1.5.0
Win32 Version: 13.1.5.0
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/DevExpress.BonusSkins.v13.1/v4.0_13.1.5.0__b88d1754d700e49a/DevExpress.BonusSkins.v13.1.dll
—————————————-
eOne.SmartConnect.Engine
Assembly Version: 20.14.1.25
Win32 Version: 20.14.1.25
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/eOne.SmartConnect.Engine/v4.0_20.14.1.25__0beb756d2a6b4456/eOne.SmartConnect.Engine.dll
—————————————-
DevExpress.XtraBars.v13.1
Assembly Version: 13.1.5.0
Win32 Version: 13.1.5.0
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/DevExpress.XtraBars.v13.1/v4.0_13.1.5.0__b88d1754d700e49a/DevExpress.XtraBars.v13.1.dll
—————————————-
DevExpress.XtraEditors.v13.1
Assembly Version: 13.1.5.0
Win32 Version: 13.1.5.0
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/DevExpress.XtraEditors.v13.1/v4.0_13.1.5.0__b88d1754d700e49a/DevExpress.XtraEditors.v13.1.dll
—————————————-
DevExpress.Data.v13.1
Assembly Version: 13.1.5.0
Win32 Version: 13.1.5.0
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/DevExpress.Data.v13.1/v4.0_13.1.5.0__b88d1754d700e49a/DevExpress.Data.v13.1.dll
—————————————-
System.Xml
Assembly Version: 4.0.0.0
Win32 Version: 4.0.30319.34230 built by: FX452RTMGDR
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Xml/v4.0_4.0.0.0__b77a5c561934e089/System.Xml.dll
—————————————-
System.Configuration
Assembly Version: 4.0.0.0
Win32 Version: 4.0.30319.33440 built by: FX45W81RTMREL
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Configuration/v4.0_4.0.0.0__b03f5f7f11d50a3a/System.Configuration.dll
—————————————-
System.Core
Assembly Version: 4.0.0.0
Win32 Version: 4.0.30319.33440 built by: FX45W81RTMREL
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Core/v4.0_4.0.0.0__b77a5c561934e089/System.Core.dll
—————————————-
eOne.SmartConnect.Common
Assembly Version: 20.14.1.25
Win32 Version: 20.14.1.25
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/eOne.SmartConnect.Common/v4.0_20.14.1.25__0beb756d2a6b4456/eOne.SmartConnect.Common.dll
—————————————-
System.Data
Assembly Version: 4.0.0.0
Win32 Version: 4.0.30319.33440 built by: FX45W81RTMREL
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_32/System.Data/v4.0_4.0.0.0__b77a5c561934e089/System.Data.dll
—————————————-
eOne.SmartConnect.Connectors.Crm2011
Assembly Version: 20.14.1.25
Win32 Version: 20.14.1.25
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/eOne.SmartConnect.Connectors.Crm2011/v4.0_20.14.1.25__0beb756d2a6b4456/eOne.SmartConnect.Connectors.Crm2011.dll
—————————————-
Microsoft.Xrm.Sdk
Assembly Version: 6.0.0.0
Win32 Version: 6.0.0000.0800
CodeBase: file:///C:/Program%20Files%20(x86)/eOne%20Solutions/SmartConnect/Microsoft.Xrm.Sdk.DLL
—————————————-
DevExpress.XtraGrid.v13.1
Assembly Version: 13.1.5.0
Win32 Version: 13.1.5.0
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/DevExpress.XtraGrid.v13.1/v4.0_13.1.5.0__b88d1754d700e49a/DevExpress.XtraGrid.v13.1.dll
—————————————-
DevExpress.XtraTreeList.v13.1
Assembly Version: 13.1.5.0
Win32 Version: 13.1.5.0
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/DevExpress.XtraTreeList.v13.1/v4.0_13.1.5.0__b88d1754d700e49a/DevExpress.XtraTreeList.v13.1.dll
—————————————-
eOne.SmartConnect.RemoteLoader
Assembly Version: 20.14.1.25
Win32 Version: 20.14.1.25
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/eOne.SmartConnect.RemoteLoader/v4.0_20.14.1.25__0beb756d2a6b4456/eOne.SmartConnect.RemoteLoader.dll
—————————————-
System.Xml.Linq
Assembly Version: 4.0.0.0
Win32 Version: 4.0.30319.33440 built by: FX45W81RTMREL
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Xml.Linq/v4.0_4.0.0.0__b77a5c561934e089/System.Xml.Linq.dll
—————————————-
System.Runtime.Serialization
Assembly Version: 4.0.0.0
Win32 Version: 4.0.30319.34230 built by: FX452RTMGDR
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Runtime.Serialization/v4.0_4.0.0.0__b77a5c561934e089/System.Runtime.Serialization.dll
—————————————-
eOne.SmartConnect.Connectors.Generic
Assembly Version: 20.14.1.25
Win32 Version: 20.14.1.25
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/eOne.SmartConnect.Connectors.Generic/v4.0_20.14.1.25__0beb756d2a6b4456/eOne.SmartConnect.Connectors.Generic.dll
—————————————-
eOne.SmartConnect.Connectors.Microsoft.Dynamics.Crm
Assembly Version: 20.14.1.25
Win32 Version: 20.14.1.25
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/eOne.SmartConnect.Connectors.Microsoft.Dynamics.Crm/v4.0_20.14.1.25__0beb756d2a6b4456/eOne.SmartConnect.Connectors.Microsoft.Dynamics.Crm.dll
—————————————-
System.Web.Services
Assembly Version: 4.0.0.0
Win32 Version: 4.0.30319.33440 built by: FX45W81RTMREL
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Web.Services/v4.0_4.0.0.0__b03f5f7f11d50a3a/System.Web.Services.dll
—————————————-
eOne.SmartConnect.Connectors.Microsoft.Dynamics.Gp
Assembly Version: 20.14.1.25
Win32 Version: 20.14.1.25
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/eOne.SmartConnect.Connectors.Microsoft.Dynamics.Gp/v4.0_20.14.1.25__0beb756d2a6b4456/eOne.SmartConnect.Connectors.Microsoft.Dynamics.Gp.dll
—————————————-
eOne.SmartConnect.Connectors.SalesForce
Assembly Version: 20.14.1.25
Win32 Version: 20.14.1.25
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/eOne.SmartConnect.Connectors.SalesForce/v4.0_20.14.1.25__0beb756d2a6b4456/eOne.SmartConnect.Connectors.SalesForce.dll
—————————————-
eOne.SmartConnect.Connectors.WebService
Assembly Version: 20.14.1.25
Win32 Version: 20.14.1.25
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/eOne.SmartConnect.Connectors.WebService/v4.0_20.14.1.25__0beb756d2a6b4456/eOne.SmartConnect.Connectors.WebService.dll
—————————————-
System.Web
Assembly Version: 4.0.0.0
Win32 Version: 4.0.30319.34248 built by: FX452RTMGDR
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_32/System.Web/v4.0_4.0.0.0__b03f5f7f11d50a3a/System.Web.dll
—————————————-
System.Transactions
Assembly Version: 4.0.0.0
Win32 Version: 4.0.30319.33440 built by: FX45W81RTMREL
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_32/System.Transactions/v4.0_4.0.0.0__b77a5c561934e089/System.Transactions.dll
—————————————-
System.ServiceModel
Assembly Version: 4.0.0.0
Win32 Version: 4.0.30319.34230 built by: FX452RTMGDR
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.ServiceModel/v4.0_4.0.0.0__b77a5c561934e089/System.ServiceModel.dll
—————————————-
System.Data.OracleClient
Assembly Version: 4.0.0.0
Win32 Version: 4.0.30319.33440 built by: FX45W81RTMREL
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_32/System.Data.OracleClient/v4.0_4.0.0.0__b77a5c561934e089/System.Data.OracleClient.dll
—————————————-
Anonymously Hosted DynamicMethods Assembly
Assembly Version: 0.0.0.0
Win32 Version: 4.0.30319.34014 built by: FX45W81RTMGDR
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_32/mscorlib/v4.0_4.0.0.0__b77a5c561934e089/mscorlib.dll
—————————————-
Microsoft.GeneratedCode
Assembly Version: 1.0.0.0
Win32 Version: 4.0.30319.34230 built by: FX452RTMGDR
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Xml/v4.0_4.0.0.0__b77a5c561934e089/System.Xml.dll
—————————————-
Microsoft.GeneratedCode
Assembly Version: 1.0.0.0
Win32 Version: 4.0.30319.34230 built by: FX452RTMGDR
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Xml/v4.0_4.0.0.0__b77a5c561934e089/System.Xml.dll
—————————————-
System.EnterpriseServices
Assembly Version: 4.0.0.0
Win32 Version: 4.0.30319.33440 built by: FX45W81RTMREL
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_32/System.EnterpriseServices/v4.0_4.0.0.0__b03f5f7f11d50a3a/System.EnterpriseServices.dll
—————————————-
System.Numerics
Assembly Version: 4.0.0.0
Win32 Version: 4.0.30319.33440 built by: FX45W81RTMREL
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Numerics/v4.0_4.0.0.0__b77a5c561934e089/System.Numerics.dll
—————————————-
SMDiagnostics
Assembly Version: 4.0.0.0
Win32 Version: 4.0.30319.34230 built by: FX452RTMGDR
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/SMDiagnostics/v4.0_4.0.0.0__b77a5c561934e089/SMDiagnostics.dll
—————————————-
System.ServiceModel.Internals
Assembly Version: 4.0.0.0
Win32 Version: 4.0.30319.34230 built by: FX452RTMGDR
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.ServiceModel.Internals/v4.0_4.0.0.0__31bf3856ad364e35/System.ServiceModel.Internals.dll
—————————————-
WindowsBase
Assembly Version: 4.0.0.0
Win32 Version: 4.0.30319.34004 built by: FX45W81RTMGDR
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/WindowsBase/v4.0_4.0.0.0__31bf3856ad364e35/WindowsBase.dll
—————————————-
DevExpress.XtraLayout.v13.1
Assembly Version: 13.1.5.0
Win32 Version: 13.1.5.0
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/DevExpress.XtraLayout.v13.1/v4.0_13.1.5.0__b88d1754d700e49a/DevExpress.XtraLayout.v13.1.dll
—————————————-
eOne.SmartConnect.UI
Assembly Version: 20.14.1.25
Win32 Version: 20.14.1.25
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/eOne.SmartConnect.UI/v4.0_20.14.1.25__0beb756d2a6b4456/eOne.SmartConnect.UI.dll
—————————————-
DevExpress.XtraNavBar.v13.1
Assembly Version: 13.1.5.0
Win32 Version: 13.1.5.0
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/DevExpress.XtraNavBar.v13.1/v4.0_13.1.5.0__b88d1754d700e49a/DevExpress.XtraNavBar.v13.1.dll
—————————————-
Accessibility
Assembly Version: 4.0.0.0
Win32 Version: 4.0.30319.33440 built by: FX45W81RTMREL
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/Accessibility/v4.0_4.0.0.0__b03f5f7f11d50a3a/Accessibility.dll
—————————————-
Microsoft.GeneratedCode
Assembly Version: 1.0.0.0
Win32 Version: 4.0.30319.34230 built by: FX452RTMGDR
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Xml/v4.0_4.0.0.0__b77a5c561934e089/System.Xml.dll
—————————————-
eOne.SmartConnect.eConnect2015
Assembly Version: 20.14.1.25
Win32 Version: 20.14.1.25
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/eOne.SmartConnect.eConnect2015/v4.0_20.14.1.25__0beb756d2a6b4456/eOne.SmartConnect.eConnect2015.dll
—————————————-
Microsoft.Dynamics.GP.eConnect
Assembly Version: 14.0.0.0
Win32 Version: 14.00.0532.000
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_32/Microsoft.Dynamics.GP.eConnect/v4.0_14.0.0.0__31bf3856ad364e35/Microsoft.Dynamics.GP.eConnect.dll
—————————————-
System.IdentityModel
Assembly Version: 4.0.0.0
Win32 Version: 4.0.30319.34230 built by: FX452RTMGDR
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.IdentityModel/v4.0_4.0.0.0__b77a5c561934e089/System.IdentityModel.dll
—————————————-
Interop.ADODB
Assembly Version: 2.8.0.0
Win32 Version: 2.8.0.0
CodeBase: file:///C:/Windows/assembly/GAC_MSIL/Interop.ADODB/2.8.0.0__0beb756d2a6b4456/Interop.ADODB.dll
—————————————-

************** JIT Debugging **************
To enable just-in-time (JIT) debugging, the .config file for this
application or computer (machine.config) must have the
jitDebugging value set in the system.windows.forms section.
The application must also be compiled with debugging
enabled.

For example:



When JIT debugging is enabled, any unhandled exception
will be sent to the JIT debugger registered on the computer
rather than be handled by this dialog box.

Dave Dery replied 6 years ago

Why do I feel like this current release of SmartConnect has taken a massive step backwards is functionality and reliability? I get that the software industry is under pressure to constantly innovate. But when did we, as a whole, decide to give up quality control and integrity just for the sake of creating a new release?

Patrick Roth Staff replied 6 years ago

I don’t think I agree with your premise of:

“even the built in connections are failing to open the ODBC because presumably you are not loading the required dll’s in the application, this would explain the issues I am having above.”

I know there is a problem if you open a validation task after it has been created. SC throws tons of errors when you try to re-open it. You’re best off just deleting it and then re-creating it again. It has been fixed internally and will be released when an update it out.

“If you review connectionstrings.com, you will see that almost all of them dictate to use either “driver” or “provider” in the string, which Smartconnect cannot validate because it doesn’t understand the terms”

the connectionstring is the connectionstring – the ODBC layer needs to understand them not the application.

“…because presumably you are not loading the required dll’s “

Well, in this case you don’t need to know the ODBC drivers – that is what the System.Data handler in .net is supposed to figure out for you. So that every application doesn’t need to re-invent the wheel. you talk to the odbc layer and that talks to the correct odbc driver for you.

But it did get me to thinking and I saw in your previous response

“Additionally, if I try to use standard VB syntax and call OdbcConnection or OdbcCommand, SmartConnect also cannot validate because it doesn’t not understand the System.Data.Odbc syntax and it will not let me load them either. “

Your exact error message would be convenient but I assume it is the same as what I got:

Error in Type OdbcConnection is not defined.

So that is an error that would make sense – .NET doesn’t know how to compile that statement, it doesn’t know what it is.
Why would that be?

Well, in order to run any kind of .net script – it has to be compiled. And the only way to do that would be to call the vb or c# compiler directly and compile the method (generated in memory and not to an external dll)

I can’t say I’ve done this personally – it is way easier to just create something in visual studio and let it do the compile details. But when you try to use an object in visual studio you need a reference – so here I would assume System.Data.Odbc.

Because we can’t directly specify that in the Smartconnect UI (in this version anyway) and you don’t want to load EVERYTHING in the GAC in the compilation, there are 8 out-of-the-box items that SmartConnect loads to compile your scripts.

You can find them in the ScriptNamespace table.
select * from ScriptNamespace

So if you wanted to use the ODBC library, you’d want to add it to this table and then your code should compile.

I tested this out on mine (after restarting SC) and the line:
Dim myConn as OdbcConnection = New OdbcConnection(“my connection string”)

compiled fine.
assuming I’d then enter a real connectionstring everything should work fine.

Of course that assumes you have the connectionstring correct and the proper odbc drivers installed.


If you would like to submit an answer or comment, please sign in to the eOne portal.